Database and Datascience: Oracle 19c: Performing Tablespace Point -In-Time Recovery (TSPITR) to recover dropped tablespace in pluggable database using Recovery Manager (RMAN)

Oracle 19c: Performing Tablespace Point -In-Time Recovery (TSPITR) to recover dropped tablespace in pluggable database using Recovery Manager (RMAN)

Donatien MBADI,

Database Expert, OCP, OCE, Instructor

Overview of RMAN TSPITR

If you already know about database point in time recovery, the issue with the database point in time recovery (DBPITR) is that the database is not accessible to users.

Let us assume that you have a problem only with one user and the transaction that the user executed has impacted one table that reside under one tablespace or one data file. So rather than performing the entire database point in time recovery, we can perform single tablespace point in time recovery just before the transactions were issued. In this way, only the affected tablespace will not be available for the users. Rest all database will still be up and running.

To perform TSPITR for CDBs and PDBs, you must connect to the root as a user with SYSDBA or SYSBACKUP privilege. To perform TSPITR of one or more PDBs, you must have backup of the root and the CDB seed on the CDB that contains PDBs.

Purpose of RMAN 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.

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.

You can also use Flashback Database to rewind data, but you must rewind the entire database rather than just a subset. Also, unlike TSPITR, the Flashback Database feature necessitates the overhead of maintaining flashback logs. The point in time to which you can flash back the database is more limited than the TSPITR window, which extends back to your earliest recoverable backup.

Lab Activity

In this activity, we will be doing the tablespace point in time recovery in pluggable database. Let us start :

- Connect to the PDB database and create a tablespace

[oracle@uat-entdbrac05 ]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 28 15:59:27 2022

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 ORCLPDB01 MOUNTED

SQL> alter pluggable database ORCLPDB01 open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 ORCLPDB01 READ WRITE NO

SQL>

SQL> alter session set container=ORCLPDB01;

Session altered.

SQL>

SQL> create tablespace tbs_01 datafile '+DATA' size 1G autoextend on next 100M maxsize 10G;

Tablespace created.

SQL>

SQL> create user user01 identified by user01 default tablespace tbs_01 quota unlimited on tbs_01;

User created.

SQL> grant connect,resource to user01;

Grant succeeded.

SQL>

SQL> alter session set current_schema=user01;

Session altered.

SQL>

SQL> create table table01 as select * from hr.employees;

Table created.

SQL>

SQL> select count(*) from table01;

COUNT(*)

----------

107

SQL>

SQL> select sequence# from v$log where status='CURRENT';

SEQUENCE#

----------

- Make a backup of database including archivelog

[oracle@uat-entdbrac05 ~]$ vi rman.txt

run

{

allocate channel t1 device type disk;

backup database

format '/u01/app/oracle/backup/orclcdb/full_orclpdb01_%d_%s_%p_%t_%c';

release channel t1;

}

run

{

allocate channel t1 device type disk;

sql "alter system archive log current";

backup archivelog all

format '/u01/app/oracle/backup/orclcdb/archorclpdb01_%d_%s_%p_%t_%c'

delete input;

release channel t1;

}

