# 권한 : oracle1
# OS
1. (1호기) SQLPLUS 접속
sqlplus "/ as sysdba"
# sqlplus
1. DB 마운트
startup mount;
2. DB 마운트 상태 확인(선택)
select status, database_status from v$instance;
3. 마운트 DB OPEN
alter database open;
4. DB 마운트 상태 확인(필수)
select status, database_status from v$instance;
5. DB 전자지갑 확인(선택)
SELECT * FROM v$encrytion_wallet;
6. DB 전자지갑 적용
alter system set ENCRYPTION wallet open identified by "key";
7. DB 전자지갑 확인(필수)
SELECT * FROM v$encrytion_wallet;
2. (2호기) SQLPLUS 접속
sqlplus "/ as sysdba"
# sqlplus
1호기와 방법 동일함
오라클 DB 데이터를 매우 빠른 속도로 복구하는 기술이다. 사용자 실수로 인한 데이터 오류, 테이블 삭제, 또는 데이터베이스 전체에 대해 문제가 발생했을 때 매우 빠른 속도로 복구할 수 있다.
FlashBack이 필요한 이유
예를 들어 쇼핑몰을 운영하는 회사에서 인플레이션으로 인해 사장님이 전체 상품에 대해 5% 인상을 이대리에게 요청한다. 이대리는 상품 테이블을 update 명령어로 5% 인상해야 하는데 실수로 50% 인상을 해버렸다.
FlashBack을 사용안하던 과거에는 실수로 상품 table을 삭제했을 때는 EXP/IMP, DATAPUMP, RMAN이 있는데 EXP/IMP, DATAPUMP는 원하는 시점으로 복구하기가 어렵고 RMAN은 가능하지만 데이터 크기가 크면 복구하는데 오래 걸릴 수 있다. 이럴 때 FlashBack table 기능을 이용하면 간단하게 복구할 수 있다.
FRA(Fast Recovery Area)
빠르게 복구할 수 있는 공간을 의미하고 백업 데이터 파일들을 통합 관리하는 기능을 제공한다.
테이블, 데이터 삭제 복구
1. find 명령어로 spfile위치를 확인한다.
find $ORACLE_HOME -name spfile*
2. spfile 백업
cp spfileSTR.ora spfileSTR.ora_20221231
3. UNDO RETENTION 파라미터 변경
-- 1Day = 86400sec
show parameter undo_retention
alter system set undo_retention=86400 scope=spfile;
4. DB 종료, 재시작
-- DB 종료
SHUTDOWN IMMEDIATE
-- DB 시작
STARTUP
5. Test 테이블, 데이터 생성 후 삭제
-- 테이블 생성
CREATE TABLE STR.FRA_TEST
(
FRA_NO VARCHAR2(20)
);
-- 데이터 1건 입력
INSERT INTO STR.FRA_TEST VALUES(TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS'));
COMMIT;
-- 데이터 확인
SELECT * FROM STR.FRA_TEST;
-- 테이블 삭제
DROP TABLE STR.FRA_TEST;
6. FLASHBACK을 이용한 테이블 복구
FLASHBACK TABLE username.table_name TO BEFORE DROP;
테이블은 남아있고 데이터만 삭제 복구
1. Test 테이블, 데이터 생성 후 삭제
-- 테이블 생성
CREATE TABLE STR.FRA_TEST
(
FRA_NO VARCHAR2(20)
);
-- 데이터 5건 입력
INSERT INTO STR.FRA_TEST VALUES('1');
INSERT INTO STR.FRA_TEST VALUES('2');
INSERT INTO STR.FRA_TEST VALUES('3');
INSERT INTO STR.FRA_TEST VALUES('4');
INSERT INTO STR.FRA_TEST VALUES('5');
COMMIT;
-- INSERT 완료 시간 확인
-- 데이터 복구 할떄는 +10초 정도 여유를 두고 복구를 진행한다.
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') AS "COMMIT_TIME" FROM DUAL;
-- 데이터 전체 삭제
DELETE FROM STR.FRA_TEST;
COMMIT;
2. 데이터 복구
-- 데이터 전체 복구
-- (INSERT 완료 시간 확인) 쿼리에 COMMIT_TIME을 확인한다.
-- 데이터 복구 할떄는 +10초 정도 여유를 두고 복구를 진행한다. (2022/12/19 10:00:00)
INSERT INTO STR.FRA_TEST
SELECT * FROM STR.FRA_TEST
AS OF TIMESTAMP TO_TIMESTAMP('2022/12/19 10:00:00','YYYY/MM/DD HH24:MI:SS');
COMMIT;
부분 데이터 삭제 복구
1. Test 데이터 삭제
-- 부분 데이터 삭제
-- FRA_NO 3 데이터 삭제
DELETE FROM STR.FRA_TEST
WHERE FRA_NO = '3';
COMMIT;
2. 부분 데이터 복구(원본 테이블은 두고 복제 테이블을 만든다.)
-- CTAS를 이용한 FRA_TEST_20221219_1000 테이블 생성 및 데이터 복구
-- (INSERT 완료 시간 확인) 쿼리에 COMMIT_TIME을 확인한다.
-- 데이터 복구 할떄는 +10초 정도 여유를 두고 복구를 진행한다. (2022/12/19 10:00:00)
CREATE TABLE STR.FRA_TEST_20221219_1000
AS
SELECT * FROM STR.FRA_TEST
AS OF TIMESTAMP TO_TIMESTAMP('2022/12/19 10:00:00','YYYY/MM/DD HH24:MI:SS');
-- 데이터 1건 복구 확인
SELECT * FROM * STR.FRA_TEST;
-- 테이블 삭제
DROP TABLE STR.FRA_TEST_20221219_1000 PURGE;
DROP TABLE STR.FRA_TEST PURGE;
3. 복제 테이블에서 원본 테이블과 빠진 데이터를 확인해서 Insert
-- 데이터 1건 복구 SQL
INSERT INTO STR.FRA_TEST
SELECT FRA_NO
FROM STR.FRA_TEST_20221219_1000
WHERE FRA_NO NOT IN (
SELECT FRA_NO FROM STR.FRA_TEST);
COMMIT;
데이터베이스 전체 복구
1. 아카이브 & FRA 디렉터리 생성, root 실행
mkdir /FRA
chown oracle.dba /FRA
ls -ld /FRA
2. FRA 파라미터 변경
-- 1Day = 1440min defualt
show parameter DB_FLASHBACK_RETENTION_TARGET
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=20G scope=spfile; -- 용량 설정
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/FRA' scope=spfile; -- 디렉터리 설정
3. DB 종료 후 Mount 상태로 올려야한다.
SHUTDOWN IMMEDIATE
STARTUP MOUNT
4. 아카이브 로그 모드, Flashback 모드로 변경 후 DB Open
ALTER DATABASE ARCHIVELOG;
-- 아카이브 로그 확인
ARCHIVE LOG LIST;
-- Flashback 모드 변경
ALTER DATABASE FLASHBACK ON;
-- Flashback 모드 확인
SELECT FLASHBACK_ON FROM V$DATABASE;
-- DB OPEN
ALTER DATABASE OPEN;
FRA를 사용하려면 아카이브 로그모드와 FlashBack on이 되어있어야 한다.
5. 아카이브 & Flashback 상태 확인
SELECT NAME, LOG_MODE, FLASHBACK_ON FROM V$DATABASE;
FlashBack이 실행되어 있으면 리눅스에서 RVWR 프로세스가 실행 중이다.
-- Linux 실행 RVWR(Recovery Writer) 프로세스 확인
ps -efl | grep [r]vwr
6. 시작 시간 확인
SELECT
TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') AS "START_TIME" ,
TIMESTAMP_TO_SCN(SYSDATE) AS "SCN"
FROM DUAL;
7. DB mount 상태로 이동 mount 상태에서 복구해야 한다.
STARTUP MOUNT
8. DB 전체 Flashback으로 원하는 시간으로 복구 후 DB를 RESETLOGS로 Open 한다.
-- (시작 시간 확인) 쿼리에 START_TIME을 확인한다. (2022/12/19 10:00:00)
FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2022/12/19 10:00:00','YYYY/MM/DD HH24:MI:SS');
-- DB OPEN
ALTER DATABASE OPEN RESETLOGS;
FRA 관리 방법
1. FRA 디렉터리에 어떤 파일들이 있는지 확인, FRA 디렉터리 사용량 확인할 수 있다.
-- sqlplus / as sysdba 접속
sqlplus / as sysdba
SET LINES 180
COL NAME FOR A20
-- FRA 디렉터리에 어떤 파일들이 있는지 확인, FRA 디렉터리 사용량 확인할 수 있다.
SELECT
FILE_TYPE,
PERCENT_SPACE_USED,
PERCENT_SPACE_RECLAIMABLE, NUMBER_OF_FILES
FROM V$FLASH_RECOVERY_AREA_USAGE;
SELECT
NAME,
ROUND(SPACE_LIMIT/1024/1024/1024) AS "SPACE(GB)",
ROUND(SPACE_USED/1024/1024/1024,2) AS "USED(GB)"
FROM V$RECOVERY_FILE_DEST;
백업 파일 삭제
1. Recovery Manager에 접속 후 백업 파일 삭제
-- rman에서 로그 확인 및 백업 파일 삭제
rman target /
REPORT OBSOLETE;
LIST ARCHIVELOG ALL;
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
DELETE EXPIRED BACKUP;
DELETE EXPIRED ARCHIVELOG ALL;
DELETE FORCE OBSOLETE;
FRA 원상 복구
1. 파라미터 원복
ALTER SYSTEM RESET UNDO_RETENTION;
ALTER SYSTEM RESET DB_RECOVERY_FILE_DEST_SIZE;
ALTER SYSTEM RESET DB_RECOVERY_FILE_DEST;
2. DB 종료 후 Mount 상태로 Open
-- DB 종료
SHUTDOWN IMMEDIATE
-- DB 시작(Mount 상태)
STARTUP MOUNT
3. Flashback, 아카이브 로그 OFF 후 DB Open
-- Flashback 기능 OFF
ALTER DATABASE FLASHBACK OFF;
-- 아카이브 로그 OFF
ALTER DATABASE NOARCHIVELOG;
-- DB OPEN
ALTER DATABASE OPEN;
4. DB 상태 확인
-- DB 상태 확인
SELECT NAME, LOG_MODE, FLASHBACK_ON FROM V$DATABASE;
vi ~/.zshrc # vi로 열어서 아래의 내용을 붙여 넣는다
#Terminal Prompt
export PS1="%F{cyan}%n@%m%f %F{green}%1~%f %# "
#SQL Developer
export SQLPATH=/Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin
alias sqld="cd $SQLPATH"
alias sqlp="$SQLPATH/sqldeveloper &"
alias sqlpe="$SQLPATH/sqldeveloper --AddVMOption=-Duser.language=en &"
#VirtualBox Linux
alias strdb="ssh oracle@192.168.0.111"
alias strdbf="sftp oracle@192.168.0.111"
#Safari
alias safari="open /Applications/Safari.app"
#AWR 환경에 맞는 폴더를 만들어서 입력한다.
alias AWRD="cd /Volumes/EXT1TB/AWR"
# Lang Set
#export LANG=en_US
#export LANG=ko_KR.UTF-8
source ~/.zshrc
AWR 분석에 가장 중요한 기본 분석 주기(DEFUALT 1시간에 8일)
SELECT
DBID
, SNAP_INTERVAL
, RETENTION
, TOPNSQL
FROM DBA_HIST_WR_CONTROL;
분석 주기 변경
-- 10분 단위 (보관주기 30일)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>10, RETENTION=>30*24*60);
-- 10분 단위 (보관주기 90일)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>10, RETENTION=>90*24*60);
-- 10분 단위 (보관주기 30일)(TOPNSQL 100)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>10, RETENTION=>30*24*60, TOPNSQL=>'100');
-- 10분 단위 (보관주기 90일)(TOPNSQL 100) -> 업계에서 많이 사용
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>10, RETENTION=>90*24*60, TOPNSQL=>'100');
-- 30분 단위 (보관주기 90일)(TOPNSQL 100)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>30, RETENTION=>90*24*60, TOPNSQL=>'100');
-- Default값 변경(60분 단위 8일 보관)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>60, RETENTION=>8*24*60, TOPNSQL=>'DEFAULT');
TOPNSQL Default값 분석
SELECT *
FROM V$PARAMETER
WHERE NAME = 'statistics_level';
만약 TYPICAL이면 값이 30이고 ALL이면 100이다.
AWR 보관 주기 관련해서 가장 중요한 건 SYSAUX 테이블스페이스 공간이 충분이 확보되어야 한다. AWR의 interval을 짧게 주고 retension을 길게 주면 빠른 속도로 데이터가 늘어나기 때문에 꼭 확인해주어야 한다.
SYSAUX TABLESPACE 상태, 사이즈 확인
SELECT
TABLESPACE_NAME,
FILE_NAME,
BYTES/1024/1024 AS "SIZE(GB)"
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'SYSAUX'
ORDER BY 1, 2;
SNAP_ID 분석
SELECT
DBID,
SNAP_ID,
TO_CHAR(END_INTERVAL_TIME, 'YYYY/MM/DD(DY) HH24:MI') AS "END_INTERVAL_TIME"
FROM
DBA_HIST_SNAPSHOT
WHERE END_INTERVAL_TIME >= TRUNC(SYSDATE) -1
AND END_INTERVAL_TIME < TRUNC(SYSDATE) +1
ORDER BY SNAP_ID;
AWR 보고서 디렉터리 생성
cd /home/oracle
mkdir AWR
AWR 보고서 생성
-- 리눅스 서버 접속(sqlplus / as ysdba 실행)
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@/oracle/app/oracle/product/19.0.0.0/db_1/rdbms/admin/awrrpt.sql
AWR 비교 보고서 생성
-- 리눅스 서버 접속(sqlplus / as ysdba 실행)
@$ORACLE_HOME/rdbms/admin/awrddrpt.sql
@/oracle/app/oracle/product/19.0.0.0/db_1/rdbms/admin/awrddrpt.sql
AWR 보고서에서 가장 많이 보는 건 오라클 이벤트를 확인하고 어디에 문제 있는지 분석하고 TOPSQL을 확인하는 게 중요하다. AWR에서 가장 중요한 건 서버에 들어갈 AWR 일자와 시간을 어떻게 뽑느냐가 관건이다. AWR interval을 짧게 주어야 보다 정확하게 분석이 가능하다.
오라클은 디스크에서 데이터를 읽어 오고 처리를 한 후 다시 디스크에 집어넣습니다. 즉 오라클이 다루는 데이터는 디스크에서 꺼내 오고 디스크로 돌아가는 것입니다. 그래서 오라클과 디스크는 떼려야 땔 수 없는 관계이다.
디스크 동작 방법
디스크를 LP판이라고 생각하고 디스크를 떠올리자
디스크는 계속 회전하고 그 위를 헤드가 움직여서 데이터를 읽거나 기록한다.
디스크는 1분에 1만 회정도 회전하고 있다. 엑추에이터는 초당 100회 정도 움직일 수 있다.
디스크의 동작
I/O 처리에 필요한 디스크의 동작
데이터를 읽기 또는 기록하기 위해서는 원하는 트랙을 찾지 않으면 안 된다. 이를 디스크 용어로 시크(Seek)라고 한다. 그 후에 원하는 정보를 읽어 낼 수 있는 위치가 회전해서 다가올 때까지 기다리고 있다. 기다리는 시간을 회전을 기다리는 시간이라고 부른다. 그리고서야 데이터를 읽고 쓴다. 디스크의 I/O는 DBMS에서 필요하지만 가능한 줄여야 하는 부분이다.
어떻게 I/O의 대기 시간을 줄일까?
시퀀셜 액세스에 관한 설명이 필요하다. 시퀀셜은 순서를 따라서라는 의미의 순차를 의미한다.
시작점부터 마지막까지 중간 부분을 빠뜨리지 않고 전부 엑세스(읽기/ 쓰기)하는 것이다.
풀 스캔(테이블의 모든 데이터를 읽어 오는 것) 할 때 메모리에 데이터가 없다면 시퀀셜 액세스가 발생한다.
테이블의 크기가 있고 시퀀셜 엑세스로 모든 데이터를 가지고 오려고 할 때 시간이 오래 걸려 인덱스라는 발생이 나오게 되었다.
책에서 무언가를 찾을 때 우리는 책의 모든 내용을 보지 않는다. 대부분 인덱스를 사용한다. 이미 알고 있듯이 인덱스에는 키워드가 순서대로 나열되어 있고 해당 페이지 번호도 쓰여있다. 데이터베이스의 인덱스도 마찬가지이다. 데이터베이스의 인덱스에는 색인할 때 사용하는 키값(SQL의 where절에 적는 조건 값)과 그 키가 존재하고 있는 위치가 기록되어있다.
인덱스 사용의 예
예를 들어 인덱스에서 해당 내용에 대한 정보를 확인 후 해당 페이지를 펼쳐 원하는 정보를 얻는다. SQL문도 마찬가지로 WHERE절에 찾고 싶은 정보를 기술하고 SELECT 뒤에 알고 싶은 항목을 적는다.
SELECT "소속회사" FROM "개인 데이터" WHERE "이름" = "현성";
이 SQL문을 인덱스를 사용해서 처리할 때는 우선 이름이 실려있는 인덱스를 조사한다. 그 결과로 어드레스(ROWID)를 얻을 수 있고, 그 어드레스로 데이터를 읽어 온다. 읽어 온 데이터에 현성이라는 모든 데이터가 있으므로 그 안에서 소속회사의 데이터를 사용자에게 반환한다.
Q&A
Q. 만약 인덱스 자체의 크기가 커지면 크기가 큰 테이블을 조회하는 것과 마찬가지로 처리하는 데 필요한 시간이 늘어날까?
A. 그런 일은 발생하지 않는다. 왜냐하면 오라클은 인덱스를 인덱스의 인덱스를 붙이는 것과 같은 형태를 여러 단계로 구성하기 때문이다.
여러 단계로 구성된 구조를 트리구조라고 부른다. 장점은 인덱스의 필요 없는 부분은 읽지 않고 끝난다는 것이다.
랜덤 인덱스
인덱스를 사용했을 때는 필요한 부분만 읽어 오면 충분하지만 필요한 부분이 디스크 위에 연속적으로 있는 경우는 거의 없습니다. 따라서 헤드를 띄엄띄엄 접근하게 됩니다. 이렇게 접근하는 방식을 랜덤 액세스라고 한다. 시퀀셜 액세스와 반대의 의미를 가진다. 랜덤 액세스를 디스크 시점에서 생각해보면 비효율적이다. 오라클 블록 크기를 4KB라고 가정하면 시크와 회전 시간을 기다리는 것에 시간을 소비하기 때문에 1초 동안 약 400KB(100회의 시크 X 1회 I/O의 크기 4KB) 밖에 읽어 오지 않는다. 실제로 데이터 전송의 효율에서 바라보면 DBMS의 I/O도 같을 수밖에 없다. 시크를 반복하기 때문에 DBMS에서 사용하는 디스크의 지표는 IOPS(초당 수행 가능한 I/O 횟수)가 중요하다고 볼 수 있다. 대부분 디스크는 IOPS가 100회에서 200회 정도이다. 따라서 한 개나 두 개의 디스크를 사용해서 데이터베이스를 만들어 버리면 부하가 집중적으로 발생했을 때 시크가 요청을 따라잡을 수 없으므로 디스크에 병목 현상이 발생하게 된다.
Tip.
Q. 접근하려는 데이터가 전체 데이터의 15% 미만일 경우에만 인덱스 액세스가 유리하다고 하는 이유는 무엇일까?
A. 시퀀셜 엑세스와 랜덤 액세스의 특징 때문이다. 테이블의 데이터가 대량이고 그중 한 개의 행을 꺼내야 한다면 당연히 인덱스 액세스가 빠르게 찾아낼 수 있다. 그에 비해 모든 데이터를 보려고 할 때 매번 인덱스를 찾은 후에 데이터를 찾아가면 오히려 속도가 느려지게 된다.
Q. 만약 데이터가 50%라면? 데이터가 25%라면?
A. 디스크에서의 랜덤 액세스는 데이터를 읽어 오는 효율성이 시퀀셜 액세스보다 떨어진다라는 특성이 중요하다. 예를 들어 테이블에 2만 건의 데이터가 저장되어 있다고 하고 그중 절반인 1만 건을 꺼낸다고 가정할 때 여기서 한 행은 4KB라고 하겠다. 이때 지금까지 사용해 왔던 디스크 성능 값을 사용해 계산해 보면 랜덤 액세스로 100초가 걸린다. 2만 건의 전부를 읽어오는 시퀀셜 액세스로 모든 것을 읽어 온다고 해도 약 4초면 끝난다. 즉 모든 데이터가 아니더라도 일정 크기 이상의 데이터를 읽는다면 디스크 특성상 시퀀셜 액세스를 사용해서 테이블을 풀 스캔 하는 것이 더 빠르다는 것을 알 수 있다.
단, 실제로 캐시에 데이터가 보관되어있는 경우도 있고 한 개의 블록에 여러 행의 데이터가 보관되어있는 경우도 있어서 1회의 I/O로 많은 데이터를 읽어오는 경우도 있다. 그래서 단순히 임계치가 15%라고 말할 수는 없다.
데이터를 보증하기 위한 디스크
오라클의 프로세스가 비정상적 종료되어도 데이터는 무사하다. DBMS는 어떠한 장애에도 견뎌야 한다. 어떻게 커밋한 데이터를 지킬 수 있을까? 데이터를 변경한 후에 커밋이라고 입력하면 오라클은 데이터를 디스크에 기록한다.
그러면 속도가 느리지 않을까? 속도를 빠르게 하기 위한 장치가 있다. DBMS들은 고속화를 위한 장치를 가지고 있어서 그 구조가 복잡한 것이다.
시퀀셜은 어떤 의미인가?
랜덤 엑세스는 db file sequential read라고 표시되고 시퀀셜 액세스는 db file scattered read라고 표시된다.
db file scattered read는 시퀀셜 하게 읽어 오는 것이므로 여러 개의 블록을 읽어온다. 오라클은 데이터 블록 단위로 메모리에 배치한다. 즉 여러 개의 블록이 연속하지 않은 곳에 놓인다. 따라서 scattered라고 표시한다. 그에 반해 db file sequential read는 단일 블록을 읽어 오므로 읽어 온 데이터는 당연히 메모리에서 연속된다. 오라클 메모리에서 연속되어 있으므로 sequential로 표시된다.
출처
그림으로 공부하는 오라클 구조
문제가 될시 ks12b9189@naver.com으로 메일 주시면 게시글 바로 삭제하겠습니다.
