oracle dba

ORACLE DBA TRAINING MODULE

Learning Objectives Understand the Recall the Explaining of the Summarize fundamentals of functions and RDMS the concept designing of RDMS Architecture , of RBMS DBA Tasks Background process etc.

INTRODUCTION TO RDMS [Relational Database Management System] RDBMS isbased on the Relational model. Data and relations are stored in theform of tables. Tables are accessed via Primary Keys, Foreign Keys and Indexes. RDBMS providesAtomicity whichmeans transactions are either completely full or no effect at all RDBMS providesconcurrency whichmeans multiple usersaccessing the same group of resources (such as tables, rows)

RDBMS & SQL Database MarketShare is User interface with RDBMS in 48 %dominated by Oracle SQL – a standard interactive and whereasIBM occupies programming language for 31%,Microsoft 12% and querying and modifying data and remaining by others. managing databases.

DBA Tasks Installing and upgrading Database software andapplication tools Maintaining Allocating system Database and storage and implementing High planning future Availability allocation Database Creating Database Optimization and and its storage parameters . Performance Tuning Set up Database Creating Database Monitoring structures , objects, users and granting privileges Set up Database Set up Database Backup and Recovery security and auditing when required

Oracle Database Architecture

Oracle Database Architecture Oracle Server Oracle Oracle Instance Database Memory Background Structures Proesses Data files Control Redo log files files SGA PGA

Oracle Database Architecture SGA shared pool Library Java Pool Data Base Cache Buffer Cache Redolog Data Dictionary Buffer Cache Cache Large Pool

Oracle Database Architecture Background SMON Process PMON DBWR LGWR CKPT

Oracle Database Architecture

Oracle Database Architecture

Oracle Database Architecture

Oracle Database Architecture

Oracle Database Architecture

Oracle Database Architecture

Oracle Database Architecture

Oracle 12c Architecture

Significant Terms to be known What is an Oracle Instance ? An Oracle instance is a means to access the Oracle Database, Always opens one and only one database What is SGA ? SGA is System Global Area . It constitutes DBBC ( Database Buffer Cache ), RBC(Redolog Buffer Cache) , Shared Pool and Shared Server.

Significant Terms to be known What is server process ? Servicesa single user process in the dedicated server andmultiple user processes in a shared server. What is user process? Runs on a client machine using tools like SQL Plus , OEM etc.

An instance What is an Instance ? controls zero or more databases. An Instance is made up of Examples : PMON, background SMON, DBWR, LOGWR etc.. processes and memory structures . A database can have one or more instances.

Oracle Instance and Database Oracle Instance Oracle Database constitutes constitutes • Background • Data files Processes • Redo log files • Control files • Memory Structures • Archive log files • Parameter files

Oracle Instance Architecture

Oracle Memory Structures

Oracle Process Architecture

Background Processes Performs functions on behalf of user proces Input, Output and Monitor other Oracle Processes to provide improved parallelism for better performance and reliability

Background Processes DBWR When it writes into data files : When dirty buffer of the whole block reaches threshold limit When Checkpoint occurs When there are no free buffers Time out Occurs Table space is taken offline /read only/begin backup Drop/truncate a table

Background Processes Log Writer: Performs sequential writes from Redolog Buffer When LGWR writes: Commit When 1/3 of memory is full Every 3 seconds When it reaches 1 MB Before DBWR writes Calls on DBWR to write into data files Used for Recovery purposes

Background Processes SYSTEM MONITOR PROCESS MONITOR ( SMON ) (PMON ) Used for Instance Recovery (System/Media Cleans the failed process Failure ) Rolls back user’s current transactions Roll forward committed transactions Releasing locks (changes in redolog files) Releasing other resources Open Database for access Restoring dead dispatchers Rollback uncommitted transactions Coalesces free spaces Deallocates Temporary Segments

Background Processes CheckPoint: Checkpoint is an event that occurs whenever the log switches Signals the DBWR to flush the data from DBBC to DF Updates SCN and LSN Stages of Redolog Groups: Active – written over, ready to move to archive Inactive – ready to overwrite , information passed to archive Current – currently writing Unused - Freshly created group

Background Processes Members Status: 1.Stale – Half- 2. Blank - 3.delete - 4. invalid – written and Currently dropped corrupted, switched log Writing members inaccessible

System Global Area (SGA) SGA is a shared memory region in an SGA It contains various components such Oracle database that is used to store as database buffers, shared pool, redo data and control information for the log buffer, and more. database instance. It is managed by the Oracle instance. The SGA is a critical component of the Oracle database architecture

SGA SGA_MAX_SIZE v$buffer_pool Fixed SGA – Shared Pool, DBBC ,RLBC Dynamic SGA – Changes made while DB is up DB_CACHE_SIZE LOG_BUFFER SHARED_POOL_SIZE LARGE_POOL_SIZE JAVA_POOL_SIZE

Shared Pool • This is used for objects shared globally such as reusable execution plans, pl/sql packages, procedures, functions, cursor information etc., • LRU Algorithm : As the Cache fills, less recently used execution plans and parse trees are removed from LC to make room for the new entries Shared Pool Contains : Library Cache and Data Dictionary Cache

Library Cache • Stores information about the shared sql and pl/sql statements Enables the sharing of commonly used statements LRU Algorithm Shared SQL Shared PL/SQL Shared_pool_size • Memory is allocated when a statement is parsed or a program unit is called.

Shared SQL • Stores and Shares the execution plan and parse trees for sql statements run against the database • If second time an sql statement is run, it can take parse information already available is reused • The text,schema and bind variables must be same to ensure that sql statements share the shared SQL Area Shared PL/SQL • Stores and shares the most recently used pl/sql statements. Parsed and compiled programs with units and Procedures are stored in this area

Data Dictionary Cache • Data Dictionary Cache is also called as Row Cache • Most recently used data definitions in the Database Includes information about database files, users, privileges, tables, indexes, columns and other database objects To resolve object names and validate access the server process looks at data dictionary Information about user data,data file names,segment names,extent location,table description.

SUMMARY Understanding the Knowledge of Learners will be purpose, DBA Oracle DBA equipped with the Tasks , and ,BGA,SGA ,etc skills and components of a knowledge of RBMS RBMS.