RMAN tablespace point-in-time recovery (TSPITR)

来源：这里教程网时间：2026-03-03 11:36:44 作者：

TSPITR 简单的说就是基于时间点的表空间恢复，它可以把一个或多个表空间恢复到过去某个时间点而不影响剩余的表空间或是数据库对象。
RMAN TSPITR主要应用场景如下：
(1)恢复逻辑数据库到某一个时间点，而在数据库物理结构上与其它表空间时间点不同
(2)恢复因使用DDL语言造成的数据丢失
(3)使用purge删除的table
(4)表的逻辑损坏
(5)恢复删除的表空间，即使不使用catalog，RMAN 一样可以使用TSPITR

在实验中使用的相关技术名词

name	exxxasdfasdf
Target instance	contains the tablespace to be recovered to the target time
target time	point in time or SCN of the tablespace after TSPITR completes
auxiliary destination	an optional disk location that RMAN uses to temporarily store the auxiliary set files,only used with an RMAN-managed auxiliary instance.
auxiliary instance	a database instance used in the recovery process to perform the work of recovery
recovery set	datafiles in the tablespaces that you intend to recover
auxiliary set	data files requried fo TSPITR that are not part of the recovery set. The auxiliary set typically includes: 1.the SYSTEM or SYSAUX tablespaces 2.data file containing rollback or undo segments from the target database instance. 3. temporary tablespace 4.control file from source database 5.archived redo logs that must be restored to recover the auxiliary instance to specified point in time. 6. online redo logs of the auxiliary intance.

一、创建实验环境

点击(此处)折叠或打开

SQL> col name for a60

SQL> select name from v$datafile;

NAME

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

/oracle/database/oradata/mhxy/system01.dbf

/oracle/database/oradata/mhxy/sysaux01.dbf

/oracle/database/oradata/mhxy/undotbs01.dbf

/oracle/database/oradata/mhxy/users01.dbf

SQL> create tablespace test datafile '/oracle/database/oradata/mhxy/test.dbf' size 10m autoextend off extent management local;

Tablespace created.

SQL> select name from v$datafile;

NAME

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

/oracle/database/oradata/mhxy/system01.dbf

/oracle/database/oradata/mhxy/sysaux01.dbf

/oracle/database/oradata/mhxy/undotbs01.dbf

/oracle/database/oradata/mhxy/users01.dbf

/oracle/database/oradata/mhxy/test.dbf

SQL> create user test identified by test default tablespace test temporary tablespace temp;

User created.

SQL> grant dba to test;

Grant succeeded.

SQL> conn test/test

Connected.

SQL> create table tt as select * from user_objects;

Table created.

SQL> create table test.t1 as select * from dba_objects;

create table test.t1 as select * from dba_objects

ERROR at line 1:

ORA-01652: unable to extend temp segment by 128 in tablespace TEST

SQL> alter database datafile '/oracle/database/oradata/mhxy/test.dbf' resize 50m;

Database altered.

SQL> create table test.t1 as select * from dba_objects;

Table created.

SQL> conn / as sysdba

Connected.

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

COUNT(*)

----------

71904

SQL> exit

#通过RMAN 备份数据库#

RMAN> backup as backupset database plus archivelog;

Starting backup at 2016-07-26 04:45:02
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=150 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=918187812
input archived log thread=1 sequence=6 RECID=2 STAMP=918189905
channel ORA_DISK_1: starting piece 1 at 2016-07-26 04:45:06
channel ORA_DISK_1: finished piece 1 at 2016-07-26 04:45:13
piece handle=/oracle/database/flash_recovery_area/MHXY/backupset/2016_07_26/o1_mf_annnn_TAG20160726T044506_csdyllfs_.bkp tag=TAG20160726T044506 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 2016-07-26 04:45:13

