oracle技术之oracle基于backup control 的完全恢复

颇为阿娇978

　　误删除表空间（有备份），利用备份的控制文件恢复
　　一、模拟环境
　　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>
　　Variable>　　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: {=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: {=suggested | filename | AUTO | CANCEL}
　　/disk2/oradata/orcl/redo03.log
　　Log applied.
　　Media recovery complete.

　　08:06:13 SQL>>
　　Database>　　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
　　*
　　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>>
　　Database>　　08:10:05 SQL> !
　　[oracle@cuug14 ~]$ cp /orabak/orcl/cold_bak/lxtbs01.dbf /disk1/oradata/orcl/

　　08:11:32 SQL>>
　　Tablespace>
　　08:11:44 SQL>>　　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: {=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>>
　　Database>　　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