Point-in-time recovery of a pluggable database.

Point-in-time recovery of a pluggable database:

In Oracle Database 12c, it is possible to do an incomplete recovery of a pluggable database without affecting other pluggable databases or the container database. The mechanism is similar to a database point-in-time recovery. An auxiliary instance is required to stage the restore and recovery.

The auxiliary instance consists of

1. The entire set of tablespaces (system, sysaux and others) belonging to the target pdb.
2. Tablespaces from the container database which are needed to perform point-in-time recovery (at least the system, sysaux, and undo).

After the restore and recovery is complete, the target pluggable database is at its desired recovery point.
The auxiliary instance gets dropped and pluggable database is ready to be opened for use.

Scenario:

Container db name : CDB1
Tenant dbs        : salespdb, hrpdb
Target pdb        : salespdb
Recovery point   : recover to a specific scn, at which a table sales.sales was known to exist in SALESPDB.

Pre-requisites :

• The target pdb salespdb must be closed.
• Adequate disk space should be provisioned to stage the auxiliary instance.
• A backup of the pdb and the container db should have been taken before the recovery point.
• Archive logs must exist to do media recovery from the time of the backup until the desired recovery point.

Procedure :
Connect to the root container from rman and execute the run block below.

RMAN> run {
2>  set until scn 1522385;
3>  restore pluggable database salespdb;
4>  recover pluggable database salespdb auxiliary destination '/u01/app/oracle/oradata/tmp';
5> }


Note:
In the run block above, a recovery point scn is specified first. This is the scn to which the named pdb will
be recovered. The recover statement in the run block also specifies an auxiliary destination folder for
staging the auxiliary instance.This run block will restore and recover pluggable database called salespdb to a specific recovery point.

After recovery, we will examines the record count of a dropped table after recovery to make sure its data has been recovered.

RMAN generates and executes a series of memory scripts to achieve the restore/recover.
The scripts and rman output are discussed below.

1) First, RMAN does an in-place restore of the pdb datafiles from an appropriate backupset according to the specified recovery point scn. 

RMAN> run {
2>  set until scn 1522385;
3>  restore pluggable database salespdb;
4>  recover pluggable database salespdb auxiliary destination '/u01/app/oracle/oradata/tmp';
5> }

executing command: SET until clause

Starting restore at 20-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/cdb1/salespdb/system.ora
channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/cdb1/salespdb/sysaux.ora
channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/cdb1/salespdb/users.ora
channel ORA_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/cdb1/salespdb/ts_sales.ora
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/oradata/fra/CDB1/F75C25CAA70D1833E0430100007F21F9/backupset/2014_04_19/o1_mf_nnndf_TAG20140419T103559_9o3r9wnb_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/oradata/fra/CDB1/F75C25CAA70D1833E0430100007F21F9/backupset/2014_04_19/o1_mf_nnndf_TAG20140419T103559_9o3r9wnb_.bkp tag=TAG20140419T103559
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 20-APR-14

Starting recover at 20-APR-14
using 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 SYSTEM
Tablespace UNDOTBS

Creating automatic instance, with SID='BvDq'

initialization parameters used for automatic instance:
db_name=CDB1
db_unique_name=BvDq_pitr_salespdb_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/oradata/tmp
log_archive_dest_1='location=/u01/app/oracle/oradata/tmp'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used


starting up automatic instance CDB1

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes
Variable Size                281019648 bytes
Database Buffers             780140544 bytes
Redo Buffers                   5480448 bytes
Automatic instance created

2) Next, RMAN prepares a controlfile for the auxiliary instance from a suitable backup of the CDB.

contents of Memory Script:
{
# set requested point in time
set until  scn 1522385;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
}
executing Memory Script

executing command: SET until clause

Starting restore at 20-APR-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=75 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/oradata/fra/CDB1/autobackup/2014_04_19/o1_mf_s_845289482_9o3rdc8b_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/oradata/fra/CDB1/autobackup/2014_04_19/o1_mf_s_845289482_9o3rdc8b_.bkp tag=TAG20140419T103802
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:09
output file name=/u01/app/oracle/oradata/tmp/CDB1/controlfile/o1_mf_9o64vdxg_.ctl
Finished restore at 20-APR-14

sql statement: alter database mount clone database

3) RMAN finalizes the datafiles for the auxiliary instance in this step by including any files from the CDB itself which may be needed for the recovery.

The system, undo, sysaux, and users tablespace from the container database are needed to create the auxiliary instance. Suitable backups of those CDB data files are being restored below to the auxiliary destination and assigned OMF names with set newname for ... to new. The auxiliary instance controlfile is updated with the new locations with a series of switch statements to prepare it for the media recovery stage.


