前天，業務反應資料庫不能連接配接
在作業系統通過字元串嘗試登陸資料庫報：ORA-00257: archiver error. Connect internal only, until freed
解決思路：
1.作業系統清理歸檔
2.rman清理expired歸檔
遇到日志不能切換，且歸檔目錄未滿的情況，且資料庫不能正常關閉的解決思路：
1.檢視log group 狀态，如果處于inactive狀态但是報需要歸檔的錯誤
2.強制clear未歸檔的日志
3.删除clear的日志組，并重建
4.如果還不能switch logfile，重新開機資料庫
詳細步驟如下：
檢視alert日志：
***********************************************************************
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for HPUX: Version 11.2.0.3.0 - Production
Oracle Bequeath NT Protocol Adapter for HPUX: Version 11.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for HPUX: Version 11.2.0.3.0 - Production
Time: 17-MAR-2015 17:28:08
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=132.33.128.28)(PORT=4035))
WARNING: inbound connection timed out (ORA-3136) 哦，原來是歸檔滿了
1.資料庫查詢歸檔位置：
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 5
Next log sequence to archive 5
Current log sequence 7
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /Oracle/apps/oracle/recovery
db_recovery_file_dest_size big integer 4977M
SQL> select * from v$recovery_file_dest
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------------------------------------- ----------- ---------- ----------------- ---------------
/Oracle/apps/oracle/recovery 5218762752 4405968896 0 3 2.進入作業系統，手動删除所有歸檔
3.rman檢查并清理歸檔
RMAN> crosscheck archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=235 device type=DISK
validation failed for archived log
archived log file name=/Oracle/apps/oracle/recovery/GJJS/archivelog/2015_02_03/o1_mf_1_2_bf1o3ohr_.arc RECID=1 STAMP=870732036
validation failed for archived log
archived log file name=/Oracle/apps/oracle/recovery/GJJS/archivelog/2015_02_12/o1_mf_1_3_bfqxh0sz_.arc RECID=2 STAMP=871461477
validation failed for archived log
archived log file name=/Oracle/apps/oracle/recovery/GJJS/archivelog/2015_02_19/o1_mf_1_4_bgcv464n_.arc RECID=3 STAMP=872114452
Crosschecked 3 objects
RMAN> list expired archivelog all;
List of Archived Log Copies for database with db_unique_name GJJS
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 2 X 28-JAN-15
Name: /Oracle/apps/oracle/recovery/GJJS/archivelog/2015_02_03/o1_mf_1_2_bf1o3ohr_.arc
2 1 3 X 03-FEB-15
Name: /Oracle/apps/oracle/recovery/GJJS/archivelog/2015_02_12/o1_mf_1_3_bfqxh0sz_.arc
3 1 4 X 12-FEB-15
Name: /Oracle/apps/oracle/recovery/GJJS/archivelog/2015_02_19/o1_mf_1_4_bgcv464n_.arc
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=235 device type=DISK
List of Archived Log Copies for database with db_unique_name GJJS
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 2 X 28-JAN-15
Name: /Oracle/apps/oracle/recovery/GJJS/archivelog/2015_02_03/o1_mf_1_2_bf1o3ohr_.arc
2 1 3 X 03-FEB-15
Name: /Oracle/apps/oracle/recovery/GJJS/archivelog/2015_02_12/o1_mf_1_3_bfqxh0sz_.arc
3 1 4 X 12-FEB-15
Name: /Oracle/apps/oracle/recovery/GJJS/archivelog/2015_02_19/o1_mf_1_4_bgcv464n_.arc
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/Oracle/apps/oracle/recovery/GJJS/archivelog/2015_02_03/o1_mf_1_2_bf1o3ohr_.arc RECID=1 STAMP=870732036
deleted archived log
archived log file name=/Oracle/apps/oracle/recovery/GJJS/archivelog/2015_02_12/o1_mf_1_3_bfqxh0sz_.arc RECID=2 STAMP=871461477
deleted archived log
archived log file name=/Oracle/apps/oracle/recovery/GJJS/archivelog/2015_02_19/o1_mf_1_4_bgcv464n_.arc RECID=3 STAMP=872114452
Deleted 3 EXPIRED objects 4.改變歸檔目錄，是歸檔不放入閃回目錄中
alter system set log_archive_dest_1='location=/Oracle/apps/oracle/arch'; OK
到這裡，歸檔空間已經清理完成，但是在作業系統上再次測試的時候依然報ORA-00257: archiver error. Connect internal only, until freed，很奇怪~~
嘗試資料庫中切換日志
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 7 INACTIVE
2 8 CURRENT
3 6 INACTIVE
SQL>alter system switch logfile;
----夯住不動，alert日志中也未出現任何錯誤 希望通過重新開機資料庫解決：
但是執行shutdown immediate後，資料庫無反應，看alert日志
License high water mark = 45
Stopping Job queue slave processes, flags = 7
Tue Mar 17 16:42:09 2015
Errors in file /Oracle/apps/oracle/diag/rdbms/gjjs/gjjs/trace/gjjs_j002_28185.trc:
ORA-12012: error on auto execute of job "EXFSYS"."RLM$EVTCLEANUP"
ORA-01089: immediate shutdown in progress - no operations are permitted
ORA-01089: immediate shutdown in progress - no operations are permitted
Process ID:
Session ID: 0 Serial number: 0
Tue Mar 17 16:42:09 2015
Errors in file /Oracle/apps/oracle/diag/rdbms/gjjs/gjjs/trace/gjjs_j001_17723.trc:
ORA-12012: error on auto execute of job "EXFSYS"."RLM$SCHDNEGACTION"
ORA-01089: immediate shutdown in progress - no operations are permitted
ORA-01089: immediate shutdown in progress - no operations are permitted
Process ID:
Session ID: 0 Serial number: 0
Job queue slave processes stopped CTRL+C結束，奇怪~~~
可能是日志檔案出現錯誤，下面進行清理，并重建日志檔案
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance gjjs (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/dev/vg03/rlv_vg03_2g_redo003'
SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance gjjs (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/dev/vg03/rlv_vg03_2g_redo003'
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 7 INACTIVE
2 8 CURRENT
3 0 UNUSED
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/dev/vg03/rlv_vg03_2g_redo001
/dev/vg03/rlv_vg03_2g_redo002
/dev/vg03/rlv_vg03_2g_redo003
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 7 INACTIVE
2 8 CURRENT
3 0 UNUSED
SQL> alter database drop logfile group 3;
Database altered.
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 7 INACTIVE
2 8 CURRENT SQL> alter database add logfile group 3 '/dev/vg03/rlv_vg03_2g_redo03.log' size 2046M reuse;
Database altered. 檢視，再次切換
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/dev/vg03/rlv_vg03_2g_redo001
/dev/vg03/rlv_vg03_2g_redo002
/dev/vg03/rlv_vg03_2g_redo03.log
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 7 INACTIVE
2 8 CURRENT
3 0 UNUSED
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 7 INACTIVE
2 8 ACTIVE
3 9 CURRENT SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 7 INACTIVE
2 8 ACTIVE
3 9 CURRENT
-----group 2一直處于active狀态，做一個checkpoint，使日志無效
SQL> alter system checkpoint;
System altered.
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 7 INACTIVE
2 8 INACTIVE
3 9 CURRENT
----切換的時候依然不能切換，重建所有的日志組
SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-00350: log 1 of instance gjjs (thread 1) needs to be archived
ORA-00312: online log 1 thread 1: '/dev/vg03/rlv_vg03_2g_redo001'
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 '/dev/vg03/rlv_vg03_2g_redo01.log' size 2046M reuse;
Database altered.
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 0 UNUSED
2 8 INACTIVE
3 9 CURRENT
SQL> alter database clear unarchived logfile group 2;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 '/dev/vg03/rlv_vg03_2g_redo02.log' size 2046M reuse;
Database altered.
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 0 UNUSED
2 0 UNUSED
3 9 CURRENT
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 10 INACTIVE
2 11 CURRENT
3 9 INACTIVE
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> c/3/1
1* alter database clear unarchived logfile group 1
SQL> /
Database altered.
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 0 UNUSED
2 11 CURRENT
3 0 UNUSED
----重建日志組後依然不行，重新開機資料庫看看
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
-----資料庫可以正常關閉
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 2.1913E+11 bytes
Fixed Size 2195616 bytes
Variable Size 1.0415E+11 bytes
Database Buffers 1.1489E+11 bytes
Redo Buffers 88866816 bytes
Database mounted.
Database opened.
SQL>
SQL>
----檢視狀态并切換日志，資料庫正常~
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 0 UNUSED
2 11 CURRENT
3 0 UNUSED
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered. 難道是修改log_arch_dest_1參數需要重新開機資料庫？？？說不通！！！
筆記
![ORA-00257:歸檔程式錯誤.[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)