OracleDG logical standby database fault -- MAXProcesses

OracleDG logical standby database fault -- MAXProcesses system environment: Operating System: RedHatEL6Oracle: Oracle11gR2DataGuard environment: 10: 29: 58SYS @ test1selectdbid, name, database_role, protect

OracleDG logical standby database fault -- MAXProcesses system environment: Operating System: RedHatEL6Oracle: Oracle11gR2DataGuard environment: 10: 29: 58SYS @ test1selectdbid, name, database_role, protect

Oracle DG logical standby database fault -- MAX Processes

System Environment:

Operating System: RedHat EL6

Oracle: Oracle 11gR2

DataGuard environment:

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, and 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

Start the redo log application of the logical standby database:

At 10:28:29 SYS @ sh> alter database start logical standby apply immediate;

Database altered.

Elapsed: 00:00:00. 30

10:28:40 SYS @ sh>

View alarm log: 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 = 30 M, Checkpoint interval = 150 M

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_000036_797856158.log'

Tue Apr 8 10:41:33 2014

RFS LogMiner: Registered logfile [/disk1/arch_sh/arch_000036_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]

View redolog applications on the slave database:

At 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.

The following error occurs in the alarm log:

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

At 10:50:37 SYS @ sh> select count (*) from v $ session;

Solution:

1. view the current 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. Current session

At 10:51:11 SYS @ sh> select count (*) from v $ session;

COUNT (*)

----------

29