oracle重做control,使用重建控制文件做完全恢复

环境:

OS:AIX 5.3

DB:11.2.0.1.0

1.备份控制文件和数据文件

cp *.ctl /oracle_bak/

cp *.dbf /oracle_bak/

2.删除控制文件和数据文件

[hxl:oracle:/u01/app/oracle/oradata/oracl]$rm *.ctl

[hxl:oracle:/u01/app/oracle/oradata/oracl]$ls

hxl01.dbf      redo0201.log   redo0302.log   temp01.dbf

redo0101.log   redo0202.log   sysaux01.dbf   undotbs01.dbf

redo0102.log   redo0301.log   system01.dbf   users01.dbf

[hxl:oracle:/u01/app/oracle/oradata/oracl]$rm *.dbf

3.尝试打开数据库

[hxl:oracle:/u01/app/oracle/oradata/oracl]$sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 10 21:27:23 2012

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

SQL> connect / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area  626327552 bytes

Fixed Size                  2209376 bytes

Variable Size             209717664 bytes

Database Buffers          411041792 bytes

Redo Buffers                3358720 bytes

ORA-00205: error in identifying control file, check alert log for more info

4.查看日志文件

SQL> show parameters dump

NAME                                 TYPE        VALUE

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

background_core_dump                 string      partial

background_dump_dest                 string      /u01/app/oracle/diag/rdbms/ora

cl/oracl/trace

core_dump_dest                       string      /u01/app/oracle/diag/rdbms/ora

cl/oracl/cdump

max_dump_file_size                   string      unlimited

shadow_core_dump                     string      partial

user_dump_dest                       string      /u01/app/oracle/diag/rdbms/ora

cl/oracl/trace

alert日志文件存放目录如下:

/u01/app/oracle/diag/rdbms/oracl/oracl/trace

查看日志文件

ALTER DATABASE   MOUNT

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/oracl/control03.ctl'

ORA-27037: unable to obtain file status

IBM AIX RISC System/6000 Error: 2: No such file or directory

Additional information: 3

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/oracl/control01.ctl'

ORA-27037: unable to obtain file status

IBM AIX RISC System/6000 Error: 2: No such file or directory

Additional information: 3

ORA-205 signalled during: ALTER DATABASE   MOUNT...

Sat Nov 10 21:27:36 2012

Checker run found 2 new persistent data failures

如上提示控制文件不存在

5.恢复控制文件

[hxl/root]#cp *.ctl /u01/app/oracle/oradata/oracl/

6.尝试打开数据库

SQL> alter database mount

2  /

Database altered.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/app/oracle/oradata/oracl/system01.dbf'

提示数据文件不存在

7.恢复数据文件

cp *.dbf /u01/app/oracle/oradata/oracl/

8.再次尝试打开数据库

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

9.恢复数据库

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

如上提示需要使用BACKUP CONTROLFILE选项

10.使用BACKUP CONTROLFILE选项恢复数据库

SQL> recover database using backup controlfile;

ORA-00279: change 1291380 generated at 11/10/2012 20:52:11 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_11_10/o1_mf_1_22_%u_.a

rc

ORA-00280: change 1291380 for thread 1 is in sequence #22

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log

'/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_11_10/o1_mf_1_22_%u_.

arc'

ORA-27037: unable to obtain file status

IBM AIX RISC System/6000 Error: 2: No such file or directory

Additional information: 3

ORA-00308: cannot open archived log

'/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_11_10/o1_mf_1_22_%u_.

arc'

ORA-27037: unable to obtain file status

IBM AIX RISC System/6000 Error: 2: No such file or directory

Additional information: 3

如上提示找不到seq=22的归档日志,因为seq=22是在线redo,还未归档,当然找不到.

11.使用重建控制文件的办法打开数据库

SQL>alter database backup controlfile to trace;

在/u01/app/oracle/diag/rdbms/oracl/oracl/trace目录下的trc文件中找到如下部分,注意这里选择NORESETLOGS做完全恢复

SQL>shutdown immediate

SQL>startup nomount

SQL>set sqlblanklines on

SQL>CREATE CONTROLFILE REUSE DATABASE "ORACL" NORESETLOGS  ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 (

'/u01/app/oracle/oradata/oracl/redo0101.log',

'/u01/app/oracle/oradata/oracl/redo0102.log'

) SIZE 50M BLOCKSIZE 512,

GROUP 2 (

'/u01/app/oracle/oradata/oracl/redo0201.log',

'/u01/app/oracle/oradata/oracl/redo0202.log'

) SIZE 50M BLOCKSIZE 512,

GROUP 3 (

'/u01/app/oracle/oradata/oracl/redo0301.log',

'/u01/app/oracle/oradata/oracl/redo0302.log'

) SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/oracl/system01.dbf',

'/u01/app/oracle/oradata/oracl/sysaux01.dbf',

'/u01/app/oracle/oradata/oracl/undotbs01.dbf',

'/u01/app/oracle/oradata/oracl/users01.dbf',

'/u01/app/oracle/oradata/oracl/hxl01.dbf'

CHARACTER SET ZHS16GBK

;

12.打开数据库

SQL> alter database open;

Database altered.

-- The End --