Subterfuge

05-Managing the Oracle Instance Memory

2011-12-26T10:27:00.001+05:00

5.0 Memory Management

Memory management involves maintaining optimal sizes for the Oracle instance memory structures as demands on the database change. These structures are the system global area (SGA) and the instance program global area (collection of all individual PGAs).

There are two different memory management methods for the SGA, and two for the instance PGA.

Automatic SGA Memory Management
Manual SGA Memory Management
Automatic PGA Memory Management
Manual PGA Memory Management

5.0.1 Automatic SGA Memory Management

To allow ASMM (Automatic Shared Memory Management) you specify the total amount of SGA memory available to an instance using the SGA_TARGET initialization parameter and Oracle instance automatically distributes this memory among the various SGA components to ensure the most effective memory utilization.

The Oracle instance automatically distributes the available memory among the various components as required, allowing the system to maximize the use of all available SGA memory. If you are using a server parameter file (SPFILE), the instance remembers the sizes of the automatically tuned SGA components across instance shutdowns. The instance can begin with information from the previous instance and continue evaluating workload where it left off at the last shutdown.

All SGA components allocate and deallocate space in units of granules. Granule size is determined by total SGA size. On most platforms, if the total SGA size is equal to or less than 1 GB, then granule size is 4 MB. For SGAs larger than 1 GB, granule size is 16 MB. If you specify a size for a component that is not a multiple of granule size, Oracle instance rounds the specified size up to the nearest multiple. You can query the V$SGAINFO view to see the granule size that is being used by an instance.

Table below lists the SGA components that are automatically sized when SGA_TARGET is set.

The V$SGA_TARGET_ADVICE view provides information that helps you decide on a value for SGA_TARGET.

You can change to ASMM from manual shared memory management as:

Obtain a value for SGA_TARGET.

Set the value of SGA_TARGET, either by editing the text initialization parameter file and restarting the database, or by issuing the following statement:

Set the values of the automatically sized SGA components listed in table given above to zero. Do this by editing the text initialization parameter file or by issuing ALTER SYSTEM statements.

5.0.2 Manual SGA Memory Management

To use MSMM (Manual Shared Memory Management) you can configure several SGA component sizes manually, and then monitor and tune these sizes on an ongoing basis as the database workload changes. If you create your database with DBCA and choose manual shared memory management, DBCA provides fields where you must enter sizes for the buffer cache, shared pool, large pool, and Java pool. It then sets the corresponding initialization parameters in the server parameter file (SPFILE) that it creates. If you instead create the database with the CREATE DATABASE statement and a text initialization parameter file, provide values for the initialization parameters that set SGA component sizes.

To enable manual shared memory management, set the MEMORY_TARGET and SGA_TARGET initialization parameter to 0. You must then set values for the various SGA components.

5.0.3 Automatic PGA Memory Management

By default, Oracle instance automatically manages the total amount of memory dedicated to the instance PGA. You can control this amount by setting the initialization parameter PGA_AGGREGATE_TARGET. Oracle instance then tries to ensure that the total amount of PGA memory allocated across all database server processes and background processes never exceeds this target.

If you create your database with DBCA, you can specify a value for the total instance PGA. DBCA then sets the PGA_AGGREGATE_TARGET initialization parameters in the server parameter file (SPFILE) that it creates. If you create the database with the CREATE DATABASE statement and a text initialization parameter file, you can provide a value for PGA_AGGREGATE_TARGET.

Following dynamic performance views provide PGA memory use statistics.

V$PGA_TARGET_ADVICE
V$PGASTAT
V$SQL_WORKAREA

5.0.4 Manual PGA Memory Management

In manual PGA memory management, you manually tune SQL work areas. The database administrator controlled the maximum size of SQL work areas by setting the following parameters:

SORT_AREA_SIZE
HASH_AREA_SIZE
BITMAP_MERGE_AREA_SIZE
CREATE_BITMAP_AREA_SIZE

Oracle strongly recommends that you leave automatic PGA memory management enabled. If you decide to tune SQL work areas manually, you must set the WORKAREA_SIZE_POLICY initialization parameter to MANUAL.

5.0.5 Database Smart Flash Cache

If your database is running on Solaris or Oracle Linux, you can optionally add another memory component: Database Smart Flash Cache (the flash cache). The flash cache is an extension of the SGA-resident buffer cache, providing a level 2 cache for database blocks. It can improve response time and overall throughput, especially for read-intensive online transaction processing (OLTP) workloads. The flash cache resides on one or more flash disk devices, which are solid state storage devices that use flash memory.

You can add flash cache when all of the following are true:

The Buffer Pool Advisory section of your Automatic Workload Repository (AWR) report or STATSPACK report indicates that doubling the size of the buffer cache would be beneficial.
db file sequential read is a top wait event.
You have spare CPU.

As a general rule, size the flash cache to be between 2 times and 10 times the size of the buffer cache. Any multiplier less than two would not provide any benefit. If you are using automatic shared memory management, make the flash cache between 2 times and 10 times the size of SGA_TARGET. Using 80% of the size of SGA_TARGET instead of the full size would also suffice for this calculation.

For each database block moved from the buffer cache to the flash cache, a small amount of metadata about the block is kept in the buffer cache. For a single instance database, the metadata consumes approximately 100 bytes. For an Oracle Real Application Clusters (Oracle RAC) database, it is closer to 200 bytes. You must therefore take this extra memory requirement into account when adding the flash cache.

If you are managing memory manually, increase the size of the buffer cache by an amount approximately equal to the number of database blocks that fit into the flash cache multiplied by 100 (or 200 for Oracle RAC).
If you are using automatic shared memory management, increase the size of SGA_TARGET.

Following are the initialization parameters that you use to configure the flash cache.

You can use ALTER SYSTEM to set db_flash_cache_size to zero to disable the flash cache. Dynamically changing the size of the flash cache is not supported.

02 - RMAN Architecture

2011-11-25T10:35:00.000+05:00

Recovery Manager is a server-managed recovery (SMR). SMR refers to the ability of the database server to perform the backup operations. RMAN is integrated into the RDBMS, it has access to your data at the same level that the database itself uses: the data block. Block-level access is what distinguishes RMAN from any other backup utility

The RMAN architecture has the following components:

RMAN Utility
Database Control File
RMAN Server Processes
Recovery Catalog
Auxiliary Database
Flash Recovery Area

2.0 RMAN Utility

RMAN is a standalone executable that makes a connection to the Oracle database to access internal backup and recovery packages (SYS schema). RMAN is not more than a command interpreter that takes commands you type and turns those commands into remote procedure calls (RPCs) that are executed at the database. The command language is unique to RMAN, and using it takes a little practice.

The RMAN utility is composed of two components: the executable file and the recover.bsq file. The recover.bsq file is the library file, from which the executable file extracts code for creating PL/SQL calls to the target database. The target database refers to the database that is being backed up. It is must be noted that the recover.bsq file and the executable file must be of the same version or nothing will work.

RMAN always connects to the target database as a sysdba user. When you connect to the target, RMAN automatically supplies the “as sysdba” to the connection:

For remote connection to the target database, you need to create a tnsnames.ora entry that can connect you to the target database with a dedicated server process. RMAN cannot use Shared Servers to make a database connection. The difference between the two can be seen in the following sample tsnames.ora file.

PROD_RMAN =
           (DESCRIPTION =
                  (ADDRESS_LIST =
                     (ADDRESS = (PROTOCOL = TCP)
                     (HOST = pc1)
                     (PORT = 1521))
                   )
                   (CONNECT_DATA =(SERVER = DEDICATED)
                   (SERVICE_NAME = prod))
          )
PROD =
           (DESCRIPTION =
                  (ADDRESS_LIST =
                      (ADDRESS = (PROTOCOL = TCP)
                      (HOST = pc1)
                      (PORT = 1521))
                  )
            (CONNECT_DATA =(SERVER = SHARED)
            (SERVICE_NAME = prod))
       )

Running RMAN locally always make a bequeath connection to the database, requiring no password file setup and no tnsnames.ora configuration.

2.1 Database Control File

The database control file acts as a repository of database file information. RMAN uses the control file to compile file lists, obtain checkpoint information, and determine recoverability. The control file also acts as an RMAN data repository. After RMAN completes a backup of any portion of the database, it writes a record of that backup to the control file, along with checkpoint information about when the backup was started and completed.

The control file has two types of records: circular reuse records and noncircular reuse records. Circular reuse records are records that include information that can be aged out of the control file, for instance, archive log history information. Noncircular reuse records are those records that cannot be deleted from the control file. If the control file runs out of space for these records, the file expands to make more room. These records include datafile and log file lists. The record of RMAN backups in the control file falls into the category of circular reuse records, meaning that the records will get aged out if the control file section that contains them becomes full. It must be noted that if the control file does not have a record of your RMAN backup, the backup cannot easily be used by RMAN for recovery. This makes the control file a critical component of the RMAN architecture. You can set a larger timeframe for when the control file will age out records. This is controlled by the init.ora parameter CONTROL FILE_RECORD_KEEP_TIME. By default, this parameter is set to 7 (in days).

RMAN needs a read consistent view of the control file. With the constant updates from the database, this is nearly impossible. To get around this, RMAN uses the snapshot control file, an exact copy of your control file that is only used by RMAN during backup and resync operations. RMAN refreshes the snapshot control file from the actual control file, thus putting a momentary lock on the control file. Then, RMAN switches to the snapshot and uses it for the duration of the backup; in this way, it has read consistency without holding up database activity. By default, the snapshot control file exists in the ORACLE_HOME/dbs directory on Unix platforms and in the ORACLE_HOME/database directory on Windows. It has a default name of SNCF<ORACLE_SID>.ORA.

2.2 RMAN Server Processes

When RMAN makes a connection to the target database, two server processes are created at the target database. The primary process is used to make calls to packages in the SYS schema in order to perform the backup or recovery operations. The secondary, or shadow, process polls any long-running transactions in RMAN and then logs the information internally. You can view the results of this polling in the view V$SESSION_LONGOPS:

You can also view these processes in the V$SESSION view.

In addition to the two default processes, an individual process is created for every channel that you allocate during a backup or restore operation. Channel is the server process at the target database that coordinates the reads from the datafiles and the writes to the specified location during backup. During a restore, the channel coordinates reads from the backup location and the writing of data blocks to the datafile locations. There are only two kinds of channels: disk channels and tape channels. You cannot allocate both kinds of channels for a single backup operation—you are writing the backup either to disk or to tape.

The RMAN server process that coordinates the work of the channels has access to two packages in the SYS schema: DBMS_RCVMAN and DBMS_BACKUP_RESTORE. Both of these RMAN packages are installed by default by running the catproc.sql script when the database is created. These packages have another important trait: they are hard-coded into the Oracle software library files, so they can be called even when the database is not open.

2.3 Recovery Catalog

The recovery catalog is a repository for metadata about RMAN backups. In a sense, you can think of the recovery catalog as merely a copy of the pertinent information out of the control file that RMAN requires for backup and recovery purposes. To use a recovery catalog, you first connect from RMAN to the target database. Then, you make a second Net connection to the recovery catalog from within RMAN, like this:

In the connect string to the catalog, you pass the username and password for the user that owns the RMAN catalog. Unlike with the target, the connection to the catalog is not a sysdba connection and does not need this privilege granted to it.

A recovery catalog can serve as a repository for more than one target database, and as such can help centralize the administration of backups of many different databases. Inside of the recovery catalog are two packages: DBMS_RCVMAN and DBMS_RCVCAT. Oracle created a series of views, all prefixed with RC_, that can be used to extract information from the catalog.

2.4 Auxiliary Database

