Oracle 备份与恢复学习笔记（6_2）

原创

客居天涯 2012-02-29 10:19:25 博主文章分类：数据库技术 ©著作权

文章标签 oracle 职场休闲 Oracle study 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者客居天涯的原创作品，请联系作者获取转载授权，否则将追究法律责任

第六章：手工不完全恢复

案例3：
---------在做完全恢复时，丢失了部分归档日志
1）基于cancel 的不完全恢复

-------模拟环境
06:01:59 SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT                           USERS
EMP                            USERS
BONUS                          USERS
SALGRADE                       USERS
TEST                           USERS
T01                            TEST
T02                            CUUG

7 rows selected.

06:02:11 SQL> conn /as sysdba
Connected.
06:02:32 SQL>
06:02:32 SQL> select * from scott.test;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8

8 rows selected.
06:02:39 SQL> select name from v$archived_log;
NAME
--------------------------------------------------
/disk1/arch/prod/arch_1_1_759390714.log
/disk1/arch/prod/arch_2_1_759390714.log
/disk1/arch/prod/arch_3_1_759390714.log
/disk1/arch/prod/arch_1_1_759391164.log
/disk1/arch/prod/arch_2_1_759391164.log

06:03:06 SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          2   52428800          1 YES ACTIVE                 1260553 17-AUG-11
         2          1          3   52428800          1 NO CURRENT                1260555 17-AUG-11
         3          1          1   52428800          1 YES ACTIVE                 1260287 17-AUG-11

06:03:34 SQL> insert into scott.test values (9);

1 row created.

06:03:45 SQL> commit;

Commit complete.

06:03:46 SQL> alter system archive log current;

System altered.

06:03:53 SQL> insert into scott.test values (10);

1 row created.

06:03:57 SQL> commit;

Commit complete.

06:03:58 SQL> alter system archive log current;

System altered.

06:04:01 SQL> insert into scott.test values (12);

1 row created.

06:04:04 SQL> commit;

Commit complete.

06:04:09 SQL> alter system archive log current;

System altered.

06:04:11 SQL> insert into scott.test values (11);

1 row created.

06:04:13 SQL> commit;

Commit complete.

06:04:15 SQL> alter system archive log current;

System altered.

06:04:17 SQL> insert into scott.test values (13);

1 row created.

06:04:21 SQL> commit;

Commit complete.

06:04:23 SQL> insert into scott.test values (14);

1 row created.

06:04:25 SQL> commit;

Commit complete.

06:04:26 SQL>

06:05:03 SQL> select name from v$archived_log;
NAME
--------------------------------------------------
/disk1/arch/prod/arch_2_1_759391164.log
/disk1/arch/prod/arch_3_1_759391164.log
/disk1/arch/prod/arch_4_1_759391164.log
/disk1/arch/prod/arch_5_1_759391164.log
/disk1/arch/prod/arch_6_1_759391164.log

06:05:10 SQL> select * from scott.test;

        ID
----------
         1
         2
         3
         9
        10
        12
        11
        13
        14
         4
         5
         6
         7
         8

14 rows selected.

--------------users 表空间datafile被误删除
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/users01.dbf
[oracle@work ~]$ mv /disk1/arch/prod/arch_4_1_759391164.log /disk1/arch/prod/arch_4_1_759391164.log.bak
[oracle@work ~]$ mv /disk1/arch/prod/arch_5_1_759391164.log /disk1/arch/prod/arch_5_1_759391164.log.bak
[oracle@work ~]$

--------------做完全恢复
06:07:27 SQL> startup
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size                  1219184 bytes
Variable Size              71304592 bytes
Database Buffers          239075328 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u01/app/oracle/oradata/prod/users01.dbf'

06:07:36 SQL> select file#,error from v$recover_file;

FILE# ERROR
---------- -----------------------------------------------------------------
2 FILE NOT FOUND
-------启动database 失败，restore datafile

[oracle@work ~]$ cp /disk1/backup/prod/close_bak/users01.dbf /u01/app/oracle/oradata/prod/

