Oracle database TSPITR(TableSpace Point-In-Time Recovery) 表空间基于时间点的恢复

 主要用于恢复人为的错误

需要有之前的backup。

适用场景

(1)恢复错误的dml语句

(2)恢复错误的ddl语句，比如说更改了表结构，这个时候无法使用flashback table

(3)恢复drop+purge的表

(4)恢复逻辑错误的表

(5)恢复被删除的表空间（rman可以在被drop的表空间上面执行TSPITR）

RMAN TSPITR的参数

Name	Explanation
Target instance	Contains the tablespaceto be recovered to the target time 包含需要恢复的表空间的实例
Target time	Point in time or SCN of the tablespace after TSPITR completes 要恢复的目标时间点或者scn
Auxiliary instance	A database instance usedin the recovery process to perform the work of recovery. The auxiliary instance has other files associated with it. See auxiliary set for a complete list. 用来处理各种恢复表空间工作的实例。 辅助实例需要有其关联的文件。
Auxiliary destination	An optional disk location that RMAN uses to temporarily store the auxiliary set files. The auxiliary destination is used only with an RMAN-managed auxiliary instance. Specifying an auxiliary destination with a user-managed auxiliary instance results in an error. All references to auxiliary destination in this chapter assume use of an RMAN-managed auxiliary instance. 用来临时存储辅助实例相关文件的辅助目录。
Recovery set	Data files in the tablespaces that you intend to recover 需要恢复的表空间中的数据文件
Auxiliary set	Data files required for TSPITR that are not part of the recovery set. 辅助实例的其他文件集合。 The auxiliary set typically includes: ■ The SYSTEMand SYSAUXtablespaces. system和sysaux表空间 ■ Data files containing rollbackor undo segments from the target database instance. 目标数据库实例中的包括undo segments的数据文件。 ■ Temporary tablespaces. 临时表空间 ■ Control file from source database. 源数据库的控制文件 ■ Archived redo logs that must be restored to recover the auxiliary instance to specified point in time. 归档日志文件 ■ Online redo logs ofthe auxiliary instance. These are not the same logs as the online redo logs from the source database. They are created when the auxiliary instance is opened with the RESETLOGSoption. 辅助实例在线日志文件 The auxiliary set does not include the parameter file, password file, or associated network files. 辅助实例不包括参数文件、密码文件、或者相关的监听器文件。

tspitr 模式

自动

半自动

手动

后面举例说明

使用rman 管理的辅助示例会自动做下面的工作：

1.If the tablespaces in the recovery set have not been dropped, checks to see if they
are self-contained by executing the DBMS_TTS.TRANSPORT_SET_CHECKfor the
recovery set tablespaces and then checking that the view TRANSPORT_SET_
VIOLATIONSis empty. If the query returns rows, RMAN stops TSPITR processing.
You must resolve any tablespace containment violations before TSPITR can
proceed. Example 21–1shows you how to set up and run the query before
invoking RMAN TSPITR.

如果要恢复的表空间没有被删除，那么检查这个表空间结合是否是自包含的。

2. Checks to see if a connection to a user-managed auxiliary instance was provided.
If it is, then RMAN TSPITR uses it. Ifnot, RMAN TSPITR creates the auxiliary
instance, starts it, and connects to it. 

检查指定连接的辅助实例是否支持这项操作。如果不支持，rman tapitr会创建一个辅助实例。

3. Takes the tablespaces to be recovered offline in the target database, if the
tablespaces in the recovery set have not been dropped.

将表空间置于offline模式（如果没有被删除）

4. Restores a backup control file from a point in time before the target time to the
auxiliary instance.

恢复target time 之前的控制文件。

5. Restores the data files from the recovery set and the auxiliary set to the auxiliary
instance. 

恢复集合中的数据文件和其他辅助实例需要的文件到辅助实例上面。
Files are restored either in the:
■ Locations that you specify for each file

每个指定的文件

■ Original location of the file (for recovery set data files) 

原始的文件位置

