ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 备份恢复' RECOVER COPY OF'

备份恢复' RECOVER COPY OF'

原创 Oracle 作者：shilei1 时间：2014-01-07 08:14:22 0 删除 编辑

一个基础的增量追加备份示例：简称basic脚本
RUN {
   RECOVER COPY OF DATABASE WITH TAG 'incr_update';
   BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_update'
       DATABASE;
   }
为了理解上述脚本，我们先看一下如果没有数据文件拷贝和增量备份运行这两个脚本的情况。
1、如果没有LEVEL0备份或者备份文件拷贝，执行BACKUP INCREMENTAL LEVEL 1... FOR RECOVER OF COPY WITH TAG...不能产生LEVEL1增量备份文件，但是RMAN会按照指定的tag在DATAFILE对应的目录下创建一分镜像文件拷贝。
2、如果没有LEVEL0备份或者备份文件拷贝，执行RECOVER COPY OF DATABASE WITH TAG...则生成一些信息但是不产生错误。


我们看一下整个basic脚本的执行情况：
第一次运行该脚本没有数据文件拷贝和增量备份所以执行RECOVER COPY OF DATABASE WITH TAG 'incr_update'没有任何结果；执行BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_update' DATABASE将产生数据文件的镜像文件拷贝。
第二次运行该脚本，由于第一次运行的时候BACKUP INCREMENTAL LEVEL 1... FOR RECOVER OF COPY WITH TAG...命令产生一个镜像文件拷贝，但是没有LEVEL1的增量备份，所以执行RECOVER COPY OF DATABASE WITH TAG 'incr_update'还是没有任何结果；执行BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_update' DATABASE将产生LEVEL1增量备份。
第三次运行该脚本，执行RECOVER COPY OF DATABASE WITH TAG 'incr_update'命令将把第二次执行该脚本产生的LEVEL1增量备份追加到镜像文件拷贝，同时又产生一个新的LEVEL1增量备份文件。
以后再执行该脚本都是把上次产生的LEVEL1的增量备份追加到镜像文件拷贝，然后再产生一个新的LEVEL1的增量备份文件。


可以把backup device type disk tag '%TAG' archivelog all not backed up delete all input;加进去
RUN {
   RECOVER COPY OF DATABASE WITH TAG 'incr_update';
   BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_update' DATABASE;
backup device type disk tag '%TAG' archivelog all not backed up delete all input;}


备份脚本：
RMAN> RUN {
2>    RECOVER COPY OF DATABASE WITH TAG 'incr_update';
3>    BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_update'
4>        DATABASE;
5>    }


Starting recover at 2014-01-07 15:04:16
using channel ORA_DISK_1
no copy of datafile 1 found to recover
no copy of datafile 2 found to recover
no copy of datafile 3 found to recover
no copy of datafile 4 found to recover
no copy of datafile 5 found to recover
no copy of datafile 6 found to recover
Finished recover at 2014-01-07 15:04:16


Starting backup at 2014-01-07 15:04:16
using channel ORA_DISK_1
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 5 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 6 found
no parent backup or copy of datafile 4 found
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/db04/sysaux01.dbf
output file name=/u01/app/oracle/fast_recovery_area/DB04/datafile/o1_mf_sysaux_9dq9qjsr_.dbf tag=INCR_UPDATE RECID=63 STAMP=836233460
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/db04/system01.dbf
output file name=/u01/app/oracle/fast_recovery_area/DB04/datafile/o1_mf_system_9dq9qqv4_.dbf tag=INCR_UPDATE RECID=64 STAMP=836233466
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/db04/example01.dbf
output file name=/u01/app/oracle/fast_recovery_area/DB04/datafile/o1_mf_example_9dq9qv9w_.dbf tag=INCR_UPDATE RECID=65 STAMP=836233468
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/db04/undotbs01.dbf
output file name=/u01/app/oracle/fast_recovery_area/DB04/datafile/o1_mf_undotbs1_9dq9qygt_.dbf tag=INCR_UPDATE RECID=66 STAMP=836233471
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/db04/gguser.dbf
output file name=/u01/app/oracle/fast_recovery_area/DB04/datafile/o1_mf_tbs_ggus_9dq9qzmc_.dbf tag=INCR_UPDATE RECID=67 STAMP=836233472
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental level 1 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 2014-01-07 15:04:33
channel ORA_DISK_1: finished piece 1 at 2014-01-07 15:04:34
piece handle=/u01/app/oracle/fast_recovery_area/DB04/backupset/2014_01_07/o1_mf_ncsn1_INCR_UPDATE_9dq9r1r8_.bkp tag=INCR_UPDATE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/db04/users01.dbf
output file name=/u01/app/oracle/fast_recovery_area/DB04/datafile/o1_mf_users_9dq9r2xo_.dbf tag=INCR_UPDATE RECID=68 STAMP=836233474
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2014-01-07 15:04:35
会在/u01/app/oracle/fast_recovery_area/DB04 下产生一个datafile目录，并在backupset目录下产生文件。




