Archive Hang 발생 시 해결 방법

* Archive File이 쌓이는 Filesystem의 Full이나 삭제 시에도 발생할 수 있으며, 거의 동일하게 해결 *

 

** 요약 **

1. Log Archive Dest 확인

2. Archive Hang 발생

3. Log Archive Dest_2 추가

4. 기존 Log Archive Dest_1에 Archiving 정지

5. 기존 Log Archive Dest_1에 Archiving 시작

6. Archiving 정지

7. Archiving 시작

8. Log Switch 발생 확인

9. Log Archive Dest_2 Parameter 원래대로 수정

 

 

1. Archive File이 쌓이는 Directory를 root 권한으로 변경 합니다. (/arch)

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[ORA11]oracle:/home/oracle>su -
Password: 

[root@oracle ~]# chown -R root:root /arch

[root@oracle ~]# exit
logout

 

 

2. Log Switch를 발생시켜 Archive Hang을 일으킵니다.

/home/oracle>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 2 15:40:05 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /
alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

 

 

3. Oracle alert.log를 확인 합니다. (Archive 관련 에러를 확인할 수 있습니다)

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[ORA11]oracle:/home/oracle>adrci

ADRCI: Release 11.2.0.4.0 - Production on Fri Oct 2 15:51:33 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/oracle"
adrci> show alert

Choose the alert log from the following homes to view:

1: diag/tnslsnr/oracle/ora11
2: diag/rdbms/ora11/ORA11
Q: to quit

Please select option: 2

2015-10-02 15:40:12.138000 +09:00
Thread 1 advanced to log sequence 66 (LGWR switch)
  Current log# 1 seq# 66 mem# 0: /oradata/ORA11/redo01.log
Unable to create archive log file '/arch/ORA11/1_65_888594624.arc'
ARC0: Error 19504 Creating archive log file to '/arch/ORA11/1_65_888594624.arc'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance ORA11 - Archival Error
ORA-16038: log 2 sequence# 65 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 2 thread 1: '/oradata/ORA11/redo02.log'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance ORA11 - Archival Error
ORA-16014: log 2 sequence# 65 not archived, no available destinations
ORA-00312: online log 2 thread 1: '/oradata/ORA11/redo02.log'
q!
Please select option: q
adrci> exit

 

 

4. Archive File이 쌓이는 Directory의 권한을 다시 oracle 로 바꿉니다.

[ORA11]oracle:/home/oracle>su -
Password: 

[root@oracle ~]# chown -R oracle:dba /arch

 

 

5. Log Switch를 다시 발생시켜 봅니다. 권한을 바꿔도 Archive Hang이 해결되지 않습니다.

[root@oracle ~]# exit
logout

[ORA11]oracle:/home/oracle>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 2 15:58:14 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

 

 

6. 새로운 터미널로 접속하여, log_archive_dest를 2개 이상으로 변경해 줍니다.

[root@oracle ~]#

[root@oracle ~]# su - oracle

[ORA11]oracle:/home/oracle>>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 2 16:01:15 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter log_archive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=/arch
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string
log_archive_dest_19                  string
log_archive_dest_2                   string
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string
log_archive_dest_3                   string
log_archive_dest_30                  string
log_archive_dest_31                  string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string

SQL> alter system set log_archive_dest_2='location=/home/orcle' scope=memory;

System altered.

SQL> show parameter log_archive_dest_;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=/arch
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string
log_archive_dest_19                  string
log_archive_dest_2                   string      location=/home/orcle
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string
log_archive_dest_3                   string
log_archive_dest_30                  string
log_archive_dest_31                  string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string

 

 

7. 아래 명령어로 Archive Hang 문제를 해결 합니다.

SQL> alter system set log_archive_dest_state_1=defer;

System altered.

SQL> alter system set log_archive_dest_state_1=enable;

System altered.

SQL> alter system archive log stop;

System altered.

SQL> alter system archive log start;

 

 

8. Log Switch가 정상적으로 발생되는 것을 확인할 수 있습니다.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

 

 

9. log_archive_dest_2 Parameter를 원래대로 변경 합니다.

SQL> alter system set log_archive_dest_2='' scope=both;

System altered.