33Managing an Oracle Instance

Objectives• Setting up operating system and

password file authentication• Creating the parameter file• Starting up an instance and opening the

database• Closing a database and shutting down

the instance• Getting and setting parameter values• Managing sessions• Monitoring ALERT and trace files

Userprocess

Serverprocess

PGA

Passwordfile

ALERTfile

Parameterfile

OverviewOverview

Control files

Data files Redo logfiles

InstanceSGA Shared pool

Database Administrator Users

• Automatically createdAutomatically created

• Granted the DBA roleGranted the DBA role

The two database administrator usersThe two database administrator usersSYS and SYSTEM are:SYS and SYSTEM are:

User SYS and SYSTEM

SYS• Password: change_on_install• Owner of the database data dictionary

SYSTEM• Password: manager• Owner of additional internal tables used by Oracle tools

Remote databaseRemote databaseadministrationadministration

Local databaseLocal databaseadministrationadministration

YesYes YesYes

NoNo NNoo

Do youhave a secureconnection?

Do you want to use OS

authentication?Use OS

authentication

Use apassword file

Authentication Methods

Operating System Authentication

• Set up the user to be authenticated by the operating system.• Set REMOTE_LOGIN_PASSWORDFILE (in init.ora) to NONE.• Use the following commands to connect to a database:

CONNECT / AS SYSDBACONNECT / AS SYSOPER

Using Password File Authentication

• Create the password file using the password utility:

• Set REMOTE_LOGIN_PASSWORDFILE (init.ora) to EXCLUSIVE or SHARED• Use the following command to connect to a database:

$orapwd file=$ORACLE_HOME/dbs/orapwU15\password=admin entries=5

CONNECT INTERNAL/ADMIN

Changing the Internal Password

• Use the password utility on NT and UNIX

to delete and create the password file.or • Use the ORADIM80 utility on NT to delete

and create a new password file.

Uses of Parameters

• •init<SID>.orainit<SID>.ora

•Size the SGASize the SGA

•Set database and instance defaultsSet database and instance defaults

•Set user or process limitsSet user or process limits

•Define various physical attributes (e.g., Define various physical attributes (e.g., database block size)database block size)

•Specify control files, datafiles, redo log files, Specify control files, datafiles, redo log files, etc.etc.

The Initialization Parameter File

SVRMGR> CONNECT / AS SYSDBASVRMGR> STARTUP PFILE=/DISK1/initU15.ora

initU15.orainitU15.ora

InstanceSGA Shared pool

Librarycache

Redo logbuffer

PMONDBWRSMON LGWRCKPT ARCH

Database buffercache

Datadictionary

cache

Parameter File Example# Initialization Parameter File: initU15.oradb_name = U15control_files = (/DISK1/control01.con, /DISK2/control02.con)db_block_size = 8192db_block_buffers = 2000shared_pool_size = 30000000 log_buffer = 64K processes = 50db_files = 100log_files = 10max_dump_file_size = 10240 background_dump_dest = (/home/disk3/user15/BDUMP)user_dump_dest = (/home/disk3/user15/UDUMP)core_dump_dest = (/home/disk3/user15/CDUMP)rollback_segments = (r01,r02,r03,r04,r05,r06,r07,r08)...

OPENOPEN

MOUNTMOUNT

NOMOUNTNOMOUNT

SHUTDOWNSHUTDOWN

All files opened as All files opened as described by the described by the control file for thiscontrol file for thisinstance.instance.

Control file Control file opened for this opened for this instance.instance.

Instance Instance started.started. SHUTDOWN

SHUTDOWN

STARTUP

STARTUP

Startup and Shutdown in Stages

Startup and Shutdown (cont.)

Starting an instance includes:Starting an instance includes:

• Reading the parameter fileReading the parameter file

• Allocating the SGAAllocating the SGA

• Starting the background processesStarting the background processes

• Opening the ALERT and trace filesOpening the ALERT and trace files

STARTUP Command

STARTUP PFILE=/DISK1/initU15.ora

Start up the instance, and Start up the instance, and open the database.open the database.

STARTUP [FORCE][RESTRICT][PFILE = filename]STARTUP [FORCE][RESTRICT][PFILE = filename]

[OPEN [RECOVER][database][OPEN [RECOVER][database]

| MOUNT| MOUNT

| NOMOUNT]| NOMOUNT]

STARTUP CommandOPEN: enables users to access the databaseOPEN: enables users to access the database

MOUNT: mounts the database for certain MOUNT: mounts the database for certain activities but does not allow user access to dbactivities but does not allow user access to db

NOMOUNT: creates SGA + bg processes; no NOMOUNT: creates SGA + bg processes; no access to databaseaccess to database

FORCE: aborts the running instance before FORCE: aborts the running instance before performing a normal startupperforming a normal startup

RESTRICT: restricted accessRESTRICT: restricted access

RECOVER: begins media recoveryRECOVER: begins media recovery

ALTER DATABASE Command

•Change state of the database from NOMOUNT to Change state of the database from NOMOUNT to MOUNTMOUNT

• ALTER DATABASE database MOUNTALTER DATABASE database MOUNT

•Open the database as a read-only databaseOpen the database as a read-only database

•ALTER DATABASE database OPEN READ ALTER DATABASE database OPEN READ ONLYONLY

Opening a Database in Read-Only Mode

