A! Help: 2022

Tuesday, November 15, 2022

Active Data Guard instances are not supported with DBMS_ROLLING

There is an earlier post on how to use dbms_rolling for database upgrade. The configuration used in that post consists of two instances. The data guard configuration this post is based on had three instances. One of them was is an active data guard instnace, open read only. This instance is to simulate a reader farm (Example 14-9 Rolling Upgrade on a Reader Farm). Below is the database names, their role and current open mode.

DB_UNIQUE_NAME   OPEN_MODE              DATABASE_ROLE
---------------- ----------             -------------
dgtest           READ WRITE             PRIMARY
dgtest2          MOUNTED                PHYSICAL STANDBY
dgtest3          READ ONLY WITH APPLY   PHYSICAL STANDBY

dgtest2 is designated as the future primary. This standby DB, as shown above is already in mount mode. MOS doc 2832235.1 states standby must be in mount mode.

SQL> exec DBMS_ROLLING.INIT_PLAN(future_primary=>'dgtest2');

PL/SQL procedure successfully completed.

However, when the build plan is called it gives the below error.

SQL> exec DBMS_ROLLING.BUILD_PLAN;
BEGIN DBMS_ROLLING.BUILD_PLAN; END;

*
ERROR at line 1:
ORA-45438: database is not in mounted mode
ORA-06512: at "SYS.DBMS_ROLLING", line 16
ORA-06512: at line 1

This is strange as the future primary, the standby that will be invovled in the switchover is already in mount mode but build plan is giving an error.
To see if the error is due to the active data guard instance (dgtest3), that instance was closed and started in mount mode. The build plan executes without error. So it seems all instances must be in mount mode for build plan to work. This means any application that rely on reader farm will incur down time.

After raising a SR Oracle confirmed that this is a bug and gave the following "Bug 25882840 : ACTIVE DATA GUARD NOT PERMITTED AS DBMS_ROLLING MEMBER". Unfortunately this is an internal bug so couldn't get much details on it. There was also no ETA on thie fix for the bug nor confirmation that ADG will be supported in the future.

Related MOS Docs
Oracle Exadata Database Service 19c Rolling Upgrade with DBMS_ROLLING from Oracle Database 12.1.0.2 [ID 2889464.1]
Exadata Cloud Database 19c Rolling Upgrade With DBMS_ROLLING [ID 2832235.1]

Related Posts
Upgrading Oracle Restart Databases From 12.2 to 18c Using DBMS_ROLLING
DG Broker Continue With Status "ROLLING DATABASE MAINTENANCE IN PROGRESS" Even After ROLLBACK_PLAN is Called

Monday, October 10, 2022

Far Sync Targets are not Discovered Correctly by OEM Auto Discovery Process

If there are far sync instances in a data guard configuration then those will not be correctly identified as far sync instances by the OEM target discovery process. There were two posts related to the same issue where far sync types were not listed for template apply. The other issue was related to data guard performance page where far sync instace graphs had error status.
Root cause for all of this is that auto discovery will assign target type of "database instance" for the far sync instance.

After an SR that spaned year and half Oracle support said that currently OEM auto discovery process is unable to correctly detect the far sync instance types. Their solution was to manaully add the far sync instance.

To manually add the far sync go to add target -> add target manually and select far sync type and the host where far sync resides.

Afterwards the far sync instaces has be views via all targets and filtering by far syncs. They will not appear under database targets which is the incorrect behaviour.

The data guard performance graphs for far sync will appear without any error status.

As far sync has been around over 8 years now and for OEM to not correctly identify them was very puzzling to say the least. The issue was first discovered on 13.4 and exists on the 13.5 (latest at the time of this post). After further deliberations Oracle has created an enhancement request (ER-34673700) to address this issue. Whether this ER will be accepted and worked upon is still unknown.

Monday, September 12, 2022

The controlfile sequence# has now reached architectural limit

A test database (an out of support 11.2.0.4) running in noarchive log mode had the following message on the alert log.

