Oracle 11g RAC中INTERMEDIATE Stuck Archiver狀態的解決方法

這篇文章給大家介紹Oracle 11g RAC中INTERMEDIATE Stuck Archiver狀態的解決方法，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

新建的庫，空跑了幾天之后，使用pl/sql工具連接數據庫一節點，報沒有監聽，然后查看監聽狀態：

rac1:/home/oracle@grid> lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 02-5月 -2018 09:53:37

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

TNS-12541: TNS:no listener

TNS-12560: TNS:protocol adapter error

TNS-00511: No listener

Linux Error: 2: No such file or directory

二節點監聽狀態：

rac2:/grid/app/11.2.0/network/admin@grid> lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 02-52018 09:48:02

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                11-42018 17:19:45

Uptime                    20 days 16 hr. 28 min. 17 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /grid/app/11.2.0/network/admin/listener.ora

Listener Log File         /grid/app/grid/diag/tnslsnr/rac2/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=136.64.194.180)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=136.64.194.182)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM2", status READY, has 1 handler(s) for this service...

Service "rac" has 1 instance(s).

Instance "rac2", status READY, has 1 handler(s) for this service...

The command completed successfully

然后查看集群狀態

rac1:/home/oracle@grid> crsctl stat res -t

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

省略

ora.rac.db

1        ONLINE  INTERMEDIATE       rac1                     Stuck Archiver

2        ONLINE  INTERMEDIATE       rac2                     Stuck Archiver

ora.rac1.vip

1        ONLINE  INTERMEDIATE rac2                     FAILED OVER

ora.rac2.vip

1        ONLINE  ONLINE       rac2

發現現在數據庫實例變成了INTERMEDIATE Stuck Archiver狀態，然后1節點的vip也failed over了

然后繼續分析alert日志，發現如下報錯信息

ARC1: Error 19809 Creating archive log file to '+ARCH'

Errors in file /oracle/diag/rdbms/rac/rac1/trace/rac1_arc3_21444.trc:

ORA-19815: WARNING: db_recovery_file_dest_size of 6005194752 bytes is 100.00% used, and has 0 remaining bytes available.

好像跟歸檔目錄有關系，然后去看歸檔情況

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     8

Next log sequence to archive   10

Current log sequence           10

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      +ARCH

db_recovery_file_dest_size           big integer 5G

發現歸檔使用ASM存儲的+ARCH區域，而且只能使用5G的大小，這也就發現了問題的原因，雖然我們有100g的歸檔空間，現在只能使用5G，所以歸檔目錄無法寫入了

開始解決問題

SQL> alter system set db_recovery_file_dest_size=100G;

System altered.

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      +ARCH

db_recovery_file_dest_size           big integer 100G

再看集群狀態，發現集群狀態恢復正常，然后啟動一節點監聽

rac1:/home/oracle@grid> srvctl start listener -l LISTENER

故障恢復。

關于Oracle 11g RAC中INTERMEDIATE Stuck Archiver狀態的解決方法就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。