Performing RMAN Tablespace Point-in-Time Recovery (TSPITR)

In this article we will practive Performing RMAN Tablespace Point-in-Time Recovery (TSPITR) .

Recovery Manager (RMAN) TSPITR enables quick recovery of one or more tablespaces in a database to an earlier time without affecting the rest of the tablespaces and objects in the database.

WHY RMAN TSPITR
=================

RMAN TSPITR is most useful for the following situations:

To recover a logical database to a point different from the rest of the physical database, when multiple logical databases exist in separate tablespaces of one physical database.
To recover data lost after data definition language (DDL) operations that change the structure of tables.
You cannot use Flashback Table to rewind a table to before the point of a structural change such as a truncate table operation.
To recover a table after it has been dropped with the PURGE option.
To recover from the logical corruption of a table.
To recover dropped tablespaces. In fact, RMAN can perform TSPITR on dropped tablespaces even when a recovery catalog is not used.

Terminology
=========

Target instance  -> A database on which ,the tablespace will  be recovered.

Target time  -> Point in time or SCN of the tablespace after TSPITR completes 

Recovery set  -> List Data files in the tablespaces that you intend to recover & This is NOT as part for Auxiliary Set.

Auxiliary database  -> A Teporary database  that used in the recovery process to perform the work of recovery. 

Auxiliary destination  -> An optional disk location that RMAN uses to temporarily store the auxiliary set files. The auxiliary destination is used only with an RMAN-managed auxiliary database. 

Auxiliary set  -> The temporary database that contains  SYSTEM and SYSAUX tablespaces ,UNDO Datafile from the target database instance.
                          Temporary tablespaces,Control file from source database.
                          Archived redo logs that must be restored to recover the auxiliary database to specified point in time.
                          Online redo logs of the auxiliary database. These logs are different from the online redo logs of the source database.
                          They are created when the auxiliary database is opened with the RESETLOGS option.
                          The auxiliary set does not include the parameter file, password file, or associated network files.


 Modes of RMAN TSPITR
=====================
You start RMAN TSPITR with the RMAN RECOVER TABLESPACE command. There are three ways to run the utility:

1=> Fully Automated (the default)

In this mode, RMAN manages the entire TSPITR process including the auxiliary database.
You specify the tablespaces of the recovery set, an auxiliary destination, the target time, and you allow RMAN to manage all other aspects of TSPITR.

2=> Automated: RMAN-Managed Auxiliary Database with User Settings

You can override some defaults of RMAN TSPITR while still using an RMAN-managed auxiliary database and destination.
This variation of the default mode enables you to benefit from some built-in management that RMAN TSITR provides while being able to specify:

Location of auxiliary set or recovery set files
Initialization parameters

3=> Non-Automated: TSPITR and User-Managed Auxiliary Database

This mode of RMAN TSPITR requires you to set up and manage all aspects of the auxiliary database and some aspects of the TSPITR process.
This mode may be appropriate if, for example, you must allocate a different number of channels or change the channel parameters for your user-managed auxiliary database.

Note
If you run TSPITR on a tablespace and bring the tablespace online at time t, then backups of the tablespace created before time t are no longer usable for recovery with a current control file.
You cannot use the current control file to recover the database to any time less than or equal to t.

After you perform TSPITR on a tablespace, you can no longer use previous backups of that tablespace after TSPITR successfully completes.
If you use the recovered tablespaces without taking a backup, then you run your database without a usable backup of these tablespaces.

Prerequisites
========

Identify and Resolve Dependencies on the Primary Database

RMAN TSPITR requires that the tablespace that is being recovered be self-contained and that no SYS-owned objects reside in the tablespace.

To identify and resolve dependencies:

Use the DBMS_TTS.TRANSPORT_SET_CHECK procedure to locate objects outside the tablespace and identify relationships between objects that span the recovery set boundaries.

If the TRANSPORT_SET_VIOLATIONS view returns rows, you must investigate and correct the problem according to the choices described in "Determining the Recovery Set".

This example illustrates how to use the DBMS_TTS.TRANSPORT_SET_CHECK procedure for an initial recovery set consisting of tablespaces tools and users.
It queries the transportable tablespace violations table to manage any dependencies. No rows are returned from this query when all dependencies are managed.

BEGIN
   DBMS_TTS.TRANSPORT_SET_CHECK('USERS', TRUE,TRUE);
END;
/
SELECT * FROM  TRANSPORT_SET_VIOLATIONS;


A=> Performing Fully Automated RMAN TSPITR 

In the default mode, RMAN bases as much of the configuration for TSPITR as possible on the target database.

During TSPITR, the recovery set data files are written in their current locations on the target database
Auxiliary set data files and other auxiliary database files, however, are stored in the auxiliary destination.

Use the AUXILIARY DESTINATION parameter to set a location for RMAN to use for the auxiliary set data files.
The auxiliary destination must be a location on disk with enough space to hold auxiliary set data files.

DEMONSTRATION 
===============

Step 1=> Create Dummy tablespace and table as below.

[oracle@DBRESTORENEW TSPITR]$  sqlplus ABHI_TEST/mPt2i#PH

SQL>  create tablespace RMAN_TSPITR datafile '+DATA' size 1g;

Tablespace created.

SQL>
SQL> create table TAB_TSPITR (id int,detail varchar2(10)) tablespace RMAN_TSPITR;

Table created.

SQL> insert into TAB_TSPITR values (1,'Before1');

1 row created.

SQL> insert into TAB_TSPITR values (2,'Before2');

1 row created.

SQL>  insert into TAB_TSPITR values (3,'Before2');

1 row created.

SQL> commit;

Commit complete.

[oracle@DBRESTORENEW TSPITR]$
[oracle@DBRESTORENEW TSPITR]$ sqlplus "/as sysdba"

SQL> alter system switch all logfile;

System altered.

SQL> /

System altered.

SQL> /
/
/

System altered.

SQL>
System altered.

SQL>
System altered.
SQL>
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

Step 2=>Take RMAN Backup of database.

[oracle@DBRESTORENEW BKP]$ rman target /

