Recovering Pluggable Database (PDB) table in Container Database (CDB)

Connect to the PDB and create a test table

SQL> connect HR/Password#123@pdb1

Connected.

SQL> set echo on

SQL> create table EMPLOYESS_TEST as select * from EMPLOYEES;

Table created.

SQL> select count (*) from EMPLOYESS_TEST;

COUNT(*)

----------

107

SQL> alter session set container=pdb1;

Session altered.

SQL> select timestamp_to_scn(sysdate) from v$database;

TIMESTAMP_TO_SCN(SYSDATE)

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

3337108

SQL> SELECT NAME, CURRENT_SCN FROM V$DATABASE;

NAME CURRENT_SCN

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

TESTDB03 3337119

NOTE this SCN which will be used to recover the table.

Take an RMAN Backup of the PDB

SQL> ho

[oracle@TESTDB03 ~]$ rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Sat Mar 3 23:33:06 2018

Version 18.1.0.0.0

connected to target database: TESTDB03 (DBID=2911931598)

RMAN> backup pluggable database pdb1;

Starting backup at 03-MAR-18

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=32 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=00013 name=/u02/app/oracle/oradata/TESTDB03/PDB1/sysaux01.dbf

input datafile file number=00012 name=/u02/app/oracle/oradata/TESTDB03/PDB1/system01.dbf

input datafile file number=00014 name=/u02/app/oracle/oradata/TESTDB03/PDB1/undotbs01.dbf

input datafile file number=00017 name=/u02/app/oracle/oradata/TESTDB03/PDB1/example01.dbf

input datafile file number=00015 name=/u02/app/oracle/oradata/TESTDB03/PDB1/PDB1_users01.dbf

channel ORA_DISK_1: starting piece 1 at 03-MAR-18

channel ORA_DISK_1: finished piece 1 at 03-MAR-18

piece handle=/u03/app/oracle/fast_recovery_area/TESTDB03/6675B1D171002866E0536ED2C40A4BAA/backupset/2018_03_03/o1_mf_nnndf_TAG20180303T233353_f9pd738l_.bkp tag=TAG20180303T233353 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35

Finished backup at 03-MAR-18

Starting Control File and SPFILE Autobackup at 03-MAR-18

piece handle=/u03/app/oracle/fast_recovery_area/TESTDB03/autobackup/2018_03_03/o1_mf_s_969838529_f9pdb29s_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 03-MAR-18

RMAN> list backup of pluggable database PDB1;

using target database control file instead of recovery catalog

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

13 Full 294.50M SBT_TAPE 00:00:08 02-MAR-18

BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20180302T225452

Handle: 1kssqd5t_1_1 Media: nswedu.storage.oraclecloud.com/v1/Storage-nswedu/DBaaS

List of Datafiles in backup set 13

Container ID: 4, PDB Name: PDB1

File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name

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

12 Full 2924084 02-MAR-18 NO /u02/app/oracle/oradata/TESTDB03/PDB1/system01.dbf

----

DROP Table from Pluggable Database (PDB1)

SQL> connect HR/Password#123@pdb1

Connected.

SQL> drop table EMPLOYESS_TEST purge;

Table dropped.

SQL> select count (*) from EMPLOYESS_TEST;

select count (*) from EMPLOYESS_TEST

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> ALTER SYSTEM SWITCH logfile;

System altered.

SQL> ALTER SYSTEM checkpoint;

System altered.

SQL> SELECT NAME, CURRENT_SCN FROM V$DATABASE;

NAME CURRENT_SCN

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

TESTDB03 3283566

SQL> ho

[oracle@TESTDB03 ~]$ rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Sat Mar 3 23:47:01 2018

Version 18.1.0.0.0

connected to target database: TESTDB03 (DBID=2911931598)

RMAN> list backup of pluggable database PDB1;

using target database control file instead of recovery catalog

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

13 Full 294.50M SBT_TAPE 00:00:08 02-MAR-18

BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20180302T225452

