一、Oracle服务器组成:
Oracle服务器主要是由实例、数据库、程序全局区和前台进程组成,而实例可以进一步划分为SGA和后台进程两部分。
系统全局区SGA
SGA是所有用户进程共享的一块内存区域,可以被多个用户进程共同使用.SGA主要由高速数据缓冲区、共享池、重做日志缓存区、JAVA池、大型池等内存结构组成。在实例启动时,SGA会加载到内存中,当数据库实例关闭时,SGA也就释放出来了。
1,高速数据缓冲区(database buffer cache)
高速数据缓冲区仲存放着Oracle系统最近访问过的数据块,当用户向数据库发出请求时,如果在DB buffer cache中存在请求的数据,则Oracle系统会直接从这里读取数据并返回给用户,否则就会打开数据文件读取请求的数据再返回给用户。从存取速度来看,内存的读取速度远快于物理硬盘,所以此区域可以大大降低对物理硬盘的读取频率,达到提高数据库服务性能的目的。为了便于管理SGA的内存数据,Oracle把DB buffer cache分为以下3部分:
(1)脏数据区:脏数据区中存放着已被修改过的数据,这些数据等待被写入到数据文件仲。当一条更新或者删除语句对某些数据块仲的数据修改后,那么这些数据块被标记为dirty,然后等待提交后通过后台进程DBWn将其写入到数据文件中。
(2)空闲区:空闲区中的数据块不包含任务数据,这些数据块可以被写入数据,Oracle可以从数据文件读取数据块,并将其存放到该区中。
(3)保留区:这里包含哪些正在被用户访问的数据块和明确保留以作为将来使用的数据块,这些数据块将被保留在缓冲区中。
2,重做日志缓冲区(Redo log buffer cache)
重做日志缓冲区用于存放对数据库进行修改操作室所产生的日志信息,这些日志信息在写入到重做日志文件之前,首先存放到重做日志缓冲区中,然后再检查点发生或重做日志缓冲区中的信息量达到一定峰值时,最后由日志写入进程LGWR将此缓冲区的内容写入到重做日志文件。
3,共享池(Large Pool)
共享池是SGA保留的内存区域,用于缓存SQL语句,PLsql语句、数据字典、资源锁等。共享池包含
库高速缓冲区 --- 主要包括共享SQL区和私有SQL区。这里存放最近用过的SQL语句、plsql语句的文本和执行计划。当下一次执行相同的SQL语句或plsql语句时,可以直接在这里找到之前已生成的执行计划,而不需要再次解析,这样可以提高系统执行效率。每条被缓存的sql或者plsql语句会被分为两个部分,分别存放在共享sql去和私有sql区。在共享区内存放SQL或plsql的语法分析结果和执行计划,如果以后要再次执行类似的语句,则可以利用共享sql区中一缓存的语法分析结果和执行计划。私有sql区存放sql语句中的绑定变量、环境和会话等信息,这些信息属于执行该语句用户的私有信息,其他用户无法共享这些信息。
字典高速缓冲区 --- 用于存放Oracle系统内部管理所需要的数据字典信息,例如用户名、数据对象和权限等。
4,大型池(Large Pool)
此在SGA区中不是必需的内存结构,在某些情况下,实例需要使用大型池来减轻共享池的访问压力,通常有以下:
* 当使用回复管理器进行备份和回复操作时,大型池将作为I/O缓冲区使用。
* 使用I/O Slave 仿真异步 I/O功能时,大型池将被当作I/O缓冲区使用
* 执行具有大量排序操作的SQL语句
* 当使用并行查询时,大型池作为并行查询进程被彼此交换信息的地方
如果在SGA中没有设置和大型池,则在实例需要时,Oracle会在共享池或者PGA中分配一定的缓存空间,这样的话就会影响共享池或者PGA的工作效率。
5,Java 池
用来提供内存空间给Java虚拟机使用,支持在数据库中运行Java程序包。
6,Stream Pool
此用于在数据库与数据库之间进行信息共享。
7,Fix SGA
The fixed SGA is an internal housekeeping area. For example, the fixed SGA contains:
-
General information about the state of the database and the instance, which the background processes need to access
-
Information communicated between processes, such as information about locks (see "Overview of Automatic Locks")
The size of the fixed SGA is set by Oracle Database and cannot be altered manually. The fixed SGA size can change from release to release.
程序全局区(PGA)
在PGA中,可以把代码、全局变量和数据结构都存放在其中,但是区域内的资源不能被所有用户进程共享,而是每个Oracle服务器进程都只拥有属于自己的那部分pga资源。在pga中,一个服务进程只能访问属于自己那部分pga资源区。通常PGA区是由私有SQL区和会话区组成。
私有SQL 区 --- 用于存储变量以及SQL语句运行时的内存结构信息,当每个用户连接到实例时,都会在实例中创建一个会话。这些会话可能会在SGA区中创建一个共享SQL区,但在PGA区中可能会创建多个私有SQL区,把一个私有SQL区与队友的共享SQL区合并在一起就可以获得一条SQL语句的完整缓存数据。每个会话的私有SQL区可以再分为静态区和动态区两部分。静态区的信息在会话过程中保持不变,只有当会话结束时,静态区才会被释放;二动态区的信息在整个会话过程中式不断变化的,一旦SQL语句制定完毕,即使会话还没有结束,动态区也被释放掉。
会话区 --- 用于从怒放用户的会话信息。如果数据库处于共享服务器连接模式下,则会话区将位于SGA区域,而不是PGA中。
前台进程
When a user runs an application such as a Pro*C program or SQL*Plus, the operating system creates a client process (sometimes called auser process) to run the user application. The client application has Oracle Database libraries linked into it that provide the APIs required to communicate with the database.
就是说前台进程能够实现用户通过应用程序与实例建立联系。
用户进程 --- 指那些能够产生或执行sql语句的应用程序,如sqlplus等。在用户进程中有2个概念:连接和会话。
A connection is a physical communication pathway between a client process and a database instance. A communication pathway is established using available interprocess communication mechanisms or network software. Typically, a connection occurs between a client process and a server process or dispatcher, but it can also occur between a client process and Oracle Connection Manager (CMAN).
A session is a logical entity in the database instance memory that represents the state of a current user login to a database. For example, when a user is authenticated by the database with a password, a session is established for this user. A session lasts from the time the user is authenticated by the database until the time the user disconnects or exits the database application.
服务进程 --- 用于处理会话过程中向数据库实例发出的SQL语句或sqlplus命令。分为专用服务器模式和共享服务器模式。Dedicated Server Processes
In dedicated server connections, the client connection is associated with one and only one server process (see"Dedicated Server Architecture"). On Linux, 20 client processes connected to a database instance are serviced by 20 server processes.
Each client process communicates directly with its server process. This server process is dedicated to its client process for the duration of the session. The server process stores process-specific information and theUGA in its PGA (see "PGA Usage in Dedicated and Shared Server Modes").
Shared Server Processes
In shared server connections, client applications connect over a network to a dispatcher process, not a server process (see "Shared Server Architecture"). For example, 20 client processes can connect to a single dispatcher process.
The dispatcher process receives requests from connected clients and puts them into a request queue in thelarge pool (see "Large Pool"). The first available shared server process takes the request from the queue and processes it. Afterward, the shared server place the result into the dispatcher response queue. The dispatcher process monitors this queue and transmits the result to the client.
Like a dedicated server process, a shared server process has its own PGA. However, the UGA for a session is in the SGA so that any shared server can access session data.
后台进程
后台进程是实例的重要组成部分,其中包括:
Mandatory Background Processes(必需后台进程)
SMON系统监控进程 --- 用于在数据库系统启动时执行恢复工作的强制性进程。
DBWn数据写入进程 --- 负责将内存中的脏数据块写到数据文件中,脏数据块是指高速数据缓冲区中的被修改过的数据块,这些数据块的内容与数据文件的数据块内容不一致。但是这并不是随时将所有的脏数据块都写入数据文件,只有满足一定的条件时,DBWn进程才开始成批量地将脏数据块写入数据文件,Oracle这样做的目的是为了尽量减少I/O操作,提高Oracle服务器性能。DBWN进程会将脏数据块写入数据文件通常发生在以下几种情况:
** 当用户进程执行插入或修改等操作时,需要将新数据写入高速数据缓冲区,如果在高速数据缓冲区没有足够大的数据块来存放这些新数据,这时Oracle会启动DBWn进程将脏数据块写入数据文件,以获得空闲数据块来存储这些新数据。
** 当检查点进程启动后,它会强制要求DBWn将某些脏数据块写入数据文件中。
** 当脏数据块在高速数据缓冲区中存放超过3秒钟,DBWn进程会自行启动并将某些脏数据写入到数据文件中。
LGWR日志写入进程 --- 日志写入进程用于将重做日志缓冲区中的数据写入重做日志文件。Oracle系统首先将用户所做的修改日志信息写入日志文件,然后再将修改结果写入数据文件。Oracle实例在运行中会产生大量日志信息,这些日志信息首先被记录在SGA的重做日志缓冲区中,当发生提交命令或者重做日志缓冲区的信息满1/3或者日志信息存放超过3秒钟时,LGWR进程就将日志信息从重做日志缓冲区中读取并写入日志文件组中序号较小的文件中,一个日志组写满后接着写另外一组,这就是所谓的日志切换。
CKPT检查点进程 --- 查看Oracle文档如下:
A checkpoint is a crucial mechanism in consistent database shutdowns, instance recovery, and Oracle Database operation generally. The termcheckpoint has the following related meanings:
-
A data structure that indicates the checkpoint position, which is theSCN in the redo stream where instance recovery must begin
The checkpoint position is determined by the oldest dirty buffer in the database buffer cache. The checkpoint position acts as a pointer to the redo stream and is stored in the control file and in each data file header.
-
The writing of modified database buffers in the database buffer cache to disk
Purpose of Checkpoints
Oracle Database uses checkpoints to achieve the following goals:
-
Reduce the time required for recovery in case of an instance or media failure
-
Ensure that dirty buffers in the buffer cache are written to disk regularly
-
Ensure that all committed data is written to disk during a consistent shutdown
When Oracle Database Initiates Checkpoints
The checkpoint process (CKPT) is responsible for writing checkpoints to the data file headers and control file. Checkpoints occur in a variety of situations. For example, Oracle Database uses the following types of checkpoints:
-
Thread checkpoints
The database writes to disk all buffers modified by redo in a specific thread before a certain target. The set of thread checkpoints on all instances in a database is adatabase checkpoint. Thread checkpoints occur in the following situations:
-
Consistent database shutdown
-
ALTER SYSTEM CHECKPOINT
statement -
Online redo log switch
-
ALTER DATABASE BEGIN BACKUP
statement
-
-
Tablespace and data file checkpoints
The database writes to disk all buffers modified by redo before a specific target. A tablespace checkpoint is a set of data file checkpoints, one for each data file in the tablespace. These checkpoints occur in a variety of situations, including making a tablespace read-only or taking it offline normal, shrinking a data file, or executing
ALTER TABLESPACE BEGIN BACKUP
. -
Incremental checkpoints
An incremental checkpoint is a type of thread checkpoint partly intended to avoid writing large numbers of blocks at online redo log switches. DBWn checks at least every three seconds to determine whether it has work to do. When DBWn writes dirty buffers, it advances the checkpoint position, causing CKPT to write the checkpoint position to the control file, but not to the data file headers.
Other types of checkpoints include instance and media recovery checkpoints and checkpoints when schema objects are dropped or truncated.
MMON MMNL可管理监控进程 ---- 查看Oracle文档如下:
The manageability monitor process (MMON) performs many tasks related to theAutomatic Workload Repository (AWR). For example, MMON writes when ametric violates its threshold value, taking snapshots, and capturing statistics value for recently modified SQL objects.
The manageability monitor lite process (MMNL) writes statistics from the Active Session History (ASH) buffer in the SGA to disk. MMNL writes to disk when the ASH buffer is full.
RECO恢复进程 --- 用于在分布式数据库模式下数据不一致时进行恢复工作。
Optional Background Processes (可选后台进程)
其余可参照Oracle文档。
Oracle后台进程还有很多,可以从数据字典v$dgprocess查询。
二、实例的启动与关闭警告日志记录和实例进程描述:
从警告日志记录中可以看到实例的启动与关闭过程中,后台进程的启动与关闭情况。
实例启动:
Fri Dec 26 11:39:09 2014
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile E:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEORCLDB.ORA
System parameters with non-default values:
processes = 150
nls_language = "AMERICAN"
nls_territory = "CHINA"
memory_target = 1000M
control_files = "E:\APP\ORACLE\ORADATA\ORCLDB\CONTROLFILE\CONTROL01.CTL"
control_files = "E:\APP\ORACLE\ORADATA\ORCLDB\CONTROLFILE\CONTROL02.CTL"
control_files = "E:\APP\ORACLE\ORADATA\ORCLDB\CONTROLFILE\CONTROL03.CTL"
log_file_name_convert = "primary"
log_file_name_convert = "standby"
db_block_size = 8192
compatible = "11.2.0.0.0"
log_archive_dest_1 = "LOCATION=E:\app\oracle\oradata\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary"
log_archive_dest_2 = "SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby"
log_archive_dest_state_1 = "ENABLE"
log_archive_dest_state_2 = "ENABLE"
fal_client = "primary"
fal_server = "standby"
log_archive_config = "DG_CONFIG=(primary,standby)"
log_archive_format = "%t_%s_%r.arc"
db_create_file_dest = "E:\app\oracle\oradata"
standby_file_management = "AUTO"
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=orcldbXDB)"
audit_file_dest = "E:\APP\ORACLE\ADMIN\ORCLDB\ADUMP"
audit_trail = "DB"
db_name = "orcldb"
db_unique_name = "primary"
open_cursors = 300
diagnostic_dest = "E:\APP\ORACLE"
Fri Dec 26 11:39:09 2014
PMON started with pid=2, OS id=3024
Fri Dec 26 11:39:09 2014
VKTM started with pid=3, OS id=1344 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Fri Dec 26 11:39:09 2014
GEN0 started with pid=4, OS id=3960
Fri Dec 26 11:39:09 2014
DIAG started with pid=5, OS id=1584
Fri Dec 26 11:39:09 2014
DBRM started with pid=6, OS id=4116
Fri Dec 26 11:39:09 2014
PSP0 started with pid=7, OS id=3472
Fri Dec 26 11:39:09 2014
DIA0 started with pid=8, OS id=4668
Fri Dec 26 11:39:09 2014
MMAN started with pid=9, OS id=4752
Fri Dec 26 11:39:09 2014
DBW0 started with pid=10, OS id=4468
Fri Dec 26 11:39:09 2014
LGWR started with pid=11, OS id=5008
Fri Dec 26 11:39:09 2014
CKPT started with pid=12, OS id=5080
Fri Dec 26 11:39:09 2014
SMON started with pid=13, OS id=3928
Fri Dec 26 11:39:09 2014
RECO started with pid=14, OS id=3916
Fri Dec 26 11:39:09 2014
MMON started with pid=15, OS id=1896
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Fri Dec 26 11:39:09 2014
MMNL started with pid=16, OS id=4408
starting up 1 shared server(s) ...
ORACLE_BASE from environment = E:\app\oracle
Fri Dec 26 11:39:09 2014
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 2303303773
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Fri Dec 26 11:39:14 2014
ALTER DATABASE OPEN
LGWR: STARTING ARCH PROCESSES
Fri Dec 26 11:39:14 2014
ARC0 started with pid=19, OS id=1728
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Fri Dec 26 11:39:15 2014
ARC1 started with pid=21, OS id=1576
Fri Dec 26 11:39:15 2014
ARC2 started with pid=22, OS id=4256
ARC1: Archival started
Fri Dec 26 11:39:15 2014
ARC3 started with pid=23, OS id=2776
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thread 1 advanced to log sequence 10 (thread open)
Thread 1 opened at log sequence 10
Current log# 1 seq# 10 mem# 0: E:\APP\ORACLE\ORADATA\PRIMARY\ONLINELOG\O1_MF_1_B9QCF3FL_.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Dec 26 11:39:16 2014
NSA2 started with pid=24, OS id=3088
SMON: enabling cache recovery
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Archived Log entry 151 added for thread 1 sequence 9 ID 0x89489e41 dest 1:
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
No Resource Manager plan active
Thread 1 cannot allocate new log, sequence 11
Private strand flush not complete
Current log# 1 seq# 10 mem# 0: E:\APP\ORACLE\ORADATA\PRIMARY\ONLINELOG\O1_MF_1_B9QCF3FL_.LOG
Starting background process QMNC
Fri Dec 26 11:39:20 2014
QMNC started with pid=25, OS id=4124
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Fri Dec 26 11:39:22 2014
Thread 1 advanced to log sequence 11 (LGWR switch)
Current log# 2 seq# 11 mem# 0: E:\APP\ORACLE\ORADATA\PRIMARY\ONLINELOG\O1_MF_2_B9QCF3Z2_.LOG
ARC0: STARTING ARCH PROCESSES
Fri Dec 26 11:39:22 2014
ARC4 started with pid=26, OS id=3276
Completed: ALTER DATABASE OPEN
ARC4: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Archived Log entry 152 added for thread 1 sequence 10 ID 0x89489e41 dest 1:
Fri Dec 26 11:39:25 2014
Starting background process CJQ0
Fri Dec 26 11:39:25 2014
CJQ0 started with pid=27, OS id=4672
实例关闭:
Fri Dec 26 11:38:28 2014
Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
Stopping background process QMNC
Fri Dec 26 11:38:29 2014
Stopping background process CJQ0
Stopping background process MMNL
Stopping background process MMON
License high water mark = 5
All dispatchers and shared servers shutdown
ALTER DATABASE CLOSE NORMAL
Fri Dec 26 11:38:34 2014
SMON: disabling tx recovery
SMON: disabling cache recovery
Fri Dec 26 11:38:35 2014
Shutting down archive processes
Archiving is disabled
Fri Dec 26 11:38:35 2014
ARCH shutting down
Fri Dec 26 11:38:35 2014
ARCH shutting down
ARC0: Archival stopped
ARC2: Archival stopped
Fri Dec 26 11:38:35 2014
ARCH shutting down
Fri Dec 26 11:38:35 2014
ARCH shutting down
ARC1: Archival stopped
ARC3: Archival stopped
Thread 1 closed at log sequence 9
Successful close of redo thread 1
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Fri Dec 26 11:38:38 2014
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Fri Dec 26 11:38:38 2014
Stopping background process VKTM:
Fri Dec 26 11:38:40 2014
Instance shutdown complete
实例进程描述
具体请参考:
http://blog.csdn.net/seulk_q/article/details/42834549