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:
- 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.