내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4
방법 : 오라클 11g R2 hot 백업, 복구(파일 복사 후 샘플데이터 생성)
이전 테스트
오라클 11g R2 begin backup 후 end backup 을 하지 않고 복구(샘플데이터 생성 후 파일 복사) https://positivemh.tistory.com/688
오라클 11g R2 begin backup 후 end backup 을 하지 않고 복구(파일 복사 후 샘플데이터 생성) https://positivemh.tistory.com/689
테스트3. begin backup 후 파일복사 후 대량 데이터 삽입 후 end backup(hot 백업 본 사용)
테스트3_1. recover 후 db open
테스트3_2. end backup 후 db open
테스트4. begin backup 후 파일복사 후 대량 데이터 삽입 후 end backup(hot 백업 본 + 최신 controlfile)
테스트4_1. 최신 controlfile, 아카이브 이용 end backup 후 db open
테스트4_2. 최신 controlfile, 아카이브 이용 recover 후 db open
테스트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 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 |
아카이브모드 확인
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 47 Next log sequence to archive 49 Current log sequence 49 |
db scn 확인
1 2 3 4 5 | SQL> select current_scn from v$database; CURRENT_SCN ----------- 23903189 |
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. |
db scn 확인
1 2 3 4 5 | SQL> select current_scn from v$database; CURRENT_SCN ----------- 23904443 |
백업 상태 확인 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 23903217 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf ACTIVE 23903223 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf ACTIVE 23903229 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf ACTIVE 23903236 |
status 가 active로 변경됨(begin backup 시작됨)
테스트2 hot 백업 파일 삭제
1 2 3 | $ cd /oracle/app/oracle/oradata/hot2 $ 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 6681680 drwxr-xr-x 2 oracle dba 4096 Jan 6 06:39 . drwxr-x---. 4 oracle dba 4096 Jan 6 02:05 .. -rw-r----- 1 oracle dba 10076160 Jan 6 06:45 control01.ctl -rw-r----- 1 oracle dba 10076160 Jan 6 06:45 control02.ctl -rw-r----- 1 oracle dba 209715712 Jan 6 06:40 redo01.log -rw-r----- 1 oracle dba 209715712 Jan 6 06:40 redo02.log -rw-r----- 1 oracle dba 209715712 Jan 6 06:42 redo03.log -rw-r----- 1 oracle dba 2673876992 Jan 6 06:42 sysaux01.dbf -rw-r----- 1 oracle dba 765468672 Jan 6 06:42 system01.dbf -rw-r----- 1 oracle dba 104865792 Jan 5 22:00 temp01.dbf -rw-r----- 1 oracle dba 597696512 Jan 6 06:42 undotbs01.dbf -rw-r----- 1 oracle dba 2147491840 Jan 6 06:42 users01.dbf |
파일 백업
1 2 3 4 5 6 7 8 9 10 11 12 | $ cd /oracle/app/oracle/oradata/ORCL11/ $ cp -av ./* ../hot2/ `./control01.ctl' -> `../hot2/control01.ctl' `./control02.ctl' -> `../hot2/control02.ctl' `./redo01.log' -> `../hot2/redo01.log' `./redo02.log' -> `../hot2/redo02.log' `./redo03.log' -> `../hot2/redo03.log' `./sysaux01.dbf' -> `../hot2/sysaux01.dbf' `./system01.dbf' -> `../hot2/system01.dbf' `./temp01.dbf' -> `../hot2/temp01.dbf' `./undotbs01.dbf' -> `../hot2/undotbs01.dbf' `./users01.dbf' -> `../hot2/users01.dbf' |
샘플 데이터용 테이블 생성(대량)
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 newimsi200(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 newimsi200%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 newimsi200 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 newimsi200; 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 = 'NEWIMSI200'; SEGMENT_NAME MB ------------- ----- NEWIMSI200 1792 |
1.7gb 생성됨
db scn 확인
1 2 3 4 5 | SQL> select current_scn from v$database; CURRENT_SCN ----------- 23906715 |
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 23903217 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf ACTIVE 23903223 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf ACTIVE 23903229 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf ACTIVE 23903236 |
change#이 변경되지 않음(begin backup 상태라서)
자세한 내용은 [오라클 Hot backup시 변경이 발생한다면? https://positivemh.tistory.com/337] 확인
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 ----------- 23906805 |
백업 상태 확인 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 NOT ACTIVE 23903217 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf NOT ACTIVE 23903223 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf NOT ACTIVE 23903229 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf NOT ACTIVE 23903236 |
status 가 not active로 변경됨(end backup)
테스트3_1. recover 후 db open
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 ../hot2/* . `../hot2/control01.ctl' -> `./control01.ctl' `../hot2/control02.ctl' -> `./control02.ctl' `../hot2/redo01.log' -> `./redo01.log' `../hot2/redo02.log' -> `./redo02.log' `../hot2/redo03.log' -> `./redo03.log' `../hot2/sysaux01.dbf' -> `./sysaux01.dbf' `../hot2/system01.dbf' -> `./system01.dbf' `../hot2/temp01.dbf' -> `./temp01.dbf' `../hot2/undotbs01.dbf' -> `./undotbs01.dbf' `../hot2/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 23903217 07-JAN-21 2 ACTIVE 23903223 07-JAN-21 3 ACTIVE 23903229 07-JAN-21 4 ACTIVE 23903236 07-JAN-21 |
end backup을 했으나 hot 백업본으로 mount 하자
현재 데이터파일 백업 status 가 여전히 active 임(begin backup 상태)
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 23903217 07-JAN-21 2 NOT ACTIVE 23903223 07-JAN-21 3 NOT ACTIVE 23903229 07-JAN-21 4 NOT ACTIVE 23903236 07-JAN-21 |
recover 명령후 not active 상태로 변함(end backup 을 입력한 상태와 같아짐)
db scn 확인
1 2 3 4 5 | SQL> select current_scn from v$database; CURRENT_SCN ----------- 0 |
open 전이라 0 으로 나옴
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 23903217 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf NOT ACTIVE 23903223 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf NOT ACTIVE 23903229 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf NOT ACTIVE 23903236 |
정상적으로 open 되고 status 도 계속 not active 상태임
db scn 확인
1 2 3 4 5 | SQL> select current_scn from v$database; CURRENT_SCN ----------- 23923416 |
데이터 확인
1 2 3 4 5 | SQL> select count(*) from newimsi200; select count(*) from newimsi200 * ERROR at line 1: ORA-00942: table or view does not exist |
테이블 자체가 없어짐
테스트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 ../hot2/* . `../hot2/control01.ctl' -> `./control01.ctl' `../hot2/control02.ctl' -> `./control02.ctl' `../hot2/redo01.log' -> `./redo01.log' `../hot2/redo02.log' -> `./redo02.log' `../hot2/redo03.log' -> `./redo03.log' `../hot2/sysaux01.dbf' -> `./sysaux01.dbf' `../hot2/system01.dbf' -> `./system01.dbf' `../hot2/temp01.dbf' -> `./temp01.dbf' `../hot2/undotbs01.dbf' -> `./undotbs01.dbf' `../hot2/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 23903217 07-JAN-21 2 ACTIVE 23903223 07-JAN-21 3 ACTIVE 23903229 07-JAN-21 4 ACTIVE 23903236 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 23903217 07-JAN-21 2 NOT ACTIVE 23903223 07-JAN-21 3 NOT ACTIVE 23903229 07-JAN-21 4 NOT ACTIVE 23903236 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 23903217 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf NOT ACTIVE 23903223 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf NOT ACTIVE 23903229 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf NOT ACTIVE 23903236 |
정상적으로 open 되고 status 도 계속 not active 상태임
데이터 확인
1 2 3 4 5 | SQL> select count(*) from newimsi200; select count(*) from newimsi200 * ERROR at line 1: ORA-00942: table or view does not exist |
테이블 자체가 없어짐
db scn 확인
1 2 3 4 5 | SQL> select current_scn from v$database; CURRENT_SCN ----------- 23923425 |
테스트3 결론 : end backup을 찍고 hot 백업본으로 복구하려할 때 recover 명령 또는 end backup 이 필요함
recover 시 바로 complete가 나오고 바로 open이 가능함
end backup 시 바로 open이 가능함
하지만 두가지 모두
begin backup 이후 파일 복사 후 만들어진 테이블은 end backup 을 찍었더라도 복구되지 못함
테이블, 데이터의 크기와는 상관없이 테스트1,2 모두 테이블은 복구되지못함
*테스트3 도 recover database 명령 대신
recover database using backup controlfile - auto 명령으로 데이터 복구 후
recover database using backup controlfile until cancel - cancel 후 resetlogs로 open 시
테이블 및 데이터 복원이 가능함
테스트4. begin backup 후 파일복사 후 대량 데이터 삽입 후 end backup(hot 백업 본 + 최신 controlfile)
기존 테이블스페이스 및 백업 상태 확인
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 24081078 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf NOT ACTIVE 24081084 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf NOT ACTIVE 24081090 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf NOT ACTIVE 24081096 |
아카이브모드 확인
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 76 Next log sequence to archive 78 Current log sequence 78 |
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. |
db scn 확인
1 2 3 4 5 | SQL> select current_scn from v$database; CURRENT_SCN ----------- 24124708 |
백업 상태 확인 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 24124686 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf ACTIVE 24124692 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf ACTIVE 24124697 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf ACTIVE 24124702 |
status 가 active로 변경됨(begin backup 시작됨)
테스트3 hot 백업 파일 삭제
1 2 3 | $ cd /oracle/app/oracle/oradata/hot2 $ 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 6703300 drwxr-xr-x 2 oracle dba 4096 Jan 10 08:59 . drwxr-x---. 4 oracle dba 4096 Jan 10 09:02 .. -rw-r----- 1 oracle dba 10076160 Jan 10 09:09 control01.ctl -rw-r----- 1 oracle dba 10076160 Jan 10 09:09 control02.ctl -rw-r----- 1 oracle dba 209715712 Jan 10 09:06 redo01.log -rw-r----- 1 oracle dba 209715712 Jan 10 09:08 redo02.log -rw-r----- 1 oracle dba 209715712 Jan 10 09:06 redo03.log -rw-r----- 1 oracle dba 2694848512 Jan 10 09:08 sysaux01.dbf -rw-r----- 1 oracle dba 765468672 Jan 10 09:08 system01.dbf -rw-r----- 1 oracle dba 104865792 Jan 10 06:00 temp01.dbf -rw-r----- 1 oracle dba 597696512 Jan 10 09:08 undotbs01.dbf -rw-r----- 1 oracle dba 2147491840 Jan 10 09:08 users01.dbf |
파일 백업
1 2 3 4 5 6 7 8 9 10 11 12 | $ cd /oracle/app/oracle/oradata/ORCL11/ $ cp -av ./* ../hot2/ `./control01.ctl' -> `../hot2/control01.ctl' `./control02.ctl' -> `../hot2/control02.ctl' `./redo01.log' -> `../hot2/redo01.log' `./redo02.log' -> `../hot2/redo02.log' `./redo03.log' -> `../hot2/redo03.log' `./sysaux01.dbf' -> `../hot2/sysaux01.dbf' `./system01.dbf' -> `../hot2/system01.dbf' `./temp01.dbf' -> `../hot2/temp01.dbf' `./undotbs01.dbf' -> `../hot2/undotbs01.dbf' `./users01.dbf' -> `../hot2/users01.dbf' |
샘플 데이터용 테이블 생성(대량)
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 newimsi300(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 newimsi300%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 newimsi300 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 newimsi300; 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 = 'NEWIMSI300'; SEGMENT_NAME MB ------------- ----- NEWIMSI300 1792 |
1.7gb 생성됨
db scn 확인
1 2 3 4 5 | SQL> select current_scn from v$database; CURRENT_SCN ----------- 24127953 |
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 24124686 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf ACTIVE 24124692 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf ACTIVE 24124697 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf ACTIVE 24124702 |
change#이 변경되지 않음(begin backup 상태라서)
자세한 내용은 [오라클 Hot backup시 변경이 발생한다면? https://positivemh.tistory.com/337] 확인
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. |
체크포인트 및 로그스위치 실행
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> alter system checkpoint; System altered. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. |
db scn 확인
1 2 3 4 5 | SQL> select current_scn from v$database; CURRENT_SCN ----------- 24128002 |
백업 상태 확인 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 NOT ACTIVE 24124686 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf NOT ACTIVE 24124692 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf NOT ACTIVE 24124697 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf NOT ACTIVE 24124702 |
status 가 not active로 변경됨(end backup)
테스트4_1. 최신 controlfile, 아카이브이용 recover 후 db open
db 강제 종료
1 2 | SQL> shutdown abort ORACLE instance shut down. |
최신 컨트롤파일 백업
1 2 3 4 | $ cd /oracle/app/oracle/oradata/ORCL11 $ cp -av ./control0* ../ `control01.ctl' -> `../control01.ctl' `control02.ctl' -> `../control02.ctl' |
데이터 파일 등 삭제
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 ../hot2/* . `../hot2/control01.ctl' -> `./control01.ctl' `../hot2/control02.ctl' -> `./control02.ctl' `../hot2/redo01.log' -> `./redo01.log' `../hot2/redo02.log' -> `./redo02.log' `../hot2/redo03.log' -> `./redo03.log' `../hot2/sysaux01.dbf' -> `./sysaux01.dbf' `../hot2/system01.dbf' -> `./system01.dbf' `../hot2/temp01.dbf' -> `./temp01.dbf' `../hot2/undotbs01.dbf' -> `./undotbs01.dbf' `../hot2/users01.dbf' -> `./users01.dbf' |
최신 컨트롤파일 복원
1 2 3 4 | $ cd /oracle/app/oracle/oradata/ORCL11 $ cp -av ../control0* . `../control01.ctl' -> `./control01.ctl' `../control02.ctl' -> `./control02.ctl' |
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 24124686 10-JAN-21 2 ACTIVE 24124692 10-JAN-21 3 ACTIVE 24124697 10-JAN-21 4 ACTIVE 24124702 10-JAN-21 |
end backup을 했으나 hot 백업본으로 mount 하자
현재 데이터파일 백업 status 가 여전히 active 임(begin backup 상태)
recover database using backup controlfile 실행
hot 백업 이후 부터 최신 컨트롤파일의 scn 까지 아카이브를 이용해 복원(log apply)
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 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 | SQL> recover database using backup controlfile; ORA-00279: change 24124686 generated at 01/11/2021 00:00:31 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_78_1059185578.arc ORA-00280: change 24124686 for thread 1 is in sequence #78 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto <-- [auto 입력] ORA-00279: change 24124998 generated at 01/11/2021 00:02:23 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_79_1059185578.arc ORA-00280: change 24124998 for thread 1 is in sequence #79 ORA-00278: log file '/oracle/app/oracle/arch/1_78_1059185578.arc' no longer needed for this recovery ORA-00279: change 24125336 generated at 01/11/2021 00:02:29 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_80_1059185578.arc ORA-00280: change 24125336 for thread 1 is in sequence #80 ORA-00278: log file '/oracle/app/oracle/arch/1_79_1059185578.arc' no longer needed for this recovery ORA-00279: change 24125704 generated at 01/11/2021 00:02:36 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_81_1059185578.arc ORA-00280: change 24125704 for thread 1 is in sequence #81 ORA-00278: log file '/oracle/app/oracle/arch/1_80_1059185578.arc' no longer needed for this recovery ORA-00279: change 24126075 generated at 01/11/2021 00:02:47 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_82_1059185578.arc ORA-00280: change 24126075 for thread 1 is in sequence #82 ORA-00278: log file '/oracle/app/oracle/arch/1_81_1059185578.arc' no longer needed for this recovery ORA-00279: change 24126389 generated at 01/11/2021 00:02:55 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_83_1059185578.arc ORA-00280: change 24126389 for thread 1 is in sequence #83 ORA-00278: log file '/oracle/app/oracle/arch/1_82_1059185578.arc' no longer needed for this recovery ORA-00279: change 24126774 generated at 01/11/2021 00:03:10 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_84_1059185578.arc ORA-00280: change 24126774 for thread 1 is in sequence #84 ORA-00278: log file '/oracle/app/oracle/arch/1_83_1059185578.arc' no longer needed for this recovery ORA-00279: change 24127100 generated at 01/11/2021 00:03:14 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_85_1059185578.arc ORA-00280: change 24127100 for thread 1 is in sequence #85 ORA-00278: log file '/oracle/app/oracle/arch/1_84_1059185578.arc' no longer needed for this recovery ORA-00279: change 24127394 generated at 01/11/2021 00:03:23 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_86_1059185578.arc ORA-00280: change 24127394 for thread 1 is in sequence #86 ORA-00278: log file '/oracle/app/oracle/arch/1_85_1059185578.arc' no longer needed for this recovery ORA-00279: change 24127717 generated at 01/11/2021 00:03:28 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_87_1059185578.arc ORA-00280: change 24127717 for thread 1 is in sequence #87 ORA-00278: log file '/oracle/app/oracle/arch/1_86_1059185578.arc' no longer needed for this recovery ORA-00279: change 24127994 generated at 01/11/2021 00:04:29 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_88_1059185578.arc ORA-00280: change 24127994 for thread 1 is in sequence #88 ORA-00278: log file '/oracle/app/oracle/arch/1_87_1059185578.arc' no longer needed for this recovery ORA-00279: change 24127997 generated at 01/11/2021 00:04:29 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_89_1059185578.arc ORA-00280: change 24127997 for thread 1 is in sequence #89 ORA-00278: log file '/oracle/app/oracle/arch/1_88_1059185578.arc' no longer needed for this recovery ORA-00279: change 24128000 generated at 01/11/2021 00:04:30 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_90_1059185578.arc ORA-00280: change 24128000 for thread 1 is in sequence #90 ORA-00278: log file '/oracle/app/oracle/arch/1_89_1059185578.arc' no longer needed for this recovery ORA-00308: cannot open archived log '/oracle/app/oracle/arch/1_90_1059185578.arc' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 |
마지막 sequence #90을 찾지못함
shutdown abort 전 아카이브로 쓰여지지 않은 redo log 중 하나임
그 redo 는 제외하고 until cancel 로 다시 recover
1 2 3 4 5 6 7 8 9 | SQL> recover database using backup controlfile until cancel; ORA-00279: change 24128000 generated at 01/11/2021 00:04:30 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_90_1059185578.arc ORA-00280: change 24128000 for thread 1 is in sequence #90 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel <-- [cancel 입력] Media recovery cancelled. |
db scn 확인
1 2 3 4 5 | SQL> select current_scn from v$database; CURRENT_SCN ----------- 0 |
open 전이라 0 으로 나옴
resetlogs 로 db open
1 2 3 | SQL> alter database open resetlogs; Database altered. |
v$backup 뷰 확인
1 2 3 4 5 6 7 8 | SQL> select * from v$backup; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 1 NOT ACTIVE 24124686 07-JAN-21 2 NOT ACTIVE 24124692 07-JAN-21 3 NOT ACTIVE 24124697 07-JAN-21 4 NOT ACTIVE 24124702 07-JAN-21 |
recover 명령후 not active 상태로 변함(end backup 을 입력한 상태와 같아짐)
백업 상태 확인 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 NOT ACTIVE 24124686 SYSAUX 2 /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf NOT ACTIVE 24124692 UNDOTBS1 3 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf NOT ACTIVE 24124697 USERS 4 /oracle/app/oracle/oradata/ORCL11/users01.dbf NOT ACTIVE 24124702 |
status 계속 not active 상태임
db scn 확인
1 2 3 4 5 | SQL> select current_scn from v$database; CURRENT_SCN ----------- 24128268 |
데이터 확인
1 2 3 4 5 | SQL> select count(*) from newimsi300; COUNT(*) ---------- 10000000 |
정상적으로 확인됨
테스트4 결론 : end backup을 찍고 hot 백업본으로 복구하려할 때 recover 명령이 필요함
recover database using backup controlfile - auto 명령으로 데이터 복구 후
recover database using backup controlfile until cancel - cancel 후 resetlogs로 open이 가능함
정상적으로 데이터가 복구됨
*추가1
4_1 테스트 후 다시 shutdown abort 후 똑같이 반복하면 아래와 같이 sequence #78에서 복원이 끝나버림
그리고 해당 newimsi300 테이블도 복구되지 않음
1 2 3 4 5 6 7 8 9 10 | SQL> recover database using backup controlfile; ORA-00279: change 24124686 generated at 01/11/2021 00:00:31 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/arch/1_78_1059185578.arc ORA-00280: change 24124686 for thread 1 is in sequence #78 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto Log applied. Media recovery complete. |
4_1 과 같은 결과를 다시 얻으려면 테스트4를 다시 처음부터 진행해야함
이유 : resetlogs로 open 시 아카이브로그도 갱신을 하기 때문
처음과 같은 결과를 얻고 싶은 경우 테스트4_1. 에서 아카이브로그도 따로 백업해둬야함
1 2 3 | $ cd /oracle/app/oracle/arch $ mkdir -p /oracle/app/oracle/archbak $ cp -av ./ ../archbak |
*19.9 버전에서 똑같이 테스트한 결과 아카이브로그 백업본 없이 해도 추가1 처럼 나오지 않고
4_1 테스트처럼 정상적으로 복구됨(12c 이후부터 정상적으로 복구되는듯함)
*추가2
4_1에서 recover 전 end backup 을 실행할 경우 아래와 같은 오류가 발생함
end 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 36 37 38 | SQL> alter tablespace system end backup; alter tablespace system end backup * ERROR at line 1: ORA-01235: END BACKUP failed for 1 file(s) and succeeded for 0 ORA-01122: database file 1 failed verification check ORA-01110: data file 1: '/oracle/app/oracle/oradata/ORCL11/system01.dbf' ORA-01208: data file is an old version - not accessing current version SQL> alter tablespace sysaux end backup; alter tablespace sysaux end backup * ERROR at line 1: ORA-01235: END BACKUP failed for 1 file(s) and succeeded for 0 ORA-01122: database file 2 failed verification check ORA-01110: data file 2: '/oracle/app/oracle/oradata/ORCL11/sysaux01.dbf' ORA-01208: data file is an old version - not accessing current version SQL> alter tablespace undotbs1 end backup; alter tablespace undotbs1 end backup * ERROR at line 1: ORA-01235: END BACKUP failed for 1 file(s) and succeeded for 0 ORA-01122: database file 3 failed verification check ORA-01110: data file 3: '/oracle/app/oracle/oradata/ORCL11/undotbs01.dbf' ORA-01208: data file is an old version - not accessing current version SQL> alter tablespace users end backup; alter tablespace users end backup * ERROR at line 1: ORA-01235: END BACKUP failed for 1 file(s) and succeeded for 0 ORA-01122: database file 4 failed verification check ORA-01110: data file 4: '/oracle/app/oracle/oradata/ORCL11/users01.dbf' ORA-01208: data file is an old version - not accessing current version |
컨트롤파일(최신버전)과 데이터파일(과거버전)의 scn이 맞지 않아 end backup 이 되지않음
참조 :
https://positivemh.tistory.com/688
https://positivemh.tistory.com/337
469950.1
http://www.dba-oracle.com/concepts/backup_recovery.htm
'ORACLE > Backup&Recover' 카테고리의 다른 글
| 오라클 11g R2 ADG Gap 발생 시나리오1 (0) | 2021.01.18 |
|---|---|
| 오라클 19c rman catalog 설명 및 catalog db 생성 (0) | 2021.01.13 |
| 오라클 11g R2 begin backup 후 end backup 을 하지 않고 복구(파일 복사 후 샘플데이터 생성) (0) | 2021.01.05 |
| 오라클 11g R2 begin backup 후 end backup 을 하지 않고 복구(샘플데이터 생성 후 파일 복사) (0) | 2021.01.04 |
| 오라클 datafile offline 후 online 테스트2 (0) | 2020.12.23 |