今天同事反应了一个问题,说是数据库挂了,后来自己recover后能启动了,不过启动不久就会自动关闭。
这个是大致情况,下面我就上去检查了下:
启动数据库:
SQL> startup
ORACLE instance started.
这个是大致情况,下面我就上去检查了下:
启动数据库:
SQL> startup
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size 2084264 bytes
Variable Size 1174405720 bytes
Database Buffers 385875968 bytes
Redo Buffers 14692352 bytes
Database mounted.
Database opened.
--这里看上去没有问题,启动很顺利,接下来看oracle的进程,出现了同事说的问题
[oracle@MACSJK-HN-207 ~]$ ps -ef |grep oracle
oracle 4912 1 11 Feb04 ? 04:50:21 /oracle/ora10/product/bin/tnslsnr LISTENER -inherit
root 5636 5561 0 17:38 pts/0 00:00:00 su - oracle
oracle 5637 5636 0 17:38 pts/0 00:00:00 -bash
oracle 5875 5637 0 17:41 pts/0 00:00:00 ps -ef
oracle 5876 5637 0 17:41 pts/0 00:00:00 grep oracle
--这里没有oracle对应的后台的进程,oracle被自动关闭了?这时候检查日志
Mon Feb 6 17:41:49 2012
Errors in file /oracle/ora10/admin/ora10g/udump/ora10g_ora_5904.trc:
ORA-00600: internal error code, arguments: [4194], [59], [65], [], [], [], [], []
Mon Feb 6 17:41:49 2012
Errors in file /oracle/ora10/admin/ora10g/bdump/ora10g_smon_5894.trc:
ORA-00600: internal error code, arguments: [4194], [18], [7], [], [], [], [], []
出现600错误,这时候大概定位问题的出在undo上了,解决这种问题,基本上就是屏蔽坏的undo段,然后重建undo
具体操作如下:
1,找出活动的回滚段
[oracle@MACSJK-HN-207 ora10g]$ strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u
and substr(drs.segment_name,1,7) != '_SYSSMU'
^D' and substr(drs.segment_name,1,7) != ''_SYSSMU'' ' );
_SYSSMU1
_SYSSMU1
_SYSSMU10
_SYSSMU11
_SYSSMU12
_SYSSMU13
_SYSSMU14
_SYSSMU15
_SYSSMU16
_SYSSMU17
_SYSSMU18
_SYSSMU19
_SYSSMU2
_SYSSMU2
_SYSSMU20
_SYSSMU21
_SYSSMU22
_SYSSMU23
_SYSSMU24
_SYSSMU25
_SYSSMU26
_SYSSMU27
_SYSSMU28
_SYSSMU29
_SYSSMU3
_SYSSMU3
_SYSSMU4
_SYSSMU4
_SYSSMU5
_SYSSMU5
_SYSSMU6
_SYSSMU6
_SYSSMU7
_SYSSMU7
_SYSSMU8
_SYSSMU8
_SYSSMU8
_SYSSMU9
_SYSSMU9
SYSTEM _SYSSMU9
可以看到上面的显示,最大的为_SYSSMU29
Fixed Size 2084264 bytes
Variable Size 1174405720 bytes
Database Buffers 385875968 bytes
Redo Buffers 14692352 bytes
Database mounted.
Database opened.
--这里看上去没有问题,启动很顺利,接下来看oracle的进程,出现了同事说的问题
[oracle@MACSJK-HN-207 ~]$ ps -ef |grep oracle
oracle 4912 1 11 Feb04 ? 04:50:21 /oracle/ora10/product/bin/tnslsnr LISTENER -inherit
root 5636 5561 0 17:38 pts/0 00:00:00 su - oracle
oracle 5637 5636 0 17:38 pts/0 00:00:00 -bash
oracle 5875 5637 0 17:41 pts/0 00:00:00 ps -ef
oracle 5876 5637 0 17:41 pts/0 00:00:00 grep oracle
--这里没有oracle对应的后台的进程,oracle被自动关闭了?这时候检查日志
Mon Feb 6 17:41:49 2012
Errors in file /oracle/ora10/admin/ora10g/udump/ora10g_ora_5904.trc:
ORA-00600: internal error code, arguments: [4194], [59], [65], [], [], [], [], []
Mon Feb 6 17:41:49 2012
Errors in file /oracle/ora10/admin/ora10g/bdump/ora10g_smon_5894.trc:
ORA-00600: internal error code, arguments: [4194], [18], [7], [], [], [], [], []
出现600错误,这时候大概定位问题的出在undo上了,解决这种问题,基本上就是屏蔽坏的undo段,然后重建undo
具体操作如下:
1,找出活动的回滚段
[oracle@MACSJK-HN-207 ora10g]$ strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u
and substr(drs.segment_name,1,7) != '_SYSSMU'
^D' and substr(drs.segment_name,1,7) != ''_SYSSMU'' ' );
_SYSSMU1
_SYSSMU1
_SYSSMU10
_SYSSMU11
_SYSSMU12
_SYSSMU13
_SYSSMU14
_SYSSMU15
_SYSSMU16
_SYSSMU17
_SYSSMU18
_SYSSMU19
_SYSSMU2
_SYSSMU2
_SYSSMU20
_SYSSMU21
_SYSSMU22
_SYSSMU23
_SYSSMU24
_SYSSMU25
_SYSSMU26
_SYSSMU27
_SYSSMU28
_SYSSMU29
_SYSSMU3
_SYSSMU3
_SYSSMU4
_SYSSMU4
_SYSSMU5
_SYSSMU5
_SYSSMU6
_SYSSMU6
_SYSSMU7
_SYSSMU7
_SYSSMU8
_SYSSMU8
_SYSSMU8
_SYSSMU9
_SYSSMU9
SYSTEM _SYSSMU9
可以看到上面的显示,最大的为_SYSSMU29
2,启动数据库到mount
SQL> startup mount
ORACLE instance started.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size 2084264 bytes
Variable Size 1174405720 bytes
Database Buffers 385875968 bytes
Redo Buffers 14692352 bytes
Database mounted.
Fixed Size 2084264 bytes
Variable Size 1174405720 bytes
Database Buffers 385875968 bytes
Redo Buffers 14692352 bytes
Database mounted.
3,设置隐含参数
SQL> alter system set "_corrupted_rollback_segments"='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$','_SYSSMU11$','_SYSSMU12$','_SYSSMU13$','_SYSSMU14$','_SYSSMU15$','_SYSSMU16$','_SYSSMU17$','_SYSSMU18$','_SYSSMU20$','_SYSSMU21$','_SYSSMU22$','_SYSSMU23$','_SYSSMU24$','_SYSSMU25$','_SYSSMU26$','_SYSSMU27$','_SYSSMU28$','_SYSSMU29$' scope=spfile;
SQL> alter system set "_corrupted_rollback_segments"='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$','_SYSSMU11$','_SYSSMU12$','_SYSSMU13$','_SYSSMU14$','_SYSSMU15$','_SYSSMU16$','_SYSSMU17$','_SYSSMU18$','_SYSSMU20$','_SYSSMU21$','_SYSSMU22$','_SYSSMU23$','_SYSSMU24$','_SYSSMU25$','_SYSSMU26$','_SYSSMU27$','_SYSSMU28$','_SYSSMU29$' scope=spfile;
System altered.
SQL> alter system set "_offline_rollback_segments"='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$','_SYSSMU11$','_SYSSMU12$','_SYSSMU13$','_SYSSMU14$','_SYSSMU15$','_SYSSMU16$','_SYSSMU17$','_SYSSMU18$','_SYSSMU20$','_SYSSMU21$','_SYSSMU22$','_SYSSMU23$','_SYSSMU24$','_SYSSMU25$','_SYSSMU26$','_SYSSMU27$','_SYSSMU28$','_SYSSMU29$' scope=spfile;
System altered.
4,关闭数据库使参数生效
SQL> shutdown immediate
ORA-01109: database not open
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
5,启动数据库
SQL> startup
ORACLE instance started.
SQL> startup
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size 2084264 bytes
Variable Size 1174405720 bytes
Database Buffers 385875968 bytes
Redo Buffers 14692352 bytes
Database mounted.
Database opened.
Fixed Size 2084264 bytes
Variable Size 1174405720 bytes
Database Buffers 385875968 bytes
Redo Buffers 14692352 bytes
Database mounted.
Database opened.
6,新建一个undo表空间
SQL> CREATE UNDO TABLESPACE UNDOTBS2 datafile '/oraundo/undotbs02.dbf' size 10g;
SQL> CREATE UNDO TABLESPACE UNDOTBS2 datafile '/oraundo/undotbs02.dbf' size 10g;
Tablespace created.
7,切换undo表空间
SQL> alter system set undo_tablespace=undotbs2 scope=both;
SQL> alter system set undo_tablespace=undotbs2 scope=both;
System altered.
8,删除原来的undo表空间
SQL> alter tablespace undotbs1 offline;
SQL> alter tablespace undotbs1 offline;
Tablespace altered.
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
9,恢复隐含参数
SQL> alter system set "_corrupted_rollback_segments"='' scope=spfile;
SQL> alter system set "_corrupted_rollback_segments"='' scope=spfile;
System altered.
SQL> alter system set "_offline_rollback_segments"='' scope=spfile;
System altered.
10,重新启动数据库
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26463985/viewspace-715677/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26463985/viewspace-715677/