12c 全自动执行基于时间点的表空间恢复(TSPITR)

windjack

12c 全自动执行基于时间点的表空间恢复(TSPITR)
-----------------------------------------------------------------
环境：
DBMS:oracle 12.1.0.2
OS:Linux7.8

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


测试步骤如下：



--创建测试表空间

SQL>create tablespace tbs_test datafile '/u01/app/oracle/oradata/sztech/SZTECH/datafile/tbs_test01.dbf' size 10m;
--创建测试表
SQL>create table hr.emp tablespace tbs_test as select * from hr.employees;
--验证记录
SQL> select count(*) from hr.emp;
  COUNT(*)
----------
       107

--执行RMAN备份

RMAN TARGET /
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;


--查询当前时间对应的SCN
SQL> select timestamp_to_scn(sysdate) from dual;
TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
                  3325522

--截断表记录
SQL> truncate table hr.emp;
Table truncated.

--再验证记录
SQL> select count(*) from hr.emp;
  COUNT(*)
----------
       0

--这时候在RMAN中通过基于时间点恢复表空间

RMAN> recover tablespace tbs_test until scn 3325522
2> auxiliary destination '/home/oracle';

Starting recover at 31-JUL-20
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='mulf'

initialization parameters used for automatic instance:
db_name=SZTECH
db_unique_name=mulf_pitr_SZTECH
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=768M
processes=200
db_create_file_dest=/home/oracle
log_archive_dest_1='location=/home/oracle'
#No auxiliary parameter file used


starting up automatic instance SZTECH

Oracle instance started

Total System Global Area     805306368 bytes

Fixed Size                     2929552 bytes
Variable Size                222301296 bytes
Database Buffers             574619648 bytes
Redo Buffers                   5455872 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  scn 3325522;
# 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 31-JUL-20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/SZTECH/backupset/2020_07_31/o1_mf_ncsnf_TAG20200731T100756_hl6z8fk8_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/SZTECH/backupset/2020_07_31/o1_mf_ncsnf_TAG20200731T100756_hl6z8fk8_.bkp tag=TAG20200731T100756
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/SZTECH/controlfile/o1_mf_hl6zl662_.ctl
Finished restore at 31-JUL-20

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 3325522;
plsql <<<--
declare
  sqlstatement       varchar2(512);
  pdbname            varchar2(30);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
   pdbname := null; -- pdbname
  sqlstatement := 'alter tablespace '||  'tbs_test' ||' 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  4 to new;
set newname for clone datafile  3 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  2 to
"/u01/app/oracle/oradata/sztech/SZTECH/datafile/tbs_test01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 4, 3, 2;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace tbs_test offline immediate

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /home/oracle/SZTECH/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 31-JUL-20
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 /home/oracle/SZTECH/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /home/oracle/SZTECH/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /home/oracle/SZTECH/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/sztech/SZTECH/datafile/tbs_test01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/SZTECH/backupset/2020_07_31/o1_mf_nnndf_TAG20200731T100756_hl6z7xdv_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/SZTECH/backupset/2020_07_31/o1_mf_nnndf_TAG20200731T100756_hl6z7xdv_.bkp tag=TAG20200731T100756
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 31-JUL-20

datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=1047204818 file name=/home/oracle/SZTECH/datafile/o1_mf_system_hl6zlcl3_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=1047204818 file name=/home/oracle/SZTECH/datafile/o1_mf_undotbs1_hl6zlcl5_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=1047204818 file name=/home/oracle/SZTECH/datafile/o1_mf_sysaux_hl6zlcl4_.dbf

