In version 12c, many new and very useful features were introduced. Among them, the possibility to retrieve a table with RMAN. This feature applies to both Multitenant and non-Multitenant architectures, allowing you to retrieve a table from a PDB if that is the case.
In the following link you can find the official release:
RMAN Recover Table Feature in Oracle 12c and Higher (Doc ID 1521524.1)
In this test example we are going to retrieve a table created in the PDB schema raul "ORCLPDB1" inside the database, whose CDB is "ORCLCDB".
SQL> show con_name
CON_NAME
---------- ----------
ORCLPDB1
SQL> show con_id
CON_ID
----------
3
To do this, we will create a simple table, and insert 5 values:
SQL> create table uno (dni number);
Table created.
SQL>insert into uno values (1);
SQL>insert into uno values (2);
SQL>insert into uno values (3);
SQL>insert into uno values (4);
SQL>insert into uno values (5);
SQL> commit;
SQL> select * from uno
DNI
----- -----
1
2
3
4
5
We make the backup of the database with which we will recover the table later.
Connected to the CBD, we will perform a complete backup of the database and archives:
[oracle~]$ rman
Recovery Manager: Release 19.0.0.0.0 – Production on Thu Nov 3 17:43:08 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle its affiliates. All rights reserved.
RMAN> connect target /
connected to target database: ORCLCDB (DBID=2778750799)
RMAN> backup database plus archivelog;
Starting backup at 2022-11-03:17:43:30
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=293 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=19 RECID=1 STAMP=1104223655t
.
.
input archived log thread=1 sequence=26 RECID=8 STAMP=1119807810
channel ORA_DISK_1: starting piece 1 at 2022-11-03:17:43:31
channel ORA_DISK_1: finished piece 1 at 2022-11-03:17:43:34
.
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010oracle.dbf
input datafile file number=00012oracle.dbf
.
.
channel ORA_DISK_1: starting piece 1 at 2022-11-03:17:44:11
channel ORA_DISK_1: finished piece 1 at 2022-11-03:17:44:12
pieceoracle.bkp tag=TAG20221103T174411 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2022-11-03:17:44:12
Starting Control File and SPFILE Autobackup at 2022-11-03:17:44:13
pieceoracle.bkp comment=NONE
.
Finished Control File and SPFILE Autobackup at 2022-11-03:17:44:14
RMAN>
To verify that we have correctly copied our database (PDB) and the tablespace where we have hosted our table, in our case ORCLPDB1:USERS, we can run the command "report schema".
RMAN> connect target /
connected to target database: ORCLCDB (DBID=2778750799)
using target database control file instead of recovery catalog
RMAN>
RMAN> report schema;
Report of database schema for database with db_unique_name ORCLCDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 920 SYSTEM YES oracle.dbf
.
.
6 330 PDB$SEED:SYSAUX NO oracle.dbf
.
.
8 100 PDB$SEED:UNDOTBS1 NO oracle.dbf
9 280 ORCLPDB1:SYSTEM YES oracle.dbf
10 370 ORCLPDB1:SYSAUX NO oracle.dbf
.
12 342 ORCLPDB1:USERS NO oracle.dbf
14 370 ORCLPDB2:SYSAUX NO oracleORCLCDB/orclpdb2/sysaux01.dbf
16 342 ORCLPDB2:USERS NO oracle.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1 131 TEMP 32767 oracle.dbf
2 36 PDB$SEED:TEMP 32767 oracle.dbf
3 36 ORCLPDB1:TEMP 32767 oracle.dbf
4 36 ORCLPDB2:TEMP 32767 oracle.dbf
Table recovery.
It is quite important to define a date format, especially in RMAN when retrieving the table, since this will be the format that the tool will use to retrieve the table.
In this example we are going to delete the table and then retrieve it.
SQL> alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
————-
03/11/2022 22:14:14
SQL> drop table uno;
We launch the restore. We run the recover_table.bck script, which indicates that we want to restore table ONE to the date and time just before the table was deleted. As you can see, once the RMAN backup has been restored, RMAN exports and imports the data from the deleted table.
RMAN> connect target /
connected to target database: ORCLCDB (DBID=2778750799)
RMAN> @recover_table.bck
RMAN> run{
2> recover table «RAUL».»UNO» OF PLUGGABLE DATABASE ORCLPDB1
3> until time «to_date(’03/11/2022 22:14:14′,’dd/mm/yyyy hh24:mi:ss’)»
4> AUXILIARY DESTINATION ‘/u01/app/oracle/oradata/ORCLCDBRESTORE’;
5> }
Starting recover at 2022-11-03:23:10:23
using target database control file instead of recovery catalog
current log archived
allocated channel: ORA_DISK_1
.
.
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time
List of tablespaces expected to have UNDO segments
.
.
Tablespace UNDOTBS1
.
.
db_name=ORCLCDB
db_unique_name=wdum_pitr_ORCLPDB1_ORCLCDB
compatible=19.0.0
.
.
db_create_file_dest=/u01/app/oracle/oradata/ORCLCDBRESTORE
log_archive_dest_1=’location=/u01/app/oracle/oradata/ORCLCDBRESTORE’
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance ORCLCDB
Oracle instance started
.
.
contents of Memory Script:
{
# set requested point in time
set until time «to_date(’03/11/2022 22:14:14′,’dd/mm/yyyy hh24:mi:ss’)»;
# restore the controlfile
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCLCDB/autobackup/2022_11_03/o1_mf_s_1119807853_kp7zhfc2_.bkp
.
.
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCLCDB/autobackup/2022_11_03/o1_mf_s_1119807853_kp7zhfc2_.bkp tag=TAG20221103T174413
.
.
set until time «to_date(’03/11/2022 22:14:14′,’dd/mm/yyyy hh24:mi:ss’)»;
.
.
set newname for clone tempfile 3 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 9, 4, 11, 3, 10;
switch clone datafile all;
}
executing Memory Script
.
.
renamed tempfile 1 to /u01/app/oracle/oradata/ORCLCDBRESTORE/ORCLCDB/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /u01/app/oracle/oradata/ORCLCDBRESTORE/ORCLCDB/8857B36632797E5CE0536210ED0ADAC7/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 2022-11-03:23:10:48
.
.
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 00001 to /u01/app/oracle/oradata/ORCLCDBRESTORE/ORCLCDB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCLCDBRESTORE/ORCLCDB/datafile/o1_mf_undotbs1_%u_.dbf
.
.
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/ORCLCDBRESTORE/ORCLCDB/8857B36632797E5CE0536210ED0ADAC7/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece
/u01/app/oracle/fast_recovery_area/ORCLCDB/8857B36632797E5CE0536210ED0ADAC7/backupset/2022_11_03/o1_mf_nnndf_TAG20221103T174334_kp7zgp9h_.bkp
channel ORA_AUX_DISK_1: piece
handle=/u01/app/oracle/fast_recovery_area/ORCLCDB/8857B36632797E5CE0536210ED0ADAC7/backupset/2022_11_03/o1_mf_nnndf_TAG20221103T174334_kp7zgp9h_.bkp
tag=TAG20221103T174334
channel ORA_AUX_DISK_1: restored backup piece 1
.
.
contents of Memory Script:
{
# set requested point in time
.
.
# recover and open database read only
recover clone database tablespace «SYSTEM», «ORCLPDB1″:»SYSTEM», «UNDOTBS1», «ORCLPDB1″:»UNDOTBS1», «SYSAUX», «ORCLPDB1″:»SYSAUX»;
sql clone ‘alter database open read only’;
}
executing Memory Script
executing command: SET until clause
.
.
using channel ORA_AUX_DISK_1
starting media recovery
.
.
media recovery complete, elapsed time: 00:00:04
Finished recover at 2022-11-03:23:11:17
sql statement: alter database open read only
contents of Memory Script:
{
sql clone ‘alter pluggable database ORCLPDB1 open read only’;
}
executing Memory Script
sql statement: alter pluggable database ORCLPDB1 open read only
contents of Memory Script:
{
sql clone «create spfile from memory»;
shutdown clone immediate;
startup clone nomount;
.
.
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
.
.
# set requested point in time
set until time «to_date(’03/11/2022 22:14:14′,’dd/mm/yyyy hh24:mi:ss’)»;
.
:
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 2022-11-03:23:12:30
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=182 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
.
.
.
set until time «to_date(’03/11/2022 22:14:14′,’dd/mm/yyyy hh24:mi:ss’)»;
# online the datafiles restored or switched
sql clone ‘ORCLPDB1’ «alter database datafile
12 online»;
# recover and open resetlogs
recover clone database tablespace «ORCLPDB1″:»USERS», «SYSTEM», «ORCLPDB1″:»SYSTEM», «UNDOTBS1», «ORCLPDB1″:»UNDOTBS1», «SYSAUX», «ORCLPDB1″:»SYSAUX» delete
archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 12 online
Starting recover at 2022-11-03:23:12:34
using channel ORA_AUX_DISK_1
starting media recovery
.
.
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_11_03/o1_mf_1_27_kp7zhcqx_.arc thread=1 sequence=27
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_11_03/o1_mf_1_28_kp8llzrd_.arc thread=1 sequence=28
media recovery complete, elapsed time: 00:00:01
Finished recover at 2022-11-03:23:12:36
database opened
contents of Memory Script:
{
sql clone ‘alter pluggable database ORCLPDB1 open’;
}
executing Memory Script
sql statement: alter pluggable database ORCLPDB1 open
contents of Memory Script:
{
# create directory for datapump import
sql ‘ORCLPDB1’ «create or replace directory
TSPITR_DIROBJ_DPDIR as »
/u01/app/oracle/oradata/ORCLCDBRESTORE»»;
# create directory for datapump export
sql clone ‘ORCLPDB1’ «create or replace directory
TSPITR_DIROBJ_DPDIR as »
/u01/app/oracle/oradata/ORCLCDBRESTORE»»;
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as »/u01/app/oracle/oradata/ORCLCDBRESTORE»
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as »/u01/app/oracle/oradata/ORCLCDBRESTORE»
Performing export of tables…
EXPDP> Starting «SYS».»TSPITR_EXP_wdum_ssEn»:
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> . . exported «RAUL».»UNO» 5.078 KB 5 rows
EXPDP> Master table «SYS».»TSPITR_EXP_wdum_ssEn» successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_wdum_ssEn is:
EXPDP> /u01/app/oracle/oradata/ORCLCDBRESTORE/tspitr_wdum_34186.dmp
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of tables…
IMPDP> Master table «SYS».»TSPITR_IMP_wdum_sDFr» successfully loaded/unloaded
IMPDP> Starting «SYS».»TSPITR_IMP_wdum_sDFr»:
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported «RAUL».»UNO» 5.078 KB 5 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
MPDP> Job «SYS».»TSPITR_IMP_wdum_sDFr» successfully completed at Thu Nov 3 23:14:00 2022 elapsed 0 00:00:27
import completed
Removing automatic instance
auxiliary instance file /u01/app/oracle/oradata/ORCLCDBRESTORE/ORCLCDB/8857B36632797E5CE0536210ED0ADAC7/datafile/o1_mf_undotbs1_kp8ln8dn_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/ORCLCDBRESTORE/ORCLCDB/datafile/o1_mf_undotbs1_kp8lmsb6_.dbf deleted
Finished recover at 2022-11-03:23:14:04
We check that the table has been restored:
oracle~]$ sqlplus raul/***@ORCLPDB1
SQL*Plus: Release 19.0.0.0.0 – Production on Thu Nov 3 23:26:37 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Thu Nov 03 2022 22:26:01 +00:00
Connected to:
Oracle 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> SELECT * FROM UNO;
DNI
——-
1
2
3
4
5
RESTORE THE TABLE WITH A DIFFERENT NAME
There is another very useful option to restore the deleted table, it is to restore it with another name or "remap" the table.
RMAN> connect target /
connected to target database: ORCLCDB (DBID=2778750799)
RMAN> @recover_table_remap.bck
RMAN> run{
2> recover table «RAUL».»UNO» OF PLUGGABLE DATABASE ORCLPDB1
3> until time «to_date(’03/11/2022 22:14:14′,’dd/mm/yyyy hh24:mi:ss’)»
4> AUXILIARY DESTINATION ‘/u01/app/oracle/oradata/ORCLCDBRESTORE’
5> REMAP TABLE «RAUL».»UNO»:»RAUL».»UNORESTORE»;
6> }
Starting recover at 2022-11-03:23:40:08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=293 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 ORCLPDB1:SYSTEM
{
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 2022-11-03:23:40:26
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=4 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: restore complete, elapsed time: 00:00:03
Finished restore at 2022-11-03:23:40:51
datafile 1 switched to datafile copy
{
.
.
Finished restore at 2022-11-03:23:42:17
Performing export of tables…
Oracle instance shut down
Performing import of tables…
IMPDP> Master table «SYS».»TSPITR_IMP_elEq_vqzl» successfully loaded/unloaded
IMPDP> Starting «SYS».»TSPITR_IMP_elEq_vqzl»:
IMPDP> . . imported «RAUL».»UNORESTORE» 5.078 KB 5 rows
.
.
Import completed
In summary, with this new functionality, we avoid creating an auxiliary or dummy database, exporting the table and importing it into the operational database and then deleting the restored database for this purpose. With this command, all the steps that we used to do by hand can be done automatically.
