alter database clear unarchived logfile group N;
- 장애가 발생한 redo log file 을 재생성하는데 사용된다.
- archive log mode 에서 archiving 이 진행되지 않은 redo log file 은 alter database drop logfile group N; 명령으로 삭제할 수 없기 때문에 redo log file 을 재생성해야 된다.
- Current redo log file 은 다음 명령어를 사용할 수 없다. (DB Open 이면 log switch 발생 후 적용)
- DB Open 상태에서 위의 명령을 사용하는 경우 재생성된 redo log file 은 hang 현상을 해제시킬 수 있을 뿐, redo log file로 사용될 수 없기 때문에 drop logfile 명령으로 삭제 후 다시 add logfile 명령으로 다시 만들어 줘야한다.
Redo log file의 장애 처리 유형
case 1. 대기중인 그룹의 멤버가 1개만 장애 발생할 경우
case 2. 대기중인 그룹 중 archive가 완료된 그룹이 지워진 경우
case 3. 대기중인 그룹 중 archive가 완료 안된 그룹이 지워진 경우 - DB Open
case 4. 대기중인 그룹 중 archive가 완료 안된 그룹이 지워진 경우 - DB Close
case 5. Current 그룹이 삭제된 경우 - DB Open
case 6. Current 그룹이 삭제된 경우 - shutdown immediate 명령으로 종료됨
case 7. Current 그룹이 삭제된 경우 - shutdown abort 명령으로 종료됨 : backup 있음
case 8. Current 그룹이 삭제된 경우 - shutdown abort 명령으로 종료됨 : no archive log mode
case 9. 전체 redo log가 삭제된 경우 - DB Open 상태
case 10. 전체 redo log가 삭제된 경우 - DB Close 상태 : backup 있음
case 11. 전체 redo log가 삭제된 경우 - DB Close 상태 : backup 없음
SYS>set line 200
SYS>col group# for 999
SYS>col member for a50
SYS>col sequence# for 999
SYS>select a.group#, a.member, b.bytes/1024/1024 mb, b.archived, b.sequence# "SEQ#", b.status
2 from v$logfile a, v$log b
3 where a.group#=b.group#
4 order by 1;
GROUP# MEMBER MB ARC SEQ# STATUS
------ -------------------------------------------------- ---------- --- ---------- ----------------
1 /app/oracle/oradata/testdb/redo01_a.log 5 YES 6 ACTIVE
1 /app/oracle/oradata/testdb/redo01_b.log 5 YES 6 ACTIVE
2 /app/oracle/oradata/testdb/redo02_b.log 5 YES 7 ACTIVE
2 /app/oracle/oradata/testdb/redo02_a.log 5 YES 7 ACTIVE
3 /app/oracle/oradata/testdb/redo03_b.log 5 NO 8 CURRENT
3 /app/oracle/oradata/testdb/redo03_a.log 5 NO 8 CURRENT
6 rows selected.
-> 멤버 하나가 삭제되는 장애 발생
SYS>!rm -f /app/oracle/oradata/testdb/redo01_a.log
SYS>!ls /app/oracle/oradata/testdb/redo01_a.log
ls: /app/oracle/oradata/testdb/redo01_a.log: ���� �������� ���������� ����
SYS>alter system switch logfile; <- 로그 스위치를 일으켜도 이상 없음
System altered.
SYS>/
System altered.
SYS>/
System altered.
SYS>/
System altered.
SYS>select a.group#, a.member, b.bytes/1024/1024 mb, b.archived, b.sequence# "SEQ#", b.status
2 from v$logfile a, v$log b
3 where a.group#=b.group#
4 order by 1;
GROUP# MEMBER MB ARC SEQ# STATUS
------ -------------------------------------------------- ---------- --- ---------- ----------------
1 /app/oracle/oradata/testdb/redo01_a.log 5 NO 12 CURRENT
1 /app/oracle/oradata/testdb/redo01_b.log 5 NO 12 CURRENT
2 /app/oracle/oradata/testdb/redo02_b.log 5 YES 10 INACTIVE
2 /app/oracle/oradata/testdb/redo02_a.log 5 YES 10 INACTIVE
3 /app/oracle/oradata/testdb/redo03_b.log 5 YES 11 INACTIVE
3 /app/oracle/oradata/testdb/redo03_a.log 5 YES 11 INACTIVE
6 rows selected.
SYS>!vi /app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log
-> alert log 확인
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /app/oracle/diag/rdbms/testdb/testdb/trace/testdb_arc0_3873.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/app/oracle/oradata/testdb/redo01_a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Tue Jul 10 09:24:59 2012
Errors in file /app/oracle/diag/rdbms/testdb/testdb/trace/testdb_m000_3997.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/app/oracle/oradata/testdb/redo01_a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Archived Log entry 9 added for thread 1 sequence 9 ID 0x98f132cd dest 1:
SYS>alter system switch logfile;
System altered.
-> 장애 발생한 멤버를 삭제 후 생성한다.
SYS>alter database drop logfile member '/app/oracle/oradata/testdb/redo01_a.log';
Database altered.
SYS>alter database add logfile member '/app/oracle/oradata/testdb/redo01_a.log' to group 1;
Database altered.
SYS>select a.group#, a.member, b.bytes/1024/1024 mb, b.archived, b.sequence# "SEQ#", b.status
2 from v$logfile a, v$log b
3 where a.group#=b.group#
4 order by 1;
GROUP# MEMBER MB ARC SEQ# STATUS
------ -------------------------------------------------- ---------- --- ---------- ----------------
1 /app/oracle/oradata/testdb/redo01_a.log 5 YES 12 INACTIVE
1 /app/oracle/oradata/testdb/redo01_b.log 5 YES 12 INACTIVE
2 /app/oracle/oradata/testdb/redo02_b.log 5 NO 13 CURRENT
2 /app/oracle/oradata/testdb/redo02_a.log 5 NO 13 CURRENT
3 /app/oracle/oradata/testdb/redo03_b.log 5 YES 11 INACTIVE
3 /app/oracle/oradata/testdb/redo03_a.log 5 YES 11 INACTIVE
6 rows selected.
case 2. 대기중인 그룹 중 archive 가 완료된 그룹이 지워진 경우
삭제된 그룹이 삭제 되기 전에 다른 곳으로 내용을 Archive 해두었기 때문에 별다른 문제가 없다. 때문에 데이터를 잃어버리는 문제는 발생하지 않고 단지 redo log file 을 만들어주면 된다.
SYS>@redo
GROUP# MEMBER MB ARCHIVED SEQ# STATUS
------ -------------------------------------------------- ----- ---------- ---------- ----------
1 /app/oracle/oradata/testdb/redo01_a.log 5 NO 15 CURRENT
1 /app/oracle/oradata/testdb/redo01_b.log 5 NO 15 CURRENT
2 /app/oracle/oradata/testdb/redo02_a.log 5 YES 13 INACTIVE
2 /app/oracle/oradata/testdb/redo02_b.log 5 YES 13 INACTIVE
3 /app/oracle/oradata/testdb/redo03_a.log 5 YES 14 INACTIVE
3 /app/oracle/oradata/testdb/redo03_b.log 5 YES 14 INACTIVE
6 rows selected.
-> 대기중인 그룹이 삭제되는 장애 발생
SYS>!rm -f /app/oracle/oradata/testdb/redo02*
SYS>!ls -f /app/oracle/oradata/testdb/redo02*
ls: /app/oracle/oradata/testdb/redo02*: ���� �������� ���������� ����
-> redo log file 삭제 후 비정상 종료
SYS>shutdown abort;
ORACLE instance shut down.
SYS>startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1344616 bytes
Variable Size 260049816 bytes
Database Buffers 155189248 bytes
Redo Buffers 6086656 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 4224
Session ID: 1 Serial number: 5
SYS>!vi /app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log
ORA-00312: online log 2 thread 1: '/app/oracle/oradata/testdb/redo02_a.log'
ORA-00312: online log 2 thread 1: '/app/oracle/oradata/testdb/redo02_b.log'
SYS>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning option
[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jul 10 09:35:24 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SYS>startup mount
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1344616 bytes
Variable Size 260049816 bytes
Database Buffers 155189248 bytes
Redo Buffers 6086656 bytes
Database mounted.
SYS>@redo
GROUP# MEMBER MB ARCHIVED SEQ# STATUS
------ -------------------------------------------------- ----- ---------- ---------- ----------
1 /app/oracle/oradata/testdb/redo01_a.log 5 NO 15 CURRENT
1 /app/oracle/oradata/testdb/redo01_b.log 5 NO 15 CURRENT
2 /app/oracle/oradata/testdb/redo02_a.log 5 YES 13 INACTIVE
2 /app/oracle/oradata/testdb/redo02_b.log 5 YES 13 INACTIVE
3 /app/oracle/oradata/testdb/redo03_a.log 5 YES 14 INACTIVE
3 /app/oracle/oradata/testdb/redo03_b.log 5 YES 14 INACTIVE
6 rows selected.
-> 삭제된 redo log file 이 archiving 된 상태이기 때문에 drop logfile 명령어로 그룹을 삭제한다.
SYS>alter database drop logfile group 2;
Database altered.
SYS>@redo
GROUP# MEMBER MB ARCHIVED SEQ# STATUS
------ -------------------------------------------------- ----- ---------- ---------- ----------
1 /app/oracle/oradata/testdb/redo01_a.log 5 NO 15 CURRENT
1 /app/oracle/oradata/testdb/redo01_b.log 5 NO 15 CURRENT
3 /app/oracle/oradata/testdb/redo03_a.log 5 YES 14 INACTIVE
3 /app/oracle/oradata/testdb/redo03_b.log 5 YES 14 INACTIVE
-> 그룹 2 생성
SYS>alter database add logfile group 2
2 ('/app/oracle/oradata/testdb/redo02_a.log',
3 '/app/oracle/oradata/testdb/redo02_b.log') size 5m;
Database altered.
SYS>@redo
GROUP# MEMBER MB ARCHIVED SEQ# STATUS
------ -------------------------------------------------- ----- ---------- ---------- ----------
1 /app/oracle/oradata/testdb/redo01_a.log 5 NO 15 CURRENT
1 /app/oracle/oradata/testdb/redo01_b.log 5 NO 15 CURRENT
2 /app/oracle/oradata/testdb/redo02_a.log 5 YES 0 UNUSED
2 /app/oracle/oradata/testdb/redo02_b.log 5 YES 0 UNUSED
3 /app/oracle/oradata/testdb/redo03_a.log 5 YES 14 INACTIVE
3 /app/oracle/oradata/testdb/redo03_b.log 5 YES 14 INACTIVE
6 rows selected.
SYS>alter database open;
Database altered.
case 3. 대기중인 그룹중 archive 가 완료 안된 그룹이 지워진 경우 - DB Open 상태
서버 운영 중 특정 그룹이 지워졌는데 계속 log switch 가 발생하게 되면 이런 장애가 발생하게 된다. 이럴 경우 archive Hang 현상이 발생하여 급기야 비정상 종료까지 되므로 신속하게 대처해야 한다. 삭제된 log file 은 archiving 이 진행될 수 없기 모든 log file 그룹의 archiving 이 진행되지 않는다.
SYS>@redo
GROUP# MEMBER MB ARCHIVED SEQ# STATUS
------ -------------------------------------------------- ----- ---------- ---------- ----------
1 /app/oracle/oradata/testdb/redo01_a.log 5 YES 15 INACTIVE
1 /app/oracle/oradata/testdb/redo01_b.log 5 YES 15 INACTIVE
2 /app/oracle/oradata/testdb/redo02_a.log 5 NO 16 CURRENT
2 /app/oracle/oradata/testdb/redo02_b.log 5 NO 16 CURRENT
3 /app/oracle/oradata/testdb/redo03_a.log 5 YES 14 INACTIVE
3 /app/oracle/oradata/testdb/redo03_b.log 5 YES 14 INACTIVE
6 rows selected.
-> 대기중이 redo log group 1번을 모두 삭제 하는 장애 발생
SYS>!rm -f /app/oracle/oradata/testdb/redo01*
SYS>!ls /app/oracle/oradata/testdb/redo01*
ls: /app/oracle/oradata/testdb/redo01*: ���� �������� ���������� ����
SYS>alter system switch logfile;
System altered.
SYS>/
System altered.
SYS>/
System altered.
SYS>/
System altered.
-> group 1 번 file 이 모두 삭제되어 log switch가 계속 일어나면 archiving 이 진행되지 않아 Hang 이 발생한다.
SYS>/
alter system switch logfile Ctrl+C
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SYS>@redo
GROUP# MEMBER MB ARCHIVED SEQ# STATUS
------ -------------------------------------------------- ----- ---------- ---------- ----------
1 /app/oracle/oradata/testdb/redo01_a.log 5 NO 18 INACTIVE
1 /app/oracle/oradata/testdb/redo01_b.log 5 NO 18 INACTIVE
2 /app/oracle/oradata/testdb/redo02_a.log 5 NO 19 INACTIVE
2 /app/oracle/oradata/testdb/redo02_b.log 5 NO 19 INACTIVE
3 /app/oracle/oradata/testdb/redo03_a.log 5 NO 20 CURRENT
3 /app/oracle/oradata/testdb/redo03_b.log 5 NO 20 CURRENT
6 rows selected.
-> sequence 번호가 group 1 번이 가장 낮은 걸로 보아 group 1 에 의해서 archiving hang 이 발생 되었다고 볼 수 있다.
SYS>!vi /app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /app/oracle/diag/rdbms/testdb/testdb/trace/testdb_arc0_3873.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/app/oracle/oradata/testdb/redo01_a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Tue Jul 10 09:24:59 2012
Errors in file /app/oracle/diag/rdbms/testdb/testdb/trace/testdb_m000_3997.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/app/oracle/oradata/testdb/redo01_a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Archived Log entry 9 added for thread 1 sequence 9 ID 0x98f132cd dest 1:
-> archiving Hang 을 해제하면서 log file을 재생성 시킨다.
SYS>alter database clear unarchived logfile group 1;
Database altered.
SYS>alter system switch logfile;
System altered.
SYS>/
System altered.
SYS>/
System altered.
case 4. 대기중인 그룹중 archive 가 완료 안된 그룹이 지워진 경우 - DB Close 상태
archiving 이 완료 안된 group 이 삭제되고 종료 되는 경우 startup 과정에서 삭제된 redo log file 을 찾을수 없어서 에러가 발생한다. 삭제된 redo log file 이전 시퀀스의 redo log file 정보까지는 복구 가능하다. archive log file 이 중간에 비어버리기 때문에 복구한 후 전체 백업을 받아야 한다.
SYS>@redo
GROUP# MEMBER MB ARCHIVED SEQ# STATUS
------ -------------------------------------------------- ----- ---------- ---------- ----------
1 /app/oracle/oradata/testdb/redo01_a.log 5 YES 39 ACTIVE
1 /app/oracle/oradata/testdb/redo01_b.log 5 YES 39 ACTIVE
2 /app/oracle/oradata/testdb/redo02_a.log 5 NO 40 CURRENT
2 /app/oracle/oradata/testdb/redo02_b.log 5 NO 40 CURRENT
3 /app/oracle/oradata/testdb/redo03_a.log 5 YES 38 INACTIVE
3 /app/oracle/oradata/testdb/redo03_b.log 5 YES 38 INACTIVE
6 rows selected.
SYS>!rm /app/oracle/oradata/testdb/redo01*
SYS>!ls /app/oracle/oradata/testdb/redo01*
ls: /app/oracle/oradata/testdb/redo01*: ���� �������� ���������� ����
SYS>alter system switch logfile;
System altered.
SYS>/
System altered.
SYS>/
System altered.
SYS>/
System altered.
SYS>/ <- archive hang
새로운 터미널을 연 후
[oracle@oracle ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jul 10 10:23:56 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.
기존 터미널에서
SYS>/
alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SYS>shutdown abort;
ORACLE instance shut down.
SYS>startup mount;
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1344616 bytes
Variable Size 260049816 bytes
Database Buffers 155189248 bytes
Redo Buffers 6086656 bytes
Database mounted.
SYS>@redo
GROUP# MEMBER MB ARCHIVED SEQ# STATUS
------ -------------------------------------------------- ----- ---------- ---------- ----------
1 /app/oracle/oradata/testdb/redo01_a.log 5 NO 42 INACTIVE
1 /app/oracle/oradata/testdb/redo01_b.log 5 NO 42 INACTIVE
2 /app/oracle/oradata/testdb/redo02_a.log 5 NO 43 INACTIVE
2 /app/oracle/oradata/testdb/redo02_b.log 5 NO 43 INACTIVE
3 /app/oracle/oradata/testdb/redo03_a.log 5 NO 44 CURRENT
3 /app/oracle/oradata/testdb/redo03_b.log 5 NO 44 CURRENT
6 rows selected.
SYS>alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-00350: log 1 of instance testdb (thread 1) needs to be archived
ORA-00312: online log 1 thread 1: '/app/oracle/oradata/testdb/redo01_b.log'
ORA-00312: online log 1 thread 1: '/app/oracle/oradata/testdb/redo01_a.log'
SYS>alter database clear unarchived logfile group 1;
Database altered.
SYS>@redo
GROUP# MEMBER MB ARCHIVED SEQ# STATUS
------ -------------------------------------------------- ----- ---------- ---------- ----------
1 /app/oracle/oradata/testdb/redo01_a.log 5 YES 0 UNUSED
1 /app/oracle/oradata/testdb/redo01_b.log 5 YES 0 UNUSED
2 /app/oracle/oradata/testdb/redo02_a.log 5 NO 43 INACTIVE
2 /app/oracle/oradata/testdb/redo02_b.log 5 NO 43 INACTIVE
3 /app/oracle/oradata/testdb/redo03_a.log 5 NO 44 CURRENT
3 /app/oracle/oradata/testdb/redo03_b.log 5 NO 44 CURRENT
6 rows selected.
SYS>alter database open;
Database altered.
case 5. Current 그룹이 삭제된 경우 - DB Open 상태
SYS@redo
GROUP# MEMBER MB ARCHIVED SEQ# STATUS
------ -------------------------------------------------- ----- ---------- ---------- ----------
1 /app/oracle/oradata/testdb/redo01_a.log 5 YES 45 INACTIVE
1 /app/oracle/oradata/testdb/redo01_b.log 5 YES 45 INACTIVE
2 /app/oracle/oradata/testdb/redo02_a.log 5 YES 46 INACTIVE
2 /app/oracle/oradata/testdb/redo02_b.log 5 YES 46 INACTIVE
3 /app/oracle/oradata/testdb/redo03_a.log 5 NO 47 CURRENT
3 /app/oracle/oradata/testdb/redo03_b.log 5 NO 47 CURRENT
6 rows selected.
SYS>!rm -f /app/oracle/oradata/testdb/redo03*
SYS>!ls /app/oracle/oradata/testdb/redo03*
ls: /app/oracle/oradata/testdb/redo03*: ���� �������� ���������� ����
SYS>alter system switch logfile;
System altered.
SYS>/
System altered.
SYS>/
alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SYS>@redo
GROUP# MEMBER MB ARCHIVED SEQ# STATUS
------ -------------------------------------------------- ----- ---------- ---------- ----------
1 /app/oracle/oradata/testdb/redo01_a.log 5 NO 48 INACTIVE
1 /app/oracle/oradata/testdb/redo01_b.log 5 NO 48 INACTIVE
2 /app/oracle/oradata/testdb/redo02_a.log 5 NO 49 CURRENT
2 /app/oracle/oradata/testdb/redo02_b.log 5 NO 49 CURRENT
3 /app/oracle/oradata/testdb/redo03_a.log 5 NO 47 INACTIVE
3 /app/oracle/oradata/testdb/redo03_b.log 5 NO 47 INACTIVE
6 rows selected.
SYS>alter database clear unarchived logfile group 3;
Database altered.
SYS>alter system switch logfile;
System altered.
SYS>/
System altered.
SYS>/
System altered.
SYS>/
'Oracle > admin' 카테고리의 다른 글
| Keep Buffer 대상 선정 SQL (0) | 2016.06.13 |
|---|---|
| 11gR2 RAC 권장 파라미터 정리 (0) | 2016.06.03 |
| Oracle User Password Policy Configuration (0) | 2016.05.26 |
| Oracle CPU Patch for Linux (0) | 2016.05.11 |
| MOVE vs SHRINK Commands (0) | 2016.05.02 |