Oracle-表空间基于时间点恢复(TSPITR)

前言:

Oracle在10G推出了tablespace point-in-time recovery(TSPITR)基于表空间的时间点恢复，可以快速的在目标库上根据备份恢复一个或多个表空间到指定的时间点，恢复期间不影响其他表空间的使用以及对象，可以快速的恢复误删数据。

TSPITR工作原理：

The figure contains the following entities:
• The target instance, containing the tablespace to be recovered
• The Recovery Manager client
• The control file and (optional) recovery catalog, used for the RMAN repository records of backup activity
• Archived redo logs and backup sets from the target database, which are the source of the reconstructed tablespace.
• The auxiliary instance, an Oracle database instance used in the recovery process to perform the actual work of recovery.
There are four other important terms related to TSPITR, which will be used in the rest of this discussion:
• The target time, the point in time or SCN that the tablespace will be left at after TSPITR
• The recovery set, which consists of the datafiles containing the tablespaces to be recovered;
• The auxiliary set, which includes datafiles required for TSPITR of the recovery set which are not themselves part of the recovery set. The auxiliary set typically includes:
○ A copy of the SYSTEM tablespace
○ Datafiles containing rollback or undo segments from the target instance
○ In some cases, a temporary tablespace, used during the export of database objects from the auxiliary instance
The auxiliary instance has other files associated with it, such as a control file, parameter file, and online logs, but they are not part of the auxiliary set.
• The auxiliary destination, an optional location on disk which can be used to store any of the auxiliary set datafiles, control files and online logs of the auxiliary instance during TSPITR. Files stored here can be deleted after TSPITR is complete.
All of these terms will be referenced throughout the remainder of this chapter.
How TSPITR Works With an RMAN-Managed Auxiliary Instance
To perform TSPITR of the recovery set using RMAN and an automated auxiliary instance, you carry out the preparations for TSPITR described in "Planning and Preparing for TSPITR", and then issue the RECOVER TABLESPACE command, specifying, at a minimum, the tablespaces of the recovery set and the target time for the point-in-time recovery, and, if desired, an auxiliary destination as well.
RMAN then carries out the following steps:
If there is no connection to an auxiliary instance, RMAN creates the auxiliary instance, starts it up and connects to it.
Takes the tablespaces to be recovered offline in the target database
Restores a backup control file from a point in time before the target time to the auxiliary instance
Restores the datafiles from the recovery set and the auxiliary set to the auxiliary instance. Files are restored either in locations you specify for each file, or the original location of the file (for recovery set files) or in the auxiliary destination (for auxiliary set files, if you used the AUXILIARY DESTINATION argument of RECOVER TABLESPACE)
Recovers the restored datafiles in the auxiliary instance to the specified time
Opens the auxiliary database with the RESETLOGS option
Exports the dictionary metadata about objects in the recovered tablespaces to the target database
Shuts down the auxiliary instance
Issues SWITCH commands on the target instance, so that the target database control file now points to the datafiles in the recovery set that were just recovered at the auxiliary instance.
Imports the dictionary metadata from the auxiliary instance to the target instance, allowing the recovered objects to be accessed.
Deletes all auxiliary set files.
At that point the TSPITR process is complete. The recovery set datafiles are returned to their contents at the specified point in time, and belong to the target database.

1 通过创建一个辅助的实例

2 将需要point in time recovery(PITR)的目标数据库表空间recover offline

3 从备份里面依据PITR点恢复控制文件到辅助实例

4 从备份里面依据PITR点恢复system，undo表空间到 AUXILIARY DESTINATION 路径下，恢复PITR目标表空间到原始路径下

5 open resetlogs打开辅助实例

6 从恢复的表空间里面导出元数据

7 关闭辅助实例

8目标库执行切换命令，目标库的控制文件指向，以便目标数据库控制文件现在指向刚刚在辅助实例上恢复的恢复集中的数据文件。

9 导入元数据到目标实例。

10 删除所有辅助实例设置文件

​TSPITR使用限制：

 • Recovery Manager  Tablespace Point-in-Time Recovery (TSPITR) enables you to recover one or more tablespaces to a point in time that is different from that of the rest of the database.But there are a number of situations which you cannot resolve by using TSPITR.
