Oracle数据库可以使用trace方法生成控制文件的创建语句，在极端情况下可以派上用场。例如在控制文件全部丢失的情况下，就可以使用这种方法来完成数据库故障的恢复。模拟一下这个故障和恢复场景。
1.巧妇难做无米之炊！必须先有备份才能谈恢复，因此我们首先需要生成控制文件的trace文件。
sys@secooler> alter database backup controlfile to trace;
Database altered.
2.查看备份生成的trace文件，内容非常丰富，很具体。值得认真探究。
生成trace文件的目录是udump。展示一下它的全貌。
$ cat /oracle/app/oracle/admin/secooler/udump/secooler_ora_3536.trc
/oracle/app/oracle/admin/secooler/udump/secooler_ora_3536.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
ORACLE_HOME = /oracle/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: secDB
Release: 2.6.18-128.el5
Version: #1 SMP Wed Dec 17 11:41:38 EST 2008
Machine: x86_64
Instance name: secooler
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 3536, image: oracle@secDB (TNS V1-V3)
*** SERVICE NAME:(SYS$USERS) 2009-12-16 22:53:12.249
*** SESSION ID:(141.72) 2009-12-16 22:53:12.249
*** 2009-12-16 22:53:12.249
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="secooler"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=2
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_10=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script. file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SECOOLER" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/oradata/secooler/redo01.log' SIZE 200M,
GROUP 2 '/oracle/oradata/secooler/redo02.log' SIZE 200M,
GROUP 3 '/oracle/oradata/secooler/redo03.log' SIZE 200M
-- STANDBY LOGFILE
DATAFILE
'/oracle/oradata/secooler/system01.dbf',
'/oracle/oradata/secooler/undotbs01.dbf',
'/oracle/oradata/secooler/sysaux01.dbf',
'/oracle/oradata/secooler/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/orabackup/%F''');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/oracle/app/oracle/flash_recovery_area/SECOOLER/archivelog/2009_12_16/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/secooler/temp01.dbf'
SIZE 568328192 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SECOOLER" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/oradata/secooler/redo01.log' SIZE 200M,
GROUP 2 '/oracle/oradata/secooler/redo02.log' SIZE 200M,
GROUP 3 '/oracle/oradata/secooler/redo03.log' SIZE 200M
-- STANDBY LOGFILE
DATAFILE
'/oracle/oradata/secooler/system01.dbf',
'/oracle/oradata/secooler/undotbs01.dbf',
'/oracle/oradata/secooler/sysaux01.dbf',
'/oracle/oradata/secooler/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/orabackup/%F''');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/oracle/app/oracle/flash_recovery_area/SECOOLER/archivelog/2009_12_16/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/secooler/temp01.dbf'
SIZE 568328192 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
3.获得控制文件信息
sys@secooler> select * from v$controlfile;
STATUS NAME IS_RECOVE BLOCK_SIZE FILE_SIZE_BLKS
------ ---------------------------------------- --------- ---------- --------------
/oracle/oradata/secooler/control01.ctl NO 16384 430
/oracle/oradata/secooler/control02.ctl NO 16384 430
/oracle/oradata/secooler/control03.ctl NO 16384 430
4.使用编辑工具（例如Linux上的vi编辑器等）破坏所有控制文件。
5.此时如果使用rman尝试连接该数据库，就会收到有关控制文件损坏的报错。
secooler@secDB /orabackup$ rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Wed Dec 16 23:06:35 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06003: ORACLE error from target database:
ORA-00204: error in reading (block 1, # blocks 1) of control file
ORA-00202: control file: '/oracle/oradata/secooler/control01.ctl'
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Additional information: 4
Additional information: 1
6.使用“SHUTDOWN ABORT;”命令模拟数据库服务器的异常断电
sys@secooler> shutdown abort
ORACLE instance shut down.
7.此时尝试重新启动数据库，一定会收到有关控制文件损坏的信息。
1）启动数据过程中显示的报错信息
sys@secooler> startup;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2074112 bytes
Variable Size 486541824 bytes
Database Buffers 1644167168 bytes
Redo Buffers 14700544 bytes
ORA-00205: error in identifying control file, check alert log for more info
2）alert文件中也会记录相关的错误信息，摘录如下。
Wed Dec 16 23:08:20 2009
ALTER DATABASE MOUNT
Wed Dec 16 23:08:20 2009
ORA-00202: control file: '/oracle/oradata/secooler/control01.ctl'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 17: File exists
Additional information: 1
Wed Dec 16 23:08:20 2009
ORA-205 signalled during: ALTER DATABASE MOUNT...
8.使用trace文件重建控制文件的方法恢复数据库
1）先关闭数据库
sys@secooler> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
2）启动数据库到nomount状态
sys@secooler> startup nomount;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2074112 bytes
Variable Size 486541824 bytes
Database Buffers 1644167168 bytes
Redo Buffers 14700544 bytes
3）使用生成的trace文件中的第一种“NORESETLOGS case”方法重建控制文件
sys@secooler> CREATE CONTROLFILE REUSE DATABASE "SECOOLER" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/oracle/oradata/secooler/redo01.log' SIZE 200M,
9 GROUP 2 '/oracle/oradata/secooler/redo02.log' SIZE 200M,
10 GROUP 3 '/oracle/oradata/secooler/redo03.log' SIZE 200M
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/oracle/oradata/secooler/system01.dbf',
14 '/oracle/oradata/secooler/undotbs01.dbf',
15 '/oracle/oradata/secooler/sysaux01.dbf',
16 '/oracle/oradata/secooler/users01.dbf'
17 CHARACTER SET WE8ISO8859P1
18 ;
Control file created.
4）如果此时open数据库的话是不可以的，想想为什么？聪明，原因就在于这是一场“异常断电”导致的故障，需要恢复。
sys@secooler> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/oradata/secooler/system01.dbf'
5）同样的道理，即使我们这里关闭数据库再使用startup方法打开数据一样是不可以的，需要恢复。
sys@secooler> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
sys@secooler> startup;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2074112 bytes
Variable Size 486541824 bytes
Database Buffers 1644167168 bytes
Redo Buffers 14700544 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/oradata/secooler/system01.dbf'
6）既然需要恢复，别无选择，恢复之！
sys@secooler> recover database;
Media recovery complete.
7）OK，到此，一个鲜活的数据库打开在我们的面前。但是请注意，整个恢复工作并没有全部完成。
sys@secooler> alter database open;
Database altered.
8）数据库都已经open了，还有哪些工作需要做呢？
如果不清楚，可以从数据库的alert文件中得到提示。
Wed Dec 16 23:30:32 2009
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done using the SQL statement:
ALTER TABLESPACE ADD TEMPFILE
Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is WE8ISO8859P1
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
then disabled.
**********************************************************
OK，从alert文件中可以得到两个警告信息，第一个警告信息提示，因为重建控制文件导致临时表空间中没有对应文件，需要为临时表空间手工添加文件；第二个警告信息提示我们需要使用RMAN的“CATALOG RECOVERY AREA”命令将RMAN的备份集注册到控制文件。
一一处理之。
（1）根据临时表空间大小的需求为其添加文件，这里使用trace文件中提示的信息完成临时文件的添加。
sys@secooler> ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/secooler/temp01.dbf' SIZE 568328192 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
（2）使用“CATALOG RECOVERY AREA”命令将RMAN的备份集注册到控制文件
RMAN> CATALOG RECOVERY AREA;
using target database control file instead of recovery catalog
searching for all files in the recovery area
List of Files Unknown to the Database
=====================================
File Name: /oracle/app/oracle/flash_recovery_area/SECOOLER/backupset/2009_12_16/o1_mf_nnndf_TAG20091216T165421_5lk81y4j_.bkp
File Name: /oracle/app/oracle/flash_recovery_area/SECOOLER/backupset/2009_12_16/o1_mf_ncnnf_TAG20091216T223431_5lkvzqp6_.bkp
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oracle/app/oracle/flash_recovery_area/SECOOLER/backupset/2009_12_16/o1_mf_nnndf_TAG20091216T165421_5lk81y4j_.bkp
File Name: /oracle/app/oracle/flash_recovery_area/SECOOLER/backupset/2009_12_16/o1_mf_ncnnf_TAG20091216T223431_5lkvzqp6_.bkp
9）查缺补漏
在生成trace文件中记载了恢复RMAN参数的信息，不妨一并调整之。
sys@secooler> VARIABLE RECNO NUMBER;
sys@secooler> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
PL/SQL procedure successfully completed.
sys@secooler> VARIABLE RECNO NUMBER;
sys@secooler> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/orabackup/%F''');
PL/SQL procedure successfully completed.
9.在整个数据恢复完成之后，本着专业的精神，最好对数据库进行一次全备份（方法随意）。
RMAN> backup database;
10.小结
备份无比重要！设想，如果没有事先备份好的trace文件作为参考，手工编写出控制文件的创建语句过程将是比较漫长的，而且极其容易出错，因此“备份在手，恢复无忧”。
恢复过程中不要放过任何蛛丝马迹，从各种提示信息中找寻恢复方法。不抛弃，不放弃！
Good luck.
secooler
09.12.16
-- The End --
1.巧妇难做无米之炊！必须先有备份才能谈恢复，因此我们首先需要生成控制文件的trace文件。
sys@secooler> alter database backup controlfile to trace;
Database altered.
2.查看备份生成的trace文件，内容非常丰富，很具体。值得认真探究。
生成trace文件的目录是udump。展示一下它的全貌。
$ cat /oracle/app/oracle/admin/secooler/udump/secooler_ora_3536.trc
/oracle/app/oracle/admin/secooler/udump/secooler_ora_3536.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
ORACLE_HOME = /oracle/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: secDB
Release: 2.6.18-128.el5
Version: #1 SMP Wed Dec 17 11:41:38 EST 2008
Machine: x86_64
Instance name: secooler
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 3536, image: oracle@secDB (TNS V1-V3)
*** SERVICE NAME:(SYS$USERS) 2009-12-16 22:53:12.249
*** SESSION ID:(141.72) 2009-12-16 22:53:12.249
*** 2009-12-16 22:53:12.249
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="secooler"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=2
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_10=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script. file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SECOOLER" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/oradata/secooler/redo01.log' SIZE 200M,
GROUP 2 '/oracle/oradata/secooler/redo02.log' SIZE 200M,
GROUP 3 '/oracle/oradata/secooler/redo03.log' SIZE 200M
-- STANDBY LOGFILE
DATAFILE
'/oracle/oradata/secooler/system01.dbf',
'/oracle/oradata/secooler/undotbs01.dbf',
'/oracle/oradata/secooler/sysaux01.dbf',
'/oracle/oradata/secooler/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/orabackup/%F''');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/oracle/app/oracle/flash_recovery_area/SECOOLER/archivelog/2009_12_16/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/secooler/temp01.dbf'
SIZE 568328192 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SECOOLER" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/oradata/secooler/redo01.log' SIZE 200M,
GROUP 2 '/oracle/oradata/secooler/redo02.log' SIZE 200M,
GROUP 3 '/oracle/oradata/secooler/redo03.log' SIZE 200M
-- STANDBY LOGFILE
DATAFILE
'/oracle/oradata/secooler/system01.dbf',
'/oracle/oradata/secooler/undotbs01.dbf',
'/oracle/oradata/secooler/sysaux01.dbf',
'/oracle/oradata/secooler/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/orabackup/%F''');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/oracle/app/oracle/flash_recovery_area/SECOOLER/archivelog/2009_12_16/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/secooler/temp01.dbf'
SIZE 568328192 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
3.获得控制文件信息
sys@secooler> select * from v$controlfile;
STATUS NAME IS_RECOVE BLOCK_SIZE FILE_SIZE_BLKS
------ ---------------------------------------- --------- ---------- --------------
/oracle/oradata/secooler/control01.ctl NO 16384 430
/oracle/oradata/secooler/control02.ctl NO 16384 430
/oracle/oradata/secooler/control03.ctl NO 16384 430
4.使用编辑工具（例如Linux上的vi编辑器等）破坏所有控制文件。
5.此时如果使用rman尝试连接该数据库，就会收到有关控制文件损坏的报错。
secooler@secDB /orabackup$ rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Wed Dec 16 23:06:35 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06003: ORACLE error from target database:
ORA-00204: error in reading (block 1, # blocks 1) of control file
ORA-00202: control file: '/oracle/oradata/secooler/control01.ctl'
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Additional information: 4
Additional information: 1
6.使用“SHUTDOWN ABORT;”命令模拟数据库服务器的异常断电
sys@secooler> shutdown abort
ORACLE instance shut down.
7.此时尝试重新启动数据库，一定会收到有关控制文件损坏的信息。
1）启动数据过程中显示的报错信息
sys@secooler> startup;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2074112 bytes
Variable Size 486541824 bytes
Database Buffers 1644167168 bytes
Redo Buffers 14700544 bytes
ORA-00205: error in identifying control file, check alert log for more info
2）alert文件中也会记录相关的错误信息，摘录如下。
Wed Dec 16 23:08:20 2009
ALTER DATABASE MOUNT
Wed Dec 16 23:08:20 2009
ORA-00202: control file: '/oracle/oradata/secooler/control01.ctl'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 17: File exists
Additional information: 1
Wed Dec 16 23:08:20 2009
ORA-205 signalled during: ALTER DATABASE MOUNT...
8.使用trace文件重建控制文件的方法恢复数据库
1）先关闭数据库
sys@secooler> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
2）启动数据库到nomount状态
sys@secooler> startup nomount;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2074112 bytes
Variable Size 486541824 bytes
Database Buffers 1644167168 bytes
Redo Buffers 14700544 bytes
3）使用生成的trace文件中的第一种“NORESETLOGS case”方法重建控制文件
sys@secooler> CREATE CONTROLFILE REUSE DATABASE "SECOOLER" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/oracle/oradata/secooler/redo01.log' SIZE 200M,
9 GROUP 2 '/oracle/oradata/secooler/redo02.log' SIZE 200M,
10 GROUP 3 '/oracle/oradata/secooler/redo03.log' SIZE 200M
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/oracle/oradata/secooler/system01.dbf',
14 '/oracle/oradata/secooler/undotbs01.dbf',
15 '/oracle/oradata/secooler/sysaux01.dbf',
16 '/oracle/oradata/secooler/users01.dbf'
17 CHARACTER SET WE8ISO8859P1
18 ;
Control file created.
4）如果此时open数据库的话是不可以的，想想为什么？聪明，原因就在于这是一场“异常断电”导致的故障，需要恢复。
sys@secooler> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/oradata/secooler/system01.dbf'
5）同样的道理，即使我们这里关闭数据库再使用startup方法打开数据一样是不可以的，需要恢复。
sys@secooler> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
sys@secooler> startup;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2074112 bytes
Variable Size 486541824 bytes
Database Buffers 1644167168 bytes
Redo Buffers 14700544 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/oradata/secooler/system01.dbf'
6）既然需要恢复，别无选择，恢复之！
sys@secooler> recover database;
Media recovery complete.
7）OK，到此，一个鲜活的数据库打开在我们的面前。但是请注意，整个恢复工作并没有全部完成。
sys@secooler> alter database open;
Database altered.
8）数据库都已经open了，还有哪些工作需要做呢？
如果不清楚，可以从数据库的alert文件中得到提示。
Wed Dec 16 23:30:32 2009
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done using the SQL statement:
ALTER TABLESPACE
Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is WE8ISO8859P1
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
then disabled.
**********************************************************
OK，从alert文件中可以得到两个警告信息，第一个警告信息提示，因为重建控制文件导致临时表空间中没有对应文件，需要为临时表空间手工添加文件；第二个警告信息提示我们需要使用RMAN的“CATALOG RECOVERY AREA”命令将RMAN的备份集注册到控制文件。
一一处理之。
（1）根据临时表空间大小的需求为其添加文件，这里使用trace文件中提示的信息完成临时文件的添加。
sys@secooler> ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/secooler/temp01.dbf' SIZE 568328192 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
（2）使用“CATALOG RECOVERY AREA”命令将RMAN的备份集注册到控制文件
RMAN> CATALOG RECOVERY AREA;
using target database control file instead of recovery catalog
searching for all files in the recovery area
List of Files Unknown to the Database
=====================================
File Name: /oracle/app/oracle/flash_recovery_area/SECOOLER/backupset/2009_12_16/o1_mf_nnndf_TAG20091216T165421_5lk81y4j_.bkp
File Name: /oracle/app/oracle/flash_recovery_area/SECOOLER/backupset/2009_12_16/o1_mf_ncnnf_TAG20091216T223431_5lkvzqp6_.bkp
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oracle/app/oracle/flash_recovery_area/SECOOLER/backupset/2009_12_16/o1_mf_nnndf_TAG20091216T165421_5lk81y4j_.bkp
File Name: /oracle/app/oracle/flash_recovery_area/SECOOLER/backupset/2009_12_16/o1_mf_ncnnf_TAG20091216T223431_5lkvzqp6_.bkp
9）查缺补漏
在生成trace文件中记载了恢复RMAN参数的信息，不妨一并调整之。
sys@secooler> VARIABLE RECNO NUMBER;
sys@secooler> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
PL/SQL procedure successfully completed.
sys@secooler> VARIABLE RECNO NUMBER;
sys@secooler> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/orabackup/%F''');
PL/SQL procedure successfully completed.
9.在整个数据恢复完成之后，本着专业的精神，最好对数据库进行一次全备份（方法随意）。
RMAN> backup database;
10.小结
备份无比重要！设想，如果没有事先备份好的trace文件作为参考，手工编写出控制文件的创建语句过程将是比较漫长的，而且极其容易出错，因此“备份在手，恢复无忧”。
恢复过程中不要放过任何蛛丝马迹，从各种提示信息中找寻恢复方法。不抛弃，不放弃！
Good luck.
secooler
09.12.16
-- The End --
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/70034820/viewspace-2993888/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- 中国OCM联盟隆重落户ITPUB社区，百位OCM已入驻，欢迎更多的OCM加入我们哦！
- OCMU联盟-一个有温度的联盟
- 丢掉盔甲，我们依然是骄傲的骑士——Secooler“三问四看”话认证培训
- 【数据安全】一次惊心动魄的ASM磁盘头损坏故障处理过程带来的深思
- 【参数】恢复db_recovery_file_dest_size参数为默认值“0”方法
- 【如何做快乐的DBA】ITPUB社区第一期博客专家专题“如何做快乐的DBA”
- 【Bitmap Index】B-Tree索引与Bitmap位图索引的锁代价比较研究
- 【RMAN】使用RMAN的Duplicate功能创建物理DataGuard报错（ORA-17627、ORA-17629）处理
- 【CUBE】Oracle分组函数之CUBE魅力
- 【ROLLUP】Oracle分组函数之ROLLUP魅力
