恢复控制文件后如何避免resetlogs打开数据库？

[[194050]]

恢复控制文件后，数据库使用resetlogs方式打开，在线日志的序列被重置。

SYS@practice>selectgroup#,sequence#,archived,status,first_change#,next_change#fromv$log;GROUP#SEQUENCE#ARCSTATUSFIRST_CHANGE#NEXT_CHANGE#----------------------------------------------------------------11NOCURRENT10144152.8147E+1420YESUNUSED0030YESUNUSED00

如果没有被resetlog截断的话当前日志的序列号应该是7，8，9

SYS@practice>selectrecid,sequence#,first_change#,next_change#fromv$log_history;RECIDSEQUENCE#FIRST_CHANGE#NEXT_CHANGE#---------------------------------------------1192570295528422955284955847339558479711514497115198780055987800997957669979571010981711010981101346382101346310134719310134711014415

如果恢复完控制文件，不使用resetlogs是打不开数据库的。

我们可以采取手工创建控制文件的方法打开数据库，保持联机日志的sequence#连续。下面的实验来演示具体操作过程。

在实验之前先手工切换3次日志，使得在线日志的序列号产生变化。在恢复结束后作为参考。

SYS@practice>altersystemarchivelogcurrent;SYS@practice>altersystemarchivelogcurrent;SYS@practice>altersystemarchivelogcurrent;

切换后当前连接日志状态如下

SYS@practice>selectgroup#,sequence#,archived,status,first_change#,next_change#fromv$log;GROUP#SEQUENCE#ARCSTATUSFIRST_CHANGE#NEXT_CHANGE#----------------------------------------------------------------14NOCURRENT10301302.8147E+1422YESINACTIVE1030109103011733YESACTIVE10301171030130

1、恢复控制文件

RMAN>startupforcenomount;OracleinstancestartedTotalSystemGlobalArea580395008bytesFixedSize2255392bytesVariableSize402654688bytesDatabaseBuffers171966464bytesRedoBuffers3518464bytesRMAN>restorecontrolfilefromautobackup;Startingrestoreat06-OCT-14usingtargetdatabasecontrolfileinsteadofrecoverycatalogallocatedchannel:ORA_DISK_1channelORA_DISK_1:SID=19devicetype=DISKrecoveryareadestination:/u01/fast_recovery_areadatabasename(ordatabaseuniquename)usedforsearch:PRACTICEchannelORA_DISK_1:AUTOBACKUP/u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251717_b35s05gm_.bkpfoundintherecoveryareachannelORA_DISK_1:lookingforAUTOBACKUPonday:20141006channelORA_DISK_1:restoringcontrolfilefromAUTOBACKUP/u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251717_b35s05gm_.bkpchannelORA_DISK_1:controlfilerestorefromAUTOBACKUPcompleteoutputfilename=/u01/oradata/practice/control01.ctloutputfilename=/u01/fast_recovery_area/practice/control02.ctlFinishedrestoreat06-OCT-14

2、启动数据库到mount状态

RMAN>mountdatabase;databasemountedreleasedchannel:ORA_DISK_1

3、恢复数据库

RMAN>recoverdatabase;Startingrecoverat06-OCT-14Startingimplicitcrosscheckbackupat06-OCT-14allocatedchannel:ORA_DISK_1channelORA_DISK_1:SID=20devicetype=DISKCrosschecked6objectsFinishedimplicitcrosscheckbackupat06-OCT-14Startingimplicitcrosscheckcopyat06-OCT-14usingchannelORA_DISK_1Finishedimplicitcrosscheckcopyat06-OCT-14searchingforallfilesintherecoveryareacatalogingfiles...catalogingdoneListofCatalogedFiles=======================FileName:/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b362kndr_.arcFileName:/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_3_b362lc83_.arcFileName:/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b362l34q_.arcFileName:/u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251717_b35s05gm_.bkpFileName:/u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251162_b35rgt84_.bkpFileName:/u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251158_b35rgpms_.bkpFileName:/u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55_.bkpusingchannelORA_DISK_1startingmediarecoveryarchivedlogforthread1withsequence1isalreadyondiskasfile/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b362kndr_.arcarchivedlogforthread1withsequence2isalreadyondiskasfile/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b362l34q_.arcarchivedlogforthread1withsequence3isalreadyondiskasfile/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_3_b362lc83_.arcarchivedlogforthread1withsequence4isalreadyondiskasfile/u01/oradata/practice/redo01.logarchivedlogfilename=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b362kndr_.arcthread=1sequence=1archivedlogfilename=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b362l34q_.arcthread=1sequence=2archivedlogfilename=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_3_b362lc83_.arcthread=1sequence=3archivedlogfilename=/u01/oradata/practice/redo01.logthread=1sequence=4mediarecoverycomplete,elapsedtime:00:00:00Finishedrecoverat06-OCT-14

