服务器断电后一连串的异常处理

最新推荐文章于 2022-10-22 18:13:16 发布

weixin_33946020

最新推荐文章于 2022-10-22 18:13:16 发布

阅读量254

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/B97128721/p/3895380.html

版权

（因为服务器是在分店，具体的环境不了解）

通过日志发现，应该是早晨7:44重新开启了服务器，至于是什么时候断电就不清楚了。

首先是有人反应连不到数据库，远程登录后，查看监听是正常的，然后查看数据为的状态

SQL> select status from v$instance;

STATUS
------------------------
STARTED

发现数据库没有OPEN，关闭数据库，然后再打开，看看有什么报错信息

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1010827264 bytes
Fixed Size                  2088920 bytes
Variable Size             608174120 bytes
Database Buffers          394264576 bytes
Redo Buffers                6299648 bytes
ORA-00214: control file '/u02/oradata/dba/control02.ctl' version 133052
inconsistent with file '/u02/oradata/dba/control01.ctl' version 133048

控制文件出了问题，第一个和第二个控制文件异常，第三个应该是好的吧

SQL> alter system set control_files='/u02/oradata/dba/control03.ctl' scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1010827264 bytes
Fixed Size                  2088920 bytes
Variable Size             608174120 bytes
Database Buffers          394264576 bytes
Redo Buffers                6299648 bytes
Database mounted.

第三个控制文件确实可用，这样问题就简单了，把第三个控制文件覆盖第一和第二个控制文件即可

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> exit

-bash-3.2$ cd /u02/oradata/dba
-bash-3.2$ cp control03.ctl control01.ctl
-bash-3.2$ cp control03.ctl control02.ctl

-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 6 11:48:15 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1010827264 bytes
Fixed Size                  2088920 bytes
Variable Size             608174120 bytes
Database Buffers          394264576 bytes
Redo Buffers                6299648 bytes
SQL> alter system set control_files='/u02/oradata/dba/control01.ctl','/u02/oradata/dba/control02.ctl','/u02/oradata/dba/control03.ctl' scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1010827264 bytes
Fixed Size                  2088920 bytes
Variable Size             608174120 bytes
Database Buffers          394264576 bytes
Redo Buffers                6299648 bytes
Database mounted.
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: '/u02/oradata/dba/sysaux01.dbf'

还有数据文件需要恢复，这也好办

SQL> recover datafile 3;
Media recovery complete.

最后修复了近10个数据文件，以为这下总可以了吧，可是在重启数据库时又出现了新的问题

SQL> startup
ORACLE instance started.

Total System Global Area 1010827264 bytes
Fixed Size                  2088920 bytes
Variable Size             608174120 bytes
Database Buffers          394264576 bytes
Redo Buffers                6299648 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error

经查问题，修改了两个参数，重启数据库成功

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1010827264 bytes
Fixed Size                  2088920 bytes
Variable Size             608174120 bytes
Database Buffers          394264576 bytes
Redo Buffers                6299648 bytes
SQL> alter system set events '10046 trace name context off';

System altered.

SQL> alter system set timed_statistics=false;

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1010827264 bytes
Fixed Size                  2088920 bytes
Variable Size             608174120 bytes
Database Buffers          394264576 bytes
Redo Buffers                6299648 bytes
Database mounted.
Database opened.
SQL>

以为问题解决了，于是去翻了一下alert，发现alert中有很多的错误

Errors in file /u01/app/oracle/admin/dba/bdump/ora8_smon_5028.trc:
ORA-01595: error freeing extent (18) of rollback segment (11))
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [14], [16], [], [], [], [], []
Wed Aug  6 13:35:50 2014
DEBUG: Replaying xcb 0x9abcc418, pmd 0x991fa808 for failed op 8
Doing block recovery for file 18 block 6187
No block recovery was needed
Wed Aug  6 13:35:50 2014
Errors in file /u01/app/oracle/admin/dba/bdump/ora8_j000_5050.trc:
ORA-00600: internal error code, arguments: [4194], [18], [23], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [18], [23], [], [], [], [], []
Wed Aug  6 13:35:51 2014
DEBUG: Replaying xcb 0x9abcc418, pmd 0x991fa808 for failed op 8
Wed Aug  6 13:35:51 2014
Errors in file /u01/app/oracle/admin/dba/bdump/ora8_j000_5050.trc:
ORA-00600: internal error code, arguments: [4194], [18], [23], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [18], [23], [], [], [], [], []
Doing block recovery for file 18 block 6187
No block recovery was needed
Wed Aug  6 13:35:51 2014
Errors in file /u01/app/oracle/admin/dba/bdump/ora8_mmon_5034.trc:
ORA-00600: internal error code, arguments: [4194], [18], [23], [], [], [], [], []
Wed Aug  6 13:35:55 2014
DEBUG: Replaying xcb 0x9abcc418, pmd 0x991fa808 for failed op 8
Doing block recovery for file 18 block 6187
No block recovery was needed
Wed Aug  6 13:35:55 2014
Errors in file /u01/app/oracle/admin/dba/bdump/ora8_pmon_5016.trc:
ORA-00600: internal error code, arguments: [4194], [18], [23], [], [], [], [], []
Wed Aug  6 13:35:57 2014
Errors in file /u01/app/oracle/admin/dba/bdump/ora8_pmon_5016.trc:
ORA-00600: internal error code, arguments: [4194], [18], [23], [], [], [], [], []
PMON: terminating instance due to error 472
Instance terminated by PMON, pid = 5016

从报错信息中发现，可能与回滚段有关，并且数据库被强行关闭。

此时想如果换一个undo表空间是否可以启动数据库，由于数据库使用的undo表空间不是系统默认的，所以此时想到改回默认的undo表空间试一下，后来发现还是不行，于是将undo的管理方式改为手动，此时可以正常启动数据库

SQL> startup mount
ORACLE instance started.

Total System Global Area 1010827264 bytes
Fixed Size                  2088920 bytes
Variable Size             608174120 bytes
Database Buffers          394264576 bytes
Redo Buffers                6299648 bytes
Database mounted.
SQL> alter system set undo_management=manual scope=spfile;

System altered.

SQL> alter system set undo_tablespace=UNDOTBS01 scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1010827264 bytes
Fixed Size                  2088920 bytes
Variable Size             608174120 bytes
Database Buffers          394264576 bytes
Redo Buffers                6299648 bytes
Database mounted.
Database opened.

然后将原来的undo表空间删除、重建，再重启数据，正常打开

SQL> drop tablespace jfsys_rb including contents and datafiles cascade constraints;

System altered.

SQL> create undo tablespace jfsys_rb datafile '/u02/oradata/jfsys_rb.dbf' size 4G;

Tablespace created.

SQL> alter tablespace jfsys_rb add datafile '/u02/oradata/jfsys_rb_add.dbf' size 4G;

Tablespace altered.

SQL> alter system set undo_tablespace=jfsys_rb scope=spfile;

System altered.

SQL> alter system set undo_management=auto scope=spfile;

System altered.

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

Total System Global Area 1010827264 bytes
Fixed Size                  2088920 bytes
Variable Size             608174120 bytes
Database Buffers          394264576 bytes
Redo Buffers                6299648 bytes
Database mounted.
Database opened.