connected to target database: TSPITR (DBID=3551235132)

RMAN> RUN
{
2> 3> sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
allocate channel CH01 type disk;
allocate channel CH02 type disk;
allocate channel CH03 type disk;
4> 5> 6> 7> allocate channel CH04 type disk;
BACKUP as compressed backupset database FORMAT '/backup/TSPITR/BKP/DB_FULL_backup_%U' plus archivelog  FORMAT '/backup/TSPITR/BKP/%d_%T_%s_%p_ARCHIVE' ;
backup format '/backup/TSPITR/BKP/BKP_cf_%d_%U_%t' current controlfile tag='backup_controlfile';
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
release channel CH01;
release channel CH02;
release channel CH03;
release channel CH04;
}

....
channel CH04: starting piece 1 at 19-JAN-23
including current control file in backup set
channel CH02: starting piece 1 at 19-JAN-23
channel CH04: finished piece 1 at 19-JAN-23
piece handle=/backup/TSPITR/BKP/DB_FULL_backup_121ia12q_34_1_1 tag=TAG20230119T043017 comment=NONE
channel CH04: backup set complete, elapsed time: 00:00:01
channel CH02: finished piece 1 at 19-JAN-23
piece handle=/backup/TSPITR/BKP/DB_FULL_backup_111ia12q_33_1_1 tag=TAG20230119T043017 comment=NONE
channel CH02: backup set complete, elapsed time: 00:00:01
channel CH01: finished piece 1 at 19-JAN-23
piece handle=/backup/TSPITR/BKP/DB_FULL_backup_0t1ia12p_29_1_1 tag=TAG20230119T043017 comment=NONE
channel CH01: backup set complete, elapsed time: 00:00:17
channel CH03: finished piece 1 at 19-JAN-23
piece handle=/backup/TSPITR/BKP/DB_FULL_backup_0v1ia12p_31_1_1 tag=TAG20230119T043017 comment=NONE
channel CH03: backup set complete, elapsed time: 00:00:47
Finished backup at 19-JAN-23

Starting backup at 19-JAN-23
current log archived
channel CH01: starting compressed archived log backup set
channel CH01: specifying archived log(s) in backup set
input archived log thread=1 sequence=33 RECID=23 STAMP=1126499465
channel CH01: starting piece 1 at 19-JAN-23
channel CH01: finished piece 1 at 19-JAN-23
piece handle=/backup/TSPITR/BKP/TSPITR_20230119_35_1_ARCHIVE tag=TAG20230119T043105 comment=NONE
channel CH01: backup set complete, elapsed time: 00:00:01
Finished backup at 19-JAN-23

Starting backup at 19-JAN-23
channel CH01: starting full datafile backup set
channel CH01: specifying datafile(s) in backup set
including current control file in backup set
channel CH01: starting piece 1 at 19-JAN-23
channel CH01: finished piece 1 at 19-JAN-23
piece handle=/backup/TSPITR/BKP/BKP_cf_TSPITR_141ia14a_36_1_1_1126499466 tag=BACKUP_CONTROLFILE comment=NONE
channel CH01: backup set complete, elapsed time: 00:00:01
Finished backup at 19-JAN-23

sql statement: ALTER SYSTEM ARCHIVE LOG CURRENT

sql statement: ALTER SYSTEM ARCHIVE LOG CURRENT

released channel: CH01

released channel: CH02

released channel: CH03

released channel: CH04

RMAN> exit

Recovery Manager complete.

[oracle@DBRESTORENEW BKP]$ ls -lrt
total 667188
-rw-r-----. 1 oracle asmadmin    770048 Jan 19 04:30 TSPITR_20230119_27_1_ARCHIVE
-rw-r-----. 1 oracle asmadmin    138240 Jan 19 04:30 TSPITR_20230119_28_1_ARCHIVE
-rw-r-----. 1 oracle asmadmin  41974272 Jan 19 04:30 TSPITR_20230119_26_1_ARCHIVE
-rw-r-----. 1 oracle asmadmin  50322432 Jan 19 04:30 TSPITR_20230119_25_1_ARCHIVE
-rw-r-----. 1 oracle asmadmin  92200448 Jan 19 04:30 TSPITR_20230119_24_1_ARCHIVE
-rw-r-----. 1 oracle asmadmin   1499136 Jan 19 04:30 DB_FULL_backup_0u1ia12p_30_1_1
-rw-r-----. 1 oracle asmadmin   1253376 Jan 19 04:30 DB_FULL_backup_101ia12p_32_1_1
-rw-r-----. 1 oracle asmadmin    114688 Jan 19 04:30 DB_FULL_backup_121ia12q_34_1_1
-rw-r-----. 1 oracle asmadmin   1114112 Jan 19 04:30 DB_FULL_backup_111ia12q_33_1_1
-rw-r-----. 1 oracle asmadmin 127565824 Jan 19 04:30 DB_FULL_backup_0t1ia12p_29_1_1
-rw-r-----. 1 oracle asmadmin 353746944 Jan 19 04:30 DB_FULL_backup_0v1ia12p_31_1_1
-rw-r-----. 1 oracle asmadmin   1814016 Jan 19 04:31 TSPITR_20230119_35_1_ARCHIVE
-rw-r-----. 1 oracle asmadmin  10682368 Jan 19 04:31 BKP_cf_TSPITR_141ia14a_36_1_1_1126499466
[oracle@DBRESTORENEW BKP]$

Step 3=> Drop tablespace and note timing (this is only for DEMO)

[oracle@DBRESTORENEW BKP]$  sqlplus ABHI_TEST/mPt2i#PH

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL>  drop tablespace RMAN_TSPITR including contents;

Tablespace dropped.

SQL>
SQL>
SQL> SELECT * FROM TAB_TSPITR;
SELECT * FROM TAB_TSPITR
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> EXIT
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0


Step 4=> Let we recover tablespace as below

[oracle@DBRESTORENEW TSPITR]$ rman target /

connected to target database: TSPITR (DBID=3551235132)