The auxiliary database refers to the instance that will become host to restored files from the target database in the event of a tablespace point-in-time recovery (TSPITR), a duplication operation (cloning the database), or the creation of a standby database using RMAN backups. When you perform any of these tasks, you will be connecting to the target database and the auxiliary database at the same time from within RMAN. In this way, you can utilize the information about the backups in the target database control file to coordinate the restore of those backups to the auxiliary database location. The following shows the connection to both the target database (locally) and the auxiliary database (using an Oracle Net connection):

2.5 Flash Recovery Area

The FRA is a specific location on disk that you set up to house all the Oracle recovery files. Recovery files refers to all files that might be required for a media recovery operation: full datafile backups, incremental backups, datafile copies, backup control files, and archive logs. The FRA also functions as a repository for mirrored copies of online redo log files, the block-change tracking file, and for a current control file. The FRA manages recovery files internally, first based on database name, then on types of files, and then by the dates the files are generated. The files themselves are named according to the Oracle Managed Files (OMF) format.

2.6 RMAN Memory Structure

RMAN utilize memory from PGA and sometime from SGA. There are two kinds of memory buffers. Input buffers are the buffers that are filled with data blocks read from files that are being backed up. Output buffers are the buffers that are filled when the memory-to-memory write occurs to determine whether a particular block needs to be backed up. The memory buffers differ depending on whether you are backing up to or restoring from disk or tape.

You can use the following query to monitor the size of buffers on a per-file basis while the backup is running:

SELECT set_count, device_type, type, filename, buffer_size,  

       buffer_count, open_time, close_time 
FROM v$backup_async_io 
ORDER BY set_count,type, open_time, close_time;

01- Backup and Recovery Overview

2011-11-24T11:56:00.001+05:00

Backup and recovery refers to the various strategies and procedures involved in protecting your database against data loss and reconstructing the database after any kind of data loss.

1.0 Types of Backups

Backup is a copy of whole or part of database that can be used to reconstruct that database. Backups can be broadly divided into physical backups and logical backups.

1.0.1 Physical & Logical Backups

Physical backups are backups of the physical files used in storing and recovering your database, such as datafiles, control files, and archived redo logs. Ultimately, every physical backup is a copy of files storing database information to some other location, whether on disk or some offline storage such as tape. It is also called file system backups. Physical backups are made with RMAN and OS Utilities.

Logical backups contain logical data (for example, tables or stored procedures) which are exported from a database with an Oracle Data Pump Export program (expdp) and stored in a binary file, for later re-importing into a database using the corresponding Data Pump Import program (impdp) utility. Note, RMAN does not do logical backup and recovery.

Physical or logical backup can be a whole or partial backup. In whole backup the copy of whole database is made while in partial backup a copy of the part of db is made.

An Oracle database can run in one of two modes i.e. ARCHIVELOG or NOARCHIVELOG Mode.

In ARCHIVELOG mode the physical or logical backup can be in the form of:

Online/Hot/Open Backup; Online backup is made while the database is open and online. It is in in-consistent state, because the files being backed up don not contain all the changes made at all the system change numbers (SCNs). In-consistent backups need Oracle recovery to be performed from online and archived redo logs.
Offline/Cold/Close Backup; Offline backup is made while the database is in shutdown mode i.e. in shutdown in normal mode. It can be both in consistent or in-consistent state. In consistent state all the data and control files are check pointed with the same SCNs. Because, it is in consistent state thus it does not need Oracle recovery.

In NOARCHIVELOG Mode the physical or logical backup can be in the form of:

Offline/Cold/Close Backup; Again it can be in consistent or in-consistent state. But performing in-consistent backup is not recommended in NOARCHIVELOG mode.

The following figure depicts the backup types graphically.

1.1 Database Recovery Process

Database recovery process involves constructing the contents of all or part of a database from a backup. It is a two phase process:

Retrieving a copy of the datafile from backup, and
Reapplying changes to the file since the backup from the archived and online redo logs, to bring the database to a desired SCN since the backup (usually, the present).

To restore a datafile or control file from backup is to retrieve the file onto disk from a backup location on tape, disk or other media, and make it available to the database server.

To recover a datafile (also called performing recovery on a datafile), is to take a restored copy of the datafile and apply to it changes recorded in the database's redo logs. To recover a whole database is to perform recovery on each of its datafiles.

Figure below illustrates the basic principle of backing up, restoring, and recovering a database.

In this example a full backup of a database (copies of its datafiles and control file) is taken at SCN 100. Redo logs generated during the operation of the database capture all changes that occur between SCN 100 and SCN 500. Along the way, some logs fill and are archived. At SCN 500, the datafiles of the database are lost due to a media failure. The database is then returned to its transaction-consistent state at SCN 500, by restoring the datafiles from the backup taken at SCN 100, then applying the transactions captured in the archived and online redo logs and undoing the un-committed transactions.

1.1.1 Data Recovery Types

The preceding scenario outlined the basics of the restore-and-recovery process. Several variants on this scenario are important to your backup and recovery work. The following forms of data recovery scenarios can be used:

Datafile Media Recovery
Complete, Incomplete and Point-In-Time Recovery
Crash Recovery

1.1.1.1 Datafile Media Recovery

Datafile media recovery (often simply called media recovery) is the most basic form of user-initiated data recovery. It can be used to recover from a lost or damaged current datafile, SPFILE or control file and apply the changes that were recorded in the redo logs or archived redo logs but not in the datafiles for a tablespace that went offline without the OFFLINE NORMAL option.

The need to restore a datafile from backup is not detected automatically. The first step in performing media recovery is to manually restore the datafile by copying it from a backup. Once a datafile has been restored from backup, however, the database does automatically detect that this datafile is out of date and must undergo media recovery.

Situations that force you to perform media recovery are:

You restore a backup of a datafile.
You restore a backup control file (even if all datafiles are current).
A datafile is taken offline (either by you or automatically by the database) without the OFFLINE NORMAL option.

A datafile that needs media recovery cannot be brought online until media recovery has been completed. A database cannot be opened if any of the online datafiles needs media recovery.

1.1.1.2 Complete, Incomplete and Point-In-Time Recovery

Complete recovery is recovering a database to the most recent point in time, without the loss of any committed transactions. Generally, the term "recovery" refers to complete recovery.

Occasionally, however, you need to return a database to its state at a past point in time. For example, to undo the effect of a user error, such as dropping or deleting the contents of a table, you may want to return the database to its contents before the delete occurred. In incomplete recovery, also known as point-in-time recovery, the goal is to restore the database to its state at some previous target SCN or time.

Point-in-time recovery is one possible response to a data loss caused by, for instance, a user error or logical corruption that goes unnoticed for some time.

Point-in-time recovery is also your only option if you have to perform a recovery and discover that you are missing an archived log covering time between the backup you are restoring from and the target SCN for the recovery. Without the missing log, you have no record of the updates to your datafiles during that period. Your only choice is to recover the database from the point in time of the restored backup, as far as the unbroken series of archived logs permits, then perform an OPEN RESETLOGS and abandon all changes in or after the missing log. (If you discover that you have lost archived logs and your database is still up, you should do a full backup immediately.)

1.1.1.3 Crash Recovery

The crash recovery process is a special form of recovery, which happens the first time an Oracle database instance is started after a crash (or SHUTDOWN ABORT). In crash recovery, the goal is to bring the datafiles to a transaction-consistent state, preserving all committed changes up to the point when the instance failed.

Unlike the forms of recovery performed manually after a data loss, crash recovery uses only the online redo log files and current online datafiles, as left on disk after the instance failure. Archived logs are never used during crash recovery, and datafiles are never restored from backup.

The database applies any pending updates in the online redo logs to the online datafiles of your database. The result is that, whenever the database is restarted after a crash, the datafiles reflect all committed changes up to the moment when the haven't said failure occurred. (After the database opens, any changes that were part of uncommitted transactions at the time of the crash are rolled back.)

The duration of crash recovery is a function of the number of instances needing recovery, amount of redo generated in the redo threads of crashed instances since the last checkpoint, and user-configurable factors such as the number and size of redo log files, checkpoint frequency, and the parallel recovery setting. You can set parameters in the database server that can tune the duration of crash recovery. You can also tune checkpointing to optimize recovery time.

1.1.2 When Recovery is Required from Backup?

While there are several types of problem that can halt the normal operation of an Oracle database or affect database I/O operations, only two typically require DBA intervention and media recovery:

User Errors
Media Failure
Application Errors

Other failures may require DBA intervention to restart the database (after an instance failure) or allocate more disk space (after statement failure due to, e.g, a full datafile) but these situations will not generally cause data loss or require recovery from backup.

1.1.2.1 User Errors

User errors occur when, either due to an error in application logic or a manual misstep; data in your database is changed or deleted incorrectly. Data loss due to user error includes such missteps as dropping important tables or deleting or changing the contents of a table.

While user training and careful management of privileges can prevent most user errors, your backup strategy determines how gracefully you recover the lost data when user error does cause data loss.

1.1.2.2 Media Failure

A media failure is the failure of a read or write of a disk file required to run the database, due to a physical problem with the disk such as a head crash. Any database file can be vulnerable to a media failure. The appropriate recovery technique following a media failure depends on the files affected and the types of backup available.

1.1.2.3 Application Errors
Sometimes a software malfunction can corrupt data blocks. In a physical corruption, which is also called a media corruption, the database does not recognize the block at all: the checksum is invalid, the block contains all zeros, or the header and footer of the block do not match. If the corruption is not extensive, then you can often repair it easily with block media recovery.

1.2 Instance Memory Used in Backups

In this section, we look at the memory areas that we need to be concerned with in relationship to RMAN. The principal memory structure that we are concerned with in terms of RMAN and backup and recovery is the System Global Area (SGA).

The particular substructures of SGA used by RMAN are the shared pool and the large pool. RMAN uses several Oracle PL/SQL packages. These packages must be loaded into the shared pool. The large pool is used by RMAN in specific cases and is not used by default, even if it is configured.

1.3 Database Structures Used in Database Recovery

The files and other structures that make up an Oracle database store data and safeguard it against possible failures. The physical structures that make up an Oracle database and their role in the reconstruction of a database from backup are:

Datafiles and Data Blocks
Redo Logs
Control Files
Undo Segments

1.4 Oracle Recovery Manager

RMAN is a stand-alone application that makes a client connection to the Oracle database to access internal backup and recovery packages. It is, at its very core, nothing more than a command interpreter that takes simplified commands you type and turns those commands into remote procedure calls (RPCs) that are executed at the database.

RMAN gives you access to several data backup and recovery techniques and features not available at all with user-managed backup and recovery. The most noteworthy are:

Incremental backups; which provide more compact backups (storing only changed blocks) and faster datafile media recovery (reducing the need to apply redo during datafile media recovery)
Block media recovery; in which a datafile with only a small number of corrupt data blocks can be repaired without being taken offline or restored from backup
Unused block compression; where RMAN can in some cases skip unused datafile blocks during backups
Binary compression; which uses a compression mechanism integrated into the Oracle database server to reduce the size of backups
Encrypted backups; which uses encryption capabilities integrated into the Oracle database to store backups in an encrypted format

RMAN also reduces the administration work associated with your backup strategy. RMAN keeps an extensive record of metadata about backups, archived logs, and its own activities, known as the RMAN repository. In restore operations, RMAN can use this information to eliminate the need for you to identify backup files for use in restores in most situations. You can also generate reports of backup activity using the information in the repository.

1.4.1 Files That RMAN Can Back Up

RMAN can back up all database files needed for efficient recovery in the event of a failure. RMAN supports backing up the following types of files:

Datafiles, and image copies of datafiles
Control files, and image copies of control files
Archived redo logs
The current server parameter file
Backup pieces, containing other backups created by RMAN