第二次与第三次
RMAN> RUN {
2>    RECOVER COPY OF DATABASE WITH TAG 'incr_update';
3>    BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_update'
4>        DATABASE;
5>    }


Starting recover at 2014-01-07 15:36:01
using channel ORA_DISK_1
no copy of datafile 1 found to recover
no copy of datafile 2 found to recover
no copy of datafile 3 found to recover
no copy of datafile 4 found to recover
no copy of datafile 5 found to recover
no copy of datafile 6 found to recover
Finished recover at 2014-01-07 15:36:01


Starting backup at 2014-01-07 15:36:01
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/db04/sysaux01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/db04/system01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/db04/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/db04/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/db04/gguser.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/db04/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2014-01-07 15:36:01
channel ORA_DISK_1: finished piece 1 at 2014-01-07 15:36:04
piece handle=/u01/app/oracle/fast_recovery_area/DB04/backupset/2014_01_07/o1_mf_nnnd1_INCR_UPDATE_9dqcm1kf_.bkp tag=INCR_UPDATE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental level 1 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 2014-01-07 15:36:05
channel ORA_DISK_1: finished piece 1 at 2014-01-07 15:36:06
piece handle=/u01/app/oracle/fast_recovery_area/DB04/backupset/2014_01_07/o1_mf_ncsn1_INCR_UPDATE_9dqcm5ox_.bkp tag=INCR_UPDATE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2014-01-07 15:36:06


RMAN> RUN {
2>    RECOVER COPY OF DATABASE WITH TAG 'incr_update';
3>    BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_update'
4>        DATABASE;
5>    }


Starting recover at 2014-01-07 15:36:47
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy file number=00001 name=/u01/app/oracle/fast_recovery_area/DB04/datafile/o1_mf_system_9dq9qqv4_.dbf
recovering datafile copy file number=00002 name=/u01/app/oracle/fast_recovery_area/DB04/datafile/o1_mf_sysaux_9dq9qjsr_.dbf
recovering datafile copy file number=00003 name=/u01/app/oracle/fast_recovery_area/DB04/datafile/o1_mf_undotbs1_9dq9qygt_.dbf
recovering datafile copy file number=00004 name=/u01/app/oracle/fast_recovery_area/DB04/datafile/o1_mf_users_9dq9r2xo_.dbf
recovering datafile copy file number=00005 name=/u01/app/oracle/fast_recovery_area/DB04/datafile/o1_mf_example_9dq9qv9w_.dbf
recovering datafile copy file number=00006 name=/u01/app/oracle/fast_recovery_area/DB04/datafile/o1_mf_tbs_ggus_9dq9qzmc_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/DB04/backupset/2014_01_07/o1_mf_nnnd1_INCR_UPDATE_9dqcm1kf_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/DB04/backupset/2014_01_07/o1_mf_nnnd1_INCR_UPDATE_9dqcm1kf_.bkp tag=INCR_UPDATE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 2014-01-07 15:36:48


Starting backup at 2014-01-07 15:36:48
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/db04/sysaux01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/db04/system01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/db04/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/db04/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/db04/gguser.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/db04/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2014-01-07 15:36:49
channel ORA_DISK_1: finished piece 1 at 2014-01-07 15:36:52
piece handle=/u01/app/oracle/fast_recovery_area/DB04/backupset/2014_01_07/o1_mf_nnnd1_INCR_UPDATE_9dqcnk4y_.bkp tag=INCR_UPDATE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental level 1 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 2014-01-07 15:36:53
channel ORA_DISK_1: finished piece 1 at 2014-01-07 15:36:54
piece handle=/u01/app/oracle/fast_recovery_area/DB04/backupset/2014_01_07/o1_mf_ncsn1_INCR_UPDATE_9dqcno8v_.bkp tag=INCR_UPDATE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2014-01-07 15:36:54




