Oracle Database 19c Table Space Point in Time Recovery for Pluggable Database

In this post I am creating a scenario for table space point in time recovery.

RMAN automatic Tablespace Point-In-Time Recovery ( TSPITR) enables you to quickly recover one or more tablespaces in an Oracle database to an earlier time, without affecting the state of the rest of the tablespaces and other objects in the database.

Prerequisites :-

1) Enable Database in Archive mode.

2) Take a full database backup

3) Make sure the table space is not default permanent table space

Change it as below

alter database default tablespace system;

Steps:

1) Lets connect to my pluggable database ORCLPDB and create few objects.

First I am creating a user in my PDB database.

[oracle@dbserver arch]$ sqlplus '/as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jun 10 07:02:23 2023
Version 19.14.0.0.0

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

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

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO

SQL> alter session set container=ORCLPDB;

Session altered.

SQL> create user himanshu identified by oracle123;

User created.

SQL> grant connect,resource to himanshu;

Grant succeeded.

SQL> ALTER USER himanshu quota unlimited on users;

User altered.

Now lets connect to the new user and create a table with some data.

[oracle@dbserver ~]$ sqlplus himanshu/oracle123@orclpdb

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

SQL> show user
USER is "HIMANSHU"
SQL> create table emp(name varchar2(20));

Table created.

SQL> BEGIN
FOR k IN 1..1100 LOOP
Insert into emp values('dummyuser');
END LOOP;
END;
/ 2 3 4 5 6

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select count(*) from himanshu.emp;

COUNT(*)
----------
1100

I created a backup table as EMP_RESTORE as well.

2) Now lets see the object in USERS Tablespace

oracle@dbserver ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jun 11 06:19:22 2023
Version 19.14.0.0.0

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

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

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL> alter session set container=ORCLPDB;

Session altered.

SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE from dba_segments where TABLESPACE_NAME='USERS';

OWNER SEGMENT_NAME SEGMENT_TYPE
--------------- -------------------- ------------------
HIMANSHU EMP TABLE
HIMANSHU EMP_RESTORE TABLE

3) Check the current sequence number

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/19c/dbhome_1/dbs/arch
Oldest online log sequence 24
Next log sequence to archive 26
Current log sequence 26

4) Lets drop the users table space in PDB. Make sure it is not the default permanent table space.

SQL> drop tablespace users including contents and datafiles;

Tablespace dropped.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP

It is dropped now.

5) Make sure archives being backed up.

[oracle@dbserver ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jun 11 06:26:11 2023
Version 19.14.0.0.0

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

connected to target database: ORCL (DBID=1666823206)

RMAN> backup archivelog all;

Starting backup at 11-JUN-23
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=144 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=20 RECID=4 STAMP=1139125489
input archived log thread=1 sequence=21 RECID=5 STAMP=1139125529
input archived log thread=1 sequence=22 RECID=6 STAMP=1139125874
input archived log thread=1 sequence=23 RECID=7 STAMP=1139205619
input archived log thread=1 sequence=24 RECID=8 STAMP=1139206378
input archived log thread=1 sequence=25 RECID=9 STAMP=1139206447
input archived log thread=1 sequence=26 RECID=10 STAMP=1139207178
channel ORA_DISK_1: starting piece 1 at 11-JUN-23
channel ORA_DISK_1: finished piece 1 at 11-JUN-23
piece handle=/u01/app/oracle/product/19c/dbhome_1/dbs/0l1udr0b_21_1_1 tag=TAG20230611T062619 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 11-JUN-23

Starting Control File and SPFILE Autobackup at 11-JUN-23
piece handle=/u01/app/oracle/product/19c/dbhome_1/dbs/c-1666823206-20230611-01 comment=NONE
Finished Control File and SPFILE Autobackup at 11-JUN-23

6) Now let's identify the log sequence timestamp.

[oracle@dbserver ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jun 11 06:28:13 2023
Version 19.14.0.0.0

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

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

SQL> select sequence#,first_change#, to_char(first_time,'HH24:MI:SS') from v$log order by 3;

SEQUENCE# FIRST_CHANGE# TO_CHAR(
---------- ------------- --------
25 2922665 06:12:58
26 2922724 06:14:07
27 2923323 06:26:18

7) As per information we know table space was dropped around some where before 6:26.

We can restore until timestamp or until sequence. I will table sequence 26 as the recovery point.

Make sure that the pluggable database is open in read write mode.

Make a temporary folder.

mkdir -p /u01/app/backup

