Oracle 10g Background processes

There are a large number of new background processes in Oracle 10g, however there are just 5 that are vital for the Database to work.

5 Old and Vital background processes

- SMON (System Monitor): Performs instance recovery, cleans up temporary segments
no longer in use and recovers dead transactions, coalesce free extents in the database.

- DBWR (Database Writer): This process in on charge of writing to the DB files.

- CKPT (Checkpoint): This process is on charge of executing checkpoint

- PMON (Process Monitor): Recovers when a user process fails, clean up cache and free
resources, it also monitors dispatchers and server processes.

- LGWR (Log Writer): This process is on charge of write to the redo log files

New Background processes

ASM:

- ARBn : (ASM) Rebalance working process , it rebalances extents.

- ORBn: Rebalance ASM data extent movements. There can be many of this at the same time.

- RBAL : This is the ASM Rebalance Master disk manager, open all disks under each disk group and make them
available to the various clients. It also creates the rebalance plan to move extents.

- OSMB: (ASM) Helps to manage the drive storage.

- ASMB: (ASM) Responsible to communicate the database Instance to the ASM Instance, provides the heartbeat to the ASM instance.

Server Side Optional

- RECO: Helps to Recover distributed Transactions.

- ORA_CJQ0_: Job queue coordinator , checks the log and spawn J### processed

- QMNC: AQ Coordinator, used to manage Oracle Streams Advance Queuing.

- ARCH, ARCn: Copies redo logs to archive logs destination.

- CTWR: Block Change Tracking Writer.

- ORA_D###_: Oracle Dispatchers processed

- ORA_J###_: Job queue process

- ORA_MMAN_: Memory manager, used for the manage of the automatic shared memory.

- ORA_MMNL_: Helps collecting metrics and information about sessions.

- ORA_MMON_: On charge of collecting stats.

- ORA###_: Dedicated server (user process).

- ORA_RVWR_: Recovery Writer (Flashback Database).

- ORA_S###_: Oracle Shared Server Process (Multi Threaded Server)

- ORA_P###_: Parallel Query Slaves Process

- ORA_LNS#_: Network Server.

Other Processes

- ORA_AQ_: Sends notifications when messages arrive into the queues (Advance Queue).

- ORA_Q###_: Queue Process.

- ORA_DMON_: Data Guard Broker Process.

- ORA_SNP###_: Snapshot process (Data Guard).

- ORA_TRWR_: Advance Queuing Time Manager.

- ORA_WMON_: Wakeup monitor process.

Undocumented Server Side Extra

- ORA_FMON_: Manage mapping information when using FILE_MAPPING

Standby Mode

- ORA_LSP0_: Logical Standby

- ORA_LSP1_: Dictionary build process for Logical Standby

- ORA_LSP2_: Set Guard Standby information for logical Standby.

- ORA_DMON_: Data Guard Broker Monitor Process.

- ORA_RSM#_: Data Guard Broker Resource Guard process.

- ORA_INSV_: Data Guard Broker Instance slave process

- ORA_NSV#_: Data Guard Broker NetSlave Process.

- ORA_MRP0_: Managed Recovery process for Data Guard.

- ORA_RFS_: Remote File Server process (Data Guard) receives archived redo from primary.

Real Application Cluster

- The following processes are unique to a RAC environment. You will not see them otherwise.

* Lock monitor (LMON) process: The LMON process monitors all instances in a cluster to detect the failure of an instance. It then facilitates the recovery of the global locks held by the failed instance. It is also responsible for reconfiguring locks and other resources when instances leave or are added to the cluster (as they fail and come back online, or as new instances are added to the cluster in real time).

* Lock manager daemon (LMD) process: The LMD process handles lock manager service requests for the global cache service (keeping the block buffers consistent between instances). It works primarily as a broker sending requests for resources to a queue that is handled by the LMSn processes. The LMD handles global deadlock detection/resolution and monitors for lock timeouts in the global environment.

* Lock manager server (LMSn) process: As noted earlier, in a RAC environment, each instance of Oracle is running on a different machine in a cluster, and they all access, in a read-write fashion, the same exact set of database files. To achieve this, the SGA block buffer caches must be kept consistent with respect to each other. This is one of the main goals of the LMSn process In earlier releases of Oracle Parallel Server (OPS) this was accomplished via a ping. That is, if a node in the cluster needed a read-consistent view of a block that was locked in exclusive mode by another node, the exchange of data was done via a disk flush (the block was pinged). This was a very expensive operation just to read data. Now, with the LMSn, this exchange is done via very fast cache-to-cache exchange over the clusters¿ high-speed connection. You may have up to ten LMSn processes per instance.

* Lock (LCK0) process: This process is very similar in functionality to the LMD process described earlier, but it handles requests for all global resources other than database block buffers.

- CM: Cluster Manager , it maintains the status of the nodes and instances across the cluster.

- GCS: Global Cache Service

- GES: Global Enqueue Service daemon

- GMS: Group Membership Service

- NM: Node Monitor

-NPIC: Network Inter-process communication

- ORA_PSP0_: Process spawner, allows Oracle PL/SQL embedded in HTML (PLUG IN)

- CSS (Cluster Synchronization Services): Cluster management and node monitoring, monitors
ASM

- CRS (Cluster Register Service):

- ORA_EMNO_: Event Monitor Process

