探索Oracle不完全恢复之--基于cancel的恢复第二篇

基于cancel 的不一致性恢复（归档丢失）第二篇   主要适用于：基于Cancel的不完全恢复适用场景：Recover时，所需的某个归档日志损坏，或主机断电，current状态的联机日志损坏。 创建测试表    创建wwl002表，切换日志，再创建新的wwl003表，主机断电，删除当前日志，模拟文件损坏。SQL> conn wwl/wwlConnected. SQL> select * from tab;TNAME                          TABTYPE CLUSTERID------------------------------ -----------------WWL001                         TABLE SQL> create table WWL002 as select *from wwl001;Table created. SQL> conn / as sysdbaConnected. 切换日志SQL> alter system switch logfile;System altered. 后再创建第二张表SQL> conn wwl/wwlConnected. SQL> create table wwl003 as select *from wwl001;Table created. 查看当前日志组，确定当前活动的日志组，是组4SQL> conn / as sysdbaConnected.SQL> set line 200SQL> select * from v$log;    GROUP#    THREAD# SEQUENCE#     BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM---------- ---------- ---------- -------------------- --- ---------------- ------------- ---------         4          1          2 134217728          2 YES ACTIVE                 1716929 30-JUL-12        5          1          1 134217728          2 YES INACTIVE               1692728 27-JUL-12        6          1          3 134217728          2 NO CURRENT                1720396 30-JUL-12        7          1          0 134217728          2 YES UNUSED                       0 定位当前日志组的日志文件，有两个。 SQL> col member format a30SQL> select * from v$logfile;    GROUP# STATUS TYPE    MEMBER                         IS_---------- ------- ------------------------------------- ---        7         ONLINE /DBBak2/oradata/WWL/redo7a.log NO        7         ONLINE /DBBak2/oradata/WWL/redo7b.log NO        6         ONLINE /DBBak2/oradata/WWL/redo6a.log NO        6         ONLINE /DBBak2/oradata/WWL/redo6b.log NO        5 STALE   ONLINE /DBBak2/oradata/WWL/redo5a.log NO        5 STALE   ONLINE /DBBak2/oradata/WWL/redo5b.log NO         4         ONLINE /DBBak2/oradata/WWL/redo4a.log NO         4         ONLINE /DBBak2/oradata/WWL/redo4b.log NO 8 rows selected. 删除当前日志组文件，模拟在线事务丢失：SQL> ！rm -f/DBBak2/oradata/WWL/redo4a.logSQL> ！rm -f/DBBak2/oradata/WWL/redo4b.log 模拟服务器断电SQL> shutdown abort;Oracle instance shut down. 恢复步骤:1、尝试启动数据库的时候报当前日志丢失。SQL> startupORACLE instance started. Total System Global Area 100663296 bytesFixed Size                  1217884 bytesVariable Size              88083108 bytesDatabase Buffers            8388608 bytesRedo Buffers                2973696 bytesDatabase mounted.ORA-00313: open failedfor members of log group 4 of thread 1ORA-00312: online log 4thread 1: "/DBBak2/oradata/WWL/redo4b.log"ORA-27037: unable toobtain file statusLinux Error: 2: No suchfile or directoryAdditional information: 3ORA-00312: online log 4thread 1: "/DBBak2/oradata/WWL/redo4a.log"ORA-27037: unable toobtain file statusLinux Error: 2: No suchfile or directoryAdditional information: 3 2、尝试Clear redo4SQL> alter database clear logfile group4;alter database clear logfile group 4*ERROR at line 1:ORA-01624: log 4 needed for crash recoveryof instance WWL （thread 1）ORA-00312: online log 4 thread 1:"/DBBak2/oradata/WWL/redo4a.log"ORA-00312: online log 4 thread 1:"/DBBak2/oradata/WWL/redo4b.log" 3、在当前库做基于Cancel的不完全恢复SQL> recover database until cancel;ORA-00279: change 1716930 generated at07/30/2012 11:03:51 needed for thread 1ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_2_789761098.dbfORA-00280: change 1716930 for thread 1 isin sequence #2  Specify log: {<RET>=suggested |filename | AUTO | CANCEL}autoORA-00279: change 1720396 generated at07/30/2012 13:37:21 needed for thread 1ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_3_789761098.dbfORA-00280: change 1720396 for thread 1 isin sequence #3ORA-00278: log file"/DBSoft/product/10.2.0/db_1/dbs/arch1_2_789761098.dbf" no longer needed forthis recovery  ORA-00308: cannot open archived log"/DBSoft/product/10.2.0/db_1/dbs/arch1_3_789761098.dbf"ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3  ORA-01547: warning: RECOVER succeeded butOPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to beconsistentORA-01110: data file 1:"/DBBak2/oradata/WWL/system01.dbf" 做完恢复之后必须使用resetlogs选项打开数据库：SQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01194: file 1 needs more recovery to beconsistentORA-01110: data file 1:"/DBBak2/oradata/WWL/system01.dbf"     恢复之后，使用Resetlogs选项，仍无法打开数据库，提示数据文件不一致，System表空间需要进一步的恢复。
    当前日志损坏时，不能基于当前的数据库做不完全恢复。只能用以前的备份，做一个基于Cancel的不完全恢复。 重建下控制文件：SQL> CREATE CONTROLFILE REUSE DATABASE"WWL" RESETLOGS ARCHIVELOG   MAXLOGFILES 16   MAXLOGMEMBERS 3   "/DBBak2/oradata/WWL/redo4a.log",   MAXDATAFILES 100   MAXINSTANCES 8 GROUP 5 （   MAXLOGHISTORY 292LOGFILE GROUP 4 （   "/DBBak2/oradata/WWL/redo4a.log",   "/DBBak2/oradata/WWL/redo4b.log" GROUP 6 （）SIZE 128M, GROUP 5 （   "/DBBak2/oradata/WWL/redo5a.log",   "/DBBak2/oradata/WWL/redo5b.log"   "/DBBak2/oradata/WWL/redo7b.log" ）SIZE 128M, GROUP 6 （   "/DBBak2/oradata/WWL/redo6a.log",   "/DBBak2/oradata/WWL/redo6b.log" ）SIZE 128M, GROUP 7 （   "/DBBak2/oradata/WWL/redo7a.log",   "/DBBak2/oradata/WWL/redo7b.log" ）SIZE 128M-- STANDBY LOGFILEDATAFILE "/DBBak2/oradata/WWL/system01.dbf", "/DBBak2/oradata/WWL/undotbs01.dbf", "/DBBak2/oradata/WWL/sysaux01.dbf", "/DBBak2/oradata/WWL/users01.dbf", "/DBBak2/oradata/WWL/wwl001", "/DBBak2/oradata/WWL/wwl002", "/DBBak2/oradata/WWL/wwl003"CHARACTER SET ZHS16CGB231280 34 ; Control file created. 再次打开，结果还是不行SQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01194: file 1 needs more recovery to beconsistentORA-01110: data file 1:"/DBBak2/oradata/WWL/system01.dbf" 可以尝试使用_allow_resetlogs_corruption隐含参数来打开数据库SQL> alter system set"_allow_resetlogs_corruption"=true scope=spfile;System altered. 修改完参数之后重启数据库到mount状态SQL> shutdown immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down. SQL> startup;ORACLE instance started. Total System Global Area 100663296 bytesFixed Size                  1217884 bytesVariable Size              88083108 bytesDatabase Buffers            8388608 bytesRedo Buffers                2973696 bytesDatabase mounted.ORA-01589: must use RESETLOGS orNORESETLOGS option for database open 同样以resetlogs模式启动数据库SQL> alter database open resetlogs;Database altered. 一定记得关闭该参数SQL> alter system set"_allow_resetlogs_corruption"=FALSE scope=spfile;System altered. 让参数关闭生效，再次启动数据库SQL> startup force;ORACLE instance started. Total System Global Area 100663296 bytesFixed Size                  1217884 bytesVariable Size              88083108 bytesDatabase Buffers            8388608 bytesRedo Buffers                2973696 bytesDatabase mounted.Database opened. 检查_allow_resetlogs_corruption 隐含参数是否关闭，确定已经关闭SQL> show parameter_allow_resetlogs_corruptionNAME                                 TYPE        VALUE----------------------------------------------- ------------------------------_allow_resetlogs_corruption          boolean     FALSESQL> 因为重建了控制文件，默认是没有制定temp表空间，这里制定下：SQL> ALTER TABLESPACE TEMP01 ADDTEMPFILE "/DBBak2/oradata/WWL/temp01.dbf" REUSE;Tablespace altered. 检查数据SQL> conn wwl/wwlConnected.  SQL> select * from tab;TNAME                          TABTYPE CLUSTERID------------------------------ -----------------WWL001                         TABLEWWL002                         TABLE 可以看到表WWL002存在，表WWL003不存在。因为WWL003的创建，是保存在当前REDO日志中的，而当前REDO日志损坏，所有当前日志中保存的操作全部丢失了。
    归档日志、或者REDO日志损失，数据库就只能恢复到丢失的日志之前了。相关阅读：探索Oracle之RMAN_01概念 http://www.linuxidc.com/Linux/2012-05/60530.htm探索Oracle之RMAN_02基本使用 http://www.linuxidc.com/Linux/2012-05/60578.htm探索Oracle之RMAN_03非一致性备份 http://www.linuxidc.com/Linux/2012-05/61025.htm探索Oracle之RMAN_04非一致性备份 http://www.linuxidc.com/Linux/2012-05/61180.htm探索Oracle之RMAN_05增量备份 http://www.linuxidc.com/Linux/2012-05/61181.htm探索Oracle之RMAN_06备份策略 http://www.linuxidc.com/Linux/2012-05/61450.htm探索Oracle之RMAN_07单个数据文件丢失恢复 http://www.linuxidc.com/Linux/2012-06/63524.htm探索Oracle之RMAN_07整个业务表空间丢失恢复 http://www.linuxidc.com/Linux/2012-07/64582.htm探索Oracle之RMAN_07 磁盘损坏数据丢失恢复 http://www.linuxidc.com/Linux/2012-07/64588.htm探索Oracle之RMAN_07 数据库所有文件全部丢失恢复 http://www.linuxidc.com/Linux/2012-07/64587.htm探索Oracle之RMAN_07 重做日志redu文件丢失恢复 http://www.linuxidc.com/Linux/2012-07/64586.htm探索Oracle之RMAN_07 参数文件丢失恢复 http://www.linuxidc.com/Linux/2012-07/64585.htm探索Oracle之RMAN_07控制文件丢失恢复 http://www.linuxidc.com/Linux/2012-07/64584.htm探索Oracle之RMAN_07 system表空间丢失恢复 http://www.linuxidc.com/Linux/2012-07/64583.htm探索Oracle不完全恢复之--基于cancel恢复第一篇探索Oracle不完全恢复之--基于备份控制文件恢复相关资讯 Oracle不完全恢复

Oracle数据库完全恢复和不完全恢复（09/29/2014 20:13:21）
探索Oracle不完全恢复之--基于（07/31/2012 21:00:44）
探索Oracle不完全恢复之--基于SCN （07/31/2012 20:58:32）

探索Oracle不完全恢复之--基于备份（07/31/2012 21:02:22）
探索Oracle不完全恢复之--基于检查（07/31/2012 21:00:01）
探索Oracle不完全恢复之--基于时间（07/31/2012 20:57:33）

本文评论查看全部评论（0）

首页 / 数据库 / MySQL / 探索Oracle不完全恢复之--基于cancel的恢复 第二篇

首页 / 数据库 / MySQL / 探索Oracle不完全恢复之--基于cancel的恢复第二篇