无棣网站定制,做网站很赚钱吗,wordpress cms 教程,百度手游app下载Oracle 12.1新特性----使用RMAN从备份中实现recover table发布时间#xff1a;2020-06-26 19:41:44来源#xff1a;51CTO阅读#xff1a;4750作者#xff1a;hbxztc在Oracle12c版本之前#xff0c;使用RMAN能恢复的级别为数据库级别和表空间级别#xff0c;如果只有一张表…Oracle 12.1新特性----使用RMAN从备份中实现recover table发布时间2020-06-26 19:41:44来源51CTO阅读4750作者hbxztc在Oracle12c版本之前使用RMAN能恢复的级别为数据库级别和表空间级别如果只有一张表需要恢复而在数据库级别或表空间级别做恢复影响范围就太大了。因此12.2版本中提供了一个新特性使用RMAN在表级别做恢复并且恢复过程中不影响数据库的正常使用。这一功能不仅可以恢复表还可以恢复表分区。To recover a table or table partition, you must have a full backup of undo, SYSTEM, SYSAUX, and the tablespace that contains the table or table partition.使用recover table的一些限制条件When you use the RECOVER command to recover tables or table partitions contained in an RMAN backup, the following limitations exist.Tables and table partitions belonging to SYS schema cannot be recovered.Tables and table partitions from SYSTEM and SYSAUX tablespaces cannot be recovered.Tables and table partitions on standby databases cannot be recovered.Tables with named NOT NULL constraints cannot be recovered with the REMAP option.下面在12.2版本上做表级别恢复的实验sysORA12Cselect * from v$version;BANNER CON_ID-------------------------------------------------------------------------------- ----------Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0PL/SQL Release 12.2.0.1.0 - Production 0CORE12.2.0.1.0Production 0TNS for Linux: Version 12.2.0.1.0 - Production 0NLSRTL Version 12.2.0.1.0 - Production 01、创建一个数据库的全备RMAN backup database;Starting backup at 20170720 17:12:05using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID36 device typeDISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number00001 nameDATA/ORA12C/DATAFILE/system.256.949764433channel ORA_DISK_1: starting piece 1 at 20170720 17:12:07channel ORA_DISK_1: finished piece 1 at 20170720 17:12:22piece handle/u01/app/oracle/product/12.2/db_home1/dbs/0vs9rar7_1_1 tagTAG20170720T171206 commentNONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:15channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number00004 nameDATA/ORA12C/DATAFILE/users.266.949764465channel ORA_DISK_1: starting piece 1 at 20170720 17:12:22channel ORA_DISK_1: finished piece 1 at 20170720 17:12:23piece handle/u01/app/oracle/product/12.2/db_home1/dbs/10s9rarm_1_1 tagTAG20170720T171206 commentNONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number00002 nameDATA/ORA12C/DATAFILE/sysaux.261.949764491channel ORA_DISK_1: starting piece 1 at 20170720 17:12:23channel ORA_DISK_1: finished piece 1 at 20170720 17:12:38piece handle/u01/app/oracle/product/12.2/db_home1/dbs/11s9rarn_1_1 tagTAG20170720T171206 commentNONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:15channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number00005 nameDATA/ORA12C/DATAFILE/examples.265.949764515channel ORA_DISK_1: starting piece 1 at 20170720 17:12:38channel ORA_DISK_1: finished piece 1 at 20170720 17:12:39piece handle/u01/app/oracle/product/12.2/db_home1/dbs/12s9ras6_1_1 tagTAG20170720T171206 commentNONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number00008 name/tmp/FY_RST_DATA.DATinput datafile file number00007 name/tmp/FY_REC_DATA.DATinput datafile file number00003 nameDATA/ORA12C/DATAFILE/undotbs1.264.949764541input datafile file number00006 nameDATA/ORA12C/DATAFILE/t_move.dbfchannel ORA_DISK_1: starting piece 1 at 20170720 17:12:40channel ORA_DISK_1: finished piece 1 at 20170720 17:12:41piece handle/u01/app/oracle/product/12.2/db_home1/dbs/13s9ras8_1_1 tagTAG20170720T171206 commentNONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 20170720 17:12:41Starting Control File and SPFILE Autobackup at 20170720 17:12:41piece handle/u01/app/oracle/product/12.2/db_home1/dbs/c-326793150-20170720-00 commentNONEFinished Control File and SPFILE Autobackup at 20170720 17:12:422、创建测试表插入测试数据并记录中间的scn号zxORA12Ccreate table t2 (id number,name varchar2(10),birthday date);Table created.zxORA12Cinsert into t2 values(1,zx,sysdate);1 row created.zxORA12Ccommit;Commit complete.zxORA12Cselect current_scn from v$database;CURRENT_SCN-----------650101zxORA12Cinsert into t2 values(2,lx,sysdate);1 row created.zxORA12Ccommit;Commit complete.zxORA12Cselect * from t2;ID NAME BIRTHDAY---------- ---------- -----------------1 zx 20170720 17:18:522 lx 20170720 17:19:343、执行表级别恢复使用remap table参数不覆盖原表恢复成t2_r表RMAN recover table zx.t2 until scn 650101 remap table zx.t2:t2_r auxiliary destination /tmp;Starting recover at 20170720 17:23:50current log archivedusing channel ORA_DISK_1RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-timeList of tablespaces expected to have UNDO segmentsTablespace SYSTEMTablespace UNDOTBS1Creating automatic instance, with SIDhtzDinitialization parameters used for automatic instance:db_nameORA12Cdb_unique_namehtzD_pitr_ORA12Ccompatible12.2.0db_block_size8192db_files200diagnostic_dest/u01/app/oracle_system_trig_enabledFALSEsga_target1712Mprocesses200db_create_file_dest/tmplog_archive_dest_1location/tmp#No auxiliary parameter file usedstarting up automatic instance ORA12COracle instance startedTotal System Global Area 1795162112 bytesFixed Size 8621760 bytesVariable Size 436207936 bytesDatabase Buffers 1342177280 bytesRedo Buffers 8155136 bytesAutomatic instance createdcontents of Memory Script:{# set requested point in timeset until scn 650101;# restore the controlfilerestore clone controlfile;# mount the controlfilesql clone alter database mount clone database;# archive current online logsql alter system archive log current;}executing Memory Scriptexecuting command: SET until clauseStarting restore at 20170720 17:24:12allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID18 device typeDISKchannel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: restoring control filechannel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2/db_home1/dbs/c-326793150-20170720-00channel ORA_AUX_DISK_1: piece handle/u01/app/oracle/product/12.2/db_home1/dbs/c-326793150-20170720-00 tagTAG20170720T171241channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01output file name/tmp/ORA12C/controlfile/o1_mf_dq0xoxot_.ctlFinished restore at 20170720 17:24:14sql statement: alter database mount clone databasesql statement: alter system archive log currentcontents of Memory Script:{# set requested point in timeset until scn 650101;# set destinations for recovery set and auxiliary set datafilesset newname for clone datafile 1 to new;set newname for clone datafile 3 to new;set newname for clone datafile 2 to new;set newname for clone tempfile 1 to new;# switch all tempfilesswitch clone tempfile all;# restore the tablespaces in the recovery set and the auxiliary setrestore clone datafile 1, 3, 2;switch clone datafile all;}executing Memory Scriptexecuting command: SET until clauseexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMErenamed tempfile 1 to /tmp/ORA12C/datafile/o1_mf_temp_%u_.tmp in control fileStarting restore at 20170720 17:24:19using 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 /tmp/ORA12C/datafile/o1_mf_system_%u_.dbfchannel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2/db_home1/dbs/0vs9rar7_1_1channel ORA_AUX_DISK_1: piece handle/u01/app/oracle/product/12.2/db_home1/dbs/0vs9rar7_1_1 tagTAG20170720T171206channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45channel 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 00002 to /tmp/ORA12C/datafile/o1_mf_sysaux_%u_.dbfchannel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2/db_home1/dbs/11s9rarn_1_1channel ORA_AUX_DISK_1: piece handle/u01/app/oracle/product/12.2/db_home1/dbs/11s9rarn_1_1 tagTAG20170720T171206channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35channel 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 00003 to /tmp/ORA12C/datafile/o1_mf_undotbs1_%u_.dbfchannel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2/db_home1/dbs/13s9ras8_1_1channel ORA_AUX_DISK_1: piece handle/u01/app/oracle/product/12.2/db_home1/dbs/13s9ras8_1_1 tagTAG20170720T171206channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15Finished restore at 20170720 17:25:55datafile 1 switched to datafile copyinput datafile copy RECID16 STAMP949857956 file name/tmp/ORA12C/datafile/o1_mf_system_dq0xp4jt_.dbfdatafile 3 switched to datafile copyinput datafile copy RECID17 STAMP949857956 file name/tmp/ORA12C/datafile/o1_mf_undotbs1_dq0xrnq2_.dbfdatafile 2 switched to datafile copyinput datafile copy RECID18 STAMP949857956 file name/tmp/ORA12C/datafile/o1_mf_sysaux_dq0xqkm0_.dbfcontents of Memory Script:{# set requested point in timeset until scn 650101;# online the datafiles restored or switchedsql clone alter database datafile 1 online;sql clone alter database datafile 3 online;sql clone alter database datafile 2 online;# recover and open database read onlyrecover clone database tablespace SYSTEM, UNDOTBS1, SYSAUX;sql clone alter database open read only;}executing Memory Scriptexecuting command: SET until clausesql statement: alter database datafile 1 onlinesql statement: alter database datafile 3 onlinesql statement: alter database datafile 2 onlineStarting recover at 20170720 17:26:02using channel ORA_AUX_DISK_1starting media recoveryarchived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/product/12.2/db_home1/dbs/arch2_2_949077789.dbfarchived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/product/12.2/db_home1/dbs/arch2_3_949077789.dbfarchived log file name/u01/app/oracle/product/12.2/db_home1/dbs/arch2_2_949077789.dbf thread1 sequence2archived log file name/u01/app/oracle/product/12.2/db_home1/dbs/arch2_3_949077789.dbf thread1 sequence3media recovery complete, elapsed time: 00:00:02Finished recover at 20170720 17:26:07sql statement: alter database open read onlycontents of Memory Script:{sql clone create spfile from memory;shutdown clone immediate;startup clone nomount;sql clone alter system set control_files /tmp/ORA12C/controlfile/o1_mf_dq0xoxot_.ctl commentRMAN set scopespfile;shutdown clone immediate;startup clone nomount;# mount databasesql clone alter database mount clone database;}executing Memory Scriptsql statement: create spfile from memorydatabase closeddatabase dismountedOracle instance shut downconnected to auxiliary database (not started)Oracle instance startedTotal System Global Area 1795162112 bytesFixed Size 8621760 bytesVariable Size 436207936 bytesDatabase Buffers 1342177280 bytesRedo Buffers 8155136 bytessql statement: alter system set control_files /tmp/ORA12C/controlfile/o1_mf_dq0xoxot_.ctl comment RMAN set scopespfileOracle instance shut downconnected to auxiliary database (not started)Oracle instance startedTotal System Global Area 1795162112 bytesFixed Size 8621760 bytesVariable Size 436207936 bytesDatabase Buffers 1342177280 bytesRedo Buffers 8155136 bytessql statement: alter database mount clone databasecontents of Memory Script:{# set requested point in timeset until scn 650101;# set destinations for recovery set and auxiliary set datafilesset newname for datafile 4 to new;# restore the tablespaces in the recovery set and the auxiliary setrestore clone datafile 4;switch clone datafile all;}executing Memory Scriptexecuting command: SET until clauseexecuting command: SET NEWNAMEStarting restore at 20170720 17:27:18allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID23 device typeDISKchannel 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 00004 to /tmp/HTZD_PITR_ORA12C/datafile/o1_mf_users_%u_.dbfchannel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2/db_home1/dbs/10s9rarm_1_1channel ORA_AUX_DISK_1: piece handle/u01/app/oracle/product/12.2/db_home1/dbs/10s9rarm_1_1 tagTAG20170720T171206channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25Finished restore at 20170720 17:27:44datafile 4 switched to datafile copyinput datafile copy RECID20 STAMP949858064 file name/tmp/HTZD_PITR_ORA12C/datafile/o1_mf_users_dq0xvq9q_.dbfcontents of Memory Script:{# set requested point in timeset until scn 650101;# online the datafiles restored or switchedsql clone alter database datafile 4 online;# recover and open resetlogsrecover clone database tablespace USERS, SYSTEM, UNDOTBS1, SYSAUX delete archivelog;alter clone database open resetlogs;}executing Memory Scriptexecuting command: SET until clausesql statement: alter database datafile 4 onlineStarting recover at 20170720 17:27:44using channel ORA_AUX_DISK_1starting media recoveryarchived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/product/12.2/db_home1/dbs/arch2_2_949077789.dbfarchived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/product/12.2/db_home1/dbs/arch2_3_949077789.dbfarchived log file name/u01/app/oracle/product/12.2/db_home1/dbs/arch2_2_949077789.dbf thread1 sequence2archived log file name/u01/app/oracle/product/12.2/db_home1/dbs/arch2_3_949077789.dbf thread1 sequence3media recovery complete, elapsed time: 00:00:01Finished recover at 20170720 17:27:48database openedcontents of Memory Script:{# create directory for datapump importsql create or replace directory TSPITR_DIROBJ_DPDIR as /tmp;# create directory for datapump exportsql clone create or replace directory TSPITR_DIROBJ_DPDIR as /tmp;}executing Memory Scriptsql statement: create or replace directory TSPITR_DIROBJ_DPDIR as /tmpsql statement: create or replace directory TSPITR_DIROBJ_DPDIR as /tmpPerforming export of tables...EXPDP Starting SYS.TSPITR_EXP_htzD_mhEh:EXPDP Processing object type TABLE_EXPORT/TABLE/TABLE_DATAEXPDP Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSEXPDP Processing object type TABLE_EXPORT/TABLE/TABLEEXPDP . . exported ZX.T2 5.898 KB 1 rowsEXPDP Master table SYS.TSPITR_EXP_htzD_mhEh successfully loaded/unloadedEXPDP ******************************************************************************EXPDP Dump file set for SYS.TSPITR_EXP_htzD_mhEh is:EXPDP /tmp/tspitr_htzD_98436.dmpEXPDP Job SYS.TSPITR_EXP_htzD_mhEh successfully completed at Thu Jul 20 17:29:48 2017 elapsed 0 00:01:05Export completedcontents of Memory Script:{# shutdown clone before importshutdown clone abort}executing Memory ScriptOracle instance shut downPerforming import of tables...IMPDP Master table SYS.TSPITR_IMP_htzD_bhqf successfully loaded/unloadedIMPDP Starting SYS.TSPITR_IMP_htzD_bhqf:IMPDP Processing object type TABLE_EXPORT/TABLE/TABLEIMPDP Processing object type TABLE_EXPORT/TABLE/TABLE_DATAIMPDP . . imported ZX.T2_R 5.898 KB 1 rowsIMPDP Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSIMPDP Job SYS.TSPITR_IMP_htzD_bhqf successfully completed at Thu Jul 20 17:30:10 2017 elapsed 0 00:00:10Import completedRemoving automatic instanceAutomatic instance removedauxiliary instance file /tmp/ORA12C/datafile/o1_mf_temp_dq0xskvm_.tmp deletedauxiliary instance file /tmp/HTZD_PITR_ORA12C/onlinelog/o1_mf_3_dq0xwo3d_.log deletedauxiliary instance file /tmp/HTZD_PITR_ORA12C/onlinelog/o1_mf_2_dq0xwo3d_.log deletedauxiliary instance file /tmp/HTZD_PITR_ORA12C/onlinelog/o1_mf_1_dq0xwo0q_.log deletedauxiliary instance file /tmp/HTZD_PITR_ORA12C/datafile/o1_mf_users_dq0xvq9q_.dbf deletedauxiliary instance file /tmp/ORA12C/datafile/o1_mf_sysaux_dq0xqkm0_.dbf deletedauxiliary instance file /tmp/ORA12C/datafile/o1_mf_undotbs1_dq0xrnq2_.dbf deletedauxiliary instance file /tmp/ORA12C/datafile/o1_mf_system_dq0xp4jt_.dbf deletedauxiliary instance file /tmp/ORA12C/controlfile/o1_mf_dq0xoxot_.ctl deletedauxiliary instance file tspitr_htzD_98436.dmp deletedFinished recover at 20170720 17:30:134、验证结果查询t2_r表zxORA12Cselect * from t2_r;ID NAME BIRTHDAY---------- ---------- -----------------1 zx 20170720 17:18:525、即使表t2做了DDL操作修改了表结构也可以用这种方法进行恢复--表t2添加一个字段zxORA12Calter table t2 add address varchar2(10);Table altered.--恢复表到t2_r2RMAN recover table zx.t2 until scn 650101 remap table zx.t2:t2_r2 auxiliary destination /tmp;Starting recover at 20170720 17:57:00using channel ORA_DISK_1RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time......Finished recover at 20170720 18:02:03--验证表t2_r2zxORA12Cselect * from t2_r2;ID NAME BIRTHDAY---------- ---------- -----------------1 zx 20170720 17:18:52--恢复成功这一功能很大程度减小了数据恢复的影响范围。参考http://docs.oracle.com/database/121/BRADV/rcmresind.htm#BRADV686
