rman恢复到指定时间点

1、RMAN作为Oracle强大的备份恢复工具，可以协助我们恢复数据库到指定时间点，这便是Oracle不完全恢复的一种体现，通过这种方法可以找回我们曾经丢失的数据。这里以找回误TRUNCATE表数据为例给大家演示一下RMAN的不完全恢复功能。1.调整数据库为归档模式ora10gsecdb /home/oracle$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 19 22:10:38 2011Copyright (c) 1982, 2005, Oracle.  All rights

2、 reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionssysora10g> archive log list;Database log mode              No Archiv

3、e ModeAutomatic archival             DisabledArchive destination            USE_DB_RECOVERY_FILE_DESTOldest online log sequence     73Current log sequence  

4、;         77sysora10g> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.sysora10g> startup mount;ORACLE instance started.Total System Global Area  536870912 bytesFixed Size       

5、60;          1220460 bytesVariable Size             318767252 bytesDatabase Buffers          209715200 bytesRedo Buffers     

6、           7168000 bytesDatabase mounted.sysora10g> alter database archivelog;Database altered.sysora10g> alter database open;Database altered.2.使用RMAN对数据库进行备份1）备份数据库ora10gsecdb /home/oracle$ rman target /Recovery Manager: Release 10.2.0.1.0 -

7、Production on Wed Oct 19 22:16:17 2011Copyright (c) 1982, 2005, Oracle.  All rights reserved.connected to target database: ORA10G (DBID=4067278754)RMAN> backup database;Starting backup at 20111019 22:16:35using target database control file instead of recovery catalogallocated channel: ORA_DI

8、SK_1channel ORA_DISK_1: sid=214 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00010 name=/oracle/ora10gR2/oradata/ora10g/tbs_perf_01.dbfinput datafile fno=00003 name=/oracle/ora10gR2/oradata/ora10g/sysaux01.d

9、bfinput datafile fno=00001 name=/oracle/ora10gR2/oradata/ora10g/system01.dbfinput datafile fno=00002 name=/oracle/ora10gR2/oradata/ora10g/undotbs01.dbfinput datafile fno=00005 name=/home/oracle/tbs_sec_d_01.dbfinput datafile fno=00004 name=/oracle/ora10gR2/oradata/ora10g/tbs_local_01.dbfinput datafi

10、le fno=00008 name=/oracle/ora10gR2/oradata/ora10g/tbs01.dbfinput datafile fno=00009 name=/oracle/ora10gR2/oradata/ora10g/tbs_secooler_01.dbfinput datafile fno=00016 name=/u01/app/oracle/oradata/PROD/disk1/INDX_01.dbfinput datafile fno=00017 name=/u01/app/oracle/oradata/PROD/disk1/TOOLS_01.dbfinput d

11、atafile fno=00007 name=/oracle/ora10gR2/oradata/ora10g/undotbs_guarantee.dbfinput datafile fno=00006 name=/oracle/ora10gR2/oradata/ora10g/users.dbfchannel ORA_DISK_1: starting piece 1 at 20111019 22:16:36channel ORA_DISK_1: finished piece 1 at 20111019 22:17:41piece handle=/oracle/ora10gR2/flash_rec

12、overy_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xpy532_.bkp tag=TAG20111019T221636 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:05channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput dataf

13、ile fno=00011 name=/u01/app/oracle/oradata/PROD/disk1/DATA01_01.dbfinput datafile fno=00012 name=/u01/app/oracle/oradata/PROD/disk2/DATA01_02.dbfinput datafile fno=00013 name=/u01/app/oracle/oradata/PROD/disk3/DATA01_03.dbfinput datafile fno=00014 name=/u01/app/oracle/oradata/PROD/disk4/DATA01_04.db

14、finput datafile fno=00015 name=/u01/app/oracle/oradata/PROD/disk5/DATA01_05.dbfchannel ORA_DISK_1: starting piece 1 at 20111019 22:17:42channel ORA_DISK_1: finished piece 1 at 20111019 22:17:45piece handle=/oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T22163

15、6_79xq067w_.bkp tag=TAG20111019T221636 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:03Finished backup at 20111019 22:17:45Starting Control File and SPFILE Autobackup at 20111019 22:17:45piece handle=/db_backup/rman_backup/c-4067278754-20111019-00 comment=NONEFinished Cont

16、rol File and SPFILE Autobackup at 20111019 22:17:482）查看备份信息RMAN> list backup;List of Backup Sets=BS Key  Type LV Size       Device Type Elapsed Time Completion Time- - - - - - -48      Full    1.10G    

17、  DISK        00:00:57     20111019 22:17:33        BP Key: 47   Status: AVAILABLE  Compressed: NO  Tag: TAG20111019T221636        Piece Name: /oracle

18、/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   

19、    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

20、 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  

21、  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/

22、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

23、_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

