Category Archives: rman备份/恢复

通过sql查询rman备份信息

Posted on by

查看所有备份集

    SELECT A.RECID "BACKUP SET",
         A.SET_STAMP,
         DECODE (B.INCREMENTAL_LEVEL,
                 '', DECODE (BACKUP_TYPE, 'L', 'Archivelog', 'Full'),
                 1, 'Incr-1级',
                 0, 'Incr-0级',
                 B.INCREMENTAL_LEVEL)
            "Type LV",
         B.CONTROLFILE_INCLUDED "包含CTL",
         DECODE (A.STATUS,
                 'A', 'AVAILABLE',
                 'D', 'DELETED',
                 'X', 'EXPIRED',
                 'ERROR')
            "STATUS",
         A.DEVICE_TYPE "Device Type",
         A.START_TIME "Start Time",
         A.COMPLETION_TIME "Completion Time",
         A.ELAPSED_SECONDS "Elapsed Seconds",
         --a.BYTES/1024/1024/1024 "大小(G)",
         --a.COMPRESSED,
         A.TAG "Tag",
         A.HANDLE "Path"
    FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B
   WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO'
ORDER BY A.COMPLETION_TIME DESC;

查找某个备份集中包含数据文件

SELECT distinct c.file#,A.SET_STAMP, D.NAME, C.CHECKPOINT_CHANGE#, C.CHECKPOINT_TIME
  FROM V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$DATAFILE D
 WHERE A.SET_STAMP = C.SET_STAMP
AND D.FILE# = C.FILE#
AND A.DELETED='NO'
AND c.set_stamp=&set_stamp
ORDER BY C.FILE#;

查询某个备份集中控制文件

SELECT DISTINCT A.SET_STAMP,
                D.NAME,
                C.CHECKPOINT_CHANGE#,
                C.CHECKPOINT_TIME
  FROM V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$CONTROLFILE D
 WHERE A.SET_STAMP = C.SET_STAMP
   AND C.FILE# = 0
   AND A.DELETED = 'NO'
   AND C.SET_STAMP = &SET_STAMP;

查看某个备份集中归档日志

SELECT DISTINCT B.SET_STAMP,
                B.THREAD#,
                B.SEQUENCE#,
                B.FIRST_TIME,
                B.FIRST_CHANGE#,
                B.NEXT_TIME,
                B.NEXT_CHANGE#
  FROM V$BACKUP_REDOLOG B, V$BACKUP_PIECE A
 WHERE A.SET_STAMP = B.SET_STAMP
   AND A.DELETED = 'NO'
   AND B.SET_STAMP = &SET_STAMP
 ORDER BY THREAD#, SEQUENCE#;

查看某个备份集SPFILE

SELECT DISTINCT A.SET_STAMP, B.COMPLETION_TIME, HANDLE
  FROM V$BACKUP_SPFILE B, V$BACKUP_PIECE A
 WHERE A.SET_STAMP = B.SET_STAMP
   AND A.DELETED = 'NO'
   AND B.SET_STAMP = &SET_STAMP;

rman配置信息

SELECT NAME,VALUE FROM V$RMAN_CONFIGURATION;

处理fast_recovery_area无剩余空间案例

Posted on by

一、打开数据库报错

[oracle@node1 ora11g]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 19 15:42:04 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
SQL> alter database mount;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 19489
Session ID: 96 Serial number: 1

二、错误信息
1.alert日志内容

Mon Dec 19 15:23:29 2011
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Started redo scan
Completed redo scan
 read 0 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 3, block 93169, scn 12899730
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
  Mem# 0: /opt/oracle/oradata/ora11g/redo03.log
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 3, block 93169, scn 12919731
 0 data blocks read, 0 data blocks written, 0 redo k-bytes read
