從Oracle 10g之後，小規模資料誤刪除、誤操作其實已經有了很好的解決方案，就是flashback技術。Flashback Query和Flashback Drop，就可以從資料記錄和資料表這類比較細的粒度。

在實際業務場景中，相對比“天塌地陷”的全庫恢復場景，單Schema、單Tablespace甚至單資料表的區域性恢復更加有施展的空間。出現誤操作的時候，使用者往往希望一個或者部分資料表恢復到過去的一個時間點。同時又不希望將全庫恢復到過去時間，丟失部分資料。

RMAN提供了一種實現所謂TSPITR（Tablespace Point-In-Time Recovery）的技術，透過簡單的一個語句，就可以在主庫不停庫（很吸引人）的情況下，利用備份集和連續的歸檔日誌，實現表空間級別的定點恢復。

本篇主要介紹進行RMAN TSPITR操作的步驟方法和一些技術細節點，供需要的朋友待查。

1、環境背景介紹

筆者選擇Oracle 11g進行試驗，具體版本為11.2.0.4版本。資料庫模式為歸檔模式。

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 5

Next log sequence to archive 8

Current log sequence 8

嘗試構建一個全新schema，在一個全新的tablespace中。實驗資料選擇scott使用者的資料。

SQL> create tablespace testtbl datafile size 10m autoextend on extent management local uniform size 1m segment space management auto;

Tablespace created

SQL> create user test identified by test default tablespace testtbl;

User created

SQL> grant resource, connect to test;

Grant succeeded

使用之前匯出的scott資料，進行remap匯入。

[oracle@SimpleLinux ~]$ impdp \"/ as sysdba\" dumpfile=scott_20150618.dmp remap_schema=scott:test remap_tablespace=users:testtbl

Import: Release 11.2.0.4.0 - Production on Thu Jun 18 09:50:17 2015

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

（篇幅原因，有省略……）

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Thu Jun 18 09:50:28 2015 elapsed 0 00:00:08

SQL> select owner ,tablespace_name, count(*) from dba_segments where owner='TEST' group by owner, tablespace_name;

OWNER TABLESPACE_NAME COUNT(*)

------------------------------ ------------------------------ ----------

TEST TESTTBL 5

兩個資料表。

SQL> select count(*) from test.emp;

COUNT(*)

----------

14

SQL> select count(*) from test.dept;

COUNT(*)

----------

4

2、資料備份和故障發生

歸檔模式下，需要有一份完全的備份，才能實現恢復操作。

RMAN> backup database plus archivelog delete input;

Starting backup at 18-JUN-15

current log archived

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=47 device type=DISK

（篇幅原因，有省略…….）

Starting Control File and SPFILE Autobackup at 18-JUN-15

piece handle=/u01/app/fast_recovery_area/ORA11G/autobackup/2015_06_18/o1_mf_s_882701042_br4d3m3c_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 18-JUN-15

SQL> select group#, sequence# from v$log where status='CURRENT';

GROUP# SEQUENCE#

---------- ----------

2 17

此時，操作時間為：

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') a from dual;

A

--------------------

2015-06-18 10:51:19

過了幾個小時，誤操作發生。

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') a from dual;

A

--------------------

2015-06-18 13:47:29

SQL> select group#, sequence# from v$log where status='CURRENT';

GROUP# SEQUENCE#

---------- ----------

3 19

誤刪除發生

SQL> drop table test.emp;

Table dropped

SQL> drop table test.dept;

Table dropped

--Flashback Drop的機會也沒有了。

SQL> purge dba_recyclebin;

Done

3、RMAN進行表空間TSPITR

單表表空間移動，要確保表空間內容“內包”，也就是不會有其他表空間牽扯其中。這個操作我們在可移動表空間的演示中也會進行。

SQL> exec dbms_tts.transport_set_check('testtbl',true);

PL/SQL procedure successfully completed

SQL> select * from transport_set_violations;

VIOLATIONS

--------------------------------------------------------------------------------

下面進行實際恢復操作，首先需要在目錄中建立出一個可訪問的空間，進行臨時檔案容納。

[root@SimpleLinux ~]# cd /