■ Auxiliary destination (if you used the AUXILIARY DESTINATIONargument 

of RECOVER TABLESPACEand an RMAN-managed auxiliary instance)

辅助目录

6. Recovers the restored data files in the auxiliary instance to the specified time.

在辅助实例上面恢复数据文件到指定的时间点

7. Opens the auxiliary database with the RESETLOGSoption. 

使用resetlogs选项爱你个打开辅助数据库

8. Makes the recovery set tablespaces read-only in the auxiliary instance.

辅助实例上讲恢复集合中包含的表空间置为readonly模式

9. Exports the recovery set tablespaces fromthe auxiliary instance using the Data 

导出metadata。

Pump utility to produce a transportable tablespace dump file.
10. Shuts down the auxiliary instance.

关闭辅助实例

11. Drops the recovery set tablespaces from the target.

删除恢复集合中的表空间（在源数据库上）

12. Data Pump utility reads the transportable tablespace dump file and plugs the
recovery set tablespaces into the target.

传输表空间数据文件，并且导入metadata（使用传输表空间的方式）

13. Makes the tablespaces that were put inthe target database read/write and
immediately takes them offline.

将恢复好的表空间置为read write状态并且立刻置为offline状态。

14. Deletes all auxiliary set files.

删除所有辅助文件

什么时候无法运行tspitr

■ If there are no archived redo logs or if the database runs in NOARCHIVELOGmode.

如果没有归档日志文件或者数据库运行在noarchivelog 模式

■ If TSPITR is used to recover a renamed tablespace to a point in time before it was
renamed, you must use the previous name of the tablespace to perform the
recovery operation. 

tspitr无法重命名表空间，如果这个表空间被重命名过。需要使用老的表空间的名称来执行恢复操作。

最后恢复完成后会出现2个表空间，一个是原始的拥有新名称的表空间，一个是使用tspitr恢复的老名称的表空间

。可以将新的表空间删除掉。

In this case when TSPITR completes, the target database contains two copies of the
same tablespace, the original tablespace with the new name and the TSPITR
tablespace with the old name. If this is not your goal, then you can drop the new
tablespace with the new name.
■ If constraints for the tables in tablespace tbs1are contained in tablespace tbs2,
then you cannot recover tbs1without also recovering tbs2.

如果不是自包含

■ You cannot use TSPITR to recover the current default tablespace.

无法运行在当前的default tablespace上面

■ You cannot use TSPITR to recover tablespaces containing any of the following
objects:

无法恢复包含下面数据类型的表空间
– Objects with underlying objects (such as materialized views) or contained
objects (such as partitioned tables) unless all of the underlying or contained
objects are in the recovery set

分区没有全部包含在恢复集合中

– Undo or rollback segments

undo或者rollback segment

– Oracle8-compatible advanced queues with multiple recipients

oracle8兼容的高级队列

– Objects owned by the userSYS. Examples of these types of objects are:
PL/SQL, Java classes, callout programs, views, synonyms, users, privileges,
dimensions, directories, and sequences

owner=sys的数据库对象。such as ： sort of 

TSPITR 限制

无法恢复统计信息

从前的备份无法使用

如果使用了

Oracle Managed File (OMF)

rman 无法重用数据文件，所以需要消耗双倍的磁盘空间。

RECOVER TABLESPACE users, tools
UNTIL LOGSEQ 1300 THREAD 1
AUXILIARY DESTINATION '/disk1/auxdest';

 

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('dextbs',true) ;


SELECT * FROM TRANSPORT_SET_VIOLATIONS;

执行tspitr之前的考虑操作

1. 选择正确的时间点

2. 确定恢复集合

3. 标识时间点到现在，最近创建的对象。

基于时间点

SELECT OWNER, NAME, TABLESPACE_NAME,
TO_CHAR(CREATION_TIME, 'YYYY-MM-DD:HH24:MI:SS')
FROM TS_PITR_OBJECTS_TO_BE_DROPPED
WHERE TABLESPACE_NAME IN ('USERS','TOOLS')
AND CREATION_TIME > TO_DATE('02-NOV-07:07:03:11','YY-MON-DD:HH24:MI:SS')
ORDER BY TABLESPACE_NAME, CREATION_TIME;

