How To Recover Tables to a Specified Point in Time

Starting from 12c, RMAN allows to perform a Table Point In Time Recovery (PITR).
On earlier releases, this was only possible by manually creating a point in time clone of the database and extracting the data via datapump.
With this “new” feature, life gets easier.

SETUP:
For this task we are assuming you have your DB/ARCHIVE log backups in place.
To demonstrate this feature, lets create a table, fill and “lose” some data.

** DB we are working on:

INST HOST		INST_NAME      STATUS	  DB_ROLE	    OPEN_MODE		  STARTUP_TIME
---- ------------------ -------------- ---------- ----------------- --------------------- --------------------
*1   standbyracnode	devdbnoncdb    OPEN	  PRIMARY	    READ WRITE		  15-AUG-2021 16:45:26

SQL> CREATE TABLE HR.STUFF(
    person_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    PRIMARY KEY(person_id)
);   

Table created.

SQL> 


**data added


SQL> select sysdate from dual;
SYSDATE
--------------------
15-AUG-2021 17:51:13   <<----- timestamp to recover to


SQL> select count(*) from HR.STUFF;

  COUNT(*)
----------
     21012 
   
SQL> delete from(select * from HR.STUFF) where rownum<=10000;
10000 rows deleted.
SQL> commit;
Commit complete.

SQL> select count(*) from HR.STUFF;
  COUNT(*)
----------
     11012

We have 2 options here:

1:- Recover the table and leave on a expdp dump file (which we would need to manually import):
	
	RECOVER TABLE HR.STUFF
	    UNTIL TIME "TO_DATE('15-AUG-2021 17:51:13', 'DD-MON-YYYY HH24:MI:SS')"
	    AUXILIARY DESTINATION '+RECO'
	    DATAPUMP DESTINATION '/backup/restore_table/dumps'
	    DUMP FILE 'HR.STUFF_exp_dump.dat'
	    NOTABLEIMPORT;


2:- Recover the table and automatically do the import with a new name (remap table)
	
	RECOVER TABLE HR.STUFF
	    UNTIL TIME "TO_DATE('15-AUG-2021 17:51:13', 'DD-MON-YYYY HH24:MI:SS')"
	    AUXILIARY DESTINATION '+RECO'
	    REMAP TABLE 'HR'.'STUFF':'HR'.'STUFF_NEW';

Lets run the recover with REMAP option:

oracle@standbyracnode restore_table]$ ls -tlr 
total 64
-rw-r--r--. 1 oracle oinstall    36 Aug 15 17:54 initaux.ora
-rwxr--r--. 1 oracle oinstall   542 Aug 15 19:03 restore_table.remap.sh
-rw-r--r--. 1 oracle oinstall 14483 Aug 15 19:13 recover_table_remap.log
drwxr-xr-x. 2 oracle oinstall    35 Aug 15 19:34 dumps
-rw-------. 1 oracle oinstall    78 Aug 15 19:36 nohup.out
[oracle@standbyracnode restore_table]$ 

**memory parameters for the auxiliary db:


[oracle@standbyracnode restore_table]$ cat initaux.ora
SGA_target=2048M
SGA_MAX_SIZE=2048M
[oracle@standbyracnode restore_table]$ 

** script:


[oracle@standbyracnode restore_table]$ cat restore_table.remap.sh
#!/bin/ksh
export ORACLE_HOME=/u01/app/oracle/product/19c/db_1
export ORACLE_SID=devdbnoncdb
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
echo $ORACLE_SID
echo $ORACLE_HOME
set echo on;
rman target  'sys/xxxxx' log /backup/restore_table/recover_table_remap.log <<EOF
run {
SET AUXILIARY INSTANCE PARAMETER FILE TO '/backup/restore_table/initaux.ora';
RECOVER TABLE HR.STUFF
    UNTIL TIME "TO_DATE('15-AUG-2021 17:51:13', 'DD-MON-YYYY HH24:MI:SS')"
    AUXILIARY DESTINATION '+RECO'
    REMAP TABLE 'HR'.'STUFF':'HR'.'STUFF_NEW';
}
EOF
[oracle@standbyracnode restore_table]$ 


** execute:


[oracle@standbyracnode restore_table]$ nohup ./restore_table.remap.sh  &

** this will create an auxiliary:


[oracle@standbyracnode restore_table]$ ps -ef | grep -i _pmon | grep -v grep
oracle    5388     1  0 16:03 ?        00:00:00 asm_pmon_+ASM1
oracle    7325     1  0 18:08 ?        00:00:00 ora_pmon_Dqts  <<----
oracle   13395     1  0 17:10 ?        00:00:00 ora_pmon_devdbnoncdb
[oracle@standbyracnode restore_table]$ 

Once script finishes, we can verify results:

SQL> select sysdate from dual;

SYSDATE
--------------------
15-AUG-2021 18:17:10

SQL> select count(*) from HR.STUFF;

  COUNT(*)
----------
     11012

** REMAPED table with same rows than before the delete:

SQL> select count(*) from HR.STUFF_NEW;

  COUNT(*)
----------
     21012

**After this, User can validate data and refresh original table.
  this is usually done this way because User is sceptical and afraid table will get lost

If we want to use the noimport option, we just need to use this script:

[oracle@standbyracnode restore_table]$ cat restore_table_noimport.sh
#!/bin/ksh
export ORACLE_HOME=/u01/app/oracle/product/19c/db_1
export ORACLE_SID=devdbnoncdb
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
echo $ORACLE_SID
echo $ORACLE_HOME
set echo on;
rman target  'sys/xxxxx' log /backup/restore_table/recover_table_noimport.log <<EOF
run {
RECOVER TABLE HR.STUFF
    UNTIL TIME "TO_DATE('15-AUG-2021 17:51:13', 'DD-MON-YYYY HH24:MI:SS')"
    AUXILIARY DESTINATION '+RECO'
    DATAPUMP DESTINATION '/backup/restore_table/dumps'
    DUMP FILE 'HR.STUFF_exp_dump.dat'
    NOTABLEIMPORT;
}
EOF
[oracle@standbyracnode restore_table]$ 

     
** dumps will be created:


[oracle@standbyracnode restore_table]$ ls -tlr /backup/restore_table/dumps
total 808
-rw-r-----. 1 oracle oinstall 827392 Aug 15 19:35 HR.STUFF_exp_dump.dat
[oracle@standbyracnode restore_table]$ 

** After this, a impdp needs to be executd to import the table on the DB