Chinaunix首页 | 论坛 | 博客
  • 博客访问: 655040
  • 博文数量: 70
  • 博客积分: 145
  • 博客等级: 入伍新兵
  • 技术积分: 1150
  • 用 户 组: 普通用户
  • 注册时间: 2012-10-11 08:15
个人简介

没有简介就是最好的简介

文章分类

全部博文(70)

文章存档

2020年(1)

2018年(2)

2017年(3)

2016年(11)

2015年(12)

2014年(16)

2013年(19)

2012年(6)

我的朋友

分类: Oracle

2014-02-18 16:50:50

问题描述
在启动数据库的时候,有一个节点的实例没有起来,查看日志发现报下面的错误
Mon Feb 17 14:28:18 CST 2014
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          201326592 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 2014

Copyright (c) 1982, 2007, 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 2014

Copyright (c) 1982, 2010, 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          201326592 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 2014

Copyright (c) 1982, 2010, 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 2014

Copyright (c) 1982, 2010, 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 2014

Copyright (c) 1982, 2010, 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 2014

Copyright (c) 1982, 2010, 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 2014

Copyright (c) 1982, 2010, 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 2014

Copyright (c) 1982, 2010, 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 2014

Copyright (c) 1982, 2007, 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 2014

Copyright (c) 1982, 2010, 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


阅读(3638) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~