Dropping Redo Log Group or Member

Dropping  Redo Log  Group:

To increase or decrease the size of online redo log groups you add or drop online redo log groups. To drop a redo log you must have the alter database system privilege.Use the alter database drop logfile group you can drop the redo log group.

Alter database drop logfile group 3;

But before drop a redo log group follow the following restrictions:

• An insatnce requires at least two groups.
• You cannot be dropped active or current group means a group can drop only if it is inactive.
• Make sure a redo log group is archived.Whan an online redo log group is dropped the system files are not deleted.

 Dropping Redo Log Member:  

If you want to drop any invalid online redo log member you can drop member by using alter database drop logfile member command:

Alter Database drop logfile member'/opt/luck/redo03.log';

When a redo log member is dropped from the database, the operating system file is not deleted from disk. The control files of the database are updated to drop the member from the database structure. But brfore drop a redo log member you have to follow the following restrictions:

• If you want to drop a redo log member and that member is the last valid member of the group, you cannot drop that member.
• If the group is current, you must force a log file switch before you can drop the member.
• If the database is running in ARCHIVELOG mode and the log file group to which the member belongs is not archived, then the member cannot be dropped.
• When an online redo log member is dropped, the operating system file is not deleted.

Read More

Status Of Redo Log File

To see the status of redo log file you have the following two dynamic views:

1) V$LOG

2) V$LOGFILE

SQL>desc v$log

This command will provide you information about GROUP#, THREAD#, SEQURNE#, BYTES, MEMBERS, STATUS, FIRST_TIME. You can see the status of redo log file by using STATUS column:

SQL>select status from v$log;

 STATUS

--------------

UNUSED- Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.

CURRENT- Current redo log. This implies that the redo log is active. The redo log could be open or closed.

ACTIVE- Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.

CLEARING- Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.

CLEARING CURRENT-  Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.

INACTIVE- Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.

SQL>desc v$logfile

V$LOGFILE will provide you information about GROUP#, STATUS, TYPE, MEMBER. You can see status of redo log file by using STATUS column:

SQL> select status from v$logfile;

STATUS

--------------------

INVALID- File is inaccessible 

STALE- This redo log file member is new and has never been used.

DELETED- File is no longer used

Null- The redo log file is in use

Read More

Queue Monitor Process (QMNn)

The QMON processes are optional background processes used by Oracle Streams Advanced Queueing (AQ), Streams and a variety of other Database products which monitor and maintain all the system and user-owned AQ persistent and buffered objects. These optional processes, like the job_queue processes, do not cause the instance to fail on process failure. They provide the mechanism for message expiration, retry, and delay, maintaining queue statistics, removing processed messages from the queue table and maintaining the dequeue IOT. They also handle all the supported buffered message operations.

Related Post :-

• Background Process:- DBWn, LGWR, SMON, PMON, CKPT, ARCn, RECO

Read More

What Is Archive Process (ARCn)

The process of turning online redo log files into archived redo log files is done by archiving process (ACRn). This process is only work if the database is running in ArchiveLog mode and automatic archiving is enabled. An archived redo log file is a copy of one of the identical filled members of an online redo log group. It includes the redo entries present in the identical member  if you are multiplexing your online redo log, and if Group 1 contains member files redo_log_01_01.log and redo_log_01_02.log, then the archiver process (ARCn) will archive one of these identical members.

An Oracle instance can have up to 10 ARCn processes (ARC0 to ARC9). The LGWR process starts a new ARCn process whenever the current number of ARCn processes is insufficient to handle the workload. The alert log keeps a record of when LGWR starts a new ARCn process. you can specify multiple archiver processes with the initialization parameter LOG_ARCHIVE_MAX_PROCESSES. The ALTER SYSTEM statement can change the value of this parameter dynamically to increase or decrease the number of ARCn processes. You do not need to change this parameter from its default value of 1. LGWR automatically starts up more ARCn processes when the database workload requires more.

Related Post :- 

• Background Process : DBWn, LGWR, SMON, PMON, CKPT, QMNn, RECO

Read More

Recoverer Process (RECO)

The Distributed Transaction Recovery Process finds pending distributed transactions and resolves them. Pending distributed transactions are two-phase commit transactions involving multiple databases.The RECO process manager two-phase commits to track and resolve in-doubt transactions. The database that the  transaction started is normally the coordinator. It will send request to other  databases involved in two-phase commit if they are ready to commit. The RECO process of a node automatically connects to other databases involved in an in-doubt distributed transaction. When the RECO process reestablishes a connection between involved database servers, it automatically resolves all in-doubt transactions, removing from each database's pending transaction table any rows that correspond to the resolved in-doubt transactions.


Related Post:-

• Background Process :- DBWn, LGWR, SMON, PMON, CKPT, ARCn, QMNn

Read More

Process Monitor (PMON)

