OracleDG逻辑备库故障--MAXProcesses

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