Oracle Database:
An Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information.A database server is the key to solving the problems of information management.
Responsibilities of Database Administrators:
- Installing and upgrading the Oracle Database server and application tools
- Allocating system storage and planning future storage requirements for the database system
- Creating primary database storage structures (tablespaces) after application developers have designed an application
- Creating primary objects (tables, views, indexes) once application developers have designed an application
- Modifying the database structure, as necessary, from information given by application developers
- Enrolling users and maintaining system security
- Ensuring compliance with Oracle license agreements
- Controlling and monitoring user access to the database
- Monitoring and optimizing the performance of the database
- Planning for backup and recovery of database information
- Maintaining archived data on tape
- Backing up and restoring the database
- Contacting Oracle for technical support
Oracle Server:
1.1 Logical structure
Physical storage structures
Oracle database consists of 3 types of physical files –
• Data files – contains all database data
• Redo log files – record all changes made to data. Used for recovery
• Control files – maintains information about physical structure of database
1.2 Oracle memory structure
1.2.1 SGA
System Global Area (SGA) is shared memory area. All users of database share information maintained in this area.
System Global Area (SGA) is shared memory area. All users of database share information maintained in this area.
The SGA and other background processes
constitute an Oracle instance.
SGA size is limited by SGA_MAX_SIZE initialization parameter.
The database buffer cache is the area of memory that caches database data,
holding blocks from data files that have been read recently.
Before a user can look at a piece of information in an Oracle database, it must first reside in the database buffer cache. Data gets into this cache based upon the Most Recently Used algorithm. Because the most recently and most frequently used data is kept in memory, less disk I/O is necessary, and overall database performance is improved.
Before a user can look at a piece of information in an Oracle database, it must first reside in the database buffer cache. Data gets into this cache based upon the Most Recently Used algorithm. Because the most recently and most frequently used data is kept in memory, less disk I/O is necessary, and overall database performance is improved.
Oracle uses an LRU mechanism to remove data from DB cache.
DB_CACHE_SIZE,DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE determines DB cache size.
DB_CACHE_ADVICE can be set to ON/OFF/READY and the result can be viewed from V$DB_CACHE_ADVICE.
Redo log buffer is a circular buffer in SGA that holds information about changes made to data.
LOG_BUFFER determines its size.
DB_CACHE_SIZE,DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE determines DB cache size.
DB_CACHE_ADVICE can be set to ON/OFF/READY and the result can be viewed from V$DB_CACHE_ADVICE.
Redo log buffer is a circular buffer in SGA that holds information about changes made to data.
LOG_BUFFER determines its size.
1.2.2 Shared pool
Library cache contains shared SQL area, PL/SQL procedures and packages etc.
It is used for maintaining recently executed SQL commands and their execution
plans.
Data dictionary cache is a collection of database tables and views containing
metadata about the database, its structures, its privilege and its users. Oracle
accesses data dictionary frequently during parsing of SQL statements. Data
dictionary cache holds most recently used data dictionary information.
SHARED_POOL_SIZE determines size of shared pool and can be dynamically
altered.
PGA contains data and information for single server process.
PGA_AGGREGATE_TARGET specifies total amount of memory that can be
used by all server processes.
1.3 Background processes
1.3.1 Database Writer (DBWn)
Oracle marks buffers in memory as dirty when the data they contain is changed.
DBWn writes content of dirty buffer to data file when – a server process can’t
find a clean buffer after searching set threshold of buffers, a checkpoint occurs,
change table space to read only/offline/backup mode, drop/truncate table etc.
1.3.2 Log Writer (LGWR)
It is responsible to redo log buffer management. Almost all activities against the database are tracked in the online redo logs. As transaction are initiated and eventually committed or rolled back, a record of this activity is written to these log files. Log writer writes to redo logs sequentially.
It is responsible to redo log buffer management. Almost all activities against the database are tracked in the online redo logs. As transaction are initiated and eventually committed or rolled back, a record of this activity is written to these log files. Log writer writes to redo logs sequentially.
1.3.3 Checkpoint (CKPT)
Helps to reduce time required for instance recovery. A checkpoint is an event
that flushes modified data from buffer cache to disk and updates control file and
data files. The CKPT process updates header of data files and control files and
DBWn writes actual blocks to file. Checkpoint occurs automatically when an
online redo log file fills (log switch).
1.3.4 System Monitor (SMON)
At startup, SMON’s job is to ensure that all the database files are consistent and
perform recovery if required. There is also an assortment of other cleanup
activities that may need to be done, which are SMON’s responsibility. The
SMON process by itself checks every so often to see whether there are any tasks
waiting for its attention.
1.3.5 Process Monitor (PMON)
Cleans up failed user processes and frees all resources used by failed process.
1.3.6 Archiver (ARCn)
It automatically saves copies of redo logs in a DBA specified storage location
when media recovery is enabled.
1.3.7 Recover (RECO)
Is used with distributed transaction to resolve failure.
1.3.8 Lock (LCKn)
It is used in RAC.
It is used in RAC.
1.3.9 Manageability monitor (MMON)
It makes snapshots of the database’s health (statistics) and stores this information in the automatic workload repository.
It makes snapshots of the database’s health (statistics) and stores this information in the automatic workload repository.
1.3.10 Processing SQL
The following steps show how Oracle processes SQL
1. Statement is passed to Oracle for processing
2. Before it is placed in the library cache, a hash value is computed that
represent s a number of characteristics of the SQL.
3. Oracle compares the computed hash value against those values in a hash
table where it maintains for SQL statements already in the cache.
4. If a match is found, the new SQL statement is thrown away and the one
sitting in the cache is executed on its behalf.
5. In no match is found, further processing is done on the new SQL
statement, an entry is made in the library cache hash table for newly
arrived code, and it is placed in the library cache.
6. There are 3 stages of SQL processing – parse, execute and fetch
• During parsing, Oracle server checks the syntax and validates
table, column names against data dictionary
• Determines whether user has privilege to execute the statement
• Determines optimal execution plans for statement
• Finds a shared SQL area for the statement
• In execution stage, for UPDATE and DELETE statement, Oracle
locks the affected rows, looks for data blocks in DB buffer cache,
if found, executes becomes faster, if not then Oracle has to read
from physical data files to buffer cache. For SELECT and INSERT
statements, locking is not necessary.
• During fetch operation, rows are fetched to user process.
1.4 Installing and managing Oracle database
To use operating system authentication, set
REMOTE_LOGIN_PASSWORDFILE parameter to NONE (default).
OS authenticated users can connect as CONNECT / AS SYSDBA (or
SYSOPER).
When using password file authentication, users connect to database by
specifying username and password.
1. Using ORAPWD utility, create a password file with SYS password.
2. Set REMOTE_LOGIN_PASSWORDFILE parameter.
3. Grant appropriate users SYSDBA or SYSOPER privilege.
1.5 Oracle Managed Files (OMF)
Set following parameters in initialization file.
DB_CREATE_FILE_DEST – default location of new datafiles
DB_CREATE_ONLINE_LOG_DEST_n – specifies location for online log files
and control files (max. 5 locations)
To create database using OMF,
use
CREATE DATABASE MYDB DEFAULT TEMPORARY TABLESPACE
TMP;
1.6 Creating a new database
The steps are shown below.
1.Firstly,export environmental variables,To export EV automatically for every session, do below changes to /home/oracle/.bashrc file:
export ORACLE_SID=DBNAME
export ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
2. Create parameter file and modify it by setting minimum required parameters:
*.db_name=DBNAME
*.db_block_size=8192
*.sga_target=1677721600
*.undo_management=’AUTO’
*.control_files = (‘/home/oracle/oracle/product/10.2.0/control01.ctl’)
*.user_dump_dest=’/home/oracle/oracle/product/10.2.0/udump’
*.background_dump_dest=’/home/oracle/oracle/product/10.2.0/bdump’
*.core_dump_dest=’/home/oracle/oracle/product/10.2.0/cdump’
After creation of this parameter file,
create below folders in /home/oracle/oracle/product/10.2.0/ directory.
Three of them are dump folders (needed for trace files and alert.log file). We’re going to keep Control Files and DataFiles in oradata folder.
– oradata
– udump
– bdump
– cdump
3. Create Server parameter file (SPFILE) using this parameter file and STARTUP the instance in NOMOUNT mode.
CREATE SPFILE FROM PFILE=’/home/oracle/oracle/product/10.2.0/init.ora’;
STARTUP NOMOUNT
(Now our instance started, SGA allocated and background processes started)
4. To create a new database, use the CREATE DATABASE statement. As a result, below files will be created:
– Redo Log files
– system.dbf and sysaux.dbf (files for SYSTEM tablespace)
– undo01.dbf file (for UNDO tablespace)
– temp_tbs.dbf file (for TEMPORARY tablespace)
– users.dbf (for DEFAULT PERMANENT tablespace)
######## Database Creation Code ########
CREATE DATABASE DBNAME
USER SYS IDENTIFIED BY PASSWORD
USER SYSTEM IDENTIFIED BY PASSWORD
USER SYSTEM IDENTIFIED BY PASSWORD
LOGFILE GROUP 1 (‘/home/oracle/oracle/product/10.2.0/oradata/redo01.log’) SIZE 50 m,
GROUP 2 (‘/home/oracle/oracle/product/10.2.0/oradata/redo02.log’) SIZE 50 m,
GROUP 3 (‘/home/oracle/oracle/product/10.2.0/oradata/redo03.log’) SIZE 50 m
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET us7ascii
NATIONAL CHARACTER SET al16utf16
DATAFILE ‘/home/oracle/oracle/product/10.2.0/oradata/system01.dbf’ SIZE 325 m REUSE EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE ‘/home/oracle/oracle/product/10.2.0/oradata/sysaux.dbf’ SIZE 400 m REUSE
DEFAULT TABLESPACE tbs_1 DATAFILE ‘/home/oracle/oracle/product/10.2.0/oradata/users.dbf’ SIZE 200m REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE ‘/home/oracle/oracle/product/10.2.0/oradata/temp_tbs.dbf’ SIZE 20m REUSE
undo TABLESPACE undotbs DATAFILE ‘/home/oracle/oracle/product/10.2.0/oradata/undo01.dbf’ SIZE 200m REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
5. Run the scripts necessary to build views, synonyms, and PL/SQL packages
CONNECT / AS SYSDBA
SQL>@$ORACLE_HOME/rdbms/admin/catalog.sql
SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql
6. Shutdown the instance and startup the database.