Note: Although the database depends on other types of files for operation, such as network configuration files, password files, and the contents of the Oracle home, these files cannot be backed up with RMAN. Likewise, some features of Oracle, such as external tables or the BFILE datatype, store data in files other than those listed here. RMAN cannot back up those files. You must use some non-RMAN backup solution for any files not in the preceding list.

1.4.2 RMAN Backup Destinations

RMAN can create and manage backups on disk and on tape, back up backups originally created on disk to tape, and restore database files from backups on disk or tape.

Devices used for tape backup are often referred to as SBT (System Backup to Tape) devices. RMAN interacts with SBT devices through software known as a media management layer, or media manager.

1.4.3 Full and Incremental Backups

Full backups are backups which include datafiles in their entirety. Full backups can be created with Recovery Manager or with operating system-level file copy commands.

Incremental backups are based on the idea of making copies only of changed data blocks in a data file. In recovery, extracting entire changed blocks from an incremental backup can substitute for application of redo for individual datafile updates during the time covered by the backup, shortening recovery times considerably. Incremental backups can only be created with RMAN.

1.4.4 Image Copies, Backup Sets and Backup Pieces

The results of an Oracle database backup created through RMAN can be either image copies or backup sets. An image copy is a bit-for-bit identical copy of a database file. RMAN can create image copy backups, although in the process, RMAN will check the contents for corruption, something that native operating-system file copy utilities cannot do. RMAN records image copies it creates in the RMAN repository, so that it can use them when restoring your database. Image copies can also be created using operating system commands such as cp in Unix or COPY in Windows.

RMAN can also store its backups in an RMAN-specific format called a backup set. A backup set is a collection of files called backup pieces, each of which may contain the backup of one or several database files. A backup task performed in RMAN can create one or more backup sets, which are recorded in the RMAN repository. Backup sets are also the only form in which RMAN can write backups to media manager devices like tape libraries. Backup sets are only created and accessed through Recovery Manager.

04 - Managing the Oracle Instance

2011-02-02T09:29:00.000+05:00

4.0 Oracle Database Management Framework

There are three major components of the Oracle database management framework:

The Management Interface.
A listener that allows connection to the database.
The database instance that is being managed.

Oracle Corporation’s recommended sequence for starting a database is to start the Management Interface, then the database listener, and then the database.

4.0.1 Management Interface

This may be either a management agent running on the database server which connects it to Oracle Enterprise Manager Grid Control or the stand-alone Oracle Enterprise Manager Database Control/Database Console. Database Control is a tool for managing one database (though this database can be clustered).

To start Database Control, use the emctl utility. This will be located in the ORACLE_HOME/bin directory. The three commands to start or stop Database Control and to check its status are:

emctl start dbconsole
emctl stop dbconsole
emctl status dbconsole

Figure below shows the startup of Database Control.

4.0.2 Database Listener

The database listener is a process that monitors a port for database connection requests. These requests use Oracle Net, Oracle’s proprietary communications protocol. Oracle Net is a layered protocol running over whatever underlying network protocol is in use, probably TCP/IP.

There are two ways to start the database listener:

With the lsnrctl utility
With Database Control

The lsnrctl utility is in the $ORACLE_HOME/bin directory. The key commands are

lsnrctl start [listener]
lsnrctl status [listener]

where listener is the name of listener. This will have defaulted to LISTENER, which is correct in most cases. Figure below shows the startup of listener.

4.0.3 Database Instance

4.0.3.1 Database Startup

When you start up a database, you create an instance of that database and you determine the state of the database. In fact, one does not start or stop a database: an instance may be started and stopped; a database is mounted and opened, and then dismounted and closed.

To start an instance, the database must read instance configuration parameters (the initialization parameters) from either a server parameter file (SPFILE) or a text initialization parameter file.

Database can be startup or shutdown from SQL*Plus, using the STARTUP and SHUTDOWN commands, or through Database Control.

If you want to override the default initialization parameter file with a text initialization parameter file, you can specify the PFILE clause of the STARTUP command to identify the initialization parameter file.

STARTUP PFILE = /u01/oracle/dbs/init.ora

You must perform some preliminary steps before attempting to start an instance of your database using SQL*Plus.

Ensure that environment variables are set so that you connect to the desired Oracle instance.

Connect to Oracle Database as SYSDBA.Ordinary users cannot start up or shut down a database. This is because an ordinary user is authenticated against the data dictionary. It is logically impossible for an ordinary user to start up an instance and open or create a database, since the data dictionary cannot be read until the database is open.

Dictionary authentication, anyone connecting with the above syntax cannot issue startup or shutdown
commands.

Instruct Oracle to go to the external password file to validate the username/password combination or

Use OS authentication; Oracle will go to the host operating system and check whether the OS user

running SQL*Plus is a member of the OS group that owns the Oracle software.

Note: SYSDBA and SYSOPER are not users; they are privileges that can be granted to users. By default, only user SYS has these privileges until they are deliberately granted to other users.

At any moment, a database will be in one of the following states:

SHUTDOWN
NOMOUNT
MOUNT
OPEN
RESTRICT

4.0.3.1.1 SHUTDOWN State

When the database is SHUTDOWN, all files are closed and the instance does not exist.

4.0.3.1.2 NOMOUNT State

In NOMOUNT mode, the instance has been started, but no connection has been made to a database. Starting an instance is typically includes the following tasks:

Searching $ORACLE_HOME/dbs for a parameter file of a particular name in this order:

spfile$ORACLE_SID.ora
If not found, spfile.ora
If not found, init$ORACLE_SID.ora

Specifying the PFILE parameter with STARTUP overrides the default behavior.

Allocating the SGA.
Starting the background processes.
Opening the alert_$ORACLE_SID.log file and the trace files. The location of the alert log is given by the BACKGROUND_DUMP_DEST parameter.

An instance is typically started only in NOMOUNT mode during:

Database Creation
Re-creation of Control Files
Backup & Recovery Scenarios

Note: The only files used in NOMOUNT mode are the parameter file and the alert log. The parameters in the parameter file are used to build the SGA in memory and to start the background processes. Entries will be written out to the alert log describing this process.

The startup process with NOMOUNT mode is described graphically in the figure below.

The following figure shows the STRATUP command with NOMOUNT state.

The above startup process is documented in the alert_$ORACLE_SID.log file as shown below:

4.0.3.1.3 MOUNT State

Once the instance is successfully started in NOMOUNT state, it may be transitioned to MOUNT state. Mounting a database includes the following tasks:

Associating a database with a previously started instance
Locating and opening the control files specified in the parameter file by using the CONTROL_FILES parameter
Reading the control files to obtain the names and statuses of the data files and online redo log files. However, no checks are performed to verify the existence of the data files and online redo log files at this time.

An instance is typically started and mounts a database, but do not open it to perform specific maintenance operations. For example:

Renaming data files (Data files for an offline tablespace can be renamed when the database is open)
Enabling and disabling online redo log file archiving options
Performing full database recovery

Note: A database may be left in MOUNT mode even though an OPEN request has been made. This may be because the database needs to be recovered in some way.

The startup process with MOUNT mode is described graphically in the figure below.

The following figure shows the STRATUP command with MOUNT state.

The above startup process is documented in the alert_$ORACLE_SID.log file as shown below:

4.0.3.1.4 OPEN State

In OPEN state the database is in normal operation mode. Normal database operation means that any valid user can connect to the database and perform typical data access operations. Opening the database includes the following tasks:

Opening the online data files
Opening the online redo log files

If any of the data files or online redo log files are not present when you attempt to open the database, then the Oracle server returns an error. The Oracle server verifies that all the data files and online redo log files can be opened and checks the consistency of the database. If necessary, the System Monitor (SMON) background process initiates instance recovery.

The overall startup process is described graphically in the figure below.

The following figure shows the STRATUP command which by default bring the database in open state.

The above startup process is documented in the alert_$ORACLE_SID.log file as shown below:

4.0.3.1.5 RESTRICT State

You can start an instance, and optionally mount and open a database, in restricted mode so that the instance is available only to administrative personnel (not general database users). Use this mode of instance startup when you need to accomplish one of the following tasks:

Perform an export or import of data
Perform a data load (with SQL*Loader)
Temporarily prevent typical users from using data
Perform certain migration or upgrade operations

Opening a database in restricted mode allows database access only to users with both the CREATE SESSION and RESTRICTED SESSION system privilege. When the instance is in restricted mode, a database administrator cannot access the instance remotely through an Oracle Net listener, but can only access the instance locally from the machine that the instance is running on. You can use the RESTRICT clause in combination with the MOUNT, NOMOUNT, and OPEN clauses.

The following figure shows the STRATUP command with RESTRICT state.

The above startup process is documented in the alert_$ORACLE_SID.log file as shown below:

Note: If an instance is running, STARTUP FORCE shuts it down with mode ABORT before restarting it.

4.0.3.1.6 Altering Database Availability

You can alter the availability of a database. You may want to do this in order to restrict access for maintenance reasons or to make the database read only.

When you need to perform specific administrative operations, the database must be started and mounted to an instance, but closed. You can achieve this scenario by starting the instance and mounting the database.

ALTER DATABASE MOUNT;

You can make a mounted but closed database available for general use by opening the database.

ALTER DATABASE OPEN;

Opening a database in read-only mode enables you to query an open database while eliminating any potential for online data content changes.

ALTER DATABASE OPEN READ ONLY;

You can also open a database in read/write mode (the default) as follows:

ALTER DATABASE OPEN READ WRITE;

4.0.3.2 Database Shutdown

To shut down a database and instance, you must first connect as SYSOPER or SYSDBA. To initiate database shutdown, use the SQL*Plus SHUTDOWN command or on EM Database Control page, you can click the Shutdown button to shut down the database and instance. If you then click the Advanced Options button, you can select the mode of the shutdown.

There are several modes for shutting down a database. Shutdown modes that wait for users to disconnect or for transactions to complete have a limit on the amount of time that they wait. If all events blocking the shutdown do not occur within one hour, the shutdown command cancels with the following message:

ORA-01013: user requested cancel of current operation

Following are the different shutdown modes:

NORMAL
IMMEDIATE
TRANSACTIONAL
ABORT

4.0.3.2.1 NORMAL Mode

To shut down a database in normal situations, use the SHUTDOWN command with the NORMAL clause:

The NORMAL clause is optional, because this is the default shutdown method if no clause is provided. Normal database shutdown proceeds with the following conditions:

No new connections are allowed after the statement is issued.
Before the database is shut down, the database waits for all currently connected users to disconnect from the database.

The next startup of the database will not require any instance recovery procedures.

Note: Typically, a normal shutdown is useless: there is always someone logged on, even if it is only the Database Control process.

4.0.3.2.2 IMMEDIATE Mode

It is the most typically used option. To shut down a database immediately, use the SHUTDOWN command with the IMMEDIATE clause:

Use immediate database shutdown only in the following situations:

To initiate an automated and unattended backup
When a power shutdown is going to occur soon
When the database or one of its applications is functioning irregularly and you cannot contact users to ask them to log off or they are unable to log off

Immediate database shutdown proceeds with the following conditions:

No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
Any uncommitted transactions are rolled back. (If long uncommitted transaction exists, this method of shutdown might not complete quickly, despite its name.)
Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly rolls back active transactions and disconnects all connected users.

The next startup of the database will not require any instance recovery procedures. The figure below shows the contents of the alert_$ORACLE_SID.log file when the database is shutdown immediately.

4.0.3.2.3 TRANSACTIONAL Mode

When you want to perform a planned shutdown of an instance while allowing active transactions to complete first, use the SHUTDOWN command with the TRANSACTIONAL clause:

Transactional database shutdown proceeds with the following conditions:

No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
After all transactions have completed, any client still connected to the instance is disconnected.
At this point, the instance shuts down just as it would when a SHUTDOWN IMMEDIATE statement is submitted.

A transactional shutdown prevents clients from losing work, and at the same time, does not require all users to log off. The next startup of the database will not require any instance recovery procedures.

4.0.3.2.4 ABORT Mode

You can shut down a database instantaneously by aborting the database instance. Use the SHUTDOWN command with the ABORT clause:

Use abort database shutdown only in the following situations:

You need to shut down the database instantaneously (for example, if you know a power shutdown is going to occur in one minute).
You experience problems when starting a database instance.

An aborted database shutdown proceeds with the following conditions:

No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
Current client SQL statements being processed by Oracle Database are immediately terminated.
Uncommitted transactions are not rolled back.
Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly disconnects all connected users.

The next startup of the database will require instance recovery procedures.

4.0.3.3 Quiescing a Database

In quiesced state you want to put a database in a state that allows only DBA transactions, queries, fetches, or PL/SQL statements. Without the ability to quiesce the database, you would need to shut down the database and reopen it in restricted mode. This is a serious restriction, especially for systems requiring 24x7 availability.

The quiesced state lets administrators perform actions that cannot safely be done otherwise. These actions include:

Actions that fail if concurrent user transactions access the same object--for example, changing the schema of a database table or adding a column to an existing table where a no-wait lock is required.
Actions whose undesirable intermediate effect can be seen by concurrent user transactions--for example, a multistep procedure for reorganizing a table when the table is first exported, then dropped, and finally imported. A concurrent user who attempts to access the table after it was dropped, but before import, would not have an accurate view of the situation.

To place a database into a quiesced state, issue the following statement:

Non-DBA active sessions will continue until they become inactive. Once all non-DBA sessions become inactive, the ALTER SYSTEM QUIESCE RESTRICTED statement completes, and the database is in a quiesced state. In an Oracle Real Application Clusters environment, this statement affects all instances, not just the one that issues the statement. You can determine the sessions that are blocking the quiesce operation by querying the V$BLOCKING_QUIESCE and V$SESSION view.

SELECT bl.sid, user, osuser, type, program

FROM v$blocking_quiesce bl, v$session se

where bl.sid = se.sid;

You can query the ACTIVE_STATE column of the V$INSTANCE view to see the current state of an instance.

The following statement restores the database to normal operation:

Note: Here DBA is defined only as user SYS or SYSTEM. Also you cannot perform a cold backup when the database is in the quiesced state.

4.0.3.4 Suspending and Resuming a Database

Suspended state halts all I/O to datafiles and control files. When the database is suspended all preexisting I/O operations are allowed to complete and any new database accesses are placed in a queued state. The suspend command is not specific to an instance. In an Oracle Real Application Clusters environment, when you issue the suspend command on one system, internal locking mechanisms propagate the halt request across instances, thereby quiescing all active instances in a given cluster.

The suspended state lets you back up a database without I/O interference.

To place a database into a suspended state, issue the following statement:

The V$INSTANCE view can be queried to confirm database status.

The following statement restores the database to normal operation:

03 - Oracle Database Server Architecture

2011-02-01T11:30:00.000+05:00

Oracle is designed to be a very portable database; it is available on every platform, from Windows to UNIX to mainframes. For this reason, the physical architecture of Oracle looks different on different operating systems. Even though the physical mechanisms used to implement Oracle from platform to platform vary, the architecture is sufficiently generalized so that you can get a good understanding of how Oracle works on all platforms.

3.0 Database Server Architecture

The Oracle database server consists of two entities, an Oracle Instance and an Oracle Database.

Instance is a set of oracle background processes (on UNIX) or single threaded process (on Windows) and shared memory area, which is shared across those processes/threads running on a single computer. Thus an instance can exist without any disk storage at all.

Database is a collection of physical operating system files or disks in case of Automatic Storage Management (ASM) or RAW partitions.

The relationship between database and instance is that a database may be mounted and opened by many instances, while an instance may mount and open a single database in its entire lifetime.

The following figure depicts graphically the previous paragraphs.

Let’s verify it practically; login as oracle user and perform the following steps.

Use the ps command to see all the processes being run by the user oracle (the Oracle software owner).

There are no Oracle server processes whatsoever at this point.

Now start up SQL*Plus and connect AS SYSDBA.

Again check process status our “instance” right now consists solely of the Oracle server process (oracleorcl).

Start instance in NOMOUNT mode. In NOMOUNT mode, the instance has been started, but no connection has been made to a database.

Now we have what we would call an “instance”. Through ps we can check the background processes that make up our instance.

We can also verify the shared memory area allocated to the instance as:

At this stage we don’t have any database i.e. no database is mounted and opened by our instance. We can verify it by querying the database as:

To bring life into the database, alter the state of instance to MOUNT and then OPEN.

We can use a simple query against some Oracle dynamic views, specifically V$DATAFILE, V$LOGFILE, and V$CONTROLFILE, to list the files that make up this database.

Let’s check the relationship between instance and database. If we close this database and try to open it again, we’ll discover that we can’t. It’s because an instance can mount and open at most one database in its life. We must discard this instance and create a new one in order to open this or any other database.

3.1 Oracle Instance

When an oracle instance is started on a server, the Oracle software allocates a shared memory area called the System Global Area (SGA) and starts several Oracle background processes.

The following figure depicts the overall architecture of the Oracle Instance.

3.1.1 System Global Area

SGA contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance’s SGA is shared among the users. Consequently, the SGA is sometimes called the shared global area.

All components (e.g. Java Pool, Large Pool) of SGA allocate and de-allocate space in units of granules. Granule size is determined by total SGA size. A single granule is an area of memory 4 MB, 8 MB, or 16 MB in size. The granule is the smallest unit of allocation, so if you ask for a Java pool of 5MB and your granule size is 4MB, Oracle will actually allocate 8MB to the Java pool (8 being the smallest number greater than or equal to 5 that is a multiple of the granule size of 4).

The Automatic Shared Memory Management feature simplifies the SGA memory management significantly. A DBA can simply specify the total amount of SGA memory available to an instance using the SGA_TARGET initialization parameter and the Oracle Database will automatically distribute this memory among various subcomponents to ensure most effective memory utilization. If SGA_TARGET is set to a value greater than SGA_MAX_SIZE at startup, then the latter is bumped up to accommodate SGA_TARGET. Note some SGA components like DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE and DB_nK_CACHE_SIZE are not automatically managed.

The SGA contains the following memory components:

Database Buffer Cache
Redo Log Buffer Cache
Shared Pool
Large Pool
Java Pool
Streams Pool
Fixed SGA

The following dynamic views can be used to investigate the information abut SGA.

V$SGAINFO
V$SGA_DYNAMIC_COMPONENTS
V$SGA_RESIZE_OPS
V$SGA_TARGET_ADVICE

The following figure depicts the internals of SGA.

3.1.1.1 Database Buffer Cache

The database buffer cache is the portion of SGA that holds copies of data blocks read from datafiles. All user processes concurrently connected to the instance share access to the database buffer cache.

Oracle supports multiple block sizes in a database. You specify the standard block size by setting the initialization parameter DB_BLOCK_SIZE. The allowed values for DB_BLOCK_SIZE are from 2K to 32K. The sizes and numbers of non-standard block size buffers are specified by the following parameters:

DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE

You can configure the database buffer cache with separate buffer pools that either keep data in the buffer cache or make the buffers available for new data immediately after using the data blocks.

The DEFAULT ( DB_BLOCK_SIZE) buffer pool contains data blocks from schema objects that are not assigned to any buffer pool, as well as schema objects that are explicitly assigned to the DEFAULT pool
The KEEP (DB_KEEP_CACHE_SIZE) buffer pool retains the schema object’s data blocks in memory.
The RECYCLE (DB_RECYCLE_CACHE_SIZE) buffer pool eliminates data blocks from memory as soon as they are no longer needed.

Note: Multiple buffer pools are only available for the standard block size. Non-standard block size caches have a single DEFAULT pool.

The buffers can be in the form of:

Free Buffers do not contain any useful data and are available for use.
Pinned Buffers are currently being accessed.
Dirty Buffers contain data that has been modified but has not yet been written to disk.

When configuring a new instance, it is impossible to know the correct size for the buffer cache. Typically, a database administrator makes a first estimate for the cache size, then runs a representative workload on the instance and examines the relevant statistics to see whether the cache is under or over configured.

3.1.1.2 Redo Log Buffer Cache

When a server process changes data in the data buffer cache (via an insert, a delete, or an update), it generates redo data, which is recorded in the redo log buffer. The log writer process (LGWR) writes redo information from the redo log buffer in memory to the redo log files on disk.

The initialization parameter LOG_BUFFER determines the size (in bytes) of the redo log buffer. In general, larger values reduce log file I/O, particularly if transactions are long or numerous. The default setting is either 512 kilobytes (KB) or 128 KB times the setting of the CPU_COUNT parameter, whichever is greater.

The redo log buffer is a circular buffer—the log writer process writes the redo entries from the redo log buffer to the redo log files, and server processes write new redo log entries over the entries that have been written to the redo log files. You only need to have a small redo log buffer, about 1MB or so. Large redo log buffers will reduce your log file I/O (especially if you have large or many transactions), but your commits will take longer as well.

3.1.1.3 Shared Pool

The shared pool portion of the SGA contains the library cache, the dictionary cache, buffers for parallel execution messages, and control structures. The total size of the shared pool is determined by the initialization parameter SHARED_POOL_SIZE. The default value of this parameter is 8MB on 32-bit platforms and 64MB on 64-bit platforms.

Library Cache: The library cache includes the shared SQL areas, private SQL areas (in the case of a shared server configuration), PL/SQL procedures and packages, and control structures such as locks and library cache handles.
Dictionary Cache: The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. The data dictionary cache is also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data).

3.1.1.4 Large Pool

An optional memory area to provide large memory allocations for:

Session memory for the shared server and the Oracle XA interface (used where transactions interact with more than one database).
I/O server processes.
Oracle backup and restore operations.

3.1.1.5 Java Pool

Java pool memory is used for all session-specific Java code and data within the JVM. In dedicated server mode, the Java pool includes the shared part of each Java class, which is actually used per session.

The parameter JAVA_POOL_SIZE is used to fix the amount of memory allocated to the Java pool for all session-specific Java code and data.

3.1.1.6 Streams Pool

The Streams pool is a new SGA structure starting in Oracle 10g. The Streams pool is used to buffer queue messages used by the Streams process as it is moving/copying data from one database to another. The Streams pool will only be important in systems using the Streams database feature. In those environments, it should be set in order to avoid “stealing” 10 percent of the Shared pool for this feature.

3.1.1.7 Fixed SGA

Fixed SGA contains a set of variables that point to other components of the SGA. It is like a bootstrap section of the SGA. This part is fixed for each release of oracle and can’t be altered by any parameter settings.

3.1.2 Oracle Processes

A process is a mechanism in an operating system that can run a series of steps. A process normally has its own private memory area in which it runs.

Oracle is a multiple-process (multi-user) database system which uses several processes to run different parts of the Oracle code and additional processes for the users—either one process for each connected user or one or more processes shared by multiple users.

The processes in an Oracle system can be categorized into two major groups:

User Processes; run the application or oracle tool code.
Oracle Server Processes; run the oracle database server code. They include Server Processes and Background Processes.

The process structure varies for different Oracle configurations, depending on the operating system and the choice of Oracle options. The code for connected users can be configured as a dedicated server or a shared server.

