RAC由于歸檔表空間滿而無法啟動實(shí)例的解決

今天想測試點(diǎn)東西，登錄測試庫；發(fā)現(xiàn)實(shí)例是關(guān)閉的；

SQL> startup
ORACLE instance started.

Total System Global Area 2722467840 bytes
Fixed Size                  2231472 bytes
Variable Size            1476395856 bytes
Database Buffers         1241513984 bytes
Redo Buffers                2326528 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 23281
Session ID: 1 Serial number: 5


查看日志

SUCCESS: diskgroup ORAARCH was mounted
ARCH: Error 19504 Creating archive log file to '+ORAARCH'
Errors in file /u01/app/oracle/diag/rdbms/xhdb/xhdb1/trace/xhdb1_ora_23565.trc:
ORA-16038: log 5 sequence# 345 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 5 thread 1: '+ORADATA/xhdb/onlinelog/group_5.272.857422319                    '
USER (ospid: 23565): terminating the instance due to error 16038
System state dump requested by (instance=1, osid=23565), summary=[abnormal insta                    nce termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/xhdb/xhdb1/trace/xh                    db1_diag_23451.trc
Dumping diagnostic data in directory=[cdmp_20141218102056], requested by (instan                    ce=1, osid=23565), summary=[abnormal instance termination].
Instance terminated by USER, pid = 23565


查看(fdisk)查看磁盤都在；切換到grid集群也在運(yùn)行，asm磁盤組都在；可能是歸檔空間不夠了；把節(jié)點(diǎn)1的數(shù)據(jù)庫實(shí)例啟動到掛載狀態(tài)下；



SQL> startup mount;
ORACLE instance started.

Total System Global Area 2722467840 bytes
Fixed Size                  2231472 bytes
Variable Size            1476395856 bytes
Database Buffers         1241513984 bytes
Redo Buffers                2326528 bytes
Database mounted.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +ORAARCH
Oldest online log sequence     345
Next log sequence to archive   345


SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;

GROUP_NUMBER NAME                           STATE         TOTAL_MB    FREE_MB
------------ ------------------------------ ----------- ---------- ----------
           1 CRS                            MOUNTED            998        600
           2 ORAARCH                        MOUNTED           8189         11
           3 ORADATA                        CONNECTED        20473      16198



發(fā)現(xiàn)是oraarch 歸檔空間不夠了,節(jié)點(diǎn)1登錄rman,把歸檔日志刪除；在節(jié)點(diǎn)1和節(jié)點(diǎn)2啟動數(shù)據(jù)庫實(shí)例，正常；

RMAN> crosscheck archivelog all;

RMAN> delete archivelog all; 


SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;

GROUP_NUMBER NAME                           STATE         TOTAL_MB    FREE_MB
------------ ------------------------------ ----------- ---------- ----------
           1 CRS                            MOUNTED            998        600
           2 ORAARCH                        CONNECTED         8189       5123
           3 ORADATA                        CONNECTED        20473      16198

SQL> alter database open;

Database altered.





節(jié)點(diǎn)2 也能正常打開
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 18 10:56:54 2014

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2722467840 bytes
Fixed Size                  2231472 bytes
Variable Size            1191183184 bytes
Database Buffers         1526726656 bytes
Redo Buffers                2326528 bytes
Database mounted.
Database opened.