Chinaunix首页 | 论坛 | 博客
  • 博客访问: 3396843
  • 博文数量: 631
  • 博客积分: 10716
  • 博客等级: 上将
  • 技术积分: 8397
  • 用 户 组: 普通用户
  • 注册时间: 2008-04-01 22:35
文章分类

全部博文(631)

文章存档

2020年(2)

2019年(22)

2018年(4)

2017年(37)

2016年(22)

2015年(1)

2013年(12)

2012年(20)

2011年(19)

2010年(20)

2009年(282)

2008年(190)

分类: Oracle

2008-06-06 11:04:55

一、        问题的类型:数据库

二、        问题的由来:

CASE号:TRIT0830057

用户名称:广东电信惠州分公司

处理工程师:闫伟


三、        问题的详细描述:

由于惠州的短信中心的短信数据库服务器所带的3510阵列异常掉电,导致数据库当机,无法启动,启动信息如下:

SQL> startup

ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translated

ORA-07286: sksagdi: cannot obtain device information.

SVR4 Error: 5: I/O error

SQL> !

$ exit

        手工进入3510挂接点目录:

root@hz-smscdr # cd opt/oracle

root@hz-smscdr # ls

db01      db02      db03      db04      smsdata   smsdata1

root@hz-smscdr # cd smsdata

root@hz-smscdr # ls

.: I/O错误

        在手工umount和mount挂接点,重新启动数据库:

 SQL> startup

ORACLE instance started.


Total System Global Area  695078588 bytes

Fixed Size                   102076 bytes

Variable Size             121004032 bytes

Database Buffers          573440000 bytes

Redo Buffers                 532480 bytes

Database mounted.

ORA-01545: rollback segment 'SMS_RBS' specified not available

四、        问题的分析和初步的解决思路:

此数据库故障时候数据库所建立的database由于所在3510阵列突然掉电,导致数据库异常当机,数据不一致,在重新加电,重新启动数据库,无法正常启动,发现所有的3510上的mount点,都存在IO错误,在手工umount和mount之后启动数据库发现数据库中的UNDO表空间已经损坏,无法启动数据库,这个时候要通过在初始化参数文件中添加特别的隐含参数文件_corrupted_rollback_segments =(SMS_RBS),是数据库能够正常启动,然后删除数据库中的SMS_RBS表空间,恢复受到影响的数据库文件,重建SMS_RBS表空间,关闭数据库,去掉初始化参数中的corrupted_rollback_segments =(SMS_RBS),在重新启动数据库,做数据库全备,使数据库正常。

五、        问题解决的详细步骤

1)umount 挂接点

  root@hz-smscdr # umount /opt/oracle/smsdata

  root@hz-smscdr # umount /opt/oracle/smsdata1

  root@hz-smscdr # mount /dev/dsk/c4t40d0s0 /opt/oracle/smsdata


2)mount 挂接点


 root@hz-smscdr # mount /dev/dsk/c4t40d0s0 /opt/oracle/smsdata

 root@hz-smscdr # mount /dev/dsk/c4t40d1s6  /opt/oracle/smsdata1


 3)通过查看alert日志,发现是ROLLBACK_SEGMENTS的问题

注释掉在init文件中的#rollback_segments=(SMS_RBS)

    

 4)在init文件中添加

 rollback_segments=(SMS_RBS)


_corrupted_rollback_segments =(SMS_RBS)


5)删除SMS_RBS


SQL> DROP ROLLBACK SEGMENT SMS_RBS;


Rollback segment dropped.


6)shutdown 数据库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.


7)注释掉在init文件中的corrupted_rollback_segments =(SMS_RBS)



8)启动数据库并查看数据库文件状态


SQL> set linesize 320

SQL> col name for a46

