Restoration of tablespace deleted by Oracle by mistake

For the restoration of accidentally deleted tablespaces, this article discusses the restoration of database-based time points and the recovery of tablespace-based time points respectively.

1. Restore accidentally deleted tablespaces through database-based time points

1. Notes

A database-based recovery time point will roll back the entire database.

B deletes the tablespace by mistake. When the database has a full-database backup that can be used for recovery and related archiving, if the database is not completely restored, the database is restored to the status before the tablespace is deleted, the deleted tablespace can be restored. But in fact, when we delete a tablespace, there will be no information about the tablespace in the database backup, and direct recovery will cause problems. As follows:

RMAN> list backup of database;

Using target database control file instead of recovery catalog

List of Backup Sets
==============================

 

BS Key Type LV Size Device Type Elapsed Time Completion Time
-----------------------------------------------------------------
34 Incr 0 2.04g disk 00:02:22 19:13:39
BP Key: 34 Status: AVAILABLE Compressed: NO Tag: TAG20140209T191116
Piece Name:/backup/crm/full-20140209-14p0792l_1_1.bak
List of Datafiles in backup set 34
File LV Type Ckp SCN Ckp Time Name
-------------------------------------------
1 0 Incr 3892854 19:11:17/Oracle/CRM/system01.dbf
2 0 Incr 3892854 19:11:17/oracle/CRM/sysaux01.dbf
3 0 Incr 3892854 19:11:17/oracle/CRM/undotbs01.dbf
4 0 Incr 3892854 19:11:17/oracle/CRM/users01.dbf
5 0 Incr 3892854 19:11:17/oracle/CRM/jxc. dbf
6 0 Incr 3892854 19:11:17/oracle/CRM/pos. dbf
7 0 Incr 3892854 19:11:17/oracle/CRM/user01.dbf
8 0 Incr 3892854 19:11:17/oracle/CRM/erp. dbf
9 0 Incr 3892854 19:11:17/oracle/CRM/undotbs03.dbf
12 0 Incr 3892854 19:11:17/oracle/CRM/crm. dbf

RMAN> host;

[Oracle @ dest bak] $ sqlplus/as sysdba

SQL * Plus: Release 11.2.0.2.0 Production on Sun Feb 9 19:16:40 2014

Copyright (c) 1982,201 0, Oracle. All rights reserved.

 

Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop tablespace pos including contents and datafiles;

Tablespace dropped.

RMAN> list backup of database;
List of Backup Sets
==============================

 

BS Key Type LV Size Device Type Elapsed Time Completion Time
-----------------------------------------------------------------
34 Incr 0 2.04g disk 00:02:22 19:13:39
BP Key: 34 Status: AVAILABLE Compressed: NO Tag: TAG20140209T191116
Piece Name:/backup/crm/full-20140209-14p0792l_1_1.bak
List of Datafiles in backup set 34
File LV Type Ckp SCN Ckp Time Name
-------------------------------------------
1 0 Incr 3892854 19:11:17/oracle/CRM/system01.dbf
2 0 Incr 3892854 19:11:17/oracle/CRM/sysaux01.dbf
3 0 Incr 3892854 19:11:17/oracle/CRM/undotbs01.dbf
4 0 Incr 3892854 19:11:17/oracle/CRM/users01.dbf
5 0 Incr 3892854 19:11:17/oracle/CRM/jxc. dbf
6 0 Incr 3892854 19:11:17
7 0 Incr 3892854 19:11:17/oracle/CRM/user01.dbf
8 0 Incr 3892854 19:11:17/oracle/CRM/erp. dbf
9 0 Incr 3892854 19:11:17/oracle/CRM/undotbs03.dbf
12 0 Incr 3892854 19:11:17/oracle/CRM/crm. dbf
Therefore, before recovery, you should first control the file backup at the last full backup time to restore the current control file, and then execute Incomplete recovery based on time points for the entire database.

2. Create a test table space and corresponding users
[Oracle @ dest bak] $ sqlplus/as sysdba

SQL * Plus: Release 11.2.0.2.0 Production on Sun Feb 9 18:18:28 2014

Copyright (c) 1982,201 0, Oracle. All rights reserved.

 

Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select group #, archived, sequence #, status from v $ log;

GROUP # arc sequence # STATUS
---------------------------------------
1 NO 1 CURRENT
2 YES 0 UNUSED
3 YES 0 UNUSED

SQL> alter system switch logfile;

System altered.

SQL>/

System altered.

SQL>/

System altered.

SQL> create tablespace jxc datafile '/oracle/CRM/jxc. dbf' size 10 m;

Tablespace created.

SQL> create user zx identified by dhhzdhhz default tablespace jxc;

User created.

SQL> grant connect, resource to zx;

Grant succeeded.

SQL> exit

1 2 3 4 Next Page