oracle数据恢复案例 - 控制文件损坏，无备份

数据丢失原因：集群多次切换，多次执行shutdown abort，集群默认执行的命令是abort方式，导致控制文件全部损坏。

 

基础环境介绍：

suse11sp2

oracle10.2.0.5

suse HA

oracle归档模式

 

处理问题思路：

1、检查是否有幸存控制文件。因为控制文件有多个，尝试使用单个控制文件启动数据库，如果有完好的控制文件，就无需回复了，直接拷贝改名解决；

2、如果所有控制文件均损坏，看看有没有备份。利用备份恢复是较高效的方法。

3、如果连备份都没有，那就用最后一招，重建控制文件吧。

 

我这里由于是测试环境，为了测试集群搭建的一个数据库，没有考虑过备份问题，所以现在的情况是第三种，控制文件全部损坏且无备份。解决问题的过程如下：

 

一、问题定位

从双机软件的报错看到，数据库起不来。

查看alert发现如下报错：

ALTER DATABASE   MOUNT

Mon May 13 16:26:31 CST 2013

Setting recovery target incarnation to 1

Mon May 13 16:26:31 CST 2013

Errors in file /opt/oracle/admin/ebai/bdump/ebai_lgwr_78381.trc:

ORA-00600: internal error code, arguments: [kccpb_sanity_check_2], [5597], [5596], [0x000000000], [], [], [], []

Mon May 13 16:26:31 CST 2013

Errors in file /opt/oracle/admin/ebai/bdump/ebai_lgwr_78381.trc:

ORA-00600: internal error code, arguments: [kccpb_sanity_check_2], [5597], [5596], [0x000000000], [], [], [], []

Mon May 13 16:26:31 CST 2013

LGWR: terminating instance due to error 470

Instance terminated by LGWR, pid = 78381

 

上网搜索查明原因是控制文件损坏。

 

 

二、解决问题的过程：

1、检查是否有幸存控制文件，看能不能偷个懒。

SQL> create pfile from spfile;

 

File created.

 

SQL> exit

Disconnected

ebdbbak:/eb_db/oracle/product/10.2/db/dbs> ls

alert_ebai.log  initdw.ora    init.ora  orapwebai

hc_ebai.dat     initebai.ora  lkEBAI    spfileebai.ora

ebdbbak:/eb_db/oracle/product/10.2/db/dbs> cp spfileebai.ora spfileebai.ora.2013                                                        0514

ebdbbak:/eb_db/oracle/product/10.2/db/dbs> vi initebai.ora

注释掉控制文件，只保留一个做测试

ebdbbak:/eb_db/oracle/product/10.2/db/dbs> whoami

oracle

ebdbbak:/eb_db/oracle/product/10.2/db/dbs> pwd

/eb_db/oracle/product/10.2/db/dbs

ebdbbak:/eb_db/oracle/product/10.2/db/dbs> ls

alert_ebai.log  hc_ebai.dat  initdw.ora  initebai.ora  init.ora  lkEBAI  orapwebai  spfileebai.ora  spfileebai.ora.20130514

ebdbbak:/eb_db/oracle/product/10.2/db/dbs> sqlplus /nolog

 

SQL*Plus: Release 10.2.0.5.0 - Production on Tue May 14 08:41:22 2013

 

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

 

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup pfile='/eb_db/oracle/product/10.2/db/dbs/initebai.ora'

ORACLE instance started.

 

Total System Global Area 1.7180E+10 bytes

Fixed Size                  2126480 bytes

Variable Size            2080378224 bytes

Database Buffers         1.5049E+10 bytes

Redo Buffers               48201728 bytes

Database mounted.

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

 

 

SQL> select open_mode from v$database;

ERROR:

ORA-03114: not connected to ORACLE

 

这次换control02尝试，依然报错：

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup pfile='/eb_db/oracle/product/10.2/db/dbs/initebai.ora'

ORACLE instance started.

 

Total System Global Area 1.7180E+10 bytes

Fixed Size                  2126480 bytes

Variable Size            2080378224 bytes

Database Buffers         1.5049E+10 bytes

Redo Buffers               48201728 bytes

Database mounted.

ORA-01122: database file 1 failed verification check

ORA-01110: data file 1: '/eb_db/oracle/oradata/ebai/system01.dbf'

ORA-01207: file is more recent than control file - old control file

不但没解决问题，还把system表空间撤出来了。

 

2、重建控制文件过程

 

由于测试环境，没有控制文件备份，尝试重建控制文件。过程如下:

SQL> startup pfile='/eb_db/oracle/product/10.2/db/dbs/initebai.ora'

ORACLE instance started.

 

Total System Global Area 1.7180E+10 bytes

Fixed Size                  2126480 bytes

Variable Size            2080378224 bytes

Database Buffers         1.5049E+10 bytes

Redo Buffers               48201728 bytes

Database mounted.

ORA-01122: database file 1 failed verification check

ORA-01110: data file 1: '/eb_db/oracle/oradata/ebai/system01.dbf'

