module3-database_startup.htm; updated May 21, 2013; Some figures shown in these notes are from the Oracle Database Concepts guide.

 

Module 3 – Database Startup

 

Objectives

 

These notes cover the use of environment variables and Oracle naming conventions for files.  You will:

·        learn to create and understand initialization parameter files and several of the specified parameters.

·        learn to startup and shutdown an Oracle Instance.

·        become familiar with and use diagnostic files.

 

 

Environment Variables

 

Operating System Environment Variables

 

Oracle makes use of environment variables on the server and client computers in both LINUX and Windows operating systems in order to:

·        establish standard locations for files, and

·        make it easier for you to use Oracle. 

 

On LINUX, environment variables values can be displayed by typing the command env at the operating system prompt.  It is common to have quite a few environment variables.  This example highlights those variables associated with the logged on user and with the Oracle database and software:  

 

dbock/@sobora2.isg.siue.edu=>env

_=/bin/env

SSH_CONNECTION=::ffff:24.207.183.37 25568 ::ffff:146.163.252.102 22

PATH=/bin:/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin:.:/u01/app/oracle/produ                      ct/11.2.0/dbhome_1/bin

SHELL=/bin/ksh

HOSTNAME=sobora2.isg.siue.edu

USER=dbock

ORACLE_BASE=/u01/app/oracle/

SSH_CLIENT=::ffff:24.207.183.37 25568 22

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

TERM=xterm

ORACLE_SID=DBORCL

LANG=en_US.UTF-8

SSH_TTY=/dev/pts/2

LOGNAME=dbock

MAIL=/var/spool/mail/oracle1

LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib

HOME=/u01/home/dbock

ORACLE_TERM=vt100

 

To create or set an environment variable value, the syntax is:

 

VARIABLE_NAME = value

export VARIABLE_NAME

 

An example of setting the ORACLE_SID database system identifier is shown here:

 

dbock/@sobora2.isg.siue.edu=> ORACLE_SID=USER350

dbock/@sobora2.isg.siue.edu=> export ORACLE_SID

 

This can be combined into a single command as shown here:

 

dbock/@sobora2.isg.siue.edu=> export ORACLE_SID=USER350

 

The following environment variables in a LINUX environment are used for the server.

 

HOME

Command:  HOME=/u01/student/dbock

Use:  Stores the location of the home directory for your files for your assigned LINUX account.  You can always easily change directories to your HOME by typing the command:   cd $HOME

 

Note:  The $ is used as the first character of the environment variable so that LINUX uses the value of the variable as opposed to the actual variable name.

 

LD_LIBRARY_PATH

Command:  LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib

Use:  Stores the path to the library products used most commonly by you.  Here the first entry in the path points to the library products for Oracle that are located in the directory /u01/app/oracle/product/11.2.0/dbhome_1/lib.   For multiple entries, you can separate Path entries by a colon.

 

ORACLE_BASE

Command:  ORACLE_BASE=/u01/app/oracle

Use:  Stores the base directory for the installation of Oracle products.  Useful if more than one version of Oracle is loaded on a server.  Other than that, this variable does not have much use.  We are not using it at SIUE.

 

ORACLE_HOME

Command:  ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

Use:  Enables easy changing to the home directory for Oracle products.  All directories that you will use are hierarchically below this one.  The most commonly used subdirectories are named dbs and rdbms. 

 

ORACLE_SID

Command:  ORACLE_SID=USER350  (or the name of your database)

Use:  Tells the operating system the system identifier for the database.  One of the databases on the SOBORA2 server is named DBORCL – when you create your own database, you will use you’re a database name assigned by your instructor as the ORACLE_SID system identifier for your database.

 

ORACLE_TERM

Command:  ORACLE_TERM=vt100

Use:  In LINUX, this specifies the terminal emulation type.  The vt100 is a very old type of emulation for keyboard character input.

 

PATH

Command:  PATH=/u01/app/oracle/product/11.2.0/dbhome_1/bin:/bin:/usr/bin:/usr/local/bin:.

Use:  This specifies path pointers to the most commonly used binary files.  A critical entry for using Oracle is the =/u01/app/oracle/product/11.2.0/dbhome_1/bin entry that points to the Oracle binaries.  If you upgrade to a new version of Oracle, you will need to upgrade this path entry to point to the new binaries.

 

 

Windows Variables

 

In a Windows operating system environment, environment variables are established by storing entries into the system registry.  Your concern here is primarily with the installation of Oracle tools software on a client computer. 

 