contents of Memory Script:
{
# set requested point in time
set until  scn 1522385;
# switch to valid datafilecopies
switch clone datafile  11 to datafilecopy 
 "/u01/app/oracle/oradata/cdb1/salespdb/system.ora";
switch clone datafile  12 to datafilecopy 
 "/u01/app/oracle/oradata/cdb1/salespdb/sysaux.ora";
switch clone datafile  13 to datafilecopy 
 "/u01/app/oracle/oradata/cdb1/salespdb/users.ora";
switch clone datafile  14 to datafilecopy 
 "/u01/app/oracle/oradata/cdb1/salespdb/ts_sales.ora";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  5 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  6 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 5, 3, 6;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

datafile 11 switched to datafile copy
input datafile copy RECID=1 STAMP=845367813 file name=/u01/app/oracle/oradata/cdb1/salespdb/system.ora

datafile 12 switched to datafile copy
input datafile copy RECID=2 STAMP=845367813 file name=/u01/app/oracle/oradata/cdb1/salespdb/sysaux.ora

datafile 13 switched to datafile copy
input datafile copy RECID=3 STAMP=845367813 file name=/u01/app/oracle/oradata/cdb1/salespdb/users.ora

datafile 14 switched to datafile copy
input datafile copy RECID=4 STAMP=845367813 file name=/u01/app/oracle/oradata/cdb1/salespdb/ts_sales.ora

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 20-APR-14
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/tmp/CDB1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_undotbs_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/oradata/fra/CDB1/backupset/2014_04_19/o1_mf_nnndf_TAG20140419T103559_9o3r8hgk_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/oradata/fra/CDB1/backupset/2014_04_19/o1_mf_nnndf_TAG20140419T103559_9o3r8hgk_.bkp tag=TAG20140419T103559
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:36
Finished restore at 20-APR-14

datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=845367911 file name=/u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_system_9o64w899_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=845367911 file name=/u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_undotbs_9o64w8n2_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=845367911 file name=/u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_sysaux_9o64w8nc_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=845367911 file name=/u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_users_9o64w8qj_.dbf

4) Taking all clone datafiles online to enable media recovery, and performing media recovery, followed by auxiliary instance drop at the end.

contents of Memory Script:
{
# set requested point in time
set until  scn 1522385;
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  5 online";
sql clone "alter database datafile  3 online";
sql clone 'SALESPDB' "alter database datafile 
 11 online";
sql clone 'SALESPDB' "alter database datafile 
 12 online";
sql clone 'SALESPDB' "alter database datafile 
 13 online";
sql clone 'SALESPDB' "alter database datafile 
 14 online";
sql clone "alter database datafile  6 online";
# recover pdb
recover clone database tablespace  "SYSTEM", "UNDOTBS", "SYSAUX", "USERS" pluggable database 
 'SALESPDB'   delete archivelog;
sql clone 'alter database open read only';
plsql <<<begin
   add_dropped_ts;
end; >>>;
plsql <<<begin
   save_pdb_clean_scn;
end; >>>;
# shutdown clone before import
shutdown clone abort
plsql <<<begin
   pdbpitr_inspect(pdbname =>  'SALESPDB');
end; >>>;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  5 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  11 online

sql statement: alter database datafile  12 online

sql statement: alter database datafile  13 online

sql statement: alter database datafile  14 online

sql statement: alter database datafile  6 online

Starting recover at 20-APR-14
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 47 is already on disk as file /u01/app/oracle/oradata/fra/CDB1/archivelog/2014_04_19/o1_mf_1_47_9o3rd8fo_.arc
archived log for thread 1 with sequence 48 is already on disk as file /u01/app/oracle/oradata/fra/CDB1/archivelog/2014_04_19/o1_mf_1_48_9o3tjwyy_.arc
archived log for thread 1 with sequence 49 is already on disk as file /u01/app/oracle/oradata/fra/CDB1/archivelog/2014_04_20/o1_mf_1_49_9o60dlml_.arc
archived log for thread 1 with sequence 50 is already on disk as file /u01/app/oracle/oradata/fra/CDB1/archivelog/2014_04_20/o1_mf_1_50_9o60rgmt_.arc
archived log file name=/u01/app/oracle/oradata/fra/CDB1/archivelog/2014_04_19/o1_mf_1_47_9o3rd8fo_.arc thread=1 sequence=47
archived log file name=/u01/app/oracle/oradata/fra/CDB1/archivelog/2014_04_19/o1_mf_1_48_9o3tjwyy_.arc thread=1 sequence=48
archived log file name=/u01/app/oracle/oradata/fra/CDB1/archivelog/2014_04_20/o1_mf_1_49_9o60dlml_.arc thread=1 sequence=49
archived log file name=/u01/app/oracle/oradata/fra/CDB1/archivelog/2014_04_20/o1_mf_1_50_9o60rgmt_.arc thread=1 sequence=50
media recovery complete, elapsed time: 00:00:18
Finished recover at 20-APR-14

sql statement: alter database open read only



Oracle instance shut down


Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_sysaux_9o64w8nc_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/tmp/CDB1/controlfile/o1_mf_9o64vdxg_.ctl deleted
Finished recover at 20-APR-14

5) Opening the newly recovered pdb from SqlPlus and checking the sales.sales table.

SQL> alter pluggable database salespdb open resetlogs;

Pluggable database altered.

