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