?Oracle Study案例之--基于表空間的時間點恢復（TSPITR)

? ? ?TSPITR(表空間時間點恢復)用于將一個或多個表空間恢復到過去某個時間點的狀態，而其他表空間仍然保持現有狀態。

TSPITR 相關的概念和術語：

(1) TSPITR (Tablespace Point-In-Time Recover)。TSPITR 是表空間時間點恢復的英文縮寫格式，它表示將一個或多個表空間恢復到過去時間點的狀態，而其他表空間仍然保持現有狀態。

(2) TSPITR 實現方法。當實現表空間時間點恢復時，既可以使用用戶管理的表空間時間點恢復方法，也可以使用RMAN 管理的表空間時間點恢復。

(3) DBPITR (Database Point-In-Time Recovery)。DBPITR 是數據庫時間點恢復的英文縮寫格式，它表示將數據庫的所有表空間恢復到過去時間點的狀態。注意，DBPITR 只適用于ARCHIVELOG 模式。

(4) 主數據庫(Primary Database)。主數據庫是指用于存放應用系統數據的Oracle 數據庫，也被稱為產品數據庫或目標數據庫。當執行TSPITR 時，主數據庫是指包含被恢復表空間的數據庫。

(5) 恢復集(Recovery Set)。恢復集是指在主數據庫上需要執行 TSPITR 的表空間集合。注意，當在恢復集的表空間上執行TSPITR 時，要求這些表空間必須是自包含的。

(6) 輔助數據庫(Auxiliary Database)。輔助數據庫是主數據庫的一個副本數據庫。當執行TSPITR 時，輔助數據庫用于將恢復集表空間恢復到過去時間點。注意，輔助數據庫的所有物理文件都是從主數據庫備份中取得，并且輔助數據庫必須包含SYSTEM 表空間、UNDO 表空間以及恢復集表空間的備份文件。

(7) 輔助集(Auxiliary Set)。輔助集是指輔助數據庫所需要的、除了恢復集表空間文件之外的其他文件集合。當執行 TSPITR 時，輔助數據庫除了需要恢復集表空間的備份文件之外，還需要備份控制文件、SYSTEM 表空間的備份文件、UNDO 表空間的備分文件。

一、TSPITR相關基本概念

什么是TSPITR？

? ? TSPITR實際上是一種時間點恢復，只不過這里的恢復是針對單個表空間而言的，利用這種方法可以將數據庫中的某一個或幾個非系統表空間恢復到過去的某個時刻而保持其他的表空間不變。

Oracle只有在控制文件、數據文件頭、聯機日志文件三者的scn都一致的情況才能正常打開數據庫，所以我們不可能在需要進行TSPITR的數據庫中直接進行恢復，這就牽涉到另外一個概念輔助實例（auxiliary instance）/輔助數據庫(auxiliary database)

主數據庫、輔助數據庫、輔助實例、恢復集、輔助集

主數據庫是指需要進行TSPITR的數據庫。 輔助數據庫是主數據庫的一個副本或是其一個子集，用于進行TSPITR，因為TSPITR不能直接在主數據庫上進行。 輔助實例對應于輔助數據庫的一個實例。 恢復集是指構成需要進行TSPITR的表空間的所有數據文件的備份。 輔助集是指表空間進行恢復所需的其它所有文件，包括system\undo\temp?表空間的備份文件、備份控制文件、歸檔日志文件、口令文件、參數文件等。

案例分析：基于表空間的時間點恢復

?

