Oracle DBA Ppt

Oracle Database Administration I

Lesson 1

Oracle Server Architecture

2

Overview

uSet of data to store and access information

uBased on a relational model of rows and columns stored in tables

uAlso Object-oriented (OO) structures n Abstract datatypes and methods

uContains these components: n Physical

n Memory n Process

Oracle’s Overall Role

u

Retrieve data

File I/O

u

Space Management

u

Change Management

4

Database Structure

uData file(s) uControl file(s) uRedo logs

uInit.ora (spfile.ora – 9i) uTrace

uAlert log uPassword

5

Instance Structure

uMemory structures are allocated n Collectively known as System Global Area.

n 95% defined by data block buffer cache, Shared SQL Pool, Large Pool

and Java Pool.

uOracle background processes are started.

uSingle database accessible by multiple instances – RAC (Real Application Cluster) a.k.a. parallel server

Instance Defined

uDefined by initialization parameter(s) that determine the size and composition

uCan be dynamically altered uinitSID.ora (ascii file) uspfileSID.ora (binary file)

uconfigSID.ora (usually only used to create a database – not widely used)

7

Oracle Instance

uComposed of:

n SGA (System Global Area)

n PGA (Program Global Area)

n Oracle BACKGROUND processes

8

System Global Area (SGA)

u

Shared memory region allocated by Oracle for an

Oracle database

u

Allocated when the Oracle database is started

u

The SGA should be in non-paged, non-swapped

memory

SGA Composition

uBuffer cache uShared SQL Pool uLarge pool (optional) uJava pool

uRedo log buffer uDictionary cache uOther miscellaneous items

10

PGA Composition

u

Non-shared memory area to which a process

(server or background) can write

u

Allocated by Oracle when a user connects to an

Oracle database and a session is created

11

Oracle Instance

u

Required Background Processes:

n

DBWn

LGWR

SMON

PMON

CKPT (9i)

Oracle Instance

uOptional Background Processes:

(not all inclusive)

n ARCn n CKPT (prior to 9i) n RECO n Lock (LCK0) n Job Queue (SNPn) n Queue Monitor (QMNn)

Oracle Instance

More Details

14

SGA

uBuffer Cache uRedo Log BUFFER

uShared SQL Pool

uDynamic (as of Oracle 9i)

Dynamic SGA

uDefined in Granules

n SGA < 128mb 4mb size n SGA > 128mb 16mb size

uCan be modified on the fly (9i) uEstablish an overall maximum

uInitially allocated – 4 for small, 3 for large (fixed SGA, buffer cache, Shared pool)

16

Data Buffer Cache Structure

uCan be defined in two ways:

n By block db_block_buffers n By byte db_cache_size

uWas divided into blocks the same size as the blocks in the datafiles

uAs of 9i, multiple buffer cache introduced (depends on block size of each tablespace)

uSize and number defined in the initialization Parameter File uHolds copies of data blocks read from disk

17

Data Buffer Cache Contents

uStores the most recently accessed BLOCKS OF DATA uContains PINNED buffers

n Data being accessed

uContains DIRTY buffers

n Data that has been MODIFIED, but not written to disk

uFREE buffers

uTwo lists, write and LRU

REDO Log Buffers

uStores the REDO ENTRIES (changes to the database) uDefault is four times the OS block size

uLarger reduces log I/O and uncommented redo to online redo logs

uChanged data is moved from the database buffer cache to the redo log buffer then to the redo log files

19

Shared Pool

uCan be dynamically modified (9i)

uMust be an integer multiple of the granule size

uOracle will round up if not

uManaged by LRU algorithm uContains:

n Library Cache n Data Dictionary Cache n Control structures n Reusable Runtime Memory

20

Shared Pool

Shared Pool

u

Library Cache

n

SHARED by all users for commonly used SQL

statements

n

Shared and Private SQL areas for executed

statements

n

PL/SQL Procedures and Packages

Allocation and Reuse of Memory

22

Library Cache

uShared SQL

n PARSE TREES for SQL statements n EXECUTION plans for SQL statements n Size dependent on complexity

uPrivate SQL

n Each session has one n Persistent and Runtime areas n Cursors

23

Library Cache

uPL/SQL Program Units

n Procedures, functions, Packages, anonymous blocks and database triggers

uAllocation and Reuse of Memory

n Every SQL statement submitted, Oracle automatically allocates memory

uChecks for existence

uAllocate private SQL area

uMay flush the shared pool

25

PGA

uVaries depending if dedicated or Shared servers (MTS) are used

uStack space

uSession information (PGA verses Shared Pool)

uMemory allocated to hold a session’s variables and arrays uAutomatically managed in 9i with the SQL memory

management

26

Questions

uWhat is the name of Oracle’s memory structure? uWhat are its contents?

uHow is it divided?

uIf I have 150 mb SGA, why does Oracle change the size? uCan you vary sizes of Oracle blocks?

n If so, how?

Oracle Database

u

Composed of two structures:

n Physical (Operating System view)

Physical View

Files

Physical Structure

n Redo Log Files – two or more with members n One or more Control Files

n One Parameter File n Trace and alert log files n One Password file

Datafile

uDisk space for a tablespace uOne or more per tablespace uAssociated with only one tablespace uConsists of segments:

n UNDO, Temporary, Data, index, undo, LOB and cache (for the most part)

31

Redo Log Files

uCharacteristics

n Record changes made to the data by various transactions

n Help RECOVER the data in case of a failure

n Streamline the process of how data is written to disk

32

Control Files

uRecord of the overall architecture and status of the database

uEasily moved and recovered uContain the following:

