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

环境:

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 --