前言
最近在学习备份恢复，12c新出的recover table是将rman恢复推到一个很小的粒度，但是这里介绍的TSPITR，它是以表空间为粒度的，不完全恢复的形式来将表空间恢复到过去某个特定的时间点（or scn）的一种恢复方式。
注意事项
要实现表空间的时点（TSPITR）恢复有以下前提，同时也存在一些限制：
1.存在有效可用的备份
2.数据库处于归档模式(且归档要保存完整)
3.表空间上存在约束关系(依赖)表的情形，依赖关系所在的表空间也需要一同做时点恢复(如外键参照，不在同一时点，则违反参照约束)
4.对于索引与数据分离的表空间在时点恢复时，应先删除索引
5.不能恢复数据库当前的缺省表空间
6.不能恢复以下对象：
存在依赖关系的物化视图，分区表等(如果要恢复，先解决依赖)
undo表空间，undo段
sys下的对象(如PL/SQL，views, synonyms, users…)
开始测试
准备环境
SYS@rac1> create tablespace test datafile size 10M autoextend on;
Tablespace created.
SYS@rac1> create user zyh identified by zyh default tablespace test;
User created.
SYS@rac1> grant dba to zyh;
Grant succeeded.
准备数据
SYS@rac1>create table zyh.test as select * from dba_objects;
Table created.
SYS@rac1>insert into zyh.test select * from dba_objects;
86691 rows created.
SYS@rac1>create table zyh.test2 as select * from dba_objects;
Table created.
SYS@rac1>select count(*) from zyh.test;
COUNT(*)
----------
173382
SYS@rac1>select count(*) from zyh.test2;
COUNT(*)
----------
86692
查看当前scn号
SYS@rac1>alter system checkpoint;
System altered.
SYS@rac1>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SYS@rac1>select CURRENT_SCN,CHECKPOINT_CHANGE#,sysdate from v$database;
CURRENT_SCN CHECKPOINT_CHANGE# SYSDATE
----------- ------------------ -------------------
6872692 6872608 2019-11-24 00:11:16
删除测试表
SYS@rac1>drop table zyh.test purge;
Table dropped.
SYS@rac1>truncate table zyh.test2;
Table truncated.
检查是否满足tspitr
SYS@rac1> exec sys.dbms_tts.transport_set_check('TEST',TRUE);
PL/SQL procedure successfully completed.
SYS@rac1>select * from transport_set_violations;
no rows selected
如果sys.dbms_tts.transport_set_check检查出有不满足tts的条件，它会将不满足的条件打印在transport_set_violations表中（比如外键约束等等）。然后就需要对不满足要求的条件进行处理。
执行基于scn的表空间恢复
命令如下：
recover tablespace “TEST” until scn 6872692 auxiliary destination ‘/u01/backup’;
需要实现创建好/u01/backup/的目录
RMAN> recover tablespace "TEST" until scn 6872692 auxiliary destination '/u01/backup';
Starting recover at 24-NOV-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=64 instance=rac1 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Tablespace UNDOTBS2NEW
Creating automatic instance, with SID='fExp' --自动创建一个辅助实例
--以下是辅助实例fExp的初始化参数
initialization parameters used for automatic instance:
db_name=RAC11G
db_unique_name=fExp_tspitr_RAC11G
compatible=11.2.0.4.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
db_create_file_dest=/u01/backup
log_archive_dest_1='location=/u01/backup'
#No auxiliary parameter file used
--启动辅助实例
starting up automatic instance RAC11G
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 281019272 bytes
Database Buffers 780140544 bytes
Redo Buffers 5517312 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 6872692;
# 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 24-NOV-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=25 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 +FRA/rac11g/backupset/2019_11_23/ncsnf0_tag20191123t233256_0.278.1025134403
channel ORA_AUX_DISK_1: piece handle=+FRA/rac11g/backupset/2019_11_23/ncsnf0_tag20191123t233256_0.278.1025134403 tag=TAG20191123T233256
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output file name=/u01/backup/RAC11G/controlfile/o1_mf_gxlpnbw4_.ctl
Finished restore at 24-NOV-19
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
--设置不完全恢复的scn（时间点），并还原必需的数据文件
contents of Memory Script:
{
# set requested point in time
set until scn 6872692;
plsql <<<-- tspitr_2
declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed, -01539);
begin
sqlstatement := 'alter tablespace '|| '"TEST"' ||' offline immediate';
krmicd.writeMsg(6162, sqlstatement);
krmicd.execSql(sqlstatement);
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 3 to new;
set newname for clone datafile 8 to new;
set newname for clone datafile 2 to new;
set newname for clone tempfile 1 to new;
set newname for datafile 6 to
"+DATA/rac11g/datafile/test.268.1016721713";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 3, 8, 2, 6;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
sql statement: alter tablespace "TEST" 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
renamed tempfile 1 to /u01/backup/RAC11G/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 24-NOV-19
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 00006 to +DATA/rac11g/datafile/test.268.1016721713
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/35uhkds6_1_1.bak
channel ORA_AUX_DISK_1: errors found reading piece handle=/u01/backup/35uhkds6_1_1.bak
channel ORA_AUX_DISK_1: failover to piece handle=/u01/35uhkds6_1_1.bak tag=TAG20191123T215206
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 00001 to /u01/backup/RAC11G/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/backup/RAC11G/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/backup/RAC11G/datafile/o1_mf_undotbs2_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/backup/RAC11G/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +FRA/rac11g/backupset/2019_11_23/nnndf0_tag20191123t233256_0.385.1025134377
channel ORA_AUX_DISK_1: piece handle=+FRA/rac11g/backupset/2019_11_23/nnndf0_tag20191123t233256_0.385.1025134377 tag=TAG20191123T233256
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 24-NOV-19
datafile 1 switched to datafile copy
input datafile copy RECID=20 STAMP=1025136829 file name=/u01/backup/RAC11G/datafile/o1_mf_system_gxlpnthv_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=21 STAMP=1025136829 file name=/u01/backup/RAC11G/datafile/o1_mf_undotbs1_gxlpntj0_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=22 STAMP=1025136829 file name=/u01/backup/RAC11G/datafile/o1_mf_undotbs2_gxlpntht_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=23 STAMP=1025136829 file name=/u01/backup/RAC11G/datafile/o1_mf_sysaux_gxlpnthx_.dbf
--将辅助实例相关数据文件联机，做介质恢复，最后以resetlogs方式打开数据库
contents of Memory Script:
{
# set requested point in time
set until scn 6872692;
# 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 8 online";
sql clone "alter database datafile 2 online";
sql clone "alter database datafile 6 online";
# recover and open resetlogs
recover clone database tablespace "TEST", "SYSTEM", "UNDOTBS1", "UNDOTBS2NEW", "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 8 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 6 online
Starting recover at 24-NOV-19
using channel ORA_AUX_DISK_1
--应用归档日志
starting media recovery
archived log for thread 1 with sequence 321 is already on disk as file +FRA/rac11g/archivelog/2019_11_23/thread_1_seq_321.773.1025133675
archived log for thread 1 with sequence 322 is already on disk as file +FRA/rac11g/archivelog/2019_11_23/thread_1_seq_322.774.1025133903
archived log for thread 1 with sequence 323 is already on disk as file +FRA/rac11g/archivelog/2019_11_23/thread_1_seq_323.776.1025134333
archived log for thread 1 with sequence 324 is already on disk as file +FRA/rac11g/archivelog/2019_11_23/thread_1_seq_324.280.1025134405
archived log for thread 1 with sequence 325 is already on disk as file +FRA/rac11g/archivelog/2019_11_23/thread_1_seq_325.785.1025134805
archived log for thread 1 with sequence 326 is already on disk as file +FRA/rac11g/archivelog/2019_11_23/thread_1_seq_326.783.1025134971
archived log for thread 1 with sequence 327 is already on disk as file +FRA/rac11g/archivelog/2019_11_23/thread_1_seq_327.782.1025134971
archived log for thread 1 with sequence 328 is already on disk as file +FRA/rac11g/archivelog/2019_11_24/thread_1_seq_328.778.1025136237
archived log for thread 1 with sequence 329 is already on disk as file +FRA/rac11g/archivelog/2019_11_24/thread_1_seq_329.648.1025136293
archived log for thread 1 with sequence 330 is already on disk as file +FRA/rac11g/archivelog/2019_11_24/thread_1_seq_330.647.1025136295
archived log for thread 1 with sequence 331 is already on disk as file +FRA/rac11g/archivelog/2019_11_24/thread_1_seq_331.646.1025136785
archived log for thread 2 with sequence 286 is already on disk as file +FRA/rac11g/archivelog/2019_11_23/thread_2_seq_286.772.1025133673
archived log for thread 2 with sequence 287 is already on disk as file +FRA/rac11g/archivelog/2019_11_23/thread_2_seq_287.775.1025133905
archived log for thread 2 with sequence 288 is already on disk as file +FRA/rac11g/archivelog/2019_11_23/thread_2_seq_288.777.1025134333
archived log for thread 2 with sequence 289 is already on disk as file +FRA/rac11g/archivelog/2019_11_23/thread_2_seq_289.281.1025134405
archived log for thread 2 with sequence 290 is already on disk as file +FRA/rac11g/archivelog/2019_11_23/thread_2_seq_290.784.1025134805
archived log for thread 2 with sequence 291 is already on disk as file +FRA/rac11g/archivelog/2019_11_24/thread_2_seq_291.781.1025136069
archived log for thread 2 with sequence 292 is already on disk as file +FRA/rac11g/archivelog/2019_11_24/thread_2_seq_292.780.1025136233
archived log for thread 2 with sequence 293 is already on disk as file +FRA/rac11g/archivelog/2019_11_24/thread_2_seq_293.779.1025136235
archived log for thread 2 with sequence 294 is already on disk as file +FRA/rac11g/archivelog/2019_11_24/thread_2_seq_294.644.1025136787
archived log file name=+FRA/rac11g/archivelog/2019_11_23/thread_1_seq_321.773.1025133675 thread=1 sequence=321
archived log file name=+FRA/rac11g/archivelog/2019_11_23/thread_2_seq_286.772.1025133673 thread=2 sequence=286
archived log file name=+FRA/rac11g/archivelog/2019_11_23/thread_1_seq_322.774.1025133903 thread=1 sequence=322
archived log file name=+FRA/rac11g/archivelog/2019_11_23/thread_2_seq_287.775.1025133905 thread=2 sequence=287
archived log file name=+FRA/rac11g/archivelog/2019_11_23/thread_1_seq_323.776.1025134333 thread=1 sequence=323
archived log file name=+FRA/rac11g/archivelog/2019_11_23/thread_2_seq_288.777.1025134333 thread=2 sequence=288
archived log file name=+FRA/rac11g/archivelog/2019_11_23/thread_1_seq_324.280.1025134405 thread=1 sequence=324
archived log file name=+FRA/rac11g/archivelog/2019_11_23/thread_2_seq_289.281.1025134405 thread=2 sequence=289
archived log file name=+FRA/rac11g/archivelog/2019_11_23/thread_1_seq_325.785.1025134805 thread=1 sequence=325
archived log file name=+FRA/rac11g/archivelog/2019_11_23/thread_2_seq_290.784.1025134805 thread=2 sequence=290
archived log file name=+FRA/rac11g/archivelog/2019_11_23/thread_1_seq_326.783.1025134971 thread=1 sequence=326
archived log file name=+FRA/rac11g/archivelog/2019_11_24/thread_2_seq_291.781.1025136069 thread=2 sequence=291
archived log file name=+FRA/rac11g/archivelog/2019_11_23/thread_1_seq_327.782.1025134971 thread=1 sequence=327
archived log file name=+FRA/rac11g/archivelog/2019_11_24/thread_2_seq_292.780.1025136233 thread=2 sequence=292
archived log file name=+FRA/rac11g/archivelog/2019_11_24/thread_1_seq_328.778.1025136237 thread=1 sequence=328
archived log file name=+FRA/rac11g/archivelog/2019_11_24/thread_2_seq_293.779.1025136235 thread=2 sequence=293
archived log file name=+FRA/rac11g/archivelog/2019_11_24/thread_1_seq_329.648.1025136293 thread=1 sequence=329
archived log file name=+FRA/rac11g/archivelog/2019_11_24/thread_1_seq_330.647.1025136295 thread=1 sequence=330
archived log file name=+FRA/rac11g/archivelog/2019_11_24/thread_2_seq_294.644.1025136787 thread=2 sequence=294
archived log file name=+FRA/rac11g/archivelog/2019_11_24/thread_1_seq_331.646.1025136785 thread=1 sequence=331
media recovery complete, elapsed time: 00:00:01
Finished recover at 24-NOV-19
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/backup''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/backup''";
}
executing Memory Script
sql statement: alter tablespace "TEST" read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/backup''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/backup''
--基于上面创建的目录实现表空间传输导出到dump文件
Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_fExp":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/COMMENT
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table "SYS"."TSPITR_EXP_fExp" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_fExp is:
EXPDP> /u01/backup/tspitr_fExp_98948.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace TEST:
EXPDP> +DATA/rac11g/datafile/test.268.1016721713
EXPDP> Job "SYS"."TSPITR_EXP_fExp" successfully completed at Sun Nov 24 00:14:23 2019 elapsed 0 00:00:24
Export completed
--删除目标数据库源表空间
contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
# drop target tablespaces before importing them back
sql 'drop tablespace "TEST" including contents keep datafiles cascade constraints';
}
executing Memory Script
database closed
database dismounted
Oracle instance shut down
sql statement: drop tablespace "TEST" including contents keep datafiles cascade constraints
--导入表空间
Performing import of metadata...
IMPDP> Master table "SYS"."TSPITR_IMP_fExp" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_fExp":
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
IMPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
IMPDP> Processing object type TRANSPORTABLE_EXPORT/COMMENT
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job "SYS"."TSPITR_IMP_fExp" successfully completed at Sun Nov 24 00:14:54 2019 elapsed 0 00:00:12
Import completed
--将表空间联机然后迅速offline
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/backup/RAC11G/datafile/o1_mf_temp_gxlpp1vr_.tmp deleted
auxiliary instance file /u01/backup/RAC11G/onlinelog/o1_mf_6_gxlpp1h0_.log deleted
auxiliary instance file /u01/backup/RAC11G/onlinelog/o1_mf_4_gxlpp16q_.log deleted
auxiliary instance file /u01/backup/RAC11G/onlinelog/o1_mf_3_gxlpp119_.log deleted
auxiliary instance file /u01/backup/RAC11G/onlinelog/o1_mf_5_gxlpp1bx_.log deleted
auxiliary instance file /u01/backup/RAC11G/onlinelog/o1_mf_2_gxlpp0pc_.log deleted
auxiliary instance file /u01/backup/RAC11G/onlinelog/o1_mf_1_gxlpp05g_.log deleted
auxiliary instance file /u01/backup/RAC11G/datafile/o1_mf_sysaux_gxlpnthx_.dbf deleted
auxiliary instance file /u01/backup/RAC11G/datafile/o1_mf_undotbs2_gxlpntht_.dbf deleted
auxiliary instance file /u01/backup/RAC11G/datafile/o1_mf_undotbs1_gxlpntj0_.dbf deleted
auxiliary instance file /u01/backup/RAC11G/datafile/o1_mf_system_gxlpnthv_.dbf deleted
auxiliary instance file /u01/backup/RAC11G/controlfile/o1_mf_gxlpnbw4_.ctl deleted
Finished recover at 24-NOV-19
验证
SYS@rac1>alter tablespace test online;
Tablespace altered.
SYS@rac1>select count(*) from zyh.test;
COUNT(*)
----------
173382
SYS@rac1>select count(*) from zyh.test2;
COUNT(*)
----------
86692
至此已经成功基于scn恢复了test表空间。同理基于时间点until time “to_date( ‘2019-11-24 00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’)”；
注意：
使用tspitr恢复之后的表空间是无法使用之前的备份恢复的，建议在做完tspitr之后对数据库或者表空间做一次备份。
扫一扫
2347
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