---------recover datafile
06:09:07 SQL> recover datafile 2;
ORA-00279: change 1258960 generated at 08/17/2011 04:37:14 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_4_1_759309442.log
ORA-00280: change 1258960 for thread 1 is in sequence #4

06:09:15 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1259691 generated at 08/17/2011 05:41:59 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_1_759390119.log
ORA-00280: change 1259691 for thread 1 is in sequence #1

ORA-00279: change 1260023 generated at 08/17/2011 05:44:31 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_2_1_759390119.log
ORA-00280: change 1260023 for thread 1 is in sequence #2
ORA-00278: log file '/disk1/arch/prod/arch_1_1_759390119.log' no longer needed for this recovery

ORA-00279: change 1260025 generated at 08/17/2011 05:44:32 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_3_1_759390119.log
ORA-00280: change 1260025 for thread 1 is in sequence #3
ORA-00278: log file '/disk1/arch/prod/arch_2_1_759390119.log' no longer needed for this recovery

ORA-00279: change 1260060 generated at 08/17/2011 05:51:54 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_1_759390714.log
ORA-00280: change 1260060 for thread 1 is in sequence #1

ORA-00279: change 1260278 generated at 08/17/2011 05:52:29 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_2_1_759390714.log
ORA-00280: change 1260278 for thread 1 is in sequence #2
ORA-00278: log file '/disk1/arch/prod/arch_1_1_759390714.log' no longer needed for this recovery

ORA-00279: change 1260280 generated at 08/17/2011 05:52:30 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_3_1_759390714.log
ORA-00280: change 1260280 for thread 1 is in sequence #3
ORA-00278: log file '/disk1/arch/prod/arch_2_1_759390714.log' no longer needed for this recovery

ORA-00279: change 1260287 generated at 08/17/2011 05:59:24 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_1_759391164.log
ORA-00280: change 1260287 for thread 1 is in sequence #1
ORA-00278: log file '/disk1/arch/prod/arch_3_1_759390714.log' no longer needed for this recovery

ORA-00279: change 1260553 generated at 08/17/2011 06:00:05 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_2_1_759391164.log
ORA-00280: change 1260553 for thread 1 is in sequence #2
ORA-00278: log file '/disk1/arch/prod/arch_1_1_759391164.log' no longer needed for this recovery

ORA-00279: change 1260555 generated at 08/17/2011 06:00:06 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_3_1_759391164.log
ORA-00280: change 1260555 for thread 1 is in sequence #3
ORA-00278: log file '/disk1/arch/prod/arch_2_1_759391164.log' no longer needed for this recovery

ORA-00279: change 1260697 generated at 08/17/2011 06:03:53 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_4_1_759391164.log
ORA-00280: change 1260697 for thread 1 is in sequence #4
ORA-00278: log file '/disk1/arch/prod/arch_3_1_759391164.log' no longer needed for this recovery

ORA-00308: cannot open archived log '/disk1/arch/prod/arch_4_1_759391164.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
----------完全恢复失败，缺少归档日志：（/disk1/arch/prod/arch_4_1_759391164.log）

06:09:22 SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/u01/app/oracle/oradata/prod/users01.dbf

---------------只能做基于cancel的不完全恢复

