Oracle DBA Basics

Oracle Database 11g Architecture

Database: is a set of controlfiles, logfiles, and datafiles to store the data. It consists:

Parameter Files - The Oracle Parameter File (PFILE or SPFILE) holds initialization parameters which indicate where the control files are, how memory is allocated, how logging is performed, and other characteristics of the Oracle instance.

Control Files - The Control File stores information needed to verify that the database is consistent, including a list of all database files making up the instance and other important data.

Redo Log Files - The Redo Log files store a sequence of entries describing all actions taken against the database. This data is used to recover a database in the event of instance failure.

Data Files - The Data Files contain blocks of data which store database objects (e.g., tables, indexes, materialized views, etc.) in the database.

Temp Files - The Temp files contains data used temporarily including intermediate results, sort results and so forth.

An Instance is a collection of Oracle background processes and shared memory structures. Memory Areas are 1. SGA - System Global Area (SGA consists of the following four (five if MTS) parts: Fixed Portion, Variable Portion, Shared pool, Java pool). 2. PGA - Process Global Area 3. UGA - User Global Area.

Processes: will manage and control the Oracle instance. This allows for optimum execution on multi-processor systems using multi-core and multi-threaded technology. Some of these processes include:

PMON - Process Monitor
SMON - System Monitor
ARCn - Redo Log Archiver
LGWR - Redo Log Writer
DBWR - Database Writer
CKPT - Checkpoint process
RECO - Recoverer
CJQn - Job Queue Coordinator
QMNn - Queue-monitor processes
Dnnn - Dispatcher Processes (multiplex server-processes on behalf of users)
Snnn - Shared server processes (serve client-requests)
MMAN - Memory Manager process which will help in automatic memory management when use sga_target,memory_target
LSP0 - Logical standby coordinator process (controls Data Guard log-application)
MRP - Media-recovery process (detached recovery-server process)
MMON - This is the process which will write to AWR base tables ie WR$ tables
MMNL - Memory monitor light (gathers and stores AWR statistics)
PSP0 - Process-spawner (spawns Oracle processes)
RFS - Remote file server process (archive to a remote site)
DBRM - DB resource manager (new in 11g)
DIAGn - Diagnosability process (new in 11g)
FBDA - Flashback data archive process (new in 11g)
VKTM - Virtual Timekeeper (new in 11g)
Wnnn - Space Management Co-ordination process (new in 11g)
SMCn - Space Manager process  

 

SGA (Shared/System Global Area):  A set of memory structures needed for the database to run (shared pool, buffer cache, log buffer etc) and dedicated to Oracle Instance. The SGA is used to store incoming data (data buffers as defined by the db_cache_size parameter), and internal control information that is needed by the database. To control the amount of memory to be allocated to the SGA by setting some of the Oracle “initialization parameters”. These might include db_cache_size, shared_pool_size and log_buffer. 40% of RAM can be used for sizing SGA rest is reserved for OS and others in 64 bit machine and in 32 bit machine max SGA configured can be 1.5GB only. Check the statspack report. Check hit ratio of Data buffer. If it is less than 90%, then increase the Data buffer. Check hit ratio of Shared pool. If it is less than 95%, then increase the Shared pool. Check log buffer. If redo buffer allocation retries/redo entries is greater than 1%, then increase log_buffer.

SGA size: It’s controlled by db_cache_size parameter. SELECT * FROM v$sga/v$sgainfo;

Instance is a set of memory structures that manage database files (database programs and RAM)

Shared pool (it’s like RAM): It’s a buffer for SQL statements. It has library cache (contains parsed SQL statements, cursor information, execution plans), dictionary cache (contains cache -user account information, priveleges information, datafile, segment and extent information), buffers for parallel execution messages, and control structure.                                         
Depending on the size of the db_cache_size parameter, the shared pool contains RAM memory regions that serve the following purposes: Library cache – The library cache contains the current SQL execution plan information. It also holds stored procedures and trigger code. Dictionary cache - The dictionary cache stores environmental information, which includes referential integrity, table definitions, indexing information, and other metadata stored within Oracle's internal tables. Session information – Systems that use SQL*Net version 2 with a multi-threaded server need this area to store session information. Beginning with Oracle, the v$session view contains information related to Oracle*Net users.

Different areas stored in the shared pool and their purpose: Shared SQL Area - The shared SQL area stores each SQL statement executed in the database. This area allows SQL execution plans to be reused by many users. Private SQL Area - Private SQL areas are non-shared memory areas assigned to unique user sessions. PL/SQL Area - Used to hold parsed and compiled PL/SQL program units, allowing the execution plans to be shared by many users. Control Structures - Common control structure information, for example, lock information.

Large pool is an optional memory area intended for memory allocations that are larger than is appropriate for the shared pool and used for backups (i.e.Parallel Query performance management, Recovery Manager (RMAN) backup and recovery operations).