Handle: 1kssqd5t_1_1 Media: nswedu.storage.oraclecloud.com/v1/Storage-nswedu/DBaaS

List of Datafiles in backup set 13

Container ID: 4, PDB Name: PDB1

File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name

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

12 Full 2924084 02-MAR-18 NO /u02/app/oracle/oradata/TESTDB03/PDB1/system01.dbf

----

RECOVER THE DROPPED TABLE

Check hat the directory of the auxiliary destination is empty

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

RMAN> HOST "ls /u02/app/oracle/oradata/TESTDB03/restab/*";

RMAN> RECOVER TABLE HR.EMPLOYESS_TEST of pluggable database pdb1 UNTIL SCN 3337119

AUXILIARY DESTINATION '/u02/app/oracle/oradata/TESTDB03/restab';

Where,

• Names of tables to be recovered [HR.EMPLOYESS_TEST]

• SCN (or point in time) to which the tables or table partitions need to be recovered [3337119]

• Whether the recovered tables or table partitions must be imported into the target database (default is Yes.)

• Auxiliary destination '/u02/app/oracle/oradata/TESTDB03/restab''.

First, confirm that the directory of the auxiliary destination is empty and then execute your RECOVER command

OUTPUT:

RMAN> RECOVER TABLE HR.EMPLOYESS_TEST of pluggable database pdb1 UNTIL SCN 3337119

AUXILIARY DESTINATION '/u02/app/oracle/oradata/TESTDB03/restab';2>

Starting recover at 04-MAR-18

using channel ORA_SBT_TAPE_1

using channel ORA_SBT_TAPE_2

using channel ORA_SBT_TAPE_3

using channel ORA_SBT_TAPE_4

using channel ORA_SBT_TAPE_5

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 PDB1:SYSTEM

Tablespace UNDOTBS1

Tablespace PDB1:UNDOTBS1

Creating automatic instance, with SID='mxtb'

initialization parameters used for automatic instance:

db_name=TESTDB03

db_unique_name=mxtb_pitr_pdb1_TESTDB03

compatible=18.0.0

db_block_size=8192

db_files=500

diagnostic_dest=/u01/app/oracle

_system_trig_enabled=FALSE

db_domain=578785016.oraclecloud.internal

sga_target=2640M

processes=200

db_create_file_dest=/u02/app/oracle/oradata/TESTDB03/restab

log_archive_dest_1='location=/u02/app/oracle/oradata/TESTDB03/restab'

enable_pluggable_database=true

_clone_one_pdb_recovery=true

#No auxiliary parameter file used

starting up automatic instance TESTDB03

Oracle instance started

Total System Global Area 2768239632 bytes

Fixed Size 8899600 bytes

Variable Size 587202560 bytes

Database Buffers 2164260864 bytes

Redo Buffers 7876608 bytes

Automatic instance created

contents of Memory Script:

{

# set requested point in time

set until scn 3337119;

# 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 04-MAR-18

allocated channel: ORA_AUX_SBT_TAPE_1

channel ORA_AUX_SBT_TAPE_1: SID=20 device type=SBT_TAPE

channel ORA_AUX_SBT_TAPE_1: Oracle Database Backup Service Library VER=12.2.0.1

allocated channel: ORA_AUX_SBT_TAPE_2

channel ORA_AUX_SBT_TAPE_2: SID=182 device type=SBT_TAPE

channel ORA_AUX_SBT_TAPE_2: Oracle Database Backup Service Library VER=12.2.0.1

allocated channel: ORA_AUX_SBT_TAPE_3

channel ORA_AUX_SBT_TAPE_3: SID=21 device type=SBT_TAPE

channel ORA_AUX_SBT_TAPE_3: Oracle Database Backup Service Library VER=12.2.0.1

allocated channel: ORA_AUX_SBT_TAPE_4

channel ORA_AUX_SBT_TAPE_4: SID=183 device type=SBT_TAPE

channel ORA_AUX_SBT_TAPE_4: Oracle Database Backup Service Library VER=12.2.0.1

allocated channel: ORA_AUX_SBT_TAPE_5

channel ORA_AUX_SBT_TAPE_5: SID=22 device type=SBT_TAPE

channel ORA_AUX_SBT_TAPE_5: Oracle Database Backup Service Library VER=12.2.0.1

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=184 device type=DISK

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore

channel ORA_AUX_SBT_TAPE_1: restoring control file

channel ORA_AUX_SBT_TAPE_1: reading from backup piece c-2911931598-20180303-a2

channel ORA_AUX_SBT_TAPE_1: piece handle=c-2911931598-20180303-a2 tag=TAG20180303T233037

channel ORA_AUX_SBT_TAPE_1: restored backup piece 1

channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:03

output file name=/u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/controlfile/o1_mf_f9pftmk6_.ctl

Finished restore at 04-MAR-18

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 3337119;

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile 1 to new;

set newname for clone datafile 12 to new;

set newname for clone datafile 4 to new;

set newname for clone datafile 14 to new;

set newname for clone datafile 3 to new;

set newname for clone datafile 13 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, 12, 4, 14, 3, 13;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed tempfile 1 to /u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/datafile/o1_mf_temp_%u_.tmp in control file

renamed tempfile 4 to /u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/6675B1D171002866E0536ED2C40A4BAA/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 04-MAR-18

using channel ORA_AUX_SBT_TAPE_1

using channel ORA_AUX_SBT_TAPE_2

using channel ORA_AUX_SBT_TAPE_3

using channel ORA_AUX_SBT_TAPE_4

using channel ORA_AUX_SBT_TAPE_5

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: restoring datafile 00001

input datafile copy RECID=30 STAMP=969795018 file name=/u03/app/oracle/fast_recovery_area/TESTDB03/datafile/o1_mf_system_f9mv73jm_.dbf

destination for restore of datafile 00001: /u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: copied datafile copy of datafile 00001, elapsed time: 00:00:07

output file name=/u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/datafile/o1_mf_system_f9pftztl_.dbf RECID=36 STAMP=969840100

channel ORA_AUX_DISK_1: restoring datafile 00004

input datafile copy RECID=29 STAMP=969795015 file name=/u03/app/oracle/fast_recovery_area/TESTDB03/datafile/o1_mf_undotbs1_f9mv8kcd_.dbf

destination for restore of datafile 00004: /u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: copied datafile copy of datafile 00004, elapsed time: 00:00:01

output file name=/u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/datafile/o1_mf_undotbs1_f9pfv6wz_.dbf RECID=37 STAMP=969840103

channel ORA_AUX_DISK_1: restoring datafile 00003

input datafile copy RECID=31 STAMP=969795022 file name=/u03/app/oracle/fast_recovery_area/TESTDB03/datafile/o1_mf_sysaux_f9mv7tpr_.dbf

destination for restore of datafile 00003: /u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: copied datafile copy of datafile 00003, elapsed time: 00:00:07

output file name=/u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/datafile/o1_mf_sysaux_f9pfv7y3_.dbf RECID=38 STAMP=969840107

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 00012 to /u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/6675B1D171002866E0536ED2C40A4BAA/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00014 to /u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/6675B1D171002866E0536ED2C40A4BAA/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00013 to /u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/6675B1D171002866E0536ED2C40A4BAA/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u03/app/oracle/fast_recovery_area/TESTDB03/6675B1D171002866E0536ED2C40A4BAA/backupset/2018_03_03/o1_mf_nnndf_TAG20180303T232606_f9pcrjoy_.bkp

channel ORA_AUX_DISK_1: piece handle=/u03/app/oracle/fast_recovery_area/TESTDB03/6675B1D171002866E0536ED2C40A4BAA/backupset/2018_03_03/o1_mf_nnndf_TAG20180303T232606_f9pcrjoy_.bkp tag=TAG20180303T232606

channel ORA_AUX_DISK_1: restored backup piece 1

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

Finished restore at 04-MAR-18

datafile 1 switched to datafile copy

input datafile copy RECID=42 STAMP=969840126 file name=/u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/datafile/o1_mf_system_f9pftztl_.dbf

datafile 12 switched to datafile copy

input datafile copy RECID=43 STAMP=969840127 file name=/u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/6675B1D171002866E0536ED2C40A4BAA/datafile/o1_mf_system_f9pfvhc6_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=44 STAMP=969840127 file name=/u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/datafile/o1_mf_undotbs1_f9pfv6wz_.dbf

datafile 14 switched to datafile copy

input datafile copy RECID=45 STAMP=969840127 file name=/u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/6675B1D171002866E0536ED2C40A4BAA/datafile/o1_mf_undotbs1_f9pfvhfn_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=46 STAMP=969840128 file name=/u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/datafile/o1_mf_sysaux_f9pfv7y3_.dbf

datafile 13 switched to datafile copy

input datafile copy RECID=47 STAMP=969840128 file name=/u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/6675B1D171002866E0536ED2C40A4BAA/datafile/o1_mf_sysaux_f9pfvhc2_.dbf

contents of Memory Script:

{

# set requested point in time

set until scn 3337119;

# online the datafiles restored or switched

sql clone "alter database datafile 1 online";

sql clone 'PDB1' "alter database datafile

12 online";

sql clone "alter database datafile 4 online";

sql clone 'PDB1' "alter database datafile

14 online";

sql clone "alter database datafile 3 online";

sql clone 'PDB1' "alter database datafile

13 online";

# recover and open database read only

recover clone database tablespace "SYSTEM", "PDB1":"SYSTEM", "UNDOTBS1", "PDB1":"UNDOTBS1", "SYSAUX", "PDB1":"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 12 online

sql statement: alter database datafile 4 online

sql statement: alter database datafile 14 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 13 online

Starting recover at 04-MAR-18

using channel ORA_AUX_SBT_TAPE_1

using channel ORA_AUX_SBT_TAPE_2

using channel ORA_AUX_SBT_TAPE_3

using channel ORA_AUX_SBT_TAPE_4

using channel ORA_AUX_SBT_TAPE_5

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting incremental datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: /u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/datafile/o1_mf_system_f9pftztl_.dbf

destination for restore of datafile 00004: /u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/datafile/o1_mf_undotbs1_f9pfv6wz_.dbf

destination for restore of datafile 00003: /u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/datafile/o1_mf_sysaux_f9pfv7y3_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u03/app/oracle/fast_recovery_area/TESTDB03/backupset/2018_03_03/o1_mf_nnnd1_INCRE_BASEBKP01_f9p47qyb_.bkp

channel ORA_AUX_DISK_1: piece handle=/u03/app/oracle/fast_recovery_area/TESTDB03/backupset/2018_03_03/o1_mf_nnnd1_INCRE_BASEBKP01_f9p47qyb_.bkp tag=INCRE_BASEBKP01

channel ORA_AUX_DISK_1: restored backup piece 1

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

starting media recovery

archived log for thread 1 with sequence 57 is already on disk as file /u03/app/oracle/fast_recovery_area/TESTDB03/archivelog/2018_03_03/o1_mf_1_57_f9p4ors2_.arc

archived log for thread 1 with sequence 58 is already on disk as file /u03/app/oracle/fast_recovery_area/TESTDB03/archivelog/2018_03_03/o1_mf_1_58_f9p4zg0v_.arc

archived log for thread 1 with sequence 59 is already on disk as file /u03/app/oracle/fast_recovery_area/TESTDB03/archivelog/2018_03_03/o1_mf_1_59_f9p5d8vn_.arc

archived log for thread 1 with sequence 60 is already on disk as file /u03/app/oracle/fast_recovery_area/TESTDB03/archivelog/2018_03_03/o1_mf_1_60_f9p6qx59_.arc

archived log for thread 1 with sequence 61 is already on disk as file /u03/app/oracle/fast_recovery_area/TESTDB03/archivelog/2018_03_03/o1_mf_1_61_f9p8hxn0_.arc

archived log for thread 1 with sequence 62 is already on disk as file /u03/app/oracle/fast_recovery_area/TESTDB03/archivelog/2018_03_03/o1_mf_1_62_f9pb86nt_.arc

archived log for thread 1 with sequence 63 is already on disk as file /u03/app/oracle/fast_recovery_area/TESTDB03/archivelog/2018_03_03/o1_mf_1_63_f9pd0f91_.arc

archived log for thread 1 with sequence 64 is already on disk as file /u03/app/oracle/fast_recovery_area/TESTDB03/archivelog/2018_03_03/o1_mf_1_64_f9pf9bys_.arc

archived log file name=/u03/app/oracle/fast_recovery_area/TESTDB03/archivelog/2018_03_03/o1_mf_1_57_f9p4ors2_.arc thread=1 sequence=57

archived log file name=/u03/app/oracle/fast_recovery_area/TESTDB03/archivelog/2018_03_03/o1_mf_1_58_f9p4zg0v_.arc thread=1 sequence=58

archived log file name=/u03/app/oracle/fast_recovery_area/TESTDB03/archivelog/2018_03_03/o1_mf_1_59_f9p5d8vn_.arc thread=1 sequence=59

archived log file name=/u03/app/oracle/fast_recovery_area/TESTDB03/archivelog/2018_03_03/o1_mf_1_60_f9p6qx59_.arc thread=1 sequence=60

archived log file name=/u03/app/oracle/fast_recovery_area/TESTDB03/archivelog/2018_03_03/o1_mf_1_61_f9p8hxn0_.arc thread=1 sequence=61

archived log file name=/u03/app/oracle/fast_recovery_area/TESTDB03/archivelog/2018_03_03/o1_mf_1_62_f9pb86nt_.arc thread=1 sequence=62

archived log file name=/u03/app/oracle/fast_recovery_area/TESTDB03/archivelog/2018_03_03/o1_mf_1_63_f9pd0f91_.arc thread=1 sequence=63

archived log file name=/u03/app/oracle/fast_recovery_area/TESTDB03/archivelog/2018_03_03/o1_mf_1_64_f9pf9bys_.arc thread=1 sequence=64

media recovery complete, elapsed time: 00:03:58

Finished recover at 04-MAR-18

sql statement: alter database open read only

contents of Memory Script:

{

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

}

executing Memory Script

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

''/u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/controlfile/o1_mf_f9pftmk6_.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 2768239632 bytes

Fixed Size 8899600 bytes

Variable Size 587202560 bytes

Database Buffers 2164260864 bytes

Redo Buffers 7876608 bytes

sql statement: alter system set control_files = ''/u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/controlfile/o1_mf_f9pftmk6_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 2768239632 bytes

Fixed Size 8899600 bytes

Variable Size 587202560 bytes

Database Buffers 2164260864 bytes

Redo Buffers 7876608 bytes

sql statement: alter database mount clone database

contents of Memory Script:

{

# set requested point in time

set until scn 3337119;

# set destinations for recovery set and auxiliary set datafiles

set newname for datafile 17 to new;

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

restore clone datafile 17;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 04-MAR-18

allocated channel: ORA_AUX_SBT_TAPE_1

channel ORA_AUX_SBT_TAPE_1: SID=182 device type=SBT_TAPE

channel ORA_AUX_SBT_TAPE_1: Oracle Database Backup Service Library VER=12.2.0.1

allocated channel: ORA_AUX_SBT_TAPE_2

channel ORA_AUX_SBT_TAPE_2: SID=18 device type=SBT_TAPE

channel ORA_AUX_SBT_TAPE_2: Oracle Database Backup Service Library VER=12.2.0.1

allocated channel: ORA_AUX_SBT_TAPE_3

channel ORA_AUX_SBT_TAPE_3: SID=183 device type=SBT_TAPE

channel ORA_AUX_SBT_TAPE_3: Oracle Database Backup Service Library VER=12.2.0.1

allocated channel: ORA_AUX_SBT_TAPE_4

channel ORA_AUX_SBT_TAPE_4: SID=22 device type=SBT_TAPE

channel ORA_AUX_SBT_TAPE_4: Oracle Database Backup Service Library VER=12.2.0.1

allocated channel: ORA_AUX_SBT_TAPE_5

channel ORA_AUX_SBT_TAPE_5: SID=184 device type=SBT_TAPE

channel ORA_AUX_SBT_TAPE_5: Oracle Database Backup Service Library VER=12.2.0.1

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=23 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 00017 to /u02/app/oracle/oradata/TESTDB03/restab/MXTB_PITR_PDB1_TESTDB03/6675B1D171002866E0536ED2C40A4BAA/datafile/o1_mf_example0_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u03/app/oracle/fast_recovery_area/TESTDB03/6675B1D171002866E0536ED2C40A4BAA/backupset/2018_03_03/o1_mf_nnndf_TAG20180303T232606_f9pcrjoy_.bkp

channel ORA_AUX_DISK_1: restored backup piece 1

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

Finished restore at 04-MAR-18

datafile 17 switched to datafile copy

input datafile copy RECID=52 STAMP=969840544 file name=/u02/app/oracle/oradata/TESTDB03/restab/MXTB_PITR_PDB1_TESTDB03/6675B1D171002866E0536ED2C40A4BAA/datafile/o1_mf_example0_f9pg8xdk_.dbf

contents of Memory Script:

{

# set requested point in time

set until scn 3337119;

# online the datafiles restored or switched

sql clone 'PDB1' "alter database datafile

17 online";

# recover and open resetlogs

recover clone database tablespace "PDB1":"EXAMPLE01", "SYSTEM", "PDB1":"SYSTEM", "UNDOTBS1", "PDB1":"UNDOTBS1", "SYSAUX", "PDB1":"SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 17 online

Starting recover at 04-MAR-18

using channel ORA_AUX_SBT_TAPE_1

using channel ORA_AUX_SBT_TAPE_2

using channel ORA_AUX_SBT_TAPE_3

using channel ORA_AUX_SBT_TAPE_4

using channel ORA_AUX_SBT_TAPE_5

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 63 is already on disk as file /u03/app/oracle/fast_recovery_area/TESTDB03/archivelog/2018_03_03/o1_mf_1_63_f9pd0f91_.arc

archived log for thread 1 with sequence 64 is already on disk as file /u03/app/oracle/fast_recovery_area/TESTDB03/archivelog/2018_03_03/o1_mf_1_64_f9pf9bys_.arc

archived log file name=/u03/app/oracle/fast_recovery_area/TESTDB03/archivelog/2018_03_03/o1_mf_1_63_f9pd0f91_.arc thread=1 sequence=63

archived log file name=/u03/app/oracle/fast_recovery_area/TESTDB03/archivelog/2018_03_03/o1_mf_1_64_f9pf9bys_.arc thread=1 sequence=64

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

Finished recover at 04-MAR-18

database opened

contents of Memory Script:

{

sql clone 'alter pluggable database PDB1 open';

}

executing Memory Script

sql statement: alter pluggable database PDB1 open

contents of Memory Script:

{

# create directory for datapump import

sql 'PDB1' "create or replace directory

TSPITR_DIROBJ_DPDIR as ''

/u02/app/oracle/oradata/TESTDB03/restab''";

# create directory for datapump export

sql clone 'PDB1' "create or replace directory

TSPITR_DIROBJ_DPDIR as ''

/u02/app/oracle/oradata/TESTDB03/restab''";

}

executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u02/app/oracle/oradata/TESTDB03/restab''

Performing export of tables...

EXPDP> Starting "SYS"."TSPITR_EXP_mxtb_CaDn":

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 "HR"."EMPLOYESS_TEST" 17.09 KB 107 rows

EXPDP> ORA-39173: Encrypted data has been stored unencrypted in dump file set.

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

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

EXPDP> Dump file set for SYS.TSPITR_EXP_mxtb_CaDn is:

EXPDP> /u02/app/oracle/oradata/TESTDB03/restab/tspitr_mxtb_59739.dmp

EXPDP> Job "SYS"."TSPITR_EXP_mxtb_CaDn" successfully completed at Sun Mar 4 00:10:17 2018 elapsed 0 00:00:23

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_mxtb_EtBe" successfully loaded/unloaded

IMPDP> Starting "SYS"."TSPITR_IMP_mxtb_EtBe":

IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

IMPDP> . . imported "HR"."EMPLOYESS_TEST" 17.09 KB 107 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_mxtb_EtBe" successfully completed at Sun Mar 4 00:10:51 2018 elapsed 0 00:00:24

Import completed

Removing automatic instance

Automatic instance removed

auxiliary instance file /u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/6675B1D171002866E0536ED2C40A4BAA/datafile/o1_mf_temp_f9pg6j2n_.tmp deleted

auxiliary instance file /u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/datafile/o1_mf_temp_f9pg6f0c_.tmp deleted

auxiliary instance file /u02/app/oracle/oradata/TESTDB03/restab/MXTB_PITR_PDB1_TESTDB03/onlinelog/o1_mf_3_f9pg98fk_.log deleted

auxiliary instance file /u02/app/oracle/oradata/TESTDB03/restab/MXTB_PITR_PDB1_TESTDB03/onlinelog/o1_mf_2_f9pg98by_.log deleted

auxiliary instance file /u02/app/oracle/oradata/TESTDB03/restab/MXTB_PITR_PDB1_TESTDB03/onlinelog/o1_mf_1_f9pg989h_.log deleted

auxiliary instance file /u02/app/oracle/oradata/TESTDB03/restab/MXTB_PITR_PDB1_TESTDB03/6675B1D171002866E0536ED2C40A4BAA/datafile/o1_mf_example0_f9pg8xdk_.dbf deleted

auxiliary instance file /u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/6675B1D171002866E0536ED2C40A4BAA/datafile/o1_mf_sysaux_f9pfvhc2_.dbf deleted

auxiliary instance file /u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/datafile/o1_mf_sysaux_f9pfv7y3_.dbf deleted

auxiliary instance file /u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/6675B1D171002866E0536ED2C40A4BAA/datafile/o1_mf_undotbs1_f9pfvhfn_.dbf deleted

auxiliary instance file /u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/datafile/o1_mf_undotbs1_f9pfv6wz_.dbf deleted

auxiliary instance file /u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/6675B1D171002866E0536ED2C40A4BAA/datafile/o1_mf_system_f9pfvhc6_.dbf deleted

auxiliary instance file /u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/datafile/o1_mf_system_f9pftztl_.dbf deleted

auxiliary instance file /u02/app/oracle/oradata/TESTDB03/restab/TESTDB03/controlfile/o1_mf_f9pftmk6_.ctl deleted

auxiliary instance file tspitr_mxtb_59739.dmp deleted

Finished recover at 04-MAR-18

RMAN>

Note: RMAN uses input to automate the process of recovering the specified table. It performs the following tasks:

a. RMAN finds the appropriate backup pieces and loads them into this instance based on YOUR SCN.

b. RMAN creates an auxiliary instance

c. RMAN proceeds to restore and recover objects into this auxiliary instance, up to the specified point in time.

d. RMAN creates a Data Pump export dump file of the table that needs to be recovered including metadatathat.

e. RMAN imports the containing the recovered table into the target database.

f. RMAN removes the auxiliary instance and disconnect from RMAN session.

Test Table after Recovery

In SQL*Plus, query all rows of the test table to confirm the success of the recovery.

SQL> alter session set container=pdb1;

Session altered.

SQL> select count (*)

2 from HR.EMPLOYESS_TEST;

COUNT(*)

----------

107

Search This Blog

Oracle Automation / Cloud