RMAN> run
{
recover tablespace RMAN_TSPITR
until time "to_date('19-JAN-2023 04:32:00','DD-MON-YYYY HH24:MI:SS')"
AUXILIARY DESTINATION '/backup/TSPITR/REST_RECO';
}2> 3> 4> 5> 6>

Starting recover at 19-JAN-23
using target database control file instead of recovery catalog
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1589 device type=DISK
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='otmc'                             <<< Create Dummy Instance

initialization parameters used for automatic instance:
db_name=TSPITR
db_unique_name=otmc_pitr_TSPITR
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u02/app/oracle/oracle_base
_system_trig_enabled=FALSE
sga_target=14400M
processes=200
db_create_file_dest=/backup/TSPITR/REST_RECO
log_archive_dest_1='location=/backup/TSPITR/REST_RECO'
#No auxiliary parameter file used

starting up automatic instance TSPITR                                   <<< Start Dummy Instance

Oracle instance started

Total System Global Area   15099491520 bytes

Fixed Size                    18351296 bytes
Variable Size               2046820352 bytes
Database Buffers           13019119616 bytes
Redo Buffers                  15200256 bytes
Automatic instance created

List of tablespaces that have been dropped from the target database:    <<< Identify Tablespaces
Tablespace RMAN_TSPITR

