oracle 逻辑备库跳过,Oracle DG 逻辑备库故障--MAX Processes

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

当前Instance 的server process 采用dedicated 模式，当建立一个session时，需分配一个server process ；当前分配的server process 加上后台进程已经超过了max processes(40)，所以需要增加max processes。

3、调整max processes 参数

10:53:19 SYS@ sh>alter system set processes=150 scope=spfile;

System altered.

重启Instance 后，参数生效.

10:55:40 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     150

10:57:01 SYS@ sh>

@至此，问题解决

©著作权归作者所有：来自51CTO博客作者客居天涯的原创作品，如需转载，请注明出处，否则将追究法律责任

Oracle数据库技术

0