SQL> select count(*) from sales;

  COUNT(*)
----------
        89

Conclusion:

The point-in-time restore of a pluggable database is similar to the point-in-time restore of a noncdb.
But the point-in-time recovery of a pluggable database is also a bit different because it requires some datafiles from the container database. Other pluggable databases can continue to operate and be available while the recovery is in progress and after it also.

RMAN 12c feature - network based restore

In 12c Data Guard environments, RMAN can recover/restore using a new network-enabled restore method. During a recovery/restore RMAN can now retrieve the datafiles/logs/backupsets that it needs for a recovery operation from its remote Data Guard counterpart.

The files needed for a restore/recovery operation are transferred to the requesting site using compressed backupsets to reduce network overhead. This technique is called network-based restore.

New syntax is introduced to support this. There is a new RESTORE FROM SERVICE <servicename> command to perform this, and it is used in memory scripts generated, for instance, during database duplication. The service name can point to a tns entry for the remote DG counterpart site.

RESTORE FROM SERVICE <dbSERVICE> <RESTOREOBJ>

Therefore, there are now two techniques available for database duplication, depending on mode of connection, number of auxiliary channels allocated and duplicate command-line options specified.

• Existing image copy method (push based) where target channels do most of the duplication.
• The new backupsets method (pull based) where the auxiliary channels do the bulk of the work.

Possible advantages of backupset method:

• Auxiliary channels do the bulk of the duplication effort, so the target database - if its  a production database - does not incur the overhead of the operation. 
• Backupsets are compact, smaller than datafiles, and are more efficient to transport over the network. User can specify that compressed backupsets be used.

The network based restore operation is seen in the rman memory scripts generated during the duplicaton of a 12c primary database as a standby.

The steps for creating a physical standby for a container database are the same as for noncdbs.
Data guard specific init params need to be set on both instnaces, tnsnames networking needs to be configured, standby logs, and archive log destinations need to be configured .

The auxiliary instance needs to be set up using a temp init.ora and temp password file, and should be  be running in nomount mode, registered statically with its listener.

The section below discusses the RMAN actions and memory scripts generated during the duplication of a CDB called CDB1 as a physical standby.

The method used here is active database duplication.


Connect to primary as target and to the standby as auxiliary from rman and issue the DUPLICATE DATABASE command.

$ > rman target sys/oracle@cdb1_scott auxiliary sys/oracle@cdb1_tiger
Issue the duplicate database command shown below.

duplicate target database for standby 
 from active database
 nofilenamecheck dorecover
  spfile
     parameter_value_convert 'scott','tiger'
      set db_unique_name 'TIGER'
      set log_archive_dest_2 'service=CDB1 async valid_for=(online_logfiles,primary_role) db_unique_name=CDB1';

The main duplication actions that follow are discussed below.
    

     1)  Copy the password file and spfile from primary (password file copy done implicitly when duplicating for creating physical standby)

