TECH
QUESTION
자주하는 질문답변 입니다.
Oracle
작성자 | 유건데이타 | 등록일 | 2015-08-14 |
제목 | COLD BACKUP을 받은 후 ARCHIVE LOG로 변환하여 사용한 경우 INCOMPLETE R | ||
---|---|---|---|
COLD BACKUP을 받은 후 ARCHIVE LOG로 변환하여 사용한 경우 INCOMPLETE RECOVERY.
====================================================================== PURPOSE -------- COLD BACKUP을 받은 후 ARCHIVE LOG MODE로 변환하여 사용한 경우의 RECOVERY 과정을 TEST로 확인하여 본다. Examples --------- ####################### No Archive log mode. ####################### SQL> select * from tab ; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- EMP TABLE EMP1 TABLE EMP2 TABLE EMP3 TABLE EMP4 TABLE 10 rows selected. SQL> select count(*) from emp3 ; COUNT(*) ---------- 0 SQL> select count(*) from emp4 ; COUNT(*) ---------- 0 ################################################## Cold backup을 받은후 Archive log mode 변경한 경우 ################################################## SVRMGR> startup mount SVRMGR> archive log list Database log mode No Archive Mode Automatic archival Enabled Archive destination D:\Oracle\oradata\SNAP\archive Oldest online log sequence 26 Current log sequence 28 SVRMGR> alter database archivelog ; SVRMGR> alter database open ; => archive log mode 변경. SQL> select * from tab ; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- EMP TABLE EMP1 TABLE EMP2 TABLE EMP3 TABLE EMP4 TABLE 10 rows selected. SQL> insert into emp3 select * from emp ; 14 rows created. SQL> commit ; Commit complete. SQL> insert into emp4 select * from emp1 ; 71680 rows created. SQL> commit ; Commit complete. SQL> select count(*) from emp3 ; COUNT(*) ---------- 14 SQL> select count(*) from emp4 ; COUNT(*) ---------- 71680 ## log switch 발생. SVRMGR> alter system switch logfile ; SQL> insert into emp3 select * from emp ; -- current log에 반영. 14 rows created. SQL> commit ; SQL> select count(*) from emp3 ; COUNT(*) ---------- 28 SQL> select count(*) from emp4 ; COUNT(*) ---------- 71680 ############################# # ALL DATABASE CRASH # ############################# ############################# # recover 과정... # ############################# 1. Restore Cold-backup 2. modify initSID.ora log_archive_start = true log_archive_dest_1 = "location=D:\Oracle\oradata\SNAP\archive" log_archive_format = %%ORACLE_SID%%T%TS%S.ARC 3. svrmgrl Statement processed. SVRMGR> startup mount ORACLE instance started. Total System Global Area 40703244 bytes Fixed Size 70924 bytes Variable Size 23777280 bytes Database Buffers 16777216 bytes Redo Buffers 77824 bytes Database mounted. SVRMGR> archive log list Database log mode No Archive Mode Automatic archival Enabled Archive destination D:\Oracle\oradata\SNAP\archive Oldest online log sequence 26 Current log sequence 28 SVRMGR> alter database archivelog ; Statement processed. SVRMGR> recover database using backup controlfile until cancel ; ORA-00279: change 340421 generated at 04/29/2001 23:42:20 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\ORADATA\SNAP\ARCHIVE\SNAPT001S00028.ARC ORA-00280: change 340421 for thread 1 is in sequence #28 Specify log: { Log applied. ORA-00279: change 340561 generated at 04/29/2001 23:47:29 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\ORADATA\SNAP\ARCHIVE\SNAPT001S00029.ARC ORA-00280: change 340561 for thread 1 is in sequence #29 ORA-00278: log file 'D:\ORACLE\ORADATA\SNAP\ARCHIVE\SNAPT001S00028.ARC' no longe r needed for this recovery Specify log: { Log applied. ORA-00279: change 340642 generated at 04/29/2001 23:47:35 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\ORADATA\SNAP\ARCHIVE\SNAPT001S00030.ARC ORA-00280: change 340642 for thread 1 is in sequence #30 ORA-00278: log file 'D:\ORACLE\ORADATA\SNAP\ARCHIVE\SNAPT001S00029.ARC' no longe r needed for this recovery Specify log: { Log applied. ORA-00279: change 340723 generated at 04/29/2001 23:47:40 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\ORADATA\SNAP\ARCHIVE\SNAPT001S00031.ARC ORA-00280: change 340723 for thread 1 is in sequence #31 ORA-00278: log file 'D:\ORACLE\ORADATA\SNAP\ARCHIVE\SNAPT001S00030.ARC' no longe r needed for this recovery Specify log: { Log applied. ORA-00279: change 340797 generated at 04/29/2001 23:48:01 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\ORADATA\SNAP\ARCHIVE\SNAPT001S00032.ARC ORA-00280: change 340797 for thread 1 is in sequence #32 ORA-00278: log file 'D:\ORACLE\ORADATA\SNAP\ARCHIVE\SNAPT001S00031.ARC' no longe r needed for this recovery Specify log: { cancel Media recovery cancelled. SVRMGR> alter database open resetlogs ; Statement processed. SVRMGR> SQL> connect scott/tiger SQL> select count(*) from emp3 ; COUNT(*) ---------- 14 SQL> select count(*) from emp4 ; COUNT(*) ---------- 71680 ######################## # 결론...... # ######################## 따라서 current log file에 기록된 14 row에 대한 부분은 recover가 될수 없지만 archive log file에 적용된 log에 대한 data는 정상적으로 복구가 가능하다. ######################## # 주의 사항 # ######################## cold backup을 restore한 후 database open후 shutdown 한 다음 archive log mode로 변경하여 recover를 진행하는 경우 SCN number가 변경되기 때문에 ora-600 error가 발생하며 media recovery를 필요로 하기 때문에 주의하여야 한다. SVRMGR> startup ORACLE instance started. Total System Global Area 40703244 bytes Fixed Size 70924 bytes Variable Size 23777280 bytes Database Buffers 16777216 bytes Redo Buffers 77824 bytes Database mounted. Database opened. SVRMGR> shutdown Database closed. Database dismounted. ORACLE instance shut down. SVRMGR> startup mount ORACLE instance started. Total System Global Area 40703244 bytes Fixed Size 70924 bytes Variable Size 23777280 bytes Database Buffers 16777216 bytes Redo Buffers 77824 bytes Database mounted. SVRMGR> alter database archivelog ; Statement processed. SVRMGR> recover database using backup controlfile until cancel ; ORA-00279: change 339542 generated at 04/29/2001 23:30:57 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\ORADATA\SNAP\ARCHIVE\SNAPT001S00003.ARC ORA-00280: change 339542 for thread 1 is in sequence #3 Specify log: { ORA-00283: recovery session canceled due to errors ORA-00600: internal error code, arguments: [3020], [8390146], [1], [3], [143], [ 240], [], [] SVRMGR> exit Server Manager complete. |
Comment | |||
---|---|---|---|
등록된 코멘트가 없습니다. |