They are as follows.
  • You cannot recover dropped tablespaces.
  • You cannot recover a renamed tablespace to a point in time before it was renamed. If you try to   perform a TSPITR to an SCN earlier than the rename operation, RMAN cannot find the new tablespace name in the repository as of that earlier SCN (because the tablespace did not have that name at that SCN). In this situation, you must recover the entire database to a point in time before the tablespace was renamed. The tablespace will be found under the name it had at that earlier time.
  • Recover a tablespace that has been dropped and re-created with the same name.
  • You cannot recover tables without their associated constraints, or constraints without the associated table
  • You cannot use TSPITR to recover any of the following:
      1. Replicated primary tables
      2. Partial tables (for example, if you perform RMAN TSPITR on partitioned tables and spread  partitions across multiple tablespaces, then you must recover all tablespaces which include partitions of the table.)
      3. Tables with VARRAY columns, nested tables, or external files
      4. Snapshot logs and snapshot tables
      5. Tablespaces containing undo or rollback segments
      6. Tablespaces that contain objects owned by SYS, including rollback segments
  • If a datafile was added after the point to which RMAN is recovering, an empty datafile by the same  name will be included   in the tablespace after RMAN TSPITR.
  •  TSPITR will not recover query optimizer statistics for recovered objects. You must gather new statistics after the  TSPITR.
  • Assume that you run TSPITR on a tablespace, and then bring the tablespace online at time t. Backups of the tablespace  created before time t are no longer usable for recovery with a current control file. You cannot run TSPITR again on this tablespace to recover it to any time less than or equal to time t, nor can you use the current control file to recover the database to any time less than or equal to t. Therefore, you must back up the tablespace as soon as TSPITR is complete.
Limitations of TSPITR Without a Recovery Catalog
====================================
  • The undo segments at the time of the TSPITR must be part of the auxiliary set. Because RMAN has no historical record of the undo in the control file, RMAN assumes that the current rollback or undo segments were the same segments present at the time to which recovery is performed. If the undo segments have changed since that time, then TSPITR will fail.
  • TSPITR to a time that is too old may not succeed if Oracle has reused the control file records for needed backups. (In planning your database, set the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter to a value large enough to ensure that control file records needed for TSPITR are kept.)
  • When not using a recovery catalog, the current control file has no record of the older incarnation of the recovered tablespace. Thus, recovery with a current control file that involves this tablespace can no longer use a backup taken prior to time t. You can, however, perform incomplete recovery of the whole database to any time less than or equal to t, if you can restore a backup control file from before time t.

TSPITR使用测试

模拟误删除场景

---在PDB ebsdb里面创建测试数据
alter session set container=ebsdb;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
create  tablespace tbstest datafile size 100M;
create table test(id number,insert_date date) tablespace tbstest;
insert into test values(1,sysdate);
commit;
​
---进行全量备份
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
backup as compressed backupset database format='/backup/%d_full_%s_%p.bak';
backup archivelog all format='/backup/%d_arch_%s_%p.bak' delete all input;
release channel c1;
release channel c2;
release channel c3;
}
---再插入数据
insert into test values(2,sysdate);
commit;
---模拟误删除数据truncate
alter session set container=ebsdb;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select sysdate from dual;
select * from test;
##########要恢复的时间点
SQL> select sysdate from dual;
​
SYSDATE
-------------------
2022-11-09 02:10:29
​
SQL> select * from test;
​
        ID INSERT_DATE
---------- -------------------
         1 2022-11-09 02:08:03
         2 2022-11-09 02:10:22
​
SQL> 
---truncate表数据
truncate table test;
---在备份归档
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
backup archivelog all format='/backup/%d_arch_%s_%p.bak' delete all input;
release channel c1;
release channel c2;
release channel c3;
}

开始通过TSPITR基于表空间的时间点恢复

由于恢复是直接在目标数据库的表空间上恢复，所以恢复的是表空间里面全部数据到指定的时间点，这种恢复在实际的生产环境中是不太可能的，因为我们往往只需要恢复某一张表，而不是将整个表空间里面的数据恢复到某个时间点

所以将恢复的目标数据库放在备库是比较合理的，接下来的恢复操作，我们也是模拟在误删数据库的备库进行操作

---关闭主库的mrp进程
alter database recover managed standby database cancel;
---failover激活备库，因为恢复需要在open write模式下进行,并且控制文件的类型不能为standby control,不能创建检查点
#RMAN-05010: target database must be opened in READ WRITE mode for Tablespace Point-in-Time Recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
alter database open;
alter pluggable database all open instances=all;
​
---将备份发生到备库主机
scp -rp /backup oracle@xxxxx:/
---并注册备份到控制文件，确保数据库可以访问到备份文件
rman target /
crosscheck backup
catalog start with '/backup'
​
---在备库执行TSPITR恢复命令,恢复EBSDB PDB里面的表空间tbstest
---AUXILIARY DESTINATION 
rman target /
RECOVER TABLESPACE ebsdb:tbstest until time "to_date('11/09/2022 02:10:29','MM/DD/YYYY HH24:MI:SS')"  AUXILIARY DESTINATION '/u01/app/oracle/auxinstance';

