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)