Study Brief: Oracle8 Database Administration
by Michael Ritacco, Oraclenotes.com

--Oracle Architecture--

User Process

Server Process

SGA (System Global Area)
-Shared Pool

  • Library Cache
    • Text of the statement
    • The compiled version of the statement (Parse Tree)
    • The Execution Plan - tells how to run the statement, determined by the optimizer.
  • Data dictionary cache: Used during the Parse phase to resolve object names and validate access privileges.
    • Table and column definitions
    • User names, Passwords and Privileges

-Database Buffer cache: Holds the most recently used data.

  • The Least Recently Used (LRU) algorithm is used to age out blocks of data in the Buffer Cache. Each buffer in the buffer cache is equal to the size of the specified data block size.
  • DB_BLOCK_BUFFERS -parameter sets the number of buffers available.
  • DB_BLOCKSIZE - parameter sets the size of the data block

-Redo Log Buffer: Registers changes made to the database via the instance. Size is defined by LOG_BUFFER in the parameter file.

  • Stores records of changes: Block that changed, the new value, and location of the change.
  • The buffer is reused after the redo entries are recorded in the redo log files.

PGA (Program Global Area)
Used by one Process only and contains the following

  • Session Information
  • Sort Area
  • Cursor State
  • Stack Space

DBWR (Database Writer)
Writes changed data to the database file when one of the following conditions are met:

  • Dirty Buffers reach a threshold value
  • No free blocks are available
  • A timeout occurs
  • A DBWR checkpoint takes place

LGWR (Log Writer)
Writes changes registered in the redo log buffer to the database file when one of the following conditions are met:

  • When the redo log buffer is 1/3 full
  • A timeout occurs (about every 3 seconds)
  • Before DBWR writes modified blocks to the data files
  • A transaction COMMITS

SMON (System Monitor)
Checks for consistency and initiates recovery.

PMON (Process Monitor)
Cleans up resources after a process fails. Watches after the server process.

CKPT (Checkpoint Process)
Updates the database status information after changes are permanently written to the database file from the buffer cache.

Database Files
-Data files
-Redo log files
-Control files
-Parameter file
-Password file
-Archived redo log files

Query Processing

  • Parse: checks syntax
  • Execute:
  • Fetch: returns data to user

DML Statements
Requires Parse and Execute

  • Server process reads the data and rollback blocks from the data files.
  • Blocks that are read then are placed in the Buffer Cache.
  • Server process locks the data and objects.
  • Server Process records the changes to the rollback and data (new) in the redo log buffer.
  • The Server Process then records the rollback block "old" and updates the data block "new" in the database buffer cache. Both are marked "dirty buffers."

Commit Processing

--Administration Tools--
SQL*Loader, Export Import utility, Password File utility

Server Manager
-Need no connection

  • EXIT
  • REMARK
  • SET
  • SHOW
  • SPOOL

-Need privileges

  • CONNECT/DISCONNECT
  • DESCRIBE
  • EXECUTE
  • SHOW SGA
  • SHOW ERRORS
  • SHOW PARAMETER

-Need special privileges

  • STARTUP/SHUTDOWN
  • RECOVER DATABASE
  • CONNECT INTERNAL

Oracle Enterprise Manager

  • Instance Manager
  • Schema Manager
  • Security Manager
  • Storage Manager
  • SQL Worksheet
  • Backup Manager
  • Data Manager

--Managing the Instance--
The two automatically created DBA users are:

  • SYS - Owner of the data dictionary
  • SYSTEM - Used by Oracle Tools

Authentication

  • Password File- to create or change password use the ORAPWD Utility.
  • OS authentication

Startup/Shutdown Stages

  • SHUTDOWN
  • NOMOUNT
  • MOUNT
  • OPEN

Performance View Availability

  • NOMOUNT: Performance views reading from memory are available. Remember that any view referencing a file, database, or thread is not available in this stage.
  • MOUNT: Performance views reading from files are available.
  • OPEN: All Performance views and Data Dictionary views are available.

Types of Shutdowns

  • ABORT
  • IMMEDIATE
  • TRANSACTIONAL
  • NORMAL

Trace Files

-ALERT FILE: set by BACKGROUND_DUMP_DEST

  • Contains detected block corruption errors.
  • Logs STARTUP/SHUTDOWN/ARCHIVE LOG/RECOVER.
  • All values of nondefault initialization parameters.

