意外删除表空间的恢复方法

=======================================================
恢复表空间：
第一种方法：数据库在闪回模式下
1.首先要把数据库配置成闪回模式。
2.做一次完整的备份
3.drop tablespace
   DROP TABLESPACE tablespace_name INCLUDING CONTENTS;
4.shutdown immediate;
5.startup mount;
6.flashback database to timestamp to_timestamp('2011-12-18 14:43:29','yyyy-mm-dd hh24:mi:ss')
7.alter database rename file '/home/oracle/product/10.2.0/db_1/dbs/UNNAMED00006' to '/home/oracle/mustang/dbf/app01.dbf';
8.restor from coldbackup
9.recover database until time '2010-12-18 14:45:04';
10.alter database open resetlogs;

第二种方法：非闪回模式下：
1.关闭闪回模式
2.创建一个新的表空间来测试
3.关闭数据库
  1)备份所有的数据文件
  2)控制文件
4.删除表空
5.恢复备份文件和控制文件
6.recover database using backup controlfile until time '*****'
7.alter database open resetlogs;

具体操作记录如下：

SQL> conn / as sysdba

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------
NO

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7
SQL>
 
SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test01.dbf' size 10M;

Tablespace created.

SQL> alter user dlt quota unlimited on test;

SQL> alter user dlt quota unlimited on test;

User altered.

SQL> conn  dlt/dlt;
Connected.
SQL>

SQL> create table test (id number,name varchar2(100)) tablespace test;

Table created.

SQL> insert into test values(1,'denglt');

1 row created.

SQL> insert into test values(2,'denglt');

1 row created.

SQL> insert into test values(3,'denglt');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

        ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 denglt
         2 denglt
         3 denglt

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

--冷备份数据文件和控制文件
[oracle@orcl tmpsoft]$ mkdir coolbackup
[oracle@orcl tmpsoft]$ cd /u01/app/oracle/oradata/orcl
[oracle@orcl orcl]$ ls
control01.ctl  control03.ctl  redo02.log  sysaux01.dbf  temp01.dbf  undotbs01.dbf
control02.ctl  redo01.log     redo03.log  system01.dbf  test01.dbf  users01.dbf
[oracle@orcl orcl]$  cp *.dbf /tmpsoft/coolbackup
[oracle@orcl orcl]$ cp *.ctl /tmpsoft/coolbackup
[oracle@orcl orcl]$

--启动数据库删除test表空间

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  243269632 bytes
Fixed Size                  1266824 bytes
Variable Size             171969400 bytes
Database Buffers           67108864 bytes
Redo Buffers                2924544 bytes
Database mounted.
Database opened.

SQL> conn dlt/dlt
SQL> select * from test;

        ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 denglt
         2 denglt
         3 denglt

SQL> insert into test values(4,'denglt');

1 row created.

SQL> insert into test values(5,'denglt');

1 row created.

SQL> insert into test values(6,'denglt');

1 row created.

SQL> commit;

Commit complete.

 

SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2011-12-21 15:49:20

SQL>  create table dlt.test2 tablespace test as select * from dlt.test;

Table created.

        
SQL> drop tablespace test INCLUDING CONTENTS;

Tablespace dropped.

SQL>

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf

 

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

--恢复备份的数据文件和控制文件
[oracle@orcl orcl]$ cp /tmpsoft/coolbackup/* ./

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area  243269632 bytes
Fixed Size                  1266824 bytes
Variable Size             155192184 bytes
Database Buffers           83886080 bytes
Redo Buffers                2924544 bytes
Database mounted.
SQL>

SQL> select name from v$datafile;

NAME
--------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/test01.dbf     --可以看到

SQL> select * from v$recover_file;

no rows selected

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.
SQL> recover database using backup controlfile  until time '2011-12-21 15:49:20';

--注意:一定要使用using backup controlfile子句，否侧无法恢复3后面的数据。
SQL> alter database open resetlogs;

Database altered.

SQL> select * from dlt.test;

        ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 denglt
         2 denglt
         3 denglt
         4 denglt
         5 denglt
         6 denglt

6 rows selected.

恢复成功

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/195110/viewspace-713719/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/195110/viewspace-713719/