Windows and Oracle allows and recommends the creation of more than one ORACLE_HOME directory (folder) on a Windows client computer.  This is explained in more detail in the installation manuals for the various Oracle software products. 

 

Basically, you should use one folder as an Oracle Home for Oracle Enterprise Manager software and a different folder as an Oracle Home for Oracle's Internet Developer Suite – this suite of software includes Oracle's Forms, Reports, Designer, and other tools for developing internet-based applications.

 

 

Initialization Parameter Files

 

When an Oracle Instance is started, the characteristics of the Instance are established by parameters specified within the initialization parameter file that is read during startup.  In the figure shown below, the initialization parameter file is named spfiledb01.ora; however, you can select any name for the parameter file—the database here has an ORACLE_SID value of db01.   

 

 

 

There are two types of initialization parameter files:

·        Static parameter file:  This has always existed and is known as the PFILE and is commonly referred to as the init.ora file.  The actual naming convention used is to name the file initSID.ora where SID is the system identifier (database name) assigned to the database. 

·        Server (persistent) parameter file:  This is the SPFILE (also termed the server parameter file) and is commonly referred to as the spfileSID.ora. 

 

There are two types of parameters:

·        Explicit parameters.  These have entries in the parameter file.

·        Implicit parameters.  These have no entries in the parameter file and Oracle uses default values.

 

Initialization parameter files include the following:

·        Instance parameters.

·        A parameter to name the database associated with the file.

·        SGA memory allocation parameters.

·        Instructions for handling online redo log files.

·        Names and locations of control files.

·        Undo segment information.

 

 

PFILE

 

This is a plain text file.  It is common to maintain this file either by editing it with the vi editor, or by FTPing it to my client computer, modifying it with Notepad, and then FTPing it back to the SOBORA2 server.

 

The file is only read during database startup so any modifications take effect the next time the database is started up.  This is an obvious limitation since shutting down and starting up an Oracle database is not desirable in a 24/7 operating environment.

 

 

The naming convention followed is to name the file initSID.ora where SID is the system identifier.  For example, the PFILE for the departmental SOBORA2 server for the database named DBORCL is named initDBORCL.ora.

 

When Oracle software is installed, a sample init.ora file is created.  You can create one for your database by simply copying the init.ora sample file and renaming it.  The sample command shown here creates an init.ora file for a database named USER350.  Here the file was copied to the user's HOME directory and named initUSER350.ora.

 

$ cp $ORACLE_HOME/dbs/init.ora  $HOME/initUSER350.ora

 

You can also create an init.ora file by typing commands into a plain text file using an editor such as Notepad. 

 

NOTE:  For a Windows operating system, the default location for the init.ora file is C:\Oracle_Home\database.

 

This is a listing of the initDBORCL.ora file for the database named DBORCL.  We will cover these parameters in our discussion below.

 

# Copyright (c) 1991, 1997, 1998 by Oracle Corp.

 

db_name='DBORCL'

audit_file_dest='/u01/oradata/DBORCL/adump'

audit_trail ='db'  

compatible ='11.2.0' 

control_files=(/u01/oradata/DBORCL/DBORCLcontrol01.ctl, 

  /u02/oradata/DBORCL/DBORCLcontrol02.ctl,

  /u03/oradata/DBORCL/DBORCLcontrol03.ctl)

db_block_size=8192 

db_domain='siue.edu' 

db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' 

db_recovery_file_dest_size=1G 

diagnostic_dest='/u01/app/oracle' 

dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'  

log_archive_dest_1='LOCATION=/u01/oradata/DBORCL/arch' 

log_archive_format='DBORCL_%t_%s_%r.arc' 

memory_target=1G  

open_cursors=300  

processes = 150   

remote_login_passwordfile='EXCLUSIVE' 

#UNDO_Management is Auto by default

undo_tablespace='UNDOTBS1'  

# End of file

 

·        The example above shows the format for specifying values:  keyword = value.

·        Each parameter has a default value that is often operating system dependent. 

·        Generally parameters can be specified in any order.

·        Comment lines can be entered and marked with the # symbol at the beginning of the comment.

·        Enclose parameters in quotation marks to include literals.

·        Usually operating systems such as LINUX are case sensitive so remember this in specifying file names.

 

The basic initialization parameters – there are about 255 parameters –the actual number changes with each version of Oracle.  Most are optional and Oracle will use default settings for them if you do not assign values to them.  Here the most commonly specified parameters are sorted according to their category.

 

 

·        DB_NAME (mandatory) – specifies the local portion of a database name.

o   Maximum name size is 8 characters.

