掉电引起的ORA-1172错误解决过程(二)

  1. 云栖社区>
  2. 博客>
  3. 正文

掉电引起的ORA-1172错误解决过程(二)

苍雪明南 2017-07-17 11:41:59 浏览813
展开阅读全文

由于UPS故障,导致机房连续多次掉电,问题解决后,发现一台本地测试数据库打开时报错,ORA-1172ORA-1151错误。

掉电引起的ORA-1172错误解决过程(一):http://yangtingkun.itpub.net/post/468/465223

尝试打开数据库。

 

 

上一篇介绍了问题的产生和现象,下面尝试用EVENTS方式打开数据库,不过由于出现ORA-600 2662错误的机制与上面一篇文章不同,因此这里不需要设置隐含参数_allow_resetlogs_corruption。不过由于当前的版本是10g,因此需要设置隐含参数_allow_error_simulationtrue,这时使用EVENTS调整SCN的前提。

SQL> conn / as sysdba
Connected to an idle instance.
SQL> create pfile='/home/oracle/inittest08.ora' from spfile;

File created.

编辑这个初始化参数文件,添加_ALLOW_ERROR_SIMULATION=true到这个文件中,并将数据库启动到mount状态:

SQL> startup pfile=/home/oracle/inittest08.ora mount
ORACLE instance started.

Total System Global Area 2483027968 bytes
Fixed Size                  2074760 bytes
Variable Size            1090520952 bytes
Database Buffers         1375731712 bytes
Redo Buffers               14700544 bytes
Database mounted.

通过EVENTS调整SCN

SQL> alter session set events '10015 trace name adjust_scn level 1';   

Session altered.

下面尝试打开数据库:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

尝试打开仍然报错,再次检查alert文件:

Errors in file /opt/ora10g/admin/test08/udump/test08_ora_6525.trc:
ORA-00600: internal error code, arguments: [2256], [0], [1073741824], [1], [2238656971], [], [], []
Thu Jun  5 16:32:01 2008
Errors in file /opt/ora10g/admin/test08/udump/test08_ora_6525.trc:
ORA-00600: internal error code, arguments: [2662], [1], [2238656973], [1], [2238756337], [8388637], [], []
Thu Jun  5 16:32:04 2008
Errors in file /opt/ora10g/admin/test08/udump/test08_ora_6525.trc:
ORA-00600: internal error code, arguments: [2662], [1], [2238656973], [1], [2238756337], [8388637], [], []
Thu Jun  5 16:32:04 2008
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 6525
ORA-1092 signalled during: alter database open...

除了刚才的ORA-600 2662错误外,又新增了6002256错误。根据METALINK文档Doc ID:  Note:30681.1的描述,这时需要对ADJUST_SCNLEVEL进行调整:

将参数c的值乘以4再根据SCN的范围确定LEVEL的值,根据需要将LEVEL设置为7,然后尝试尝试打开数据库:

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile=/home/oracle/inittest08.ora mount
ORACLE instance started.

Total System Global Area 2483027968 bytes
Fixed Size                  2074760 bytes
Variable Size            1090520952 bytes
Database Buffers         1375731712 bytes
Redo Buffers               14700544 bytes
Database mounted.
SQL> alter session set events '10015 trace name adjust_scn level 7';

Session altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error

错误信息这次发生了变化,检查alert文件:

Errors in file /opt/ora10g/admin/test08/udump/test08_ora_6700.trc:
ORA-00600: internal error code, arguments: [4194], [30], [29], [], [], [], [], []
Thu Jun  5 16:43:30 2008
Errors in file /opt/ora10g/admin/test08/bdump/test08_smon_6686.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 32529)
ORA-01110: data file 1: '/data/oradata/test08/system01.dbf'
Thu Jun  5 16:43:31 2008
Errors in file /opt/ora10g/admin/test08/bdump/test08_smon_6686.trc:
ORA-00600: internal error code, arguments: [4194], [109], [98], [], [], [], [], []
Thu Jun  5 16:43:33 2008
Doing block recovery for file 2 block 133
Block recovery from logseq 531, block 60 to scn 7516192829
Thu Jun  5 16:43:33 2008
Recovery of Online Redo Log: Thread 1 Group 2 Seq 531 Reading mem 0
  Mem# 0: /data/oradata/test08/redo02.log
Block recovery stopped at EOT rba 531.62.16
Block recovery completed at rba 531.62.16, scn 1.3221225531
Doing block recovery for file 2 block 5
Block recovery from logseq 531, block 60 to scn 7516192826
Thu Jun  5 16:43:33 2008
Recovery of Online Redo Log: Thread 1 Group 2 Seq 531 Reading mem 0
  Mem# 0: /data/oradata/test08/redo02.log
