Oracle 19c OCM授课讲义：表空间时间点恢复

oracleace 2023-09-08

269

关于号主，姚远：

Oracle ACE（Oracle和MySQL数据库方向）
华为云最有价值专家
《MySQL 8.0运维与优化》的作者
拥有 Oracle 10g、12c和19c OCM等数十项数据库认证
曾任IBM公司数据库部门经理
20+年DBA经验，服务2万+客户
精通C和Java，发明两项计算机专利

在Oracle中，通常所有的表空间都要在同一个时间点上保持一致。但实际工作中，有时我们需要在同一个数据库中，把部分数据恢复到不同的时间点。这时就要用到RMAN的表空间时间点恢复功能。这里姚远老师给大家介绍一下这个功能，参考官方文档《Backup and Recovery User's Guide》21 Performing RMAN Tablespace Point-in-Time Recovery (TSPITR)

表空间时间点恢复实质是先将指定表空间按照时间点恢复到一个辅助的实例，然后用数据泵把数据抽取处理，再将抽取处理的数据导入到老的实例中。

—

环境准备

首先我们先进行环境准备。

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

—

全自动恢复

姚远老师这里计划把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' ;

—

客户化自动恢复参数

前面的例子是全自动的恢复，实际工作中还可以对一些参数进行客户化，例如可以使用指定的参数文件进行恢复。下面是一个参数文件的例子。

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' ;
}

—

可能会遇到的坑

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。

欢迎加我的微信，拉你进数据库微信群👇

推荐文章👇

试看《MySQL 8.0运维与优化》（清华大学出版社）

托业890分的Oracle ACE为您翻译国际大佬的雄文（合集）

最后修改时间：2023-09-19 14:25:22

文章转载自oracleace，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle 19c OCM授课讲义：表空间时间点恢复

环境准备

全自动恢复

客户化自动恢复参数

评论