确认在pdb下操作：

SQL> show con_name

CON_NAME

------------------------------

PDBA

创建一个表：

SQL> create table pdb (id number,tdate date);

插入部分数据：

SQL> select * from pdb;

ID TDATE

---------- -------------------

1 2014/12/12 16:14:28

2 2014/12/12 16:14:54

3 2014/12/12 16:14:58

4 2014/12/12 16:15:03

开始备份CDB：

[oracle@DBA12C01 ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Dec 12 16:45:38 2014

connected to target database: CDB (DBID=1982730304)

RMAN> select sysdate from dual;

using target database control file instead of recovery catalog

SYSDATE

-------------------

2014/12/12 16:45:56

RMAN> backup database format '/soft/backup/%U';

Starting backup at 2014/12/12 16:45:58

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=277 instance=CDB1 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=00012 name=+OCR_DATA/CDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/example.298.866127575

input datafile file number=00010 name=+OCR_DATA/CDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/sysaux.299.866127577

input datafile file number=00009 name=+OCR_DATA/CDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/system.300.866127577

input datafile file number=00011 name=+OCR_DATA/CDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/users.301.866127577

channel ORA_DISK_1: starting piece 1 at 2014/12/12 16:46:00

channel ORA_DISK_1: finished piece 1 at 2014/12/12 16:46:26

piece handle=/soft/backup/01pq09u8_1_1 tag=TAG20141212T164559 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=+OCR_DATA/CDB/DATAFILE/system.279.866126665

input datafile file number=00003 name=+OCR_DATA/CDB/DATAFILE/sysaux.278.866126609

input datafile file number=00004 name=+OCR_DATA/CDB/DATAFILE/undotbs1.281.866126731

input datafile file number=00008 name=+OCR_DATA/CDB/DATAFILE/undotbs2.292.866127243

input datafile file number=00006 name=+OCR_DATA/CDB/DATAFILE/users.280.866126731

channel ORA_DISK_1: starting piece 1 at 2014/12/12 16:46:26

channel ORA_DISK_1: finished piece 1 at 2014/12/12 16:47:02

piece handle=/soft/backup/02pq09v2_1_1 tag=TAG20141212T164559 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00007 name=+OCR_DATA/CDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/sysaux.289.866126811

input datafile file number=00005 name=+OCR_DATA/CDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/system.290.866126811

channel ORA_DISK_1: starting piece 1 at 2014/12/12 16:47:03

channel ORA_DISK_1: finished piece 1 at 2014/12/12 16:47:18

piece handle=/soft/backup/03pq0a07_1_1 tag=TAG20141212T164559 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

Finished backup at 2014/12/12 16:47:18

Starting Control File and SPFILE Autobackup at 2014/12/12 16:47:18

piece handle=+OCR_DATA/CDB/AUTOBACKUP/2014_12_12/s_866134038.304.866134039 comment=NONE

Finished Control File and SPFILE Autobackup at 2014/12/12 16:47:21

RMAN>

修改表数据：

SQL> select * from pdb;

ID TDATE

---------- -------------------

1 2014/12/12 16:14:28

2 2014/12/12 16:14:54

3 2014/12/12 16:14:58

4 2014/12/12 16:15:03

5 2014/12/12 16:51:02

6 2014/12/12 16:51:06

7 2014/12/12 16:51:12

8 2014/12/12 16:51:17

8 rows selected.

SQL> show con_name

CON_NAME

------------------------------

PDBA

删除表后进行恢复：

SQL> drop table pdb purge;

Table dropped.

SQL>

RMAN> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

-------------------

2014-12-12 17:07:35

RMAN> recover table pdb.pdb of pluggable database pdba until time "2014-12-12 17:07:35" auxiliary destination '/soft/auxi' datapump destination '/soft/pdump' notableimport;

recover table pdb.pdb of pluggable database pdba until time " to_char(‘2014-12-15 09:39:31’,'yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/soft/auxi' datapump destination '/soft/pdump' notableimport;

RMAN> recover table pdb.pdb of pluggable database pdba until time "to_char('2014-12-15 09:39:31','yyyy-mm-dd hh24:mi:ss')" ^C

user interrupt received

RMAN> recover table pdb.pdb of pluggable database pdba until time '2014-12-15 09:39:31' auxiliary destination '/soft/auxi' datapump destination '/soft/pdump' notableimport;

Starting recover at 2014/12/15 09:43:56

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

Tablespace UNDOTBS2

Creating automatic instance, with SID='fkdm'

initialization parameters used for automatic instance:

db_name=CDB

db_unique_name=fkdm_pitr_pdba_CDB

compatible=12.1.0.2.0

db_block_size=8192

db_files=200

diagnostic_dest=/oracle/app/db

_system_trig_enabled=FALSE

sga_target=2480M

processes=200

db_create_file_dest=/soft/auxi

log_archive_dest_1='location=/soft/auxi'

enable_pluggable_database=true

_clone_one_pdb_recovery=true

#No auxiliary parameter file used

starting up automatic instance CDB

Oracle instance started

Total System Global Area 2600468480 bytes

Fixed Size 2927768 bytes

Variable Size 570426216 bytes

Database Buffers 2013265920 bytes

Redo Buffers 13848576 bytes

Automatic instance created

contents of Memory Script:

{

# set requested point in time

set until time "2014-12-15 09:39:31";

# 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 2014/12/15 09:44:43

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=162 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 +OCR_DATA/CDB/AUTOBACKUP/2014_12_15/s_866366807.334.866366809

channel ORA_AUX_DISK_1: piece handle=+OCR_DATA/CDB/AUTOBACKUP/2014_12_15/s_866366807.334.866366809 tag=TAG20141215T092647

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:46

output file name=/soft/auxi/CDB/controlfile/o1_mf_b8wh9w5r_.ctl

Finished restore at 2014/12/15 09:45:34

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 "2014-12-15 09:39:31";

# 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 8 to new;

set newname for clone datafile 3 to new;

set newname for clone datafile 9 to new;

set newname for clone datafile 10 to new;

set newname for clone tempfile 1 to new;

set newname for clone tempfile 3 to new;

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 1, 4, 8, 3, 9, 10;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed tempfile 1 to /soft/auxi/CDB/datafile/o1_mf_temp_%u_.tmp in control file

renamed tempfile 3 to /soft/auxi/CDB/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 2014/12/15 09:45:51

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 /soft/auxi/CDB/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /soft/auxi/CDB/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00008 to /soft/auxi/CDB/datafile/o1_mf_undotbs2_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /soft/auxi/CDB/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /soft/backup/05pq7cl8_1_1

channel ORA_AUX_DISK_1: piece handle=/soft/backup/05pq7cl8_1_1 tag=TAG20141215T091520

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:39

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 00009 to /soft/auxi/CDB/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00010 to /soft/auxi/CDB/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /soft/backup/09pq7d9t_1_1

channel ORA_AUX_DISK_1: piece handle=/soft/backup/09pq7d9t_1_1 tag=TAG20141215T092621

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16

Finished restore at 2014/12/15 09:46:48

datafile 1 switched to datafile copy

input datafile copy RECID=13 STAMP=866368010 file name=/soft/auxi/CDB/datafile/o1_mf_system_b8whbjm5_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=14 STAMP=866368010 file name=/soft/auxi/CDB/datafile/o1_mf_undotbs1_b8whbjm7_.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=15 STAMP=866368010 file name=/soft/auxi/CDB/datafile/o1_mf_undotbs2_b8whbjmc_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=16 STAMP=866368010 file name=/soft/auxi/CDB/datafile/o1_mf_sysaux_b8whbjlw_.dbf

datafile 9 switched to datafile copy

input datafile copy RECID=17 STAMP=866368010 file name=/soft/auxi/CDB/datafile/o1_mf_system_b8whcrq5_.dbf

datafile 10 switched to datafile copy

input datafile copy RECID=18 STAMP=866368010 file name=/soft/auxi/CDB/datafile/o1_mf_sysaux_b8whcrpx_.dbf

contents of Memory Script:

{

# set requested point in time

set until time "2014-12-15 09:39:31";

# 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 8 online";

sql clone "alter database datafile 3 online";

sql clone 'PDBA' "alter database datafile

9 online";

sql clone 'PDBA' "alter database datafile

10 online";

# recover and open database read only

recover clone database tablespace "SYSTEM", "UNDOTBS1", "UNDOTBS2", "SYSAUX", "PDBA":"SYSTEM", "PDBA":"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 4 online

sql statement: alter database datafile 8 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 9 online

sql statement: alter database datafile 10 online

Starting recover at 2014/12/15 09:46:52

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 47 is already on disk as file +OCR_DATA/CDB/ARCHIVELOG/2014_12_15/thread_1_seq_47.333.866367837

archived log for thread 2 with sequence 16 is already on disk as file +OCR_DATA/CDB/ARCHIVELOG/2014_12_15/thread_2_seq_16.330.866367839

archived log file name=+OCR_DATA/CDB/ARCHIVELOG/2014_12_15/thread_1_seq_47.333.866367837 thread=1 sequence=47

archived log file name=+OCR_DATA/CDB/ARCHIVELOG/2014_12_15/thread_2_seq_16.330.866367839 thread=2 sequence=16

media recovery complete, elapsed time: 00:00:01

Finished recover at 2014/12/15 09:46:58

sql statement: alter database open read only

contents of Memory Script:

{

sql clone 'alter pluggable database PDBA open read only';

}

executing Memory Script

sql statement: alter pluggable database PDBA 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 =

''/soft/auxi/CDB/controlfile/o1_mf_b8wh9w5r_.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 2600468480 bytes

Fixed Size 2927768 bytes

Variable Size 587203432 bytes

Database Buffers 1996488704 bytes

Redo Buffers 13848576 bytes

sql statement: alter system set control_files = ''/soft/auxi/CDB/controlfile/o1_mf_b8wh9w5r_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 2600468480 bytes

Fixed Size 2927768 bytes

Variable Size 587203432 bytes

Database Buffers 1996488704 bytes

Redo Buffers 13848576 bytes

sql statement: alter database mount clone database

contents of Memory Script:

{

# set requested point in time

set until time "2014-12-15 09:39:31";

# set destinations for recovery set and auxiliary set datafiles

set newname for datafile 11 to new;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 11;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 2014/12/15 09:48:28

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=181 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 00011 to /soft/auxi/FKDM_PITR_PDBA_CDB/datafile/o1_mf_users_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /soft/backup/09pq7d9t_1_1

channel ORA_AUX_DISK_1: piece handle=/soft/backup/09pq7d9t_1_1 tag=TAG20141215T092621

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 2014/12/15 09:48:31

datafile 11 switched to datafile copy

input datafile copy RECID=20 STAMP=866368111 file name=/soft/auxi/FKDM_PITR_PDBA_CDB/datafile/o1_mf_users_b8whhgql_.dbf

contents of Memory Script:

{

# set requested point in time

set until time "2014-12-15 09:39:31";

# online the datafiles restored or switched

sql clone 'PDBA' "alter database datafile

11 online";

# recover and open resetlogs

recover clone database tablespace "PDBA":"USERS", "SYSTEM", "UNDOTBS1", "UNDOTBS2", "SYSAUX", "PDBA":"SYSTEM", "PDBA":"SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 11 online

Starting recover at 2014/12/15 09:48:32

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 47 is already on disk as file +OCR_DATA/CDB/ARCHIVELOG/2014_12_15/thread_1_seq_47.333.866367837

archived log for thread 2 with sequence 16 is already on disk as file +OCR_DATA/CDB/ARCHIVELOG/2014_12_15/thread_2_seq_16.330.866367839

archived log file name=+OCR_DATA/CDB/ARCHIVELOG/2014_12_15/thread_1_seq_47.333.866367837 thread=1 sequence=47

archived log file name=+OCR_DATA/CDB/ARCHIVELOG/2014_12_15/thread_2_seq_16.330.866367839 thread=2 sequence=16

media recovery complete, elapsed time: 00:00:01

Finished recover at 2014/12/15 09:48:35

database opened

contents of Memory Script:

{

sql clone 'alter pluggable database PDBA open';

}

executing Memory Script

sql statement: alter pluggable database PDBA open

contents of Memory Script:

{

# create directory for datapump import

sql 'PDBA' "create or replace directory

TSPITR_DIROBJ_DPDIR as ''

/soft/pdump''";

# create directory for datapump export

sql clone 'PDBA' "create or replace directory

TSPITR_DIROBJ_DPDIR as ''

/soft/pdump''";

}

executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/soft/pdump''

Performing export of tables...

EXPDP> Starting "SYS"."TSPITR_EXP_fkdm_jruu":

EXPDP> Estimate in progress using BLOCKS method...

EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

EXPDP> Total estimation using BLOCKS method: 64 KB

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> ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_ORDERS_QUEUETABLE_S','IX',1,1,'12.01.00.02.00',newblock)

ORA-00376: file 12 cannot be read at this time

ORA-01110: data file 12: '+OCR_DATA/CDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/example.298.866127575'

ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197

ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_METADATA", line 10261

ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_STREAMS_QUEUE_TABLE_S','IX',1,1,'12.01.00.02.00',newblock)

ORA-00376: file 12 cannot be read at this time

ORA-01110: data file 12: '+OCR_DATA/CDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/example.298.866127575'

ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197

ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_METADATA", line 10261

EXPDP> . . exported "PDB"."PDB" 5.554 KB 6 rows

EXPDP> Master table "SYS"."TSPITR_EXP_fkdm_jruu" successfully loaded/unloaded

EXPDP> ******************************************************************************

EXPDP> Dump file set for SYS.TSPITR_EXP_fkdm_jruu is:

EXPDP> /soft/pdump/tspitr_fkdm_34183.dmp

EXPDP> Job "SYS"."TSPITR_EXP_fkdm_jruu" completed with 2 error(s) at Mon Dec 15 09:50:39 2014 elapsed 0 00:00:37

Export completed

Not performing table import after point-in-time recovery

Removing automatic instance

shutting down automatic instance

Oracle instance shut down

Automatic instance removed

auxiliary instance file /soft/auxi/CDB/datafile/o1_mf_temp_b8whdvb3_.tmp deleted

auxiliary instance file /soft/auxi/CDB/datafile/o1_mf_temp_b8whdml6_.tmp deleted

auxiliary instance file /soft/auxi/FKDM_PITR_PDBA_CDB/onlinelog/o1_mf_4_b8whho2j_.log deleted

auxiliary instance file /soft/auxi/FKDM_PITR_PDBA_CDB/onlinelog/o1_mf_3_b8whhns4_.log deleted

auxiliary instance file /soft/auxi/FKDM_PITR_PDBA_CDB/onlinelog/o1_mf_2_b8whhngx_.log deleted

auxiliary instance file /soft/auxi/FKDM_PITR_PDBA_CDB/onlinelog/o1_mf_1_b8whhn4s_.log deleted

auxiliary instance file /soft/auxi/FKDM_PITR_PDBA_CDB/datafile/o1_mf_users_b8whhgql_.dbf deleted

auxiliary instance file /soft/auxi/CDB/datafile/o1_mf_sysaux_b8whcrpx_.dbf deleted

auxiliary instance file /soft/auxi/CDB/datafile/o1_mf_system_b8whcrq5_.dbf deleted

auxiliary instance file /soft/auxi/CDB/datafile/o1_mf_sysaux_b8whbjlw_.dbf deleted

auxiliary instance file /soft/auxi/CDB/datafile/o1_mf_undotbs2_b8whbjmc_.dbf deleted

auxiliary instance file /soft/auxi/CDB/datafile/o1_mf_undotbs1_b8whbjm7_.dbf deleted

auxiliary instance file /soft/auxi/CDB/datafile/o1_mf_system_b8whbjm5_.dbf deleted

auxiliary instance file /soft/auxi/CDB/controlfile/o1_mf_b8wh9w5r_.ctl deleted

Finished recover at 2014/12/15 09:50:47

RMAN>

[oracle@DBA12C01 pdump]$ ls

tspitr_fkdm_34183.dmp

[oracle@DBA12C01 pdump]$ pwd

/soft/pdump

[oracle@DBA12C01 pdump]$

SQL> select * from pdb;

select * from pdb

*

ERROR at line 1:

ORA-00942: table or view does not exist

ITPub博客

Oracle 12C 新特性：Rman的单个表恢复测试--未导入系统

最新文章