Mon Dec 19 15:23:29 2011
LGWR: STARTING ARCH PROCESSES
Mon Dec 19 15:23:29 2011
ARC0 started with pid=27, OS id=19539 
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Mon Dec 19 15:23:30 2011
ARC1 started with pid=28, OS id=19548 
Mon Dec 19 15:23:30 2011
ARC2 started with pid=29, OS id=19550 
Mon Dec 19 15:23:30 2011
ARC3 started with pid=30, OS id=19552 
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_arc2_19550.trc:
ORA-19815: 警告: db_recovery_file_dest_size 字节 (共 4322230272 字节) 已使用 100.00%, 尚有 0 字节可用。
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
ARC2: Error 19809 Creating archive log file to '/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_2_%u_.arc'
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_19489.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4322230272 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to '/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_1_%u_.arc'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance ora11g - Archival Error
ORA-16038: 日志 2 sequence# 2 无法归档
ORA-19809: 超出了恢复文件数的限制
ORA-00312: 联机日志 2 线程 1: '/opt/oracle/oradata/ora11g/redo02.log'
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_19489.trc:
ORA-16038: log 1 sequence# 1 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/ora11g/redo01.log'
USER (ospid: 19489): terminating the instance due to error 16038
Mon Dec 19 15:23:31 2011
System state dump requested by (instance=1, osid=19489), summary=[abnormal instance termination].
System State dumped to trace file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_diag_19463.trc
Dumping diagnostic data in directory=[cdmp_20111219152331], requested by (instance=1, osid=19489), summary=[abnormal instance termination].
Instance terminated by USER, pid = 19489

2.trace文件信息(ora11g_ora_19489.trc)

*** 2011-12-19 15:23:31.026 4320 krsh.c
ARCH: Error 19809 Creating archive log file to '/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_1_%u_.arc'
*** 2011-12-19 15:23:31.026 2932 krsi.c
krsi_dst_fail: dest:1 err:19809 force:0 blast:1
DDE: Problem Key 'ORA 312' was flood controlled (0x1) (no incident)
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/ora11g/redo01.log'
ORA-16038: log 1 sequence# 1 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/ora11g/redo01.log'
kjzduptcctx: Notifying DIAG for crash event
----- Abridged Call Stack Trace -----
ksedsts()+461<-kjzdssdmp()+267<-kjzduptcctx()+232<-kjzdicrshnfy()+53<-ksuitm()+1332<-kcfopd()+5962<-adbdrv()+51834<-opiexe()+18384<-opiosq0()+3870<-kpooprx()
+274<-kpoal8()+829<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opidrv()+570
<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+252 
----- End of Abridged Call Stack Trace -----

这个错误很明显：因为数据库归档日志放置在fast_recovery_area中，而空间已满，导致联机日志sequence# 2不能被归档，数据库无法打开

三、修改db_recovery_file_dest_size,打开数据库

SQL>  Alter system set db_recovery_file_dest_size=20G scope=both;

System altered.

SQL> alter database open;

Database altered.

设置较大db_recovery_file_dest_size，先打开数据库，再解决问题(减少down机时间是dba一大准则)

四、删除历史归档日志
从sequence# 为2中很明显看出来，数据库进行了resetlogs打开，所以前面的归档日志，在原则上已经无效(不再使用原始备份集恢复)，因为数据库的控制文件中，无原归档日志信息，所以无法使用rman删除归档日志。那只能使用os命令先删除掉历史归档日志，然后再使用rman处理

[root@node1 archivelog]# find ./ -mtime +1| xargs rm -rf
[root@node1 archivelog]# ll
总计 72
drwxr-x--- 2 oracle oinstall 4096 12-18 22:35 2011_12_18
drwxr-x--- 2 oracle oinstall 4096 12-19 13:25 2011_12_19

RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
validation failed for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_10_31/o1_mf_1_5_7bxbhkof_.arc RECID=1 STAMP=766015219
validation failed for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_11_01/o1_mf_1_6_7bxw2gpo_.arc RECID=2 STAMP=766033231
……………………………………
validation failed for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_105_7gc3co97_.arc RECID=132 STAMP=770306728
validation failed for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_106_7gc3cv1w_.arc RECID=123 STAMP=770306728
validation failed for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_107_7gc3mbpr_.arc RECID=127 STAMP=770306728
validation succeeded for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_1_7gxtrlnq_.arc RECID=134 STAMP=770312597
validation succeeded for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_2_7gxtrloz_.arc RECID=135 STAMP=770312597
validation succeeded for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_3_7gxtrodg_.arc RECID=136 STAMP=770312599
validation failed for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.arc RECID=126 STAMP=770306728
……………………………………
validation succeeded for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_20_7gxlq29k_.arc RECID=113 STAMP=770306728
validation succeeded for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_21_7gxl3zdm_.arc RECID=114 STAMP=770306728
Crosschecked 136 objects

