Introduction to Oracle Database

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.

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.

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.

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.

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. 

1.3.9 Manageability monitor (MMON)
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

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.