Starting backup at 2016-07-26 04:45:13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/database/oradata/mhxy/system01.dbf
input datafile file number=00002 name=/oracle/database/oradata/mhxy/sysaux01.dbf
input datafile file number=00003 name=/oracle/database/oradata/mhxy/undotbs01.dbf
input datafile file number=00005 name=/oracle/database/oradata/mhxy/test.dbf
input datafile file number=00004 name=/oracle/database/oradata/mhxy/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2016-07-26 04:45:14
channel ORA_DISK_1: finished piece 1 at 2016-07-26 04:46:51
piece handle=/oracle/database/flash_recovery_area/MHXY/backupset/2016_07_26/o1_mf_nnndf_TAG20160726T044513_csdyltqt_.bkp tag=TAG20160726T044513 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:37
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2016-07-26 04:46:55
channel ORA_DISK_1: finished piece 1 at 2016-07-26 04:46:56
piece handle=/oracle/database/flash_recovery_area/MHXY/backupset/2016_07_26/o1_mf_ncsnf_TAG20160726T044513_csdyoz8x_.bkp tag=TAG20160726T044513 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-07-26 04:46:56

Starting backup at 2016-07-26 04:46:56
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=3 STAMP=918190016
channel ORA_DISK_1: starting piece 1 at 2016-07-26 04:46:57
channel ORA_DISK_1: finished piece 1 at 2016-07-26 04:46:58
piece handle=/oracle/database/flash_recovery_area/MHXY/backupset/2016_07_26/o1_mf_annnn_TAG20160726T044656_csdyp11c_.bkp tag=TAG20160726T044656 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-07-26 04:46:58

RMAN> exit

二、确定需要恢复的时间点（point-in-time），这个时间在实验环境中可以通过自己判断，然后drop tablespace test。但在实际环境中，需要通过logmintor确定，下一篇会阐述下logmintor的使用。

点击(此处)折叠或打开

SQL> set linesize 300

SQL>

SQL> select to_char(scn_to_timestamp(current_scn),'yyyy-mm-dd hh24:mi:ss'),current_scn from v$database;