- ORA_DIAG_: Diagnosability daemon (DIAG) process: The DIAG process is used exclusively in a RAC environment. It is responsible for monitoring the overall ¿health¿ of the instance, and it captures information needed in the processing of instance failures.

SQL Server useful hints for Oracle and Sybase DBAs

1. Some System Catalog tables to get familiar with:

Sysdatabases - Contains one row for each database on Microsoft® SQL Server™. When SQL Server is initially installed, sysdatabases contains entries for the master, model, msdb, mssqlweb, and tempdb databases. This table is stored only in the master database.

Sysdevices - Contains one row for each disk backup file, tape backup file, and database file. This table is stored only in the master database.

Sysfiles - Contains one row for each file in a database. This system table is a virtual table; it cannot b updated or modified directly.

Sysindexes - Contains one row for each index and table in the database. This table is stored in each database.

Syslogins - Contains one row for each login account.

Sysobjects - Contains one row for each object (constraint, default, log, rule, stored procedure, and so on) created within a database. In tempdb only, this table includes a row for each temporary object.

Sysprocesses - The sysprocesses table holds information about processes running on Microsoft® SQL Server™. These processes can be client processes or system processes. sysprocesses is stored only in the master database.

2. Filegroups:

Database files can be grouped together in filegroups for allocation and administration purposes. Some systems can improve their performance by controlling the placement of data and indexes onto specific disk drives. Filegroups can aid this process. The system administrator can create filegroups for each disk drive, then assign specific tables, indexes, or the text, ntext, or image data from a table, to specific filegroups.

These are what Sybase calls database segments. In MS SQL Server you can backup, restore and run DBCC’s on individual filegroups of a database.

3. Indexes

Clustered indexes have one row in sysindexes with indid = 1. The pages in the data
chain and the rows in them are ordered on the value of the clustered index key.
No more than one clustered index per table.

When the clustered index is rebuild, all non clustered index get automatically rebuild.

4. xp_cmdshell command:

Xp_cmdshell - Executes a given command string as an operating-system command i.e. 1>xp_cmdshell dir

*** This is considered a security risk. When allowed. ***

5. Statistics

statistics - Statistical information can be used by the query processor to determine the optimal strategy for evaluating a query. When you create an index, SQL Server automatically stores statistical information regarding the distribution of values in the indexed column(s). The query optimizer in SQL Server uses these statistics to estimate the cost of using the index for a query. Additionally, when the AUTO_CREATE_STATISTICS database option is set to ON (default), SQL Server automatically creates statistics for columns without indexes that are used in a predicate.

sp_autostats - Displays or changes the automatic UPDATE STATISTICS setting for a specific index and statistics, or for all indexes and statistics for a given table or indexed view in the current database.

The following commands can help you determine if a index needs to be rebuild.

DBCC show statistics - Displays the current distribution statistics for the specified target on the specified table. The results returned indicate the selectivity of an index (the lower the density returned, the higher the selectivity) and provide the basis for determining whether or not an index is useful to the query optimizer. The results returned are based on distribution steps of the index.

sp_spaceused – If the unused column goes negitave, it is a candidate to check the index.

** When you do a sp_helpindex and see an indexed name _WA_sys_xxx, where xxx is a column name, These are statistics that are generated by SQL server. You can look at these as possible candidates to put and index on.

6. Terms

MSDB database - This is essentially where SQL server stores backup and restore
history. Must have it, one of the four necessary DB’s along with model, tempdb
and master.

SQL Server Agent – Essentially is the process that allows you to schedule periodic activities and notifications on the SQL Servers jobs scheduler.

1433 – MS SQL Server default listening port. On Sybase it was 2025. We usually change this to another port for security purposes.

Tran log - Unlike Sybase, on MS SQL Server 2000 you must have a separate file for the transaction log. It must be separate from the data files

Detach and attach a database- In SQL Server 2000, the data and transaction log files of a database can be detached and then reattached to another server, or even to the same server. Detaching a database removes the database from SQL Server but leaves the database intact within the data and transaction log files that compose the database. These data and transaction log files can then be used to attach the database to any instance of SQL Server, including the server from which the database was detached. This makes the database available in exactly the same state it was in when it was detached.

Log Shipping - In SQL Serve 2000 Enterprise Edition, you can use log shipping to feed transaction logs from one database to another on a constant basis. Continually backing up the transaction logs from a source database and then copying and restoring the logs to a destination database keeps the destination database synchronized with the source database. You must have Enterprise Edition, otherwise, you can do this yourself manually or with scheduled jobs.

Microsoft Clustering - A cluster is a group of independent computers that work together to run a common set of applications and provide the image of a single system to the client and application. The computers are physically connected by cables and programmatically connected by cluster software. These connections allow computers to use failover and load balancing, which is not possible with a stand-alone computer.

7. Database Placement

A general rule of thumb is to place the data, logs and backups on separate devices or disk drives. In case a drive should go bad.

Oracle Hints:

1. Analyze command. Analyze command serves several purposes in Oracle.

The two features that are most important to remember are:

A. Analyze allows you to update statistics on tables. (i.e. like update statistics

B. Analyze allows you to validate objects. (i.e. like dbcc checktable)
2. If you in log mode, Oracle writes the archive logs to disk when they become full.
If the disk where the archive logs are written is full, Oracle will stop working until
Space is made to right the archive logs.