contents of Memory Script:                                               <<< Perform Controlfile PITR
{
# set requested point in time
set until  time "to_date('19-JAN-2023 04:32:00','DD-MON-YYYY HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;

# mount the controlfile
sql clone 'alter database mount clone database';

# archive current online log
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 19-JAN-23
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=129 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 /backup/TSPITR/BKP/BKP_cf_TSPITR_141ia14a_36_1_1_1126499466
channel ORA_AUX_DISK_1: piece handle=/backup/TSPITR/BKP/BKP_cf_TSPITR_141ia14a_36_1_1_1126499466 tag=BACKUP_CONTROLFILE
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/backup/TSPITR/REST_RECO/TSPITR/controlfile/o1_mf_kwjzjbon_.ctl
Finished restore at 19-JAN-23

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:                                                   <<< Perform Database Restore PITR
{ 
# set requested point in time
set until  time "to_date('19-JAN-2023 04:32:00','DD-MON-YYYY HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  4 to new;
set newname for clone datafile  3 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  8 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 4, 3, 8;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /backup/TSPITR/REST_RECO/TSPITR/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 19-JAN-23
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /backup/TSPITR/REST_RECO/TSPITR/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /backup/TSPITR/BKP/DB_FULL_backup_0u1ia12p_30_1_1
channel ORA_AUX_DISK_1: piece handle=/backup/TSPITR/BKP/DB_FULL_backup_0u1ia12p_30_1_1 tag=TAG20230119T043017
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
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 /backup/TSPITR/REST_RECO/TSPITR/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /backup/TSPITR/BKP/DB_FULL_backup_0t1ia12p_29_1_1
channel ORA_AUX_DISK_1: piece handle=/backup/TSPITR/BKP/DB_FULL_backup_0t1ia12p_29_1_1 tag=TAG20230119T043017
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /backup/TSPITR/REST_RECO/TSPITR/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /backup/TSPITR/BKP/DB_FULL_backup_0v1ia12p_31_1_1
channel ORA_AUX_DISK_1: piece handle=/backup/TSPITR/BKP/DB_FULL_backup_0v1ia12p_31_1_1 tag=TAG20230119T043017
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 19-JAN-23

datafile 8 switched to datafile copy
input datafile copy RECID=5 STAMP=1126500534 file name=+DATA/TSPITR/DATAFILE/rman_tspitr.7693.1126462661
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=1126500534 file name=/backup/TSPITR/REST_RECO/TSPITR/datafile/o1_mf_system_kwjzkkjz_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=1126500534 file name=/backup/TSPITR/REST_RECO/TSPITR/datafile/o1_mf_undotbs1_kwjzjokl_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=1126500534 file name=/backup/TSPITR/REST_RECO/TSPITR/datafile/o1_mf_sysaux_kwjzjrly_.dbf

contents of Memory Script:                                                                <<< Perform Database Recovery
{
# set requested point in time
set until  time "to_date('19-JAN-2023 04:32:00','DD-MON-YYYY HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  4 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  8 online";
# recover and open resetlogs
recover clone database tablespace  "RMAN_TSPITR", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  8 online

Starting recover at 19-JAN-23
using channel ORA_AUX_DISK_1

Executing: alter database datafile 2, 5, 7 offline
starting media recovery

archived log for thread 1 with sequence 33 is already on disk as file +DATA/TSPITR/ARCHIVELOG/2023_01_19/thread_1_seq_33.8432.1126461665
archived log for thread 1 with sequence 34 is already on disk as file +DATA/TSPITR/ARCHIVELOG/2023_01_19/thread_1_seq_34.8436.1126461669
archived log for thread 1 with sequence 35 is already on disk as file +DATA/TSPITR/ARCHIVELOG/2023_01_19/thread_1_seq_35.7669.1126461671
archived log for thread 1 with sequence 36 is already on disk as file +DATA/TSPITR/ARCHIVELOG/2023_01_19/thread_1_seq_36.7677.1126462635
archived log file name=+DATA/TSPITR/ARCHIVELOG/2023_01_19/thread_1_seq_33.8432.1126461665 thread=1 sequence=33
archived log file name=+DATA/TSPITR/ARCHIVELOG/2023_01_19/thread_1_seq_34.8436.1126461669 thread=1 sequence=34
archived log file name=+DATA/TSPITR/ARCHIVELOG/2023_01_19/thread_1_seq_35.7669.1126461671 thread=1 sequence=35
archived log file name=+DATA/TSPITR/ARCHIVELOG/2023_01_19/thread_1_seq_36.7677.1126462635 thread=1 sequence=36
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-JAN-23

database opened                                                                       <<< Open Dummy Database

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  RMAN_TSPITR read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/backup/TSPITR/REST_RECO''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/backup/TSPITR/REST_RECO''";
}
executing Memory Script

sql statement: alter tablespace  RMAN_TSPITR read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/backup/TSPITR/REST_RECO''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/backup/TSPITR/REST_RECO''

Performing export of metadata...                                                       <<< Export Tablespace
   EXPDP> Starting "SYS"."TSPITR_EXP_otmc_tmuo":
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Master table "SYS"."TSPITR_EXP_otmc_tmuo" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_otmc_tmuo is:
   EXPDP>   /backup/TSPITR/REST_RECO/tspitr_otmc_37615.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace RMAN_TSPITR:
   EXPDP>   +DATA/TSPITR/DATAFILE/rman_tspitr.7693.1126462661
   EXPDP> Job "SYS"."TSPITR_EXP_otmc_tmuo" successfully completed at Thu Jan 19 04:49:34 2023 elapsed 0 00:00:29
Export completed

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

Oracle instance shut down

Performing import of metadata...                                                                <<< Import Tablespace
   IMPDP> Master table "SYS"."TSPITR_IMP_otmc_afAb" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_otmc_afAb":
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_otmc_afAb" successfully completed at Thu Jan 19 04:49:44 2023 elapsed 0 00:00:06
Import completed

contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  RMAN_TSPITR read write';
sql 'alter tablespace  RMAN_TSPITR offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script

sql statement: alter tablespace  RMAN_TSPITR read write

sql statement: alter tablespace  RMAN_TSPITR offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance
Automatic instance removed
auxiliary instance file /backup/TSPITR/REST_RECO/TSPITR/datafile/o1_mf_temp_kwjzm5v6_.tmp deleted
auxiliary instance file /backup/TSPITR/REST_RECO/TSPITR/onlinelog/o1_mf_3_kwjzm1jz_.log deleted
auxiliary instance file /backup/TSPITR/REST_RECO/TSPITR/onlinelog/o1_mf_2_kwjzm1j6_.log deleted
auxiliary instance file /backup/TSPITR/REST_RECO/TSPITR/onlinelog/o1_mf_1_kwjzm1gr_.log deleted
auxiliary instance file /backup/TSPITR/REST_RECO/TSPITR/datafile/o1_mf_sysaux_kwjzjrly_.dbf deleted
auxiliary instance file /backup/TSPITR/REST_RECO/TSPITR/datafile/o1_mf_undotbs1_kwjzjokl_.dbf deleted
auxiliary instance file /backup/TSPITR/REST_RECO/TSPITR/datafile/o1_mf_system_kwjzkkjz_.dbf deleted
auxiliary instance file /backup/TSPITR/REST_RECO/TSPITR/controlfile/o1_mf_kwjzjbon_.ctl deleted
auxiliary instance file tspitr_otmc_37615.dmp deleted
Finished recover at 19-JAN-23

RMAN> exit

Recovery Manager complete.


Step 5=>Make Tablespace ONLINE.
[oracle@DBRESTORENEW TSPITR]$ sqlplus "/as sysdba"

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL> alter tablespace RMAN_TSPITR ONLINE;

Tablespace altered.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

Step 6=>Check table data as below

[oracle@DBRESTORENEW TSPITR]$  sqlplus ABHI_TEST/mPt2i#PH

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 19 04:52:05 2023
Version 19.16.0.0.0

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

Last Successful login time: Thu Jan 19 2023 04:50:48 +05:30

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL>  SELECT * FROM TAB_TSPITR;

        ID DETAIL
---------- ----------
         1 Before1
         2 Before2
         3 Before2

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@DBRESTORENEW TSPITR]$

Hence this practice complete . Cheers !!!!

B=> Performing Automated RMAN TSPITR 

In below example i will be changing Path of datafile creation on Target System . Same could be done using SET NEWNAME command as below

DEMONSTRATION 
===============

=> Create Dummy tables

[oracle@NEW_TESTRESTO ~]$  sqlplus ABHI_TEST/mPt2i#PH

SQL> create tablespace TSPITR_RMAN datafile '+DATA' size 500m;

Tablespace created.

SQL> create table rman_tisptr (id int) tablespace TSPITR_RMAN;

Table created.

SQL> insert into rman_tisptr values (1);

1 row created.

SQL>  insert into rman_tisptr values (2);

1 row created.

SQL>  insert into rman_tisptr values (3);

1 row created.

SQL> commit;

Commit complete.

=> Check datafile Path

SQL> select file_name from dba_data_files where tablespace_name='TSPITR_RMAN';

FILE_NAME
--------------------------------------------------------------------------------
+DATA/TSPITR/DATAFILE/tspitr_rman.7693.1126546213

=>Take backup of database

[oracle@NEW_TESTRESTO BKP]$ rman target /

connected to target database: TSPITR (DBID=3551235132)

RMAN>  RUN
2> {
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
allocate channel CH01 type disk;
allocate channel CH02 type disk;
allocate channel CH04 type disk;
BACKUP as compressed backupset database FORMAT '/backup/TSPITR/BKP/DB_FULL_backup_%U' plus archivelog  FO3> RMAT '/backup/TSPITR/BKP/%d_%T_%s_%p_ARCHIVE' ;
backup format '/backup/TSPITR/BKP/BKP_cf_%d_%U_%t' current controlfile tag='backup_controlfile';
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
4> 5> sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
release channel CH01;
release channel CH02;
release channel CH03;
release channel CH04;
}6> 7> 8> 9> 10> 11> 12> 13> 14> 15>

[oracle@NEW_TESTRESTO BKP]$ ls -lrt
total 714540
-rw-r-----. 1 oracle asmadmin  45932544 Jan 20 04:03 TSPITR_20230120_39_1_ARCHIVE
-rw-r-----. 1 oracle asmadmin    787968 Jan 20 04:03 TSPITR_20230120_40_1_ARCHIVE
-rw-r-----. 1 oracle asmadmin  92200448 Jan 20 04:03 TSPITR_20230120_37_1_ARCHIVE
-rw-r-----. 1 oracle asmadmin  91650048 Jan 20 04:03 TSPITR_20230120_38_1_ARCHIVE
-rw-r-----. 1 oracle asmadmin   1400832 Jan 20 04:03 DB_FULL_backup_1b1icjt3_43_1_1
-rw-r-----. 1 oracle asmadmin   1114112 Jan 20 04:03 DB_FULL_backup_1c1icjt6_44_1_1
-rw-r-----. 1 oracle asmadmin    114688 Jan 20 04:03 DB_FULL_backup_1d1icjt8_45_1_1
-rw-r-----. 1 oracle asmadmin 133644288 Jan 20 04:04 DB_FULL_backup_191icjt3_41_1_1
-rw-r-----. 1 oracle asmadmin 354099200 Jan 20 04:04 DB_FULL_backup_1a1icjt3_42_1_1
-rw-r-----. 1 oracle asmadmin     23552 Jan 20 04:04 TSPITR_20230120_46_1_ARCHIVE
-rw-r-----. 1 oracle asmadmin  10715136 Jan 20 04:04 BKP_cf_TSPITR_1f1icjud_47_1_1_1126584269
[oracle@NEW_TESTRESTO BKP]$

=> Insert few Wrong records

[oracle@NEW_TESTRESTO datafile_tspitr]$  sqlplus ABHI_TEST/mPt2i#PH

SQL> select * from rman_tisptr;

        ID
----------
         1
         2
         3

SQL> insert into rman_tisptr values(0);

1 row created.

SQL>  insert into rman_tisptr values(0);

1 row created.

SQL> commit;

Commit complete.

=>Create New destination for datafile as below.

[oracle@NEW_TESTRESTO datafile_tspitr]$ pwd
/backup/TSPITR/datafile_tspitr

=> Restore tablespace using SET NEWNAME command
[oracle@NEW_TESTRESTO REST_RECO]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jan 20 04:12:29 2023
Version 19.16.0.0.0

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

connected to target database: TSPITR (DBID=3551235132)

RMAN> run
{
 SET NEWNAME FOR DATAFILE '+DATA/TSPITR/DATAFILE/tspitr_rman.7693.1126546213'  to '/backup/TSPITR/datafile_tspitr/tspitr_rman.dbf';
recover tablespace TSPITR_RMAN
until time "to_date('20-JAN-2023 04:06:00','DD-MON-YYYY HH24:MI:SS')"
AUXILIARY DESTINATION '/backup/TSPITR/REST_RECO';
}

executing command: SET NEWNAME

Starting recover at 20-JAN-23
....
Finished restore at 20-JAN-23

datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=1126584881 file name=/backup/TSPITR/datafile_tspitr/tspitr_rman.dbf
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=1126584881 file name=/backup/TSPITR/REST_RECO/TSPITR/datafile/o1_mf_system_kwmkxcvt_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=1126584881 file name=/backup/TSPITR/REST_RECO/TSPITR/datafile/o1_mf_undotbs1_kwmkwhmd_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=1126584881 file name=/backup/TSPITR/REST_RECO/TSPITR/datafile/o1_mf_sysaux_kwmkwltp_.dbf

...
....
auxiliary instance file /backup/TSPITR/REST_RECO/TSPITR/controlfile/o1_mf_kwmkw5vj_.ctl deleted
auxiliary instance file tspitr_pxDA_82194.dmp deleted
Finished recover at 20-JAN-23

RMAN> exit

Recovery Manager complete.

=> Verify OLD datafile still there

[grid@NEW_TESTRESTO ~]$ asmcmd
ASMCMD> cd +DATA/TSPITR/DATAFILE/
ASMCMD> ls
SYSAUX.4845.1126159815
SYSTEM.4857.1126159781
TSPITR_RMAN.7693.1126546213
UNDOTBS1.4833.1126159841
USERS.4809.1126159841
ASMCMD> exit

=> Verify NEW  datafile also still there

[oracle@NEW_TESTRESTO TSPITR]$
[oracle@NEW_TESTRESTO TSPITR]$ cd datafile_tspitr
[oracle@NEW_TESTRESTO datafile_tspitr]$ ls -lrt
total 512008
-rw-r-----. 1 oracle asmadmin 524296192 Jan 20 04:16 tspitr_rman.dbf
[oracle@NEW_TESTRESTO datafile_tspitr]$ sqlplus "/as sysdba"

SQL> SQL> alter tablespace TSPITR_RMAN ONLINE;

Tablespace altered.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@NEW_TESTRESTO datafile_tspitr]$

