Oracle DBA

Synopsis:
This article demonstrates how to recover a dropped table in Oracle 19c using RMAN’s point-in-time tablespace recovery (TSPITR) technique. The procedure uses an auxiliary instance to mount a recovered copy of necessary tablespaces at the requested point in time, performs an export of the target table from the auxiliary instance, and then imports it back to the production instance. Replace all paths, SIDs, DBIDs and timestamps with values appropriate to your environment.

---

Assumptions & Preconditions

• The database is in ARCHIVELOG mode.
• You have working RMAN backups (datafile and archived redo logs) that cover the point in time to which you want to recover.
• You have sufficient disk space to create an auxiliary recovery area (auxiliary destination).
• You have SYSDBA privileges and OS access to run RMAN, sqlplus, expdp and impdp.
• The target table was backed up indirectly by the datafile backups and archived logs (i.e., it existed at the requested recovery time).

---

Executive summary (one-line)

Use RMAN RECOVER TABLE ... AUXILIARY DESTINATION to create an auxiliary recovery instance at the desired point in time, export the recovered table from the auxiliary instance, then import it into the production instance.

---

Demo: Step-by-step (lab transcript adapted for copy/paste)

Example environment values used in this demo — change them to match your system: ORACLE_SID=dbwr, auxiliary location /u01/ARC_BKP, and recovery point 2019-05-16 02:21:27.

1. Create an application user and populate the table

-- connect as SYS (or other privileged account)
SQL> CREATE USER odba IDENTIFIED BY "oracle" DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp PROFILE DEFAULT ACCOUNT UNLOCK;
User created.

SQL> GRANT CREATE SESSION, CREATE TABLE TO odba;
Grant succeeded.

SQL> ALTER USER odba QUOTA 500M ON users;
User altered.

-- Connect as the user and create/populate the table
SQL> CONNECT odba/oracle;
SQL> CREATE TABLE emp(id NUMBER);
Table created.

-- Bulk insert for demo
SQL> BEGIN
  FOR i IN 1 .. 100000 LOOP
    INSERT INTO emp VALUES(i);
  END LOOP;
END;
/
PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM emp;
COUNT(*)
--------
100000

-- Note the point-in-time timestamp to which we will recover
SQL> SELECT TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL;
         NOW
---------------------
05-16-2019 02:21:27

Record this timestamp precisely — it is the target UNTIL TIME value for the TSPITR operation.

---

2. Ensure ARCHIVELOG mode & take a full RMAN backup

SQL> ARCHIVE LOG LIST;
-- Ensure Database log mode: Archive Mode

Take a full backup (example):

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Sample RMAN output (abridged):

Starting backup at 16-MAY-19
...
Finished backup at 16-MAY-19
Starting Control File and SPFILE Autobackup at 16-MAY-19
piece handle=... comment=NONE
Finished Control File and SPFILE Autobackup at 16-MAY-19

You cannot recover a table to a point that has not been captured in a backup plus archived logs. Ensure backups and logs exist for the target time.

---

3. Simulate the loss (drop the table)

SQL> DROP TABLE odba.emp;
Table dropped.

SQL> SELECT COUNT(*) FROM odba.emp;
ERROR at line 1:
ORA-00942: table or view does not exist

---

4. Run RMAN table recovery (TSPITR) using an auxiliary destination

The RECOVER TABLE command creates an auxiliary instance automatically, recovers the necessary tablespaces to the UNTIL point, exports the required table(s) from the auxiliary instance using Data Pump, and then imports the table(s) back into the production database.

Example RMAN command (replace the until time with your recorded timestamp and change auxiliary destination):

rman target /
RMAN> RECOVER TABLE odba.emp
      UNTIL TIME "TO_DATE('2019-05-16 02:21:27','YYYY-MM-DD HH24:MI:SS')"
      AUXILIARY DESTINATION '/u01/ARC_BKP';

Sample RMAN activity (abridged, illustrative):

