Recover a dropped table to a specific Point in Time using RMAN

Starting from 12c, RECOVER TABLE command that allows point in time recovery of a table or a table partition.

We must have RMAN backups of the tables or table partitions as they existed at the point in time to which you want to recover the dropped objects.

Host	Database Name	CDB	Database Version
srvdb01.localdomain	orcl	No	19.3.0.0.0

1. Let’s check the table row count and drop the dept table.

[oracle@srvdb01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 6 21:41:01 2024

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> conn scott

Enter password:

Connected.

SQL> set time on

22:06:42 SQL> select count(*) from dept;

COUNT(*)

----------

22:06:44 SQL> drop table dept purge;

Table dropped.

2. Will invoke rman and proceed with recover table PITR.

RMAN performs a series of steps while automating the process of recovering tables or table partitions.

a). Determine which backup contains the tables that need to be recovered.

b). Creates an auxiliary database on the target host and recovers the specified tables, until the specified point in time into this auxiliary database.

c). Creates a Data Pump export dump file that contains the recovered tables or table partitions.

d). Imports the Data Pump export dump file into the target instance.

e). Auxiliary destination would remove automatically once the table is recovered.

[oracle@srvdb01 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Jan 6 22:07:41 2024

Version 19.3.0.0.0

connected to target database: ORCL (DBID=1685275839)

RMAN> recover table scott.dept until time "to_date('06-JAN-2024 22:06:00','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '/u01/app/oracle/aux/';

Starting recover at 06-JAN-24

using target database control file instead of recovery catalog

current log archived

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=273 device type=DISK

RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

Creating automatic instance, with SID='eefz'

initialization parameters used for automatic instance:

db_name=ORCL

db_unique_name=eefz_pitr_ORCL

compatible=19.0.0

db_block_size=8192

db_files=200

diagnostic_dest=/u01/app/oracle

_system_trig_enabled=FALSE

sga_target=516M

processes=200

db_create_file_dest=/u01/app/oracle/aux/

log_archive_dest_1='location=/u01/app/oracle/aux/'

#No auxiliary parameter file used

starting up automatic instance ORCL

Oracle instance started

Total System Global Area 541063272 bytes

Fixed Size 8898664 bytes

Variable Size 167772160 bytes

Database Buffers 356515840 bytes

Redo Buffers 7876608 bytes

Automatic instance created

contents of Memory Script:

{

# set requested point in time

set until time "to_date('06-JAN-2024 22:06:00','DD-MON-YYYY HH24:MI:SS')";

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET until clause

Starting restore at 06-JAN-24

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=21 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-1685275839-20240106-00

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-1685275839-20240106-00 tag=TAG20240106T213739

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/aux/ORCL/controlfile/o1_mf_lsm0k60n_.ctl

Finished restore at 06-JAN-24

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:

{

# set requested point in time

set until time "to_date('06-JAN-2024 22:06:00','DD-MON-YYYY HH24:MI:SS')";

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile 1 to new;

set newname for clone datafile 4 to new;

set newname for clone datafile 3 to new;

set newname for clone tempfile 1 to new;

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 1, 4, 3;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/aux/ORCL/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 06-JAN-24

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/aux/ORCL/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/cdb_052fufs4_1_1

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/cdb_052fufs4_1_1 tag=FULLDB

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/aux/ORCL/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/cdb_042fufs4_1_1

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/cdb_042fufs4_1_1 tag=FULLDB

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/aux/ORCL/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/cdb_032fufs4_1_1

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/cdb_032fufs4_1_1 tag=FULLDB

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35

Finished restore at 06-JAN-24

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=1157580601 file name=/u01/app/oracle/aux/ORCL/datafile/o1_mf_system_lsm0kxtj_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=5 STAMP=1157580601 file name=/u01/app/oracle/aux/ORCL/datafile/o1_mf_undotbs1_lsm0kcpj_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=6 STAMP=1157580601 file name=/u01/app/oracle/aux/ORCL/datafile/o1_mf_sysaux_lsm0kgrn_.dbf

contents of Memory Script:

{

# set requested point in time

set until time "to_date('06-JAN-2024 22:06:00','DD-MON-YYYY HH24:MI:SS')";

# online the datafiles restored or switched

sql clone "alter database datafile 1 online";

sql clone "alter database datafile 4 online";

sql clone "alter database datafile 3 online";

# recover and open database read only

recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX";

sql clone 'alter database open read only';

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 4 online

sql statement: alter database datafile 3 online

Starting recover at 06-JAN-24

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/arch/1_7_1157495106.dbf

archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/arch/1_8_1157495106.dbf

archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/arch/1_9_1157495106.dbf

archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/arch/1_10_1157495106.dbf

archived log file name=/u01/app/oracle/arch/1_7_1157495106.dbf thread=1 sequence=7

archived log file name=/u01/app/oracle/arch/1_8_1157495106.dbf thread=1 sequence=8

archived log file name=/u01/app/oracle/arch/1_9_1157495106.dbf thread=1 sequence=9

archived log file name=/u01/app/oracle/arch/1_10_1157495106.dbf thread=1 sequence=10

media recovery complete, elapsed time: 00:00:02

Finished recover at 06-JAN-24

sql statement: alter database open read only

contents of Memory Script:

{

sql clone "create spfile from memory";

shutdown clone immediate;

startup clone nomount;

sql clone "alter system set control_files =

''/u01/app/oracle/aux/ORCL/controlfile/o1_mf_lsm0k60n_.ctl'' comment=

''RMAN set'' scope=spfile";

shutdown clone immediate;

startup clone nomount;

# mount database

sql clone 'alter database mount clone database';

}

executing Memory Script

sql statement: create spfile from memory

database closed

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 541063272 bytes

Fixed Size 8898664 bytes

Variable Size 167772160 bytes

Database Buffers 356515840 bytes

Redo Buffers 7876608 bytes

sql statement: alter system set control_files = ''/u01/app/oracle/aux/ORCL/controlfile/o1_mf_lsm0k60n_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 541063272 bytes

Fixed Size 8898664 bytes

Variable Size 167772160 bytes

Database Buffers 356515840 bytes

Redo Buffers 7876608 bytes

sql statement: alter database mount clone database

contents of Memory Script:

{

# set requested point in time

set until time "to_date('06-JAN-2024 22:06:00','DD-MON-YYYY HH24:MI:SS')";

# set destinations for recovery set and auxiliary set datafiles

set newname for datafile 7 to new;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 7;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 06-JAN-24

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/aux/EEFZ_PITR_ORCL/datafile/o1_mf_users_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/cdb_062fufs4_1_1

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/cdb_062fufs4_1_1 tag=FULLDB

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 06-JAN-24

datafile 7 switched to datafile copy

input datafile copy RECID=8 STAMP=1157580671 file name=/u01/app/oracle/aux/EEFZ_PITR_ORCL/datafile/o1_mf_users_lsm0o6mj_.dbf

contents of Memory Script:

{

# set requested point in time

set until time "to_date('06-JAN-2024 22:06:00','DD-MON-YYYY HH24:MI:SS')";

# online the datafiles restored or switched

sql clone "alter database datafile 7 online";

# recover and open resetlogs

recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 7 online