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
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444