Oracle Startup and Shutdown

An Oracle instance is started and stopped using the server manager program, SVRMGR. The server manager is a command-line program. The command STARTUP NOMOUNT will initialise a database instance.

Database Creation

The CREATE DATABASE command will tell the Oracle kernel about the initial file in the SYSTEM tablespace, create redo logs, how basic control files are to be set up, alert log and will start any required service processes. The CREATE DATABASE command can be used to specify values for database structures such as.

The MAX parameters control the fixed size of the control file and may only be changed by rebuilding the control file. The control file can also set National Language System (NLS) parameters for the entire database.

Database Startup

STARTUP database_name [PFILE=init.ora]

Database startup consists of the following steps.

  1. Invoke the SVRMGR program.
  2. Connect to the instance using CONNECT INTERNAL.
  3. Use the STARTUP command to mount and open the database. The STARTUP command has the following options.

    1. NOMOUNT - used for the creation of a database only and is performed automatically by all other options. This option only starts the Oracle instance, it does not mount and open the database. The starting of the instance implies starting of all background processes plus allocation of memory segments for the SGA.
    2. MOUNT - used for certain maintenance operations and is performed automatically by using the following command options. MOUNT performs a startup of instance processes and mounts the database leaving that database accessible to only the server manager process. In this case only the control files and not the data files are opened. Only the DBA can make changes. The MOUNT option allows only the performing of functions which alter the control file. Typical operations are renaming of datafiles, redo log file and archiving changes or database recovery. Mounting of the database effectivley mounts the logical database structures to the physical database structures, ie. the tablespcaes.
    3. OPEN - opens the database for general use when opened in unrestricted mode, the default. The options below are all combined with the STARTUP OPEN command in the form of STARTUP option or STARTUP OPEN option.

      1. FORCE - emergencies only. Performs a SHUTDOWN ABORT and a STARTUP OPEN.
      2. RESTRICT - performs a database startup with the database in restricted user mode, usually DBA's only. Used for database maintenence allowing only DBAs to connect and disallowing general user population activity. Change this mode by using the ALTER DATABASE [RESTRICT] OPEN command.
      3. RECOVER - used when known that a database will need recovery.
      4. PARALLEL - mounting a database in a parallel server configuration, ie. multiple instances use the same database files.

Change the mode of the database without shutting the database down by using the ALTER DATABASE MOUNT or ALTER DATABASE OPEN commands. A database can be opened in read-only mode such that datafiles and redo log files are not written to by using the ALTER DATABASE OPEN READ ONLY command.

Database Shutdown

The following steps shut down the database instance.

  1. Start the SVRMGR program.
  2. Log onto the database as CONNECT INTERNAL or as a user with SYSDBA or SYSOPER roles.
  3. The SHUTDOWN command has the following options.

    1. NORMAL - this is the no options default. This option waits for the log-off of all users and allows no new connections. This is the slowest shutdown however provides the fastest subsequent startup since no instance recovery is performed.
    2. IMMEDIATE - forces rollback of all pending/uncommitted transactions, logs users off and shuts down the Oracle database instance. Instance recovery is required on startup.
    3. TRANSACTIONAL - allows for completion of all pending/active/uncommitted transactions and then logs off all users. Does not require recovery on instance startup.
    4. ABORT - immediately shuts down all Oracle processes, fastest and always requires instance recovery on instance re-startup. This option may cause database corruption and inhibit future database instance startup. The SHUTDOWN ABORT should always be followed by a STARTUP and a SHUTDOWN NORMAL if possible. Effectively the SHUTDOWN ABORT command does not actually dismount the database since dismounting of the database implies committing or rollback (completion) of all pending transactions plus no checkpointing or closing of files is performed. Instance recovery is automatically executed on startup of the database after a SHUTDOWN ABORT, theoretically in order to firstly dismount the database or put the database into a state from which the database can be remounted into memory and opened for transactional use.

How to do a Database SHutdown

  1. Execute SVRMGRL on the server.
  2. Connect as SYSDBA : connect system/manager;
  3. Disallow further connections : alter system enable restricted session;
  4. Check database connections : select saddr,sid,serial#,username from v$session;
  5. Selectively kill sessions as follows : alter system kill session '<SID>,<SERIAL#>'; ... for example ... alter system kill session '27,5264';
  6. disconnect;
  7. connect internal/change_on_install;
  8. shutdown immediate;
  9. startup
  10. If the shutdown is not executed DO NOT FORGET THIS ... alter system disable restricted session;

It may also be necessary to stop and start the database service on NT. Note that restarting the database service on NT may restart the database aswell in certain versions of Oracle. SHUTDOWN IMMEDIATE is a much safer way than shutdown abort. Abort can result in database corruption and data loss. Connections by internal users may be obvious by username. Web server connections will probably be connected by whatever your web server is connecting as. Thus be very careful. ALTER SYSTEM KILL SESSION will rollback incomplete transactions, release session locks and do some session resource recovery back for general use. The IMMEDIATE option will force a rollback and disconnection regardless of what any user is doing, internal or Web. This will allow for a faster shutdown, ie. ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>' IMMEDIATE;

Database Suspend and Resume

The ALTER SYSTEM SUSPEND/RESUME command suspends the database by halting all datafile I/O and control file activity. Database suspension allows database backup without I/O activity and can thus give a snapshot backup. In suspend mode all pending I/O operations are completed and all new I/O operations are queued for post-suspend execution. This method of backing up provides a method of snapshot-copying the entire database directory at operating system level, ie. a cold backup, much like placing of tablespaces into BACKUP mode where entire blocks are written to the redo logs whilst a tablespace is in backup mode. When tablespace backup mode is ended all block written redo log entries are written to the datafiles. The database suspend will allow a physical copy of the entire database including the redo logs. It is probably a very sensible idea to place all tablespace into backup mode prior to using ALTER SYSTEM SUSPEND for operating system copies. Suspending a database is not a substitute for tablespace hot backups.

The Recovery Manager

The recovery manager (RMAN) can be used to execute startup and shutdown commands.

Default Oracle Administrator Passwords

  1. system - manager
  2. sys - change_on_install
  3. internal - oracle
  4. scott - tiger

Shared and Dedicated Server Configurations

A database is either a shared or a dedicated server.

User requirements are generally very small however, user idle time causes raises this resource usage per user. Shared processes preserve resources whilst not sacrificing performance. MTS should generally be used for over 150 users per Oracle database instance.

Dedicated servers sort inside the user's process global area (PGA). Individual servers are started for each user to connect to the database and read disk blocks. The standard SQLNET files are required for set up of the dedicated server.

The SQLNET listener process immediately starts a server process and handles the user connection for processing. The user and server processes are separate processes. In a dedicated user environement only discrete user/server process sets and combined user/server processes are allowed. The combined user/server process is referred to as single-tasked.

Multi-threaded servers do sorting inside the SGA and in the UGA areas that are part of the shared pool. In TMS environements the shared pool size may be several times the size of the dedicated server shared pool. A pool of dispatcher and server processes is configured based on startup parameters.

The SQLNET listener process assigns a user connection either as a dedicated connection or as a shared connection. Shared connections imply connection pooling. So much for the Microsoft Transaction Server. If the SQLNET process determines that a user process should be made part of a shared process it is handed off to a dispatcher process which places it with one of the shared server processes. The MTS server will support shared servers, dedicated servers and combined user/servers such that they co-exist in the same instance.