contents of Memory Script: (edited for clarity)
     {
        backup as copy reuse
        targetfile  '/u01/.../dbhome_1/dbs/orapwCDB1' 
            auxiliary format '/u01/app/.../dbhome_1/dbs/orapwCDB1'   ;
        restore clone from service  'cdb1_scott' spfile to
            '/u01/app/.../dbhome_1/dbs/spfileCDB1.ora';
        sql clone "alter system set spfile= ''/u01/app/...dbhome_1/dbs/spfileCDB1.ora''";
    }

     2) Set up requested site-specific init params on standby as specified in the DUPLICATE ...SPFILE SET option.

    
          contents of Memory Script:
          {
             sql clone "alter system set  db_unique_name =  ''TIGER'' comment=
           '''' scope=spfile";
             sql clone "alter system set  log_archive_dest_2 = ''service=CDB1 async valid_for=(online_logfiles,primary_role) db_unique_name=CDB1'' comment=
           '''' scope=spfile";
             shutdown clone immediate;
             startup clone nomount;
          }    

     3) Restore primary controlfile to standby as a standby controlfile. Note that this is a network-based restore from an appropriate controlfile backupset.

          contents of Memory Script:
          {
             restore clone from service  'cdb1_scott' standby controlfile;
          }    
          channel ORA_AUX_DISK_1: starting datafile backup set restore
          channel ORA_AUX_DISK_1: using network backup set from service cdb1_scott
          channel ORA_AUX_DISK_1: restoring control file
          channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
          output file name=/u01/app/oracle/oradata/cdb1/control1.ctl
          output file name=/u01/app/oracle/oradata/cdb1/control2.ctl
          Finished restore at 02-FEB-14

     4) Mount the standby database using the restored controlfile.

          contents of Memory Script:
          {
             sql clone 'alter database mount standby database';
          }

     5) Restore all datafiles. RMAN uses the restore from service syntax

  
  executing Memory Script

  contents of Memory Script:
  {
     set newname for tempfile  1 to  "/u01/app/oracle/oradata/cdb1/temp.ora";
       ...
     set newname for tempfile  7 to  "/u01/app/oracle/oradata/cdb1/exnoncdb/temp.ora";
     switch clone tempfile all;

     set newname for datafile  1 to  "/u01/app/oracle/oradata/cdb1/system.ora";
     ...
     set newname for datafile  55 to  "/u01/app/oracle/oradata/cdb1/salespdb/ts_recovery.ora";

     restore  from service  'cdb1_scott'  clone database
     ;
     sql 'alter system archive log current';
  }   
  channel ORA_AUX_DISK_1: starting datafile backup set restore
  channel ORA_AUX_DISK_1: using network backup set from service cdb1_scott
  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/cdb1/system.ora
  ...
  ...
  ...
  channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
  channel ORA_AUX_DISK_1: restoring datafile 00055 to /u01/app/oracle/oradata/cdb1/salespdb/ts_recovery.ora
  channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
  Finished restore at 02-FEB-14

  6) Recover the datafiles to the recovery point specified, if any.

    Note below, that the archive log files with sequence 59 and 60 are being pulled from primary.
    Then they are applied to the standby instance datafiles.
       
  contents of Memory Script:
  {
     restore clone force from service  'cdb1_scott'
          archivelog from scn  1871401;
     switch clone datafile all;
  }    
  channel ORA_AUX_DISK_1: starting archived log restore to default destination
  channel ORA_AUX_DISK_1: using network backup set from service cdb1_scott

  channel ORA_AUX_DISK_1: restoring archived log
  archived log thread=1 sequence=59
  channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
  channel ORA_AUX_DISK_1: starting archived log restore to default destination
  channel ORA_AUX_DISK_1: using network backup set from service cdb1_scott
  channel ORA_AUX_DISK_1: restoring archived log
  archived log thread=1 sequence=60
  channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
  Finished restore at 02-FEB-14
  contents of Memory Script:
 {
    set until scn  1873330;
    recover
    standby
    clone database
    delete archivelog
   ;
 }
 executing Memory Script

 executing command: SET until clause

 Starting recover at 02-FEB-14
 using channel ORA_AUX_DISK_1

 starting media recovery

 archived log for thread 1 with sequence 59 is already on disk as file /u01/.../arch1_59_837593794.dbf
 archived log for thread 1 with sequence 60 is already on disk as file /u01/.../arch1_60_837593794.dbf
 archived log file name=/u01/.../arch1_59_837593794.dbf thread=1 sequence=59
 archived log file name=/u01/.../arch1_60_837593794.dbf thread=1 sequence=60
 media recovery complete, elapsed time: 00:00:03
 Finished recover at 02-FEB-14
 Finished Duplicate Db at 02-FEB-14    

Restoring a 12c container database after loss of all datafiles, spfiles and control files.

Restoring a container database after loss of all of its files.

 

The restore will be done using a multisection image copy backup.

Oracle database 12c has a multisection image copy backup feature - which allows multiple sections of a huge datafile to be backed up in parallel but still producing a single output file per datafile.

In 11g, the multisection backup feature existed, but it created a multipart backupset and not an image copy.Image copies are much faster to restore and recover than backupsets – they are duplicates of the data files ( though they are larger).In this demonstration, we will use a multisection image copy backup and a controlfile autobackup to restore a fully destroyed multitenant container database.

Scenario:

The platform is Red Hat Linux 5 64 bit, running Oracle 12c release 1.
A CDB existed called CDB1, it had these tenant databases. It needs to be restored.

PDB$SEED – the seed database.
SALESPDB
SALESTEST
MARKETING
EXNONCDB
The CDB was operating in noarchivelog mode, and a backup had been taken in mount mode.
The CDB had been backed up using the multisection image copy backup feature, control file and spfile are also backed up due to the controlfile autobackup feature.
The spfile, password file, and the directory tree containing the CDB files and folders have all been deleted. There are no archived logs or online logs to apply and there is no media recovery to do.

This exercise will demonstrate recovery of the cdb after complete loss of all the datafiles, controlfiles and redo log files.

 1. Create a temp init.ora file containing these settings

DB_NAME=CDB1
CONTROL_FILES='/u01/app/oracle/oradata/cdb1/control1.ctl','/u01/app/oracle/oradata/cdb1/control2.ctl';
ENABLE_PLUGGABLE_DATABASE=TRUE;
2. Create a password file using orapwd, if you also lost the password file.
3. Retrieve the original database's dbid

If not using an fra, its part of the controlfile autobackup file name --> 802949696

  /u01/app/oracle/product/12.1.0/dbhome_1/dbs/c-802949696-20140105-00

4. Start up rman

[oracle@laboms cdb1]$ export ORACLE_SID=CDB1
[oracle@laboms cdb1]$ rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Jan 8 08:16:32 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)


5. Startup the instance in nomount mode using the pfile created in step 1.
RMAN> startup nomount pfile='init.ora';

Oracle instance started

Total System Global Area     229683200 bytes

Fixed Size                     2286800 bytes
Variable Size                171969328 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5095424 bytes
6. Set the DBID, restore the spfile from the controlfile autobackup.
RMAN> SET DBID 802949696;

