“drop user;drop tablespace”最好的恢复方法

2013/06/10 by Leave a Comment

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 ： 13764045638   QQ号:47079569    邮箱：service@parnassusdata.com

 

你好，

我正在练习一些不同的RMAN恢复场景在oracle11g windows2003

我模拟的场景是：

drop user MYUSRE cascade;

drop table space MYUSER including contents and datafiles;

一开始，我尝试使用RMAN，根据前面所说的情况，好像rman比较合适这种恢复

什么是最好的方法恢复？

感谢任何提示

恢复被删除的表空间

恢复这种被删除的表空间，基于时间点的表空间恢复是不能使用的。

当你删除一个表空间，控制文件将不再记录任何关于这个表空间的。试图使用RMAN恢复表空间将会返回如下RMAN错误  RMAN-016019-“could not translate tablespace name”

SQL> drop tablespace rman10 including contents and datafiles;

Tablespace dropped.

testdb:/u01/oracle/diag/rdbms/apex/apex/trace> rman target /

Recovery Manager: Release 10.2.0.4.0 – Production on Mon Aug 3 11:53:58 2009

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

connected to target database: TESTDB (DBID=2469552796)

RMAN> restore tablespace rman10;

Starting restore at 03-AUG-09

using target database control file instead of recovery catalog

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: sid=141 devtype=SBT_TAPE

channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=140 devtype=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 08/03/2009 11:54:11

RMAN-20202: tablespace not found in the recovery catalog

所以恢复被删除的表空间，我们有两种选择：

对整个数据库做一个时间点恢复，直到表空间被删除。

创建一个数据库副本从有效的备份中，导出需要的表从被删除的表空间中，重建表空间然后导入表到副本中。

第一种方法需要中断整个数据库并且整个数据库将被回滚为了恢复数据库。第二种方法可以在线做，但是我们需要额外的硬盘空间去创建被删除表空间的数据库副本。

让我们检测下第一种方法用于下面这个例子

在这个列子中，数据库CONTROLFILE AUTOBACKUP 和Flashback已经开启

开启Flashback的情况下，db_recovery_file_dest 将会如下所示每天产生一个子目录‘autobackup’

  ttestdb:/u02/oradata/testdb/TESTDB/autobackup/2009_08_03> ls -lrt

total 63040

-rw-r—–    1 oracle   dba         6455296 Aug  3 10:22 o1_mf_s_693915680_57dlgcqh_.bkp

-rw-r—–    1 oracle   dba         6455296 Aug  3 11:49 o1_mf_s_693920955_57dqkw0j_.bkp

-rw-r—–    1 oracle   dba         6455296 Aug  3 13:28 o1_mf_s_693926889_57dxcbdx_.bkp

-rw-r—–    1 oracle   dba         6455296 Aug  3 14:18 o1_mf_s_693928526_57f094n9_.bkp

-rw-r—–    1 oracle   dba         6455296 Aug  3 14:20 o1_mf_s_693930026_57f0fbo2_.bkp

当我们删除表空间时，会改变数据库的结构并且控制文件的自动备份会打开，我们可以看到另一个备份文件被创建在闪回自动备份区域

SQL> drop tablespace arul including contents and datafiles;

Tablespace dropped.

SQL> quit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

testdb:/u02/oradata/testdb/TESTDB/autobackup/2009_08_03> ls -lrt

total 75648

-rw-r—–    1 oracle   dba         6455296 Aug  3 10:22 o1_mf_s_693915680_57dlgcqh_.bkp

-rw-r—–    1 oracle   dba         6455296 Aug  3 11:49 o1_mf_s_693920955_57dqkw0j_.bkp

-rw-r—–    1 oracle   dba         6455296 Aug  3 13:28 o1_mf_s_693926889_57dxcbdx_.bkp

-rw-r—–    1 oracle   dba         6455296 Aug  3 14:18 o1_mf_s_693928526_57f094n9_.bkp

