在Oracle中，通常所有的表空间都要在同一个时间点上保持一致。但实际工作中，有时我们需要在同一个数据库中，把部分数据恢复到不同的时间点。这时就要用到RMAN的表空间时间点恢复功能。这里姚远老师给大家介绍一下这个功能，参考官方文档《Backup and Recovery User's Guide》21 Performing RMAN Tablespace Point-in-Time Recovery (TSPITR)
表空间时间点恢复实质是先将指定表空间按照时间点恢复到一个辅助的实例，然后用数据泵把数据抽取处理，再将抽取处理的数据导入到老的实例中。
01
—
环境准备
首先我们先进行环境准备。
SQL> -- Performing RMAN Tablespace Point-in-Time Recovery (TSPITR)
SQL> create tablespace mva datafile size 10m;
SQL> create table tbb(i int) tablespace mva;
SQL> insert into tbb values(2);
SQL> insert into tbb values(2);
SQL> insert into tbb values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13
SQL> insert into tbb values(4);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14
使用下面的存储过程检查MVA表空间是否是自包含，如果检查到违反自包含的结果会放在视图TRANSPORT_SET_VIOLATIONS中。
BEGIN
DBMS_TTS.TRANSPORT_SET_CHECK('MVA', TRUE,TRUE);
END;
/
SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
02
—
全自动恢复
姚远老师这里计划把MVA这个表空间恢复到第13个归档日志的时间点，使用下面的RMAN命令进行表空间的时间点恢复。
此处有图未下载
RECOVER TABLESPACE MVA
UNTIL LOGSEQ 13
3> AUXILIARY DESTINATION '/u01/tmp' ;
Starting recover at 2023-08-28
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 PDBA:SYSTEM
Tablespace UNDOTBS1
Tablespace PDBA:UNDOTBS1
Creating automatic instance, with SID='bAwz'
initialization parameters used for automatic instance:
db_name=SMALL
db_unique_name=bAwz_pitr_SMALL
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=7312M
processes=200
db_create_file_dest=/u01/tmp
log_archive_dest_1='location=/u01/tmp'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance SMALL
Oracle instance started
Total System Global Area 7667185656 bytes
Fixed Size 9152504 bytes
Variable Size 1291845632 bytes
Database Buffers 6308233216 bytes
Redo Buffers 57954304 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 logseq 13 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 2023-08-28
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=246 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 +DATA3/SMALL/AUTOBACKUP/2023_08_28/s_1146076218.575.1146076219
channel ORA_AUX_DISK_1: piece handle=+DATA3/SMALL/AUTOBACKUP/2023_08_28/s_1146076218.575.1146076219 tag=TAG20230828T183018
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output file name=/u01/tmp/SMALL/controlfile/o1_mf_lgrxyfbj_.ctl
Finished restore at 2023-08-28
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 13 thread 1;
plsql <<<--
declare
sqlstatement varchar2(512);
pdbname varchar2(128);
offline_not_needed exception;
pragma exception_init(offline_not_needed, -01539);
begin
pdbname := null; -- pdbname
sqlstatement := 'alter tablespace '|| 'MVA' ||' 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 9 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 11 to new;
set newname for clone datafile 3 to new;
set newname for clone tempfile 1 to new;
set newname for datafile 13 to
"+DATA3/SMALL/DATAFILE/mva.585.1146075991";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 9, 4, 11, 3, 13;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
sql statement: alter tablespace MVA 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
renamed tempfile 1 to /u01/tmp/SMALL/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 2023-08-28
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/tmp/SMALL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/tmp/SMALL/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/tmp/SMALL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00013 to +DATA3/SMALL/DATAFILE/mva.585.1146075991
channel ORA_AUX_DISK_1: reading from backup piece +DATA3/SMALL/BACKUPSET/2023_08_28/nnndf0_tag20230828t183003_0.580.1146076203
channel ORA_AUX_DISK_1: piece handle=+DATA3/SMALL/BACKUPSET/2023_08_28/nnndf0_tag20230828t183003_0.580.1146076203 tag=TAG20230828T183003
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 00009 to /u01/tmp/SMALL/03F9F6DBB7925A5AE063B257A8C0F566/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/tmp/SMALL/03F9F6DBB7925A5AE063B257A8C0F566/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +DATA3/SMALL/03F9F6DBB7925A5AE063B257A8C0F566/BACKUPSET/2023_08_28/nnndf0_tag20230828t183003_0.579.1146076211
channel ORA_AUX_DISK_1: piece handle=+DATA3/SMALL/03F9F6DBB7925A5AE063B257A8C0F566/BACKUPSET/2023_08_28/nnndf0_tag20230828t183003_0.579.1146076211 tag=TAG20230828T183003
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 2023-08-28
datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=1146076392 file name=/u01/tmp/SMALL/datafile/o1_mf_system_lgrxyp7h_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=10 STAMP=1146076392 file name=/u01/tmp/SMALL/03F9F6DBB7925A5AE063B257A8C0F566/datafile/o1_mf_system_lgrxz59b_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=1146076392 file name=/u01/tmp/SMALL/datafile/o1_mf_undotbs1_lgrxyp7j_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=12 STAMP=1146076392 file name=/u01/tmp/SMALL/03F9F6DBB7925A5AE063B257A8C0F566/datafile/o1_mf_undotbs1_lgrxz59d_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=1146076392 file name=/u01/tmp/SMALL/datafile/o1_mf_sysaux_lgrxyp7j_.dbf
contents of Memory Script:
{
# set requested point in time
set until logseq 13 thread 1;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone 'PDBA' "alter database datafile
9 online";
sql clone "alter database datafile 4 online";
sql clone 'PDBA' "alter database datafile
11 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 13 online";
# recover and open resetlogs
recover clone database tablespace "MVA", "SYSTEM", "PDBA":"SYSTEM", "UNDOTBS1", "PDBA":"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 9 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 11 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 13 online
Starting recover at 2023-08-28
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 11 is already on disk as file +DATA3/SMALL/ARCHIVELOG/2023_08_28/thread_1_seq_11.577.1146076217
archived log for thread 1 with sequence 12 is already on disk as file +DATA3/SMALL/ARCHIVELOG/2023_08_28/thread_1_seq_12.574.1146076261
archived log file name=+DATA3/SMALL/ARCHIVELOG/2023_08_28/thread_1_seq_11.577.1146076217 thread=1 sequence=11
archived log file name=+DATA3/SMALL/ARCHIVELOG/2023_08_28/thread_1_seq_12.574.1146076261 thread=1 sequence=12
media recovery complete, elapsed time: 00:00:01
Finished recover at 2023-08-28
database opened
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace MVA read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/tmp''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/tmp''";
}
executing Memory Script
sql statement: alter tablespace MVA read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/tmp''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/tmp''
Performing export of metadata...
EXPDP>
Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
EXPDP> Starting "SYS"."TSPITR_EXP_bAwz_irAw":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
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_bAwz_irAw" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_bAwz_irAw is:
EXPDP> /u01/tmp/tspitr_bAwz_11401.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace MVA:
EXPDP> +DATA3/SMALL/DATAFILE/mva.585.1146075991
EXPDP> Job "SYS"."TSPITR_EXP_bAwz_irAw" successfully completed at Mon Aug 28 18:33:56 2023 elapsed 0 00:00:30
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
# drop target tablespaces before importing them back
sql 'drop tablespace MVA including contents keep datafiles cascade constraints';
}
executing Memory Script
Oracle instance shut down
sql statement: drop tablespace MVA including contents keep datafiles cascade constraints
Performing import of metadata...
IMPDP>
Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
IMPDP> Master table "SYS"."TSPITR_IMP_bAwz_tzxB" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_bAwz_tzxB":
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/STATISTICS/MARKER
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job "SYS"."TSPITR_IMP_bAwz_tzxB" successfully completed at Mon Aug 28 18:34:25 2023 elapsed 0 00:00:19
Import completed
contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace MVA read write';
sql 'alter tablespace MVA offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
sql statement: alter tablespace MVA read write
sql statement: alter tablespace MVA offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/tmp/SMALL/datafile/o1_mf_temp_lgrxzjbn_.tmp deleted
auxiliary instance file /u01/tmp/SMALL/onlinelog/o1_mf_3_lgrxzd4t_.log deleted
auxiliary instance file /u01/tmp/SMALL/onlinelog/o1_mf_2_lgrxzd49_.log deleted
auxiliary instance file /u01/tmp/SMALL/onlinelog/o1_mf_1_lgrxzd3s_.log deleted
auxiliary instance file /u01/tmp/SMALL/datafile/o1_mf_sysaux_lgrxyp7j_.dbf deleted
auxiliary instance file /u01/tmp/SMALL/03F9F6DBB7925A5AE063B257A8C0F566/datafile/o1_mf_undotbs1_lgrxz59d_.dbf deleted
auxiliary instance file /u01/tmp/SMALL/datafile/o1_mf_undotbs1_lgrxyp7j_.dbf deleted
auxiliary instance file /u01/tmp/SMALL/03F9F6DBB7925A5AE063B257A8C0F566/datafile/o1_mf_system_lgrxz59b_.dbf deleted
auxiliary instance file /u01/tmp/SMALL/datafile/o1_mf_system_lgrxyp7h_.dbf deleted
auxiliary instance file /u01/tmp/SMALL/controlfile/o1_mf_lgrxyfbj_.ctl deleted
auxiliary instance file tspitr_bAwz_11401.dmp deleted
Finished recover at 2023-08-28
注意until子句指定的log sequence号，但不包括这个log，具体参见官方文档database-backup-and-recovery-reference.pdf。
完成恢复后表空间为offline的状态，需要备份后再改为online。
RMAN> backup tablespace mva;
Starting backup at 2023-08-28
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=00014 name=+DATA3/SMALL/DATAFILE/mva.585.1146075991
channel ORA_DISK_1: starting piece 1 at 2023-08-28
channel ORA_DISK_1: finished piece 1 at 2023-08-28
piece handle=+DATA3/SMALL/BACKUPSET/2023_08_28/nnndf0_tag20230828t183438_0.571.1146076479 tag=TAG20230828T183438 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2023-08-28
Starting Control File and SPFILE Autobackup at 2023-08-28
piece handle=+DATA3/SMALL/AUTOBACKUP/2023_08_28/s_1146076479.570.1146076479 comment=NONE
Finished Control File and SPFILE Autobackup at 2023-08-28
RMAN> alter tablespace mva online;
Statement processed
恢复完成后检查数据
SQL> select * from tbb;
I
----------
1
2
3
发现没有恢复记值为4的记录，也就是恢复到第13个归档日志。
如果是恢复PDB中的表空间，可以使用类似下面的命令
RECOVER TABLESPACE PDBA:MVA
UNTIL LOGSEQ 30
AUXILIARY DESTINATION '/u01/tmp' ;
03
—
客户化自动恢复参数
前面的例子是全自动的恢复，实际工作中还可以对一些参数进行客户化，例如可以使用指定的参数文件进行恢复。下面是一个参数文件的例子。
oracle@YaoYuan ~$ cat tmp/mva.ora
SGA_TARGET=2g
PROCESSES=200
还可以指定数据文件恢复的路径，下面是恢复脚本的一个例子。
run
{
SET NEWNAME FOR DATAFILE '+DATA3/SMALL/DATAFILE/mva.585.1146075991' to '/home/oracle/tmp/mva.dbf';
SET AUXILIARY INSTANCE PARAMETER FILE to '/home/oracle/tmp/mva.ora';
RECOVER TABLESPACE MVA
UNTIL LOGSEQ 21
AUXILIARY DESTINATION '/u01/tmp' ;
}
04
—
可能会遇到的坑
run
{
SET AUXILIARY INSTANCE PARAMETER FILE TO '/home/oracle/tmp/mva.ora';
RECOVER TABLESPACE PDBA:MVA
UNTIL TIME "TO_DATE('03-AUG-28:10:19:19','YY-MON-DD:HH24:MI:SS')"
AUXILIARY DESTINATION '/u01/tmp' ;
}
RECOVER TABLESPACE MVA
UNTIL TIME "TO_DATE('03-AUG-28:10:19:19','YY-MON-DD:HH24:MI:SS')"
AUXILIARY DESTINATION '/u01/tmp' ;
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/28/2023 11:45:52
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "SYSTEM"
日期格式错误，参见文档《RMAN-05015 RMAN-20202 & RMAN-06019 : Could Not Translate Tablespace Name "SYSTEM" (Doc ID 2952072.1)》要用2023，而不是23。
RECOVER TABLESPACE MVA
UNTIL TIME "to_date('08/28/2023 15:11:49','MM/DD/YYYY HH24:MI:SS')"
AUXILIARY DESTINATION '/u01/tmp' ;
经过测试的时间点粒度不能到具体的时间，只能恢复到logilfe。
注意until子句指定的log sequence号，但不包括这个log，具体参见官方文档database-backup-and-recovery-reference.pdf。