[oracle@uat-entdbrac05 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Apr 28 16:57:22 2022

Version 19.3.0.0.0

connected to target database: ORCLCDB (DBID=2873114568)

RMAN> @rman.txt

RMAN> run

2> {

3> allocate channel t1 device type disk;

4> backup database

5> format '/u01/app/oracle/backup/orclcdb/full_orclpdb01_%d_%s_%p_%t_%c';

6> release channel t1;

7> }

using target database control file instead of recovery catalog

allocated channel: t1

channel t1: SID=5690 instance=orclcdb1 device type=DISK

Starting backup at 28-APR-22

channel t1: starting full datafile backup set

channel t1: specifying datafile(s) in backup set

input datafile file number=00003 name=+DATA/ORCLCDB/DATAFILE/sysaux.514.1102053083

input datafile file number=00001 name=+DATA/ORCLCDB/DATAFILE/system.513.1102053027

input datafile file number=00004 name=+DATA/ORCLCDB/DATAFILE/undotbs1.515.1102053107

input datafile file number=00009 name=+DATA/ORCLCDB/DATAFILE/undotbs2.525.1102054195

input datafile file number=00007 name=+DATA/ORCLCDB/DATAFILE/users.516.1102053109

channel t1: starting piece 1 at 28-APR-22

channel t1: finished piece 1 at 28-APR-22

piece handle=/u01/app/oracle/backup/orclcdb/full_orclpdb01_ORCLCDB_1_1_1103216256_1 tag=TAG20220428T165735 comment=NONE

channel t1: backup set complete, elapsed time: 00:00:45

channel t1: starting full datafile backup set

channel t1: specifying datafile(s) in backup set

input datafile file number=00015 name=+DATA/ORCLCDB/DCABA073737EF010E0531826420A82D6/DATAFILE/tbs_01.582.1103215287

input datafile file number=00011 name=+DATA/ORCLCDB/DCABA073737EF010E0531826420A82D6/DATAFILE/sysaux.531.1102055087

input datafile file number=00010 name=+DATA/ORCLCDB/DCABA073737EF010E0531826420A82D6/DATAFILE/system.530.1102055087

input datafile file number=00012 name=+DATA/ORCLCDB/DCABA073737EF010E0531826420A82D6/DATAFILE/undotbs1.529.1102055085

input datafile file number=00013 name=+DATA/ORCLCDB/DCABA073737EF010E0531826420A82D6/DATAFILE/undo_2.533.1102055131

input datafile file number=00014 name=+DATA/ORCLCDB/DCABA073737EF010E0531826420A82D6/DATAFILE/users.534.1102055159

channel t1: starting piece 1 at 28-APR-22

channel t1: finished piece 1 at 28-APR-22

piece handle=/u01/app/oracle/backup/orclcdb/full_orclpdb01_ORCLCDB_2_1_1103216302_1 tag=TAG20220428T165735 comment=NONE

channel t1: backup set complete, elapsed time: 00:00:15

channel t1: starting full datafile backup set

channel t1: specifying datafile(s) in backup set

input datafile file number=00006 name=+DATA/ORCLCDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.522.1102053725

input datafile file number=00005 name=+DATA/ORCLCDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.521.1102053725

input datafile file number=00008 name=+DATA/ORCLCDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.523.1102053725

channel t1: starting piece 1 at 28-APR-22

channel t1: finished piece 1 at 28-APR-22

piece handle=/u01/app/oracle/backup/orclcdb/full_orclpdb01_ORCLCDB_3_1_1103216317_1 tag=TAG20220428T165735 comment=NONE

channel t1: backup set complete, elapsed time: 00:00:07

Finished backup at 28-APR-22

Starting Control File and SPFILE Autobackup at 28-APR-22

piece handle=+FRA/ORCLCDB/AUTOBACKUP/2022_04_28/s_1103216325.572.1103216327 comment=NONE

Finished Control File and SPFILE Autobackup at 28-APR-22

released channel: t1

RMAN> run

2> {

3> allocate channel t1 device type disk;

4> sql "alter system archive log current";

5> backup archivelog all

6> format '/u01/app/oracle/backup/orclcdb/archorclpdb01_%d_%s_%p_%t_%c'

7> delete input;

8> release channel t1;

9> }

allocated channel: t1

channel t1: SID=5690 instance=orclcdb1 device type=DISK

sql statement: alter system archive log current

Starting backup at 28-APR-22

current log archived

channel t1: starting archived log backup set

channel t1: specifying archived log(s) in backup set

input archived log thread=2 sequence=21 RECID=1 STAMP=1103216208

input archived log thread=1 sequence=25 RECID=2 STAMP=1103216332

input archived log thread=1 sequence=26 RECID=3 STAMP=1103216332

channel t1: starting piece 1 at 28-APR-22

channel t1: finished piece 1 at 28-APR-22

piece handle=/u01/app/oracle/backup/orclcdb/archorclpdb01_ORCLCDB_5_1_1103216333_1 tag=TAG20220428T165853 comment=NONE

channel t1: backup set complete, elapsed time: 00:00:03

channel t1: deleting archived log(s)

archived log file name=+FRA/ORCLCDB/ARCHIVELOG/2022_04_28/thread_2_seq_21.573.1103216209 RECID=1 STAMP=1103216208

archived log file name=+FRA/ORCLCDB/ARCHIVELOG/2022_04_28/thread_1_seq_25.566.1103216331 RECID=2 STAMP=1103216332

archived log file name=+FRA/ORCLCDB/ARCHIVELOG/2022_04_28/thread_1_seq_26.564.1103216333 RECID=3 STAMP=1103216332

Finished backup at 28-APR-22

Starting Control File and SPFILE Autobackup at 28-APR-22

piece handle=+FRA/ORCLCDB/AUTOBACKUP/2022_04_28/s_1103216337.564.1103216339 comment=NONE

Finished Control File and SPFILE Autobackup at 28-APR-22

released channel: t1

RMAN> **end-of-file**

RMAN>

- Drop the tablespace

[oracle@uat-entdbrac05 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 28 17:01:10 2022

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> alter session set container=cdb$root;

Session altered.

SQL>

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL>

SQL> alter session set container=ORCLPDB01;

Session altered.

SQL>

SQL> drop tablespace tbs_01 including contents and datafiles;

Tablespace dropped.

SQL> select thread#,sequence# from v$log where status='CURRENT';

THREAD# SEQUENCE#

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

1 29

SQL>

SQL> select count(*) from table01;

select count(*) from table01

ERROR at line 1:

ORA-00942: table or view does not exist

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 NO

UNDOTBS2 ONLINE NO

6 rows selected.

- Use RMAN to recover the cropped tablespace

[oracle@uat-entdbrac05 ~]$ mkdir -p /tmp/tbs_01rest

[oracle@uat-entdbrac05 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Apr 28 17:09:10 2022

Version 19.3.0.0.0

connected to target database: ORCLCDB (DBID=2873114568)

RMAN> recover tablespace ORCLPDB01:tbs_01 until sequence 29 auxiliary destination '/tmp/tbs_01rest';

Starting recover at 28-APR-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=2301 instance=orclcdb1 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 ORCLPDB01:SYSTEM

Tablespace UNDOTBS1

Tablespace ORCLPDB01:UNDOTBS1

Tablespace UNDOTBS2

Tablespace ORCLPDB01:UNDO_2

Creating automatic instance, with SID='deuD'

initialization parameters used for automatic instance:

db_name=ORCLCDB

db_unique_name=deuD_pitr_ORCLPDB01_ORCLCDB

compatible=19.0.0

db_block_size=8192

db_files=200

diagnostic_dest=/u01/app/oracle

_system_trig_enabled=FALSE

sga_target=10240M

processes=200

db_create_file_dest=/tmp/tbs_01rest

log_archive_dest_1='location=/tmp/tbs_01rest'

enable_pluggable_database=true

_clone_one_pdb_recovery=true

#No auxiliary parameter file used

starting up automatic instance ORCLCDB

Oracle instance started

Total System Global Area 10737417784 bytes

Fixed Size 12684856 bytes

Variable Size 1711276032 bytes

Database Buffers 8959033344 bytes

Redo Buffers 54423552 bytes

Automatic instance created

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

Tablespace ORCLPDB01:TBS_01

contents of Memory Script:

{

# set requested point in time

set until logseq 29 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 28-APR-22

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=325 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 +FRA/ORCLCDB/AUTOBACKUP/2022_04_28/s_1103216337.564.1103216339

channel ORA_AUX_DISK_1: piece handle=+FRA/ORCLCDB/AUTOBACKUP/2022_04_28/s_1103216337.564.1103216339 tag=TAG20220428T165857

channel ORA_AUX_DISK_1: restored backup piece 1

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

output file name=/tmp/tbs_01rest/ORCLCDB/controlfile/o1_mf_k6oh5btk_.ctl

Finished restore at 28-APR-22

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 29 thread 1;

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile 1 to new;

set newname for clone datafile 10 to new;

set newname for clone datafile 4 to new;

set newname for clone datafile 12 to new;

set newname for clone datafile 9 to new;

set newname for clone datafile 13 to new;

set newname for clone datafile 3 to new;

set newname for clone datafile 11 to new;

set newname for clone tempfile 1 to new;

set newname for clone tempfile 3 to new;

set newname for datafile 15 to new;

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 1, 10, 4, 12, 9, 13, 3, 11, 15;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed tempfile 1 to /tmp/tbs_01rest/ORCLCDB/datafile/o1_mf_temp_%u_.tmp in control file

renamed tempfile 3 to /tmp/tbs_01rest/ORCLCDB/DCABA073737EF010E0531826420A82D6/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 28-APR-22

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 /tmp/tbs_01rest/ORCLCDB/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /tmp/tbs_01rest/ORCLCDB/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00009 to /tmp/tbs_01rest/ORCLCDB/datafile/o1_mf_undotbs2_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/tbs_01rest/ORCLCDB/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orclcdb/full_orclpdb01_ORCLCDB_1_1_1103216256_1

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/orclcdb/full_orclpdb01_ORCLCDB_1_1_1103216256_1 tag=TAG20220428T165735

channel ORA_AUX_DISK_1: restored backup piece 1

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

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 00010 to /tmp/tbs_01rest/ORCLCDB/DCABA073737EF010E0531826420A82D6/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00012 to /tmp/tbs_01rest/ORCLCDB/DCABA073737EF010E0531826420A82D6/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00013 to /tmp/tbs_01rest/ORCLCDB/DCABA073737EF010E0531826420A82D6/datafile/o1_mf_undo_2_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00011 to /tmp/tbs_01rest/ORCLCDB/DCABA073737EF010E0531826420A82D6/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00015 to +DATA

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orclcdb/full_orclpdb01_ORCLCDB_2_1_1103216302_1

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/orclcdb/full_orclpdb01_ORCLCDB_2_1_1103216302_1 tag=TAG20220428T165735

channel ORA_AUX_DISK_1: restored backup piece 1

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

Finished restore at 28-APR-22

datafile 15 switched to datafile copy

input datafile copy RECID=13 STAMP=1103217204 file name=+DATA/ORCLCDB/DCABA073737EF010E0531826420A82D6/DATAFILE/tbs_01.582.1103217169

datafile 1 switched to datafile copy

input datafile copy RECID=14 STAMP=1103217204 file name=/tmp/tbs_01rest/ORCLCDB/datafile/o1_mf_system_k6oh5qv4_.dbf

datafile 10 switched to datafile copy

input datafile copy RECID=15 STAMP=1103217205 file name=/tmp/tbs_01rest/ORCLCDB/DCABA073737EF010E0531826420A82D6/datafile/o1_mf_system_k6oh80cr_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=16 STAMP=1103217205 file name=/tmp/tbs_01rest/ORCLCDB/datafile/o1_mf_undotbs1_k6oh5qyt_.dbf

datafile 12 switched to datafile copy

input datafile copy RECID=17 STAMP=1103217205 file name=/tmp/tbs_01rest/ORCLCDB/DCABA073737EF010E0531826420A82D6/datafile/o1_mf_undotbs1_k6oh80fp_.dbf

datafile 9 switched to datafile copy

input datafile copy RECID=18 STAMP=1103217205 file name=/tmp/tbs_01rest/ORCLCDB/datafile/o1_mf_undotbs2_k6oh5r2g_.dbf

datafile 13 switched to datafile copy

input datafile copy RECID=19 STAMP=1103217205 file name=/tmp/tbs_01rest/ORCLCDB/DCABA073737EF010E0531826420A82D6/datafile/o1_mf_undo_2_k6oh80gg_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=20 STAMP=1103217205 file name=/tmp/tbs_01rest/ORCLCDB/datafile/o1_mf_sysaux_k6oh5qhf_.dbf

datafile 11 switched to datafile copy

input datafile copy RECID=21 STAMP=1103217206 file name=/tmp/tbs_01rest/ORCLCDB/DCABA073737EF010E0531826420A82D6/datafile/o1_mf_sysaux_k6oh80b3_.dbf

contents of Memory Script:

{

# set requested point in time

set until logseq 29 thread 1;

# online the datafiles restored or switched

sql clone "alter database datafile 1 online";