掉电引起的ORA-1172错误的解决方法

这期内容当中小编将会给大家带来有关掉电引起的ORA-1172错误的解决方法，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

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

尝试打开数据库。

下面尝试用EVENTS方式打开数据库，不过由于出现ORA-600 2662错误的机制与上面一篇文章不同，因此这里不需要设置隐含参数_allow_resetlogs_corruption。不过由于当前的版本是10g，因此需要设置隐含参数_allow_error_simulation为true，这时使用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错误外，又新增了600的2256错误。根据METALINK文档Doc ID:  Note:30681.1的描述，这时需要对ADJUST_SCN的LEVEL进行调整：

将参数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-600的4194和4193错误，根据错误信息的看来是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导出，建立一个干净的数据库，然后导入既可。

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

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.

上述就是小编为大家分享的掉电引起的ORA-1172错误的解决方法了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注亿速云行业资讯频道。