Chap.06 Log Miner 활용과 Redo log 장애 (by ljh1148) [2015.11.13]
06 Log Miner 활용과 Redo log 장애
6.1 Oracle log Miner - 8i
- Redo log file : 바이너리 파일
제약 사항
- 1. Log Miner는 8i 이후 버전에서만 작동을 합니다.
- 2. 분석 대상 인스턴스와 같은 플래폼에서 동작하고 같은 케릭터셋을 사용하는
db에서 생성된 Redo Log File만 분석할 수 있습니다. - 3. 분석 대상 인스턴스와 Redo log가 생성된 인스턴스는 DB_BLOCK_SIZE가 동일해야
합니다. 만약 다를 경우 Corruption이 발생했다는 내용이 기록됩니다.
-- 디폴트 : NO
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
*
1행에 오류:
ORA-28365: 전자 지갑이 열려 있지 않습니다.
SQL> alter system set encryption key identified by foobar;
시스템이 변경되었습니다.
SQL> -- DATABASE LEVAL
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
데이타베이스가 변경되었습니다.
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
YES
SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
데이타베이스가 변경되었습니다.
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
NO
SQL> -- TABLE LEVEL
SQL> ALTER TABLE SCOTT.EMP ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
테이블이 변경되었습니다.
SQL>
6.2 Oracle Log Miner - 9i New Feature
- 1) DDL 문장을 지원합니다. v$logmnr_contents의 operation 칼럼에서 조회가 가능합니다.
단 이 기능은 9i 이후에 만들어진 Redo Log File만 지원합니다. - 2) Dictionary file을 기존처럼 flat file에 생성할 수 있고 redo log 파일에도 저장 가능합니다.
단 이렇게 딕셔너리를 Redo Log File에 저장하는 기능을 사용하면 반드시 archive log mode여야만 합니다. - 3) 분석하려는 Redo Log File 에 block corruption이 발생할 경우 해당 Block을 건너뛰고
분석을 완료 합니다. 기존 8i에서는 분석 중 block corruption 이 발생할 경우
일단 종료되고 다시 시작해서 분석을 계속하였던 것이 skip 기능으로 변경되것입니다.- options => dbms_logmnr.skip_corruption
- 4) Committed_data_only 옵션이 추가 되어서 commit된 데이터만 조회가 가능합니다.
- 5) Index clustered와 관련된 DML도 분석됩니다. ( 8i X )
9i 에서 지원 되지 않는 기능
- Long / LOB data type
- Object Types
- Nested tables
- Object refs
- IOT
6.3 Oracle Log Miner - 10g New Feature
- 1) Shared Server 지원 - 기존에 9i까지는 Dedicated Server mode만 지원했지만
10g 버전부터 Shared server mode도 지원하게 되었습니다. - 2) Index Organized Table( IOT ) 지원
- 3) Long / LOB / NCLOB 지원
- 4) NO_ROWID_IN_STMT 지원
- sql_redo와 sql_undo 칼럼을 조회할 때 rowid를 나타내지 않도록 하여
다른 환경에서도 쉽게 사용할 수 있도록 지원합니다.
- sql_redo와 sql_undo 칼럼을 조회할 때 rowid를 나타내지 않도록 하여
- 5) Log Miner 세션에서 Redo Log File을 list에서 삭제할 수 있습니다.
6.4 Log Miner 실습하기 1
- drop table 장애가 발생했을 경우 Log Miner을 활용하여 어떻게 Redo Log File에서
해당 정보을 찾아내는지 실습해보겠습니다.
Step 1. 현재 상태 확인
SQL> @LOG
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE#
---------- ---------------------------------------- ---------- ---------- --- ---------------- -------------
1 D:\APP\LG\ORADATA\JHLEE68\REDO01.LOG 51200 256 NO INACTIVE 7814798
2 D:\APP\LG\ORADATA\JHLEE68\REDO02.LOG 51200 257 NO INACTIVE 7848117
3 D:\APP\LG\ORADATA\JHLEE68\REDO03.LOG 51200 258 NO CURRENT 7849319
SQL>
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
YES
Step 2. 새로운 테이블 생성 후 데이터 입력하고 삭제
SQL>
SQL> CREATE TABLE SCOTT.TEST1 ( NO NUMBER );
테이블이 생성되었습니다.
SQL> INSERT INTO SCOTT.TEST1 VALUES( 1 );
1 개의 행이 만들어졌습니다.
SQL>
SQL> COMMIT;
커밋이 완료되었습니다.
SQL> DROP TABLE SCOTT.TEST1 PURGE;
테이블이 삭제되었습니다.
SQL> @LOG
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE#
---------- ---------------------------------------- ---------- ---------- --- ---------------- -------------
1 D:\APP\LG\ORADATA\JHLEE68\REDO01.LOG 51200 256 NO INACTIVE 7814798
2 D:\APP\LG\ORADATA\JHLEE68\REDO02.LOG 51200 257 NO INACTIVE 7848117
3 D:\APP\LG\ORADATA\JHLEE68\REDO03.LOG 51200 258 NO CURRENT 7849319 <--
SQL>
Step 3. Log Miner 딕셔너리를 생성합니다.
- 반드시 분석 하고자 하는 DB 에서 생성
-- dictionary file ( 존재시 )
INSERT INTO EMP ( NAME, SALARY ) VALUES( 'John Doe', 50000 );
-- dictionary file ( 미 존재시 )
INSERT INTO Object#2581 ( col#1, col#2 ) VALUES( hextoraw( '4a6f686e20446f65'), hextoraw('c306' ) );
1. Online Catalog 사용
- Redo Log File이 생성된 Source Database에 접근이 가능한 경우
- 관련 table들에 칼럼 정의가 변경이 없을 경우
- exec dbms_logmnr.start_logmnr( options => dbms_logmnr.dict_from_online_catalog );
2. Redo Log File 자체에 저장하는 방법
- Redo Log File이 생성된 Source Database에 접근이 불가능할 경우
- 9i 이상에서 지원됩니다.
- DB는 Archive mode여야 합니다.
- 딕셔너리를 추출하는 동안 어떤한 DDL도 허용되지 않습니다.
- exec dbms_logmnr_d.build( options => dbms_logmnr_d.store_in_redo_logs );
3. Flat file에 저장하는 방법
- 이전 버전과 호환성을 보장해서 작업 시에 맣이 사용하는 방법입니다.
- exec dbms_logmnr_d.build( 'dict.dat','/home/oracle/logmnr/', dbms_logmnr_d.store_in_flat_file );
| 1) DB 전체를 종료한 후 parameter file 에 딕셔너리 파일의 위치를 아래와 같이 지정합니다. |
|---|
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string D:\APP\LG\PRODUCT\11.2.0\DBHOM
E_1\DATABASE\SPFILEJHLEE68.ORA
SQL> alter system set utl_file_dir="D:\APP\LG\ORADATA\JHLEE68" scope=spfile;
시스템이 변경되었습니다.
SQL>
SQL> show parameter utl_file_dir;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string
SQL>
|| 2) DB를 시작합니다.||
SQL> startup force;
ORACLE 인스턴스가 시작되었습니다.
Total System Global Area 2522038272 bytes
Fixed Size 2283864 bytes
Variable Size 620758696 bytes
Database Buffers 1879048192 bytes
Redo Buffers 19947520 bytes
데이터베이스가 마운트되었습니다.
ORA-28365: 전자 지갑이 열려 있지 않습니다.
SQL> show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string D:\APP\LG\ORADATA\JHLEE68
SQL>
SQL> alter system set encryption wallet open identified by foobar;
시스템이 변경되었습니다.
SQL> alter database open;
데이타베이스가 변경되었습니다.
|| 3) 아래와 같이 딕셔너리를 생성합니다.||
CREATE TABLE SCOTT.TEST1 ( NO NUMBER );
INSERT INTO SCOTT.TEST1 VALUES( 1 );
COMMIT;
SQL> -- 딕셔너리 생성
SQL> exec dbms_logmnr_d.build( dictionary_filename => 'dict.dat', dictionary_location => 'D:\APP\LG\ORADATA\JHLEE68');
PL/SQL 처리가 정상적으로 완료되었습니다.
|| 4) Log Miner에 분석할 로그를 추가합니다.||
SQL> -- 1 : 신규등록, 2 : 삭제, 3 : 추가등록
SQL> exec dbms_logmnr.add_logfile( 'D:\APP\LG\ORADATA\JHLEE68\REDO01.LOG', 1 );
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec dbms_logmnr.add_logfile( 'D:\APP\LG\ORADATA\JHLEE68\REDO02.LOG', 3 );
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec dbms_logmnr.add_logfile( 'D:\APP\LG\ORADATA\JHLEE68\REDO03.LOG', 3 );
PL/SQL 처리가 정상적으로 완료되었습니다.
|| 5) Log Miner를 시작해서 log를 분석한후 결과를 조회합니다.||
SQL> col username for a10
SQL> col operation for a10
SQL> col sql_redo for a50
SQL> alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS';
세션이 변경되었습니다.
SQL> SELECT TIMESTAMP, USERNAME, OPERATION, SEG_OWNER --, SQL_REDO
2 FROM V$LOGMNR_CONTENTS
3 WHERE 1 =1
4 AND SEG_NAME='TEST1'
5 --AND SEG_OWNER='SCOTT';
선택된 레코드가 없습니다.
SQL>
|| 6) 데이타가 없어서 전체 재실행||
20:03:46 SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE#
--
--
-- ---
-
1 D:\APP\LG\ORADATA\JHLEE68\REDO01.LOG 51200 271 NO CURRENT 8035468
2 D:\APP\LG\ORADATA\JHLEE68\REDO02.LOG 51200 269 NO INACTIVE 8010248
3 D:\APP\LG\ORADATA\JHLEE68\REDO03.LOG 51200 270 NO INACTIVE 8012422
20:03:48 SQL>
20:03:49 SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
YES
20:03:52 SQL> CREATE TABLE SCOTT.TEST1 ( NO NUMBER );
테이블이 생성되었습니다.
20:04:06 SQL>
20:04:06 SQL> INSERT INTO SCOTT.TEST1 VALUES( 1 );
1 개의 행이 만들어졌습니다.
20:04:07 SQL>
20:04:07 SQL> COMMIT;
커밋이 완료되었습니다.
20:04:08 SQL> DROP TABLE SCOTT.TEST1 PURGE;
테이블이 삭제되었습니다.
20:04:15 SQL>
20:04:31 SQL> exec dbms_logmnr_d.build( dictionary_filename => 'dict.dat', dictionary_location => 'D:\APP\LG\ORADATA\JHLEE68');
PL/SQL 처리가 정상적으로 완료되었습니다.
20:05:09 SQL>
20:05:09 SQL> exec dbms_logmnr.add_logfile( 'D:\APP\LG\ORADATA\JHLEE68\REDO01.LOG', 1 );
PL/SQL 처리가 정상적으로 완료되었습니다.
20:05:09 SQL>
20:05:09 SQL> exec dbms_logmnr.add_logfile( 'D:\APP\LG\ORADATA\JHLEE68\REDO02.LOG', 3 );
PL/SQL 처리가 정상적으로 완료되었습니다.
20:05:09 SQL>
20:05:09 SQL>
20:05:09 SQL> exec dbms_logmnr.add_logfile( 'D:\APP\LG\ORADATA\JHLEE68\REDO03.LOG', 3 );
PL/SQL 처리가 정상적으로 완료되었습니다.
20:05:09 SQL> begin
20:05:14 2 dbms_logmnr.start_logmnr( dictfilename => 'D:\APP\LG\ORADATA\JHLEE68\dict.dat'
20:05:14 3 , options => dbms_logmnr.ddl_dict_tracking + dbms_logmnr.committed_data_only
20:05:14 4 );
20:05:14 5 end;
20:05:15 6 /
PL/SQL 처리가 정상적으로 완료되었습니다.
20:06:32 SQL> SELECT TIMESTAMP, USERNAME, OPERATION, SEG_OWNER --, SQL_REDO
20:06:56 2 FROM V$LOGMNR_CONTENTS
20:06:56 3 WHERE 1 =1
20:06:56 4 AND SEG_NAME='TEST1';
선택된 레코드가 없습니다.
20:06:59 SQL> SELECT TIMESTAMP, USERNAME, OPERATION, SEG_OWNER --, SQL_REDO
20:07:28 2 FROM V$LOGMNR_CONTENTS
20:07:28 3 WHERE 1 =1
20:07:28 4 --AND SEG_NAME='TEST1'
20:07:28 5 AND SEG_OWNER='SCOTT'
20:07:28 6 ;
선택된 레코드가 없습니다.
20:07:30 SQL> @LOG
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE#
--
--
-- ---
-
1 D:\APP\LG\ORADATA\JHLEE68\REDO01.LOG 51200 274 NO CURRENT 8061772
2 D:\APP\LG\ORADATA\JHLEE68\REDO02.LOG 51200 272 NO ACTIVE 8058290
3 D:\APP\LG\ORADATA\JHLEE68\REDO03.LOG 51200 273 NO ACTIVE 8060270
20:07:38 SQL>
h3. 6.5 Log Miner 실습하기 2 - update table 시간 확인하기
{code:sql}|| 1. 현재 상태 조회하기 ||
20:12:01 SQL> @log
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE#
---------- ---------------------------------------- ---------- ---------- --- ---------------- -------------
1 D:\APP\LG\ORADATA\JHLEE68\REDO01.LOG 51200 274 NO CURRENT 8061772
2 D:\APP\LG\ORADATA\JHLEE68\REDO02.LOG 51200 272 NO INACTIVE 8058290
3 D:\APP\LG\ORADATA\JHLEE68\REDO03.LOG 51200 273 NO INACTIVE 8060270
20:12:08 SQL>
20:12:18 SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
YES
20:12:19 SQL>
|| 2. 신규 테이블 생성 후 데이터 입력하고 update 합니다. ||
20:18:55 SQL> CREATE TABLE SCOTT.TEST2 ( NO NUMBER, NAME VARCHAR2(10) );
테이블이 생성되었습니다.
20:18:56 SQL>
20:18:56 SQL> INSERT INTO SCOTT.TEST2 VALUES( 1, 'AAA' );
1 개의 행이 만들어졌습니다.
20:18:56 SQL>
20:18:56 SQL> INSERT INTO SCOTT.TEST2 VALUES( 2, 'BBB' );
1 개의 행이 만들어졌습니다.
20:18:56 SQL>
20:18:56 SQL> INSERT INTO SCOTT.TEST2 VALUES( 3, 'CCC' );
1 개의 행이 만들어졌습니다.
20:18:57 SQL>
20:20:00 SQL> SELECT * FROM SCOTT.TEST2;
NO NAME
--
--
1 AAA
2 BBB
3 CCC
20:20:01 SQL> UPDATE SCOTT.TEST2 SET NAME ='DDD';
3 행이 갱신되었습니다.
20:20:34 SQL>
20:20:39 SQL> SELECT * FROM SCOTT.TEST2;
NO NAME
--
--
1 DDD
2 DDD
3 DDD
20:20:39 SQL> COMMIT;
커밋이 완료되었습니다.
20:20:42 SQL>
20:20:51 SQL> SHOW PARAMETER UTL
NAME TYPE VALUE
---
--
create_stored_outlines string
utl_file_dir string D:\APP\LG\ORADATA\JHLEE68
20:20:57 SQL>
20:22:43 SQL>
20:23:00 SQL>
20:23:00 SQL> exec dbms_logmnr_d.build( dictionary_filename => 'dict2.dat', dictionary_location => 'D:\APP\LG\ORADATA\JHLEE68');
PL/SQL 처리가 정상적으로 완료되었습니다.
20:23:37 SQL> @LOG
GROUP# MEMBER MB SEQ ARC STATUS FIRST_CHANGE#
--
--
-- ---
-
1 D:\APP\LG\ORADATA\JHLEE68\REDO01.LOG 51200 274 NO CURRENT 8061772
2 D:\APP\LG\ORADATA\JHLEE68\REDO02.LOG 51200 272 NO INACTIVE 8058290
3 D:\APP\LG\ORADATA\JHLEE68\REDO03.LOG 51200 273 NO INACTIVE 8060270
20:24:23 SQL> exec dbms_logmnr.add_logfile( 'D:\APP\LG\ORADATA\JHLEE68\REDO01.LOG', 1 );
PL/SQL 처리가 정상적으로 완료되었습니다.
20:24:34 SQL>
20:24:34 SQL> exec dbms_logmnr.add_logfile( 'D:\APP\LG\ORADATA\JHLEE68\REDO02.LOG', 3 );
PL/SQL 처리가 정상적으로 완료되었습니다.
20:24:34 SQL>
20:24:34 SQL>
20:24:34 SQL> exec dbms_logmnr.add_logfile( 'D:\APP\LG\ORADATA\JHLEE68\REDO03.LOG', 3 );
PL/SQL 처리가 정상적으로 완료되었습니다.
20:24:36 SQL> begin
20:25:23 2 dbms_logmnr.start_logmnr( dictfilename => 'D:\APP\LG\ORADATA\JHLEE68\dict2.dat'
20:25:23 3 );
20:25:23 4 end;
20:25:23 5 /
PL/SQL 처리가 정상적으로 완료되었습니다.
20:25:32 SQL> SELECT TIMESTAMP, USERNAME, OPERATION, SEG_OWNER --, SQL_REDO
20:26:36 2 FROM V$LOGMNR_CONTENTS
20:26:36 3 WHERE 1 =1
20:26:36 4 AND SEG_NAME='TEST2'
20:26:36 5 -- AND SEG_OWNER='SCOTT'
20:26:36 6 ;
TIMESTAM USERNAME OPERATI SEG_OWN SQL_REDO
--
---
---
--
15/11/05 SYS DDL SCOTT CREATE TABLE SCOTT.TEST2 ( NO NUMBER, NAME VARCHAR2(10) );
15/11/05 SYS INSERT SCOTT insert into "SCOTT"."TEST2"("NO","NAME") values ('1','AAA');
15/11/05 SYS INSERT SCOTT insert into "SCOTT"."TEST2"("NO","NAME") values ('2','BBB');
15/11/05 SYS INSERT SCOTT insert into "SCOTT"."TEST2"("NO","NAME") values ('3','CCC');
15/11/05 SYS UPDATE SCOTT update "SCOTT"."TEST2" set "NAME" = 'DDD' where "NAME" = 'AAA' and ROW
ID = 'AAAVfkAAEAAAAKkAAA';
15/11/05 SYS UPDATE SCOTT update "SCOTT"."TEST2" set "NAME" = 'DDD' where "NAME" = 'BBB' and ROW
ID = 'AAAVfkAAEAAAAKkAAB';
15/11/05 SYS UPDATE SCOTT update "SCOTT"."TEST2" set "NAME" = 'DDD' where "NAME" = 'CCC' and ROW
ID = 'AAAVfkAAEAAAAKkAAC';
h3. 6.6 Supplemental logging
h3. 1) 개요
* 미설정 : 변경 컬럼의 ROWID와 변견 컬럼만 기록
* 실 정 : 변경 컬럼의 모든 레코드가 기록
** 장점
*** 정합성 검증
*** 정장하고 싶은 칼럼을 수동으로 지정할 수 있다( Supplemental log group )
h3. 2) Supplemental logging의 종류
* table level : 특정 테이블만 이 기능을 활성화
** 장점 : 부하 적고, 성능도 괜찮다
** 단점 : 다소 불편 ( 건건이 )
* database level
** 단점 : Redo log의 발생량이 아주 많아진다는 것입니다.
*** minmal logging ( 권장 )
**** Log Miner가 sql을 재생성하는데 필요한 최소한의 정보만을 기록
*** identification key logging
**** 모든 내역을 기록으로 redo log 발생량이 많이짐
h3. 3) Supplemental logging group
* supplemental logging을 할 때 변경이 일어나는 칼럼 외에 추가로 저장할 다른 컬럼들을 지정하는 기능
** conditional supplemental log groups ( 조건적 )
*** supplemental log group에 설정된 컬럼이 변경될 때만 before image가 기록되는 것을 말함
** unconditional supplemental log groups ( 비조건적 ) == always log group
*** 어떤 컬럼이 변경되는 상관없이 기록되는 것을 의미합니다.
h3. 6.7 Case 별로 보는 Redo Log File 장애와 대처 방법
h3. 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 상태 - 백업 없는 상태
h3. Redo Log File 장애 처리 순서도
!2015-11-07_01.PNG!
h3. 1) 현재 상태 확인
{code:sql}
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.
{CODE}
h3. Case 1 - 1개의 member가 삭제되는 장애가 발생하는 경우
{CODE:SQL}
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}
|| 장애가 확인된 멤버는 삭제한 후 다시 생성 ||
{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}
h3. Case 2 - archive 완료된 그룹이 지워진 후 DB Close 상태
* 에러 코드 : ORA-00313:open failed for members of log group...
* Current 가 아닌 하나의 그룹이 삭제된 후 DB가 종료된 경우
** 포인트 : Archive 완료 여부
* 장애 이유 : 오라클이 시작 될 때 Control file을 읽어서 지정된 Redo Log File을
찾으려고 하는데 실제 파일이 삭제되어 없기 때문에 발생한 것입니다.
** 해결 방안 : Control file에서 해당 그룹의 명단을 삭제하면 간단히 해결
{code: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> !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. 현재 상태 확인 |
|---|
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.
| Step 2. 그룹 삭제 ( 현재 archive 완료된 1번 그룹을 삭제합니다. ) |
|---|
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 순서 )
| Step 3. Alert log 확인 후 원인 파악 하기 |
|---|
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'
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
...
- 1번 그룹이 계속 장애가 발생
| Step 4. 장애 해결 |
|---|
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>
- clear unarchived : drop 하고 신규로 add 해주는 역할
- 온라인 중에 문제 해결 가능
- 작업 이후 반드시 full backup 수행해야 합니다.
- archive log file이 중간에 비어버리기 때문에 그냥 넘어갔다가는 향후에 장애가 발생해
복구가 안되니 반드시 이 작업 후에는 전체 받힌 백업을 받아야 합니다.- 11g는 여기까지하면 문제 해결
- 10버전에서는 log switch가 발생하면 다시 archive hang이 발생합니다. ( page. 179 )
- clear 작업 이후 drop 이후 add ( 10g )
- archive log file이 중간에 비어버리기 때문에 그냥 넘어갔다가는 향후에 장애가 발생해
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 로그인 복구 |
|---|
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>
| Step 3. 재시작시키면 에러가 발생하면서 중단됩니다. |
|---|
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
| Step 4. 다른 터미널을 하나 더 열어서 Alert log에서 장애를 확인합니다. |
|---|
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
..
| Case 2의 경우에 해결했던 명령으로 시도 |
|---|
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.
- 파일이 자동으로 생성
- 이 명령으로 사용한 후에는 반드시 전체 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. 현재 상태 확인 |
|---|
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>
| Step 2. 데이터 입력 후 장애 발생 |
|---|
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 /app/oracle/oradata/jigi/redo03_b.log 51200 30 NO CURRENT 1096228 10-NOV-15 NO
6 rows selected.
SQL>
- 3번 그룹에 저장
| Step 3. Current 상태인 2번 그룹을 OS 명령어로 삭제한 후 재부팅합니다. |
|---|
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
| Step 4. Alert log 의 에러 메세지를 확인 합니다. |
|---|
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
...
| Step 5. 복구 후 Open 합니다. |
|---|
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 옵션을 쓰며 됩니다. ( 설명 뒤장 )
- 문제는 RESETLOGS 옵션은 불완전 복구를 수행했을 경우만 쓸 수 있다는 겂입니다.
- Control File에는 명단이 있으나 실제 파일이 없는 Redo log를 재생성시키는 RESETLOGS 옵션을 사용하는 것입니다.
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
page. 192 그림
- SCN 106번 작업이 Current Redo Log File에 저장되어 있음
- Full export + archive log + redo log를 Current가 지워지기 직전까지인 SCN 105번 까지 복구
| Step 0. DB 종료 후 전체 데이터베이스를 full backup을 수행하세요 |
|---|
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 후 현재 상태 확인 |
|---|
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
| Step 2. Current group 삭제 후 shutdown abort 시킴. |
|---|
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
| Step 3. 복구 하기 |
|---|
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
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 로 변경하기 |
|---|
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>
| Step 2. 데이터 입력 |
|---|
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
| Step 4. 복구 |
|---|
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>
- 체크 포인트가 발생했기 때문에 데이터가 데이터파일에 모두 저장되었기 때문입니다.
상황 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 정보가 모두 동일해야 합니다. ( 불일치 불안전 복구 )