|
Oracle 12C RMAN新特性 -基于表时间点恢复(上)
|
|
|
在Oracle数据库中,对于用户因DDL(drop或truncate)等误操作,引起的数据丢失;一般可以通过基于数据库的时间点不完全恢复和基于表空间的时间点恢复(TSPITR)进行数据恢复;但在Oracle 12c RMAN又推出了新的特性:基于表的时间点恢复,相比前两种方式,更加方便和更具有可操作性。11:56:54 SCOTT@ orcl> select * from tab---------------------------------------- ------- ----------11:56:54 SCOTT@ orcl>select count(1) from emp1;11:57:09 SCOTT@ orcl>select table_name,tablespace_name from user_tables;TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------RMAN> list backup of database;BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------3 Full 1.70M DISK 00:00:00 08-AUG-16 BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20160808T115958 Piece Name: /backup/orcl/ORCL_users_3.bk List of Datafiles in backup set 3 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 6 Full 2874187 08-AUG-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_crgy2c5t_.dbfBS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------4 Full 1.18G DISK 00:01:27 08-AUG-16 BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20160808T120909 Piece Name: /backup/orcl/ORCL_4.bak List of Datafiles in backup set 4 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 2875198 08-AUG-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_cr26h8nw_.dbf 2 Full 2875198 08-AUG-16 /u01/app/oracle/oradata/ORCL/datafile/adotbs1_01.dbf 3 Full 2875198 08-AUG-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_cr26g2f6_.dbf 4 Full 2875198 08-AUG-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_cr26k3t7_.dbf 5 Full 2875198 08-AUG-16 /u01/app/oracle/oradata/ORCL/datafile/low_cost_tbs01.dbf 6 Full 2875198 08-AUG-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_crgy2c5t_.dbfRMAN> list backup of tablespace users;BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------3 Full 1.70M DISK 00:00:00 08-AUG-16 BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20160808T115958 Piece Name: /backup/orcl/ORCL_users_3.bk List of Datafiles in backup set 3 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 6 Full 2874187 08-AUG-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_crgy2c5t_.dbfBS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------4 Full 1.18G DISK 00:01:27 08-AUG-16 BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20160808T120909 Piece Name: /backup/orcl/ORCL_4.bak List of Datafiles in backup set 4 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 6 Full 2875198 08-AUG-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_crgy2c5t_.dbfBS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------6 Full 1.70M DISK 00:00:00 08-AUG-16 BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20160808T121206 Piece Name: /backup/orcl/ORCL_users_6.bk List of Datafiles in backup set 6 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 6 Full 2875369 08-AUG-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_crgy2c5t_.dbf以此时间点或scn作为表恢复的时间点(对于生产环境,需用logminer工具查询准确的时间点,本案例为测试环境)12:13:02 SYS@ orcl>select timestamp_to_scn(current_timestamp) from v$database;TIMESTAMP_TO_SCN(CURRENT_TIMESTAMP)-----------------------------------12:08:42 SCOTT@ orcl>select count(*) from emp1;12:08:57 SCOTT@ orcl>drop table emp1 purge;[oracle@enmoedu ~]$ mkdir /u01/auxRMAN> recover table scott.emp13> auxiliary destination '/u01/aux';Starting recover at 08-AUG-16using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=80 device type=DISKRMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-TimeList of tablespaces expected to have UNDO segments// Oracle自动建立用于恢复的auxiliary InstanceCreating automatic instance, with SID='fwsy'initialization parameters used for automatic instance:db_unique_name=fwsy_pitr_ORCLdiagnostic_dest=/u01/app/oracle_system_trig_enabled=FALSEdb_create_file_dest=/u01/auxlog_archive_dest_1='location=/u01/aux'#No auxiliary parameter file usedstarting up automatic instance ORCLTotal System Global Area 591396864 bytesVariable Size 176162312 bytesDatabase Buffers 406847488 bytesRedo Buffers 5459968 bytesAutomatic instance created//通过源库的rman备份,restore数据库到auxiliary databasecontents of Memory Script:# set requested point in time# restore the controlfilerestore clone controlfile;sql clone 'alter database mount clone database';# archive current online logsql 'alter system archive log current';executing command: SET until clauseStarting restore at 08-AUG-16allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=21 device type=DISKchannel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: restoring control filechannel ORA_AUX_DISK_1: reading from backup piece /backup/orcl/ORCL_5.bakchannel ORA_AUX_DISK_1: piece handle=/backup/orcl/ORCL_5.bak tag=TAG20160808T120909channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01output file name=/u01/aux/ORCL/controlfile/o1_mf_ctj1v7x9_.ctlFinished restore at 08-AUG-16sql statement: alter database mount clone databasesql statement: alter system archive log currentcontents of Memory Script:# set requested point in time// restore的表空间有system、sysaux、undo、users(表所在的表空间)# set destinations for recovery set and auxiliary set datafilesset newname for clone datafile 1 to new;set newname for clone datafile 4 to new;set newname for clone datafile 3 to new;set newname for clone tempfile 1 to new;switch clone tempfile all;# restore the tablespaces in the recovery set and the auxiliary setrestore clone datafile 1, 4, 3;switch clone datafile all;executing command: SET until clauseexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMErenamed tempfile 1 to /u01/aux/ORCL/datafile/o1_mf_temp_%u_.tmp in control fileStarting restore at 08-AUG-16using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/aux/ORCL/datafile/o1_mf_system_%u_.dbfchannel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/aux/ORCL/datafile/o1_mf_undotbs1_%u_.dbfchannel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/aux/ORCL/datafile/o1_mf_sysaux_%u_.dbfchannel ORA_AUX_DISK_1: reading from backup piece /backup/orcl/ORCL_4.bakchannel ORA_AUX_DISK_1: piece handle=/backup/orcl/ORCL_4.bak tag=TAG20160808T120909channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05Finished restore at 08-AUG-16datafile 1 switched to datafile copyinput datafile copy RECID=4 STAMP=919340207 file name=/u01/aux/ORCL/datafile/o1_mf_system_ctj1vgr5_.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=5 STAMP=919340207 file name=/u01/aux/ORCL/datafile/o1_mf_undotbs1_ctj1vgsj_.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=6 STAMP=919340208 file name=/u01/aux/ORCL/datafile/o1_mf_sysaux_ctj1vgr8_.dbfcontents of Memory Script:# set requested point in time# online the datafiles restored or switchedsql clone "alter database datafile 1 online";sql clone "alter database datafile 4 online";sql clone "alter database datafile 3 online";# recover and open database read onlyrecover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX";sql clone 'alter database open read only';executing command: SET until clausesql statement: alter database datafile 1 onlinesql statement: alter database datafile 4 onlinesql statement: alter database datafile 3 online
|
分享按钮
|
作者:恩墨学院(李全新)
来源:恩墨学院(李全新) |
|
|
|
|
|