ORA-16038简单的处理过程

作者：gtlions 【转载时请以超链接心事标明文章出处和作者信息,谢谢.】

链接：http://blog.youkuaiyun.com/gtlions/archive/2010/04/02/5446069.aspx

今天同事的笔记本上的一个oracle 10g实例出现了错误，同事无法处理，我就接过来看了下并解决之。

解决过程记录如下。

检查了下alter日志：

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

Fri Apr 02 09:56:09 2010
Errors in file d:/oracle/product/10.2.0/admin/orcl/bdump/orcl_arc1_5392.trc:
ORA-16014: log 1 sequence# 93 not archived, no available destinations
ORA-00312: online log 1 thread 1: 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO01.LOG'

Fri Apr 02 09:56:09 2010
ARCH: Error 19809 Creating archive log file to 'D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2010_04_02/O1_MF_1_89_U_.ARC'
Fri Apr 02 09:56:09 2010
ARCH: Failed to archive thread 1 sequence 89 (19809)
ORA-16038 signalled during: alter database open...
Fri Apr 02 09:57:09 2010
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 89 (4)
Fri Apr 02 09:58:09 2010
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 89 (4)
Fri Apr 02 09:59:09 2010
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 89 (4)
Fri Apr 02 09:59:44 2010
alter database datafile 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG' offline
Fri Apr 02 09:59:44 2010
ORA-1516 signalled during: alter database datafile 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG' offline...
Fri Apr 02 10:00:09 2010
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 89 (4)
Fri Apr 02 10:00:56 2010
alter database datafile 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG' offline drop
Fri Apr 02 10:00:56 2010
ORA-1516 signalled during: alter database datafile 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG' offline drop...
Fri Apr 02 10:01:08 2010
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 89 (4)
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 89 (4)
Fri Apr 02 10:01:23 2010
alter database datafile 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG' offline drop
Fri Apr 02 10:01:23 2010
ORA-1516 signalled during: alter database datafile 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG' offline drop...
Fri Apr 02 10:02:09 2010
Errors in file d:/oracle/product/10.2.0/admin/orcl/bdump/orcl_arc1_5392.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.

Fri Apr 02 10:02:09 2010
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
Fri Apr 02 10:02:09 2010
Errors in file d:/oracle/product/10.2.0/admin/orcl/bdump/orcl_arc1_5392.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 47716352 bytes disk space from 2147483648 limit

ARC1: Error 19809 Creating archive log file to 'D:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2010_04_02/O1_MF_1_89_U_.ARC'
ARC1: Failed to archive thread 1 sequence 89 (19809)
ARCH: Archival stopped, error occurred. Will continue retrying
Fri Apr 02 10:02:10 2010
Errors in file d:/oracle/product/10.2.0/admin/orcl/bdump/orcl_arc1_5392.trc:
ORA-16038: log 2 sequence# 89 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG'

Fri Apr 02 10:02:56 2010
Archive Log Next
ARCH: Archiving not possible: No primary destinations
ARCH: Failed to archive thread 1 sequence 89 (4)
Fri Apr 02 10:02:56 2010
Errors in file d:/oracle/product/10.2.0/admin/orcl/udump/orcl_ora_5404.trc:
ORA-16014: 日志 2 的序列号 89 未归档, 没有可用的目的地
ORA-00312: 联机日志 2 线程 1: 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG'

Archive Log Next
ARCH: Archiving not possible: No primary destinations
ARCH: Failed to archive thread 1 sequence 89 (4)
Fri Apr 02 10:02:59 2010
Errors in file d:/oracle/product/10.2.0/admin/orcl/udump/orcl_ora_5404.trc:
ORA-16014: 日志 2 的序列号 89 未归档, 没有可用的目的地
ORA-00312: 联机日志 2 线程 1: 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG'

Archive Log Next
ARCH: Archiving not possible: No primary destinations
ARCH: Failed to archive thread 1 sequence 89 (4)
Fri Apr 02 10:03:00 2010
Errors in file d:/oracle/product/10.2.0/admin/orcl/udump/orcl_ora_5404.trc:
ORA-16014: 日志 2 的序列号 89 未归档, 没有可用的目的地
ORA-00312: 联机日志 2 线程 1: 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG'