*************************************************************************
ATTENTION: The controlfile sequence# has now reached 4294967295 (0xffffffff),
           which is the architectural limit. Further controlfile updates
           are no longer possible. To resume normal database operation
           it is necessary to shutdown abort all instances and perform
           the steps described in Doc ID 20324049.8 at My Oracle Support
           to reset the controlfile sequence# to 1.
*************************************************************************
Errors in file /opt/app/oracle/diag/rdbms/uat/uat/trace/uat_ora_28299.trc  (incident=1295436):
ORA-00600: internal error code, arguments: [kccfhb_3], [4294967295], [], [], [], [], [], [], [], [], [], []
Incident details in: /opt/app/oracle/diag/rdbms/uat/uat/incident/incdir_1295436/uat_ora_28299_i1295436.trc

In this situation database would not mount, only starting with nomount mode is possible.
The MOS doc 20324049.8 mentioned in the message is helpful in resolving the issue.
The workaround option given in it, which is to set the db_unrecoverable_scn_tracking=false did not work. It is mentioned in the MOS that setting this parameter is a proactive step not a reactive. So once the issue has occured this workaround is of no help.

The solution is to backup the controlfile to a trace file and then use it to recreate the controlfile. But for this the database must be (at least) in mount mode. As stated earlier database cannot be mounted at this stage. So opted to create a control file backup of a different database. Then modified that trace file by replacing the database name, online logfile and datafile names and locations with the names and locations from the problem database. Then started the database in nomount mode without setting the db_unrecoverable_scn_tracking parameter (MOS doc states "after installing this fix" without specifying what it is). Ran the create contfolfile script which resulted in new control files being created. Finally recover and open the database (without resetlogs). This resolved the issue and database is able to function as before without incuring any data loss.
The MOS doc mentions ORA-00227 associated with this issue. There were no ORA-00227 messages on the alert log. It was associated only with an ora-6000 [kccfhb_3] which is listed in the MOS doc as one of the symptoms of this issue.

Useful MOS doc
Bug 20324049 - ORA-227 Controlfile Corruption when reaching Maximum Value for Control Seq kccfhcsq [20324049.8]

Wednesday, May 11, 2022

Upgrading Oracle Restart from 19c to 21c on RHEL 7

This post shows the steps for upgrading the grid infrastructure portion of 19c Oracle Restart to 21c.

1. The current Oracle restart configuration is as below.

Resource Name             Type                      Target             State              Host
-------------             ------                    -------            --------           ----------
ora.DATA.dg               ora.diskgroup.type        ONLINE             ONLINE             ip-172-31-10-91
ora.FRA.dg                ora.diskgroup.type        ONLINE             ONLINE             ip-172-31-10-91
ora.LISTENER.lsnr         ora.listener.type         ONLINE             ONLINE             ip-172-31-10-91
ora.asm                   ora.asm.type              ONLINE             ONLINE             ip-172-31-10-91
ora.cssd                  ora.cssd.type             ONLINE             ONLINE             ip-172-31-10-91
ora.diskmon               ora.diskmon.type          OFFLINE            OFFLINE
ora.evmd                  ora.evm.type              ONLINE             ONLINE             ip-172-31-10-91
ora.ons                   ora.ons.type              OFFLINE            OFFLINE
ora.testcdb.db            ora.database.type         ONLINE             ONLINE             ip-172-31-10-91

The software and release version of the HAS are as below.

crsctl query has releaseversion
Oracle High Availability Services release version on the local node is [19.0.0.0.0]

crsctl query has softwareversion
Oracle High Availability Services version on the local node is [19.0.0.0.0]

2. The current patch on 19c GI is 19.12.

$ORACLE_HOME/OPatch/opatch lspatches
32918050;TOMCAT RELEASE UPDATE 19.0.0.0.0 (32918050)
32904851;Database Release Update : 19.12.0.0.210720 (32904851)
32585572;DBWLM RELEASE UPDATE 19.0.0.0.0 (32585572)
32915586;ACFS RELEASE UPDATE 19.12.0.0.0 (32915586)
32916816;OCW RELEASE UPDATE 19.12.0.0.0 (32916816)

3. Make the directory for GI home and unzip the 21c installer to this directory.

