oracledg逻辑备库故障--maxprocesses系统环境：操作系统：redhatel6oracle：oracle11gr2dataguard环境:10:29:58sys@test1selectdbid,name,database_role,protect
oracle dg 逻辑备库故障--max processes
系统环境：
操作系统：redhat el6
oracle： oracle 11gr2
dataguard 环境:
10:29:58 sys@ test1>select dbid,name,database_role,protection_mode from v$database;
dbid name database_role protection_mode
---------- --------- ---------------- --------------------
1174898526 test1 primary maximum performance
10:30:06 sys@ sh>select dbid,name,database_role,protection_mode from v$database;
dbid name database_role protection_mode
---------- --------- ---------------- --------------------
1675496824 shdb logical standby unprotected
10:28:11 sys@ sh>alter database open;
database altered.
elapsed: 00:00:08.18
启动逻辑备库的redo log 应用：
10:28:29 sys@ sh>alter database start logical standby apply immediate;
database altered.
elapsed: 00:00:00.30
10:28:40 sys@ sh>
查看告警日志：altert.log:
alter database start logical standby apply (sh)
tue apr 8 10:37:08 2014
with optional part
immediate
lsp0 started with pid=19, os id=3263
tue apr 8 10:37:08 2014
attempt to start background logical standby process
logstdby parameter: disable_apply_delay =
logstdby parameter: log_auto_delete = false
logstdby parameter: real_time =
completed: alter database start logical standby apply immediate
tue apr 8 10:37:08 2014
logstdby status: ora-16111: log mining and apply setting up
tue apr 8 10:37:08 2014
logminer: parameters summary for session# = 1
logminer: number of processes = 3, transaction chunk size = 201
logminer: memory size = 30m, checkpoint interval = 150m
logminer: session# = 1, builder process p001 started with pid=21 os id=3267
logminer: session# = 1, reader process p000 started with pid=20 os id=3265
logminer: session# = 1, preparer process p002 started with pid=22 os id=3269
logstdby analyzer process p003 started with pid=23 os id=3271
logstdby apply process p004 started with pid=24 os id=3273
logstdby apply process p006 started with pid=26 os id=3277
logstdby apply process p007 started with pid=27 os id=3279
logstdby apply process p008 started with pid=28 os id=3281
logstdby apply process p005 started with pid=25 os id=3275
tue apr 8 10:41:33 2014
redo shipping client connected as public
-- connected user is valid
rfs[1]: assigned to rfs process 3295
rfs[1]: identified database type as 'logical standby'
tue apr 8 10:41:33 2014
rfs logminer: client enabled and ready for notification
rfs[1]: archived log: '/disk1/arch_sh/arch_1_36_797856158.log'
tue apr 8 10:41:33 2014
rfs logminer: registered logfile [/disk1/arch_sh/arch_1_36_797856158.log] to logminer session id [1]
tue apr 8 10:41:33 2014
redo shipping client connected as public
-- connected user is valid
rfs[2]: assigned to rfs process 3297
rfs[2]: identified database type as 'logical standby'
tue apr 8 10:41:33 2014
rfs logminer: client enabled and ready for notification
rfs[2]: archived log: '/disk1/arch_sh/arch_1_35_797856158.log'
tue apr 8 10:41:33 2014
rfs logminer: registered logfile [/disk1/arch_sh/arch_1_35_797856158.log] to logminer session id [1]
tue apr 8 10:41:34 2014
logminer: begin mining logfile: /disk1/arch_sh/arch_1_35_797856158.log
tue apr 8 10:41:34 2014
logminer: end mining logfile: /disk1/arch_sh/arch_1_35_797856158.log
tue apr 8 10:41:34 2014
logminer: begin mining logfile: /disk1/arch_sh/arch_1_36_797856158.log
tue apr 8 10:41:34 2014
logminer: end mining logfile: /disk1/arch_sh/arch_1_36_797856158.log
tue apr 8 10:43:26 2014
rfs[2]: successfully opened standby log 4: '/u01/app/oracle/oradata/sh/std_redo04a.log'
tue apr 8 10:43:26 2014
rfs logminer: client enabled and ready for notification
tue apr 8 10:43:26 2014
logminer: begin mining logfile: /u01/app/oracle/oradata/sh/std_redo04a.log
tue apr 8 10:43:26 2014
logminer: end mining logfile: /u01/app/oracle/oradata/sh/std_redo04a.log
tue apr 8 10:43:26 2014
rfs logminer: registered logfile [/disk1/arch_sh/arch_1_37_797856158.log] to logminer session id [1]
在备库上查看redolog 的应用：
11:00:27 sys@ sh>select thread#,sequence#,applied from dba_logstdby_log;
thread# sequence# applied
---------- ---------- --------
1 31 yes
1 32 yes
1 33 yes
1 34 yes
1 35 yes
1 36 yes
1 37 yes
1 38 yes
1 39 yes
1 40 yes
10 rows selected.
在告警日志中出现以下，故障现象：
errors in file /u01/app/oracle/admin/sh/bdump/sh_m000_3326.trc:
ora-00018: maximum number of sessions exceeded
tue apr 8 10:50:29 2014
errors in file /u01/app/oracle/admin/sh/bdump/sh_m000_3332.trc:
ora-00018: maximum number of sessions exceeded
tue apr 8 10:51:29 2014
errors in file /u01/app/oracle/admin/sh/bdump/sh_m000_3335.trc:
ora-00018: maximum number of sessions exceeded
10:50:37 sys@ sh>select count(*) from v$session;
解决问题：
1、查看当前process
10:51:11 sys@ sh>show parameter process
name type value
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 0
log_archive_max_processes integer 3
processes integer 40
2、当前建立的session
10:51:11 sys@ sh>select count（*） from v$session;
count(*)
----------
29