如果没有及时的发现已经丢失了所有的控制文件，这里重建控制文件需要知道一些额外的信息，包括redolog的位置、数据文件的位置还有字符集。（这里也可以使用snapshot控制文件来生成trace文件）
sys@BKT> shutdown immediate
Database closed.
ORA-00210: cannot open the specifiedcontrol file
ORA-00202: control file:'/u02/oradat/bkt/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file ordirectory
Additional information: 3
sys@BKT> shutdown abort ;
sys@BKT> alter database mount ;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying controlfile, check alert log for more info
如果这里重做日志没有损坏，则可以以noresetlogs的方式重建控制文件
CREATE CONTROLFILE REUSE DATABASE"BKT" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u02/oradat/bkt/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u02/oradat/bkt/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u02/oradat/bkt/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u02/oradat/bkt/system01.dbf',
'/u02/oradat/bkt/sysaux01.dbf',
'/u02/oradat/bkt/undotbs01.dbf',
'/u02/oradat/bkt/test1_01.dbf',
'/u02/oradat/bkt/users01.dbf',
'/u02/oradat/bkt/example01.dbf'
CHARACTER SET ZHS16GBK
;
这里改变了数据文件的位置
sys@BKT> CREATE CONTROLFILE REUSEDATABASE "BKT" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1'/u02/oradat/bkt/redo01.log' SIZE 50MBLOCKSIZE 512,
9 GROUP 2'/u02/oradat/bkt/redo02.log' SIZE 50MBLOCKSIZE 512,
10 GROUP 3 '/u02/oradat/bkt/redo03.log' SIZE 50M BLOCKSIZE 512
11 --STANDBY LOGFILE
12 DATAFILE
13 '/u02/oradat/bkt/system01.dbf',
14 '/u02/oradat/bkt/sysaux01.dbf',
15 '/u02/oradat/bkt/undotbs01.dbf',
16 '/u02/oradat/bkt/test1_01.dbf',
17 '/u02/oradat/bkt/users01.dbf',
18 '/u02/oradat/bkt/example01.dbf'
19 CHARACTER SET ZHS16GBK
20 ;
Control file created.
sys@BKT> select status from v$instance ;
STATUS
------------
MOUNTED
也可能需要手动的recover一下
recover database
sys@BKT> alter database open ;
Database altered.
添加临时文件
sys@BKT> ALTER TABLESPACE TEMP ADDTEMPFILE '/u02/oradat/bkt/temp01.dbf';
RMAN> list backup ;
specification does not match any backup inthe repository
RMAN> catalog db_recovery_file_dest ;
RMAN> list backup ;
List of Backup Sets
===================
BS Key Type LV Size Device TypeElapsed Time Completion Time
------- ---- -- ---------- ----------------------- ---------------
1 Full 1.07G DISK 00:00:00 18-APR-13
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20130418T223159
Piece Name:/u02/flash_recovery_area/BKT/backupset/2013_04_18/o1_mf_nnndf_TAG20130418T223159_8q00yzs1_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN CkpTime Name
---- -- ---- ---------- --------- ----
1 Full 1095500 18-APR-13 /u02/oradat/bkt/system01.dbf
2 Full 1095500 18-APR-13 /u02/oradat/bkt/sysaux01.dbf
3 Full 1095500 18-APR-13 /u02/oradat/bkt/undotbs01.dbf
4 Full 1095500 18-APR-13 /u02/oradat/bkt/users01.dbf
5 Full 1095500 18-APR-13 /u02/oradat/bkt/example01.dbf
BS Key Type LV Size Device TypeElapsed Time Completion Time
------- ---- -- ---------- ----------------------- ---------------
2 Full 9.36M DISK 00:00:00 18-APR-13
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20130418T223159
Piece Name:/u02/flash_recovery_area/BKT/backupset/2013_04_18/o1_mf_ncsnf_TAG20130418T223159_8q016gmf_.bkp
SPFILE Included: Modification time: 18-APR-13
SPFILE db_unique_name: BKT
Control File Included: Ckp SCN: 1095500 Ckp time: 18-APR-13
RMAN> list incarnation
2> ;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ------------------- ---------- ----------
1 1 BKT 536381303 CURRENT 995548 18-APR-13
RMAN>
Continue applying redo log files until thelast log has been applied to the restored
data files, then cancel recovery byexecuting the following command:
CANCEL
The database indicates whether recovery issuccessful. If you cancel before all the
data files have been recovered to a consistentSCN and then try to open the
database, then you get an ORA-1113error ifmore recovery is necessary. You can
query V$RECOVER_FILEto determine whethermore recovery is needed, or if a
backup of a data file was notrestoredbefore starting incomplete recovery.
无备份使用snap控制文件RMAN> show all
2> ;
using target database control file insteadof recovery catalog
RMAN configuration parameters for databasewith db_unique_name BKT are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;# default
CONFIGURE BACKUP OPTIMIZATION OFF; #default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; #default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; #default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FORDEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICETYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FORDEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; #default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; #default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' ASOF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TONONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO'/u01/apps/oracle/product/11gr2/db_1/dbs/snapcf_bkt.f'; # default
删除后alert日志报错：
Errors in file/u01/apps/oracle/diag/rdbms/bkt/bkt/trace/bkt_m000_15066.trc:
ORA-00210: cannot open the specifiedcontrol file
ORA-00202: control file:'/u02/oradat/bkt/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file ordirectory
Additional information: 3
sys@BKT> shutdown immediate
Database closed.
ORA-00210: cannot open the specifiedcontrol file
ORA-00202: control file:'/u02/oradat/bkt/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file ordirectory
Additional information: 3
sys@BKT> shutdown abort ;
ORACLE instance shut down.
sys@BKT> startup
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 322964784 bytes
Database Buffers 83886080 bytes
Redo Buffers 8466432 bytes
ORA-00205: error in identifying controlfile, check alert log for more info
[root@master ~]# cp/u01/apps/oracle/product/11gr2/db_1/dbs/snapcf_bkt.f/u02/oradat/bkt/control01.ctl
[root@master ~]# cp/u01/apps/oracle/product/11gr2/db_1/dbs/snapcf_bkt.f/u02/flash_recovery_area/bkt/control02.ctl
[root@master ~]# chown oracle:oinstall/u02/oradat/bkt/control01.ctl
[root@master ~]# chown oracle:oinstall/u02/flash_recovery_area/bkt/control02.ctl
sys@BKT> recover database using backupcontrolfile ;
ORA-00279: change 1266893 generated at05/09/2013 03:27:56 needed for thread 1
ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_08/o1_mf_1_28_%u_.arc
ORA-00280: change 1266893 for thread 1 isin sequence #28
Specify log: {<RET>=suggested |filename | AUTO | CANCEL}
因为是老的controlfile，所以sequence都是老旧的，
dle> select group#,sequence#,status fromv$log ;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 25 INACTIVE
3 24 INACTIVE
2 26 CURRENT
idle> archive log list ;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 24
Next log sequence to archive 26
Current log sequence 26
查看相应的目录
[oracle@master ~]$ ll/u02/flash_recovery_area/BKT/archivelog/2013_05_08/
total 62224
-rw-r----- 1 oracle oinstall 177152 May 7 12:10 o1_mf_1_10_8rlng20m_.arc
-rw-r----- 1 oracle oinstall 1024 May 7 12:12 o1_mf_1_11_8rlnkd1g_.arc
-rw-r----- 1 oracle oinstall 1024 May 7 12:12 o1_mf_1_12_8rlnkfdr_.arc
-rw-r----- 1 oracle oinstall 10752 May 7 12:17 o1_mf_1_13_8rlnt65r_.arc
-rw-r----- 1 oracle oinstall 1024 May 7 12:17 o1_mf_1_14_8rlnt6r7_.arc
-rw-r----- 1 oracle oinstall 1536 May 7 12:17 o1_mf_1_15_8rlnt7b1_.arc
-rw-r----- 1 oracle oinstall 1024 May 7 12:17 o1_mf_1_16_8rlnt7x7_.arc
-rw-r----- 1 oracle oinstall 1024 May 7 12:17 o1_mf_1_17_8rlnt8g7_.arc
-rw-r----- 1 oracle oinstall 1024 May 7 12:17 o1_mf_1_18_8rlnt8wk_.arc
-rw-r----- 1 oracle oinstall 1024 May 7 12:17 o1_mf_1_19_8rlnt9bn_.arc
-rw-r----- 1 oracle oinstall 1024 May 7 12:17 o1_mf_1_20_8rlnt9p8_.arc
-rw-r----- 1 oracle oinstall 184832 May 7 12:30 o1_mf_1_21_8rlokyy2_.arc
-rw-r----- 1 oracle oinstall 259584 May 7 12:55 o1_mf_1_22_8rlq1j4j_.arc
-rw-r----- 1 oracle oinstall 1024 May 7 12:55 o1_mf_1_23_8rlq1tck_.arc
-rw-r----- 1 oracle oinstall 1024 May 7 13:29 o1_mf_1_23_8rls13lv_.arc
-rw-r----- 1 oracle oinstall 2048 May 7 12:56 o1_mf_1_24_8rlq3pxx_.arc
-rw-r----- 1 oracle oinstall 2048 May 7 13:29 o1_mf_1_24_8rls13lj_.arc
-rw-r----- 1 oracle oinstall 243712 May 7 13:29 o1_mf_1_25_8rls13pd_.arc
-rw-r----- 1 oracle oinstall 42203648May 8 07:01 o1_mf_1_26_8rnpoljr_.arc
-rw-r----- 1 oracle oinstall 10391552May 8 12:28 o1_mf_1_27_8ro9sxgq_.arc
-rw-r----- 1 oracle oinstall 10078720May 7 11:58 o1_mf_1_9_8rlmpf3o_.arc
idle> select * from v$logfile ;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------------------------------------- ---
1 STALE ONLINE /u02/oradat/bkt/redo01.log NO
3 STALE ONLINE /u02/oradat/bkt/redo03.log NO
2 ONLINE /u02/oradat/bkt/redo02.log NO
顺序3、1、2
sys@BKT> recover database using backupcontrolfile ;
ORA-00279: change 1266893 generated at05/09/2013 03:27:56 needed for thread 1
ORA-00289: suggestion :/u02/flash_recovery_area/BKT/archivelog/2013_05_08/o1_mf_1_28_%u_.arc
ORA-00280: change 1266893 for thread 1 isin sequence #28
Specify log: {<RET>=suggested |filename | AUTO | CANCEL}
/u02/oradat/bkt/redo03.log
ORA-00310: archived log contains sequence27; sequence 28 required
ORA-00334: archived log:'/u02/oradat/bkt/redo03.log'
这里必须resetlogs
sys@BKT> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option fordatabase open
sys@BKT> alter database open resetlogs ;
sys@BKT> ALTER TABLESPACE TEMP ADDTEMPFILE '/u02/oradat/bkt/temp01.dbf' REUSE;
Tablespace altered.
RMAN> catalog db_recovery_file_dest ;
using target database control file insteadof recovery catalog
searching for all files in the recoveryarea