o   Must begin with alphanumeric character.

o   Once set it cannot be changed without recreating the database.

o   DB_NAME is recorded in the header portion of each datafile, redo log file, and control file.

 

·        DB_BLOCK_SIZE (mandatory) – specifies the size of the default Oracle block in the database.  At database creation time, the SYSTEM, TEMP, and SYSAUX tablespaces are created with this block size.  An 8KB block size is about the smallest you should use for any database although 2KB and 4KB block sizes are legal values.

 

·        DB_CACHE_SIZE and DB_nK_CACHE_SIZE (recommended, optional):

o   DB_CACHE_SIZE – specifies the size of the area the SGA allocates to hold blocks of the default size.  If the parameter is not specified, then the default is 0 (internally determined by the Oracle Database). If the parameter is specified, then the user-specified value indicates a minimum value for the memory pool.

o   DB_nK_CACHE_SIZE –  specifies up to four other non-default block sizes, and is useful when transporting a tablespace from another database with a block size other than DB_BLOCK_SIZE.  This parameter is only used when you have a tablespace(s) that is a non-standard size.

o   This parameter is NOT in the initDBORCL.ora parameter file - it was used often in the past, but is now usually allowed to default. 

 

·        DB_FILE_MULTIBLOCK_READ_COUNT = 16 (recommended) – used to minimize I/O during table scans.

o   It specifies the maximum number of blocks read in one I/O operation during a sequential scan (in this example the value is set to 16).

o   The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation. 

o   Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter.

o   This parameter is NOT in the initDBORCL.ora parameter file.

 

·        DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE (recommended) – specifies the default location for the flash recovery area.

o   The flash recovery area contains multiplexed copies of current control files and online redo logs, as well as archived redo logs, flashback logs, and RMAN backups. 

o   Specifying this parameter without also specifying the DB_RECOVERY_FILE_DEST_SIZE initialization parameter is not allowed.

 

·        CURSOR_SHARING (optional) – setting this to FORCE or SIMILAR allows similar SQL statements to share the Shared SQL area in the SGA.  The SIMILAR specification doesn't result in a deterioration in execution plans for the SQL statements.  A setting of EXACT allows SQL statements to share the SQL area only if their text matches exactly.

 

·        OPEN_CURSORS (recommended) – a cursor is a handle or name for a private SQL area—an area in memory in which a parsed statement and other information for processing the statement are kept. 

o   Each user session can open multiple cursors up to the limit set by the initialization parameter OPEN_CURSORS.  OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once.

o   You can use this parameter to prevent a session from opening an excessive number of cursors. 

 

·        AUDIT_FILE_DEST (recommended) – specifies the operating system directory into which the audit trail is written when the AUDIT_TRAIL initialization parameter is set to os, xml, or xml, extended. 

o   The audit records will be written in XML format if the AUDIT_TRAIL initialization parameter is set to xml or xml, extended.

o   It is also the location to which mandatory auditing information is written and, if so specified by the AUDIT_SYS_OPERATIONS initialization parameter, audit records for user SYS.

o   The first default value is: ORACLE_BASE/admin/ORACLE_SID/adump

o   The second default value (used if the first default value does not exist or is unusable, is: ORACLE_HOME/rdbms/audit

·        TIMED_STATISTICS (optional) – a setting of TRUE causes Oracle to collect and store information about system performance in trace files or for display in the V$SESSSTATS and V$SYSSTATS dynamic performance views.  Normally the setting is FALSE to avoid the overhead of collecting these statistics.  Leaving this on can cause unnecessary overhead for the system.

 

·        CONTROL_FILES (mandatory) – tells Oracle the location of the control files to be read during database startup and operation.  The control files are typically multiplexed (multiple copies).

 

#Control File Configuration

CONTROL_FILES = ("/u01/student/dbockstd/oradata/USER350control01.ctl", "/u02/student/dbockstd/oradata/USER350control02.ctl")

 

·        DIAGNOSTIC_DEST (recommended) – this parameter specifies where Oracle places "dump" files caused by actions such as the failure of a user or background process. 

o   This parameter is new to Oracle 11g.

o   It specifies an alternative location for the "diag" directory contents.

o   It is part of the new ADR (Automatic Diagnostic Repository) and Incident Packaging System -- these allow quick access to alert and diagnostic information.

o   The default value of $ADR_HOME by default is $ORACLE_BASE/diag. 

o   This replaced the older udump, bdump, and cdump (user dump, background dump, core dump) directories used up to version Oracle 10g.

 

diagnostic_dest='/u01/student/dbockstd/diag'

 

·        LOG_ARCHIVE_DEST and LOG_ARCHIVE_DEST_n (mandatory if running in archive mode):

o   You choose whether to archive redo logs to a single destination or multiplex the archives. 

o   If you want to archive only to a single destination, you specify that destination in the LOG_ARCHIVE_DEST initialization parameter.

o   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) or to archive only to a primary and secondary destination (using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST).

 

