Restore Table Into dumpfile using Oracle database Rman Backup of Pluggable database

 

There are many  documents Online   for this  but since we  had requirement  to perform same   thought of   documenting my own 

 

Instead of importing  table in same database its preferable to import into   dump file . 

Below is the high level procedure on how the restoration works.

1) Identifies the set of tablespace that needs to be recovered for this table restore.

2) Creates temporary instance with a random unique name

3) Starts database in nomount state and performs control file restore based on until time/scn value defined.

4) Sets new destinations for the identified data files to be restored

5) Then initiates the restoration of SYSTEM,SYSAUX,UNDO  tablespaces for CDB and SYSTEM and SYSAUX and EXAMPLE tablespace  for PDB

6) Perform recovery of database until time/scn by restoring archive logs and applying them to temporary database.

7) Once done its open the Database in open read only along with any PDB database under which table is available

8) Restart in nomount state and initiate restoraton of Datafiles related to Application Table(User Tablespace datafiles)

9) Recover archive logs and apply until time/scn

10) RMAN opens database and creates temporary DBA Directory to hold expdp dump

11) Performs expdp dump of table

12) Shutdown abort temporary database

13) Import the table dump to Source database

14) Cleanup all files created at the end of session

1) Restore  table into  dumpfile 

Create a pfile for auxiliary db use /tmp/init_aux.ora .  Username and  Table name must be in caps 

Add below parameter in it (Ensure other parameters like db_name/sga are specified along with it)

_disable_health_check=true

Main parameters to be added  to  auxiliary pfile are 

enable_pluggable_database=true 

_clone_one_pdb_recovery=true 

wallet_root

tde_configuration

compatible 

db_block_size 

db_files 

diagnostic_dest 

_system_trigger_enabled =false 

db_domain 

sga_target 

db_create_files_dest      ( to auxiliary destination used in   restore ) 

log_archive_dest_1       ( to auxiliary destination used in   restore ) 

db_name 

db_unique_name

rman target=/

RUN {

SET AUXILIARY INSTANCE PARAMETER FILE TO '/tmp/init_aux.ora';

recover table  "MYUSER"."MYTABLE"   OF PLUGGABLE DATABASE pdb2

  until scn 37128783

  auxiliary destination '/tmp/aux'

  datapump destination '/var/oracle/data/export'

  dump file 'saved_copy.dmp'

  notableimport;

}

Or 

RUN {

SET AUXILIARY INSTANCE PARAMETER FILE TO '/tmp/init_aux.ora';

recover table "MYUSER"."MYTABLE" OF PLUGGABLE DATABASE nameofpluggabledb

until time "to_date('07/11/2022 16:34:10','dd/mm/yyyy hh24:mi:ss')"

auxiliary destination '/var/oracle/backup/aux'

datapump destination '/var/oracle/backup/datapump'

dump file 'saved_copy.dmp'

notableimport;

}

If we  want to  import directly in database we can  use below 

RUN {

SET AUXILIARY INSTANCE PARAMETER FILE TO '/tmp/init_aux.ora';

recover table "MYUSER"."MYTABLE" OF PLUGGABLE DATABASE nameofpluggabledb

until time "to_date('07/11/2022 16:34:10','dd/mm/yyyy hh24:mi:ss')"

auxiliary destination '/var/oracle/backup/aux'

REMAP TABLE 'SMEDS'.'RECTEST':'TEST_RECTEST';

}

2) Import dumpfile in database from above dmpfile 

impdp youruser/yourpassword@yourhost/nameofpluggabledb full=Y directory=DUMP_DIR dumpfile=saved_copy.dmp

References :

Rman Recover Table Doesnot Clean up Files Created Under Dbs Folder For the Auxiliary Instance After it Completes (Doc ID 2882639.1)

RMAN Recover Table Feature in Oracle Database 12c and Higher (Doc ID 1521524.1)

Undo Datafile at Auxiliary Instance Remains after Executing Recover Table (Doc ID 2407419.1)