RMAN备份恢复（日志文件）

如果日志文件丢失会有几种情况   SQL> desc v$log;  Name                                           Null?    Type  ----------------------------------------- -------- ----------------------------  GROUP#                                             NUMBER  THREAD#                                            NUMBER  SEQUENCE#                                            NUMBER  BYTES                                                    NUMBER  BLOCKSIZE                                            NUMBER  MEMBERS                                            NUMBER  ARCHIVED                                            VARCHAR2(3)  STATUS                                             VARCHAR2(16)  FIRST_CHANGE#                                            NUMBER  FIRST_TIME                                            DATE  NEXT_CHANGE#                                            NUMBER  NEXT_TIME                                            DATE   SQL> select GROUP#,STATUS from v$log;       GROUP# STATUS ---------- ----------------  1 INACTIVE  2 INACTIVE  3 CURRENT   SQL> alter system switch logfile;   System altered.   SQL> /   System altered.   SQL> /   System altered   SQL> desc v$logfile;  Name                                           Null?    Type  ----------------------------------------- -------- ----------------------------  GROUP#                                             NUMBER  STATUS                                             VARCHAR2(7)  TYPE                                                    VARCHAR2(7)  MEMBER                                             VARCHAR2(513)  IS_RECOVERY_DEST_FILE                                    VARCHAR2(3)   SQL> select GROUP# ,MEMBER from v$logfile;       GROUP# ---------- MEMBER --------------------------------------------------------------------------------  3 /u01/app/oracle/oradata/enmoedu/redo03.log    2 /u01/app/oracle/oradata/enmoedu/redo02.log    1 /u01/app/oracle/oradata/enmoedu/redo01.log     我们可以把第一组给删除了； SQL>  alter database drop logfile group 1;   Database altered.   SQL>  select GROUP# ,MEMBER from v$logfile;       GROUP# ---------- MEMBER --------------------------------------------------------------------------------  3 /u01/app/oracle/oradata/enmoedu/redo03.log    2 /u01/app/oracle/oradata/enmoedu/redo02.log   [oracle@enmoedu enmoedu]$ ls control01.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbf example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf [oracle@enmoedu enmoedu]$ rm redo01.log [oracle@enmoedu enmoedu]$ ls control01.ctl  redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf example01.dbf  redo03.log  system01.dbf  undotbs01.dbf inactive的日志文件被删了之后，重建一份就可以了。 SQL> alter database add logfile '/u01/app/oracle/oradata/enmoedu/redo01.log' size 50m;   Database altered. 或者 SQL> alter database clera unarchived logfile group 1；   SQL>  select GROUP# ,MEMBER from v$logfile;       GROUP# ---------- MEMBER --------------------------------------------------------------------------------  3 /u01/app/oracle/oradata/enmoedu/redo03.log    2 /u01/app/oracle/oradata/enmoedu/redo02.log    1 /u01/app/oracle/oradata/enmoedu/redo01.log   [oracle@enmoedu enmoedu]$ ls control01.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbf example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf   SQL> select GROUP#,STATUS from v$log;       GROUP# STATUS ---------- ----------------  1 UNUSED  2 INACTIVE  3 CURRENT   SQL>  alter system switch logfile;---------切换日志之后，再查看日志组的状态；   System altered.   SQL> /   System altered.   SQL> / /     System altered.   SQL> System altered.   SQL>  select GROUP#,STATUS from v$log;       GROUP# STATUS ---------- ----------------  1 CURRENT  2 INACTIVE  3 INACTIVE     current的日志丢了会怎么办？？？ 马上切日志，重建一个，然后一致性关闭数据库，会发生检查点，检查点就会把赃块同步到数据文件中的，那么我们的1号current的文件还是能抢救回来的。 但是如果不马上切换日志呢？？？？会怎么样呢？？？ 这个时候我们shutdown abort，然后open，这个时候我们再重建一个日志文件的时候就会发生错误了！所以就没办法恢复啦。。。。。。。       [oracle@enmoedu enmoedu]$ ls control01.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbf example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf [oracle@enmoedu enmoedu]$ rm redo01.log [oracle@enmoedu enmoedu]$ ls control01.ctl  redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf example01.dbf  redo03.log  system01.dbf  undotbs01.dbf [oracle@enmoedu dbs]$ rman target/   Recovery Manager: Release 11.2.0.4.0 - Production on Sun Mar 13 14:30:49 2016   Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.   connected to target database: ENMOEDU (DBID=163971148, not open)   RMAN> restore database;   Starting restore at 13-MAR-16 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=10 device type=DISK   channel ORA_DISK_1: restoring datafile 00001 input datafile copy RECID=2 STAMP=906374531 file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/datafile/o1_mf_system_cg9nvvbr_.dbf destination for restore of datafile 00001: /u01/app/oracle/oradata/enmoedu/system01.dbf channel ORA_DISK_1: copied datafile copy of datafile 00001 output file name=/u01/app/oracle/oradata/enmoedu/system01.dbf RECID=0 STAMP=0 channel ORA_DISK_1: restoring datafile 00002 input datafile copy RECID=3 STAMP=906374541 file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/datafile/o1_mf_sysaux_cg9nwbfk_.dbf destination for restore of datafile 00002: /u01/app/oracle/oradata/enmoedu/sysaux01.dbf channel ORA_DISK_1: copied datafile copy of datafile 00002 output file name=/u01/app/oracle/oradata/enmoedu/sysaux01.dbf RECID=0 STAMP=0 channel ORA_DISK_1: restoring datafile 00003 input datafile copy RECID=5 STAMP=906374545 file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/datafile/o1_mf_undotbs1_cg9nwjpk_.dbf destination for restore of datafile 00003: /u01/app/oracle/oradata/enmoedu/undotbs01.dbf channel ORA_DISK_1: copied datafile copy of datafile 00003 output file name=/u01/app/oracle/oradata/enmoedu/undotbs01.dbf RECID=0 STAMP=0 channel ORA_DISK_1: restoring datafile 00004 input datafile copy RECID=7 STAMP=906374549 file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/datafile/o1_mf_users_cg9nwo1y_.dbf destination for restore of datafile 00004: /u01/app/oracle/oradata/enmoedu/users01.dbf channel ORA_DISK_1: copied datafile copy of datafile 00004 output file name=/u01/app/oracle/oradata/enmoedu/users01.dbf RECID=0 STAMP=0 channel ORA_DISK_1: restoring datafile 00005 input datafile copy RECID=4 STAMP=906374543 file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/datafile/o1_mf_example_cg9nwfhp_.dbf destination for restore of datafile 00005: /u01/app/oracle/oradata/enmoedu/example01.dbf channel ORA_DISK_1: copied datafile copy of datafile 00005 output file name=/u01/app/oracle/oradata/enmoedu/example01.dbf RECID=0 STAMP=0 Finished restore at 13-MAR-16   RMAN> recover database;   Starting recover at 13-MAR-16 using channel ORA_DISK_1   starting media recovery   archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_8_cg9o32xp_.arc archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_9_cg9o43cm_.arc archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_10_cg9onn02_.arc archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_11_cg9onog0_.arc archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_12_cg9onp7p_.arc archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_13_cg9th94w_.arc archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_14_cg9thcc0_.arc archived log for thread 1 with sequence 15 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_15_cg9thghw_.arc archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_16_cg9thgj2_.arc archived log for thread 1 with sequence 17 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_17_cg9thgmf_.arc archived log for thread 1 with sequence 18 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_18_cg9thmbn_.arc archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_19_cg9thmbt_.arc archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_20_cg9thmgd_.arc archived log for thread 1 with sequence 21 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_21_cg9vst0h_.arc archived log for thread 1 with sequence 22 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_22_cg9vsv8l_.arc archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_23_cg9vt0jm_.arc archived log for thread 1 with sequence 24 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_24_cg9ymdxn_.arc archived log for thread 1 with sequence 25 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_25_cg9ymfz2_.arc archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_26_cg9ymkqv_.arc archived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_27_cg9ymkr1_.arc archived log for thread 1 with sequence 28 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_28_cgb10og0_.arc archived log for thread 1 with sequence 29 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_29_cgb10q8y_.arc archived log for thread 1 with sequence 30 is already on disk as file /u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_30_cgb10t15_.arc archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_8_cg9o32xp_.arc thread=1 sequence=8 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_9_cg9o43cm_.arc thread=1 sequence=9 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_10_cg9onn02_.arc thread=1 sequence=10 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_11_cg9onog0_.arc thread=1 sequence=11 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_12_cg9onp7p_.arc thread=1 sequence=12 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_13_cg9th94w_.arc thread=1 sequence=13 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_14_cg9thcc0_.arc thread=1 sequence=14 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_15_cg9thghw_.arc thread=1 sequence=15 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_16_cg9thgj2_.arc thread=1 sequence=16 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_17_cg9thgmf_.arc thread=1 sequence=17 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_18_cg9thmbn_.arc thread=1 sequence=18 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_19_cg9thmbt_.arc thread=1 sequence=19 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_20_cg9thmgd_.arc thread=1 sequence=20 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_21_cg9vst0h_.arc thread=1 sequence=21 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_22_cg9vsv8l_.arc thread=1 sequence=22 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_23_cg9vt0jm_.arc thread=1 sequence=23 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_24_cg9ymdxn_.arc thread=1 sequence=24 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_25_cg9ymfz2_.arc thread=1 sequence=25 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_26_cg9ymkqv_.arc thread=1 sequence=26 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_27_cg9ymkr1_.arc thread=1 sequence=27 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_28_cgb10og0_.arc thread=1 sequence=28 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_29_cgb10q8y_.arc thread=1 sequence=29 archived log file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_30_cgb10t15_.arc thread=1 sequence=30 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 03/13/2016 14:31:31 ORA-00283: recovery session canceled due to errors RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/app/oracle/fast_recovery_area/ENMOEDU/archivelog/2016_03_13/o1_mf_1_30_cgb10t15_.arc' ORA-00283: recovery session canceled due to errors ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/enmoedu/redo01.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3   RMAN> recover database until sequence 30;------------因为current日志丢失，没办法恢复回来，所以我们只能恢复到sequence=30之前的数据了；   Starting recover at 13-MAR-16 using channel ORA_DISK_1   starting media recovery media recovery complete, elapsed time: 00:00:00   Finished recover at 13-MAR-16   SQL> alter database open resetlogs;-------------数据库open不了，做一次不完全恢复；清理日志，因为你恢复的是sequence=30之前的数据，所以后面的redo也就没意义了。   Database altered.   SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started.   Total System Global Area  830930944 bytes Fixed Size                    2257800 bytes Variable Size                  536874104 bytes Database Buffers          285212672 bytes Redo Buffers                    6586368 bytes Database mounted. Database opened. SQL> select GROUP#,STATUS from v$log;       GROUP# STATUS ---------- ----------------  1 CURRENT  2 UNUSED  3 UNUSED     RMAN> backup as copy database;-----------此时我们必须得重新做一遍全库备份了，因为刚刚恢复的是sequence=30之前的数据；   Starting backup at 13-MAR-16 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=17 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/enmoedu/system01.dbf output file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/datafile/o1_mf_system_cgb2rtry_.dbf tag=TAG20160313T143922 RECID=8 STAMP=906388767 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/u01/app/oracle/oradata/enmoedu/sysaux01.dbf output file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/datafile/o1_mf_sysaux_cgb2s1ts_.dbf tag=TAG20160313T143922 RECID=9 STAMP=906388772 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=/u01/app/oracle/oradata/enmoedu/example01.dbf output file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/datafile/o1_mf_example_cgb2s4wm_.dbf tag=TAG20160313T143922 RECID=10 STAMP=906388774 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/enmoedu/undotbs01.dbf output file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/datafile/o1_mf_undotbs1_cgb2s7yg_.dbf tag=TAG20160313T143922 RECID=11 STAMP=906388776 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting datafile copy copying current control file output file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/controlfile/o1_mf_TAG20160313T143922_cgb2s910_.ctl tag=TAG20160313T143922 RECID=12 STAMP=906388777 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/enmoedu/users01.dbf output file name=/u01/app/oracle/fast_recovery_area/ENMOEDU/datafile/o1_mf_users_cgb2sb7c_.dbf tag=TAG20160313T143922 RECID=13 STAMP=906388778 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 13-MAR-16 channel ORA_DISK_1: finished piece 1 at 13-MAR-16 piece handle=/u01/app/oracle/fast_recovery_area/ENMOEDU/backupset/2016_03_13/o1_mf_nnsnf_TAG20160313T143922_cgb2sccz_.bkp tag=TAG20160313T143922 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 13-MAR-16   SQL> select GROUP#,STATUS,SEQUENCE# from v$log;           GROUP# STATUS             SEQUENCE# ---------- ---------------- ----------  1 CURRENT                     1----------------------此时的sequence是1  2 UNUSED                     0  3 UNUSED                     0

