ITPub博客

首页 > Linux操作系统 > Linux操作系统 > rman 恢复--current redo 损坏的情况

rman 恢复--current redo 损坏的情况

原创 Linux操作系统 作者:haozg_oracle 时间:2012-07-06 08:55:04 0 删除 编辑
用rman恢复,current redo 日志损坏的情况
1、做全库备份
RMAN> run{
 allocate channel c1 type disk maxpiecesize=500m;
 backup current controlfile format '+dgasm/backup/haozg/ctl_%d_%s';
 backup full database format '+dgasm/backup/haozg/db_%d_%s_%p_%t';
 release channel c1;
 }2> 3> 4> 5> 6>
released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=19 device type=DISK
Starting backup at 02-JUL-12
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 02-JUL-12
channel c1: finished piece 1 at 02-JUL-12
piece handle=+DGASM/backup/haozg/ctl_ora11_57 tag=TAG20120702T110101 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 02-JUL-12
Starting backup at 02-JUL-12
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DGASM/ora11/datafile/system.257.785186755
input datafile file number=00002 name=+DGASM/ora11/datafile/sysaux.258.785186845
input datafile file number=00003 name=+DGASM/ora11/datafile/undotbs1.259.785186901
input datafile file number=00004 name=+DGASM/ora11/datafile/users.272.787487359
channel c1: starting piece 1 at 02-JUL-12
channel c1: finished piece 1 at 02-JUL-12
piece handle=+DGASM/backup/haozg/db_ora11_58_1_787575668 tag=TAG20120702T110107 comment=NONE
channel c1: starting piece 2 at 02-JUL-12
channel c1: finished piece 2 at 02-JUL-12
piece handle=+DGASM/backup/haozg/db_ora11_58_2_787575668 tag=TAG20120702T110107 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:10
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 02-JUL-12
channel c1: finished piece 1 at 02-JUL-12
piece handle=+DGASM/backup/haozg/db_ora11_59_1_787575738 tag=TAG20120702T110107 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:02
Finished backup at 02-JUL-12
released channel: c1
RMAN>
2、做操作,让不同的操作分布在不同的归档日志文件中,并且检查日志情况和检查点情况
SQL> select first_change#,next_change#,group#,sequence#,status from v$log;
FIRST_CHANGE# NEXT_CHANGE#     GROUP#  SEQUENCE# STATUS
------------- ------------ ---------- ---------- ----------------
       999200       999353          1          7 INACTIVE
       999353       999426          2          8 INACTIVE
       999426   2.8147E+14          3          9 CURRENT
SQL> create table test22(aab001 varchar(15),aab003 varchar(20));
Table created.
SQL>  insert into test22 values ('soft','0001');
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test22;
AAB001          AAB003
--------------- --------------------
soft            0001
soft            0001
soft            0001
SQL> alter system switch logfile;
System altered.
SQL> select first_change#,next_change#,group#,sequence#,status from v$log;
FIRST_CHANGE# NEXT_CHANGE#     GROUP#  SEQUENCE# STATUS
------------- ------------ ---------- ---------- ----------------
      1000076   2.8147E+14          1         10 CURRENT
       999353       999426          2          8 INACTIVE
       999426      1000076          3          9 ACTIVE
SQL>  create table test33(aab001 number,aab004 varchar2(19));
Table created.
SQL> insert into test33 values(1,'zhang');
1 row created.
SQL> /.
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test33;
    AAB001 AAB004
---------- -------------------
         1 zhang
         1 zhang
         1 zhang
SQL> alter system switch logfile;
System altered.
SQL>  select first_change#,next_change#,group#,sequence#,status from v$log;
FIRST_CHANGE# NEXT_CHANGE#     GROUP#  SEQUENCE# STATUS
------------- ------------ ---------- ---------- ----------------
      1000076      1000117          1         10 ACTIVE
      1000117   2.8147E+14          2         11 CURRENT
       999426      1000076          3          9 ACTIVE
SQL> create table c_test(aac001 varchar2(10),name varchar2(13));
Table created.
SQL> insert into c_test values('1392027','haozg');
1 row created.
SQL> /
1 row created.
SQL> /.
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> select * from c_test;
AAC001     NAME
---------- -------------
1392027    haozg
1392027    haozg
1392027    haozg
1392027    haozg
SQL> select first_change#,next_change#,group#,sequence#,status from v$log;
FIRST_CHANGE# NEXT_CHANGE#     GROUP#  SEQUENCE# STATUS
------------- ------------ ---------- ---------- ----------------
      1000076      1000117          1         10 ACTIVE
      1000117   2.8147E+14          2         11 CURRENT
       999426      1000076          3          9 ACTIVE

3、shutdown abort
SQL> shutdown abort;
ORACLE instance shut down.
4、删除破坏current redo log
   建立1.txt 文件,覆盖current log
[oracle@haozg onlinelog]$ cat 1.txt
456789
[oracle@haozg onlinelog]$
[oracle@haozg onlinelog]$
[oracle@haozg onlinelog]$
[oracle@haozg onlinelog]$
[oracle@haozg onlinelog]$ ls -al
total 153792
drwxr-x--- 2 oracle oinstall     4096 Jul  2 11:08 .
drwxr-x--- 3 oracle oinstall     4096 Jun  5 08:35 ..
-rw-r--r-- 1 oracle oinstall        7 Jul  2 11:08 1.txt
-rw-r----- 1 oracle oinstall 52429312 Jul  2 11:04 o1_mf_1_7z21xdyh_.log
-rw-r----- 1 oracle oinstall 52429312 Jul  2 11:07 o1_mf_2_7z21xh53_.log
-rw-r----- 1 oracle oinstall 52429312 Jul  2 11:03 o1_mf_3_7z21xk0x_.log
[oracle@haozg onlinelog]$ mv 1.txt o1_mf_2_7z21xh53_.log
[oracle@haozg onlinelog]$ cat o1_mf_2_7z21xh53_.log
456789
[oracle@haozg onlinelog]$

