Recovering an Oracle Table After a Week-Long Data Mishap with RMAN and DBLINK

Background

A production Oracle Database 11.2.0.1 on Windows 64‑bit was running in ARCHIVELOG mode with regular RMAN backups but without Data Guard. An accidental UPDATE/DELETE corrupted a table for more than a week. The required point‑in‑time for recovery is 2021/06/08 17:00:00.

Analysis of Recovery Options

UNDO tablespace snapshots are unavailable because the error is older than the undo retention period.

Data Guard flashback cannot be used (no DG).

Oracle 12c single‑table recovery is not applicable to an 11g environment.

RMAN backup exists, so the affected tablespace can be restored on a separate host and the table extracted via a database link.

Conclusion: perform an RMAN tablespace restore on an auxiliary server, then fetch the recovered table through a DBLINK.

Test Environment (Illustrative)

Two Linux hosts (RHEL 6.9) were used to simulate the scenario. Both run Oracle 11.2.0.1.

Primary host – IP 10.211.55.111, instance name orcl.

Auxiliary host – IP 10.211.55.112, no instance created initially.

Test data were created in tablespace LUCIFER:

sqlplus / as sysdba
-- create tablespaces
create tablespace lucifer datafile '/oradata/orcl/lucifer01.dbf' size 10M autoextend off;
create tablespace ltest   datafile '/oradata/orcl/ltest01.dbf'   size 10M autoextend off;
-- create user and table
create user lucifer identified by lucifer;
granted dba to lucifer;
conn lucifer/lucifer
create table lucifer(id number not null, name varchar2(20)) tablespace lucifer;
insert into lucifer values(1,'lucifer');
insert into lucifer values(2,'test1');
insert into lucifer values(3,'test2');
commit;

A full RMAN backup was taken:

run {
  allocate channel c1 device type disk;
  allocate channel c2 device type disk;
  crosscheck backup;
  crosscheck archivelog all;
  sql "alter system switch logfile";
  delete noprompt expired backup;
  delete noprompt obsolete device type disk;
  backup database include current controlfile format '/backup/backlv0_%d_%T_%t_%s_%p';
  backup archivelog all delete input;
  release channel c1;
  release channel c2;
}

After the backup, deliberate DELETE/UPDATE statements were executed to simulate the corruption.

Step‑by‑Step Recovery Procedure

Identify the tablespace that contains the corrupted table.

select owner, tablespace_name from dba_segments where segment_name='LUCIFER';
select tablespace_name from dba_tablespaces;

Prepare the auxiliary host:

Install the same Oracle version (11.2.0.1).

Replicate the directory layout ( /u01/app/oracle, /oradata/orcl, /archivelog, etc.).

Copy the primary database’s spfile (or generate a pfile) and the password file to the auxiliary host.

sqlplus / as sysdba
create pfile='/home/oracle/pfile.ora' from spfile;
scp /home/oracle/pfile.ora 10.211.55.112:/tmp
scp $ORACLE_HOME/dbs/orapworcl 10.211.55.112:$ORACLE_HOME/dbs
mkdir -p /u01/app/oracle/admin/orcl/adump /oradata/orcl /archivelog
chown -R oracle:oinstall /archivelog /oradata

Start the auxiliary instance in NOMOUNT using the copied pfile.

sqlplus / as sysdba
startup nomount pfile='/tmp/pfile.ora';

Restore the control file from the RMAN backup and mount the instance.

rman target /
list backup of controlfile;
scp /backup/backlv0_ORCL_20210617_107548592* 10.211.55.112:/tmp
restore controlfile from '/tmp/backlv0_ORCL_20210617_1075485924_9_1';
alter database mount;

Restore the target tablespace (and any required system tablespaces) to a point just before the desired time.

run {
  sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
  set until time "2021-06-08 16:00:00";
  allocate channel ch01 device type disk;
  allocate channel ch02 device type disk;
  restore tablespace SYSTEM, SYSAUX, UNDOTBS1, USERS, LUCIFER;
  release channel ch01; release channel ch02;
}

Recover the database while skipping tablespaces that are not needed (e.g., LTEST).

run {
  sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
  set until time "2021-06-08 16:00:00";
  allocate channel ch01 device type disk;
  recover database skip tablespace LTEST, EXAMPLE;
  release channel ch01;
}

If the RECOVER DATABASE SKIP TABLESPACE step fails on Windows (or leaves tablespaces offline), generate ALTER DATABASE DATAFILE … OFFLINE DROP statements for the problematic tablespaces and reopen the database in read‑only mode.

select 'alter database datafile '||file_id||' offline drop;' from dba_data_files where tablespace_name in ('LTEST','EXAMPLE');
alter database open read only;

Verify the restored data.

select * from lucifer.lucifer;

If the data are not yet at the required point, repeat step 6 with a later UNTIL TIME until the correct state is reached.

Create a public database link from the primary database to the auxiliary host and copy the recovered table.

CREATE PUBLIC DATABASE LINK ORCL112 CONNECT TO lucifer IDENTIFIED BY lucifer USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.211.55.112)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)))';
create table lucifer.lucifer_0618 as select /*+full(lucifer)*/ * from lucifer.lucifer@ORCL112;
select * from lucifer.lucifer_0618;

Handling Missing Archive Logs

If the recovery stops because an archive log is missing, identify the required sequence and restore it before re‑running the RECOVER command.

alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss";
select first_time, sequence# from v$archived_log where sequence#='7';
restore archivelog sequence 7;
run {
  sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
  set until time "2021-06-08 18:06:00";
  allocate channel ch01 device type disk;
  recover database skip tablespace LTEST, EXAMPLE;
  release channel ch01;
}

Conclusion

The workflow demonstrates that, even when a table has been corrupted for more than a week, a point‑in‑time recovery is possible using RMAN tablespace restore on an auxiliary server and a DBLINK to extract the table. The key steps are:

Identify the affected tablespace.

Build an identical auxiliary environment.

Restore control file and tablespace to the required SCN/time.

Recover the database while skipping unrelated tablespaces.

Open the auxiliary instance read‑only, verify the data, and pull the table via DBLINK.

This approach avoids a full database restore, reduces downtime, and works with standard RMAN backups available in Oracle 11g.