恢復控制文件后如何避免resetlogs打開數據庫？
作者：Snowdba
恢復控制文件后，數據庫使用resetlogs方式打開，在線日志的序列被重置。如果恢復完控制文件，不使用resetlogs是打不開數據庫的。
恢復控制文件后，數據庫使用resetlogs方式打開，在線日志的序列被重置。
- SYS@practice >select group#,sequence#,archived,status,first_change#,next_change# from v$log;
- GROUP# SEQUENCE# ARC STATUS FIRST_CHANGE# NEXT_CHANGE#
- ---------- ---------- --- ---------------- ------------- ------------
- 1 1 NO CURRENT 1014415 2.8147E+14
- 2 0 YES UNUSED 0 0
- 3 0 YES UNUSED 0 0
如果沒有被resetlog截斷的話當前日志的序列號應該是7，8，9
- SYS@practice >select recid,sequence#,first_change#,next_change# from v$log_history;
- RECID SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
- ---------- ---------- ------------- ------------
- 1 1 925702 955284
- 2 2 955284 955847
- 3 3 955847 971151
- 4 4 971151 987800
- 5 5 987800 997957
- 6 6 997957 1010981
- 7 1 1010981 1013463
- 8 2 1013463 1013471
- 9 3 1013471 1014415
如果恢復完控制文件，不使用resetlogs是打不開數據庫的。
我們可以采取手工創建控制文件的方法打開數據庫，保持聯機日志的sequence#連續。下面的實驗來演示具體操作過程。
在實驗之前先手工切換3次日志，使得在線日志的序列號產生變化。在恢復結束后作為參考。
- SYS@practice >alter system archive log current;
- SYS@practice >alter system archive log current;
- SYS@practice >alter system archive log current;
切換后當前連接日志狀態如下
- SYS@practice >select group#,sequence#,archived,status,first_change#,next_change# from v$log;
- GROUP# SEQUENCE# ARC STATUS FIRST_CHANGE# NEXT_CHANGE#
- ---------- ---------- --- ---------------- ------------- ------------
- 1 4 NO CURRENT 1030130 2.8147E+14
- 2 2 YES INACTIVE 1030109 1030117
- 3 3 YES ACTIVE 1030117 1030130
1、恢復控制文件
- RMAN> startup force nomount;
- Oracle instance started
- Total System Global Area 580395008 bytes
- Fixed Size 2255392 bytes
- Variable Size 402654688 bytes
- Database Buffers 171966464 bytes
- Redo Buffers 3518464 bytes
- RMAN> restore controlfile from autobackup;
- Starting restore at 06-OCT-14
- using target database control file instead of recovery catalog
- 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_860251717_b35s05gm_.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_860251717_b35s05gm_.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
2、啟動數據庫到mount狀態
- RMAN> mount database;
- database mounted
- released channel: ORA_DISK_1
3、恢復數據庫
- 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 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_1_b362kndr_.arc
- File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_3_b362lc83_.arc
- File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b362l34q_.arc
- File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251717_b35s05gm_.bkp
- 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_b362kndr_.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_b362l34q_.arc
- archived log for thread 1 with sequence 3 is already on disk as file /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_3_b362lc83_.arc
- archived log for thread 1 with sequence 4 is already on disk as file /u01/oradata/practice/redo01.log
- archived log file name=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b362kndr_.arc thread=1 sequence=1
- archived log file name=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b362l34q_.arc thread=1 sequence=2
- archived log file name=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_3_b362lc83_.arc thread=1 sequence=3
- archived log file name=/u01/oradata/practice/redo01.log thread=1 sequence=4
- media recovery complete, elapsed time: 00:00:00
- Finished recover at 06-OCT-14
4，備份控制文件到trace
- SYS@practice >select open_mode from v$database;
- OPEN_MODE
- --------------------
- MOUNTED
- SYS@practice >alter database backup controlfile to trace;
- SYS@practice >select value from v$diag_info where name='Default Trace File';
- VALUE
- --------------------------------------------------------------------------------
- /u01/diag/rdbms/practice/practice/trace/practice_ora_1185.trc
5、重啟實例到nomount狀態
- RMAN> startup force nomount;
- Oracle instance started
- Total System Global Area 580395008 bytes
- Fixed Size 2255392 bytes
- Variable Size 402654688 bytes
- Database Buffers 171966464 bytes
- Redo Buffers 3518464 bytes
6、執行重建控制文件命令進入到mount狀態
- vi /home/oracle/create_controlfile.sql
- 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
- ;
- 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' REUSE;
在sqlplus下執行創建控制文件腳本，數據庫會啟動到open狀態并添加臨時表空間文件。
- SYS@practice >@create_controlfile.sql
- Control file created.
- PL/SQL procedure successfully completed.
- Media recovery complete.
- System altered.
- Database altered.
- Tablespace altered.
此時數據庫已經處于read write狀態也就是open狀態了
- SYS@practice >select open_mode from v$database;
- OPEN_MODE
- --------------------
- READ WRITE
查看當前日志的序列號，沒有被重置。
- SYS@practice >select group#,sequence#,archived,status,first_change#,next_change# from v$log;
- GROUP# SEQUENCE# ARC STATUS FIRST_CHANGE# NEXT_CHANGE#
- ---------- ---------- --- ---------------- ------------- ------------
- 1 4 YES INACTIVE 1030130 1050296
- 2 5 NO CURRENT 1050296 2.8147E+14
- 3 3 YES INACTIVE 1030117 1030130
查看臨時表數據文件也被創建出來了。
- SYS@practice >select name from v$tempfile;
- NAME
- --------------------------------------------------------------------------------
- /u01/oradata/practice/temp01.dbf
7，重新識別控制文件的備份信息和配置信息
此時的控制文件中沒有之前備份過控制文件的信息
- RMAN> list backup of controlfile;
- using target database control file instead of recovery catalog
- specification does not match any backup in the repository
從閃回恢復區重新注冊備份信息
- RMAN> catalog db_recovery_file_dest;
從執行過數據庫全備份的地址注冊備份信息
- RMAN> catalog start with '/home/oracle/';
再次列出備份過的控制文件，可以看到已經全部注冊成功
- RMAN> list backup of controlfile;
- List of Backup Sets
- ===================
- BS Key Type LV Size Device Type Elapsed Time Completion Time
- ------- ---- -- ---------- ----------- ------------ ---------------
- 1 Full 9.67M DISK 00:00:00 06-OCT-14
- BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20141006T175610
- Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860262970_b363zt2x_.bkp
- Control File Included: Ckp SCN: 1051644 Ckp time: 06-OCT-14
- BS Key Type LV Size Device Type Elapsed Time Completion Time
- ------- ---- -- ---------- ----------- ------------ ---------------
- 3 Full 9.33M DISK 00:00:00 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
- ------- ---- -- ---------- ----------- ------------ ---------------
- 7 Full 9.33M DISK 00:00:00 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
責任編輯：龐桂玉
來源：
Oracle疑點通