With dedicated server configuration, Oracle will create a new dedicated process for incoming connection for each session. There is a one-to-one mapping between a connection to the database and a server process or thread.
With shared server configuration, Oracle uses a pool of “shared processes” for a large community of users. A process (or set of processes) called dispatchers are used by the user process to talk to shared process.

3.1.2.1 User Processes

When a user runs an application program (e.g. oracle form) or an Oracle tool (e.g. Enterprise Manager or SQL*Plus), Oracle creates a user process to run the user’s application. Two terms, Connection and Session are closely related to user process.

A connection is a physical path from a client to an Oracle instance. A connection is established either over a network (Oracle Net Services) when different computers run the database application and Oracle, and communicate through a network or over an IPC mechanism when a computer runs both the user process and Oracle.

A session, on the other hand, is a logical entity in the instance, where a user process can execute SQL and so on. Many independent sessions can be associated with a single connection, and these sessions can even exist independently of a connection. A connection may have zero, one, or more sessions established on it.

We can use the v$session dynamic view to see sessions in action.

3.1.2.2 Oracle Server Processes

Oracle server processes run the Oracle database server code. Again it can be categorized into two groups:

Server Processes
Background Processes

3.1.2.2.1 Server Processes

The server process is the process that services an individual user process. In some situations when the application and Oracle operate on the same computer, it is possible to combine the user process and corresponding server process into a single process to reduce system overhead. However, when the application and Oracle operate on different computers, a user process always communicates with Oracle through a separate server process.

The most common configuration for the server process is to assign each user a dedicated server process. However, Oracle provides for a more sophisticated means of servicing several users through the same server process, called the shared server architecture.

The following self-explanatory figures depict the dedicated and shared server configuration.

We can query the v$shared_server dynamic view to get information about the shared server process.

For server process the Oracle allocates a memory area called a program global area (PGA). PGA contains data and control information for server process. Unlike the SGA, the PGA is for the exclusive use of each server process and can’t be shared by multiple processes; consequently, the PGA is sometimes called the private global area.

The total PGA memory allocated by each server process attached to an Oracle instance is also referred to as the aggregated PGA memory allocated by the instance. You use automatic PGA memory management by setting the PGA_AGGREGATE_TARGET parameter.

The content of the PGA memory varies, depending on whether the instance is running the shared server option. But generally it can be:

Session Memory: Session memory is the memory allocated to hold a session’s variables (logon information) and other information related to the session. For a shared server, the session memory is shared and not private.
Private SQL Area: Each session that issues a SQL statement has a private SQL area. For dedicated server, private SQL areas are located in the server process’s PGA. However, for shared server, part of the private SQL area is kept in the SGA. We can query the V$SQL_WORKAREA to get information about SQL area.

The following dynamic views can be used to investigate the information abut PGA.

V$PGASTAT
V$PROCESS 

3.1.2.2.2 Background Processes

The background processes are the real workhorses of the Oracle instance—they enable large numbers of users to concurrently and efficiently use information stored in database. Each of the Oracle background processes is in charge of a separate task, thus increasing the efficiency of the database instance. These processes are automatically created by Oracle when you start the database instance, and they terminate when the database is shut down.

An Oracle instance can have many background processes; not all are always present. You can query the V$BGPROCESS view for more information on the background processes.

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)
Job Queue Processes
Queue Monitor Processes (QMNn)
Other Background Processes

Figure below illustrates how each background process interacts with the different parts of an Oracle database.

Database Writer Process (DBWn): The database writer processes (DBWn) are responsible for writing modified (dirty) buffers in the database buffer cache to disk. The initialization parameter DB_WRITER_PROCESSES specifies the number of DBWn processes. The maximum number of DBWn processes is 20 i.e. DBW1 through DBW9 and DBWa through DBWj. But these additional DBWn processes are only useful on multi-processor systems. If DB_WRITER_PROCESSES is not specified by the user during startup, Oracle determines how to set it based on the number of CPUs and processor groups.

DBWn manages the buffer cache so that user processes can always find free buffers. The database

writer process writes dirty buffers to disk under the following conditions:

When the database issues a checkpoint
When a server process can’t find a clean reusable buffer
Every 3 seconds

Log Writer Process (LGWR): The log writer process (LGWR) is responsible for redo log buffer management—writing the redo log buffer to a redo log file on disk. The redo log buffer is a circular buffer. When LGWR writes redo entries from the redo log buffer to a redo log file, server processes can then copy new entries over the entries in the redo log buffer that have been written to disk.

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.

The log writer process writes the contents of the redo log buffer to disk under any of the following circumstances:

Every three seconds
Whenever someone commits
When LGWR is asked to switch log files
When the redo buffer gets one-third full or contains 1MB of cached redo log data

Checkpoint Process (CKPT): The checkpoint (CKPT) process is charged with telling the database writer process when to write the dirty data in the memory buffers to disk. After telling the database writer process to write the changed data, the checkpoint process updates the data file headers and the control file to indicate when the checkpoint was performed. The purpose of the checkpoint process is to synchronize the buffer cache information with the information on the database disks.

A checkpointing process involves the following steps:

Flushing the contents of the redo log buffers to the redo log files
Writing a checkpoint record to the redo log file
Flushing the contents of the database buffer cache to disk
Updating the data file headers and the control files after the checkpoint completes

System Monitor Process (SMON): The system monitor process (SMON) performs recovery, if necessary, at instance startup. SMON is also responsible for cleaning up temporary segments that are no longer in use and for coalescing contiguous free extents within dictionary managed tablespaces. If any terminated transactions were skipped during instance recovery because of file-read or offline errors, SMON recovers them when the tablespace or file is brought back online. Other processes can call SMON if they detect a need for it.

With Real Application Clusters, the SMON process of one instance can perform instance recovery for a failed CPU or instance.

Process Monitor Process (PMON): The process monitor (PMON) performs process recovery when a user process fails. PMON is responsible for cleaning up the database buffer cache and freeing resources that the user process was using.

PMON periodically checks the status of dispatcher and server processes, and restarts any that have stopped running (but not any that Oracle has terminated intentionally). 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.

Recoverer Process (RECO): The recoverer process (RECO) is used with the distributed database configuration that automatically resolves failures involving distributed transactions. 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.

Archiver Processes (ARCn): The archiver process (ARCn) copies redo log files to a designated storage device after a log switch has occurred. ARCn processes are present only when the database is in ARCHIVELOG mode, and automatic archiving is enabled.

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.

If you anticipate a heavy workload for archiving, such as during bulk loading of data, you can specify multiple archiver processes with the initialization parameter LOG_ARCHIVE_MAX_PROCESSES.

Job Queue Processes (Jnnn): Job queue processes are used for batch processing. They run user jobs. For each instance, these job queue processes are dynamically spawned by a coordinator job queue (CJQ0) background process.

The initialization parameter JOB_QUEUE_PROCESSES represents the maximum number of job queue processes that can concurrently run on an instance.

Queue Monitor Processes (QMNn): The queue monitor process is an optional background process for Oracle Streams Advanced Queuing, which monitors the message queues. You can configure up to 10 queue monitor processes.

Other Background Processes: There are several other background processes that might be running. These can include the following:

Manageability Monitor (MMON) process collects several types of statistics to help the database manage itself. For example, MMON collects the Automatic Workload Repository (AWR) snapshot information, which is the basis for the performance diagnostics capability of the Automatic Database Diagnostic Monitor (ADDM).
Memory Manager (MMAN) process coordinates the sizing of the memory components. It observes the system and workload in order to determine the ideal distribution of memory, and it ensures that the needed memory is available.
Rebalance Master (RBAL) process coordinates disk rebalancing activity when you use an Automatic Storage Management (ASM) storage system.
ASM Rebalance (ARBn) processes perform the disk rebalancing activity in an ASM instance.
ASM Background (ASMB) process is present in all Oracle databases that use an ASM storage system. The ASMB process communicates with the ASM instance by logging into the ASM instance as a foreground process.
Recovery Writer (RVWR) process writes the flashback data from the flashback buffer to the flashback logs.

3.2 Oracle Database

The Oracle database has a logical layer and a physical layer. The physical layer consists of the files that reside on the disk; the components of the logical layer map the data to these physical components.

The separation of logical layer from physical layer is a necessary part of the relational database paradigm. The relational paradigm states that programmers should address only logical structures and let the database manage the mapping to physical structures. Thus database administrators see physical components while programmers see logical components.

The physical layer of the database consists of the following types of files:

Data Files
Control Files
Online Redo Log Files
Archive Log Files
Parameter Files
Trace Files
Alert Files
Password File
Backup Files
Flashback Log Files (Optional)
Change Tracking Files (Optional)

The logical layer of the database consists of the following types of structures:

Tablespaces
Segments
Extents
Oracle Blocks

Figure below shows the ER diagram of logical structure and physical structure.

Note: A schema is a collection of database objects that are owned by a particular user. A schema has the same name as that user. Schema objects are the logical structures.

3.2.1 Physical Layer

3.2.1.1 Data Files

Data files are the files where all of the data of the database will be stored. Every database has at least two data file associated with it, and typically it will have many more than two. The first tablespace in any database is always the SYSTEM tablespace, so Oracle automatically allocates the first datafiles of any database for the SYSTEM tablespace during database creation.

A datafile can be associated with only one tablespace and only one database but a tablespace can spawn more than one datafile. If several disk drives are available to store the database, consider placing potentially contending datafiles on separate disks. This way, when users query information, both disk drives can work simultaneously, retrieving data at the same time.

Note: Datafiles should not be stored on the same disk drive that stores the database redo log files.

You can investigate about the data files by querying the following views:

V$DATAFILE
DBA_DATA_FILES
V$TEMPFILE
DBA_TEMP_FILES

3.2.1.2 Control Files

The database control file is a small binary file necessary for the database to start and operate successfully. The control file is critical to the functioning of the database, and recovery is difficult without access to an up-to-date control file. If all control files of a database are permanently lost during operation, then the instance is aborted and media recovery (type of recovery that takes a backup and applies redo) is required.

The control file contains the names and locations of the data files, redo log files, current log sequence numbers, backup set and backup piece details, check point information and the all-important system change number (SCN), which indicates the most recent version of committed changes in the database.

You specify control file names using the CONTROL_FILES initialization parameter in the database initialization parameter file. Every database has one control file, but due to the file’s importance, multiple identical copies (usually three) are maintained on a different physical disk—when the database writes to the control file, all copies of the file get written to. It is very important that you back up your control files. This is true initially, and every time you change the physical structure of your database.

The following views display information about control files:

V$CONTROLFILE
V$PARAMETER

3.2.1.3 Online Redo Log Files

The online redo log files record all the changes made to the database, and they are vital during the recovery of a database. The online redo log consists of groups of online redo log files, each file being known as a member. An Oracle database requires at least two groups of at least one member each to function.

These files are filled with redo records or redo entry. Redo entries record data that you can use to reconstruct all changes made to the database. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks.

Whenever a transaction is committed, LGWR writes the transaction redo records from the redo log buffer of the SGA to a redo log file, and assigns a system change number (SCN) to identify the redo records for each committed transaction. Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed.

LGWR writes to redo log files in a circular fashion. When the current redo log file fills, LGWR begins writing to the next available redo log file. When the last available redo log file is filled, LGWR returns to the first redo log file and writes to it, starting the cycle again.

The point at which the database stops writing to one redo log file and begins writing to another is called log switching. Each online or archived redo log file is uniquely identified by its log sequence number. During crash, instance, or media recovery, the database properly applies redo log files in ascending order by using the log sequence number of the necessary archived and redo log files.

The following views provide information on redo logs.

V$LOG
V$LOGFILE

3.2.1.4 Archive Log Files

An archived redo log file is a copy of one of the filled members of an online redo log group. The process of turning redo log files into archived redo log files is called archiving. This process is only possible if the database is running in ARCHIVELOG mode. You can choose automatic or manual archiving.

