| Oracle® Database Administrator's Guide 10g Release 1 (10.1) Part Number B10739-01 |
|
<
br />
Contents
< table summary="layout table" cellspacing="0" cellpadding="0" width="100%">
 Previous |
 Next |
| Oracle® Database Administrator's Guide 10g Release 1 (10.1) Part Number B10739-01 |
|
Previous |
Next |
This chapter describes the procedures for starting up a nd shutting down an Oracle Database instance and contains the following topics:
Suspending and Resuming a Database
|
See Also: Oracle Real Application Clusters Administrator's Guide for additional information specific to an Orac le Real Application Clusters environment |
When you start up a database, you create an instance of that database and you determine the state of the database. Normally, you start up an instance by mounting and opening the database. Doing so makes the database available for any valid user to connect to and perform typical data access operatio ns. Other options exist, and these are also discussed in this section.
This section contains the following topics relating to starting up an instance of a database:
You can start up and administer an instance of your d atabase if several says, as described in the sections that follow.
You can start a SQL*Plus session, connect to Oracle Database with administrator privileges,
and then issue the STARTUP command. Using SQL*Plus in this way is the only method described in detail in this book.
You can also use Recovery Mana
ger (RMAN) to execute STARTUP and SHUTDOWN commands. You may prefer to do this if your are within the RMAN
environment and do not want to invoke SQL*Plus.
|
See Also: Oracle Data base Backup and Recovery Basics for information on starting up the database using RMAN |
You can use Oracle Enterprise Manager (EM) to administer your database, incl uding starting it up and shutting it down. Enterprise Manager is a separate Oracle product that combines a GUI console, agents, commo n services, and tools to provide an integrated and comprehensive systems management platform for managing Oracle products. EM enables you to perform the functions discussed in this book using a GUI interface, rather than command line operations.
The remainder of t his section describes using SQL*Plus to start up a database instance.
You must perform some preliminary steps before attempting t o start an instance of your database using SQL*Plus.
Start SQL*Plus without connecting to the database:
SQLPLUS /NOLOG
Connect to Oracle Database as SYSDBA:
CONNECT username/password AS SYSDBA
Now you are connected to the database and ready to start up an instance of your database.
|
See Also: SQL*Plus User's Guide and Reference for descriptions and syntax for the < code>CONNECT,STARTUP, and SHUTDOWN commands. These commands are SQL*Plus commands. |
You use the SQL*Plus STARTUP command to start up an Oracle Database instance. To start an instance, the database must read instance confi
guration parameters (the initialization parameters) from either a server parameter file or a traditional text initialization paramete
r file.
When you issue the STARTUP command, by default, the database reads the initialization parameters from a <
a id="sthref447" name="sthref447">server parameter file (SPFILE) in a platform-specific default location. If you hav
e not created a server parameter file, or if you wish to use a traditional text parameter file instead, you must specify the PF
ILE clause of the STARTUP command to identify the initialization parameter file.
|
Note: For UNIX, the platform-specific default location (d irectory) for the server parameter file (or text initialization parameter file) is:$ORACLE_HOME/dbs |
In the platform-specific default location, Oracle Database locates your initialization parameter file by examining filenames in the following order:
spfile<
em>$ORACLE_SID.ora
spfile.ora
initora
|
No te: Thespfile.ora file is included in this search path because in a Real Application Clusters envir
onment one server parameter file is used to store the initialization parameter settings for all instances. There is no instance-speci
fic location for storing a server parameter file.
For more information about the server parameter file for a Real Application Clu sters environment, see Oracle Real Application Cl usters Administrator's Guide. |
You can direct the database to read initialization parameters from a traditional text initializati
on parameter file, by using the PFILE clause of the STARTUP command. For example:
STARTUP PFILE = /u01/oracle/dbs/init.ora
It is not usually necessary to start an instance with a nondefault server p
arameter file. However, should such a need arise, you can use this PFILE clause to start an instance with a nondefault s
erver parameter file as follows:
Create a one-line text initialization parameter file that contai
ns only the SPFILE parameter. The value of the parameter is the nondefault server parameter file location.
For ex
ample, create a text initialization parameter file /u01/oracle/dbs/spf_init.ora that contains only the following paramet
er:
SPFILE = /u01/oracle/dbs/test_spfile.ora
|
Note: You cannot use theIFILE initialization parameter within a t
ext initialization parameter file to point to a server parameter file. In this context, you must use the SPFILE initiali
zation parameter. |
Start up the instance pointin g to this initialization parameter file.
STARTUP PFILE = /u01/oracle/dbs/spf_init.ora
The server parameter file must reside on the machine running the database server. Therefore, the preceding method also provides
a means for a client machine to start a database that uses a server parameter file. It also eliminates the need for a client machine
to maintain a client-side initialization parameter file. When the client machine reads the initialization parameter file containing t
he SPFILE parameter, it passes the value t
o the server where the specified server parameter file is read.
You can start an instance in various modes:
Start the instance without mounting a database. This does not allow access to the database and usually would be done only for database creation or the re-creation of control files.
Start the instance and mount the database, bu t leave it closed. This state allows for certain DBA activities, but does not allow general access to the database.
Start the instance, and mount and open the database. This can be done in unrestricted mode, allowing access to all users, or in restricted mode, allowing access for database administrators only.
Note:
You cannot start a database instance if you are connected to the database thro ugh a shared server process.In addition, you c
an force the instance to start, or start the instance and have complete media recovery begin immediately. The STARTUP co
mmand clauses that you specify to achieve these states are illustrated in the following section.
|
See Also: Chapter 2, " Cr eating an Oracle Database", for more information about initialization parameters, initialization parameter files, and server para meter files |
The following scenarios describe and illustrate the various states in whi
ch you can start up an instance. Some restrictions apply when combining clauses of the STARTUP command.
|
Note: It is possible to encounter problem s starting up an instance if control files, database files, or redo log files are not available. < /a>If one or more of the files specified by theC
ONTROL_FILES initialization parameter does not exist or cannot be opened when you attempt to mount a database, Oracle Database
returns a warning message and does not mount the database.
If one or more of the datafiles or redo log files
is not available or cannot be opened when attempting to open a database, the database returns a warning message and does not open the
database. |
|
See Also: SQL*Plus User's Guide and Reference for information about the restrictions that apply when combining clauses of the < code>STARTUP command |
Normal database opera tion means that an instance is started and the database is mounted and open. This mode allows any valid user to connect to the databa se and perform typical data access operations.
Start an instance, read the initialization parameters from the default server p
arameter file location, and then mount and open the database by using the STARTUP command by itself (you can, of course,
optionally specify the PFILE clause):
STARTUP
You can start an instance without mounting a da
tabase. Typically, you do so only during database creation. Use the STARTUP command with the NOMOUNT clause:
STARTUP NOMOUNT
You can start an instance and mount a database without opening it, allowing you to perform specific maintenance operations. For example, the database must be mount ed but not open during the following tasks:
Enabling and disabling redo log archiving options. For more information, please refer to Chapter 7, " Managing Archived Redo Logs".
Performing full database recovery. For more information, please refer to Oracle Database Backup and Recovery Basics
Start an instance and mount the database, but leave
it closed by using the STARTUP command wi
th the MOUNT clause:
STARTUP MOUNT
You can start an instance, and optionally mount and open a database, in restricted mode so that the instance is availa ble 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 database data
Perfor m a data load (with SQL*Loader)
Temporarily prevent typical users from using data
During certain migration and upgrade operations
Typically, all users with the CR
EATE SESSION system privilege can connect to an open database. Opening a database in restricted mode allows database access on
ly to users with both the CREATE SESSION and RESTRICTED SESSION system privilege. Only database administrat
ors should have the RESTRICTED SESSION system privilege. Further, when the instance is in restricted mode, a database ad
ministrator cannot access the instance remotely through an Oracle Net listener, but can only access the instance locally from the mac
hine that the instance is running on.
Start an instance (and, optionally, mount and open the database) in restricted mode by u
sing the STARTUP command with the RE
STRICT clause:
STARTUP RESTRICT
Later, use the ALTER SYSTEM statement t
o disable the RESTRICTED SESSION feature:
ALTER SYSTEM DISABLE RESTRICTED SESSION;
If you open the database in nonrestricted mode and later find you need to restrict access, you can use the ALTER SYSTEM statement to do so, as described in "Restricting Access to an Open Database".
|
See Also: Oracle Database SQL Reference for more information on theALTER
SYSTEM statement |
In unusual circumstances, you might experience problems when attempting to star t a database instance. You should not force a database to start unless you are faced with the following:
You cannot shut down the current instance with the SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN
TRANSACTIONAL commands.
You experience problems when starting an instance.
If on
e of these situations arises, you can usually solve the problem by starting a new instance (and optionally mounting and opening the d
atabase) using the STARTUP command with the FORCE clause:
STARTUP FORCEIf an instance is running,
STARTUP FORCEshuts it down with modeABORTbefore restarting it.
See Also:
"Shutting Down with the ABORT Clause" to understand the side effects of aborting the current instance
Starting an Instance, Mounting a Database, and Starting Compl ete Media Recovery
If you know that media recovery is required, you can start an instance, mount a database to the instance, and have the recovery process automatically start by using the
STARTUPcommand with theRECOVERclause:STARTUP OPEN RECOVERIf y ou attempt to perform recovery when no recovery is required, Oracle Database issues an error message.
Starting Remote Instances
If your local Oracle Database server is part of a distributed database, you might want to start a remo te instance and database. Procedures for starting and stopping remote instances vary widely depending on communication protocol and o perating system.
Altering Database Availability
You can alter the availability of a databas e. You may want to do this in order to restrict access for maintenance reasons or to make the database read only. The following secti ons explain how to alter the availability of a database:
Mounting a Database to an Instance
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.
To mount a database to a previously started, but not opened instance, use the SQL statement
ALTER DATABASEwith theMOUNTclause as follows:ALTER DATABASE MOUNT;
See Also:
"Starting an Instance and Mounting a Database" for a list of operations that require the database to be mounted and closed (a nd procedures to start an instance and mount a database in one step)Opening a Closed Database
You can make a mounted but closed database available for general use by opening the database. To open a mounted data base, use the
ALTER DATABASEstatement with theOPENclause:ALTER DATABASE OPEN; After executing this statement, any valid Oracle Database user with the
CREATE SESSIONsystem privilege can connect to the database.Ope ning a Database in Read-Only Mode
Openin g a database in read-only mode enables you to query an open database while eliminating any potential for online data content changes. While opening a database in read-only mode guarantees that datafile and redo log files are not written to, it does not restrict data base recovery or operations that change the state of the database without generating redo. For example, you can take datafiles offlin e or bring them online since these operations do not affect data content.
If a query against a database in read-only mode uses temporary tablespace, for example to do disk sorts, then the issuer of the query must have a locally managed tablespace assigned as the default temporary tablespace. Otherwise, the query will fail. This is explained in "Creating a Loc ally Managed Temporary Tablespace".
Ideally, you open a database in read-only mode when you alternate a standby database b etween read-only and recovery mode. Be aware that these are mutually exclusive modes.
The following statement opens a database in read-only mode:
ALTER DATABASE OPEN READ ONLY;Y ou can also open a database in read/write mode as follows:
ALTER DATABASE OPEN READ WRITE;However, read/write is the default mode.
Note :
You cannot use theRESETLOGSclause with aREAD ONLYclause.< a id="sthref510" name="sthref510">Restricting Access to an Open D atabase
To place an instance in restrict ed mode, where only users with administrative privileges can access it, use the SQL statement
ALTER SYSTEMwith theENABLE RESTRICTED SESSION clause. After placing an instance in restricted mode, you should consider killing all current user sessions before performing any administrative tasks. To lift an instance from restricted mode, use
ALTER SYSTEMwith theDISABLE RESTRICTED SESSIONclause.
See Also:
"Terminating Sessions" fo r directions for killing user sessions
"Restricting Access to an Instance at Startup " to learn some reasons for placing an instance in restricted mode
Shutting Down a D atabase
To initiate database shutdown, use the SQL*Plus
SHUTDOWNc ommand. Control is not returned to the session that initiates a database shutdown until shutdown is complete. Users who attempt connections while a shutdown is in progress receive a message like the following:ORA-01090: shutdown in progress - connection is not permitted
Note:
You cannot shut down a database if you are connected to the database thro ugh a shared server process.To shut down a database and instance, you must first connect as
SYSOPERorSYSDBA. There are sev eral modes for shutting down a database. These are discussed in the following sections:
Shutting Down with the NORMAL Cla use
To shut down a database in normal situations, use the
SHUTDOWNcommand with theNORMALclause:SHUTDOWN NORMAL < /pre>Normal database shutdown proceeds with the following conditions:
No new connections are allowe d 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 reco very procedures.
Shutting Down 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
To shut down a database immediately, u se the
SHUTDOWNcommand with theIMMEDIATEclause:SHUTDOWN IMMEDIATEImmediate 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 transact ions are rolled back. (If long uncommitted transactions exist, 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 wil l not require any instance recovery procedures.
Shutting Down with the T RANSACTIONAL Clause
When you want to perform a planned shutdown of an instance while allowing active transactions to complete first, use the
SHUTDOWNcommand wi th theTRANSACTIONALclause:SHUTDOWN TRANSACTIONALTransactional database shu tdown proceeds with the following conditions:
No new connections are allowed, nor are new transactions a llowed to be started, after the statement is issued.
After all transactions have completed, any client s till connected to the instance is disconnected.
At this point, the instance shuts down just as it would when a
SHUTDOWN IMMEDIATEstatement is submitted.The next startup of the database will not require any instance recovery procedures.
A transactional shutdown prevents clients from losing work, and at the same time, does not requ ire all users to log off.
Shutting Down with the ABORT Clause
You can shut down a database instantaneously by aborting the database instance. If possib le, perform this type of shutdown only in the following situations:
The database or one of its applications is functi oning irregularly and none of the other types of shutdown works.
You need to shut down the data base instantaneously (for example, if you know a power shutdown is going to occur in one minute).
You ex perience problems when starting a database instance.
When you must do a database shutdown by aborting transactions a nd user connections, issue the
SHUTDOWNcommand with theABORTclaus e:SHUTDOWN ABORTAn 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 issue d.
Current client SQL statements being processed by Oracle Database are immediately terminated.
Uncommitted transactions are not rolled back.
Oracle Database does not wait for use rs currently connected to the database to disconnect. The database implicitly disconnects all connected users.
The n ext startup of the database will require instance recovery procedures.
Quiescing a Database< /a>
Occasionally you might want to put a database in a state that allows only DBA transactions, queries, fetches, or P L/SQL statements. Such a state is referred to as a quiesced state, in the sense that no ongoing non-DBA transactions , queries, fetches, or PL/SQL statements are running in the system.
Note:
In this discussion of quiesce database, a DBA is defined as userSYSorSYSTEM. Other users, including those with theDBArole, are not allowed to issue theALTER SYSTEM QU IESCE DATABASEstatement or proceed after the database is quiesced.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 sch ema of a database table or adding a column to an existing table where a no-wait lock is required.
Action s whose undesirable intermediate effect can be seen by concurrent user transactions--for example, a multistep procedure for reorganiz ing 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.
Without the ability to qu iesce the database, you would need to shut down the database and reopen it in restricted mode. This is a serious restriction, especia lly for systems requiring 24 x 7 availability. Quiescing a database is much a smaller restriction, because it eliminates the disrupti on to users and the downtime associated with shutting down and restarting the database.
To be able to quiesce the database, you must have the Database Resource Manager feature activated, and it must have been activated s ince the current instance (or all instances in an Oracle Real Application Clusters environment) started up. It is through the facilit ies of the Database Resource Manager that non-DBA sessions are prevented from becoming active. Also, while this statement is in effec t, any attempt to change the current resource plan will be queued until after the system is unquiesced. Please refer to Chapter 24, " Using the Database Resource Manager" for more information about the Database Resource Manager.
< div class="sect2">Placing a Da tabase into a Quiesced State
To place a database into a quiesced state, issue the following statement:
ALTER SYSTEM QUIESCE RESTRICTED;Non-DBA active sessions will conti nue until they become inactive. An active session is one that is currently inside of a transaction, a query, a fetch, or a PL/SQL sta tement; or a session that is currently holding any shared resources (for example, enqueues). No inactive sessions are allowed to beco me active. For example, If a user issues a SQL query in an attempt to force an inactive session to become active, the query will appe ar to be hung. When the database is later unquiesced, the session is resumed, and the blocked action is processed.
Once all no n-DBA sessions become inactive, the
ALTER SYSTEM QUIESCE RESTRICTEDstatement completes, and the database is in a quiesc ed state. In an Oracle Real Application Clusters environment, this statement affects all instances, not just the one that issues the statement.The
ALTER SYSTEM QUIESCE RESTRICTEDstatement may wait a long time for active sessions to become inact ive. If you interrupt the request, or if your session terminates abnormally before all active sessions are quiesced, then Oracle Data base will automatically reverses any partial effects of the statement.For queries that are carried out by successive multiple Oracle Call Interface (OCI) fetches, the
ALTER SYSTEM QUIESCE RESTRICTEDstatement does not wait for all fetches to fin ish. It only waits for the current fetch to finish.For both dedicated and shared server connections, all non-DBA logins after this statement is issued are queued by the Database Resource Manager, and are not allowed to proceed. To the user, it appears as if the login is hung. The login will resume when the database is unquiesced.
The database remains in the quiesced state even if t he session that issued the statement exits. A DBA must log in to the database to issue the statement that specifically unquiesces the database.
Note:
You cann ot perform a cold backup when the database is in the quiesced state, because Oracle Database background processes may still perform u pdates for internal purposes even while the database is quiesced. In addition, the file headers of online datafiles continue to appea r to be accessible. They do not look the same as if a clean shutdown had been performed. However, you can still take online backups w hile the database is in a quiesced state.< div class="sect2">Restoring the System to Normal Operation
The following statement restores the database to normal operation:
ALTER SYSTEM UNQUIESCE;All non-DBA activity is allowed to pro ceed. In an Oracle Real Application Clusters environment, this statement is not required to be issued from the same session, or even the same instance, as that which quiesced the database. If the session issuing the
ALTER SYSTEM UNQUIESCEstatement term inates abnormally, then the Oracle Database server ensures that the unquiesce operation completes.Viewing the Quiesce State of an Instance
You can query the
ACTIVE_STATEcolumn of theV$INSTANCEview to see the current state of an instance. The column values has one of these values:
NORMAL: Normal unquiesed state.
QUIESCING: Being quiesced, bu t some non-DBA sessions are still active.
QUIESCED: Quiesced; no non-DBA sessions are activ e or allowed.Suspending and Res uming a Database
The
ALTER SYSTEM SUSPENDstatement halts all input and output (I/ O) to datafiles (file header and file data) and control files. The suspended state lets you back up a database without I/O interferen ce. 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 quiesci ng all active instances in a given cluster. However, if someone starts a new instance another instance is being suspended, the new in stance will not be suspended.
Use the
ALTER SYSTEM RESUMEstatement to resume normal database operations. TheSUSPEND and RESUMEcommands can be issued from different instances. For example, if instances 1, 2, and 3 are running, and you issue anALTER SYSTEM SUSPENDstatement from instance 1, then you can issue aRESUMEstate ment from instance 1, 2, or 3 with the same effect.The suspend/resume feature is useful in systems that allow you to mirror a disk or file and then split the mirror, providing an alternative backup and restore solution. If you use a system that is unable to split a mirrored disk from an existing database while writes are occurring, then you can use the suspend/resume feature to facilitate the split.
The suspend/resume feature is not a suitable substitute for normal shutdown operations, because copies of a suspen ded database can contain uncommitted updates.
Caution:
Do not use theALTER SYSTEM SUSPENDstatement as a substitute for placing a tablespace in hot backup mode. Precede any database suspend operation by anALTER TABLESPACE BEGIN BACKUPstatement.The following statements illustrate
ALTER SYSTEM SUSPEND/RESUME usage. TheV$INSTANCEview is queried to confirm database status.SQL> ALTER SYSTEM SUSPEND; System altered SQL> SELECT DATABASE_STATUS FROM V$INSTANCE; DATABASE_STATUS --------- SUSPENDED SQL> ALTER SYSTEM RES UME; System altered SQL> SELECT DATABASE_STATUS FROM V$INSTANCE; DATABASE_STATUS --------- ACTIVE
See Also:
Oracle Database Backup and Recovery Advanced User's Guide for details about backing up a database using the database suspend/resume feature
Previous
Next
Copyright © 2001, 2003 Oracle Corporation
All Rights Reserved.
Home
Book List
Contents
Index
Master Index
Feedback