基于scn

SELECT OWNER, NAME, TABLESPACE_NAME,
TO_CHAR(CREATION_TIME,'YYYY-MM-DD:HH24:MI:SS')
FROM TS_PITR_OBJECTS_TO_BE_DROPPED
WHERE TABLESPACE_NAME IN ('USERS','TOOLS')
AND CREATION_TIME > TO_DATE(TO_CHAR(SCN_TO_TIMESTAMP(1645870),
'MM/DD/YYYY HH24:MI:SS'),
'MM/DD/YYYY HH24:MI:SS')
ORDER BY TABLESPACE_NAME, CREATION_TIME;

view ts_pitr_objects_to_be_dropped :

create  or  replace  view  ts_pitr_objects_to_be_dropped

(owner,  name , creation_time, tablespace_name)

as

( select  u.name,o.name,o.ctime,tablespace_name

from  user$ u, obj$ o, dba_segments s

where  u.user# = o.owner#

   and  o.name  = s.segment_name

   and  u.name  = s.owner);

RECOVER TABLESPACE users, tools
UNTIL LOGSEQ 1300 THREAD 1
AUXILIARY DESTINATION '/disk1/auxdest';

RECOVER TABLESPACE parts UNTIL TIME to_Date('','yyyymmddhh24miss') 

AUXILIARY DESTINATION '' ;

example1 :

11.2.0.3

恢复被删除的表空间，必须在这个表空间创建以后拥有备份文件。

recover dropped tablespace 

create tablespace tspitrtbs datafile file '

create tablespace tspitrtbs datafile '/u01/apps/oracle/oradata/gg1/tspitr01.dbf' size 10m autoextend on next 100m ;

create table t tablespace tspitrtbs as select level id , level ||'name' as name from dual connect by level <= 10000 ;

backup database ;

drop tablespace tspitrtbs including contents and datafiles ;

