误删除表空间（有备份），利用备份的控制文件恢复




一、模拟环境


07:59:14 SQL> select count(*) from scott.dept2;


 COUNT(*)

----------

       12



07:59:50 SQL> drop tablespace lxtbs1 including contents and datafiles;


Tablespace dropped.


07:59:56 SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

08:00:58 SQL> !


Fri Mar 23 18:50:06 2012

drop tablespace cuug including contents and datafiles

Fri Mar 23 18:50:08 2012

Deleted file /u01/app/oracle/oradata/anny/cuug01.dbf

Completed: drop tablespace cuug including contents and datafiles




二、转储所有数据文件

[oracle@cuug14 ~]$ cp /orabak/orcl/cold_bak/*.dbf /disk1/oradata/orcl



08:03:26 SQL> recover database until time '2012-02-12 07:59:53' using backup controlfile;

ORA-01034: ORACLE not available



08:04:12 SQL> startup mount

ORACLE instance started.


Total System Global Area  167772160 bytes

Fixed Size                  1218316 bytes

Variable Size              75499764 bytes

Database Buffers           88080384 bytes

Redo Buffers                2973696 bytes

Database mounted.


三、recover  database


08:04:36 SQL> recover database until time '2012-02-12 07:59:53' using backup controlfile;

ORA-00279: change 831098 generated at 02/12/2012 06:32:28 needed for thread 1

ORA-00289: suggestion : /arch/orcl/arch_1_6_775023202.log

ORA-00280: change 831098 for thread 1 is in sequence #6



08:04:45 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 832010 generated at 02/12/2012 07:55:37 needed for thread 1

ORA-00289: suggestion : /arch/orcl/arch_1_1_775036537.log

ORA-00280: change 832010 for thread 1 is in sequence #1



ORA-00279: change 832995 generated at 02/12/2012 07:58:39 needed for thread 1

ORA-00289: suggestion : /arch/orcl/arch_1_2_775036537.log

ORA-00280: change 832995 for thread 1 is in sequence #2

ORA-00278: log file '/arch/orcl/arch_1_1_775036537.log' no longer needed for this recovery



ORA-00279: change 832997 generated at 02/12/2012 07:58:40 needed for thread 1

ORA-00289: suggestion : /arch/orcl/arch_1_3_775036537.log

ORA-00280: change 832997 for thread 1 is in sequence #3

ORA-00278: log file '/arch/orcl/arch_1_2_775036537.log' no longer needed for this recovery



ORA-00279: change 833000 generated at 02/12/2012 07:58:43 needed for thread 1

ORA-00289: suggestion : /arch/orcl/arch_1_4_775036537.log

ORA-00280: change 833000 for thread 1 is in sequence #4

ORA-00278: log file '/arch/orcl/arch_1_3_775036537.log' no longer needed for this recovery



ORA-00279: change 833017 generated at 02/12/2012 07:59:13 needed for thread 1

ORA-00289: suggestion : /arch/orcl/arch_1_5_775036537.log

ORA-00280: change 833017 for thread 1 is in sequence #5

ORA-00278: log file '/arch/orcl/arch_1_4_775036537.log' no longer needed for this recovery



ORA-00279: change 833019 generated at 02/12/2012 07:59:14 needed for thread 1

ORA-00289: suggestion : /arch/orcl/arch_1_6_775036537.log

ORA-00280: change 833019 for thread 1 is in sequence #6

ORA-00278: log file '/arch/orcl/arch_1_5_775036537.log' no longer needed for this recovery



ORA-00308: cannot open archived log '/arch/orcl/arch_1_6_775036537.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3





四、利用当前日志组恢复


08:04:52 SQL> select name from v$archived_log;


NAME

------------------------------------------------------------------------------------------------------------------------

/arch/orcl/arch_1_9_771838300.log

/arch/orcl/arch_1_10_771838300.log

/arch/orcl/arch_1_11_771838300.log

/arch/orcl/arch_1_12_771838300.log

/arch/orcl/arch_1_13_771838300.log

/arch/orcl/arch_1_14_771838300.log

/arch/orcl/arch_1_15_771838300.log

/arch/orcl/arch_1_16_771838300.log

/arch/orcl/arch_1_17_771838300.log

/arch/orcl/arch_1_18_771838300.log

/arch/orcl/arch_1_19_771838300.log

/arch/orcl/arch_1_20_771838300.log

/arch/orcl/arch_1_21_771838300.log

/arch/orcl/arch_1_4_775023202.log

/arch/orcl/arch_1_5_775023202.log

/arch/orcl/arch_1_1_775036537.log

/arch/orcl/arch_1_2_775036537.log


NAME

------------------------------------------------------------------------------------------------------------------------

/arch/orcl/arch_1_3_775036537.log

/arch/orcl/arch_1_4_775036537.log

/arch/orcl/arch_1_5_775036537.log


20 rows selected.


08:05:06 SQL> select member from v$logfile;


MEMBER

------------------------------------------------------------------------------------------------------------------------

/disk2/oradata/orcl/redo03.log

/disk2/oradata/orcl/redo02.log

/disk2/oradata/orcl/redo01.log


08:05:25 SQL> select * from v$log;


   GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------

        1          1          5   52428800          1 YES INACTIVE                833017 2012-02-12 07:59:13

        3          1          6   52428800          1 NO  CURRENT                 833019 2012-02-12 07:59:14

        2          1          4   52428800          1 YES INACTIVE                833000 2012-02-12 07:58:43



08:05:59 SQL> recover database until time '2012-02-12 07:59:53' using backup controlfile;

ORA-00279: change 833019 generated at 02/12/2012 07:59:14 needed for thread 1

ORA-00289: suggestion : /arch/orcl/arch_1_6_775036537.log

ORA-00280: change 833019 for thread 1 is in sequence #6



08:06:07 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/disk2/oradata/orcl/redo03.log

Log applied.

Media recovery complete.

08:06:13 SQL> alter database open resetlogs;


Database altered.


08:06:40 SQL> select name from v$datafile;


NAME

------------------------------------------------------------------------------------------------------------------------

/disk1/oradata/orcl/system01.dbf

/disk1/oradata/orcl/undotbs01.dbf

/disk1/oradata/orcl/sysaux01.dbf

/disk1/oradata/orcl/users01.dbf

/disk1/oradata/orcl/example01.dbf

/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00006


6 rows selected.


08:06:48 SQL> !

[oracle@cuug14 ~]$ ls /u01/app/oracle/product/10.2.0/db_1/dbs/

hc_orcl.dat  initdw.ora  init.ora  initorcl.ora  lkORCL  orapworcl  spfileorcl.ora

[oracle@cuug14 ~]$ ls -a /u01/app/oracle/product/10.2.0/db_1/dbs/

.  ..  hc_orcl.dat  initdw.ora  init.ora  initorcl.ora  lkORCL  orapworcl  spfileorcl.ora


08:08:29 SQL> col file_name for a50

08:08:35 SQL> select file_id,file_name,tablespace_name from dba_data_files;


  FILE_ID FILE_NAME                                          TABLESPACE_NAME

---------- -------------------------------------------------- ------------------------------

        4 /disk1/oradata/orcl/users01.dbf                    USERS

        3 /disk1/oradata/orcl/sysaux01.dbf                   SYSAUX

        2 /disk1/oradata/orcl/undotbs01.dbf                  UNDOTBS1

        1 /disk1/oradata/orcl/system01.dbf                   SYSTEM

        6 /u01/app/oracle/product/10.2.0/db_1/dbs/MISSING000 LXTBS1

          06


        5 /disk1/oradata/orcl/example01.dbf                  EXAMPLE


6 rows selected.




五、利用备份的datafile 再做完全恢复


08:08:59 SQL> alter tablespace lxtbs1 offline;

alter tablespace lxtbs1 offline

*

ERROR at line 1:

ORA-01191: file 6 is already offline - cannot do a normal offline

ORA-01111: name for data file 6 is unknown - rename to correct file

ORA-01110: data file 6: '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00006'




08:09:58 SQL> alter database  datafile 6 offline;


Database altered.


08:10:05 SQL> !

[oracle@cuug14 ~]$ cp /orabak/orcl/cold_bak/lxtbs01.dbf /disk1/oradata/orcl/




08:11:32 SQL> alter tablespace lxtbs1  rename datafile '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00006' to '/disk1/oradata/orcl/lxtbs01.dbf' ;


Tablespace altered.


08:11:44 SQL> alter tablespace lxtbs1 online;

alter tablespace lxtbs1 online

*

ERROR at line 1:

ORA-01190: control file or data file 6 is from before the last RESETLOGS

ORA-01110: data file 6: '/disk1/oradata/orcl/lxtbs01.dbf'



08:11:58 SQL> recover datafile 6;

ORA-00279: change 831098 generated at 02/12/2012 06:32:28 needed for thread 1

ORA-00289: suggestion : /arch/orcl/arch_1_6_775023202.log

ORA-00280: change 831098 for thread 1 is in sequence #6



08:12:19 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 832010 generated at 02/12/2012 07:55:37 needed for thread 1

ORA-00289: suggestion : /arch/orcl/arch_1_1_775036537.log

ORA-00280: change 832010 for thread 1 is in sequence #1



ORA-00279: change 832995 generated at 02/12/2012 07:58:39 needed for thread 1

ORA-00289: suggestion : /arch/orcl/arch_1_2_775036537.log

ORA-00280: change 832995 for thread 1 is in sequence #2

ORA-00278: log file '/arch/orcl/arch_1_1_775036537.log' no longer needed for this recovery



ORA-00279: change 832997 generated at 02/12/2012 07:58:40 needed for thread 1

ORA-00289: suggestion : /arch/orcl/arch_1_3_775036537.log

ORA-00280: change 832997 for thread 1 is in sequence #3

ORA-00278: log file '/arch/orcl/arch_1_2_775036537.log' no longer needed for this recovery



ORA-00279: change 833000 generated at 02/12/2012 07:58:43 needed for thread 1

ORA-00289: suggestion : /arch/orcl/arch_1_4_775036537.log

ORA-00280: change 833000 for thread 1 is in sequence #4

ORA-00278: log file '/arch/orcl/arch_1_3_775036537.log' no longer needed for this recovery



ORA-00279: change 833017 generated at 02/12/2012 07:59:13 needed for thread 1

ORA-00289: suggestion : /arch/orcl/arch_1_5_775036537.log

ORA-00280: change 833017 for thread 1 is in sequence #5

ORA-00278: log file '/arch/orcl/arch_1_4_775036537.log' no longer needed for this recovery



ORA-00279: change 833019 generated at 02/12/2012 07:59:14 needed for thread 1

ORA-00289: suggestion : /arch/orcl/arch_1_6_775036537.log

ORA-00280: change 833019 for thread 1 is in sequence #6

ORA-00278: log file '/arch/orcl/arch_1_5_775036537.log' no longer needed for this recovery



Log applied.

Media recovery complete.



08:12:35 SQL> alter  database datafile 6 online;


Database altered.


08:12:42 SQL> select * from scott.dept2;


   DEPTNO DNAME          LOC

---------- -------------- -------------

       10 ACCOUNTING     NEW YORK

       20 RESEARCH       DALLAS

       30 SALES          CHICAGO

       40 OPERATIONS     BOSTON

       10 ACCOUNTING     NEW YORK

       20 RESEARCH       DALLAS

       30 SALES          CHICAGO

       40 OPERATIONS     BOSTON

       10 ACCOUNTING     NEW YORK

       20 RESEARCH       DALLAS

       30 SALES          CHICAGO

       40 OPERATIONS     BOSTON


12 rows selected.


oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html