Describe 최악의복구시나리오에대한해결방법 here {{{ 최악의 복구 시나리오에 대한 해결방법(SCN이 모두 틀림) 안녕하세요. OCM 카페쥔장 민연홍이랍니다. http://cafe.daum.net/oraclemanager SCN이 틀렸을 경우에 복구하는 방법인데요. 정말 위급한 복구 상황에 닥쳤을 경우에 유용하겠지요. 일반적인 복구의 개념이 아니라.. 어거지로 어떻게든 복구해서 올려야 하는 상황에 사용하시길 바랍니다. 꼭 정말 위험한 상황에서 사용해야지. 일반적인 복구 상황에서 사용하시면 안됩니다. 아래에서.. 복구 작업이 끝난이후 꼭 해야 하는 것은.. DB재생성후 export후 import 하는 것이 권장된답니다. 그리고 tempfile 추가하는 것도 잊지 말구요. undo도 깨뜨리고 하니 새로 생성하셔야 합니다. 최악의 복구시나리오시에.. 그래도 해야 할 것은.. system tablespace는 무조건 백업해두어야 합니다. system tablespace가 깨지면 'dul' 유틸을 통해서 블록을 읽으는 복구도 불가능하다고 하네요. system tablespace의 bootstrap이 깨지면 너무 오래된 백업이라면 object를 생성한 dictionary 정보가 없으니 당연히 못쓰겠지요.. 아래에서도 마지막에 만든 k4 라는 테이블은 데이터는 있는데 테이블은 dictionary 정보에 없는 기이한 일이 될 수도 있겠지요. system을 먼저 cp 백업하고. 테이블만들고 datafile을 백업한다면요.. _corrupted_rollback_segments=true => rollback segment가 깨졌음, commit을 했다고 오라클을 속임, _offline_rollback_segments 강제로 rollback segment를 offline 시킴(_corrupted~, _offline~ 은 같이사용 _allow_resetlogs_corruption=true =>redo log 의 한계범위 내에서 데이터파일의 SCN이 틀어져 있거나 또는 recover시에 archive를 찾지 못해서 fail났을 경우, 즉 current redo log를 통해서 복구가 불가능할 경우, 억지로 current redo log를 reset log 시키고 open하는 파라미터 ORA- 10501 event를 통해서 adjust scn은 아래와 같은 레벨이 깨졌을 경우 레벨을올려가면서 복구하는 방법입니다. 10501, 00000, "periodically check selected heap" // *Cause: // *Action: // Level: 0x01 PGA // 0x02 SGA // 0x04 UGA // 0x08 current call // 0x10 user call // 0x20 large allocation pool begin backup을 하지 않고 cp로 복사해서 백업을 했을 경우 복구 방법 , archive도 없는 상태이며 SCN이 모두 틀림 1. 트랜잭션이 있는 상태에서 cp로 백업 vi kkk.sql create table k1 tablespace data02 as select * from dba_segments; insert into k1 select * from k1; commit; create table k2 tablespace data01 as select * from dba_objects; insert into k2 select * from k2; insert into k1 select * from k1; commit; create table k3 tablespace users as select * from dba_extents; insert into k3 select * from k3; commit; vi kkk2.sql insert into k1 select * from k1; insert into k2 select * from k2; insert into k3 select * from k3; commit; SQL> @kkk @kkk2 @kkk2 @kkk2 @kkk2 @kkk2 @kkk2 @kkk2 SQL> !cp /data1/oradata/PROD/*.dbf PROD_CP SQL> alter database backup controlfile to '/data1/oradata/PROD_CP/control01.ctl'; SQL> !cp /data1/oradata/PROD/system01.dbf PROD_CP SQL> create table k4 tablespace users as select * from dba_extents; - archive도 없음 mv archive archive3 2. cp로 백업한 것을 restore함(datafile, controlfile만 존재, redo log는 없음) mv PROD PROD_CP_ORG mv PROD_CP PROD 3. recovery status 확인 SQL> startup ORACLE instance started. Total System Global Area 101799240 bytes Fixed Size 456008 bytes Variable Size 50331648 bytes Database Buffers 50331648 bytes Redo Buffers 679936 bytes Database mounted. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open 4. 복구 시도 archive 가 없으므로 controlfile trace에서 RESETLOGS ARCHIVELOG => RESETLOGS NOARCHIVELOG로 변경 (1) 일반적인 복구 시도했으나 archive가 없음 SQL> recover database; ORA-00283: recovery session canceled due to errors ORA-01610: recovery using the BACKUP CONTROLFILE option must be done SQL> recover database until cancel using backup controlfile; ORA-00279: change 5531180 generated at 11/11/2005 00:20:07 needed for thread 1 ORA-00289: suggestion : /data1/oradata/archive/1_37.arc ORA-00280: change 5531180 for thread 1 is in sequence #37 Specify log: {=suggested | filename | AUTO | CANCEL} ORA-00308: cannot open archived log '/data1/oradata/archive/1_37.arc' ORA-27037: unable to obtain file status SVR4 Error: 2: No such file or directory Additional information: 3 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/data1/oradata/PROD/system01.dbf' (2) resetlog open도 불가능함 SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/data1/oradata/PROD/system01.dbf' (3) hidden parameter 적용 initPROD.ora 파일에 아래를 추가 _offline_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3 $,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$) _corrupted_rollback_segments=true _allow_resetlogs_corruption=true (4) controlfile 재생성 SQL> alter database backup controlfile to trace; SQL> show parameter user_dump_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ user_dump_dest string /data1/oradata/admin/PROD/udump - 트레이스에서 RESETLOGS ARCHIVELOG => RESETLOGS NOARCHIVELOG 로 변경 CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS NOARCHIVELOG -- SET STANDBY TO MAXIMIZE PERFORMANCE MAXLOGFILES 16 MAXLOGMEMBERS 2 MAXDATAFILES 30 MAXINSTANCES 3 MAXLOGHISTORY 337 LOGFILE GROUP 1 '/data1/oradata/PROD/log01a.log' SIZE 50M, GROUP 2 '/data1/oradata/PROD/log02a.log' SIZE 50M, GROUP 3 '/data1/oradata/PROD/log03b.log' SIZE 50M -- STANDBY LOGFILE DATAFILE '/data1/oradata/PROD/system01.dbf', '/data1/oradata/PROD/undotbs.dbf', '/data1/oradata/PROD/users01.dbf', '/data1/oradata/PROD/data01_02.dbf', '/data1/oradata/PROD/data02_02.dbf', '/data1/oradata/PROD/data01_03.dbf', '/data1/oradata/PROD/data01_04.dbf', '/data1/oradata/PROD/data01_01.dbf', '/data1/oradata/PROD/data02_01.dbf', '/data1/oradata/PROD/users02.dbf' CHARACTER SET KO16KSC5601; (5) DB재기동후 controlfile 재생성 SQL> startup nomount ORACLE instance started. Total System Global Area 101799240 bytes Fixed Size 456008 bytes Variable Size 50331648 bytes Database Buffers 50331648 bytes Redo Buffers 679936 bytes SQL> SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS NOARCHIVELOG 2 -- SET STANDBY TO MAXIMIZE PERFORMANCE 3 MAXLOGFILES 16 4 MAXLOGMEMBERS 2 5 MAXDATAFILES 30 6 MAXINSTANCES 3 7 MAXLOGHISTORY 337 8 LOGFILE 9 GROUP 1 '/data1/oradata/PROD/log01a.log' SIZE 50M, 10 GROUP 2 '/data1/oradata/PROD/log02a.log' SIZE 50M, 11 GROUP 3 '/data1/oradata/PROD/log03b.log' SIZE 50M 12 -- STANDBY LOGFILE 13 DATAFILE 14 '/data1/oradata/PROD/system01.dbf', 15 '/data1/oradata/PROD/undotbs.dbf', 16 '/data1/oradata/PROD/users01.dbf', 17 '/data1/oradata/PROD/data01_02.dbf', 18 '/data1/oradata/PROD/data02_02.dbf', 19 '/data1/oradata/PROD/data01_03.dbf', 20 '/data1/oradata/PROD/data01_04.dbf', 21 '/data1/oradata/PROD/data01_01.dbf', 22 '/data1/oradata/PROD/data02_01.dbf', 23 '/data1/oradata/PROD/users02.dbf' 24 CHARACTER SET KO16KSC5601; Control file created. (6) db를 resetlogs open하려하나 DB가 DOWN 됨 SQL> recover database until cancel using backup controlfile; ORA-00279: change 5531180 generated at 11/11/2005 00:20:07 needed for thread 1 ORA-00289: suggestion : /data1/oradata/archive/1_37.arc ORA-00280: change 5531180 for thread 1 is in sequence #37 Specify log: {=suggested | filename | AUTO | CANCEL} cancel ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/data1/oradata/PROD/system01.dbf' ORA-01112: media recovery not started SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced (7) alert log 분석 - system tablespace에서 메모리를 관리할 때 오라클 내부적으로 사용하는 bootstrap 깨짐 - 600 [4000] 에러가 발생, hidden parameter 을 써서 rollback semgent를 offline , corrupt 시켰기 때문에 발생하는 것으로 판단됨 ARC1: Media recovery disabled Fri Nov 11 00:48:00 2005 Errors in file /data1/oradata/admin/PROD/udump/prod_ora_12450.trc: ORA-00600: internal error code, arguments: [4000], [5], [], [], [], [], [], [] Fri Nov 11 00:48:04 2005 Errors in file /data1/oradata/admin/PROD/udump/prod_ora_12450.trc: ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00600: internal error code, arguments: [4000], [5], [], [], [], [], [], [] Fri Nov 11 00:48:04 2005 Error 704 happened during db open, shutting down database USER: terminating instance due to error 704 Instance terminated by USER, pid = 12450 ORA-1092 signalled during: alter database open resetlogs... (8) DB재기동 및 controlfile , redo를 모두 지움, controlfile 재생성 SQL> startup nomount ORACLE instance started. Total System Global Area 101799240 bytes Fixed Size 456008 bytes Variable Size 50331648 bytes Database Buffers 50331648 bytes Redo Buffers 679936 bytes SQL> !rm /data1/oradata/PROD/*.log SQL> !rm /data1/oradata/PROD/*.ctl SQL> SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS NOARCHIVELOG 2 -- SET STANDBY TO MAXIMIZE PERFORMANCE 3 MAXLOGFILES 16 4 MAXLOGMEMBERS 2 5 MAXDATAFILES 30 6 MAXINSTANCES 3 7 MAXLOGHISTORY 337 8 LOGFILE 9 GROUP 1 '/data1/oradata/PROD/log01a.log' SIZE 50M, 10 GROUP 2 '/data1/oradata/PROD/log02a.log' SIZE 50M, 11 GROUP 3 '/data1/oradata/PROD/log03b.log' SIZE 50M 12 -- STANDBY LOGFILE 13 DATAFILE 14 '/data1/oradata/PROD/system01.dbf', 15 '/data1/oradata/PROD/undotbs.dbf', 16 '/data1/oradata/PROD/users01.dbf', 17 '/data1/oradata/PROD/data01_02.dbf', 18 '/data1/oradata/PROD/data02_02.dbf', 19 '/data1/oradata/PROD/data01_03.dbf', 20 '/data1/oradata/PROD/data01_04.dbf', 21 '/data1/oradata/PROD/data01_01.dbf', 22 '/data1/oradata/PROD/data02_01.dbf', 23 '/data1/oradata/PROD/users02.dbf' 24 CHARACTER SET KO16KSC5601; Control file created. SQL> SQL> alter session set events '10015 trace name adjust_scn level 1'; Session altered. SQL> recover database until cancel using backup controlfile; ORA-00279: change 5531188 generated at 11/11/2005 00:58:07 needed for thread 1 ORA-00289: suggestion : /data1/oradata/archive/1_1.arc ORA-00280: change 5531188 for thread 1 is in sequence #1 Specify log: {=suggested | filename | AUTO | CANCEL} cancel ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/data1/oradata/PROD/system01.dbf' ORA-01112: media recovery not started SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced (9) adjust_scn level을 늘려가면서 재시도 QL> startup mount ORACLE instance started. Total System Global Area 101799240 bytes Fixed Size 456008 bytes Variable Size 50331648 bytes Database Buffers 50331648 bytes Redo Buffers 679936 bytes Database mounted. SQL> SQL> alter session set events '10015 trace name adjust_scn level 2'; Session altered. SQL> recover database; Media recovery complete. SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01139: RESETLOGS option only valid after an incomplete database recovery SQL> alter database open; Database altered. }}}