·        LOG_ARCHIVE_FORMAT (optional, but recommended if running in archive mode) – specifies the format used to name the system generated archive log files so they can be read by Recovery Manager to automate recovery.

 

#Archive

log_archive_dest_1='LOCATION=/u01/student/dbockstd/oradata/arch' 

log_archive_format='USER350_%t_%s_%r.arc'

 

·        SHARED_SERVERS (optional) – this parameter specifies the number of server processes to create when an instance is started. If system load decreases, then this minimum number of servers is maintained. Therefore, you should take care not to set SHARED_SERVERS too high at system startup.

 

·        DISPATCHERS (optional) – this parameter configures dispatcher processes in the shared server architecture.

 

#Shared Server Only use these parameters for a Shared Server

# installation – the parameter starts shared server if set > 0

SHARED_SERVERS=2

#Uncomment and use first DISPATCHERS parameter if the listener

#is configured for SSL security

#(listener.ora and sqlnet.ora)

#DISPATCHERS='(PROTOCOL=TCPS)(SER=MODOSE)',

#            '(PROTOCOL=TCPS)(PRE=oracle.aurora.server.SGiopServer)'

DISPATCHERS='(PROTOCOL=TCP)(SER=MODOSE)",

            '(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)',

            '(PROTOCOL=TCP)'

 

·        COMPATIBLE (optional) – allows a newer version of Oracle binaries to be installed while restricting the feature set as if an older version was installed – used to move forward with a database upgrade while remaining compatible with applications that may fail if run with new software versions.  The parameter can be increased as applications are reworked.

 

·        INSTANCE_NAME (Optional) – in a Real Application Clusters environment, multiple instances can be associated with a single database service.  Clients can override Oracle's connection load balancing by specifying a particular instance by which to connect to the database.  INSTANCE_NAME specifies the unique name of this instance.  In a single-instance database system, the instance name is usually the same as the database name.

 

#Miscellaneous

COMPATIBLE='11.2.0'

INSTANCE_NAME=USER350

 

·        DB_DOMAIN (recommended) – this parameter is used in a distributed database system.  DB_DOMAIN specifies the logical location of the database within the network structure. You should set this parameter if this database is or ever will be part of a distributed system.

 

#Distributed, Replication, and SnapShot

DB_DOMAIN='isg.siue.edu'

 

·        REMOTE_LOGIN_PASSWORDFILE (recommended) – specifies the name of the password file that stores user names and passwords for privileged (DBAs, SYS, and SYSTEM) users of the database.

 

#Security and Auditing

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

 

·        MEMORY_TARGET (recommended) – The amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. This parameter is dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the MEMORY_MAX_TARGET limit. The default value is "0".

 

  #Memory sizing

  MEMORY_TARGET=1G

 

·        PGA_AGGREGATE_TARGET (recommended, but not needed if MEMORY_TARGET is set) and SORT_AREA_SIZE (no longer recommended) – specifies the target aggregate PGA memory available to all server processes attached to the instance.

o   When managing memory manually, Oracle RDBMS tries to ensure the total PGA memory allocated for all database server processes and background processes does not exceed this target.

o   In the past, this was an often used parameter to improve sorting performance, this parameter SORT_AREA_SIZE specifies (in bytes) the maximum amount of memory Oracle will use for a sort. 

o   Now Oracle doesn’t recommend using the parameter unless the instance is configured with a shared server option.  Instead use the PGA_AGGREGATE_TARGET parameter instead (use a minimum of 10MB, the default Oracle setting is 20% of the size of the SGA).

 

·        JAVA_POOL_SIZE, LARGE_POOL_SIZE and SHARED_POOL_SIZE (optional) – these parameters size the shared pool, large pool, and Java pool.  These are automatically sized by the Automatic Shared Memory Management (ASSM) if you set the MEMORY_TARGET or SGA_TARGET initialization parameter.

o   To let Oracle manage memory, set the SGA_TARGET parameter to the total amount of memory for all SGA components.

o   Even if SGA_TARGET is set, you can also set these parameters when you want to manage the cache sizes manually.

