oracle controlfile backup and recovery

备份：<wbr style=""><div style=""> <table border="1" cellspacing="1" cellpadding="1" width="80%" style="line-height:25px"><tbody style="line-height:25px"><tr style="line-height:25px"> <td style="line-height:25px">alter database backup controlfile to 'xxxxxxx' reuse;<br style="line-height:25px"> alter database bakcup controlfile to trace as 'xxxxxxx';<br style="line-height:25px"> RMAN备份控制文件<br style="line-height:25px"> </td> </tr></tbody></table> 恢复实例：<br style="line-height:25px"> 1.控制文件损坏后利用二进制备份恢复(异常断电）<br style="line-height:25px"> </div> <table border="1" cellspacing="1" cellpadding="1" width="80%" style=""><tbody style="line-height:25px"><tr style="line-height:25px"> <td style="line-height:25px">备份控制文件：<br style="line-height:25px"> alter database backup controlfile to '/home/oracle/control.ctl ' reuse;<br style="line-height:25px"> 插入测试数据：<br style="line-height:25px"> SQL&gt; insert into t1 values(1);<br style="line-height:25px"> SQL&gt; commit;<br style="line-height:25px"> 模拟断电：<br style="line-height:25px"> SQL&gt; shutdown abort;<br style="line-height:25px"> 模拟控制文件故障：<br style="line-height:25px"> rm -rf /database/oradata/skyread/control01.ctl<br style="line-height:25px"> rm -rf /database/oradata/skyread/control02.ctl<br style="line-height:25px"> rm -rf /database/oradata/skyread/control03.ctl<br style="line-height:25px"> 再次开启数据库：<br style="line-height:25px"> SQL&gt; startup;<br style="line-height:25px"> ORA-00205: error in identifying control file, check alert log for more info<br style="line-height:25px"> 报00205错误<br style="line-height:25px"> 把备份文件复制到数据库控制文件所在位置：<br style="line-height:25px"> cp /home/oracle/control.ctl /database/oradata/skyread/control01.ctl<br style="line-height:25px"> cp /home/oracle/control.ctl /database/oradata/skyread/control02.ctl<br style="line-height:25px"> cp /home/oracle/control.ctl /database/oradata/skyread/control03.ctl<br style="line-height:25px"> SQL&gt; startup force mount<br style="line-height:25px"> Total System Global Area 5049942016 bytes<br style="line-height:25px"> Fixed Size 2090880 bytes<br style="line-height:25px"> Variable Size 1375733888 bytes<br style="line-height:25px"> Database Buffers 3657433088 bytes<br style="line-height:25px"> Redo Buffers 14684160 bytes<br style="line-height:25px"> Database mounted.<br style="line-height:25px"> </td> </tr></tbody></table> <span style=""> </span> <div style="">检查数据文件，数据文件头，日志文件以及控制文件的scn信息，由于是机器断电，所以这里fuzzy的状态是YES <div style="line-height:25px"> <div style="line-height:25px">SQL&gt; select file#,status,fuzzy,CHECKPOINT_CHANGE# from v$datafile_header;</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px"> FILE# STATUS FUZ CHECKPOINT_CHANGE#</div> <div style="line-height:25px">-------------------- ------- --- --------------------</div> <div style="line-height:25px"> 1 ONLINE YES 122694280130</div> <div style="line-height:25px"> 2 ONLINE YES 122694280130</div> <div style="line-height:25px"> 3 ONLINE YES 122694280130</div> <div style="line-height:25px"> 4 ONLINE YES 122694280130</div> <div style="line-height:25px"> 18 ONLINE YES 122694280130</div> <div style="line-height:25px"> 26 ONLINE YES 122694280130</div> <div style="line-height:25px"> 31 ONLINE YES 122694280130</div> </div> <div style="line-height:25px"> <div style="line-height:25px">SQL&gt; select group#,sequence#,members,archived,status,first_change# from v$log;</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px"> GROUP# SEQUENCE# MEMBERS ARC STATUS FIRST_CHANGE#</div> <div style="line-height:25px">-------------------- -------------------- -------------------- --- ---------------- --------------------</div> <div style="line-height:25px"> 1 1 1 NO CURRENT 122694212304</div> <div style="line-height:25px"> 2 2 1 YES INACTIVE 122693905385</div> <div style="line-height:25px"> 5 3 1 YES INACTIVE 122694191761</div> <div style="line-height:25px"> 4 4 1 YES INACTIVE 122694190542</div> <div style="line-height:25px"> 3 5 1 YES INACTIVE 122694168156</div> </div> <div style="line-height:25px"><span style="line-height:22px"><strong style="line-height:25px">CONTROLFILE_CHANGE#是备份时的控制文件SCN，这里数据文件的检查点肯定是大于当时备份的控制文件，所以数据打开的时候，数据文件，日志文件，控制文件的检查点信息是不一致的，要进行介质恢复到一致，才能打开，以上V$LOG,V$DATAFILE都是和备份的控制文件的检查点一致的。但是v$datafile_header记录的是最新的，就是数据库关闭前的。所以如果打开的时候做会做检查，检查数据文件头的检查点计数和与现在控制文件的检查点技术是否相等，如果相等，进行第二次检查，也就是检查数据文件头开始的SCN和控制文件结束的SCN是否相等，如果相等，就不需要进行恢复，不相等，就需要进行恢复。这里明显是不相等的，所以需要recovery。</strong></span></div> <div style="line-height:25px"> <div style="line-height:25px">SQL&gt; select checkpoint_change#,controlfile_change#,resetlogs_change# from v$database;</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px"> CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# RESETLOGS_CHANGE#</div> <div style="line-height:25px">-------------------- -------------------- --------------------</div> <div style="line-height:25px"> 122694212305 <span></span><span style="color:#ff0000; line-height:25px">122694259161 </span><span></span> 122693676208</div> </div> <div style="line-height:25px">介质恢复数据库：提示需要应用redo01.log</div> <div style="line-height:25px"> <div style="line-height:25px">SQL&gt; recover database using backup controlfile;</div> <div style="line-height:25px">ORA-00279: change 122694280130 generated at 05/03/2013 11:25:37 needed for</div> <div style="line-height:25px">thread 1</div> <div style="line-height:25px">ORA-00289: suggestion : /database/oradata/arch/1_1_814447469.dbf</div> <div style="line-height:25px">ORA-00280: change 122694280130 for thread 1 is in sequence #1</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">Specify log: {&lt;RET&gt;=suggested | filename | AUTO | CANCEL}</div> <div style="line-height:25px"><span style="color:#ff0000; line-height:25px">/database/oradata/skyread/redo01.log</span></div> <div style="line-height:25px">Log applied.</div> <div style="line-height:25px">Media recovery complete.</div> <div style="line-height:25px">重置日志文件打开数据库：</div> <div style="line-height:25px">SQL&gt; alter database open resetlogs;</div> <div style="line-height:25px">Database altered.</div> </div> </div> <div style=""><br style="line-height:25px"></div> <div style=""><span style="line-height:22px">2.控制文件损坏后利用trace文件重建控制文件(正常关机）</span></div> <div style=""><span style="line-height:22px">备份控制文件为文本格式</span></div> <div style=""> <div style="line-height:25px">SQL&gt; alter database backup controlfile to trace as '/home/oracle/controlfile.sql' reuse;</div> <div style="line-height:25px">Database altered.</div> <div style="line-height:25px">干净关闭数据库并破坏所有控制文件</div> <div style="line-height:25px">SQL&gt; shutdown immediate;</div> </div> <div style=""> <div style="line-height:25px">mv /database/oradata/skyread/control01.ctl /database/oradata/skyread/control01.ctl.bak</div> <div style="line-height:25px">mv /database/oradata/skyread/control02.ctl /database/oradata/skyread/control02.ctl.bak</div> <div style="line-height:25px">mv /database/oradata/skyread/control03.ctl /database/oradata/skyread/control03.ctl.bak</div> </div> <div style="">打开数据库出错</div> <div style=""> <div style="line-height:25px">SQL&gt; startup;</div> <div style="line-height:25px">ORACLE instance started.</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">Total System Global Area 5049942016 bytes</div> <div style="line-height:25px">Fixed Size 2090880 bytes</div> <div style="line-height:25px">Variable Size 1375733888 bytes</div> <div style="line-height:25px">Database Buffers 3657433088 bytes</div> <div style="line-height:25px">Redo Buffers 14684160 bytes</div> <div style="line-height:25px">ORA-00205: error in identifying control file, check alert log for more info</div> </div> <div style=""><strong style="line-height:25px">在nomount模式用<span style="line-height:22px">/home/oracle/controlfile.sql中的语句重建控制文件，注意这里用的是</span><span style="line-height:22px">NORESETLOGS,这里因为我们是干净关闭数据做的测试，相当于数据文件和日志文件都是没有损坏的，这里是基于数据文件头<span style="line-height:25px; white-space:pre"> </span>和日志文件头的信息构建出控制文件，所以这个控制文件和数据文件头和日志文件头都是一致的，既然是一致的，那么都不需要recover，直接打开数据库即可。</span></strong></div> <div style=""><span style="line-height:22px"><span style="color:#ff0000; line-height:25px"><strong style="line-height:25px">注意：这里如果是用二进制备份控制文件进行恢复，还是需要recover的，因为你备份的二进制文件和当前的数据文件头信息一般是不一致的。</strong></span></span></div> <div style=""> <div style="line-height:25px">CREATE CONTROLFILE REUSE DATABASE "SKYREAD" NORESETLOGS FORCE LOGGING ARCHIVELOG</div> <div style="line-height:25px"> MAXLOGFILES 20</div> <div style="line-height:25px"> MAXLOGMEMBERS 5</div> <div style="line-height:25px"> MAXDATAFILES 1000</div> <div style="line-height:25px"> MAXINSTANCES 8</div> <div style="line-height:25px"> MAXLOGHISTORY 2337</div> <div style="line-height:25px">LOGFILE</div> <div style="line-height:25px"> GROUP 1 '/database/oradata/skyread/redo01.log' SIZE 512M,</div> <div style="line-height:25px"> GROUP 2 '/database/oradata/skyread/redo02.log' SIZE 512M,</div> <div style="line-height:25px"> GROUP 3 '/database/oradata/skyread/redo03.log' SIZE 512M,</div> <div style="line-height:25px"> GROUP 4 '/database/oradata/skyread/redo04.log' SIZE 512M,</div> <div style="line-height:25px"> GROUP 5 '/database/oradata/skyread/redo05.log' SIZE 512M</div> <div style="line-height:25px">DATAFILE</div> <div style="line-height:25px"> '/database/oradata/skyread/system01.dbf',</div> <div style="line-height:25px"> '/database/oradata/skyread/tbs_test.dbf',</div> <div style="line-height:25px"> '/database/oradata/skyread/sysaux01.dbf',</div> <div style="line-height:25px"> '/database/oradata/skyread/users01.dbf',</div> <div style="line-height:25px"> '/database2/oradata/skyread/TBS_MRPMUSIC01.dbf',</div> <div style="line-height:25px"> '/database/oradata/skyread/sf01.dbf',</div> <div style="line-height:25px"> '/database2/oradata/skyread/undotbs02'</div> <div style="line-height:25px">CHARACTER SET UTF8;</div> </div> <div style=""> <div style="line-height:25px">SQL&gt; alter database open;</div> <div style="line-height:25px">Database altered</div> </div> </wbr>