-TRACE FILES: set by USER_DUMP_DEST

  • Logs errors detected by background processes.

Remember that:
-ALTER SYSTEM and w/ DEFERRED commands are recorded in the ALERT file.

-ALTER SESSION: modifies the parameter for only the current session.

-Alter System: Globally changes the parameter value but only until instance is shutdown. -Restricted Sessions: ALTER SYSTEM command only limits future connects and does not disconnect current sessions.

-Terminating Sessions causes PMON to rollback the user's transaction, release locks, and free reserved user resources. Inactive sessions will return "ORA-00028:your session has been killed" only when the user attempts to make a call.

--Creating a Database--
NO DATA DICTIONARY VIEWS are created by the CREATE DATABASE command.

PARAMETER FILE (Specify at least these parameters)

  • DB_NAME
  • CONTROL_FILES
  • DB_BLOCK_SIZE

File Locations
-Control Files

  • Oracle suggests at least 2 on two different disks


-Redo Log Files

  • One disk for each group member.


-Data Files

  • Separate objects with different life spans, temporary data, applications into separate tablespaces
  • Put tables and indexes in different tablespaces
  • Give redo logs there own tablespace

Parameter file

  • DB_NAME and DB_BLOCKSIZE cannot be changed after database creation.

Troubleshooting
A CREATE DATATBASE fails for the following reasons:

  • SQL Syntax errors
  • Files being created already exist
  • OS permissions are wrong or not enough space

Follow these steps to fix a failed CREATE DATABASE command

  • Shutdown database
  • Delete files created by CREATE DATABASE command
  • Correct errors
  • Reissue CREATE DATABASE command

--Creating Data Dictionary Views/Standard Packages--
 The data dictionary is updated every time a DDL command is executed, or when a Table is expanded using DML.

SYSTEM TABLESPACE: OWNER is SYS
Contains the following:

  • BASE TABLES
    • Created by sql.bsq during database creation (automatically)
    • Information is encoded, tables are normalized, not accessed by users.
    • NEVER update the BASE TABLES directly: NO DML!
  • DATA DICTIONARY VIEWS
    • Data dictionary views on base tables; performance views and their synonyms are created by the catalog.sql script (MUST be RUN as SYS). The catalog.sql also runs a script called standard.sql
    • catproc.sql runs scripts required for PL/SQL, and creates views for tablespace point-in-time recovery, and the use of LOBs. (MUST be RUN as SYS)

Administrative Scripts

  • cat*.sql: catalog and data dictionary info.
  • dbms*.sql: database package specs.
  • prvt*.plb: wrapped database package code
  • utl*.sql: views and tables for utilities

Dependent Objects

  • After executing a DDL command a dependent object status is INVALID.
  • Loading views with the Import utility can also cause INVALID object status.
  • Oracle automatically recompiles invalid views and PL/SQL units the next time they are used.

--Storage Structure--

Database Blocks

  • Header (INITANS and MAXTRANS)
  • Free Space (PCTFREE)
  • Data Space (PCTUSED)

Storage Precedence

  • Segment overrides (except for MINIMUM EXTENT)
  • Tablespace overrides
  • Oracle default

Guidelines:

  • Altered storage parameters apply to new extents only.
  • SMON only coalesces extents in tablespaces where PCTINCREASE is set greater to zero.
  • Extents are coalesced when a DBA commands, SMON initiates a space transaction, or when the server needs to allocate an extent that is larger then any free adjacent free extents.

--Rollback Segments--

Rollback segments allow for:

-Transaction rollback: a copy of the old image of data is stored so that if a rollback is necessary the data restored its original state.

-Transaction recovery: In case of instance failure during a transaction, at the next startup uncommitted data can rollback. (Rollback segment must have redo logging enabled for this to occur.)

-Read consistency: Other users cannot see uncommitted data. The rollback segments provide the data to readers.

Types of Segments

-System rollback segment: is found in the system tablespace and is created at database creation.

-Private rollback segments: Used by objects in any non-system tablespace. Must be named in the parameter file or brought online by command.

-Public rollback segments: Part of a pool available

-Deferred rollback segments: created and managed by Oracle when changes are made to data in a offline tablespace. Automatically dropped when not needed.