•Any database can be opened as a read-only Any database can be opened as a read-only databasedatabase

•A read-only database can be used to:A read-only database can be used to:

•Execute queriesExecute queries

•Execute disk sortsExecute disk sorts

•Take datafiles offline and onlineTake datafiles offline and online

•Perform recovery of offline data filesPerform recovery of offline data files

Shutdown mode:

A Abort I Immediate

T Transactional N Normal

Shutdown Options

T

X

X

O

O

I

X

X

X

O

A

X

X

X

X

Shutdown Mode

Allow new connections

Wait until current sessions end

Wait until current transactions end

Force a checkpoint and close files

N

X

O

O

O

YES

NO

Shutdown Time

ImmediateTransactionalTimeTime

4

3

1Normal Abort

2

Oracle Memory and Background Processes: Connecting to a Shared Server

Red lines trace the path between the client and the instance

Oracle Memory and Background Processes: Connecting to a Dedicated Server

Red lines trace the path between the client and the instance

Oracle Memory : PGA & SGA

The PGA has private areas for each application

The SGA is allocated when the instance starts up

Oracle Memory : SGA Components

The shared pool stores parsed SQL commands for possible reuse

The buffer cache stores data blocks read from the datafiles

Oracle Memory: SGA Components

The cursor pool is optional and provides extra memory for program cursors

The redo log buffer stores all changes to data before the changes are written to the datafiles

The large pool is optional and adds more memory for better response time

Background Processes: PMON & SMON

PMON (Process Monitor) cleans up remaining resourcesafter the server and user processes end

SMON (System Monitor) handles recovery if needed, cleans up old temporary tables, and restores space

Background Processes: DBWn & CKPT

DBWn (Database Writer) writes modified buffers to the datafiles

CKPT (Checkpoint Process) signals the DBWn process and assigns SCN

Background Processes: ARCn & LGWR

ARCn (Archiver) copies redo log files to archive log files

LGWR (Log Writer) writes redo log buffers to the redo log files

Background Processes: RECO & LMS

RECO (Recoverer Process) fixes errors if changes across distributed databases fail

LMS (Lock Manager Service) handles updates that affect data in multiple clustered databases

Dynamic Performance Views

• Maintained by the Oracle Server and continuously updated• Contain data on disk and memory structures• Contain data that is useful for performance tuning• Have public synonyms with the prefix V$

OPENOPEN

MOUNTMOUNT

NOMOUNTNOMOUNT

Data dictionaryData dictionary

Accessing Dynamic Performance Views

Dynamic Dynamic performance performance views reading views reading data from diskdata from disk

DynamicDynamicperformanceperformanceviews reading views reading from memoryfrom memorySHUTDOWN SHUTDOWN

ExampleV$PARAMETERV$PARAMETERV$SGAV$SGAV$OPTIONV$OPTIONV$PROCESSV$PROCESSV$SESSION V$SESSION V$VERSIONV$VERSIONV$INSTANCEV$INSTANCE

Control file

SGA

V$THREADV$THREADV$CONTROLFILE V$CONTROLFILE V$DATABASEV$DATABASEV$DATAFILEV$DATAFILEV$DATAFILE_HEADER V$DATAFILE_HEADER

V$LOGFILEV$LOGFILE

Displaying Current Parameter Values

• Use the Server Manager command:

SHOW PARAMETER control

• Query the dynamic performance viewV$PARAMETER:

SELECT name FROM v$parameter WHERE name LIKE ‘%control%’;

Dynamic Initialization Parameters

ALTER SESSION SET SQL_TRACE=true;

Some initialization parameters can beSome initialization parameters can bemodified while an instance is running.modified while an instance is running.

ALTER SYSTEM SET TIMED_STATISTICS=true;

ALTER SYSTEM SET SORT_AREA_SIZE=131072 DEFERRED;

Enable and Disable Restricted Session

• Use the STARTUP command to restrict access to a database:

STARTUP RESTRICT

• Use the ALTER SYSTEM command to place an instance in restricted mode:

ALTER SYSTEM ENABLE RESTRICTED SESSION;

Terminating Sessions

SELECT sid, serial# FROM v$session WHERE username=‘SCOTT’;

1. Identify which session to terminate . Identify which session to terminate with the dynamic performance view with the dynamic performance view

V$SESSION: V$SESSION:

2. Execute the ALTER SYSTEM command:. Execute the ALTER SYSTEM command:

ALTER SYSTEM KILL SESSION ‘7,15’;

Trace Files• Trace files can be written by server

and background processes.• Oracle dumps information about errors

in trace files.• The ALERT file consists of a

chronological log of messages and errors.• Server process tracing can be enabled

or disabled by:– An ALTER SESSION command– The parameter SQL_TRACE

Controlling the Trace File

Userprocess

Serverprocess

USER_DUMP_DESTUSER_DUMP_DEST BACKGROUND_DUMP_DESTBACKGROUND_DUMP_DEST

ALERT fileALERT file

InstanceSGA Shared pool

Guidelines

Check the ALERT file periodically to:• Detect internal errors (ORA-600)

and block corruption errors• Monitor database operations• View the nondefault initialization

parameter

Summary

• Creating the parameter file• Starting up and shutting down an

instance• Understanding the use of dynamic

performance views• Manage sessions• Describing the use of trace files