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