12c rman-4 online recover table

测试表信息

[oracle@oel12c1 tmp]$ sqlplus blue/blue@pdbrac1
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 14 12:43:47 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Wed Jan 14 2015 11:15:15 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select count(*) from p_blue;

  COUNT(*)
----------
     90943

备份数据库

RMAN> backup database plus archivelog;

获取备份后的SCN
SQL> select dbms_flashback.get_system_change_number scn from dual;  

       SCN
----------
   3838751

Drop掉测试表
SQL> drop table p_blue purge;

Table dropped.

恢复

Mkdir /backup/recover

RMAN> recover table blue.p_blue of pluggable database pdbrac until scn 3838751 auxiliary destination '/backup/recover';

Starting recover at 14-JAN-15
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time
Mkdir /backup/recover

RMAN> recover table blue.p_blue of pluggable database pdbrac until scn 3838751 auxiliary destination '/backup/recover';

Starting recover at 14-JAN-15
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
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
Tablespace UNDOTBS2

Creating automatic instance, with SID='iAoE'

......省略
set until  scn 3838751;
......省略

# create directory for datapump import
sql 'PDBRAC' "create or replace directory 
TSPITR_DIROBJ_DPDIR as ''
/backup/recover''";
# create directory for datapump export
sql clone 'PDBRAC' "create or replace directory 
TSPITR_DIROBJ_DPDIR as ''
/backup/recover''";
}
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/backup/recover''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/backup/recover''

Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_iAoE_kqnq":  
   EXPDP> Estimate in progress using BLOCKS method...
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Total estimation using BLOCKS method: 24 MB
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   EXPDP> . . exported "BLUE"."P_BLUE":"P1"                        202.4 KB    4997 rows
   EXPDP> . . exported "BLUE"."P_BLUE":"P2"                        210.9 KB    4955 rows
   EXPDP> . . exported "BLUE"."P_BLUE":"P3"                        4.003 MB   80991 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_iAoE_kqnq" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_iAoE_kqnq is:
   EXPDP>   /backup/recover/tspitr_iAoE_90225.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_iAoE_kqnq" successfully completed at Wed Jan 14 13:08:12 2015 elapsed 0 00:01:07
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 "SYSBACKUP"."TSPITR_IMP_iAoE_rmop" successfully loaded/unloaded
   IMPDP> Starting "SYSBACKUP"."TSPITR_IMP_iAoE_rmop":  
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "BLUE"."P_BLUE":"P1"                        202.4 KB    4997 rows
   IMPDP> . . imported "BLUE"."P_BLUE":"P2"                        210.9 KB    4955 rows
   IMPDP> . . imported "BLUE"."P_BLUE":"P3"                        4.003 MB   80991 rows
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   IMPDP> Job "SYSBACKUP"."TSPITR_IMP_iAoE_rmop" successfully completed at Wed Jan 14 13:12:06 2015 elapsed 0 00:03:08
Import completed


Removing automatic instance
Automatic instance removed
auxiliary instance file /backup/recover/CDBRAC/datafile/o1_mf_temp_bccy0411_.tmp deleted
auxiliary instance file /backup/recover/CDBRAC/datafile/o1_mf_temp_bccxzvtf_.tmp deleted
auxiliary instance file /backup/recover/IAOE_PITR_PDBRAC_CDBRAC/onlinelog/o1_mf_4_bccy4fr2_.log deleted
auxiliary instance file /backup/recover/IAOE_PITR_PDBRAC_CDBRAC/onlinelog/o1_mf_3_bccy4dmr_.log deleted
auxiliary instance file /backup/recover/IAOE_PITR_PDBRAC_CDBRAC/onlinelog/o1_mf_2_bccy4crm_.log deleted
auxiliary instance file /backup/recover/IAOE_PITR_PDBRAC_CDBRAC/onlinelog/o1_mf_1_bccy4bnl_.log deleted
auxiliary instance file /backup/recover/IAOE_PITR_PDBRAC_CDBRAC/datafile/o1_mf_tbs_12c_bccy2fjo_.dbf deleted
auxiliary instance file /backup/recover/CDBRAC/datafile/o1_mf_sysaux_bccxpd69_.dbf deleted
auxiliary instance file /backup/recover/CDBRAC/datafile/o1_mf_system_bccxpdh0_.dbf deleted
auxiliary instance file /backup/recover/CDBRAC/datafile/o1_mf_sysaux_bccxs14w_.dbf deleted
auxiliary instance file /backup/recover/CDBRAC/datafile/o1_mf_undotbs2_bccxpdqh_.dbf deleted
auxiliary instance file /backup/recover/CDBRAC/datafile/o1_mf_undotbs1_bccxpd9s_.dbf deleted
auxiliary instance file /backup/recover/CDBRAC/datafile/o1_mf_system_bccxpctj_.dbf deleted
auxiliary instance file /backup/recover/CDBRAC/controlfile/o1_mf_bccxo2st_.ctl deleted
auxiliary instance file tspitr_iAoE_90225.dmp deleted
Finished recover at 14-JAN-15

恢复完成进行验证

SQL> select count(*) from p_blue;

  COUNT(*)
----------
     90943

小节

在整个恢复过程中,数据库会创建一个随机的实例名来进行恢复,本次实验的为iAoE

[oracle@oel12c1 backup]$ ps -ef | grep smon
oracle    3740     1  0 10:23 ?        00:00:00 asm_smon_+ASM1
root      3893     1  0 10:23 ?        00:01:20 /u01/app/12.1.0.2/grid/bin/osysmond.bin
oracle    4916     1  0 10:24 ?        00:00:01 ora_smon_cdbrac1
oracle   22300     1  0 13:01 ?        00:00:00 ora_smon_iAoE
oracle   22378  5158  0 13:01 pts/3    00:00:00 grep smon

在恢复完成之后会被清理掉

恢复过程中会在/backup/recover下生成一些数据库文件和最后的expdp生成的文件

[oracle@oel12c1 recover]$ ls -l
total 4708
drwxr-x--- 5 oracle oinstall    4096 Jan 14 12:10 CDBRAC
drwxr-x--- 4 oracle oinstall    4096 Jan 14 13:03 IAOE_PITR_PDBRAC_CDBRAC
-rw-r----- 1 oracle oinstall 4800512 Jan 14 13:08 tspitr_iAoE_90225.dmp

单一的pluggable database备份无法进行上述的表恢复,因为无法构建一个auxiliary instance。

Test做为数据库的reserve word,初次的实验是在test用户下的t1表,报错如下,test和t1均加上双引号也不可以。
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/14/2015 12:28:17
RMAN-05063: Cannot recover specified tables
RMAN-05057: Table test.t1 not found

SQL> select * from GV$RESERVED_WORDS where keyword like 'TEST';

   INST_ID KEYWORD                            LENGTH R R R R D     CON_ID
---------- ------------------------------ ---------- - - - - - ----------
         1 TEST                                    4 N N N N N          0
此条目发表在rman分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s