=> Check data
[oracle@NEW_TESTRESTO datafile_tspitr]$  sqlplus ABHI_TEST/mPt2i#PH

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
RMAN_TISPTR

SQL> select * from RMAN_TISPTR;

        ID
----------
         1
         2
         3

SQL>

C=> Performing RMAN TSPITR Using Your Own Auxiliary Database

For TSPITR, the target and auxiliary database instances must be on the same host.

DEMONSTRATION
================
Step 1=> Create Dummy tablespace and Table .

[oracle@DB_RESTORE_NEW ~]$  sqlplus ABHI_TEST/mPt2i#PH

SQL> create tablespace TSPITR_NEW datafile '+DATA' size 500m;

Tablespace created.

SQL> create table TSPITR_NEW_TAB (data date) tablespace TSPITR_NEW;

Table created.

SQL> insert into TSPITR_NEW_TAB values (systimestamp);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL>
SQL>
SQL> commit;

Commit complete.

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select * from TSPITR_NEW_TAB;

DATA
--------------------
21-JAN-2023 03:30:38
21-JAN-2023 03:30:38
21-JAN-2023 03:30:39
21-JAN-2023 03:30:39
21-JAN-2023 03:30:39

SQL> exit

Step 2=> Take backup of database as below 

[oracle@DB_RESTORE_NEW ~]$ rman target /

connected to target database: TSPITR (DBID=3551235132)