Note: multiple transactions can write to the same extent in a rollback segment.

Planning for Rollback Segments

-BATCH environments: create fewer and larger rollback segments in large tablespaces.

-OLTP environments: create many small rollback segments. 1 RBS to every 4 concurrent transactions.

Out of Space Solutions/Errors

-Tablespace: extend data files, enable autoextend, add files to tablespace.

-Rollback segment: increase maxextents, drop and recreate with larger extent size.

-SNAPSHOT TOO OLD: occurs when the before image in a rollback segment is overwritten by another transaction, or a slot in a rollback header has been reused. Fix by creating rollback segments with:

  • Higher MINEXTENTS
  • Larger extents
  • Increase OPTIMAL

Guidelines:

  • You must have at least 2 rollback segments
  • INITIAL=NEXT
  • Set OPTIMAL based on the size of an average transaction
  • Put rollback segments in a separate tablespace to reduce fragmentation and contention.
  • Rollback segments with MINEXTENTS=20 is recommended to help reduce extension.
  • Rollback segments must be offline to be dropped.
  • You cannot take a tablespace offline that contains an active rollback segment.
  • LOG_CHECKPOINT_INTERVAL specifies the number of O/S blocks written by LGWR that will trigger a log switch.
  • LOG_CHECKPOINT_TIMEOUT specifies the number of seconds between checkpoints.

--Managing Tables--

Types of Tables

-Regular tables have very little control over the distribution of rows sometimes called a heap-organized table.

-Partitioned tables have one or more partitions that store rows based on a key value. Each partition is a separate segment and can have multiple processes concurrently querying and manipulating.

-Index-organized tables

-Clustered tables

Row Structure

-Row header: stores the number of columns, row lock status and chaining information.

-Row data: Oracle stores the column length and value. Requires (1) byte to store column length for columns <=250 bytes. Requires (3) bytes for column lengths >250 bytes long in length. The column value is stored after the column length bytes.

Datatypes

-CHAR

-NCHAR -supports fixed-width or variable length character sets. (Fixed Length, stored with padded blanks, limit of 2000 bytes per row, default is 1 byte.)

-VARCHAR2

-NVARCHAR2 (Variable length, uses only the actual bytes needed to store the column value)

-NUMBER: stored as variable-length data, stores up to 38 digits, requires 1 byte for exponent, 1 byte for negative numbers if the digits equal less than 38 bytes.

-DATE: 7 bytes and fixed length, requires 8 bytes in memory.

-LONG, LONG RAW: up to 2G, single column per table, select returns data, data stored inline, no support for object types, and sequential access to chunks.

-LOBS: up to 4G, multiple columns per table, select returns locators, data stored either in-line or out-line, supports object types, and random access to chunks.

  • BOB
  • CLOB
  • BFILE stores unstructured data in OS files

-ROWID: consists of the data object number (locates the tablespace), block number (locates the block containing the row), relative file number (locates the file), and row number (locates the row directory entry for the row).

-VARRAYS: stores a list of a small number of elements i.e. multiple phone numbers. note: Version 7 databases only support up to 1022 data files because they use the restricted ROWID(block number, row number, file number) format.

--INDEXES--

Logical
-Single Column
-Concatenated colums (max is 32)

Guidelines:

  • Set INITRANS higher then on a table
  • Nonunique index keys can point to multiple rows.
  • Rebuild if ratio of DEL_LF_ROWS to LF_ROWS is >30%
  • Drop indexes prior to bulk loads, then rebuild. Load performance will increase and index space on rebuild will be used more efficiently.
  • Indexes marked invalid should be dropped and recreated.

B-Tree Index

  • Entry header: stores number of columns and locking info.
  • ROWID of a row contains the key values.
  • No index entry for columns that are null

Use when:

  • OLTP
  • High cardinality columns
  • Lots of updates

DML on B-Tree

  • INSERT: results in insertion of an index entry in the block.
  • UPDATE: results in a "logical delete" and an insert to the index. PCTFREE has no effect on an index except at creation.
  • DELETE: results only in a logical deletion. Space is not released until all entries in a block are deleted.
  • Reverse Key Indexes: reversing the data value of the key spreads the index updates across the index tree. Range searches cannot be performed. NOSORT keyword cannot be used.

Bitmap Index

  • -Require less space then other indexes.

