DEV Community 👩‍💻👨‍💻

Project-42
Project-42

Posted on

Recover a Table using RMAN Backup in 12c

One of the great progress Oracle database made with 12C was the posibility of Recover a table using RMAN without the need of any knowledge of Point-In-Time Recovery and how it is really done.

12C really simplifies everything in a really nice way that allows you a quick recovery as long as you have backup of the table in question (and of course you have it... right?)

Lets start one of the scenarios creating a small Tablespace/schema/Table in our 12.1 "Non-CDB" (No container on this case, we will explore options for a PDB later on this guide)

SQL> CREATE TABLESPACE P42;

Tablespace created.

SQL> alter tablespace P42 online;

Tablespace altered.


SQL> ALTER TABLESPACE P42
ADD DATAFILE '+DATA2'
SIZE 1G
AUTOEXTEND ON;  2    3    4

Tablespace altered.



Tablespace 
-------------
P42
UNDOTBS1
SYSTEM
UNDOTBS2
SYSAUX
USERS

6 rows selected.


SQL> CREATE USER P42
IDENTIFIED BY Welcome1
DEFAULT TABLESPACE P42
TEMPORARY TABLESPACE TEMP
QUOTA 200M on P42;

User created.

SQL>

SQL> GRANT CONNECT TO P42;

Grant succeeded.



SQL> grant create session, create procedure,create table  to P42;

Grant succeeded.

SQL>



SQL> connect P42
Enter password:
Connected.
SQL>



SQL> create table TEST
(
ID                 VARCHAR2(4 BYTE)         NOT NULL primary key,
First_Name         VARCHAR2(10 BYTE),
Last_Name          VARCHAR2(10 BYTE),
Start_Date         DATE,
End_Date           DATE,
Salary             Number(8,2),
City               VARCHAR2(10 BYTE),
Description        VARCHAR2(15 BYTE)
)
/  2    3    4    5    6    7    8    9   10   11   12

Table created.


SQL>
BEGIN
  FOR v_LoopCounter IN 1..50 LOOP
    INSERT INTO TEST (id)
      VALUES (v_LoopCounter);
  END LOOP;
END;
/SQL>   2    3    4    5    6    7

PL/SQL procedure successfully completed.

Right, we have our "TEST" table in "P42" schema.

Lets create a Backup

