Oracle Database Commands and Practical Examples: RMAN - Performing Tablespace Point-in-Time Recovery (TSPITR) Table

Scenario Preview:

Database in archivelog mode
No catalog database
Backup of the database is available
Table is dropped on particular time (idata , ilog tablespace)
Auxiliary instance is created which in turn creates its temporary work area

Solution:

Create table and index after incident.
Execute DBMS_TTS.TRANSPORT_SET_CHECK
Check TRANSPORT_SET_VIOLATIONS ( Voilations Found )
Execute DBMS_TTS.TRANSPORT_SET_CHECK providing tablespace details
Check TRANSPORT_SET_VIOLATIONS (No Violations found)
Check creation time using TS_PITR_OBJECTS_TO_BE_DROPPED
Create directory auxdest.
RECOVER TABLESPACE providing recover time and auxiliary destination
Found Error RMAN-11003, ORA-00604, ORA-02429
Check and disable constraint
Again RECOVER TABLESPACE providing recover time and auxiliary destination
BACKUP TABLESPACE idata,ilog TAG 'TBS Idata_Ilog'
ALTER TABLESPACE idata online
ALTER TABLESPACE ilog online
Check table infodba.POM_BACKPOINTER
Enable constraint

VIOLATIONS: ORA-39907

Error Found :
RMAN-11003: failure during parse/execution of SQL statement: drop tablespace IDATA including contents keep datafiles cascade constraints
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key

