Restore and Recovery the dropped tablespace in Oracle (RMAN-20202)

When you try to restore the tablespace, then its show following error because current control file does not have information of the tablespace. So, you need to first restore the old control file.

RMAN-20202: tablespace not found in the recovery catalog

We have two option to restore table space which is dropped:

1. Recover point in time recovery upto drop command fire.
2. Clone database at new location and transfer table from that database by export or import utility

We are following first method to restore tablespace:

  1. Before Using point in time recovery to restore tablespace ,First I create Tablespace in Oracle database.

SQL> create tablespace TS_TEST datafile ‘/u01/app/oracle/oradata/mydb/ts_test.dbf’ size 10M autoextend on;

Tablespace created.

SQL> select name from v$datafile;

NAME

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

/u01/app/oracle/oradata/mydb/system01.dbf

/u01/app/oracle/oradata/mydb/sysaux01.dbf

/u01/app/oracle/oradata/mydb/undotbs01.dbf

/u01/app/oracle/oradata/mydb/users01.dbf

/u01/app/oracle/oradata/mydb/ts_test.dbf

2. After I create Table inside tablespace which created above .

SQL> create table talip_test (id number) tablespace TS_TEST;

Table created.

SQL> desc talip_test;

Name Null? Type

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

ID NUMBER

Insert Value to table.

SQL> INSERT INTO talip_test (ID) VALUES (001);

1 row created.

SQL> select * from talip_test;

ID

— — — — —

1

1

SQL> exit;

3. Next ,Take cold backup of completed database as precautions

[oracle@rac1 rman]$ rman target/

Recovery Manager: Release 11.2.0.4.0 — Production on Mon Oct 7 18:13:38 2019

Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: MYDB (DBID=2934281758)

RMAN> run {

allocate channel c1 type disk;

allocate channel c2 type disk;

backup database format ‘/home/oracle/rman/%U_DB’ plus archivelog format ‘/home/oracle/rman/%U_ARCH’;

release channel c1;

release channel c2;

}

spool log to ‘/home/oracle/rman/rman_mydb.log’;2> 3> 4> 5> 6> 7>

using target database control file instead of recovery catalog

allocated channel: c1

channel c1: SID=31 device type=DISK

allocated channel: c2

channel c2: SID=45 device type=DISK

Starting backup at 07-OCT-19

current log archived

channel c1: starting archived log backup set

…………………………

piece handle=/home/oracle/rman/12udo3k6_1_1_ARCH tag=TAG20191007T181637 comment=NONE

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

Finished backup at 07-OCT-19

released channel: c1

released channel: c2

RMAN>

RMAN>

RMAN> exit;

[oracle@rac1 rman]$ ls -lrt

total 1436548

-rw-r — — -. 1 oracle oinstall 133422592 Oct 7 18:14 0tudo3f4_1_1_ARCH

-rw-r — — -. 1 oracle oinstall 158826496 Oct 7 18:14 0sudo3f4_1_1_ARCH

-rw-r — — -. 1 oracle oinstall 468942848 Oct 7 18:15 0vudo3ga_1_1_DB

-rw-r — — -. 1 oracle oinstall 699891712 Oct 7 18:16 0uudo3g9_1_1_DB

-rw-r — — -. 1 oracle oinstall 98304 Oct 7 18:16 11udo3jq_1_1_DB

-rw-r — — -. 1 oracle oinstall 9797632 Oct 7 18:16 10udo3ja_1_1_DB

-rw-r — — -. 1 oracle oinstall 27648 Oct 7 18:16 12udo3k6_1_1_ARCH

-rw-r — r — . 1 oracle oinstall 120 Oct 7 18:18 rman_mydb.log

4.Then, Again connect to RMAN and Backup current control file as copy

[oracle@rac1 rman]$ rman target/

Recovery Manager: Release 11.2.0.4.0 — Production on Mon Oct 7 18:19:01 2019

Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: MYDB (DBID=2934281758)

RMAN> backup as copy current controlfile format /home/oracle/rman/bkp_controlfile.ctl

Starting backup at 07-OCT-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 datafile copy

copying current control file

output file name=/home/oracle/rman/bkp_controlfile.ctl tag=TAG20191007T181947 RECID=1 STAMP=1021054791

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

Finished backup at 07-OCT-19

RMAN> exit;

5.After,Drop Tablespace which created Previously:

[oracle@rac1 rman]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 7 18:26:28 2019

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Produc tion

With the Partitioning, OLAP, Data Mining and Real Application Testing op tions

SQL> select * from talip_test;

ID

----------

1

1

SQL> DROP TABLESPACE TS_TEST INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> shutdown;

Database closed.

Database dismounted.

ORACLE instance shut down

6. Start the database into nomount stage.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 626327552 bytes

Fixed Size 2255832 bytes

Variable Size 276825128 bytes

Database Buffers 343932928 bytes

Redo Buffers 3313664 bytes

SQL> exit;

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0. 4.0–64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing op tions

7. Restore control file backup from autobackup or last backup file.

RMAN> restore controlfile from ‘/home/oracle/rman/bkp_controlfile.ctl’;

Starting restore at 07-OCT-19

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=/u01/app/oracle/oradata/mydb/control01.ctl

output file name=/u01/app/oracle/flash_area2/mydb/control02.ctl

Finished restore at 07-OCT-19

RMAN> exit;

Recovery Manager complete

8. Mount the database

SQL> alter database mount;
Database altered.

9. Check dropped tablespace is present :

[RMAN> report schema;

10. Checked alert log file for drop tablespace command and give time before that command for restore operation:
Suppose tablespace TS_TEST is dropped at 18:31:00.

RMAN> run {set until time “to_date(‘07-OCT-2019 18:27:00’,’DD-MON-YYYY HH24:Mi:SS’)”;

restore database;

recover database;}

11. Open database with resetlogs

SQL> alter database open resetlogs;

Database altered.

12. Check tabespace present in database:

SQL> select file_name,bytes from dba_data_files where tablespace_name=’TS_TEST’;

FILE_NAME

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

BYTES

— — — — —

/u01/app/oracle/oradata/mydb/ts_test.dbf

Thank You!

Happy Blogging.

Undergraduate at University of Moratuwa,Faculty of Information Technology.