Transcript Keed Admin Course

Keed Education
Oracle Database
Administration
Basic
Copyright 2009 Keed Education BV
Version 0.0.1. Concept
Page Footer
Slide Titel
Part I
Oracle database architecture
Page Footer
Slide Titel
Overview
Chapter 1
Oracle Instance
Chapter 2
Logical structure of an Oracle database
Chapter 3
Physical structure of an Oracle database
Page Footer
Slide Titel
Chapter 1
The Oracle Instance
Page Footer
Logical database structure: The Oracle Instance
An Oracle Instance consists of:
• Memory Structures
Configurable chunks of internal server memory used to optimize database
actions like storing data blocks and database language commands (SQL). The
Memory structures together form the Shared Global Area (SGA)
• Background Processes
Generic processes to perform certain database functions like monitoring
processes and writing changed data back to datafiles.
Page Footer
Oracle Logical Database Structure
Oracle Instance
Shared Global Area
Shared Pool
Database Buffer Cache
Library
Cache
Data
Library
Dictionary
Cache
Cache
Large Pool
Redo
Log
Buffer
Java Pool
PMON
Page Footer
SMON
DBWR
LGWR
CKPT
Others
Logical database structure: Shared Global Area (1 of 2)
The Shared Global Area (SGA) consists of:
• Library Cache
Structure used to store SQL commands for reuse
Sharted Pool
• Data Dictionary Cache
Structure used to store Meta information (information about the database
objects like table-columns and object-privileges
• Database Buffer Cache
Structure used to store data read from data files. If data is changed, it is always
done in the Database Buffer Cache (never direct on disk)
• Redo Log Buffer
Buffer used to cache information about database transaction. From this buffer
the redo data is written to the Online Redo Log Files
Page Footer
Logical database structure: Shared Global Area (2 of 2)
The Shared Global Area (SGA) consists of:
• Large Pool
The Large Pool is an optional Memory structure which is used to store large
memory allocations. Especially when RMAN (Backup and Recovery Manager)
is used, it is recommended to configure the Large Pool
• Java Pool
The Java Pool is only used to execute Java Stored Procedures. If no Java is
used in the database, the size of the Java Pool can set up 0 (default)
Page Footer
Logical database structure: Background processes
Oracle Background Processes:
• Execute general database task
• Are automatically started when database is started
• Mandatory and optional background processes
• serveral instances of some background processes can start (heavy use)
• Background processes are visible in V$BGPROCESS
Page Footer
Logical database structure: Shared Global Area (1 of 2)
The Shared Global Area (SGA) consists of:
• Library Cache
Structure used to store SQL commands for reuse
• Data Dictionary Cache
Structure used to store Meta information (information about the database
objects like table-columns and object-privileges
• Database Buffer Cache
Structure used to store data read from data files. If data is changed, it is always
done in the Database Buffer Cache (never direct on disk)
• Redo Log Buffer
Buffer used to cache information about database transaction. From this buffer
the redo data is written to the Online Redo Log Files
Page Footer
Oracle Instance: Background Processes
The Shared Global Area (SGA) consists of:
• Large Pool
The Large Pool is an optional Memory structure which is used to store large
memory allocations. Especially when RMAN (Backup and Recovery Manager)
is used, it is recommended to configure the Large Pool
• Java Pool
The Java Pool is only used to execute Java Stored Procedures. If no Java is
used in the database, the size of the Java Pool can set up 0 (default)
Page Footer
Logical database structure: Background Processes (1/2)
Oracle basic Background processes:
• SMON
: System Monitor
• PMON
: Process Monitor
• DBWR
: Database Writer
• LGWR
: Log Writer
• CKPT
: Checkpoint Process
• ARCH
: Archiver Process
• J000
: Job Queue Process
Page Footer
Logical database structure: Program Global Area (PGA)
Oracle basic Background processes:
• SMON
: System Monitor
• PMON
: Process Monitor
• DBWR
: Database Writer
• LGWR
: Log Writer
• CKPT
: Checkpoint Process
• ARCH
: Archiver Process
• J000
: Job Queue Process
Page Footer
Slide Titel
Chapter 2
Logical structure of an Oracle
database
Page Footer
Logical database structure: Overview
Logical database structure:
• Tablespace : Container for tables, indexes, Undo or Temporary structures
• Segment
: The type of the segment (table, index, undo or temporary segment)
• Extent
: Unit of allocation of storage for specific segment
• Data Block : Smallest unit in which data is stored
Page Footer
Logical database structure: Overview
Oracle Tablespace:
•
Logical container for segments (e.g. table, index, temporary)
•
An Oracle database have several mandatory tablespaces (system, sysaux,
undo, temporary)
•
Default storage clause for tables and indexes can be set on tablespace level
•
Tablespace can be permanent (datafiles) or temporary (tempfiles)
•
Tablespace can be locally managed or dictionary managed
•
Tablespace can be set offline (data in tablespace is not accessible)
•
Storage quotum for users or applications can be set on tablespace
Page Footer
Logical database structure: Mandatory tablespaces
An Oracle database has several mandatory tablespace:
• System:
contains the data dictionary and store temporary segments when no
default temporary tablespace is defined
• Sysaux:
is used to store information for specific database functions like
OLAP, RMAN and Ultra Search
• Undo:
the Undo tablespace is used for storing Undo information (changes
made in transactions). No other objects can be created in the Undo
tablespace.
• Temp:
Tablespace used to execute sorts which are too big to do in memory
by using temporary segments
Page Footer
Logical database structure: Tablespaces
Free space management in an Oracle tablespace:
• Data Dictionary Managed tablespaces (DMT)
Free space management is done in the system tablespace
Default management mode in Oracle 8i and previous versions
• Locally Managed Tablespaces (LMT)
Free space management is done in the tablespace itself
A bitmap structure is used to map and identifiy free blocks in the tablespace
Default management mode in Oracle 9i and onwards
Combination of DMT and LMT tablespace possible in database as long as
system tablespace is dictionary managed.
Page Footer
Logical database structure: BigFile Tablespaces
BigFile Tablespaces:
• Data Dictionary Managed tablespaces (DMT)
Free space management is done in the system tablespace
Default management mode in Oracle 8i and previous versions
• Locally Managed Tablespaces (LMT)
Free space management is done in the tablespace itself
A bitmap structure is used to map and identifiy free blocks in the tablespace
Default management mode in Oracle 9i and onwards
Page Footer
Logical database structure: Segments
Oracle Segments:
• Oracle has four types of segments: Table, Index, Undo and Temporary
• Logical container for extents (e.g. table, index or temporary)
• All extents within a segment are used to store similar object (e.g. Table)
• DBA_SEGMENTS records information about segments.
Page Footer
Logical database structure: Extents
Segment:
• Logical container for segments (e.g. table, index, temporary)
Page Footer
Logical database structure: Blocks
Blocks:
• A blocks is the smallest unit used in storage
• a Block has a header in which meta-data is stored
• Default block size is configured by parameter DB_BLOCK_SIZE
• if space in block is insufficient Row Migrating and Row Chaining will occur
Page Footer
Logical database structure: Segments, extents and blocks
Relationship among segments, extents and blocks:
Segment
256k
Extent
64k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
8k
Extent
8k
192k
Data blocks
Page Footer
Slide Titel
Chapter 3
Physical structure of an
Oracle database
Page Footer
Physical database structure: Overview
The physical structure of an Oracle database comprises of:
• Data files
• Temp files
• Online redo log files
• Control files
• Parameter or initialization file
• Password file
Page Footer
Physical database structure: Data files
Oracle Data files:
• Permanent tablespaces using data files for storage
• Every tablespace has at least 1 data file
• A data file can be configured to grow automatically (autoextent)
• A data file can be set Online or Offline
• The view DBA_DATA_FILES contains information about the data files
Page Footer
Physical database structure: Temp files
Oracle Temp files:
• Temporary tablespaces using temp files for storage
• Temp tablespaces are mainly used for big sorting operations
• Creation of temp files is fast. Only header and last block are written
• Temp files are not recorded in the control file
• It is not necesarry to backup temp files (with RMAN)
Page Footer
Physical database structure: Redo Log Files
Oracle Redo Log files:
• Redo log files are transaction journals
• At least two redo log groups must be defined in an Oracle database
• Every redo log group must contain at lease 1 member
• It is recommended to multiplex Redo log members
• A filled up redo log file is archived when database is running is archive log mode
• Redo log groups and members information is recorded in the control file
Page Footer
Physical database structure: Control Files
Oracle Control files:
• Control file contains physical information about the Oracle databas
• Control file is read in nomount-phase of database startup
• Control file can be dumped with SQL-command:
alter database backup controlfile to trace
• Control files must be multiplexed to ensure availability during operation
• Locations of control files are included in the parameter file
Page Footer
Physical database structure: Password File
Oracle Password file:
• Used to authenticate sysdba & sysoper users when database is not running
• Is created with Oracle utility $ORACLE_HOME/bin/orapwd
• Can be shared among databases on same server or can be dedicated to single
database on server (recommended)
• Parameter REMOTE_LOGIN_PASSWORDFILE is used to configure use
• The view V$PWFILE_USERS gives an overview of users recorded in file
Page Footer
Physical database structure: Oracle Managed Files
Oracle Managed Files (OMF):
• Used to authenticate sysdba & sysoper users when database is not running
• Is created with Oracle utility $ORACLE_HOME/bin/orapwd
• Can be shared among databases on same server or can be dedicated to single
database on server (recommended)
• Parameter REMOTE_LOGIN_PASSWORDFILE is used to configure use
• The view V$PWFILE_USERS gives an overview of users recorded in file
Page Footer
Slide Titel
Chapter X
Creating an Oracle database
Page Footer
Creating an Oracle Database: Parameter file
Oracle Managed Files (OMF):
• Used to authenticate sysdba & sysoper users when database is not running
• Is created with Oracle utility $ORACLE_HOME/bin/orapwd
• Can be shared among databases on same server or can be dedicated to single
database on server (recommended)
• Parameter REMOTE_LOGIN_PASSWORDFILE is used to configure use
• The view V$PWFILE_USERS gives an overview of users recorded in file
Page Footer
Creating an Oracle Database: Basic initialization parameters
Oracle Managed Files (OMF):
• Used to authenticate sysdba & sysoper users when database is not running
• Is created with Oracle utility $ORACLE_HOME/bin/orapwd
• Can be shared among databases on same server or can be dedicated to single
database on server (recommended)
• Parameter REMOTE_LOGIN_PASSWORDFILE is used to configure use
• The view V$PWFILE_USERS gives an overview of users recorded in file
Page Footer
Creating an Oracle Database: Creating an Oracle Database
Oracle Managed Files (OMF):
• Used to authenticate sysdba & sysoper users when database is not running
• Is created with Oracle utility $ORACLE_HOME/bin/orapwd
• Can be shared among databases on same server or can be dedicated to single
database on server (recommended)
• Parameter REMOTE_LOGIN_PASSWORDFILE is used to configure use
• The view V$PWFILE_USERS gives an overview of users recorded in file
Page Footer
Creating an Oracle Database: Manually creating an Oracle Database
Oracle Managed Files (OMF):
• Used to authenticate sysdba & sysoper users when database is not running
• Is created with Oracle utility $ORACLE_HOME/bin/orapwd
• Can be shared among databases on same server or can be dedicated to single
database on server (recommended)
• Parameter REMOTE_LOGIN_PASSWORDFILE is used to configure use
• The view V$PWFILE_USERS gives an overview of users recorded in file
Page Footer
Creating an Oracle Database: Creating an Oracle Database using DBCA
Oracle Managed Files (OMF):
• Used to authenticate sysdba & sysoper users when database is not running
• Is created with Oracle utility $ORACLE_HOME/bin/orapwd
• Can be shared among databases on same server or can be dedicated to single
database on server (recommended)
• Parameter REMOTE_LOGIN_PASSWORDFILE is used to configure use
• The view V$PWFILE_USERS gives an overview of users recorded in file
Page Footer
Starting en Stopping Oracle: Startup phases
Startup nomount
Nomount
Parameter file (PFILE or SPFILE) is read
SGA (memory) is allocated and the background
processes are started
Startup mount
Mount
Control file is read
Startup
Open
Data files and temp files are opened
Database become available for users
Page Footer
Starting en Stopping Oracle: Start Oracle with alternative pfile
Parameter file:
• The use of a SPFILE is recommended over the use of the PFILE
•
Page Footer
Starting en Stopping Oracle: PFILE and SPFILE
Parameter file:
• The use of a SPFILE is recommended over the use of the PFILE
•
Page Footer
Oracle user management: Oracle database users (overview)
Syntax for creating a database user:
CREATE USER username IDENTIFIED BY password
DEFAULT TABLESPACE tablespace
TEMPORARY TABLESPACE tablespace
PROFILE profile ;
• Username is a 30-length string and should not start with digit
• TEMPORARY TABLESPACE is obsolete (In 10g default temporary tablespace)
• Resource requirements can set in the profile (like number of invalid logins)
• Privileges and rights cannot be assigned in CREATE-command
Page Footer
Oracle user management: Creating database users
Syntax for creating a database user:
CREATE USER username IDENTIFIED BY password
DEFAULT TABLESPACE tablespace
TEMPORARY TABLESPACE tablespace
PROFILE profile ;
• Username is a 30-length string and should not start with digit
• TEMPORARY TABLESPACE is obsolete (In 10g default temporary tablespace)
• Resource requirements can set in the profile (like number of invalid logins)
• Privileges and rights cannot be assigned in CREATE-command
Page Footer
Oracle user management: Profiles
Syntax for creating a database user:
CREATE USER username IDENTIFIED BY password
DEFAULT TABLESPACE tablespace
TEMPORARY TABLESPACE tablespace
PROFILE profile ;
• Username is a 30-length string and should not start with digit
• TEMPORARY TABLESPACE is obsolete (In 10g default temporary tablespace)
• Resource requirements can set in the profile (like number of invalid logins)
• Privileges and rights cannot be assigned in CREATE-command
Page Footer
Oracle user management: Privileges
Syntax for creating a database user:
CREATE USER username IDENTIFIED BY password
DEFAULT TABLESPACE tablespace
TEMPORARY TABLESPACE tablespace
PROFILE profile ;
• Username is a 30-length string and should not start with digit
• TEMPORARY TABLESPACE is obsolete (In 10g default temporary tablespace)
• Resource requirements can set in the profile (like number of invalid logins)
• Privileges and rights cannot be assigned in CREATE-command
Page Footer
Oracle user management: Roles
Syntax for creating a database user:
CREATE USER username IDENTIFIED BY password
DEFAULT TABLESPACE tablespace
TEMPORARY TABLESPACE tablespace
PROFILE profile ;
• Username is a 30-length string and should not start with digit
• TEMPORARY TABLESPACE is obsolete (In 10g default temporary tablespace)
• Resource requirements can set in the profile (like number of invalid logins)
• Privileges and rights cannot be assigned in CREATE-command
Page Footer