Java pool is an area of memory that stores all session-specific Java code and data within the Java Virtual Machine (JVM). This memory includes Java objects that are migrated to the Java session space at end-of-call.

PGA (Program or Process Global Area) is a memory area (RAM) that stores data and control information for a single process. For example, it typically contains a sort area, hash area, session cursor cache, etc.

 

Database Buffer Cache: Database Buffer Cache is the place where data blocks are copied from datafiles to perform SQL operations. Buffer Cache is shared memory structure and it is concurrently accessed by all server processes.

Oracle allows different block size for different tablespaces. A standard block size is defined in DB_BLOCK_SIZE initialization parameter. System tablespace uses standard block size. DB_CACHE_SIZE parameter is used to define size for Database buffer cache. For example to create a cache of 800 MB, set parameter as below DB_CACHE_SIZE=800M.

Redo Log Buffer: The Redo Log buffer is a RAM area (defined by the initialization parameter log_buffer) that works to save changes to data, in case something fails and Oracle has to put it back into its original state (a “rollback”). When Oracle SQL updates a table (a process called Data Manipulation Language, or DML), redo images are created and stored in the redo log buffer. Since RAM is faster than disk, this makes the storage of redo very fast.

Oracle Background processes (V$PROCESS): V$PROCESS This view contains information about the currently active processes for one or many sessions.

 

GEN0 (General Task Execution Process): Performs required tasks including SQL and DML.

PSP0 (Process Spawner Process): Spawns Oracle background processes after initial instance startup.

SMON (System Monitor Process): SMON performs many database maintenance tasks i.e. 1.Creates and manages the temporary tablespace metadata 2.Reclaims space used by orphaned temporary segments 3. Maintains the undo tablespace by onlining, offlining, and shrinking the undo segments based on undo space usage statistics 4. Cleans up the data dictionary when it is in a transient and inconsistent state 5. Maintains the SCN to time mapping table used to support Oracle Flashback features.

PMON (Process Monitor): Monitors the other background processes and performs process recovery when a server or dispatcher process terminates abnormally. PMON periodically performs cleanup of all the following: 1.Processes that died abnormally 2. Sessions that were killed 3. Detached transactions that have exceeded their idle timeout 4. Detached network connections which have exceeded their idle timeout.

DIAG (Diagnostic Capture Process): Performs diagnostic dumps requested by other processes and dumps triggered by process or instance termination.

DIA0 (Diagnostic Process): Detects and resolves hangs and deadlocks.

DBRM (Database Resource Manager Process): Sets resource plans and performs other tasks related to the Database Resource Manager.

DBWR is the only background process can write into datafiles.
DBWR is used to write dirty buffers from DBBC to Datafiles.
LGWR is the only background process can write into logfiles.
LGWR is used to write redo logs from RLBC to Online Redo Log Files.

 

Default passwords of SYSTEM/SYS? MANAGER or CHANGE_ON_INSTALL

tnsnames.ora file to establish Oracle connections from a remote client to database.

Listener Commands

$ lsnrctl status (checking the status).

$ lsnrctl start (start)

$ lsnrctl stop

$ lsnrctl reload

PFILE is a Static, text file that initializes the database parameters when it started. To modify parameters in PFILE, you have to restart the database.

SPFILE is a dynamic, binary file that allows you to overwrite parameters in run time by using ALERT SYSTEM.
DML: SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE
DDL: CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME
DCL: GRANT, REVOKE

To use RMAN to make consistent hot backups, database should be in ARCHIVELOG mode. RMAN for taking hot back-up for database, which will take a consistent backup even when your DB is up and running.
To reduce the space of TEMP datafile, you have to recreate the datafile, in 11g use shrink TEMP tablespace option.
Few commands:

$ rman target / -- $ mkdir -p /backup/rman

DB Shutdown options: SHUTDOWN: It waits for all sessions to end, without allowing new connections.
SHUTDOWN IMMEDIATE: Rollback current transactions and terminates every session.
SHUTDOWN ABORT: Aborts all the sessions, leaving the database in an inconsistent state.
Backup the database without RMAN, by using controlfile.
Oracle Grid Control components: OMR (Oracle Management Repository), OMS (Oracle Management Server) and OMA (Oracle Management Agent).
Passwords in oracle case sensitive in Oracle 11g.
In order to create a RAC, you need one instance.
RAC views has the prefix ‘G’. For example, GV$SESSION instead of normal views V$SESSION.
The PGA contains data and control information for a single user process.
RMAN do not take read-only table space backups.
Real Application Cluster (RAC) is a component of the Oracle 9i database product that allows a database to be installed across multiple servers.
Oracle Clusterware is a technology transforming a server farm into a cluster. A cluster is defined as a group of independent, but connected servers, cooperating as a single system.

In Real Application Clusters environments, all nodes concurrently execute transactions against the same database. Real Application Clusters coordinates each node's access to the shared data to provide consistency and integrity.
Benefits: Improve response time, Improve throughput, High availability and Transparency