-rw-r—–    1 oracle   dba         6455296 Aug  3 14:20 o1_mf_s_693930026_57f0fbo2_.bkp

-rw-r—–    1 oracle   dba         6455296 Aug  3 14:38 o1_mf_s_693931114_57f1hbmo_.bkp

然后我们关闭数据库，启动到nomount并尝试还原控制文件从autobackup

时间最近的控制文件将被还原，但是由于是表空间被删除之后，新还原的控制文件中不会有被删除的表空间（ARUL）。如果我们试着恢复数据库，被删除的表空间将不会被恢复。

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  264241152 bytes

Fixed Size                  2083304 bytes

Variable Size             142607896 bytes

Database Buffers          113246208 bytes

Redo Buffers                6303744 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 03-AUG-09

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /u02/oradata/testdb/

database name (or database unique name) used for search: TESTDB

channel ORA_DISK_1: autobackup found in the recovery area

channel ORA_DISK_1: autobackup found: /u02/oradata/testdb/TESTDB/autobackup/2009_08_03/o1_mf_s_693931114_57f1hbmo_.bkp

channel ORA_DISK_1: control file restore from autobackup complete

output filename=/u02/oradata/testdb/control01.ctl

output filename=/u02/oradata/testdb/control02.ctl

output filename=/u02/oradata/testdb/control03.ctl

Finished restore at 03-AUG-09

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> report schema;

…

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

—- ——– ——————– ——- ————————

1    1230     SYSTEM               ***     /u02/oradata/testdb/system01.dbf

2    1700     UNDOTBS1             ***     /u02/oradata/testdb/undotbs01.dbf

3    370      SYSAUX               ***     /u02/oradata/testdb/sysaux01.dbf

4    280      USERS                ***     /u02/oradata/backup/bkp.04klgv2b

5    131      EXAMPLE              ***     /u02/oradata/testdb/example01.dbf

6    150      USERS                ***     /u02/oradata/backup/bkp.06klgv3k

9    100      USERS                ***     /u02/oradata/backup/bkp.08klgv4i

我们需要恢复一个包含表空间ARUL的控制文件。使用恢复控制文件命令恢复一个合适的控制文件的自动备份。

RMAN>  restore controlfile from ‘/u02/oradata/testdb/TESTDB/autobackup/2009_08_03/o1_mf_s_693930026_57f0fbo2_.bkp’;

Starting restore at 03-AUG-09

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

output filename=/u02/oradata/testdb/control01.ctl

output filename=/u02/oradata/testdb/control02.ctl

output filename=/u02/oradata/testdb/control03.ctl

Finished restore at 03-AUG-09

RMAN> report schema;

…..

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

—- ——– ——————– ——- ————————

1    1230     SYSTEM               ***     /u02/oradata/testdb/system01.dbf

2    1700     UNDOTBS1             ***     /u02/oradata/testdb/undotbs01.dbf

3    370      SYSAUX               ***     /u02/oradata/testdb/sysaux01.dbf

4    280      USERS                ***     /u02/oradata/backup/bkp.04klgv2b

5    131      EXAMPLE              ***     /u02/oradata/testdb/example01.dbf

6    150      USERS                ***     /u02/oradata/backup/bkp.06klgv3k

7    0        ARUL                 ***     /u02/oradata/testdb/arul01.dbf

9    100      USERS                ***     /u02/oradata/backup/bkp.08klgv4i

告警日志会记录表空间被删除的时间，我们也可以看到表空间被删除后，控制文件自动备份。

drop tablespace arul including contents and datafiles

Mon Aug  3 14:38:34 2009

Deleted file /u02/oradata/testdb/arul01.dbf

Starting control autobackup

Control autobackup written to DISK device

        handle ‘/u02/oradata/testdb/TESTDB/autobackup/2009_08_03/o1_mf_s_693931114_57f1hbmo_.bkp’

Completed: drop tablespace arul including contents and datafiles

现在我们知道表空间被删除的时间，我们可以做一个时间点恢复为了被删除的表空间