o   The total of the parameters cannot exceed the parameter SGA_MAX_SIZE which specifies a hard upper limit for the entire SGA.

 

·        SGA_TARGET (recommended, but not needed if MEMORY_TARGET is set) – a SGA_TARGET specifies the total size of all SGA components.  If SGA_TARGET is specified, then the following memory pools are automatically sized:

o   Buffer cache (DB_CACHE_SIZE)

o   Shared pool (SHARED_POOL_SIZE)

o   Large pool (LARGE_POOL_SIZE)

o   Java pool (JAVA_POOL_SIZE)

 

#Pool sizing

SGA_TARGET=134217728

 

#Alternatively you can set these individually to establish minimum sizes for these caches, but this is not recommended

DB_CACHE_SIZE=1207959552

JAVA_POOL_SIZE=31457280

LARGE_POOL_SIZE=1048576

SHARED_POOL_SIZE=123232153   #This is the minimum for 10g

 

·        PROCESSES (recommended) – this parameter represents the total number of processes that can simultaneously connect to the database, including background and user processes. 

o   The background processes is generally 15 and you would add the # of maximum concurrent users.

o   There is little or no overhead associated with making PROCESSES too big.

 

·        JOB_QUEUE_PROCESSES (recommended, especially to update materialized views) – specifies the maximum number of processes that can be created for the execution of jobs per instance.

o   Advanced queuing uses job queues for message propagation.

o   You can create user job requests through the DBMS_JOB package. 

o   Some job queue requests are created automatically. An example is refresh support for materialized views. If you wish to have your materialized views updated automatically, you must set JOB_QUEUE_PROCESSES to a value of one or higher.

 

#Processes and Sessions

PROCESSES=150

JOB_QUEUE_PROCESSES=10

 

·        FAST_START_MTTR_TARGET (optional) – this specifies the number of seconds the database takes to perform crash recovery of a single instance.

 

#Redo Log and Recovery

FAST_START_MTTR_TARGET=300

 

·        RESOURCE_MANAGER_PLAN (optional) – this specifies the top-level resource plan to use for an instance.

o   The resource manager will load this top-level plan along with all its descendants (subplans, directives, and consumer groups).

o   If you do not specify this parameter, the resource manager is off by default. 

o   If you specify a plan name that does not exist within the data dictionary, Oracle will return an error message.

 

#Resource Manager

RESOURCE_MANAGER_PLAN=SYSTEM_PLAN

 

·        UNDO_MANAGEMENT and UNDO_TABLESPACE (recommended but actually required for most installations) – Automatic Undo Management automates the recovery of segments that handle undo information for transactions.

o   It is recommended to set the UNDO_MANAGEMENT parameter to AUTO.  This is the default value.

o   Specify the name of the UNDO tablespace with the UNDO_TABLESPACE parameter.

o   Only one UNDO tablespace can be active at a time.

 

#Automatic Undo Management

#UNDO_Management is Auto by default

UNDO_TABLESPACE=undo1

 

So, which parameters should you include in your PFILE when you create a database?  I suggest a simple init.ora file initially - you can add to it as time goes on in this course. 

 

 

SPFILE

 

The SPFILE is a binary file.  You must NOT manually modify the file and it must always reside on the server.  After the file is created, it is maintained by the Oracle server.

 

The SPFILE enables you to make changes that are termed persistent across startup and shutdown operations.  You can make dynamic changes to Oracle while the database is running and this is the main advantage of using this file. 

 

The default location is in the $ORACLE_HOME/dbs directory with a default name of spfileSID.ora.  For example, a database named USER350 would have a SPFILE with a name of spfileUSER350.ora. 

 

 

As is shown in the figure above, you can create an SPFILE from an existing PFILE by typing in the command shown while using SQL*Plus.  Note that the filenames are enclosed in single-quote marks. 

 

Recreating a PFILE

 

You can also create a PFILE from an SPFILE by exporting the contents through use of the CREATE command.   You do not have to specify file names as Oracle will use the spfile associated with the ORACLE_SID for the database to which you are connected.

 

CREATE PFILE FROM SPFILE;

 

You would then edit the PFILE and use the CREATE command to create a new SPFILE from the edited PFILE.

 

 

The STARTUP Command

 

The STARTUP command is used to startup an Oracle database.  You have learned about two different initialization parameter files.  There is a precedence to which initialization parameter file is read when an Oracle database starts up as only one of them is used.

 

These priorities are used when you simply issue the STARTUP command within SQL to startup a database.

·        Oracle knows which database to startup based on the value of ORACLE_SID.