[root@SimpleLinux /]# mkdir -p /extend/oradata/aux

[root@SimpleLinux /]# chown -R oracle:oinstall /extend/

[root@SimpleLinux /]# ls -l | grep extend

drwxr-xr-x. 3 oracle oinstall 4096 Jun 18 10:21 extend

啟動RMAN，登入後執行recover tablespace操作，指定出恢復時間。注意：RMAN的recover tablespace命令本質上是執行一系列的指令指令碼，將利用RMAN恢復過程自動化，無需人工介入。

[oracle@SimpleLinux ~]$ rman nocatalog

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jun 18 13:48:44 2015

RMAN> connect target /

connected to target database: ORA11G (DBID=4239941846)

using target database control file instead of recovery catalog

RMAN> recover tablespace testtbl until time "to_date('2015-06-18 10:51:19','yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/extend/oradata/aux';

Starting recover at 18-JUN-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=44 device type=DISK

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

Creating automatic instance, with SID='DDfe' –在目錄中建立出一個虛擬的Oracle例項

--啟動例項的引數都準備好了。

initialization parameters used for automatic instance:

db_name=ORA11G

db_unique_name=DDfe_tspitr_ORA11G

compatible=11.2.0.4.0

db_block_size=8192

db_files=200

sga_target=1G

processes=80

db_create_file_dest=/extend/oradata/aux

log_archive_dest_1='location=/extend/oradata/aux'

#No auxiliary parameter file used

starting up automatic instance ORA11G

Oracle instance started

Total System Global Area 1071333376 bytes

Fixed Size 1369420 bytes

Variable Size 281021108 bytes

Database Buffers 784334848 bytes

Redo Buffers 4608000 bytes

Automatic instance created

--自包檢查

Running TRANSPORT_SET_CHECK on recovery set tablespaces

TRANSPORT_SET_CHECK completed successfully

--第一塊指令碼片段，利用備份集合和歸檔日誌，恢復資料到虛擬例項上。

contents of Memory Script:

{

# set requested point in time

set until time "to_date('2015-06-18 10:51:19','yyyy-mm-dd hh24:mi:ss')";

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

# avoid unnecessary autobackups for structural changes during TSPITR

sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';

}

executing Memory Script

executing command: SET until clause

Starting restore at 18-JUN-15

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=19 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/fast_recovery_area/ORA11G/autobackup/2015_06_18/o1_mf_s_882701042_br4d3m3c_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/fast_recovery_area/ORA11G/autobackup/2015_06_18/o1_mf_s_882701042_br4d3m3c_.bkp tag=TAG20150618T104402

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02

output file name=/extend/oradata/aux/ORA11G/controlfile/o1_mf_br4q1v2o_.ctl

Finished restore at 18-JUN-15

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:

{

# set requested point in time

set until time "to_date('2015-06-18 10:51:19','yyyy-mm-dd hh24:mi:ss')";

plsql <<

declare

sqlstatement varchar2(512);

offline_not_needed exception;

pragma exception_init(offline_not_needed, -01539);

begin

sqlstatement := 'alter tablespace '|| 'TESTTBL' ||' offline immediate';

krmicd.writeMsg(6162, sqlstatement);

krmicd.execSql(sqlstatement);

exception

when offline_not_needed then

null;

end; >>>;

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile 1 to new;

set newname for clone datafile 5 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;

set newname for datafile 7 to

"/u01/app/oradata/ORA11G/datafile/o1_mf_testtbl_br48h8wp_.dbf";

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 1, 5, 3, 2, 7;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

sql statement: alter tablespace TESTTBL offline immediate

executing command: SET NEWNAME

renamed tempfile 1 to /extend/oradata/aux/ORA11G/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 18-JUN-15

using channel ORA_AUX_DISK_1

--只恢復system, sysaux, undo和目標testtbl表空間。

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /extend/oradata/aux/ORA11G/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /extend/oradata/aux/ORA11G/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /extend/oradata/aux/ORA11G/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /extend/oradata/aux/ORA11G/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oradata/ORA11G/datafile/o1_mf_testtbl_br48h8wp_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/fast_recovery_area/ORA11G/backupset/2015_06_18/o1_mf_nnndf_TAG20150618T104315_br4d23ty_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/fast_recovery_area/ORA11G/backupset/2015_06_18/o1_mf_nnndf_TAG20150618T104315_br4d23ty_.bkp tag=TAG20150618T104315

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05

Finished restore at 18-JUN-15

datafile 1 switched to datafile copy

input datafile copy RECID=5 STAMP=882712324 file name=/extend/oradata/aux/ORA11G/datafile/o1_mf_system_br4q22sy_.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=6 STAMP=882712324 file name=/extend/oradata/aux/ORA11G/datafile/o1_mf_system_br4q230g_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=7 STAMP=882712324 file name=/extend/oradata/aux/ORA11G/datafile/o1_mf_undotbs1_br4q22z4_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=8 STAMP=882712324 file name=/extend/oradata/aux/ORA11G/datafile/o1_mf_sysaux_br4q22yx_.dbf

--啟動虛擬例項資料，完成恢復。

contents of Memory Script:

{

# set requested point in time

set until time "to_date('2015-06-18 10:51:19','yyyy-mm-dd hh24:mi:ss')";

# online the datafiles restored or switched

sql clone "alter database datafile 1 online";

sql clone "alter database datafile 5 online";

sql clone "alter database datafile 3 online";

sql clone "alter database datafile 2 online";

sql clone "alter database datafile 7 online";

# recover and open resetlogs

recover clone database tablespace "TESTTBL", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 5 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 2 online

sql statement: alter database datafile 7 online

Starting recover at 18-JUN-15

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 17 is already on disk as file /u01/app/fast_recovery_area/ORA11G/archivelog/2015_06_18/o1_mf_1_17_br4pw26f_.arc

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=16

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/fast_recovery_area/ORA11G/backupset/2015_06_18/o1_mf_annnn_TAG20150618T104401_br4d3k9f_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/fast_recovery_area/ORA11G/backupset/2015_06_18/o1_mf_annnn_TAG20150618T104401_br4d3k9f_.bkp tag=TAG20150618T104401

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=/extend/oradata/aux/1_16_846081044.dbf thread=1 sequence=16

channel clone_default: deleting archived log(s)

archived log file name=/extend/oradata/aux/1_16_846081044.dbf RECID=27 STAMP=882712328

archived log file name=/u01/app/fast_recovery_area/ORA11G/archivelog/2015_06_18/o1_mf_1_17_br4pw26f_.arc thread=1 sequence=17

media recovery complete, elapsed time: 00:00:01

Finished recover at 18-JUN-15

database opened

--將表空間匯出來

contents of Memory Script:

{

# make read only the tablespace that will be exported

sql clone 'alter tablespace TESTTBL read only';

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/extend/oradata/aux''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/extend/oradata/aux''";

}

executing Memory Script

sql statement: alter tablespace TESTTBL read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/extend/oradata/aux''

Performing export of metadata...

EXPDP> Starting "SYS"."TSPITR_EXP_DDfe":

EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX

EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT

EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

EXPDP> Master table "SYS"."TSPITR_EXP_DDfe" successfully loaded/unloaded

EXPDP> ******************************************************************************

EXPDP> Dump file set for SYS.TSPITR_EXP_DDfe is:

EXPDP> /extend/oradata/aux/tspitr_DDfe_54026.dmp

EXPDP> ******************************************************************************

EXPDP> Datafiles required for transportable tablespace TESTTBL:

EXPDP> /u01/app/oradata/ORA11G/datafile/o1_mf_testtbl_br48h8wp_.dbf

EXPDP> Job "SYS"."TSPITR_EXP_DDfe" successfully completed at Thu Jun 18 13:54:10 2015 elapsed 0 00:01:13

Export completed

--關閉虛擬例項

contents of Memory Script:

{

# shutdown clone before import

shutdown clone immediate

# drop target tablespaces before importing them back

sql 'drop tablespace TESTTBL including contents keep datafiles cascade constraints';

}

executing Memory Script

database closed

database dismounted

Oracle instance shut down

--把原來的表空間刪除掉

sql statement: drop tablespace TESTTBL including contents keep datafiles cascade constraints

--恢復匯入資料

Performing import of metadata...

IMPDP> Master table "SYS"."TSPITR_IMP_DDfe" successfully loaded/unloaded

IMPDP> Starting "SYS"."TSPITR_IMP_DDfe":

IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX

IMPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

IMPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT

IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

IMPDP> Job "SYS"."TSPITR_IMP_DDfe" successfully completed at Thu Jun 18 13:54:54 2015 elapsed 0 00:00:10

Import completed

--第三部分指令碼，恢復表空間

contents of Memory Script:

{

# make read write and offline the imported tablespaces

sql 'alter tablespace TESTTBL read write';

sql 'alter tablespace TESTTBL offline';

# enable autobackups after TSPITR is finished

sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';

}

executing Memory Script

sql statement: alter tablespace TESTTBL read write

sql statement: alter tablespace TESTTBL offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance

Automatic instance removed

auxiliary instance file /extend/oradata/aux/ORA11G/datafile/o1_mf_temp_br4q4ojl_.tmp deleted

auxiliary instance file /extend/oradata/aux/ORA11G/onlinelog/o1_mf_4_br4q4j17_.log deleted

auxiliary instance file /extend/oradata/aux/ORA11G/onlinelog/o1_mf_3_br4q4ghl_.log deleted

auxiliary instance file /extend/oradata/aux/ORA11G/onlinelog/o1_mf_2_br4q4dt5_.log deleted

auxiliary instance file /extend/oradata/aux/ORA11G/onlinelog/o1_mf_1_br4q4brh_.log deleted

auxiliary instance file /extend/oradata/aux/ORA11G/datafile/o1_mf_sysaux_br4q22yx_.dbf deleted

auxiliary instance file /extend/oradata/aux/ORA11G/datafile/o1_mf_undotbs1_br4q22z4_.dbf deleted

auxiliary instance file /extend/oradata/aux/ORA11G/datafile/o1_mf_system_br4q230g_.dbf deleted

auxiliary instance file /extend/oradata/aux/ORA11G/datafile/o1_mf_system_br4q22sy_.dbf deleted

auxiliary instance file /extend/oradata/aux/ORA11G/controlfile/o1_mf_br4q1v2o_.ctl deleted

Finished recover at 18-JUN-15

RMAN>

上面的步驟可以歸納為如下：

ü 在目錄中建立一個虛擬臨時資料庫例項；

ü RMAN自動輔助庫AUX建立，利用源資料庫的備份集合和歸檔日誌進行全庫不完全恢復。恢復範圍包括system、sysaux、undo和目標表空間；

ü 藉助Expdp和Impdp，使用可移動表空間原理步驟，將目標表空間資料匯出並導回源資料庫；

ü 恢復完成；

下面可以看實際效果。

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') a from dual;

A

--------------------

2015-06-18 13:55:43

SQL> select group#, sequence# from v$log where status='CURRENT';

GROUP# SEQUENCE#

---------- ----------

1 20

嘗試訪問資料表。

SQL> select count(*) from test.emp;

select count(*) from test.emp

ORA-00376: 無法讀取資料檔案 7

ORA-01110: 資料檔案 7: '/u01/app/oradata/ORA11G/datafile/o1_mf_testtbl_br48h8wp_.dbf'

此時，表空間被offline，需要手工進行online操作。

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS

------------------------------ ---------

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

TESTTBL OFFLINE

6 rows selected

SQL> alter tablespace testtbl online;

Tablespace altered

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS

------------------------------ ---------

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

TESTTBL ONLINE

6 rows selected

SQL> select count(*) from test.emp;

COUNT(*)

----------

14

SQL> select count(*) from test.dept;

COUNT(*)

----------

4

5、結論

Oracle RMAN的TSPITR在實際中還是很有用處的，特別是當我們沒有辦法使用Flashback技術（如已經發生了Undo覆蓋）的時候。Tablespace的粒度還是比較大，希望以後可以實現Schema或者資料表table級別的的不完全恢復。

Oracle RMAN實現“一鍵式”表空間TSPITR

相關文章