[oracle@rac1-node1 ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Feb 15 18:15:36 2019

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

connected to target database: P42 (DBID=1077208911)

RMAN> backup database plus archivelog;


Starting backup at 15-FEB-19
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=376 instance=P421 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=2 sequence=3 RECID=6 STAMP=999950422
input archived log thread=1 sequence=52 RECID=7 STAMP=999957621
input archived log thread=2 sequence=4 RECID=8 STAMP=999975615

[....]



piece handle=+RECO/P42/BACKUPSET/2019_02_15/ncsnf0_tag20190215t181621_0.1024.1000318619 tag=TAG20190215T181621 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-FEB-19

Starting backup at 15-FEB-19
current log archived
using channel ORA_DISK_1
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=62 RECID=28 STAMP=1000318623
input archived log thread=2 sequence=13 RECID=26 STAMP=1000318555
input archived log thread=2 sequence=14 RECID=27 STAMP=1000318621
channel ORA_DISK_1: starting piece 1 at 15-FEB-19
channel ORA_DISK_1: finished piece 1 at 15-FEB-19
piece handle=+RECO/P42/BACKUPSET/2019_02_15/annnf0_tag20190215t181703_0.933.1000318623 tag=TAG20190215T181703 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-FEB-19

RMAN>

Lets check the time and drop the table

SQL> alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;


SYSDATE
-------------------
15/02/2019 18:18:25

SQL>


SQL> drop table p42.test;

Table P42.TEST dropped.

Now that everything is in place, lets explore the following options:

Table Recovery

Ok, lets recover the table we just dropped. I added some comments in the output to see how RMAN does the table recover.

Is a beautiful and full automatic process

We are doing the recover to a point in time, but you can also restore
to an SCN, or to a log sequence number

## Command Example ##

recover table "P42"."TEST"
until time "to_date('15/02/2019 18:18:00','dd/mm/yyyy hh24:mi:ss')"
AUXILIARY DESTINATION '/u01/oradata/AUX';



## Output ##

# RMAN starts the Restore process creating

RMAN> recover table "P42"."TEST"
until time "to_date('15/02/2019 18:18:00','dd/mm/yyyy hh24:mi:ss')"
AUXILIARY DESTINATION '/u01/oradata/AUX';2> 3>

Starting recover at 15-FEB-19
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 UNDOTBS1
Tablespace UNDOTBS2

Creating automatic instance, with SID='ioes'

initialization parameters used for automatic instance:
db_name=P42
db_unique_name=ioes_pitr_P42
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=2400M
processes=200
db_create_file_dest=/u01/oradata/AUX
log_archive_dest_1='location=/u01/oradata/AUX'
#No auxiliary parameter file used


starting up automatic instance P42

Oracle instance started

Total System Global Area    2516582400 bytes

Fixed Size                     2927528 bytes
Variable Size                570426456 bytes
Database Buffers            1929379840 bytes
Redo Buffers                  13848576 bytes
Automatic instance created


# If we go to the system we can see the AUXILIARY system running:


[oracle@rac1-node1 AUX]$ ps -ef |grep pmon
oracle    3379     1  0 Feb14 ?        00:00:08 asm_pmon_+ASM1
oracle    4034     1  0 Feb14 ?        00:00:08 apx_pmon_+APX1
oracle    4813     1  0 Feb14 ?        00:00:08 mdb_pmon_-MGMTDB
oracle    4990     1  0 Feb14 ?        00:00:13 ora_pmon_P421

oracle   32353     1  0 18:38 ?        00:00:00 ora_pmon_ioes 

oracle   32443 28371  0 18:38 pts/0    00:00:00 grep pmon
[oracle@rac1-node1 AUX]$




# RMAN will now create that AUXILIARY with the minimun Tablespaces to start ("SYSTEM", "UNDOTBS1", "UNDOTBS2", "SYSAUX")

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('15/02/2019 18:18:00','dd/mm/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 15-FEB-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=244 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 +RECO/P42/BACKUPSET/2019_02_15/ncsnf0_tag20190215t181621_0.1024.1000318619
channel ORA_AUX_DISK_1: piece handle=+RECO/P42/BACKUPSET/2019_02_15/ncsnf0_tag20190215t181621_0.1024.1000318619 tag=TAG20190215T181621
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:06
output file name=/u01/oradata/AUX/P42/controlfile/o1_mf_g6g1pyx2_.ctl
Finished restore at 15-FEB-19

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('15/02/2019 18:18:00','dd/mm/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  3 to new;
set newname for clone datafile  4 to new;
set newname for clone datafile  2 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, 3, 4, 2;

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

renamed tempfile 1 to /u01/oradata/AUX/P42/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 15-FEB-19
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/oradata/AUX/P42/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/oradata/AUX/P42/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/oradata/AUX/P42/datafile/o1_mf_undotbs2_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/oradata/AUX/P42/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +RECO/P42/BACKUPSET/2019_02_15/nnndf0_tag20190215t181621_0.890.1000318583
channel ORA_AUX_DISK_1: piece handle=+RECO/P42/BACKUPSET/2019_02_15/nnndf0_tag20190215t181621_0.890.1000318583 tag=TAG20190215T181621
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 15-FEB-19

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=1000319875 file name=/u01/oradata/AUX/P42/datafile/o1_mf_system_g6g1qbv0_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=1000319875 file name=/u01/oradata/AUX/P42/datafile/o1_mf_undotbs1_g6g1qbw7_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=1000319875 file name=/u01/oradata/AUX/P42/datafile/o1_mf_undotbs2_g6g1qbx0_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=1000319875 file name=/u01/oradata/AUX/P42/datafile/o1_mf_sysaux_g6g1qbvx_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('15/02/2019 18:18:00','dd/mm/yyyy hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  4 online";
sql clone "alter database datafile  2 online";
# recover and open database read only
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "UNDOTBS2", "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  3 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  2 online

Starting recover at 15-FEB-19
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 62 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_62.1141.1000318623
archived log for thread 1 with sequence 63 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_63.1400.1000319431
archived log for thread 2 with sequence 14 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_14.844.1000318621
archived log for thread 2 with sequence 15 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_15.929.1000319431
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_62.1141.1000318623 thread=1 sequence=62
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_14.844.1000318621 thread=2 sequence=14
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_15.929.1000319431 thread=2 sequence=15
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_63.1400.1000319431 thread=1 sequence=63
media recovery complete, elapsed time: 00:00:00
Finished recover at 15-FEB-19


# At this point we have the AUXILIARY system Restored and recovered until the point we requested.
# Now, RMAN will restore/recover Datafiles 6,7 (from P42 Tablespace) into AUXILIARY


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/oradata/AUX/P42/controlfile/o1_mf_g6g1pyx2_.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    2516582400 bytes

Fixed Size                     2927528 bytes
Variable Size                587203672 bytes
Database Buffers            1912602624 bytes
Redo Buffers                  13848576 bytes

sql statement: alter system set  control_files =   ''/u01/oradata/AUX/P42/controlfile/o1_mf_g6g1pyx2_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    2516582400 bytes

Fixed Size                     2927528 bytes
Variable Size                587203672 bytes
Database Buffers            1912602624 bytes
Redo Buffers                  13848576 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('15/02/2019 18:18:00','dd/mm/yyyy hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  6 to new;
set newname for datafile  7 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  6, 7;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 15-FEB-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=9 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 00006 to /u01/oradata/AUX/IOES_PITR_P42/datafile/o1_mf_p42_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/oradata/AUX/IOES_PITR_P42/datafile/o1_mf_p42_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +RECO/P42/BACKUPSET/2019_02_15/nnndf0_tag20190215t181621_0.890.1000318583
channel ORA_AUX_DISK_1: piece handle=+RECO/P42/BACKUPSET/2019_02_15/nnndf0_tag20190215t181621_0.890.1000318583 tag=TAG20190215T181621
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 15-FEB-19

datafile 6 switched to datafile copy
input datafile copy RECID=11 STAMP=1000319957 file name=/u01/oradata/AUX/IOES_PITR_P42/datafile/o1_mf_p42_g6g1t5ww_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=12 STAMP=1000319957 file name=/u01/oradata/AUX/IOES_PITR_P42/datafile/o1_mf_p42_g6g1t5v7_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('15/02/2019 18:18:00','dd/mm/yyyy hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile  6 online";
sql clone "alter database datafile  7 online";
# recover and open resetlogs
recover clone database tablespace  "P42", "SYSTEM", "UNDOTBS1", "UNDOTBS2", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  6 online

sql statement: alter database datafile  7 online

Starting recover at 15-FEB-19
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 62 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_62.1141.1000318623
archived log for thread 1 with sequence 63 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_63.1400.1000319431
archived log for thread 2 with sequence 14 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_14.844.1000318621
archived log for thread 2 with sequence 15 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_15.929.1000319431
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_62.1141.1000318623 thread=1 sequence=62
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_14.844.1000318621 thread=2 sequence=14
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_15.929.1000319431 thread=2 sequence=15
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_63.1400.1000319431 thread=1 sequence=63
media recovery complete, elapsed time: 00:00:01
Finished recover at 15-FEB-19




# We have now AUXILIARY with all the needed elements including P42 Tablespace where Table "P42"."TEST" resided
# Now, is time to export the Table from AUXILIARY into a temporal directory


database opened

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

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/oradata/AUX''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/oradata/AUX''

Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_ioes_dmgg":
   EXPDP> Estimate in progress using BLOCKS method...
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Total estimation using BLOCKS method: 64 KB
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
   EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   EXPDP> . . exported "P42"."TEST"                                8.390 KB      50 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_ioes_dmgg" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_ioes_dmgg is:
   EXPDP>   /u01/oradata/AUX/tspitr_ioes_35921.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_ioes_dmgg" successfully completed at Fri Feb 15 18:39:53 2019 elapsed 0 00:00:24
Export completed


# And now, as final step, RMAN will Import the Table into our Database

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_ioes_fFdD" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_ioes_fFdD":
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "P42"."TEST"                                8.390 KB      50 rows
   IMPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
   IMPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   IMPDP> Job "SYS"."TSPITR_IMP_ioes_fFdD" successfully completed at Fri Feb 15 18:40:44 2019 elapsed 0 00:00:37
Import completed


# Since all is completed, RMAN will delete the AUXILIARY:

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/oradata/AUX/P42/datafile/o1_mf_temp_g6g1r876_.tmp deleted
auxiliary instance file /u01/oradata/AUX/IOES_PITR_P42/onlinelog/o1_mf_4_g6g1tryt_.log deleted
auxiliary instance file /u01/oradata/AUX/IOES_PITR_P42/onlinelog/o1_mf_3_g6g1trn0_.log deleted
auxiliary instance file /u01/oradata/AUX/IOES_PITR_P42/onlinelog/o1_mf_2_g6g1tqxs_.log deleted
auxiliary instance file /u01/oradata/AUX/IOES_PITR_P42/onlinelog/o1_mf_1_g6g1tqqc_.log deleted
auxiliary instance file /u01/oradata/AUX/IOES_PITR_P42/datafile/o1_mf_p42_g6g1t5v7_.dbf deleted
auxiliary instance file /u01/oradata/AUX/IOES_PITR_P42/datafile/o1_mf_p42_g6g1t5ww_.dbf deleted
auxiliary instance file /u01/oradata/AUX/P42/datafile/o1_mf_sysaux_g6g1qbvx_.dbf deleted
auxiliary instance file /u01/oradata/AUX/P42/datafile/o1_mf_undotbs2_g6g1qbx0_.dbf deleted
auxiliary instance file /u01/oradata/AUX/P42/datafile/o1_mf_undotbs1_g6g1qbw7_.dbf deleted
auxiliary instance file /u01/oradata/AUX/P42/datafile/o1_mf_system_g6g1qbv0_.dbf deleted
auxiliary instance file /u01/oradata/AUX/P42/controlfile/o1_mf_g6g1pyx2_.ctl deleted
auxiliary instance file tspitr_ioes_35921.dmp deleted
Finished recover at 15-FEB-19

RMAN>


# And is done, we can go back to the DB how the table is there again


SQL> select count(*) from p42.test;

  COUNT(*)
----------
        50

Table Recovery with Remap Option

Lets try now something a bit different. Maybe you didnt drop the table but delete part of it, and you want to conserve the "current" one as well as the previous version.
To do that, we can use REMAP option as below so you will have a new table called "TEST1" as result
Also, instead of using Filesystem as AUXILIARY DESTINATION, lets use +ASM Diskgroup for a faster recovery process

Since we are using +RECO wich is used for our target DB as well,
please notice how the system wont need to restore part of the
Archivelogs since they are still on the Diskgroup

## Command Example ##

recover table "P42"."TEST"
until time "to_date('15/02/2019 18:18:00','dd/mm/yyyy hh24:mi:ss')"
AUXILIARY DESTINATION '+RECO'
remap table "P42"."TEST":"TEST1"

## Output ##

RMAN> recover table "P42"."TEST"
until time "to_date('15/02/2019 18:18:00','dd/mm/yyyy hh24:mi:ss')"
AUXILIARY DESTINATION '+RECO'
remap table "P42"."TEST":"TEST1";2> 3> 4>

Starting recover at 15-FEB-19
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 UNDOTBS1
Tablespace UNDOTBS2

Creating automatic instance, with SID='kadi'

initialization parameters used for automatic instance:
db_name=P42
db_unique_name=kadi_pitr_P42
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=2400M
processes=200
db_create_file_dest=+RECO
log_archive_dest_1='location=+RECO'
#No auxiliary parameter file used


starting up automatic instance P42

Oracle instance started

Total System Global Area    2516582400 bytes

Fixed Size                     2927528 bytes
Variable Size                570426456 bytes
Database Buffers            1929379840 bytes
Redo Buffers                  13848576 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('15/02/2019 18:18:00','dd/mm/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 15-FEB-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=253 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 +RECO/P42/BACKUPSET/2019_02_15/ncsnf0_tag20190215t181621_0.1024.1000318619
channel ORA_AUX_DISK_1: piece handle=+RECO/P42/BACKUPSET/2019_02_15/ncsnf0_tag20190215t181621_0.1024.1000318619 tag=TAG20190215T181621
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output file name=+RECO/P42/CONTROLFILE/current.287.1000321269
Finished restore at 15-FEB-19

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('15/02/2019 18:18:00','dd/mm/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  3 to new;
set newname for clone datafile  4 to new;
set newname for clone datafile  2 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, 3, 4, 2;

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

renamed tempfile 1 to +RECO in control file

Starting restore at 15-FEB-19
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 +RECO
channel ORA_AUX_DISK_1: restoring datafile 00003 to +RECO
channel ORA_AUX_DISK_1: restoring datafile 00004 to +RECO
channel ORA_AUX_DISK_1: restoring datafile 00002 to +RECO
channel ORA_AUX_DISK_1: reading from backup piece +RECO/P42/BACKUPSET/2019_02_15/nnndf0_tag20190215t181621_0.890.1000318583
channel ORA_AUX_DISK_1: piece handle=+RECO/P42/BACKUPSET/2019_02_15/nnndf0_tag20190215t181621_0.890.1000318583 tag=TAG20190215T181621
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 15-FEB-19

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=1000321324 file name=+RECO/P42/DATAFILE/system.344.1000321289
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=1000321324 file name=+RECO/P42/DATAFILE/undotbs1.289.1000321289
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=1000321324 file name=+RECO/P42/DATAFILE/undotbs2.306.1000321289
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=1000321324 file name=+RECO/P42/DATAFILE/sysaux.440.1000321289

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('15/02/2019 18:18:00','dd/mm/yyyy hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  4 online";
sql clone "alter database datafile  2 online";
# recover and open database read only
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "UNDOTBS2", "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  3 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  2 online

Starting recover at 15-FEB-19
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 62 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_62.1141.1000318623
archived log for thread 1 with sequence 63 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_63.1400.1000319431
archived log for thread 2 with sequence 14 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_14.844.1000318621
archived log for thread 2 with sequence 15 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_15.929.1000319431
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_62.1141.1000318623 thread=1 sequence=62
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_14.844.1000318621 thread=2 sequence=14
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_15.929.1000319431 thread=2 sequence=15
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_63.1400.1000319431 thread=1 sequence=63
media recovery complete, elapsed time: 00:00:01
Finished recover at 15-FEB-19

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 =
  ''+RECO/P42/CONTROLFILE/current.287.1000321269'' 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    2516582400 bytes

Fixed Size                     2927528 bytes
Variable Size                587203672 bytes
Database Buffers            1912602624 bytes
Redo Buffers                  13848576 bytes

sql statement: alter system set  control_files =   ''+RECO/P42/CONTROLFILE/current.287.1000321269'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    2516582400 bytes

Fixed Size                     2927528 bytes
Variable Size                587203672 bytes
Database Buffers            1912602624 bytes
Redo Buffers                  13848576 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('15/02/2019 18:18:00','dd/mm/yyyy hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  6 to new;
set newname for datafile  7 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  6, 7;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 15-FEB-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=172 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 00006 to +RECO
channel ORA_AUX_DISK_1: restoring datafile 00007 to +RECO
channel ORA_AUX_DISK_1: reading from backup piece +RECO/P42/BACKUPSET/2019_02_15/nnndf0_tag20190215t181621_0.890.1000318583
channel ORA_AUX_DISK_1: piece handle=+RECO/P42/BACKUPSET/2019_02_15/nnndf0_tag20190215t181621_0.890.1000318583 tag=TAG20190215T181621
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 15-FEB-19

datafile 6 switched to datafile copy
input datafile copy RECID=11 STAMP=1000321415 file name=+RECO/KADI_PITR_P42/DATAFILE/p42.1137.1000321401
datafile 7 switched to datafile copy
input datafile copy RECID=12 STAMP=1000321415 file name=+RECO/KADI_PITR_P42/DATAFILE/p42.1066.1000321401

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('15/02/2019 18:18:00','dd/mm/yyyy hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile  6 online";
sql clone "alter database datafile  7 online";
# recover and open resetlogs
recover clone database tablespace  "P42", "SYSTEM", "UNDOTBS1", "UNDOTBS2", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  6 online

sql statement: alter database datafile  7 online

Starting recover at 15-FEB-19
using channel ORA_AUX_DISK_1

starting media recovery


# The Archivelogs no need to be restored since we are using same Diskgroup:

archived log for thread 1 with sequence 62 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_62.1141.1000318623
archived log for thread 1 with sequence 63 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_63.1400.1000319431
archived log for thread 2 with sequence 14 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_14.844.1000318621
archived log for thread 2 with sequence 15 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_15.929.1000319431
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_62.1141.1000318623 thread=1 sequence=62
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_14.844.1000318621 thread=2 sequence=14
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_15.929.1000319431 thread=2 sequence=15
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_63.1400.1000319431 thread=1 sequence=63
media recovery complete, elapsed time: 00:00:00
Finished recover at 15-FEB-19

database opened

contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
+RECO''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
+RECO''";
}
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''+RECO''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''+RECO''

Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_kadi_ikwz":
   EXPDP> Estimate in progress using BLOCKS method...
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Total estimation using BLOCKS method: 64 KB
   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 "P42"."TEST"                                8.390 KB      50 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_kadi_ikwz" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_kadi_ikwz is:
   EXPDP>   +RECO/tspitr_kadi_53859.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_kadi_ikwz" successfully completed at Fri Feb 15 19:04:24 2019 elapsed 0 00:00:30
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_kadi_tstF" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_kadi_tstF":
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "P42"."TEST1"                               8.390 KB      50 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_kadi_tstF" successfully completed at Fri Feb 15 19:04:54 2019 elapsed 0 00:00:24
Import completed


Removing automatic instance
Automatic instance removed
waiting for ASM instance to release file state object
waiting for ASM instance to release file state object
waiting for ASM instance to release file state object
waiting for ASM instance to release file state object
 auxiliary instance file +RECO/P42/TEMPFILE/temp.1292.1000321329 deleted
auxiliary instance file +RECO/KADI_PITR_P42/ONLINELOG/group_4.766.1000321423 deleted
auxiliary instance file +RECO/KADI_PITR_P42/ONLINELOG/group_3.803.1000321421 deleted
auxiliary instance file +RECO/KADI_PITR_P42/ONLINELOG/group_2.858.1000321421 deleted
auxiliary instance file +RECO/KADI_PITR_P42/ONLINELOG/group_1.832.1000321419 deleted
auxiliary instance file +RECO/KADI_PITR_P42/DATAFILE/p42.1066.1000321401 deleted
auxiliary instance file +RECO/KADI_PITR_P42/DATAFILE/p42.1137.1000321401 deleted
auxiliary instance file +RECO/P42/DATAFILE/sysaux.440.1000321289 deleted
auxiliary instance file +RECO/P42/DATAFILE/undotbs2.306.1000321289 deleted
auxiliary instance file +RECO/P42/DATAFILE/undotbs1.289.1000321289 deleted
auxiliary instance file +RECO/P42/DATAFILE/system.344.1000321289 deleted
auxiliary instance file +RECO/P42/CONTROLFILE/current.287.1000321269 deleted
auxiliary instance file tspitr_kadi_53859.dmp deleted
Finished recover at 15-FEB-19

RMAN>



-- And here it is:


SQL> select count(*) from p42.test1;

  COUNT(*)
----------
        50

Table Recovery with No Import option

For this case, the result will be a Dump file that we can later import into a Database

## Command Example ##


recover table "P42"."TEST"
until time "to_date('15/02/2019 18:18:00','dd/mm/yyyy hh24:mi:ss')"
AUXILIARY DESTINATION '+RECO'
DATAPUMP DESTINATION '/u01/oradata/DUMP'
DUMP FILE 'P42_TEST.dmp'
NOTABLEIMPORT;



## Output ##


RMAN> recover table "P42"."TEST"
until time "to_date('15/02/2019 18:18:00','dd/mm/yyyy hh24:mi:ss')"
AUXILIARY DESTINATION '+RECO'
DATAPUMP DESTINATION '/u01/oradata/DUMP'
DUMP FILE 'P42_TEST.dmp'
NOTABLEIMPORT;2> 3> 4> 5> 6>

Starting recover at 16-FEB-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=370 instance=P421 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
Tablespace UNDOTBS2

Creating automatic instance, with SID='neld'

initialization parameters used for automatic instance:
db_name=P42
db_unique_name=neld_pitr_P42
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=2400M
processes=200
db_create_file_dest=+RECO
log_archive_dest_1='location=+RECO'
#No auxiliary parameter file used


starting up automatic instance P42

Oracle instance started

Total System Global Area    2516582400 bytes

Fixed Size                     2927528 bytes
Variable Size                570426456 bytes
Database Buffers            1929379840 bytes
Redo Buffers                  13848576 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('15/02/2019 18:18:00','dd/mm/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 16-FEB-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=246 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 +RECO/P42/BACKUPSET/2019_02_15/ncsnf0_tag20190215t181621_0.1024.1000318619
channel ORA_AUX_DISK_1: piece handle=+RECO/P42/BACKUPSET/2019_02_15/ncsnf0_tag20190215t181621_0.1024.1000318619 tag=TAG20190215T181621
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output file name=+RECO/P42/CONTROLFILE/current.306.1000365997
Finished restore at 16-FEB-19

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('15/02/2019 18:18:00','dd/mm/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  3 to new;
set newname for clone datafile  4 to new;
set newname for clone datafile  2 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, 3, 4, 2;

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

renamed tempfile 1 to +RECO in control file

Starting restore at 16-FEB-19
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 +RECO
channel ORA_AUX_DISK_1: restoring datafile 00003 to +RECO
channel ORA_AUX_DISK_1: restoring datafile 00004 to +RECO
channel ORA_AUX_DISK_1: restoring datafile 00002 to +RECO
channel ORA_AUX_DISK_1: reading from backup piece +RECO/P42/BACKUPSET/2019_02_15/nnndf0_tag20190215t181621_0.890.1000318583
channel ORA_AUX_DISK_1: piece handle=+RECO/P42/BACKUPSET/2019_02_15/nnndf0_tag20190215t181621_0.890.1000318583 tag=TAG20190215T181621
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 16-FEB-19

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=1000366044 file name=+RECO/P42/DATAFILE/system.1066.1000366009
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=1000366044 file name=+RECO/P42/DATAFILE/undotbs1.858.1000366011
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=1000366045 file name=+RECO/P42/DATAFILE/undotbs2.803.1000366011
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=1000366045 file name=+RECO/P42/DATAFILE/sysaux.832.1000366011

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('15/02/2019 18:18:00','dd/mm/yyyy hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  4 online";
sql clone "alter database datafile  2 online";
# recover and open database read only
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "UNDOTBS2", "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  3 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  2 online

Starting recover at 16-FEB-19
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 62 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_62.1141.1000318623
archived log for thread 1 with sequence 63 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_63.1400.1000319431
archived log for thread 2 with sequence 14 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_14.844.1000318621
archived log for thread 2 with sequence 15 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_15.929.1000319431
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_62.1141.1000318623 thread=1 sequence=62
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_14.844.1000318621 thread=2 sequence=14
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_15.929.1000319431 thread=2 sequence=15
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_63.1400.1000319431 thread=1 sequence=63
media recovery complete, elapsed time: 00:00:01
Finished recover at 16-FEB-19

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 =
  ''+RECO/P42/CONTROLFILE/current.306.1000365997'' 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    2516582400 bytes

Fixed Size                     2927528 bytes
Variable Size                587203672 bytes
Database Buffers            1912602624 bytes
Redo Buffers                  13848576 bytes

sql statement: alter system set  control_files =   ''+RECO/P42/CONTROLFILE/current.306.1000365997'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    2516582400 bytes

Fixed Size                     2927528 bytes
Variable Size                587203672 bytes
Database Buffers            1912602624 bytes
Redo Buffers                  13848576 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('15/02/2019 18:18:00','dd/mm/yyyy hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  6 to new;
set newname for datafile  7 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  6, 7;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 16-FEB-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=91 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 00006 to +RECO
channel ORA_AUX_DISK_1: restoring datafile 00007 to +RECO
channel ORA_AUX_DISK_1: reading from backup piece +RECO/P42/BACKUPSET/2019_02_15/nnndf0_tag20190215t181621_0.890.1000318583
channel ORA_AUX_DISK_1: piece handle=+RECO/P42/BACKUPSET/2019_02_15/nnndf0_tag20190215t181621_0.890.1000318583 tag=TAG20190215T181621
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 16-FEB-19

datafile 6 switched to datafile copy
input datafile copy RECID=11 STAMP=1000366722 file name=+RECO/NELD_PITR_P42/DATAFILE/p42.879.1000366697
datafile 7 switched to datafile copy
input datafile copy RECID=12 STAMP=1000366722 file name=+RECO/NELD_PITR_P42/DATAFILE/p42.1292.1000366697

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('15/02/2019 18:18:00','dd/mm/yyyy hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile  6 online";
sql clone "alter database datafile  7 online";
# recover and open resetlogs
recover clone database tablespace  "P42", "SYSTEM", "UNDOTBS1", "UNDOTBS2", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  6 online

sql statement: alter database datafile  7 online

Starting recover at 16-FEB-19
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 62 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_62.1141.1000318623
archived log for thread 1 with sequence 63 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_63.1400.1000319431
archived log for thread 2 with sequence 14 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_14.844.1000318621
archived log for thread 2 with sequence 15 is already on disk as file +RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_15.929.1000319431
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_62.1141.1000318623 thread=1 sequence=62
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_14.844.1000318621 thread=2 sequence=14
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_2_seq_15.929.1000319431 thread=2 sequence=15
archived log file name=+RECO/P42/ARCHIVELOG/2019_02_15/thread_1_seq_63.1400.1000319431 thread=1 sequence=63
media recovery complete, elapsed time: 00:00:01
Finished recover at 16-FEB-19

database opened

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

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/oradata/DUMP''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/oradata/DUMP''

Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_neld_bomE":
   EXPDP> Estimate in progress using BLOCKS method...
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Total estimation using BLOCKS method: 64 KB
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
   EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   EXPDP> . . exported "P42"."TEST"                                8.390 KB      50 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_neld_bomE" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_neld_bomE is:
   EXPDP>   /u01/oradata/DUMP/P42_TEST.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_neld_bomE" successfully completed at Sat Feb 16 07:39:56 2019 elapsed 0 00:00:38
Export completed

Not performing table import after point-in-time recovery

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
waiting for ASM instance to release file state object
waiting for ASM instance to release file state object
waiting for ASM instance to release file state object
waiting for ASM instance to release file state object
waiting for ASM instance to release file state object
auxiliary instance file +RECO/P42/TEMPFILE/temp.766.1000366051 deleted
auxiliary instance file +RECO/NELD_PITR_P42/ONLINELOG/group_4.1094.1000366731 deleted
auxiliary instance file +RECO/NELD_PITR_P42/ONLINELOG/group_3.919.1000366729 deleted
auxiliary instance file +RECO/NELD_PITR_P42/ONLINELOG/group_2.971.1000366729 deleted
auxiliary instance file +RECO/NELD_PITR_P42/ONLINELOG/group_1.852.1000366727 deleted
auxiliary instance file +RECO/NELD_PITR_P42/DATAFILE/p42.1292.1000366697 deleted
auxiliary instance file +RECO/NELD_PITR_P42/DATAFILE/p42.879.1000366697 deleted
auxiliary instance file +RECO/P42/DATAFILE/sysaux.832.1000366011 deleted
auxiliary instance file +RECO/P42/DATAFILE/undotbs2.803.1000366011 deleted
auxiliary instance file +RECO/P42/DATAFILE/undotbs1.858.1000366011 deleted
auxiliary instance file +RECO/P42/DATAFILE/system.1066.1000366009 deleted
auxiliary instance file +RECO/P42/CONTROLFILE/current.306.1000365997 deleted
Finished recover at 16-FEB-19

RMAN>





# Here it is:


[oracle@rac1-node1 DUMP]$ ls -lrth
total 180K
-rw-r----- 1 oracle oinstall 176K Feb 16 07:39 P42_TEST.dmp
[oracle@rac1-node1 DUMP]$

Table Recovery from PDB

The same we are recovering a Table from a non container Database, we can recover a table from a PDB.

Lets create the table, do a Backup and drop it as we did on previous cases, but this time inside PDB1 of our 12.2 Database called "db122"

[oracle@rac1-node1 ~]$ srvctl start database -d db122
[oracle@rac1-node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Feb 16 11:40:28 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

sys@db1221>SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

NAME                   CON_ID   DBID    CON_UID GUID
------------------------------ ---------- ---------- ---------- --------------------------------
CDB$ROOT                1  917830880          1 4700A987085A3DFAE05387E5E50A8C7B
PDB$SEED                2 3465701856 3465701856 73B1B2FDD77568EFE055000000000001
PDB1                    3  101088986  101088986 73B1D299401C7A66E055000000000001

sys@db1221>ALTER SESSION SET CONTAINER = pdb1;


Session altered.

sys@db1221>sys@db1221>CREATE TABLESPACE P42;

Tablespace created.

sys@db1221>alter tablespace P42 online;

Tablespace altered.

sys@db1221>CREATE USER P42
IDENTIFIED BY Welcome1
DEFAULT TABLESPACE P42
TEMPORARY TABLESPACE TEMP
QUOTA 200M on P42;  2    3    4    5

User created.

sys@db1221>GRANT CONNECT TO P42;

Grant succeeded.

sys@db1221>grant create session, create procedure,create table  to P42;

Grant succeeded.
undefined



sys@db1221>conn P42/Welcome1@//localhost:1521/pdb1.raclab.local;
Connected.
p42@//localhost:1521/pdb1.raclab.local>



p42@//localhost:1521/pdb1.raclab.local>create table TEST
(
ID                 VARCHAR2(4 BYTE)         NOT NULL primary key,
First_Name         VARCHAR2(10 BYTE),
Last_Name          VARCHAR2(10 BYTE),
Start_Date         DATE,
End_Date           DATE,
Salary             Number(8,2),
City               VARCHAR2(10 BYTE),
Description        VARCHAR2(15 BYTE)
)
/
  2    3    4    5    6    7    8    9   10   11   12

Table created.

p42@//localhost:1521/pdb1.raclab.local>p42@//localhost:1521/pdb1.raclab.local>
p42@//localhost:1521/pdb1.raclab.local>BEGIN
  FOR v_LoopCounter IN 1..50 LOOP
    INSERT INTO TEST (id)
      VALUES (v_LoopCounter);
  END LOOP;
END;
/   2    3    4    5    6    7

PL/SQL procedure successfully completed.

p42@//localhost:1521/pdb1.raclab.local>commit ;

Commit complete.


p42@//localhost:1521/pdb1.raclab.local>select count(*) from p42.test;

  COUNT(*)
----------
    50

p42@//localhost:1521/pdb1.raclab.local>








RMAN> backup database plus archivelog;


Starting backup at 16-FEB-19
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 instance=db1221 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=2 sequence=626 RECID=2057 STAMP=999927313
input archived log thread=1 sequence=620 RECID=2066 STAMP=999946487
input archived log thread=2 sequence=627 RECID=2061 STAMP=999929124
input archived log thread=2 sequence=628 RECID=2065 STAMP=999946486

[......]

channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=+DATA_DB/DB122/73B1D299401C7A66E055000000000001/DATAFILE/sysaux.278.984472971
input datafile file number=00010 name=+DATA_DB/DB122/73B1D299401C7A66E055000000000001/DATAFILE/system.279.984472971
input datafile file number=00012 name=+DATA_DB/DB122/73B1D299401C7A66E055000000000001/DATAFILE/undotbs1.286.984472971
input datafile file number=00013 name=+DATA_DB/DB122/73B1D299401C7A66E055000000000001/DATAFILE/undo_2.299.984473013
input datafile file number=00016 name=+DATA_DB/DB122/73B1D299401C7A66E055000000000001/DATAFILE/p42.319.1000381369
input datafile file number=00014 name=+DATA_DB/DB122/73B1D299401C7A66E055000000000001/DATAFILE/users.300.984473031
channel ORA_DISK_1: starting piece 1 at 16-FEB-19
channel ORA_DISK_1: finished piece 1 at 16-FEB-19
piece handle=+RECO/DB122/73B1D299401C7A66E055000000000001/BACKUPSET/2019_02_16/nnndf0_tag20190216t121103_0.984.1000383151 tag=TAG20190216T121103 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

[......]


Finished backup at 16-FEB-19

Starting Control File and SPFILE Autobackup at 16-FEB-19
piece handle=+RECO/DB122/AUTOBACKUP/2019_02_16/s_1000383193.1025.1000383195 comment=NONE
Finished Control File and SPFILE Autobackup at 16-FEB-19

RMAN>












p42@//localhost:1521/pdb1.raclab.local>alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';

Session altered.

p42@//localhost:1521/pdb1.raclab.local>select sysdate from dual;

SYSDATE
-------------------
16/02/2019 12:14:22

p42@//localhost:1521/pdb1.raclab.local>




p42@//localhost:1521/pdb1.raclab.local>drop table test;

Table dropped.

p42@//localhost:1521/pdb1.raclab.local>

Now, lets recover that table

## Command Example ##


recover table "P42"."TEST" OF PLUGGABLE DATABASE pdb1
until time "to_date('16/02/2019 12:14:00','dd/mm/yyyy hh24:mi:ss')"
AUXILIARY DESTINATION '+RECO'



## Output ##

RMAN> recover table "P42"."TEST" OF PLUGGABLE DATABASE pdb1
until time "to_date('16/02/2019 12:14:00','dd/mm/yyyy hh24:mi:ss')"
AUXILIARY DESTINATION '+RECO'2> 3> ;

Starting recover at 17-FEB-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=408 instance=db1221 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 PDB1:SYSTEM
Tablespace UNDOTBS1
Tablespace PDB1:UNDOTBS1
Tablespace UNDOTBS2
Tablespace PDB1:UNDO_2

Creating automatic instance, with SID='sdmE'

initialization parameters used for automatic instance:
db_name=DB122
db_unique_name=sdmE_pitr_pdb1_DB122
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
db_domain=raclab.local
sga_target=2400M
processes=200
db_create_file_dest=+RECO
log_archive_dest_1='location=+RECO'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used


starting up automatic instance DB122

Oracle instance started

Total System Global Area    2516582400 bytes

Fixed Size                     8795904 bytes
Variable Size                570427648 bytes
Database Buffers            1929379840 bytes
Redo Buffers                   7979008 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('16/02/2019 12:14:00','dd/mm/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 17-FEB-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=96 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 +RECO/DB122/AUTOBACKUP/2019_02_16/s_1000383193.1025.1000383195
channel ORA_AUX_DISK_1: piece handle=+RECO/DB122/AUTOBACKUP/2019_02_16/s_1000383193.1025.1000383195 tag=TAG20190216T121313
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:06
output file name=+RECO/DB122/CONTROLFILE/current.323.1000452525
Finished restore at 17-FEB-19

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('16/02/2019 12:14:00','dd/mm/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  10 to new;
set newname for clone datafile  4 to new;
set newname for clone datafile  12 to new;
set newname for clone datafile  9 to new;
set newname for clone datafile  13 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  11 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, 10, 4, 12, 9, 13, 3, 11;

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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to +RECO in control file
renamed tempfile 3 to +RECO in control file

Starting restore at 17-FEB-19
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 +RECO
channel ORA_AUX_DISK_1: restoring datafile 00004 to +RECO
channel ORA_AUX_DISK_1: restoring datafile 00009 to +RECO
channel ORA_AUX_DISK_1: restoring datafile 00003 to +RECO
channel ORA_AUX_DISK_1: reading from backup piece +RECO/DB122/BACKUPSET/2019_02_16/nnndf0_tag20190216t121103_0.981.1000383065
channel ORA_AUX_DISK_1: piece handle=+RECO/DB122/BACKUPSET/2019_02_16/nnndf0_tag20190216t121103_0.981.1000383065 tag=TAG20190216T121103
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 00010 to +RECO
channel ORA_AUX_DISK_1: restoring datafile 00012 to +RECO
channel ORA_AUX_DISK_1: restoring datafile 00013 to +RECO
channel ORA_AUX_DISK_1: restoring datafile 00011 to +RECO
channel ORA_AUX_DISK_1: reading from backup piece +RECO/DB122/73B1D299401C7A66E055000000000001/BACKUPSET/2019_02_16/nnndf0_tag20190216t121103_0.984.1000383151
channel ORA_AUX_DISK_1: piece handle=+RECO/DB122/73B1D299401C7A66E055000000000001/BACKUPSET/2019_02_16/nnndf0_tag20190216t121103_0.984.1000383151 tag=TAG20190216T121103
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 17-FEB-19

datafile 1 switched to datafile copy
input datafile copy RECID=12 STAMP=1000452739 file name=+RECO/DB122/DATAFILE/system.314.1000452539
datafile 10 switched to datafile copy
input datafile copy RECID=13 STAMP=1000452739 file name=+RECO/DB122/73B1D299401C7A66E055000000000001/DATAFILE/system.1395.1000452707
datafile 4 switched to datafile copy
input datafile copy RECID=14 STAMP=1000452739 file name=+RECO/DB122/DATAFILE/undotbs1.311.1000452541
datafile 12 switched to datafile copy
input datafile copy RECID=15 STAMP=1000452740 file name=+RECO/DB122/73B1D299401C7A66E055000000000001/DATAFILE/undotbs1.1399.1000452709
datafile 9 switched to datafile copy
input datafile copy RECID=16 STAMP=1000452740 file name=+RECO/DB122/DATAFILE/undotbs2.315.1000452539
datafile 13 switched to datafile copy
input datafile copy RECID=17 STAMP=1000452740 file name=+RECO/DB122/73B1D299401C7A66E055000000000001/DATAFILE/undo_2.1184.1000452709
datafile 3 switched to datafile copy
input datafile copy RECID=18 STAMP=1000452740 file name=+RECO/DB122/DATAFILE/sysaux.319.1000452539
datafile 11 switched to datafile copy
input datafile copy RECID=19 STAMP=1000452740 file name=+RECO/DB122/73B1D299401C7A66E055000000000001/DATAFILE/sysaux.280.1000452707

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('16/02/2019 12:14:00','dd/mm/yyyy hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone 'PDB1' "alter database datafile
 10 online";
sql clone "alter database datafile  4 online";
sql clone 'PDB1' "alter database datafile
 12 online";
sql clone "alter database datafile  9 online";
sql clone 'PDB1' "alter database datafile
 13 online";
sql clone "alter database datafile  3 online";
sql clone 'PDB1' "alter database datafile
 11 online";
# recover and open database read only
recover clone database tablespace  "SYSTEM", "PDB1":"SYSTEM", "UNDOTBS1", "PDB1":"UNDOTBS1", "UNDOTBS2", "PDB1":"UNDO_2", "SYSAUX", "PDB1":"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  10 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  12 online

sql statement: alter database datafile  9 online

sql statement: alter database datafile  13 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  11 online

Starting recover at 17-FEB-19
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 632 is already on disk as file +RECO/DB122/ARCHIVELOG/2019_02_16/thread_1_seq_632.1138.1000383191
archived log for thread 1 with sequence 633 is already on disk as file +RECO/DB122/ARCHIVELOG/2019_02_16/thread_1_seq_633.818.1000385767
archived log for thread 2 with sequence 640 is already on disk as file +RECO/DB122/ARCHIVELOG/2019_02_16/thread_2_seq_640.1031.1000383191
archived log for thread 2 with sequence 641 is already on disk as file +RECO/DB122/ARCHIVELOG/2019_02_16/thread_2_seq_641.782.1000389353
archived log file name=+RECO/DB122/ARCHIVELOG/2019_02_16/thread_1_seq_632.1138.1000383191 thread=1 sequence=632
archived log file name=+RECO/DB122/ARCHIVELOG/2019_02_16/thread_2_seq_640.1031.1000383191 thread=2 sequence=640
archived log file name=+RECO/DB122/ARCHIVELOG/2019_02_16/thread_1_seq_633.818.1000385767 thread=1 sequence=633
archived log file name=+RECO/DB122/ARCHIVELOG/2019_02_16/thread_2_seq_641.782.1000389353 thread=2 sequence=641
media recovery complete, elapsed time: 00:00:01
Finished recover at 17-FEB-19

sql statement: alter database open read only

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

sql statement: alter pluggable database  PDB1 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 =
  ''+RECO/DB122/CONTROLFILE/current.323.1000452525'' 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    2516582400 bytes

Fixed Size                     8795904 bytes
Variable Size                570427648 bytes
Database Buffers            1929379840 bytes
Redo Buffers                   7979008 bytes

sql statement: alter system set  control_files =   ''+RECO/DB122/CONTROLFILE/current.323.1000452525'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    2516582400 bytes

Fixed Size                     8795904 bytes
Variable Size                570427648 bytes
Database Buffers            1929379840 bytes
Redo Buffers                   7979008 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('16/02/2019 12:14:00','dd/mm/yyyy hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  16 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  16;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 17-FEB-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=256 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 00016 to +RECO
channel ORA_AUX_DISK_1: reading from backup piece +RECO/DB122/73B1D299401C7A66E055000000000001/BACKUPSET/2019_02_16/nnndf0_tag20190216t121103_0.984.1000383151
channel ORA_AUX_DISK_1: piece handle=+RECO/DB122/73B1D299401C7A66E055000000000001/BACKUPSET/2019_02_16/nnndf0_tag20190216t121103_0.984.1000383151 tag=TAG20190216T121103
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 17-FEB-19

datafile 16 switched to datafile copy
input datafile copy RECID=21 STAMP=1000452862 file name=+RECO/SDME_PITR_PDB1_DB122/73B1D299401C7A66E055000000000001/DATAFILE/p42.1351.1000452855

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('16/02/2019 12:14:00','dd/mm/yyyy hh24:mi:ss')";
# online the datafiles restored or switched
sql clone 'PDB1' "alter database datafile
 16 online";
# recover and open resetlogs
recover clone database tablespace  "PDB1":"P42", "SYSTEM", "PDB1":"SYSTEM", "UNDOTBS1", "PDB1":"UNDOTBS1", "UNDOTBS2", "PDB1":"UNDO_2", "SYSAUX", "PDB1":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  16 online

Starting recover at 17-FEB-19
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 632 is already on disk as file +RECO/DB122/ARCHIVELOG/2019_02_16/thread_1_seq_632.1138.1000383191
archived log for thread 1 with sequence 633 is already on disk as file +RECO/DB122/ARCHIVELOG/2019_02_16/thread_1_seq_633.818.1000385767
archived log for thread 2 with sequence 640 is already on disk as file +RECO/DB122/ARCHIVELOG/2019_02_16/thread_2_seq_640.1031.1000383191
archived log for thread 2 with sequence 641 is already on disk as file +RECO/DB122/ARCHIVELOG/2019_02_16/thread_2_seq_641.782.1000389353
archived log file name=+RECO/DB122/ARCHIVELOG/2019_02_16/thread_1_seq_632.1138.1000383191 thread=1 sequence=632
archived log file name=+RECO/DB122/ARCHIVELOG/2019_02_16/thread_2_seq_640.1031.1000383191 thread=2 sequence=640
archived log file name=+RECO/DB122/ARCHIVELOG/2019_02_16/thread_1_seq_633.818.1000385767 thread=1 sequence=633
archived log file name=+RECO/DB122/ARCHIVELOG/2019_02_16/thread_2_seq_641.782.1000389353 thread=2 sequence=641
media recovery complete, elapsed time: 00:00:02
Finished recover at 17-FEB-19

database opened

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

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 ''
+RECO''";
# create directory for datapump export
sql clone 'PDB1' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
+RECO''";
}
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''+RECO''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''+RECO''

Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_sdmE_FrnA":
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
   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> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
   EXPDP> . . exported "P42"."TEST"                                8.382 KB      50 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_sdmE_FrnA" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_sdmE_FrnA is:
   EXPDP>   +RECO/tspitr_sdme_96597.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_sdmE_FrnA" successfully completed at Sun Feb 17 07:37:00 2019 elapsed 0 00:01:13
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_sdmE_DgFf" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_sdmE_DgFf":
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "P42"."TEST"                                8.382 KB      50 rows
   IMPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
   IMPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   IMPDP> Job "SYS"."TSPITR_IMP_sdmE_DgFf" successfully completed at Sun Feb 17 07:40:39 2019 elapsed 0 00:02:05
Import completed


Removing automatic instance
Automatic instance removed
waiting for ASM instance to release file state object
auxiliary instance file +RECO/DB122/73B1D299401C7A66E055000000000001/TEMPFILE/temp.1350.1000452765 deleted
auxiliary instance file +RECO/DB122/TEMPFILE/temp.1359.1000452757 deleted
auxiliary instance file +RECO/SDME_PITR_PDB1_DB122/ONLINELOG/group_4.382.1000452871 deleted
auxiliary instance file +RECO/SDME_PITR_PDB1_DB122/ONLINELOG/group_3.381.1000452871 deleted
auxiliary instance file +RECO/SDME_PITR_PDB1_DB122/ONLINELOG/group_2.507.1000452871 deleted
auxiliary instance file +RECO/SDME_PITR_PDB1_DB122/ONLINELOG/group_1.1085.1000452871 deleted
auxiliary instance file +RECO/SDME_PITR_PDB1_DB122/73B1D299401C7A66E055000000000001/DATAFILE/p42.1351.1000452855 deleted
auxiliary instance file +RECO/DB122/73B1D299401C7A66E055000000000001/DATAFILE/sysaux.280.1000452707 deleted
auxiliary instance file +RECO/DB122/DATAFILE/sysaux.319.1000452539 deleted
auxiliary instance file +RECO/DB122/73B1D299401C7A66E055000000000001/DATAFILE/undo_2.1184.1000452709 deleted
auxiliary instance file +RECO/DB122/DATAFILE/undotbs2.315.1000452539 deleted
auxiliary instance file +RECO/DB122/73B1D299401C7A66E055000000000001/DATAFILE/undotbs1.1399.1000452709 deleted
auxiliary instance file +RECO/DB122/DATAFILE/undotbs1.311.1000452541 deleted
auxiliary instance file +RECO/DB122/73B1D299401C7A66E055000000000001/DATAFILE/system.1395.1000452707 deleted
auxiliary instance file +RECO/DB122/DATAFILE/system.314.1000452539 deleted
auxiliary instance file +RECO/DB122/CONTROLFILE/current.323.1000452525 deleted
auxiliary instance file tspitr_sdmE_96597.dmp deleted
Finished recover at 17-FEB-19

RMAN>



# Here it is:

p42@//localhost:1521/pdb1.raclab.local>select count(*) from p42.test;

  COUNT(*)
----------
        50

p42@//localhost:1521/pdb1.raclab.local>

Ok, I think is enough for today.

Is a really good and fast way to do Table recovery.
You can of course explore more options and do Table partitions recovery or do Tablespace remap as well as Schema name remap (Schema name remap is only an option starting with 12.2)
As always, go to the usual suspects to check on more options:

https://oracle-base.com/articles/12c/rman-table-point-in-time-recovery-12cr1

I will try couple of more recovery options (back to 11g and something else in 12.2/18c) and probably come back to this one.

One of the downsides is the fact that you need the Target Database running during the process, and as far as I tried, you cant do this process pointing to AUXILIARY DESTINATION in a different system/cluster.

Something I couldn't work out and bothers me, is the fact that I couldn't use a directory when using Diskgroup, so the AUXILIARY DESTINATION would be '+RECO/AUX' instead of just '+RECO'.

I was getting an error with db_create_file_dest parameter.
Maybe there is a way to specify db_create_file_dest as well in the script but didn't manage to find it this time

RMAN>  recover table "P42"."TEST"
until time "to_date('15/02/2019 18:18:00','dd/mm/yyyy hh24:mi:ss')"
AUXILIARY DESTINATION '+RECO/AUX'
DATAPUMP DESTINATION '/u01/oradata/DUMP'
DUMP FILE 'P42_TEST.dmp'
NOTABLEIMPORT;2> 3> 4> 5> 6>

Starting recover at 16-FEB-19
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 UNDOTBS1
Tablespace UNDOTBS2

Creating automatic instance, with SID='txbh'

initialization parameters used for automatic instance:
db_name=P42
db_unique_name=txbh_pitr_P42
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=2400M
processes=200
db_create_file_dest=+RECO/AUX
log_archive_dest_1='location=+RECO/AUX'
#No auxiliary parameter file used


starting up automatic instance P42

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/16/2019 07:54:15
RMAN-04014: startup failed: ORA-01261: Parameter db_create_file_dest destination string cannot be translated

RMAN>

Top comments (0)

🌚 Life is too short to browse without dark mode