TO_CHAR(SCN_TO_TIMESTAMP(CURRENT_SCN), CURRENT_SCN

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

2016-07-26 04:48:56 1008558

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------------------------------------ ------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE

SQL> col name for a50

SQL> select name from v$datafile;

NAME

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

/oracle/database/oradata/mhxy/system01.dbf

/oracle/database/oradata/mhxy/sysaux01.dbf

/oracle/database/oradata/mhxy/undotbs01.dbf

/oracle/database/oradata/mhxy/users01.dbf

SQL> host ll -lrt /oracle/database/oradata/mhxy/

/bin/bash: ll: command not found

SQL> host ls -lrt /oracle/database/oradata/mhxy/

total 1476888

-rw-r----- 1 oracle oinstall 30416896 Jul 26 04:23 temp01.dbf

-rw-r----- 1 oracle oinstall 52429312 Jul 26 04:45 redo03.log

-rw-r----- 1 oracle oinstall 702554112 Jul 26 04:45 system01.dbf

-rw-r----- 1 oracle oinstall 99622912 Jul 26 04:45 undotbs01.dbf

-rw-r----- 1 oracle oinstall 534781952 Jul 26 04:45 sysaux01.dbf

-rw-r----- 1 oracle oinstall 5251072 Jul 26 04:45 users01.dbf

-rw-r----- 1 oracle oinstall 52429312 Jul 26 04:46 redo01.log

-rw-r----- 1 oracle oinstall 52429312 Jul 26 04:50 redo02.log

-rw-r----- 1 oracle oinstall 9748480 Jul 26 04:50 control01.ctl

SQL> exit

# 通过视图和操作系统命令发现，表空间test 数据文件test.dbf以及在操作系统目录下面的test.dbf都已经不存在了

三、通过RMAN 进行TSPITR操作，在执行过程很简单，但是在其中也遇到的很多问题。1. auxiliary destination 目录需要在操作系统中通mkdir创建 2. 表空间的名称需要用引号括起来并行字母大写，如"TEST"
点击(此处)折叠或打开

RMAN> recover tablespace "test" until time '2016-07-26 04:48:56' auxiliary destination '/oracle/auxiliary';

# 未创建auxiliary destination，表空间名称未大写

Starting recover at 2016-07-26 04:54:30

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=138 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='knwx'

initialization parameters used for automatic instance:

db_name=MHXY

db_unique_name=knwx_tspitr_MHXY

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=/oracle/auxiliary

log_archive_dest_1='location=/oracle/auxiliary'

#No auxiliary parameter file used

starting up automatic instance MHXY

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 07/26/2016 04:54:40

RMAN-04014: startup failed: ORA-01261: Parameter db_create_file_dest destination string cannot be translated

ORA-01262: Stat failed on a file destination directory

Linux-x86_64 Error: 2: No such file or directory

RMAN> exit

Recovery Manager complete.

[oracle@mhxy164 ~]$ mkdir -p /oracle/auxiliary

[oracle@mhxy164 ~]$ ll !$

ll /oracle/auxiliary

total 0

[oracle@mhxy164 ~]$ echo $？

$？

[oracle@mhxy164 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jul 26 04:55:14 2016

connected to target database: MHXY (DBID=4238569140)

RMAN> recover tablespace "test" until time '2016-07-26 04:48:56' auxiliary destination '/oracle/auxiliary';

#表空间名称未大写

Starting recover at 2016-07-26 04:55:19

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=26 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='cmmh'

initialization parameters used for automatic instance:

db_name=MHXY

db_unique_name=cmmh_tspitr_MHXY

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=/oracle/auxiliary

log_archive_dest_1='location=/oracle/auxiliary'

#No auxiliary parameter file used

starting up automatic instance MHXY

Oracle instance started

Total System Global Area 292278272 bytes

Fixed Size 2212736 bytes

Variable Size 100666496 bytes

Database Buffers 184549376 bytes

Redo Buffers 4849664 bytes

Automatic instance created

List of tablespaces that have been dropped from the target database:

Tablespace test

contents of Memory Script:

{

# set requested point in time

set until time "2016-07-26 04:48:56";

# 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 2016-07-26 04:55:29

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=59 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 /oracle/database/flash_recovery_area/MHXY/backupset/2016_07_26/o1_mf_ncsnf_TAG20160726T044513_csdyoz8x_.bkp

channel ORA_AUX_DISK_1: piece handle=/oracle/database/flash_recovery_area/MHXY/backupset/2016_07_26/o1_mf_ncsnf_TAG20160726T044513_csdyoz8x_.bkp tag=TAG20160726T044513

channel ORA_AUX_DISK_1: restored backup piece 1

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

output file name=/oracle/auxiliary/MHXY/controlfile/o1_mf_csdz6245_.ctl

Finished restore at 2016-07-26 04:55:31

sql statement: alter database mount clone database

sql statement: alter system archive log current

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

Removing automatic instance

shutting down automatic instance

database dismounted

Oracle instance shut down

Automatic instance removed

auxiliary instance file /oracle/auxiliary/MHXY/controlfile/o1_mf_csdz6245_.ctl deleted

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 07/26/2016 04:55:41

RMAN-06136: ORACLE error from auxiliary database: RMAN-20202: Tablespace not found in the recovery catalog

RMAN-06019: could not translate tablespace name "test"

#正确的命令

RMAN> recover tablespace "TEST" until time '2016-07-26 04:48:56' auxiliary destination '/oracle/auxiliary';

Starting recover at 2016-07-26 04:57:30

using channel ORA_DISK_1

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='nAif'

initialization parameters used for automatic instance:

db_name=MHXY

db_unique_name=nAif_tspitr_MHXY

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=/oracle/auxiliary

log_archive_dest_1='location=/oracle/auxiliary'

#No auxiliary parameter file used

starting up automatic instance MHXY

Oracle instance started

Total System Global Area 292278272 bytes

Fixed Size 2212736 bytes

Variable Size 100666496 bytes

Database Buffers 184549376 bytes

Redo Buffers 4849664 bytes

Automatic instance created

List of tablespaces that have been dropped from the target database:

Tablespace TEST

contents of Memory Script:

{

# set requested point in time

set until time "2016-07-26 04:48:56";

# 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 2016-07-26 04:57:40

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=59 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 /oracle/database/flash_recovery_area/MHXY/backupset/2016_07_26/o1_mf_ncsnf_TAG20160726T044513_csdyoz8x_.bkp

channel ORA_AUX_DISK_1: piece handle=/oracle/database/flash_recovery_area/MHXY/backupset/2016_07_26/o1_mf_ncsnf_TAG20160726T044513_csdyoz8x_.bkp tag=TAG20160726T044513

channel ORA_AUX_DISK_1: restored backup piece 1

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

output file name=/oracle/auxiliary/MHXY/controlfile/o1_mf_csdzb4pk_.ctl

Finished restore at 2016-07-26 04:57:41

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 "2016-07-26 04:48:56";

# set destinations for recovery set and auxiliary set datafiles

set 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;

set newname for datafile 5 to

"/oracle/database/oradata/mhxy/test.dbf";

# switch all tempfiles

switch clone tempfile all;

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

restore clone datafile 1, 3, 2, 5;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed tempfile 1 to /oracle/auxiliary/MHXY/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 2016-07-26 04:57:46

using channel ORA_AUX_DISK_1

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 /oracle/auxiliary/MHXY/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /oracle/auxiliary/MHXY/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /oracle/auxiliary/MHXY/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /oracle/database/oradata/mhxy/test.dbf

channel ORA_AUX_DISK_1: reading from backup piece /oracle/database/flash_recovery_area/MHXY/backupset/2016_07_26/o1_mf_nnndf_TAG20160726T044513_csdyltqt_.bkp

channel ORA_AUX_DISK_1: piece handle=/oracle/database/flash_recovery_area/MHXY/backupset/2016_07_26/o1_mf_nnndf_TAG20160726T044513_csdyltqt_.bkp tag=TAG20160726T044513

channel ORA_AUX_DISK_1: restored backup piece 1

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

Finished restore at 2016-07-26 04:58:52

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=918190732 file name=/oracle/auxiliary/MHXY/datafile/o1_mf_system_csdzbc66_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=5 STAMP=918190732 file name=/oracle/auxiliary/MHXY/datafile/o1_mf_undotbs1_csdzbc7g_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=6 STAMP=918190732 file name=/oracle/auxiliary/MHXY/datafile/o1_mf_sysaux_csdzbc79_.dbf

contents of Memory Script:

{

# set requested point in time

set until time "2016-07-26 04:48:56";

# online the datafiles restored or switched

sql clone "alter database datafile 1 online";

sql clone "alter database datafile 3 online";

sql clone "alter database datafile 2 online";

sql clone "alter database datafile 5 online";

# recover and open resetlogs

recover clone database tablespace "TEST", "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 3 online

sql statement: alter database datafile 2 online

sql statement: alter database datafile 5 online

Starting recover at 2016-07-26 04:58:52

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /oracle/database/flash_recovery_area/MHXY/archivelog/2016_07_26/o1_mf_1_7_csdyp0pw_.arc

archived log for thread 1 with sequence 8 is already on disk as file /oracle/database/flash_recovery_area/MHXY/archivelog/2016_07_26/o1_mf_1_8_csdz67oz_.arc

archived log file name=/oracle/database/flash_recovery_area/MHXY/archivelog/2016_07_26/o1_mf_1_7_csdyp0pw_.arc thread=1 sequence=7

archived log file name=/oracle/database/flash_recovery_area/MHXY/archivelog/2016_07_26/o1_mf_1_8_csdz67oz_.arc thread=1 sequence=8

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

Finished recover at 2016-07-26 04:58:55

database opened

contents of Memory Script:

{

# make read only the tablespace that will be exported

sql clone 'alter tablespace "TEST" read only';

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/oracle/auxiliary''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/oracle/auxiliary''";

}

executing Memory Script

sql statement: alter tablespace "TEST" read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/oracle/auxiliary''

Performing export of metadata...

EXPDP> Starting "SYS"."TSPITR_EXP_nAif":

EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

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

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

EXPDP> Dump file set for SYS.TSPITR_EXP_nAif is:

EXPDP> /oracle/auxiliary/tspitr_nAif_78214.dmp

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

EXPDP> Datafiles required for transportable tablespace TEST:

EXPDP> /oracle/database/oradata/mhxy/test.dbf

EXPDP> Job "SYS"."TSPITR_EXP_nAif" successfully completed at 05:00:20

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone immediate

}

executing Memory Script

database closed

database dismounted

Oracle instance shut down

Performing import of metadata...

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

IMPDP> Starting "SYS"."TSPITR_IMP_nAif":

IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

IMPDP> Job "SYS"."TSPITR_IMP_nAif" successfully completed at 05:00:57

Import completed

contents of Memory Script:

{

# make read write and offline the imported tablespaces

sql 'alter tablespace "TEST" read write';

sql 'alter tablespace "TEST" offline';

# enable autobackups after TSPITR is finished

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

}

executing Memory Script

sql statement: alter tablespace "TEST" read write

sql statement: alter tablespace "TEST" offline

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

Removing automatic instance

Automatic instance removed

auxiliary instance file /oracle/auxiliary/MHXY/datafile/o1_mf_temp_csdzds8s_.tmp deleted

auxiliary instance file /oracle/auxiliary/MHXY/onlinelog/o1_mf_3_csdzdnmk_.log deleted

auxiliary instance file /oracle/auxiliary/MHXY/onlinelog/o1_mf_2_csdzdlfx_.log deleted

auxiliary instance file /oracle/auxiliary/MHXY/onlinelog/o1_mf_1_csdzdhj7_.log deleted

auxiliary instance file /oracle/auxiliary/MHXY/datafile/o1_mf_sysaux_csdzbc79_.dbf deleted

auxiliary instance file /oracle/auxiliary/MHXY/datafile/o1_mf_undotbs1_csdzbc7g_.dbf deleted

auxiliary instance file /oracle/auxiliary/MHXY/datafile/o1_mf_system_csdzbc66_.dbf deleted

auxiliary instance file /oracle/auxiliary/MHXY/controlfile/o1_mf_csdzb4pk_.ctl deleted

Finished recover at 2016-07-26 05:01:00

RMAN>

四、在sqlplus中验证，发现通过TSPITR恢复的表空间状态是offline，需要alter 到online

点击(此处)折叠或打开

SQL> col name for a60

SQL> select name from v$datafile;

NAME

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

/oracle/database/oradata/mhxy/system01.dbf

/oracle/database/oradata/mhxy/sysaux01.dbf

/oracle/database/oradata/mhxy/undotbs01.dbf

/oracle/database/oradata/mhxy/users01.dbf

/oracle/database/oradata/mhxy/test.dbf

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------------------------------------ ------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TEST OFFLINE

6 rows selected.

SQL> alter tablespace test online;

Tablespace altered.

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

COUNT(*)

----------

71904

五、思考，在实际生产环境中需要坚持是否自包含对象，如下就会有自包含现象，需要删除索引等表空间TEST恢复后重新创建

点击(此处)折叠或打开

SQL> create index idx1 on test.t1(owner) tablespace users;

Index created.

SQL> select * from SYS.TS_PITR_CHECK a WHERE a.ts1_name='TEST';

OBJ1_OWNER    OBJ1_NAME OBJ1_SUBNAME OBJ1_TYPE TS1_NAME   OBJ2_NAME OBJ2_SUBNAME OBJ2_TYPE OBJ2_OWNER TS2_NAME   CONSTRAINT_NAME REASON
---------- -----------   -------------   ----------   --------- --------- ------------ ---------- ----------- ---------- --------------- ----------
TEST   T1     BLE     TEST     IDX1   INDEX SYS USERS Tables and associated indexes not fully
contained in the recovery set