The background process ARCn automates archiving operations when automatic archiving is enabled. The database starts multiple archiver processes as needed to ensure that the archiving of filled redo logs does not fall behind. The LOG_ARCHIVE_MAX_PROCESSES initialization parameter specifies the num ARCn processes that the database initially invokes. The default is two processes.

If you want to archive only to a single destination, you specify that destination in the LOG_ARCHIVE_DEST initialization parameter. If you want to multiplex the archived logs, you can choose whether to archive to up to ten locations (using the LOG_ARCHIVE_DEST_n parameters). The two modes of transmitting archived logs to their destination are normal archiving transmission and standby transmission mode. Normal transmission involves transmitting files to a local disk. Standby transmission involves transmitting files through a network to either a local or remote standby database.

You can display information about the archived redo logs using the following sources:

V$DATABASE
V$ARCHIVE_DEST
SQL*Plus command ARCHIVE LOG LIST

3.2.1.5 Parameter Files

There are many different parameter files associated with an Oracle database, from a tnsnames.ora file on a client workstation (used to find a server on the network), to a listener.ora file on the server (for the network listener startup), to the sqlnet.ora, cman.ora, and ldap.ora files, to name a few. The most important parameter file, however, is the database’s parameter file—without this, we cannot even get a database started.

The parameter file for a database is commonly known as an init, pfile or spfile. The init and pfile (parameter file) is a text based file while spfile (server parameter file) has a binary format. It is called server parameter file because of the fact that it must reside on the server, while the text based parameter file can also be located on the client system. Because spfile is always stored on the database server; it removes the proliferation of parameter files and removes the need to manually maintain parameter files using text editors outside of the database.

Parameter file contains a list of initialization parameters for an instance and a database. Parameter is a key/value pair. Initialization parameter tells Oracle, the name of database for which to startup the instance, memory for SGA, name and location for database control files.

By default the init<SID>.ora can be found in dbs (on Linux) or database folder (on windows) while the spfile is located in dbs directory on both platforms. It is not necessary that a parameter file must be in particular location, you can use the pfile=name option with startup command.

We can convert the pfile into spfile and vice versa as shown below:

CREATE SPFILE FROM PFILE='/u01/oracle/dbs/init.ora';
CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora'
FROM PFILE='/u01/oracle/dbs/test_init.ora';

Spfiles are binary files, so what happens if one gets corrupted and the database won’t start? At least the init.ora file was just text, so we could edit it and fix it. First, the amount of binary data in the SPFILE is very small. If you are on a Linux platform, a simple strings command will extract all of your settings:

In the event that the spfile has just “gone missing”, you can also restore the information for your parameter file from the database’s alert log. Every time you start the database, the alert log will contain a section having initialization parameter information.

You can display information about the archived redo logs using the following sources:

V$PARAMETER
V$PARAMETER2
V$SPPARAMETER
SQL*Plus command SHOW PARAMETERS

3.2.1.6 Trace Files

Each server and background process writes to an associated trace file. When a process detects an internal error, it dumps information about the error to its trace file. Trace files are a source of debugging information. Programmers who wrote the database kernel put the debugging code, and they left it in, on purpose.

All filenames of trace files associated with a process contain the name of the process that generated the trace file. The one exception to this is trace files generated by job queue processes (Jnnn).

Trace file is generated on the database server machine in one of two locations:

If you are using a dedicated server connection, the trace file will be generated in the directory specified by the USER_DUMP_DEST parameter.
If you are using a shared server connection, the trace file will be generated in the directory specified by the BACKGROUND_DUMP_DEST parameter.

You can display information about the trace files using the following sources:

V$PARAMETER
SQL*Plus command SHOW PARAMETER DUMP_DEST

3.2.1.7 Alert Files

The alert file also known as the alert log is the diary of the database. It is a simple text file written to from the day the database is created to until you erase it. In this file, you will find a chronological history of your database—the log switches; the internal errors that might be raised; when tablespaces were created, taken offline, put back online; and so on.

The alert log can come in handy during troubleshooting—it is usually the first place you should check to get an idea about what was happening inside the database when a problem occurred. In fact, Oracle support may ask you for a copy of the pertinent sections of the alert log during their analysis of database problems.

Oracle puts the alert log (alertdb_name.log) in the location specified for the BACKGROUND_DUMP_DEST initialization parameter.

V$ALERT_TYPES
DBA_OUTSTANDING_ALERTS
DBA_ALERT_HISTORY
SQL*Plus command SHOW PARAMETER background_dump; to find out where the alert log is located

Too see if there are any Oracle-related errors in your alert log, simply issue the following command:

3.2.1.8 Password File

The password file is a file in which you can specify the names of database users who have been granted the special SYSDBA or SYSOPER administrative privileges.

When you attempt to start up Oracle, there is no database available that can be consulted to verify passwords. When you start up Oracle on the “local” system (i.e., not over the network, but from the machine the database instance will reside on), Oracle will use the OS to perform the authentication. When Oracle was installed, the person performing the installation was asked to specify the “group” for the administrators. Normally on UNIX/Linux, this group will be dba by default and osdba on Windows. It can be any legitimate group name on that platform, however. That group is “special,” in that any user in that group can connect to Oracle “as SYSDBA” without specifying a username or password, for example:

However, suppose you wanted to perform these operations from another machine, over the network. In that case, you would attempt to connect using @tns-connect-string. However, this would fail:

OS authentication won’t work over the network for SYSDBA, even if the very unsafe parameter REMOTE_OS_AUTHENT is set to TRUE. Here comes the password file for rescue. For remote authentication, first, we have set the REMOTE_LOGIN_PASSWORDFILE.

Here EXCLUSIVE means only one database can uses a given password file. This setting cannot be changed dynamically while the instance is up and running, so we’ll have to restart for this to take effect.

The next step is to use the command-line tool named orapwd:

Now we can connect as SYSDBA over the network:

sqlplus sys/oracle@localhost.localdomain/orcl as sysdba

Password file resides in the $ORACLE_HOME/dbs directory on linux (%ORACLE_HOME%\database on windows).

The V$PWFILE_USERS view can be used to see the users who have been granted SYSDBA or SYSOPER system privileges for a database.

3.2.1.9 Backup Files

Backup files are used for database recovery. A backup is a copy of data. This copy can include important parts of the database, such as the control file and datafiles.

3.2.1.10 Flashback Log Files

Flashback logs were introduced in Oracle 10g in support of the FLASHBACK DATABASE command. The FLASHBACK DATABASE command was introduced to speed up the otherwise slow process of a point in time database recovery. It can be used in place of a full database restore and a rolling forward using archive logs, and it is primarily designed to speed up the recovery from an “accident.” Flash-back logs contain “before images” of modified database blocks that can be used to return the database to the way it was at some prior point in time.

3.2.1.11 Change Tracking Files

The change tracking file is a new file, use with Oracle 10g Enterprise Edition. The sole purpose of this file is to track what blocks have modified since the last incremental backup. In this fashion, the Recovery Manager (RMAN) tool can back up only the database blocks that have actually been modified without having to read the entire database.

The process of creating the change tracking file is simple and is accomplished via the ALTER DATABASE command:

To turn off and remove the block change tracking file, you would use the ALTER DATABASE command once again:

Note: This command will in fact erase the block change tracking file. It does not just disable the feature it removes the file as well.

3.2.2 Logical Layer

3.2.2.1 Tablespaces

A database is divided into logical storage units called tablespaces, which group related logical structures (table, index etc) together. One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace.

The previous paragraph is graphically depicted in the following figure:

Tablespaces are divided into logical units of storage called segments, which are further divided into extents. The units of database space allocation are data blocks, extents, and segments.

There is no hard and fast rule regarding the number of tablespaces you can have in a database. The following five tablespaces are generally the default tablespaces that all databases must have, even though it’s possible to create and use a database with just the first two:

System Tablespace: It always contains the data dictionary tables for the entire database.
Sysaux Tablespace: It is an auxiliary tablespace to the SYSTEM tablespace. The SYSAUX tablespace provides a centralized location for database metadata that does not reside in the SYSTEM tablespace.
Undo Tablespace: It is used solely for storing undo information. You cannot create any other segment types (for example, tables or indexes) in undo tablespaces. In automatic undo management mode, each Oracle instance is assigned one (and only one) undo tablespace.
Temporary Tablespace: It contains transient data that persists only for the duration of the session.
Default Permanent Tablespace: It contains user objects.

Tablespaces allocate space in extents. Tablespaces can use two different methods to keep track of their free and used space:

Locally Managed Tablespaces: extent management is done by the tablespace. It maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Changes do not generate rollback information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).
Dictionary Managed Tablespaces: extent management is done by the data dictionary. Oracle updates the appropriate tables in the data dictionary whenever an extent is allocated or freed for reuse. Oracle also stores rollback information about each update of the dictionary tables.

The following data dictionary and dynamic performance views provide useful information about the tablespaces of a database.

V$TABLESPACE
DBA_TABLESPACES
USER_TABLESPACES

3.2.2.2 Segments

A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace. There are three types of segments in an Oracle database:

Data Segments: A single data segment in an Oracle database holds all of the data for one of the following: table that is not partitioned or clustered, partition of a partitioned table, cluster of tables and a materialized view.
Index Segments: Every non partitioned index in an Oracle database has a single index segment to hold all of its data. For a partitioned index, every partition has a single index segment to hold its data.
Temporary Segments: When processing queries, Oracle often requires temporary workspace for intermediate stages of SQL statement parsing and execution. Oracle automatically allocates this disk space called a temporary segment. Typically, Oracle requires a temporary segment as a database area for sorting, temporary tables and there indexes.

The following data dictionary and dynamic performance views provide useful information about the segments.

DBA_SEGMENTS
USER_SEGMENTS
V$SORT_SEGMENT
V$TEMPSEG_USAGE

3.2.2.3 Extents

An extent is a logical unit of database storage space allocation made up of a number of contiguous data blocks. One or more extents in turn make up a segment.

When you create a table, Oracle allocates to the table’s data segment an initial extent of a specified number of data blocks. Although no rows have been inserted yet, the Oracle data blocks that correspond to the initial extent are reserved for that table’s rows. If the data blocks of a segment’s initial extent become full and more space is required to hold new data, Oracle automatically allocates an incremental extent for that segment. An incremental extent is a subsequent extent of the same or greater size than the previously allocated extent in that segment.

For maintenance purposes, the header block of each segment contains a directory of the extents in that segment. A tablespace that manages its extents locally can have either uniform extent sizes or variable extent sizes that are determined automatically by the system.

You can display information about the extents using the following views:

DBA_EXTENTS
USER_EXTENTS
DBA_FREE_SPACE
USER_FREE_SPACE

3.2.2.4 Oracle Blocks

Extents, in turn, consist of Oracle blocks. An Oracle block is the smallest unit of space allocation in Oracle. In contrast, at the physical, operating system level, all data is stored in bytes. Each operating system has a block size. Oracle requests data in multiples of Oracle data blocks, not operating system blocks.

The standard block size is specified by the DB_BLOCK_SIZE initialization parameter. In addition, you can specify up to five nonstandard block sizes. The data block sizes should be a multiple of the operating system’s block size within the maximum limit to avoid unnecessary I/O.

The Oracle data block format is similar regardless of whether the data block contains table, index, or clustered data. Figure below illustrates the format of a data block.

