Restore dropped or delete record table to another instance for large size database in Oracle

Suppose we have large database having multiple users and their associated tablespaces. The database size in terabytes. Its is difficult to restore whole database to recover one table. So, We recovery only associated tablespace that belong to that the table. With this we save time and space for recovering the table.

For this restore operation you need to have the full backup of the database before the table deleted or dropped. If many application is working on same database then you need to restore one table of one application then you do not need to restore completed database. You can restore system tablespaces plus the tablespace that application is using to save time. If database is in terabytes then it will take time and need more space to restore complete database. then fetch the table from it.

For restore table, we need to restore associated tablespace and create a new instance by restoring major tablespace needed for the instance.

Step 1. Find the table data in which tablespace it belong.

SELECT owner, table_name, tablespace_name
FROM dba_tables
WHERE tablespace_name = 'tablespace_name';

Step 2. Create new database on another server.

Step 3. Restored the tablespace  system,undo ,sysaux  and with tablespace which having data.

Restore tablespace system,undo,sysaux , app_data_tx;

Step 4. Recover the database with skip option of other tablespace which is present in the database.

Recover database skip …….(all other tablespace which not needed)

Step 5. Startup the database

Startup mount

Step 6. alter database open resetlog.

alter database open resetlog;