问题描述
在启动数据库的时候，有一个节点的实例没有起来，查看日志发现报下面的错误
Mon Feb 17 14:28:18 CST
Errors in file /oracle/app/admin/hnzz/bdump/hnzz2_ckpt_31237.trc:
ORA-00202: control file: '+FRA/hnzz/controlfile/current.256.839348429'
ORA-15080: synchronous I/O operation to a disk failed
判断是控制文件损坏造成的
解决过程(在正常启动的节点上面执行)，
1，首先备份一下数据库实例的spfile
SQL> create pfile='/tmp/pfile' from spfile;
File created.
2，确定数据库实例当前的控制文件名
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+ORADATA/hnzz/controlfile/current.256.839348429
+FRA/hnzz/controlfile/current.256.839348429
3，停止所有节点上使用这个实例文件的数据库实例(因为另外一个没起来这里直接停止当前节点的就行)
$ srvctl stop instance -d hnzz -i hnzz1
4，启动数据库到nomount状态
SQL> startup nomount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1273276 bytes
Variable Size 79692356 bytes
Database Buffers 26592 bytes
Redo Buffers 2920448 bytes
5，使用rman来复制一个controfile,原文件是实例1启动起来用的控制文件，这里就是+ORADATA/hnzz/controlfile/current.256.839348429，目标时间里到+FRA中
[root@node1 ~]# su - oracle
[oracle@node1 ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Tue Feb 18 15:13:40
Copyright (c) 1982, , Oracle. All rights reserved.
connected to target database: hnzz (not mounted)
RMAN>restore controlfile to '+FRA' from '+ORADATA/hnzz/controlfile/current.256.839348429';
Starting restore at 18-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=148 instance=hnzz1 devtype=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 18-FEB-14
6，利用asmcmd找到新复制的controlfile
[oracle@node1 ~]$ export ORACLE_SID=+ASM1
[oracle@node1 ~]$ asmcmd
ASMCMD> ls
FRA/
ORADATA/
ASMCMD> cd fra
ASMCMD> ls
HNZZ/
ASMCMD> cd hnzz
ASMCMD> ls
CONTROLFILE/
ONLINELOG/
ASMCMD> cd controlfile
ASMCMD> ls
Current.256.839348429
current.261.839862881
下面的这个就是新创建的，
7，设置数据库实例的初始化参数来使用新创建的的controlfile文件
[oracle@node1 ~]$ export ORACLE_SID=hnzz1
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 18 15:20:47
Copyright (c) 1982, , Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> alter system set control_files='+fra/hnzz/controlfile/current.261.839862881' scope=spfile;
System altered.
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +ORADATA/hnzz/controlfile/curr
ent.256.839348429, +FRA/hnzz/c
ontrolfile/current.256.8393484
29
8，重启实例
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1273276 bytes
Variable Size 79692356 bytes
Database Buffers 26592 bytes
Redo Buffers 2920448 bytes
Database mounted.
Database opened.
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +FRA/hnzz/controlfile/current.
261.839862881
在节点2上启动
[oracle@node2 ]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 18 15:35:25
Copyright (c) 1982, , Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1273276 bytes
Variable Size 138412612 bytes
Database Buffers 142606336 bytes
Redo Buffers 2920448 bytes
Database mounted.
Database opened.
实例恢复正常
增加控制文件
[oracle@node2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 18 16:10:50
Copyright (c) 1982, , Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1273276 bytes
Variable Size 138412612 bytes
Database Buffers 142606336 bytes
Redo Buffers 2920448 bytes
SQL>alter system set control_files='+oradata/hnzz/controlfile/Current.256.839348429','+fra/hnzz/controlfile/current.261.839862881' scope=spfile;
System altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@node2 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....STAF.cs application OFFLINE OFFLINE
ora....zz1.srv application OFFLINE OFFLINE
ora.hnzz.db application OFFLINE OFFLINE
ora....z1.inst application OFFLINE OFFLINE
ora....z2.inst application OFFLINE OFFLINE
ora....SM1.asm application ONLINE ONLINE node1
ora....E1.lsnr application ONLINE ONLINE node1
ora.node1.gsd application ONLINE ONLINE node1
ora.node1.ons application ONLINE ONLINE node1
ora.node1.vip application ONLINE ONLINE node1
ora....SM2.asm application ONLINE ONLINE node2
ora....E2.lsnr application ONLINE ONLINE node2
ora.node2.gsd application ONLINE ONLINE node2
ora.node2.ons application ONLINE ONLINE node2
ora.node2.vip application ONLINE ONLINE node2
[oracle@node2 ~]$ srvctl start instance -d hnzz -i hnzz1,hnzz2
PRKP-1001 : Error starting instance hnzz1 on node node1
CRS-0215: Could not start resource 'ora.hnzz.hnzz1.inst'.
PRKP-1001 : Error starting instance hnzz2 on node node2
CRS-0215: Could not start resource 'ora.hnzz.hnzz2.inst'.
[oracle@node2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 18 16:15:33
Copyright (c) 1982, , Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1273276 bytes
Variable Size 138412612 bytes
Database Buffers 142606336 bytes
Redo Buffers 2920448 bytes
ORA-00214: control file '+FRA/hnzz/controlfile/current.261.839862881' version
1031 inconsistent with file '+ORADATA/hnzz/controlfile/current.256.839348429'
version 1017 报错说两个控制文件的版本不一致
SQL> startup nomunt
SP2-0714: invalid combination of STARTUP options
SQL> startup nomount;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@node2 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....STAF.cs application OFFLINE OFFLINE
ora....zz1.srv application OFFLINE OFFLINE
ora.hnzz.db application OFFLINE OFFLINE
ora....z1.inst application ONLINE OFFLINE
ora....z2.inst application OFFLINE OFFLINE
ora....SM1.asm application ONLINE ONLINE node1
ora....E1.lsnr application ONLINE ONLINE node1
ora.node1.gsd application ONLINE ONLINE node1
ora.node1.ons application ONLINE ONLINE node1
ora.node1.vip application ONLINE ONLINE node1
ora....SM2.asm application ONLINE ONLINE node2
ora....E2.lsnr application ONLINE ONLINE node2
ora.node2.gsd application ONLINE ONLINE node2
ora.node2.ons application ONLINE ONLINE node2
ora.node2.vip application ONLINE ONLINE node2
[oracle@node2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 18 16:19:14
Copyright (c) 1982, , Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1273276 bytes
Variable Size 138412612 bytes
Database Buffers 142606336 bytes
Redo Buffers 2920448 bytes
SQL>alter system set control_files='+oradata/hnzz/controlfile/Current.256.839348429' scope=spfile; 第一次尝试 重新回到之前的controlfile
System altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1273276 bytes
Variable Size 138412612 bytes
Database Buffers 142606336 bytes
Redo Buffers 2920448 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '+ORADATA/hnzz/datafile/system.259.839348441'
ORA-01207: file is more recent than control file - old control file 报错说控制文件的版本太老
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@node2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 18 16:23:25
Copyright (c) 1982, , Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1273276 bytes
Variable Size 138412612 bytes
Database Buffers 142606336 bytes
Redo Buffers 2920448 bytes
SQL>alter system set control_files='+fra/hnzz/controlfile/current.261.839862881' scope=spfile; 第二次尝试 使用上面刚恢复的controlfile
System altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1273276 bytes
Variable Size 138412612 bytes
Database Buffers 142606336 bytes
Redo Buffers 2920448 bytes
Database mounted.
Database opened.
SQL> select name from v$controfile;
select name from v$controfile
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+FRA/hnzz/controlfile/current.261.839862881
SQL>
数据库正常启动，但是只有一个控制文件
下面是完整的增加步骤(先恢复后增加)
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 18 16:33:30
Copyright (c) 1982, , Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> create pfile='/tmp/pfile' from spfile;
File created.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+FRA/hnzz/controlfile/current.261.839862881
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1273276 bytes
Variable Size 88080964 bytes
Database Buffers 192937984 bytes
Redo Buffers 2920448 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@node1 ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Tue Feb 18 16:36:11
Copyright (c) 1982, , Oracle. All rights reserved.
connected to target database: hnzz (not mounted)
RMAN> restore controlfile to '+ORADATA' from '+FRA/hnzz/controlfile/current.261.839862881';
Starting restore at 18-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=149 instance=hnzz1 devtype=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 18-FEB-14
RMAN> quit
Recovery Manager complete.
[oracle@node1 ~]$ echo $ORACLE_SID
hnzz1
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 18 16:41:59
Copyright (c) 1982, , Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1273276 bytes
Variable Size 88080964 bytes
Database Buffers 192937984 bytes
Redo Buffers 2920448 bytes
SQL> alter system set control_files='+oradata/hnzz/controlfile/current.268.839867897','+FRA/hnzz/controlfile/current.261.839862881' scope=spfile; 标红的为新恢复的
System altered.
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +FRA/hnzz/controlfile/current.
261.839862881
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1273276 bytes
Variable Size 88080964 bytes
Database Buffers 192937984 bytes
Redo Buffers 2920448 bytes
Database mounted.
Database opened.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+ORADATA/hnzz/controlfile/current.268.839867897
+FRA/hnzz/controlfile/current.261.839862881
