Chap.06 Log Miner 활용과 Redo log 장애 NEW2 (by ljh1148) [2015.11.13]
6.7 Case 별로 보는 Redo Log File 장애와 대처 방법
Redo Log File 장애 종류
- a. 그룹의 멤버가 1개만 장애 발생할 경우
- b. Current 아닌 그룹 중 archive 완료된 그룹이 지워진 후 DB Close
- c. Current 아닌 그룹 중 archive 안 된 그룹이 지워진 경우 - DB Open
- d. Current 아닌 그룹 중 archive 안 된 그룹이 지워진 경우 - DB Close
- e. Current 그룹이 삭제된 경우 - DB Open
- f. Current 그룹이 삭제된 경우 - Shutdown immediate로 종료됨
- g. Current 그룹이 삭제된 경우 - Shutdown abort로 종료 - backup 있음
- h. Current 그룹이 삭제된 경우 - Shutdown abort로 종료 - backup 없음 ( 아카이브로그 없음 )
- i. 전체 Redo log가 삭제된 경우 - DB Open 상태
- j. 전체 Redo log가 삭제된 경우 - DB Close 상태 - 백업 있는 상태
- k. 전체 Redo log가 삭제된 경우 - DB Close 상태 - 백업 없는 상태
Redo Log File 장애 처리 순서도
1) 현재 상태 확인
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- --------------------------------------------- ---------- ---------- --- ---------------- ------------- ------------
1 /app/oracle/oradata/jigi/redo01_a.log 51200 10 NO CURRENT 1059150 10-NOV-15
1 /app/oracle/oradata/jigi/redo01_b.log 51200 10 NO CURRENT 1059150 10-NOV-15
2 /app/oracle/oradata/jigi/redo02_a.log 51200 8 YES INACTIVE 1038768 10-NOV-15
2 /app/oracle/oradata/jigi/redo02_b.log 51200 8 YES INACTIVE 1038768 10-NOV-15
3 /app/oracle/oradata/jigi/redo03_a.log 51200 9 YES ACTIVE 1058781 10-NOV-15
3 /app/oracle/oradata/jigi/redo03_b.log 51200 9 YES ACTIVE 1058781 10-NOV-15
6 rows selected.
Case 1 - 1개의 member가 삭제되는 장애가 발생하는 경우
SQL> !rm -f /app/oracle/oradata/jigi/redo02_a.log
SQL> !ls /app/oracle/oradata/jigi/redo02_a.log
ls: cannot access /app/oracle/oradata/jigi/redo02_a.log: 그런 파일이나 디렉터리가 없습니다
SQL> alter system switch logfile
2 ;
System altered.
SQL> /
System altered.
SQL> /
/
System altered.
SQL>
System altered.
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- --------------------------------------------- ---------- ---------- --- ---------------- ------------- ------------
1 /app/oracle/oradata/jigi/redo01_a.log 51200 13 YES INACTIVE 1059319 10-NOV-15
1 /app/oracle/oradata/jigi/redo01_b.log 51200 13 YES INACTIVE 1059319 10-NOV-15
2 /app/oracle/oradata/jigi/redo02_a.log 51200 14 NO CURRENT 1059322 10-NOV-15
2 /app/oracle/oradata/jigi/redo02_b.log 51200 14 NO CURRENT 1059322 10-NOV-15
3 /app/oracle/oradata/jigi/redo03_a.log 51200 12 YES INACTIVE 1059316 10-NOV-15
3 /app/oracle/oradata/jigi/redo03_b.log 51200 12 YES INACTIVE 1059316 10-NOV-15
6 rows selected.
SQL> !vi /app/oracle/diag/rdbms/jigi/jigi/trace/alert_jigi.log
...
Archived Log entry 11 added for thread 1 sequence 11 ID 0x718bcb06 dest 1:
Archived Log entry 12 added for thread 1 sequence 11 ID 0x718bcb06 dest 2:
Tue Nov 10 20:42:21 2015
Errors in file /app/oracle/diag/rdbms/jigi/jigi/trace/jigi_m000_2408.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/app/oracle/oradata/jigi/redo02_a.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Thread 1 cannot allocate new log, sequence 13
Checkpoint not complete
Current log# 3 seq# 12 mem# 0: /app/oracle/oradata/jigi/redo03_a.log
Current log# 3 seq# 12 mem# 1: /app/oracle/oradata/jigi/redo03_b.log
Checker run found 1 new persistent data failures
Thread 1 advanced to log sequence 13 (LGWR switch)
Current log# 1 seq# 13 mem# 0: /app/oracle/oradata/jigi/redo01_a.log
Current log# 1 seq# 13 mem# 1: /app/oracle/oradata/jigi/redo01_b.log
...
|
| 장애가 확인된 멤버는 삭제한 후 다시 생성 |
|---|
| {code:sql} |
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME
--
-
--
-- ---
-
1 /app/oracle/oradata/jigi/redo01_a.log 51200 16 NO CURRENT 1059961 10-NOV-15
1 /app/oracle/oradata/jigi/redo01_b.log 51200 16 NO CURRENT 1059961 10-NOV-15
2 /app/oracle/oradata/jigi/redo02_a.log 51200 14 YES ACTIVE 1059322 10-NOV-15
2 /app/oracle/oradata/jigi/redo02_b.log 51200 14 YES ACTIVE 1059322 10-NOV-15
3 /app/oracle/oradata/jigi/redo03_a.log 51200 15 YES ACTIVE 1059943 10-NOV-15
3 /app/oracle/oradata/jigi/redo03_b.log 51200 15 YES ACTIVE 1059943 10-NOV-15
6 rows selected.
SQL> alter database drop logfile member '/app/oracle/oradata/jigi/redo02_a.log';
Database altered.
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME
--
-
--
-- ---
-
1 /app/oracle/oradata/jigi/redo01_a.log 51200 16 NO CURRENT 1059961 10-NOV-15
1 /app/oracle/oradata/jigi/redo01_b.log 51200 16 NO CURRENT 1059961 10-NOV-15
2 /app/oracle/oradata/jigi/redo02_b.log 51200 14 YES ACTIVE 1059322 10-NOV-15
3 /app/oracle/oradata/jigi/redo03_a.log 51200 15 YES ACTIVE 1059943 10-NOV-15
3 /app/oracle/oradata/jigi/redo03_b.log 51200 15 YES ACTIVE 1059943 10-NOV-15
SQL> alter database add logfile member '/app/oracle/oradata/jigi/redo02_a.log' to group 2;
Database altered.
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME
--
-
--
-- ---
-
1 /app/oracle/oradata/jigi/redo01_a.log 51200 16 NO CURRENT 1059961 10-NOV-15
1 /app/oracle/oradata/jigi/redo01_b.log 51200 16 NO CURRENT 1059961 10-NOV-15
2 /app/oracle/oradata/jigi/redo02_a.log 51200 14 YES ACTIVE 1059322 10-NOV-15
2 /app/oracle/oradata/jigi/redo02_b.log 51200 14 YES ACTIVE 1059322 10-NOV-15
3 /app/oracle/oradata/jigi/redo03_a.log 51200 15 YES ACTIVE 1059943 10-NOV-15
3 /app/oracle/oradata/jigi/redo03_b.log 51200 15 YES ACTIVE 1059943 10-NOV-15
6 rows selected.
| {code} |
Case 2 - archive 완료된 그룹이 지워진 후 DB Close 상태
- 에러 코드 : ORA-00313:open failed for members of log group...
- Current 가 아닌 하나의 그룹이 삭제된 후 DB가 종료된 경우
- 포인트 : Archive 완료 여부
- 장애 이유 : 오라클이 시작 될 때 Control file을 읽어서 지정된 Redo Log File을
찾으려고 하는데 실제 파일이 삭제되어 없기 때문에 발생한 것입니다.- 해결 방안 : Control file에서 해당 그룹의 명단을 삭제하면 간단히 해결
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
---------- --------------------------------------------- ---------- ---------- --- ---------------- ------------- ------------ ---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 16 NO CURRENT 1059961 10-NOV-15 NO
1 /app/oracle/oradata/jigi/redo01_b.log 51200 16 NO CURRENT 1059961 10-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_a.log 51200 14 YES INACTIVE 1059322 10-NOV-15 YES
2 /app/oracle/oradata/jigi/redo02_b.log 51200 14 YES INACTIVE 1059322 10-NOV-15 YES
3 /app/oracle/oradata/jigi/redo03_a.log 51200 15 YES INACTIVE 1059943 10-NOV-15 YES
3 /app/oracle/oradata/jigi/redo03_b.log 51200 15 YES INACTIVE 1059943 10-NOV-15 YES
6 rows selected.
SQL> !rm /app/oracle/oradata/jigi/redo02*
SQL> !ls /app/oracle/oradata/jigi/redo02*
ls: cannot access /app/oracle/oradata/jigi/redo02*: 그런 파일이나 디렉터리가 없습니다
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2217464 bytes
Variable Size 494930440 bytes
Database Buffers 285212672 bytes
Redo Buffers 2637824 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 2959
Session ID: 1 Serial number: 5
SQL> !vi /app/oracle/diag/rdbms/jigi/jigi/trace/alert_jigi.log
...
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Errors in file /app/oracle/diag/rdbms/jigi/jigi/trace/jigi_lgwr_2873.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/app/oracle/oradata/jigi/redo02_b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /app/oracle/diag/rdbms/jigi/jigi/trace/jigi_lgwr_2873.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/app/oracle/oradata/jigi/redo02_b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /app/oracle/diag/rdbms/jigi/jigi/trace/jigi_ora_2959.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 2 thread 1: '/app/oracle/oradata/jigi/redo02_b.log'
ORA-00312: online log 2 thread 1: '/app/oracle/oradata/jigi/redo02_a.log'
USER (ospid: 2959): terminating the instance due to error 313
Tue Nov 10 21:34:26 2015
ARC1 started with pid=21, OS id=2963
Instance terminated by USER, pid = 2959
...
SQL> conn sys/oracle as sysdba
Connected to an idle instance.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2217464 bytes
Variable Size 494930440 bytes
Database Buffers 285212672 bytes
Redo Buffers 2637824 bytes
Database mounted.
SQL>
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
---------- --------------------------------------------- ---------- ---------- --- ---------------- ------------- ------------ ---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 16 NO CURRENT 1059961 10-NOV-15 NO
1 /app/oracle/oradata/jigi/redo01_b.log 51200 16 NO CURRENT 1059961 10-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_a.log 51200 14 YES INACTIVE 1059322 10-NOV-15 YES
2 /app/oracle/oradata/jigi/redo02_b.log 51200 14 YES INACTIVE 1059322 10-NOV-15 YES
3 /app/oracle/oradata/jigi/redo03_a.log 51200 15 YES INACTIVE 1059943 10-NOV-15 YES
3 /app/oracle/oradata/jigi/redo03_b.log 51200 15 YES INACTIVE 1059943 10-NOV-15 YES
6 rows selected.
SQL> alter database drop logfile group 2;
Database altered.
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
---------- --------------------------------------------- ---------- ---------- --- ---------------- ------------- ------------ ---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 16 NO CURRENT 1059961 10-NOV-15 NO
1 /app/oracle/oradata/jigi/redo01_b.log 51200 16 NO CURRENT 1059961 10-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_a.log 51200 15 YES INACTIVE 1059943 10-NOV-15 YES
3 /app/oracle/oradata/jigi/redo03_b.log 51200 15 YES INACTIVE 1059943 10-NOV-15 YES
SQL>
SQL> alter database open;
Database altered.
SQL> alter database add logfile group 2 ( '/app/oracle/oradata/jigi/redo02_a.log', '/app/oracle/oradata/jigi/redo02_b.log' ) size 50m;
Database altered.
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
---------- --------------------------------------------- ---------- ---------- --- ---------------- ------------- ------------ ---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 16 NO CURRENT 1059961 10-NOV-15 NO
1 /app/oracle/oradata/jigi/redo01_b.log 51200 16 NO CURRENT 1059961 10-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_a.log 51200 0 YES UNUSED 0 YES
2 /app/oracle/oradata/jigi/redo02_b.log 51200 0 YES UNUSED 0 YES
3 /app/oracle/oradata/jigi/redo03_a.log 51200 15 YES INACTIVE 1059943 10-NOV-15 YES
3 /app/oracle/oradata/jigi/redo03_b.log 51200 15 YES INACTIVE 1059943 10-NOV-15 YES
6 rows selected.
SQL>
Case 3 - Current 아닌 그룹 중에 archive 안 된 그룹이 삭제되고 DB Open 상태일 경우
- 에러 코드 : ORA-00313:open failed for members of log group...
- 서버 운영 중에 특정 그룹이 지워진 후 계속 log switch가 발생하게 되면 이런 상태가 됨
- Archive Hang 현상이 발생하여 중단이 되다가 강제로 비정상 종료가 발생 ( Case 4 )
| Step 1. 현재 상태 확인 |
|---|
| {code:sql} |
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
--
-
--
-- ---
-
---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 19 YES INACTIVE 1062460 10-NOV-15 YES
1 /app/oracle/oradata/jigi/redo01_b.log 51200 19 YES INACTIVE 1062460 10-NOV-15 YES
2 /app/oracle/oradata/jigi/redo02_a.log 51200 20 YES INACTIVE 1062463 10-NOV-15 YES
2 /app/oracle/oradata/jigi/redo02_b.log 51200 20 YES INACTIVE 1062463 10-NOV-15 YES
3 /app/oracle/oradata/jigi/redo03_a.log 51200 21 NO CURRENT 1082910 10-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_b.log 51200 21 NO CURRENT 1082910 10-NOV-15 NO
6 rows selected.
| {code} | Step 2. 그룹 삭제 ( 현재 archive 완료된 1번 그룹을 삭제합니다. ) |
|---|---|
| {code:sql} |
SQL> !rm -f /app/oracle/oradata/jigi/redo01*
SQL> !ls /app/oracle/oradata/jigi/redo01*
ls: cannot access /app/oracle/oradata/jigi/redo01*: 그런 파일이나 디렉터리가 없습니다
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> / <-- 행 발생 Ctrl + C
^Calter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SQL> !log
/bin/bash: log: command not found
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
--
-
--
-- ---
-
---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 22 NO INACTIVE 1095467 10-NOV-15 NO
1 /app/oracle/oradata/jigi/redo01_b.log 51200 22 NO INACTIVE 1095467 10-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_a.log 51200 23 NO INACTIVE 1095470 10-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_b.log 51200 23 NO INACTIVE 1095470 10-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_a.log 51200 24 NO CURRENT 1095474 10-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_b.log 51200 24 NO CURRENT 1095474 10-NOV-15 NO
6 rows selected.
SQL>
- 오라클에서는 Archiving을 순서대로 하기 때문에 전부 NO
- 1번 그룹 삭제 되어 log file switch 으로 인해 순서대로 아카이빙을 하지 못해 발생 ( SEQ 순서 )
| {code} | Step 3. Alert log 확인 후 원인 파악 하기 |
|---|---|
| {code:sql} |
SQL> !vi /app/oracle/diag/rdbms/jigi/jigi/trace/alert_jigi.log
...
Tue Nov 10 22:53:25 2015
Errors in file /app/oracle/diag/rdbms/jigi/jigi/trace/jigi_m000_4084.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/app/oracle/oradata/jigi/redo01_b.log' -- 1번 그룹이 계속 장애가 발생
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/app/oracle/oradata/jigi/redo01_a.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
...
| {code} | Step 4. 장애 해결 |
|---|---|
| {code:sql} |
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
/
System altered.
SQL>
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL>
| {code} * clear unarchived : drop 하고 신규로 add 해주는 역할 * 온라인 중에 문제 해결 가능 * 작업 이후 반드시 full backup 수행해야 합니다. ** archive log file이 중간에 비어버리기 때문에 그냥 넘어갔다가는 향후에 장애가 발생해 복구가 안되니 반드시 이 작업 후에는 전체 받힌 백업을 받아야 합니다. *** 11g는 여기까지하면 문제 해결 *** 10버전에서는 log switch가 발생하면 다시 archive hang이 발생합니다. ( page. 179 ) **** clear 작업 이후 drop 이후 add ( 10g ) |
Case 4 - Current 아닌 그룹 중 archive 안 된 그룹이 지워지고 DB Close 상태인 경우
- 에러코드 : ORA-00257:archiver error. connect internal only, until freed
- 에러코드 : ORA-00313:openr failed for members of log group 2 of thread 1
- 에러코드 : ORA-00313:openr failed for members of log group 2 of thread 1
- 장애 현상 : Case 3의 경우에 DB가 종료된 상황
- DB를 Startup하면 장애가 발생해서 Open이 되지 않습니다.
- Case 2번의 경우 해당 로그 파일 삭제 후 기동
- DB를 Startup하면 장애가 발생해서 Open이 되지 않습니다.
| Step 1. 현재 상태 확인 |
|---|
| {CODE:SQL} |
QL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
--
-
--
-- ---
-
---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 16 NO CURRENT 1059961 10-NOV-15 NO
1 /app/oracle/oradata/jigi/redo01_b.log 51200 16 NO CURRENT 1059961 10-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_a.log 51200 0 YES UNUSED 0 YES
2 /app/oracle/oradata/jigi/redo02_b.log 51200 0 YES UNUSED 0 YES
3 /app/oracle/oradata/jigi/redo03_a.log 51200 15 YES INACTIVE 1059943 10-NOV-15 YES
3 /app/oracle/oradata/jigi/redo03_b.log 51200 15 YES INACTIVE 1059943 10-NOV-15 YES
6 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> !rm -f /app/oracle/oradata/jigi/redo03*
SQL> !ls /app/oracle/oradata/jigi/redo03*
ls: cannot access /app/oracle/oradata/jigi/redo03*: 그런 파일이나 디렉터리가 없습니다
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
/
System altered.
SQL>
System altered.
SQL> / <-- hang
| {CODE} | 일반 계정 접속 에러 및 sys 로그인 복구 |
|---|---|
| {code:sql} |
SQL> conn scott/tiger
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.
Warning: You are no longer connected to ORACLE.
SQL>
SQL> conn scott/tiger
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.
SQL> conn scott/tiger
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.
SQL>
SQL> conn sys/oracle as sysdba
Connected.
SQL>
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
| {code} | Step 3. 재시작시키면 에러가 발생하면서 중단됩니다. |
|---|---|
| {code:sql} |
SQL> startup
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2217464 bytes
Variable Size 494930440 bytes
Database Buffers 285212672 bytes
Redo Buffers 2637824 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 3476
Session ID: 1 Serial number: 5
| {code} | Step 4. 다른 터미널을 하나 더 열어서 Alert log에서 장애를 확인합니다. |
|---|---|
| {code:Sql} |
SQL> !vi /app/oracle/diag/rdbms/jigi/jigi/trace/alert_jigi.log
...
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Errors in file /app/oracle/diag/rdbms/jigi/jigi/trace/jigi_lgwr_3390.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/app/oracle/oradata/jigi/redo03_b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/app/oracle/oradata/jigi/redo03_a.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /app/oracle/diag/rdbms/jigi/jigi/trace/jigi_lgwr_3390.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/app/oracle/oradata/jigi/redo03_b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/app/oracle/oradata/jigi/redo03_a.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /app/oracle/diag/rdbms/jigi/jigi/trace/jigi_ora_3476.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 3 thread 1: '/app/oracle/oradata/jigi/redo03_a.log'
ORA-00312: online log 3 thread 1: '/app/oracle/oradata/jigi/redo03_b.log'
USER (ospid: 3476): terminating the instance due to error 313
Tue Nov 10 22:00:15 2015
ARC1 started with pid=21, OS id=3480
Instance terminated by USER, pid = 3476
..
| {code} | Case 2의 경우에 해결했던 명령으로 시도 |
|---|---|
| {code:sql} |
SQL> conn sys/oracle as sysdba
Connected to an idle instance.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2217464 bytes
Variable Size 494930440 bytes
Database Buffers 285212672 bytes
Redo Buffers 2637824 bytes
Database mounted.
SQL>
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance jigi (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/app/oracle/oradata/jigi/redo03_a.log'
ORA-00312: online log 3 thread 1: '/app/oracle/oradata/jigi/redo03_b.log'
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL>
SQL> alter database open;
Database altered.
SQL> !ls /app/oracle/oradata/jigi/redo03*
/app/oracle/oradata/jigi/redo03_a.log /app/oracle/oradata/jigi/redo03_b.log
SQL>
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
--
-
--
-- ---
-
---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 19 YES INACTIVE 1062460 10-NOV-15 YES
1 /app/oracle/oradata/jigi/redo01_b.log 51200 19 YES INACTIVE 1062460 10-NOV-15 YES
2 /app/oracle/oradata/jigi/redo02_a.log 51200 20 YES INACTIVE 1062463 10-NOV-15 YES
2 /app/oracle/oradata/jigi/redo02_b.log 51200 20 YES INACTIVE 1062463 10-NOV-15 YES
3 /app/oracle/oradata/jigi/redo03_a.log 51200 21 NO CURRENT 1082910 10-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_b.log 51200 21 NO CURRENT 1082910 10-NOV-15 NO
6 rows selected.
| {code} * 파일이 자동으로 생성 * 이 명령으로 사용한 후에는 반드시 전체 Full Backup을 수행하길 권장 |
Case 5-Current 그룹이 삭제된 후 DB Open 상태
- Case 3과 같은 경우입니다. 해결 방법도 동일
Case 6.Current 그룹이 삭제된 경우 - Shutdown immediate로 종료
- 에러 코드 : ORA-00313:open failed for members of log group
- 에러 코드 : ORA-03113:end-of-file on communication channel
- Current 그룹이 삭제된 후 DB가 종료되었다면 정상 종료인지 비정상 종료인지에 따라 향후
재시작 할 때 심각한 문제가 될 수도 있습니다.
| Step 1. 현재 상태 확인 |
|---|
| {code:sql} |
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
--
-
--
-- ---
-
---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 28 YES INACTIVE 1095851 10-NOV-15 YES
1 /app/oracle/oradata/jigi/redo01_b.log 51200 28 YES INACTIVE 1095851 10-NOV-15 YES
2 /app/oracle/oradata/jigi/redo02_a.log 51200 29 YES INACTIVE 1096225 10-NOV-15 YES
2 /app/oracle/oradata/jigi/redo02_b.log 51200 29 YES INACTIVE 1096225 10-NOV-15 YES
3 /app/oracle/oradata/jigi/redo03_a.log 51200 30 NO CURRENT 1096228 10-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_b.log 51200 30 NO CURRENT 1096228 10-NOV-15 NO
6 rows selected.
SQL>
| {code} | Step 2. 데이터 입력 후 장애 발생 |
|---|---|
| {code:sql} |
SQL> create table scott.tt200( no number ) tablespace users;
Table created.
SQL> insert into scott.tt200 values( 1 );
1 row created.
SQL> commit;
Commit complete.
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
--
-
--
-- ---
-
---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 28 YES INACTIVE 1095851 10-NOV-15 YES
1 /app/oracle/oradata/jigi/redo01_b.log 51200 28 YES INACTIVE 1095851 10-NOV-15 YES
2 /app/oracle/oradata/jigi/redo02_a.log 51200 29 YES INACTIVE 1096225 10-NOV-15 YES
2 /app/oracle/oradata/jigi/redo02_b.log 51200 29 YES INACTIVE 1096225 10-NOV-15 YES
3 /app/oracle/oradata/jigi/redo03_a.log 51200 30 NO CURRENT 1096228 10-NOV-15 NO -- 3번 그룹에 저장
3 /app/oracle/oradata/jigi/redo03_b.log 51200 30 NO CURRENT 1096228 10-NOV-15 NO
6 rows selected.
SQL>
| {code} | Step 3. Current 상태인 2번 그룹을 OS 명령어로 삭제한 후 재부팅합니다. |
|---|---|
| {code:sql} |
SQL> !rm -f /app/oracle/oradata/jigi/redo03*
SQL> !ls /app/oracle/oradata/jigi/redo03*
ls: cannot access /app/oracle/oradata/jigi/redo03*: 그런 파일이나 디렉터리가 없습니다
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2217464 bytes
Variable Size 499124744 bytes
Database Buffers 281018368 bytes
Redo Buffers 2637824 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 4509
Session ID: 1 Serial number: 5
| {code} | Step 4. Alert log 의 에러 메세지를 확인 합니다. |
|---|---|
| {code:sql} |
SQL> !vi /app/oracle/diag/rdbms/jigi/jigi/trace/alert_jigi.log
...
Errors in file /app/oracle/diag/rdbms/jigi/jigi/trace/jigi_lgwr_4424.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/app/oracle/oradata/jigi/redo03_b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/app/oracle/oradata/jigi/redo03_a.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /app/oracle/diag/rdbms/jigi/jigi/trace/jigi_ora_4509.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 3 thread 1: '/app/oracle/oradata/jigi/redo03_a.log'
ORA-00312: online log 3 thread 1: '/app/oracle/oradata/jigi/redo03_b.log'
USER (ospid: 4509): terminating the instance due to error 313
Tue Nov 10 23:20:39 2015
ARC2 started with pid=22, OS id=4515
Instance terminated by USER, pid = 4509
...
| {code} | Step 5. 복구 후 Open 합니다. |
|---|---|
| {code:sql} |
SQL> conn sys/oracle as sysdba
Connected to an idle instance.
SQL>
SQL> startup mount
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2217464 bytes
Variable Size 499124744 bytes
Database Buffers 281018368 bytes
Redo Buffers 2637824 bytes
Database mounted.
SQL>
SQL> recover database until cancel
Media recovery complete.
SQL>
SQL> alter database open resetlogs
2 ;
Database altered.
SQL> select * from scott.tt200;
NO
--
1
SQL>
* Shutdown immediate란 DB Buffer Cache에 있는 내용 중에서 Commit 완료된 내용은 데이터 파일로
전부 저장하고 Commit이 안 된 내용은 rollback한 후 종료 ( 복구 불필요 )
* Current 상태의 Redo log file이 없기 때문에 Open 이 되지 않습니다.
** Control File에는 명단이 있으나 실제 파일이 없는 Redo log를 재생성시키는 RESETLOGS 옵션을 사용하는 것입니다.
*** 문제는 RESETLOGS 옵션은 불완전 복구를 수행했을 경우만 쓸 수 있다는 ?입니다.
**** 분완전 복구 옵션 중에 until cancel 옵션을 쓰며 됩니다. ( 설명 뒤장 )
h3. Case 7 - Current 그룹이 삭제된 후 Shutdown abort로 종료 ( Backup file이 존재할 경우 )
* Archive log mode 일 경우를 가정하고 진행 ( Noarchive log mode : 복구 불가능 )
* ORA-00326:log begins at change...
* ORA-00283:recovery session canceled due to errors
h3. page. 192 그림
!2015-11-07_02.PNG!
* SCN 106번 작업이 Current Redo Log File에 저장되어 있음
* Full export + archive log + redo log를 Current가 지워지기 직전까지인 SCN 105번 까지 복구
|| Step 0. DB 종료 후 전체 데이터베이스를 full backup을 수행하세요 ||
|{code:sql}
SQL> select name from v$datafile;
NAME
----------------------------------------
/app/oracle/oradata/jigi/system01.dbf
/app/oracle/oradata/jigi/sysaux01.dbf
/app/oracle/oradata/jigi/undotbs01.dbf
/app/oracle/oradata/jigi/users01.dbf
/app/oracle/oradata/jigi/example01.dbf
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> !cp /app/oracle/oradata/jigi/*.dbf /app/oracle/oradata
|
| Step 1. Open 후 현재 상태 확인 |
|---|
| {code:Sql} |
SQL> startup
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2217464 bytes
Variable Size 499124744 bytes
Database Buffers 281018368 bytes
Redo Buffers 2637824 bytes
Database mounted.
Database opened.
SQL>
SQL> !ls /app/oracle/oradata
example01.dbf jigi sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
--
-
--
-- ---
-
---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 1 YES INACTIVE 1122437 12-NOV-15 YES
1 /app/oracle/oradata/jigi/redo01_b.log 51200 1 YES INACTIVE 1122437 12-NOV-15 YES
2 /app/oracle/oradata/jigi/redo02_a.log 51200 2 NO CURRENT 1142886 12-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_b.log 51200 2 NO CURRENT 1142886 12-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_a.log 51200 0 YES UNUSED 0 YES
3 /app/oracle/oradata/jigi/redo03_b.log 51200 0 YES UNUSED 0 YES
6 rows selected.
SQL> create table scott.tt600 ( no number );
Table created.
SQL> insert into scott.tt600 values( 1 );
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
--
-
--
-- ---
-
---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 1 YES INACTIVE 1122437 12-NOV-15 YES
1 /app/oracle/oradata/jigi/redo01_b.log 51200 1 YES INACTIVE 1122437 12-NOV-15 YES
2 /app/oracle/oradata/jigi/redo02_a.log 51200 2 YES ACTIVE 1142886 12-NOV-15 YES
2 /app/oracle/oradata/jigi/redo02_b.log 51200 2 YES ACTIVE 1142886 12-NOV-15 YES
3 /app/oracle/oradata/jigi/redo03_a.log 51200 3 NO CURRENT 1143360 12-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_b.log 51200 3 NO CURRENT 1143360 12-NOV-15 NO
6 rows selected.
SQL> insert into scott.tt600 values( 2 );
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
--
-
--
-- ---
-
---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 4 NO CURRENT 1143390 12-NOV-15 NO
1 /app/oracle/oradata/jigi/redo01_b.log 51200 4 NO CURRENT 1143390 12-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_a.log 51200 2 YES ACTIVE 1142886 12-NOV-15 YES
2 /app/oracle/oradata/jigi/redo02_b.log 51200 2 YES ACTIVE 1142886 12-NOV-15 YES
3 /app/oracle/oradata/jigi/redo03_a.log 51200 3 YES ACTIVE 1143360 12-NOV-15 YES
3 /app/oracle/oradata/jigi/redo03_b.log 51200 3 YES ACTIVE 1143360 12-NOV-15 YES
6 rows selected.
SQL> insert into scott.tt600 values( 3 );
1 row created.
SQL> commit;
Commit complete.
SQL> sleect * from scott.tt600;
SP2-0734: unknown command beginning "sleect * f..." - rest of line ignored.
SQL> select * from scott.tt600;
NO
--
1
2
3
| {code} | Step 2. Current group 삭제 후 shutdown abort 시킴. |
|---|---|
| {code:Sql} SQL> @log |
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
--
-
--
-- ---
-
---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 4 NO CURRENT 1143390 12-NOV-15 NO
1 /app/oracle/oradata/jigi/redo01_b.log 51200 4 NO CURRENT 1143390 12-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_a.log 51200 2 YES ACTIVE 1142886 12-NOV-15 YES
2 /app/oracle/oradata/jigi/redo02_b.log 51200 2 YES ACTIVE 1142886 12-NOV-15 YES
3 /app/oracle/oradata/jigi/redo03_a.log 51200 3 YES ACTIVE 1143360 12-NOV-15 YES
3 /app/oracle/oradata/jigi/redo03_b.log 51200 3 YES ACTIVE 1143360 12-NOV-15 YES
SQL> !rm -f /app/oracle/oradata/jigi/redo01*
SQL> !ls /app/oracle/oradata/jigi/redo01*
ls: cannot access /app/oracle/oradata/jigi/redo01*: 그런 파일이나 디렉터리가 없습니다
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
SQL> !ls /app/oracle/oradata/*.dbf
/app/oracle/oradata/example01.dbf /app/oracle/oradata/system01.dbf /app/oracle/oradata/undotbs01.dbf
/app/oracle/oradata/sysaux01.dbf /app/oracle/oradata/temp01.dbf /app/oracle/oradata/users01.dbf
| {code} | Step 3. 복구 하기 |
|---|---|
| {code:Sql} |
SQL> !cp /app/oracle/oradata/*.dbf /app/oracle/oradata/jigi/
SQL> SQL>
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2217464 bytes
Variable Size 499124744 bytes
Database Buffers 281018368 bytes
Redo Buffers 2637824 bytes
Database mounted.
SQL>
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/app/oracle/oradata/jigi/redo01_b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/app/oracle/oradata/jigi/redo01_a.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> recover database until cancel;
ORA-00279: change 1143390 generated at 11/12/2015 21:06:37 needed for thread 1
ORA-00289: suggestion : /home/data/arc2/arch_1_4_895611083.arc
ORA-00280: change 1143390 for thread 1 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
^C^C
SQL>
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL> select * from scott.tt600;
NO
--
1
2
| {code} |
Case 8 - Current 그룹이 삭제되고 Shutdown abort로 종료됨( Backup File이나 Archive Log File 없을 경우 )
- ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
- ORA-00308: cannot open archived log....
- ORA-01194: file 1 needs more recovery to be consistent
상황 1) 데이터를 모두 복구해내는 경우
| Step 1. No Archive Log Mode 로 변경하기 |
|---|
| {code:sql} |
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2217464 bytes
Variable Size 499124744 bytes
Database Buffers 281018368 bytes
Redo Buffers 2637824 bytes
Database mounted.
SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/data/arc2
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL>
SQL> alter database noarchivelog;
Database altered.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /home/data/arc2
Oldest online log sequence 1
Current log sequence 1
SQL>
SQL> alter database open;
Database altered.
SQL>
| {code} | Step 2. 데이터 입력 |
|---|---|
| {code:sql} |
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
--
-
--
-- ---
-
---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 1 NO CURRENT 1143391 12-NOV-15 NO
1 /app/oracle/oradata/jigi/redo01_b.log 51200 1 NO CURRENT 1143391 12-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_a.log 51200 0 YES UNUSED 0 YES
2 /app/oracle/oradata/jigi/redo02_b.log 51200 0 YES UNUSED 0 YES
3 /app/oracle/oradata/jigi/redo03_a.log 51200 0 YES UNUSED 0 YES
3 /app/oracle/oradata/jigi/redo03_b.log 51200 0 YES UNUSED 0 YES
6 rows selected.
SQL> create table scott.tt650( no number );
Table created.
SQL> insert into scott.tt650 values( 1 );
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
--
-
--
-- ---
-
---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 1 NO ACTIVE 1143391 12-NOV-15 NO
1 /app/oracle/oradata/jigi/redo01_b.log 51200 1 NO ACTIVE 1143391 12-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_a.log 51200 2 NO CURRENT 1149706 12-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_b.log 51200 2 NO CURRENT 1149706 12-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_a.log 51200 0 YES UNUSED 0 YES
3 /app/oracle/oradata/jigi/redo03_b.log 51200 0 YES UNUSED 0 YES
6 rows selected.
SQL> insert into scott.tt650 values( 2 );
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
--
-
--
-- ---
-
---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 1 NO ACTIVE 1143391 12-NOV-15 NO
1 /app/oracle/oradata/jigi/redo01_b.log 51200 1 NO ACTIVE 1143391 12-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_a.log 51200 2 NO ACTIVE 1149706 12-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_b.log 51200 2 NO ACTIVE 1149706 12-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_a.log 51200 3 NO CURRENT 1149729 12-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_b.log 51200 3 NO CURRENT 1149729 12-NOV-15 NO
6 rows selected.
SQL> insert into scott.tt650 values( 3 );
1 row created.
SQL> commit;
Commit complete.
SQL> select * from scott.tt650;
NO
--
1
2
3
| {code} | Step 4. 복구 |
|---|---|
| {code:sql} |
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
--
-
--
-- ---
-
---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 4 NO INACTIVE 1149838 12-NOV-15 NO
1 /app/oracle/oradata/jigi/redo01_b.log 51200 4 NO INACTIVE 1149838 12-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_a.log 51200 5 NO INACTIVE 1149842 12-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_b.log 51200 5 NO INACTIVE 1149842 12-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_a.log 51200 6 NO CURRENT 1149845 12-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_b.log 51200 6 NO CURRENT 1149845 12-NOV-15 NO
6 rows selected.
SQL> !rm -f /app/oracle/oradata/jigi/redo03*
SQL> !ls /app/oracle/oradata/jigi/redo03*
ls: cannot access /app/oracle/oradata/jigi/redo03*: 그런 파일이나 디렉터리가 없습니다
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
SQL> !ls /$ORACLE_HOME/dbs/*
//app/oracle/product/11g/dbs/hc_jigi.dat //app/oracle/product/11g/dbs/lkJIGI //app/oracle/product/11g/dbs/spfilejigi_20151108.ora
//app/oracle/product/11g/dbs/init.ora //app/oracle/product/11g/dbs/orapwjigi
//app/oracle/product/11g/dbs/initjigi.ora //app/oracle/product/11g/dbs/spfilejigi.ora
SQL>
SQL> !vi /app/oracle/product/11g/dbs/initjigi.ora
SQL>
SQL> -- _allow_resetlogs_corruption=true
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2217464 bytes
Variable Size 499124744 bytes
Database Buffers 281018368 bytes
Redo Buffers 2637824 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/app/oracle/oradata/jigi/redo03_b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/app/oracle/oradata/jigi/redo03_a.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
--
-
--
-- ---
-
---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 4 NO INACTIVE 1149838 12-NOV-15 NO
1 /app/oracle/oradata/jigi/redo01_b.log 51200 4 NO INACTIVE 1149838 12-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_a.log 51200 5 NO INACTIVE 1149842 12-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_b.log 51200 5 NO INACTIVE 1149842 12-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_a.log 51200 6 NO CURRENT 1149845 12-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_b.log 51200 6 NO CURRENT 1149845 12-NOV-15 NO
6 rows selected.
SQL> recover database until cancel;
ORA-00279: change 1149845 generated at 11/12/2015 22:49:05 needed for thread 1
ORA-00289: suggestion : /home/data/arc2/arch_1_6_895613121.arc
ORA-00280: change 1149845 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/home/data/arc2/arch_1_6_895613121.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 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: '/app/oracle/oradata/jigi/system01.dbf'
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: '/app/oracle/oradata/jigi/system01.dbf'
SQL> recover database until cancel;
ORA-00279: change 1149845 generated at 11/12/2015 22:49:05 needed for thread 1
ORA-00289: suggestion : /home/data/arc2/arch_1_6_895613121.arc
ORA-00280: change 1149845 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/home/data/arc2/arch_1_6_895613121.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 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: '/app/oracle/oradata/jigi/system01.dbf'
SQL> show parameter spfile;
NAME TYPE VALUE
---
--
spfile string /app/oracle/product/11g/dbs/sp
filejigi.ora
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup pfile=/app/oracle/product/11g/dbs/initjigi.ora
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2217464 bytes
Variable Size 473958920 bytes
Database Buffers 306184192 bytes
Redo Buffers 2637824 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select * from scott.tt650;
NO
--
1
2
3
SQL>
| {code} * 체크 포인트가 발생했기 때문에 데이터가 데이터파일에 모두 저장되었기 때문입니다. |
상황 2) Current에 저장된 데이터는 복구 못 하는 경우
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
---------- --------------------------------------------- ---------- ---------- --- ---------------- ------------- ------------ ---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 1 NO CURRENT 1149846 12-NOV-15 NO
1 /app/oracle/oradata/jigi/redo01_b.log 51200 1 NO CURRENT 1149846 12-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_a.log 51200 0 YES UNUSED 0 YES
2 /app/oracle/oradata/jigi/redo02_b.log 51200 0 YES UNUSED 0 YES
3 /app/oracle/oradata/jigi/redo03_a.log 51200 0 YES UNUSED 0 YES
3 /app/oracle/oradata/jigi/redo03_b.log 51200 0 YES UNUSED 0 YES
6 rows selected.
SQL> create table scott.tt660( no number );
Table created.
SQL> insert into scott.tt660 values( 1 );
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into scott.tt660 values( 2 );
1 row created.
SQL> commit;
Commit complete.
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
---------- --------------------------------------------- ---------- ---------- --- ---------------- ------------- ------------ ---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 1 NO ACTIVE 1149846 12-NOV-15 NO
1 /app/oracle/oradata/jigi/redo01_b.log 51200 1 NO ACTIVE 1149846 12-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_a.log 51200 2 NO CURRENT 1150194 12-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_b.log 51200 2 NO CURRENT 1150194 12-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_a.log 51200 0 YES UNUSED 0 YES
3 /app/oracle/oradata/jigi/redo03_b.log 51200 0 YES UNUSED 0 YES
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> insert into scott.tt660 values( 3 );
1 row created.
SQL> commit;
Commit complete.
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
---------- --------------------------------------------- ---------- ---------- --- ---------------- ------------- ------------ ---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 1 NO ACTIVE 1149846 12-NOV-15 NO
1 /app/oracle/oradata/jigi/redo01_b.log 51200 1 NO ACTIVE 1149846 12-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_a.log 51200 2 NO ACTIVE 1150194 12-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_b.log 51200 2 NO ACTIVE 1150194 12-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_a.log 51200 3 NO CURRENT 1150216 12-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_b.log 51200 3 NO CURRENT 1150216 12-NOV-15 NO
6 rows selected.
SQL> !rm -f /app/oracle/oradata/jigi/redo03*
SQL> !ls /app/oracle/oradata/jigi/redo03*
ls: cannot access /app/oracle/oradata/jigi/redo03*: 그런 파일이나 디렉터리가 없습니다
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
SQL> !vi /app/oracle/product/11g/dbs/initjigi.ora
SQL> -- _allow_resetlogs_corruption=true
SQL>
SQL> startup mount pfile=/app/oracle/product/11g/dbs/initjigi.ora
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2217464 bytes
Variable Size 473958920 bytes
Database Buffers 306184192 bytes
Redo Buffers 2637824 bytes
Database mounted.
SQL>
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
---------- --------------------------------------------- ---------- ---------- --- ---------------- ------------- ------------ ---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 1 NO ACTIVE 1149846 12-NOV-15 NO
1 /app/oracle/oradata/jigi/redo01_b.log 51200 1 NO ACTIVE 1149846 12-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_a.log 51200 2 NO ACTIVE 1150194 12-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_b.log 51200 2 NO ACTIVE 1150194 12-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_a.log 51200 3 NO CURRENT 1150216 12-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_b.log 51200 3 NO CURRENT 1150216 12-NOV-15 NO
6 rows selected.
SQL> recover database until cancel;
ORA-00279: change 1149849 generated at 11/12/2015 22:59:21 needed for thread 1
ORA-00289: suggestion : /home/data/arc2/arch_1_1_895618740.arc
ORA-00280: change 1149849 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/app/oracle/oradata/jigi/redo01_a.log
ORA-00279: change 1150194 generated at 11/12/2015 23:04:41 needed for thread 1
ORA-00289: suggestion : /home/data/arc2/arch_1_2_895618740.arc
ORA-00280: change 1150194 for thread 1 is in sequence #2
ORA-00278: log file '/app/oracle/oradata/jigi/redo01_a.log' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/app/oracle/oradata/jigi/redo02_a.log
ORA-00279: change 1150216 generated at 11/12/2015 23:05:17 needed for thread 1
ORA-00289: suggestion : /home/data/arc2/arch_1_3_895618740.arc
ORA-00280: change 1150216 for thread 1 is in sequence #3
ORA-00278: log file '/app/oracle/oradata/jigi/redo02_a.log' no longer needed for this recovery
Specify log: {<RET>=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: '/app/oracle/oradata/jigi/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
Database altered.
SQL> select * from scott.tt660;
NO
----------
1
2
SQL>
- _allow_resetlogs_corruption=true
- 오픈 이후 full backup ( 권장 )
- 데이터 파일에 저장된 데이터 까지만 살릴수 있고 삭제된 리두로그에
있던 데이터는 복구 하지 못할 수도 있습니다. - Hidden Parameter를 사용하는 방법은 최후의 수단이며 성공 확률도 높지 않기 때문에
정말 최악의 경우에만 사용하시길 부탁드리며 이 방법은 실패하는 경우도많다는 것을
꼭 기억하시고 항상 백업에 힘써주시길 당부 드립니다.
참고 Resetlogs 와 _allow_resetlogs_corruption
- 데이터베이스가 Open 되기 위해서 운영 중인 Data File과 Redo Log File, Controlfile 의 Checkpoint 정보가 모두 동일해야 합니다. ( 불일치 불안전 복구 )
- 즉 a.dbf, b.dbf, c.dbf, system01.dbf 이렇게는 SCN이 같지만 Redo Log File과 Control File과 의 Checkpoint 정보가 다를 경우에 사용하는 것
- Data File끼리 정보가 다를 경우에는 RESETLOGS 옵션을 사용해도 Open X
- _allow_resetlogs_corruption=true ( 가능 )
- Open하는 경우 데이터파일 헤더 부분에 checkpoint 정보을 확인하는데, 옵션 사용시 생략 및 강제 Open
- _allow_resetlogs_corruption=true ( 가능 )
Case 9 - 전체 Redo Log File이 삭제된 경우 DB Open 상태( archive log mode 에서 작업 )
- Hidden Parameter를 삭제 및 Archive log mode로 변경
| Step 1. 현재 상태 확인 |
|---|
| {code:sql} |
SQL> startup mount
ORACLE instance started.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /home/data/arc2
Oldest online log sequence 1
Current log sequence 1
SQL>
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode
SQL> alter database open;
Database altered.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2217464 bytes
Variable Size 499124744 bytes
Database Buffers 281018368 bytes
Redo Buffers 2637824 bytes
Database mounted.
SQL>
SQL> alter database archivelog;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/data/arc2
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SQL>
SQL> show parameter pfile
NAME TYPE VALUE
---
--
spfile string /app/oracle/product/11g/dbs/sp
filejigi.ora
SQL>
SQL> alter database open;
Database altered.
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
--
-
--
-- ---
-
---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 1 YES INACTIVE 1150217 12-NOV-15 YES
1 /app/oracle/oradata/jigi/redo01_b.log 51200 1 YES INACTIVE 1150217 12-NOV-15 YES
2 /app/oracle/oradata/jigi/redo02_a.log 51200 2 NO CURRENT 1170834 13-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_b.log 51200 2 NO CURRENT 1170834 13-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_a.log 51200 0 YES UNUSED 0 YES
3 /app/oracle/oradata/jigi/redo03_b.log 51200 0 YES UNUSED 0 YES
6 rows selected.
SQL>
SQL> select status from v$instance;
STATUS
OPEN
SQL>
SQL> !rm /app/oracle/oradata/jigi/redo*.log
SQL> !ls /app/oracle/oradata/jigi/redo*.log
ls: cannot access /app/oracle/oradata/jigi/redo*.log: 그런 파일이나 디렉터리가 없습니다
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
^Calter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
--
-
--
-- ---
-
---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 4 NO CURRENT 1171515 13-NOV-15 NO
1 /app/oracle/oradata/jigi/redo01_b.log 51200 4 NO CURRENT 1171515 13-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_a.log 51200 2 NO INACTIVE 1170834 13-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_b.log 51200 2 NO INACTIVE 1170834 13-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_a.log 51200 3 NO INACTIVE 1171509 13-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_b.log 51200 3 NO INACTIVE 1171509 13-NOV-15 NO
6 rows selected.
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
--
-
--
-- ---
-
---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 4 NO ACTIVE 1171515 13-NOV-15 NO
1 /app/oracle/oradata/jigi/redo01_b.log 51200 4 NO ACTIVE 1171515 13-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_a.log 51200 2 NO INACTIVE 1170834 13-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_b.log 51200 2 NO INACTIVE 1170834 13-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_a.log 51200 5 NO CURRENT 1171590 13-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_b.log 51200 5 NO CURRENT 1171590 13-NOV-15 NO
6 rows selected.
SQL> alter database clear unarchived logfile group 2;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
--
-
--
-- ---
-
---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 4 NO INACTIVE 1171515 13-NOV-15 NO
1 /app/oracle/oradata/jigi/redo01_b.log 51200 4 NO INACTIVE 1171515 13-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_a.log 51200 6 NO CURRENT 1171641 13-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_b.log 51200 6 NO CURRENT 1171641 13-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_a.log 51200 5 NO ACTIVE 1171590 13-NOV-15 NO
3 /app/oracle/oradata/jigi/redo03_b.log 51200 5 NO ACTIVE 1171590 13-NOV-15 NO
6 rows selected.
SQL> alter system switch logfile;
^Calter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME ARC
--
-
--
-- ---
-
---
1 /app/oracle/oradata/jigi/redo01_a.log 51200 10 NO CURRENT 1171707 13-NOV-15 NO
1 /app/oracle/oradata/jigi/redo01_b.log 51200 10 NO CURRENT 1171707 13-NOV-15 NO
2 /app/oracle/oradata/jigi/redo02_a.log 51200 9 YES ACTIVE 1171704 13-NOV-15 YES
2 /app/oracle/oradata/jigi/redo02_b.log 51200 9 YES ACTIVE 1171704 13-NOV-15 YES
3 /app/oracle/oradata/jigi/redo03_a.log 51200 8 YES INACTIVE 1171701 13-NOV-15 YES
3 /app/oracle/oradata/jigi/redo03_b.log 51200 8 YES INACTIVE 1171701 13-NOV-15 YES
6 rows selected.
SQL>
| {code} |
Case 10 - 전체 Redo Log File이 삭제되는 경우 DB Close 상태 - 백업 있는 상태
- Shutdown immediate 상태로 종료가 되었을 경우에는 앞에서 확인한 장애 Case 6q번 경우와 동일하게 해결하면 됩니다.
- Shutdown abort로 비정상 종료된 경우라면 Case 7 - Current Redo log 삭제 - shutdown abort 종료 - 백업파일 있을의 경우와 복구 방법이 동일
Caset 11 - 전체 Redo log가 삭제된 경우 DB Close 상태 - 백업 없는 상태
- Case 8 - Current Redo log 삭제 - shutdown abort 종료 - 백업ㅍ일 없음의 경우와 복구 방법이 동일 합니다.