내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4
방법 : 오라클 11g R2 begin backup 후 end backup 을 하지 않고 복구(샘플데이터 생성 후 파일 복사)
조금 다른 테스트 두가지
오라클 11g R2 begin backup 후 end backup 을 하지 않고 복구(샘플데이터 생성 후 파일 복사) https://positivemh.tistory.com/688
오라클 11g R2 begin backup 후 end backup 을 하지 않고 복구(파일 복사 후 샘플데이터 생성) https://positivemh.tistory.com/689
begin (hot) backup 후 테스트 데이터 소량, 대량 각각 생성 후
controlfile, datafile, redo 등 파일 복사 후 end backup을 하지 않은 상태에서 db 강제종료
이후 해당 파일과 아카이브 로그를 이용해 복구 할 시 정상적으로 db가 올라오는지를 테스트함
테스트1. begin backup 후 소량 데이터 삽입 후 파일복사
테스트1_1. recover 후 db open
테스트1_2. end backup 후 db open
테스트2. begin backup 후 대량 데이터 삽입 후 파일복사
테스트2_1. recover 후 db open
테스트2_2. end backup 후 db open
테스트3. begin backup 후 대량 데이터 삽입 후 파일복사 후 end backup(정상적인 hot 백업)
테스트3_1. recover 후 db open
테스트3_2. end backup 후 db open
테스트1. begin backup 후 소량 데이터 삽입 후 파일복사
기존 테이블스페이스 및 백업 상태 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> set lines 200 pages 1000 col file_name for a70 col tablespace_name for a10 select d.tablespace_name, file_id, d.file_name, b.status, b.change# from dba_data_files d, v$backup b where d.file_id = b.file# order by 2; TABLESPACE FILE_ID FILE_NAME STATUS CHANGE# ---------- ---------- ---------------------------------------------------------------------- ------------------ ---------- SYSTEM 1 /oracle/app/oracle/oradata/ORCL11/system01.dbf NOT ACTIVE 23627880 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf NOT ACTIVE 23627886 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf NOT ACTIVE 23627891 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf NOT ACTIVE 23627897 |
아카이브모드 확인
1 2 3 4 5 6 7 | SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /oracle/app/oracle/arch Oldest online log sequence 17 Next log sequence to archive 19 Current log sequence 19 |
begin backup 진행
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> alter tablespace system begin backup; Tablespace altered. SQL> alter tablespace sysaux begin backup; Tablespace altered. SQL> alter tablespace undotbs1 begin backup; Tablespace altered. SQL> alter tablespace users begin backup; Tablespace altered. |
백업 상태 확인 v$backup 의 status 컬럼
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> set lines 200 pages 1000 col file_name for a70 col tablespace_name for a10 select d.tablespace_name, file_id, d.file_name, b.status, b.change# from dba_data_files d, v$backup b where d.file_id = b.file# order by 2; TABLESPACE FILE_ID FILE_NAME STATUS CHANGE# ---------- ---------- ---------------------------------------------------------------------- ------------------ ---------- SYSTEM 1 /oracle/app/oracle/oradata/ORCL11/system01.dbf ACTIVE 23691111 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf ACTIVE 23691117 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf ACTIVE 23691122 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf ACTIVE 23691127 |
status 가 active로 변경됨(begin backup 시작됨)
샘플 데이터용 테이블 생성(소량)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | SQL> create table newimsi (col number) tablespace users; Table created. SQL> insert into newimsi values (100); 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> commit ; Commit complete. SQL> select * from newimsi; COL ---------- 100 100 100 |
100 이 세건 insert 됨
데이터 파일 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | $ cd /oracle/app/oracle/oradata/ORCL11 $ ls -al total 4686900 drwxr-xr-x 2 oracle dba 4096 Jan 5 02:04 . drwxr-x---. 3 oracle dba 4096 Jan 6 02:04 .. -rw-r----- 1 oracle dba 10076160 Jan 6 02:05 control01.ctl -rw-r----- 1 oracle dba 10076160 Jan 6 02:05 control02.ctl -rw-r----- 1 oracle dba 209715712 Jan 6 02:04 redo01.log -rw-r----- 1 oracle dba 209715712 Jan 5 02:35 redo02.log -rw-r----- 1 oracle dba 209715712 Jan 5 02:40 redo03.log -rw-r----- 1 oracle dba 2673876992 Jan 6 02:03 sysaux01.dbf -rw-r----- 1 oracle dba 765468672 Jan 6 02:03 system01.dbf -rw-r----- 1 oracle dba 104865792 Jan 5 22:00 temp01.dbf -rw-r----- 1 oracle dba 597696512 Jan 6 02:03 undotbs01.dbf -rw-r----- 1 oracle dba 104865792 Jan 6 02:03 users01.dbf |
파일 백업
1 2 3 4 5 6 7 8 9 10 11 12 13 | $ cd /oracle/app/oracle/oradata/ORCL11/ $ mkdir -p ../hot $ cp -av ./* ../hot/ `./control01.ctl' -> `../hot/control01.ctl' `./control02.ctl' -> `../hot/control02.ctl' `./redo01.log' -> `../hot/redo01.log' `./redo02.log' -> `../hot/redo02.log' `./redo03.log' -> `../hot/redo03.log' `./sysaux01.dbf' -> `../hot/sysaux01.dbf' `./system01.dbf' -> `../hot/system01.dbf' `./temp01.dbf' -> `../hot/temp01.dbf' `./undotbs01.dbf' -> `../hot/undotbs01.dbf' `./users01.dbf' -> `../hot/users01.dbf' |
테스트1_1. recover 후 db open
end backup 없이 db 강제 종료
1 2 | SQL> shutdown abort ORACLE instance shut down. |
데이터 파일 등 삭제
1 2 3 | $ cd /oracle/app/oracle/oradata/ORCL11 $ rm -rf ./* $ ls |
모두 지워짐
파일 복원
1 2 3 4 5 6 7 8 9 10 11 | $ cp -av ../hot/* . `../hot/control01.ctl' -> `./control01.ctl' `../hot/control02.ctl' -> `./control02.ctl' `../hot/redo01.log' -> `./redo01.log' `../hot/redo02.log' -> `./redo02.log' `../hot/redo03.log' -> `./redo03.log' `../hot/sysaux01.dbf' -> `./sysaux01.dbf' `../hot/system01.dbf' -> `./system01.dbf' `../hot/temp01.dbf' -> `./temp01.dbf' `../hot/undotbs01.dbf' -> `./undotbs01.dbf' `../hot/users01.dbf' -> `./users01.dbf' |
db mount 상태로 기동
1 2 3 4 5 6 7 8 9 | SQL> startup mount ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2254952 bytes Variable Size 1375733656 bytes Database Buffers 754974720 bytes Redo Buffers 4923392 bytes Database mounted. |
상태확인
1 2 3 4 5 6 7 8 9 10 11 | SQL> set lines 200 pages 1000 col file_name for a70 col tablespace_name for a10 select d.tablespace_name, file_id, d.file_name, b.status, b.change# from dba_data_files d, v$backup b where d.file_id = b.file# order by 2; * ERROR at line 2: ORA-01219: database not open: queries allowed on fixed tables/views only |
현재 open 상태가 아니기 때문에 dba_data_files 뷰를 볼수 없음
v$backup 뷰만 이용해 확인
1 2 3 4 5 6 7 8 | SQL> select * from v$backup; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 1 ACTIVE 23691111 06-JAN-21 2 ACTIVE 23691117 06-JAN-21 3 ACTIVE 23691122 06-JAN-21 4 ACTIVE 23691127 06-JAN-21 |
현재 데이터파일 백업 status 가 여전히 active 임(begin backup 상태)
db open 시도
1 2 3 4 5 6 | SQL> alter database open; alter database open * ERROR at line 1: ORA-10873: file 1 needs to be either taken out of backup mode or media recovered ORA-01110: data file 1: '/oracle/app/oracle/oradata/ORCL11/system01.dbf' |
바로 open 되지 않음
recover database 시도
1 2 | SQL> recover database; Media recovery complete. |
정상적으로 복구됨
v$backup 뷰 확인
1 2 3 4 5 6 7 8 | SQL> select * from v$backup; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 1 NOT ACTIVE 23691111 06-JAN-21 2 NOT ACTIVE 23691117 06-JAN-21 3 NOT ACTIVE 23691122 06-JAN-21 4 NOT ACTIVE 23691127 06-JAN-21 |
recover 명령후 not active 상태로 변함(end backup 을 입력한 상태와 같음)
db open 후 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL> alter database open; Database altered. SQL> set lines 200 pages 1000 col file_name for a70 col tablespace_name for a10 select d.tablespace_name, file_id, d.file_name, b.status, b.change# from dba_data_files d, v$backup b where d.file_id = b.file# order by 2; TABLESPACE FILE_ID FILE_NAME STATUS CHANGE# ---------- ---------- ---------------------------------------------------------------------- ------------------ ---------- SYSTEM 1 /oracle/app/oracle/oradata/ORCL11/system01.dbf NOT ACTIVE 23691111 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf NOT ACTIVE 23691117 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf NOT ACTIVE 23691122 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf NOT ACTIVE 23691127 |
정상적으로 open 되고 status 도 계속 not active 상태임
데이터 확인
1 2 3 4 5 6 7 | SQL> select * from newimsi; COL ---------- 100 100 100 |
데이터가 정상적으로 확인됨
테스트1_2. end backup 후 db open
1_2 테스트를위해 다시 db 강제 종료
1 2 | SQL> shutdown abort ORACLE instance shut down. |
데이터 파일 등 삭제
1 2 3 | $ cd /oracle/app/oracle/oradata/ORCL11 $ rm -rf ./* $ ls |
모두 지워짐
처음에 백업한 파일로 복원
1 2 3 4 5 6 7 8 9 10 11 | $ cp -av ../hot/* . `../hot/control01.ctl' -> `./control01.ctl' `../hot/control02.ctl' -> `./control02.ctl' `../hot/redo01.log' -> `./redo01.log' `../hot/redo02.log' -> `./redo02.log' `../hot/redo03.log' -> `./redo03.log' `../hot/sysaux01.dbf' -> `./sysaux01.dbf' `../hot/system01.dbf' -> `./system01.dbf' `../hot/temp01.dbf' -> `./temp01.dbf' `../hot/undotbs01.dbf' -> `./undotbs01.dbf' `../hot/users01.dbf' -> `./users01.dbf' |
db mount 상태로 기동
1 2 3 4 5 6 7 8 9 | SQL> startup mount ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2254952 bytes Variable Size 1375733656 bytes Database Buffers 754974720 bytes Redo Buffers 4923392 bytes Database mounted. |
v$backup 뷰만 이용해 확인
1 2 3 4 5 6 7 8 | SQL> select * from v$backup; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 1 ACTIVE 23691111 06-JAN-21 2 ACTIVE 23691117 06-JAN-21 3 ACTIVE 23691122 06-JAN-21 4 ACTIVE 23691127 06-JAN-21 |
현재 데이터파일 백업 status 가 여전히 active 임(begin backup 상태)
db open 시도
1 2 3 4 5 6 | SQL> alter database open; alter database open * ERROR at line 1: ORA-10873: file 1 needs to be either taken out of backup mode or media recovered ORA-01110: data file 1: '/oracle/app/oracle/oradata/ORCL11/system01.dbf' |
바로 open 되지 않음
end backup 진행
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> alter tablespace system end backup; Tablespace altered. SQL> alter tablespace sysaux end backup; Tablespace altered. SQL> alter tablespace undotbs1 end backup; Tablespace altered. SQL> alter tablespace users end backup; Tablespace altered. |
정상적으로 end backup 명령이 실행됨
v$backup 뷰 확인
1 2 3 4 5 6 7 8 | SQL> select * from v$backup; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 1 NOT ACTIVE 23691111 06-JAN-21 2 NOT ACTIVE 23691117 06-JAN-21 3 NOT ACTIVE 23691122 06-JAN-21 4 NOT ACTIVE 23691127 06-JAN-21 |
end backup 명령후 not active 상태로 변함
db open 후 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL> alter database open; Database altered. SQL> set lines 200 pages 1000 col file_name for a70 col tablespace_name for a10 select d.tablespace_name, file_id, d.file_name, b.status, b.change# from dba_data_files d, v$backup b where d.file_id = b.file# order by 2; TABLESPACE FILE_ID FILE_NAME STATUS CHANGE# ---------- ---------- ---------------------------------------------------------------------- ------------------ ---------- SYSTEM 1 /oracle/app/oracle/oradata/ORCL11/system01.dbf NOT ACTIVE 23691111 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf NOT ACTIVE 23691117 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf NOT ACTIVE 23691122 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf NOT ACTIVE 23691127 |
정상적으로 open 되고 status 도 계속 not active 상태임
데이터 확인
1 2 3 4 5 6 7 | SQL> select * from newimsi; COL ---------- 100 100 100 |
데이터가 정상적으로 확인됨
테스트1 결론 : end backup을 찍지 않고 hot 백업본으로 복구하려할 때 recover 명령 또는 end backup 이 필요함
recover 시 바로 complete가 나오고 바로 open이 가능함
end backup 시 바로 open이 가능함
begin backup 이후 파일 복사 전 만들어진 테이블은 end backup 없이 abort 되어도 복구가 가능함
테스트2. begin backup 후 대량 데이터 삽입 후 파일복사
기존 테이블스페이스 및 백업 상태 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> set lines 200 pages 1000 col file_name for a70 col tablespace_name for a10 select d.tablespace_name, file_id, d.file_name, b.status, b.change# from dba_data_files d, v$backup b where d.file_id = b.file# order by 2; TABLESPACE FILE_ID FILE_NAME STATUS CHANGE# ---------- ---------- ---------------------------------------------------------------------- ------------------ ---------- SYSTEM 1 /oracle/app/oracle/oradata/ORCL11/system01.dbf NOT ACTIVE 23691111 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf NOT ACTIVE 23691117 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf NOT ACTIVE 23691122 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf NOT ACTIVE 23691127 |
아카이브모드 확인
1 2 3 4 5 6 7 | SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /oracle/app/oracle/arch Oldest online log sequence 18 Next log sequence to archive 20 Current log sequence 20 |
begin backup 진행
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> alter tablespace system begin backup; Tablespace altered. SQL> alter tablespace sysaux begin backup; Tablespace altered. SQL> alter tablespace undotbs1 begin backup; Tablespace altered. SQL> alter tablespace users begin backup; Tablespace altered. |
백업 상태 확인 v$backup 의 status 컬럼
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> set lines 200 pages 1000 col file_name for a70 col tablespace_name for a10 select d.tablespace_name, file_id, d.file_name, b.status, b.change# from dba_data_files d, v$backup b where d.file_id = b.file# order by 2; TABLESPACE FILE_ID FILE_NAME STATUS CHANGE# ---------- ---------- ---------------------------------------------------------------------- ------------------ ---------- SYSTEM 1 /oracle/app/oracle/oradata/ORCL11/system01.dbf ACTIVE 23712427 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf ACTIVE 23712433 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf ACTIVE 23712439 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf ACTIVE 23712445 |
status 가 active로 변경됨(begin backup 시작됨)
샘플 데이터용 테이블 생성(대량)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | SQL> create table newimsi2(cola varchar2(20), colb number, colc number, cold varchar2(30), cole varchar2(30), colf varchar2(30), colg number, colh varchar2(30), coli varchar2(30), colj varchar2(30)) tablespace users; Table created. SQL> set serveroutput on; DECLARE TYPE tbl_ins IS TABLE OF newimsi2%ROWTYPE INDEX BY BINARY_INTEGER; w_ins tbl_ins; BEGIN FOR i IN 1..5000000 LOOP w_ins(i).COLA :=i; w_ins(i).COLB :=300000; w_ins(i).COLC :=999; w_ins(i).COLD :='DDDDDDDDDDDDDDDD'; w_ins(i).COLE :='EEEEEEEEEEEEEEEE'; w_ins(i).COLF :='FFFFFFFFFFFFFFFF'; w_ins(i).COLG :=9999999; w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH'; w_ins(i).COLI :='IIIIIIIIIIIIIIIllllIIIIIIIIIII'; w_ins(i).COLJ :='AAAAAAAAAAAAAAAAAAAAAAAAAA'; END LOOP; FORALL i in 1..5000000 INSERT INTO newimsi2 VALUES w_ins(i); COMMIT; END; / PL/SQL procedure successfully completed. SQL> / PL/SQL procedure successfully completed. |
벌크 insert 구문 2번 실행
데이터 건수 확인
1 2 3 4 5 | SQL> select count(*) from newimsi2; COUNT(*) ---------- 10000000 |
1억개 row가 생성됨
테이블 용량 확인
1 2 3 4 5 6 7 8 | SQL> select segment_name, bytes/1024/1024 mb from dba_segments where segment_name = 'NEWIMSI2'; SEGMENT_NAME MB ------------- ----- NEWIMSI2 1792 |
1.7gb 생성됨
scn 확인(v$backup 의 chance# 컬럼)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> set lines 200 pages 1000 col file_name for a70 col tablespace_name for a10 select d.tablespace_name, file_id, d.file_name, b.status, b.change# from dba_data_files d, v$backup b where d.file_id = b.file# order by 2; TABLESPACE FILE_ID FILE_NAME STATUS CHANGE# ---------- ---------- ---------------------------------------------------------------------- ------------------ ---------- SYSTEM 1 /oracle/app/oracle/oradata/ORCL11/system01.dbf ACTIVE 23712427 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf ACTIVE 23712433 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf ACTIVE 23712439 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf ACTIVE 23712445 |
change#이 변경되지 않음(begin backup 상태라서)
자세한 내용은 [오라클 Hot backup시 변경이 발생한다면? https://positivemh.tistory.com/337] 확인
테스트1 hot 백업 파일 삭제
1 2 3 | $ cd /oracle/app/oracle/oradata/hot $ rm -rf ./* $ ls |
데이터 파일 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | $ cd /oracle/app/oracle/oradata/ORCL11 $ ls -al total 6681672 drwxr-xr-x 2 oracle dba 4096 Jan 6 02:15 . drwxr-x---. 4 oracle dba 4096 Jan 6 02:05 .. -rw-r----- 1 oracle dba 10076160 Jan 6 02:54 control01.ctl -rw-r----- 1 oracle dba 10076160 Jan 6 02:54 control02.ctl -rw-r----- 1 oracle dba 209715712 Jan 6 02:54 redo01.log -rw-r----- 1 oracle dba 209715712 Jan 6 02:49 redo02.log -rw-r----- 1 oracle dba 209715712 Jan 6 02:49 redo03.log -rw-r----- 1 oracle dba 2673876992 Jan 6 02:54 sysaux01.dbf -rw-r----- 1 oracle dba 765468672 Jan 6 02:54 system01.dbf -rw-r----- 1 oracle dba 104865792 Jan 5 22:00 temp01.dbf -rw-r----- 1 oracle dba 597696512 Jan 6 02:54 undotbs01.dbf -rw-r----- 1 oracle dba 2147491840 Jan 6 02:54 users01.dbf |
파일 백업
1 2 3 4 5 6 7 8 9 10 11 12 | $ cd /oracle/app/oracle/oradata/ORCL11/ $ cp -av ./* ../hot/ `./control01.ctl' -> `../hot/control01.ctl' `./control02.ctl' -> `../hot/control02.ctl' `./redo01.log' -> `../hot/redo01.log' `./redo02.log' -> `../hot/redo02.log' `./redo03.log' -> `../hot/redo03.log' `./sysaux01.dbf' -> `../hot/sysaux01.dbf' `./system01.dbf' -> `../hot/system01.dbf' `./temp01.dbf' -> `../hot/temp01.dbf' `./undotbs01.dbf' -> `../hot/undotbs01.dbf' `./users01.dbf' -> `../hot/users01.dbf' |
테스트2_1. recover 후 db open
end backup 없이 db 강제 종료
1 2 | SQL> shutdown abort ORACLE instance shut down. |
데이터 파일 등 삭제
1 2 3 | $ cd /oracle/app/oracle/oradata/ORCL11 $ rm -rf ./* $ ls |
모두 지워짐
파일 복원
1 2 3 4 5 6 7 8 9 10 11 | $ cp -av ../hot/* . `../hot/control01.ctl' -> `./control01.ctl' `../hot/control02.ctl' -> `./control02.ctl' `../hot/redo01.log' -> `./redo01.log' `../hot/redo02.log' -> `./redo02.log' `../hot/redo03.log' -> `./redo03.log' `../hot/sysaux01.dbf' -> `./sysaux01.dbf' `../hot/system01.dbf' -> `./system01.dbf' `../hot/temp01.dbf' -> `./temp01.dbf' `../hot/undotbs01.dbf' -> `./undotbs01.dbf' `../hot/users01.dbf' -> `./users01.dbf' |
db mount 상태로 기동
1 2 3 4 5 6 7 8 9 | SQL> startup mount ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2254952 bytes Variable Size 1375733656 bytes Database Buffers 754974720 bytes Redo Buffers 4923392 bytes Database mounted. |
v$backup 뷰만 이용해 확인
1 2 3 4 5 6 7 8 | SQL> select * from v$backup; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 1 ACTIVE 23712427 06-JAN-21 2 ACTIVE 23712433 06-JAN-21 3 ACTIVE 23712439 06-JAN-21 4 ACTIVE 23712445 06-JAN-21 |
현재 데이터파일 백업 status 가 여전히 active 임(begin backup 상태)
db open 시도
1 2 3 4 5 6 | SQL> alter database open; alter database open * ERROR at line 1: ORA-10873: file 1 needs to be either taken out of backup mode or media recovered ORA-01110: data file 1: '/oracle/app/oracle/oradata/ORCL11/system01.dbf' |
바로 open 되지 않음
recover database 시도
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | SQL> recover database; ORA-00279: change 23712427 generated at 01/06/2021 02:41:17 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_20_1059185578.arc ORA-00280: change 23712427 for thread 1 is in sequence #20 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto <-- [auto 입력] ORA-00279: change 23712855 generated at 01/06/2021 02:43:54 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_21_1059185578.arc ORA-00280: change 23712855 for thread 1 is in sequence #21 ORA-00279: change 23713215 generated at 01/06/2021 02:44:27 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_22_1059185578.arc ORA-00280: change 23713215 for thread 1 is in sequence #22 ORA-00279: change 23713566 generated at 01/06/2021 02:44:32 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_23_1059185578.arc ORA-00280: change 23713566 for thread 1 is in sequence #23 ORA-00279: change 23713937 generated at 01/06/2021 02:45:38 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_24_1059185578.arc ORA-00280: change 23713937 for thread 1 is in sequence #24 ORA-00279: change 23714305 generated at 01/06/2021 02:45:44 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_25_1059185578.arc ORA-00280: change 23714305 for thread 1 is in sequence #25 ORA-00279: change 23714666 generated at 01/06/2021 02:45:52 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_26_1059185578.arc ORA-00280: change 23714666 for thread 1 is in sequence #26 ORA-00279: change 23715034 generated at 01/06/2021 02:46:02 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_27_1059185578.arc ORA-00280: change 23715034 for thread 1 is in sequence #27 ORA-00279: change 23715400 generated at 01/06/2021 02:46:21 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_28_1059185578.arc ORA-00280: change 23715400 for thread 1 is in sequence #28 ORA-00279: change 23715642 generated at 01/06/2021 02:48:09 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_29_1059185578.arc ORA-00280: change 23715642 for thread 1 is in sequence #29 ORA-00279: change 23715820 generated at 01/06/2021 02:48:13 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_30_1059185578.arc ORA-00280: change 23715820 for thread 1 is in sequence #30 ORA-00279: change 23716012 generated at 01/06/2021 02:48:16 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_31_1059185578.arc ORA-00280: change 23716012 for thread 1 is in sequence #31 Log applied. Media recovery complete. |
recover 시도 시 아카이브 파일을 적용하는 단계가 나옴
auto 입력 시 begin backup, 파일 복제 이후 변경여부(scn) 확인 및 아카이브로그 적용함(복원)
v$backup 뷰 확인
1 2 3 4 5 6 7 8 | SQL> select * from v$backup; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 1 NOT ACTIVE 23712427 06-JAN-21 2 NOT ACTIVE 23712433 06-JAN-21 3 NOT ACTIVE 23712439 06-JAN-21 4 NOT ACTIVE 23712445 06-JAN-21 |
recover 명령후 not active 상태로 변함(end backup 을 입력한 상태와 같음)
db open 후 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL> alter database open; Database altered. SQL> set lines 200 pages 1000 col file_name for a70 col tablespace_name for a10 select d.tablespace_name, file_id, d.file_name, b.status, b.change# from dba_data_files d, v$backup b where d.file_id = b.file# order by 2; TABLESPACE FILE_ID FILE_NAME STATUS CHANGE# ---------- ---------- ---------------------------------------------------------------------- ------------------ ---------- SYSTEM 1 /oracle/app/oracle/oradata/ORCL11/system01.dbf NOT ACTIVE 23712427 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf NOT ACTIVE 23712433 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf NOT ACTIVE 23712439 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf NOT ACTIVE 23712445 |
정상적으로 open 되고 status 도 계속 not active 상태임
데이터 확인
1 2 3 4 5 | SQL> select count(*) from newimsi2; COUNT(*) ---------- 10000000 |
데이터가 정상적으로 확인됨
테스트2_2. end backup 후 db open
2_2 테스트를위해 다시 db 강제 종료
1 2 | SQL> shutdown abort ORACLE instance shut down. |
데이터 파일 등 삭제
1 2 3 | $ cd /oracle/app/oracle/oradata/ORCL11 $ rm -rf ./* $ ls |
모두 지워짐
처음에 백업한 파일로 복원
1 2 3 4 5 6 7 8 9 10 11 | $ cp -av ../hot/* . `../hot/control01.ctl' -> `./control01.ctl' `../hot/control02.ctl' -> `./control02.ctl' `../hot/redo01.log' -> `./redo01.log' `../hot/redo02.log' -> `./redo02.log' `../hot/redo03.log' -> `./redo03.log' `../hot/sysaux01.dbf' -> `./sysaux01.dbf' `../hot/system01.dbf' -> `./system01.dbf' `../hot/temp01.dbf' -> `./temp01.dbf' `../hot/undotbs01.dbf' -> `./undotbs01.dbf' `../hot/users01.dbf' -> `./users01.dbf' |
db mount 상태로 기동
1 2 3 4 5 6 7 8 9 | SQL> startup mount ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2254952 bytes Variable Size 1375733656 bytes Database Buffers 754974720 bytes Redo Buffers 4923392 bytes Database mounted. |
v$backup 뷰만 이용해 확인
1 2 3 4 5 6 7 8 | SQL> select * from v$backup; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 1 ACTIVE 23712427 06-JAN-21 2 ACTIVE 23712433 06-JAN-21 3 ACTIVE 23712439 06-JAN-21 4 ACTIVE 23712445 06-JAN-21 |
현재 데이터파일 백업 status 가 여전히 active 임(begin backup 상태)
db open 시도
1 2 3 4 5 6 | SQL> alter database open; alter database open * ERROR at line 1: ORA-10873: file 1 needs to be either taken out of backup mode or media recovered ORA-01110: data file 1: '/oracle/app/oracle/oradata/ORCL11/system01.dbf' |
바로 open 되지 않음
end backup 진행
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> alter tablespace system end backup; Tablespace altered. SQL> alter tablespace sysaux end backup; Tablespace altered. SQL> alter tablespace undotbs1 end backup; Tablespace altered. SQL> alter tablespace users end backup; Tablespace altered. |
정상적으로 end backup 명령이 실행됨
v$backup 뷰 확인
1 2 3 4 5 6 7 8 | SQL> select * from v$backup; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 1 NOT ACTIVE 23712427 06-JAN-21 2 NOT ACTIVE 23712433 06-JAN-21 3 NOT ACTIVE 23712439 06-JAN-21 4 NOT ACTIVE 23712445 06-JAN-21 |
end backup 명령후 not active 상태로 변함
db open 후 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL> alter database open; Database altered. SQL> set lines 200 pages 1000 col file_name for a70 col tablespace_name for a10 select d.tablespace_name, file_id, d.file_name, b.status, b.change# from dba_data_files d, v$backup b where d.file_id = b.file# order by 2; TABLESPACE FILE_ID FILE_NAME STATUS CHANGE# ---------- ---------- ---------------------------------------------------------------------- ------------------ ---------- SYSTEM 1 /oracle/app/oracle/oradata/ORCL11/system01.dbf NOT ACTIVE 23712427 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf NOT ACTIVE 23712433 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf NOT ACTIVE 23712439 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf NOT ACTIVE 23712445 |
정상적으로 open 되고 status 도 계속 not active 상태임
데이터 확인
1 2 3 4 5 | SQL> select count(*) from newimsi2; COUNT(*) ---------- 10000000 |
데이터가 정상적으로 확인됨
테스트2 결론 : end backup을 찍지 않고 hot 백업본으로 복구하려할 때 recover 명령 또는 end backup 이 필요함
recover 시에는 begin backup 이후 발생한 아카이브로그와 현재 db를
비교하여 변경된 부분이 있는지(scn)를 확인하고 로그를 적용한 후 open이 가능함
end backup 시에는 open 명령 실행시 자동으로 crash recovery를 한 뒤 open됨(상세 로그는 테스트3 마지막부분 확인)
begin backup 이후 파일 복사 전 만들어진 테이블은 end backup 없이 abort 되어도 복구가 가능함
테스트3. begin backup 후 대량 데이터 삽입 후 파일복사 후 end backup(정상적인 hot 백업)
기존 테이블스페이스 및 백업 상태 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> set lines 200 pages 1000 col file_name for a70 col tablespace_name for a10 select d.tablespace_name, file_id, d.file_name, b.status, b.change# from dba_data_files d, v$backup b where d.file_id = b.file# order by 2; TABLESPACE FILE_ID FILE_NAME STATUS CHANGE# ---------- ---------- ---------------------------------------------------------------------- ------------------ ---------- SYSTEM 1 /oracle/app/oracle/oradata/ORCL11/system01.dbf NOT ACTIVE 23836115 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf NOT ACTIVE 23836121 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf NOT ACTIVE 23836127 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf NOT ACTIVE 23836133 |
아카이브모드 확인
1 2 3 4 5 6 7 | SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /oracle/app/oracle/arch Oldest online log sequence 37 Next log sequence to archive 39 Current log sequence 39 |
db scn 확인
1 2 3 4 5 | SQL> select current_scn from v$database; CURRENT_SCN ----------- 23878298 |
begin backup 진행
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> alter tablespace system begin backup; Tablespace altered. SQL> alter tablespace sysaux begin backup; Tablespace altered. SQL> alter tablespace undotbs1 begin backup; Tablespace altered. SQL> alter tablespace users begin backup; Tablespace altered. |
백업 상태 확인 v$backup 의 status 컬럼
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> set lines 200 pages 1000 col file_name for a70 col tablespace_name for a10 select d.tablespace_name, file_id, d.file_name, b.status, b.change# from dba_data_files d, v$backup b where d.file_id = b.file# order by 2; TABLESPACE FILE_ID FILE_NAME STATUS CHANGE# ---------- ---------- ---------------------------------------------------------------------- ------------------ ---------- SYSTEM 1 /oracle/app/oracle/oradata/ORCL11/system01.dbf ACTIVE 23878324 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf ACTIVE 23878330 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf ACTIVE 23878336 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf ACTIVE 23878342 |
status 가 active로 변경됨(begin backup 시작됨)
샘플 데이터용 테이블 생성(대량)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | SQL> create table newimsi100(cola varchar2(20), colb number, colc number, cold varchar2(30), cole varchar2(30), colf varchar2(30), colg number, colh varchar2(30), coli varchar2(30), colj varchar2(30)) tablespace users; Table created. SQL> set serveroutput on; DECLARE TYPE tbl_ins IS TABLE OF newimsi100%ROWTYPE INDEX BY BINARY_INTEGER; w_ins tbl_ins; BEGIN FOR i IN 1..5000000 LOOP w_ins(i).COLA :=i; w_ins(i).COLB :=300000; w_ins(i).COLC :=999; w_ins(i).COLD :='DDDDDDDDDDDDDDDD'; w_ins(i).COLE :='EEEEEEEEEEEEEEEE'; w_ins(i).COLF :='FFFFFFFFFFFFFFFF'; w_ins(i).COLG :=9999999; w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH'; w_ins(i).COLI :='IIIIIIIIIIIIIIIllllIIIIIIIIIII'; w_ins(i).COLJ :='AAAAAAAAAAAAAAAAAAAAAAAAAA'; END LOOP; FORALL i in 1..5000000 INSERT INTO newimsi100 VALUES w_ins(i); COMMIT; END; / PL/SQL procedure successfully completed. SQL> / PL/SQL procedure successfully completed. |
벌크 insert 구문 2번 실행
데이터 건수 확인
1 2 3 4 5 | SQL> select count(*) from newimsi100; COUNT(*) ---------- 10000000 |
1억개 row가 생성됨
테이블 용량 확인
1 2 3 4 5 6 7 8 | SQL> select segment_name, bytes/1024/1024 mb from dba_segments where segment_name = 'NEWIMSI100'; SEGMENT_NAME MB ------------- ----- NEWIMSI100 1792 |
1.7gb 생성됨
db scn 확인
1 2 3 4 5 | SQL> select current_scn from v$database; CURRENT_SCN ----------- 23881777 |
scn 확인(v$backup 의 chance# 컬럼)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> set lines 200 pages 1000 col file_name for a70 col tablespace_name for a10 select d.tablespace_name, file_id, d.file_name, b.status, b.change# from dba_data_files d, v$backup b where d.file_id = b.file# order by 2; TABLESPACE FILE_ID FILE_NAME STATUS CHANGE# ---------- ---------- ---------------------------------------------------------------------- ------------------ ---------- SYSTEM 1 /oracle/app/oracle/oradata/ORCL11/system01.dbf ACTIVE 23878324 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf ACTIVE 23878330 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf ACTIVE 23878336 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf ACTIVE 23878342 |
change#이 변경되지 않음(begin backup 상태라서)
자세한 내용은 [오라클 Hot backup시 변경이 발생한다면? https://positivemh.tistory.com/337] 확인
테스트2 hot 백업 파일 삭제
1 2 3 | $ cd /oracle/app/oracle/oradata/hot $ rm -rf ./* $ ls |
데이터 파일 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | $ cd /oracle/app/oracle/oradata/ORCL11 $ ls -al total 6681672 drwxr-xr-x 2 oracle dba 4096 Jan 6 02:15 . drwxr-x---. 4 oracle dba 4096 Jan 6 02:05 .. -rw-r----- 1 oracle dba 10076160 Jan 6 02:54 control01.ctl -rw-r----- 1 oracle dba 10076160 Jan 6 02:54 control02.ctl -rw-r----- 1 oracle dba 209715712 Jan 6 02:54 redo01.log -rw-r----- 1 oracle dba 209715712 Jan 6 02:49 redo02.log -rw-r----- 1 oracle dba 209715712 Jan 6 02:49 redo03.log -rw-r----- 1 oracle dba 2673876992 Jan 6 02:54 sysaux01.dbf -rw-r----- 1 oracle dba 765468672 Jan 6 02:54 system01.dbf -rw-r----- 1 oracle dba 104865792 Jan 5 22:00 temp01.dbf -rw-r----- 1 oracle dba 597696512 Jan 6 02:54 undotbs01.dbf -rw-r----- 1 oracle dba 2147491840 Jan 6 02:54 users01.dbf |
파일 백업
1 2 3 4 5 6 7 8 9 10 11 12 | $ cd /oracle/app/oracle/oradata/ORCL11/ $ cp -av ./* ../hot/ `./control01.ctl' -> `../hot/control01.ctl' `./control02.ctl' -> `../hot/control02.ctl' `./redo01.log' -> `../hot/redo01.log' `./redo02.log' -> `../hot/redo02.log' `./redo03.log' -> `../hot/redo03.log' `./sysaux01.dbf' -> `../hot/sysaux01.dbf' `./system01.dbf' -> `../hot/system01.dbf' `./temp01.dbf' -> `../hot/temp01.dbf' `./undotbs01.dbf' -> `../hot/undotbs01.dbf' `./users01.dbf' -> `../hot/users01.dbf' |
end backup 진행
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> alter tablespace system end backup; Tablespace altered. SQL> alter tablespace sysaux end backup; Tablespace altered. SQL> alter tablespace undotbs1 end backup; Tablespace altered. SQL> alter tablespace users end backup; Tablespace altered. |
db scn 확인
1 2 3 4 5 | SQL> select current_scn from v$database; CURRENT_SCN ----------- 23881992 |
테스트3_1. recover 후 db open
end backup 없이 db 강제 종료
1 2 | SQL> shutdown abort ORACLE instance shut down. |
데이터 파일 등 삭제
1 2 3 | $ cd /oracle/app/oracle/oradata/ORCL11 $ rm -rf ./* $ ls |
모두 지워짐
파일 복원
1 2 3 4 5 6 7 8 9 10 11 | $ cp -av ../hot/* . `../hot/control01.ctl' -> `./control01.ctl' `../hot/control02.ctl' -> `./control02.ctl' `../hot/redo01.log' -> `./redo01.log' `../hot/redo02.log' -> `./redo02.log' `../hot/redo03.log' -> `./redo03.log' `../hot/sysaux01.dbf' -> `./sysaux01.dbf' `../hot/system01.dbf' -> `./system01.dbf' `../hot/temp01.dbf' -> `./temp01.dbf' `../hot/undotbs01.dbf' -> `./undotbs01.dbf' `../hot/users01.dbf' -> `./users01.dbf' |
db mount 상태로 기동
1 2 3 4 5 6 7 8 9 | SQL> startup mount ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2254952 bytes Variable Size 1375733656 bytes Database Buffers 754974720 bytes Redo Buffers 4923392 bytes Database mounted. |
v$backup 뷰만 이용해 확인
1 2 3 4 5 6 7 8 | SQL> select * from v$backup; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 1 ACTIVE 23878324 07-JAN-21 2 ACTIVE 23878330 07-JAN-21 3 ACTIVE 23878336 07-JAN-21 4 ACTIVE 23878342 07-JAN-21 |
end backup을 했으나 hot 백업본으로 mount 하자
현재 데이터파일 백업 status 가 여전히 active 임(begin backup 상태)
db open 시도
1 2 3 4 5 6 | SQL> alter database open; alter database open * ERROR at line 1: ORA-10873: file 1 needs to be either taken out of backup mode or media recovered ORA-01110: data file 1: '/oracle/app/oracle/oradata/ORCL11/system01.dbf' |
바로 open 되지 않음
recover database 시도
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | SQL> recover database; ORA-00279: change 23878324 generated at 01/07/2021 04:53:24 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_39_1059185578.arc ORA-00280: change 23878324 for thread 1 is in sequence #39 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto <-- [auto 입력] ORA-00279: change 23878762 generated at 01/07/2021 04:55:04 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_40_1059185578.arc ORA-00280: change 23878762 for thread 1 is in sequence #40 ORA-00279: change 23879121 generated at 01/07/2021 04:55:08 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_41_1059185578.arc ORA-00280: change 23879121 for thread 1 is in sequence #41 ORA-00279: change 23879478 generated at 01/07/2021 04:55:15 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_42_1059185578.arc ORA-00280: change 23879478 for thread 1 is in sequence #42 ORA-00279: change 23879827 generated at 01/07/2021 04:55:18 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_43_1059185578.arc ORA-00280: change 23879827 for thread 1 is in sequence #43 ORA-00279: change 23880177 generated at 01/07/2021 04:56:17 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_44_1059185578.arc ORA-00280: change 23880177 for thread 1 is in sequence #44 ORA-00279: change 23880530 generated at 01/07/2021 04:56:21 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_45_1059185578.arc ORA-00280: change 23880530 for thread 1 is in sequence #45 Log applied. Media recovery complete. |
recover 시도 시 아카이브 파일을 적용하는 단계가 나옴
auto 입력 시 begin backup, 파일 복제 이후 변경여부(scn) 확인 및 아카이브로그 적용함(복원)
db scn 확인
1 2 3 4 5 | SQL> select current_scn from v$database; CURRENT_SCN ----------- 0 |
open 전이라 0 으로 나옴
v$backup 뷰 확인
1 2 3 4 5 6 7 8 | SQL> select * from v$backup; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 1 NOT ACTIVE 23878324 07-JAN-21 2 NOT ACTIVE 23878330 07-JAN-21 3 NOT ACTIVE 23878336 07-JAN-21 4 NOT ACTIVE 23878342 07-JAN-21 |
recover 명령후 not active 상태로 변함(end backup 을 입력한 상태와 같아짐)
db open 후 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL> alter database open; Database altered. SQL> set lines 200 pages 1000 col file_name for a70 col tablespace_name for a10 select d.tablespace_name, file_id, d.file_name, b.status, b.change# from dba_data_files d, v$backup b where d.file_id = b.file# order by 2; TABLESPACE FILE_ID FILE_NAME STATUS CHANGE# ---------- ---------- ---------------------------------------------------------------------- ------------------ ---------- SYSTEM 1 /oracle/app/oracle/oradata/ORCL11/system01.dbf NOT ACTIVE 23878324 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf NOT ACTIVE 23878330 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf NOT ACTIVE 23878336 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf NOT ACTIVE 23878342 |
정상적으로 open 되고 status 도 계속 not active 상태임
데이터 확인
1 2 3 4 5 | SQL> select count(*) from newimsi100; COUNT(*) ---------- 10000000 |
데이터가 정상적으로 확인됨
db scn 확인
1 2 3 4 5 | SQL> select current_scn from v$database; CURRENT_SCN ----------- 23904376 |
테스트3_2. end backup 후 db open
3_2 테스트를위해 다시 db 강제 종료
1 2 | SQL> shutdown abort ORACLE instance shut down. |
데이터 파일 등 삭제
1 2 3 | $ cd /oracle/app/oracle/oradata/ORCL11 $ rm -rf ./* $ ls |
모두 지워짐
처음에 백업한 파일로 복원
1 2 3 4 5 6 7 8 9 10 11 | $ cp -av ../hot/* . `../hot/control01.ctl' -> `./control01.ctl' `../hot/control02.ctl' -> `./control02.ctl' `../hot/redo01.log' -> `./redo01.log' `../hot/redo02.log' -> `./redo02.log' `../hot/redo03.log' -> `./redo03.log' `../hot/sysaux01.dbf' -> `./sysaux01.dbf' `../hot/system01.dbf' -> `./system01.dbf' `../hot/temp01.dbf' -> `./temp01.dbf' `../hot/undotbs01.dbf' -> `./undotbs01.dbf' `../hot/users01.dbf' -> `./users01.dbf' |
db mount 상태로 기동
1 2 3 4 5 6 7 8 9 | SQL> startup mount ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2254952 bytes Variable Size 1375733656 bytes Database Buffers 754974720 bytes Redo Buffers 4923392 bytes Database mounted. |
v$backup 뷰만 이용해 확인
1 2 3 4 5 6 7 8 | SQL> select * from v$backup; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 1 ACTIVE 23878324 07-JAN-21 2 ACTIVE 23878330 07-JAN-21 3 ACTIVE 23878336 07-JAN-21 4 ACTIVE 23878342 07-JAN-21 |
현재 데이터파일 백업 status 가 여전히 active 임(begin backup 상태)
db open 시도
1 2 3 4 5 6 | SQL> alter database open; alter database open * ERROR at line 1: ORA-10873: file 1 needs to be either taken out of backup mode or media recovered ORA-01110: data file 1: '/oracle/app/oracle/oradata/ORCL11/system01.dbf' |
바로 open 되지 않음
end backup 진행
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> alter tablespace system end backup; Tablespace altered. SQL> alter tablespace sysaux end backup; Tablespace altered. SQL> alter tablespace undotbs1 end backup; Tablespace altered. SQL> alter tablespace users end backup; Tablespace altered. |
정상적으로 end backup 명령이 실행됨
v$backup 뷰 확인
1 2 3 4 5 6 7 8 | SQL> select * from v$backup; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 1 NOT ACTIVE 23878324 07-JAN-21 2 NOT ACTIVE 23878330 07-JAN-21 3 NOT ACTIVE 23878336 07-JAN-21 4 NOT ACTIVE 23878342 07-JAN-21 |
end backup 명령후 not active 상태로 변함
db open 후 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL> alter database open; Database altered. SQL> set lines 200 pages 1000 col file_name for a70 col tablespace_name for a10 select d.tablespace_name, file_id, d.file_name, b.status, b.change# from dba_data_files d, v$backup b where d.file_id = b.file# order by 2; TABLESPACE FILE_ID FILE_NAME STATUS CHANGE# ---------- ---------- ---------------------------------------------------------------------- ------------------ ---------- SYSTEM 1 /oracle/app/oracle/oradata/ORCL11/system01.dbf NOT ACTIVE 23878324 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf NOT ACTIVE 23878330 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf NOT ACTIVE 23878336 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf NOT ACTIVE 23878342 |
정상적으로 open 되고 status 도 계속 not active 상태임
데이터 확인
1 2 3 4 5 | SQL> select count(*) from newimsi100; COUNT(*) ---------- 10000000 |
데이터가 정상적으로 확인됨
db scn 확인
1 2 3 4 5 | SQL> select current_scn from v$database; CURRENT_SCN ----------- 23901982 |
3_1 기동할 때와 3_2 기동할 때의 alert log 차이
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | mount -> recover database -> open 시 alert log $ vi /oracle/app/oracle/diag/rdbms/orcl11/ORCL11/trace/alert_ORCL11.log Thu Jan 07 05:05:36 2021 alter database open Beginning crash recovery of 1 threads Started redo scan Completed redo scan read 328124 KB redo, 0 data blocks need recovery Started redo application at Thread 1: logseq 46, block 306117 Recovery of Online Redo Log: Thread 1 Group 1 Seq 46 Reading mem 0 Mem# 0: /oracle/app/oracle/oradata/ORCL11/redo01.log Recovery of Online Redo Log: Thread 1 Group 2 Seq 47 Reading mem 0 Mem# 0: /oracle/app/oracle/oradata/ORCL11/redo02.log Recovery of Online Redo Log: Thread 1 Group 3 Seq 48 Reading mem 0 Mem# 0: /oracle/app/oracle/oradata/ORCL11/redo03.log Completed redo application of 0.00MB Completed crash recovery at Thread 1: logseq 48, block 182924, scn 23901788 0 data blocks read, 0 data blocks written, 328124 redo k-bytes read mount -> end backup -> open 시 alert log $ vi /oracle/app/oracle/diag/rdbms/orcl11/ORCL11/trace/alert_ORCL11.log Thu Jan 07 06:18:10 2021 alter database open Beginning crash recovery of 1 threads Started redo scan Completed redo scan read 328124 KB redo, 1071 data blocks need recovery Started redo application at Thread 1: logseq 46, block 306117 Recovery of Online Redo Log: Thread 1 Group 1 Seq 46 Reading mem 0 Mem# 0: /oracle/app/oracle/oradata/ORCL11/redo01.log Recovery of Online Redo Log: Thread 1 Group 2 Seq 47 Reading mem 0 Mem# 0: /oracle/app/oracle/oradata/ORCL11/redo02.log Recovery of Online Redo Log: Thread 1 Group 3 Seq 48 Reading mem 0 Mem# 0: /oracle/app/oracle/oradata/ORCL11/redo03.log Completed redo application of 11.47MB Completed crash recovery at Thread 1: logseq 48, block 182924, scn 23901788 1071 data blocks read, 1071 data blocks written, 328124 redo k-bytes read |
end backup 을 찍은 경우에는 open 시 crash recovery 를 하면서 데이터를 복구하고 올라옴
recover 를 한 경우에는 Completed redo application of 가 0MB로 나오는데
이미 recover 명령시에 데이터를 이미 복구해서 open 시 Completed redo application of 가 0MB 로 나오는것임
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | recover database 입력한 시점 $ vi /oracle/app/oracle/diag/rdbms/orcl11/ORCL11/trace/alert_ORCL11.log Thu Jan 07 05:04:12 2021 ALTER DATABASE RECOVER database Media Recovery Start Serial Media Recovery started ORA-279 signalled during: ALTER DATABASE RECOVER database ... ALTER DATABASE RECOVER CONTINUE DEFAULT Media Recovery Log /oracle/app/oracle/arch/1_39_1059185578.arc ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... ALTER DATABASE RECOVER CONTINUE DEFAULT Media Recovery Log /oracle/app/oracle/arch/1_40_1059185578.arc ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... ALTER DATABASE RECOVER CONTINUE DEFAULT Media Recovery Log /oracle/app/oracle/arch/1_41_1059185578.arc ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... ALTER DATABASE RECOVER CONTINUE DEFAULT Media Recovery Log /oracle/app/oracle/arch/1_42_1059185578.arc ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... ALTER DATABASE RECOVER CONTINUE DEFAULT Media Recovery Log /oracle/app/oracle/arch/1_43_1059185578.arc Thu Jan 07 05:04:22 2021 ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... ALTER DATABASE RECOVER CONTINUE DEFAULT Media Recovery Log /oracle/app/oracle/arch/1_44_1059185578.arc ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... ALTER DATABASE RECOVER CONTINUE DEFAULT Media Recovery Log /oracle/app/oracle/arch/1_45_1059185578.arc Recovery of Online Redo Log: Thread 1 Group 1 Seq 46 Reading mem 0 Mem# 0: /oracle/app/oracle/oradata/ORCL11/redo01.log Recovery of Online Redo Log: Thread 1 Group 2 Seq 47 Reading mem 0 Mem# 0: /oracle/app/oracle/oradata/ORCL11/redo02.log Recovery of Online Redo Log: Thread 1 Group 3 Seq 48 Reading mem 0 Mem# 0: /oracle/app/oracle/oradata/ORCL11/redo03.log Media Recovery Complete (ORCL11) Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT alter database open 입력 시점 Thu Jan 07 05:05:36 2021 alter database open Beginning crash recovery of 1 threads Started redo scan Completed redo scan read 328124 KB redo, 0 data blocks need recovery Started redo application at Thread 1: logseq 46, block 306117 Recovery of Online Redo Log: Thread 1 Group 1 Seq 46 Reading mem 0 Mem# 0: /oracle/app/oracle/oradata/ORCL11/redo01.log Recovery of Online Redo Log: Thread 1 Group 2 Seq 47 Reading mem 0 Mem# 0: /oracle/app/oracle/oradata/ORCL11/redo02.log Recovery of Online Redo Log: Thread 1 Group 3 Seq 48 Reading mem 0 Mem# 0: /oracle/app/oracle/oradata/ORCL11/redo03.log Completed redo application of 0.00MB Completed crash recovery at Thread 1: logseq 48, block 182924, scn 23901788 0 data blocks read, 0 data blocks written, 328124 redo k-bytes read |
테스트3 결론 : end backup을 찍은 hot 백업본으로 복구하려할 때 recover 명령 또는 end backup 이 필요함
recover 시에는 begin backup 이후 발생한 아카이브로그와 현재 db를
비교하여 변경된 부분이 있는지(scn)를 확인하고 로그를 적용한 후 open이 가능함
end backup 시에는 open 명령 실행시 자동으로 crash recovery를 한 뒤 open됨
추가
begin backup 후 shutdown abort 후
복사한 파일을 다시 원래 경로에 넣은 후 startup mount 시 alert log에
일반적인 mount 메세지가 아닌 다른 메세지가 나옴
1 2 3 4 5 6 7 | $ vi /oracle/app/oracle/diag/rdbms/orcl11/ORCL11/trace/alert_ORCL11.log Wed Jan 06 02:57:53 2021 ALTER DATABASE MOUNT Successful mount of redo thread 1, with mount id 12858225 Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE MOUNT |
참조 :
https://positivemh.tistory.com/337
https://positivemh.tistory.com/689
469950.1'ORACLE > Backup&Recover' 카테고리의 다른 글
| 오라클 11g R2 hot 백업, 복구(파일 복사 후 샘플데이터 생성) (0) | 2021.01.11 |
|---|---|
| 오라클 11g R2 begin backup 후 end backup 을 하지 않고 복구(파일 복사 후 샘플데이터 생성) (0) | 2021.01.05 |
| 오라클 datafile offline 후 online 테스트2 (0) | 2020.12.23 |
| 오라클 datafile offline 후 online 테스트 (0) | 2020.12.21 |
| 오라클 11g R2 엔진 백업 복구 테스트 (8) | 2020.11.27 |