Fri Apr 02 10:03:09 2010
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 89 (4)
ARCH: Archival stopped, error occurred. Will continue retrying
Fri Apr 02 10:03:09 2010
Errors in file d:/oracle/product/10.2.0/admin/orcl/bdump/orcl_arc0_4208.trc:
ORA-16014: log 2 sequence# 89 not archived, no available destinations
ORA-00312: online log 2 thread 1: 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG'

Fri Apr 02 10:04:09 2010
alter database drop logfile member 'D:/oracle/product/10.2.0/oradata/orcl/REDO02.LOG'
Fri Apr 02 10:04:09 2010
ORA-361 signalled during: alter database drop logfile member 'D:/oracle/product/10.2.0/oradata/orcl/REDO02.LOG'...
Fri Apr 02 10:04:09 2010
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 89 (4)
Fri Apr 02 10:04:36 2010
alter database clear unarchived logfile 'D:/oracle/product/10.2.0/oradata/orcl/REDO02.LOG'
Fri Apr 02 10:04:36 2010
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
BEFORE 03/31/2010 09:16:02 (CHANGE 3059992) CANNOT BE USED FOR RECOVERY.
Clearing online log 2 of thread 1 sequence number 89
Completed: alter database clear unarchived logfile 'D:/oracle/product/10.2.0/oradata/orcl/REDO02.LOG'
Fri Apr 02 10:04:50 2010
alter database open
Fri Apr 02 10:04:56 2010
Thread 1 advanced to log sequence 95
Thread 1 opened at log sequence 95
Current log# 2 seq# 95 mem# 0: D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG
Successful open of redo thread 1
Fri Apr 02 10:04:57 2010
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Apr 02 10:04:58 2010
ARC0: Archiving not possible: No primary destinations
Fri Apr 02 10:04:58 2010
SMON: enabling cache recovery
Fri Apr 02 10:04:58 2010
ARC0: Failed to archive thread 1 sequence 93 (4)
Fri Apr 02 10:04:58 2010
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 93 (4)
Fri Apr 02 10:04:59 2010
Successfully onlined Undo Tablespace 1.
Fri Apr 02 10:04:59 2010
SMON: enabling tx recovery
Fri Apr 02 10:05:00 2010
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=21, OS id=4272
Fri Apr 02 10:05:09 2010
Completed: alter database open
Fri Apr 02 10:05:12 2010
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 93 (4)
Fri Apr 02 10:05:15 2010
Starting background process EMN0
EMN0 started with pid=28, OS id=5144
Fri Apr 02 10:05:15 2010
Shutting down instance: further logons disabled
Fri Apr 02 10:05:16 2010
ksvcreate: Process(q001) creation failed
Fri Apr 02 10:05:17 2010
Stopping background process QMNC
Fri Apr 02 10:05:18 2010
Stopping background process CJQ0
Fri Apr 02 10:05:19 2010
Stopping background process MMNL
Fri Apr 02 10:05:23 2010
Stopping background process MMON
Fri Apr 02 10:05:27 2010
Shutting down instance (immediate)
License high water mark = 7
Fri Apr 02 10:05:27 2010
Stopping Job queue slave processes
Fri Apr 02 10:05:27 2010
Job queue slave processes stopped
All dispatchers and shared servers shutdown
Fri Apr 02 10:05:35 2010
ALTER DATABASE CLOSE NORMAL
Fri Apr 02 10:05:36 2010
SMON: disabling tx recovery
SMON: disabling cache recovery
Fri Apr 02 10:05:39 2010
LGWR: Waiting for ORLs to be archived...
Fri Apr 02 10:06:08 2010
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 93 (4)
Fri Apr 02 10:06:11 2010
Shutting down archive processes
Fri Apr 02 10:06:16 2010
ARCH shutting down
ARC2: Archival stopped
Fri Apr 02 10:06:16 2010
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 93 (4)
Fri Apr 02 10:06:39 2010
LGWR: Archiving did not complete