Use when:

  • Table has millions of rows and key columns have low cardinality (few distinct values for the column)
  • When queries use multiple WHERE conditions involving OR.
  • When read-only or has low update activity on the key columns.
  • Data Warehousing

Bitmap indexes use restricted ROWID. Cannot be unique. CREATE_BITMAP_AREA_SIZE sets the amount of space the will be used for storing bitmap segments in memory, default is 8MB. Higher the cardinality the more memory needed.

--Constraints--

Constraint States
-Disabled
-Enabled novalidate
-Enabled valid
-Deferred: checked only when a transaction commits, must be defined at creation.

  • Initially immediate: functions as a immediate constraint.
  • Initially deferred: specifies by default the constraint be enforced at commit. -Nondeferred: are enforced after every DML statement.

-Out-of-line constraints needed when:

  • A constraint names 2+ columns
  • A table is altered to add any constraint except NOT NULL

Triggers
-Event driven, no action on existing data

-Row trigger: executes once for every row affected.

-Statement trigger: executes once per statement.

Guidelines
-Put PK indexes in separate tablespace from the table

-Disable constraints before bulk loading

-Enable invalidate is faster then enable validate

-Enable Validate (default) locks the table and existing data must meet the validation rules, statements are rolled back if any data violates the constraint.

- The EXCEPTIONS clause dumps the data that violated the constraint in the exception table, (utlexcpt.sql) creates the exception table.

--Clusters--
Smaller then normal index and stores NULL values

Types

  • Index cluster: uses a "cluster index" to store, access or maintain data in index cluster.
  • Hash cluster: uses function to locate/retrieve row. Better performance then index cluster in applicable situations.

Use Index Cluster when:
-Uniform key distribution
-Rarely updated key
-Often joining master-detail tables

Use Hash Cluster except when:
-Often joining master-detail tables

Index-Organized tables

--Roles--

Predefined Roles:

  • Connect
  • Resource
  • DBA
  • EXP_FULL_DATABASE
  • IMP_FULL_DATABASE
  • DELETE_CATALOG_ROLE
  • EXECUTE_CATALOG_ROLE
  • SELECT_CATALOG_ROLE

--Views Summary--

DATA DICTIONARY VIEWS

  • DICTIONARY: names of all the data dictionary views.
  • DICT
  • DICT_COLUMNS: also names all the data dictionary views.
  • DBA_OBJECTS
  • DBA_DATA_FILES: file name, tablespace name, bytes, max bytes,
  • DBA_TABLESPACES: shows default storage parameters for all tablespaces in database. (tablespace name, next extent, max extents, pct increase, status)
  • DBA_SEGMENTS: shows the size and storage settings for all segements in the database.
  • DBA_EXTENTS: number of extents, the location, the owner, segment name, size, extent id, and the number of blocks used by a table.
  • DBA_ROLLBACK_SEGS: shows name and ID of each RBS, tablespace, owner, and the RBS status (ONLINE, OFFLINE).
  • DBA_FREE_SPACE: shows the number of extents in a table.
  • DBA_FREE_SPACE_COALESCED
  • DBA_TABLES: the analyze command updates the chaining information in the CHAIN_COUNT column and number of empty blocks.
  • DBA_OBJECTS: has the object status column (VALID, INVALID).
  • DBA_INDEXES: name, owner name, type, and status of index
  • DBA_IND_COLUMNS: index owners, what tables, and what columns
  • IND$
  • INDEX_STATS: blocks, PCT_USED, distinct keys, DEL_LF_ROWS, and LF_ROWS
  • INDEX_TYPE: indicates index type (bitmap or normal)
  • DBA_CONSTRAINTS: type, name, status, owner, deferrable, validated, enabled, of all constraints in database
  • DBA_CONS_COLUMNS
  • DBA_TRIGGERS DBA_TRIGGERS_COLS
  • DBA_TAB_COLUMNS
  • DBA_CLU_COLUMNS: names of the columnsin the cluster and table column
  • DBA_CLUSTER: information about all clusters in the database, function and number of keys in a hash cluster.
  • DBA_USERS: information about password expiration, locking dates, account status for all the database users.
  • DBA_TS_QUOTAS
  • DBA_PROFILES
  • DBA_SYS_PRIVS
  • SESSION_PRIVS
  • DBA_TAB_PRIVS
  • DBA_COL_PRIVS
  • DBA_ROLES: displays all the roles in the database and if they require a password.
  • DBA_ROLE_PRIVS
  • DBA_SYS_PRIVS
  • RESOURCE_COST: shows the weights assigned to resource limits.
  • ROLE_ROLE_PRIVS
  • ROLE_SYS_PRIVS
  • TOLE_TAB_PRIVS
  • SESSION_ROLES: shows currently enabled roles for a user.
  • ALL_DEF_AUDIT_OPTS
  • AUDIT_ACTIONS
  • DBA_AUDIT_EXISTS
  • DBA_AUDIT_OBJECT
  • DBA_AUDIT_SESSION
  • DBA_AUDIT_STATEMENT
  • DBA_AUDIT_TRAIL
  • DBA_OBJ_AUDIT_OPTS
  • DBA_PRIV_AUDIT_OPTS
  • DBA_STMT_AUDIT_OPTS
  • NLS_DATABASE_PARAMETERS: displays the database and national character sets.
  • NLS_INSTANCE_PARAMETERS: displays the values of the NLS initialization parameters listed in the parameter file.
  • NLS_SESSION_PARAMETERS: displays current NLS parameters

