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)
