OTN Appreciation Day : Recovering Tables and Table Partitions from RMAN Backups

11 Oct OTN Appreciation Day : Recovering Tables and Table Partitions from RMAN Backups

As some of you may know , one of my very first errors back when I was using Oracle RDBMS 9.2 was to drop a table from production instead of development just for not following FIT-ACER , but back then recovering a table was not as easy , as you had to recover the tablespaces and the table dependencies and its tablespaces , and it was just a long and sometimes complicated mess. In 12.1 , it became very easy to do this recover, as the RECOVER TABLE command was introduced.

In 12c, RMAN allows you to recover one or more tables/table partitions to a specific point in time without affecting the rest of your objects in your container or pluggable databases and without the need to restore the entire containing tablespace. Before you restore your table, the following prerequisites need to be met:

• The target database must be in read-write mode.
• The target database must be in ARCHIVELOG mode.
• You must have RMAN backups of the tables or table partitions as they existed at the point in time to which you want recover these objects.
• You need to have at least 1 Gigabyte extra in memory for the auxiliary database.
• You need to have the physical disk space to hold the SYSTEM/SYSAUX/UNDO/TEMP, the physical disk space that was occupied by the table as well as the space of the datapump export that is generated.
• As of version 12.1, you need to use a SERVICE_NAME when connecting to RMAN or it will fail.
• You can use SCN, Time or a Log Sequence number
• Tables and table partitions on standby databases cannot be recovered.
• Tables with named NOT NULL constraints cannot be recovered with the REMAP option.

In the example below, we will restore the table TEST.RENE, with the command RECOVER TABLE, that was logically corrupted and remap the table to TEST.RENE_RCV, we are also running this from the container database CDB1, and so we have to specify that the table belongs to the pluggable database PDB1. Several lines have been removed to help readability

[email protected] [cdb1] /u01/app/oracle/exports
oracle $ rman target sys/oracle@cdb1
Recovery Manager: Release 12.1.0.1.0 - Production on Sun Oct 09 17:09:12 2016
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB1 (DBID=808250731)

RMAN> RECOVER TABLE 'TEST'.'RENE' OF PLUGGABLE DATABASE PDB1
2> UNTIL TIME "to_date('08-OCT-2016 17:08:20','dd-MON-yyyy hh24:mi:ss')"
3> AUXILIARY DESTINATION '/u01/app/oracle/exports'
4> REMAP TABLE 'TEST'.'RENE':'RENE_RCV';
…
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='obtp'

initialization parameters used for automatic instance:
db_name=CDB1
db_unique_name=obtp_pitr_PDB1_CDB1
compatible=12.1.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
diagnostic_dest=/u01/app/oracle
db_create_file_dest=/u01/app/oracle/exports
log_archive_dest_1='location=/u01/app/oracle/exports'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used

starting up automatic instance CDB1

Oracle instance started
…
Automatic instance created

…
Finished recover at 09/10/2016 17:12:24
sql statement: alter database open read only
contents of Memory Script:
{
sql clone 'alter pluggable database PDB1 open read only';
}
…
contents of Memory Script:
{
# set requested point in time
set until time "to_date('08-OCT-2016 17:08:20','dd-MON-yyyy hh24:mi:ss')";
# online the datafiles restored or switched
sql clone 'PDB1' "alter database datafile
10 online";
# recover and open resetlogs
recover clone database tablespace "PDB1":"USERS", "SYSTEM", "UNDOTBS1", "SYSAUX", "PDB1":"SYSTEM", "PDB1":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
…
starting media recovery
…
database opened
…
sql statement: alter pluggable database PDB1 open

contents of Memory Script:
{
# create directory for datapump import
sql 'PDB1' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/exports''";
…
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_obtp_nBpo":
…
EXPDP> . . exported "TEST"."RENE" 5.062 KB 1 rows
EXPDP> Master table "SYS"."TSPITR_EXP_obtp_nBpo" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_obtp_nBpo is:
EXPDP> /u01/app/oracle/exports/tspitr_obtp_55157.dmp
EXPDP> Job "SYS"."TSPITR_EXP_obtp_nBpo" successfully completed at Sun Oct 9 17:13:54 2016 elapsed 0 00:00:25
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_obtp_zdmu" successfully loaded/unloaded
…
IMPDP> . . imported "TEST"."RENE_RCV" 5.062 KB 1 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_obtp_zdmu" successfully completed at Sun Oct 9 17:14:10 2016 elapsed 0 00:00:04
Import completed
…
Removing automatic instance
Automatic instance removed
…
Finished recover at 09/10/2016 17:14:13

Hope this helps you out and thank you OTN for everything #ThanksOTN