RMAN Tablespace Point-in-Time Recovery（TSPITR）基于表空间时间点恢复
目录
TSPITR(RMAN Tablespace Point-in-Time Recovery)
RMAN Tablespace Point-in-Time Recovery
如果说DDL/DML操作误删test1表空间，使用TSPITR恢复时，保险起见先对该表空间的其他对象逻辑备份。
rman动作：
- 建立辅助实例
- restore database(包括system、sysaux、undo、对象所在的表空间)
- recover database until time
- transport tablespace to target db(表空间传输)
这些动作rman自动完成，也可以自己手动完成，手动第四步也可以自己使用导入导出来完成.
TSPITR和表恢复一样，最大的好处是可以不用关库
所以在12c推出表恢复，对其改进了，可以恢复到表，而不是表空间，对一些表的恢复来说更加高效
例子：
1.删除表空间的对象，备份其他对象
SQL> select tablespace_name,contents from dba_tablespaces;
TABLESPACE_NAME CONTENTS
------------------------------ ---------------------
SYSTEM PERMANENT
SYSAUX PERMANENT
UNDOTBS1 UNDO
TEMP TEMPORARY
USERS PERMANENT
TEST1 PERMANENT
test1表空间上的对象
SQL> select table_name,tablespace_name from user_tables where tablespace_name='TEST1';
TABLE_NAME TABLESPACE_NAME
-------------------- ------------------------------
T1 TEST1
EMP1 TEST1
EMP2 TEST1
SQL> select count(*) from emp2;
COUNT(*)
----------
20480
SQL> truncate table emp2;
SQL> insert into emp1 select * from emp1;
SQL> select count(*) from emp1;
COUNT(*)
----------
20
commit;
emp1增加的数据不需要恢复，所以安全起见逻辑备份
exp \'sys/Oracle123 as sysdba\' file=emp1.dmp log=emp1.log tables=emp1
About to export specified tables via Conventional Path ...
. . exporting table EMP1 20 rows exported
Export terminated successfully without warnings.
2.建立辅助实例恢复
#辅助实例存放目录
[oracle@db2 ~]$ mkdir /home/oracle/aux
#自包含检查，开始前检查表空间和其他表空间有没有关联关系
EXEC DBMS_TTS.TRANSPORT_SET_CHECK('TEST1',true);
SQL> EXEC DBMS_TTS.TRANSPORT_SET_CHECK('TEST1',true);
PL/SQL procedure successfully completed.
#完成之后查看TRANSPORT_SET_VIOLATIONS视图，若无数据，则表明是自包含的
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
#确认好truncate时间点，恢复到辅助实例(两个表空间，隔开)
recover tablespace test1 until time "to_date('2022/05/18 14:28:39','YYYY/MM/DD HH24:MI:SS')" auxiliary destination '/home/oracle/aux';
建立辅助实例的日志
和12c的恢复表，动作基本一致
注意日志中的，在表空间传输时会drop掉target上的表空间
sql statement: alter tablespace TEST1 read only
随后：sql 'drop tablespace TEST1 including contents keep datafiles cascade constraints';
并没有删除数据文件
随后辅助实例又expdp导出表空间
在生产库target 上导入,然后offline, alter tablespace TEST1 offline
迁移完成后删除了辅助实例产生的数据文件
生产库target的表空间一直offline，没有主动去online,得手动online
RMAN> recover tablespace test1 until time "to_date('2022/05/18 14:28:39','YYYY/MM/DD HH24:MI:SS')" auxiliary destination '/home/oracle/aux';
Starting recover at 18-MAY-22
using target database control file instead of recovery catalog
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=505 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
Creating automatic instance, with SID='zyjl'
initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=zyjl_pitr_ORCL
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=4720M
processes=200
db_create_file_dest=/home/oracle/aux
log_archive_dest_1='location=/home/oracle/aux'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance ORCL
Oracle instance started
Total System Global Area 4949277056 bytes
Fixed Size 9144704 bytes
Variable Size 922746880 bytes
Database Buffers 4009754624 bytes
Redo Buffers 7630848 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('2022/05/18 14:28:39','YYYY/MM/DD 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
executing command: SET until clause
Starting restore at 18-MAY-22
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1 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 /tmp/test/full_0_ORCL_545
channel ORA_AUX_DISK_1: piece handle=/tmp/test/full_0_ORCL_545 tag=TAG20220518T104233
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/aux/ORCL/controlfile/o1_mf_k896l17r_.ctl
Finished restore at 18-MAY-22
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 time "to_date('2022/05/18 14:28:39','YYYY/MM/DD HH24:MI:SS')";
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 '|| 'TEST1' ||' 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 19 to
"/u01/app/oracle/oradata/ORCL/test1.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, 19;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
sql statement: alter tablespace TEST1 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/aux/ORCL/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 18-MAY-22
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 00004 to /home/oracle/aux/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /home/oracle/aux/ORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00019 to /u01/app/oracle/oradata/ORCL/test1.dbf
channel ORA_AUX_DISK_1: reading from backup piece /tmp/test/full_0_ORCL_537
channel ORA_AUX_DISK_1: piece handle=/tmp/test/full_0_ORCL_537 tag=TAG20220518T104114
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
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/aux/ORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /tmp/test/full_0_ORCL_536
channel ORA_AUX_DISK_1: piece handle=/tmp/test/full_0_ORCL_536 tag=TAG20220518T104114
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 18-MAY-22
datafile 1 switched to datafile copy
input datafile copy RECID=11 STAMP=1105023948 file name=/home/oracle/aux/ORCL/datafile/o1_mf_system_k896n0qv_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=1105023948 file name=/home/oracle/aux/ORCL/datafile/o1_mf_undotbs1_k896l9pv_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=1105023948 file name=/home/oracle/aux/ORCL/datafile/o1_mf_sysaux_k896l9ok_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022/05/18 14:28:39','YYYY/MM/DD HH24:MI:SS')";
# 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 19 online";
# recover and open resetlogs
recover clone database tablespace "TEST1", "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 19 online
Starting recover at 18-MAY-22
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/product/19.3/dbhome_1/dbs/arch1_5_1104945266.dbf
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/product/19.3/dbhome_1/dbs/arch1_6_1104945266.dbf
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=4
channel ORA_AUX_DISK_1: reading from backup piece /tmp/test/full_0_ORCL_544
channel ORA_AUX_DISK_1: piece handle=/tmp/test/full_0_ORCL_544 tag=TAG20220518T104231
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/home/oracle/aux/1_4_1104945266.dbf thread=1 sequence=4
channel clone_default: deleting archived log(s)
archived log file name=/home/oracle/aux/1_4_1104945266.dbf RECID=156 STAMP=1105023951
archived log file name=/u01/app/oracle/product/19.3/dbhome_1/dbs/arch1_5_1104945266.dbf thread=1 sequence=5
archived log file name=/u01/app/oracle/product/19.3/dbhome_1/dbs/arch1_6_1104945266.dbf thread=1 sequence=6
media recovery complete, elapsed time: 00:00:17
Finished recover at 18-MAY-22
database opened
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace TEST1 read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/home/oracle/aux''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/home/oracle/aux''";
}
executing Memory Script
sql statement: alter tablespace TEST1 read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle/aux''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle/aux''
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_zyjl_hzbk":
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_zyjl_hzbk" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_zyjl_hzbk is:
EXPDP> /home/oracle/aux/tspitr_zyjl_62248.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace TEST1:
EXPDP> /u01/app/oracle/oradata/ORCL/test1.dbf
EXPDP> Job "SYS"."TSPITR_EXP_zyjl_hzbk" successfully completed at Wed May 18 15:08:09 2022 elapsed 0 00:00:44
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
# drop target tablespaces before importing them back
sql 'drop tablespace TEST1 including contents keep datafiles cascade constraints';
}
executing Memory Script
Oracle instance shut down
sql statement: drop tablespace TEST1 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_zyjl_ccax" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_zyjl_ccax":
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_zyjl_ccax" successfully completed at Wed May 18 15:08:59 2022 elapsed 0 00:00:30
Import completed
contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace TEST1 read write';
sql 'alter tablespace TEST1 offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
sql statement: alter tablespace TEST1 read write
sql statement: alter tablespace TEST1 offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file /home/oracle/aux/ORCL/datafile/o1_mf_temp_k896s4dq_.tmp deleted
auxiliary instance file /home/oracle/aux/ORCL/onlinelog/o1_mf_3_k896q2go_.log deleted
auxiliary instance file /home/oracle/aux/ORCL/onlinelog/o1_mf_2_k896q2g1_.log deleted
auxiliary instance file /home/oracle/aux/ORCL/onlinelog/o1_mf_1_k896q2fh_.log deleted
auxiliary instance file /home/oracle/aux/ORCL/datafile/o1_mf_sysaux_k896l9ok_.dbf deleted
auxiliary instance file /home/oracle/aux/ORCL/datafile/o1_mf_undotbs1_k896l9pv_.dbf deleted
auxiliary instance file /home/oracle/aux/ORCL/datafile/o1_mf_system_k896n0qv_.dbf deleted
auxiliary instance file /home/oracle/aux/ORCL/controlfile/o1_mf_k896l17r_.ctl deleted
auxiliary instance file tspitr_zyjl_62248.dmp deleted
Finished recover at 18-MAY-22
3.target库的表空间online
SQL> alter tablespace TEST1 online;
Tablespace altered.
SQL> select count(*) from emp2;
COUNT(*)
----------
20480
select count(*) from emp1;
COUNT(*)
----------
10
回到了truncate之前的时间点，时间点之后的数据就没了，所以得利用上开始之前对其他对象的备份来恢复
SQL> truncate table emp1;
Table truncated.
利用备份导入
imp \'sys/Oracle123 as sysdba\' file=emp1.dmp log=emp1.log tables=emp1 IGNORE=y
. . importing table "EMP1" 20 rows imported
Import terminated successfully with warnings.
#恢复完成
SQL> select count(*) from emp1;
COUNT(*)
----------
20
