ORACLE数据库db write进程及redo log不足导致minact-scn useg scan erroring out with error e12751错误
Apr222016
今天又遇到了db write进程不足，导致数据库缓慢的问题，同事在搭建这个数据库时，忘了告诉他修改db write进程的个数，测试人员在大量修改和迁入数据时，数据库突然没有响应，程序连接不上数据库。我在本地登录时，将近五分钟才登录到数据库，观察告警日志发现大量的cannot allocate new log提示，这说明redo log不足，导致日志无法切换，但最根本的原因还是db write进程不足导致的。
Thu Apr 21 17:28:41 2016 Thread 1 cannot allocate new log, sequence 132 Checkpoint not complete Current log# 5 seq# 131 mem# 0: /u01/app/oracle/oradata/ivldb/redo08.log Thu Apr 21 17:31:33 2016 Thread 1 advanced to log sequence 132 (LGWR switch) Current log# 6 seq# 132 mem# 0: /u01/app/oracle/oradata/ivldb/redo03.log Thu Apr 21 17:31:43 2016 Archived Log entry 126919 added for thread 1 sequence 131 ID 0x52f4e8d7 dest 1: Thu Apr 21 17:32:19 2016 Thread 1 cannot allocate new log, sequence 133 Checkpoint not complete Current log# 6 seq# 132 mem# 0: /u01/app/oracle/oradata/ivldb/redo03.log Thu Apr 21 17:35:15 2016 Thread 1 advanced to log sequence 133 (LGWR switch) Current log# 1 seq# 133 mem# 0: /u01/app/oracle/oradata/ivldb/redo01.log Thu Apr 21 17:35:25 2016 Archived Log entry 126920 added for thread 1 sequence 132 ID 0x52f4e8d7 dest 1: Thu Apr 21 17:36:08 2016 Thread 1 cannot allocate new log, sequence 134 Checkpoint not complete Current log# 1 seq# 133 mem# 0: /u01/app/oracle/oradata/ivldb/redo01.log Thu Apr 21 17:58:04 2016 Some DDE async actions failed or were cancelled Thu Apr 21 18:02:53 2016 Thread 1 advanced to log sequence 134 (LGWR switch) Current log# 8 seq# 134 mem# 0: /u01/app/oracle/oradata/ivldb/redo07.log Thu Apr 21 18:03:07 2016 Archived Log entry 126921 added for thread 1 sequence 133 ID 0x52f4e8d7 dest 1: Thu Apr 21 18:04:42 2016 Thread 1 cannot allocate new log, sequence 135 Checkpoint not complete Current log# 8 seq# 134 mem# 0: /u01/app/oracle/oradata/ivldb/redo07.log
在用户登录数据库时，在告警日志中会出现minact-scn: useg scan erroring out with error e:12751错误提示。
Thu Apr 21 18:10:59 2016 minact-scn: useg scan erroring out with error e:12751 Thu Apr 21 18:21:11 2016 minact-scn: useg scan erroring out with error e:12751
登录数据库，发现当前8组日志出CURRENT状态外，其他都是ACTIVE状态，ACTIVE状态的日志是不能切换为CURRENT状态的，只有等日志的状态变为INACTIVE状态才可以被切换为CURRENT状态。
sys@IVLDB> select GROUP#,SEQUENCE#,BYTES,STATUS from v$log;
GROUP# SEQUENCE# BYTES STATUS
---------- --------- ---------- ---------
1 133 524288000 ACTIVE
2 128 524288000 ACTIVE
3 130 524288000 ACTIVE
4 129 524288000 ACTIVE
5 131 524288000 ACTIVE
6 132 524288000 ACTIVE
7 135 524288000 CURRENT
8 134 524288000 ACTIVE
8 rows selected.
当前的db write进程个数为3个，不足以满足当前的大量DML操作的需求，最终的解决方案就是增加db write进程的个数，由于这是测试数据库，并发并不高，为了提高数据库的写数据能力，将db write进程设置为8个。
sys@IVLDB> show parameter db_wr NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_writer_processes integer 3 sys@IVLDB> alter system set db_writer_processes=8 scope=spfile; System altered.
这个参数是静态参数，需要重启数据库后才能生效，但是当前测试人员的程序正在运行，不能重启数据库，只好临时增加redo日志组来缓解这个问题。从上面的信息可以看到，当前数据库有8组500M的redo日志，为了缓解这个问题，我临时向数据库中加了4组5G的redo日志。
sys@IVLDB> alter database add logfile group 9 '/u01/app/oracle/oradata/ivldb/redo09.log' size 5G; Database altered. sys@IVLDB> alter database add logfile group 10 '/u01/app/oracle/oradata/ivldb/redo10.log' size 5G; Database altered. sys@IVLDB> alter database add logfile group 11 '/u01/app/oracle/oradata/ivldb/redo11.log' size 5G; Database altered. sys@IVLDB> alter database add logfile group 12 '/u01/app/oracle/oradata/ivldb/redo12.log' size 5G; Database altered.
增加后，日志切换不再阻塞。
sys@IVLDB> select GROUP#,SEQUENCE#,BYTES,STATUS from v$log;
GROUP# SEQUENCE# BYTES STATUS
---------- ---------- ---------- --------
1 133 524288000 ACTIVE
2 128 524288000 INACTIVE
3 130 524288000 ACTIVE
4 129 524288000 ACTIVE
5 131 524288000 ACTIVE
6 132 524288000 ACTIVE
7 135 524288000 ACTIVE
8 134 524288000 ACTIVE
9 136 5368709120 CURRENT
10 0 5368709120 UNUSED
11 0 5368709120 UNUSED
12 0 5368709120 UNUSED
12 rows selected.
ORACLE数据库db write进程及redo log不足导致minact-scn useg scan erroring out with error e12751错误：目前有1 条留言
发表评论
666
2016-04-22 19:08