executing command: SET DBID

RMAN> set controlfile autobackup format for device type disk to '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN> restore spfile from autobackup;

Starting restore at 08-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140108
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140107
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140106
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140105
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/oracle/product/12.1.0/dbhome_1/dbs/c-802949696-20140105-00
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/product/12.1.0/dbhome_1/dbs/c-802949696-20140105-00
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 08-JAN-14


7. Start up the database using the restored spfile.
RMAN> startup force nomount;

Oracle instance started

Total System Global Area    1570009088 bytes

Fixed Size                     2288776 bytes
Variable Size               1342178168 bytes
Database Buffers             218103808 bytes
Redo Buffers                   7438336 bytes

8. Restore the controlfile from the backup;
RMAN> restore controlfile from autobackup;

Starting restore at 08-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140108
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140107
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140106
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140105
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/oracle/product/12.1.0/dbhome_1/dbs/c-802949696-20140105-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/product/12.1.0/dbhome_1/dbs/c-802949696-20140105-00
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/cdb1/control1.ctl
output file name=/u01/app/oracle/oradata/cdb1/control2.ctl
Finished restore at 08-JAN-14

9. Mount the database.
RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1

10. Examine the controlfile metadata, to make sure restore destination folders exist (optional).

RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name CDB1

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    0        SYSTEM               ***     /u01/app/oracle/oradata/cdb1/system.ora
2    0        PDB$SEED:SYSTEM      ***     /u01/app/oracle/oradata/cdb1/seed/system.ora
3    0        SYSAUX               ***     /u01/app/oracle/oradata/cdb1/sysaux.ora
4    0        PDB$SEED:SYSAUX      ***     /u01/app/oracle/oradata/cdb1/seed/sysaux.ora
5    0        UNDOTBS              ***     /u01/app/oracle/oradata/cdb1/undo.ora
6    0        USERS                ***     /u01/app/oracle/oradata/cdb1/users.ora
7    0        PDB$SEED:USERS       ***     /u01/app/oracle/oradata/cdb1/seed/users.ora
23   0        SALESPDB:SYSTEM      ***     /u01/app/oracle/oradata/cdb1/salespdb/system.ora
24   0        SALESPDB:SYSAUX      ***     /u01/app/oracle/oradata/cdb1/salespdb/sysaux.ora
25   0        SALESPDB:USERS       ***     /u01/app/oracle/oradata/cdb1/salespdb/users.ora
26   0        SALESPDB:SALES       ***     /u01/app/oracle/oradata/cdb1/salespdb/sales.ora
32   0        SALESTEST:SYSTEM     ***     /u01/app/oracle/oradata/cdb1/salestest/system.ora
33   0        SALESTEST:SYSAUX     ***     /u01/app/oracle/oradata/cdb1/salestest/sysaux.ora
34   0        SALESTEST:USERS      ***     /u01/app/oracle/oradata/cdb1/salestest/users.ora
35   0        SALESTEST:TS_SALESTEST ***     /u01/app/oracle/oradata/cdb1/salestest/ts_salestest.ora
36   0        MARKETING:SYSTEM     ***     /u01/app/oracle/oradata/cdb1/marketing/system.ora
37   0        MARKETING:SYSAUX     ***     /u01/app/oracle/oradata/cdb1/marketing/sysaux.ora
38   0        MARKETING:USERS      ***     /u01/app/oracle/oradata/cdb1/marketing/users.ora
39   0        MARKETING:TS_SALESTEST ***     /u01/app/oracle/oradata/cdb1/marketing/ts_marketing.ora
50   0        EXNONCDB:SYSTEM      ***     /u01/app/oracle/oradata/cdb1/exnoncdb/system.ora
51   0        EXNONCDB:SYSAUX      ***     /u01/app/oracle/oradata/cdb1/exnoncdb/sysaux.ora
52   0        EXNONCDB:USERS       ***     /u01/app/oracle/oradata/cdb1/exnoncdb/users.ora
53   0        EXNONCDB:ILMTBS      ***     /u01/app/oracle/oradata/cdb1/exnoncdb/ilmts.ora
54   0        EXNONCDB:LOW_COST_STORE ***     /u01/app/oracle/oradata/cdb1/exnoncdb/lowcostts.ora

List of Temporary Files
=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u01/app/oracle/oradata/cdb1/temp.ora
2    20       PDB$SEED:TEMP        32767       /u01/app/oracle/oradata/cdb1/seed/temp.ora
3    20       SALESPDB:TEMP        32767       /u01/app/oracle/oradata/cdb1/salespdb/temp.ora
4    20       SALESTEST:TEMP       32767       /u01/app/oracle/oradata/cdb1/salestest/temp.ora
5    15       SALESPDB:TEMPSALES   15          /u01/app/oracle/oradata/cdb1/salespdb/tempsales.ora
6    20       MARKETING:TEMP       32767       /u01/app/oracle/oradata/cdb1/marketing/temp.ora
7    20       EXNONCDB:TEMP        32767       /u01/app/oracle/oradata/cdb1/exnoncdb/temp.ora