mkdir -p /opt/app/oracle/product/21.x.0/grid
unzip LINUX.X64_213000_grid_home.zip -d /opt/app/oracle/product/21.x.0/grid

4. Run the cluster verification tool from 21c GI home with hacfg option. The failure listed below is due to 0 SWAP size on AWS node. This could be ignored.

./runcluvfy.sh stage -pre hacfg

Performing following verification checks ...

  Physical Memory ...PASSED
  Available Physical Memory ...PASSED
  Swap Size ...FAILED (PRVF-7573)
  Free Space: ip-172-31-10-91:/usr,ip-172-31-10-91:/var,ip-172-31-10-91:/etc,ip-172-31-10-91:/sbin,ip-172-31-10-91:/tmp ...PASSED
  User Existence: oracle ...
    Users With Same UID: 501 ...PASSED
  User Existence: oracle ...PASSED
  Group Existence: dba ...PASSED
  Group Existence: oinstall ...PASSED
  Group Membership: oinstall(Primary) ...PASSED
  Group Membership: dba ...PASSED
  Run Level ...PASSED
  Architecture ...PASSED
  OS Kernel Version ...PASSED
  OS Kernel Parameter: semmsl ...PASSED
  OS Kernel Parameter: semmns ...PASSED
  OS Kernel Parameter: semopm ...PASSED
  OS Kernel Parameter: semmni ...PASSED
  OS Kernel Parameter: shmmax ...PASSED
  OS Kernel Parameter: shmmni ...PASSED
  OS Kernel Parameter: shmall ...PASSED
  OS Kernel Parameter: file-max ...PASSED
  OS Kernel Parameter: ip_local_port_range ...PASSED
  OS Kernel Parameter: rmem_default ...PASSED
  OS Kernel Parameter: rmem_max ...PASSED
  OS Kernel Parameter: wmem_default ...PASSED
  OS Kernel Parameter: wmem_max ...PASSED
  OS Kernel Parameter: aio-max-nr ...PASSED
  Package: kmod-20-21 (x86_64) ...PASSED
  Package: kmod-libs-20-21 (x86_64) ...PASSED
  Package: binutils-2.23.52.0.1 ...PASSED
  Package: libgcc-4.8.2 (x86_64) ...PASSED
  Package: libstdc++-4.8.2 (x86_64) ...PASSED
  Package: sysstat-10.1.5 ...PASSED
  Package: ksh ...PASSED
  Package: make-3.82 ...PASSED
  Package: glibc-2.17 (x86_64) ...PASSED
  Package: glibc-devel-2.17 (x86_64) ...PASSED
  Package: libaio-0.3.109 (x86_64) ...PASSED
  Package: nfs-utils-1.2.3-15 ...PASSED
  Package: smartmontools-6.2-4 ...PASSED
  Package: net-tools-2.0-0.17 ...PASSED
  Package: libxcb-1.11 (x86_64) ...PASSED
  Package: libX11-1.6.3 (x86_64) ...PASSED
  Package: libXau-1.0.8 (x86_64) ...PASSED
  Package: libXi-1.7.4 (x86_64) ...PASSED
  Package: libXtst-1.2.2 (x86_64) ...PASSED
  Package: policycoreutils-python-2.5-17 ...PASSED
  Package: policycoreutils-2.5-17 ...PASSED
  Users With Same UID: 0 ...PASSED
  Current Group ID ...PASSED
  Root user consistency ...PASSED

Pre-check for Oracle Restart configuration was unsuccessful.


Failures were encountered during execution of CVU verification request "stage -pre hacfg".

Swap Size ...FAILED
ip-172-31-10-91: PRVF-7573 : Sufficient swap size is not available on node
                 "ip-172-31-10-91" [Required = 15.5115GB (1.6265024E7KB) ;
                 Found = 0.0 bytes]


CVU operation performed:      stage -pre hacfg
Date:                         Apr 25, 2022 12:56:04 PM
CVU home:                     /opt/app/oracle/product/21.x.0/grid
Grid home:                    /opt/app/oracle/product/19.x.0/grid
User:                         oracle
Operating system:             Linux3.10.0-1062.9.1.el7.x86_64