不完全恢复：  先建表空间 SEQUENCE#=2的时候，没有建表，做一次全备，删除上一次的备份（为了节省空间） SEQUENCE#=3的时候，建表T1 SEQUENCE#=4的时候，向表T1中插入数据1，A SEQUENCE#=5的时候，向表T2中插入数据2 ,  B SEQUENCE#=6的时候，向表T2中插入数据3，C 如果现在想恢复2，B的时候，只需要恢复到5号归档-------------restore  database  from（在有很多备份的情况下，如果你想恢复到你想要的状态，就得加from，如果不加，数据库就找最近备份的） -------RMAN> restore database； -------RMAN> recover database until sequence 6;------------6不读 打开必须清理在线redo（alter database open resetlogs） 如果我现在想恢复1，A的数据。只需要恢复到4号归档 -------RMAN> restore database； -------RMAN> recover database until sequence 4;   RMAN> list incarnation;-------看数据有几次化身，也就是数据库被resetlog几次；resetlog之后得有全库备份的好习惯；   using target database control file instead of recovery catalog   List of Database Incarnations DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1       1       ENMOEDU  163971148        PARENT  1          24-AUG-13 2       2       ENMOEDU  163971148        PARENT  925702     25-FEB-16 3       3       ENMOEDU  163971148        CURRENT 1036755    13-MAR-16

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

转载于:http://blog.itpub.net/30606702/viewspace-2058426/