contents of Memory Script:
{
# set requested point in time
set until  scn 3325522;
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  4 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
# recover and open resetlogs
recover clone database tablespace  "tbs_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  4 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  2 online

Starting recover at 31-JUL-20
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/fast_recovery_area/SZTECH/archivelog/2020_07_31/o1_mf_1_8_hl6z8gn2_.arc
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/fast_recovery_area/SZTECH/archivelog/2020_07_31/o1_mf_1_9_hl6z9kvv_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/SZTECH/archivelog/2020_07_31/o1_mf_1_8_hl6z8gn2_.arc thread=1 sequence=8
archived log file name=/u01/app/oracle/fast_recovery_area/SZTECH/archivelog/2020_07_31/o1_mf_1_9_hl6z9kvv_.arc thread=1 sequence=9
media recovery complete, elapsed time: 00:00:00
Finished recover at 31-JUL-20

database opened

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

sql statement: alter tablespace  tbs_test read only

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

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

Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_mulf_fAvi":  
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TSPITR_EXP_mulf_fAvi" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_mulf_fAvi is:
   EXPDP>   /home/oracle/tspitr_mulf_24306.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace tbs_test:
   EXPDP>   /u01/app/oracle/oradata/sztech/SZTECH/datafile/tbs_test01.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_mulf_fAvi" successfully completed at Fri Jul 31 10:14:33 2020 elapsed 0 00:00:49
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
# drop target tablespaces before importing them back
sql 'drop tablespace  tbs_test including contents keep datafiles cascade constraints';
}
executing Memory Script

Oracle instance shut down

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

Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_mulf_cmrk" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_mulf_cmrk":  
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_mulf_cmrk" successfully completed at Fri Jul 31 10:14:51 2020 elapsed 0 00:00:12
Import completed


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

sql statement: alter tablespace  tbs_test read write

sql statement: alter tablespace  tbs_test offline

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

Removing automatic instance
Automatic instance removed
auxiliary instance file /home/oracle/SZTECH/datafile/o1_mf_temp_hl6zlmqs_.tmp deleted
auxiliary instance file /home/oracle/SZTECH/onlinelog/o1_mf_3_hl6zlmjq_.log deleted
auxiliary instance file /home/oracle/SZTECH/onlinelog/o1_mf_2_hl6zlmgm_.log deleted
auxiliary instance file /home/oracle/SZTECH/onlinelog/o1_mf_1_hl6zlmcy_.log deleted
auxiliary instance file /home/oracle/SZTECH/datafile/o1_mf_sysaux_hl6zlcl4_.dbf deleted
auxiliary instance file /home/oracle/SZTECH/datafile/o1_mf_undotbs1_hl6zlcl5_.dbf deleted
auxiliary instance file /home/oracle/SZTECH/datafile/o1_mf_system_hl6zlcl3_.dbf deleted
auxiliary instance file /home/oracle/SZTECH/controlfile/o1_mf_hl6zl662_.ctl deleted
auxiliary instance file tspitr_mulf_24306.dmp deleted
Finished recover at 31-JUL-20

--恢复完成后，查看表空间状态

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
TBS_IDX                        OFFLINE

--修改表空间online
SQL> alter tablespace tbs_idx online;
Tablespace altered.

--验证表的记录，恢复成功
SQL> select count(*) from hr.emp;

  COUNT(*)
----------
       107


此种方法基于时间点恢复表空间是非常简便的。
但请注意，如果待恢复表空间中的对象和其它表空间对象存在依赖关系时，
在恢复时会报错，请在恢复前删除相关的依赖关系，要么就一起恢复。


附：
RMAN 执行表空间恢复的步骤：
1.    Creates the auxiliary instance, starts it, and connects to it
2.    Takes the tablespaces that will be recovered offline
3.    Restores a backup control file from a point in time before the target time to the auxiliary instance
4.    Restores the data files from the recovery set and the auxiliary set to the auxiliary instance
5.    Recovers the restored data files to the specified time
6.    Opens the auxiliary database with the RESETLOGS option

7.    Exports the dictionary metadata about objects in the recovered tablespaces to the target database
8.    Shuts down the auxiliary instance. (Issues SWITCH commands on the target database so that the target database control file points to the data files in the recovery set that were recovered on the auxiliary instance)
9.    Imports the dictionary metadata from the auxiliary instance to the target instance
10.    Deletes all auxiliary set files