5. ASM will be upgraded as part of the upgrade process. Therefore stop the database before starting the GI upgrade. Upgrade process will prompt to stop the database during gridSetup run as well.

Stop the DB.

srvctl stop database -d testcdb

6. Run the gridsetup with '-applyRU' to apply the latest RU on 21c. One the patch is applied the OUI will appear.

./gridSetup.sh -applyRU /opt/installs/21c/psu_apr22/33859395
Preparing the home to patch...
Applying the patch /opt/installs/21c/psu_apr22/33859395...

Select upgrade GI

The unzip location is the GI home. Only Oracle base could be changed at install time.

During the pre-req check the following two items failed.

First item is related to ASM compatibility.

As shown on the message the asm.compatibility must be 11.2.0.2 or above. This was lower on the system used for this upgrade test. Upgrade the asm.compatibility to above 11.2.0.2.

NAME                           VALUE                          GROUP_NUMBER 
------------------------------ ------------------------------ ------------ 
compatible.asm                 19.0.0.0.0                                1 
compatible.rdbms               19.0.0.0.0                                1 
compatible.asm                 19.0.0.0.0                                2 
compatible.rdbms               19.0.0.0.0                                2

Once the asm.compatibility was updated this pre-req failure went away.
The second failure is related to disk discovery string.

The message states there's a mismatch between values in GnP profile and ASM instance. The asm disk discovery string could be at ASM instance level either with

SQL> alter system set asm_diskstring='/dev/oracleasm/cg*' scope=both;

dsset /dev/oracleasm/cg*

However, this did not resolve this pre-req failure and kept being flagged. Finally decided to ignore this pre-req failure and continue.
Summary page is shown below.

7. When prompted run the rootupgrade.sh.

# /opt/app/oracle/product/21.x.0/grid/rootupgrade.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /opt/app/oracle/product/21.x.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]: y
   Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]: y
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /opt/app/oracle/product/21.x.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /opt/app/oracle/crsdata/ip-172-31-10-91/crsconfig/roothas_2022-04-25_01-50-58PM.log
2022/04/25 13:51:00 CLSRSC-595: Executing upgrade step 1 of 12: 'UpgPrechecks'.
2022/04/25 13:51:04 CLSRSC-595: Executing upgrade step 2 of 12: 'GetOldConfig'.
2022/04/25 13:51:06 CLSRSC-595: Executing upgrade step 3 of 12: 'GenSiteGUIDs'.
2022/04/25 13:51:06 CLSRSC-595: Executing upgrade step 4 of 12: 'SetupOSD'.
2022/04/25 13:51:07 CLSRSC-595: Executing upgrade step 5 of 12: 'PreUpgrade'.
2022/04/25 13:51:35 CLSRSC-595: Executing upgrade step 6 of 12: 'UpgradeAFD'.
2022/04/25 13:51:36 CLSRSC-595: Executing upgrade step 7 of 12: 'UpgradeOLR'.
clscfg: EXISTING configuration version 0 detected.
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
2022/04/25 13:51:42 CLSRSC-595: Executing upgrade step 8 of 12: 'UpgradeOCR'.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node ip-172-31-10-91 successfully pinned.
2022/04/25 13:51:45 CLSRSC-595: Executing upgrade step 9 of 12: 'CreateOHASD'.
2022/04/25 13:51:46 CLSRSC-595: Executing upgrade step 10 of 12: 'ConfigOHASD'.
2022/04/25 13:51:46 CLSRSC-329: Replacing Clusterware entries in file 'oracle-ohasd.service'
2022/04/25 13:52:03 CLSRSC-595: Executing upgrade step 11 of 12: 'UpgradeSIHA'.

ip-172-31-10-91     2022/04/25 13:52:55     /opt/app/oracle/crsdata/ip-172-31-10-91/olr/backup_20220425_135255.olr     498597253