ORA-01207: file is more recent than control file - old control file

 

 

SQL> alter database backup controlfile to trace;

 

Database altered.

 

SQL> @gettrcname

SP2-0310: unable to open file "gettrcname.sql"

上网查到原因，据说这个脚本是个人写的，不是oracle程序下面的工具脚本，于是我也重建脚本如下：

SQL> host vi gettrcname.sql

 

SELECT    d.VALUE
       || '/'
       || LOWER (RTRIM (i.INSTANCE, CHR (0)))
       || '_ora_'
       || p.spid
       || '.trc' trace_file_name
  FROM (SELECT p.spid
          FROM SYS.v$mystat m, SYS.v$session s, SYS.v$process p
         WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
       (SELECT t.INSTANCE
          FROM SYS.v$thread t, SYS.v$parameter v
         WHERE v.NAME = 'thread'
           AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
       (SELECT VALUE
          FROM SYS.v$parameter
         WHERE NAME = 'user_dump_dest') d
/

 

 

SQL> @gettrcname

 

TRACE_FILE_NAME

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

/opt/oracle/admin/ebai/udump/ebai_ora_10248.trc

 

 

ebdbbak:/eb_db/oracle> sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.5.0 - Production on Tue May 14 09:48:01 2013

 

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

 

Connected to an idle instance.

 

SQL> set echo on

SQL> @createctlfile

SQL>

SQL> STARTUP NOMOUNT

ORACLE instance started.

 

Total System Global Area 1.7180E+10 bytes

Fixed Size                  2126480 bytes

Variable Size            2080378224 bytes

Database Buffers         1.5049E+10 bytes

Redo Buffers               48201728 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "EBAI" NORESETLOGS  ARCHIVELOG

  2      MAXLOGFILES 16

  3      MAXLOGMEMBERS 3

  4      MAXDATAFILES 300

  5      MAXINSTANCES 8

  6      MAXLOGHISTORY 584

  7  LOGFILE

  8    GROUP 1 (

  9      '/eb_db/oracle/redologA/redo01a.log',

 10      '/eb_db/oracle/redologB/redo01b.log'

 11    ) SIZE 500M,

 12    GROUP 2 (

 13      '/eb_db/oracle/redologA/redo02a.log',

 14      '/eb_db/oracle/redologB/redo02b.log'

 15    ) SIZE 500M,

 16    GROUP 3 (

 17      '/eb_db/oracle/redologA/redo03a.log',

 18      '/eb_db/oracle/redologB/redo03b.log'

 19    ) SIZE 500M,

 20    GROUP 4 (

 21      '/eb_db/oracle/redologA/redo04a.log',

 22      '/eb_db/oracle/redologB/redo04b.log'

 23    ) SIZE 500M

 24  -- STANDBY LOGFILE

 25  DATAFILE

 26    '/eb_db/oracle/oradata/ebai/system01.dbf',

 27    '/eb_db/oracle/oradata/ebai/undotbs01.dbf',

 28    '/eb_db/oracle/oradata/ebai/sysaux01.dbf',

 29    '/eb_db/oracle/oradata/ebai/users01.dbf',

 30    '/eb_db/oracle/oradata/ebai/users02.dbf',

 31    '/eb_db/oracle/oradata/ebai/users09.dbf',

 32    '/eb_db/oracle/oradata/ebai/users08.dbf',

 33    '/eb_db/oracle/oradata/ebai/users07.dbf',

 34    '/eb_db/oracle/oradata/ebai/users06.dbf',

 35    '/eb_db/oracle/oradata/ebai/users05.dbf',

 36    '/eb_db/oracle/oradata/ebai/users03.dbf',

 37    '/eb_db/oracle/oradata/ebai/users04.dbf',

 38    '/eb_db/oracle/oradata/ebai/undotbs02.dbf',

 39    '/eb_db/oracle/oradata/ebai/indx01.dbf',

 40    '/eb_db/oracle/oradata/ebai/indx02.dbf',

 41    '/eb_db/oracle/oradata/ebai/indx03.dbf',

 42    '/eb_db/oracle/oradata/ebai/indx04.dbf',

 43    '/eb_db/oracle/oradata/ebai/indx05.dbf'

 44  CHARACTER SET UTF8

 45  ;

 

Control file created.

 

SQL>

SQL>

SQL> RECOVER DATABASE

Media recovery complete.

SQL>

SQL> ALTER SYSTEM ARCHIVE LOG ALL;

 

System altered.

 

SQL>

SQL> ALTER DATABASE OPEN;

 

Database altered.

 

SQL>

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/eb_db/oracle/oradata/ebai/temp01.dbf' REUSE;

 

Tablespace altered.

 

SQL>

 

参考链接：http://www.eygle.com/archives/2004/10/backup_and_recreate_controlfile.html

 

 

 

___________________________________________________________________________________

版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!

Author:   laven54 (lurou)

Email:    laven54@163.com

Blog:      http://blog.csdn.net/laven54