亲身经历故障－－如何解决

－－－－－－－－－－－－－－－

故障汇总

－－－－－－－－－－－－－－－

故障一：

（１）问题描述：

SQL> alter database open;
alter database open
ERROR at line 1:
ORA-16014: log 1 sequence# 576 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/tt/redo01.log'

（２）解决办法：

SQL> show parameter log_archive

SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/flash_recovery_area/TT/archivelog';
System altered.
SQL> alter database open;
Database altered.

故障二：

启动时提示日志不一致

        SQL> startup

        ORACLE instance started.

         Total System Global Area  251658240 bytes

        Fixed Size                  1218796 bytes

        Variable Size              83887892 bytes

        Database Buffers          163577856 bytes

        Redo Buffers                2973696 bytes

        Database mounted.

        ORA-00341:log 1 of thread 1,wrong log # in header

        ORA-00312:online log 1 thread 1:'/u01/app/oracle/oradata/orcl/redo1a.rdo'  

        ORA-00312:online log 1 thread 1:'/u01/app/oracle/oradata/orcl/redo1b.rdo'

 解决办法：

        SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

         Database altered.

         SQL> ALTER DATABASE OPEN;

         Database opened.

故障三：

日志文件丢失(非current状态日志组)

 SQL> startup

      ORACLE instance started.

      Total System Global Area  251658240 bytes

        Fixed Size                  1218796 bytes

        Variable Size              88082196 bytes

        Database Buffers          159383552 bytes

        Redo Buffers                2973696 bytes

        Database mounted.

        ORA-00313: open failed for members of log group 1 of thread 1

        ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo1a.rdo'

        ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo1b.rdo'

解决办法：

 SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

      Database altered.

 SQL> ALTER DATABASE OPEN;

      Database altered.

故障四：

日志文件丢失(current状态日志组)

        SQL> startup

        ORACLE instance started.

        Total System Global Area  251658240 bytes

        Fixed Size                  1218796 bytes

        Variable Size              83887892 bytes

        Database Buffers          163577856 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: '/u01/app/oracle/oradata/orcl/redo3a.rdo'

        ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo3b.rdo'

解决办法：

--查看告警日志

        SQL> ho tail -n 30 /u01/app/oracle/admin/orcl/bdump/alert_orcl.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: '/u01/app/oracle/oradata/orcl/redo3a.rdo'

        ORA-27037: unable to obtain file status

        Linux Error: 2: No such file or directory

        Additional information: 3

        Tue Jul 20 10:45:58 2010

        Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_lgwr_4112.trc:

        ORA-00313: open failed for members of log group 3 of thread 1

        ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo3b.rdo'

        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: '/u01/app/oracle/oradata/orcl/redo3a.rdo'

        ORA-27037: unable to obtain file status

        Linux Error: 2: No such file or directory

        Additional information: 3

        Tue Jul 20 10:45:58 2010

        ARC0: STARTING ARCH PROCESSES

        Tue Jul 20 10:45:58 2010

        ARC1: Becoming the 'no FAL' ARCH

        ARC1: Becoming the 'no SRL' ARCH

        Tue Jul 20 10:45:58 2010

        ARC2: Archival started

        ARC0: STARTING ARCH PROCESSES COMPLETE

        ARC0: Becoming the heartbeat ARCH

        ARC2 started with pid=18, OS id=4137

        Tue Jul 20 10:45:58 2010

        ORA-313 signalled during: ALTER DATABASE OPEN...

        --查看物理日志文件是否存在

        SQL> ho ls /u01/app/oracle/oradata/orcl/redo3a.rdo

        ls: /u01/app/oracle/oradata/orcl/redo3a.rdo: No such file or directory

        SQL> ho ls /u01/app/oracle/oradata/orcl/redo3b.rdo

        ls: /u01/app/oracle/oradata/orcl/redo3b.rdo: No such file or directory

       --尝试使用清空日志组命令

        SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

        ALTER DATABASE CLEAR LOGFILE GROUP 3

        *

        ERROR at line 1:   --系统处于非归档模式，且group 3状态为CURRENT

        ORA-00350: log 3 of instance orcl (thread 1) needs to be archived

        ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo3a.rdo'

        ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo3b.rdo'

         --尝试使用不归档清空日志

        SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

        ALTER DATABASE CLEAR UNARCHIVED 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: '/u01/app/oracle/oradata/orcl/redo3b.rdo'

        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: '/u01/app/oracle/oradata/orcl/redo3a.rdo'

        ORA-27037: unable to obtain file status

        Linux Error: 2: No such file or directory

        Additional information: 3

         --使用带控制文件的介质恢复

        SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;

        ORA-00279: change 2835232 generated at 07/20/2010 10:40:23 needed for thread 1

        ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_07_20/o1_mf_1_39_%u_.arc

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

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

        ORA-00308: cannot open archived log '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_07_20/o1_mf_1_39_%u_.arc'

        ORA-27037: unable to obtain file status

        Linux Error: 2: No such file or directory

        Additional information: 3

         --使用resetlogs选项打开数据库

        SQL> ALTER DATABASE OPEN RESETLOGS;

         Database altered.      

         SQL> SELECT * FROM v$log;   --系统重建group 3

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

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

                 1          1          2   31457280          2 NO  CURRENT                2835234 20-JUL-10

                 2          1          1   31457280          2 YES INACTIVE               2835233 20-JUL-10

                 3          1          0   31457280          2 YES UNUSED                       0          

        SQL> SELECT * FROM v$logfile;   --为group 3增加了两个成员redo3a.rdo ，redo3b.rdo  

             GROUP# STATUS  TYPE    MEMBER                                                  IS_

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

                 2         ONLINE  /u01/app/oracle/oradata/orcl/redo2a.rdo                 NO

                 2         ONLINE  /u01/app/oracle/oradata/orcl/redo2b.rdo                 NO

                 1         ONLINE  /u01/app/oracle/oradata/orcl/redo1a.rdo                 NO

                 3         ONLINE  /u01/app/oracle/oradata/orcl/redo3a.rdo                 NO

                 3         ONLINE  /u01/app/oracle/oradata/orcl/redo3b.rdo                 NO

                 1         ONLINE  /u01/app/oracle/oradata/orcl/redo1b.rdo                 NO      

 

        对于CURRENT组的也可以使用隐藏参数来解决

        步骤：

            alter system set "_allow_resetlogs_corruption" = true scope = spfile;

            recover database using bakcup controlfile;

            alter database open resetlogs;

            shutdown immediate;

            startup mount;

            alter database open resetlogs;

            alter system reset "_allow_resetlogs_corruption" scope = spfile sid = '*'

           对于归档模式下的日志文件丢失，同样可以按上述步骤处理

