oracle无法启动分析,一次oracle数据库无法启动的故障处理

在oracle 11g测试库数据库上做实验，临时需要重启数据库，为了快速使用了startup

force命令，结果呢，报错ORA-03113，再次尝试重启数据库，发现还是报错

SQL> startup force

ORACLE instance started.

Total System Global Area 839282688 bytes

Fixed

Size 2233000 bytes

Variable

Size 683674968 bytes

Database

Buffers 150994944 bytes

Redo

Buffers 2379776 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 25518

Session ID: 1 Serial number: 5

SQL> show user

USER is "SYS"

SQL> select status from v$instance;

ERROR:

ORA-03114: not connected to ORACLE

SQL> conn / as sysdba

Connected to an idle instance.

SQL>

SQL>

SQL>

SQL> startup

ORACLE instance started.

Total System Global Area 839282688 bytes

Fixed

Size 2233000 bytes

Variable

Size 683674968 bytes

Database

Buffers 150994944 bytes

Redo

Buffers 2379776 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 26206

Session ID: 1 Serial number: 5

出来这样的故障呢，首先的习惯是检查一下oracle数据库的警告日志，通常这里面会有故障的一些信息

[oracle@asm11g ~]$ cd

/u01/app/oracle/diag/rdbms/tj01/tj01/trace

[oracle@asm11g trace]$ tail -30 alert_tj01.log

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Thu Sep 13 16:34:30 2012

ARC1 started with pid=27, OS id=26460

Thu Sep 13 16:34:30 2012

ARC2 started with pid=28, OS id=26462

Errors in file

/u01/app/oracle/diag/rdbms/tj01/tj01/trace/tj01_ora_26406.trc:

ORA-19816: WARNING: Files may exist in db_recovery_file_dest that

are not known to database.

ORA-17502: ksfdcre:4 Failed to create file +FRADATA

ORA-15041: diskgroup "FRADATA" space

exhausted*************************************************************

WARNING: A file of type ARCHIVED LOG may exist in

db_recovery_file_dest that is not known to the database.

Use the RMAN command CATALOG RECOVERY AREA to re-catalog

any such files. If files cannot be cataloged, then manually

delete them using OS command. This is most likely the

result of a crash during file creation.

*************************************************************

ARCH: Error 19504 Creating archive log file

to '+FRADATA'

Errors in file

/u01/app/oracle/diag/rdbms/tj01/tj01/trace/tj01_ora_26406.trc:

ORA-16038: log 1 sequence# 97 cannot be

archivedORA-19504: failed to create file ""

ORA-00312: online log 1 thread 1:

'+DG1/tj01/onlinelog/group_1.261.783988383'

ORA-00312: online log 1 thread 1:

'+FRADATA/tj01/onlinelog/group_1.257.783988387'

USER (ospid: 26406): terminating the instance due to error

16038

System state dump requested by (instance=1, osid=26406),

summary=[abnormal instance termination].

System State dumped to trace file

/u01/app/oracle/diag/rdbms/tj01/tj01/trace/tj01_diag_26368.trc

Dumping diagnostic data in directory=[cdmp_20120913163430],

requested by (instance=1, osid=26406), summary=[abnormal instance

termination].

Instance terminated by USER, pid = 26406

在上面的日志中，我发现了很多ORA-

的错误信息，仔细分析发现呢，是oracle无法完成归档操作，作为归档目录的asm磁盘组'+FRADATA'满了，这才想起来，刚刚做了一个大表的dml操作

既然找到了问题，解决起来就容易了，我的数据库只是练习用的，有一个全备，只要删掉就可以释放空间了

[oracle@asm11g trace]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Sep 13

16:41:02 2012

Copyright (c) 1982, 2011, Oracle and/or its

affiliates. All rights reserved.

connected to target database (not started)

RMAN> startup mount

Oracle instance started

database mounted

Total System Global

Area 839282688 bytes

Fixed

Size 2233000 bytes

Variable

Size 683674968 bytes

Database

Buffers 150994944 bytes

Redo

Buffers 2379776 bytes

RMAN> delete backupset;

using target database control file instead of recovery

catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1 device type=DISK

List of Backup Pieces

BP Key BS Key Pc# Cp#

Status Device Type Piece Name

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

1 1 1 1 AVAILABLE DISK +FRADATA/tj01/backupset/2012_07_18/nnndf0_tag20120718t165243_0.300.788979165

2 2 1 1 AVAILABLE DISK +FRADATA/tj01/backupset/2012_07_18/nnndf0_tag20120718t165254_0.299.788979175

3 3 1 1 AVAILABLE DISK +FRADATA/tj01/backupset/2012_09_12/ncnnf0_tag20120912t174456_0.308.793820701

Do you really want to delete the above objects (enter YES or

NO)? yes

deleted backup piece

backup piece

handle=+FRADATA/tj01/backupset/2012_07_18/nnndf0_tag20120718t165243_0.300.788979165

RECID=1 STAMP=788979164

deleted backup piece

backup piece

handle=+FRADATA/tj01/backupset/2012_07_18/nnndf0_tag20120718t165254_0.299.788979175

RECID=2 STAMP=788979174

deleted backup piece

backup piece

handle=+FRADATA/tj01/backupset/2012_09_12/ncnnf0_tag20120912t174456_0.308.793820701

RECID=3 STAMP=793820700

Deleted 3 objects

RMAN> exit

Recovery Manager complete.

再次进入sqlplus，打开数据库，一切正常。

[oracle@asm11g trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 13 16:41:37

2012

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

reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

Production

With the Partitioning, Automatic Storage Management, OLAP, Data

Mining

and Real Application Testing options

SQL> alter database open;

Database altered.

诊断数据库呢，除了有丰富的经验作为判断依据外，更主要的是要养成好的习惯，比如遇到问题切入点是看警告日志文件，根据里面提供的信息

进一步判断。

还好是测试库，管理生产库呢，这就算一个事故了。