ip-172-31-10-91     2021/08/27 12:17:16     /opt/app/oracle/crsdata/ip-172-31-10-91/olr/backup_20210827_121716.olr     1944883066
2022/04/25 13:52:56 CLSRSC-595: Executing upgrade step 12 of 12: 'InstallACFS'.
2022/04/25 13:53:54 CLSRSC-327: Successfully configured Oracle Restart for a standalone server

8. This would have successfully upgrade GI to 21c.

9. Current release and software version for HAS are as below.

crsctl query has releaseversion
Oracle High Availability Services release version on the local node is [21.0.0.0.0]

crsctl query has softwareversion
Oracle High Availability Services version on the local node is [21.0.0.0.0]

10. Run the cluvfy for post ugprade.

cluvfy stage -post hacfg

Performing following verification checks ...

  Oracle Restart Integrity ...PASSED
  OLR Integrity ...PASSED

Post-check for Oracle Restart configuration was successful.

CVU operation performed:      stage -post hacfg
Date:                         Apr 25, 2022 2:17:30 PM
CVU home:                     /opt/app/oracle/product/21.x.0/grid
Grid home:                    /opt/app/oracle/product/21.x.0/grid
User:                         oracle
Operating system:             Linux3.10.0-1062.9.1.el7.x86_64

Saturday, March 26, 2022

Initiating Switchover on a Physical Standby

It seems the best instance to intiaiate the switchover changes based on whether data guard broker is used or not. Oracle documentation states when performing switchover using SQL, the switchover process is initiated on the primary database.

Initiate the switchover on the primary database, BOSTON, by issuing the following SQL statement

On the data guard broker documentation it doesn't explicitly states where to initiate the switchover. In practice switchover command could be initiated while logged into any instance. However, executing switchover on the primary instance when data gaurd broker is used will output the following in the data guard broker log. The DG configuration is

DGMGRL> show configuration

Configuration - test_dg

  Protection Mode: MaxAvailability
  Members:
  dgtest  - Primary database
    dgtest2 - Physical standby database
    dgtest3 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 9 seconds ago)

Switchover command is issued while connected to the current primary, dgtest.

2022-03-21T10:41:33.122+00:00
Forwarding MON_PROPERTY operation to member dgtest2 for processing
2022-03-21T10:41:42.319+00:00
Initiating a healthcheck...
SWITCHOVER TO dgtest2
Switchover to physical standby database cannot be initiated from the primary database
redirecting connection to switchover target database dgtest2...
...using connect identifier: dgtest2tns
SWITCHOVER TO dgtest2
Notifying Oracle Clusterware to prepare primary database for switchover
2022-03-21T10:41:44.319+00:00
Executing SQL: [ALTER DATABASE SWITCHOVER TO 'dgtest2']
2022-03-21T10:42:02.573+00:00
SQL [ALTER DATABASE SWITCHOVER TO 'dgtest2'] executed successfully
2022-03-21T10:42:03.652+00:00
Switchover successful

From the output it seems that DG broker is creating a remote connection to the standby (new primary) using the TNS entry used when the standby was added to the data guard broker.

On the otherhand if connected to the standby and switchover command is issued no such message appears.

DGMGRL>  show configuration

Configuration - test_dg

  Protection Mode: MaxAvailability
  Members:
  dgtest2 - Primary database
    dgtest  - Physical standby database
    dgtest3 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 5 seconds ago)

New primary is dgtest2 and switchover command is issued from standby, dgtest. The output on DG broker log of the primary (dgtest2) shows the below

2022-03-21T10:50:27.266+00:00
Initiating a healthcheck...
SWITCHOVER TO dgtest
2022-03-21T10:50:28.682+00:00
Notifying Oracle Clusterware to prepare primary database for switchover
Executing SQL: [ALTER DATABASE SWITCHOVER TO 'dgtest']
2022-03-21T10:50:49.451+00:00
SQL [ALTER DATABASE SWITCHOVER TO 'dgtest'] executed successfully

Similary if the switchvoer is issued from another standby that is not the future primary (in this confiugration dgtest3) then again the primary's DG broker log output will have the below output