RMAN> DELETE EXPIRED  archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
List of Archived Log Copies for database with db_unique_name ORA11G
……………………………………
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
……………………………………
deleted archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_16/o1_mf_1_14_7gpood3n_.arc RECID=115 STAMP=770306728
deleted archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_17/o1_mf_1_15_7gqhvvhh_.arc RECID=106 STAMP=770306727
Deleted 124 EXPIRED objects

五、补充说明
1.使用os命令删除fast_recovery_area内容后，需要使用crosscheck检测(如:archivelog all,backup等)。
2.然后使用 DELETE EXPIRED命令删除(archivelog all,backup等)
3.fast_recovery_area设置合适大小+合适的策略
4.resetlogs打开数据库后，做好备份
5.fast_recovery_area无剩余空间处理思路
5.1)如果数据库不能登录：重启至mount，增大fast_recovery_area，open数据库，然后使用rman删除历史垃圾数据(备份集,日志,闪回日志等)
5.2)如果数据库可以使用sys登录，增大fast_recovery_area(使其数据库可以正常工作)，然后使用rman处理垃圾数据

使用dbms_backup_restore包恢复数据库

Posted on by

Oracle提供了一个包:DBMS_BACKUP_RESTORE.DBMS_BACKUP_RESTORE 包是由dbmsbkrs.sql 和 prvtbkrs.plb 这两个脚本创建的.catproc.sql 脚本运行后会调用这两个包.所以是每个数据库都有的这个包是Oracle服务器和操作系统之间IO操作的接口.由恢复管理器直接调用。而且据说这两个脚本的功能是内建到Oracle的一些库文件中的.
由此可见，我们可以在数据库 nomount 情况下调用这些package ,来达到我们的恢复目的
1、启动数据库到nomount状态

[oracle@node1 ora11g]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 19 13:34:22 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes

2、恢复controlfile

SQL> DECLARE
  2  devtype varchar2(256);
  3  done boolean;
  4  BEGIN
  5  devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
  6  sys.dbms_backup_restore.restoreSetDatafile;
  7  sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/opt/oracle/oradata/ora11g/control01.ctl');
  8  sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/ora11g_02mu1avd_1_1.rman', params=>null);
  9  sys.dbms_backup_restore.deviceDeallocate;
 10  END;
 11  /

PL/SQL procedure successfully completed.

3、恢复数据文件

SQL> DECLARE
  2  devtype varchar2(256);
  3  done boolean;
  4  BEGIN
  5  devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
  6  sys.dbms_backup_restore.restoreSetDatafile;
  7  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>1,toname=>'/opt/oracle/oradata/ora11g/system01.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>2,toname=>'/opt/oracle/oradata/ora11g/sysaux01.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>3,toname=>'/opt/oracle/oradata/ora11g/undotbs01.dbf');
  8    9   10  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>4,toname=>'/opt/oracle/oradata/ora11g/users01.dbf');
 11  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>5,toname=>'/opt/oracle/oradata/ora11g/example01.dbf');
 12  sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/ora11g_01mu1aqq_1_1.rman', params=>null);
 13  sys.dbms_backup_restore.deviceDeallocate;
 14  END;
 15  /

PL/SQL procedure successfully completed.

4、启动数据库

SQL> alter database mount;
SQL> alter database recover until cancel using backup controlfile;
SQL> alter database open resetlogs;

5、特别说明
在oracle 10g及其以后版本中，因为rman中有catalog with start命令，可以实现rman备份的加载，所以不需要使用dbms_backup_restore包处理，在oracle 9i及其以前版本中，可能因为没有catalog库，控制文件中又没有了备份集信息，需要采用这种方法处理数据文件还原，然后根据实际情况，使用ALTER DATABASE REGISTER LOGFILE 添加日志，进行恢复

