主要用于恢复人为的错误
需要有之前的backup。
适用场景(1)恢复错误的dml语句 (2)恢复错误的ddl语句，比如说更改了表结构，这个时候无法使用flashback table (3)恢复drop+purge的表 (4)恢复逻辑错误的表 (5)恢复被删除的表空间（rman可以在被drop的表空间上面执行TSPITR）
RMAN TSPITR的参数
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 :
createorreplaceviewts_pitr_objects_to_be_dropped (owner,name, creation_time, tablespace_name) as (selectu.name,o.name,o.ctime,tablespace_name fromuser$ u, obj$ o, dba_segments s whereu.user# = o.owner# ando.name = s.segment_name andu.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 tspitrtbsUNTIL 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/_07_30/o1_mf_s_822111655_8zfmo7vh_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/apps/oracle/fast_recovery_area/GG1/autobackup/_07_30/o1_mf_s_822111655_8zfmo7vh_.bkp tag=TAG0730T042055
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/_07_30/o1_mf_nnndf_TAG0730T041810_8zfmj327_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/apps/oracle/fast_recovery_area/GG1/backupset/_07_30/o1_mf_nnndf_TAG0730T041810_8zfmj327_.bkp tag=TAG0730T041810
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/_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/_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/_07_30/o1_mf_1_23_8zfmp8y8_.arc
archived log file name=/u01/apps/oracle/fast_recovery_area/GG1/archivelog/_07_30/o1_mf_1_21_8zfmp4b4_.arc thread=1 sequence=21
archived log file name=/u01/apps/oracle/fast_recovery_area/GG1/archivelog/_07_30/o1_mf_1_22_8zfmp5d6_.arc thread=1 sequence=22
archived log file name=/u01/apps/oracle/fast_recovery_area/GG1/archivelog/_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
--------------
0730191955
test@ORCL>
test@ORCL> @scn
CURRENT_SCN
-----------
922
test@ORCL> drop table user_info ;
Table dropped.
drop tablespaceIOTEXTBSincluding 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
--------------
0730192040
test@ORCL> @scn
CURRENT_SCN
-----------
308
这里可以使用logmnr来查找删除表时候的scn。
RECOVER TABLESPACE "TEST"UNTIL SCN 922AUXILIARY DESTINATION '/u01/apps/oracle/aux_dir' ;
test@ORCL> ALTER TABLESPACE TEST ONLINE ;
Tablespace altered.
test@ORCL> SELECT COUNT(*) FROM USER_INFO ;
COUNT(*)
----------
13
相当有用的嘛
example 3same 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/_07_30/o1_mf_nnndf_TAG0730T083039_8zg29jq2_.bkp tag=TAG0730T083039 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/_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 ;@scndrop 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
--------------
0730083432
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
--------------
0730083456
dex@GG2> @scn
CURRENT_SCN
-----------
1177599
ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDDHH24MISS' ; COL NAME FOR A70
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/_07_28/o1_mf_1 0605232035 0728213619
_1_8zb7l6yo_.arc
/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/_07_29/o1_mf_1 0728213605 0729140045
_2_8zd18bff_.arc
/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/_07_29/o1_mf_1 0729140042 072926
_3_8zdxhbyj_.arc
/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/_07_30/o1_mf_1 072926 0730075224
_4_8zg01p0c_.arc
/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/_07_30/o1_mf_1 0730075221 0730080715
_5_8zg0xmbt_.arc
/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/_07_30/o1_mf_1 0730080715 0730080716
_6_8zg0xnp4_.arc
/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/_07_30/o1_mf_1 0730080716 0730080723
_7_8zg0xv14_.arc
/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/_07_30/o1_mf_1 0730080722 0730081618
_8_8zg1glck_.arc
/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/_07_30/o1_mf_1 0730081618 073008
_9_8zg1ot87_.arc
/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/_07_30/o1_mf_1 073008 0730083416
_10_8zg2j7y7_.arc
/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/_07_30/o1_mf_1 0730083415 0730083416
_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 073008 1177522 0730083415
2 1 11 52428800 512 1 YES ACTIVE 1177522 0730083415 1177526 0730083416
3 1 12 52428800 512 1 NO CURRENT 1177526 0730083416 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.
selectt.scn,t.sql_redofromsys.t twhereupper(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 1AUXILIARY DESTINATION '/u01/oinsdir' ;recover tablespace "test" until time "to_date('','yyyymmddhh24miss')"AUXILIARY DESTINATION '/u01/oinsdir' ;recover tablespace "test" until scn1177542AUXILIARY DESTINATION '/u01/oinsdir' ;recover tablespace "test"UNTIL LOGSEQ 5 THREAD 1AUXILIARY DESTINATION '/u01/oinsdir' ;）
recover tablespace"TEST"until scn1177542AUXILIARY 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/_07_30/o1_mf_s_822126765_8zg2ffo6_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/apps/oracle/fast_recovery_areas/GG2/autobackup/_07_30/o1_mf_s_822126765_8zg2ffo6_.bkp tag=TAG0730T083245
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/_07_30/o1_mf_nnndf_TAG0730T083039_8zg29jq2_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/apps/oracle/fast_recovery_areas/GG2/backupset/_07_30/o1_mf_nnndf_TAG0730T083039_8zg29jq2_.bkp tag=TAG0730T083039
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/_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/_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/_07_30/o1_mf_1_12_8zg2s1gy_.arc
archived log file name=/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/_07_30/o1_mf_1_10_8zg2j7y7_.arc thread=1 sequence=10
archived log file name=/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/_07_30/o1_mf_1_11_8zg2j8g2_.arc thread=1 sequence=11
archived log file name=/u01/apps/oracle/fast_recovery_areas/GG2/archivelog/_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
大功告成
