RMAN-20202 & RMAN-06019 Errors During RMAN Tablespace Restore

These errors are occured when we try to restore a dropped tablespace. If we drop a tablespace then the controlfile will not keep any records related to the dropped tablespace. If we try to use the RMAN RESTORE or RECOVER TABLESPACE command, it will get the RMAN-20202, RMAN-06019 errors.

Let’s do a test

# sqlplus / as sysdba

SQL> create tablespace TS_TEST datafile ‘/data/test11g/ts_test.dbf’ size 10M autoextend on;

# rman target /

RMAN> backup tablespace TS_TEST;

Starting backup at 23-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00031 name=/data/test11g/ts_test.dbf
channel ORA_DISK_1: starting piece 1 at 23-AUG-12
channel ORA_DISK_1: finished piece 1 at 23-AUG-12
piece handle=/oracle/yedek/bck_test11g/a8njcggd_1_1.bck tag=TAG20120823T153645 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-AUG-12
Starting Control File Autobackup at 23-AUG-12
piece handle=/oracle/yedek/bck_test11g/cf_sp_file_c-1532875336-20120823-01 comment=NONE
Finished Control File Autobackup at 23-AUG-12

# sqlplus / as sysdba

SQL> drop tablespace TS_TEST including contents and datafiles;

# rman target /

RMAN> restore tablespace TS_TEST;

Starting restore at 23-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=136 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/23/2012 15:38:40
RMAN-20202: tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name “TS_TEST”

To recover a dropped tablespace, we must do a point in time recovery of the whole database until the time the tablespace was dropped.
A tablespace backup is good if you have a media error related to tablespace.

Now let’s do another test

# sqlplus / as sysdba

SQL> create tablespace TS_TEST datafile ‘/data/test11g/ts_test.dbf’ size 10M autoextend on;

# rman target /

RMAN> backup tablespace TS_TEST;

Starting backup at 23-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00031 name=/data/test11g/ts_test.dbf
channel ORA_DISK_1: starting piece 1 at 23-AUG-12
channel ORA_DISK_1: finished piece 1 at 23-AUG-12
piece handle=/oracle/yedek/bck_test11g/acnjch45_1_1.bck tag=TAG20120823T154717 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-AUG-12
Starting Control File Autobackup at 23-AUG-12
piece handle=/oracle/yedek/bck_test10g/cf_sp_file_c-1532875336-20120823-04 comment=NONE
Finished Control File Autobackup at 23-AUG-12

Remove datafile belong to TS_TEST tablespace in operating system

# rm /data/test11g/ts_test.dbf

Now, let’s try to create a table in TS_TEST tablespace

SQL> create table talip_test (id number) tablespace TS_TEST;
create table talip_test (id number) tablespace TS_TEST
*
ERROR at line 1:
ORA-01116: error in opening database file 31
ORA-01110: data file 31: ‘/data/test10g/ts_test.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

There is a media error and we must restore tablespace backup.

# rman target /

RMAN> sql “alter database datafile 31 offline”;

sql statement: alter database datafile 31 offline

RMAN> restore tablespace TS_TEST;

Starting restore at 23-AUG-12
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00031 to /data/test11g/ts_test.dbf
channel ORA_DISK_1: reading from backup piece /oracle/yedek/bck_test11g/acnjch45_1_1.bck
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/yedek/bck_test11g/acnjch45_1_1.bck tag=TAG20120823T154717
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 23-AUG-12

RMAN> recover tablespace TS_TEST;

Starting recover at 23-AUG-12
using channel ORA_DISK_1

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

Finished recover at 23-AUG-12

RMAN> sql “alter database datafile 31 online”;

sql statement: alter database datafile 31 online