RMAN> RUN
{
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
allocate channel CH01 type disk;
allocate channel CH02 type disk;
allocate channel CH03 type disk;
allocate channel CH04 type disk;
BACKUP as compressed backupset database FORMAT '/backup/TSPITR/BKP/DB_FULL_backup_%U' plus archivelog  FORMAT '/backup/TSPITR/BKP/%d_%T_%s_%p_ARCHIVE' ;
backup format '/backup/TSPITR/BKP/BKP_cf_%d_%U_%t' current controlfile tag='backup_controlfile';
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
release channel CH01;
release channel CH02;
release channel CH03;
release channel CH04;
....
....
...
Finished backup at 21-JAN-23

sql statement: ALTER SYSTEM ARCHIVE LOG CURRENT

sql statement: ALTER SYSTEM ARCHIVE LOG CURRENT

released channel: CH01

released channel: CH02

released channel: CH03

released channel: CH04

RMAN> exit

Recovery Manager complete.

Step 3=> Create required Directories for AUXILIARY Database as below.


[oracle@DB_RESTORE_NEW ~]$ cd /backup/TSPITR/
[oracle@DB_RESTORE_NEW TSPITR]$ ls -lrt
total 8
drwxr-xr-x. 8 oracle oinstall 4096 Jan 21 02:50 AUX_TSPITR
drwxr-xr-x. 2 oracle oinstall 4096 Jan 21 03:33 BKP
[oracle@DB_RESTORE_NEW TSPITR]$
[oracle@DB_RESTORE_NEW TSPITR]$
[oracle@DB_RESTORE_NEW TSPITR]$
[oracle@DB_RESTORE_NEW TSPITR]$ cd AUX_TSPITR/
[oracle@DB_RESTORE_NEW AUX_TSPITR]$ ls -lrt
total 4
drwxr-xr-x. 2 oracle oinstall  10 Jan 21 01:36 PFILE
drwxr-xr-x. 2 oracle oinstall  10 Jan 21 01:36 LOGFILE
drwxr-xr-x. 2 oracle oinstall  10 Jan 21 01:36 REDO
-rw-r--r--. 1 oracle oinstall 813 Jan 21 02:50 pfile_aux.ora
drwxr-xr-x. 2 oracle oinstall  10 Jan 21 03:10 CONTROLFILE
drwxr-xr-x. 2 oracle oinstall  10 Jan 21 03:27 DATAFILE
drwxr-xr-x. 2 oracle oinstall  10 Jan 21 03:28 FRA
[oracle@DB_RESTORE_NEW AUX_TSPITR]$

[oracle@DB_RESTORE_NEW AUX_TSPITR]$

Step 4=> Create required PFILE  for AUXILIARY Database as below.

[oracle@DB_RESTORE_NEW AUX_TSPITR]$ cat pfile_aux.ora
*.audit_file_dest='/u02/app/oracle/admin/TSPITR/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/backup/TSPITR/AUX_TSPITR/CONTROLFILE/control01.ctl','/backup/TSPITR/AUX_TSPITR/CONTROLFILE/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/backup/TSPITR/AUX_TSPITR/DATAFILE/'
*.db_name='TSPITR'
*.DB_UNIQUE_NAME=TSPITR_N
*.db_recovery_file_dest='/backup/TSPITR/AUX_TSPITR/FRA'
*.db_recovery_file_dest_size=8256m
*.diagnostic_dest='/app/oracle/oracle_base'
*.open_cursors=300
*.pga_aggregate_target=4800m
*.processes=1280
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=14400m
*.undo_tablespace='UNDOTBS1'
*.DB_FILE_NAME_CONVERT=('+DATA/TSPITR/DATAFILE/','/backup/TSPITR/AUX_TSPITR/DATAFILE/')
*.LOG_FILE_NAME_CONVERT=('+DATA/TSPITR/ONLINELOG/','/backup/TSPITR/AUX_TSPITR/REDOFILES')

Step 5=> Create ORACLE_SID and Password File as below.

[oracle@DB_RESTORE_NEW AUX_TSPITR]$ export ORACLE_SID=TSPITR_N

[oracle@DB_RESTORE_NEW dbs]$ orapwd file=orapwTSPITR_N password=mPt2i#PH

Step 6=> Add below entry in tnsnames.ora and Listener.ora 

[oracle@DB_RESTORE_NEW BKP]$ cat /u01/app/19.0.0/grid/network/admin/listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DB_RESTORE_NEW.aufadmgmt.com)(PORT = 1521))
    )
  )

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON            # line added by Agent

SID_LIST_LISTENER =
        (SID_LIST =
                (SID_DESC =
                        (SID_NAME = TSPITR_N)
                        (ORACLE_HOME = /u01/app/19.0.0/grid)
                )
        )
[oracle@DB_RESTORE_NEW BKP]$

[oracle@DB_RESTORE_NEW BKP]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u02/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TSPITR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DB_RESTORE_NEW.aufadmgmt.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSPITR)
    )
  )

LISTENER_TSPITR =
  (ADDRESS = (PROTOCOL = TCP)(HOST = DB_RESTORE_NEW.aufadmgmt.com)(PORT = 1521))

TSPITR_N =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DB_RESTORE_NEW.aufadmgmt.com)(PORT = 1533))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSPITR_N)
        (UR=A)
    )
  )

[oracle@DB_RESTORE_NEW BKP]$

[oracle@DB_RESTORE_NEW BKP]$ tnsping TSPITR_N

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 21-JAN-2023 03:51:55

Copyright (c) 1997, 2022, Oracle.  All rights reserved.

Used parameter files:
/u02/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = DB_RESTORE_NEW.aufadmgmt.com)(PORT = 1533)) 
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TSPITR_N) (UR=A)))
OK (10 msec)
[oracle@DB_RESTORE_NEW BKP]$

Step 7=> Start Auxiliary database as below