Process Monitor process recovers failed process resources. PMON monitors and restarts any failed dispatcher or server processes. In RAC, PMON’s role as service registration agent is particularly important. PMON periodically checks the status of dispatcher and server processes, and restarts any that have stopped running. PMON also registers information about the instance and dispatcher processes with the network listener. Like SMON, PMON checks regularly to see whether it is needed and can be called if another process detects the need for it.

The background process PMON cleans up after failed processes by:
• Rolling back the user’s current transaction
• Releasing all currently held table or row locks
• Freeing other resources currently reserved by the user
• Restarts dead dispatchers


Related Post:- 

• Background Process:- DBWn, LGWR, SMON, CKPT, RECO, ARCn, QMNn

Read More

System Monitor Process (SMON)

The system monitor process (SMON) performs recovery, if necessary, at instance startup. If the Oracle instance fails, any information in the SGA that has not been written to disk is lost. For example, the failure of the operating system causes an instance failure. After the loss of the instance, the background process SMON automatically performs instance recovery when the database is reopened. Instance recovery when the database is reopened. Instance recovery consists of the following steps:

• Rolling forward to recover data that has not been recorded in the data files but that has been recorded in the online redo log. This data has not been written to disk because of the loss of the SGA during instance failure. During this process, SMON reads the redo log files and applies the changes recorded in the redo log to the data blocks. Because all committed transactions have been written to the redo logs, this process completely recovers these transactions.
• Opening the database so that users can log on. Any data that is not locked by unrecovered transactions is immediately available.
• Rolling back uncommitted transactions. They are rolled back by SMON or by the individual server processes as they access locked data.

SMON also performs some space maintenance functions:

• It combines, or coalesces, adjacent areas of free space in the data files.
• It deallocates temporary segments to return them as free space in data files

Related Post :-

• Background Process:- DBWn, LGWR, PMON, ARCn, CKPT, QMNn, RECO

Read More

What is Checkpoint (CKPT)

The checkpoint process is responsible for updating file headers in the database datafiles. A checkpoint occurs when the Oracle backgroundprocess DBWn writes all the modified database buffers in the SGA  including both committed and uncommitted data to the data files. Checkpoints are implemented for the following reasons:

• Checkpoints ensure that data blocks in memory that change frequently are written to data files regularly. Because of the least recently used algorithm of DBWn, a data block that changes frequently might never qualify as the least recently used block and thus might never be written to disk if checkpoints did not occur.
• Because all database changes up to the checkpoint have been recorded in the data files, redo log entries before the checkpoint no longer need to be applied to the data files if instance recovery is required. Therefore, checkpoints are useful because they can expedite instance recovery.

At a checkpoint, the following information is written:

• Checkpoint number into the data file headers
• Checkpoint number, log sequence number, archived log names, and system change numbers into the control file.

CKPT does not write data blocks to disk or redo blocks to the online redo logs.Some checkpoints can be logged to the alert log by setting log_checkpoints_to_alert to true.Controlfile and datafile headers are updated CHECKPOINT_CHANGE#

Every 3 seconds CKPT calculates the checkpoint  target RBA based on:

The most current RBA

 log_checkpoint_timeout

 log_checkpoint_interval

 fast_start_mttr_target

 fast_start_io_target

Related Post:- 

• Background Process:- DWRn, LGWR, SMON, PMON, ARCn, RECO, QMNn

Read More

Log Writer Process (LGWR)

Oracle database keeps record of changes made to data. Every time user performs a DML, DDL or DCL operation, its redo entries are also created. These redo entries contain commands to rebuild or redo the changes. These entries are stored in Redo Log buffer. Log writer process (LGWR) writes these redo entries to redo log files. Redo log buffer works in circular fashion. It means that it overwrites old entries. Before overwriting LGWR writes  old entries in to redo log files. Log writer process (LGWR) writes redo entries after certain amount of time to ensure that free space is available for new redo entries. LGWR process writes

• When a transaction commits
• When the redo log buffer cache is one-third full
• When there is more than a megabyte of changes records in the redo log buffer cache
• Before DBWn writes modified blocks in the database buffer cache to the data files
• Every 3 seconds.

 Because the redo is needed for recovery, LGWR confirms the commit only after the redo is

written to disk. When DBWn writes modified data from Database buffer cache to disk, corresponding redo entries must also be written to disk. DBWn process checks for redo entries, it signal LGWR process if redo entries have not been written.

When a user commits a transaction, the transaction is assigned a system change number (SCN), which Oracle records along with the transaction's redo entries in the redo log. SCNs are recorded in the redo log so that recovery operations can be synchronized in Real Application Clusters and distributed databases.

 LOG_BUFFER initialization parameter is used to set the size Redo Log buffer.

Related Post :-

• Background Process :- DBWn, SMON, PMON, CKPT, RECO, ARCn,QMNn
• Redo Log Buffer

Read More

Database Writer Process (DBWn)

