Beside the pre-req mentioned in the Oracle documenttion there are few other things to look out for. By default the auxiliary instance (called auotmatic instance in the rman output) set the sga_target size to the same value as the target database.
Creating automatic instance, with SID='Cviy' initialization parameters used for automatic instance: db_name=CDBRHEL8 db_unique_name=Cviy_pitr_pdbrhel8_CDBRHEL8 compatible=19.0.0 db_block_size=8192 db_files=200 diagnostic_dest=/opt/company/app/oracle _system_trig_enabled=FALSE sga_target=4096M processes=200 db_create_file_dest=/opt/backup/recover/oracle log_archive_dest_1='location=/opt/backup/recover/oracle' enable_pluggable_database=true _clone_one_pdb_recovery=true wallet_root=/opt/company/app/oracle/wallet #No auxiliary parameter file usedThis could be problem if SGA is set high and there isn't enough free memory in the instance to accommodate two such SGAs. This could result in instance/server hang. Solution is to use an auxiliary instance pfile and specify that in the rman command. Below aux.ora is created with a smaller sga_target size.
cat aux.ora SGA_TARGET=2048MThe auxiliary instance parameter file is specified in the rman command.
RUN
{
SET AUXILIARY INSTANCE PARAMETER FILE TO '/home/oracle/aux.ora';
RECOVER TABLE ...
}RMAN output will show that auxiliary instnace is using this parameter file.Creating automatic instance, with SID='Czto' using contents of file /home/oracle/aux.ora initialization parameters used for automatic instance: db_name=CDBRHEL8 db_unique_name=Czto_pitr_pdbrhel8_CDBRHEL8 compatible=19.0.0 db_block_size=8192 db_files=200 diagnostic_dest=/opt/company/app/oracle _system_trig_enabled=FALSE sga_target=4096M processes=200 db_create_file_dest=/opt/backup/recover/oracle log_archive_dest_1='location=/opt/backup/recover/oracle' enable_pluggable_database=true _clone_one_pdb_recovery=true wallet_root=/opt/company/app/oracle/wallet ifile=/home/oracle/aux.oraThe RMAN output still shows the default for the sga_target value which is same as target database. But on the alert log for auxiliary instance it is shown that value for sga_target is the one specified in the aux.ora.
Using parameter settings in client-side pfile /tmp/ora_tfilJyuc09 on machine ip-172-31-2-172.eu-west-1.compute.internal System parameters with non-default values: processes = 200 sga_target = 2G db_block_size = 8192 compatible = "19.0.0" log_archive_dest_1 = "location=/opt/backup/recover/oracle" db_files = 200 db_create_file_dest = "/opt/backup/recover/oracle" _clone_one_pdb_recovery = TRUE wallet_root = "/opt/company/app/oracle/wallet" _system_trig_enabled = FALSE db_name = "CDBRHEL8" db_unique_name = "Czto_pitr_pdbrhel8_CDBRHEL8" ifile = "/home/oracle/aux.ora" diagnostic_dest = "/opt/company/app/oracle" enable_pluggable_database= TRUEIf the target database uses TDE then those parameters are also included in the auxiliary instance when the recovery command is run.
Creating automatic instance, with SID='rzCd' using contents of file /home/oracle/aux.ora initialization parameters used for automatic instance: db_name=CDBRHEL8 db_unique_name=rzCd_pitr_pdbrhel8_CDBRHEL8 compatible=19.0.0 db_block_size=8192 db_files=200 diagnostic_dest=/opt/company/app/oracle _system_trig_enabled=FALSE sga_target=4096M processes=200 db_create_file_dest=/opt/backup/recover/oracle log_archive_dest_1='location=/opt/backup/recover/oracle' enable_pluggable_database=true _clone_one_pdb_recovery=true wallet_root=/opt/company/app/oracle/wallet tde_configuration='KEYSTORE_CONFIGURATION=FILE' ifile=/home/oracle/aux.oraLastly, if the table is recovered into a different schema then that schema must have existed in the time being recovered to. For example if table is being recovered to 2022-02-21 15:10:50 and schema ASANGA was created after that (say at 2022-02-21 16:00) then recovery with remap table will fail as below
RECOVER TABLE cnx.mytablerestore OF PLUGGABLE DATABASE pdbrhel8
UNTIL TIME "to_date('2022-02-21 15:10:50','YYYY-MM-DD HH24:MI:SS')"
AUXILIARY DESTINATION '/opt/backup/recover/oracle'
REMAP TABLE cnx.mytablerestore:asanga.mytablerestore2;
...
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/21/2022 16:52:40
RMAN-05063: Cannot recover specified tables
RMAN-05144: schema ASANGA does not existThis behaviour seems to contradict Oracle documentation which says "The new schema must exist in the target database before you perform the recovery." It was confirmed by MOS SR ticket that user must exist before the point in time being recovered to. Below examples show various scenarios of PITR of tables. The recovery scenario was created by deleting some rows from a table. Recovered table will show all the rows before the delete.
SQL> begin 2 for i in 1 .. 100000 3 loop 4 insert into mytablerestore values(i*100, 'abc '||i,i); 5 end loop; 6 end; 7 / commit; SQL> ! date Mon Feb 21 15:10:56 UTC 2022 SQL> delete from mytablerestore where mod(c,2)=0; 50000 rows deleted. SQL> commit; Commit complete.Common to all examples is the AUXILIARY DESTINATION which is the location where the auxiliary instance will be created.
PITR of table into a dump file
To recover the file into a dump file which could be later imported using impdp use NOTABLEIMPORT clause. The dump file will be created in the directory specified by DATAPUMP DESTINATION and will have the name specified by DUMP FILE. The recovery command will be like below.
RECOVER TABLE cnx.mytablerestore OF PLUGGABLE DATABASE pdbrhel8
UNTIL TIME "to_date('2022-02-21 15:10:50','YYYY-MM-DD HH24:MI:SS')"
AUXILIARY DESTINATION '/opt/backup/recover/oracle'
DATAPUMP DESTINATION '/opt/backup/recover/dumpfile'
DUMP FILE 'mypitr.dmp'
NOTABLEIMPORT;The full output is RMAN> RECOVER TABLE cnx.mytablerestore OF PLUGGABLE DATABASE pdbrhel8
UNTIL TIME "to_date('2022-02-21 15:10:50','YYYY-MM-DD HH24:MI:SS')"
AUXILIARY DESTINATION '/opt/backup/recover/oracle'
2> DATAPUMP DESTINATION '/opt/backup/recover/dumpfile'
DUMP FILE 'mypitr.dmp'
3> 4> 5> 6> NOTABLEIMPORT;
Starting recover at 2022-02-21 16:02:34
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=141 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=264 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=378 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 PDB$SEED:SYSTEM
Tablespace PDBRHEL8:SYSTEM
Tablespace UNDOTBS1
Tablespace PDB$SEED:UNDOTBS1
Tablespace PDBRHEL8:UNDOTBS1
Creating automatic instance, with SID='Cviy'
initialization parameters used for automatic instance:
db_name=CDBRHEL8
db_unique_name=Cviy_pitr_pdbrhel8_CDBRHEL8
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/opt/company/app/oracle
_system_trig_enabled=FALSE
sga_target=4096M
processes=200
db_create_file_dest=/opt/backup/recover/oracle
log_archive_dest_1='location=/opt/backup/recover/oracle'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
wallet_root=/opt/company/app/oracle/wallet
#No auxiliary parameter file used
starting up automatic instance CDBRHEL8
Oracle instance started
Total System Global Area 4294963752 bytes
Fixed Size 9142824 bytes
Variable Size 805306368 bytes
Database Buffers 3472883712 bytes
Redo Buffers 7630848 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-21 15:10:50','YYYY-MM-DD 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 2022-02-21 16:02:47
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=91 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=172 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=2 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=11 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 /opt/backup/2022-02-21/arc_c-582064218-20220221-01.ctl
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-21/arc_c-582064218-20220221-01.ctl tag=TAG20220221T150207
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/backup/recover/oracle/CDBRHEL8/controlfile/o1_mf_k17fx9pv_.ctl
Finished restore at 2022-02-21 16:02:50
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('2022-02-21 15:10:50','YYYY-MM-DD 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 2 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
set newname for clone datafile 11 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 10 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, 2, 9, 5, 6, 11, 3, 10;
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 /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 2022-02-21 16:02:55
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
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 00003 to /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_020mdana_2_1_1
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00005 to /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_030mdanh_3_1_1
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00001 to /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_3: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_040mdano_4_1_1
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00010 to /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_4: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_060mdaol_6_1_1
channel ORA_AUX_DISK_4: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_060mdaol_6_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:16
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00002 to /opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_4: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_070mdaoo_7_1_1
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_030mdanh_3_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:20
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00009 to /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_080mdaph_8_1_1
channel ORA_AUX_DISK_3: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_040mdano_4_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:38
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00006 to /opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_3: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_090mdaqa_9_1_1
channel ORA_AUX_DISK_4: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_070mdaoo_7_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:23
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00011 to /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_4: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_0a0mdaqh_10_1_1
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_080mdaph_8_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:19
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_020mdana_2_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:41
channel ORA_AUX_DISK_3: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_090mdaqa_9_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:09
channel ORA_AUX_DISK_4: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_0a0mdaqh_10_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:08
Finished restore at 2022-02-21 16:03:44
datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=1097251425 file name=/opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_system_k17fxk7d_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=1097251425 file name=/opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_system_k17fy1nf_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1097251425 file name=/opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_system_k17fy5xd_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=1097251425 file name=/opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_undotbs1_k17fxk6o_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=1097251425 file name=/opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_undotbs1_k17fyr49_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=14 STAMP=1097251425 file name=/opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_undotbs1_k17fyrm2_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=1097251425 file name=/opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_sysaux_k17fxk6c_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=16 STAMP=1097251425 file name=/opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_sysaux_k17fxk7s_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-21 15:10:50','YYYY-MM-DD HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone 'PDB$SEED' "alter database datafile
2 online";
sql clone 'PDBRHEL8' "alter database datafile
9 online";
sql clone "alter database datafile 5 online";
sql clone 'PDB$SEED' "alter database datafile
6 online";
sql clone 'PDBRHEL8' "alter database datafile
11 online";
sql clone "alter database datafile 3 online";
sql clone 'PDBRHEL8' "alter database datafile
10 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "PDB$SEED":"SYSTEM", "PDBRHEL8":"SYSTEM", "UNDOTBS1", "PDB$SEED":"UNDOTBS1", "PDBRHEL8":"UNDOTBS1", "SYSAUX", "PDBRHEL8":"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 2 online
sql statement: alter database datafile 9 online
sql statement: alter database datafile 5 online
sql statement: alter database datafile 6 online
sql statement: alter database datafile 11 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 10 online
Starting recover at 2022-02-21 16:03:50
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-21/full_arc_CDBRHEL8_20220221_0r0mdara_27_1_1
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=9
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0t0mdave_29_1_1
channel ORA_AUX_DISK_3: starting archived log restore to default destination
channel ORA_AUX_DISK_3: restoring archived log
archived log thread=1 sequence=10
channel ORA_AUX_DISK_3: reading from backup piece /opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0v0mdbhe_31_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-21/full_arc_CDBRHEL8_20220221_0r0mdara_27_1_1 tag=FULL_ARC_BKP_2022_02_21
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:00
archived log file name=/opt/backup/recover/oracle/1_8_1096378714.dbf thread=1 sequence=8
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0t0mdave_29_1_1 tag=ARCHIVE_LOG_2022_02_21_15_02
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:00
archived log file name=/opt/backup/recover/oracle/1_9_1096378714.dbf thread=1 sequence=9
channel ORA_AUX_DISK_3: piece handle=/opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0v0mdbhe_31_1_1 tag=ARCHIVE_LOG_2022_02_21_15_11
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:04
archived log file name=/opt/backup/recover/oracle/1_10_1096378714.dbf thread=1 sequence=10
media recovery complete, elapsed time: 00:00:01
Finished recover at 2022-02-21 16:04:00
sql statement: alter database open read only
contents of Memory Script:
{
sql clone 'alter pluggable database PDBRHEL8 open read only';
}
executing Memory Script
sql statement: alter pluggable database PDBRHEL8 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 =
''/opt/backup/recover/oracle/CDBRHEL8/controlfile/o1_mf_k17fx9pv_.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 4294963752 bytes
Fixed Size 9142824 bytes
Variable Size 805306368 bytes
Database Buffers 3472883712 bytes
Redo Buffers 7630848 bytes
sql statement: alter system set control_files = ''/opt/backup/recover/oracle/CDBRHEL8/controlfile/o1_mf_k17fx9pv_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 4294963752 bytes
Fixed Size 9142824 bytes
Variable Size 805306368 bytes
Database Buffers 3472883712 bytes
Redo Buffers 7630848 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-21 15:10:50','YYYY-MM-DD HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 14 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 14;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 2022-02-21 16:04:57
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=14 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=93 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=174 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: 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 00014 to /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_elbo_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_0d0mdaqq_13_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_0d0mdaqq_13_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2022-02-21 16:05:01
datafile 14 switched to datafile copy
input datafile copy RECID=18 STAMP=1097251501 file name=/opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_elbo_k17g1d28_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-21 15:10:50','YYYY-MM-DD HH24:MI:SS')";
# online the datafiles restored or switched
sql clone 'PDBRHEL8' "alter database datafile
14 online";
# recover and open resetlogs
recover clone database tablespace "PDBRHEL8":"ELBOX", "SYSTEM", "PDB$SEED":"SYSTEM", "PDBRHEL8":"SYSTEM", "UNDOTBS1", "PDB$SEED":"UNDOTBS1", "PDBRHEL8":"UNDOTBS1", "SYSAUX", "PDBRHEL8":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 14 online
Starting recover at 2022-02-21 16:05:01
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-21/full_arc_CDBRHEL8_20220221_0r0mdara_27_1_1
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=9
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0t0mdave_29_1_1
channel ORA_AUX_DISK_3: starting archived log restore to default destination
channel ORA_AUX_DISK_3: restoring archived log
archived log thread=1 sequence=10
channel ORA_AUX_DISK_3: reading from backup piece /opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0v0mdbhe_31_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-21/full_arc_CDBRHEL8_20220221_0r0mdara_27_1_1 tag=FULL_ARC_BKP_2022_02_21
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:00
archived log file name=/opt/backup/recover/oracle/1_8_1096378714.dbf thread=1 sequence=8
channel clone_default: deleting archived log(s)
archived log file name=/opt/backup/recover/oracle/1_8_1096378714.dbf RECID=8 STAMP=1097251506
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0t0mdave_29_1_1 tag=ARCHIVE_LOG_2022_02_21_15_02
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:01
archived log file name=/opt/backup/recover/oracle/1_9_1096378714.dbf thread=1 sequence=9
channel clone_default: deleting archived log(s)
archived log file name=/opt/backup/recover/oracle/1_9_1096378714.dbf RECID=9 STAMP=1097251507
channel ORA_AUX_DISK_3: piece handle=/opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0v0mdbhe_31_1_1 tag=ARCHIVE_LOG_2022_02_21_15_11
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:04
archived log file name=/opt/backup/recover/oracle/1_10_1096378714.dbf thread=1 sequence=10
channel clone_default: deleting archived log(s)
archived log file name=/opt/backup/recover/oracle/1_10_1096378714.dbf RECID=10 STAMP=1097251509
media recovery complete, elapsed time: 00:00:01
Finished recover at 2022-02-21 16:05:11
database opened
contents of Memory Script:
{
sql clone 'alter pluggable database PDBRHEL8 open';
}
executing Memory Script
sql statement: alter pluggable database PDBRHEL8 open
contents of Memory Script:
{
# create directory for datapump import
sql 'PDBRHEL8' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/opt/backup/recover/dumpfile''";
# create directory for datapump export
sql clone 'PDBRHEL8' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/opt/backup/recover/dumpfile''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/opt/backup/recover/dumpfile''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/opt/backup/recover/dumpfile''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_Cviy_Evpl":
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/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
EXPDP> . . exported "cnx"."MYTABLERESTORE" 2.262 MB 100000 rows
EXPDP> Master table "SYS"."TSPITR_EXP_Cviy_Evpl" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_Cviy_Evpl is:
EXPDP> /opt/backup/recover/dumpfile/mypitr.dmp
EXPDP> Job "SYS"."TSPITR_EXP_Cviy_Evpl" successfully completed at Mon Feb 21 16:07:10 2022 elapsed 0 00:00:24
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
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_temp_k17fzkyh_.tmp deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_temp_k17fzjh4_.tmp deleted
auxiliary instance file /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_20_k17g3tz7_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_19_k17g3tz6_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_18_k17g3tz6_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_17_k17g3j89_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_16_k17g2yh9_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_15_k17g2yh9_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_14_k17g2yh9_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_13_k17g2rwj_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_12_k17g2rwh_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_11_k17g2rwh_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_10_k17g2rwh_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_9_k17g2jm2_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_8_k17g1r12_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_7_k17g1qyd_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_6_k17g1qww_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_5_k17g1qtl_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_4_k17g1qsl_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_3_k17g1qrf_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_2_k17g1qr0_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_1_k17g1qqn_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CVIY_PITR_PDBRHEL8_CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_elbo_k17g1d28_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_sysaux_k17fxk7s_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_sysaux_k17fxk6c_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_undotbs1_k17fyrm2_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_undotbs1_k17fyr49_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_undotbs1_k17fxk6o_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_system_k17fy5xd_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_system_k17fy1nf_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_system_k17fxk7d_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/controlfile/o1_mf_k17fx9pv_.ctl deleted
Finished recover at 2022-02-21 16:07:14
Importing the resulting dumpfile.
impdp cnx@`hostname -s`:1522/cnxrw directory=dumpdir dumpfile=mypitr.dmp remap_table=mytablerestore:mytablerestore2
Import: Release 19.0.0.0.0 - Production on Mon Feb 21 16:13:26 2022
Version 19.14.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "cnx"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "cnx"."SYS_IMPORT_FULL_01": cnx/********@ip-172-31-2-172:1522/cnxrw directory=dumpdir dumpfile=mypitr.dmp remap_table=mytablerestore:mytablerestore2
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "cnx"."MYTABLERESTORE2" 2.262 MB 100000 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "cnx"."SYS_IMPORT_FULL_01" successfully completed at Mon Feb 21 16:13:34 2022 elapsed 0 00:00:05
SQL> select count(*) from mytablerestore;
COUNT(*)
----------
50000
SQL> select count(*) from mytablerestore2;
COUNT(*)
----------
100000PITR of table to a different schema and using a different name
Use the REMAP TABLE clause to specify the new schema and new table name. As the import of the table is done by RMAN itself there's no dump file related clauses. The recovery command for this is
RECOVER TABLE cnx.mytablerestore OF PLUGGABLE DATABASE pdbrhel8
UNTIL TIME "to_date('2022-02-21 15:10:50','YYYY-MM-DD HH24:MI:SS')"
AUXILIARY DESTINATION '/opt/backup/recover/oracle'
REMAP TABLE cnx.mytablerestore:audit.mytablerestore2;The full output is RMAN> RECOVER TABLE cnx.mytablerestore OF PLUGGABLE DATABASE pdbrhel8
UNTIL TIME "to_date('2022-02-21 15:10:50','YYYY-MM-DD HH24:MI:SS')"
AUXILIARY DESTINATION '/opt/backup/recover/oracle'
REMAP TABLE cnx.mytablerestore:audit.mytablerestore2;2> 3> 4>
Starting recover at 2022-02-21 16:33:14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=378 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=261 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=140 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=264 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 PDB$SEED:SYSTEM
Tablespace PDBRHEL8:SYSTEM
Tablespace UNDOTBS1
Tablespace PDB$SEED:UNDOTBS1
Tablespace PDBRHEL8:UNDOTBS1
Creating automatic instance, with SID='Bepc'
initialization parameters used for automatic instance:
db_name=CDBRHEL8
db_unique_name=Bepc_pitr_pdbrhel8_CDBRHEL8
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/opt/company/app/oracle
_system_trig_enabled=FALSE
sga_target=4096M
processes=200
db_create_file_dest=/opt/backup/recover/oracle
log_archive_dest_1='location=/opt/backup/recover/oracle'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
wallet_root=/opt/company/app/oracle/wallet
#No auxiliary parameter file used
starting up automatic instance CDBRHEL8
Oracle instance started
Total System Global Area 4294963752 bytes
Fixed Size 9142824 bytes
Variable Size 805306368 bytes
Database Buffers 3472883712 bytes
Redo Buffers 7630848 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-21 15:10:50','YYYY-MM-DD 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 2022-02-21 16:33:27
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=91 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=172 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=2 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=11 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 /opt/backup/2022-02-21/arc_c-582064218-20220221-01.ctl
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-21/arc_c-582064218-20220221-01.ctl tag=TAG20220221T150207
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/backup/recover/oracle/CDBRHEL8/controlfile/o1_mf_k17hpsdq_.ctl
Finished restore at 2022-02-21 16:33:30
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('2022-02-21 15:10:50','YYYY-MM-DD 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 2 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
set newname for clone datafile 11 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 10 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, 2, 9, 5, 6, 11, 3, 10;
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 /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 2022-02-21 16:33:35
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
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 00003 to /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_020mdana_2_1_1
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00005 to /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_030mdanh_3_1_1
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00001 to /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_3: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_040mdano_4_1_1
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00010 to /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_4: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_060mdaol_6_1_1
channel ORA_AUX_DISK_4: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_060mdaol_6_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:17
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00002 to /opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_4: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_070mdaoo_7_1_1
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_030mdanh_3_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:22
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00009 to /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_080mdaph_8_1_1
channel ORA_AUX_DISK_3: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_040mdano_4_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:28
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00006 to /opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_3: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_090mdaqa_9_1_1
channel ORA_AUX_DISK_4: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_070mdaoo_7_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:19
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00011 to /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_4: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_0a0mdaqh_10_1_1
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_080mdaph_8_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:18
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_020mdana_2_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:46
channel ORA_AUX_DISK_3: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_090mdaqa_9_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:18
channel ORA_AUX_DISK_4: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_0a0mdaqh_10_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:09
Finished restore at 2022-02-21 16:34:22
datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=1097253262 file name=/opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_system_k17hq0oy_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=1097253262 file name=/opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_system_k17hqls7_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1097253262 file name=/opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_system_k17hqqo2_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=1097253262 file name=/opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_undotbs1_k17hq0of_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=1097253262 file name=/opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_undotbs1_k17hqwqd_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=14 STAMP=1097253262 file name=/opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_undotbs1_k17hr5nh_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=1097253262 file name=/opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_sysaux_k17hq0nz_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=16 STAMP=1097253262 file name=/opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_sysaux_k17hq0ph_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-21 15:10:50','YYYY-MM-DD HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone 'PDB$SEED' "alter database datafile
2 online";
sql clone 'PDBRHEL8' "alter database datafile
9 online";
sql clone "alter database datafile 5 online";
sql clone 'PDB$SEED' "alter database datafile
6 online";
sql clone 'PDBRHEL8' "alter database datafile
11 online";
sql clone "alter database datafile 3 online";
sql clone 'PDBRHEL8' "alter database datafile
10 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "PDB$SEED":"SYSTEM", "PDBRHEL8":"SYSTEM", "UNDOTBS1", "PDB$SEED":"UNDOTBS1", "PDBRHEL8":"UNDOTBS1", "SYSAUX", "PDBRHEL8":"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 2 online
sql statement: alter database datafile 9 online
sql statement: alter database datafile 5 online
sql statement: alter database datafile 6 online
sql statement: alter database datafile 11 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 10 online
Starting recover at 2022-02-21 16:34:27
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-21/full_arc_CDBRHEL8_20220221_0r0mdara_27_1_1
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=9
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0t0mdave_29_1_1
channel ORA_AUX_DISK_3: starting archived log restore to default destination
channel ORA_AUX_DISK_3: restoring archived log
archived log thread=1 sequence=10
channel ORA_AUX_DISK_3: reading from backup piece /opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0v0mdbhe_31_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-21/full_arc_CDBRHEL8_20220221_0r0mdara_27_1_1 tag=FULL_ARC_BKP_2022_02_21
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:00
archived log file name=/opt/backup/recover/oracle/1_8_1096378714.dbf thread=1 sequence=8
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0t0mdave_29_1_1 tag=ARCHIVE_LOG_2022_02_21_15_02
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:01
archived log file name=/opt/backup/recover/oracle/1_9_1096378714.dbf thread=1 sequence=9
channel ORA_AUX_DISK_3: piece handle=/opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0v0mdbhe_31_1_1 tag=ARCHIVE_LOG_2022_02_21_15_11
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:05
archived log file name=/opt/backup/recover/oracle/1_10_1096378714.dbf thread=1 sequence=10
media recovery complete, elapsed time: 00:00:00
Finished recover at 2022-02-21 16:34:37
sql statement: alter database open read only
contents of Memory Script:
{
sql clone 'alter pluggable database PDBRHEL8 open read only';
}
executing Memory Script
sql statement: alter pluggable database PDBRHEL8 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 =
''/opt/backup/recover/oracle/CDBRHEL8/controlfile/o1_mf_k17hpsdq_.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 4294963752 bytes
Fixed Size 9142824 bytes
Variable Size 805306368 bytes
Database Buffers 3472883712 bytes
Redo Buffers 7630848 bytes
sql statement: alter system set control_files = ''/opt/backup/recover/oracle/CDBRHEL8/controlfile/o1_mf_k17hpsdq_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 4294963752 bytes
Fixed Size 9142824 bytes
Variable Size 805306368 bytes
Database Buffers 3472883712 bytes
Redo Buffers 7630848 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-21 15:10:50','YYYY-MM-DD HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 14 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 14;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 2022-02-21 16:35:37
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=14 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=93 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=174 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: 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 00014 to /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_elbo_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_0d0mdaqq_13_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_0d0mdaqq_13_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2022-02-21 16:35:40
datafile 14 switched to datafile copy
input datafile copy RECID=18 STAMP=1097253341 file name=/opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_elbo_k17htvtw_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-21 15:10:50','YYYY-MM-DD HH24:MI:SS')";
# online the datafiles restored or switched
sql clone 'PDBRHEL8' "alter database datafile
14 online";
# recover and open resetlogs
recover clone database tablespace "PDBRHEL8":"ELBOX", "SYSTEM", "PDB$SEED":"SYSTEM", "PDBRHEL8":"SYSTEM", "UNDOTBS1", "PDB$SEED":"UNDOTBS1", "PDBRHEL8":"UNDOTBS1", "SYSAUX", "PDBRHEL8":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 14 online
Starting recover at 2022-02-21 16:35:41
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-21/full_arc_CDBRHEL8_20220221_0r0mdara_27_1_1
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=9
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0t0mdave_29_1_1
channel ORA_AUX_DISK_3: starting archived log restore to default destination
channel ORA_AUX_DISK_3: restoring archived log
archived log thread=1 sequence=10
channel ORA_AUX_DISK_3: reading from backup piece /opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0v0mdbhe_31_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-21/full_arc_CDBRHEL8_20220221_0r0mdara_27_1_1 tag=FULL_ARC_BKP_2022_02_21
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:00
archived log file name=/opt/backup/recover/oracle/1_8_1096378714.dbf thread=1 sequence=8
channel clone_default: deleting archived log(s)
archived log file name=/opt/backup/recover/oracle/1_8_1096378714.dbf RECID=8 STAMP=1097253346
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0t0mdave_29_1_1 tag=ARCHIVE_LOG_2022_02_21_15_02
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:00
archived log file name=/opt/backup/recover/oracle/1_9_1096378714.dbf thread=1 sequence=9
channel clone_default: deleting archived log(s)
archived log file name=/opt/backup/recover/oracle/1_9_1096378714.dbf RECID=9 STAMP=1097253346
channel ORA_AUX_DISK_3: piece handle=/opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0v0mdbhe_31_1_1 tag=ARCHIVE_LOG_2022_02_21_15_11
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:03
archived log file name=/opt/backup/recover/oracle/1_10_1096378714.dbf thread=1 sequence=10
channel clone_default: deleting archived log(s)
archived log file name=/opt/backup/recover/oracle/1_10_1096378714.dbf RECID=10 STAMP=1097253348
media recovery complete, elapsed time: 00:00:00
Finished recover at 2022-02-21 16:35:50
database opened
contents of Memory Script:
{
sql clone 'alter pluggable database PDBRHEL8 open';
}
executing Memory Script
sql statement: alter pluggable database PDBRHEL8 open
contents of Memory Script:
{
# create directory for datapump import
sql 'PDBRHEL8' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/opt/backup/recover/oracle''";
# create directory for datapump export
sql clone 'PDBRHEL8' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/opt/backup/recover/oracle''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/opt/backup/recover/oracle''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/opt/backup/recover/oracle''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_Bepc_icry":
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> . . exported "cnx"."MYTABLERESTORE" 2.262 MB 100000 rows
EXPDP> Master table "SYS"."TSPITR_EXP_Bepc_icry" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_Bepc_icry is:
EXPDP> /opt/backup/recover/oracle/tspitr_Bepc_67677.dmp
EXPDP> Job "SYS"."TSPITR_EXP_Bepc_icry" successfully completed at Mon Feb 21 16:37:50 2022 elapsed 0 00:00:25
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_Bepc_kvpq" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_Bepc_kvpq":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "audit"."MYTABLERESTORE2" 2.262 MB 100000 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Job "SYS"."TSPITR_IMP_Bepc_kvpq" successfully completed at Mon Feb 21 16:37:57 2022 elapsed 0 00:00:03
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_temp_k17hrzpk_.tmp deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_temp_k17hry8p_.tmp deleted
auxiliary instance file /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_20_k17hxb36_.log deleted
auxiliary instance file /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_19_k17hxb36_.log deleted
auxiliary instance file /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_18_k17hxb35_.log deleted
auxiliary instance file /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_17_k17hwzx5_.log deleted
auxiliary instance file /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_16_k17hwcxm_.log deleted
auxiliary instance file /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_15_k17hwcxm_.log deleted
auxiliary instance file /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_14_k17hwcxm_.log deleted
auxiliary instance file /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_13_k17hw7q3_.log deleted
auxiliary instance file /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_12_k17hw7nf_.log deleted
auxiliary instance file /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_11_k17hw7nf_.log deleted
auxiliary instance file /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_10_k17hw7nf_.log deleted
auxiliary instance file /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_9_k17hvzdf_.log deleted
auxiliary instance file /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_8_k17hv74b_.log deleted
auxiliary instance file /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_7_k17hv72h_.log deleted
auxiliary instance file /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_6_k17hv70x_.log deleted
auxiliary instance file /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_5_k17hv6z4_.log deleted
auxiliary instance file /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_4_k17hv6yo_.log deleted
auxiliary instance file /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_3_k17hv6y7_.log deleted
auxiliary instance file /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_2_k17hv6xs_.log deleted
auxiliary instance file /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_1_k17hv6xd_.log deleted
auxiliary instance file /opt/backup/recover/oracle/BEPC_PITR_PDBRHEL8_CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_elbo_k17htvtw_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_sysaux_k17hq0ph_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_sysaux_k17hq0nz_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_undotbs1_k17hr5nh_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_undotbs1_k17hqwqd_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_undotbs1_k17hq0of_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_system_k17hqqo2_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_system_k17hqls7_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_system_k17hq0oy_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/controlfile/o1_mf_k17hpsdq_.ctl deleted
auxiliary instance file tspitr_Bepc_67677.dmp deleted
Finished recover at 2022-02-21 16:37:58PITR of table to same schema with a different name
It is possible to import into same schema but with a different name. If RMAN finds that table already exists it will give an error. The recovery command is
RECOVER TABLE cnx.mytablerestore OF PLUGGABLE DATABASE pdbrhel8
UNTIL TIME "to_date('2022-02-21 15:10:50','YYYY-MM-DD HH24:MI:SS')"
AUXILIARY DESTINATION '/opt/backup/recover/oracle'
REMAP TABLE cnx.mytablerestore:mytablerestore2;The full output is RMAN> RECOVER TABLE cnx.mytablerestore OF PLUGGABLE DATABASE pdbrhel8
UNTIL TIME "to_date('2022-02-21 15:10:50','YYYY-MM-DD HH24:MI:SS')"
AUXILIARY DESTINATION '/opt/backup/recover/oracle'
REMAP TABLE cnx.mytablerestore:mytablerestore2;
2> 3> 4>
Starting recover at 2022-02-21 16:44:09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=263 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=378 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=262 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=141 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 PDB$SEED:SYSTEM
Tablespace PDBRHEL8:SYSTEM
Tablespace UNDOTBS1
Tablespace PDB$SEED:UNDOTBS1
Tablespace PDBRHEL8:UNDOTBS1
Creating automatic instance, with SID='CfEq'
initialization parameters used for automatic instance:
db_name=CDBRHEL8
db_unique_name=CfEq_pitr_pdbrhel8_CDBRHEL8
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/opt/company/app/oracle
_system_trig_enabled=FALSE
sga_target=4096M
processes=200
db_create_file_dest=/opt/backup/recover/oracle
log_archive_dest_1='location=/opt/backup/recover/oracle'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
wallet_root=/opt/company/app/oracle/wallet
#No auxiliary parameter file used
starting up automatic instance CDBRHEL8
Oracle instance started
Total System Global Area 4294963752 bytes
Fixed Size 9142824 bytes
Variable Size 805306368 bytes
Database Buffers 3472883712 bytes
Redo Buffers 7630848 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-21 15:10:50','YYYY-MM-DD 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 2022-02-21 16:44:22
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=91 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=172 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=2 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=11 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 /opt/backup/2022-02-21/arc_c-582064218-20220221-01.ctl
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-21/arc_c-582064218-20220221-01.ctl tag=TAG20220221T150207
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/backup/recover/oracle/CDBRHEL8/controlfile/o1_mf_k17jc8qv_.ctl
Finished restore at 2022-02-21 16:44:25
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('2022-02-21 15:10:50','YYYY-MM-DD 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 2 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
set newname for clone datafile 11 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 10 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, 2, 9, 5, 6, 11, 3, 10;
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 /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 2022-02-21 16:44:30
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
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 00003 to /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_020mdana_2_1_1
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00005 to /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_030mdanh_3_1_1
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00001 to /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_3: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_040mdano_4_1_1
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00010 to /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_4: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_060mdaol_6_1_1
channel ORA_AUX_DISK_4: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_060mdaol_6_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:16
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00002 to /opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_4: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_070mdaoo_7_1_1
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_030mdanh_3_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:24
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00009 to /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_080mdaph_8_1_1
channel ORA_AUX_DISK_3: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_040mdano_4_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:29
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00006 to /opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_3: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_090mdaqa_9_1_1
channel ORA_AUX_DISK_4: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_070mdaoo_7_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:21
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00011 to /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_4: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_0a0mdaqh_10_1_1
channel ORA_AUX_DISK_3: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_090mdaqa_9_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:12
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_020mdana_2_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:46
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_080mdaph_8_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:21
channel ORA_AUX_DISK_4: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_0a0mdaqh_10_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:09
Finished restore at 2022-02-21 16:45:18
datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=1097253918 file name=/opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_system_k17jcj24_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=1097253918 file name=/opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_system_k17jd0yj_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1097253918 file name=/opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_system_k17jd98k_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=1097253919 file name=/opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_undotbs1_k17jcj1k_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=1097253919 file name=/opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_undotbs1_k17jdfmz_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=14 STAMP=1097253919 file name=/opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_undotbs1_k17jdp1x_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=1097253919 file name=/opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_sysaux_k17jcj13_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=16 STAMP=1097253919 file name=/opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_sysaux_k17jcj2s_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-21 15:10:50','YYYY-MM-DD HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone 'PDB$SEED' "alter database datafile
2 online";
sql clone 'PDBRHEL8' "alter database datafile
9 online";
sql clone "alter database datafile 5 online";
sql clone 'PDB$SEED' "alter database datafile
6 online";
sql clone 'PDBRHEL8' "alter database datafile
11 online";
sql clone "alter database datafile 3 online";
sql clone 'PDBRHEL8' "alter database datafile
10 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "PDB$SEED":"SYSTEM", "PDBRHEL8":"SYSTEM", "UNDOTBS1", "PDB$SEED":"UNDOTBS1", "PDBRHEL8":"UNDOTBS1", "SYSAUX", "PDBRHEL8":"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 2 online
sql statement: alter database datafile 9 online
sql statement: alter database datafile 5 online
sql statement: alter database datafile 6 online
sql statement: alter database datafile 11 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 10 online
Starting recover at 2022-02-21 16:45:23
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-21/full_arc_CDBRHEL8_20220221_0r0mdara_27_1_1
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=9
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0t0mdave_29_1_1
channel ORA_AUX_DISK_3: starting archived log restore to default destination
channel ORA_AUX_DISK_3: restoring archived log
archived log thread=1 sequence=10
channel ORA_AUX_DISK_3: reading from backup piece /opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0v0mdbhe_31_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-21/full_arc_CDBRHEL8_20220221_0r0mdara_27_1_1 tag=FULL_ARC_BKP_2022_02_21
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:00
archived log file name=/opt/backup/recover/oracle/1_8_1096378714.dbf thread=1 sequence=8
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0t0mdave_29_1_1 tag=ARCHIVE_LOG_2022_02_21_15_02
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:01
archived log file name=/opt/backup/recover/oracle/1_9_1096378714.dbf thread=1 sequence=9
channel ORA_AUX_DISK_3: piece handle=/opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0v0mdbhe_31_1_1 tag=ARCHIVE_LOG_2022_02_21_15_11
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:04
archived log file name=/opt/backup/recover/oracle/1_10_1096378714.dbf thread=1 sequence=10
media recovery complete, elapsed time: 00:00:01
Finished recover at 2022-02-21 16:45:33
sql statement: alter database open read only
contents of Memory Script:
{
sql clone 'alter pluggable database PDBRHEL8 open read only';
}
executing Memory Script
sql statement: alter pluggable database PDBRHEL8 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 =
''/opt/backup/recover/oracle/CDBRHEL8/controlfile/o1_mf_k17jc8qv_.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 4294963752 bytes
Fixed Size 9142824 bytes
Variable Size 805306368 bytes
Database Buffers 3472883712 bytes
Redo Buffers 7630848 bytes
sql statement: alter system set control_files = ''/opt/backup/recover/oracle/CDBRHEL8/controlfile/o1_mf_k17jc8qv_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 4294963752 bytes
Fixed Size 9142824 bytes
Variable Size 805306368 bytes
Database Buffers 3472883712 bytes
Redo Buffers 7630848 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-21 15:10:50','YYYY-MM-DD HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 14 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 14;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 2022-02-21 16:46:35
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=14 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=93 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=175 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: 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 00014 to /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_elbo_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_0d0mdaqq_13_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_0d0mdaqq_13_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2022-02-21 16:46:39
datafile 14 switched to datafile copy
input datafile copy RECID=18 STAMP=1097253999 file name=/opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_elbo_k17jhg73_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-21 15:10:50','YYYY-MM-DD HH24:MI:SS')";
# online the datafiles restored or switched
sql clone 'PDBRHEL8' "alter database datafile
14 online";
# recover and open resetlogs
recover clone database tablespace "PDBRHEL8":"ELBOX", "SYSTEM", "PDB$SEED":"SYSTEM", "PDBRHEL8":"SYSTEM", "UNDOTBS1", "PDB$SEED":"UNDOTBS1", "PDBRHEL8":"UNDOTBS1", "SYSAUX", "PDBRHEL8":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 14 online
Starting recover at 2022-02-21 16:46:39
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-21/full_arc_CDBRHEL8_20220221_0r0mdara_27_1_1
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=9
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0t0mdave_29_1_1
channel ORA_AUX_DISK_3: starting archived log restore to default destination
channel ORA_AUX_DISK_3: restoring archived log
archived log thread=1 sequence=10
channel ORA_AUX_DISK_3: reading from backup piece /opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0v0mdbhe_31_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-21/full_arc_CDBRHEL8_20220221_0r0mdara_27_1_1 tag=FULL_ARC_BKP_2022_02_21
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:00
archived log file name=/opt/backup/recover/oracle/1_8_1096378714.dbf thread=1 sequence=8
channel clone_default: deleting archived log(s)
archived log file name=/opt/backup/recover/oracle/1_8_1096378714.dbf RECID=8 STAMP=1097254004
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0t0mdave_29_1_1 tag=ARCHIVE_LOG_2022_02_21_15_02
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:01
archived log file name=/opt/backup/recover/oracle/1_9_1096378714.dbf thread=1 sequence=9
channel clone_default: deleting archived log(s)
archived log file name=/opt/backup/recover/oracle/1_9_1096378714.dbf RECID=9 STAMP=1097254004
channel ORA_AUX_DISK_3: piece handle=/opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0v0mdbhe_31_1_1 tag=ARCHIVE_LOG_2022_02_21_15_11
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:04
archived log file name=/opt/backup/recover/oracle/1_10_1096378714.dbf thread=1 sequence=10
channel clone_default: deleting archived log(s)
archived log file name=/opt/backup/recover/oracle/1_10_1096378714.dbf RECID=10 STAMP=1097254007
media recovery complete, elapsed time: 00:00:01
Finished recover at 2022-02-21 16:46:49
database opened
contents of Memory Script:
{
sql clone 'alter pluggable database PDBRHEL8 open';
}
executing Memory Script
sql statement: alter pluggable database PDBRHEL8 open
contents of Memory Script:
{
# create directory for datapump import
sql 'PDBRHEL8' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/opt/backup/recover/oracle''";
# create directory for datapump export
sql clone 'PDBRHEL8' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/opt/backup/recover/oracle''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/opt/backup/recover/oracle''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/opt/backup/recover/oracle''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_CfEq_ilmF":
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> . . exported "cnx"."MYTABLERESTORE" 2.262 MB 100000 rows
EXPDP> Master table "SYS"."TSPITR_EXP_CfEq_ilmF" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_CfEq_ilmF is:
EXPDP> /opt/backup/recover/oracle/tspitr_CfEq_45885.dmp
EXPDP> Job "SYS"."TSPITR_EXP_CfEq_ilmF" successfully completed at Mon Feb 21 16:48:49 2022 elapsed 0 00:00:25
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_CfEq_aaou" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_CfEq_aaou":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "cnx"."MYTABLERESTORE2" 2.262 MB 100000 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Job "SYS"."TSPITR_IMP_CfEq_aaou" successfully completed at Mon Feb 21 16:48:55 2022 elapsed 0 00:00:03
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_temp_k17jfhms_.tmp deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_temp_k17jfg4t_.tmp deleted
auxiliary instance file /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_20_k17jkw28_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_19_k17jkw28_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_18_k17jkw28_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_17_k17jklz3_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_16_k17jjycy_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_15_k17jjycy_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_14_k17jjycy_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_13_k17jjt6k_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_12_k17jjt6k_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_11_k17jjt6k_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_10_k17jjt3l_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_9_k17jjlt9_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_8_k17jhsls_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_7_k17jhsl7_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_6_k17jhsjw_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_5_k17jhshp_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_4_k17jhsgd_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_3_k17jhsfk_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_2_k17jhsf3_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_1_k17jhsdq_.log deleted
auxiliary instance file /opt/backup/recover/oracle/CFEQ_PITR_PDBRHEL8_CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_elbo_k17jhg73_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_sysaux_k17jcj2s_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_sysaux_k17jcj13_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_undotbs1_k17jdp1x_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_undotbs1_k17jdfmz_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_undotbs1_k17jcj1k_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_system_k17jd98k_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_system_k17jd0yj_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_system_k17jcj24_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/controlfile/o1_mf_k17jc8qv_.ctl deleted
auxiliary instance file tspitr_CfEq_45885.dmp deleted
Finished recover at 2022-02-21 16:48:57PITR of table after the last backup
In this example there's a full DB backup and archive log backups but table is recovered to a point in time before the next backup has taken place. In this case the RMAN will use the archive logs that are available on disk to roll forward to destired point in time. The full output below show how RMAN is using archivelog available on disks as oppose to from backups.
RMAN> RUN
{
SET AUXILIARY INSTANCE PARAMETER FILE TO '/home/oracle/aux.ora';
2> 3> 4> RECOVER TABLE cnx.mytablerestore OF PLUGGABLE DATABASE pdbrhel8
UNTIL TIME "to_date('2022-02-22 10:05:50','YYYY-MM-DD HH24:MI:SS')"
5> 6> AUXILIARY DESTINATION '/opt/backup/recover/oracle'
7> REMAP TABLE cnx.mytablerestore:mytablerestore2;
}8>
executing command: SET auxiliary parameter file
using target database control file instead of recovery catalog
Starting recover at 2022-02-22 10:07:41
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=148 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=272 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=273 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 PDB$SEED:SYSTEM
Tablespace PDBRHEL8:SYSTEM
Tablespace UNDOTBS1
Tablespace PDB$SEED:UNDOTBS1
Tablespace PDBRHEL8:UNDOTBS1
Creating automatic instance, with SID='lymq'
using contents of file /home/oracle/aux.ora
initialization parameters used for automatic instance:
db_name=CDBRHEL8
db_unique_name=lymq_pitr_pdbrhel8_CDBRHEL8
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/opt/company/app/oracle
_system_trig_enabled=FALSE
sga_target=4096M
processes=200
db_create_file_dest=/opt/backup/recover/oracle
log_archive_dest_1='location=/opt/backup/recover/oracle'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
wallet_root=/opt/company/app/oracle/wallet
ifile=/home/oracle/aux.ora
starting up automatic instance CDBRHEL8
Oracle instance started
Total System Global Area 2147482136 bytes
Fixed Size 9136664 bytes
Variable Size 486539264 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7639040 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-22 10:05:50','YYYY-MM-DD 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 2022-02-22 10:07:55
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=2 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=91 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=172 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=11 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 /opt/backup/2022-02-21/arc_c-582064218-20220221-02.ctl
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-21/arc_c-582064218-20220221-02.ctl tag=TAG20220221T151145
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/backup/recover/oracle/CDBRHEL8/controlfile/o1_mf_k19fhy9q_.ctl
Finished restore at 2022-02-22 10:07:59
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('2022-02-22 10:05:50','YYYY-MM-DD 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 2 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
set newname for clone datafile 11 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 10 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, 2, 9, 5, 6, 11, 3, 10;
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 /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 2022-02-22 10:08:04
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
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 00003 to /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_020mdana_2_1_1
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00005 to /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_030mdanh_3_1_1
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00001 to /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_3: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_040mdano_4_1_1
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00010 to /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_4: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_060mdaol_6_1_1
channel ORA_AUX_DISK_4: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_060mdaol_6_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:16
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00002 to /opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_4: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_070mdaoo_7_1_1
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_030mdanh_3_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:24
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00009 to /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_080mdaph_8_1_1
channel ORA_AUX_DISK_3: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_040mdano_4_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:33
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00006 to /opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_3: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_090mdaqa_9_1_1
channel ORA_AUX_DISK_4: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_070mdaoo_7_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:18
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00011 to /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_4: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_0a0mdaqh_10_1_1
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_080mdaph_8_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:18
channel ORA_AUX_DISK_3: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_090mdaqa_9_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:12
channel ORA_AUX_DISK_4: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_0a0mdaqh_10_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:12
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_020mdana_2_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:46
Finished restore at 2022-02-22 10:08:51
datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=1097316531 file name=/opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_system_k19fj5qs_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=1097316531 file name=/opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_system_k19fjots_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1097316531 file name=/opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_system_k19fjy79_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=1097316531 file name=/opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_undotbs1_k19fj5q7_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=1097316531 file name=/opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_undotbs1_k19fk6l8_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=14 STAMP=1097316531 file name=/opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_undotbs1_k19fk7dl_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=1097316531 file name=/opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_sysaux_k19fj5pr_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=16 STAMP=1097316531 file name=/opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_sysaux_k19fj5r8_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-22 10:05:50','YYYY-MM-DD HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone 'PDB$SEED' "alter database datafile
2 online";
sql clone 'PDBRHEL8' "alter database datafile
9 online";
sql clone "alter database datafile 5 online";
sql clone 'PDB$SEED' "alter database datafile
6 online";
sql clone 'PDBRHEL8' "alter database datafile
11 online";
sql clone "alter database datafile 3 online";
sql clone 'PDBRHEL8' "alter database datafile
10 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "PDB$SEED":"SYSTEM", "PDBRHEL8":"SYSTEM", "UNDOTBS1", "PDB$SEED":"UNDOTBS1", "PDBRHEL8":"UNDOTBS1", "SYSAUX", "PDBRHEL8":"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 2 online
sql statement: alter database datafile 9 online
sql statement: alter database datafile 5 online
sql statement: alter database datafile 6 online
sql statement: alter database datafile 11 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 10 online
Starting recover at 2022-02-22 10:08:56
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
starting media recovery
archived log for thread 1 with sequence 11 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_11.278.1097249257
archived log for thread 1 with sequence 12 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_12.277.1097249831
archived log for thread 1 with sequence 13 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_13.279.1097250935
archived log for thread 1 with sequence 14 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_14.280.1097251375
archived log for thread 1 with sequence 15 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_15.281.1097252503
archived log for thread 1 with sequence 16 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_16.282.1097252757
archived log for thread 1 with sequence 17 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_17.283.1097253215
archived log for thread 1 with sequence 18 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_18.284.1097253871
archived log for thread 1 with sequence 19 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_19.285.1097254295
archived log for thread 1 with sequence 20 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_20.286.1097254911
archived log for thread 1 with sequence 21 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_22/thread_1_seq_21.287.1097316107
archived log for thread 1 with sequence 22 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_22/thread_1_seq_22.288.1097316463
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-21/full_arc_CDBRHEL8_20220221_0r0mdara_27_1_1
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=9
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0t0mdave_29_1_1
channel ORA_AUX_DISK_3: starting archived log restore to default destination
channel ORA_AUX_DISK_3: restoring archived log
archived log thread=1 sequence=10
channel ORA_AUX_DISK_3: reading from backup piece /opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0v0mdbhe_31_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-21/full_arc_CDBRHEL8_20220221_0r0mdara_27_1_1 tag=FULL_ARC_BKP_2022_02_21
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:00
archived log file name=/opt/backup/recover/oracle/1_8_1096378714.dbf thread=1 sequence=8
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0t0mdave_29_1_1 tag=ARCHIVE_LOG_2022_02_21_15_02
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:00
archived log file name=/opt/backup/recover/oracle/1_9_1096378714.dbf thread=1 sequence=9
channel ORA_AUX_DISK_3: piece handle=/opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0v0mdbhe_31_1_1 tag=ARCHIVE_LOG_2022_02_21_15_11
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:04
archived log file name=/opt/backup/recover/oracle/1_10_1096378714.dbf thread=1 sequence=10
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_11.278.1097249257 thread=1 sequence=11
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_12.277.1097249831 thread=1 sequence=12
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_13.279.1097250935 thread=1 sequence=13
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_14.280.1097251375 thread=1 sequence=14
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_15.281.1097252503 thread=1 sequence=15
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_16.282.1097252757 thread=1 sequence=16
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_17.283.1097253215 thread=1 sequence=17
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_18.284.1097253871 thread=1 sequence=18
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_19.285.1097254295 thread=1 sequence=19
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_20.286.1097254911 thread=1 sequence=20
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_22/thread_1_seq_21.287.1097316107 thread=1 sequence=21
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_22/thread_1_seq_22.288.1097316463 thread=1 sequence=22
media recovery complete, elapsed time: 00:00:07
Finished recover at 2022-02-22 10:09:12
sql statement: alter database open read only
contents of Memory Script:
{
sql clone 'alter pluggable database PDBRHEL8 open read only';
}
executing Memory Script
sql statement: alter pluggable database PDBRHEL8 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 =
''/opt/backup/recover/oracle/CDBRHEL8/controlfile/o1_mf_k19fhy9q_.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 2147482136 bytes
Fixed Size 9136664 bytes
Variable Size 486539264 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7639040 bytes
sql statement: alter system set control_files = ''/opt/backup/recover/oracle/CDBRHEL8/controlfile/o1_mf_k19fhy9q_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2147482136 bytes
Fixed Size 9136664 bytes
Variable Size 486539264 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7639040 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-22 10:05:50','YYYY-MM-DD HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 14 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 14;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 2022-02-22 10:10:11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=256 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=13 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=93 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=175 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 00014 to /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_elbo_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_0d0mdaqq_13_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-21/full_bkp_CDBRHEL8_20220221_0d0mdaqq_13_1_1 tag=FULL_BKP_2022_02_21
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2022-02-22 10:10:14
datafile 14 switched to datafile copy
input datafile copy RECID=18 STAMP=1097316614 file name=/opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_elbo_k19fn5kg_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-22 10:05:50','YYYY-MM-DD HH24:MI:SS')";
# online the datafiles restored or switched
sql clone 'PDBRHEL8' "alter database datafile
14 online";
# recover and open resetlogs
recover clone database tablespace "PDBRHEL8":"ELBOX", "SYSTEM", "PDB$SEED":"SYSTEM", "PDBRHEL8":"SYSTEM", "UNDOTBS1", "PDB$SEED":"UNDOTBS1", "PDBRHEL8":"UNDOTBS1", "SYSAUX", "PDBRHEL8":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 14 online
Starting recover at 2022-02-22 10:10:15
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
starting media recovery
archived log for thread 1 with sequence 11 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_11.278.1097249257
archived log for thread 1 with sequence 12 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_12.277.1097249831
archived log for thread 1 with sequence 13 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_13.279.1097250935
archived log for thread 1 with sequence 14 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_14.280.1097251375
archived log for thread 1 with sequence 15 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_15.281.1097252503
archived log for thread 1 with sequence 16 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_16.282.1097252757
archived log for thread 1 with sequence 17 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_17.283.1097253215
archived log for thread 1 with sequence 18 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_18.284.1097253871
archived log for thread 1 with sequence 19 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_19.285.1097254295
archived log for thread 1 with sequence 20 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_20.286.1097254911
archived log for thread 1 with sequence 21 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_22/thread_1_seq_21.287.1097316107
archived log for thread 1 with sequence 22 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_22/thread_1_seq_22.288.1097316463
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-21/full_arc_CDBRHEL8_20220221_0r0mdara_27_1_1
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=9
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0t0mdave_29_1_1
channel ORA_AUX_DISK_3: starting archived log restore to default destination
channel ORA_AUX_DISK_3: restoring archived log
archived log thread=1 sequence=10
channel ORA_AUX_DISK_3: reading from backup piece /opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0v0mdbhe_31_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-21/full_arc_CDBRHEL8_20220221_0r0mdara_27_1_1 tag=FULL_ARC_BKP_2022_02_21
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:00
archived log file name=/opt/backup/recover/oracle/1_8_1096378714.dbf thread=1 sequence=8
channel clone_default: deleting archived log(s)
archived log file name=/opt/backup/recover/oracle/1_8_1096378714.dbf RECID=9 STAMP=1097316620
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0t0mdave_29_1_1 tag=ARCHIVE_LOG_2022_02_21_15_02
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:00
archived log file name=/opt/backup/recover/oracle/1_9_1096378714.dbf thread=1 sequence=9
channel clone_default: deleting archived log(s)
archived log file name=/opt/backup/recover/oracle/1_9_1096378714.dbf RECID=10 STAMP=1097316620
channel ORA_AUX_DISK_3: piece handle=/opt/backup/2022-02-21/archive_CDBRHEL8_20220221_0v0mdbhe_31_1_1 tag=ARCHIVE_LOG_2022_02_21_15_11
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:03
archived log file name=/opt/backup/recover/oracle/1_10_1096378714.dbf thread=1 sequence=10
channel clone_default: deleting archived log(s)
archived log file name=/opt/backup/recover/oracle/1_10_1096378714.dbf RECID=11 STAMP=1097316622
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_11.278.1097249257 thread=1 sequence=11
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_12.277.1097249831 thread=1 sequence=12
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_13.279.1097250935 thread=1 sequence=13
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_14.280.1097251375 thread=1 sequence=14
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_15.281.1097252503 thread=1 sequence=15
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_16.282.1097252757 thread=1 sequence=16
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_17.283.1097253215 thread=1 sequence=17
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_18.284.1097253871 thread=1 sequence=18
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_19.285.1097254295 thread=1 sequence=19
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_21/thread_1_seq_20.286.1097254911 thread=1 sequence=20
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_22/thread_1_seq_21.287.1097316107 thread=1 sequence=21
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_22/thread_1_seq_22.288.1097316463 thread=1 sequence=22
media recovery complete, elapsed time: 00:00:03
Finished recover at 2022-02-22 10:10:27
database opened
contents of Memory Script:
{
sql clone 'alter pluggable database PDBRHEL8 open';
}
executing Memory Script
sql statement: alter pluggable database PDBRHEL8 open
contents of Memory Script:
{
# create directory for datapump import
sql 'PDBRHEL8' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/opt/backup/recover/oracle''";
# create directory for datapump export
sql clone 'PDBRHEL8' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/opt/backup/recover/oracle''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/opt/backup/recover/oracle''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/opt/backup/recover/oracle''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_lymq_wnsr":
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> . . exported "cnx"."MYTABLERESTORE" 1.135 MB 50000 rows
EXPDP> Master table "SYS"."TSPITR_EXP_lymq_wnsr" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_lymq_wnsr is:
EXPDP> /opt/backup/recover/oracle/tspitr_lymq_28085.dmp
EXPDP> Job "SYS"."TSPITR_EXP_lymq_wnsr" successfully completed at Tue Feb 22 10:12:25 2022 elapsed 0 00:00:24
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_lymq_oBio" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_lymq_oBio":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "cnx"."MYTABLERESTORE2" 1.135 MB 50000 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Job "SYS"."TSPITR_IMP_lymq_oBio" successfully completed at Tue Feb 22 10:12:34 2022 elapsed 0 00:00:05
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_temp_k19flb0s_.tmp deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_temp_k19fl8op_.tmp deleted
auxiliary instance file /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_20_k19fpp5q_.log deleted
auxiliary instance file /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_19_k19fpp5r_.log deleted
auxiliary instance file /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_18_k19fpp5r_.log deleted
auxiliary instance file /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_17_k19fpp4y_.log deleted
auxiliary instance file /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_16_k19fonkl_.log deleted
auxiliary instance file /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_15_k19fonkl_.log deleted
auxiliary instance file /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_14_k19fonkh_.log deleted
auxiliary instance file /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_13_k19fonkl_.log deleted
auxiliary instance file /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_12_k19fonkh_.log deleted
auxiliary instance file /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_11_k19fonkl_.log deleted
auxiliary instance file /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_10_k19fonkj_.log deleted
auxiliary instance file /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_9_k19fonjs_.log deleted
auxiliary instance file /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_8_k19fnn4z_.log deleted
auxiliary instance file /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_7_k19fnn2q_.log deleted
auxiliary instance file /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_6_k19fnn0t_.log deleted
auxiliary instance file /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_5_k19fnmxr_.log deleted
auxiliary instance file /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_4_k19fnmw3_.log deleted
auxiliary instance file /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_3_k19fnmtz_.log deleted
auxiliary instance file /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_2_k19fnmtl_.log deleted
auxiliary instance file /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/onlinelog/o1_mf_1_k19fnmt6_.log deleted
auxiliary instance file /opt/backup/recover/oracle/LYMQ_PITR_PDBRHEL8_CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_elbo_k19fn5kg_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_sysaux_k19fj5r8_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_sysaux_k19fj5pr_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_undotbs1_k19fk7dl_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_undotbs1_k19fk6l8_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_undotbs1_k19fj5q7_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/datafile/o1_mf_system_k19fjy79_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/datafile/o1_mf_system_k19fjots_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/datafile/o1_mf_system_k19fj5qs_.dbf deleted
auxiliary instance file /opt/backup/recover/oracle/CDBRHEL8/controlfile/o1_mf_k19fhy9q_.ctl deleted
auxiliary instance file tspitr_lymq_28085.dmp deleted
Finished recover at 2022-02-22 10:12:37
PITR of table with auxiliary destination set to ASM Disk group
The AUXILIARY DESTINATION could be set to ASM disk group as well. In below example the FRA diskgroup is used for AUXILIARY DESTINATION. In this case the dump files will also be created in ASM. The command is
RUN
{
SET AUXILIARY INSTANCE PARAMETER FILE TO '/home/oracle/aux.ora';
RECOVER TABLE cnx.mytablerestore OF PLUGGABLE DATABASE pdbrhel8
UNTIL TIME "to_date('2022-02-23 11:47:20','YYYY-MM-DD HH24:MI:SS')"
AUXILIARY DESTINATION '+FRA'
REMAP TABLE cnx.mytablerestore:asanga.mytablerestore2;
}Full outputRMAN> RUN
{
2> SET AUXILIARY INSTANCE PARAMETER FILE TO '/home/oracle/aux.ora';
3> RECOVER TABLE cnx.mytablerestore OF PLUGGABLE DATABASE pdbrhel8
UNTIL TIME "to_date('2022-02-23 11:47:20','YYYY-MM-DD HH24:MI:SS')"
4> AUXILIARY DESTINATION '+FRA'
5> 6> 7> REMAP TABLE cnx.mytablerestore:asanga.mytablerestore2;
}8>
executing command: SET auxiliary parameter file
using target database control file instead of recovery catalog
Starting recover at 2022-02-23 15:20:25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=355 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=9 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=150 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=249 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 PDB$SEED:SYSTEM
Tablespace PDBRHEL8:SYSTEM
Tablespace UNDOTBS1
Tablespace PDB$SEED:UNDOTBS1
Tablespace PDBRHEL8:UNDOTBS1
Creating automatic instance, with SID='bDmC'
using contents of file /home/oracle/aux.ora
initialization parameters used for automatic instance:
db_name=CDBRHEL8
db_unique_name=bDmC_pitr_pdbrhel8_CDBRHEL8
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/opt/company/app/oracle
_system_trig_enabled=FALSE
sga_target=4096M
processes=200
db_create_file_dest=+FRA
log_archive_dest_1='location=+FRA'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
wallet_root=/opt/company/app/oracle/wallet
tde_configuration='KEYSTORE_CONFIGURATION=FILE'
ifile=/home/oracle/aux.ora
starting up automatic instance CDBRHEL8
Oracle instance started
Total System Global Area 2147482136 bytes
Fixed Size 9136664 bytes
Variable Size 486539264 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7639040 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-23 11:47:20','YYYY-MM-DD 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 2022-02-23 15:20:37
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=2 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=93 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=173 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=11 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 /opt/backup/2022-02-23/arc_c-582064218-20220223-01.ctl
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-23/arc_c-582064218-20220223-01.ctl tag=TAG20220223T114012
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+FRA/CDBRHEL8/CONTROLFILE/current.278.1097421641
Finished restore at 2022-02-23 15:20:41
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('2022-02-23 11:47:20','YYYY-MM-DD 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 2 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
set newname for clone datafile 11 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 10 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, 2, 9, 5, 6, 11, 3, 10;
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 +FRA in control file
renamed tempfile 3 to +FRA in control file
Starting restore at 2022-02-23 15:20:46
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
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 00005 to +FRA
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1b0mi7qb_43_1_1
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00003 to +FRA
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1a0mi7qb_42_1_1
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00010 to +FRA
channel ORA_AUX_DISK_3: reading from backup piece /opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1e0mi7qj_46_1_1
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00006 to +FRA
channel ORA_AUX_DISK_4: reading from backup piece /opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1h0mi7qp_49_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1b0mi7qb_43_1_1 tag=FULL_BKP_2022_02_23
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:13
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 00011 to +FRA
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1i0mi7r0_50_1_1
channel ORA_AUX_DISK_4: piece handle=/opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1h0mi7qp_49_1_1 tag=FULL_BKP_2022_02_23
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:20
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00001 to +FRA
channel ORA_AUX_DISK_4: reading from backup piece /opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1c0mi7qb_44_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1i0mi7r0_50_1_1 tag=FULL_BKP_2022_02_23
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:19
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to +FRA
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1f0mi7qj_47_1_1
channel ORA_AUX_DISK_3: piece handle=/opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1e0mi7qj_46_1_1 tag=FULL_BKP_2022_02_23
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:36
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00002 to +FRA
channel ORA_AUX_DISK_3: reading from backup piece /opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1g0mi7ql_48_1_1
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1a0mi7qb_42_1_1 tag=FULL_BKP_2022_02_23
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:36
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1f0mi7qj_47_1_1 tag=FULL_BKP_2022_02_23
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_3: piece handle=/opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1g0mi7ql_48_1_1 tag=FULL_BKP_2022_02_23
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_4: piece handle=/opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1c0mi7qb_44_1_1 tag=FULL_BKP_2022_02_23
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:29
Finished restore at 2022-02-23 15:21:38
datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=1097421699 file name=+FRA/CDBRHEL8/DATAFILE/system.290.1097421671
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=1097421699 file name=+FRA/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/DATAFILE/system.288.1097421683
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1097421699 file name=+FRA/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/DATAFILE/system.289.1097421683
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=1097421699 file name=+FRA/CDBRHEL8/DATAFILE/undotbs1.284.1097421647
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=1097421699 file name=+FRA/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/DATAFILE/undotbs1.281.1097421647
datafile 11 switched to datafile copy
input datafile copy RECID=14 STAMP=1097421699 file name=+FRA/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/DATAFILE/undotbs1.280.1097421663
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=1097421699 file name=+FRA/CDBRHEL8/DATAFILE/sysaux.283.1097421647
datafile 10 switched to datafile copy
input datafile copy RECID=16 STAMP=1097421699 file name=+FRA/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/DATAFILE/sysaux.282.1097421647
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-23 11:47:20','YYYY-MM-DD HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone 'PDB$SEED' "alter database datafile
2 online";
sql clone 'PDBRHEL8' "alter database datafile
9 online";
sql clone "alter database datafile 5 online";
sql clone 'PDB$SEED' "alter database datafile
6 online";
sql clone 'PDBRHEL8' "alter database datafile
11 online";
sql clone "alter database datafile 3 online";
sql clone 'PDBRHEL8' "alter database datafile
10 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "PDB$SEED":"SYSTEM", "PDBRHEL8":"SYSTEM", "UNDOTBS1", "PDB$SEED":"UNDOTBS1", "PDBRHEL8":"UNDOTBS1", "SYSAUX", "PDBRHEL8":"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 2 online
sql statement: alter database datafile 9 online
sql statement: alter database datafile 5 online
sql statement: alter database datafile 6 online
sql statement: alter database datafile 11 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 10 online
Starting recover at 2022-02-23 15:21:41
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
starting media recovery
archived log for thread 1 with sequence 33 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_23/thread_1_seq_33.285.1097409007
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=31
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-23/full_arc_CDBRHEL8_20220223_230mi7r7_67_1_1
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=32
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-23/archive_CDBRHEL8_20220223_250mi7sr_69_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-23/full_arc_CDBRHEL8_20220223_230mi7r7_67_1_1 tag=FULL_ARC_BKP_2022_02_23
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:00
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_23/thread_1_seq_31.291.1097421707 thread=1 sequence=31
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-23/archive_CDBRHEL8_20220223_250mi7sr_69_1_1 tag=ARCHIVE_LOG_2022_02_23_11_40
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_23/thread_1_seq_32.292.1097421707 thread=1 sequence=32
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_23/thread_1_seq_33.285.1097409007 thread=1 sequence=33
media recovery complete, elapsed time: 00:00:01
Finished recover at 2022-02-23 15:21:49
sql statement: alter database open read only
contents of Memory Script:
{
sql clone 'alter pluggable database PDBRHEL8 open read only';
}
executing Memory Script
sql statement: alter pluggable database PDBRHEL8 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 =
''+FRA/CDBRHEL8/CONTROLFILE/current.278.1097421641'' 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 2147482136 bytes
Fixed Size 9136664 bytes
Variable Size 486539264 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7639040 bytes
sql statement: alter system set control_files = ''+FRA/CDBRHEL8/CONTROLFILE/current.278.1097421641'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2147482136 bytes
Fixed Size 9136664 bytes
Variable Size 486539264 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7639040 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-23 11:47:20','YYYY-MM-DD HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 14 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 14;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 2022-02-23 15:22:53
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=94 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=175 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: 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 00014 to +FRA
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1l0mi7r4_53_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1l0mi7r4_53_1_1 tag=FULL_BKP_2022_02_23
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2022-02-23 15:22:56
datafile 14 switched to datafile copy
input datafile copy RECID=18 STAMP=1097421776 file name=+FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/DATAFILE/ELBOX.295.1097421775
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-23 11:47:20','YYYY-MM-DD HH24:MI:SS')";
# online the datafiles restored or switched
sql clone 'PDBRHEL8' "alter database datafile
14 online";
# recover and open resetlogs
recover clone database tablespace "PDBRHEL8":"ELBOX", "SYSTEM", "PDB$SEED":"SYSTEM", "PDBRHEL8":"SYSTEM", "UNDOTBS1", "PDB$SEED":"UNDOTBS1", "PDBRHEL8":"UNDOTBS1", "SYSAUX", "PDBRHEL8":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 14 online
Starting recover at 2022-02-23 15:22:57
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
starting media recovery
archived log for thread 1 with sequence 33 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_23/thread_1_seq_33.285.1097409007
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=31
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-23/full_arc_CDBRHEL8_20220223_230mi7r7_67_1_1
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=32
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-23/archive_CDBRHEL8_20220223_250mi7sr_69_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-23/full_arc_CDBRHEL8_20220223_230mi7r7_67_1_1 tag=FULL_ARC_BKP_2022_02_23
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:00
archived log file name=+FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ARCHIVELOG/2022_02_23/thread_1_seq_31.296.1097421785 thread=1 sequence=31
channel clone_default: deleting archived log(s)
archived log file name=+FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ARCHIVELOG/2022_02_23/thread_1_seq_31.296.1097421785 RECID=30 STAMP=1097421784
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-23/archive_CDBRHEL8_20220223_250mi7sr_69_1_1 tag=ARCHIVE_LOG_2022_02_23_11_40
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:00
archived log file name=+FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ARCHIVELOG/2022_02_23/thread_1_seq_32.297.1097421785 thread=1 sequence=32
channel clone_default: deleting archived log(s)
archived log file name=+FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ARCHIVELOG/2022_02_23/thread_1_seq_32.297.1097421785 RECID=31 STAMP=1097421784
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_23/thread_1_seq_33.285.1097409007 thread=1 sequence=33
media recovery complete, elapsed time: 00:00:01
Finished recover at 2022-02-23 15:23:05
database opened
contents of Memory Script:
{
sql clone 'alter pluggable database PDBRHEL8 open';
}
executing Memory Script
sql statement: alter pluggable database PDBRHEL8 open
contents of Memory Script:
{
# create directory for datapump import
sql 'PDBRHEL8' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
+FRA''";
# create directory for datapump export
sql clone 'PDBRHEL8' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
+FRA''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''+FRA''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''+FRA''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_bDmC_mssD":
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> . . exported "cnx"."MYTABLERESTORE" 1.135 MB 50000 rows
EXPDP> ORA-39173: Encrypted data has been stored unencrypted in dump file set.
EXPDP> Master table "SYS"."TSPITR_EXP_bDmC_mssD" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_bDmC_mssD is:
EXPDP> +FRA/tspitr_bdmc_36470.dmp
EXPDP> Job "SYS"."TSPITR_EXP_bDmC_mssD" successfully completed at Wed Feb 23 15:25:10 2022 elapsed 0 00:00:27
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_bDmC_eius" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_bDmC_eius":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "ASANGA"."MYTABLERESTORE2" 1.135 MB 50000 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_bDmC_eius" successfully completed at Wed Feb 23 15:25:37 2022 elapsed 0 00:00:21
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file +FRA/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/TEMPFILE/temp.294.1097421711 deleted
auxiliary instance file +FRA/CDBRHEL8/TEMPFILE/temp.293.1097421709 deleted
auxiliary instance file +FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_20.315.1097421861 deleted
auxiliary instance file +FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_19.314.1097421857 deleted
auxiliary instance file +FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_18.313.1097421851 deleted
auxiliary instance file +FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_17.312.1097421841 deleted
auxiliary instance file +FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_16.306.1097421821 deleted
auxiliary instance file +FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_15.308.1097421821 deleted
auxiliary instance file +FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_14.309.1097421821 deleted
auxiliary instance file +FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_13.310.1097421821 deleted
auxiliary instance file +FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_12.311.1097421823 deleted
auxiliary instance file +FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_11.307.1097421821 deleted
auxiliary instance file +FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_10.305.1097421821 deleted
auxiliary instance file +FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_9.304.1097421819 deleted
auxiliary instance file +FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_8.303.1097421787 deleted
auxiliary instance file +FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_7.302.1097421787 deleted
auxiliary instance file +FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_6.301.1097421785 deleted
auxiliary instance file +FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_5.300.1097421785 deleted
auxiliary instance file +FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_4.299.1097421785 deleted
auxiliary instance file +FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_3.298.1097421785 deleted
auxiliary instance file +FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_2.296.1097421787 deleted
auxiliary instance file +FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_1.297.1097421787 deleted
auxiliary instance file +FRA/BDMC_PITR_PDBRHEL8_CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/DATAFILE/ELBOX.295.1097421775 deleted
auxiliary instance file +FRA/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/DATAFILE/sysaux.282.1097421647 deleted
auxiliary instance file +FRA/CDBRHEL8/DATAFILE/sysaux.283.1097421647 deleted
auxiliary instance file +FRA/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/DATAFILE/undotbs1.280.1097421663 deleted
auxiliary instance file +FRA/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/DATAFILE/undotbs1.281.1097421647 deleted
auxiliary instance file +FRA/CDBRHEL8/DATAFILE/undotbs1.284.1097421647 deleted
auxiliary instance file +FRA/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/DATAFILE/system.289.1097421683 deleted
auxiliary instance file +FRA/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/DATAFILE/system.288.1097421683 deleted
auxiliary instance file +FRA/CDBRHEL8/DATAFILE/system.290.1097421671 deleted
auxiliary instance file +FRA/CDBRHEL8/CONTROLFILE/current.278.1097421641 deleted
auxiliary instance file tspitr_bDmC_36470.dmp deleted
Finished recover at 2022-02-23 15:25:39PITR of table into a dumpfile in ASM disk group
Similar to previous example the dumpfile could also be created in ASM. This is done by setting DATAPUMP DESTINATION to ASM disk group. The command for this is
RUN
{
SET AUXILIARY INSTANCE PARAMETER FILE TO '/home/oracle/aux.ora';
RECOVER TABLE cnx.mytablerestore OF PLUGGABLE DATABASE pdbrhel8
UNTIL TIME "to_date('2022-02-23 11:47:20','YYYY-MM-DD HH24:MI:SS')"
AUXILIARY DESTINATION '+FRA'
DATAPUMP DESTINATION '+DATA'
DUMP FILE 'mypitr.dmp'
NOTABLEIMPORT;
}Full output is RMAN> RUN
{
SET AUXILIARY INSTANCE PARAMETER FILE TO '/home/oracle/aux.ora';
2> 3> 4> RECOVER TABLE cnx.mytablerestore OF PLUGGABLE DATABASE pdbrhel8
UNTIL TIME "to_date('2022-02-23 11:47:20','YYYY-MM-DD HH24:MI:SS')"
AUXILIARY DESTINATION '+FRA'
5> 6> 7> DATAPUMP DESTINATION '+DATA'
8> DUMP FILE 'mypitr.dmp'
9> NOTABLEIMPORT;
}
10>
executing command: SET auxiliary parameter file
Starting recover at 2022-02-23 15:29:31
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
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 PDB$SEED:SYSTEM
Tablespace PDBRHEL8:SYSTEM
Tablespace UNDOTBS1
Tablespace PDB$SEED:UNDOTBS1
Tablespace PDBRHEL8:UNDOTBS1
Creating automatic instance, with SID='ykoi'
using contents of file /home/oracle/aux.ora
initialization parameters used for automatic instance:
db_name=CDBRHEL8
db_unique_name=ykoi_pitr_pdbrhel8_CDBRHEL8
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/opt/company/app/oracle
_system_trig_enabled=FALSE
sga_target=4096M
processes=200
db_create_file_dest=+FRA
log_archive_dest_1='location=+FRA'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
wallet_root=/opt/company/app/oracle/wallet
tde_configuration='KEYSTORE_CONFIGURATION=FILE'
ifile=/home/oracle/aux.ora
starting up automatic instance CDBRHEL8
Oracle instance started
Total System Global Area 2147482136 bytes
Fixed Size 9136664 bytes
Variable Size 486539264 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7639040 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-23 11:47:20','YYYY-MM-DD 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 2022-02-23 15:29:42
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=2 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=93 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=173 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=255 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 /opt/backup/2022-02-23/arc_c-582064218-20220223-01.ctl
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-23/arc_c-582064218-20220223-01.ctl tag=TAG20220223T114012
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=+FRA/CDBRHEL8/CONTROLFILE/current.317.1097422185
Finished restore at 2022-02-23 15:29:46
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('2022-02-23 11:47:20','YYYY-MM-DD 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 2 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
set newname for clone datafile 11 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 10 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, 2, 9, 5, 6, 11, 3, 10;
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 +FRA in control file
renamed tempfile 3 to +FRA in control file
Starting restore at 2022-02-23 15:29:51
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
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 00005 to +FRA
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1b0mi7qb_43_1_1
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00003 to +FRA
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1a0mi7qb_42_1_1
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00010 to +FRA
channel ORA_AUX_DISK_3: reading from backup piece /opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1e0mi7qj_46_1_1
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00006 to +FRA
channel ORA_AUX_DISK_4: reading from backup piece /opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1h0mi7qp_49_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1b0mi7qb_43_1_1 tag=FULL_BKP_2022_02_23
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:13
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 00011 to +FRA
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1i0mi7r0_50_1_1
channel ORA_AUX_DISK_4: piece handle=/opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1h0mi7qp_49_1_1 tag=FULL_BKP_2022_02_23
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:18
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00001 to +FRA
channel ORA_AUX_DISK_4: reading from backup piece /opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1c0mi7qb_44_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1i0mi7r0_50_1_1 tag=FULL_BKP_2022_02_23
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:17
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to +FRA
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1f0mi7qj_47_1_1
channel ORA_AUX_DISK_3: piece handle=/opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1e0mi7qj_46_1_1 tag=FULL_BKP_2022_02_23
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00002 to +FRA
channel ORA_AUX_DISK_3: reading from backup piece /opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1g0mi7ql_48_1_1
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1a0mi7qb_42_1_1 tag=FULL_BKP_2022_02_23
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:36
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1f0mi7qj_47_1_1 tag=FULL_BKP_2022_02_23
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:18
channel ORA_AUX_DISK_3: piece handle=/opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1g0mi7ql_48_1_1 tag=FULL_BKP_2022_02_23
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:16
channel ORA_AUX_DISK_4: piece handle=/opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1c0mi7qb_44_1_1 tag=FULL_BKP_2022_02_23
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:32
Finished restore at 2022-02-23 15:30:44
datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=1097422244 file name=+FRA/CDBRHEL8/DATAFILE/system.280.1097422213
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=1097422244 file name=+FRA/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/DATAFILE/system.282.1097422229
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1097422244 file name=+FRA/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/DATAFILE/system.283.1097422227
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=1097422244 file name=+FRA/CDBRHEL8/DATAFILE/undotbs1.290.1097422193
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=1097422244 file name=+FRA/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/DATAFILE/undotbs1.284.1097422193
datafile 11 switched to datafile copy
input datafile copy RECID=14 STAMP=1097422245 file name=+FRA/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/DATAFILE/undotbs1.281.1097422209
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=1097422245 file name=+FRA/CDBRHEL8/DATAFILE/sysaux.288.1097422193
datafile 10 switched to datafile copy
input datafile copy RECID=16 STAMP=1097422245 file name=+FRA/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/DATAFILE/sysaux.289.1097422193
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-23 11:47:20','YYYY-MM-DD HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone 'PDB$SEED' "alter database datafile
2 online";
sql clone 'PDBRHEL8' "alter database datafile
9 online";
sql clone "alter database datafile 5 online";
sql clone 'PDB$SEED' "alter database datafile
6 online";
sql clone 'PDBRHEL8' "alter database datafile
11 online";
sql clone "alter database datafile 3 online";
sql clone 'PDBRHEL8' "alter database datafile
10 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "PDB$SEED":"SYSTEM", "PDBRHEL8":"SYSTEM", "UNDOTBS1", "PDB$SEED":"UNDOTBS1", "PDBRHEL8":"UNDOTBS1", "SYSAUX", "PDBRHEL8":"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 2 online
sql statement: alter database datafile 9 online
sql statement: alter database datafile 5 online
sql statement: alter database datafile 6 online
sql statement: alter database datafile 11 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 10 online
Starting recover at 2022-02-23 15:30:47
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
starting media recovery
archived log for thread 1 with sequence 33 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_23/thread_1_seq_33.285.1097409007
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=31
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-23/full_arc_CDBRHEL8_20220223_230mi7r7_67_1_1
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=32
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-23/archive_CDBRHEL8_20220223_250mi7sr_69_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-23/full_arc_CDBRHEL8_20220223_230mi7r7_67_1_1 tag=FULL_ARC_BKP_2022_02_23
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:00
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_23/thread_1_seq_31.295.1097422253 thread=1 sequence=31
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-23/archive_CDBRHEL8_20220223_250mi7sr_69_1_1 tag=ARCHIVE_LOG_2022_02_23_11_40
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:00
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_23/thread_1_seq_32.297.1097422253 thread=1 sequence=32
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_23/thread_1_seq_33.285.1097409007 thread=1 sequence=33
media recovery complete, elapsed time: 00:00:01
Finished recover at 2022-02-23 15:30:54
sql statement: alter database open read only
contents of Memory Script:
{
sql clone 'alter pluggable database PDBRHEL8 open read only';
}
executing Memory Script
sql statement: alter pluggable database PDBRHEL8 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 =
''+FRA/CDBRHEL8/CONTROLFILE/current.317.1097422185'' 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 2147482136 bytes
Fixed Size 9136664 bytes
Variable Size 486539264 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7639040 bytes
sql statement: alter system set control_files = ''+FRA/CDBRHEL8/CONTROLFILE/current.317.1097422185'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2147482136 bytes
Fixed Size 9136664 bytes
Variable Size 486539264 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7639040 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-23 11:47:20','YYYY-MM-DD HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 14 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 14;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 2022-02-23 15:31:58
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=94 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=175 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: 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 00014 to +FRA
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1l0mi7r4_53_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-23/full_bkp_CDBRHEL8_20220223_1l0mi7r4_53_1_1 tag=FULL_BKP_2022_02_23
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2022-02-23 15:32:01
datafile 14 switched to datafile copy
input datafile copy RECID=18 STAMP=1097422322 file name=+FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/DATAFILE/ELBOX.299.1097422321
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-02-23 11:47:20','YYYY-MM-DD HH24:MI:SS')";
# online the datafiles restored or switched
sql clone 'PDBRHEL8' "alter database datafile
14 online";
# recover and open resetlogs
recover clone database tablespace "PDBRHEL8":"ELBOX", "SYSTEM", "PDB$SEED":"SYSTEM", "PDBRHEL8":"SYSTEM", "UNDOTBS1", "PDB$SEED":"UNDOTBS1", "PDBRHEL8":"UNDOTBS1", "SYSAUX", "PDBRHEL8":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 14 online
Starting recover at 2022-02-23 15:32:02
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
starting media recovery
archived log for thread 1 with sequence 33 is already on disk as file +FRA/CDBRHEL8/ARCHIVELOG/2022_02_23/thread_1_seq_33.285.1097409007
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=31
channel ORA_AUX_DISK_1: reading from backup piece /opt/backup/2022-02-23/full_arc_CDBRHEL8_20220223_230mi7r7_67_1_1
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=32
channel ORA_AUX_DISK_2: reading from backup piece /opt/backup/2022-02-23/archive_CDBRHEL8_20220223_250mi7sr_69_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/backup/2022-02-23/full_arc_CDBRHEL8_20220223_230mi7r7_67_1_1 tag=FULL_ARC_BKP_2022_02_23
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:00
archived log file name=+FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ARCHIVELOG/2022_02_23/thread_1_seq_31.300.1097422329 thread=1 sequence=31
channel clone_default: deleting archived log(s)
archived log file name=+FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ARCHIVELOG/2022_02_23/thread_1_seq_31.300.1097422329 RECID=30 STAMP=1097422329
channel ORA_AUX_DISK_2: piece handle=/opt/backup/2022-02-23/archive_CDBRHEL8_20220223_250mi7sr_69_1_1 tag=ARCHIVE_LOG_2022_02_23_11_40
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:00
archived log file name=+FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ARCHIVELOG/2022_02_23/thread_1_seq_32.301.1097422329 thread=1 sequence=32
channel clone_default: deleting archived log(s)
archived log file name=+FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ARCHIVELOG/2022_02_23/thread_1_seq_32.301.1097422329 RECID=31 STAMP=1097422329
archived log file name=+FRA/CDBRHEL8/ARCHIVELOG/2022_02_23/thread_1_seq_33.285.1097409007 thread=1 sequence=33
media recovery complete, elapsed time: 00:00:01
Finished recover at 2022-02-23 15:32:10
database opened
contents of Memory Script:
{
sql clone 'alter pluggable database PDBRHEL8 open';
}
executing Memory Script
sql statement: alter pluggable database PDBRHEL8 open
contents of Memory Script:
{
# create directory for datapump import
sql 'PDBRHEL8' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
+DATA''";
# create directory for datapump export
sql clone 'PDBRHEL8' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
+DATA''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''+DATA''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''+DATA''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_ykoi_yAmo":
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 "cnx"."MYTABLERESTORE" 1.135 MB 50000 rows
EXPDP> ORA-39173: Encrypted data has been stored unencrypted in dump file set.
EXPDP> Master table "SYS"."TSPITR_EXP_ykoi_yAmo" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_ykoi_yAmo is:
EXPDP> +DATA/mypitr.dmp
EXPDP> Job "SYS"."TSPITR_EXP_ykoi_yAmo" successfully completed at Wed Feb 23 15:34:13 2022 elapsed 0 00:00:26
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
auxiliary instance file +FRA/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/TEMPFILE/temp.298.1097422257 deleted
auxiliary instance file +FRA/CDBRHEL8/TEMPFILE/temp.296.1097422255 deleted
auxiliary instance file +FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_20.319.1097422405 deleted
auxiliary instance file +FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_19.318.1097422405 deleted
auxiliary instance file +FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_18.294.1097422399 deleted
auxiliary instance file +FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_17.293.1097422387 deleted
auxiliary instance file +FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_16.315.1097422367 deleted
auxiliary instance file +FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_15.306.1097422365 deleted
auxiliary instance file +FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_14.312.1097422365 deleted
auxiliary instance file +FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_13.314.1097422367 deleted
auxiliary instance file +FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_12.308.1097422365 deleted
auxiliary instance file +FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_11.313.1097422365 deleted
auxiliary instance file +FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_10.309.1097422365 deleted
auxiliary instance file +FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_9.310.1097422363 deleted
auxiliary instance file +FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_8.311.1097422331 deleted
auxiliary instance file +FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_7.307.1097422331 deleted
auxiliary instance file +FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_6.305.1097422331 deleted
auxiliary instance file +FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_5.304.1097422331 deleted
auxiliary instance file +FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_4.303.1097422331 deleted
auxiliary instance file +FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_3.302.1097422331 deleted
auxiliary instance file +FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_2.300.1097422331 deleted
auxiliary instance file +FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/ONLINELOG/group_1.301.1097422331 deleted
auxiliary instance file +FRA/YKOI_PITR_PDBRHEL8_CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/DATAFILE/ELBOX.299.1097422321 deleted
auxiliary instance file +FRA/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/DATAFILE/sysaux.289.1097422193 deleted
auxiliary instance file +FRA/CDBRHEL8/DATAFILE/sysaux.288.1097422193 deleted
auxiliary instance file +FRA/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/DATAFILE/undotbs1.281.1097422209 deleted
auxiliary instance file +FRA/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/DATAFILE/undotbs1.284.1097422193 deleted
auxiliary instance file +FRA/CDBRHEL8/DATAFILE/undotbs1.290.1097422193 deleted
auxiliary instance file +FRA/CDBRHEL8/D7BFD18D89D54E7DE053AC021FAC8A34/DATAFILE/system.283.1097422227 deleted
auxiliary instance file +FRA/CDBRHEL8/D7BF392E9FAC3698E053AC021FACB5BA/DATAFILE/system.282.1097422229 deleted
auxiliary instance file +FRA/CDBRHEL8/DATAFILE/system.280.1097422213 deleted
auxiliary instance file +FRA/CDBRHEL8/CONTROLFILE/current.317.1097422185 deleted
Finished recover at 2022-02-23 15:34:19
