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

第五章： 手工完全恢复

-------------非归档模式

案例1： 历史日志没有被覆盖

1）切换到非归档模式

06:58:57 SQL>
06:58:57 SQL> archive log list                                                                                                          
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /disk1/arch/prod
Oldest online log sequence     45
Next log sequence to archive   47
Current log sequence           47
06:59:04 SQL> shutdown immediate                                                                                                        
Database closed.
Database dismounted.
ORACLE instance shut down.
07:00:15 SQL> startup mount                                                                                                             
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1219184 bytes
Variable Size              79693200 bytes
Database Buffers          230686720 bytes
Redo Buffers                2973696 bytes
Database mounted.
07:00:27 SQL> alter database noarchivelog;                                                                                              

Database altered.

07:00:33 SQL> archive log list;                                                                                                         
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /disk1/arch/prod
Oldest online log sequence     45
Current log sequence           47
07:00:35 SQL> alter database open;                                                                                                      

Database altered.

07:00:45 SQL> !                                                                                                                         
[oracle@work ~]$ ls /disk1/backup/prod/close_bak/*
/disk1/backup/prod/close_bak/control02.ctl  /disk1/backup/prod/close_bak/sysaux01.dbf  /disk1/backup/prod/close_bak/undo_tbs01.dbf
/disk1/backup/prod/close_bak/control03.ctl  /disk1/backup/prod/close_bak/system01.dbf  /disk1/backup/prod/close_bak/users01.dbf
/disk1/backup/prod/close_bak/example01.dbf  /disk1/backup/prod/close_bak/test01.dbf
/disk1/backup/prod/close_bak/index01.dbf    /disk1/backup/prod/close_bak/test02.dbf
[oracle@work ~]$ rm /disk1/backup/prod/close_bak/*
[oracle@work ~]$ rm /disk1/arch/prod/*
[oracle@work ~]$ exit
exit

2）重新做数据库的全备（一致性备份-冷备份）
-------备份所有的datafile 和 controlfile
3）模拟环境

SQL> select * from v$log                                                                                                                
  2  ;                                                                                                                                  
         1          1         47   52428800          1 NO  CURRENT                1250545 16-AUG-11
         2          1         45   52428800          1 YES INACTIVE               1209278 16-AUG-11
         3          1         46   52428800          1 YES INACTIVE               1229885 16-AUG-11
SQL> select * from scott.test;                                                                                                          
         1
         2
         3
         4
         5
         6
         7
         8
SQL> set heading on                                                                                                                     
SQL> insert into scott.test values (9);                                                                                                 
SQL> insert into scott.test values (10);                                                                                                
SQL> insert into scott.test values (11);                                                                                                
SQL> commit;                                                                                                                            
SQL> select * from v$log;                                                                                                               
         1          1         47   52428800          1 NO  CURRENT                1250545 16-AUG-11
         2          1         45   52428800          1 YES INACTIVE               1209278 16-AUG-11
         3          1         46   52428800          1 YES INACTIVE               1229885 16-AUG-11
        
QL> select segment_name,tablespace_NAME from dba_segments                                                                              
  2   where segment_name='TEST';                                                                                                        
TEST                                                                              USERS
SQL> SHUTDOWN ABORT                                                                                                                     
ORACLE instance shut down.

2) 删除数据文件
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/users01.dbf

3）启动数据库
07:06:22 SQL> startup                                                                                                                   
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1219184 bytes
Variable Size              79693200 bytes
Database Buffers          230686720 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'

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

     FILE# ERROR
---------- -----------------------------------------------------------------
         2 FILE NOT FOUND
4）恢复

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

---------recover datafile
07:07:37 SQL> recover datafile 2; 

告警日志信息：
ALTER DATABASE RECOVER  datafile 2 
Tue Aug 16 07:07:56 2011
Media Recovery Start
Tue Aug 16 07:07:56 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 47 Reading mem 0
  Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo01.log
Tue Aug 16 07:07:57 2011
Media Recovery Complete (prod)
Completed: ALTER DATABASE RECOVER  datafile 2                                                                                                      
Media recovery complete.

5）验证
07:08:00 SQL> alter database open;                                                                                                      

Database altered.

07:08:08 SQL> select * from scott.test;                                                                                                 

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

11 rows selected.

07:08:14 SQL>
 
 
案例2：日志发生切换，历史日志已经被覆盖

1）模拟环境
07:08:14 SQL> insert into scott.test values (12);                                                                                       

1 row created.

07:10:17 SQL> insert into scott.test values (13);                                                                                       

1 row created.

07:10:20 SQL> insert into scott.test values (14);                                                                                       

1 row created.

07:10:22 SQL> insert into scott.test values (15);                                                                                       

1 row created.

07:10:24 SQL> commit;                                                                                                                   

Commit complete.

07:10:25 SQL> alter system switch logfile;                                                                                              

System altered.

07:10:34 SQL> /                                                                                                                         

System altered.
07:10:39 SQL> select * from v$log;                                                                                                      

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         50   52428800          1 NO  INACTIVE               1272776 16-AUG-11
         2          1         51   52428800          1 NO  ACTIVE                 1272779 16-AUG-11
         3          1         52   52428800          1 NO  CURRENT                1272781 16-AUG-11

07:10:43 SQL> alter system switch logfile;                                                                                              

System altered.

07:10:45 SQL> alter system switch logfile;                                                                                              

System altered.

07:10:51 SQL> select * from scott.test;                                                                                                 

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

15 rows selected.

07:10:57 SQL>

07:10:57 SQL> shutdown abort                                                                                                            
ORACLE instance shut down.
07:11:35 SQL> !                                                                                                                         
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/users01.dbf

2）启动数据库
07:11:54 SQL> startup                                                                                                                   
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1219184 bytes
Variable Size              79693200 bytes
Database Buffers          230686720 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'

07:12:02 SQL> select file#,error from v$recover_file;                                                                                   

     FILE# ERROR
---------- -----------------------------------------------------------------
         2 FILE NOT FOUND
        
  3）恢复
  --------------restore datafile
  cp /disk1/backup/prod/close_bak/users01.dbf /u01/app/oracle/oradata/prod/
 
  ---------recover datafile
  07:13:11 SQL> recover datafile 2;                                                                                                       
ORA-00279: change 1252332 generated at 08/16/2011 07:02:01 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_47_1_758481658.log
ORA-00280: change 1252332 for thread 1 is in sequence #47

07:13:15 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto                                                                                                                                    
ORA-00308: cannot open archived log '/disk1/arch/prod/arch_47_1_758481658.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

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

-------需要归档日志。。。。。。。。。。。。。。。。

-----------恢复需要转储所有的控制文件和datafile
07:13:20 SQL> select name from v$controlfile;                                                                                           

NAME
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/control02.ctl
/u01/app/oracle/oradata/prod/control03.ctl

07:14:12 SQL> shutdown                                                                                                                  
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
07:14:21 SQL> !                                                                                                                         
[oracle@work ~]$ cp /disk1/backup/prod/close_bak/control* /u01/app/oracle/oradata/prod/
[oracle@work ~]$ cp /disk1/backup/prod/close_bak/*.dbf /u01/app/oracle/oradata/prod/

-------------启动数据库到mount
07:15:58 SQL> startup mount                                                                                                             
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1219184 bytes
Variable Size              79693200 bytes
Database Buffers          230686720 bytes
Redo Buffers                2973696 bytes
Database mounted.
07:16:11 SQL> select file#,checkpoint_change# from v$datafile;                                                                          

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1252332
         2            1252332
         3            1252332
         4            1252332
         5            1252332
         6            1252332
         7            1252332
         8            1252332
         9            1252332

9 rows selected.

07:16:30 SQL> select file#,checkpoint_change# from v$datafile_header;                                                                   

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1252332
         2            1252332
         3            1252332
         4            1252332
         5            1252332
         6            1252332
         7            1252332
         8            1252332
         9            1252332

9 rows selected.

07:16:35 SQL>

07:16:35 SQL> alter database open;                                                                                                      
alter database open
*
ERROR at line 1:
ORA-00314: log 1 of thread 1, expected sequence#  doesn't match
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/prod/redo01.log'
---------如果此刻直接打开库，因为redo log 和controlfile、datafile 不同步，不能直接打开

07:17:07 SQL> recover database until cancel;                                                                                            
Media recovery complete.

------做不完全恢复
07:17:17 SQL> alter database open resetlogs;                                                                                            

Database altered.
------对database进行resetlogs 方式打开

查看告警日志信息：
ALTER DATABASE RECOVER  database until cancel 
Tue Aug 16 07:17:17 2011
Media Recovery Start
Media Recovery Not Required
Completed: ALTER DATABASE RECOVER  database until cancel 
Tue Aug 16 07:17:22 2011
alter database open resetlogs
RESETLOGS after complete recovery through change 1252332
Resetting resetlogs activation ID 170334582 (0xa271976)
Tue Aug 16 07:17:27 2011
Setting recovery target incarnation to 3
Tue Aug 16 07:17:27 2011
Assigning activation ID 171172278 (0xa33e1b6)
Thread 1 advanced to log sequence 2
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/prod/redo02.log
Successful open of redo thread 1

07:17:38 SQL>

-------验证

07:17:38 SQL> select * from v$log;                                                                                                      

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          1   52428800          1 NO  INACTIVE               1252333 16-AUG-11
         2          1          2   52428800          1 NO  CURRENT                1252334 16-AUG-11
         3          1          0   52428800          1 YES UNUSED                       0

07:19:22 SQL>

---------数据库被resetlog ，建议立刻做一个数据库的全备。
07:19:22 SQL> select * from scott.test;                                                                                                 

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

8 rows selected.

----------只能恢复到最后一次备份

11、控制文件和redo 日志文件恢复

控制文件恢复
     单个文件丢失：
    
        [oracle@oracle dbs]$ rm /disk2/lx02/oradata/control03.ctl
[oracle@oracle dbs]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 1 06:14:54 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

06:14:54 SQL> startup
ORACLE instance started.

Total System Global Area  176160768 bytes
Fixed Size                  1218364 bytes
Variable Size              88082628 bytes
Database Buffers           83886080 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

通过告警日志获得信息：

ALTER DATABASE   MOUNT
Mon Aug  1 06:14:57 2011
ORA-00202: control file: '/disk2/lx02/oradata/control03.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

 

06:14:57 SQL> shutdown
ORA-01507: database not mounted

ORACLE instance shut down.
06:15:14 SQL> !
[oracle@oracle dbs]$ cp /disk1/lx02/oradata/control02.ctl /disk2/lx02/oradata/control03.ctl
[oracle@oracle dbs]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 1 06:15:36 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

06:15:37 SQL> startup
ORACLE instance started.

Total System Global Area  176160768 bytes
Fixed Size                  1218364 bytes
Variable Size              88082628 bytes
Database Buffers           83886080 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
06:15:47 SQL> select name from v$controlfile;

NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/lx02/control01.ctl
/disk1/lx02/oradata/control02.ctl
/disk2/lx02/oradata/control03.ctl

06:16:00 SQL>

     所有的文件丢失：
    
        06:16:00 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
06:17:22 SQL> !
[oracle@oracle dbs]$ rm /u01/app/oracle/oradata/lx02/control01.ctl
[oracle@oracle dbs]$ rm /disk1/lx02/oradata/control02.ctl
[oracle@oracle dbs]$ rm /disk2/lx02/oradata/control03.ctl
[oracle@oracle dbs]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 1 06:17:51 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

06:17:51 SQL> startup
ORACLE instance started.

Total System Global Area  176160768 bytes
Fixed Size                  1218364 bytes
Variable Size              88082628 bytes
Database Buffers           83886080 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

告警日志：
ALTER DATABASE   MOUNT
Mon Aug  1 06:17:54 2011
ORA-00202: control file: '/u01/app/oracle/oradata/lx02/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Mon Aug  1 06:17:54 2011

 利用trace 文件重建
 在nomount 状态
 
  06:19:51 SQL>CREATE CONTROLFILE REUSE DATABASE "LX02" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 30
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/lx02/redo01a.log'  SIZE 10M,
  GROUP 2 '/u01/app/oracle/oradata/lx02/redo02a.log'  SIZE 10M
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/lx02/system01.dbf',
  '/u01/app/oracle/oradata/lx02/rtbs01.dbf',
  '/u01/app/oracle/oradata/lx02/sysaux01.dbf',
  '/u01/app/oracle/oradata/lx02/user01.dbf',
  '/u01/app/oracle/oradata/lx02/example01.dbf',
  '/u01/app/oracle/oradata/lx02/indx01.dbf',
  '/u01/app/oracle/oradata/lx02/OLTP01.DBF'
CHARACTER SET ZHS16GBK
06:21:23  20  ;

Control file created.

06:21:27 SQL> alter database open resetlogs;

Database altered.

06:21:39 SQL>

日志恢复

    1、多元化成员中，单个成员丢失
      05:10:06 SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          9   10485760          2 NO  INACTIVE                384007 02-AUG-11
         3          1          8   10485760          2 NO  INACTIVE                384005 02-AUG-11
         2          1         10   10485760          2 NO  CURRENT                 385481 02-AUG-11

05:10:12 SQL> !
[oracle@oracle ~]$ ls /disk2/lx01/oradata/
control03.ctl  redo01a.log  redo02a.log  redo03a.log  redo04a.log  redo05a.log
[oracle@oracle ~]$ exit
exit

05:14:31 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
05:14:41 SQL> !
[oracle@oracle ~]$ rm  /disk2/lx02/oradata/redo01a.log
[oracle@oracle ~]$ !sql
sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 2 05:15:02 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

05:15:02 SQL> startup
ORACLE instance started.

Total System Global Area  251658240 bytes
Fixed Size                  1218820 bytes
Variable Size             125830908 bytes
Database Buffers          121634816 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
05:15:12 SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          9   10485760          2 NO  INACTIVE                384007 02-AUG-11
         3          1          8   10485760          2 NO  INACTIVE                384005 02-AUG-11
         2          1         10   10485760          2 NO  CURRENT                 385481 02-AUG-11

05:15:24 SQL> desc v$logfile;
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 GROUP#                                                                                       NUMBER
 STATUS                                                                                       VARCHAR2(7)
 TYPE                                                                                         VARCHAR2(7)
 MEMBER                                                                                       VARCHAR2(513)
 IS_RECOVERY_DEST_FILE                                                                        VARCHAR2(3）
 05:15:43 SQL> col member for a50
05:15:48 SQL> r
  1* select group#,member ,status from v$logfile

    GROUP# MEMBER                                             STATUS
---------- -------------------------------------------------- -------
         2 /disk2/lx02/oradata/redo02a.log
         1 /disk2/lx02/oradata/redo01a.log                    INVALID
         3 /disk2/lx02/oradata/redo03a.log
         1 /disk1/lx02/oradata/redo01b.log
         2 /disk1/lx02/oradata/redo02b.log
         3 /disk1/lx02/oradata/redo03b.log

6 rows selected.

05:15:48 SQL>

告警日志：
Errors in file /u01/app/oracle/admin/lx02/bdump/lx02_lgwr_9105.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/disk2/lx02/oradata/redo01a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

解决：
05:15:48 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
05:17:47 SQL> !
[oracle@oracle ~]$ cp /disk1/lx02/oradata/redo01b.log /disk2/lx02/oradata/redo01a.log
[oracle@oracle ~]$ !sql
sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 2 05:18:02 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

05:18:02 SQL> startup
ORACLE instance started.

Total System Global Area  251658240 bytes
Fixed Size                  1218820 bytes
Variable Size             125830908 bytes
Database Buffers          121634816 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
05:18:14 SQL> col member for a50
05:18:26 SQL> select group#,member ,status from v$logfile
05:18:29   2  ;

    GROUP# MEMBER                                             STATUS
---------- -------------------------------------------------- -------
         2 /disk2/lx02/oradata/redo02a.log
         1 /disk2/lx02/oradata/redo01a.log                    INVALID
         3 /disk2/lx02/oradata/redo03a.log
         1 /disk1/lx02/oradata/redo01b.log
         2 /disk1/lx02/oradata/redo02b.log
         3 /disk1/lx02/oradata/redo03b.log

6 rows selected.

05:18:31 SQL> alter system switch logfile;

System altered.

05:18:37 SQL> /

System altered.

05:18:39 SQL> select group#,member ,status from v$logfile
05:18:40   2  ;

    GROUP# MEMBER                                             STATUS
---------- -------------------------------------------------- -------
         2 /disk2/lx02/oradata/redo02a.log
         1 /disk2/lx02/oradata/redo01a.log
         3 /disk2/lx02/oradata/redo03a.log
         1 /disk1/lx02/oradata/redo01b.log
         2 /disk1/lx02/oradata/redo02b.log
         3 /disk1/lx02/oradata/redo03b.log

6 rows selected.

05:18:42 SQL>

    2、非当前日志组所有成员丢失
   
    05:19:42 SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         12   10485760          2 NO  CURRENT                 386507 02-AUG-11
         3          1         11   10485760          2 NO  INACTIVE                386505 02-AUG-11
         2          1         10   10485760          2 NO  INACTIVE                385481 02-AUG-11

05:19:45 SQL>
  05:19:45 SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
05:19:59 SQL> !
[oracle@oracle ~]$ rm /disk2/lx02/oradata/redo02a.log
[oracle@oracle ~]$ rm /disk1/lx02/oradata/redo02b.log
[oracle@oracle ~]$ !sql
sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 2 05:20:21 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

05:20:22 SQL> startup
ORACLE instance started.

Total System Global Area  251658240 bytes
Fixed Size                  1218820 bytes
Variable Size             125830908 bytes
Database Buffers          121634816 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/disk2/lx02/oradata/redo02a.log'
ORA-00312: online log 2 thread 1: '/disk1/lx02/oradata/redo02b.log'

05:20:29 SQL> alter database clear logfile group 2;

Database altered.

05:21:00 SQL> alter database open;

Database altered.

05:21:08 SQL>
       3、当前日志组丢失
      
    
05:22:16 SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         12   10485760          2 NO  INACTIVE                386507 02-AUG-11
         3          1         14   10485760          2 NO  CURRENT                 386751 02-AUG-11
         2          1         13   10485760          2 NO  ACTIVE                  386654 02-AUG-11

05:22:17 SQL>

05:22:17 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
05:22:36 SQL> !
[oracle@oracle ~]$ rm /disk2/lx02/oradata/redo03a.log
[oracle@oracle ~]$ rm /disk1/lx02/oradata/redo03b.log
[oracle@oracle ~]$ !sql
sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 2 05:23:03 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

05:23:03 SQL> startup
ORACLE instance started.

Total System Global Area  251658240 bytes
Fixed Size                  1218820 bytes
Variable Size             125830908 bytes
Database Buffers          121634816 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/disk2/lx02/oradata/redo03a.log'
ORA-00312: online log 3 thread 1: '/disk1/lx02/oradata/redo03b.log'

05:23:10 SQL>
告警日志：
Errors in file /u01/app/oracle/admin/lx02/bdump/lx02_lgwr_9314.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/disk1/lx02/oradata/redo03b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/disk2/lx02/oradata/redo03a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Tue Aug  2 05:23:10 2011
Errors in file /u01/app/oracle/admin/lx02/bdump/lx02_lgwr_9314.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/disk1/lx02/oradata/redo03b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/disk2/lx02/oradata/redo03a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-313 signalled during: ALTER DATABASE OPEN...

解决：

05:23:10 SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/disk1/lx02/oradata/redo03b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/disk2/lx02/oradata/redo03a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
--------对于当前日志组不能clear

05:24:04 SQL> recover database until cancel;
Media recovery complete.
05:24:23 SQL> alter database open resetlogs;

Database altered.

05:24:41 SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          2   10485760          2 NO  CURRENT                 386892 02-AUG-11
         3          1          1   10485760          2 NO  INACTIVE                386891 02-AUG-11
         2          1          0   10485760          2 YES UNUSED                       0

05:24:44 SQL> alter system switch logfile;

System altered.

05:26:28 SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          2   10485760          2 NO  ACTIVE                  386892 02-AUG-11
         3          1          1   10485760          2 NO  INACTIVE                386891 02-AUG-11
         2          1          3   10485760          2 NO  CURRENT                 387003 02-AUG-11

05:26:29 SQL>

本文出自 “天涯客的blog” 博客，请务必保留此出处http://tiany.blog.51cto.com/513694/792465