2022-03-21T11:31:51.242+00:00
Initiating a healthcheck...
SWITCHOVER TO dgtest2
2022-03-21T11:31:52.601+00:00
Notifying Oracle Clusterware to prepare primary database for switchover
Executing SQL: [ALTER DATABASE SWITCHOVER TO 'dgtest2']

Friday, March 18, 2022

ATP Auto Indexing

ATP DB has the ability to automatically create indexes based on the improvment those indexes would bring to the query execution. This post explore the usage of this feature. By default auto indexing is off when an ATP is first created. All of the parameters related to auto indexing could be found out by querying the DBA_AUTO_INDEX_CONFIG.

select PARAMETER_NAME,PARAMETER_VALUE from DBA_AUTO_INDEX_CONFIG;

PARAMETER_NAME                           PARAMETER_VALUE
---------------------------------------- ----------------
AUTO_INDEX_SCHEMA
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_SPACE_BUDGET                  100
AUTO_INDEX_REPORT_RETENTION              373
AUTO_INDEX_RETENTION_FOR_AUTO            373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_MODE                          OFF
AUTO_INDEX_COMPRESSION                   ON

For detail explanation on setting these parameters refer the Oracle documentation.
Auto indexing configuration could be set to read only mode where indexes are created in invisible state or implement mode where indexes are created visible mode and any invisible indexes prevoiusly created are also made visible if they improve the performance. To set the configuration to implement execute the following

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

PL/SQL procedure successfully completed.

select PARAMETER_NAME,PARAMETER_VALUE from DBA_AUTO_INDEX_CONFIG WHERE PARAMETER_NAME='AUTO_INDEX_MODE';
PARAMETER_NAME       PARAMETER_
-------------------- ----------
AUTO_INDEX_MODE      IMPLEMENT

For read only mode do the following

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');

select PARAMETER_NAME,PARAMETER_VALUE from DBA_AUTO_INDEX_CONFIG WHERE PARAMETER_NAME='AUTO_INDEX_MODE';
PARAMETER_NAME       PARAMETER_VALUE
-------------------- --------------------
AUTO_INDEX_MODE      REPORT ONLY

To see auto indexing in use, created a test case with two tables with parent child relationship. The child table did not have any indexes created on it. Ran a select statement which used child table as the driver, multiple times with different values.

create table x (a number primary key, b number);
create table y (c number,d number, foreign key (c) references x(a));
insert into x select level,mod(level,100) from dual connect by level < 100000;
insert into x values (0,0);
insert into y select mod(level,100000),level from dual connect by level < 400000;
commit;

select x.a,y.d from x,y where x.a=y.c and c=500;

The past and current auto index executions could be viewd on DBA_AUTO_INDEX_EXECUTIONS. When an auto indexing job is running the status would be executing.

col EXECUTION_NAME format a30
col ERROR_MESSAGE format a10
select EXECUTION_NAME,to_char(EXECUTION_START,'YYYY-MM-DD HH24:MI') exec_st, to_char(EXECUTION_END,'YYYY-MM-DD HH24:MI') exec_end,ERROR_MESSAGE,STATUS from DBA_AUTO_INDEX_EXECUTIONS order by 2;
EXECUTION_NAME                                               EXEC_ST          EXEC_END         ERROR_MESSAGE        STATUS
------------------------------------------------------------ ---------------- ---------------- -------------------- -----------
SYS_AI_2021-10-26/11:35:25                                   2021-10-26 11:35 2021-10-26 11:35                      COMPLETED
...
EXECUTION_NAME                                               EXEC_ST          EXEC_END         ERROR_MESSAGE        STATUS
------------------------------------------------------------ ---------------- ---------------- -------------------- -----------
SYS_AI_2021-10-26/11:35:25                                   2021-10-26 11:35 2021-10-26 11:35                      COMPLETED
SYS_AI_2021-10-26/12:07:36                                   2021-10-26 12:07                                       EXECUTING
...
EXECUTION_NAME                                               EXEC_ST          EXEC_END         ERROR_MESSAGE        STATUS
------------------------------------------------------------ ---------------- ---------------- -------------------- -----------
SYS_AI_2021-10-26/11:35:25                                   2021-10-26 11:35 2021-10-26 11:35                      COMPLETED
SYS_AI_2021-10-26/12:07:36                                   2021-10-26 12:07 2021-10-26 12:13                      COMPLETED

