Working with the STARTUP Command


Working with the STARTUP Command

When the STARTUP command is given, it is inferred that, if you do not specify, you intend for the instance to be started, the database to be mounted and opened for operation, or STARUP OPEN. The order of operations is as follows:

  1. The command STARTUP is issued.

  2. The instance opens in the NOMOUNT state.

  3. The database is mounted.

  4. The database is opened for access.

The following are a few examples of variations with the STARTUP command:

  • STARTUP Starts up and opens the database inferred by whatever the SID environment variable is set to with the default SPFILE if available or PFILE.

  • STARTUP mydb1 Starts up and opens the mydb1 database with the default SPFILE if available or PFILE.

  • STARTUP OPEN mydb1 In effect, has the same outcome as the previous example, only explicitly assures the one issuing the command that he is indeed opening the database.

The following command starts up and opens the database named in the init.ora parameter file:

 STARTUP pfile=$ORACLE_HOME/dbs/initmydb1.ora 

The general form of the STARTUP command is as follows:

 Startup [FORCE|RESTRICT|MOUNT|NOMOUNT|OPEN|RECOVER] [database name] [pfile=<filename>] 

Table 4.5 provides the names and meanings for the variables in this form of the STARTUP command. Although this is not the exhaustive list, it is the most common commands and the ones that might find their way into the exam.

Table 4.5. STARTUP Parameters and Their Meanings

Parameter

Meaning

Open

Enables any user to access the database

Mount

Mounts the database and enables certain DBA tasks to be performed but does not allow users to access the database

Nomount

Creates the SGA and starts the background processes but does not allow access to the database

PFILE= Parameterfile

Enables the DBA to specify a nondefault parameter file to be used to configure the instance

Force

Startup force aborts the running instance and then issues a STARTUP NORMAL

Restrict

Enables any user with restricted session privilege to access the database

Recover

Begins media recovery when the database starts


By default, Oracle looks in %ORACLE_HOME%\database (Windows) or $ORACLE_HOME/dbs (Unix) for the initialization parameter file. It first tries to find the spfileSID.ora and then initSID.ora, if nothing is otherwise specified. If none are located, an error message is received.


Automating Startup

On Unix, you can automate database startup and shutdown by utilizing independent operating system routines that read the entries in a special file, particularly the oratab file located in the /var/opt/oracle directory and use those entries to assist the automation.

On Windows, you can start the database automatically by creating a service in your system that automatically calls the executable that starts the database on your system. These services are automatically installed with the Oracle installer and are configured automatically for you when you use the Database Configuration Assistant. You can set the database instance to start in Automatic, Manual, or Disabled mode in the Windows services.

You can find extensive information on this in the installation guide for the operating system.

Altering the Database State

If you start up the database in MOUNT or NOMOUNT mode, and, after completing your maintenance, decide that you are ready to open the database, from NOMOUNT to MOUNT, the command is as follows:

 ALTER DATABASE mydb1 MOUNT; 

To alter the database from NOMOUNT to MOUNT (or restrict or recover) to read-only, the command is as follows:

 ALTER DATABASE mydb1 OPEN READ ONLY; 

Setting Restricted Mode

The database can be placed into restricted mode in either of two ways. First, the DBA can issue the restricted mode command at database startup as follows:

 STARTUP RESTRICT 

Alternatively, the DBA can alter the system and place the database in restricted session mode:

 ALTER SYSTEM ENABLE RESTRICTED SESSION; 

ENABLE RESTRICTED SESSION does nothing to sessions currently logged on; however, future logons are only enabled for users who have been granted RESTRICTED SESSION privilege.

To allow users who don't have RESTRICTED SESSION privilege to again be able to log on and access the database, you can use the following command:

 ALTER SYSTEM DISABLE RESTRICTED SESSION; 

RESTRICTED SESSION is useful when performing structural maintenance or an import or export (although it is not necessary for an import/export operation to be in restricted session mode).

If you use the ALTER SYSTEM ENABLE RESTRICTED SESSION command, you may want to kill any currently connected user sessions before starting any administrative tasks. This will be safer for your database and anything being done in those sessions. You can find the sessions that you should kill by selecting information from the V$SESSION view.

You can run the following script first to find all active sessions and then to kill all active sessions in the database:

 SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME NOT IN ('SYS', 'SYSTEM'); ALTER SYSTEM KILL SESSION 'integer1, interger2' 

integer1 is SID, and integer2 is SERIAL#.

The Session ID and the serial number of the session are used to uniquely identify sessions. This guarantees that your ALTER SYSTEM KILL SESSION command kills the correct session, even if the original user logs off and a new session uses the same Session ID. Narrowing the sessions that are killed allows you to make sure that you are not killing yourself. Killing your own session isn't the end of the world. You can log back on because you, as SYS and SYSTEM, have restricted session privilege, but it is something that can be avoided.

When the sessions are killed, PMON (Process Monitor) rolls back the user's current transaction, releases all the held locks at the table and row level, and frees all the resources reserved by the users.

Setting Read-Only Mode

You can open your database in a read-only mode as long as you don't already have it open for read-write. When the database is in read-only mode, users can connect and execute queries, and cause disk sorts to occur if the user's default temporary tablespace is a locally managed tablespace (this means that more complex queries can be executed with order by or group by clauses). If the user's default tablespace is not a locally managed tablespace, any query run by that user that causes disk sorts will fail.

Locally managed tablespaces will be covered Chapter 10, "Managing Tablespaces and Data Files."


Read-only mode is particularly useful if you want to use the read-only database as a standby database, or as a way to offload query processing from a production online transaction processing database.

A read-only database does not restrict the DBA's ability to perform recovery operations or to make any changes to the database's current state as long as those changes do not cause redo data to be generated. When your database is in read-only mode, you can take individual data files online and offline, instead of having to offline complete tablespaces. You can then perform recovery on offline data files or tablespaces.

To maintain information in nondata related files, such as control files, operating system audit trail files, trace files, and alert log files, writes to these structures continue to occur even when the database is in read-only mode.



    Oracle 9i Fundamentals I Exam Cram 2
    Oracle 9i Fundamentals I Exam Cram 2
    ISBN: 0789732653
    EAN: 2147483647
    Year: 2004
    Pages: 244
    Authors: April Wells

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net