模拟跨resetlogs恢复

Posted on by

一、模拟跨越resetlog恢复环境

[oracle@node1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 12 13:09:29 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA11G (DBID=4162194039)

RMAN>  list incarnation;

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       ORA11G   4162194039       PARENT  1          2011-09-17 09:46:04
2       2       ORA11G   4162194039       CURRENT 995548     2011-10-31 16:05:14

RMAN> backup database format '/tmp/ora11g_%U.rman';

Starting backup at 2011-12-12 13:10:49
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=161 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/opt/oracle/oradata/ora11g/sysaux01.dbf
input datafile file number=00001 name=/opt/oracle/oradata/ora11g/system01.dbf
input datafile file number=00005 name=/opt/oracle/oradata/ora11g/example01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/ora11g/users01.dbf
input datafile file number=00003 name=/opt/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 2011-12-12 13:10:50
channel ORA_DISK_1: finished piece 1 at 2011-12-12 13:13:17
piece handle=/tmp/ora11g_01mu1aqq_1_1.rman tag=TAG20111212T131049 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:27
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2011-12-12 13:13:20
channel ORA_DISK_1: finished piece 1 at 2011-12-12 13:13:22
piece handle=/tmp/ora11g_02mu1avd_1_1.rman tag=TAG20111212T131049 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 2011-12-12 13:13:22

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    2137886720 bytes

Fixed Size                     2230072 bytes
Variable Size               1493174472 bytes
Database Buffers             637534208 bytes
Redo Buffers                   4947968 bytes

RMAN> recover database;

Starting recover at 2011-12-12 13:14:56
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 2011-12-12 13:14:58

RMAN>  alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/12/2011 13:15:14
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

RMAN> exit


Recovery Manager complete.
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 12 13:16:02 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> recover database until cancel; 
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

[oracle@node1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 12 13:17:47 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA11G (DBID=4162194039)

RMAN> list incarnation;

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       ORA11G   4162194039       PARENT  1          2011-09-17 09:46:04
2       2       ORA11G   4162194039       PARENT  995548     2011-10-31 16:05:14
3       3       ORA11G   4162194039       CURRENT 12881971   2011-12-12 13:17:30

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 12 13:18:34 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

[oracle@node1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 12 13:18:53 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA11G (DBID=4162194039)

RMAN> backup database format '/tmp/ora11g_new_%U';

Starting backup at 2011-12-12 13:19:30
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=160 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/opt/oracle/oradata/ora11g/sysaux01.dbf
input datafile file number=00001 name=/opt/oracle/oradata/ora11g/system01.dbf
input datafile file number=00005 name=/opt/oracle/oradata/ora11g/example01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/ora11g/users01.dbf
input datafile file number=00003 name=/opt/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 2011-12-12 13:19:31
channel ORA_DISK_1: finished piece 1 at 2011-12-12 13:20:56
piece handle=/tmp/ora11g—_new_03mu1bb3_1_1 tag=TAG20111212T131931 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2011-12-12 13:20:57
channel ORA_DISK_1: finished piece 1 at 2011-12-12 13:20:58
piece handle=/tmp/ora11g—_new_04mu1bdo_1_1 tag=TAG20111212T131931 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2011-12-12 13:20:58

RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile
--备份成功后还切换日志，为了证明可以利用这些日志恢复

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> exit

Recovery Manager complete.
[oracle@node1 ~]$ cd /opt/oracle/oradata/ora11g/
[oracle@node1 ora11g]$ ll
总计 2568524
-rw-r----- 1 oracle oinstall   9846784 12-12 13:27 control01.ctl
-rw-r----- 1 oracle oinstall 362422272 12-12 13:22 example01.dbf
-rw-r----- 1 oracle oinstall  52429312 12-12 13:22 redo01.log
-rw-r----- 1 oracle oinstall  52429312 12-12 13:22 redo02.log
-rw-r----- 1 oracle oinstall  52429312 12-12 13:26 redo03.log
-rw-r----- 1 oracle oinstall 828383232 12-12 13:22 sysaux01.dbf
-rw-r----- 1 oracle oinstall 765468672 12-12 13:22 system01.dbf
-rw-r----- 1 oracle oinstall 235937792 12-11 18:05 temp01.dbf
-rw-r----- 1 oracle oinstall  99622912 12-12 13:22 undotbs01.dbf
-rw-r----- 1 oracle oinstall 169091072 12-12 13:22 users01.dbf

[oracle@node1 ora11g]$ rm redo0*
[oracle@node1 ora11g]$ rm *.dbf
[oracle@node1 ora11g]$ ll
总计 9636
-rw-r----- 1 oracle oinstall 9846784 12-12 13:31 control01.ctl
--删除除控制文件外的所有文件

--今天产生的归档日志，从这个里面也可以看到resetlogs操作的界限
[oracle@node1 ora11g]$ ll /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/
总计 152120
-rw-r----- 1 oracle oinstall 47702528 12-12 13:13 o1_mf_1_104_7gc3cogp_.arc
-rw-r----- 1 oracle oinstall 47702528 12-12 13:13 o1_mf_1_105_7gc3co97_.arc
-rw-r----- 1 oracle oinstall 47702528 12-12 13:13 o1_mf_1_106_7gc3cv1w_.arc
-rw-r----- 1 oracle oinstall 11425792 12-12 13:17 o1_mf_1_107_7gc3mbpr_.arc
-rw-r----- 1 oracle oinstall   984576 12-12 13:18 o1_mf_1_1_7gc3ojqw_.arc
-rw-r----- 1 oracle oinstall     1024 12-12 13:18 o1_mf_1_2_7gc3okx8_.arc
-rw-r----- 1 oracle oinstall     4608 12-12 13:18 o1_mf_1_3_7gc3onnq_.arc
-rw-r----- 1 oracle oinstall     1536 12-12 13:22 o1_mf_1_4_7gc3wnvf_.arc
-rw-r----- 1 oracle oinstall     1024 12-12 13:22 o1_mf_1_5_7gc3wt48_.arc

二、跨越resetlogs恢复

[oracle@node1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 12 13:22:50 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    2137886720 bytes

Fixed Size                     2230072 bytes
Variable Size               1493174472 bytes
Database Buffers             637534208 bytes
Redo Buffers                   4947968 bytes

RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORA11G   4162194039       PARENT  1          2011-09-17 09:46:04
2       2       ORA11G   4162194039       PARENT  995548     2011-10-31 16:05:14
3       3       ORA11G   4162194039       CURRENT 12881971   2011-12-12 13:17:30

--指定incarnation恢复
RMAN> reset database to incarnation 2;

database reset to incarnation 2

RMAN> restore database;

Starting restore at 2011-12-12 13:33:25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 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 /opt/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /opt/oracle/oradata/ora11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /opt/oracle/oradata/ora11g/example01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/ora11g_01mu1aqq_1_1.rman
channel ORA_DISK_1: piece handle=/tmp/ora11g_01mu1aqq_1_1.rman tag=TAG20111212T131049
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
Finished restore at 2011-12-12 13:34:51
--证明恢复会使用incarnation 2对应的备份集

RMAN> recover database;

Starting recover at 2011-12-12 13:35:13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 106 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_106_7gc3cv1w_.arc
archived log for thread 1 with sequence 107 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_107_7gc3mbpr_.arc
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_106_7gc3cv1w_.arc thread=1 sequence=106
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_107_7gc3mbpr_.arc thread=1 sequence=107
media recovery complete, elapsed time: 00:00:03
Finished recover at 2011-12-12 13:35:18
--incarnation 2数据还原恢复完成

--指定恢复incarnation 3归档日志
RMAN>  reset database to incarnation 3;

database reset to incarnation 3

RMAN> recover database;

Starting recover at 2011-12-12 13:49:36
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.arc
archived log for thread 1 with sequence 2 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_2_7gc3okx8_.arc
archived log for thread 1 with sequence 3 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_3_7gc3onnq_.arc
archived log for thread 1 with sequence 4 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_4_7gc3wnvf_.arc
archived log for thread 1 with sequence 5 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_5_7gc3wt48_.arc
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.arc thread=1 sequence=1
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_2_7gc3okx8_.arc thread=1 sequence=2
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_3_7gc3onnq_.arc thread=1 sequence=3
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_4_7gc3wnvf_.arc thread=1 sequence=4
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_5_7gc3wt48_.arc thread=1 sequence=5
unable to find archived log
archived log thread=1 sequence=6
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/12/2011 13:49:39
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6 and starting SCN of 12882851
--缺少归档日志，恢复完seq=5的归档日志，属于正常情况

--因为没有redo，进行的是不完全恢复，使用resetlogs开打数据库
RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/12/2011 14:06:04
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], []
Process ID: 26406
Session ID: 96 Serial number: 7
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-03114: not connected to ORACLE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/12/2011 14:06:04
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], []
Process ID: 26406
Session ID: 96 Serial number: 7
--发现意外出现ORA-00600[2898]错误，打开数据库终止

三、查看alert日志

Mon Dec 12 14:05:59 2011
SMON: enabling cache recovery
[26406] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:1208679594 end:1208679684 diff:90 (0 seconds)
Dictionary check beginning
File #5 is offline, but is part of an online tablespace.
data file 5: '/opt/oracle/oradata/ora11g/example01.dbf'
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_26406.trc  (incident=4953):
ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], []
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_4953/ora11g_ora_26406_i4953.trc
Mon Dec 12 14:06:02 2011
Dumping diagnostic data in directory=[cdmp_20111212140602], requested by (instance=1, osid=26406), summary=[incident=4953].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_26406.trc:
ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], []
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_26406.trc:
ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 26406): terminating the instance due to error 600
Mon Dec 12 14:06:03 2011
Instance terminated by USER, pid = 26406
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (26406) as a result of ORA-1092
Mon Dec 12 14:06:04 2011
ORA-1092 : opitsk aborting process