24、0;     Full 6494715    20111019 22:16:36 /u01/app/oracle/oradata/PROD/disk1/TOOLS_01.dbfBS Key  Type LV Size       Device Type Elapsed Time Completion Time- - - - - - -49      Full    1.02M&

25、#160;     DISK        00:00:01     20111019 22:17:43        BP Key: 48   Status: AVAILABLE  Compressed: NO  Tag: TAG20111019T221636      

26、60; 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  - - - - - -

27、60; 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   &#

28、160;  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

29、60;   20111019 22:17:42 /u01/app/oracle/oradata/PROD/disk5/DATA01_05.dbfBS Key  Type LV Size       Device Type Elapsed Time Completion Time- - - - - - -50      Full    6.83M      DISK &

30、#160;      00:00:00     20111019 22:17:45        BP Key: 49   Status: AVAILABLE  Compressed: NO  Tag: TAG20111019T221745        Piece Name: /db_backup/rman_backup/c-

31、4067278754-20111019-00  Control File Included: Ckp SCN: 6494743      Ckp time: 20111019 22:17:45  SPFILE Included: Modification time: 20111019 22:11:533.模拟数据库故障表的误TRUNCATE1）连接到数据库的sec用户ora10gsecdb /home/oracle$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Prod

32、uction on Wed Oct 19 22:20:57 2011Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionssysora10g> conn sec/oracle_1Connected.2）查看T表中的数据行数secora10g> selec

33、t count(*) from t;  COUNT(*)-      1000此时T表中包含1000条数据。3）查看当前时间，以便后续使用RMAN进行恢复secora10g> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'Session altered.secora10g> select sysdate from dual;SYSDATE-2011-10-19 22:21:384）这里模拟对T表的误删除（DDL类型的TRUNCATE方法）s

34、ecora10g> truncate table t;Table truncated.secora10g> select sysdate from dual;SYSDATE-2011-10-19 22:22:05secora10g> select count(*) from t;  COUNT(*)-         04.使用RMAN恢复到故障发生之前的时间点我们这里恢复的时间点的目标是T表被删除之前的2011-10-19 22:21:38时刻。【重要提醒】在使用R

35、MAN完成基于时间点的不完全恢复之前，最好对现场做一个备份，我们这里只需要备份数据库的控制文件和日志文件即可。当恢复结束后不满足我们要求时，可以恢复控制文件和日志文件后重新进行恢复。1）将数据库启动到mount状态secora10g> conn / as sysdbaConnected.sysora10g> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.sysora10g> startup mount;ORACLE instance started.Total

36、System Global Area  536870912 bytesFixed Size                  1220460 bytesVariable Size             318767252 bytesDatabase Buffers   

37、;       209715200 bytesRedo Buffers                7168000 bytesDatabase mounted.2）使用RMAN脚本恢复数据库到指定时间点（1）恢复脚本如下run allocate channel c1 type disk;allocate channel c2 type disk;sql 'alter

38、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 s

39、et 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 catalogallocated channel: c1channel c1: sid=211 devtyp

40、e=DISKallocated channel: c2channel c2: sid=210 devtype=DISKsql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"executing command: SET until clauseStarting restore at 20111019 22:31:04channel c1: starting datafile backupset restorechannel c1: specifying datafile(s) to re

41、store from backup setrestoring datafile 00001 to /oracle/ora10gR2/oradata/ora10g/system01.dbfrestoring datafile 00002 to /oracle/ora10gR2/oradata/ora10g/undotbs01.dbfrestoring datafile 00003 to /oracle/ora10gR2/oradata/ora10g/sysaux01.dbfrestoring datafile 00004 to /oracle/ora10gR2/oradata/ora10g/tb

42、s_local_01.dbfrestoring datafile 00005 to /home/oracle/tbs_sec_d_01.dbfrestoring datafile 00006 to /oracle/ora10gR2/oradata/ora10g/users.dbfrestoring datafile 00007 to /oracle/ora10gR2/oradata/ora10g/undotbs_guarantee.dbfrestoring datafile 00008 to /oracle/ora10gR2/oradata/ora10g/tbs01.dbfrestoring

43、datafile 00009 to /oracle/ora10gR2/oradata/ora10g/tbs_secooler_01.dbfrestoring datafile 00010 to /oracle/ora10gR2/oradata/ora10g/tbs_perf_01.dbfrestoring datafile 00016 to /u01/app/oracle/oradata/PROD/disk1/INDX_01.dbfrestoring datafile 00017 to /u01/app/oracle/oradata/PROD/disk1/TOOLS_01.dbfchannel

44、 c1: reading from backup piece /oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xpy532_.bkpchannel c2: starting datafile backupset restorechannel c2: specifying datafile(s) to restore from backup setrestoring datafile 00011 to /u01/app/oracle/oradata/PROD/disk1/DATA01_01.dbfrestoring datafile 00012 to /u01/app/oracle/oradata/PROD/disk2/DATA01_02.dbfrestoring datafile 00013 to /u01/app/oracle/oradata/PROD/disk3/DATA01_03.dbfrestoring dataf