The auto indexing job could also be viewed on the performance hub.

The result of the auto indexing job could be viewed using the last activity report, either in text

set long 1000000 pagesize 0
select dbms_auto_index.report_last_activity() from dual;

or in html

select dbms_auto_index.report_last_activity(type => 'HTML') from dual;

Below is the output from an HTML report. The summary section shows out of two index candidates one was created as a visible index (when auto indexing mode is set to implement).

Index detail section tells the name of the index and on which table column it was created on.

The verification section shows the justification for creating the index, i.e. performance improvment gained due to the index.

Finally the plan section shows the change of query plan due to the auto index.

Even though the report says only one index was created querying the user_indexes shows that two auto indexes were created. One that was mentioned in the report and another one in invisible mode.

SQL>  select table_name,INDEX_NAME,COMPRESSION,AUTO,CONSTRAINT_INDEX,SECONDARY,VISIBILITY from user_indexes;

TABLE_NAME           INDEX_NAME           COMPRESSION   AUT CON S VISIBILIT
-------------------- -------------------- ------------- --- --- - ---------
X                    SYS_C0027706         DISABLED      NO  YES N VISIBLE
Y                    SYS_AI_614y39b1xnucg ADVANCED LOW  YES NO  N VISIBLE
Y                    SYS_AI_6ztznk7h3fq0b ADVANCED LOW  YES NO  N INVISIBLE

On the otherhand if the auto indexing mode was report only then the index would be created as an invisible index. The report shows no overall performance improvment. It could be interpreted as no performance improvment until index is made visible.

At times in addition to an index the auto indexing job could create SQL baselines as well. These are also mentioned in the report. (below screenshot is from a different set of tables than mentioned above).

The indexes created by auto index job cannot be dropped with a drop statement. To drop these indexes dbms_auto_index PL/SQL package should be used.

select table_name,INDEX_NAME,COMPRESSION,AUTO,CONSTRAINT_INDEX,SECONDARY,VISIBILITY from user_indexes;

TABLE_NAME           INDEX_NAME           COMPRESSION   AUT CON S VISIBILIT
-------------------- -------------------- ------------- --- --- - ---------
X                    SYS_C0027706         DISABLED      NO  YES N VISIBLE
Y                    SYS_AI_614y39b1xnucg ADVANCED LOW  YES NO  N VISIBLE
Y                    SYS_AI_6ztznk7h3fq0b ADVANCED LOW  YES NO  N INVISIBLE

SQL> drop index SYS_AI_6ztznk7h3fq0b;

SQL>  drop index "SYS_AI_6ztznk7h3fq0b";
 drop index "SYS_AI_6ztznk7h3fq0b"
            *
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

exec dbms_auto_index.drop_auto_indexes('ASANGA','"SYS_AI_6ztznk7h3fq0b"',TRUE);

select table_name,INDEX_NAME,COMPRESSION,AUTO,CONSTRAINT_INDEX,SECONDARY,VISIBILITY from user_indexes;

TABLE_NAME           INDEX_NAME           COMPRESSION   AUT CON S VISIBILIT
-------------------- -------------------- ------------- --- --- - ---------
X                    SYS_C0027706         DISABLED      NO  YES N VISIBLE
Y                    SYS_AI_614y39b1xnucg ADVANCED LOW  YES NO  N VISIBLE

The dbms_auto_index package also offeres a method to remove all indexes from a table that isn't used for constraints. This method cannot be used to remove auto indexes.

SQL>  exec dbms_auto_index.drop_secondary_indexes('ASANGA','Y');
BEGIN dbms_auto_index.drop_secondary_indexes('ASANGA','Y'); END;

*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes
ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 456
ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 456
ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 452
ORA-06512: at line 1

Also any invisible indexes created by the auto index job (say during report only mode) cannot be made visible. Only way to make them visible is to change the auto indexing mode to implement.