n Database NAME

n Datafiles NAMES and LOCATIONS

n Redo log file NAMES, Log sequence number and LOCATIONS

Parameter File

uAttributes of an instance are defined through an Oracle Parameter File

uDetermines the size and characteristics of the instance uStored in an ASCII file or housed with the database in a

34

Parameter File

u

The only Oracle file that is ASCII

All other files are binary and not directly

changeable by the DBA

u

Read-only when the instance starts

n

Most changes require a stop and restart of the

instance

35

Trace and Alert Logs

u

Trace files contain information about significant

events

u

Alert log records the commands and results of

major events in the life of the database

n

Monitor daily

n

Rename for historical purposes

Logical View

Structures

37

Logical View Structures

u

Tablespace(s)

Schema objects

Segments

Extents

Data blocks

Tablespace(s)

uLogically divides a database

uOne or more datafiles are explicity created for each one uLogically divided into segments that are further divided into

extents and blocks uCan be defined as read-only

uA unit of space to store a schema’s objects

Object(s)

u

Schema(s)

n

A collection of objects owned by one Oracle user

u

Object(s)

n

Tables, indexes, views, sequences, stored

40

Extents and Blocks

u

A collection of the smallest object in a database

An extent can be of a varying size

u

Extents MUST be contiguous

u

Blocks are the finest collection of space in an

Oracle database

41

Questions

uWhat are the physical files of a database system? uWhat are the logical entities of a database? uWhat is the smallest structure?

uWhich structure MUST be contiguous? uWhich structure will tell you event information?

Background Processes

Review

43

Background Processes

n

Every instance has BACKGROUND PROCESSES

BACKGROUND PROCESSES perform I/O, handle

process cleanup and monitor the ORACLE database

n

Provide support, increase performance, assist in

recovery and make the database more reliable

44

Background Processes

DBW0 (DBWn)

LGWR

PMON

SMON

CKPT

ARCn

Database Buffer

uAfter the changed data (redo entries) are written to the online redo log files, the changed data will be written to disk uThese changed data are called ‘dirty’

uDirty data are moved to the ‘write’ list

46

DBWn

u

At some time after the redo entries have been

written into the online redo logs, the database

writer (DBWn) writes the dirty buffer back to the

database

u

This is called ‘delayed write’ and is based on

initialization parameters

47

DBWn (Database Writer)

u

Can have one or more running

n

Only available on systems with

Asynch I/O

n

Can use I/O slaves to duplicate the above on

systems without it

u

Performs all WRITES to the database

n Keeps the BUFFER CACHE clean and free

When does the DBWR write?

u

The dirty list reaches a threshold length

u

A DBWR timeout

49

LGWR (Log Writer)

u

Writes the REDO log entries (located in the REDO

LOG BUFFER) to the REDO LOG files

u

Only the changed data, not the entire Oracle

block(s) is written to the redo logs

u

Issues checkpoints

50

Online Redo Log Files

u

The online redo log files hold the

‘redo entries’

u

Only the changed bytes are written to the online

redo log file

u

The entire data block is not written to these logs

Online Redo Log Files

u

There is a fixed number of online redo log file

groups

u

Defined at database creation time

u