[oracle@DB_RESTORE_NEW dbs]$ cd -
/backup/TSPITR/AUX_TSPITR
[oracle@DB_RESTORE_NEW AUX_TSPITR]$ ls -lrt
total 4
drwxr-xr-x. 2 oracle oinstall  10 Jan 21 01:36 PFILE
drwxr-xr-x. 2 oracle oinstall  10 Jan 21 01:36 LOGFILE
drwxr-xr-x. 2 oracle oinstall  10 Jan 21 01:36 REDO
-rw-r--r--. 1 oracle oinstall 813 Jan 21 02:50 pfile_aux.ora
drwxr-xr-x. 2 oracle oinstall  10 Jan 21 03:10 CONTROLFILE
drwxr-xr-x. 2 oracle oinstall  10 Jan 21 03:27 DATAFILE
drwxr-xr-x. 2 oracle oinstall  10 Jan 21 03:28 FRA

[oracle@DB_RESTORE_NEW AUX_TSPITR]$ sqlplus "/as sysdba"

Connected to an idle instance.

SQL> startup nomount pfile='pfile_aux.ora';
ORACLE instance started.

Total System Global Area 1.5099E+10 bytes
Fixed Size                 18351296 bytes
Variable Size            2046820352 bytes
Database Buffers         1.3019E+10 bytes
Redo Buffers               15200256 bytes
SQL>
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@DB_RESTORE_NEW AUX_TSPITR]$

Step 8=> Start RMAN session and connect to Auxiliary databas as below and start recovery.

[oracle@DB_RESTORE_NEW BKP]$ rman target / auxiliary sys/mPt2i#PH@TSPITR_N

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Jan 21 03:39:02 2023
Version 19.16.0.0.0

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

connected to target database: TSPITR (DBID=3551235132)
connected to auxiliary database: TSPITR (not mounted)

RMAN> run {
ALLOCATE AUXILIARY CHANNEL ch00 device type disk;
ALLOCATE AUXILIARY CHANNEL ch01 device type disk;
recover tablespace TSPITR_NEW until time "to_date('2023 JAN 21 03:34','YYYY MON DD HH24:MI')";
}2> 3> 4> 5>

using target database control file instead of recovery catalog
allocated channel: ch00
channel ch00: SID=735 device type=DISK

allocated channel: ch01
channel ch01: SID=857 device type=DISK

Starting recover at 21-JAN-23
current log archived
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2023 JAN 21 03:34','YYYY MON DD HH24:MI')";
# 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';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 21-JAN-23

channel ch00: starting datafile backup set restore
channel ch00: restoring control file
channel ch00: reading from backup piece /backup/TSPITR/BKP/BKP_cf_TSPITR_281if6g5_72_1_1_1126668805
channel ch00: piece handle=/backup/TSPITR/BKP/BKP_cf_TSPITR_281if6g5_72_1_1_1126668805 tag=BACKUP_CONTROLFILE
channel ch00: restored backup piece 1
channel ch00: restore complete, elapsed time: 00:00:01
output file name=/backup/TSPITR/AUX_TSPITR/CONTROLFILE/control01.ctl
output file name=/backup/TSPITR/AUX_TSPITR/CONTROLFILE/control02.ctl
Finished restore at 21-JAN-23

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2023 JAN 21 03:34','YYYY MON DD HH24:MI')";
plsql <<<--
declare
  sqlstatement       varchar2(512);
  pdbname            varchar2(128);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
   pdbname := null; -- pdbname
  sqlstatement := 'alter tablespace '||  'TSPITR_NEW' ||' offline immediate';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement, 0, pdbname);
exception
  when offline_not_needed then
    null;
end; >>>;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  1 to
 "/backup/TSPITR/AUX_TSPITR/DATAFILE/system.4857.1126159781";
set newname for datafile  4 to
 "/backup/TSPITR/AUX_TSPITR/DATAFILE/undotbs1.4833.1126159841";
set newname for datafile  3 to
 "/backup/TSPITR/AUX_TSPITR/DATAFILE/sysaux.4845.1126159815";
set newname for clone tempfile  1 to new;
set newname for datafile  5 to
 "+DATA/TSPITR/DATAFILE/tspitr_new.2842.1126630801";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 4, 3, 5;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace TSPITR_NEW offline immediate

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /backup/TSPITR/AUX_TSPITR/DATAFILE/TSPITR/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 21-JAN-23

channel ch00: starting datafile backup set restore
channel ch00: specifying datafile(s) to restore from backup set
channel ch00: restoring datafile 00004 to /backup/TSPITR/AUX_TSPITR/DATAFILE/undotbs1.4833.1126159841
channel ch00: reading from backup piece /backup/TSPITR/BKP/DB_FULL_backup_241if6el_68_1_1
channel ch01: starting datafile backup set restore
channel ch01: specifying datafile(s) to restore from backup set
channel ch01: restoring datafile 00003 to /backup/TSPITR/AUX_TSPITR/DATAFILE/sysaux.4845.1126159815
channel ch01: restoring datafile 00005 to +DATA/TSPITR/DATAFILE/tspitr_new.2842.1126630801
channel ch01: reading from backup piece /backup/TSPITR/BKP/DB_FULL_backup_221if6el_66_1_1
channel ch00: piece handle=/backup/TSPITR/BKP/DB_FULL_backup_241if6el_68_1_1 tag=TAG20230121T033237
channel ch00: restored backup piece 1
channel ch00: restore complete, elapsed time: 00:00:03
channel ch00: starting datafile backup set restore
channel ch00: specifying datafile(s) to restore from backup set
channel ch00: restoring datafile 00001 to /backup/TSPITR/AUX_TSPITR/DATAFILE/system.4857.1126159781
channel ch00: reading from backup piece /backup/TSPITR/BKP/DB_FULL_backup_231if6el_67_1_1
channel ch01: piece handle=/backup/TSPITR/BKP/DB_FULL_backup_221if6el_66_1_1 tag=TAG20230121T033237
channel ch01: restored backup piece 1
channel ch01: restore complete, elapsed time: 00:00:28
channel ch00: piece handle=/backup/TSPITR/BKP/DB_FULL_backup_231if6el_67_1_1 tag=TAG20230121T033237
channel ch00: restored backup piece 1
channel ch00: restore complete, elapsed time: 00:00:45
Finished restore at 21-JAN-23

