RMAN完全恢复与不完全恢复测试
一、完全恢复
场景：在T0时间点存在RMAN全备，在T1时间点由于介质损坏，数据文件丢失。redo以及归档文件完整。
解决方法：
可用RMAN备份执行完全恢复
1、T0时间点做RMAN全备
RMAN> backup as compressed backupset database format '/u01/rman_bak/orcldb_full_%T_%U.bak'；
2、创建测试表
SQL> create table tbs as select * from dba_users;
Table created.
3、T1时间点删除数据文件
SQL> !rm -rf /u01/app/oracle/oradata/orcl/admin.dbf
SQL> shutdown immediate;
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/admin.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
4、RMAN恢复
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;
5、验证数据
SQL> select count(*) from tbs;
COUNT(*)
----------
31
已恢复完毕
二、不完全恢复
2.1 基于时间点
场景：数据库在T0时间点做了全备，T1时间点由于误操作update了整张表，此时归档日志连续并完整，可以通过RMAN基于时间点不完全恢复，将数据库恢复到T1时间点（不包含T1）之前。
解决方法：
通过指定时间执行RMAN不完全恢复
1、T0时间点RMAN全备
RMAN> backup as compressed backupset database format '/u01/rman_bak/orcldb_full_%T_%U.bak'；
2、创建测试表
SQL> create table tbs2 as select * from test;
Table created.
记录此时的时间点（T1时间点前）
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') currtime from dual;
CURRTIME
-------------------
2018-08-08 11:14:09
SQL> select * from tbs2;
ID
----------
2
2
3、T1时间点误操作update并提交
SQL> update tbs2 set id=22;
SQL> commit;
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') currtime from dual;
CURRTIME
-------------------
2018-08-08 11:16:36
SQL> select * from tbs2;
ID
----------
22
22
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
4、通过RMAN执行不完全恢复，指点时间点到T1之前
RMAN> restore database until time "to_date('2018-08-08 11:14:09','yyyy-mm-dd hh24:mi:ss')";
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 2018-08-08 11:21:49
RMAN> recover database until time "to_date('2018-08-08 11:14:09','yyyy-mm-dd hh24:mi:ss')";
Starting recover at 2018-08-08 11:25:24
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2018-08-08 11:25:25
恢复完毕
5、打开数据库
RMAN> alter database open resetlogs;
6、验证表数据
SQL> conn admin/oracle
Connected.
SQL> select * from tbs2;
ID
----------
2
2
表已恢复到T1时间点之前
2.2 基于日志序列号
场景：数据库在T0时间点做了全备，T1时间点创建1张表，T2时间点创建1张表，之后数据文件介质故障，归档日志文件并不连续，丢失T1、T2之间的日志。
解决方法：
可以通过日志序列号执行不完全恢复，将数据库恢复到T1时间点（包含T1时间点）。
1、T0时间全备
RMAN> backup database format '/u01/rman_orcl/full_db_%d_%T.bak';
2、T1时间创建表tbs_seq
SQL> conn admin/admin123
Connected.
SQL> create table t_seq(id number);
Table created.
SQL> insert into t_seq values(1);
1 row created.
SQL> commit;
Commit complete.
查看当前日志序号
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
3、多次切换日志
SQL> alter system switch logfile;
/
/
/
查看日志序列号
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
4、查看归档目录日志文件
[oracle@gs arch]$ ll -hrt
total 60K
-rw-r----- 1 oracle oinstall 14K Aug 10 17:14 1_4_983803174.dbf
-rw-r----- 1 oracle oinstall 1.0K Aug 10 17:14 1_5_983803174.dbf
-rw-r----- 1 oracle oinstall 1.5K Aug 10 17:14 1_6_983803174.dbf
-rw-r----- 1 oracle oinstall 18K Aug 10 17:18 1_7_983803174.dbf
-rw-r----- 1 oracle oinstall 1.0K Aug 10 17:19 1_8_983803174.dbf
-rw-r----- 1 oracle oinstall 2.0K Aug 10 17:19 1_9_983803174.dbf
-rw-r----- 1 oracle oinstall 1.0K Aug 10 17:19 1_10_983803174.dbf
当前归档日志序号是10，包含表t_seq的ddl，dml操作的redo信息。
5、T2时间点创建表t_seq2
SQL> create table t_seq2 (id number);
Table created.
SQL> insert into t_seq2 values(1);
1 row created.
SQL> commit;
Commit complete.
6、多次切换日志
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
查看日志序列号
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15
7、查看归档目录
[oracle@gs arch]$ ll -hrt
total 260K
-rw-r----- 1 oracle oinstall 14K Aug 10 17:14 1_4_983803174.dbf
-rw-r----- 1 oracle oinstall 1.0K Aug 10 17:14 1_5_983803174.dbf
-rw-r----- 1 oracle oinstall 1.5K Aug 10 17:14 1_6_983803174.dbf
-rw-r----- 1 oracle oinstall 18K Aug 10 17:18 1_7_983803174.dbf
-rw-r----- 1 oracle oinstall 1.0K Aug 10 17:19 1_8_983803174.dbf
-rw-r----- 1 oracle oinstall 2.0K Aug 10 17:19 1_9_983803174.dbf
-rw-r----- 1 oracle oinstall 1.0K Aug 10 17:19 1_10_983803174.dbf
-rw-r----- 1 oracle oinstall 186K Aug 10 17:30 1_11_983803174.dbf
-rw-r----- 1 oracle oinstall 1.0K Aug 10 17:30 1_12_983803174.dbf
-rw-r----- 1 oracle oinstall 3.5K Aug 10 17:30 1_13_983803174.dbf
-rw-r----- 1 oracle oinstall 1.0K Aug 10 17:30 1_14_983803174.dbf
其中11、12、13、14包含表t_seq2的ddl、dml redo记录
8、模拟介质故障
删除所有数据文件
[oracle@gs orcl]$ rm -f *.dbf
9、模拟归档日志丢失
删除日志序号10的文件
[oracle@gs arch]$ mv 1_10_983803174.dbf 1_10_983803174.old
10、数据库启动到mount状态
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
#数据库此时正常关闭已报错
SQL> shutdown abort
ORACLE instance shut down.
#强制关闭数据库
SQL> startup mount;
11、RMAN还原数据文件
RMAN> restore database;
Starting restore at 2018/08/10 17:38:40
using target database control file instead of recovery catalog
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 2018/08/10 17:39:06
12、恢复数据库（应用日志）
RMAN> recover database;
Starting recover at 2018/08/10 17:39:58
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 7 is already on disk as file /u01/arch/1_7_983803174.dbf
archived log for thread 1 with sequence 8 is already on disk as file /u01/arch/1_8_983803174.dbf
archived log for thread 1 with sequence 9 is already on disk as file /u01/arch/1_9_983803174.dbf
archived log for thread 1 with sequence 11 is already on disk as file /u01/arch/1_11_983803174.dbf
archived log for thread 1 with sequence 12 is already on disk as file /u01/arch/1_12_983803174.dbf
archived log for thread 1 with sequence 13 is already on disk as file /u01/arch/1_13_983803174.dbf
archived log for thread 1 with sequence 14 is already on disk as file /u01/arch/1_14_983803174.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/10/2018 17:39:59
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 10 and starting SCN of 3095297 found to restore
#--10号日志已丢失，只能恢复到序号9的日志，上述语句是恢复10（不包含10）之前所有的日志
指定日志序列号再次恢复
RMAN> recover database until sequence 10;
Starting recover at 2018/08/10 17:41:39
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 7 is already on disk as file /u01/arch/1_7_983803174.dbf
archived log for thread 1 with sequence 8 is already on disk as file /u01/arch/1_8_983803174.dbf
archived log for thread 1 with sequence 9 is already on disk as file /u01/arch/1_9_983803174.dbf
archived log file name=/u01/arch/1_7_983803174.dbf thread=1 sequence=7
archived log file name=/u01/arch/1_8_983803174.dbf thread=1 sequence=8
archived log file name=/u01/arch/1_9_983803174.dbf thread=1 sequence=9
media recovery complete, elapsed time: 00:00:00
Finished recover at 2018/08/10 17:41:40
13、打开数据库
RMAN> alter database open resetlogs;
database opened
14、检验数据
SQL> select * from t_seq;
ID
----------
1
SQL> select * from t_seq2;
select * from t_seq2
*
ERROR at line 1:
ORA-00942: table or view does not exist
t_seq2已丢失，已恢复到日志序号10之前（1-9），序号10以及之后的数据会丢数据
2.3 基于SCN 恢复
场景：数据库在T0时间点做了全备，T1时间点创建1张表T1并插入3条数据，T2时间点误删除表T1。
解决方法：
通过logmnr分析表删除SCN，可用具体SCN执行不完全恢复表。
1、创建表T1
create table tbs (id number);
insert into tbs values(1);
insert into tbs values(2);
insert into tbs values(3);
commit;
alter system switch logfile;
SQL> select * from tbs;
ID
----------
1
2
3
2、删除表T1
SQL> drop table tbs purge;
Table dropped.
SQL> alter system switch logfile;
System altered.
SQL> select * from tbs;
select * from tbs
*
ERROR at line 1:
ORA-00942: table or view does not exist
3、logminer分析归档日志
SQL> select scn,table_name,sql_redo,operation from v$logmnr_contents where table_name='TBS';
SCN TABLE_NAME SQL_REDO OPERATION
------- ---------- -------------------------- -----------------
975275 TBS create table tbs (id number); DDL
975322 TBS drop table tbs purge; DDL
确定drop TBS的scn为975322
4、RMAN恢复数据
RMAN> restore database until scn 975322;
RMAN> recover database until scn 975322;
RMAN> alter database open resetlogs;
5、验证数据
SQL> select * from tbs;
ID
----------
1
2
3
已恢复完毕
2.4 通过INCARNATION不完全恢复
场景：表TBS原先有5条数据，在T0时间点做RMAN全备，T1，T2时间点误操作分别删除了一行数据。随后不完全恢复数据库到T2之前，并resetlogs。如果此时要再次不完全恢复到T1时间点，需要通过RMAN指定INCARNATION。
解决方法：
因为每次resetlogs之后数据库会生成一个新的incarnation，如果要用resetlogs之前的RMAN备份不完全恢复，再次恢复到resetlogs之前的时间点，就必须先指定时间还原控制文件，随后将RMAN重置incarnation到上一个时间点，最后执行不完全恢复。
1、创建表TBS
create table tbs (id number);
insert into tbs values(1);
insert into tbs values(2);
insert into tbs values(3);
insert into tbs values(4);
insert into tbs values(5);
commit;
alter system switch logfile;
2、T1时间点删除一行数据
delete from tbs where id=1;
commit;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') currtime from dual;
记下此时T1时间点：
2018-08-17 14:29:45
3、T2时间点删除一行数据
delete from tbs where id=2;
commit;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') currtime from dual;
记下此时T2时间点：
2018-08-17 14:30:45
4、不完全恢复到T2之前
restore database until time "to_date('2018-08-17 14:30:45','yyyy-mm-dd hh24:mi:ss')";
recover database until time "to_date('2018-08-17 14:30:45','yyyy-mm-dd hh24:mi:ss')";
alter database open resetlogs;
SQL> select * from tbs;
ID
----------
2
3
4
5
5、再次不完全恢复
这次直接指定时间到T1
restore database until time "to_date('2018-08-17 14:29:45','yyyy-mm-dd hh24:mi:ss')";
recover database until time "to_date('2018-08-17 14:29:45','yyyy-mm-dd hh24:mi:ss')";
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time
这是无法恢复
6、还原控制文件（指定时间）
还原resetlogs之前的控制文件
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup until time"to_date('2018-08-17 14:29:49','yyyy-mm-dd hh24:mi:ss')";
RMAN> alter database mount;
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORCL 1511917677 PARENT 1 2013-08-24 11:37:30
2 2 ORCL 1511917677 PARENT 925702 2018-08-14 11:22:23
3 3 ORCL 1511917677 PARENT 975323 2018-08-14 16:29:37
4 4 ORCL 1511917677 CURRENT 1128098 2018-08-17 14:33:29
7、重置数据库化身incarnation
RMAN> reset database to incarnation 3;
以此为rman备份，还原的起点
8、不完全恢复
run{
restore database until time "to_date('2018-08-17 14:29:45','yyyy-mm-dd hh24:mi:ss')";
recover database until time "to_date('2018-08-17 14:29:45','yyyy-mm-dd hh24:mi:ss')";
alter database open resetlogs;
}
9、再次查询TBS数据是否回到T1之前
SQL> select * from tbs;
ID
----------
1
2
3
4
5
2.5 基于表空间时间点的不完全恢复(TSPITR)
场景：在T0时间点按照顺序创建3张表，t_trun、t_drop记下时间，最后创建表t_lost。在T1时间点truncate表t_trun，在T2时间点drop表t_drop。需要将数据在表空间级别恢复到T1时间点（truncate）
解决方法：
1、先用存储过程检查表空间是否是自包含关系(没有其他关联表空间)
2、查询指定的恢复时间点之后的表是否会丢失
（某个时间点之后，可能有其他表会创建）
3、导出会丢失的表数据
4、执行TSPITR
5、表空间ONLINE
6、导入数据（会丢失的表数据）
1、创建3个测试表
10:10:56 SQL> conn cs/oracle
Connected.
10:11:00 SQL> create table t_trun as select * from dba_objects;
Table created.
10:12:10 SQL> create index idx_trun_id on t_trun(object_id);
Index created.
10:12:43 SQL> create table t_drop as select * from dba_users;
Table created.
10:13:00 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') currtime from dual;
CURRTIME
---------------------------------------------------------
2018-06-26 10:13:09
2、最后创建一张表t_lost
10:13:46 SQL> create table t_lost as select * from dba_segments;
Table created.
3、对t_trun做截断，对t_drop删除表
10:13:55 SQL> truncate table t_trun;
Table truncated.
10:14:17 SQL> drop table t_drop purge;
Table dropped.
4、创建一个存储过程，用来确定表空间CS是否是自包含关系
10:14:29 SQL> conn / as sysdba
Connected.
10:15:51 SQL> BEGIN
10:16:11 2 DBMS_TTS.TRANSPORT_SET_CHECK('CS', TRUE,TRUE);
10:16:12 3 END;
10:16:12 4 /
PL/SQL procedure successfully completed.
5、查询是否是自包含关系
10:16:17 SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
执行过上面的包后，没有返回行则表示自包含
6、查询执行表空间不完全恢复，哪些表会丢失
10:16:35 SQL> col owner for a30
10:17:06 SQL> col name for a30
10:17:13 SQL> col tablespace_name for a35
10:17:18 SQL> col ctime for a45
10:17:23 SQL> set linesize 200
10:17:28 SQL> SELECT OWNER, NAME, TABLESPACE_NAME,TO_CHAR(CREATION_TIME, 'YYYY-MM-DD:HH24:MI:SS') ctime FROM TS_PITR_OBJECTS_TO_BE_DROPPED WHERE TABLESPACE_NAME='CS' aND CREATION_TIME > TO_DATE('2018-06-26 10:13:09','YYYY-MM-DD HH24:MI:SS') ORDER BY TABLESPACE_NAME, CREATION_TIME;
OWNER NAME TABLESPACE_NAME CTIME
------------------------------ ------------------------------ ----------------------------------- ---------------------------------------------
CS T_LOST CS 2018-06-26:10:13:55
上述的查询表示，如果执行TSPITR，会丢失时间为2018-06-26:10:13:55的表T_LOST
7、此时需要把会丢失的表导出来
[root@gs oracle]# expdp cs/oracle directory=dump dumpfile=t_lost.dmp tables=t_lost
备注：待TSPITR恢复之后，再手动将表导回去
8、TSPITR恢复部分，执行不完全恢复
RMAN> RECOVER TABLESPACE "CS" UNTIL TIME "TO_DATE('2018-06-26:10:13:55','YYYY-MM-DD HH24:MI:SS')" AUXILIARY DESTINATION '/u01/rman3';
这里确定好时间点，将时间定位到会丢失表的时间点（如上查询出的时间2018-06-26:10:13:55）
Starting recover at 2018/06/26 10:33:41
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='uDsa'
initialization parameters used for automatic instance:
db_name=ORCL3
db_unique_name=uDsa_tspitr_ORCL3
compatible=11.2.0.4.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
db_create_file_dest=/u01/rman3
log_archive_dest_1='location=/u01/rman3'
#No auxiliary parameter file used
starting up automatic instance ORCL3
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 285213576 bytes
Database Buffers 775946240 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 time "TO_DATE('2018-06-26:10:13:55','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 2018/06/26 10:33:49
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=114 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/rman3/ctl_spfile_c-910863102-20180626-00.ctl
channel ORA_AUX_DISK_1: piece handle=/u01/rman3/ctl_spfile_c-910863102-20180626-00.ctl tag=TAG20180626T095332
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/rman3/ORCL3/controlfile/o1_mf_fm39jfs0_.ctl
Finished restore at 2018/06/26 10:33:50
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('2018-06-26:10:13:55','YYYY-MM-DD HH24:MI:SS')";
plsql <<<-- tspitr_2
declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed, -01539);
begin
sqlstatement := 'alter tablespace '|| '"CS"' ||' 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 2 to new;
set newname for clone tempfile 1 to new;
set newname for datafile 7 to
"/u01/app/oracle/oradata/orcl3/cs.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 3, 2, 7;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
sql statement: alter tablespace "CS" 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 /u01/rman3/ORCL3/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 2018/06/26 10:33:55
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/rman3/ORCL3/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/rman3/ORCL3/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/rman3/ORCL3/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/orcl3/cs.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/rman3/orcl3_fulldb_20180626.bak
channel ORA_AUX_DISK_1: piece handle=/u01/rman3/orcl3_fulldb_20180626.bak tag=TAG20180626T095317
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 2018/06/26 10:34:11
datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=979814051 file name=/u01/rman3/ORCL3/datafile/o1_mf_system_fm39jn0b_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=979814051 file name=/u01/rman3/ORCL3/datafile/o1_mf_undotbs1_fm39jn05_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=12 STAMP=979814051 file name=/u01/rman3/ORCL3/datafile/o1_mf_sysaux_fm39jn03_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "TO_DATE('2018-06-26:10:13:55','YYYY-MM-DD HH24:MI:SS')";
# 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 2 online";
sql clone "alter database datafile 7 online";
# recover and open resetlogs
recover clone database tablespace "CS", "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 3 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 7 online
Starting recover at 2018/06/26 10:34:11
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /u01/arch3/1_1_979754431.dbf
archived log file name=/u01/arch3/1_1_979754431.dbf thread=1 sequence=1
media recovery complete, elapsed time: 00:00:01
Finished recover at 2018/06/26 10:34:12
database opened
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace "CS" read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/rman3''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/rman3''";
}
executing Memory Script
sql statement: alter tablespace "CS" read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/rman3''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/rman3''
Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_uDsa":
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/INDEX_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table "SYS"."TSPITR_EXP_uDsa" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_uDsa is:
EXPDP> /u01/rman3/tspitr_uDsa_48488.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace CS:
EXPDP> /u01/app/oracle/oradata/orcl3/cs.dbf
EXPDP> Job "SYS"."TSPITR_EXP_uDsa" successfully completed at Tue Jun 26 10:35:01 2018 elapsed 0 00:00:44
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
# drop target tablespaces before importing them back
sql 'drop tablespace "CS" including contents keep datafiles cascade constraints';
}
executing Memory Script
database closed
database dismounted
Oracle instance shut down
sql statement: drop tablespace "CS" including contents keep datafiles cascade constraints
Performing import of metadata...
IMPDP> Master table "SYS"."TSPITR_IMP_uDsa" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_uDsa":
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/INDEX_STATISTICS
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job "SYS"."TSPITR_IMP_uDsa" successfully completed at Tue Jun 26 10:35:16 2018 elapsed 0 00:00:03
Import completed
contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace "CS" read write';
sql 'alter tablespace "CS" offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
sql statement: alter tablespace "CS" read write
sql statement: alter tablespace "CS" offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/rman3/ORCL3/datafile/o1_mf_temp_fm39k6nk_.tmp deleted
auxiliary instance file /u01/rman3/ORCL3/onlinelog/o1_mf_5_fm39k5rl_.log deleted
auxiliary instance file /u01/rman3/ORCL3/onlinelog/o1_mf_4_fm39k582_.log deleted
auxiliary instance file /u01/rman3/ORCL3/onlinelog/o1_mf_3_fm39k4tz_.log deleted
auxiliary instance file /u01/rman3/ORCL3/onlinelog/o1_mf_2_fm39k4km_.log deleted
auxiliary instance file /u01/rman3/ORCL3/onlinelog/o1_mf_1_fm39k47y_.log deleted
auxiliary instance file /u01/rman3/ORCL3/datafile/o1_mf_sysaux_fm39jn03_.dbf deleted
auxiliary instance file /u01/rman3/ORCL3/datafile/o1_mf_undotbs1_fm39jn05_.dbf deleted
auxiliary instance file /u01/rman3/ORCL3/datafile/o1_mf_system_fm39jn0b_.dbf deleted
auxiliary instance file /u01/rman3/ORCL3/controlfile/o1_mf_fm39jfs0_.ctl deleted
Finished recover at 2018/06/26 10:35:23
9、将表空间cs online
RMAN> sql 'alter tablespace cs online';
sql statement: alter tablespace cs online
10、导入需要恢复的表
[root@gs oracle]# impdp cs/oracle directory=dump dumpfile=t_lost.dmp tables=t_lost
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "CS"."T_LOST" 798.5 KB 6076 rows
Job "CS"."SYS_IMPORT_TABLE_01" successfully completed at Tue Jun 26 10:38:54 2018 elapsed 0 00:00:02
11、查询表数据是否恢复
10:32:42 SQL> conn cs/oracle
Connected.
10:36:17 SQL> select count(*) from t_drop;
COUNT(*)
----------
33
10:36:52 SQL> select count(*) from t_trun;
COUNT(*)
----------
86421
10:36:57 SQL> select count(*) from t_lost;
COUNT(*)
----------
6076
已恢复完毕
TSPITR适用场景:
1、错误的批处理作业或数据操作语言DML
2、恢复数据定义语言（DDL）后丢失的数据操作，改变表的结构。不能使用闪回表将表倒回结构更改点之前，例如截断表(truncate)操作。
3、恢复drop时使用了purge选项的表
4、恢复存在逻辑错误的表
5、存在有效可用的备份
不能使用的场景：
1、表空间包含sys用户下的对象
2、表空间包含undo、rollback段
3、被删除的表空间
4、嵌套表以及数组列Tables with nested tables or varray columns
5、Tables that reference external files.
6、Tables that reference external files.
7、If a table is partitioned across multiple tablespaces, then you cannot recover just one of its tablespaces; you must recover all tablespaces associated with the partitioned table.
2.6 12c RMAN 表级恢复
在Oracle 12C之前RMAN只能在数据库级（数据文件）、表空间级进行完全恢复或者不完全恢复。如果是某张表发生了截断或者删除，可通过闪回表或者闪回数据库快速恢复，或者是dmp文件。然而到了12C，RMAN可以通过备份将数据表恢复到故障时间点，而其他表不受影响。其过程是在恢复过程中创建辅助实例，还原系统数据文件（system、sysaux、undotbs），基于redo或者archivelog应用，最后通过数据泵导入目标shemas，恢复之后自动创建的辅助实例会被自动删除。
场景1：
在T0时间点，存在1张表T1此时无任何数据，此时做cdb级别RMAN全备。在T1时间点插入3条测试数据并提交，在T2时间点删除表T1(模拟误删）。
解决方法：
通过表级恢复，表T1恢复到T2时间点之前。
1、先查看表T1
12:47:45 SQL> select * from t1;
no rows selected
12:47:55 SQL> select * from t2;
ID
----------
1
2
查看下此时表T1和T2数据
2、T0时间点做CDB级别的RMAN全备
RMAN> backup as compressed backupset database format '/u01/rman/%d_%T_%U.bak';
Starting backup at 2018-08-13 12:48:14
Finished backup at 2018-08-13 12:49:25
3、T1时间点插入3条数据
12:48:04 SQL> insert into t1 values(3);
1 row created.
12:50:54 SQL> insert into t1 values(4);
1 row created.
12:51:12 SQL> insert into t1 values(5);
1 row created.
12:51:25 SQL> commit;
Commit complete.
12:51:28 SQL> select * from t1;
ID
----------
3
4
5
查看表T1，T1时间点已完成数据插入
4、T2时间点删除表T1
12:51:50 SQL> drop table t1 purge;
Table dropped.
记下此时删除时间：12:52:03
12:52:03 SQL> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist
表T1已被删除
5、执行表级恢复
RMAN> RECOVER TABLE admin.t1 of pluggable database pdborcl2
UNTIL time "to_date('2018-08-13 12:52:03','yyyy-mm-dd hh24:mi:ss')"
#指定时间drop之前（12:52:03）
AUXILIARY DESTINATION '/u01/inst'
#auxiliary为oracle自动创建辅助实例的目录
datapump destination '/u01/dump';
#datapump为oracle恢复数据之后再导入到原数据库
DUMP FILE 'tablename.dmp'
#(可选)NOTABLEIMPORT 此选项避免自动导入表
恢复过程日志
Starting recover at 2018-08-13 12:52:54
current log archived
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='Fina'
initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=Fina_pitr_pdborcl2_ORCL
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=796M
processes=200
db_create_file_dest=/u01/inst
log_archive_dest_1='location=/u01/inst'
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 834666496 bytes
Fixed Size 8798264 bytes
Variable Size 226496456 bytes
Database Buffers 595591168 bytes
Redo Buffers 3780608 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2018-08-13 12:52:03','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';
}
executing Memory Script
executing command: SET until clause
Starting restore at 2018-08-13 12:53:03
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=34 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/product/12.2.0/db_1/dbs/c-1498869325-20180813-04
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/db_1/dbs/c-1498869325-20180813-04 tag=TAG20180813T124925
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/inst/ORCL/controlfile/o1_mf_fq23oj3h_.ctl
Finished restore at 2018-08-13 12:53:05
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2018-08-13 12:52:03','yyyy-mm-dd hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 19 to new;
set newname for clone datafile 20 to new;
set newname for clone tempfile 1 to new;
set newname for clone tempfile 4 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 5, 3, 19, 20;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
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/inst/ORCL/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 4 to /u01/inst/ORCL/67AFB4BA944126D2E0536E0410ACB92F/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 2018-08-13 12:53:09
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/inst/ORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/inst/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/inst/ORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/rman/ORCL_20180813_1otaevse_1_1.bak
channel ORA_AUX_DISK_1: piece handle=/u01/rman/ORCL_20180813_1otaevse_1_1.bak tag=TAG20180813T124814
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
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 00019 to /u01/inst/ORCL/67AFB4BA944126D2E0536E0410ACB92F/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00020 to /u01/inst/ORCL/67AFB4BA944126D2E0536E0410ACB92F/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/rman/ORCL_20180813_1ptaevt8_1_1.bak
channel ORA_AUX_DISK_1: piece handle=/u01/rman/ORCL_20180813_1ptaevt8_1_1.bak tag=TAG20180813T124814
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 2018-08-13 12:53:49
datafile 1 switched to datafile copy
input datafile copy RECID=12 STAMP=984056029 file name=/u01/inst/ORCL/datafile/o1_mf_system_fq23oov3_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=984056029 file name=/u01/inst/ORCL/datafile/o1_mf_undotbs1_fq23ooth_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=984056029 file name=/u01/inst/ORCL/datafile/o1_mf_sysaux_fq23oovg_.dbf
datafile 19 switched to datafile copy
input datafile copy RECID=15 STAMP=984056029 file name=/u01/inst/ORCL/67AFB4BA944126D2E0536E0410ACB92F/datafile/o1_mf_system_fq23pgw4_.dbf
datafile 20 switched to datafile copy
input datafile copy RECID=16 STAMP=984056029 file name=/u01/inst/ORCL/67AFB4BA944126D2E0536E0410ACB92F/datafile/o1_mf_sysaux_fq23pgx3_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2018-08-13 12:52:03','yyyy-mm-dd hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 5 online";
sql clone "alter database datafile 3 online";
sql clone 'PDBORCL2' "alter database datafile
19 online";
sql clone 'PDBORCL2' "alter database datafile
20 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX", "PDBORCL2":"SYSTEM", "PDBORCL2":"SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 5 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 19 online
sql statement: alter database datafile 20 online
Starting recover at 2018-08-13 12:53:50
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 35 is already on disk as file /u01/arch/1_35_971088589.dbf
archived log file name=/u01/arch/1_35_971088589.dbf thread=1 sequence=35
media recovery complete, elapsed time: 00:00:00
Finished recover at 2018-08-13 12:53:51
sql statement: alter database open read only
contents of Memory Script:
{
sql clone 'alter pluggable database PDBORCL2 open read only';
}
executing Memory Script
sql statement: alter pluggable database PDBORCL2 open read only
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/u01/inst/ORCL/controlfile/o1_mf_fq23oj3h_.ctl'' comment=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 834666496 bytes
Fixed Size 8798264 bytes
Variable Size 226496456 bytes
Database Buffers 595591168 bytes
Redo Buffers 3780608 bytes
sql statement: alter system set control_files = ''/u01/inst/ORCL/controlfile/o1_mf_fq23oj3h_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 834666496 bytes
Fixed Size 8798264 bytes
Variable Size 226496456 bytes
Database Buffers 595591168 bytes
Redo Buffers 3780608 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2018-08-13 12:52:03','yyyy-mm-dd hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 27 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 27;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 2018-08-13 12:54:35
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=34 device type=DISK
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 00027 to /u01/inst/FINA_PITR_PDBORCL2_ORCL/67AFB4BA944126D2E0536E0410ACB92F/datafile/o1_mf_admin_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/rman/ORCL_20180813_1ptaevt8_1_1.bak
channel ORA_AUX_DISK_1: piece handle=/u01/rman/ORCL_20180813_1ptaevt8_1_1.bak tag=TAG20180813T124814
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2018-08-13 12:54:36
datafile 27 switched to datafile copy
input datafile copy RECID=18 STAMP=984056077 file name=/u01/inst/FINA_PITR_PDBORCL2_ORCL/67AFB4BA944126D2E0536E0410ACB92F/datafile/o1_mf_admin_fq23rcy4_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2018-08-13 12:52:03','yyyy-mm-dd hh24:mi:ss')";
# online the datafiles restored or switched
sql clone 'PDBORCL2' "alter database datafile
27 online";
# recover and open resetlogs
recover clone database tablespace "PDBORCL2":"ADMIN", "SYSTEM", "UNDOTBS1", "SYSAUX", "PDBORCL2":"SYSTEM", "PDBORCL2":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 27 online
Starting recover at 2018-08-13 12:54:37
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 35 is already on disk as file /u01/arch/1_35_971088589.dbf
archived log file name=/u01/arch/1_35_971088589.dbf thread=1 sequence=35
media recovery complete, elapsed time: 00:00:00
Finished recover at 2018-08-13 12:54:37
database opened
contents of Memory Script:
{
sql clone 'alter pluggable database PDBORCL2 open';
}
executing Memory Script
sql statement: alter pluggable database PDBORCL2 open
contents of Memory Script:
{
# create directory for datapump import
sql 'PDBORCL2' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/u01/dump''";
# create directory for datapump export
sql clone 'PDBORCL2' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/u01/dump''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/dump''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/dump''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_Fina_zezj":
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> . . exported "ADMIN"."T1" 5.062 KB 3 rows
EXPDP> Master table "SYS"."TSPITR_EXP_Fina_zezj" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_Fina_zezj is:
EXPDP> /u01/dump/tspitr_Fina_70883.dmp
EXPDP> Job "SYS"."TSPITR_EXP_Fina_zezj" successfully completed at Mon Aug 13 12:55:09 2018 elapsed 0 00:00:19
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_Fina_Cpli" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_Fina_Cpli":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "ADMIN"."T1" 5.062 KB 3 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_Fina_Cpli" successfully completed at Mon Aug 13 12:55:27 2018 elapsed 0 00:00:13
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/inst/ORCL/67AFB4BA944126D2E0536E0410ACB92F/datafile/o1_mf_temp_fq23q0fj_.tmp deleted
auxiliary instance file /u01/inst/ORCL/datafile/o1_mf_temp_fq23pzdk_.tmp deleted
auxiliary instance file /u01/inst/FINA_PITR_PDBORCL2_ORCL/onlinelog/o1_mf_3_fq23rgso_.log deleted
auxiliary instance file /u01/inst/FINA_PITR_PDBORCL2_ORCL/onlinelog/o1_mf_2_fq23rfsh_.log deleted
auxiliary instance file /u01/inst/FINA_PITR_PDBORCL2_ORCL/onlinelog/o1_mf_1_fq23rfr4_.log deleted
auxiliary instance file /u01/inst/FINA_PITR_PDBORCL2_ORCL/67AFB4BA944126D2E0536E0410ACB92F/datafile/o1_mf_admin_fq23rcy4_.dbf deleted
auxiliary instance file /u01/inst/ORCL/67AFB4BA944126D2E0536E0410ACB92F/datafile/o1_mf_sysaux_fq23pgx3_.dbf deleted
auxiliary instance file /u01/inst/ORCL/67AFB4BA944126D2E0536E0410ACB92F/datafile/o1_mf_system_fq23pgw4_.dbf deleted
auxiliary instance file /u01/inst/ORCL/datafile/o1_mf_sysaux_fq23oovg_.dbf deleted
auxiliary instance file /u01/inst/ORCL/datafile/o1_mf_undotbs1_fq23ooth_.dbf deleted
auxiliary instance file /u01/inst/ORCL/datafile/o1_mf_system_fq23oov3_.dbf deleted
auxiliary instance file /u01/inst/ORCL/controlfile/o1_mf_fq23oj3h_.ctl deleted
auxiliary instance file tspitr_Fina_70883.dmp deleted
Finished recover at 2018-08-13 12:55:30
已恢复表完毕
6、验证数据
12:52:12 SQL> select * from t1;
ID
----------
3
4
5
12:56:03 SQL> select * from t2;
ID
----------
1
2
结果：RMAN已将表T1恢复到删除之前。
场景2 先truncate表，再drop表，用RMAN恢复表
1、先插入1条测试数据
12:56:10 SQL> insert into t1 values(6);
1 row created.
13:26:17 SQL> commit;
Commit complete.
13:26:17 SQL> select * from t1;
ID
----------
3
4
5
6
2、truncate表T1
13:26:21 SQL> truncate table t1;
Table truncated.
记录时间点13:26:45truncate执行完毕
3、drop表T1
13:26:45 SQL> drop table t1 purge;
Table dropped.
4、RMAN恢复
RMAN> RECOVER TABLE admin.t1 of pluggable database pdborcl2
UNTIL time "to_date('2018-08-13 13:26:45','yyyy-mm-dd hh24:mi:ss')"
#指定时间点truncate之前
AUXILIARY DESTINATION '/u01/inst'
datapump destination '/u01/dump';
5、查看表数据
13:26:59 SQL> select * from t1;
ID
----------
3
4
5
6
13:34:30 SQL> select * from t2;
ID
----------
1
2
结果：恢复成功
三、总结
1、12c的RMAN表级恢复可以恢复到被删除之前的任意时间点。假设表T1在drop之前，执行了truncate操作，表删除之后，可以恢复到truncate之前，其他表数据无影响。
2、表级恢复只能用cdb RMAN备份。
3、恢复期间会自动创建辅助实例，会还原system，sysaux，undo表空间，恢复目录需要有充足空间，最后通过数据泵把数据导入到原数据库。
2617
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
