基于backup control 的完全恢复

 

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

 

一、模拟环境

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.