備份與恢復系列 十一 控制檔案的備份與恢復
看看當前資料庫的控制檔案位置。一個在/u01/oradata/practice/下，另一個在快速恢復區，彼此互為映象。
SYS@practice >show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/oradata/practice/control0
1.ctl, /u01/fast_recovery_area
/practice/control02.ctl
控制檔案的備份方式有以下幾種
1，RMAN自動備份
在rman中將controlfile autobackup設定為on即可。用RMAN執行備份命令就會觸發控制檔案自動備份，或者資料庫物理結構發生變化之後，比如新增資料檔案，新增日誌檔案組等。值得注意的是即使沒有開啟自動備份，用RMAN執行系統表空間SYSTEM備份的時候也會自動備份控制檔案。
開啟控制檔案自動備份功能
RMAN> configure controlfile autobackup on;
users表空間比較小，備份一下users表空間看看觸發自動備份控制檔案的效果。
RMAN> report schema;
Report of database schema for database with db_unique_name PRACTICE
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 750 SYSTEM *** /u01/oradata/practice/system01.dbf
2 540 SYSAUX *** /u01/oradata/practice/sysaux01.dbf
3 100 UNDOTBS1 *** /u01/oradata/practice/undotbs01.dbf
4 5 USERS *** /u01/oradata/practice/users01.dbf
5 313 EXAMPLE *** /u01/oradata/practice/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /u01/oradata/practice/temp01.dbf
執行結果如下，藍色字型部分為控制檔案同引數檔案被自動備份到快速恢復區的autobackup/2014_10_06目錄下
RMAN> backup tablespace users;
Starting backup at 06-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/oradata/practice/users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-OCT-14
channel ORA_DISK_1: finished piece 1 at 06-OCT-14
piece handle=/u01/fast_recovery_area/PRACTICE/backupset/2014_10_06/o1_mf_nnndf_TAG20141006T125042_b35l32qt_.bkp tag=TAG20141006T125042 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-OCT-14
Starting Control File and SPFILE Autobackup at 06-OCT-14
piece handle=/u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860244643_b35l33tf_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 06-OCT-14
使用list backup of controlfile命令檢視備份過的控制檔案。顯示的備份結果就是剛才自動備份的控制檔案。
RMAN> list backup of controlfile;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.36M DISK 00:00:00 06-OCT-14
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20141006T125043
Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860244643_b35l33tf_.bkp
Control File Included: Ckp SCN: 1001360 Ckp time: 06-OCT-14
2，RMAN手動備份控制檔案
在RMAN中可以使用backup current control file命令來備份控制檔案。輸入結果中藍色字型部分為控制檔案備份地址。在手動備份之後控制檔案的自動備份被觸發，從而又一次備份了控制檔案和引數檔案。
RMAN> backup current controlfile;
Starting backup at 06-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 06-OCT-14
channel ORA_DISK_1: finished piece 1 at 06-OCT-14
piece handle=/u01/fast_recovery_area/PRACTICE/backupset/2014_10_06/o1_mf_ncnnf_TAG20141006T132827_b35n9w39_.bkp tag=TAG20141006T132827 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-OCT-14
（下面是控制檔案和引數檔案的自動備份，又RMAN的配置引數來控制）
Starting Control File and SPFILE Autobackup at 06-OCT-14
piece handle=/u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 06-OCT-14
3，SQLPLUS手動備份控制檔案
3-1備份控制檔案到指定目錄
SYS@practice >alter database backup controlfile to '/home/oracle/control.ctl’;
3-2備份控制檔案到trace檔案
SYS@practice >alter database backup controlfile to trace;
注意Defualt Trace File首字母要大些
SYS@practice >select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/diag/rdbms/practice/practice/trace/practice_ora_12710.trc
3-3檢視trace檔案，關注norestlogs部分，為了便於閱讀，刪除註釋部分內容。
cat /u01/diag/rdbms/practice/practice/trace/practice_ora_12710.trc
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PRACTICE" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/oradata/practice/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/oradata/practice/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/oradata/practice/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/oradata/practice/system01.dbf',
'/u01/oradata/practice/sysaux01.dbf',
'/u01/oradata/practice/undotbs01.dbf',
'/u01/oradata/practice/users01.dbf',
'/u01/oradata/practice/example01.dbf'
CHARACTER SET AL32UTF8
;
（藍色字型部分同步了在RMAN中手動開啟controlfile autobackup on選項）
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/practice/temp01.dbf'
SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
檢視全部控制檔案備份資訊
RMAN> list backup of controlfile;
List of Backup Sets
===================
（備份Users表空間是自動備份的控制檔案）
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.36M DISK 00:00:00 06-OCT-14
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20141006T125043
Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860244643_b35l33tf_.bkp
Control File Included: Ckp SCN: 1001360 Ckp time: 06-OCT-14
（RMAN手動備份的控制檔案）
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 9.33M DISK 00:00:01 06-OCT-14
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20141006T132827
Piece Name: /u01/fast_recovery_area/PRACTICE/backupset/2014_10_06/o1_mf_ncnnf_TAG20141006T132827_b35n9w39_.bkp
Control File Included: Ckp SCN: 1005439 Ckp time: 06-OCT-14
（手動備份後觸發的自動備份控制檔案）
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 9.36M DISK 00:00:00 06-OCT-14
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20141006T132829
Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55_.bkp
Control File Included: Ckp SCN: 1005449 Ckp time: 06-OCT-14
控制檔案的恢復過程
1，從備份中還原控制檔案-restore
2，用重做日誌介質恢復資料庫-recover
3，重設日誌的方式開啟資料庫-resetlogs
情景一，控制檔案採用多路映象。至少有一個控制檔案是可用的，其它的出現了損壞導致例項崩潰。我們無法定位具體是那個檔案損壞了。這時候可以採用簡單的排除法來解決。
1，啟動資料庫到nomount狀態下
2，create pfile='/home/oracle/pfile' from spfile;
3，vi開啟/home/oracle/pfile，修改*.control_files='/u01/oradata/practice/control01.ctl','/u01/fast_recovery_area/practice/control02.ctl’部分，只保留一個控制檔案地址，其它的暫時去掉。
4，使用修改過的pfile啟動例項並嘗試mount資料庫
startup nomount pfile='/home/oracle/pfile';
alter database mount;
5,如果mount成功證明目前引數檔案中的控制檔案是好的。可以再關閉例項後將該控制檔案cp到其它控制檔案的路徑並修改相應的名稱。如果mount不成功，再重複剛才的步驟來修改引數檔案，嘗試保留下一個控制檔案。知道能mount成功。
情景二，採用自動備份的控制檔案來恢復
RMAN> shutdown immediate;
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
（RMAN選擇了最新的一次自動備份控制檔案來恢復）
Starting restore at 06-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
recovery area destination: /u01/fast_recovery_area
database name (or database unique name) used for search: PRACTICE
channel ORA_DISK_1: AUTOBACKUP /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20141006
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/oradata/practice/control01.ctl
output file name=/u01/fast_recovery_area/practice/control02.ctl
Finished restore at 06-OCT-14
RMAN> mount database;
RMAN> recover database;
Starting recover at 06-OCT-14
Starting implicit crosscheck backup at 06-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 06-OCT-14
Starting implicit crosscheck copy at 06-OCT-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 06-OCT-14
searching for all files in the recovery area
cataloging files...
cataloging done
（自動讀取所需的歸檔日誌來恢復資料庫）
List of Cataloged Files
=======================
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_5_b35g8x44_.arc
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55_.bkp
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 6 is already on disk as file /u01/oradata/practice/redo03.log
archived log file name=/u01/oradata/practice/redo03.log thread=1 sequence=6
media recovery complete, elapsed time: 00:00:00
Finished recover at 06-OCT-14
RMAN> alter database open resetlogs;
情景三，遷移資料庫時控制檔案是採用backup database include current control file命令備份的。在恢復控制檔案時需要找到控制檔案所在的備份片，並採用from關鍵字來指出位置。
1，執行全庫備份，控制檔案採用include 方式備份。
RMAN> run{
2> allocate channel c1 device type disk;
3> allocate channel c2 device type disk;
4> backup database format '/home/oracle/full_%d_%s_%T_%p.bak'
5> include current controlfile;
6> sql 'alter system archive log current';
7> backup archivelog all format '/home/oracle/arch_%d_%s_%T_%p.bak';
8> release channel c1;
9> release channel c2;
10> }
2，控制檔案備份在/home/oracle/full_PRACTICE_9_20141006_1.bak備份片中
RMAN> list backup of controlfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.36M DISK 00:00:00 06-OCT-14
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20141006T125043
Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860244643_b35l33tf_.bkp
Control File Included: Ckp SCN: 1001360 Ckp time: 06-OCT-14
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 9.33M DISK 00:00:01 06-OCT-14
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20141006T132827
Piece Name: /u01/fast_recovery_area/PRACTICE/backupset/2014_10_06/o1_mf_ncnnf_TAG20141006T132827_b35n9w39_.bkp
Control File Included: Ckp SCN: 1005439 Ckp time: 06-OCT-14
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 9.36M DISK 00:00:01 06-OCT-14
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20141006T142002
Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860250002_b35qblyy_.bkp
Control File Included: Ckp SCN: 1011114 Ckp time: 06-OCT-14
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 9.33M DISK 00:00:01 06-OCT-14
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20141006T143909
Piece Name: /home/oracle/full_PRACTICE_9_20141006_1.bak
Control File Included: Ckp SCN: 1013438 Ckp time: 06-OCT-14
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 9.36M DISK 00:00:00 06-OCT-14
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20141006T143918
Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251158_b35rgpms_.bkp
Control File Included: Ckp SCN: 1013447 Ckp time: 06-OCT-14
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
12 Full 9.36M DISK 00:00:00 06-OCT-14
BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20141006T143922
Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251162_b35rgt84_.bkp
Control File Included: Ckp SCN: 1013480 Ckp time: 06-OCT-14
3，指定目錄恢復控制檔案
RMAN> run{
2> startup force nomount;
3> restore controlfile from '/home/oracle/full_PRACTICE_9_20141006_1.bak';
4> mount database;
5> recover database;
6> alter database open resetlogs;
7> }
Oracle instance started
Total System Global Area 580395008 bytes
Fixed Size 2255392 bytes
Variable Size 411043296 bytes
Database Buffers 163577856 bytes
Redo Buffers 3518464 bytes
Starting restore at 06-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oradata/practice/control01.ctl
output file name=/u01/fast_recovery_area/practice/control02.ctl
Finished restore at 06-OCT-14
database mounted
released channel: ORA_DISK_1
Starting recover at 06-OCT-14
Starting implicit crosscheck backup at 06-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 06-OCT-14
Starting implicit crosscheck copy at 06-OCT-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 06-OCT-14
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b35rgr1k_.arc
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b35rgr0g_.arc
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251162_b35rgt84_.bkp
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251158_b35rgpms_.bkp
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55_.bkp
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b35rgr0g_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b35rgr1k_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u01/oradata/practice/redo03.log
archived log file name=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b35rgr0g_.arc thread=1 sequence=1
archived log file name=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b35rgr1k_.arc thread=1 sequence=2
archived log file name=/u01/oradata/practice/redo03.log thread=1 sequence=3
media recovery complete, elapsed time: 00:00:00
Finished recover at 06-OCT-14
database opened
控制檔案的恢復到此結束。
SYS@practice >show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/oradata/practice/control0
1.ctl, /u01/fast_recovery_area
/practice/control02.ctl
控制檔案的備份方式有以下幾種
1，RMAN自動備份
在rman中將controlfile autobackup設定為on即可。用RMAN執行備份命令就會觸發控制檔案自動備份，或者資料庫物理結構發生變化之後，比如新增資料檔案，新增日誌檔案組等。值得注意的是即使沒有開啟自動備份，用RMAN執行系統表空間SYSTEM備份的時候也會自動備份控制檔案。
開啟控制檔案自動備份功能
RMAN> configure controlfile autobackup on;
users表空間比較小，備份一下users表空間看看觸發自動備份控制檔案的效果。
RMAN> report schema;
Report of database schema for database with db_unique_name PRACTICE
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 750 SYSTEM *** /u01/oradata/practice/system01.dbf
2 540 SYSAUX *** /u01/oradata/practice/sysaux01.dbf
3 100 UNDOTBS1 *** /u01/oradata/practice/undotbs01.dbf
4 5 USERS *** /u01/oradata/practice/users01.dbf
5 313 EXAMPLE *** /u01/oradata/practice/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /u01/oradata/practice/temp01.dbf
執行結果如下，藍色字型部分為控制檔案同引數檔案被自動備份到快速恢復區的autobackup/2014_10_06目錄下
RMAN> backup tablespace users;
Starting backup at 06-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/oradata/practice/users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-OCT-14
channel ORA_DISK_1: finished piece 1 at 06-OCT-14
piece handle=/u01/fast_recovery_area/PRACTICE/backupset/2014_10_06/o1_mf_nnndf_TAG20141006T125042_b35l32qt_.bkp tag=TAG20141006T125042 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-OCT-14
Starting Control File and SPFILE Autobackup at 06-OCT-14
piece handle=/u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860244643_b35l33tf_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 06-OCT-14
使用list backup of controlfile命令檢視備份過的控制檔案。顯示的備份結果就是剛才自動備份的控制檔案。
RMAN> list backup of controlfile;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.36M DISK 00:00:00 06-OCT-14
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20141006T125043
Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860244643_b35l33tf_.bkp
Control File Included: Ckp SCN: 1001360 Ckp time: 06-OCT-14
2，RMAN手動備份控制檔案
在RMAN中可以使用backup current control file命令來備份控制檔案。輸入結果中藍色字型部分為控制檔案備份地址。在手動備份之後控制檔案的自動備份被觸發，從而又一次備份了控制檔案和引數檔案。
RMAN> backup current controlfile;
Starting backup at 06-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 06-OCT-14
channel ORA_DISK_1: finished piece 1 at 06-OCT-14
piece handle=/u01/fast_recovery_area/PRACTICE/backupset/2014_10_06/o1_mf_ncnnf_TAG20141006T132827_b35n9w39_.bkp tag=TAG20141006T132827 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-OCT-14
（下面是控制檔案和引數檔案的自動備份，又RMAN的配置引數來控制）
Starting Control File and SPFILE Autobackup at 06-OCT-14
piece handle=/u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 06-OCT-14
3，SQLPLUS手動備份控制檔案
3-1備份控制檔案到指定目錄
SYS@practice >alter database backup controlfile to '/home/oracle/control.ctl’;
3-2備份控制檔案到trace檔案
SYS@practice >alter database backup controlfile to trace;
注意Defualt Trace File首字母要大些
SYS@practice >select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/diag/rdbms/practice/practice/trace/practice_ora_12710.trc
3-3檢視trace檔案，關注norestlogs部分，為了便於閱讀，刪除註釋部分內容。
cat /u01/diag/rdbms/practice/practice/trace/practice_ora_12710.trc
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PRACTICE" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/oradata/practice/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/oradata/practice/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/oradata/practice/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/oradata/practice/system01.dbf',
'/u01/oradata/practice/sysaux01.dbf',
'/u01/oradata/practice/undotbs01.dbf',
'/u01/oradata/practice/users01.dbf',
'/u01/oradata/practice/example01.dbf'
CHARACTER SET AL32UTF8
;
（藍色字型部分同步了在RMAN中手動開啟controlfile autobackup on選項）
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/practice/temp01.dbf'
SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
檢視全部控制檔案備份資訊
RMAN> list backup of controlfile;
List of Backup Sets
===================
（備份Users表空間是自動備份的控制檔案）
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.36M DISK 00:00:00 06-OCT-14
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20141006T125043
Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860244643_b35l33tf_.bkp
Control File Included: Ckp SCN: 1001360 Ckp time: 06-OCT-14
（RMAN手動備份的控制檔案）
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 9.33M DISK 00:00:01 06-OCT-14
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20141006T132827
Piece Name: /u01/fast_recovery_area/PRACTICE/backupset/2014_10_06/o1_mf_ncnnf_TAG20141006T132827_b35n9w39_.bkp
Control File Included: Ckp SCN: 1005439 Ckp time: 06-OCT-14
（手動備份後觸發的自動備份控制檔案）
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 9.36M DISK 00:00:00 06-OCT-14
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20141006T132829
Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55_.bkp
Control File Included: Ckp SCN: 1005449 Ckp time: 06-OCT-14
控制檔案的恢復過程
1，從備份中還原控制檔案-restore
2，用重做日誌介質恢復資料庫-recover
3，重設日誌的方式開啟資料庫-resetlogs
情景一，控制檔案採用多路映象。至少有一個控制檔案是可用的，其它的出現了損壞導致例項崩潰。我們無法定位具體是那個檔案損壞了。這時候可以採用簡單的排除法來解決。
1，啟動資料庫到nomount狀態下
2，create pfile='/home/oracle/pfile' from spfile;
3，vi開啟/home/oracle/pfile，修改*.control_files='/u01/oradata/practice/control01.ctl','/u01/fast_recovery_area/practice/control02.ctl’部分，只保留一個控制檔案地址，其它的暫時去掉。
4，使用修改過的pfile啟動例項並嘗試mount資料庫
startup nomount pfile='/home/oracle/pfile';
alter database mount;
5,如果mount成功證明目前引數檔案中的控制檔案是好的。可以再關閉例項後將該控制檔案cp到其它控制檔案的路徑並修改相應的名稱。如果mount不成功，再重複剛才的步驟來修改引數檔案，嘗試保留下一個控制檔案。知道能mount成功。
情景二，採用自動備份的控制檔案來恢復
RMAN> shutdown immediate;
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
（RMAN選擇了最新的一次自動備份控制檔案來恢復）
Starting restore at 06-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
recovery area destination: /u01/fast_recovery_area
database name (or database unique name) used for search: PRACTICE
channel ORA_DISK_1: AUTOBACKUP /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20141006
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/oradata/practice/control01.ctl
output file name=/u01/fast_recovery_area/practice/control02.ctl
Finished restore at 06-OCT-14
RMAN> mount database;
RMAN> recover database;
Starting recover at 06-OCT-14
Starting implicit crosscheck backup at 06-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 06-OCT-14
Starting implicit crosscheck copy at 06-OCT-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 06-OCT-14
searching for all files in the recovery area
cataloging files...
cataloging done
（自動讀取所需的歸檔日誌來恢復資料庫）
List of Cataloged Files
=======================
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_5_b35g8x44_.arc
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55_.bkp
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 6 is already on disk as file /u01/oradata/practice/redo03.log
archived log file name=/u01/oradata/practice/redo03.log thread=1 sequence=6
media recovery complete, elapsed time: 00:00:00
Finished recover at 06-OCT-14
RMAN> alter database open resetlogs;
情景三，遷移資料庫時控制檔案是採用backup database include current control file命令備份的。在恢復控制檔案時需要找到控制檔案所在的備份片，並採用from關鍵字來指出位置。
1，執行全庫備份，控制檔案採用include 方式備份。
RMAN> run{
2> allocate channel c1 device type disk;
3> allocate channel c2 device type disk;
4> backup database format '/home/oracle/full_%d_%s_%T_%p.bak'
5> include current controlfile;
6> sql 'alter system archive log current';
7> backup archivelog all format '/home/oracle/arch_%d_%s_%T_%p.bak';
8> release channel c1;
9> release channel c2;
10> }
2，控制檔案備份在/home/oracle/full_PRACTICE_9_20141006_1.bak備份片中
RMAN> list backup of controlfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.36M DISK 00:00:00 06-OCT-14
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20141006T125043
Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860244643_b35l33tf_.bkp
Control File Included: Ckp SCN: 1001360 Ckp time: 06-OCT-14
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 9.33M DISK 00:00:01 06-OCT-14
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20141006T132827
Piece Name: /u01/fast_recovery_area/PRACTICE/backupset/2014_10_06/o1_mf_ncnnf_TAG20141006T132827_b35n9w39_.bkp
Control File Included: Ckp SCN: 1005439 Ckp time: 06-OCT-14
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 9.36M DISK 00:00:01 06-OCT-14
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20141006T142002
Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860250002_b35qblyy_.bkp
Control File Included: Ckp SCN: 1011114 Ckp time: 06-OCT-14
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 9.33M DISK 00:00:01 06-OCT-14
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20141006T143909
Piece Name: /home/oracle/full_PRACTICE_9_20141006_1.bak
Control File Included: Ckp SCN: 1013438 Ckp time: 06-OCT-14
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 9.36M DISK 00:00:00 06-OCT-14
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20141006T143918
Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251158_b35rgpms_.bkp
Control File Included: Ckp SCN: 1013447 Ckp time: 06-OCT-14
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
12 Full 9.36M DISK 00:00:00 06-OCT-14
BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20141006T143922
Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251162_b35rgt84_.bkp
Control File Included: Ckp SCN: 1013480 Ckp time: 06-OCT-14
3，指定目錄恢復控制檔案
RMAN> run{
2> startup force nomount;
3> restore controlfile from '/home/oracle/full_PRACTICE_9_20141006_1.bak';
4> mount database;
5> recover database;
6> alter database open resetlogs;
7> }
Oracle instance started
Total System Global Area 580395008 bytes
Fixed Size 2255392 bytes
Variable Size 411043296 bytes
Database Buffers 163577856 bytes
Redo Buffers 3518464 bytes
Starting restore at 06-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oradata/practice/control01.ctl
output file name=/u01/fast_recovery_area/practice/control02.ctl
Finished restore at 06-OCT-14
database mounted
released channel: ORA_DISK_1
Starting recover at 06-OCT-14
Starting implicit crosscheck backup at 06-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 06-OCT-14
Starting implicit crosscheck copy at 06-OCT-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 06-OCT-14
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b35rgr1k_.arc
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b35rgr0g_.arc
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251162_b35rgt84_.bkp
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251158_b35rgpms_.bkp
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55_.bkp
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b35rgr0g_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b35rgr1k_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u01/oradata/practice/redo03.log
archived log file name=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b35rgr0g_.arc thread=1 sequence=1
archived log file name=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b35rgr1k_.arc thread=1 sequence=2
archived log file name=/u01/oradata/practice/redo03.log thread=1 sequence=3
media recovery complete, elapsed time: 00:00:00
Finished recover at 06-OCT-14
database opened
控制檔案的恢復到此結束。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29047826/viewspace-1290110/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 備份與恢復--利用備份的控制檔案恢復
- 備份與恢復系列 十 引數檔案spfile的備份與恢復
- 備份與恢復--重建控制檔案
- 【備份與恢復】控制檔案的恢復（不完全恢復）
- Oracle備份與恢復【丟失控制檔案的恢復】Oracle
- 備份與恢復（Parameter 檔案恢復篇）
- 【備份與恢復】恢復受損的複用控制檔案
- 【rman 備份與恢復】恢復丟失所有的控制檔案
- oralce備份與恢復1 控制檔案(轉)
- 【備份與恢復】恢復受損的口令檔案
- 【備份恢復】利用 備份控制檔案到指定目錄下的控制檔案 恢復控制檔案
- 備份與恢復：polardb資料庫備份與恢復資料庫
- 利用備份的控制檔案恢復
- 控制檔案的備份和恢復
- RMAN備份恢復之控制檔案的恢復（三）
- RMAN備份恢復之控制檔案的恢復（二）
- RMAN備份恢復之控制檔案的恢復（一）
- 備份與恢復--重建控制檔案後資料檔案損壞的恢復
- 備份＆恢復之四：非歸檔模式下的備份與恢復模式
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- 備份與恢復--從備份的歸檔日誌中恢復資料
- 【備份恢復】 控制檔案多路徑
- Postgresql 備份與恢復SQL
- MySQL備份與恢復MySql
- Oracle備份與恢復【丟失資料檔案的恢復】Oracle
- 【備份與恢復】archivelog模式中資料檔案的恢復Hive模式
- 【備份與恢復】noarchivelog模式中資料檔案的恢復Hive模式
- 備份與恢復系列 八 丟失所有資料檔案的還原與恢復
- 【備份恢復】所有控制檔案丟失後 利用trace中的控制檔案備份執行恢復
- 【備份恢復】Oracle 資料備份與恢復微實踐Oracle
- Mysql的備份與恢復MySql
- controlfile檔案的備份與恢復
- Mysql備份與恢復(1)---物理備份MySql
- RMAN備份與恢復之加密備份加密
- rman備份丟失控制檔案恢復
- Oracle 備份 與 恢復 概述Oracle
- gitlab備份與恢復Gitlab
- MongoDB之備份與恢復MongoDB