如果手动在操作系统上删除了备份产生的datafile 需要crosscheck datafilecopy all;再进行delete datafilecopy all;        


说明：
The RECOVER COPY OF DATABASE WITH TAG... command causes RMAN to apply any available incremental level 1 backups to a set of datafile copies with the specified tag.把增量备份应用到以前COPY的datafile 上
对COPY出来的数据库作增量同步。

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
数据恢复测试
1.调整数据库为归档模式
ora10g@secdb /home/oracle$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 19 22:10:38 2011


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




Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


sys@ora10g> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     73
Current log sequence           77




sys@ora10g> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ora10g> startup mount;
ORACLE instance started.


Total System Global Area  536870912 bytes
Fixed Size                  1220460 bytes
Variable Size             318767252 bytes
Database Buffers          209715200 bytes
Redo Buffers                7168000 bytes
Database mounted.
sys@ora10g> alter database archivelog;


Database altered.


sys@ora10g> alter database open;


Database altered.


2.使用RMAN对数据库进行备份
1）备份数据库
ora10g@secdb /home/oracle$ rman target /


Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 19 22:16:17 2011


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


connected to target database: ORA10G (DBID=4067278754)


RMAN> backup database;


Starting backup at 20111019 22:16:35
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=214 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00010 name=/oracle/ora10gR2/oradata/ora10g/tbs_perf_01.dbf
input datafile fno=00003 name=/oracle/ora10gR2/oradata/ora10g/sysaux01.dbf
input datafile fno=00001 name=/oracle/ora10gR2/oradata/ora10g/system01.dbf
input datafile fno=00002 name=/oracle/ora10gR2/oradata/ora10g/undotbs01.dbf
input datafile fno=00005 name=/home/oracle/tbs_sec_d_01.dbf
input datafile fno=00004 name=/oracle/ora10gR2/oradata/ora10g/tbs_local_01.dbf
input datafile fno=00008 name=/oracle/ora10gR2/oradata/ora10g/tbs01.dbf
input datafile fno=00009 name=/oracle/ora10gR2/oradata/ora10g/tbs_secooler_01.dbf
input datafile fno=00016 name=/u01/app/oracle/oradata/PROD/disk1/INDX_01.dbf
input datafile fno=00017 name=/u01/app/oracle/oradata/PROD/disk1/TOOLS_01.dbf
input datafile fno=00007 name=/oracle/ora10gR2/oradata/ora10g/undotbs_guarantee.dbf
input datafile fno=00006 name=/oracle/ora10gR2/oradata/ora10g/users.dbf
channel ORA_DISK_1: starting piece 1 at 20111019 22:16:36
channel ORA_DISK_1: finished piece 1 at 20111019 22:17:41
piece handle=/oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xpy532_.bkp tag=TAG20111019T221636 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00011 name=/u01/app/oracle/oradata/PROD/disk1/DATA01_01.dbf
input datafile fno=00012 name=/u01/app/oracle/oradata/PROD/disk2/DATA01_02.dbf
input datafile fno=00013 name=/u01/app/oracle/oradata/PROD/disk3/DATA01_03.dbf
input datafile fno=00014 name=/u01/app/oracle/oradata/PROD/disk4/DATA01_04.dbf
input datafile fno=00015 name=/u01/app/oracle/oradata/PROD/disk5/DATA01_05.dbf
channel ORA_DISK_1: starting piece 1 at 20111019 22:17:42
channel ORA_DISK_1: finished piece 1 at 20111019 22:17:45
piece handle=/oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xq067w_.bkp tag=TAG20111019T221636 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 20111019 22:17:45


Starting Control File and SPFILE Autobackup at 20111019 22:17:45
piece handle=/db_backup/rman_backup/c-4067278754-20111019-00 comment=NONE
Finished Control File and SPFILE Autobackup at 20111019 22:17:48


