Restore Table from RMAN Backup — Not Flashback

Recently, there is a change made by Developer to a oracle database table where they dropped one of the column and changed datatype for another column.

Later they identified issue with this change and request to revert back the table to original just before the change made.

DBA initially tried reverting using Oracle Flashback feature (as DB has Flashback functionality) but it failed as there is a data definition change happened. Then we looked at our options and decided that we go with RMAN Table recovery option. All our backups are in OCI Object store and these backups are done manually via cron schedule

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 filesto be restored
5. Then initiates the restoration of datafiles(SYSTEM,SYSAUX,UNDO) to this temporary location
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

Restore Commands

Restore Table from backup (NONPDB )

RMAN> RECOVER TABLE '<OWNER>'.'<TABLE NAME>' of until time "to_date('2023-11-19 23:21:27','yyyy-mm-dd:hh24:mi:ss')" AUXILIARY DESTINATION '/u01/restore';

Restore Table from backup (PDB)

RMAN> RECOVER TABLE '<OWNER>'.'<TABLE NAME>' of pluggable database <PDBNAME> until time "to_date('2023-11-19 23:21:27','yyyy-mm-dd:hh24:mi:ss')" AUXILIARY DESTINATION '/u01/restore';

Sample Log

RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace PDB:SYSTEM
Tablespace SYSTEM
Tablespace PDB:UNDOTBS1
Tablespace UNDOTBS1

contents of Memory Script:
{
# set requested point in time
set until time “to_date(‘2023–11–19 23:21:27’,’yyyy-mm-dd:hh24:mi:ss’)”;
# 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’;
}

restore clone datafile 110, 1, 112, 4, 3, 111;

contents of Memory Script:
{
# set requested point in time
set until time “to_date(‘2023–11–19 23:21:27’,’yyyy-mm-dd:hh24:mi:ss’)”;
# online the datafiles restored or switched
sql clone ‘PDB’ “alter database datafile
110 online”;
sql clone “alter database datafile 1 online”;
sql clone ‘PDB’ “alter database datafile
112 online”;
sql clone “alter database datafile 4 online”;
sql clone “alter database datafile 3 online”;
sql clone ‘PDB’ “alter database datafile
111 online”;
# recover and open database read only
recover clone database tablespace “PDB”:”SYSTEM”, “SYSTEM”, “PDB”:”UNDOTBS1", “UNDOTBS1”, “SYSAUX”, “PDB”:”SYSAUX”;
sql clone ‘alter database open read only’;
}

sql clone ‘alter pluggable database PDB open read only’;

contents of Memory Script:
{
# set requested point in time
set until time “to_date(‘2023–11–19 23:21:27’,’yyyy-mm-dd:hh24:mi:ss’)”;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 122 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 122;

switch clone datafile all;
}

contents of Memory Script:
{
# set requested point in time
set until time “to_date(‘2023–11–19 23:21:27’,’yyyy-mm-dd:hh24:mi:ss’)”;
# online the datafiles restored or switched
sql clone ‘PDB’ “alter database datafile
122 online”;
# recover and open resetlogs
recover clone database tablespace “PDB”:”USER_DATA”, “PDB”:”SYSTEM”, “SYSTEM”, “PDB”:”UNDOTBS1", “UNDOTBS1”, “SYSAUX”, “PDB”:”SYSAUX” delete archivelog;
alter clone database open resetlogs;
}

Contents of Memory Script:
{
# create directory for datapump import
sql ‘PDB’ “create or replace directory TSPITR_DIROBJ_DPDIR as ‘’
/u01/restore’’”;
# create directory for datapump export
sql clone ‘PDB’ “create or replace directory TSPITR_DIROBJ_DPDIR as ‘’
/u01/restore’’”;
}

Performing export of tables…
EXPDP> . . exported “USER”.”USER_TABLE” 954.5 KB 6273 rows
EXPDP> ORA-39173: Encrypted data has been stored unencrypted in dump file set.
EXPDP> Master table “SYS”.”TSPITR_EXP_CEio_qcjC” successfully loaded/unloaded
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
Performing import of tables…
IMPDP> Master table “SYS”.”TSPITR_IMP_CEio_qnla” successfully loaded/unloaded
IMPDP> . . imported “USER”.”USER_TABLE” 954.5 KB 6273 rows
IMPDP> Processing object type
Import completed

auxiliary instance file /u01/restore/DB1/E901BD22C810451EE053DE04D80AE5A5/datafile/o1_mf_temp_lorjwnvp_.tmp deleted
auxiliary instance file /u01/restore/DB1/datafile/o1_mf_temp_lorjwm7w_.tmp deleted
auxiliary instance file /u01/restore/CEIO_PITR_PDB/onlinelog/o1_mf_3_lork248k_.log deleted
auxiliary instance file /u01/restore/CEIO_PITR_PDB/E901BD22C810451EE053DE04D80AE5A5/datafile/o1_mf_data_d_lorjyfh0_.dbf deleted
auxiliary instance file /u01/restore/DB1/E901BD22C810451EE053DE04D80AE5A5/datafile/o1_mf_sysaux_lorjr37h_.dbf deleted
auxiliary instance file /u01/restore/DB1/E901BD22C810451EE053DE04D80AE5A5/datafile/o1_mf_undotbs1_lorjqw7n_.dbf deleted
auxiliary instance file /u01/restore/DB1/E901BD22C810451EE053DE04D80AE5A5/datafile/o1_mf_system_lorjrw7f_.dbf deleted