SQL> select file#,name,status from v$datafile;


     FILE# NAME                                           STATUS

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

         1 /opt/oracle/db02/oradata/VASSMS/system01.dbf   SYSTEM

         2 /opt/oracle/db02/oradata/VASSMS/tools01.dbf    ONLINE

         3 /opt/oracle/db02/oradata/VASSMS/rbs01.dbf      ONLINE

         4 /opt/oracle/db02/oradata/VASSMS/temp01.dbf     ONLINE

         5 /opt/oracle/db02/oradata/VASSMS/users01.dbf    ONLINE

         6 /opt/oracle/db02/oradata/VASSMS/indx01.dbf     ONLINE

         7 /opt/oracle/smsdata/VASSMS/permanent_sms_001.d ONLINE

           bf


         8 /opt/oracle/smsdata/VASSMS/permanent_sms_002.d ONLINE

           bf


     FILE# NAME                                           STATUS

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


         9 /opt/oracle/smsdata/VASSMS/permanent_sms_003.d ONLINE

           bf


        10 /opt/oracle/smsdata/VASSMS/permanent_sms_004.d ONLINE

           bf


        11 /opt/oracle/smsdata/VASSMS/permanent_sms_005.d ONLINE

           bf


        12 /opt/oracle/smsdata/VASSMS/permanent_sms_006.d ONLINE


     FILE# NAME                                           STATUS

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

           bf


        13 /opt/oracle/smsdata/VASSMS/permanent_sms_007.d ONLINE

           bf


        14 /opt/oracle/smsdata/VASSMS/permanent_sms_008.d ONLINE

           bf


        15 /opt/oracle/smsdata/VASSMS/permanent_sms_009.d RECOVER

           bf



     FILE# NAME                                           STATUS

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

        16 /opt/oracle/smsdata/VASSMS/permanent_sms_010.d RECOVER

           bf


        17 /opt/oracle/smsdata/VASSMS/temporary_sms-001.d ONLINE

           bf


        18 /opt/oracle/smsdata/VASSMS/idx_sm_pas_cdr01.db RECOVER

           f


        19 /opt/oracle/smsdata/VASSMS/idx_sm_pas_cdr02.db RECOVER

           f


     FILE# NAME                                           STATUS

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


        20 /opt/oracle/smsdata/VASSMS/idx_sm_pas_cdr03.db RECOVER

           f


        21 /opt/oracle/smsdata/VASSMS/idx_sm_pas_cdr04.db RECOVER

           f


        22 /opt/oracle/smsdata/VASSMS/temporary_sms-002.d ONLINE

           bf


        23 /opt/oracle/smsdata/VASSMS/idx_sm_pas_cdr05.db RECOVER


     FILE# NAME                                           STATUS

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

           f


        24 /opt/oracle/smsdata/VASSMS/idx_sm_pas_cdr06.db RECOVER

           f


        25 /opt/oracle/smsdata/VASSMS/idx_sm_pas_cdr07.db RECOVER

           f


        26 /opt/oracle/smsdata/VASSMS/idx_sm_pas_cdr08.db RECOVER

           f



     FILE# NAME                                           STATUS

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

        27 /opt/oracle/smsdata/VASSMS/idx_sm_pas_cdr09.db RECOVER

           f


        28 /opt/oracle/smsdata/VASSMS/idx_sm_pas_cdr10.db RECOVER

           f


        29 /opt/oracle/smsdata/VASSMS/idx_sm_pas_cdr11.db RECOVER

           f


        30 /opt/oracle/smsdata/VASSMS/idx_sm_pas_cdr12.db RECOVER

           f


     FILE# NAME                                           STATUS

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


        31 /opt/oracle/smsdata/VASSMS/idx_sm_pas_cdr13.db RECOVER

           f


        32 /opt/oracle/smsdata/VASSMS/permanent_sms_011.d ONLINE

           bf


        33 /opt/oracle/smsdata/VASSMS/permanent_sms_012.d RECOVER

           bf


        34 /opt/oracle/smsdata/VASSMS/permanent_sms_013.d RECOVER


     FILE# NAME                                           STATUS

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

           bf


        35 /opt/oracle/smsdata/VASSMS/permanent_sms_014.d ONLINE

           bf


        36 /opt/oracle/smsdata/VASSMS/permanent_sms_015.d ONLINE

           bf


        37 /opt/oracle/smsdata/VASSMS/permanent_sms_016.d ONLINE

           bf



     FILE# NAME                                           STATUS

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

        38 /opt/oracle/smsdata/VASSMS/permanent_sms_017.d ONLINE

           bf


        39 /opt/oracle/smsdata/VASSMS/permanent_sms_018.d ONLINE

           bf


        40 /opt/oracle/smsdata/VASSMS/permanent_sms_019.d ONLINE

           bf


        41 /opt/oracle/smsdata/VASSMS/permanent_sms_020.d ONLINE

           bf


     FILE# NAME                                           STATUS

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


        42 /opt/oracle/smsdata/VASSMS/permanent_sms_021.d ONLINE

           bf


        43 /opt/oracle/smsdata/VASSMS/permanent_sms_022.d RECOVER

           bf


        44 /opt/oracle/smsdata/VASSMS/permanent_sms_023.d RECOVER

           bf


        45 /opt/oracle/smsdata/VASSMS/permanent_sms_024.d RECOVER


     FILE# NAME                                           STATUS

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

           bf


        46 /opt/oracle/smsdata/VASSMS/permanent_sms_025.d RECOVER

           bf


        47 /opt/oracle/smsdata/VASSMS/permanent_sms_026.d RECOVER

           bf


        48 /opt/oracle/smsdata/VASSMS/permanent_sms_027.d ONLINE

           bf



     FILE# NAME                                           STATUS

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

        49 /opt/oracle/smsdata/VASSMS/permanent_sms_028.d ONLINE

           bf


        50 /opt/oracle/smsdata/VASSMS/permanent_sms_029db RECOVER

           f


        51 /opt/oracle/smsdata/VASSMS/permanent_sms_030db ONLINE

           f


        52 /opt/oracle/smsdata/VASSMS/idx_sm_pas_cdr14.db RECOVER

           f


     FILE# NAME                                           STATUS

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


        53 /opt/oracle/smsdata/VASSMS/idx_sm_pas_cdr15.db RECOVER

           f


        54 /opt/oracle/smsdata/VASSMS/permanent_sms_031.d ONLINE

           bf


        55 /opt/oracle/smsdata/VASSMS/permanent_sms_032.d ONLINE

           bf


        56 /opt/oracle/smsdata/VASSMS/permanent_sms_033.d ONLINE


     FILE# NAME                                           STATUS

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

           bf


        57 /opt/oracle/smsdata/VASSMS/permanent_sms_034.d ONLINE

           bf


        58 /opt/oracle/smsdata/VASSMS/permanent_sms_035.d ONLINE

           bf


        59 /opt/oracle/smsdata/VASSMS/permanent_sms_036.d ONLINE

           bf



     FILE# NAME                                           STATUS

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

        60 /opt/oracle/smsdata/VASSMS/permanent_sms_037.d ONLINE

           bf


        61 /opt/oracle/smsdata/VASSMS/permanent_sms_038.d ONLINE

           bf


        62 /opt/oracle/smsdata/VASSMS/permanent_sms_039.d ONLINE

           bf


        63 /opt/oracle/smsdata/VASSMS/idx_sm_pas_cdr16.db RECOVER

           f


     FILE# NAME                                           STATUS

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


        64 /opt/oracle/smsdata/VASSMS/permanent_sms_040.d ONLINE

           bf


        65 /opt/oracle/smsdata1/VASSMS/permanent_sms_041. ONLINE

           dbf


        66 /opt/oracle/db02/oradata/VASSMS/users          ONLINE