2）查看备份信息
RMAN> list backup;




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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
48      Full    1.10G      DISK        00:00:57     20111019 22:17:33
        BP Key: 47   Status: AVAILABLE  Compressed: NO  Tag: TAG20111019T221636
        Piece Name: /oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xpy532_.bkp
  List of Datafiles in backup set 48
  File LV Type Ckp SCN    Ckp Time          Name
  ---- -- ---- ---------- ----------------- ----
  1       Full 6494715    20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/system01.dbf
  2       Full 6494715    20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/undotbs01.dbf
  3       Full 6494715    20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/sysaux01.dbf
  4       Full 6494715    20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/tbs_local_01.dbf
  5       Full 6494715    20111019 22:16:36 /home/oracle/tbs_sec_d_01.dbf
  6       Full 6494715    20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/users.dbf
  7       Full 6494715    20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/undotbs_guarantee.dbf
  8       Full 6494715    20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/tbs01.dbf
  9       Full 6494715    20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/tbs_secooler_01.dbf
  10      Full 6494715    20111019 22:16:36 /oracle/ora10gR2/oradata/ora10g/tbs_perf_01.dbf
  16      Full 6494715    20111019 22:16:36 /u01/app/oracle/oradata/PROD/disk1/INDX_01.dbf
  17      Full 6494715    20111019 22:16:36 /u01/app/oracle/oradata/PROD/disk1/TOOLS_01.dbf


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
49      Full    1.02M      DISK        00:00:01     20111019 22:17:43
        BP Key: 48   Status: AVAILABLE  Compressed: NO  Tag: TAG20111019T221636
        Piece Name: /oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xq067w_.bkp
  List of Datafiles in backup set 49
  File LV Type Ckp SCN    Ckp Time          Name
  ---- -- ---- ---------- ----------------- ----
  11      Full 6494738    20111019 22:17:42 /u01/app/oracle/oradata/PROD/disk1/DATA01_01.dbf
  12      Full 6494738    20111019 22:17:42 /u01/app/oracle/oradata/PROD/disk2/DATA01_02.dbf
  13      Full 6494738    20111019 22:17:42 /u01/app/oracle/oradata/PROD/disk3/DATA01_03.dbf
  14      Full 6494738    20111019 22:17:42 /u01/app/oracle/oradata/PROD/disk4/DATA01_04.dbf
  15      Full 6494738    20111019 22:17:42 /u01/app/oracle/oradata/PROD/disk5/DATA01_05.dbf


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
50      Full    6.83M      DISK        00:00:00     20111019 22:17:45
        BP Key: 49   Status: AVAILABLE  Compressed: NO  Tag: TAG20111019T221745
        Piece Name: /db_backup/rman_backup/c-4067278754-20111019-00
  Control File Included: Ckp SCN: 6494743      Ckp time: 20111019 22:17:45
  SPFILE Included: Modification time: 20111019 22:11:53


3.模拟数据库故障——表的误TRUNCATE
1）连接到数据库的sec用户
ora10g@secdb /home/oracle$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 19 22:20:57 2011


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




Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


sys@ora10g> conn sec/oracle_1
Connected.


2）查看T表中的数据行数
sec@ora10g> select count(*) from t;


  COUNT(*)
----------
      1000


此时T表中包含1000条数据。


3）查看当前时间，以便后续使用RMAN进行恢复
sec@ora10g> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';


Session altered.


sec@ora10g> select sysdate from dual;


SYSDATE
-------------------
2011-10-19 22:21:38


4）这里模拟对T表的误删除（DDL类型的TRUNCATE方法）
sec@ora10g> truncate table t;


Table truncated.


sec@ora10g> select sysdate from dual;


SYSDATE
-------------------
2011-10-19 22:22:05


sec@ora10g> select count(*) from t;


  COUNT(*)
----------
         0


4.使用RMAN恢复到故障发生之前的时间点
我们这里恢复的时间点的目标是T表被删除之前的2011-10-19 22:21:38时刻。
【重要提醒】在使用RMAN完成基于时间点的不完全恢复之前，最好对现场做一个备份，我们这里只需要备份数据库的控制文件和日志文件即可。当恢复结束后不满足我们要求时，可以恢复控制文件和日志文件后重新进行恢复。
1）将数据库启动到mount状态
sec@ora10g> conn / as sysdba
Connected.
sys@ora10g> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ora10g> startup mount;
ORACLE instance started.