发现ORA-00600[2898]错误，导致数据库abort，因为这个错误暂时未找到权威说明。初步怀疑是因为在resetlogs时候，遇到File #5 is offline, but is part of an online tablespace导致

四、重新开启数据库

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 12 14:08:28 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
Database mounted.
SQL> alter database open;

Database altered.

SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;

     FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
         5 OFFLINE      12881970

SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;

     FILE# TO_CHAR(CHECK
---------- -------------
         1      12902896
         2      12902896
         3      12902896
         4      12902896
         5             0

SQL> select FILE#,to_char(checkpoint_change#,'999999999999') from v$datafile_header;

     FILE# TO_CHAR(CHECK
---------- -------------
         1      12902896
         2      12902896
         3      12902896
         4      12902896
         5      12881970

意外的发现数据库竟然open成功，从这里可以看到datafile 5处于offline状态，而且其数据文件头部scn比其他文件小，怀疑没有恢复到一致状态

五、查看重启数据库后alert日志

Archived Log entry 109 added for thread 1 sequence 1 ID 0xf84e7829 dest 1:
File #5 is offline, but is part of an online tablespace.
data file 5: '/opt/oracle/oradata/ora11g/example01.dbf'
Mon Dec 12 14:09:01 2011
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw0_28180.trc:
ORA-01157: ????/?????? 201 - ??? DBWR ????
ORA-01110: ???? 201: '/opt/oracle/oradata/ora11g/temp01.dbf'
ORA-27037: ????????
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw0_28180.trc:
ORA-01186: ?? 201 ??????
ORA-01157: ????/?????? 201 - ??? DBWR ????
ORA-01110: ???? 201: '/opt/oracle/oradata/ora11g/temp01.dbf'
File 201 not verified due to error ORA-01157
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Re-creating tempfile /opt/oracle/oradata/ora11g/temp01.dbf
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28225.trc  (incident=6153):
ORA-25319: Queue table repartitioning aborted
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_6153/ora11g_ora_28225_i6153.trc
error 25319 happened during Queue table repartitioning
Starting background process QMNC
Mon Dec 12 14:09:03 2011
QMNC started with pid=31, OS id=28288 
LOGSTDBY: Validating controlfile with logical metadata
Mon Dec 12 14:09:04 2011
Dumping diagnostic data in directory=[cdmp_20111212140904], requested by (instance=1, osid=28225), summary=[incident=6153].
LOGSTDBY: Validation complete
Completed: alter database open
Mon Dec 12 14:09:04 2011
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_mmon_28190.trc  (incident=6121):
ORA-25319: 队列表重新分区已中止
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_6121/ora11g_mmon_28190_i6121.trc
error 25319 happened during Queue table repartitioning
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_mmon_28190.trc  (incident=6122):
ORA-25319: 队列表重新分区已中止
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_6122/ora11g_mmon_28190_i6122.trc
Dumping diagnostic data in directory=[cdmp_20111212140906], requested by (instance=1, osid=28190 (MMON)), summary=[incident=6121].
error 25319 happened during Queue table repartitioning
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_mmon_28190.trc  (incident=6123):
ORA-25319: 队列表重新分区已中止
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_6123/ora11g_mmon_28190_i6123.trc
Dumping diagnostic data in directory=[cdmp_20111212140907], requested by (instance=1, osid=28190 (MMON)), summary=[incident=6122].
error 25319 happened during Queue table repartitioning
Dumping diagnostic data in directory=[cdmp_20111212140908], requested by (instance=1, osid=28190 (MMON)), summary=[incident=6123].
Mon Dec 12 14:09:08 2011
db_recovery_file_dest_size of 4122 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Dec 12 14:09:08 2011
Starting background process CJQ0
Mon Dec 12 14:09:08 2011
CJQ0 started with pid=32, OS id=28308 
Mon Dec 12 14:09:39 2011
Sweep [inc][6153]: completed
Sweep [inc][6123]: completed
Sweep [inc][6122]: completed