·        Oracle uses the priorities listed below to decide which parameter file to use during startup.

 

STARTUP

 

·        First Priority:  the spfileSID.ora on the server side is used to start up the instance.

·        Second Priority:  If the spfileSID.ora is not found, the default SPFILE on the server side is used to start the instance.

·        Third Priority:  If the default SPFILE is not found, the initSID.ora on the server side will be used to start the instance.

 

A specified PFILE can override the use of the default SPFILE to start an instance.  Examples:

 

STARTUP PFILE=$ORACLE_HOME/dbs/initUSER350.ora

 

Or

 

STARTUP PFILE=$HOME/initUSER350.ora

 

·        A PFILE can optionally contain a definition to indicate use of an SPFILE. 

·        This is the only way to start the instance with an SPFILE in a non-default location.  

·        To start the database with an SPFILE not in the default location, SPFILE=<full path and filename> must be placed in the PFILE.

 

Example PFILE parameter:

 

 SPFILE=$HOME/initUSER350.ora

 

 

Modifying SPFILE Parameters

 

Earlier you read that an advantage of the SPFILE is that certain dynamic parameters can be changed without shutting down the Oracle database.  These changes are made as shown in the figure below by using the ALTER SYSTEM command.  Modifications made in this way change the contents of the SPFILE.  If you shutdown the database and startup again, the modifications you previously made will take effect because the SPFILE was modified.

 

 

The ALTER SYSTEM SET command is used to change the value of instance parameters and has a number of different options as shown here.

 

ALTER SYSTEM SET parameter_name = parameter_value

[COMMENT 'text'] [SCOPE = MEMORY|SPFILE|BOTH]

[SID= 'sid'|'*']

 

where

 

·        parameter_name:  Name of the parameter to be changed

·        parameter_value:  Value the parameter is being changed to

·        COMMENT:  A comment to be added into the SPFILE next to the parameter being altered

·        SCOPE:  Determines if change should be made in memory, SPFILE, or in both areas

·        MEMORY:  Changes the parameter value only in the currently running instance

·        SPFILE:  Changes the parameter value in the SPFILE only

·        BOTH:  Changes the parameter value in the currently running instance and the SPFILE

·        SID:  Identifies the ORACLE_SID for the SPFILE being used

·        'sid':  Specific SID to be used in altering the SPFILE

·        '*':  Uses the default SPFILE

 

Here is an example coding script within SQL*Plus that demonstrates how to display current parameter values and to alter these values. 

 

SQL> SHOW PARAMETERS timed_statistics

 

 

NAME                 TYPE        VALUE

------------------   ----------- -----

timed_statistics     boolean     FALSE

 

 

SQL> ALTER SYSTEM SET timed_statistics = FALSE

  2  COMMENT = 'temporary setting' SCOPE=BOTH

  3  SID='USER350';

 

System altered.

 

You can also use the ALTER SYSTEM RESET command to delete a parameter setting or revert to a default value for a parameter.

 

SQL> ALTER SYSTEM RESET timed_statistics

  2  SCOPE=BOTH

  3  SID='USER350';

 

System altered.

 

SQL> SHOW PARAMETERS timed_statistics

 

NAME                 TYPE        VALUE

------------------   ----------- -----

timed_statistics     boolean     FALSE

 

 

Starting Up a Database

 

Instance Stages

 

Databases can be started up in various states or stages.  The diagram shown below illustrates the stages through which a database passes during startup and shutdown.

 

 

 

NOMOUNT:  This stage is only used when first creating a database or when it is necessary to recreate a database's control files.  Startup includes the following tasks.

·        Read the spfileSID.ora or spfile.ora or initSID.ora.

·        Allocate the SGA.

·        Startup the background processes.

·        Open a log file named alert_SID.log and any trace files specified in the initialization parameter file.

·        Example startup commands for creating the Oracle database and for the database belonging to USER350 are shown here.

 

SQL> STARTUP NOMOUNT PFILE=$ORACLE_HOME/dbs/initDBORCL.ora

SQL> STARTUP NOMOUNT PFILE=$HOME/initUSER350.ora

 

MOUNT:  This stage is used for specific maintenance operations.  The database is mounted, but not open.  You can use this option if you need to:

·        Rename datafiles.

·        Enable/disable redo log archiving options.

·        Perform full database recovery.

·        When a database is mounted it

o   is associated with the instance that was started during NOMOUNT stage.

o   locates and opens the control files specified in the parameter file.

o   reads the control file to obtain the names/status of datafiles and redo log files, but it does not check to verify the existence of these files.