4，备份控制文件到trace

SYS@practice>selectopen_modefromv$database;OPEN_MODE--------------------MOUNTEDSYS@practice>alterdatabasebackupcontrolfiletotrace;SYS@practice>selectvaluefromv$diag_infowherename='DefaultTraceFile';VALUE--------------------------------------------------------------------------------/u01/diag/rdbms/practice/practice/trace/practice_ora_1185.trc

5、重启实例到nomount状态

RMAN>startupforcenomount;OracleinstancestartedTotalSystemGlobalArea580395008bytesFixedSize2255392bytesVariableSize402654688bytesDatabaseBuffers171966464bytesRedoBuffers3518464bytes

6、执行重建控制文件命令进入到mount状态

vi/home/oracle/create_controlfile.sqlSTARTUPNOMOUNTCREATECONTROLFILEREUSEDATABASE"PRACTICE"NORESETLOGSARCHIVELOGMAXLOGFILES16MAXLOGMEMBERS3MAXDATAFILES100MAXINSTANCES8MAXLOGHISTORY292LOGFILEGROUP1'/u01/oradata/practice/redo01.log'SIZE50MBLOCKSIZE512,GROUP2'/u01/oradata/practice/redo02.log'SIZE50MBLOCKSIZE512,GROUP3'/u01/oradata/practice/redo03.log'SIZE50MBLOCKSIZE512DATAFILE'/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'CHARACTERSETAL32UTF8;VARIABLERECNONUMBER;EXECUTE:RECNO:=SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILEAUTOBACKUP','ON');RECOVERDATABASEALTERSYSTEMARCHIVELOGALL;ALTERDATABASEOPEN;ALTERTABLESPACETEMPADDTEMPFILE'/u01/oradata/practice/temp01.dbf'REUSE;

在sqlplus下执行创建控制文件脚本，数据库会启动到open状态并添加临时表空间文件。

SYS@practice>@create_controlfile.sqlControlfilecreated.PL/SQLproceduresuccessfullycompleted.Mediarecoverycomplete.Systemaltered.Databasealtered.Tablespacealtered.

此时数据库已经处于read write状态也就是open状态了

SYS@practice>selectopen_modefromv$database;OPEN_MODE--------------------READWRITE

查看当前日志的序列号，没有被重置。

SYS@practice>selectgroup#,sequence#,archived,status,first_change#,next_change#fromv$log;GROUP#SEQUENCE#ARCSTATUSFIRST_CHANGE#NEXT_CHANGE#----------------------------------------------------------------14YESINACTIVE1030130105029625NOCURRENT10502962.8147E+1433YESINACTIVE10301171030130

查看临时表数据文件也被创建出来了。

SYS@practice>selectnamefromv$tempfile;NAME--------------------------------------------------------------------------------/u01/oradata/practice/temp01.dbf

7，重新识别控制文件的备份信息和配置信息

此时的控制文件中没有之前备份过控制文件的信息

RMAN>listbackupofcontrolfile;usingtargetdatabasecontrolfileinsteadofrecoverycatalogspecificationdoesnotmatchanybackupintherepository

从闪回恢复区重新注册备份信息

RMAN>catalogdb_recovery_file_dest;

从执行过数据库全备份的地址注册备份信息

RMAN>catalogstartwith'/home/oracle/';

再次列出备份过的控制文件，可以看到已经全部注册成功

RMAN>listbackupofcontrolfile;ListofBackupSets===================BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime-------------------------------------------------------------1Full9.67MDISK00:00:0006-OCT-14BPKey:1Status:AVAILABLECompressed:NOTag:TAG20141006T175610PieceName:/u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860262970_b363zt2x_.bkpControlFileIncluded:CkpSCN:1051644Ckptime:06-OCT-14BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime-------------------------------------------------------------3Full9.33MDISK00:00:0006-OCT-14BPKey:3Status:AVAILABLECompressed:NOTag:TAG20141006T132827PieceName:/u01/fast_recovery_area/PRACTICE/backupset/2014_10_06/o1_mf_ncnnf_TAG20141006T132827_b35n9w39_.bkpControlFileIncluded:CkpSCN:1005439Ckptime:06-OCT-14BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime-------------------------------------------------------------7Full9.33MDISK00:00:0006-OCT-14BPKey:7Status:AVAILABLECompressed:NOTag:TAG20141006T143909PieceName:/home/oracle/full_PRACTICE_9_20141006_1.bakControlFileIncluded:CkpSCN:1013438Ckptime:06-OCT-14