In version 12c, many useful new features were introduced. Among them is the ability to recover a table with RMAN. This feature applies to both multitenant and non-multitenant architectures, allowing you to recover a table from a PDB if necessary.
The official note link be found at the following link :
RMAN Recover Table Feature in Oracle Database 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@oracle21c ~]$ 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 and/or 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=00010 name=/u01/app/oracle/oradata/ORCLCDB/orclpdb1/sysaux01.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/ORCLCDB/orclpdb1/users01.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
piece handle=/u01/app/oracle/fast_recovery_area/ORCLCDB/backupset/2022_11_03/o1_mf_annnn_TAG20221103T174411_kp7zhcyq_.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
piece handle=/u01/app/oracle/fast_recovery_area/ORCLCDB/autobackup/2022_11_03/o1_mf_s_1119807853_kp7zhfc2_.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 /u01/app/oracle/oradata/ORCLCDB/system01.dbf
3 850 SYSAUX NO /u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf
4 330 UNDOTBS1 YES /u01/app/oracle/oradata/ORCLCDB/undotbs01.dbf
5 270 PDB$SEED:SYSTEM NO /u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
6 330 PDB$SEED:SYSAUX NO /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
7 5 USERS NO /u01/app/oracle/oradata/ORCLCDB/users01.dbf
8 100 PDB$SEED:UNDOTBS1 NO /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
9 280 ORCLPDB1:SYSTEM YES /u01/app/oracle/oradata/ORCLCDB/orclpdb1/system01.dbf
10 370 ORCLPDB1:SYSAUX NO /u01/app/oracle/oradata/ORCLCDB/orclpdb1/sysaux01.dbf
11 100 ORCLPDB1:UNDOTBS1 YES /u01/app/oracle/oradata/ORCLCDB/orclpdb1/undotbs01.dbf
12 342 ORCLPDB1:USERS NO /u01/app/oracle/oradata/ORCLCDB/orclpdb1/users01.dbf
13 280 ORCLPDB2:SYSTEM YES /u01/app/oracle/oradata/ORCLCDB/orclpdb2/system01.dbf
14 370 ORCLPDB2:SYSAUX NO /u01/app/oracle/oradata/ORCLCDB/orclpdb2/sysaux01.dbf
15 100 ORCLPDB2:UNDOTBS1 YES /u01/app/oracle/oradata/ORCLCDB/orclpdb2/undotbs01.dbf
16 342 ORCLPDB2:USERS NO /u01/app/oracle/oradata/ORCLCDB/orclpdb2/users01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1 131 TEMP 32767 /u01/app/oracle/oradata/ORCLCDB/temp01.dbf
2 36 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/ORCLCDB/pdbseed/temp012019-05-08_01-48-43-006-AM.dbf
3 36 ORCLPDB1:TEMP 32767 /u01/app/oracle/oradata/ORCLCDB/orclpdb1/temp01.dbf
4 36 ORCLPDB2:TEMP 32767 /u01/app/oracle/oradata/ORCLCDB/orclpdb2/temp01.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 one;
We launch the restore. We execute the script recover_table.bck, where it is indicated that we want to restore the table UNO just to the date and time before the deletion of the table. As you can see, what RMAN does once the RMAN backup is restored, is to export and import through an import the data of 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
channel ORA_DISK_1: SID=303 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
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: 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 requested point in time
set until time «to_date(’03/11/2022 22:14:14′,’dd/mm/yyyy hh24:mi:ss’)»;
# set destinations for recovery set and auxiliary set datafiles
.
.
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@oracle21c ~]$ 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 Database 19c 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 short, 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.