5、启动数据库,看检查点
SQL> startup mount;
ORACLE instance started.
Total System Global Area  146472960 bytes
Fixed Size                  1335080 bytes
Variable Size              92274904 bytes
Database Buffers           50331648 bytes
Redo Buffers                2531328 bytes
Database mounted.
SQL> select first_change#,next_change#,group#,sequence#,status from v$log;
FIRST_CHANGE# NEXT_CHANGE#     GROUP#  SEQUENCE# STATUS
------------- ------------ ---------- ---------- ----------------
      1000076      1000117          1         10 INACTIVE
       999426      1000076          3          9 INACTIVE
      1000117   2.8147E+14          2         11 CURRENT
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1:
'/oracle/flash_recovery_area/ORA11/onlinelog/o1_mf_2_7z21xh53_.log'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1

SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
           1000117
           1000117
           1000117
           1000117
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
           1000117
           1000117
           1000117
           1000117
SQL> /
CHECKPOINT_CHANGE#
------------------
           1000117
           1000117
           1000117
           1000117
6、转储数据库
RMAN> restore database;
Starting restore at 02-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DGASM/ora11/datafile/system.257.785186755
channel ORA_DISK_1: restoring datafile 00002 to +DGASM/ora11/datafile/sysaux.258.785186845
channel ORA_DISK_1: restoring datafile 00003 to +DGASM/ora11/datafile/undotbs1.259.785186901
channel ORA_DISK_1: restoring datafile 00004 to +DGASM/ora11/datafile/users.272.787487359
channel ORA_DISK_1: reading from backup piece +DGASM/backup/haozg/db_ora11_58_1_787575668
channel ORA_DISK_1: piece handle=+DGASM/backup/haozg/db_ora11_58_1_787575668 tag=TAG20120702T110107
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: reading from backup piece +DGASM/backup/haozg/db_ora11_58_2_787575668
channel ORA_DISK_1: piece handle=+DGASM/backup/haozg/db_ora11_58_2_787575668 tag=TAG20120702T110107
channel ORA_DISK_1: restored backup piece 2
channel ORA_DISK_1: restore complete, elapsed time: 00:01:41
Finished restore at 02-JUL-12
7、检查数据文件头检查点
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
            999656
            999656
            999656
            999656
SQL>
8、恢复数据库
RMAN> recover database;
Starting recover at 02-JUL-12
using channel ORA_DISK_1
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/02/2012 11:14:45
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
 start
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oracle/flash_recovery_area/ORA11/onlinelog/o1_mf_2_7z21xh53_.log'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
注意上面的错误,提示redo 日志有问题,并且是current redo log,清掉或者删除的方法都不行。这种情况只有恢复到当前redo
的上一个sequence (10)对应的next_change#(1000117),也就是恢复到最近的归档日志,这样就只会丢失current redo对应的数据,最小化
数据库损失。
==========================================================================
SQL> select first_change#,next_change#,group#,sequence#,status from v$log;
FIRST_CHANGE# NEXT_CHANGE#     GROUP#  SEQUENCE# STATUS
------------- ------------ ---------- ---------- ----------------
      1000076      1000117          1         10 INACTIVE
       999426      1000076          3          9 INACTIVE
      1000117   2.8147E+14          2         11 CURRENT
==========================================================================

另外就是转储数据库后,数据文件头的检查点和没有转储前变小了,这样就会以控制文件为基准,来恢复数据库。
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
            999656
            999656
            999656
            999656
控制文件中记录的数据文件的scn 1000117
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
           1000117
           1000117
           1000117
           1000117
          
最近的归档日志的next_change# scn 1000117
SQL> select first_change#,next_change#,group#,sequence#,status from v$log;
FIRST_CHANGE# NEXT_CHANGE#     GROUP#  SEQUENCE# STATUS
------------- ------------ ---------- ---------- ----------------
      1000076      1000117          1         10 INACTIVE
       999426      1000076          3          9 INACTIVE
      1000117   2.8147E+14          2         11 CURRENT
所以恢复到 scn 1000117 就可以了。
继续recover
RMAN> recover database until scn 1000117;
Starting recover at 02-JUL-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 02-JUL-12
9、以resetlogs 方式打开数据库,并且检查数据
SQL> alter database open resetlogs;
Database altered.
SQL> connect haozg/haozg
Connected.
SQL> select * from test22;
AAB001          AAB003
--------------- --------------------
soft            0001
soft            0001
soft            0001
这个表存在说明应用了 sequence为9 的归档
SQL> select * from test33;
    AAB001 AAB004
---------- -------------------
         1 zhang
         1 zhang
         1 zhang
        
这个表存在说明应用了 sequence为10 的归档
SQL> select * from c_test;
select * from c_test
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>
这个表不存在说明current redo 日志对应的数据丢失。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23062014/viewspace-734749/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-12-22

  • 博文量
    37
  • 访问量
    156693