故障五：

（1）控制文件不一致问题：

我们可以通过拷贝高版本的控制文件到你新增加的几个控制文件，具体做法(确保control01 的版本号是最高的): 

①cp /u01/oracle/oradata/orcl/control01.ctl  /u01/oracle/oradata/orcl/control02.ctl 

②cp /u01/oracle/oradata/orcl/control01.ctl  /u01/oracle/oradata/orcl/control03.ctl  

此时就可以正常启动数据库了！

（2）控制文件（丢失某一个，不是全部丢失）：用现存的拷贝其他的就行。

cp /u01/oracle/oradata/orcl/control01.ctl  /u01/oracle/oradata/orcl/control02.ctl 

cp /u01/oracle/oradata/orcl/control01.ctl  /u01/oracle/oradata/orcl/control03.ctl  

（3）全部丢失，非归档模式，有没有做过备份，解决办法只有一个，重建控制文件：

重建控制文件比较困难，需要知道有多少日志文件，多少数据文件，及其对应路径：

①查看有多少日志文件和数据文件：  ls  /u01/oracle/oradata/orcl/     

②查看告警日志文件： cat  $ORACLE_HOME/dbs/alert_orcl.log 

③sql>create controlfile reuse database orcl noarchivelog norestlogs 

       maxlogfiles 16

       maxinstances 8

       maxlogmembers 3

       maxloghistory 1

       datafile

       '/u01/app/oracle/oradata/orcl/system.dbf',

      '………………………………………………'

     logfile

    group 1  '/u01/app/oracle/oradata/orcl/redo01.log' size 50M,

    group 2  '/u01/app/oracle/oradata/orcl/redo02.log' size 50M,

    group 2  '/u01/app/oracle/oradata/orcl/redo03.log' size 50M

    character set UTF8

    /

④sql>recover database; 

⑤sql>alter database open;

(4)归档模式下丢失控制文件,日志文件都在，之前做过备份sql>alter database backup controlfile to '/u01/oracle/control2014.ctl'：

①show parameter control_files  查看控制文件有几个

②在系统目录下查看控制文件是否存在： ls /u01/oracle/

③拷贝备份的控制文件（有几个控制文件拷贝几次），一定要到到对应的路径：

cp /u01/oracle/control2014.ctl /u01/oracle/control01.ctl

cp /u01/oracle/control2014.ctl /u01/oracle/control02.ctl

cp /u01/oracle/control2014.ctl /u01/oracle/control03.ctl

④sql>alter database recover database using backup controlfile;  ---日志文件，归档日志都在，使用老版本的控制文件一定要这样做

⑤shutdown immediate

   startup

⑥alter database recover database using backup controlfile; 

⑦alter database recover database using backup controlfile until change 1071696;

⑧shutdown immediate;

   startup;

   alter database recover database using backup controlfile until change 1071696;

   alter database open resetlogs;

⑨shutdown immediate;

   startup;

alter database open resetlogs;

⑩实在没办法，一种极端的办法：

 alter system set "_allow_resetlogs_corruption"=true scope=spfile;   ------不用验证一致性

startup force;

alter database open resetlogs;