通过重建控制文件的方法恢复数据库

今天配置dataguard的时候，一不小心把主库的控制文件全删了，主库是集团的测试库，所以心里比较慌。静下心来在网上找了一些资料之后，发现可以通过重建控制文件的方法来恢复数据库，就做了一下测试。

第一种方法：通过重建控制文件的方法恢复数据库。

找个能启动的数据库，执行如下语句：

alter database backup controlfile to trace;

oracle11g会在$ORACLE_BASE /diag/rdbms/$ORACLE_SID/trace目录下，找到相应的文件，打开，把创建控制文件的脚本拷出来：

CREATE CONTROLFILE REUSE DATABASE "YHDATA2" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 584
LOGFILE
  GROUP 1 '/data/yhdata2/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/data/yhdata2/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/data/yhdata2/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
--   GROUP 4 '/data/yhdata2/standbyredo04.log'  SIZE 50M BLOCKSIZE 512,
--   GROUP 5 '/data/yhdata2/standbyredo05.log'  SIZE 50M BLOCKSIZE 512,
--   GROUP 6 '/data/yhdata2/standbyredo06.log'  SIZE 50M BLOCKSIZE 512,
--   GROUP 7 '/data/yhdata2/standbyredo07.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/data/yhdata2/system01.dbf',
  '/data/yhdata2/sysaux01.dbf',
  '/data/yhdata2/undotbs01.dbf',
  '/data/yhdata2/users01.dbf',
  '/data/BPS_01.dbf',
  '/data/I_BPS_01.dbf',
  '/data/RQREPORT_SPACE_01.dbf',
  '/data/I_RQREPORT_01.dbf',
  '/data/D_GDGS_01.dbf',
  '/data/I_GDGS_01.dbf',
  '/data/D_MEMS_01.dbf',
  '/data/D_MEMS_02.dbf',
  '/data/I_MEMS_01.dbf',
  '/data/D_PTCMS_01.dbf',
  '/data/D_PTCMS_02.dbf',
  '/data/I_PTCMS_01.dbf',
  '/data/D_BCTCMS_01.dbf',
  '/data/D_BCTCMS_02.dbf',
  '/data/I_BCTCMS_01.dbf',
  '/data/D_GDGS_02.dbf'
CHARACTER SET ZHS16GBK;

将里面的日志文件，数据文件改成相对应的路径。先把数据库起到nomount状态，在sqlplus里执行该语句，执行成功之后，再对数据库做一次恢复recover database。

第二种方法：通过rman恢复数据库(前提是使用rman对数据库做过备份)

查看rman备份的控制文件路径：
RMAN> list backup of controlfile;

[oracle@SYS-TEST-008 yhdata2]$ rman target/


Recovery Manager: Release 11.2.0.1.0 - Production on Wed May 25 20:50:58 2016


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


connected to target database (not started)


RMAN> startup nomount;


Oracle instance started


Total System Global Area    6680915968 bytes


Fixed Size                     2213936 bytes
Variable Size               4630513616 bytes
Database Buffers            2013265920 bytes
Redo Buffers                  34922496 bytes


RMAN> restore controlfile from '/data/db_backup/full1ir6dsmg_50_1';


Starting restore at 25-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=189 device type=DISK


channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/data/yhdata2/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/yhdata2/control02.ctl
Finished restore at 25-MAY-16


RMAN> alter database mount;


database mounted
released channel: ORA_DISK_1


RMAN> restore database;


Starting restore at 25-MAY-16
Starting implicit crosscheck backup at 25-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=189 device type=DISK
Crosschecked 46 objects
Finished implicit crosscheck backup at 25-MAY-16


Starting implicit crosscheck copy at 25-MAY-16
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 25-MAY-16


searching for all files in the recovery area
cataloging files...
no files cataloged


using channel ORA_DISK_1


channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /data/yhdata2/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /data/yhdata2/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /data/yhdata2/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /data/yhdata2/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /data/BPS_01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /data/I_BPS_01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /data/RQREPORT_SPACE_01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /data/I_RQREPORT_01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /data/D_GDGS_01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /data/I_GDGS_01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /data/D_MEMS_01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /data/D_MEMS_02.dbf
channel ORA_DISK_1: restoring datafile 00013 to /data/I_MEMS_01.dbf
channel ORA_DISK_1: restoring datafile 00014 to /data/D_PTCMS_01.dbf
channel ORA_DISK_1: restoring datafile 00015 to /data/D_PTCMS_02.dbf
channel ORA_DISK_1: restoring datafile 00016 to /data/I_PTCMS_01.dbf
channel ORA_DISK_1: restoring datafile 00017 to /data/D_BCTCMS_01.dbf
channel ORA_DISK_1: restoring datafile 00018 to /data/D_BCTCMS_02.dbf
channel ORA_DISK_1: restoring datafile 00019 to /data/I_BCTCMS_01.dbf
channel ORA_DISK_1: restoring datafile 00020 to /data/D_GDGS_02.dbf
channel ORA_DISK_1: reading from backup piece /data/db_backup/full1hr6dq7i_49_1
channel ORA_DISK_1: piece handle=/data/db_backup/full1hr6dq7i_49_1 tag=TAG20160524T193929
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:27:25
Finished restore at 25-MAY-16


RMAN> recover database;


Starting recover at 25-MAY-16
using channel ORA_DISK_1


starting media recovery


archived log for thread 1 with sequence 1966 is already on disk as file /data/yhdata2/redo01.log
archived log for thread 1 with sequence 1967 is already on disk as file /data/yhdata2/redo02.log
archived log for thread 1 with sequence 1968 is already on disk as file /data/yhdata2/redo03.log
archived log file name=/u01/app/oracle/product/11.2.0/dbhome/dbs/archive/yhdata2/1_1953_902252612.arc thread=1 sequence=1953
archived log file name=/u01/app/oracle/product/11.2.0/dbhome/dbs/archive/yhdata2/1_1954_902252612.arc thread=1 sequence=1954
archived log file name=/u01/app/oracle/product/11.2.0/dbhome/dbs/archive/yhdata2/1_1955_902252612.arc thread=1 sequence=1955
archived log file name=/u01/app/oracle/product/11.2.0/dbhome/dbs/archive/yhdata2/1_1956_902252612.arc thread=1 sequence=1956
archived log file name=/u01/app/oracle/product/11.2.0/dbhome/dbs/archive/yhdata2/1_1957_902252612.arc thread=1 sequence=1957
archived log file name=/u01/app/oracle/product/11.2.0/dbhome/dbs/archive/yhdata2/1_1958_902252612.arc thread=1 sequence=1958
archived log file name=/u01/app/oracle/product/11.2.0/dbhome/dbs/archive/yhdata2/1_1959_902252612.arc thread=1 sequence=1959
archived log file name=/u01/app/oracle/product/11.2.0/dbhome/dbs/archive/yhdata2/1_1960_902252612.arc thread=1 sequence=1960
archived log file name=/u01/app/oracle/product/11.2.0/dbhome/dbs/archive/yhdata2/1_1961_902252612.arc thread=1 sequence=1961
archived log file name=/u01/app/oracle/product/11.2.0/dbhome/dbs/archive/yhdata2/1_1962_902252612.arc thread=1 sequence=1962
archived log file name=/u01/app/oracle/product/11.2.0/dbhome/dbs/archive/yhdata2/1_1963_902252612.arc thread=1 sequence=1963
archived log file name=/u01/app/oracle/product/11.2.0/dbhome/dbs/archive/yhdata2/1_1964_902252612.arc thread=1 sequence=1964
archived log file name=/u01/app/oracle/product/11.2.0/dbhome/dbs/archive/yhdata2/1_1965_902252612.arc thread=1 sequence=1965
archived log file name=/data/yhdata2/redo01.log thread=1 sequence=1966
archived log file name=/data/yhdata2/redo02.log thread=1 sequence=1967
archived log file name=/data/yhdata2/redo03.log thread=1 sequence=1968
media recovery complete, elapsed time: 00:08:53
Finished recover at 25-MAY-16


RMAN> alter database open resetlogs;


database opened


RMAN>

这样数据库就恢复成功了。