Can add or drop groups or members (discussed

52

Online Redo Log Files

u

Redo log file groups cycle

u

If there are three redo log file groups:

n

When the first group is full, LGWR starts writing to

the second group

n

When the second group is full, LGWR writes to the

third group

n

When the third group is full, LGWR writes to the first

group

53

LGWR (Log Writer)

uWhat causes the LGWR to clear the REDO LOG BUFFER and transfer the transactions to disk?

n When a user process COMMITS a TRANSACTION

n TIME-OUT (caused every three seconds) n When the redo log buffer becomes 1/3 FULL

n When the DBWR writes the dirty buffers to disk

LGWR (Log Writer)

u

LGWR can be bypassed

n

Use the UNRECOVERABLE ‘key word’

n

This is to be used very carefully. No redo entries will

ever exist after this point

55

PMON

uCleans up after failed server processes by releasing system resources

uFREEs resource locks uRolls back aborted processes

uRestarts dispatchers and shared-server processes

56

SMON

uINSTANCE RECOVERY performed at startup uCLEANS up temp segment(s) no longer in use uCan coalesce fragmented tablespaces

CKPT

uUpdates the header record of the data files with a SCN (System Change Number)

uThis SCN is used for synchronization of all files uDoes this on behalf of the LGWR

58

Archive Mode

u

Saves the online redo log files as archived redo

log files

u

A database in archive log mode can be restored to

the last committed transaction

u

Production databases are run in archive log mode

59

ARCn

u

Copies the REDO logs to the ARCHIVE storage

when the REDO logs become filled

n

Process was optional before 9i, but now is

automatically started when archivelog mode is set.

n

Must be turned on

ARCn

u

ARCn copies a filled online redo log file to an

archived redo log file

u

ARCn gives each archived redo log file a unique

name

u

ARCn must be finished copying the online redo

61

Process Questions

u

Which process can cause the DBWR to write to

disk?

u

Which process can coalesce the free space in a

tablespace?

u

Which process ‘wakes up’ to take care of shared

servers?

How does this all fit together?

Instance and Database

u

The instance is started

u

The instance mounts the database

u

The database opens

64

User Process

uIn network access mode (web or client/server), a user process utilizes the Transparent Network Substrate (TNS) that allow the client applications (i.e., ORACLE tools) to communicate with the server processes

uInitiates a session on the PC that connects to a dedicated server process

65

Session

u

The activity of a user from the time the user

connects to the database to the time the user

disconnects from the database

u

Can execute multiple transactions

User Starts Transaction

u

A user application on the PC talks to SQL*Net

u

SQL*Net talks to the user server process

u

The server process ‘parses’ the SQL statement

67

A Transaction is Started

u

A logical unit of work

n

One or more SQL statements that must all be saved

(committed) or discarded (rolled back) as a group

u

Begins with the first executable SQL statement

68

A Transaction is Started

u

Ends with a COMMIT or ROLLBACK SQL

statement

n Explicitly with a COMMIT or ROLLBACK n Implicitly when a DDL statement is issued

u

The EXIT statement will do a commit by default

Dedicated Server

• Assume for now that we have a Dedicated Server Architecture

(Multi Threaded Server, MTS will be discussed later)

n Each user has one server process n Each server process works for only one user n Session information is stored in the PGA

70

Dedicated Server

u

The user process on the PC communicates to the

server through Oracle’s SQL*NET

u

A network listener process is created

u

A dedicated server process is created

71

Server Process

uAn OS process that works on behalf of the user uReads the data from disk into the buffer cache uCommunicates with the database for the user

n User processes do not connect directly to the database

73

SGA Activity

u

The server process reads data from the database

datafiles and stores it into the data buffer cache

u

The user SQL statement changes the data in the

data buffer cache

74

PGA (Program Global Area)

uAn area in memory that contains data and controls information for a single user

n Memory is ALLOCATED when a user process connects to the database

n Extra memory is allocated for session management if in dedicated server mode

PGA (Program Global Area)

u

What does the PGA hold?

n

Variables

Arrays

76

Physical and Logical reads

u

Physical reads get data from the datafiles

u

Logical reads get data from memory (The SGA

data buffer cache)

u

All managed by the server process(es)

77

The SQL Statement

uThe server process receives the SQL statement. Processed either by:

n If an identical SQL statement is found in the shared pool, it uses it n If not found in the shared pool, a new shared SQL area is allocated

uThere are three phases to a SQL statement:

n PARSE, EXECUTE and FETCH

PARSE

uCHECKS the statement for syntactic and semantic validity

n Data Dictionary

uEnsures the processes issuing the command has the RIGHTS to execute the command

uALLOCATES a private SQL area for the statement ( PGA ) uSCANS existing SQL area for matching statement

79

PARSE TREE

u

Method Oracle uses to find the specific data in the

database

n

Two Types

uCost Optimization uRule Optimization

nBanner uses rule

80

EXECUTE

u

Will only execute if the PARSE is successful

u

APPLIES the PARSE TREE to the data

u

PERFORM physical and logical reads

EXECUTE

u

CHANGES data where necessary

u

Performs constraint CHECKING

u

FETCH RETURNS rows of data for a SELECT

82

UNDO Segments

u

The data before it was changed is written to the

UNDO segments

n

These segments are in a tablespace

u

If the user rolls back the data, the UNDO segments

are used to restore the old data

83

Redo Log Buffer

u

The changed data is placed in the redo log buffer

u

If the data is committed, the LGWR writes the

changed data to the online redo log files

u

The commit is completed after the data is written

to the redo log

85

Buffer Cache

uConsists of buffers the size of the data blocks (only option prior to 9i) or in bytes

uTwo purposes:

n To improve performance for repeated statements usage n To allow users to make changes quickly in memory

uContains two lists:

n LRU

n WRITE

86

LRU (Least Recently Used)

uData retained in SGA is by time order

n Newest to oldest information n Longer it sits, older it becomes

n When new space is needed or a CKPT (checkpoint) has occurred:

uDIRTY buffers are written to disk and removed from memory

uOldest data in the SGA can be written over

Parts of the Buffer lists

u

FREE

n Empty section of memory ready for data

u

DIRTY

n Section of memory that CONTAINS data that has been modified, but not yet written to disk

u

PINNED

88

Redo Log Buffer

uWritten to when a user modifies data

uThe server process moves the changed data from user memory to this buffer

uContains only the redo entries

uLGWR writes the redo entries to the online redo log file

89

Shared Pool

u

Has two mandatory structures:

n

Library Cache

Dictionary Cache

u

One optional structure

n

Large Pool

Mandatory Contents

u

Data Dictionary cache (also called

row cache)

n

Dictionary row information recently accessed

u

Library cache

n

Parsed statements

Execution plans

Read Consistency

How Oracle maintains a ‘Consistent’ view of the

data for each transaction

92

UNDO Segments

u

Main purpose

n

Read consistency

Transaction rollback

uUser initiated ROLLBACK uAbnormal termination uAbnormal database termination

UNDO Segments

u

Contains

n

Block Information

File names and block ID

n

Data as it existed before a transaction

Transaction numbers

94

UNDO (Rollback) Segments

u

Stores the data before it was changed by a

transaction

u

If the user cancels a transaction (rolls back the

data), the UNDO segments are used to restore the

old data

u

Discussed in a later section

95

UNDO Segments

u

Record the transaction that occurred on the data

n

Should an error occur during a modification of data,

the record retained by the UNDO SEGMENT will

restore the data prior to the modification attempt

Read Consistency

u

Ensures data seen by a statement is consistent

with respect to a point-in-time

u

Even data changed by another transaction is not

97

Read Consistency

u

Basic principles

n

Readers can not lock data blocks

Readers can not block other readers

Readers can not block writers

Writers can not block readers

Writers can block writers

98

COMMIT

u

When a statement is executed and saved

u

Can be explicitly or implicitly commited

u

Ends the current transaction

u

Generates a System Change Number (SCN)

COMMIT

u

What happens when a COMMIT is made?

n

Modification becomes PERMANENT

n

A copy of the transaction is placed in the REDO

BUFFER

100

COMMIT

uWhen a COMMIT is made:

n User is NOTIFIED

n Resource locks are RELEASED

n DBW0 may WRITE the data back to the database file

uNote: Issuing a COMMIT does not guarantee that a transaction has been written back to the DATAFILE

101

Redo Log Files

u

When a REDO BUFFER is flushed, its information

is stored in the REDO LOG FILES

n

Hold records of all transactions that have been

applied to the database

n

With these entries, a database can be reconstructed

during a database crash

Redo Log Files

u

REDO LOG FILES

n REDO LOG FILES are circular

n When a transaction is COMMITTED (data modified), the LGWR transfers the statement from the REDO LOG BUFFER to the REDO LOG FILES

n A SCN (System Change Number) is also associated to each COMMITTED transaction that is written to a REDO LOG FILE

103

Redo Log Files

u

When a REDO LOG FILE becomes full, the LGWR

moves to the next file

n

If there are no new REDO LOG FILES, the old files

are reused

n

If these other files were not archived, all data before

this point can no longer be recovered

104

Archiving Redo Logs

u

If ARCHIVELOG is ENABLED, redo log files can be

stored

u

Archiving can be duplexed

u

Standby databases can use these same archives

Checkpoint Variables in Parameter

File

uLOG_CHECKPOINT_INTERVAL (will be obsoleted in 10i)

n Number of filled REDO LOG FILE blocks written between

consecutive CHECKPOINTS (OS-dependent)

uNOTE: Do not make this value larger than the REDO LOG FILE. If it is larger, the CKPT only occurs when switching logs

uLOG_CHECKPOINT_TIMEOUT

n Maximum amount of time before another CHECKPOINT occurs

(default 900 sec)

uLOG_CHECKPOINT_TO_ALERT

106

SWITCHING LOG FILES

u

Occur in Two ways

n

Filled REDO LOG

n

Forced by DBA with the ALTER SYSTEM SWITCH

LOGFILE command

107

Multiplexing Redo Log Files

u

Increase the security of the Oracle database

n Groups must contain identical sets of logfiles (names should be different)

n Place on different disks to help increase the REDO LOG files chances of survival should there be a computer failure

n When the REDO buffers are flushed to the REDO LOG files, the information to be stored is mirrored to a log file in both groups

Multiplexing Redo Log Files

B_LOG1

B_LOG2

A_LOG1

A_LOG2

LGWR

Group 1

Group 2

1,3,5,..

2,4,6,..

109

CONTROL FILE

u

A binary file that is necessary for the database to

run

n

Should remain read/write to the database

File is constantly updated

uNOTE: It is an excellent idea to have backup copies of this file, in case something should happen to it

110

CONTROL FILE

u

Contains

n DATABASE NAME

n TIMESTAMP of database creation n TIMESTAMP of last access

n NAMES and LOCATIONS of databases and redo log files n Current LOG SEQUENCE number

TRANSACTION

u

Logical unit of work

n

One or more SQL statements executed by a single

112

TRANSACTION

u

A TRANSACTION starts when a user executes a

SQL statement

u

The TRANSACTION ends when:

n

SQL statement is COMMITTED

A user ends the session unexpectedly

A user EXITS the database

113

TRANSACTION

u

The TRANSACTION is aborted when:

n

A ROLLBACK occurs

User TERMINATES

ABNORMAL user exit

Processor FAILURE

Media FAILURE

Parameter File

u

initSID.ora

configSID.ora

115

Data Buffer Cache

Parameters

uDB_CACHE_SIZE (9i)

uNumber of blocks in the data block buffer cache (3000 to 10,000)

uCan have five different block sizes

uDB_#K_CACHE_SIZE

uCannot exceed the MAX_SGA_SIZE

uDB_BLOCK_SIZE (bytes)

uSpecifies the size of a Oracle block

uCannot be changed except by rebuilding the database/tablespace (2k, 4k or 8k)

116

Log Buffer Parameters

u

log_buffer (bytes)

n

Default value is:

u256k

Shared Pool Parameter

u

Shared_pool_size (bytes)

n

75,000,000 is minimum for Banner

118

Archiving Parameters

u

log_archive_start (true / false)

n

Starts automatic archiving

u

log_archive_dest (directory path)

n

The disk location of the archived redo log files

u

log_archive_format (format of the name of the

archive redo log files)

119

Archiving Parameters

uStandby_archive_dest uLog_archive_dest_n

n As of 9i can be up to 10 locations

n (SERVICE=tns_service | LOCATION= local location) n MANDATORY | OPTIONAL

uLog_archive_dest_state_n

n Used for managing the 1-10 archive destinations

Archiving Parameters

Log_archive_duplex_dest

Log_archive_min_succeed_dest

V$archive_dest

V$archive

V$logs

121

Checkpoint Parameter

uCheckpoints should be avoided in an OLTP system like Banner

uLarger redo log files will reduce checkpoints uLonger checkpoint intervals, larger redo gaps

n ‘How much are you willing to lose?’

uParameters

n log_checkpoint_interval (blocks) n log_checkpoint_timeout (seconds)

u0 value will turn it off

122

CKPT Background Process

uBackground Process that helps LGWR at log switch uThis process updates the data file header files uCKPT_PROCESS = TRUE

n Used only in Oracle systems prior to 9i

n In 9i, remove this parameter from any init.ora. It will cause an error.

n Automatically started in 9i

Other Banner Parameters

u

dml_locks (1500 to 2000)

u

processes (number of user plus oracle

background processes)

u

open_cursors = 1024 (2048 is better)

More to life than a DEDICATED server

Server Configurations

Three types

DEDICATED server

MULTITHREAD server

n

COMBINED user/server process

DEDICATED server

uTwo tasks

n USER process and SERVER process

n If the two processes are on the same machine, the program interface is handled by the LOCAL MACHINE'S I/O

n If the two processes are on different machines, the program interface is handled by a COMMUNICATION MECHANISM (SQL*Net/Net80)

127

DEDICATED server

128

MULTITHREADED server

u

Allows many user processes to share a server

process

n

This allows the system resources to be freed, but

can cause decreased response time for heavy users

MULTITHREADED Server

Dispatcher Processes

Request

queue ResponseQueues

System Global Area

Application Code Oracle Server Code Client Workstation Database Server

130

DISPATCHER

u

The USER process connects to a DISPATCHER

n The DISPATCHER routes the USER request to the Request Queue

n The SERVER process checks the queue for new requests (FIFO)

n The SERVER process then places the response on the calling dispatcher's response queue, and then back to the application

131

Parameter Settings - MTS

Mts_servers = n

Mts_max_servers = n

Mts_dispatchers =

“(protocol=ipc/tcp) (dispatchers=N)”

Mts_max_dispatchers = n

Summary

n Data, redo log, control, trace, alert log and parameter files

uOracle Instance

n The set of background processes which access data, log, and control files.

n An Oracle Instance is defined by one or more Parameter Files that is read at startup time

133

Oracle INSTANCE

u

Processes that control

n

DATAFILES

REDO LOG FILES

CONTROL FILES

134

Physical structure

u

One or more DATA files that contain data

u

Two or more REDO files

u

One or more CONTROL files

u

One or more Parameter Files

DATAFILE

u

How is a DATAFILE used?

n

Data is read into stored memory cache

Can manipulate, view, or change the data

After you complete the transaction, the data is

136

REDO log files

u

When do they help?

n

When a system failure has prevented Oracle from

updating DATA

n

Allows the transaction to be recalled

n

Prevents existing data from becoming corrupted

through an incomplete transaction by restoring the

original information

137

CONTROL FILES

u

Why does Oracle need them?

n

When an Oracle database is started, the CONTROL

file that is listed first in the parameter file is read

n

Identifies the database

n

Opens the redo logs to allow the database to

function

SETTING BUFFERS

u

Parameter File

u

Data buffers

n

DB_CACHE_SIZE

uCannot be used with db_block_buffers

u

Redo log

139

PGA (Program Global Area)

u

MULTITHREADED PGA

n

The SGA is responsible for allocating the memory to

the user sessions

u

DEDICATED PGA

n

PGA allocates the memory to the user sessions as

needed

BACKGROUND PROCESSES

uSMON (System Monitor) uS### (Server)

u PMON (Process Monitor) u ARCn (Archiver) u RECO (Recover) u Dnnn (Dispatcher) u LCKn (Lock)

RECO

u

Resolves in-doubt transactions in distributed

142

Dnnn

u

Used for MULTITHREADED server

n

An optional feature

n

The Dispatcher is used to TRANSLATE

communications from one protocol to another

143

LCKn

u

Used with the PARALLEL (RAC – 9i) server option

n

It instantly LOCKS data to prevent data corruption

caused by simultaneous access of data by different

processes

CKPT

u

Primary goal

n Make sure all modified data buffers get written to files n FORCES DBWR to clean the BUFFER CACHE n CAUSES the switching of REDO LOG FILES n FLUSHES the REDO LOG BUFFER to disk

145

CKPT

u

PROBLEMS

n

The CKPT processes will put more overhead on the

server

n

May cause a considerable slowdown

u

Parameter File

n

In 9i, the CKPT process is predefined and started.

In earlier version, to activate CKPT:

uCHECKPOINT_PROCESS=TRUE

Oracle Database Administration I

Lesson 2

Starting and Stopping

an Oracle Instance

Starting and Stopping

an Oracle Database

u

Agenda

n

Application(s) used in starting a database

n

Required environment settings to startup a

database

n

Various levels of a database startup and

148

Starting an Oracle Database

u

After the installation of Oracle, the DBA

configures, creates and starts a database

for user access

u

Before you can create a database, you must

start an application to begin

149

Packages to Use for Startup

uSQLPLUS

uUse a database management tool (Enterprise Manager, TOAD)

uScripts

uOracle Universal Installer

uRMAN (Recovery Manager – not discussed in this course)

SQLPLUS

uThe replacement for Server Manager (svrmgrl) starting with release 8.1.5

uWith Password file

nSqlplus /nolog nThen issue

connect <username>/<syspassword> as sysdba (9i)

nConnect internal as sysdba (8i – to be desupported after

9i)

151

Database Management tools

u

Oracle Enterprise Manager (OEM)

n Has become more sophisticated n Comes with the Enterprise Edition

n Use the Instance Manager as INTERNAL or AS SYSDBA

u

TOAD

n Downloadable version - minimal

u

Many other types

152

Starting an Oracle Database

u

Scripts

n

Unix: csh, sh, perl

VMS: dcl, perl

NT: perl

uAll require several environment variables to be set prior to running any Oracle application

Environment Variable - UNIX

u$ORACLE_HOME

uSet to the directory that Oracle is installed under

u$ORACLE_SID

uSet to the name of the database the DBA wants to startup/shutdown

u$LD_LIBRARY_PATH

uSet to $ORACLE_HOME/lib

u(optional) $PATH

154

Setting Environment - UNIX

uExecuting the script oraenv, typically located in

/usr/local/bin, automatically sets all environment variables uOr manually at the command prompt

n Examples

nORACLE_HOME = /u01/app/oracle/9.0.1/bin nORACLE_SID=TEST

uNOTE: Oracle account should set variables in

$ORACLE_HOME/bin with no application-specific definitions

155

Environment Variable - VMS

u

ORA_DB

uDirectory location for database parameter, configuration and dump files

u

ORA_SID

uSet to the name of the database the DBA wants to startup/shutdown

u

ORA_ROOTDIR

uOracle’s home directory

Setting Environment -VMS

u

Running the command file ORAUSER_<SID>.COM,

located under ORA_ROOT:[db_<SID>],

automatically sets all logicals and symbols

correctly

157

Environment Variable - NT

uRegistry values are defined and a service entry is created when the database is created

uRegistry values are automatically set when you use OEM or during installation

n Run regedit

uHKEY_LOCAL_MACHINE/SOFTWARE/ORACLE

158

Setting Environment - NT

u

ORADIM – can be used to create it

n Oradim –NEW –SID sid –SRVC service

uPlease refer to the documentation for full syntax

u

CMD – at the DOS prompt setting

n SET ORACLE_HOME=d:\oracle\ora91\bin n SET ORACLE_SID=TEST

Local Database Startup

uOnce the proper environment variables are set, the DBA then needs to invoke the local database management utility

sqlplus /nolog

connect <username>/<password> as sysdba Or

connect “/ as sysdba”

n NT has other options:

uMANUAL or AUTOMATIC service

160

Connection Example

C:\Documents and Settings\vdevore>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Apr 4 21:07:52 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys/systest as sysdba Connected to an idle instance. SQL>

161

Startup by OS

u

User must have OS PRIVILEGES:

n

Unix: group DBA(defined in /etc/groups)

n

VMS: Rights identifier ORA_<SID>_DBA (defined in

sys$system:authorize)

n

NT: be in the group ORA_DBA

Startup using ORAPWD

u

As of 8i, Oracle introduced the password file

u

Grant sysdba, sysoper for remote or local

connection

163

Why use AS SYSDBA/INTERNAL?

u

When the database is shut down, there is no way

to verify username and password

u

It is necessary to find a way for an individual to log

onto the database

u

Once connected, the user has sys privileges

164

What Options are there?

u

Once the DBA has connected to the database,

he/she can proceed to:

uStartup the database in several modes uShutdown the database in several modes uOr create a database

Let’s ‘Start a database’

uThere are several ways to start the database's ‘instance’ uWe will look at:

n Nomount n Mount n Open n Restrict n Force

166

Start the Instance

uStarting the instance involves:

n Reading a parameter file (text - init.ora, binary – spfile.ora) n Allocating SGA (system global area) in memory n Creating background processes

n Reading the control files for system information (for existing databases only)

n Open files for read and/or read-write

167

NOMOUNT

u

Typically used during database creation

u

Starts the background processes and allocates

memory

u

Also used in recovery

Nomount - example

C:\Documents and Settings\vdevore>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Apr 4 21:07:52 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys/systest as sysdba Connected to an idle instance. SQL> startup nomount ORACLE instance started.

Total System Global Area 135338868 bytes Fixed Size 453492 bytes

169

MOUNT

uMounting the database involves:

n Associating the database with the started instance

n Opening the control file, reading it, obtaining the names of the redo log and data files

n Verify checksum value (if enabled) and/or SCN synchronization

170

Mount - example

C:\Documents and Settings\vdevore>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Apr 4 21:07:52 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys/systest as sysdba Connected to an idle instance. SQL> startup mount ORACLE instance started.

Total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database mounted.

SQL>

OPEN

u

Opening the database involves:

n Opening the data file(s) and online redo logs for read-write n Allowing regular users to connect to the database n Attempts to acquire one or more UNDO segments

n Performs automatic crash recovery if database was shutdown abnormally

172

OPEN -Example

SQL> startup open ORACLE instance started.

Total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database mounted.

Database opened. SQL>

173

STARTUP – No Arguments

uOracle assumes that STARTUP with no arguments implies a STARTUP OPEN

uIt is also assumed that the current SID environment variable is what will be used.

n DBAs must ALWAYS be sure that the proper environment is set

uPFILE or SPFILE must be defined and accessible or it will fail

uDBA must have SYSDBA or OSDBA

Starting an instance

u

NOMOUNT, MOUNT, and OPEN are different

stages of an Oracle database startup

n

When a STARTUP OPEN is issued, the database first

startups in a NOMOUNT state, then proceeds to a

MOUNT state, and finally proceeds to an OPEN state

175

RESTRICT

uSTARTUP RESTRICT

uOpens the database, but only allows users with RESTRICTED SESSION privilege to access the database uWhy start a database in a restricted mode?

uMaintenance (database upgrades)

uExporting and importing data

176

RESTRICT - Example

SQL> startup restrict ORACLE instance started.

Total System Global Area 4199600 bytes Fixed Size 42028 bytes Variable Size 4026500 bytes Database Buffers 122880 bytes Redo Buffers 8192 bytes Database mounted.

Database opened. SQL>

FORCE

uSTARTUP FORCE is used for troubleshooting a failed or failure to open database

uIf the database is closed, forces the database open uIf the database is open, it will abort the database and then

open it

Example:

To shutdown the current instance, restart it without mounting or opening and allow only DBA access:

178

Other Startup Options

uParallel/Shared (RAC – 9i)

(Shared is a synonym for Parallel)

n Allows multiple instances to access the database n Cannot be used with EXCLUSIVE or NOMOUNT

n Invalid if initialization parameter SINGLE_PROCESS is set to TRUE

uRetry

n Automatically restarts every five seconds if the instance is busy being recovered

n Will not retry for any other reason - parallel mode only

179

Other Startup Options

uSTARTUP RECOVER

n Specifies that media recovery be performed n Has same effect as issuing RECOVER DATABASE n The recovery processes

uEnable log archiving for media recovery

uPerform complete database recovery

uPerform partial database recovery

Other Startup Commands

uPfile = parameterfilename

n A site-specific parameter file to initialize an instance at startup. n If not specified, Oracle searches for it in:

uUNIX -$ORACLE_HOME/dbs/init<SID>.ora

uVMS - ORA_ROOT:[db_<SID>]<NODE>_<SID>_init.ora

181

Changing the startup state of a

database

u

The DBA can change the current state of a

database using the ALTER DATABASE SQL

command

u

The syntax is on the next slide

182

Changing the startup state of a

database

ALTER DATABASE [MOUNT | OPEN]

SQL> startup nomount ORACLE instance started.

Total System Global Area 4199600 bytes Fixed Size 42028 bytes Variable Size 4026500 bytes Database Buffers 122880 bytes Redo Buffers 8192 bytes SQL> alter database mount;

Statement processed. SQL> alter database open; Statement processed. SQL>

Changing the startup state of a

database

uThe ALTER DATABASE SQL command only moves the startup state of the database to a higher level

n For example, the ALTER DATABASE SQL command cannot change the startup state from OPEN to MOUNT

uIn this case, the DBA must first shutdown the database and then issue a STARTUP MOUNT command

184

Database shutdown

u

A database shutdown stops an instance and

makes the database unavailable to users

u

Syntax

SHUTDOWN [ABORT|IMMEDIATE|NORMAL|TRANSACTIONAL]

185

Commonly Used Shutdown Options

uSHUTDOWN NORMAL

n Flushes all buffered writes to the disk, waits for all sessions to disconnect and then terminates the instance

uSHUTDOWN IMMEDIATE

n Flushes all buffered writes to the disk, kills all user connections, performs a rollback on all uncommitted transactions, then terminates the instance

Shutdown Immediate - example

SQL*Plus: Release 9.2.0.1.0 - Production on Sun Apr 6 13:16:30 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys/systest as sysdba Connected.

SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL>

187

Database shutdown

u

SHUTDOWN ABORT

n

Should only be used in emergency situations, such

as when the DBA detects a media failure, power

failure, or security breach to the database

188

Shutdown Abort

uSHUTDOWN ABORT

n Immediately terminates the Oracle instance n All buffered writes are NOT flushed to disk

n Does not perform a rollback on uncommitted transactions n An instance recovery is required on the next database startup

(performed automatically)

Database shudown

uSHUTDOWN TRANSACTIONAL

(new with Oracle 8i)

n All current sessions in a transaction are allowed to complete n No new sessions are allowed to start

n Any connected sessions are disconnected either by issuing a new transaction or once all transactions are committed or aborted

190

Database shutdown

u

SHUTDOWN NORMAL, SHUTDOWN

TRANSACTIONAL and SHUTDOWN IMMEDIATE

are either the usual or safest shutdown commands

n

The following steps occur during a shutdown (See

next slide)

191

Database shutdown

u(SHUTDOWN IMMEDIATE) USER CONNECTIONS ARE

TERMINATED

uAll database data in the SGA is written back to the database files

uAll redo log buffer data is written back to the redo log files uDatabase is dismounted

uThe control files are closed

uThe SGA is removed from memory and the background processes are closed

193

Views

u

V$database

n

Contains database information from the control file

u

V$instance

n

Gives the state of the current instance

194

Questions?

uWhich OEM manager can use to start up a database? uHow must you connect to startup a database? uWhich shutdown command satisfies these conditions?

uThe database shuts down when the last user logs off

uForce the database to close with users connected and disconnects their transactions midstream

uForce the database to close with users connected, but let transactions complete

Summary

uStart an Oracle DATABASE

n Log onto the host O/S, set up the proper environment and then run

sqlplus /nolog

uA database startup proceeds in three stages:

n Starting the instance (NOMOUNT) n Opening the controlfile(s) (MOUNT) n Opening the datafiles and redo logfiles (OPEN)

u Ways to shutdown a database:

Oracle Database Administration I

Lesson 3

Creating and Managing Tablespaces

197

Creating and Managing Tablespaces

u

Agenda

n

Describe Oracle tablespaces

Create and modify tablespaces

n

Manage tablespaces by querying the appropriate

data dictionary views

Creating and Managing Tablespaces

uAll schema objects within the database (tables, views, packages, functions) are stored in segments and collectively stored in tablespaces

n A tablespace is a logical unit of storage that is defined within Oracle

n Each tablespace corresponds to one or more operating-system level datafiles

199

Creating and Managing

Tablespaces

200

Creating and Managing Tablespaces

uA tablespace can be in several states while the database is open

n Online or Offline n Read-only or read-write

uA tablespace can be altered back and forth between these options as needed

n Conditions must be tested before each alter (no current transactions may be active)

Creating and Managing Tablespaces

uEach database must be created with at least one tablespace (SYSTEM).

n This holds all data dictionary objects and built-in PL/SQL program units (procedures, functions, packages and triggers)

uEach tablespace can contain one or more schemas and each schema can have one or more objects

202

Creating and Managing Tablespaces

uWhen the Oracle Universal Installer (OUI) or OEM creates a database, it creates several tablespaces by default. For example:

n SYSTEM - contains system definitions (Required) n TEMP - used for sorting

n RBS/UNDO - used for rollback/UNDO segments n TOOLS - used for storing PL/SQL program units n USERS - used as default tablespace for users

203

Creating and Managing Tablespaces

uPrior to creating a tablespace, the DBA will need to consider

(covered extensively in Lesson 5):

n Tablespace storage

uThis includes the type of data file to create n Segment storage

n Disk contention

n Free space and segment fragmentation

Tablespace Storage

uTablespace Storage considerations

n System limitation on datafile size

n Database limitation when database is created (maxdatafiles)

n Data file management

uAutoextend feature on an existing datafile

205

Data File Types and Behavior

uPermanent uTemporary

uOracle Managed Files (OMF) uExtent management n Dictionary n Locally uUniform extent uautoallocated 206

Tablespace Storage

uEach tablespace contains object segment(s)

uEach segment is subdivided into logical units of storage called EXTENTS

uWhen a user creates an object (such as tables, views, sequences) within the database, Oracle allocates an extent(s) within a tablespace in which to store the object’s segment(s)

208

Segment Storage

uSegments can span across datafiles, but extents cannot uEach segment can be composed of one or more extents uEach extent is further subdivided into units of storage

called DATABASE BLOCKS

uExtents must be made up of contiguous DATABASE BLOCKS

209

Database Blocks

u

Not identical to operating system-level file blocks

u

A logical database block may span multiple

system level blocks

u

Should be created to either fit into an OS-level file

block or be the same

Database Blocks

uThe size of each database block is determined by the parameter DB_BLOCK_SIZE in the initialization parameter file

uThis size is fixed during database creation time. Cannot be changed unless the database is re-created

211

Tablespace Storage

u

Logical storage hierarchy

:

n

Tablespace

Object segment

Dictionary vs. Local

uStarting with 8i, you can now create tablespaces that are managed locally within the header file of the tablespace uIf you specify local in a create tablespace, you cannot

specify default storage, minextents, nor temporary clause uLocal allows two types of management: uniform extent or

autoallocate

Create Tablespace (dictionary)

uCreate a tablespace using the CREATE TABLESPACE SQL command.

uThe syntax for dictionary managed is:

CREATE TABLESPACE <tablespace name>

datafile '<datafile location>‘size <datafile size> [M|K]

[autoextend_clause]

DEFAULT STORAGE (<storage_clause>)

214

Create Tablespace (local)

uCreate locally managed tablespace syntax:

CREATE TABLESPACE <tablespace name>

datafile '<datafile location>' size <datafile size> [M|K]

EXTENT MANAGEMENT LOCAL UNIFORM SIZE <extent size> [M|K];

215

Create Tablespace (OMF)

uCreate an Oracle Managed File in a default location for datafile creation with defaults of 100M size and unlimited autoextensible maximum size:

alter system set db_create_file_dest =

‘<datafile location>’

CREATE TABLESPACE <tablespace name>;

Datafile Naming

uThe datafile name must contain the absolute path

n No UNIX/NT variable names nor VMS logicals should be specified in the datafile name

uThe directory that contains the datafile MUST have write permissions for the Oracle software owner

217

UNIX Naming

u

Example

n Create a tablespace called DEVELOPMENT

n Specify a datafile '/u01/oradata/SEED/SEED_DEVELOPMENT_01.dbf' with a size of 300 Megabytes

n Make this tablespace available to all users immediately

218

UNIX Naming Example

u

The answer is:

SQL> create tablespace development

2> datafile '/u01/oradata/SEED/SEED_DEVELOPMENT_01.dbf'

3> size 300M;

Tablespace created.

VMS Naming

u

Example:

n Create a tablespace called RBS

n Specify a datafile 'dka300:[oradata.seed]seed_rbs_01.dbf' with a size of 300 Megabytes

n Make this tablespace available to all users immediately and autoextensible at 500k increments to a maximum of 400 megabytes

220

VMS Naming Example

u

The answer is:

SQL> create tablespace rbs

2> datafile 'dka300:[oradata.seed]seed_rbs_01.dbf’

3> size 300M

4> autoextend on next 500k maxsize 400M;

Tablespace created.

221

NT Naming

u

Example

n Create a tablespace called NEWDATA

n Specify a datafile ‘d:\oradata\seed\seed_newdata_01.dbf' with a size of 900 Kilobytes

n Make this tablespace unavailable to users.

uExtra credit – what is missing with the final statement and assumed?

NT Naming Example

u

The answer is:

SQL> create tablespace newdata

2> datafile ‘d:\oradata\seed\seed_newdata_01.dbf’

3> size 900K offline;

223

Creating and Managing Tablespaces

u

When creating datafiles for tablespaces, adopt a

common file naming convention

u

Most Oracle file types are named as follows (see

next slides)

224

Creating and Managing Tablespaces

u

DATAFILES

<TABLESPACE NAME>_<SID NAME>_<FILE ID>.dbf

u

Example

n

The third datafile of the SYSTEM tablespace

belonging to the SEED database

system_SEED_03.dbf

Creating and Managing Tablespaces

uUse the ALTER DATABASE SQL command to modify the characteristics of an existing tablespace

n Add/rename/change the state of a datafile n Change a tablespace to read-only n Take a tablespace offline/online n Change the storage parameters n To begin/end online hot backups n Coalesce free space

226

Alter Tablespace Syntax

227

Datafile Options

Creating and Managing Tablespaces

u

Example:

n

Add a second 100 MB datafile to the system

tablespace

n

Place the datafile under

/u01/oradata/SEED/SYSTEM_SEED_02.dbf