TSPITR恢复日志分析

####################创建辅助实例
​
Creating automatic instance, with SID='Csgj'
​
initialization parameters used for automatic instance:
db_name=RACPDB
db_unique_name=Csgj_pitr_ebsdb_RACPDB
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=2320M
processes=200
db_create_file_dest=/u01/app/oracle/auxinstance
log_archive_dest_1='location=/u01/app/oracle/auxinstance'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
​
​
starting up automatic instance RACPDB
​
Oracle instance started
​
Total System Global Area    2432695632 bytes
​
Fixed Size                     9137488 bytes
Variable Size                536870912 bytes
Database Buffers            1879048192 bytes
Redo Buffers                   7639040 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully
####################从备份恢复控制文件
contents of Memory Script:
{
# set requested point in time
set until  time "to_date('11/09/2022 02:10:29','MM/DD/YYYY HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;
 
# mount the controlfile
sql clone 'alter database mount clone database';
 
# archive current online log 
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script
​
####################指定时间点restore,system,sysaux,undo到指定的auxiliray路径下，恢复的PITR表空间会到原路径下
contents of Memory Script:
{
# set requested point in time
set until  time "to_date('11/09/2022 02:10:29','MM/DD/YYYY HH24:MI:SS')";
plsql <<<--
declare
  sqlstatement       varchar2(512);
  pdbname            varchar2(128);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
   pdbname := 'EBSDB'; -- pdbname
  sqlstatement := 'alter tablespace '||  'TBSTEST' ||' offline immediate';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement, 0, pdbname);
exception
  when offline_not_needed then
    null;
end; >>>;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  16 to new;
set newname for clone datafile  5 to new;
set newname for clone datafile  18 to new;
set newname for clone datafile  19 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  17 to new;
set newname for clone tempfile  1 to new;
set newname for clone tempfile  5 to new;
set newname for datafile  63 to 
 "/u01/app/oracle/oradata/RACPDG/ECDB7CD71FEB9A44E053DC01A8C0E647/datafile/o1_mf_tbstest_kpzcnwbw_.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 16, 5, 18, 19, 3, 17, 63;
 
switch clone datafile all;
}
executing Memory Script
​
executing command: SET until clause
​
sql statement: alter tablespace TBSTEST offline immediate
​
executing command: SET NEWNAME
​
executing command: SET NEWNAME
​
executing command: SET NEWNAME
​
executing command: SET NEWNAME
​
executing command: SET NEWNAME
​
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/app/oracle/auxinstance/RACPDG/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 5 to /u01/app/oracle/auxinstance/RACPDG/ECDB7CD71FEB9A44E053DC01A8C0E647/datafile/o1_mf_temp_%u_.tmp in control file
​
Starting restore at 2022/11/12 22:41:31
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/app/oracle/auxinstance/RACPDG/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/auxinstance/RACPDG/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /backup/backup/RACPDB_full_250_1.bak
channel ORA_AUX_DISK_1: piece handle=/backup/backup/RACPDB_full_250_1.bak tag=TAG20221109T020837
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
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 00016 to /u01/app/oracle/auxinstance/RACPDG/ECDB7CD71FEB9A44E053DC01A8C0E647/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /backup/backup/RACPDB_full_252_1.bak
channel ORA_AUX_DISK_1: piece handle=/backup/backup/RACPDB_full_252_1.bak tag=TAG20221109T020837
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
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 00005 to /u01/app/oracle/auxinstance/RACPDG/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /backup/backup/RACPDB_full_254_1.bak
channel ORA_AUX_DISK_1: piece handle=/backup/backup/RACPDB_full_254_1.bak tag=TAG20221109T020837
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
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 00018 to /u01/app/oracle/auxinstance/RACPDG/ECDB7CD71FEB9A44E053DC01A8C0E647/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /backup/backup/RACPDB_full_253_1.bak
channel ORA_AUX_DISK_1: piece handle=/backup/backup/RACPDB_full_253_1.bak tag=TAG20221109T020837
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
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 00019 to /u01/app/oracle/auxinstance/RACPDG/ECDB7CD71FEB9A44E053DC01A8C0E647/datafile/o1_mf_undo_2_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00017 to /u01/app/oracle/auxinstance/RACPDG/ECDB7CD71FEB9A44E053DC01A8C0E647/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00063 to /u01/app/oracle/oradata/RACPDG/ECDB7CD71FEB9A44E053DC01A8C0E647/datafile/o1_mf_tbstest_kpzcnwbw_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /backup/backup/RACPDB_full_251_1.bak
channel ORA_AUX_DISK_1: piece handle=/backup/backup/RACPDB_full_251_1.bak tag=TAG20221109T020837
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 2022/11/12 22:42:55
​
datafile 63 switched to datafile copy
input datafile copy RECID=9 STAMP=1120603375 file name=/u01/app/oracle/oradata/RACPDG/ECDB7CD71FEB9A44E053DC01A8C0E647/datafile/o1_mf_tbstest_kpzcnwbw_.dbf
datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=1120603375 file name=/u01/app/oracle/auxinstance/RACPDG/datafile/o1_mf_system_kpzd4w3c_.dbf
datafile 16 switched to datafile copy
input datafile copy RECID=11 STAMP=1120603375 file name=/u01/app/oracle/auxinstance/RACPDG/ECDB7CD71FEB9A44E053DC01A8C0E647/datafile/o1_mf_system_kpzd5o4k_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=1120603375 file name=/u01/app/oracle/auxinstance/RACPDG/datafile/o1_mf_undotbs1_kpzd6g5l_.dbf
datafile 18 switched to datafile copy
input datafile copy RECID=13 STAMP=1120603375 file name=/u01/app/oracle/auxinstance/RACPDG/ECDB7CD71FEB9A44E053DC01A8C0E647/datafile/o1_mf_undotbs1_kpzd6k6l_.dbf
datafile 19 switched to datafile copy
input datafile copy RECID=14 STAMP=1120603375 file name=/u01/app/oracle/auxinstance/RACPDG/ECDB7CD71FEB9A44E053DC01A8C0E647/datafile/o1_mf_undo_2_kpzd707w_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=1120603375 file name=/u01/app/oracle/auxinstance/RACPDG/datafile/o1_mf_sysaux_kpzd4w3r_.dbf
datafile 17 switched to datafile copy
input datafile copy RECID=16 STAMP=1120603375 file name=/u01/app/oracle/auxinstance/RACPDG/ECDB7CD71FEB9A44E053DC01A8C0E647/datafile/o1_mf_sysaux_kpzd707v_.dbf
​
contents of Memory Script:
{
# set requested point in time
set until  time "to_date('11/09/2022 02:10:29','MM/DD/YYYY HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone 'EBSDB' "alter database datafile 
 16 online";
sql clone "alter database datafile  5 online";
sql clone 'EBSDB' "alter database datafile 
 18 online";
sql clone 'EBSDB' "alter database datafile 
 19 online";
sql clone "alter database datafile  3 online";
sql clone 'EBSDB' "alter database datafile 
 17 online";
sql clone 'EBSDB' "alter database datafile 
 63 online";
# recover and open resetlogs
recover clone database tablespace  "EBSDB":"TBSTEST", "SYSTEM", "EBSDB":"SYSTEM", "UNDOTBS1", "EBSDB":"UNDOTBS1", "EBSDB":"UNDO_2", "SYSAUX", "EBSDB":"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  16 online
​
sql statement: alter database datafile  5 online
​
sql statement: alter database datafile  18 online
​
sql statement: alter database datafile  19 online
​
sql statement: alter database datafile  3 online
​
sql statement: alter database datafile  17 online
​
sql statement: alter database datafile  63 online
​
Starting recover at 2022/11/12 22:42:55
using channel ORA_AUX_DISK_1
​
starting media recovery
​
archived log for thread 1 with sequence 87 is already on disk as file /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_87_1120132767.dbf
archived log for thread 1 with sequence 88 is already on disk as file /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_88_1120132767.dbf
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_87_1120132767.dbf thread=1 sequence=87
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_88_1120132767.dbf thread=1 sequence=88
media recovery complete, elapsed time: 00:00:01
Finished recover at 2022/11/12 22:42:57
​
database opened
​
contents of Memory Script:
{
sql clone 'alter pluggable database  EBSDB open';
}
executing Memory Script
​
sql statement: alter pluggable database  EBSDB open
​
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'EBSDB' 'alter tablespace 
 TBSTEST read only';
# create directory for datapump import
sql 'EBSDB' "create or replace directory 
TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/auxinstance''";
# create directory for datapump export
sql clone 'EBSDB' "create or replace directory 
TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/auxinstance''";
}
executing Memory Script
​
sql statement: alter tablespace  TBSTEST read only
​
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/auxinstance''
​
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/auxinstance''
###############################################从辅助实例导出元数据
Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_Csgj_jizC":  
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Master table "SYS"."TSPITR_EXP_Csgj_jizC" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_Csgj_jizC is:
   EXPDP>   /u01/app/oracle/auxinstance/tspitr_Csgj_71324.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace TBSTEST:
   EXPDP>   /u01/app/oracle/oradata/RACPDG/ECDB7CD71FEB9A44E053DC01A8C0E647/datafile/o1_mf_tbstest_kpzcnwbw_.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_Csgj_jizC" successfully completed at Sat Nov 12 22:43:41 2022 elapsed 0 00:00:28