datafile 5 switched to datafile copy
input datafile copy RECID=2 STAMP=1126669260 file name=+DATA/TSPITR/DATAFILE/tspitr_new.2842.1126630801

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2023 JAN 21 03:34','YYYY MON DD HH24:MI')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  4 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  5 online";
# recover and open resetlogs
recover clone database tablespace  "TSPITR_NEW", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  5 online

Starting recover at 21-JAN-23

Executing: alter database datafile 7 offline
starting media recovery

archived log for thread 1 with sequence 61 is already on disk as file +DATA/TSPITR/ARCHIVELOG/2023_01_21/thread_1_seq_61.3305.1126631005
archived log for thread 1 with sequence 62 is already on disk as file +DATA/TSPITR/ARCHIVELOG/2023_01_21/thread_1_seq_62.3356.1126631007
archived log for thread 1 with sequence 63 is already on disk as file +DATA/TSPITR/ARCHIVELOG/2023_01_21/thread_1_seq_63.3449.1126631007
archived log for thread 1 with sequence 64 is already on disk as file +DATA/TSPITR/ARCHIVELOG/2023_01_21/thread_1_seq_64.3413.1126631399
archived log file name=+DATA/TSPITR/ARCHIVELOG/2023_01_21/thread_1_seq_61.3305.1126631005 thread=1 sequence=61
archived log file name=+DATA/TSPITR/ARCHIVELOG/2023_01_21/thread_1_seq_62.3356.1126631007 thread=1 sequence=62
archived log file name=+DATA/TSPITR/ARCHIVELOG/2023_01_21/thread_1_seq_63.3449.1126631007 thread=1 sequence=63
archived log file name=+DATA/TSPITR/ARCHIVELOG/2023_01_21/thread_1_seq_64.3413.1126631399 thread=1 sequence=64
media recovery complete, elapsed time: 00:00:00
Finished recover at 21-JAN-23

database opened

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  TSPITR_NEW read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/backup/TSPITR/AUX_TSPITR/DATAFILE/''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/backup/TSPITR/AUX_TSPITR/DATAFILE/''";
}
executing Memory Script

sql statement: alter tablespace  TSPITR_NEW read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/backup/TSPITR/AUX_TSPITR/DATAFILE/''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/backup/TSPITR/AUX_TSPITR/DATAFILE/''

Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_ytof_Brna":
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Master table "SYS"."TSPITR_EXP_ytof_Brna" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_ytof_Brna is:
   EXPDP>   /backup/TSPITR/AUX_TSPITR/DATAFILE/tspitr_ytof_24984.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace TSPITR_NEW:
   EXPDP>   +DATA/TSPITR/DATAFILE/tspitr_new.2842.1126630801
   EXPDP> Job "SYS"."TSPITR_EXP_ytof_Brna" successfully completed at Sat Jan 21 03:41:37 2023 elapsed 0 00:00:25
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
# drop target tablespaces before importing them back
sql 'drop tablespace  TSPITR_NEW including contents keep datafiles cascade constraints';
}
executing Memory Script

Oracle instance shut down

sql statement: drop tablespace  TSPITR_NEW including contents keep datafiles cascade constraints

Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_ytof_Dnwr" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_ytof_Dnwr":
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_ytof_Dnwr" successfully completed at Sat Jan 21 03:41:48 2023 elapsed 0 00:00:02
Import completed

contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  TSPITR_NEW read write';
sql 'alter tablespace  TSPITR_NEW offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script

sql statement: alter tablespace  TSPITR_NEW read write

sql statement: alter tablespace  TSPITR_NEW offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
auxiliary instance file /backup/TSPITR/AUX_TSPITR/DATAFILE/TSPITR/datafile/o1_mf_temp_kwp4cxfd_.tmp deleted
auxiliary instance file /backup/TSPITR/AUX_TSPITR/FRA/TSPITR/onlinelog/o1_mf_3_kwp4cq0w_.log deleted
auxiliary instance file /backup/TSPITR/AUX_TSPITR/DATAFILE/TSPITR/onlinelog/o1_mf_3_kwp4cpvd_.log deleted
auxiliary instance file /backup/TSPITR/AUX_TSPITR/FRA/TSPITR/onlinelog/o1_mf_2_kwp4cq11_.log deleted
auxiliary instance file /backup/TSPITR/AUX_TSPITR/DATAFILE/TSPITR/onlinelog/o1_mf_2_kwp4cptq_.log deleted
auxiliary instance file /backup/TSPITR/AUX_TSPITR/DATAFILE/TSPITR/onlinelog/o1_mf_1_kwp4cpt6_.log deleted
auxiliary instance file /backup/TSPITR/AUX_TSPITR/FRA/TSPITR/onlinelog/o1_mf_1_kwp4cpyy_.log deleted
auxiliary instance file /backup/TSPITR/AUX_TSPITR/DATAFILE/sysaux.4845.1126159815 deleted
auxiliary instance file /backup/TSPITR/AUX_TSPITR/DATAFILE/undotbs1.4833.1126159841 deleted
auxiliary instance file /backup/TSPITR/AUX_TSPITR/DATAFILE/system.4857.1126159781 deleted
auxiliary instance file /backup/TSPITR/AUX_TSPITR/CONTROLFILE/control02.ctl deleted
auxiliary instance file /backup/TSPITR/AUX_TSPITR/CONTROLFILE/control01.ctl deleted
auxiliary instance file tspitr_ytof_24984.dmp deleted
Finished recover at 21-JAN-23

RMAN> exit
Recovery Manager complete.

Step 9=> Make Tablespace ONLINE and check data availability.

[oracle@DB_RESTORE_NEW BKP]$ sqlplus "/as sysdba"

SQL> alter tablespace TSPITR_NEW ONLINE;

Tablespace altered.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

[oracle@DB_RESTORE_NEW BKP]$  sqlplus ABHI_TEST/mPt2i#PH

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select * from TSPITR_NEW_TAB;

DATA
--------------------
21-JAN-2023 03:30:38
21-JAN-2023 03:30:38
21-JAN-2023 03:30:39
21-JAN-2023 03:30:39
21-JAN-2023 03:30:39

SQL> exit