因为第一个resetlogs没有成功，所以temp文件没有创建，这里先创建了temp文件.还有个ORA-25319的错误，和datafile 5 offline有关系

六、trace文件中内容

*** 2011-12-12 14:17:46.627
Started Serial Media Recovery
Datafile 5 belongs to incarnation with resetlogs SCN : 12881971, timestamp: 2de0acea
Media Recovery apply resetlogs offline range for datafile 5, incarnation : 1
Datafile 5 belongs to incarnation with resetlogs SCN : 12881971, timestamp: 2de0acea
Dumping database incarnation table:
Resetlogs 0 scn and time: 0x0000.00c493a4 12/12/2011 14:05:53
Resetlogs 1 scn and time: 0x0000.00c49033 12/12/2011 13:17:30
Recovery target incarnation = 4, activation ID = 0
Influx buffer limit = 37449 min(50% x 74898, 100000)
Start recovery at thread 1 ckpt scn 12881971 logseq 1 block 2
Initial buffer sizes: read 1024K, overflow 832K, change 805K

*** 2011-12-12 14:17:46.725
Media Recovery add redo thread 1

*** 2011-12-12 14:18:47.348
Media Recovery Log 2011_12_12/o1_mf_1_1_7gc3ojqw_.arc

*** 2011-12-12 14:19:00.198
Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
Datafile 5 belongs to incarnation with resetlogs SCN : 12881971, timestamp: 2de0acea