(Reference : https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmtspit.htm#BRADV89788)

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. For example, you maintain logical databases in the orders and personnel tablespaces. An incorrect batch job or data manipulation language (DML) statement corrupts the data in only one tablespace.
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.

[oracle@srv1 expdp]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Aug 7 17:13:48 2019

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

connected to target database: SMR1 (DBID=3409868754)

RMAN> BACKUP DATABASE TAG 'DB_FULL';

Starting backup at 07-AUG-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/smr1/idata01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/smr1/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/smr1/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/smr1/undotbs01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/smr1/ilog01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/smr1/indx01.dbf
input datafile file number=00006 name=/u01/rmanbkp/copy_db_data_D-SMR1_I-3409868754_TS-USERS_FNO-6_0uu8k31s
channel ORA_DISK_1: starting piece 1 at 07-AUG-19
ALTER SYSTEM SWITCH LOGFILE;
channel ORA_DISK_1: finished piece 1 at 07-AUG-19
piece handle=/u01/app/oracle/fast_recovery_area/SMR1/backupset/2019_08_07/o1_mf_nnndf_DB_FULL_gnogcvyt_.bkp tag=DB_FULL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:46
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 07-AUG-19
channel ORA_DISK_1: finished piece 1 at 07-AUG-19
piece handle=/u01/app/oracle/fast_recovery_area/SMR1/backupset/2019_08_07/o1_mf_ncsnf_DB_FULL_gnogh6n5_.bkp tag=DB_FULL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-AUG-19

RMAN>
Statement processed

RMAN> ALTER SYSTEM SWITCH LOGFILE;

Statement processed

RMAN> ALTER SYSTEM SWITCH LOGFILE;

Statement processed

RMAN> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD:HH24:MI:SS') FROM DUAL;

TO_CHAR(SYSDATE,'YY
-------------------
2019-08-07:17:16:24

RMAN> SELECT COUNT(*) FROM infodba.POM_BACKPOINTER;

COUNT(*)

----------

3424222

RMAN> drop table infodba.POM_BACKPOINTER;

Statement processed

RMAN> ALTER SYSTEM SWITCH LOGFILE;

Statement processed

[oracle@srv1 expdp]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 7 17:21:29 2019

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> CREATE TABLE infodba.POM_BACKPOINTER1 TABLESPACE idata AS SELECT TABLE_NAME FROM DBA_TABLES;

Table created.

SQL> CREATE INDEX infodba.POM_BACKPOINTER1_ndx ON infodba.POM_BACKPOINTER1 (TABLE_NAME) TABLESPACE idata;

Index created.

SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('idata', TRUE,TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS

--------------------------------------------------------------------------------

ORA-39907: Index INFODBA.PRIN_OB_TRANSACTION_ID in tablespace IDATA points to ta

ble INFODBA.PRINCIPAL_OBJ_IN_TRANS_TABLE in tablespace ILOG.

ORA-39907: Index INFODBA.MMV_EQUIVALENT_SPATIALREP1 in tablespace IDATA points t

o table INFODBA.MMV_EQUIVALENT_THREADS in tablespace ILOG.

ORA-39907: Index INFODBA.BOM_ACCT_TABLE_INDEX2 in tablespace IDATA points to tab

le INFODBA.BOM_ACCT_TABLE in tablespace ILOG.

ORA-39907: Index INFODBA.EXP_TO_TRANSACTION_ID in tablespace IDATA points to tab

le INFODBA.EXPORT_TO_SITE_TABLE in tablespace ILOG.

VIOLATIONS

--------------------------------------------------------------------------------

ORA-39907: Index INFODBA.MMVEQUIVALENTTHREADS_PUID_PK in tablespace IDATA points

to table INFODBA.MMV_EQUIVALENT_THREADS in tablespace ILOG.

ORA-39908: Index INFODBA.PK_PRINCIPAL_OBJ_TBL in tablespace IDATA enforces prima

ry constraints of table INFODBA.PRINCIPAL_OBJ_IN_TRANS_TABLE in tablespace ILOG

ORA-39907: Index INFODBA.ROOT_OBJ_TRANS_ID in tablespace IDATA points to table I

NFODBA.ROOT_OBJS_IN_TRANS_TABLE in tablespace ILOG.

VIOLATIONS

--------------------------------------------------------------------------------

ORA-39907: Index INFODBA.MMV_SPATIAL_CELLID1 in tablespace IDATA points to table

INFODBA.MMV_SPATIAL_CELL_INDEX in tablespace ILOG.

ORA-39907: Index INFODBA.ACCT_TABLE_INDEX3 in tablespace IDATA points to table I

NFODBA.ACCT_TABLE in tablespace ILOG.

ORA-39907: Index INFODBA.ACCT_TABLE_INDEX4 in tablespace IDATA points to table I

NFODBA.ACCT_TABLE in tablespace ILOG.

ORA-39907: Index INFODBA.MMV_EQUIVALENT_EQUIVTHREAD1 in tablespace IDATA points

to table INFODBA.MMV_EQUIVALENT_THREADS in tablespace ILOG.

VIOLATIONS

--------------------------------------------------------------------------------

ORA-39907: Index INFODBA.MMV_SPATIAL_SPATIALREP1 in tablespace IDATA points to t

able INFODBA.MMV_SPATIAL_CELL_INDEX in tablespace ILOG.

ORA-39907: Index INFODBA.MMVSPATIALCELLINDEX_PUID_PK in tablespace IDATA points

to table INFODBA.MMV_SPATIAL_CELL_INDEX in tablespace ILOG.

ORA-39907: Index INFODBA.BOM_ACCT_TABLE_INDEX in tablespace IDATA points to tabl

e INFODBA.BOM_ACCT_TABLE in tablespace ILOG.

ORA-39908: Index INFODBA.PK_EXP_TO_TBL in tablespace IDATA enforces primary cons

VIOLATIONS

--------------------------------------------------------------------------------

traints of table INFODBA.EXPORT_TO_SITE_TABLE in tablespace ILOG.

ORA-39907: Index INFODBA.SCRATCH_TABLE_INDEX in tablespace IDATA points to table

INFODBA.SCRATCH_TABLE in tablespace ILOG.

ORA-39908: Index INFODBA.PK_TRANSACTION_TBL in tablespace IDATA enforces primary

constraints of table INFODBA.TRANSACTION_TABLE in tablespace ILOG.

ORA-39907: Index INFODBA.MMV_SPATIAL_OCCPATHPREFIX1 in tablespace IDATA points t

o table INFODBA.MMV_SPATIAL_CELL_INDEX in tablespace ILOG.

VIOLATIONS

--------------------------------------------------------------------------------

ORA-39907: Index INFODBA.BOM_RECIPE_TABLE_INDEX2 in tablespace IDATA points to t

able INFODBA.BOM_RECIPE_TABLE in tablespace ILOG.

ORA-39907: Index INFODBA.ACCT_TABLE_INDEX in tablespace IDATA points to table IN

FODBA.ACCT_TABLE in tablespace ILOG.

ORA-39907: Index INFODBA.ACCT_TABLE_INDEX2 in tablespace IDATA points to table I

NFODBA.ACCT_TABLE in tablespace ILOG.

ORA-39908: Index INFODBA.PK_EXP_OBJ_TBL in tablespace IDATA enforces primary con

straints of table INFODBA.EXP_OBJ_TABLE in tablespace ILOG.

VIOLATIONS

--------------------------------------------------------------------------------

ORA-39908: Index INFODBA.PK_ROOT_OBJ_TBL in tablespace IDATA enforces primary co

nstraints of table INFODBA.ROOT_OBJS_IN_TRANS_TABLE in tablespace ILOG.

ORA-39907: Index INFODBA.MMV_EQUIVALENT_ANCHORTHREAD1 in tablespace IDATA points

to table INFODBA.MMV_EQUIVALENT_THREADS in tablespace ILOG.

ORA-39907: Index INFODBA.TIE_CONCURRENCY_GSID_PK in tablespace IDATA points to t

able INFODBA.TIE_CONCURRENCY in tablespace ILOG.

ORA-39907: Index INFODBA.BOM_RECIPE_TABLE_INDEX in tablespace IDATA points to ta

VIOLATIONS

--------------------------------------------------------------------------------

ble INFODBA.BOM_RECIPE_TABLE in tablespace ILOG.

26 rows selected.

SQL>

SQL> drop index infodba.POM_BACKPOINTER1_ndx;

Index dropped.

SQL> CREATE INDEX infodba.POM_BACKPOINTER1_ndx ON infodba.POM_BACKPOINTER1 (TABLE_NAME) TABLESPACE ILOG;

Index created.

SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('idata,ilog', TRUE,TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

SQL>

SQL> col OWNER for a10

col NAME for a10

col TABLESPACE_NAME for a10

SELECT OWNER, NAME,TABLESPACE_NAME FROM TS_PITR_OBJECTS_TO_BE_DROPPED WHERE TABLESPACE_NAME IN ('IDATA,ILOG') AND CREATION_TIME > TO_DATE('2019-08-07:17:16:24','YYYY-MM-DD:HH24:MI:SS') ORDER BY 1,2; SQL> SQL> SQL>

no rows selected

SQL> desc TS_PITR_OBJECTS_TO_BE_DROPPED;

Name Null? Type

----------------------------------------- -------- ----------------------------

OWNER NOT NULL VARCHAR2(128)

NAME NOT NULL VARCHAR2(128)

CREATION_TIME NOT NULL DATE

TABLESPACE_NAME VARCHAR2(30)

SQL> select * from TS_PITR_OBJECTS_TO_BE_DROPPED;

OWNER NAME CREATION_TIME TABLESPACE

---------- ---------- ------------------- ----------

OWNER NAME CREATION_TIME TABLESPACE

---------- ---------- ------------------- ----------

P0DIS_3

INFODBA PIPL10N_AW 2019-08-07 13:12:43 IDATA

P0DIS_4

INFODBA BIN$j4Z/Ve 2019-08-07 12:27:02 IDATA

v3PdrgU0c4

qMD92Q==$0

INFODBA BIN$j4Z/Ve 2019-08-07 12:27:23 IDATA

v4PdrgU0c4

OWNER NAME CREATION_TIME TABLESPACE

---------- ---------- ------------------- ----------

qMD92Q==$0

INFODBA BIN$j4Z/Ve 2019-08-07 12:24:05 IDATA

v5PdrgU0c4

qMD92Q==$0

INFODBA POM_BACKPO 2019-08-07 17:22:42 IDATA

INTER1

INFODBA POM_BACKPO 2019-08-07 17:59:26 IDATA

INTER2

OWNER NAME CREATION_TIME TABLESPACE

---------- ---------- ------------------- ----------

INFODBA POM_BACKPO 2019-08-07 17:29:26 ILOG

INTER1_NDX

19595 rows selected.

SQL> col OWNER for a10

col NAME for a10

col TABLESPACE_NAME for a10

SELECT OWNER, NAME,TABLESPACE_NAME FROM TS_PITR_OBJECTS_TO_BE_DROPPED WHERE TABLESPACE_NAME IN ('IDATA,ILOG') AND CREATION_TIME > TO_DATE('2019-08-07 17:16:24','YYYY-MM-DD HH24:MI:SS') ORDER BY 1,2; SQL> SQL> SQL>

no rows selected

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@srv1 expdp]$ cd /media/

[oracle@srv1 media]$ ll

total 4

drwxrwx---. 2 root vboxsf 4096 Jun 24 13:42 sf_varun_docs

[oracle@srv1 media]$ cd sf_varun_docs/

[oracle@srv1 sf_varun_docs]$ ll

total 0

[oracle@srv1 sf_varun_docs]$ pwd

/media/sf_varun_docs

[oracle@srv1 sf_varun_docs]$ host mkdir /media/sf_varun_docs/auxdest

host: couldn't get address for '/media/sf_varun_docs/auxdest': failure

[oracle@srv1 sf_varun_docs]$ mkdir /media/sf_varun_docs/auxdest

[oracle@srv1 sf_varun_docs]$ ll

total 4

drwxr-xr-x 2 oracle oinstall 4096 Aug 7 18:25 auxdest

[oracle@srv1 sf_varun_docs]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Aug 7 18:26:02 2019

connected to target database: SMR1 (DBID=3409868754)

RMAN> RECOVER TABLESPACE idata,ilog UNTIL TIME "to_date('2019-08-07:17:16:24','YYYY-MM-DD:HH24:MI:SS')" AUXILIARY DESTINATION '/media/sf_varun_docs/auxdest';

Starting recover at 07-AUG-19

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1 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='hEma'

initialization parameters used for automatic instance:

db_name=SMR1

db_unique_name=hEma_pitr_SMR1

compatible=12.1.0.2.0

db_block_size=8192

db_files=200

diagnostic_dest=/u01/app/oracle

_system_trig_enabled=FALSE

sga_target=1712M

processes=200

db_create_file_dest=/media/sf_varun_docs/auxdest

log_archive_dest_1='location=/media/sf_varun_docs/auxdest'

#No auxiliary parameter file used

starting up automatic instance SMR1

Oracle instance started

Total System Global Area 1795162112 bytes

Fixed Size 2925456 bytes

Variable Size 436210800 bytes

Database Buffers 1342177280 bytes

Redo Buffers 13848576 bytes

Automatic instance created

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('2019-08-07:17:16:24','YYYY-MM-DD:HH24:MI:SS')";

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

# 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 07-AUG-19

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=22 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 /u01/app/oracle/fast_recovery_area/SMR1/backupset/2019_08_07/o1_mf_ncsnf_DB_FULL_gnogh6n5_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/SMR1/backupset/2019_08_07/o1_mf_ncsnf_DB_FULL_gnogh6n5_.bkp tag=DB_FULL

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/media/sf_varun_docs/auxdest/SMR1/controlfile/o1_mf_gnolotlv_.ctl

Finished restore at 07-AUG-19

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('2019-08-07:17:16:24','YYYY-MM-DD:HH24:MI:SS')";

plsql <<<--

declare

sqlstatement varchar2(512);

pdbname varchar2(30);

offline_not_needed exception;

pragma exception_init(offline_not_needed, -01539);

begin

pdbname := null; -- pdbname

sqlstatement := 'alter tablespace '|| 'IDATA' ||' offline immediate';

krmicd.writeMsg(6162, sqlstatement);

krmicd.execSql(sqlstatement, 0, pdbname);

exception

when offline_not_needed then

null;

end; >>>;

plsql <<<--

declare

sqlstatement varchar2(512);

pdbname varchar2(30);

offline_not_needed exception;

pragma exception_init(offline_not_needed, -01539);

begin

pdbname := null; -- pdbname

sqlstatement := 'alter tablespace '|| 'ILOG' ||' 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 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 5 to

"/u01/app/oracle/oradata/smr1/idata01.dbf";

set newname for datafile 2 to

"/u01/app/oracle/oradata/smr1/ilog01.dbf";

# 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, 2;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

sql statement: alter tablespace IDATA offline immediate

sql statement: alter tablespace ILOG offline immediate

executing command: SET NEWNAME

renamed tempfile 1 to /media/sf_varun_docs/auxdest/SMR1/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 07-AUG-19

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /media/sf_varun_docs/auxdest/SMR1/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /media/sf_varun_docs/auxdest/SMR1/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /media/sf_varun_docs/auxdest/SMR1/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/smr1/idata01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/smr1/ilog01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/SMR1/backupset/2019_08_07/o1_mf_nnndf_DB_FULL_gnogcvyt_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/SMR1/backupset/2019_08_07/o1_mf_nnndf_DB_FULL_gnogcvyt_.bkp tag=DB_FULL

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:55

Finished restore at 07-AUG-19

datafile 1 switched to datafile copy

input datafile copy RECID=13 STAMP=1015698573 file name=/media/sf_varun_docs/auxdest/SMR1/datafile/o1_mf_system_gnolp275_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=14 STAMP=1015698573 file name=/media/sf_varun_docs/auxdest/SMR1/datafile/o1_mf_undotbs1_gnolp27g_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=15 STAMP=1015698573 file name=/media/sf_varun_docs/auxdest/SMR1/datafile/o1_mf_sysaux_gnolp278_.dbf

contents of Memory Script:

{

# set requested point in time

set until time "to_date('2019-08-07:17:16:24','YYYY-MM-DD: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 5 online";

sql clone "alter database datafile 2 online";

# recover and open resetlogs

recover clone database tablespace "IDATA", "ILOG", "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

sql statement: alter database datafile 2 online

Starting recover at 07-AUG-19

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_1_gnoghcnz_.arc

archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_2_gnoghfqp_.arc

archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_3_gnoghk7f_.arc

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_4_gnogl2h8_.arc

archived log file name=/u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_1_gnoghcnz_.arc thread=1 sequence=1

archived log file name=/u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_2_gnoghfqp_.arc thread=1 sequence=2

archived log file name=/u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_3_gnoghk7f_.arc thread=1 sequence=3

archived log file name=/u01/app/oracle/fast_recovery_area/SMR1/archivelog/2019_08_07/o1_mf_1_4_gnogl2h8_.arc thread=1 sequence=4

media recovery complete, elapsed time: 00:00:01

Finished recover at 07-AUG-19

database opened

contents of Memory Script:

{

# make read only the tablespace that will be exported

sql clone 'alter tablespace IDATA read only';

sql clone 'alter tablespace ILOG read only';

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/media/sf_varun_docs/auxdest''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/media/sf_varun_docs/auxdest''";

}

executing Memory Script

sql statement: alter tablespace IDATA read only

sql statement: alter tablespace ILOG read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/media/sf_varun_docs/auxdest''

Performing export of metadata...

EXPDP> Starting "SYS"."TSPITR_EXP_hEma_jjqn":

EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX

EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX

EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS

EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT

EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER

EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

EXPDP> Master table "SYS"."TSPITR_EXP_hEma_jjqn" successfully loaded/unloaded

EXPDP> ******************************************************************************

EXPDP> Dump file set for SYS.TSPITR_EXP_hEma_jjqn is:

EXPDP> /media/sf_varun_docs/auxdest/tspitr_hEma_13301.dmp

EXPDP> ******************************************************************************

EXPDP> Datafiles required for transportable tablespace IDATA:

EXPDP> /u01/app/oracle/oradata/smr1/idata01.dbf

EXPDP> Datafiles required for transportable tablespace ILOG:

EXPDP> /u01/app/oracle/oradata/smr1/ilog01.dbf

EXPDP> Job "SYS"."TSPITR_EXP_hEma_jjqn" successfully completed at Wed Aug 7 18:32:02 2019 elapsed 0 00:01:55

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone abort

# drop target tablespaces before importing them back

sql 'drop tablespace IDATA including contents keep datafiles cascade constraints';

sql 'drop tablespace ILOG including contents keep datafiles cascade constraints';

}

executing Memory Script

Oracle instance shut down

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

Removing automatic instance

Automatic instance removed

auxiliary instance file /media/sf_varun_docs/auxdest/SMR1/datafile/o1_mf_temp_gnolsxxj_.tmp deleted

auxiliary instance file /media/sf_varun_docs/auxdest/SMR1/onlinelog/o1_mf_3_gnolsvpp_.log deleted

auxiliary instance file /media/sf_varun_docs/auxdest/SMR1/onlinelog/o1_mf_2_gnolst53_.log deleted

auxiliary instance file /media/sf_varun_docs/auxdest/SMR1/onlinelog/o1_mf_1_gnolss0l_.log deleted

auxiliary instance file /media/sf_varun_docs/auxdest/SMR1/datafile/o1_mf_sysaux_gnolp278_.dbf deleted

auxiliary instance file /media/sf_varun_docs/auxdest/SMR1/datafile/o1_mf_undotbs1_gnolp27g_.dbf deleted

auxiliary instance file /media/sf_varun_docs/auxdest/SMR1/datafile/o1_mf_system_gnolp275_.dbf deleted

auxiliary instance file /media/sf_varun_docs/auxdest/SMR1/controlfile/o1_mf_gnolotlv_.ctl deleted

auxiliary instance file tspitr_hEma_13301.dmp deleted

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 08/07/2019 18:32:37

RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of sql command on default channel at 08/07/2019 18:32:37

RMAN-11003: failure during parse/execution of SQL statement: drop tablespace IDATA including contents keep datafiles cascade constraints

ORA-00604: error occurred at recursive SQL level 1

ORA-02429: cannot drop index used for enforcement of unique/primary key

RMAN>

col OWNER for a30

col CONSTRAINT_NAME for a30

col TABLE_NAME for a30

col INDEX_OWNER for a30

col INDEX_NAME for a30

select owner, constraint_name,table_name,index_owner,index_name from dba_constraints where (index_owner,index_name) in (select owner,index_name from dba_indexes where tablespace_name='IDATA');

OWNER CONSTRAINT_NAME TABLE_NAME INDEX_OWNER INDEX_NAME

------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------

INFODBA PK_EXP_OBJ_TBL EXP_OBJ_TABLE INFODBA PK_EXP_OBJ_TBL

INFODBA PK_EXP_TO_TBL EXPORT_TO_SITE_TABLE INFODBA PK_EXP_TO_TBL

INFODBA PK_PRINCIPAL_OBJ_TBL PRINCIPAL_OBJ_IN_TRANS_TABLE INFODBA PK_PRINCIPAL_OBJ_TBL

INFODBA PK_ROOT_OBJ_TBL ROOT_OBJS_IN_TRANS_TABLE INFODBA PK_ROOT_OBJ_TBL

INFODBA PK_TRANSACTION_TBL TRANSACTION_TABLE INFODBA PK_TRANSACTION_TBL

SQL> ALTER TABLE INFODBA.EXP_OBJ_TABLE DISABLE CONSTRAINT PK_EXP_OBJ_TBL;

Table altered.

SQL> ALTER TABLE INFODBA.EXPORT_TO_SITE_TABLE DISABLE CONSTRAINT PK_EXP_TO_TBL;

Table altered.

SQL> ALTER TABLE INFODBA.PRINCIPAL_OBJ_IN_TRANS_TABLE DISABLE CONSTRAINT PK_PRINCIPAL_OBJ_TBL;

Table altered.

SQL> ALTER TABLE INFODBA.ROOT_OBJS_IN_TRANS_TABLE DISABLE CONSTRAINT PK_ROOT_OBJ_TBL;

Table altered.

SQL> ALTER TABLE INFODBA.TRANSACTION_TABLE DISABLE CONSTRAINT PK_TRANSACTION_TBL;

ALTER TABLE INFODBA.TRANSACTION_TABLE DISABLE CONSTRAINT PK_TRANSACTION_TBL

ERROR at line 1:

ORA-02297: cannot disable constraint (INFODBA.PK_TRANSACTION_TBL) - dependencies exist

SQL> SELECT p.table_name "Parent Table", c.table_name "Child Table",p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint" FROM user_constraints p JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name) WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U') AND c.constraint_type = 'R' AND p.table_name = UPPER('&table_name');

Enter value for table_name: TRANSACTION_TABLE

old 1: SELECT p.table_name "Parent Table", c.table_name "Child Table",p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint" FROM user_constraints p JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name) WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U') AND c.constraint_type = 'R' AND p.table_name = UPPER('&table_name')

new 1: SELECT p.table_name "Parent Table", c.table_name "Child Table",p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint" FROM user_constraints p JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name) WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U') AND c.constraint_type = 'R' AND p.table_name = UPPER('TRANSACTION_TABLE')

no rows selected

SQL> ALTER TABLE INFODBA.TRANSACTION_TABLE DISABLE CONSTRAINT PK_TRANSACTION_TBL cascade;

Table altered.

SQL>

SQL> col OWNER for a30

col CONSTRAINT_NAME for a30

col TABLE_NAME for a30

col INDEX_OWNER for a30

col INDEX_NAME for a30

select owner, constraint_name,table_name,index_owner,index_name from dba_constraints where (index_owner,index_name) in (select owner,index_name from dba_indexes where tablespace_name='IDATA');SQL> SQL> SQL> SQL> SQL>

no rows selected

SQL>

RMAN> RECOVER TABLESPACE idata,ilog UNTIL TIME "to_date('2019-08-07:17:16:24','YYYY-MM-DD:HH24:MI:SS')" AUXILIARY DESTINATION '/u01/rmanbkp/auxdest/';

Starting recover at 08-AUG-19

using channel ORA_DISK_1

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

Creating automatic instance, with SID='jquf'

initialization parameters used for automatic instance:

db_name=SMR1

db_unique_name=jquf_pitr_SMR1

compatible=12.1.0.2.0

db_block_size=8192

db_files=200

diagnostic_dest=/u01/app/oracle

_system_trig_enabled=FALSE

sga_target=1712M

processes=200

db_create_file_dest=/u01/rmanbkp/auxdest/

log_archive_dest_1='location=/u01/rmanbkp/auxdest/'

#No auxiliary parameter file used

starting up automatic instance SMR1

Oracle instance started

Total System Global Area 1795162112 bytes

Fixed Size 2925456 bytes

Variable Size 436210800 bytes

Database Buffers 1342177280 bytes

Redo Buffers 13848576 bytes

Automatic instance created

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('2019-08-07:17:16:24','YYYY-MM-DD:HH24:MI:SS')";

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

# 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 08-AUG-19

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=22 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 /u01/app/oracle/fast_recovery_area/SMR1/backupset/2019_08_07/o1_mf_ncsnf_DB_FULL_gnogh6n5_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/SMR1/backupset/2019_08_07/o1_mf_ncsnf_DB_FULL_gnogh6n5_.bkp tag=DB_FULL

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/rmanbkp/auxdest/SMR1/controlfile/o1_mf_gnqmgshn_.ctl

Finished restore at 08-AUG-19

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('2019-08-07:17:16:24','YYYY-MM-DD:HH24:MI:SS')";

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile 1 to new;

set newname for clone datafile 4 to new;

set newname for clone datafile 3 to new;

set newname for clone tempfile 1 to new;

set newname for datafile 5 to

"/u01/app/oracle/oradata/smr1/idata01.dbf";

set newname for datafile 2 to

"/u01/app/oracle/oradata/smr1/ilog01.dbf";

# 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, 2;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed tempfile 1 to /u01/rmanbkp/auxdest/SMR1/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 08-AUG-19

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/rmanbkp/auxdest/SMR1/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/rmanbkp/auxdest/SMR1/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/rmanbkp/auxdest/SMR1/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/smr1/idata01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/smr1/ilog01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/SMR1/backupset/2019_08_07/o1_mf_nnndf_DB_FULL_gnogcvyt_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/SMR1/backupset/2019_08_07/o1_mf_nnndf_DB_FULL_gnogcvyt_.bkp tag=DB_FULL

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:55

Finished restore at 08-AUG-19

datafile 1 switched to datafile copy

input datafile copy RECID=13 STAMP=1015764906 file name=/u01/rmanbkp/auxdest/SMR1/datafile/o1_mf_system_gnqmgzkr_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=14 STAMP=1015764906 file name=/u01/rmanbkp/auxdest/SMR1/datafile/o1_mf_undotbs1_gnqmgzl6_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=15 STAMP=1015764906 file name=/u01/rmanbkp/auxdest/SMR1/datafile/o1_mf_sysaux_gnqmgzkw_.dbf