Sunday, June 16, 2024

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)

No comments:

Post a Comment