66 rows selected.


9)recover 所有的数据文件


10)建立ROLLBACK SEGMENT SMS_RBS并online建立的表空间

SQL> CREATE ROLLBACK SEGMENT SMS_RBS TABLESPACE SMS  STORAGE ( INITIAL 102400K MINEXTENTS 2  MAXEXTENTS 32765);


Rollback segment created.


SQL> ALTER ROLLBACK SEGMENT SMS_RBS ONLINE;


Rollback segment altered.


11)关闭数据库并重新启动


SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.


Total System Global Area  695078588 bytes

Fixed Size                   102076 bytes

Variable Size             121004032 bytes

Database Buffers          573440000 bytes

Redo Buffers                 532480 bytes

Database mounted.

Database opened.


12)确认ROLLBACK SEGMENT SMS_RBS状态


SQL> set linesize 300

SQL> select  SEGMENT_NAME,OWNER,TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,STATUS from dba_rollback_segs;

2!"

select TABLESPACE_NAME,STATUS from dba_tablespaces;

SEGMENT_NAME                   OWNER  TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS STATUS

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

SYSTEM                         SYS    SYSTEM                                  57344       57344           2         505 ONLINE

RBS0                           PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS1                           PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS2                           PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS3                           PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS4                           PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS5                           PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS6                           PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS7                           PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS8                           PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS9                           PUBLIC RBS                                    524288     4194304           8        4096 ONLINE


SEGMENT_NAME                   OWNER  TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS STATUS

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

RBS10                          PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS11                          PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS12                          PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS13                          PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS14                          PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS15                          PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS16                          PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS17                          PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS18                          PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS19                          PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS20                          PUBLIC RBS                                    524288     4194304           8        4096 ONLINE


SEGMENT_NAME                   OWNER  TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS STATUS

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

RBS21                          PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS22                          PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS23                          PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

RBS24                          PUBLIC RBS                                    524288     4194304           8        4096 ONLINE

SMS_RBS                        SYS    SMS                                 104857600      524288           2       32765 ONLINE


27 rows selected


13)确认数据库进程启动,并正常运行


SQL> !

$ ps -ef|grep ora_

  oracle  7525     1  0 12:08:44 ?        0:00 ora_dbw0_VASSMS

  oracle  7531     1  0 12:08:44 ?        0:00 ora_smon_VASSMS

  oracle  7527     1  0 12:08:44 ?        0:00 ora_lgwr_VASSMS

  oracle  7533     1  0 12:08:44 ?        0:00 ora_reco_VASSMS

  oracle  8181  7539  0 12:09:03 pts/1    0:00 grep ora_

  oracle  7529     1  0 12:08:44 ?        0:00 ora_ckpt_VASSMS

  oracle  7523     1  0 12:08:44 ?        0:00 ora_pmon_VASSMS

  oracle  7535     1  0 12:08:45 ?        0:00 ora_arc0_VASSMS

$ exit


14) 关闭数据库,做数据库冷备

15)重新启动数据库和业务

六、        执行结果

数据库故障解决,数据库备份正常,业务恢复。

七、        遗留问题

八、        更换备件列表

阅读(3109) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~