南山草舍

0%

数据库hang住，应用连接缓慢甚至无法连接

发表于 分类于

问题现象

数据库抗住，开发人员反映应用断断续续无法连接，远程telnet数据库端口不通

登录数据库，查看日志位置

1
2
3
4
5
SQL> select value from v$diag_info where name ='Diag Trace';

VALUE
--------------------------------------------------------------------------------
e:\app\administrator\diag\rdbms\sx21\sx21\trace

报错1：redo日志无法切换

查看alert_sx21.log日志，发现大量redo日志报错

1
2
3
4
Checkpoint not complete
Current log# 2 seq# 76268 mem# 0: E:\APP\ADMINISTRATOR\ORADATA\SX21\REDO02.LOG
Thread 1 advanced to log sequence 76269 (LGWR switch)
Current log# 3 seq# 76269 mem# 0: E:\APP\ADMINISTRATOR\ORADATA\SX21\REDO03.LOG

增加redo日志

1
2
alter database add logfile group 4 ('E:\APP\ADMINISTRATOR\ORADATA\SX21\REDO04.LOG') size 50m;
alter database add logfile group 5 ('E:\APP\ADMINISTRATOR\ORADATA\SX21\REDO05.LOG') size 50m;

查看redo日志

1
select group#,sequence#,bytes,members,status from v$log;

修改 fast_start_mttr_target参数

1
alter system set fast_start_mttr_target = 30 ;

报错2：

1
2
3
4
5
6
7
8
9
10
11
Sat Sep 01 08:37:01 2018
Errors in file e:\app\administrator\diag\rdbms\sx21\sx21\trace\sx21_mmon_6264.trc  (incident=144681):
ORA-00445: 后台进程 "m000" 在 120 秒之后仍没有启动
Sat Sep 01 08:39:06 2018
Errors in file e:\app\administrator\diag\rdbms\sx21\sx21\trace\sx21_smco_13976.trc  (incident=144698):
ORA-00445: 后台进程 "W000" 在 120 秒之后仍没有启动
Sat Sep 01 08:41:12 2018
Errors in file e:\app\administrator\diag\rdbms\sx21\sx21\trace\sx21_cjq0_21740.trc  (incident=144688):
ORA-00445: 后台进程 "J000" 在 120 秒之后仍没有启动
kkjcre1p: unable to spawn jobq slave process 
Errors in file e:\app\administrator\diag\rdbms\sx21\sx21\trace\sx21_cjq0_21740.trc:

此问题现象为系统内存不足，建议增加内存，或增加sga，由于暂时无法增加系统内存，我先调整了sga

修改sga

1
2
3
4
5
6
7
8
9
10
11
SQL> show parameter sga;

NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 10G
sga_target big integer 0
SQL> alter system set sga_max_size=20480M scope=spfile;    #一定要保证SGA_MAX_SIZE小于MEMORY_TARGET，否则启动会报错
SQL> shutdown immediate
SQL> startup

一定要保证SGA_MAX_SIZE小于MEMORY_TARGET，否则启动会报以下错误

ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 21474836480 cannot be set to more than MEMORY_TARGET 10737418240.

解决方法如下，创建一个pfile文件，然后修改内存参数，然后在创建一格spfile，启动数据库

1
SQL> create pfile from spfile;

修改该文件以下参数E:\app\Administrator\product\11.2.0\dbhome_4\database\INITsx21.ORA
*.memory_target=27487790694
*.sga_max_size=21990232555
*.sga_target=21990232555

1
SQL> create spfile from pfile;

参考文档：

http://blog.51cto.com/mervin/490322
http://blog.51cto.com/sysadmin/203502
http://blog.51cto.com/leoguan/584494
https://blog.csdn.net/ggwxk1990/article/details/78712204
https://blog.csdn.net/leshami/article/details/5782516