Block recovery completed at rba 531.62.16, scn 1.3221225531
Thu Jun  5 16:43:33 2008
Errors in file /opt/ora10g/admin/test08/bdump/test08_smon_6686.trc:
ORA-01595: error freeing extent (2) of rollback segment (1))
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [109], [98], [], [], [], [], []
Thu Jun  5 16:43:34 2008
Doing block recovery for file 2 block 1102
Block recovery from logseq 531, block 58 to scn 7516192831
Thu Jun  5 16:43:34 2008
Recovery of Online Redo Log: Thread 1 Group 2 Seq 531 Reading mem 0
  Mem# 0: /data/oradata/test08/redo02.log
Block recovery stopped at EOT rba 531.62.16
Block recovery completed at rba 531.62.16, scn 1.3221225531
Doing block recovery for file 2 block 61
Block recovery from logseq 531, block 58 to scn 7516192825
Thu Jun  5 16:43:34 2008
Recovery of Online Redo Log: Thread 1 Group 2 Seq 531 Reading mem 0
  Mem# 0: /data/oradata/test08/redo02.log
Block recovery completed at rba 531.60.16, scn 1.3221225530
Thu Jun  5 16:43:34 2008
Errors in file /opt/ora10g/admin/test08/udump/test08_ora_6700.trc:
ORA-00600: internal error code, arguments: [4193], [4306], [4309], [], [], [], [], []
DEBUG: Replaying xcb 0xf0eba330, pmd 0xf3d4c360 for failed op 8
Doing block recovery for file 2 block 1085
No block recovery was needed

这次又出现了ORA-60041944193错误,根据错误信息的看来是Oracle进行恢复的过程中出现了问题。查询METALINK,发现是REDO中的回滚记录和UNDO中的不一致造成的。尝试使用隐含参数_CORRUPTED_ROLLBACK_SEGMENTS来打开数据库。在刚才的建立的inittest08.ora初始化文件中添加下面的信息:

undo_management='MANUAL'
_corrupted_rollback_segments=(_SYSSMU1&,_SYSSMU2&,_SYSSMU3&,_SYSSMU4&,_SYSSMU5&,_SYSSMU6&,_SYSSMU7&,_SYSSMU8&,_SYSSMU9&,_SYSSMU10&,_SYSSMU11&,_SYSSMU12&,_SYSSMU13&,_SYSSMU14&,_SYSSMU15&,_SYSSMU16&,_SYSSMU17&,_SYSSMU18&,_SYSSMU19&,_SYSSMU20&,_SYSSMU21&,_SYSSMU22&,_SYSSMU23&,_SYSSMU24&,_SYSSMU25&,_SYSSMU26&,_SYSSMU27&,_SYSSMU28&,_SYSSMU29&,_SYSSMU30&,_SYSSMU31&,_SYSSMU32&,_SYSSMU33&,_SYSSMU34&,_SYSSMU35&,_SYSSMU36&,_SYSSMU37&,_SYSSMU38&,_SYSSMU39&,_SYSSMU40&,_SYSSMU41&)

尝试启动数据库:

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile=/home/oracle/inittest08.ora
ORACLE instance started.

Total System Global Area 2483027968 bytes
Fixed Size                  2074760 bytes
Variable Size            1090520952 bytes
Database Buffers         1375731712 bytes
Redo Buffers               14700544 bytes
Database mounted.
Database opened.

重要数据库成功的打开,由于使用了初始化参数_CORRUPTED_ROLLBACK_SEGMENTS,可能导致数据库出现不一致的状态,因此很可能已经造成了数据的丢失,不过好在是测试数据库。下面只需要将非系统的SCHEMA导出,建立一个干净的数据库,然后导入既可。

此时其实并没有解决掉REDOUNDO中记录冲突的问题,如果关闭数据库,尝试不适应隐含参数打开,会碰到下面的错误:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2483027968 bytes
Fixed Size                  2074760 bytes
Variable Size            1090520952 bytes
Database Buffers         1375731712 bytes
Redo Buffers               14700544 bytes
Database mounted.
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [30], [29], [], [], [], [],
[]


SQL> shutdown immediate
ORA-03113: end-of-file on communication channel
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile=/home/oracle/inittest08.ora
ORACLE instance started.

Total System Global Area 2483027968 bytes
Fixed Size                  2074760 bytes
Variable Size            1090520952 bytes
Database Buffers         1375731712 bytes
Redo Buffers               14700544 bytes
Database mounted.
Database opened.

 

 

网友评论

登录后评论
0/500
评论
苍雪明南
+ 关注