Forum Stats

  • 3,769,803 Users
  • 2,253,023 Discussions


Steps to recover unknown tables from a know schema in 19C ASM DB with RMAN tape backup only

User_WWXJJ Member Posts: 2 Green Ribbon

Hi there,

Our developer deleted by mistake during application upgrade some tables. We don`t know which tables(no audit) so we can not used the rman table recovery option.

My idea is to recover data like below:

a. create a new instance from the production db spfile, add listener and tnsnames entries

b. recover and restore database from tape from an older backup

c. Make a diff using dba_objects on prod and new restored data

d. export data from the new database

e. import only tables results at diff or import using the table_exists_action=skip option

Please let me know if this approach is good and how to proceed exactly on step b. as I have a full backup from weekend and I need to restore and recover until the last backup before tables deletion.

Also, for step e, please let me know what option should be the best practice.


Dumitru Motei


  • Luís Gustavo Lira
    Luís Gustavo Lira Member Posts: 113 Silver Badge


    in this case, unfortunately, you don't have many options.

    It is best to restore the database on another host and export the required tables via datapump.

    In the next application upgrades I would recommend enabling flashback and creating guaranteed restore points to avoid all this work.

    About step b, if you have the backup of the archive logs, you can do a point-in-time recovery.

    In your restore script you can use:

    set until time "to_date('<DATE>','<DATE_FORMAT>)";

    And as you are restoring in another host, if you use different diskgroup names, you can use set newname like this for your datafiles and tempfiles before the restore command:

    set newname for datafile 1 to '<+DISKGROUP_NAME>';


    set newname for tempfile 1 to '<+DISKGROUP_NAME>';


    And after restore:

    switch datafile all;

    switch tempfile all;

    Dumitru Motei