Oracle 19c: Performing Tablespace Point -In-Time Recovery (TSPITR) to
recover dropped tablespace in pluggable database using Recovery Manager
(RMAN)
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
Copyright (c) 1982, 2019, Oracle. All rights reserved.
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>
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#
----------
25
-
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
Copyright (c) 1982, 2019, Oracle and/or its
affiliates. All rights reserved.
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
Copyright (c) 1982, 2019, Oracle. All rights reserved.
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
Copyright (c) 1982, 2019, Oracle and/or its
affiliates. All rights reserved.
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
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
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";
sql clone 'ORCLPDB01' "alter database datafile
10
online";
sql clone "alter database datafile 4 online";
sql clone 'ORCLPDB01' "alter database datafile
12
online";
sql clone "alter database datafile 9 online";
sql clone 'ORCLPDB01' "alter database datafile
13
online";
sql clone "alter database datafile 3 online";
sql clone 'ORCLPDB01' "alter database datafile
11
online";
sql clone 'ORCLPDB01' "alter database datafile
15
online";
# recover and open resetlogs
recover clone database tablespace "ORCLPDB01":"TBS_01",
"SYSTEM", "ORCLPDB01":"SYSTEM",
"UNDOTBS1", "ORCLPDB01":"UNDOTBS1", "UNDOTBS2",
"ORCLPDB01":"UNDO_2", "SYSAUX",
"ORCLPDB01":"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 10 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 12 online
sql statement: alter database datafile 9 online
sql statement: alter database datafile 13 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 11 online
sql statement: alter database datafile 15 online
Starting recover at 28-APR-22
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 27 is
already on disk as file
+FRA/ORCLCDB/ARCHIVELOG/2022_04_28/thread_1_seq_27.566.1103216505
archived log for thread 1 with sequence 28 is
already on disk as file +FRA/ORCLCDB/ARCHIVELOG/2022_04_28/thread_1_seq_28.573.1103216509
channel ORA_AUX_DISK_1: starting archived log
restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=25
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=26
channel ORA_AUX_DISK_1: reading from backup piece
/u01/app/oracle/backup/orclcdb/archorclpdb01_ORCLCDB_5_1_1103216333_1
channel ORA_AUX_DISK_1: piece
handle=/u01/app/oracle/backup/orclcdb/archorclpdb01_ORCLCDB_5_1_1103216333_1
tag=TAG20220428T165853
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed
time: 00:00:01
archived log file
name=/tmp/tbs_01rest/1_25_1102053196.dbf thread=1 sequence=25
channel clone_default: deleting archived log(s)
archived log file name=/tmp/tbs_01rest/1_25_1102053196.dbf
RECID=5 STAMP=1103217220
archived log file
name=/tmp/tbs_01rest/1_26_1102053196.dbf thread=1 sequence=26
channel clone_default: deleting archived log(s)
archived log file
name=/tmp/tbs_01rest/1_26_1102053196.dbf RECID=4 STAMP=1103217219
archived log file
name=+FRA/ORCLCDB/ARCHIVELOG/2022_04_28/thread_1_seq_27.566.1103216505 thread=1
sequence=27
archived log file
name=+FRA/ORCLCDB/ARCHIVELOG/2022_04_28/thread_1_seq_28.573.1103216509 thread=1
sequence=28
media recovery complete, elapsed time: 00:00:03
Finished recover at 28-APR-22
database opened
contents of Memory Script:
{
sql clone 'alter pluggable database ORCLPDB01 open';
}
executing Memory Script
sql statement: alter pluggable database ORCLPDB01 open
contents of Memory Script:
{
# make read only the tablespace that will be
exported
sql clone 'ORCLPDB01' 'alter tablespace
TBS_01 read
only';
# create directory for datapump import
sql 'ORCLPDB01' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/tmp/tbs_01rest''";
# create directory for datapump export
sql clone 'ORCLPDB01' "create or replace
directory
TSPITR_DIROBJ_DPDIR as ''
/tmp/tbs_01rest''";
}
executing Memory Script
sql statement: alter tablespace TBS_01 read only
sql statement: create or replace directory
TSPITR_DIROBJ_DPDIR as ''/tmp/tbs_01rest''
sql statement: create or replace directory
TSPITR_DIROBJ_DPDIR as ''/tmp/tbs_01rest''
Performing export of metadata...
EXPDP>
Starting "SYS"."TSPITR_EXP_deuD_puep":
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_deuD_puep" successfully
loaded/unloaded
EXPDP>
******************************************************************************
EXPDP>
Dump file set for SYS.TSPITR_EXP_deuD_puep is:
EXPDP>
/tmp/tbs_01rest/tspitr_deuD_59875.dmp
EXPDP>
******************************************************************************
EXPDP>
Datafiles required for transportable tablespace TBS_01:
EXPDP>
+DATA/ORCLCDB/DCABA073737EF010E0531826420A82D6/DATAFILE/tbs_01.582.1103217169
EXPDP>
Job "SYS"."TSPITR_EXP_deuD_puep" successfully completed at
Thu Apr 28 17:16:21 2022 elapsed 0 00:01:26
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_deuD_sDen" successfully
loaded/unloaded
IMPDP>
Starting "SYS"."TSPITR_IMP_deuD_sDen":
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_deuD_sDen" successfully completed at
Thu Apr 28 17:18:07 2022 elapsed 0 00:01:12
Import completed
contents of Memory Script:
{
# make read write and offline the imported
tablespaces
sql 'ORCLPDB01' 'alter tablespace
TBS_01 read
write';
sql 'ORCLPDB01' 'alter tablespace
TBS_01
offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE);
end;';
}
executing Memory Script
sql statement: alter tablespace TBS_01 read write
sql statement: alter tablespace TBS_01 offline
sql statement: begin
dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file
/tmp/tbs_01rest/ORCLCDB/DCABA073737EF010E0531826420A82D6/datafile/o1_mf_temp_k6ohblfs_.tmp
deleted
auxiliary instance file
/tmp/tbs_01rest/ORCLCDB/datafile/o1_mf_temp_k6ohbbm0_.tmp deleted
auxiliary instance file /tmp/tbs_01rest/ORCLCDB/onlinelog/o1_mf_4_k6oh9slk_.log
deleted
auxiliary instance file
/tmp/tbs_01rest/ORCLCDB/onlinelog/o1_mf_3_k6oh9sjz_.log deleted
auxiliary instance file
/tmp/tbs_01rest/ORCLCDB/onlinelog/o1_mf_2_k6oh9sh0_.log deleted
auxiliary instance file /tmp/tbs_01rest/ORCLCDB/onlinelog/o1_mf_1_k6oh9sff_.log
deleted
auxiliary instance file
/tmp/tbs_01rest/ORCLCDB/DCABA073737EF010E0531826420A82D6/datafile/o1_mf_sysaux_k6oh80b3_.dbf
deleted
auxiliary instance file
/tmp/tbs_01rest/ORCLCDB/datafile/o1_mf_sysaux_k6oh5qhf_.dbf deleted
auxiliary instance file
/tmp/tbs_01rest/ORCLCDB/DCABA073737EF010E0531826420A82D6/datafile/o1_mf_undo_2_k6oh80gg_.dbf
deleted
auxiliary instance file
/tmp/tbs_01rest/ORCLCDB/datafile/o1_mf_undotbs2_k6oh5r2g_.dbf deleted
auxiliary instance file
/tmp/tbs_01rest/ORCLCDB/DCABA073737EF010E0531826420A82D6/datafile/o1_mf_undotbs1_k6oh80fp_.dbf
deleted
auxiliary instance file
/tmp/tbs_01rest/ORCLCDB/datafile/o1_mf_undotbs1_k6oh5qyt_.dbf deleted
auxiliary instance file
/tmp/tbs_01rest/ORCLCDB/DCABA073737EF010E0531826420A82D6/datafile/o1_mf_system_k6oh80cr_.dbf
deleted
auxiliary instance file
/tmp/tbs_01rest/ORCLCDB/datafile/o1_mf_system_k6oh5qv4_.dbf deleted
auxiliary instance file
/tmp/tbs_01rest/ORCLCDB/controlfile/o1_mf_k6oh5btk_.ctl deleted
auxiliary instance file tspitr_deuD_59875.dmp
deleted
Finished recover at 28-APR-22
RMAN>
-
Check
the status of the tablespace
SQL> alter session set container=ORCLPDB01;
Session 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
UNDO_2 ONLINE NO
USERS ONLINE NO
TBS_01 OFFLINE YES
7 rows selected.
SQL>
SQL> select count(*) from table01;
select count(*) from table01
*
ERROR at line 1:
ORA-00376: file 17 cannot be read at this time
ORA-01110: data file 17:
'+DATA/ORCLCDB/DCABA073737EF010E0531826420A82D6/DATAFILE/tbs_01.582.1103217957'
-
Make
the tablespace online
SQL> alter tablespace TBS_01 online;
Tablespace altered.
SQL> select count(*) from table01;
COUNT(*)
----------
107
SQL>