PERFORMANCE VIEWS

  • V$FIXED_TABLE
  • V$PARAMETER: displays current settings of any parameter.
  • V$CONTROLFILE: displays the names and locations of the control files.
  • V$SYSTEM_PARAMETER: shows the current system values.
  • V$CONTROLFILE_RECORD_SECTION: shows info stored in the control file. example:MAXDATAFILES
  • V$DATABASE: shows if in archive mode or not
  • V$DATAFILE
  • V$DATAFILE_HEADER
  • V$INSTANCE
  • V$OPTION
  • V$PROCESS
  • V$PWFILE_USERS: names the users with the SYSDBA or SYSOPER privileges.
  • V$SESSION: gives the SID and serial number of a user's session.
  • V$SGA
  • V$VERSION
  • V$THREAD: number of redo logs groups, current log group, sequence number.
  • V$LOG: shows information about redo log files contained in the control file.
  • V$LOGFILE: names of all group members and contains the status of each log file member INVALID, STALE, DELETED, NULL
  • V$TABLESPACE
  • V$ROLLNAME: shows name and USN of RBS.
  • V$ROLLSTAT: shows the current and active extent of each rollback segment, status, optimal size, current size, high water mark, and
  • V$TRANSACTION: session address of user with an pending transaction along with the number of the assigned RBS.
  • V$SORT_SEGMENT: information about temporary segements, other tablespaces with sort segments, and status of sort extent pool.
  • V$SORT_USAGE: shows only the currently active sorts
  • V$NLS_VALID_VALUES
  • V$NLS_PARAMETERS

Joined Views

  • V$SESSION and V$SORT_USAGE: will show the user who is currently sorting.

PACKAGES

  • DBMS_LOB
  • DBMS_SESSION
  • SBMS_UTILITY
  • DBMS_SPACE: finds high water mark and number of blocks above it.
  • DBMS_ROWID contains the following functions:
    • ROWID_CREATE
    • ROWID_OBJECT
    • ROWID_RELATIVE_FNO
    • ROWID_BLOCK_NUMBER
    • ROWID_ROW_NUMBER
    • ROWID_TO_ABSOLUTE_FNO
    • ROWID_TO_EXTENDED
    • ROWID_TO_RESTRICTED
  • DBMS_SHARED_POOL
  • DBMS_SESSION.SET_ROLE
  • DBMS_SPACE.UNUSED_SPACE
  • DBMS_DDL.ANALYZE_SCHEMA: used to analyze all objects owned by a user.
  • DBMS_DDL.ANALYZE_OBJECT: used to analyze a specific object.
  • DBMS_UTILITY.ANALYZE_DATABASE: used to analyze all objects in a database.
  • VERIFY_FUNCTION
  • DBMS_SESSION_.SET_NLS

 

About Oraclenotes.com  |  Advertise  |  Contribute   |  Disclaimer 
© Copyright 1999 - 2004 Oraclenotes.com. All Rights Reserved.
Use of this website signifies your agreement to the Terms of Use.
Not affliated or endorced by Oracle Corporation.