一则奇怪的案例处理：ORA-00257: archiver error. Connect internal only, until freed
前天，业务反应数据库不能连接
在操作系统通过字符串尝试登陆数据库报：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参数需要重启数据库？？？说不通！！！
笔记