Export completed
​
​
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
# drop target tablespaces before importing them back
sql 'EBSDB' 'drop tablespace 
 TBSTEST including contents keep datafiles cascade constraints';
}
executing Memory Script
​
Oracle instance shut down
###############################################目标数据库删除原来的表空间
sql statement: drop tablespace  TBSTEST including contents keep datafiles cascade constraints
###############################################目标数据库导入元数据
Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_Csgj_uqxb" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_Csgj_uqxb":  
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_Csgj_uqxb" successfully completed at Sat Nov 12 22:43:53 2022 elapsed 0 00:00:03
Import completed
​
###############################################目标数据库offline恢复表空间
contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'EBSDB' 'alter tablespace 
 TBSTEST read write';
sql 'EBSDB' 'alter tablespace 
 TBSTEST offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
​
sql statement: alter tablespace  TBSTEST read write
​
sql statement: alter tablespace  TBSTEST offline
​
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
###############################################清理辅助实例
Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/auxinstance/RACPDG/ECDB7CD71FEB9A44E053DC01A8C0E647/datafile/o1_mf_temp_kpzd7pxy_.tmp deleted
auxiliary instance file /u01/app/oracle/auxinstance/RACPDG/datafile/o1_mf_temp_kpzd7nvx_.tmp deleted
auxiliary instance file /u01/app/oracle/auxinstance/RACPDG/onlinelog/o1_mf_3_kpzd7kpw_.log deleted
auxiliary instance file /u01/app/oracle/auxinstance/RACPDG/onlinelog/o1_mf_2_kpzd7knq_.log deleted
auxiliary instance file /u01/app/oracle/auxinstance/RACPDG/onlinelog/o1_mf_1_kpzd7kmd_.log deleted
auxiliary instance file /u01/app/oracle/auxinstance/RACPDG/ECDB7CD71FEB9A44E053DC01A8C0E647/datafile/o1_mf_sysaux_kpzd707v_.dbf deleted
auxiliary instance file /u01/app/oracle/auxinstance/RACPDG/datafile/o1_mf_sysaux_kpzd4w3r_.dbf deleted
auxiliary instance file /u01/app/oracle/auxinstance/RACPDG/ECDB7CD71FEB9A44E053DC01A8C0E647/datafile/o1_mf_undo_2_kpzd707w_.dbf deleted
auxiliary instance file /u01/app/oracle/auxinstance/RACPDG/ECDB7CD71FEB9A44E053DC01A8C0E647/datafile/o1_mf_undotbs1_kpzd6k6l_.dbf deleted
auxiliary instance file /u01/app/oracle/auxinstance/RACPDG/datafile/o1_mf_undotbs1_kpzd6g5l_.dbf deleted
auxiliary instance file /u01/app/oracle/auxinstance/RACPDG/ECDB7CD71FEB9A44E053DC01A8C0E647/datafile/o1_mf_system_kpzd5o4k_.dbf deleted
auxiliary instance file /u01/app/oracle/auxinstance/RACPDG/datafile/o1_mf_system_kpzd4w3c_.dbf deleted
auxiliary instance file /u01/app/oracle/auxinstance/RACPDG/controlfile/o1_mf_kpzd4p8l_.ctl deleted
auxiliary instance file tspitr_Csgj_71324.dmp deleted
Finished recover at 2022/11/12 22:43:55
​
RMAN> 

TSPITR恢复完成之后验证数据

---online表空间tbstest，验证数据
SQL> alter tablespace tbstest online;
​
Tablespace altered.
​
​---误删除的数据恢复到之前的时间点
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
​
Session altered.
​
SQL> select * from test;
​
        ID INSERT_DATE
---------- -------------------
         1 2022-11-09 02:08:03
         2 2022-11-09 02:10:22