RMAN> run {

2> set until time “to_date(’03-AUG-2009 14:38:00′,’DD-MON-YYYY HH24:Mi:SS’)”;

3> restore database;

4> recover database;

5> }

executing command: SET until clause

using target database control file instead of recovery catalog

Starting restore at 03-AUG-09

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: sid=159 devtype=SBT_TAPE

channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: restoring datafile 00004

input datafile copy recid=14 stamp=693929215 filename=/u02/oradata/testdb/users01.dbf

destination for restore of datafile 00004: /u02/oradata/backup/bkp.04klgv2b

channel ORA_SBT_TAPE_1: starting datafile backupset restore

channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /u02/oradata/testdb/system01.dbf

restoring datafile 00002 to /u02/oradata/testdb/undotbs01.dbf

restoring datafile 00003 to /u02/oradata/testdb/sysaux01.dbf

restoring datafile 00005 to /u02/oradata/testdb/example01.dbf

restoring datafile 00007 to /u02/oradata/testdb/arul01.dbf

channel ORA_SBT_TAPE_1: reading from backup piece 0gkloo6p_1_1

channel ORA_DISK_1: copied datafile copy of datafile 00004

output filename=/u02/oradata/backup/bkp.04klgv2b recid=21 stamp=693932732

channel ORA_DISK_1: restoring datafile 00006

input datafile copy recid=13 stamp=693929146 filename=/u02/oradata/testdb/users02.dbf

destination for restore of datafile 00006: /u02/oradata/backup/bkp.06klgv3k

channel ORA_DISK_1: copied datafile copy of datafile 00006

output filename=/u02/oradata/backup/bkp.06klgv3k recid=23 stamp=693932755

channel ORA_DISK_1: restoring datafile 00009

input datafile copy recid=10 stamp=693929108 filename=/u02/oradata/testdb/users03.dbf

destination for restore of datafile 00009: /u02/oradata/backup/bkp.08klgv4i

channel ORA_DISK_1: copied datafile copy of datafile 00009

output filename=/u02/oradata/backup/bkp.08klgv4i recid=26 stamp=693932809

channel ORA_SBT_TAPE_1: restored backup piece 1

piece handle=0gkloo6p_1_1 tag=TAG20090803T113241

channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:02:40

Finished restore at 03-AUG-09

Starting recover at 03-AUG-09

using channel ORA_SBT_TAPE_1

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 8 is already on disk as file /u02/oradata/testdb/arch/arch.8.1.693662800.log

archive log thread 1 sequence 9 is already on disk as file /u02/oradata/testdb/arch/arch.9.1.693662800.log

archive log thread 1 sequence 10 is already on disk as file /u02/oradata/testdb/arch/arch.10.1.693662800.log

archive log thread 1 sequence 1 is already on disk as file /u02/oradata/testdb/redo01.log

archive log thread 1 sequence 2 is already on disk as file /u02/oradata/testdb/redo02.log

archive log filename=/u02/oradata/testdb/arch/arch.8.1.693662800.log thread=1 sequence=8

archive log filename=/u02/oradata/testdb/arch/arch.9.1.693662800.log thread=1 sequence=9

archive log filename=/u02/oradata/testdb/arch/arch.10.1.693662800.log thread=1 sequence=10

archive log filename=/u02/oradata/testdb/redo01.log thread=1 sequence=1

archive log filename=/u02/oradata/testdb/redo02.log thread=1 sequence=2

media recovery complete, elapsed time: 00:00:06

Finished recover at 03-AUG-09

RMAN>

RMAN> alter database open resetlogs;

database opened

现在我们可以看到哪些被删除的表空间已经恢复

SQL> select file_name,bytes from dba_data_files where

  2  tablespace_name=’ARUL’;

FILE_NAME

——————————————————————————–

     BYTES

———-

/u02/oradata/testdb/arul01.dbf

  37748736
Filed Under: Oracle

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号