Header (Common and Variable): The header contains general block information, such as the block address and the type of segment for example, data or index.
Table Directory: This portion of the data block contains information about the table having rows in this block.
Row Directory: This portion of the data block contains information about the actual rows in the block (including addresses for each row piece in the row data area). After the space has been allocated in the row directory of a data block’s overhead, this space is not reclaimed when the row is deleted. Therefore, a block that is currently empty but had up to 50 rows at one time continues to have 100 bytes allocated in the header for the row directory. Oracle reuses this space only when new rows are inserted in the block.
Row Data: This portion of the data block contains table or index data. Rows can span blocks.
Free Space: Free space is allocated for insertion of new rows and for updates to rows that require additional space. In data blocks allocated for the data segment of a table or cluster, or for the index segment of an index, free space can also hold transaction entries. A transaction entry is required in a block for each INSERT, UPDATE, DELETE, and SELECT...FOR UPDATE statement accessing one or more rows in the block.

In two circumstances, the data for a row in a table may be too large to fit into a single data block. In the first case, the row is too large to fit into one data block when it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. Row chaining in these cases is unavoidable.

However, in the second case, a row that originally fit into one data block is updated so that the overall row length increases, and the block’s free space is already completely filled. In this case, Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row. The rowid of a migrated row does not change.

When a row is chained or migrated, I/O performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for the row.

02 - Oracle Database Creation

2011-01-19T09:21:00.000+05:00

Before you can create a new database, the following prerequisites must be met:

The Oracle software must be installed: This includes setting various environment variables unique to your operating system and establishing the directory structure for software and database files.
You must have the operating system privileges associated with a fully operational database administrator. Your Oracle username should be included in the DBA group by the system administrator, if you are working on a UNIX or a Linux server.

You can create an Oracle Database in two ways:

Use the CREATE DATABASE statement.
Use the Database Configuration Assistant (DBCA).

2.0 Planning the Database

The database must be created with proper planning and research. Below is the list of some recommended actions:

Plan the Logical Storage Structure: The number of tablespces, tables and indexes to use and estimate the amount of space they will require.
Plan Physical Storage Structure: The underlying operating system files your database will comprise. Proper distribution of files can improve database performance dramatically by distributing the I/O during file access. You can distribute I/O in several ways when you install Oracle software and create your database. For example, you can place redo log files on separate disks or use striping.
Use Oracle Managed Files: Consider using Oracle-managed files and Automatic Storage Management to create and manage the operating system files that make up your database storage.
Use Server Parameter File: A server parameter file lets you store and manage your initialization parameters persistently in a server-side disk file.
Use Time Zones: Consider what time zones your database must support.
Block Size: Select the standard database block size. This is specified at database creation by the DB_BLOCK_SIZE initialization parameter and cannot be changed after the database is created.

2.1 Using the CREATE DATABASE Statement

Decide on a directory structure for the database; it is recommended that you comply with Oracle's Flexible Architecture as shown below:

Decide on a unique Oracle system identifier (SID) for your instance and set the ORACLE_SID environment variable accordingly. This identifier is used to distinguish this instance from other Oracle Database instances that you may create later and run concurrently on your system.

Open your favorite text editor and type the following minimum initialization parameters.

Store your initialization parameter file with name initdb1.ora in the /u01/app/oracle/admin/db1/pfile

directory.

Start SQL*Plus and connect to your Oracle Database instance AS SYSDBA.

Create a Server Parameter File. The server parameter file enables you to change initialization parameters with database commands and persist the changes across a shutdown and startup.

Start an instance without mounting a database. Typically, you do this only during database creation or while performing maintenance on the database.

To create the new database, use the CREATE DATABASE statement. The following statement creates database db1, note we are using Oracle-Managed Files.

To make the database functional, you need to create additional user tablespace.

Run the scripts necessary to build the database. CATALOG.SQL creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. CATPROC.SQL Runs all scripts required for or used with PL/SQL.

The following graphics shows the database files.

2.2 Using the Database Configuration Assistant

The Database Configuration Assistant (DBCA) tool creates a database from Oracle-supplied templates. Templates contain settings optimized for workload. Choose the template suited to the type of workload your database will support. Alternatively, you can create your own database and templates.

Start DBCA on UNIX or Linux, or at the command-line prompt on the Windows operating system, enter the following command:

The dbca utility is typically located in the $ORACLE_HOME/bin directory.

Click Next to bypass with Welcome window.
Select Create Database from the wizard and click next.

The next screen shows pre-defined templates. If you are not sure which to choose, select the default General Purpose template. You can check the initialization parameters by clicking the Show Details button. Click next.

In the Global Database Name box, enter the database name in the form database_name.domain_name. In the SID box, enter the Oracle system identifier. The SID defaults to the database name and uniquely identifies the instance that runs the database. Click next.

Set up your database so it can be managed with Oracle Enterprise Manager.

Specify the passwords for the administrative accounts such as SYS and SYSTEM.

Select storage mechanism do you want your database to use.

File System option creates database files that are managed by your operating system's file system. Automatic Storage Management enables you to define a pool of storage (called a disk group) in which

Oracle automatically manages database file placement and naming. Raw Devices option enables you to manage your storage devices outside of the operating system file system by providing Oracle Database with unformatted physical disk space called raw devices.

If you are not certain of which option to use, choose the default selection of File System.

Specify file locations for the database files. For now select the Use Database File Location from Template, we will change it at the end according to our requirements.

Specify Flash Recovery Area option to specify a backup and recovery area and specify its directory location and size. Enable Archiving option to enable archiving of database redo logs, which can be used to recover a database.

Load database with data. Check Sample Schemas to include the Sample Schemas tablespace in your database. In the Custom Scripts tab, you can specify one or more SQL scripts to run after your database is created. Scripts are useful for performing post-installation tasks, such as loading custom schemas.

The initialization parameters window is used to set the initialization parameters that control how the database manages its memory usage.

Sizing tab specify the smallest block size and the maximum number of operating system user processes that can simultaneously connect to the database. Character Sets tab define the character sets used by your database. Character sets are the encoding schemes used to display characters on your computer screen. National character set is an alternative character set that enables you to store Unicode characters in a database that does not have a Unicode database character set. Connection Mode can be shared or dedicated.

Next a navigation tree displays the storage structure of your database (control files, datafiles, redo log groups, and so forth). Here we will change the files location as shown below:

Check create database checkbox and select finish to start orcl database creation.

The database configuration assistant window will be appeared and shows the database creation progress.

The following graphics shows the database files.

2.3 Viewing Information about the Database

You can view information about your database content and structure using the following views:

DATABASE_PROPERTIES
GLOBAL_NAME
V$DATABASE
V$PARAMETER
V$PARAMETER2
V$SPPARAMETER

01 - Oracle Database Server Installation

2011-01-10T09:34:00.000+05:00

Installing the Oracle server software is really a simple affair. Installing all the software will probably not take you more than a couple of hours. All the real effort goes into the proper planning of such things as the operating system configuration and physical space you need for your Oracle databases to function optimally.

1.0 Choice of Operating System

All operating systems have good and bad points: none are suitable for all applications. In general, Oracle Corporation supports all the mainstream platforms, including:

Linux on Intel and AMD
Microsoft Windows on Intel and AMD
Solaris on SPARC (Scalable Processor Architecture) from Sun Microsystems
HPUX on PA-RISC (Precision Architecture- Reduced Instruction Set Computer) from HP
AIX on POWER from IBM

1.0.1 Setting up the RHEL 5.0 on VMware Workstation

Open the VMware and Select File --> New --> Virtual Machine.
Select typical configuration from the Welcome screen and click Next.

Select the last option on next screen and click Next.

Select Red Hat Enterprise Linux 5 from the drop down list and click Next.

Enter virtual machine Name, Location and click Next button.

On next screen enter the value for disk size and click Finish on next screen.

On the RedHat5 tab select Edit Virtual Machine Settings.

Click the Add button at the bottom of list. Select Hard Disk from the hardware list and click Next.

Select Create a new virtual disk option and click next button.

Here select the SCSI option.

Provide 10 GB size and on next screen accept the default name and clcik Finish.

Similarly add three more hard disks each one has a size of 10GB.
Your Virtual Machine Setting should be identical to the one as shown below.

Insert the RHEL media in the DVD player and Power On the Virtual Machine, you will be welcomed by the RHEL installer.

Click the Next button on the next screen.

On next screens select Language, Keyboard and enter Installation Number.
Select yes on next screen to create partitions on our virtual drives.

Select Custom Layout from the drop down list on next screen and click Next.

Set the partition on first drive (sda) as shown below. For oracle the swap size should be 1.5 times of the RAM size.

Partition the next drives as shown below.

Provide root user password and click Next. On package selection screen select Customize Now.

Select the all packages for development section of customization to fulfill the oracle installation requirements.

Finally select Next, after formatting the partition the installer will start installation of the required packages.

1.0.2 Configure RHEL Kernel for Oracle

Oracle recommends that you tune the following values to optimize the performance of the system.

     Semaphore Parameters:         semmsl, semmns, semopm, semmni
     Shared Memory Segments:      shmall, shmmax, shmmni
     File Handles:                            file-max
     Port Numbers:               ip_local_port_range
     Socket Receive Buffer Size:     rmem_default, rmem_max
     Socket Send Buffer Size:         wmem_default, wmem_max

If the value of any kernel parameter is different from the recommended value, then using any text editor, create or edit the /etc/sysctl.conf file, and add or edit lines similar to the following:

Restart the system to persist the changes.

1.0.3 Configure RHEL Shell for Oracle

To improve the performance of the software on Linux systems, you must increase the following shell limits for the oracle user:

nofile Maximum number of open file descriptors
nproc Maximum number of processes available to a single user

Add the following lines to the /etc/security/limits.conf file:

Add or edit the following line in the /etc/pam.d/login file, if it does not already exist:

1.0.4 Check Network Configuration

Verify that the /etc/hosts file is used for name resolution. You can do this by checking the hosts file entry in the nsswitch.conf file.

The output of the command should contain an entry for files.

Check hostname.

The output should be similar to the output shown above.

Check the domain name has not been set dynamically.

Check the hosts file contains the fully qualified host name by using the following command.

This output should contain an entry for the fully qualified host name for localhost.

1.0.5 Create Groups & User for Oracle

Create the following groups;

OSDBA group (dba); identifies operating system user accounts that have database administrative privileges (the SYSDBA privilege)

OSOPER group (oper); optional group, create this group if you want a separate group of operating system users to have a limited set of database administrative privileges (the SYSOPER privilege)
Oracle Inventory group (oinstall); create this group the first time you install Oracle software on the system

Create an Oracle software owner user (oracle).

In the preceding command,

g denotes the primary group of the oracle user, which is the oinstall group
G is the secondary group, which is the dba group
d denotes the home directory for the new user
p is the password for the oracle user

1.1 Directory Structure & File Naming Conventions

The Oracle needs a file system into which it can be installed. Oracle strongly recommends a disk layout methodology formally called the Optimal Flexible Architecture (OFA).

The OFA is a set of recommendations from Oracle Corporation aimed at simplifying management of complex software and databases often running under multiple versions of software. The OFA essentially imposes a standardized way of naming the different physical components of Oracle and places them on the file system according to a logical plan.

OFA was designed to achieve minimize disk contention, to provide for operating more than one database without administrative confusion, and to improve database performance. You can consider the OFA guidelines a set of best practices regarding two important issues—disk layout and naming conventions—based on extensive field experience by Oracle professionals.

1.1.1 Mount Points

Mounting refers to attach a directory with a file system. This directory is the top-level directory of a file system. Oracle recommends that all your Oracle mount points be named according to the convention /pm, where p is a string constant to distinguish itself from other mount points, and m is a two-digit number. This means you can name your mount points /u01, /u02, /u03, and so on.