[oracle@dbserver ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jun 11 08:05:09 2023
Version 19.14.0.0.0

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

connected to target database: ORCL (DBID=1666823206)

RMAN> recover tablespace ORCLPDB:USERS until sequence 26 auxiliary destination '/u01/app/backup';

Starting recover at 11-JUN-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 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 ORCLPDB:SYSTEM
Tablespace UNDOTBS1
Tablespace ORCLPDB:UNDOTBS1

Creating automatic instance, with SID='tgEF'

initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=tgEF_pitr_ORCLPDB_ORCL
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=4720M
processes=200
db_create_file_dest=/u01/app/backup
log_archive_dest_1='location=/u01/app/backup'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used

starting up automatic instance ORCL

Oracle instance started

Total System Global Area 4949277048 bytes

Fixed Size 9144696 bytes
Variable Size 889192448 bytes
Database Buffers 4043309056 bytes
Redo Buffers 7630848 bytes
Automatic instance created

List of tablespaces that have been dropped from the target database:
Tablespace ORCLPDB:USERS

contents of Memory Script:
{
# set requested point in time
set until logseq 26 thread 1;
# 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 11-JUN-23
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=9 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/product/19c/dbhome_1/dbs/c-1666823206-20230610-02
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/19c/dbhome_1/dbs/c-1666823206-20230610-02 tag=TAG20230610T074531
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/app/backup/ORCL/controlfile/o1_mf_l8c023tm_.ctl
Finished restore at 11-JUN-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 logseq 26 thread 1;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 11 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 10 to new;
set newname for clone tempfile 1 to new;
set newname for clone tempfile 3 to new;
set newname for datafile 12 to
"/u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 9, 4, 11, 3, 10, 12;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/backup/ORCL/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /u01/app/backup/ORCL/FDAFACE81836315CE055000017019382/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 11-JUN-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 00001 to /u01/app/backup/ORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/backup/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/backup/ORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/19c/dbhome_1/dbs/0g1udq7e_16_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/19c/dbhome_1/dbs/0g1udq7e_16_1_1 tag=TAG20230611T061301
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/app/backup/ORCL/FDAFACE81836315CE055000017019382/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/app/backup/ORCL/FDAFACE81836315CE055000017019382/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/app/backup/ORCL/FDAFACE81836315CE055000017019382/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/19c/dbhome_1/dbs/0h1udq8h_17_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/19c/dbhome_1/dbs/0h1udq8h_17_1_1 tag=TAG20230611T061301
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 11-JUN-23

datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=1139213190 file name=/u01/app/backup/ORCL/datafile/o1_mf_system_l8c02bjm_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1139213190 file name=/u01/app/backup/ORCL/FDAFACE81836315CE055000017019382/datafile/o1_mf_system_l8c03xxk_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=1139213190 file name=/u01/app/backup/ORCL/datafile/o1_mf_undotbs1_l8c02bjz_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=1139213190 file name=/u01/app/backup/ORCL/FDAFACE81836315CE055000017019382/datafile/o1_mf_undotbs1_l8c043jh_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=1139213190 file name=/u01/app/backup/ORCL/datafile/o1_mf_sysaux_l8c02bjs_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=15 STAMP=1139213190 file name=/u01/app/backup/ORCL/FDAFACE81836315CE055000017019382/datafile/o1_mf_sysaux_l8c03qm4_.dbf

contents of Memory Script:
{
# set requested point in time
set until logseq 26 thread 1;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone 'ORCLPDB' "alter database datafile
9 online";
sql clone "alter database datafile 4 online";
sql clone 'ORCLPDB' "alter database datafile
11 online";
sql clone "alter database datafile 3 online";
sql clone 'ORCLPDB' "alter database datafile
10 online";
sql clone 'ORCLPDB' "alter database datafile
12 online";
# recover and open resetlogs
recover clone database tablespace "ORCLPDB":"USERS", "SYSTEM", "ORCLPDB":"SYSTEM", "UNDOTBS1", "ORCLPDB":"UNDOTBS1", "SYSAUX", "ORCLPDB":"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 9 online

sql statement: alter database datafile 4 online

sql statement: alter database datafile 11 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 10 online

sql statement: alter database datafile 12 online

Starting recover at 11-JUN-23
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 22 is already on disk as file /u01/app/oracle/product/19c/dbhome_1/dbs/arch/1_22_1139042473.dbf
archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/product/19c/dbhome_1/dbs/arch/1_23_1139042473.dbf
archived log for thread 1 with sequence 24 is already on disk as file /u01/app/oracle/product/19c/dbhome_1/dbs/arch/1_24_1139042473.dbf
archived log for thread 1 with sequence 25 is already on disk as file /u01/app/oracle/product/19c/dbhome_1/dbs/arch/1_25_1139042473.dbf
archived log file name=/u01/app/oracle/product/19c/dbhome_1/dbs/arch/1_22_1139042473.dbf thread=1 sequence=22
archived log file name=/u01/app/oracle/product/19c/dbhome_1/dbs/arch/1_23_1139042473.dbf thread=1 sequence=23
archived log file name=/u01/app/oracle/product/19c/dbhome_1/dbs/arch/1_24_1139042473.dbf thread=1 sequence=24
archived log file name=/u01/app/oracle/product/19c/dbhome_1/dbs/arch/1_25_1139042473.dbf thread=1 sequence=25
media recovery complete, elapsed time: 00:00:01
Finished recover at 11-JUN-23

database opened

contents of Memory Script:
{
sql clone 'alter pluggable database ORCLPDB open';
}
executing Memory Script

sql statement: alter pluggable database ORCLPDB open

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

sql statement: alter tablespace USERS read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/backup''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/backup''

Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_tgEF_fDui":
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_tgEF_fDui" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_tgEF_fDui is:
EXPDP> /u01/app/backup/tspitr_tgEF_29031.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace USERS:
EXPDP> /u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf
EXPDP> Job "SYS"."TSPITR_EXP_tgEF_fDui" successfully completed at Sun Jun 11 08:07:26 2023 elapsed 0 00:00:36
Export completed

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

Oracle instance shut down

Performing import of metadata...
IMPDP> Master table "SYS"."TSPITR_IMP_tgEF_Blzj" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_tgEF_Blzj":
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_tgEF_Blzj" successfully completed at Sun Jun 11 08:07:58 2023 elapsed 0 00:00:26
Import completed

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

sql statement: alter tablespace USERS read write

sql statement: alter tablespace USERS offline

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

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/backup/ORCL/FDAFACE81836315CE055000017019382/datafile/o1_mf_temp_l8c04l69_.tmp deleted
auxiliary instance file /u01/app/backup/ORCL/datafile/o1_mf_temp_l8c04jdr_.tmp deleted
auxiliary instance file /u01/app/backup/ORCL/onlinelog/o1_mf_3_l8c04b6y_.log deleted
auxiliary instance file /u01/app/backup/ORCL/onlinelog/o1_mf_2_l8c04b6f_.log deleted
auxiliary instance file /u01/app/backup/ORCL/onlinelog/o1_mf_1_l8c04b61_.log deleted
auxiliary instance file /u01/app/backup/ORCL/FDAFACE81836315CE055000017019382/datafile/o1_mf_sysaux_l8c03qm4_.dbf deleted
auxiliary instance file /u01/app/backup/ORCL/datafile/o1_mf_sysaux_l8c02bjs_.dbf deleted
auxiliary instance file /u01/app/backup/ORCL/FDAFACE81836315CE055000017019382/datafile/o1_mf_undotbs1_l8c043jh_.dbf deleted
auxiliary instance file /u01/app/backup/ORCL/datafile/o1_mf_undotbs1_l8c02bjz_.dbf deleted
auxiliary instance file /u01/app/backup/ORCL/FDAFACE81836315CE055000017019382/datafile/o1_mf_system_l8c03xxk_.dbf deleted
auxiliary instance file /u01/app/backup/ORCL/datafile/o1_mf_system_l8c02bjm_.dbf deleted
auxiliary instance file /u01/app/backup/ORCL/controlfile/o1_mf_l8c023tm_.ctl deleted
auxiliary instance file tspitr_tgEF_29031.dmp deleted
Finished recover at 11-JUN-23

8) Rman uses transportable tablespace mechanism to plug the dropped tablespace back in to the database.

SQL> select tablespace_name,status,plugged_in from dba_tablespaces;

TABLESPACE_NAME STATUS PLU
------------------------------ --------- ---
SYSTEM ONLINE NO
SYSAUX ONLINE NO
UNDOTBS1 ONLINE NO
TEMP ONLINE NO
USERS OFFLINE YES

SQL> alter tablespace users online;

Tablespace altered.

SQL> select tablespace_name,status,plugged_in from dba_tablespaces;

TABLESPACE_NAME STATUS PLU
------------------------------ --------- ---
SYSTEM ONLINE NO
SYSAUX ONLINE NO
UNDOTBS1 ONLINE NO
TEMP ONLINE NO
USERS ONLINE YES

9) Let's validate the data from the tables;

SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE from dba_segments where TABLESPACE_NAME='USERS';

OWNER SEGMENT_NAME SEGMENT_TYPE
--------------- --------------- ------------------
HIMANSHU EMP TABLE
HIMANSHU EMP_RESTORE TABLE

SQL> select count(*) from himanshu.emp;

COUNT(*)
----------
1100

SQL> select count(*) from himanshu.EMP_RESTORE;

COUNT(*)
----------
1100