*** 2011-12-12 14:19:15.911
Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_2_7gc3okx8_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

*** 2011-12-12 14:19:22.638
Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_3_7gc3onnq_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

*** 2011-12-12 14:19:31.007
Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_4_7gc3wnvf_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
Datafile 5 belongs to incarnation with resetlogs SCN : 12881971, timestamp: 2de0acea

*** 2011-12-12 14:19:37.116
Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_5_7gc3wt48_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
Initial buffer sizes: read 1024K, overflow 832K, change 805K
Thread 1 initialized for new incarnation 1 at scn 12882852
Media Recovery current incarnation depth : 0
File 5 (stop scn 12882852) completed recovery at checkpoint scn 12882852

从这里可以知道，datafile 5，没有应用o1_mf_1_5_7gc3wt48_.arc日志恢复，那么恢复datafile 5 需要o1_mf_1_5_7gc3wt48_.arc日志文件

七、恢复数据文件5，打开数据库

SQL> recover datafile 5 ;
ORA-00279: change 12881971 generated at 12/12/2011 13:14:05 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.
arc
ORA-00280: change 12881971 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_5_7gc3wt48_.arc
Log applied.
Media recovery complete.

SQL> alter database datafile 5 online;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
Database mounted.
Database opened.
SQL>

rman从多份备份中还原操作