·        Example startup commands for maintaining the Oracle database and for the database belonging to USER350 are shown here.

 

SQL> STARTUP MOUNT PFILE=$ORACLE_HOME/dbs/initDBORCL.ora

SQL> STARTUP MOUNT PFILE=$HOME/initUSER350.ora

 

OPEN:  This stage is used for normal database operations.  Any valid user can connect to the database.  Opening the database includes opening datafiles and redo log files.  If any of these files are missing, Oracle will return an error.  If errors occurred during the previous database shutdown, the SMON background process will initiate instance recovery.  An example command to startup the database in OPEN stage is shown here.

 

SQL> STARTUP PFILE=$ORACLE_HOME/dbs/initDBORCL.ora

SQL> STARTUP PFILE=$HOME/initUSER350.ora

 

If the database initialization parameter file is in the default location at $ORACLE_HOME/dbs, then you can simply type the command STARTUP and the database associated with the current value of ORACLE_SID will startup.

 

Startup Command Options:

 

You can force a restart of a running database that aborts the current Instance and starts a new normal instance with the FORCE option.

 

SQL> STARTUP FORCE PFILE=$HOME/initUSER350.ora

 

Sometimes you will want to startup the database, but restrict connection to users with the RESTRICTED SESSION privilege so that you can perform certain maintenance activities such as exporting or importing part of the database. 

 

SQL> STARTUP RESTRICT PFILE=$HOME/initUSER350.ora

 

You may also want to begin media recovery when a database starts where your system has suffered a disk crash.

 

SQL> STARTUP RECOVER PFILE=$HOME/initUSER350.ora

 

On a LINUX server, you can automate startup/shutdown of an Oracle database by making entries in a special operating system file named oratab located in the /var/opt/oracle directory. 

 

IMPORTANT NOTE:  If an error occurs during a STARTUP command, you must issue a SHUTDOWN command prior to issuing another STARTUP command.

 

 

ALTER DATABASE Command

 

You can change the stage of a database.  This example changes the database from OPEN to READ ONLY.

 

SQL> startup mount pfile=$HOME/initUSER350.ora

ORACLE instance started.

 

Total System Global Area   25535380 bytes

Fixed Size                   279444 bytes

Variable Size              20971520 bytes

Database Buffers            4194304 bytes

Redo Buffers                  90112 bytes

Database mounted.

 

SQL> ALTER DATABASE user350 OPEN READ ONLY;

 

Database altered.

 

 

Restricted Mode

 

Earlier you learned to startup the database in a restricted mode with the RESTRICT option.   If the database is open, you can change to a restricted mode with the ALTER SYSTEM command as shown here.  The first command restricts logon to users with restricted privileges.  The second command enables all users to connect.

 

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

 

One of the tasks you may perform during restricted session is to kill current user sessions prior to performing a task such as the export of objects (tables, indexes, etc.).  The ALTER SYSTEM KILL SESSION 'integer1, integer2' command is used to do this.  The values of integer1 and integer2 are obtained from the SID and SERIAL# columns in the V$SESSION view.  The first six SID values shown below are for background processes and should be left alone!  Notice that the users SYS and USER350 are connected.  We can kill the session for user account name DBOCKSTD.

 

SQL> SELECT sid, serial#, status, username FROM v$session WHERE username='DBOCK';

 

       SID    SERIAL# STATUS   USERNAME

---------- ---------- -------- ------------------------------

260                1352 INACTIVE DBOCK

 

SQL> ALTER SYSTEM KILL SESSION '260,1352';

 

System altered.

 

Now when DBOCK attempts to select data, the following message is received.

 

SQL> select patientid, lastname, firstname, bedno

  2  from patient

  3  where bedno=1;

*

ERROR at line 1:

ORA-00028: your session has been killed

 

When a session is killed, PMON will rollback the user's current transaction and release all table and row locks held and free all resources reserved for the user.

 

 

READ ONLY Mode

 

You can open a database as read-only provided it is not already open in read-write mode.  This is useful when you have a standby database that you want to use to enable system users to execute queries while the production database is being maintained. 

 

 

 

Database Shutdown

 

The SHUTDOWN command is used to shutdown a database instance.  You must be connected as either SYSOPER or SYSDBA to shutdown a database. 

 

Shutdown Normal:  This is the default shutdown mode. 

·        No new connections are allowed.

·        The server waits for all users to disconnect before completing the shutdown.

·        Database and redo buffers are written to disk.

·        The SGA memory allocation is released and background processes terminate.

