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 reserved.Co

2、nnected to:Oracle Database10gEnterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP andDataMining optionssysora10g archive log list;Database log mode No Archive ModeAutomatic archival DisabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 73Current log

3、 sequence 77sysora10g shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.sysora10g startup mount;ORACLE instance started.Total System Global Area 536870912 bytesFixed Size 1220460 bytesVariable Size 318767252 bytesDatabase Buffers 209715200 bytesRedo Buffers 7168000 byt

4、esDatabase 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 - Production on Wed Oct 19 22:16:17 2011Copyright (c) 1982, 2005, Oracle. All righ

5、ts 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_DISK_1channel ORA_DISK_1: sid=214 devtype=DISKchannel ORA_DISK_1: starting full datafile bac

6、kupsetchannel 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.dbfinput datafile fno=00001 name=/oracle/ora10gR2/oradata/ora10g/system01.dbfinput datafile

7、 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 datafile fno=00008 name=/oracle/ora10gR2/oradata/ora10g/tbs01.dbfinput datafile fno=00009 name=/

8、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 datafile fno=00007 name=/oracle/ora10gR2/oradata/ora10g/undotbs_guarantee.dbfinput datafile

9、 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_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xpy532_.bkp tag=TA

10、G20111019T221636 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 datafile fno=00011 name=/u01/app/oracle/oradata/PROD/disk1/DATA01_01.dbfinput datafile fno=0001

11、2 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.dbfinput datafile fno=00015 name=/u01/app/oracle/oradata/PROD/disk5/DATA01_05.dbfchannel ORA

12、_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_TAG20111019T221636_79xq067w_.bkp tag=TAG20111019T221636 comment=NONEchannel ORA_DISK_1: backup set complete

13、, 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 Control File and SPFILE Autobackup at 20111019 22:17:482）查看备份信息RMAN list backup;List of Backup

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

15、_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/ora10

16、gR2/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/ora10gR

17、2/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 201

18、11019 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.dbfBS 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 Compresse

19、d: 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/dis

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

21、/u01/app/oracle/oradata/PROD/disk5/DATA01_05.dbfBS 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 Contr

22、ol 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 - Production on Wed Oct 19 22:20:57 2011Copyright (c) 1982, 2005, Oracle. Al

23、l 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 select count(*) from t; COUNT(*)- 1000此时T表中包含1000条数据。3）查看当前时间，以便后续使用RMAN进行恢复secora10g alt

24、er 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方法）secora10g truncatetablet;Table truncated.secora10g select sysdate from dual;SYSDATE-2011-10-19 22:22:05secora10g select count(*) from t; C

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

26、instance shut down.sysora10g startup mount;ORACLE instance started.Total System Global Area 536870912 bytesFixed Size 1220460 bytesVariable Size 318767252 bytesDatabase Buffers 209715200 bytesRedo Buffers 7168000 bytesDatabase mounted.2）使用RMAN脚本恢复数据库到指定时间点（1）恢复脚本如下run allocate channel c1 type disk;a

27、llocate channel c2 type disk;sqlalter 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_d

28、ate_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 devtype=DISKallocated channel: c2channel c2: sid=210 de

29、vtype=DISKsql statement: alter session set nls_date_format=yyyy-mm-dd hh24:mi:ssexecuting command: SET until clauseStarting restore at 20111019 22:31:04channel c1: starting datafile backupset restorechannel c1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to /oracle/ora1

30、0gR2/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/tbs_local_01.dbfrestoring datafile 00005 to /home/oracle/tbs_se

31、c_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 datafile 00009 to /oracle/ora10gR2/oradata/ora10g/tbs_secoole

32、r_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 c1: reading from backup piece /oracle/ora10gR2/flash_recover

33、y_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/

34、oracle/oradata/PROD/disk2/DATA01_02.dbfrestoring datafile 00013 to /u01/app/oracle/oradata/PROD/disk3/DATA01_03.dbfrestoring datafile 00014 to /u01/app/oracle/oradata/PROD/disk4/DATA01_04.dbfrestoring datafile 00015 to /u01/app/oracle/oradata/PROD/disk5/DATA01_05.dbfchannel c2: reading from backup piece /oracle/ora10gR2/flash_recovery_area/ORA10G/backupset/2011_10_19/o1_mf_nnndf_TAG20111019T221636_79xq067w_.bkp