【恢復】使用控制檔案的trace備份恢復因異常斷電導致所有控制檔案損壞的資料庫故障
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部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-622655/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 備份與恢復--重建控制檔案後資料檔案損壞的恢復
- 【備份恢復】所有控制檔案丟失後 利用trace中的控制檔案備份執行恢復
- 使用備份的控制檔案恢復資料庫資料庫
- 備份＆恢復之十三：損壞全部控制檔案
- 損壞控制檔案的恢復方法
- 所有控制檔案損壞的恢復--resetlogs方式
- 所有控制檔案損壞的恢復--noresetlogs方式
- 備份＆恢復之十二：損壞單個控制檔案
- trace檔案備份控制檔案並執行恢復
- 【備份恢復】利用 備份控制檔案到指定目錄下的控制檔案 恢復控制檔案
- 【備份與恢復】恢復受損的複用控制檔案
- 控制檔案恢復—從trace檔案中恢復
- 使用舊的控制檔案備份來恢復控制檔案
- 單個控制檔案損壞的恢復
- 備份與恢復--重新控制檔案資料字典和控制檔案不一致的恢復
- 備份與恢復--利用備份的控制檔案恢復
- rman恢復資料庫--用備份的控制檔案資料庫
- 利用備份的控制檔案恢復
- 控制檔案的備份和恢復
- 某個控制檔案損壞的恢復案例
- 一次控制檔案損壞的恢復
- 磁碟損壞導致資料檔案丟失的恢復
- 【rman 備份與恢復】恢復丟失所有的控制檔案
- RMAN備份恢復之控制檔案的恢復（三）
- RMAN備份恢復之控制檔案的恢復（二）
- RMAN備份恢復之控制檔案的恢復（一）
- 恢復之單個控制檔案損壞
- 【備份與恢復】控制檔案的恢復（不完全恢復）
- 備份與恢復系列 十一 控制檔案的備份與恢復
- 控制檔案被破壞的資料庫恢復方法資料庫
- 【RMAN】如果控制檔案損壞那麼如何恢復？恢復控制檔案的方式有哪幾種？
- Oracle備份與恢復【丟失控制檔案的恢復】Oracle
- 控制檔案全部丟失，無備份，通過異機trace恢復
- 【vSAN資料恢復案例】異常斷電導致vSAN底層資料損壞的資料恢復資料恢復
- 【備份恢復】 控制檔案多路徑
- 備份與恢復--重建控制檔案
- Oracle資料庫控制檔案在備份恢復中的作用Oracle資料庫
- 資料庫檔案壞塊損壞導致開啟時報錯的恢復方法資料庫