11. Create any missing destinations
In the case of the example these folders were created under /u01/app/oracle/oradata/cdb1

seed
salespdb
salestest
marketing
exnoncdb
audit



12. Restore the database (note below that the rman is using input datafilecopy - indicates that an image copy is being used for restore).
RMAN> restore database;

Starting restore at 08-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK

channel ORA_DISK_1: restoring datafile 00001
input datafile copy RECID=2 STAMP=836068929 file name=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/data_D-CDB1_I-802949696_TS-SYSTEM_FNO-1_05otapi1
destination for restore of datafile 00001: /u01/app/oracle/oradata/cdb1/system.ora
...
...
...
channel ORA_DISK_1: restoring datafile 00054
input datafile copy RECID=22 STAMP=836069342 file name=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/data_D-CDB1_I-802949696_TS-LOW_COST_STORE_FNO-54_1cotaput
destination for restore of datafile 00054: /u01/app/oracle/oradata/cdb1/exnoncdb/lowcostts.ora
channel ORA_DISK_1: copied datafile copy of datafile 00054
output file name=/u01/app/oracle/oradata/cdb1/exnoncdb/lowcostts.ora RECID=0 STAMP=0
Finished restore at 08-JAN-14


13. Open the database with resetlogs
RMAN> alter database open resetlogs;

Statement processed

14. Exit rman and examine the db in SqlPlus
RMAN> exit


Recovery Manager complete.


[oracle@laboms cdb1]$ sqlplus sys/oracle@CDB1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jan 8 09:45:17 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> select pdb_name from cdb_pdbs;

PDB_NAME
--------------------------------------------------------------------------------
PDB$SEED
MARKETING
SALESTEST
SALESPDB
EXNONCDB

Restore a table's specific partition to a specific point in time

The ability to restore a specific Table/partition is new in 12c.

The procedures are analogous to the tablespace Point In Time recovery (PITR) of 11g.
Table/partition point-in-time restore can be considered an evolution of tablespace PITR.
This procedure is indicated for cases where:

• Only a few tables need PITR but they are in a tablespace which contains many other tables.
• Execution of DDL on the table or the lack of UNDO prevents use of FLASHBACK TABLE.

 About table/partition PITR

• Available for CDBs and non-cdbs.
• Uses an auxiliary instance to recover the specified tables to specified point.
• Uses available backups/archive logs to achieve PITR.
• Uses a data pump dump to optionally export the recovered tablespace or tables back to the target database.

Scenario

A CDB called CDB1 contains a PDB called SALESPDB.
The PDB has a partitioned table called sales.
This is a point-in-time recovery to a specific SCN, 495664950.

It has these partitions.

SQL> select table_owner, table_name, partition_name
from dba_tab_partitions where table_name='SALES';
  2 
TABLE_OWNER          TABLE_NAME           PARTITION_NAME
-------------------- -------------------- --------------------
SALESADMIN           SALES                P2010
SALESADMIN           SALES                P2011
SALESADMIN           SALES                P2012
SALESADMIN           SALES                P2013

SQL> select count(*) from salesadmin.sales partition(p2010);

  COUNT(*)
----------
       364
It will be recovered as a new table named P2010, to another tablespace called TS_RECOVERY.

The partition P2010 will be truncated at the below SCN, to simulate the data loss.


SQL> select current_scn from v$database;

CURRENT_SCN
-----------
  495664950
 

SQL> alter table salesadmin.sales truncate partition p2010;

Table truncated.
 

Using RMAN to restore the table

The RMAN syntax for the point-in-time recovery of the table partition is :

recover table salesadmin.sales:p2010
   of pluggable database salespdb
    until scn 495664950
    remap tablespace salespdb:ts_recov
     auxiliary destination '/u01/app/oracle/oradata/tmp';

controlfile backup checkpoint scn   : 1470397
restore point scn                            : 495664950
partition restore target tablespace : TS_RECOV
partition restore target schema      : SALESADMIN
partition restore destination table  : P2010


Precautions:

• Ensure backups of cdb, controlfile, are available for an scn earlier than recovery point.
• Ensure that archive logs are available to recover data upto the desired point.
• Ensure enough disk space is available for auxiliary instance and the recovery operation.
• Ensure that table owner has enough quota in destination tablespace for the restored partitions.
• An auxiliary destination must be specified otherwise the command will fail.

The main steps executed in the rman-generated memory scripts are

1. Auxiliary instance creation and startup in nomount mode
2. Memory script to restore a suitable controlfile from a backup and mount it
3. Restoring the system files of the CDB and PDB – auxiliary set (system,sysaux,undo)
4. Memory script to perform media recovery of the auxiliary set to scn 495664950.
5. Restart the auxiliary instance with an spfile and mount it.
6. Restore datafile 9, which contains the actual user data which needs to be recovered
7. Performing media recovery on the auxiliary data files and opens the auxiliary instance.
8. Open the PDB, create data pump directory objects in target as well as auxiliary.
9. Shut down the auxiliary instance and start export/import job.
10. The remainder of the document shows the RMAN recover table session highlights its actions