SQL> select PARAMETER_NAME,PARAMETER_VALUE from DBA_AUTO_INDEX_CONFIG WHERE PARAMETER_NAME='AUTO_INDEX_MODE';
PARAMETER_NAME       PARAMETER_VALUE
-------------------- --------------------
AUTO_INDEX_MODE      REPORT ONLY

select table_name,INDEX_NAME,COMPRESSION,AUTO,CONSTRAINT_INDEX,SECONDARY,VISIBILITY from user_indexes;

TABLE_NAME           INDEX_NAME           COMPRESSION   AUT CON S VISIBILIT
-------------------- -------------------- ------------- --- --- - ---------
X                    SYS_C0027706         DISABLED      NO  YES N VISIBLE
Y                    SYS_AI_614y39b1xnucg ADVANCED LOW  YES NO  N INVISIBLE
Y                    SYS_AI_6ztznk7h3fq0b ADVANCED LOW  YES NO  N INVISIBLE

SQL> alter index "SYS_AI_614y39b1xnucg" visible;
alter index "SYS_AI_614y39b1xnucg" visible
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

Finally as mentioned earlier drop_secondary_indexes method allows dropping all indexes in a table that isn't used for enforcing constraints. This is useful if all indexes (not used for constriant enforcement) are to be dropped and let auto indexing take care of creating indexes. Below show case the use of this method. On the tables below X is the parent table and Y is the child table. The AIDX index is on the foreign key column and AIDX2 is an index on another column not used for enforcing any constraints.

col index_name format a30
col table_name format a30
set line 1000
select table_name,INDEX_NAME,COMPRESSION,AUTO,CONSTRAINT_INDEX,SECONDARY,VISIBILITY from user_indexes;

TABLE_NAME           INDEX_NAME           COMPRESSION   AUT CON S VISIBILIT
-------------------- -------------------- ------------- --- --- - ---------
X                    SYS_C0027706         DISABLED      NO  YES N VISIBLE
Y                    AIDX                 DISABLED      NO  NO  N VISIBLE
Y                    AIDX2                DISABLED      NO  NO  N VISIBLE

Executing drop secondary index meethod on table X will not drop the index created to enforce the primary key (SYS_C0027706).

exec dbms_auto_index.drop_secondary_indexes('ASANGA','X');

PL/SQL procedure successfully completed.

TABLE_NAME INDEX_NAME      COLUMN_NAM
---------- --------------- ----------
X          SYS_C0027706    A
Y          AIDX            C
Y          AIDX2           D

On the child table the index created on the foreign key column is not dropped. However, other index that is not enforcing any constraints is dropped.

exec dbms_auto_index.drop_secondary_indexes('ASANGA','Y');

PL/SQL procedure successfully completed.


TABLE_NAME INDEX_NAME      COLUMN_NAM
---------- --------------- ----------
X          SYS_C0027706    A
Y          AIDX            C

Tuesday, March 1, 2022

Point-in-Time Recovery of Tables

Oracle allows point in time recovery of tables from backup. In high-level the recover table command creates an auxiliary instance (and the PDB) and export the table(s) to a dump file or import into the target database with a different name and if desired into a different schema.
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 used

This 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=2048M

The 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.ora

The 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= TRUE

If 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.ora

Lastly, 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 exist

This 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(*)
----------
    100000

PITR 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:58

PITR 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:57

PITR 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 output

RMAN> 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:39

PITR 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

A! Help

Labels

Tuesday, November 15, 2022

Active Data Guard instances are not supported with DBMS_ROLLING

Monday, October 10, 2022

Far Sync Targets are not Discovered Correctly by OEM Auto Discovery Process

Monday, September 12, 2022

The controlfile sequence# has now reached architectural limit

Wednesday, May 11, 2022

Upgrading Oracle Restart from 19c to 21c on RHEL 7

Saturday, March 26, 2022

Initiating Switchover on a Physical Standby

Friday, March 18, 2022

ATP Auto Indexing

Tuesday, March 1, 2022

Point-in-Time Recovery of Tables

About Me

Downloads

Quick Response

Popular

Blog Archive

Total Pageviews

Followers

Oracle Documentation