Total System Global Area  536870912 bytes
Fixed Size                  1220460 bytes
Variable Size             318767252 bytes
Database Buffers          209715200 bytes
Redo Buffers                7168000 bytes
Database mounted.


2）使用RMAN脚本恢复数据库到指定时间点
（1）恢复脚本如下
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
set until time = '2011-10-19 22:21:38';
restore database;
recover database;
alter database open resetlogs;}


（2）恢复过程记录
RMAN> run {
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
5> set until time = '2011-10-19 22:21:38';
6> restore database;
7> recover database;
8> alter database open resetlogs;}


using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=211 devtype=DISK


allocated channel: c2
channel c2: sid=210 devtype=DISK


sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"


executing command: SET until clause


Starting restore at 20111019 22:31:04


channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle/ora10gR2/oradata/ora10g/system01.dbf
restoring datafile 00002 to /oracle/ora10gR2/oradata/ora10g/undotbs01.dbf
restoring datafile 00003 to /oracle/ora10gR2/oradata/ora10g/sysaux01.dbf
restoring datafile 00004 to /oracle/ora10gR2/oradata/ora10g/tbs_local_01.dbf
restoring datafile 00005 to /home/oracle/tbs_sec_d_01.dbf
restoring datafile 00006 to /oracle/ora10gR2/oradata/ora10g/users.dbf
restoring datafile 00007 to /oracle/ora10gR2/oradata/ora10g/undotbs_guarantee.dbf
restoring datafile 00008 to /oracle/ora10gR2/oradata/ora10g/tbs01.dbf
restoring datafile 00009 to /oracle/ora10gR2/oradata/ora10g/tbs_secooler_01.dbf
restoring datafile 00010 to /oracle/ora10gR2/oradata/ora10g/tbs_perf_01.dbf
restoring datafile 00016 to /u01/app/oracle/oradata/PROD/disk1/INDX_01.dbf
restoring datafile 00017 to /u01/app/oracle/oradata/PROD/disk1/TOOLS_01.dbf
channel c1: reading from backup piece /oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xpy532_.bkp
channel c2: starting datafile backupset restore
channel c2: specifying datafile(s) to restore from backup set
restoring datafile 00011 to /u01/app/oracle/oradata/PROD/disk1/DATA01_01.dbf
restoring datafile 00012 to /u01/app/oracle/oradata/PROD/disk2/DATA01_02.dbf
restoring datafile 00013 to /u01/app/oracle/oradata/PROD/disk3/DATA01_03.dbf
restoring datafile 00014 to /u01/app/oracle/oradata/PROD/disk4/DATA01_04.dbf
restoring datafile 00015 to /u01/app/oracle/oradata/PROD/disk5/DATA01_05.dbf
channel c2: reading from backup piece /oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xq067w_.bkp
channel c2: restored backup piece 1
piece handle=/oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xq067w_.bkp tag=TAG20111019T221636
channel c2: restore complete, elapsed time: 00:00:08
channel c1: restored backup piece 1
piece handle=/oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xpy532_.bkp tag=TAG20111019T221636
channel c1: restore complete, elapsed time: 00:00:53
Finished restore at 20111019 22:31:58


Starting recover at 20111019 22:31:58


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


Finished recover at 20111019 22:32:00


database opened
released channel: c1
released channel: c2


5.验证恢复成果
ora10g@secdb /home/oracle$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 19 22:35:37 2011


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




Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


sys@ora10g>
sys@ora10g>
sys@ora10g> conn sec/oracle_1
Connected.
sec@ora10g> select count(*) from t;


  COUNT(*)
----------
      1000


至此，曾经因TRUNCATE导致T表数据丢失的故障已被成功恢复。


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

0
0
上一篇： Fatal NI connect error 12170
下一篇： ORACLE数据库基于时间点的不完全恢复
请登录后发表评论 登录
全部评论
shilei1

注册时间：2018-10-10

  • 博文量
    548
  • 访问量
    129457

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593