The server process records changes to rollback and data blocks in the buffer cache. Database Writer (DBWn) writes the dirty buffers from the database buffer cache to the data files. Although one database writer process is enough for most systems, you can configure additional processes through  from DBW1 to DBW9 and from DBWa to DBWj to improve write performance if your system modifies data heavily. When a buffer in the database buffer cache is changed, it is marked as a dirty buffer. A dirty buffer is a buffer that has not been recently used according to the least recently used . The DBWn process writes dirty buffers to disk so that user processes are able to find free buffers that can be used to write new blocks into the cache. If the number of free buffers are low in the cache and user processor are not able to find free blocks in the cache then  DBWn manages the buffer cache so that user processes can always find free buffers.

DBWn improves the performance because server process make changes only in buffer cache and DBWn manages the buffer cache to keep free buffers in the cache so that processor can find easily free buffer in cache and dirty buffer writes in to the data files. For example, blocks that are often  accessed small tables or indexes are kept in the cache so that there will be cache hit means there is no requirement to read these blocks from disk. The LRU algorithm keeps more frequently accessed blocks in the buffer cache so that when a buffer is written to disk, it is unlikely to contain data that will be useful soon.

The initialization parameter DB_WRITER_PROCESSES specifies the number of DBWn processes. The maximum number of DBWn processes is 20. The DBWn process writes dirty buffers to disk under the following conditions:

• Incremental or normal checkpoint
• The number of dirty buffers reaches a threshold value
• A process scans a specified number of blocks when scanning for free buffers and cannot fine any.
• Timeout occurs.
• A ping request in Real Application Clusters environment.
• Placing a normal or temporary tablespace offline.
• Placing a tablespace in read only mode.
• Dropping or Truncating a table.
• ALTER TABLESPACE tablespace name BEGIN BACKUP

Related Post :- 

• Background Process :-LGWR, SMON, PMON, CKPT, RECO, ARCn, QMNn
• Database Buffer Cache 

Read More

Background Processes

An Oracle instance is the combination of the background processes and memory structures. The relationship between the physical and memory structures is maintained and enforced by Oracle’s background processes.Background processes perform functions on behalf of the invoking process. They consolidate functions that would otherwise be handled by multiple Oracle programs running for each user. The background processes perform input/output (I/O) and monitor other Oracle processes to provide increased parallelism for better performance and reliability. Many other processes exist that are used by other options within Oracle, such as Advanced Queuing, Real Application Clusters, Shared Server, Advanced Replication, and so on. The background processes in an Oracle instance can include the following:

Database Writer Process (DBWn)

Log Writer Process (LGWR)

Checkpoint Process (CKPT)

System Monitor Process (SMON)

Process Monitor Process (PMON)

Recoverer Process (RECO)

Archiver Processes (ARCn)

Queue Monitor Processes (QMNn)

Read More

Server Process

A server process is a program that directly interacts with the Oracle server. Once a user has established a connection, a server process is started to handle the user processes requests. A server process can be either a dedicated server process or a shared server process. In a dedicated server environment, the server process handles the request of a single user process. Once a user process disconnects, the server process is terminated. In ashared server environment, the server process handles the request of several user processes. The server process communicates with the Oracle server using the Oracle Program Interface Server processes (or the server portion of combined user/server processes) created on behalf of each user's application can perform one or more of the following:

• Parse and run SQL statements issued through the application
• Read necessary data blocks from datafiles on disk into the shared database buffers of the SGA, if the blocks are not already present in the SGA
• Return results in such a way that the application can process the information

Related Post:- 

• User Process
• Background Process 

Read More

User Process

User Processes

When a user runs an Oracle tool Oracle creates a user process to run the user's application. A database user who needs to request information from the database must first make a connection with the Oracle server. The connection is requested using a database interface tool, such as SQL*Plus, and beginning the user process. The user process does not interact directly with the Oracle server. Rather it generates calls through the user program interface (UPI), which creates a session and starts a server process

Connections and Sessions

The terms "connection" and "session" are closely related to the term "user process", but are very different in meaning.

A connection is a communication pathway between a user process and an Oracle instance. A communication pathway is established using available interprocess communication mechanisms (on a computer that executes both the user process and Oracle) or network software (when different computers execute the database application and Oracle, and communicate via a network).

A session is a specific connection of a user to an Oracle instance via a user process. For example, when a user starts SQL*Plus, the user must provide a valid username and password and then a session is established for that user. A session lasts from the time the user connects until the time the user disconnects or exits the database application.

Related Posts:

• Server Process 
• Background Process 

Read More

Introduction to processes

All connected Oracle users must execute two modules of code to access an Oracle database instance:

• Application or Oracle tool 
• Oracle server code 

These code modules are executed by processes. A process is a "thread of control" or a mechanism in an operating system that can execute a series of steps. (Some operating systems use the terms job or task. A process normally has its own private memory area in which it runs.

The process structure varies for different Oracle configurations, depending on the operating system and the choice of Oracle options. There are some processes 

• User Process 
• Server Process
• Background Process 

Read More