---转储所有的datafile
[oracle@work ~]$ cp /disk1/backup/prod/close_bak/*.dbf /u01/app/oracle/oradata/prod/

06:10:16 SQL> recover database until cancel;
ORA-00279: change 1258960 generated at 08/17/2011 04:37:14 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_4_1_759309442.log
ORA-00280: change 1258960 for thread 1 is in sequence #4

06:11:54 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1259691 generated at 08/17/2011 05:41:59 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_1_759390119.log
ORA-00280: change 1259691 for thread 1 is in sequence #1

ORA-00279: change 1260060 generated at 08/17/2011 05:51:54 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_1_759390714.log
ORA-00280: change 1260060 for thread 1 is in sequence #1

ORA-00308: cannot open archived log '/disk1/arch/prod/arch_4_1_759391164.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

--------在执行一次
06:12:01 SQL> recover database until cancel;
ORA-00279: change 1260697 generated at 08/17/2011 06:03:53 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_4_1_759391164.log
ORA-00280: change 1260697 for thread 1 is in sequence #4

06:12:03 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
------选择cancel ，在丢失的归档日志前终止recover

查看告警日志：
ALTER DATABASE RECOVER database until cancel
Wed Aug 17 06:11:53 2011
Media Recovery Start
Media Recovery start incarnation depth : 3, target inc# : 6, irscn : 1259690
ORA-279 signalled during: ALTER DATABASE RECOVER database until cancel ...
Wed Aug 17 06:11:56 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:11:56 2011
Media Recovery Log /disk1/arch/prod/arch_4_1_759309442.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:11:58 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:11:58 2011
Media Recovery Log /disk1/arch/prod/arch_1_1_759390119.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:11:58 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:11:58 2011
Media Recovery Log /disk1/arch/prod/arch_2_1_759390119.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:11:58 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:11:58 2011
Media Recovery Log /disk1/arch/prod/arch_3_1_759390119.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:11:58 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:11:58 2011
Media Recovery Log /disk1/arch/prod/arch_1_1_759390714.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:11:59 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:11:59 2011
Media Recovery Log /disk1/arch/prod/arch_2_1_759390714.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:11:59 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:11:59 2011
Media Recovery Log /disk1/arch/prod/arch_3_1_759390714.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:11:59 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:11:59 2011
Media Recovery Log /disk1/arch/prod/arch_1_1_759391164.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:11:59 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:11:59 2011
Media Recovery Log /disk1/arch/prod/arch_2_1_759391164.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:11:59 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:11:59 2011
Media Recovery Log /disk1/arch/prod/arch_3_1_759391164.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:12:00 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:12:00 2011
Media Recovery Log /disk1/arch/prod/arch_4_1_759391164.log
Errors with log /disk1/arch/prod/arch_4_1_759391164.log
ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:12:00 2011
ALTER DATABASE RECOVER CANCEL
Wed Aug 17 06:12:01 2011
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL
Wed Aug 17 06:12:03 2011
ALTER DATABASE RECOVER database until cancel
Media Recovery Start
ORA-279 signalled during: ALTER DATABASE RECOVER database until cancel ...
Wed Aug 17 06:12:06 2011
ALTER DATABASE RECOVER    CANCEL
Wed Aug 17 06:12:07 2011
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER    CANCEL
Wed Aug 17 06:12:13 2011
alter database open resetlogs

----------验证
06:12:07 SQL> alter database open resetlogs;

Database altered.

06:12:36 SQL> select * from scott.test;

        ID
----------
         1
         2
         3
         9
         4
         5
         6
         7
         8

9 rows selected
---------只恢复到sequence 为3的日志所记录的data block

案例4：
----------------------误删除表空间（有备份）
1）基于backup control 的不完全恢复

06:24:21 SQL> select file_id,file_name ,tablespace_name from dba_data_files;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
         8 /u01/app/oracle/oradata/prod/test02.dbf            TEST
         3 /u01/app/oracle/oradata/prod/sysaux01.dbf          SYSAUX
         2 /u01/app/oracle/oradata/prod/users01.dbf           USERS
         1 /u01/app/oracle/oradata/prod/system01.dbf          SYSTEM
         5 /u01/app/oracle/oradata/prod/example01.dbf         EXAMPLE
         6 /u01/app/oracle/oradata/prod/test01.dbf            TEST
         7 /u01/app/oracle/oradata/prod/undo_tbs01.dbf        UNDO_TBS
         4 /u01/app/oracle/oradata/prod/index01.dbf           INDEXES
         9 /u01/app/oracle/oradata/prod/cuug01.dbf            CUUG

9 rows selected.

06:24:38 SQL> select table_name,tablespace_name from user_tables;

7 rows selected.

6:24:24 SQL> conn scott/tiger
Connected.
06:24:38 SQL>
06:24:38 SQL> select table_name,tablespace_name from user_tables;

7 rows selected.

06:24:47 SQL> select * from t02;

        ID
----------
         1
         2
         3

06:25:03 SQL> insert into t02 select * from t02;

3 rows created.

06:25:13 SQL> commit;

Commit complete.

06:25:15 SQL> select * from t02;

        ID
----------
         1
         2
         3
         1
         2
         3

6 rows selected.

06:25:17 SQL> conn /as sysdba
Connected.
06:25:22 SQL>
06:25:22 SQL> alter database backup controlfile to '/disk1/backup/prod_control.bak';

Database altered.
---------生成控制文件备份

06:25:42 SQL> insert into scott.t02 values (4);

1 row created.

06:25:56 SQL> insert into scott.t02 values (5);

1 row created.

06:25:58 SQL> commit;

Commit complete.

06:25:59 SQL>

------------误删除了cuug的表空间
06:25:59 SQL> drop tablespace cuug including contents and datafiles;

Tablespace dropped.

06:26:35 SQL>
06:26:52 SQL> select file_id,file_name ,tablespace_name from dba_data_files;

8 rows selected.

查看告警日志信息：
---------查看 tablespace的删除的时间点
Wed Aug 17 06:26:33 2011
drop tablespace cuug including contents and datafiles
Wed Aug 17 06:26:35 2011
Deleted file /u01/app/oracle/oradata/prod/cuug01.dbf
Completed: drop tablespace cuug including contents and datafiles

----------关闭数据库，启动到no mount状态
6:26:56 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
06:28:54 SQL> startup nomount
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size                  1219184 bytes
Variable Size              67110288 bytes
Database Buffers          243269632 bytes
Redo Buffers                2973696 bytes
06:29:07 SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u01/app/oracle/oradata/prod/c
                                                 ontrol01.ctl, /u01/app/oracle/
                                                 oradata/prod/control02.ctl, /u
                                                 01/app/oracle/oradata/prod/con
                                                 trol03.ctl
06:29:17 SQL>

-----------用备份的控制文件覆盖当前的controlfile
[oracle@work ~]$ cp /disk1/backup/prod_control.bak /u01/app/oracle/oradata/prod/control01.ctl
[oracle@work ~]$ cp /disk1/backup/prod_control.bak /u01/app/oracle/oradata/prod/control02.ctl
[oracle@work ~]$ cp /disk1/backup/prod_control.bak /u01/app/oracle/oradata/prod/control03.ctl

----------启动到mount状态，转储所有的数据文件
[oracle@work prod]$ cp /disk1/backup/prod/close_bak/*.dbf /u01/app/oracle/oradata/prod/

------------基于backup controlfile的不完全恢复
06:30:44 SQL> recover database until time '2011-08-17 06:26:33' using backup controlfile;
ORA-00279: change 1258960 generated at 08/17/2011 04:37:14 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_4_1_759309442.log
ORA-00280: change 1258960 for thread 1 is in sequence #4

06:33:21 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1259691 generated at 08/17/2011 05:41:59 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_1_759390119.log
ORA-00280: change 1259691 for thread 1 is in sequence #1

ORA-00279: change 1260060 generated at 08/17/2011 05:51:54 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_1_759390714.log
ORA-00280: change 1260060 for thread 1 is in sequence #1

ORA-00279: change 1260287 generated at 08/17/2011 05:59:24 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_1_759391164.log
ORA-00280: change 1260287 for thread 1 is in sequence #1

ORA-00279: change 1260698 generated at 08/17/2011 06:12:13 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_1_759391933.log
ORA-00280: change 1260698 for thread 1 is in sequence #1

ORA-00279: change 1261012 generated at 08/17/2011 06:15:08 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_2_1_759391933.log
ORA-00280: change 1261012 for thread 1 is in sequence #2
ORA-00278: log file '/disk1/arch/prod/arch_1_1_759391933.log' no longer needed for this recovery

ORA-00279: change 1261014 generated at 08/17/2011 06:15:09 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_3_1_759391933.log
ORA-00280: change 1261014 for thread 1 is in sequence #3
ORA-00278: log file '/disk1/arch/prod/arch_2_1_759391933.log' no longer needed for this recovery

ORA-00308: cannot open archived log '/disk1/arch/prod/arch_3_1_759391933.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

06:33:28 SQL> recover database until time '2011-08-17 06:26:33' using backup controlfile;
ORA-00279: change 1261014 generated at 08/17/2011 06:15:09 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_3_1_759391933.log
ORA-00280: change 1261014 for thread 1 is in sequence #3

06:33:34 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

查看告警日志：
ALTER DATABASE RECOVER database until time '2011-08-17 06:26:33' using backup controlfile ；
Wed Aug 17 06:33:20 2011
Media Recovery Start
Media Recovery start incarnation depth : 4, target inc# : 7, irscn : 1259690
ORA-279 signalled during: ALTER DATABASE RECOVER database until time '2011-08-17 06:26:33' using backup controlfile ...
Wed Aug 17 06:33:23 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:33:23 2011
Media Recovery Log /disk1/arch/prod/arch_4_1_759309442.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:33:25 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:33:25 2011
Media Recovery Log /disk1/arch/prod/arch_1_1_759390119.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:33:25 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:33:25 2011
Media Recovery Log /disk1/arch/prod/arch_2_1_759390119.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:33:25 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:33:25 2011
Media Recovery Log /disk1/arch/prod/arch_3_1_759390119.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:33:26 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:33:26 2011
Media Recovery Log /disk1/arch/prod/arch_1_1_759390714.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:33:26 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:33:26 2011
Media Recovery Log /disk1/arch/prod/arch_2_1_759390714.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:33:26 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:33:26 2011
Media Recovery Log /disk1/arch/prod/arch_3_1_759390714.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:33:26 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:33:26 2011
Media Recovery Log /disk1/arch/prod/arch_1_1_759391164.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:33:26 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:33:26 2011
Media Recovery Log /disk1/arch/prod/arch_2_1_759391164.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:33:26 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:33:26 2011
Media Recovery Log /disk1/arch/prod/arch_3_1_759391164.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:33:27 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:33:27 2011
Media Recovery Log /disk1/arch/prod/arch_1_1_759391933.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:33:27 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:33:27 2011
Media Recovery Log /disk1/arch/prod/arch_2_1_759391933.log
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:33:27 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Aug 17 06:33:27 2011
Media Recovery Log /disk1/arch/prod/arch_3_1_759391933.log
Errors with log /disk1/arch/prod/arch_3_1_759391933.log
ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...
Wed Aug 17 06:33:27 2011
ALTER DATABASE RECOVER CANCEL
Wed Aug 17 06:33:28 2011
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL
Wed Aug 17 06:33:34 2011
ALTER DATABASE RECOVER database until time '2011-08-17 06:26:33' using backup controlfile
Media Recovery Start
ORA-279 signalled during: ALTER DATABASE RECOVER database until time '2011-08-17 06:26:33' using backup controlfile ...
Wed Aug 17 06:33:38 2011
ALTER DATABASE RECOVER    CANCEL
Wed Aug 17 06:33:40 2011
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER    CANCEL
Wed Aug 17 06:33:46 2011
alter database open resetlogs
RESETLOGS after incomplete recovery UNTIL CHANGE 1261014
Resetting resetlogs activation ID 171222051 (0xa34a423)

-----------验证：
6:33:40 SQL> alter database open resetlogs;

Database altered.

06:34:06 SQL> col file_name for a50
06:35:02 SQL> select file_id,file_name,tablespace_name from dba_data_files;

9 rows selected.

06:35:13 SQL> select * from scott.t02;

        ID
----------
         1
         2
         3

06:35:20 SQL>

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

一、模拟环境

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> !

二、转储所有数据文件
[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

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.