dexter@GG1> dexter@GG1> select max(sequence#) from v$log ;

MAX(SEQUENCE#)
--------------
            24

RUN 
{

SET NEWNAME FOR TABLESPACE tspitrtbs
TO '/u01/%b';

RECOVER TABLESPACE tspitrtbs UNTIL LOGSEQ 24 THREAD 1

AUXILIARY DESTINATION '/u01' ;

}

---

RMAN> RUN
2> {
3> SET NEWNAME FOR TABLESPACE tspitrtbs
4> TO '/u01/%b';
5> RECOVER TABLESPACE tspitrtbs UNTIL LOGSEQ 24 THREAD 1
AUXILIARY DESTINATION '/u01' ;
}
6> 7>
executing command: SET NEWNAME

Starting recover at 30-JUL-13
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='Fota'

initialization parameters used for automatic instance:
db_name=GG1
db_unique_name=Fota_tspitr_GG1
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/u01
log_archive_dest_1='location=/u01'
#No auxiliary parameter file used


starting up automatic instance GG1

Oracle instance started

Total System Global Area     292278272 bytes

Fixed Size                     2227744 bytes
Variable Size                100663776 bytes
Database Buffers             184549376 bytes
Redo Buffers                   4837376 bytes
Automatic instance created


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

contents of Memory Script:
{
# set requested point in time
set until  logseq 24 thread 1;
# 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 30-JUL-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=80 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/apps/oracle/fast_recovery_area/GG1/autobackup/2013_07_30/o1_mf_s_822111655_8zfmo7vh_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/apps/oracle/fast_recovery_area/GG1/autobackup/2013_07_30/o1_mf_s_822111655_8zfmo7vh_.bkp tag=TAG20130730T042055
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/GG1/controlfile/o1_mf_8zfmsy99_.ctl
Finished restore at 30-JUL-13

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  logseq 24 thread 1;
# 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  8 to
"/u01/tspitr01.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, 8;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/GG1/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 30-JUL-13
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 /u01/GG1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/GG1/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/GG1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/tspitr01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/apps/oracle/fast_recovery_area/GG1/backupset/2013_07_30/o1_mf_nnndf_TAG20130730T041810_8zfmj327_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/apps/oracle/fast_recovery_area/GG1/backupset/2013_07_30/o1_mf_nnndf_TAG20130730T041810_8zfmj327_.bkp tag=TAG20130730T041810
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:25
Finished restore at 30-JUL-13

datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=822111958 file name=/u01/tspitr01.dbf
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=822111958 file name=/u01/GG1/datafile/o1_mf_system_8zfmt5sz_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=822111958 file name=/u01/GG1/datafile/o1_mf_undotbs1_8zfmt62o_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=822111958 file name=/u01/GG1/datafile/o1_mf_sysaux_8zfmt5yl_.dbf

contents of Memory Script:
{
# set requested point in time
set until  logseq 24 thread 1;
# 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  8 online";
# recover and open resetlogs
recover clone database tablespace  "TSPITRTBS", "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  8 online

Starting recover at 30-JUL-13
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 21 is already on disk as file /u01/apps/oracle/fast_recovery_area/GG1/archivelog/2013_07_30/o1_mf_1_21_8zfmp4b4_.arc
archived log for thread 1 with sequence 22 is already on disk as file /u01/apps/oracle/fast_recovery_area/GG1/archivelog/2013_07_30/o1_mf_1_22_8zfmp5d6_.arc
archived log for thread 1 with sequence 23 is already on disk as file /u01/apps/oracle/fast_recovery_area/GG1/archivelog/2013_07_30/o1_mf_1_23_8zfmp8y8_.arc
archived log file name=/u01/apps/oracle/fast_recovery_area/GG1/archivelog/2013_07_30/o1_mf_1_21_8zfmp4b4_.arc thread=1 sequence=21
archived log file name=/u01/apps/oracle/fast_recovery_area/GG1/archivelog/2013_07_30/o1_mf_1_22_8zfmp5d6_.arc thread=1 sequence=22
archived log file name=/u01/apps/oracle/fast_recovery_area/GG1/archivelog/2013_07_30/o1_mf_1_23_8zfmp8y8_.arc thread=1 sequence=23
media recovery complete, elapsed time: 00:00:01
Finished recover at 30-JUL-13

database opened

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  TSPITRTBS read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01''";
}
executing Memory Script

sql statement: alter tablespace  TSPITRTBS read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01''

Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_Fota": 
   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_Fota" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_Fota is:
   EXPDP>   /u01/tspitr_Fota_34269.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace TSPITRTBS:
   EXPDP>   /u01/tspitr01.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_Fota" successfully completed at 04:27:52
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_Fota" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_Fota": 
   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_Fota" successfully completed at 04:28:15
Import completed


contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  TSPITRTBS read write';
sql 'alter tablespace  TSPITRTBS offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script

sql statement: alter tablespace  TSPITRTBS read write

sql statement: alter tablespace  TSPITRTBS offline

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

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/GG1/datafile/o1_mf_temp_8zfmz18y_.tmp deleted
auxiliary instance file /u01/GG1/onlinelog/o1_mf_3_8zfmyy8j_.log deleted
auxiliary instance file /u01/GG1/onlinelog/o1_mf_2_8zfmyw2v_.log deleted
auxiliary instance file /u01/GG1/onlinelog/o1_mf_1_8zfmysbf_.log deleted
auxiliary instance file /u01/GG1/datafile/o1_mf_sysaux_8zfmt5yl_.dbf deleted
auxiliary instance file /u01/GG1/datafile/o1_mf_undotbs1_8zfmt62o_.dbf deleted
auxiliary instance file /u01/GG1/datafile/o1_mf_system_8zfmt5sz_.dbf deleted
auxiliary instance file /u01/GG1/controlfile/o1_mf_8zfmsy99_.ctl deleted
Finished recover at 30-JUL-13

---

dexter@GG1> alter database datafile 8 online ;

Database altered.

dexter@GG1> alter tablespace tspitrtbs online ;

Tablespace altered.

dexter@GG1> select count(*) from t ;

  COUNT(*)
----------
     10000

example 2 :

10.2.0.5

10g 不支持恢复删除的表空间。

sys@ORCL> select table_name,owner from dba_tables where tablespace_name='IOTEXTBS' ;

TABLE_NAME                     OWNER
------------------------------ ------------------------------
t                              TEST
TEST2                          TEST
TEST1                          TEST

...

49 rows selected.

backup database ;

select max(sequence#) from v$log ;

select to_char(sysdate,'yyyymmddhh24miss') from dual ;

@scn

test@ORCL> select max(sequence#) from v$log ;


MAX(SEQUENCE#)
--------------
           110

test@ORCL> test@ORCL>
test@ORCL> select to_char(sysdate,'yyyymmddhh24miss') from dual ;

TO_CHAR(SYSDAT
--------------
20130730191955

test@ORCL>
test@ORCL> @scn

CURRENT_SCN
-----------
    2009922

test@ORCL> drop table user_info ;

Table dropped.

drop tablespace IOTEXTBS including contents and datafiles ;

select max(sequence#) from v$log ;

select to_char(sysdate,'yyyymmddhh24miss') from dual ;

@scn

test@ORCL> select max(sequence#) from v$log ;

MAX(SEQUENCE#)
--------------
           110

test@ORCL> select to_char(sysdate,'yyyymmddhh24miss') from dual ;

TO_CHAR(SYSDAT
--------------
20130730192040

test@ORCL> @scn

CURRENT_SCN
-----------
    2010308

这里可以使用logmnr来查找删除表时候的scn。

RECOVER TABLESPACE "TEST" UNTIL SCN  2009922 AUXILIARY DESTINATION '/u01/apps/oracle/aux_dir' ;

test@ORCL> ALTER TABLESPACE TEST ONLINE ;

Tablespace altered.

test@ORCL> SELECT COUNT(*) FROM USER_INFO ;

  COUNT(*)
----------
        13

相当有用的嘛 

example 3 

same like example 1

archive log list ;

create tablespace test datafile '&file_path' size 10m autoextend on next 100m ;

create table t tablespace test as select level as id , level ||'name' as name from dual connect by level<= 10000 ;

create table f tablespace test as select * from all_objects ;

---

sys@GG2> create tablespace test datafile '&file_path' size 10m autoextend on next 100m ;
Enter value for file_path: /u01/apps/oracle/oradata/gg2/test01.dbf
old   1: create tablespace test datafile '&file_path' size 10m autoextend on next 100m
new   1: create tablespace test datafile '/u01/apps/oracle/oradata/gg2/test01.dbf' size 10m autoextend on next 100m

Tablespace created.

dex@GG2> create table t tablespace test as select level as id , level ||'name' as name from dual connect by level<= 10000 ;


Table created.

dex@GG2> dex@GG2> create table f tablespace test as select * from all_objects ;


Table created.

---

backup database ;

---

RMAN> backup database ;
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=/u01/apps/oracle/oradata/gg2/system01.dbf
input datafile file number=00002 name=/u01/apps/oracle/oradata/gg2/sysaux01.dbf
input datafile file number=00005 name=/u01/apps/oracle/oradata/gg2/example01.dbf
input datafile file number=00008 name=/u01/apps/oracle/oradata/gg2/test01.dbf
input datafile file number=00003 name=/u01/apps/oracle/oradata/gg2/undotbs01.dbf
input datafile file number=00004 name=/u01/apps/oracle/oradata/gg2/users01.dbf
input datafile file number=00006 name=/u01/oinsdir/dextbs01.dbf
input datafile file number=00007 name=/u01/oinsdir/dextertbs01.dbf
channel ORA_DISK_1: starting piece 1 at 30-JUL-13
channel ORA_DISK_1: finished piece 1 at 30-JUL-13
piece handle=/u01/apps/oracle/fast_recovery_areas/GG2/backupset/2013_07_30/o1_mf_nnndf_TAG20130730T083039_8zg29jq2_.bkp tag=TAG20130730T083039 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:05
Finished backup at 30-JUL-13

Starting Control File and SPFILE Autobackup at 30-JUL-13
piece handle=/u01/apps/oracle/fast_recovery_areas/GG2/autobackup/2013_07_30/o1_mf_s_822126765_8zg2ffo6_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 30-JUL-13

---

alter system archive log current ;

alter system archive log current ;

---

sys@GG2> alter system archive log current ;

alter system archive log current ;

System altered.

sys@GG2> sys@GG2>
System altered.

---

select max(sequence#) from v$log ;

select to_char(sysdate,'yyyymmddhh24miss') from dual ;

@scn

drop table t purge ;

drop tablespace test including contents and datafiles ;

select max(sequence#) from v$log ;

select to_char(sysdate,'yyyymmddhh24miss') from dual ;

@scn

---

dex@GG2> select max(sequence#) from v$log ;

MAX(SEQUENCE#)
--------------
            12

dex@GG2> select to_char(sysdate,'yyyymmddhh24miss') from dual ;

TO_CHAR(SYSDAT
--------------
20130730083432

dex@GG2> @scn

CURRENT_SCN
-----------
    1177533

dex@GG2> drop table t purge ;

Table dropped.

dex@GG2>
dex@GG2> drop tablespace test including contents and datafiles ;

Tablespace dropped.

dex@GG2> select max(sequence#) from v$log ;

MAX(SEQUENCE#)
--------------
            12

dex@GG2> select to_char(sysdate,'yyyymmddhh24miss') from dual ;

TO_CHAR(SYSDAT
--------------
20130730083456

dex@GG2> @scn

CURRENT_SCN
-----------
    1177599

---

ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDDHH24MISS' ;

COL NAME FOR A70

SELECT NAME,FIRST_TIME,COMPLETION_TIME FROM V$ARCHIVED_LOG ;

---

dex@GG2> ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDDHH24MISS' ;

Session altered.

dex@GG2>
dex@GG2> COL NAME FOR A70
dex@GG2>
dex@GG2> SELECT NAME,FIRST_TIME,COMPLETION_TIME FROM V$ARCHIVED_LOG ;

NAME                                                                   FIRST_TIME     COMPLETION_TIM
---------------------------------------------------------------------- -------------- --------------
/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/2013_07_28/o1_mf_1 20130605232035 20130728213619
_1_8zb7l6yo_.arc

/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/2013_07_29/o1_mf_1 20130728213605 20130729140045
_2_8zd18bff_.arc

/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/2013_07_29/o1_mf_1 20130729140042 20130729220226
_3_8zdxhbyj_.arc

/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/2013_07_30/o1_mf_1 20130729220216 20130730075224
_4_8zg01p0c_.arc

/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/2013_07_30/o1_mf_1 20130730075221 20130730080715
_5_8zg0xmbt_.arc

/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/2013_07_30/o1_mf_1 20130730080715 20130730080716
_6_8zg0xnp4_.arc

/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/2013_07_30/o1_mf_1 20130730080716 20130730080723
_7_8zg0xv14_.arc

/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/2013_07_30/o1_mf_1 20130730080722 20130730081618
_8_8zg1glck_.arc

/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/2013_07_30/o1_mf_1 20130730081618 20130730082010
_9_8zg1ot87_.arc

/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/2013_07_30/o1_mf_1 20130730082010 20130730083416
_10_8zg2j7y7_.arc

/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/2013_07_30/o1_mf_1 20130730083415 20130730083416
_11_8zg2j8g2_.arc


11 rows selected.

dex@GG2> select * from v$log ;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME     NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- ------------ --------------
         1          1         10   52428800        512          1 YES ACTIVE                 1176710 20130730082010      1177522 20130730083415
         2          1         11   52428800        512          1 YES ACTIVE                 1177522 20130730083415      1177526 20130730083416
         3          1         12   52428800        512          1 NO  CURRENT                1177526 20130730083416   2.8147E+14

dex@GG2> select * from v$logfile ;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
IS_
---
         3         ONLINE
/u01/apps/oracle/oradata/gg2/redo03.log
NO

         2         ONLINE
/u01/apps/oracle/oradata/gg2/redo02.log
NO

         1         ONLINE
/u01/apps/oracle/oradata/gg2/redo01.log
NO

---

execute dbms_logmnr.add_logfile(options =>dbms_logmnr.new,logfilename =>'&logfile_path') ;

exec DBMS_LOGMNR.START_LOGMNR ();

create table t nologging as select * from v$logmnr_contents  ;

---

sys@GG2> execute dbms_logmnr.add_logfile(options =>dbms_logmnr.new,logfilename =>'&logfile_path') ;
Enter value for logfile_path: /u01/apps/oracle/oradata/gg2/redo03.log

PL/SQL procedure successfully completed.

sys@GG2> exec DBMS_LOGMNR.START_LOGMNR ();

PL/SQL procedure successfully completed.

sys@GG2>
sys@GG2> drop table t purge ;

Table dropped.

sys@GG2> create table t nologging as select * from v$logmnr_contents  ;


Table created.

---

select t.scn,t.sql_redo from sys.t t where upper(sql_redo) like '%DROP%' ;

1     1177542     drop table t purge ;
2     1177565     drop table "DEX"."F" cascade constraints purge;
3     1177592     drop tablespace test including contents and datafiles ;
4     1177663     drop table t purge ;

不开启补充日志可以看到sql语句，但是很多sql语句都没有对应到object。很多dml语句没有具体的值

恢复到 1177542

其他的恢复方法：

（

recover tablespace "test" UNTIL LOGSEQ 12 THREAD 1 AUXILIARY DESTINATION '/u01/oinsdir' ;

recover tablespace "test" until time "to_date('','yyyymmddhh24miss')"  AUXILIARY DESTINATION '/u01/oinsdir' ;

recover tablespace "test" until scn 1177542 AUXILIARY DESTINATION '/u01/oinsdir' ;

recover tablespace "test" UNTIL LOGSEQ 5 THREAD 1 AUXILIARY DESTINATION '/u01/oinsdir' ;

）

recover tablespace "TEST" until scn 1177542 AUXILIARY DESTINATION '/u01/oinsdir' ;

****************表空间名称大写

---

RMAN> recover tablespace "TEST" until scn 1177542 AUXILIARY DESTINATION '/u01/oinsdir' ;

Starting recover at 30-JUL-13
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='xFCh'

initialization parameters used for automatic instance:
db_name=GG2
db_unique_name=xFCh_tspitr_GG2
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/u01/oinsdir
log_archive_dest_1='location=/u01/oinsdir'
#No auxiliary parameter file used


starting up automatic instance GG2

Oracle instance started

Total System Global Area     292278272 bytes

Fixed Size                     2227744 bytes
Variable Size                100663776 bytes
Database Buffers             184549376 bytes
Redo Buffers                   4837376 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  scn 1177542;
# 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 30-JUL-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=80 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/apps/oracle/fast_recovery_areas/GG2/autobackup/2013_07_30/o1_mf_s_822126765_8zg2ffo6_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/apps/oracle/fast_recovery_areas/GG2/autobackup/2013_07_30/o1_mf_s_822126765_8zg2ffo6_.bkp tag=TAG20130730T083245
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oinsdir/GG2/controlfile/o1_mf_8zg2w727_.ctl
Finished restore at 30-JUL-13

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  scn 1177542;
# 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  8 to
"/u01/apps/oracle/oradata/gg2/test01.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, 8;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/oinsdir/GG2/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 30-JUL-13
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 /u01/oinsdir/GG2/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/oinsdir/GG2/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/oinsdir/GG2/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/apps/oracle/oradata/gg2/test01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/apps/oracle/fast_recovery_areas/GG2/backupset/2013_07_30/o1_mf_nnndf_TAG20130730T083039_8zg29jq2_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/apps/oracle/fast_recovery_areas/GG2/backupset/2013_07_30/o1_mf_nnndf_TAG20130730T083039_8zg29jq2_.bkp tag=TAG20130730T083039
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:56
Finished restore at 30-JUL-13

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=822127361 file name=/u01/oinsdir/GG2/datafile/o1_mf_system_8zg2wfqz_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=822127361 file name=/u01/oinsdir/GG2/datafile/o1_mf_undotbs1_8zg2wftc_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=822127361 file name=/u01/oinsdir/GG2/datafile/o1_mf_sysaux_8zg2wfr8_.dbf

contents of Memory Script:
{
# set requested point in time
set until  scn 1177542;
# 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  8 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  8 online

Starting recover at 30-JUL-13
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 10 is already on disk as file /u01/apps/oracle/fast_recovery_areas/GG2/archivelog/2013_07_30/o1_mf_1_10_8zg2j7y7_.arc
archived log for thread 1 with sequence 11 is already on disk as file /u01/apps/oracle/fast_recovery_areas/GG2/archivelog/2013_07_30/o1_mf_1_11_8zg2j8g2_.arc
archived log for thread 1 with sequence 12 is already on disk as file /u01/apps/oracle/fast_recovery_areas/GG2/archivelog/2013_07_30/o1_mf_1_12_8zg2s1gy_.arc
archived log file name=/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/2013_07_30/o1_mf_1_10_8zg2j7y7_.arc thread=1 sequence=10
archived log file name=/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/2013_07_30/o1_mf_1_11_8zg2j8g2_.arc thread=1 sequence=11
archived log file name=/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/2013_07_30/o1_mf_1_12_8zg2s1gy_.arc thread=1 sequence=12
media recovery complete, elapsed time: 00:00:01
Finished recover at 30-JUL-13

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 ''
/u01/oinsdir''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/oinsdir''";
}
executing Memory Script

sql statement: alter tablespace  "TEST" read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/oinsdir''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/oinsdir''

Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_xFCh": 
   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_xFCh" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_xFCh is:
   EXPDP>   /u01/oinsdir/tspitr_xFCh_38719.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace TEST:
   EXPDP>   /u01/apps/oracle/oradata/gg2/test01.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_xFCh" successfully completed at 08:44: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_xFCh" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_xFCh": 
   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_xFCh" successfully completed at 08:44:41
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 /u01/oinsdir/GG2/datafile/o1_mf_temp_8zg30g54_.tmp deleted
auxiliary instance file /u01/oinsdir/GG2/onlinelog/o1_mf_3_8zg30bkv_.log deleted
auxiliary instance file /u01/oinsdir/GG2/onlinelog/o1_mf_2_8zg306gv_.log deleted
auxiliary instance file /u01/oinsdir/GG2/onlinelog/o1_mf_1_8zg303hj_.log deleted
auxiliary instance file /u01/oinsdir/GG2/datafile/o1_mf_sysaux_8zg2wfr8_.dbf deleted
auxiliary instance file /u01/oinsdir/GG2/datafile/o1_mf_undotbs1_8zg2wftc_.dbf deleted
auxiliary instance file /u01/oinsdir/GG2/datafile/o1_mf_system_8zg2wfqz_.dbf deleted
auxiliary instance file /u01/oinsdir/GG2/controlfile/o1_mf_8zg2w727_.ctl deleted
Finished recover at 30-JUL-13

---

dex@GG2> alter tablespace test online ;

Tablespace altered.

dex@GG2> select * from tab ;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
F                              TABLE
T                              TABLE

dex@GG2> select count(*) from t ;

  COUNT(*)
----------
     10000

大功告成