1、測試環境03:57:50?SQL>?conn?scott/tiger?????????????????????????????????????????????????????????????????????????????????????????????????????????? Connected. 03:57:57?SQL>03:59:43?SQL>?select?*?from?tab;????????????????????????????????????????????????????????????????????????????????????????????????????????TNAME??????????????????????????TABTYPE?CLUSTERID ------------------------------?-------?---------- EXCEPTIONS?????????????????????TABLE LXTB1??????????????????????????TABLE LXTB2??????????????????????????TABLE SALGRADE???????????????????????TABLE SYS_TEMP_FBT???????????????????TABLE BONUS??????????????????????????TABLE DEPT???????????????????????????TABLE EMP????????????????????????????TABLE8?rows?selected.03:59:45?SQL>?select?table_name,tablespace_name?from?user_tables;???????????????????????????????????????????????????????????????????????TABLE_NAME?????????????????????TABLESPACE_NAME ------------------------------?------------------------------ EXCEPTIONS?????????????????????USERS LXTB1??????????????????????????USERS LXTB2??????????????????????????LXTBS2 SALGRADE???????????????????????USERS BONUS??????????????????????????USERS DEPT???????????????????????????USERS EMP????????????????????????????USERS SYS_TEMP_FBT8?rows?selected.04:00:08?SQL>?select?*?from?lxtb2;??????????????????????????????????????????????????????????????????????????????????????????????????????ID ----------12345678101110?rows?selected.2、對主庫做熱備份04:00:09?SQL>?conn?/as?sysdba??????????????????????????????????????????????????????????????????????????????????????????????????????????? Connected. 04:00:14?SQL> 04:00:14?SQL>?@/home/oracle/test_hot_bak???????????????????????????????????????????????????????????????????????????????????????????????? 04:00:22?SQL>?set?feedback?off?pagesize?0?heading?off?verify?off?linesize?100?trimspool?on?echo?off?time?off ***spooling?to?/disk1/backup/test/hot_cmd.sql spool?/disk1/backup/test/hot_bak/hot_bak.lst alter?system?switch?logfile; alter?tablespace?SYSTEM?begin?backup; host?cp?/u01/app/oracle/oradata/test/system01.dbf?/disk1/backup/test/hot_bak alter?tablespace?SYSTEM?end?backup; alter?tablespace?RTBS?begin?backup; host?cp?/u01/app/oracle/oradata/test/rtbs01.dbf?/disk1/backup/test/hot_bak alter?tablespace?RTBS?end?backup; alter?tablespace?SYSAUX?begin?backup; host?cp?/u01/app/oracle/oradata/test/sysaux01.dbf?/disk1/backup/test/hot_bak alter?tablespace?SYSAUX?end?backup; alter?tablespace?USERS?begin?backup; host?cp?/u01/app/oracle/oradata/test/users01.dbf?/disk1/backup/test/hot_bak alter?tablespace?USERS?end?backup; alter?tablespace?LOB_16K?begin?backup; host?cp?/u01/app/oracle/oradata/test/lob_16k01.dbf?/disk1/backup/test/hot_bak alter?tablespace?LOB_16K?end?backup; alter?tablespace?UNDOTBS1?begin?backup; host?cp?/u01/app/oracle/oradata/test/undotbs1.dbf?/disk1/backup/test/hot_bak alter?tablespace?UNDOTBS1?end?backup; alter?tablespace?INDX?begin?backup; host?cp?/u01/app/oracle/oradata/test/indx01.dbf?/disk1/backup/test/hot_bak alter?tablespace?INDX?end?backup; alter?tablespace?LXTBS1?begin?backup; host?cp?/u01/app/oracle/oradata/test/lxtbs01.dbf?/disk1/backup/test/hot_bak alter?tablespace?LXTBS1?end?backup; alter?tablespace?LXTBS2?begin?backup; host?cp?/u01/app/oracle/oradata/test/lxtbs2.dbf?/disk1/backup/test/hot_bak alter?tablespace?LXTBS2?end?backup; archive?log?list; spool?off;Database?log?mode??????????????Archive?Mode Automatic?archival?????????????Enabled Archive?destination????????????/disk4/arch/test Oldest?online?log?sequence?????6 Next?log?sequence?to?archive???9 Current?log?sequence???????????93、備份控制文件，作為備庫的controlfileSQL>?alter?database?backup?controlfile?to?'/u01/app/oracle/oradata/test2/control01.ctl';???????????????????????????????????????????????? SQL>?exit??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? Disconnected?from?Oracle?Database?10g?Enterprise?Edition?Release?10.2.0.1.0?-?Production With?the?Partitioning,?OLAP?and?Data?Mining?options4、生成pfile?文件SQL>?create?pfile?from?spfile；5、表被誤操作（truncate）[oracle@work?test]$?sqlplus?/?as?sysdba;SQL*Plus:?Release?10.2.0.1.0?-?Production?on?Thu?Oct?27?04:01:49?2011Copyright?(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?options04:01:50?SQL> 04:01:50?SQL>?insert?into?scott.lxtb2?values?(9);???????????????????????????????????????????????????????????????????????????????????????1?row?created.04:02:15?SQL>?insert?into?scott.lxtb2?values?(12);??????????????????????????????????????????????????????????????????????????????????????1?row?created.04:02:17?SQL>?insert?into?scott.lxtb2?values?(13);??????????????????????????????????????????????????????????????????????????????????????1?row?created.04:02:19?SQL>?commit;???????????????????????????????????????????????????????????????????????????????????????????????????????????????????Commit?complete.04:02:21?SQL>?alter?system?switch?logfile;??????????????????????????????????????????????????????????????????????????????????????????????System?altered.04:02:39?SQL>?/?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????System?altered.04:02:40?SQL>?/?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????System?altered.04:02:41?SQL>?truncate?table?scott.lxtb2;???????????????????????????????????????????????????????????????????????????????????????????????Table?truncated.04:02:50?SQL>?select?*?from?scott.lxtb2;????????????????????????????????????????????????????????????????????????????????????????????????no?rows?selected04:02:56?SQL>?!?6、生成備庫的pfile?文件（inittest2.ora)和備庫口令文件[oracle@work?dbs]$?orapwd?file=orapwtest2?password=oracle?entries=3?force=y[oracle@work?hot_bak]$?cp?$ORACLE_HOME/dbs/inittest.ora?$ORACLE_HOME/dbs/inittest2.ora--------備庫pfile?文件[oracle@work?dbs]$?cat?inittest2.oratest.__db_cache_size=251658240 test.__java_pool_size=4194304 test.__large_pool_size=4194304 test.__shared_pool_size=150994944 test.__streams_pool_size=0 *.audit_trail='NONE' *.background_dump_dest='$ORACLE_BASE/admin/test/bdump' *.core_dump_dest='$ORACLE_BASE/admin/test/cdump' *.db_16k_cache_size=12582912 *.db_4k_cache_size=12582912 *.db_block_size=8192 *.db_cache_size=30M#DEMO *.db_file_multiblock_read_count=16 *.db_name='test' *.db_recovery_file_dest='/disk1/flash/test' *.db_recovery_file_dest_size=2147483648 *.fast_start_mttr_target=900 *.fast_start_parallel_rollback='HIGH' *.log_archive_dest_1='location=/disk4/arch/test' *.log_archive_dest_2='' *.log_archive_format='arch_%t_%s_%r.log' *.log_checkpoints_to_alert=TRUE *.nls_date_format='yyyy-mm-dd?hh24:mi:ss' *.O7_DICTIONARY_ACCESSIBILITY=FALSE *.optimizer_mode='choose' *.parallel_threads_per_cpu=4#SMALL *.pga_aggregate_target=10485760 *.query_rewrite_enabled='true' *.query_rewrite_integrity='trusted' *.recovery_parallelism=4 *.remote_login_passwordfile='EXCLUSIVE' *.resource_limit=TRUE *.sga_max_size=440401920 *.sga_target=418m *.shared_pool_size=100M#DEMO *.star_transformation_enabled='true' *.undo_management='auto' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='$ORACLE_BASE/admin/test/udump' *.utl_file_dir='/home/oracle/logmnr'---------添加以下內容*.control_files='/u01/app/oracle/oradata/test2/control01.ctl' db_unique_name?=?test2 db_file_name_convert=('/u01/app/oracle/oradata/test','/u01/app/oracle/oradata/test2') log_file_name_convert=('/u01/app/oracle/oradata/test','/u01/app/oracle/oradata/test2','/disk1/oradata/test','/disk1/oradata/test2')7、拷貝主庫備份數據文件到備庫目錄下[oracle@work?hot_bak]$?mkdir?/u01/app/oracle/oradata/test2 [oracle@work?hot_bak]$?mkdir?/disk1/oradata/test2[oracle@work?test]$?cd?/disk1/backup/test/hot_bak/ [oracle@work?hot_bak]$?ls hot_bak.lst?indx01.dbf?lob_16k01.dbf?lxtbs01.dbf?lxtbs2.dbf?rtbs01.dbf?sysaux01.dbf?system01.dbf?undotbs1.dbf?users01.dbf [oracle@work?hot_bak]$?cp?*.dbf?/u01/app/oracle/oradata/test2/8、啟動備庫到nomountexport?ORACLE_SID=test2 [oracle@work?hot_bak]$?export?ORACLE_SID=test2 [oracle@work?hot_bak]$?!sql sqlplus?/?as?sysdba;SQL*Plus:?Release?10.2.0.1.0?-?Production?on?Thu?Oct?27?04:05:28?2011Copyright?(c)?1982,?2005,?Oracle.?All?rights?reserved.Connected?to?an?idle?instance.04:05:29?SQL> 04:05:29?SQL>?startup?nomount?pfile=$ORACLE_HOME/dbs/inittest2.ora?????????????????????????????????????????????????????????????????????? ORACLE?instance?started.Total?System?Global?Area?440401920?bytes Fixed?Size??????????????????1219904?bytes Variable?Size?????????????113246912?bytes Database?Buffers??????????322961408?bytes Redo?Buffers????????????????2973696?bytes8、啟動備庫到mount狀態04:05:43?SQL>?alter?database?mount?clone?database;??????????????????????????????????????????????????????????????????????????????????????Database?altered.04:06:25?SQL>?col?name?for?a50?????????????????????????????????????????????????????????????????????????????????????????????????????????? 04:06:32?SQL>?select?name?,file#?,status?from?v$datafile;???????????????????????????????????????????????????????????????????????????????NAME????????????????????????????????????????????????????FILE#?STATUS --------------------------------------------------?----------?------- /u01/app/oracle/oradata/test2/system01.dbf??????????????????1?SYSOFF /u01/app/oracle/oradata/test2/rtbs01.dbf????????????????????2?OFFLINE /u01/app/oracle/oradata/test2/sysaux01.dbf??????????????????3?OFFLINE /u01/app/oracle/oradata/test2/users01.dbf???????????????????4?OFFLINE /u01/app/oracle/oradata/test2/lob_16k01.dbf?????????????????5?OFFLINE /u01/app/oracle/oradata/test2/lxtbs01.dbf???????????????????6?OFFLINE /u01/app/oracle/oradata/test2/lxtbs2.dbf????????????????????7?OFFLINE /u01/app/oracle/oradata/test2/undotbs1.dbf??????????????????9?OFFLINE /u01/app/oracle/oradata/test2/indx01.dbf???????????????????14?OFFLINE9?rows?selected.10、將數據文件聯機04:09:17?SQL>?alter?database?datafile?1?online;?????????????????????????????????????????????????????????????????????????????????????????Database?altered.04:09:24?SQL>?alter?database?datafile?2?online;?????????????????????????????????????????????????????????????????????????????????????????Database?altered.04:09:26?SQL>?alter?database?datafile?3?online;?????????????????????????????????????????????????????????????????????????????????????????Database?altered.04:09:28?SQL>?alter?database?datafile?4?online;?????????????????????????????????????????????????????????????????????????????????????????Database?altered.04:09:30?SQL>?alter?database?datafile?5?online;?????????????????????????????????????????????????????????????????????????????????????????Database?altered.04:09:32?SQL>?alter?database?datafile?6?online;?????????????????????????????????????????????????????????????????????????????????????????Database?altered.04:09:34?SQL>?alter?database?datafile?7?online;?????????????????????????????????????????????????????????????????????????????????????????Database?altered.04:09:36?SQL>?alter?database?datafile?9?online;?????????????????????????????????????????????????????????????????????????????????????????Database?altered.04:09:40?SQL>?alter?database?datafile?14?online;????????????????????????????????????????????????????????????????????????????????????????Database?altered.04:09:42?SQL>?select?name?,file#?,status?from?v$datafile???????????????????????????????????????????????????????????????????????????????? 04:09:47???2?;?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????NAME????????????????????????????????????????????????????FILE#?STATUS --------------------------------------------------?----------?------- /u01/app/oracle/oradata/test2/system01.dbf??????????????????1?SYSTEM /u01/app/oracle/oradata/test2/rtbs01.dbf????????????????????2?ONLINE /u01/app/oracle/oradata/test2/sysaux01.dbf??????????????????3?ONLINE /u01/app/oracle/oradata/test2/users01.dbf???????????????????4?ONLINE /u01/app/oracle/oradata/test2/lob_16k01.dbf?????????????????5?ONLINE /u01/app/oracle/oradata/test2/lxtbs01.dbf???????????????????6?ONLINE /u01/app/oracle/oradata/test2/lxtbs2.dbf????????????????????7?ONLINE /u01/app/oracle/oradata/test2/undotbs1.dbf??????????????????9?ONLINE /u01/app/oracle/oradata/test2/indx01.dbf???????????????????14?ONLINE9?rows?selected.11、在備庫上做基于時間點的database?recover（時間點就是truncate時的時間點，可以用logmnr找出）04:09:48?SQL>?recover?database?until?time?'2011-10-27?04:01:50'?using?backup?controlfile;??????????????????????????????????????????????? ORA-00279:?change?1354493?generated?at?10/27/2011?04:00:23?needed?for?thread?1 ORA-00289:?suggestion?:?/disk4/arch/test/arch_1_9_765501215.log ORA-00280:?change?1354493?for?thread?1?is?in?sequence?#904:10:30?Specify?log:?{<RET>=suggested?|?filename?|?AUTO?|?CANCEL} auto???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? Log?applied. Media?recovery?complete.12、open?database?查看恢復04:10:37?SQL>?alter?database?open?resetlogs;????????????????????????????????????????????????????????????????????????????????????????????Database?altered.04:10:58?SQL>?select?*?from?scott.lxtb2;????????????????????????????????????????????????????????????????????????????????????????????????ID ----------12345678101110?rows?selected.04:11:30?SQL> ---------------------恢復成功13、將恢復后的表空間導出（lxtbs2?tablespace）[oracle@work?data]$?exp?userid=\'sys/oracle?as?sydba\'?point_in_time_recover=y?tablespaces=lxtbs2?file=lxtbs2.dmpExport:?Release?10.2.0.1.0?-?Production?on?Thu?Oct?27?04:15:34?2011Copyright?(c)?1982,?2005,?Oracle.?All?rights?reserved.EXP-00004:?invalid?username?or?password Username:?sys?as?sysdba????????????????????????????????????????????????????????????????????????????????????????????????????????????????? Password:???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????Connected?to:?Oracle?Database?10g?Enterprise?Edition?Release?10.2.0.1.0?-?Production With?the?Partitioning,?OLAP?and?Data?Mining?options Export?done?in?US7ASCII?character?set?and?AL16UTF16?NCHAR?character?set server?uses?ZHS16GBK?character?set?(possible?charset?conversion) Note:?table?data?(rows)?will?not?be?exportedAbout?to?export?Tablespace?Point-in-time?Recovery?objects... For?tablespace?LXTBS2?... .?exporting?cluster?definitions .?exporting?table?definitions .?.?exporting?table??????????????????????????LXTB2 EXP-00091:?Exporting?questionable?statistics. .?exporting?referential?integrity?constraints .?exporting?triggers .?end?point-in-time?recovery Export?terminated?successfully?with?warnings. [oracle@work?data]$14、將表空間導入到主庫----------將主庫表空間脫機?test?(主庫）04:13:45?SQL>?alter?tablespace?lxtbs2?offline;??????????????????????????????????????????????????????????????????????????????????????????Tablespace?altered.--------拷貝備庫表空間datafile?到主庫目錄下[oracle@work?data]$?cp?/u01/app/oracle/oradata/test2/lxtbs2.dbf?/u01/app/oracle/oradata/test[oracle@work?data]$?export?ORACLE_SID=test-------------導入到主庫[oracle@work?data]$?imp?userid=\'sys/oracle?as?sysdba\'?point_in_time_recover=y?file=lxtbs2.dmp?ignore=yImport:?Release?10.2.0.1.0?-?Production?on?Thu?Oct?27?04:38:29?2011Copyright?(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?optionsExport?file?created?by?EXPORT:V10.02.01?via?conventional?path About?to?import?Tablespace?Point-in-time?Recovery?objects... import?done?in?US7ASCII?character?set?and?AL16UTF16?NCHAR?character?set import?server?uses?ZHS16GBK?character?set?(possible?charset?conversion) .?importing?SYS's?objects?into?SYS .?importing?SCOTT's?objects?into?SCOTT .?.?importing?table????????????????????????"LXTB2" .?importing?SYS's?objects?into?SYS Import?terminated?successfully?without?warnings15、驗證04:30:39?SQL>?alter?tablespace?lxtbs2?online;???????????????????????????????????????????????????????????????????????????????????????????Tablespace?altered.04:38:51?SQL>?select?*?from?scott.lxtb2;????????????????????????????????????????????????????????????????????????????????????????????????ID ----------12345678101110?rows?selected.-----------數據文件恢復到truncate?之前

轉載于:https://blog.51cto.com/tiany/732226

總結

以上是生活随笔為你收集整理的Oracle Study案例之--基于表空间的时间点恢复（TSPITR)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。