在12c之前对于用户因DDL(DROP TRUNCATE )等操作引起的数据丢失，一般通过数据库时间点不完全恢复、基于表空间的时间点恢复(TSPITR)、闪回技术。
12c推出了rman基于时间点对表的恢复。原理如下：
Recover Table新特性是利用创建辅助临时实例加数据泵工具来实现的。通常在进行Recover Table之前应该准备好两个目录(AUXILIARY DESTINATION和DATAPUMP DESTINATION)，AUXILIARY DESTINATION用来临时存放辅助实例的数据文件，DATAPUMP DESTINATION用来临时存放数据泵导出的文件。
一：测试过程如下：
1.
SQL> set lines 60
SQL> desc tb ;
Name Null? Type
----------------------------- -------- --------------------
OWNER VARCHAR2(128)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED DATE
LAST_DDL_TIME DATE
SQL> insert into tb select * from tb ;
72633 rows created.
SQL> commit ;
Commit complete.
SQL>
SQL> conn / as sysdba
Connected.
2.切换日志使数据写到磁盘上。
SQL> alter system switch logfile ;
3.rman备份数据库
Recovery Manager: Release 12.2.0.1.0 - Production on Fri Jun 29 11:41:03 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: MISDB (DBID=4279250949)
RMAN> run
2> {
3> crosscheck archivelog all;
4> crosscheck backup;
5> delete noprompt obsolete;
6> delete noprompt expired backup;
7> allocate channel d1 type disk ;
8> allocate channel d2 type disk ;
9> backup format '/u01/bk/%T_%d_%s_%p.dat' tag 'full_data' database ;
10> backup format '/u01/bk/%T_%d_%s_%p.arc' tag 'full_arc' archivelog all delete all input ;
11> backup format '/u01/bk/%T_%d_%s_%p.ctl' tag 'full_ctl' current controlfile;
12> release channel d1;
13> release channel d2;
14> };
released channel: d1
released channel: d2
RMAN>
SQL> select count(*) from rita.tb ;
COUNT(*)
----------
145266
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2074690
4. 删除表
SQL> drop table rita.tb purge;
SQL> select count(*) from rita.tb ;
select count(*) from rita.tb
*
ERROR at line 1:
ORA-00942: table or view does not exist
5.建辅助目录
[oracle@host01 u01]$ mkdir recover/
[oracle@host01 u01]$ mkdir dumpfiles
[oracle@host01 u01]$ ls
app bk dumpfiles lost+found recover
[oracle@host01 u01]$ cd dumpfiles/
[oracle@host01 dumpfiles]$ ls
[oracle@host01 dumpfiles]$ cd ..
[oracle@host01 u01]$
6.查看数据库备份：
list backup of database;
connected to target database: MISDB (DBID=4279250949)
RMAN>
using target database control file instead of recovery catalog
7.恢复删除的表：
rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Fri Jun 29 15:22:20 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: MISDB (DBID=4279250949)
RMAN> run{
2> RECOVER TABLE rita.tb
3> UNTIL SCN 2074690
4> AUXILIARY DESTINATION '/u01/recover'
5> datapump destination '/u01/dumpfiles';
6> };
Starting recover at 29-JUN-18
using target database control file instead of recovery catalog
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 instance=misdb1 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='ygBi'自动创建一个数据库实例
oracle@host01 datafile]$ ps -ef |grep smon
grid 5050 1 0 14:57 ? 00:00:00 asm_smon_+ASM1
root 5144 1 1 14:58 ? 00:00:30 /u01/app/12.2.0/grid/bin/osysmond.bin
oracle 7730 1 0 15:00 ? 00:00:00 ora_smon_misdb1
oracle 36939 1 0 15:28 ? 00:00:00 ora_smon_ygBi
reating automatic instance, with SID='ygBi'
initialization parameters used for automatic instance:
db_name=MISDB
db_unique_name=ygBi_pitr_MISDB
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=1728M
processes=200
db_create_file_dest=/u01/recover
log_archive_dest_1='location=/u01/recover'---这个空间要足够大
#No auxiliary parameter file used
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=980090756 file name=/u01/recover/MISDB/datafile/o1_mf_system_fmcqnsc8_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=980090756 file name=/u01/recover/MISDB/datafile/o1_mf_undotbs1_fmcqpx2n_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=980090756 file name=/u01/recover/MISDB/datafile/o1_mf_sysaux_fmcqpx17_.dbf
contents of Memory Script:
{
# set requested point in time
set until scn 2074690;
# 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";
# recover and open database read only
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX";
sql clone 'alter database open read only';
。。。
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/dumpfiles''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/dumpfiles''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/dumpfiles''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/dumpfiles''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_ygBi_gcph":
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 "RITA"."TB" 19.21 MB 145266 rows
EXPDP> Master table "SYS"."TSPITR_EXP_ygBi_gcph" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_ygBi_gcph is:
EXPDP> /u01/dumpfiles/tspitr_ygBi_13106.dmp
EXPDP> Job "SYS"."TSPITR_EXP_ygBi_gcph" successfully completed at Fri Jun 29 15:31:30 2018 elapsed 0 00:01:09
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_ygBi_yzeq" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_ygBi_yzeq":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "RITA"."TB" 19.21 MB 145266 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_ygBi_yzeq" successfully completed at Fri Jun 29 15:32:44 2018 elapsed 0 00:00:41
Import completed
二：从上边的例子可以看出，恢复大体流程是
1.rman 根据指定的表确定备份。
2.rman 根据指定的recover 路径恢复一个辅助实例，恢复到指定的时间点。
Creating automatic instance, with SID='ygBi'
initialization parameters used for automatic instance:
db_name=MISDB
db_unique_name=ygBi_pitr_MISDB
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 scn 2074690;
# 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;
3.用恢复好的辅助实例，数据库导出在导入生产环境
EXPDP> Starting "SYS"."TSPITR_EXP_ygBi_gcph":
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
Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_ygBi_yzeq" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_ygBi_yzeq":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "RITA"."TB" 19.21 MB 145266 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_ygBi_yzeq" successfully completed at Fri Jun 29 15:32:44 2018 elapsed 0 00:00:41
Import completed
4.删除辅助实例
恢复完成。
三：RECOVER TABLE需要注意的几个问题：
1.目标数据库必须被置于读写模式。
2.目标数据库必须被置于归档模式。
3.SYS用户下的表或分区无法恢复。
4.存储于SYSAUX和SYSTEM表空间下的表和分区无法恢复。
5.Standby数据库上的表或表分区不能进行恢复。
6.在执行“RECOVER TABLE”命令时，可以根据需要在以下三种级别指定时间：
(1)SCN号
(2)Sequence number(日志序列号)
(3)Time：根据NLS_LANG和NLS_DATE_FORMAT环境变量中的格式来指定时间，也可以用SYSDATE，比如"SYSDATE-30"、"to_date('2018-06-29:17:51:48','yyyy-mm-dd hh24:mi:ss')"
参考文档
1527
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