·        The database is closed and dismounted.

·        The shutdown command is:

 

Shutdown

  

Or

 

Shutdown Normal

 

Shutdown Transactional:  This prevents client computers from losing work.

·        No new connections are allowed.

·        No connected client can start a new transaction.

·        Clients are disconnected as soon as the current transaction ends.

·        Shutdown proceeds when all transactions are finished.

·        The shutdown command is:

 

Shutdown Transactional

 

Shutdown Immediate:  This can cause client computers to lose work.

·        No new connections are allowed.

·        Connected clients are disconnected and SQL statements in process are not completed.

·        Oracle rolls back active transactions.

·        Oracle closes/dismounts the database.

·        The shutdown command is:

 

Shutdown Immediate

 

Shutdown Abort:  This is used if the normal or transactional or immediate options fail.  This is the LEAST favored option because the next startup will require instance recovery and you CANNOT backup a database that has been shutdown with the ABORT option.

·        Current SQL statements are immediately terminated.

·        Users are disconnected.

·        Database and redo buffers are NOT written to disk.

·        Uncommitted transactions are NOT rolled back.

·        The Instance is terminated without closing files.

·        The database is NOT closed or dismounted.

·        Database recovery by SMON must occur on the next startup.

·        The shutdown command is:

 

Shutdown Abort

 

 

Diagnostic Files

 

These files are used to store information about database activities and are useful tools for troubleshooting and managing a database.  There are several types of diagnostic files.

 

Starting with Oracle 11g, the $ORACLE_BASE parameter value is the anchor for diagnostic and alert files.  New in Oracle 11g is the new ADR (Automatic Diagnostic Repository) and Incident Packaging System.  It is designed to allow quick access to alert and diagnostic information.

·        The new $ADR_HOME directory is located by default at $ORACLE_BASE/diag. 

·        There are directories for each instance at $ORACLE_HOME/diag/$ORACLE_SID.   

·        The new initialization parameter DIAGNOSTIC_DEST can be used to specify an alternative location for the diag directory contents.

 

In 11g, each $ORACLE_HOME/diag/$ORACLE_SID directory may contain these new directories:

·        alert - A new alert directory for the plain text and XML versions of the alert log.

·        incident - A new directory for the incident packaging software.

·        incpkg - A directory for packaging an incident into a bundle.

·        trace - A replacement for the ancient background dump (bdump) and user dump (udump) destination.  This is where the alert_SID.log is stored.

·        cdump - The old core dump directory retains it's Oracle 10g name.

 

Oracle 11g writes two alert logs. 

·        One is written as a plain text file and is named alert_SID.log (for example a database named USER350 would have an alert log named alert_USER350.log.

·        The other alert log is formatted as XML and is named log.xml. 

·        The alert log files are stored by default to:  $ORACLE_BASE/diag/rdbms/$ORACLE_SID.

·        It will be stored to the location specified by DIAGNOSTIC_DEST if you set that parameter.  I found the DBORCL alert log named alert_DBORCL.log located at /u01/app/oracle/diag/rdbms/dborcl/DBORCL/trace.   This location directory was generated based on a setting of DIAGNOSTIC_DEST = '/u01/app/oracle'.

 

You can access the alert log via standard SQL using the new V$DIAG_INFO  view:

 

column name format a22;

column value format a55;

select name, value from v$diag_info;

 

NAME                   VALUE

---------------------- -------------------------------------------------------

Diag Enabled           TRUE

ADR Base               /u01/app/oracle

ADR Home               /u01/app/oracle/diag/rdbms/dborcl/DBORCL

Diag Trace             /u01/app/oracle/diag/rdbms/dborcl/DBORCL/trace

Diag Alert             /u01/app/oracle/diag/rdbms/dborcl/DBORCL/alert

Diag Incident          /u01/app/oracle/diag/rdbms/dborcl/DBORCL/incident

Diag Cdump             /u01/app/oracle/diag/rdbms/dborcl/DBORCL/cdump

Health Monitor         /u01/app/oracle/diag/rdbms/dborcl/DBORCL/hm

Default Trace File     /u01/app/oracle/diag/rdbms/dborcl/DBORCL/trace/DBORCL_o

                       ra_25119.trc

Active Problem Count   1

Active Incident Count  2

 

11 rows selected.

 

You can enable or disable user tracing with the ALTER SESSION command as shown here.

 

 ALTER SESSION SET SQL_TRACE = TRUE

 

·        You can also set the SQL_TRACE = TRUE parameter in the initialization parameter files.

 

 

END OF NOTES