Oracle RAC 环境下重建控制文件
注：ORA-00600: internal error code, arguments: [3716], [], [], [], [], [], [], [] 问题处理。

问题：

在RAC环境下分别启动两个实例都出现了ora-03113错误。

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 6 08:50:32 2014

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area  839282688 bytes

Fixed Size                      2233000 bytes

Variable Size              557845848 bytes

Database Buffers      276824064 bytes

Redo Buffers               2379776 bytes

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

Process ID: 23438

Session ID: 1 Serial number: 3

查看alter日志中发现如下信息:

ORA-00600: internal error code, arguments: [3716], [], [], [], [], [], [], [] 
ORA-600 signalled during: ALTER DATABASE OPEN... 

原因是在节点重启时，实例没有完成一至性关闭。可能错过了写控制文件。

在启动实例的时候会发现控制文件配置有问题。

首先想到的是用rman备份来恢复控制文件,但是数据库没有备份。

RMAN> list backup;

using target database control file instead of recovery catalog

specification does not match any backup in the repository

并且数据库没开归档。

SQL> archive log list;

Database log mode               No Archive Mode

Automatic archival        Disabled

Archive destination                USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Current log sequence            2

解决方法：

1.生成可以重建控制文件的文本

SQL> alter database backup controlfile to trace;

Database altered.

2.在alert日志中找到生成的文件路径

alter database backup controlfile to trace

Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/racdb/RACDB2/trace/RACDB2_ora_10126.trc

Completed: alter database backup controlfile to trace

3.打开此trace文件再找到创建控制文件的SQL

4.执行此SQL重新创建控制文件

SQL> @?/crecontr

CREATE CONTROLFILE REUSE DATABASE "RACDB" NORESETLOGS  NOARCHIVELOG

*

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-12720: operation requires database is in EXCLUSIVE mode

在 rac环境下有cluster_database这个参数

SQL> show parameter cluster_database

NAME                                     TYPE      VALUE

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

cluster_database                  boolean         TRUE

cluster_database_instances       integer  2

修改参数cluster_database

SQL> alter system set cluster_database=false scope=spfile;

System altered.

重新启动数据库到nomount状态

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  839282688 bytes

Fixed Size                      2233000 bytes

Variable Size              574623064 bytes

Database Buffers      260046848 bytes

Redo Buffers               2379776 bytes

SQL> show parameter cluster

NAME                                     TYPE      VALUE

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

cluster_database                  boolean         FALSE

cluster_database_instances       integer  1

cluster_interconnects                  string

重新执行创建语句，创建成功

SQL> @?/crecontr

Control file created.

启动到Open状态报错

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '+DATA/racdb/datafile/system.260.849510771'

Recovey恢复

SQL> recover database;

Media recovery complete.

SQL> alter database open;

Database altered.

可以启动了，但是查看alert发现如下信息。

ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"

ORA-25153: Temporary Tablespace is Empty

ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073

ORA-06512: at line 1

Tue Jun 10 16:26:04 2014

Starting background process SMCO

Tue Jun 10 16:26:04 2014

SMCO started with pid=39, OS id=12213

Tue Jun 10 16:26:06 2014

Errors in file /u01/app/oracle/diag/rdbms/racdb/RACDB2/trace/RACDB2_j000_12209.trc:

ORA-25153: Temporary Tablespace is Empty

创建控制文件的SQL没有执行全，临时表空间是空。

在trace文件中找到相应sql重新执行。

ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/racdb/tempfile/temp.263.849510777' REUSE;

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/racdb/tempfile/temp.263.849510777' REUSE;

Tablespace altered.

改回rac环境的参数并重启两个实例

SQL> alter system set cluster_database=true scope=spfile;

System altered.

SQL> alter system set cluster_database_instances=2 scope=spfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area  839282688 bytes

Fixed Size                      2233000 bytes

Variable Size              574623064 bytes

Database Buffers      260046848 bytes

Redo Buffers               2379776 bytes

Database mounted.

Database opened.

启动第二个节点

SQL> startup;

ORACLE instance started.

Total System Global Area  839282688 bytes

Fixed Size                      2233000 bytes

Variable Size              557845848 bytes

Database Buffers      276824064 bytes

Redo Buffers               2379776 bytes

Database mounted.

Database opened.

实例全部启来了。

总结：

1.执行alter database backup controlfile to trace生成可以重建控制文件的trace文件

2.在alert日志中找到生成的trace文件路径

3.如果在 rac环境下需要修改参数cluster_database

4.打开此trace文件再找到创建控制文件的SQL语句

5.执行此SQ语句重新创建控制文件

6.如果在RAC环境需改回cluster_database参数设置
7.重启数据库(需要recovery)

      

            DBA_建瑾
            2014.6.21