• RMAN will create an automatic auxiliary instance (SID like bCxt) with initialization parameters tuned for the recovery.
• RMAN restores a clone control file and mounts the auxiliary database.
• RMAN restores and switches copies of required datafiles (SYSTEM, SYSAUX, UNDOTBS1, USERS) for the auxiliary instance.
• RMAN performs media recovery on the auxiliary instance up to the UNTIL time.
• RMAN opens the auxiliary database (read-only or RESETLOGS depending on operation) and automatically performs Export/Import (Data Pump) of the recovered table:
  • An expdp job runs on the auxiliary instance and writes a dump file (e.g., /u01/ARC_BKP/tspitr_bCxt_17369.dmp).
  • RMAN transfers the dump to the target instance and runs impdp to import the table into the production schema.

Key RMAN excerpts (from the demo):

Starting recover at 16-MAY-19
...
Automatic instance created
...
restoring control file to /u01/ARC_BKP/DBWR/controlfile/o1_mf_gfs181c5_.ctl
...
datafile 1 switched to datafile copy ...
recover clone database tablespace "SYSTEM","UNDOTBS1","SYSAUX"
sql clone 'alter database open read only'
...
Performing export of tables...
EXPDP> . . exported "ODBA"."EMP" 856.34 KB 100000 rows
Export completed
...
Performing import of tables...
IMPDP> . . imported "ODBA"."EMP" 856.34 KB 100000 rows
Import completed
Finished recover at 16-MAY-19

---

5. Verification

Back in your production instance:

SQL> SELECT COUNT(*) FROM odba.emp;
COUNT(*)
---------
100000

The table and its rows have been restored to production as of the requested point in time.

---

How it works — conceptual summary

1. Auxiliary instance: RMAN creates a transient auxiliary database instance and restores the control file and relevant datafiles into AUXILIARY DESTINATION.
2. Point-in-time recovery: RMAN recovers the necessary tablespaces to the UNTIL time using backups and archived redo logs.
3. Export from auxiliary: RMAN runs a Data Pump expdp on the auxiliary instance to extract the table definition and data into a dump file.
4. Import into target: RMAN then runs impdp on the production (target) database to import the table.
5. Cleanup: Auxiliary instance files and dump files are removed unless KEEP flags are used.

This approach avoids restoring an entire database into production, isolates recovery work, and is efficient for single-table or small object recovery.

---

Important considerations & caveats

• Space: Ensure AUXILIARY DESTINATION has enough space for the auxiliary instance files and exported dump.
• Permissions: RMAN must be able to start an auxiliary instance (OS permissions), and the target DB must allow Data Pump import.
• Tables with dependencies: If the table has dependent objects (indexes, constraints, triggers, LOBs), RMAN/TSPITR handles them by exporting/importing those objects as well. Complex dependencies (e.g., cross-schema references) may require manual intervention.
• Temporal accuracy: Choose the UNTIL timestamp carefully — if you select an earlier point than needed, data changes after that point will be lost.
• Performance: TSPITR can be I/O intensive on the backup storage and the auxiliary destination. Run during maintenance windows for large recoveries.
• Backups & logs requirement: You cannot recover a table to a time for which backups + archive logs do not exist. Validate that backups cover the UNTIL time.
• Data pump file location: The auxiliary instance writes the dump file to the auxiliary location — ensure read/write access and later deletion if desired.

---

Post-recovery checklist (short)

1. Verify the table structure and data (SELECT COUNT(*), sample queries).
2. Rebuild dependent objects if required (indexes, statistics).
3. Take a fresh backup of the database or affected tablespaces as per policy.
4. Document the recovery operation (time, cause, RMAN job output, files used).
5. Validate scheduled backups and archive log retention to prevent recurrence.

---

Recommended best practices

• Regularly test TSPITR in a non-production environment to be familiar with timings and disk requirements.
• Maintain frequent backups and protect archived redo logs (secondary copies or offsite storage).
• Monitor FRA and RMAN retention to ensure required backup retention.
• Automate RMAN cataloging of successful backups and maintain DBID records for quick recovery operations.