The remainder of the document shows the RMAN recover table session highlights its actions

Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jan 27 12:24:19 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDB1 (DBID=801127382)

RMAN> recover table salesadmin.sales:p2010
   of pluggable database salespdb
    until scn 495664950
    remap tablespace salespdb:ts_recov
     auxiliary destination '/u01/app/oracle/oradata/tmp';
2> 3> 4> 5>

Auxiliary instance creation and startup in nomount mode

Starting recover at 27-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 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='kxnv'

initialization parameters used for automatic instance:
db_name=CDB1
db_unique_name=kxnv_pitr_salespdb_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_16k_cache_size=128M
db_create_file_dest=/u01/app/oracle/oradata/tmp
log_archive_dest_1='location=/u01/app/oracle/oradata/tmp'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used


starting up automatic instance CDB1

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes
Variable Size                251659520 bytes
Database Buffers             809500672 bytes
Redo Buffers                   5480448 bytes
Automatic instance created

Memory script to restore a suitable controlfile from a backup and mount it

contents of Memory Script:
{
# set requested point in time
set until  scn 495664950;
# 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 27-JAN-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=75 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/fast_recovery_area/CDB1/autobackup/2014_01_27/o1_mf_s_837947651_9gcmr5xn_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2014_01_27/o1_mf_s_837947651_9gcmr5xn_.bkp tag=TAG20140127T111411
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/oradata/tmp/CDB1/controlfile/o1_mf_9gcqw1mm_.ctl
Finished restore at 27-JAN-14

sql statement: alter database mount clone database

sql statement: alter system archive log current

Restoring the system files of the CDB and PDB - auxiliary set only (system,sysaux,undo)

contents of Memory Script:
{
# set requested point in time
set until  scn 495664950;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  5 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  7 to new;
set newname for clone datafile  8 to new;
set newname for clone tempfile  1 to new;
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, 5, 3, 7, 8;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 27-JAN-14
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/tmp/CDB1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/backupset/2014_01_27/o1_mf_nnndf_TAG20140127T110930_9gcmhckh_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2014_01_27/o1_mf_nnndf_TAG20140127T110930_9gcmhckh_.bkp tag=TAG20140127T110930
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:46
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/oradata/tmp/CDB1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/E8B1D5DF593A7F63E0436801A8C0DFEC/backupset/2014_01_27/o1_mf_nnndf_TAG20140127T110930_9gcmln77_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/E8B1D5DF593A7F63E0436801A8C0DFEC/backupset/2014_01_27/o1_mf_nnndf_TAG20140127T110930_9gcmln77_.bkp tag=TAG20140127T110930
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 27-JAN-14

datafile 1 switched to datafile copy
input datafile copy RECID=13 STAMP=837952121 file name=/u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_system_9gcqw98r_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=837952121 file name=/u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_undotbs1_9gcqw96p_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=837952121 file name=/u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_sysaux_9gcqw98n_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=16 STAMP=837952121 file name=/u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_system_9gcr1gbf_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=17 STAMP=837952121 file name=/u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_sysaux_9gcr1gbd_.dbf

Memory script to perform media recovery of the auxiliary set data files to scn 495664950

contents of Memory Script:
{
# set requested point in time
set until  scn 495664950;
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  5 online";
sql clone "alter database datafile  3 online";
sql clone 'SALESPDB' "alter database datafile
 7 online";
sql clone 'SALESPDB' "alter database datafile
 8 online";
# recover and open database read only
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX", "SALESPDB":"SYSTEM", "SALESPDB":"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  5 online
sql statement: alter database datafile  3 online
sql statement: alter database datafile  7 online
sql statement: alter database datafile  8 online

Starting recover at 27-JAN-14
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 237 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2014_01_27/o1_mf_1_237_9gcn8mqb_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2014_01_27/o1_mf_1_237_9gcn8mqb_.arc thread=1 sequence=237
media recovery complete, elapsed time: 00:00:01
Finished recover at 27-JAN-14

sql statement: alter database open read only

 

Restart the auxiliary instance with an spfile and mount it.

contents of Memory Script:
{
sql clone 'alter pluggable database  SALESPDB open read only';
}
executing Memory Script

sql statement: alter pluggable database  SALESPDB 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/oradata/tmp/CDB1/controlfile/o1_mf_9gcqw1mm_.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    1068937216 bytes

Fixed Size                     2296576 bytes
Variable Size                255853824 bytes
Database Buffers             805306368 bytes
Redo Buffers                   5480448 bytes

sql statement: alter system set  control_files =   ''/u01/app/oracle/oradata/tmp/CDB1/controlfile/o1_mf_9gcqw1mm_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes
Variable Size                255853824 bytes
Database Buffers             805306368 bytes
Redo Buffers                   5480448 bytes

sql statement: alter database mount clone database

Restore datafile 9, which contains the actual user data which needs to be recovered

contents of Memory Script:
{
# set requested point in time
set until  scn 495664950;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  9 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  9;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 27-JAN-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=11 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 00009 to /u01/app/oracle/oradata/tmp/KXNV_PITR_SALESPDB_CDB1/datafile/o1_mf_salespdb_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/E8B1D5DF593A7F63E0436801A8C0DFEC/backupset/2014_01_27/o1_mf_nnndf_TAG20140127T110930_9gcmln77_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/E8B1D5DF593A7F63E0436801A8C0DFEC/backupset/2014_01_27/o1_mf_nnndf_TAG20140127T110930_9gcmln77_.bkp tag=TAG20140127T110930
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 27-JAN-14

datafile 9 switched to datafile copy
input datafile copy RECID=19 STAMP=837952172 file name=/u01/app/oracle/oradata/tmp/KXNV_PITR_SALESPDB_CDB1/datafile/o1_mf_salespdb_9gcr55b5_.dbf

Performing media recovery on the auxiliary data files and open the aux CDB database

contents of Memory Script:
{
# set requested point in time
set until  scn 495664950;
# online the datafiles restored or switched
sql clone 'SALESPDB' "alter database datafile
 9 online";
# recover and open resetlogs
recover clone database tablespace  "SALESPDB":"SALESPDB", "SYSTEM", "UNDOTBS1", "SYSAUX", "SALESPDB":"SYSTEM", "SALESPDB":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  9 online

Starting recover at 27-JAN-14
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 237 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2014_01_27/o1_mf_1_237_9gcn8mqb_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2014_01_27/o1_mf_1_237_9gcn8mqb_.arc thread=1 sequence=237
media recovery complete, elapsed time: 00:00:00
Finished recover at 27-JAN-14

database opened

Opening the auxiliary instance PDB

contents of Memory Script:
{
sql clone 'alter pluggable database  SALESPDB open';
}
executing Memory Script

sql statement: alter pluggable database  SALESPDB open

Creating the directory objects in the target and auxiliary instance

contents of Memory Script:
{
# create directory for datapump import
sql 'SALESPDB' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/oradata/tmp''";
# create directory for datapump export
sql clone 'SALESPDB' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/oradata/tmp''";
}
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata/tmp''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata/tmp''

Exporting the recovered data to a .dmp file

Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_kxnv_Ewvl": 
   EXPDP> Estimate in progress using BLOCKS method...
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Total estimation using BLOCKS method: 8 MB
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   EXPDP> . . exported "SALESADMIN"."SALES":"P2010"                12.62 KB     364 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_kxnv_Ewvl" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_kxnv_Ewvl is:
   EXPDP>   /u01/app/oracle/oradata/tmp/tspitr_kxnv_86390.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_kxnv_Ewvl" successfully completed at Mon Jan 27 12:30:59 2014 elapsed 0 00:00:41
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script

Oracle instance shut down

Importing the data back to the target database according to the remaps specified

Performing import of tables...
   IMPDP> Master table "SYS"."TSPITR_IMP_kxnv_fnzi" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_kxnv_fnzi": 
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "SALESADMIN"."SALES_P2010"                  12.62 KB     364 rows
   IMPDP> Job "SYS"."TSPITR_IMP_kxnv_fnzi" successfully completed at Mon Jan 27 12:31:16 2014 elapsed 0 00:00:09
Import completed


Cleaning up the auxiliary instance

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_temp_9gcr4b9f_.tmp deleted
auxiliary instance file /u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_temp_9gcr3xfk_.tmp deleted
auxiliary instance file /u01/app/oracle/oradata/tmp/KXNV_PITR_SALESPDB_CDB1/onlinelog/o1_mf_2_9gcr5jkh_.log deleted
auxiliary instance file /u01/app/oracle/oradata/tmp/KXNV_PITR_SALESPDB_CDB1/onlinelog/o1_mf_1_9gcr5fxm_.log deleted
auxiliary instance file /u01/app/oracle/oradata/tmp/KXNV_PITR_SALESPDB_CDB1/datafile/o1_mf_salespdb_9gcr55b5_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_sysaux_9gcr1gbd_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_system_9gcr1gbf_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_sysaux_9gcqw98n_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_undotbs1_9gcqw96p_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/tmp/CDB1/datafile/o1_mf_system_9gcqw98r_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/tmp/CDB1/controlfile/o1_mf_9gcqw1mm_.ctl deleted
auxiliary instance file tspitr_kxnv_86390.dmp deleted
Finished recover at 27-JAN-14

RMAN>
   
 

The restore operation leaves the recovered partition in a table named SALES_P2010 by default, leaving the original table and rest of the database as-is.

[oracle@laboms ~]$ sqlplus salesadmin/oracle@SALESPDB

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jan 27 13:15:01 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Mon Jan 27 2014 13:04:07 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> select count(*) from salesadmin.sales_p2010;

  COUNT(*)
----------
       364

SQL> select count(*) from salesadmin.sales partition(p2010);

  COUNT(*)
----------
         0

SQL>