Oracle recommends that you have four mount points to fully comply with the OFA guidelines. The first of these is for locating the Oracle Database server binaries, and the other three are for holding the database files. Let’s say you’re creating mount points for a database named “orcl”. In this case, the three mount points designated for the data files can be clearly named as follows: /u02/oradata/orcl, /u03/oradata/orcl, and /u04/oradata/orcl. This nomenclature makes it clear that these file systems are meant for Oracle databases and that the data for different databases is stored on separate mount points.

In Linux and UNIX systems, a home directory is the directory that a user lands in when he or she first logs in. All home directories should follow the naming convention /pm/h/u, where pm is the mount point name, h is a standard directory name, and u refers to the directory owner. For example, the /u01/app/oracle directory could be the home directory for the user named oracle.

1.1.2 Oracle Base Directory

At the root of the Oracle directory structure is the directory called Oracle base, denoted by the environment variable ORACLE_BASE. The Oracle base directory is the top directory for all Oracle software installations. Oracle recommends that you use the form /pm/h/u. Where:

pm is the mount point name (e.g. u01)
h is the name of a standard directory (e.g. app)
u is the directory’s owner’s name (e.g. oracle)

The default owner of the Oracle base directory is usually a user named oracle, and the standard directory is usually named app. Therefore, the Oracle base directory usually has the form of /pm/app/oracle (for example, /u01/app/oracle).

The following commands create the Oracle Base Directory.

Change the owner of base directory to oracle and group to oinstall.

Change permission on the base directory.

In the numeric 775 permission, 7 means rwx and 5 means r-x.

1.1.3 Oracle Home Directory

The Oracle home directory is denoted by the ORACLE_HOME environment variable, the Oracle server software executable files and other configuration files are located under this directory. For example, the $ORACLE_HOME/bin directory holds the executables for the Oracle products.

In order to comply with the OFA requirement of enabling the simultaneous running of multiple versions of Oracle software, you need to install Oracle software in a directory with the following path: /pm/h/u/product/v/type_n, where:

pm: The mount point name (e.g. u01)
h: The standard directory name (e.g. app)
u: The owner of the directory (e.g. oracle)
v: The version of the software (e.g. 10.2.0)
type: The type of installation, such as database (db), client (client) or companion (companion)
n: An optional counter, which enables you to install the same product multiple times under the same Oracle base directory

In the preceding syntax for the Oracle home, /pm/h/u/product/v_type_n, the first part, /pm/h/u, is nothing but the Oracle base directory. Thus the Oracle home directory is always located underneath the Oracle base directory, and it can also be specified as $ORACLE_BASE/product/v/type_n.

Oracle supports multiple Oracle homes, but you can’t install products from one release of Oracle into an Oracle home directory of a different release. You must install the Oracle Database 10.2 software into a new Oracle home directory. For example, you can’t install release 10.2 software into an existing Oracle9i Oracle home directory.

Oracle Home Directory (e.g. /u01/app/oracle/product/10.2.0/db_1), is automatically created by OUI.

1.1.4 Oracle Inventory Directory

The Oracle Inventory Directory, also known as OraInventory, is used to store an inventory of all the Oracle software on a server. Multiple Oracle installations can share the same Oracle Inventory Directory. You need to specify the path for this directory only the first time you install an Oracle product on a server. The usual format of this directory is as follows: $ORACLE_BASE/ora_inventory.

Again the OUI will prompt for its location and no need to create manually.

1.1.5 Flash Recovery Area Directory

Flash recovery area is a disk area (a directory or ASM disk group but not a raw file system) set apart for storing all the recovery-related files. It’s a good idea to create it on entirely different storage devices from where you have your data files. The usual format is as follows: $ORACLE_BASE/flash_recovery_area.

The following commands create the flash recovery area, change owner and permissions.

1.1.6 Administrative Files

Every Oracle database has several administrative files associated with it. Among these files are con-figuration files, core dump files, trace files, export files, and other related log files. You need to store these files under separate directories for ease of maintenance.

Oracle recommends the following directory structure for clarity: /h/admin/d/a, where:

h is the Oracle base directory (e.g. /u01/app/oracle)
admin indicates that this directory holds administration-related files
d refers to the specific database, and
a refers to the sub directories for each specific type of administrative files

Following is the list of standard administrative directories that you’ll need in most cases.

adhoc; Contains ad hoc SQL files
arch; Contains archived redo log files
adump; Contains any audit files
bdump; Contains background process trace files
create; Contains SQL scripts that you can use for creating your databases
cdump; Contains core dump files
dpdump; Contains the Data Pump Export files
pfile; Contains instance parameter files (such as init.ora)
udump; Contains SQL trace files for user processes

These directories will be automatically created by the database configuration assistant.

1.1.7 Product Files

Oracle recommends that you keep each version of the software in a separate directory distinguished by the naming convention /h/product/v, where h is the home directory, product indicates that the software versions are under this directory, and v is the version of the product. For example, I have a directory on my system called /u01/app/oracle/product/10.2.0 under which I save all the Oracle server software subdirectories during installation. If I decide to install the 10.2.1 version, I’ll do so under the directory /u01/app/oracle/product/10.2.1. You can see that this type of naming convention makes it very easy to install and manage multiple versions of the Oracle software.

1.1.8 Database Files

These include the data files that contain the table and index data and certain operational files called control files and redo log files.

Oracle recommends that all tablespaces be named with no more than eight characters, with the format tn, where t is a descriptive name for the tablespace and n is a two-digit number. For data files, the recommended notation is /pm/q/d/tn.dbf, where pm is the mount point; q is an indicator, usually “oradata”, d is the database name, t is the descriptive name for the tablespace that contains this data file, and n is a two-digit number. Thus, a typical data file under the OFA guidelines would have a name like /u02/oradata/prod/system01.dbf, which refers to a data file in the System table-space.

The recommendation for control and redo files is to follow the naming conventions /pm/q/d/controln.ctl and /pm/q/d/redon.log, respectively. In this notation, pm is the mount point; q is an indicator, such as “oradata”, denoting that the files contain Oracle-related data; d is the data-base name, and n is a two-digit number.

The following commands create the directories for database files.

1.2 Configure Oracle User’s Environment

Login as the oracle user and set the following environment variables.

Open the oracle user’s shell startup file in any text editor, on bourne shell , bash shell, or korn shell it will be .bash_profile while on c shell it will be .login and enter the following lines

Depending on the oracle user's default shell, make the following changes to the default shell start-up file: For the Bourne, Bash, or Korn shell, add the following lines to the /etc/profile file (or the file on SUSE systems) /etc/profile.local:

Run the shell script

1.3 Running Oracle Universal Installer

Oracle Universal Installer (OUI) is a Java application that performs component-based installations. OUI provides the following capabilities for addressing software management and distribution:

Automatic dependency resolution and complex logic handling
Installation from the Web
Component and suite installations
Implicit deinstallation
Support for multiple Oracle homes
NLS or globalization support
Support for distributed installations
Unattended “silent” installations that use response files

Create a staging directory, such as /staging, and copy the contents of the CD to this directory as shown: $ cp -r /mnt/cdrom /u01/staging

Here we are going to install oracle software only so uncheck the “Create Starter Database” option and click Next.

On next screen accept the suggested location for oracle inventory.

Click Install button on summary page to start the installation.

After installation login as root user and execute the following scripts to complete the installation.

Posts List - Oracle Database Administration

2011-01-02T11:56:00.053+05:00

01 - Oracle Database Server Installation

1.0 Choice of Operating System

1.0.1 Setting up the RHEL 5.0 on VMware Workstation
1.0.2 Configure RHEL Kernel for Oracle
1.0.3 Configure RHEL Shell for Oracle
1.0.4 Check Network Configuration
1.0.5 Create Groups & User for Oracle

1.1 Directory Structure & File Naming Conventions

1.1.1 Mount Points
1.1.2 Oracle Base Directory
1.1.3 Oracle Home Directory
1.1.4 Oracle Inventory Directory
1.1.5 Flash Recovery Area Directory
1.1.6 Administrative Files
1.1.7 Product Files
1.1.8 Database Files

1.2 Configure Oracle User’s Environment
1.3 Running Oracle Universal Installer

02 - Creating an Oracle Database

2.0 Planning the Database
2.1 Using the CREATE DATABASE Statement
2.2 Using the DBCA
2.3 Viewing Information about the Database

03 - Oracle Database Server Architecture

3.0 Database Server Architecture

3.1 Oracle Instance

3.1.1 System Global Area

3.1.1.1 Database Buffer Cache
3.1.1.2 Redo Log Buffer Cache
3.1.1.3 Shared Pool
3.1.1.4 Large Pool
3.1.1.5 Java Pool
3.1.1.6 Streams Pool
3.1.1.7 Fixed SGA

3.1.2 Oracle Processes

3.1.2.1 User Processes
3.1.2.2 Oracle Server Processes

3.1.2.2.1 Server Processes
3.1.2.2.2 Background Processes

3.2 Oracle Database

3.2.1 Physical Layer

3.2.1.1 Data Files
3.2.1.2 Control Files
3.2.1.3 Online Redo Log Files
3.2.1.4 Archive Log Files
3.2.1.5 Parameter Files
3.2.1.6 Trace Files
3.2.1.7 Alert Files
3.2.1.8 Password File
3.2.1.9 Backup Files
3.2.1.10 Flashback Log Files
3.2.1.11 Change Tracking Files

3.2.2 Logical Layer

3.2.2.1 Tablespaces
3.2.2.2 Segments
3.2.2.3 Extents
3.2.2.4 Oracle Blocks

04 - Managing the Oracle Instance

4.0 Oracle Database Management Framework

4.0.1 Management Interface
4.0.2 Database Listener
4.0.3 Database Instance

4.0.3.1 Database Startup

4.0.3.1.1 SHUTDOWN State
4.0.3.1.2 NOMOUNT State
4.0.3.1.3 MOUNT State
4.0.3.1.4 OPEN State
4.0.3.1.5 RESTRICT State
4.0.3.1.6 Altering Database Availability

4.0.3.2 Database Shutdown

4.0.3.2.1 NORMAL Mode
4.0.3.2.2 IMMEDIATE Mode
4.0.3.2.3 TRANSACTIONAL Mode
4.0.3.2.4 ABORT Mode

4.0.3.3 Quiescing a Database
4.0.3.4 Suspending and Resuming a Database

05 - Managing the Oracle Instance Memory

5.0 Memory Management

5.0.1 Automatic SGA Memory Management
5.0.2 Manual SGA Memory Management
5.0.3 Automatic PGA Memory Management
5.0.4 Manual PGA Memory Management
5.0.5 Database Smart Flash Cache

Posts List - Recovery Manager

2011-01-02T11:53:00.000+05:00

01- Backup and Recovery Overview

1.0 Types of Backups

1.0.1 Physical & Logical Backups

1.1 Database Recovery Process

1.1.1 Data Recovery Types

1.1.1.1 Datafile Media Recovery
1.1.1.2 Complete, Incomplete and Point-In-Time Recovery
1.1.1.3 Crash Recovery

1.1.2 When Recovery is Required from Backup?

1.1.2.1 User Errors
1.1.2.2 Media Failure
1.1.2.3 Application Errors

1.2 Instance Memory Used in Backups
1.3 Database Structures Used in Database Recovery
1.4 Oracle Recovery Manager

1.4.1 Files That RMAN Can Back Up
1.4.2 RMAN Backup Destinations
1.4.3 Full and Incremental Backups
1.4.4 Image Copies, Backup Sets and Backup Pieces

02 - RMAN Architecture

2.0 RMAN Utility
2.1 Database Control File
2.2 RMAN Server Processes
2.3 Recovery Catalog
2.4 Auxiliary Database
2.5 Flash Recovery Area
2.6 RMAN Memory Structure

Gallery

2011-01-01T12:05:00.003+05:00