Oracle DG 逻辑备库故障--MAX Processes-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

Oracle DG 逻辑备库故障--MAX Processes

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 05:26 PM

oracleFaultlogic

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(*)

----------

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles