|Oracle9i Database Concepts
Release 1 (9.0.1)
Part Number A88856-02
This chapter explains the procedures involved in starting and stopping an Oracle instance and database. It includes:
Every running Oracle database is associated with an Oracle instance. When a database is started on a database server (regardless of the type of computer), Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes. This combination of the SGA and the Oracle processes is called an Oracle instance. The memory and processes of an instance manage the associated database's data efficiently and serve the one or multiple users of the database.
Figure 6-1 shows an Oracle instance.
After starting an instance, Oracle associates the instance with the specified database. This is called mounting the database. The database is then ready to be opened, which makes it accessible to authorized users.
Multiple instances can execute concurrently on the same computer, each accessing its own physical database. In clustered and massively parallel systems (MPP), Oracle9i Real Application Clusters enables multiple instances to mount a single database.
Only the database administrator can start up an instance and open the database. If a database is open, the database administrator can shut down the database so that it is closed. When a database is closed, users cannot access the information that it contains.
Security for database startup and shutdown is controlled through connections to Oracle with administrator privileges. Normal users do not have control over the current status of an Oracle database.
Oracle9i Real Application Clusters Concepts for information
Database startup and shutdown are powerful administrative options and are restricted to users who connect to Oracle with administrator privileges. Depending on the operating system, one of the following conditions establishes administrator privileges for a user:
SYSOPERprivileges, and the database uses password files to authenticate database administrators.
When you connect with
SYSDBA privileges, you are placed in the schema owned by
SYS. When you connect as
SYSOPER privileges are a subset of
To start an instance, Oracle must read an initialization parameter file--a file containing a list of configuration parameters for that instance and database. Set these parameters to particular values to initialize many of the memory and process settings of an Oracle instance. Most initialization parameters belong to one of the following groups:
Among other things, the initialization parameters tell Oracle:
The following is an example of a typical parameter file:
DB_BLOCK_BUFFERS = 550 DB_NAME = ORA8PROD DB_DOMAIN = US.ACME.COM # LICENSE_MAX_USERS = 64 # CONTROL_FILES = filename1, filename2 # LOG_ARCHIVE_DEST = c:\logarch LOG_ARCHIVE_FORMAT = arch%S.ora LOG_ARCHIVE_START = TRUE LOG_BUFFER = 64512 LOG_CHECKPOINT_INTERVAL = 256000 UNDO_MANAGEMENT = TRUE UNDO_TABLESPACE = ut_one
The database administrator can adjust variable parameters to improve the performance of a database system. Exactly which parameters most affect a system is a function of numerous database characteristics and variables.
Some parameters can be changed dynamically by using the
ALTER SESSION or
ALTER SYSTEM statement while the instance is running. Unless you are using a server parameter file, changes made using the
ALTER SYSTEM statement are only in effect for the current instance. You must manually update the text initialization parameter file, for the changes to be known the next time you start up an instance. When you use a server parameter file, you can update the parameters on disk, so that changes persist across database shutdown and startup.
Oracle treats string literals defined for Globalization Support parameters in the file as if they are in the database character set.
Oracle9i Globalization Support Guide for more information about Globalization Support
The three steps to starting an Oracle database and making it available for systemwide use are:
A database administrator can perform these steps using the SQL*Plus
STARTUP command or Oracle Enterprise Manager.
When Oracle starts an instance, first it reads the initialization parameter file to determine the values of initialization parameters and then it allocates an SGA--a shared area of memory used for database information--and creates background processes. At this point, no database is associated with these memory structures and processes.
You can start an instance in restricted mode (or later alter an existing instance to be in restricted mode). This restricts connections to only those users who have been granted the
RESTRICTED SESSION system privilege.
In unusual circumstances, a previous instance might not have been shut down cleanly. For example, one of the instance's processes might not have terminated properly. In such situations, the database can return an error during normal instance startup. To resolve this problem, you must terminate all remnant Oracle processes of the previous instance before starting the new instance.
The instance mounts a database to associate the database with that instance. To mount the database, the instance finds the database control files and opens them. Control files are specified in the
CONTROL_FILES initialization parameter in the parameter file used to start the instance. Oracle then reads the control files to get the names of the database's datafiles and redo log files.
At this point, the database is still closed and is accessible only to the database administrator. The database administrator can keep the database closed while completing specific maintenance operations. However, the database is not yet available for normal operations.
If Oracle allows multiple instances to mount the same database concurrently, the database administrator can use the initialization parameter
CLUSTER_DATABASE to make the database available to multiple instances. The default value of the
CLUSTER_DATABASE parameter is
FALSE. Versions of Oracle that do not support Real Application Clusters only allow
CLUSTER_DATABASE to be
FALSE for the first instance that mounts a database, only that instance can mount the database. If
CLUSTER_DATABASE is set to
TRUE on the first instance, other instances can mount the database if their
CLUSTER_DATABASE parameters are set to
TRUE. The number of instances that can mount the database is subject to a predetermined maximum, which you can specify when creating the database.
for more information about the use of multiple instances with a single database
A standby database maintains a duplicate copy of your primary database and provides continued availability in the event of a disaster.
The standby database is constantly in recovery mode. To maintain your standby database, you must mount it in standby mode using the
ALTER DATABASE statement and apply the archived redo logs that your primary database generates.
You can open a standby database in read-only mode to use it as a temporary reporting database. You cannot open a standby database in read/write mode.
A clone database is a specialized copy of a database that can be used for tablespace point-in-time recovery. When you perform tablespace point-in-time recovery, you mount the clone database and recover the tablespaces to the desired time, then export metadata from the clone to the primary database and copy the datafiles from the recovered tablespaces.
Opening a mounted database makes it available for normal database operations. Any valid user can connect to an open database and access its information. Usually a database administrator opens the database to make it available for general use.
When you open the database, Oracle opens the online datafiles and online redo log files. If a tablespace was offline when the database was previously shut down, the tablespace and its corresponding datafiles will still be offline when you reopen the database.
If any of the datafiles or redo log files are not present when you attempt to open the database, Oracle returns an error. You must perform recovery on a backup of any damaged or missing files before you can open the database.
"Online and Offline Tablespaces" for information about opening an offline tablespace
If the database was last closed abnormally, either because the database administrator aborted its instance or because of a power failure, Oracle automatically performs crash recovery when the database is reopened.
When you open the database, the instance attempts to acquire one or more rollback segments or undo tablespaces. You determine whether to operate in manual undo management mode or automatic undo management mode at instance startup using the
UNDO_MANAGEMENT initialization parameter. The supported values are
Occasionally a database closes abnormally with one or more distributed transactions in doubt (neither committed nor rolled back). When you reopen the database and crash recovery is complete, the RECO background process automatically, immediately, and consistently resolves any in-doubt distributed transactions.
Oracle9i Database Administrator's Guide for information about recovery from distributed transaction failures
You can open any database in read-only mode to prevent its data contents from being modified by user transactions. Read-only mode restricts database access to read-only transactions, which cannot write to the datafiles or to the redo log files.
Disk writes to other files, such as control files, operating system audit trails, trace files, and alert files, can continue in read-only mode. Temporary tablespaces for sort operations are not affected by the database being open in read-only mode. However, you cannot take permanent tablespaces offline while a database is open in read-only mode. Job queues are not available in read-only mode.
Read-only mode does not restrict database recovery or operations that change the database's state without generating redo data. For example, in read-only mode:
One useful application of read-only mode occurs when standby databases function as temporary reporting databases.
Oracle9i Database Administrator's Guide for information about how to open a database in read-only mode
The three steps to shutting down a database and its associated instance are:
A database administrator can perform these steps using Oracle Enterprise Manager. Oracle automatically performs all three steps whenever an instance is shut down.
When you close a database, Oracle writes all database data and recovery data in the SGA to the datafiles and redo log files, respectively. Next, Oracle closes all online datafiles and online redo log files. (Any offline datafiles of any offline tablespaces have been closed already. If you subsequently reopen the database, any tablespace that was offline and its datafiles remain offline and closed, respectively.) At this point, the database is closed and inaccessible for normal operations. The control files remain open after a database is closed but still mounted.
In rare emergency situations, you can abort the instance of an open database to close and completely shut down the database instantaneously. This process is fast, because the operation of writing all data in the buffers of the SGA to the datafiles and redo log files is skipped. The subsequent reopening of the database requires crash recovery, which Oracle performs automatically.
Once the database is closed, Oracle dismounts the database to disassociate it from the instance. At this point, the instance remains in the memory of your computer.
After a database is dismounted, Oracle closes the control files of the database.
The final step in database shutdown is shutting down the instance. When you shut down an instance, the SGA is removed from memory and the background processes are terminated.
In unusual circumstances, shutdown of an instance might not occur cleanly; all memory structures might not be removed from memory or one of the background processes might not be terminated. When remnants of a previous instance exist, subsequent instance startup most likely will fail. In such situations, the database administrator can force the new instance to start up by first removing the remnants of the previous instance and then starting a new instance, or by issuing a
SHUTDOWN ABORT statement in Oracle Enterprise Manager.
Oracle9i Database Administrator's Guide for more detailed information about instance and database startup and shutdown