分类： 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）重新启动数据库和业务
六、 执行结果
数据库故障解决，数据库备份正常，业务恢复。
七、 遗留问题
无
八、 更换备件列表