Posted on by

1、现象重现

RMAN> shutdown immediate

database dismounted
Oracle instance shut down

RMAN> startup

connected to target database (not started)
Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 11/18/2011 14:40:40
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/opt/oracle/oradata/test/xifenfei03.dbf'

RMAN> restore datafile 11;

Starting restore at 2011-11-18 14:41:04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: restoring datafile 00011
input datafile copy recid=13 stamp=767543949 filename=/tmp/11.dbf
destination for restore of datafile 00011: /opt/oracle/oradata/test/xifenfei03.dbf
ORA-19505: failed to identify file "/tmp/11.dbf"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-19600: input file is datafile-copy 13 (/tmp/11.dbf)
ORA-19601: output file is datafile 11 (/opt/oracle/oradata/test/xifenfei03.dbf)
failover to previous backup

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00011 to /opt/oracle/oradata/test/xifenfei03.dbf
channel ORA_DISK_1: reading from backup piece /tmp/test_full_01mrkqdh_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/tmp/test_full_01mrkqdh_1_1 tag=TAG20111114T124433
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 2011-11-18 14:41:08

虽然整个过程datafile 11恢复成功了，但是在恢复过程中，先是去读取/tmp/11.dbf的copy文件，读取这个文件失败，然后继续使用/tmp/test_full_01mrkqdh_1_1来恢复数据文件，为什么会出现这样的情况呢？

2、原因分析

RMAN> list copy of datafile 11;


List of Datafile Copies
Key     File S Completion Time     Ckp SCN    Ckp Time            Name
------- ---- - ------------------- ---------- ------------------- ----
13      11   A 2011-11-18 14:39:09 11517136   2011-11-14 12:44:33 /tmp/11.dbf
--确实存在datafile 11的copy文件/tmp/11.dbf,并且有效
--checkpoint time为:2011-11-14 12:44:33，创建时间为:2011-11-18 14:39:09

RMAN> list backup of datafile 11;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
1       Full    9.32G      DISK        00:04:24     2011-11-14 12:48:57
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20111114T124433
        Piece Name: /tmp/test_full_01mrkqdh_1_1
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  11      Full 11517136   2011-11-14 12:44:33 /opt/oracle/oradata/test/xifenfei03.dbf
--backupset中也有datafile 11
--checkpoint time为:2011-11-14 12:44:33，创建时间为:2011-11-14 12:48:57

--通过比较这两个关于datafile 11的备份时间，rman自动选择了创建时间比较新的备份恢复

RMAN> crosscheck copy of datafile 11;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
validation failed for datafile copy
datafile copy filename=/tmp/11.dbf recid=13 stamp=767543949
Crosschecked 1 objects

RMAN> list copy of datafile 11;

List of Datafile Copies
Key     File S Completion Time     Ckp SCN    Ckp Time            Name
------- ---- - ------------------- ---------- ------------------- ----
13      11   X 2011-11-18 14:39:09 11517136   2011-11-14 12:44:33 /tmp/11.dbf
--通过检测发现/tmp/11.dbf是无效的，所以rman继续使用backupset进行恢复datafile 11

在有些时候，当rman对中关于一个对象的备份有多个(备份时间不一致),rman会从最新备份的开始还原，如果第一个失败，使用下一个备份，但是在有些时候可能第一个失败后，rman不自动使用下一个，这个时候的处理思路是:使用crosscheck backup/copy检测无效的备份，删除掉，继续执行