oracle 12c 表恢复,ORACLE 12C使用RMAN进行表恢复

在12C以前的版本中rman可以进行db,tablespace,datafile,block级别和TSPITR的恢复,12C引入了table级别的恢复

在没有打开flashback的时候可以进行单个表的恢复.

例,创建表

SQL> >

Session>　　SQL> create table frank.t1 as select * from dba_tables;

Table created.

备份数据库

RMAN> backup database;

Starting backup at 16-JAN-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=265 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00003 name=/u01/app/oracle/oradata/c12/sysaux01.dbf

input datafile file number=00001 name=/u01/app/oracle/oradata/c12/system01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/c12/undotbs01.dbf

input datafile file number=00038 name=/home/oracle/t1.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/c12/users01.dbf

input datafile file number=00014 name=/u01/app/oracle/product/12.1.0/db_1/dbs/test

....................

查询当前数据库的scn号

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

4898443

SQL> drop table frank.t1;

[oracle@o12c ~]$ mkdir /home/oracle/abc

RMAN> recover table frank.t1 of pluggable database pdb2

2>  until scn 4898443

3>  auxiliary destination '/home/oracle/abc'

4>   datapump destination '/home/oracle/abc';

Starting recover at 16-JAN-14

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='vewD'

initialization parameters used for automatic instance:

db_name=C12

db_unique_name=vewD_pitr_pdb2_C12

compatible=12.1.0.0.0

db_block_size=8192

db_files=200

sga_target=1G

processes=80

diagnostic_dest=/u01/app/oracle

db_create_file_dest=/home/oracle/abc

log_archive_dest_1='location=/home/oracle/abc'

enable_pluggable_database=true

_clone_one_pdb_recovery=true

#No auxiliary parameter file used

starting up automatic instance C12

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed>

Variable>　　Database Buffers             780140544 bytes

Redo Buffers                   5480448 bytes

Automatic instance created

contents of Memory Script:

..............................................................

也是会新建一个auxiliary  instance,然后在auxiliary instance上做全库恢复再通过datapump工具导出和导入

整个过程和TSPITR极为相似.

恢复成功再次查询

SQL> select count(*) from frank.t1;

COUNT(*)

----------

2318

注意common user引用表的名称为 "c##frank".t1,而不是"c##frank.t1",否则会报以下错误

RMAN> recover table "c##frank.t1" until scn 4894140 auxiliary destination '/home/oracle/abc' datapump destination '/home/oracle/abc';

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00558: error encountered while parsing input commands

RMAN-01009: syntax error: found "until": expecting one of: "dot"

RMAN-01007: at line 1 column 29 file: standard input