🚀 Oracle DBA Workshop | 3 Hours Live Session | Oracle Architecture

1. Other DBs Vs Oracle 

Other DBs

- DB2 

- SAP 

- MSSQL 

- PostgreSQL 

- MongoDB 

Other DBs

Instance 

- Single instance mapped with multiple databases 

- From single Instance we can mange multiple DBs 

Oracle DBs 

Instance 

- Single instance mapped with single databases

Oracle Multitenant Architecture (12c) 

- CDB 

- PDB 

Normal DB architecture 

7/8i/9i/10g/11g

Multitenant DB Architecture 

12c 

12c/18c/19c

- Option 1 - Normal DB architecture

- Option 2 - Multitenant DB Architecture 

21c/23ai/26ai 

- Option 2 - Multitenant DB Architecture 

2. Normal DB architecture vs Multiple Architecture in Oracle 

3. Types Oracle DBs 

- Standalone 

- Standalone in OS files system (OS files system / NFS / SAN / NAS etc )

- Standalone in ASM files system (OS files system / NFS / SAN / NAS etc )

- RAC 

- RAC database (ASM Storage / NFS storage)

- RAC Onenode database (ASM Storage / NFS storage)

Database Architecture 

++++++++++++++++++++++

Can you brief of database architecture or Oracle architecture 

++++++++++++++++++++++

Oracle Architecture = Instance + Database 

Instance = Logical Structure 

Database = Physical Structure 

Instance = Logical Structure 

- Memory + BG Process 

Memory = SGA + PGA 

BG Process = Mandatory BG Process + Non-Mandatory BG Process

SGA - system / shgared global area 

- DB buffer cache (keep pool, recycle pool, nk buffer pool) 

- log buffer cache 

- shared pool (library cache, dictionary cache) 

- large pool (IO activity backup and recovery)

- java pool (Application JVM framwork)

- stream pool (GG or table replication)

PGA - private / program global area 

- stack space 

- bitmap / bittree area 

- sort area 

- cursor area 

- UGA 

- temp area 

Mandatory BG Process

- smon 

- pmon 

- ckpt 

- LGWR

- DBWR 

Non-Mandatory BG Process

- arch 

- MOMN 

- MMNL 

- RECO 

- etc 

Database = Physical Structure 

- Datafile / Storage file / End user data / Backed files

- System 

- Sysaux 

- User 

- Temp 

- Undo 

oraclelab1

- Instance - DEVDB 

- Database - DEVDB 

How much max PGA I can be allocated to a user?

pga_aggregate_limit          big integer 2338M (max)

pga_aggregate_target      big integer 1169M

How much max SGA can be allocated to my database? 

sga_target          big integer 3520M

sga_max_size      big integer 10000M(10GB) 

Client 1:

- Parsing happened (hard parshing) (2 mins)

- Execution (Cache miss) (2 mins)

- Fetching the result and give it to end user (1 mins)

- Total time = 5 mins 

Client 2:

- Parsing did happened - (soft parsing) (0 mins)

- Execution (Cache hit) (0 mins)

- Fetching the result and give it to end user (1 mins)

- Total time = 1 mins 

Database Architecture 

Instance + Database

Memory + BG Process Dataafile 

create user apps identified by apps;

APPS - assigned with Tablespace -> USERS 

Manage / maintain / backup and recovery / Performance 

1. Manage and Maintain 

2. Backup and recovery

RMAN tool

- take backup of datafile 

- take backup of all logs file 

use case 1 - In case any crash of single DF or entire database we should be able recover it 

use case 2 - Build DEV/TEST/UAT database using PROD backup

Oracle software give this RMAN tool 

Sunday - L0

Weekdays (Mon to Sat) - L1 (cumm + diff)

Regularlly take archive/transaction log backup 

3. Performance 

My query used to run 10 mins but last 1 week it is taking 1 hours?

- is this Server side issue 

- top, vmstat, iostat, free, sar 

- OEM, Nagios (server monitroing tools)

- OS watcher, Exa watcher 

- is this Ddatabase side issue 

- Is my database is upto date patch level or at leaste n-1

(Qaurtley patches Jan, Apr, Jul & Oct)

- What are the table involved on that query and what is the stats status 

- Gather stats on table 

- What are all the index involved on that query and what is the stats status 

- Gather stats on index 

- What are all the index involved on that query and what is the index status 

- rebuild index (online / offline)

- What are all the table involved on that query and what is the fragmentaion on those table

- table move 

- table shrink 

- Export & Import

- What is the execution plan / wrong execution plan 

- Based line 

Plan P1 - 10 min 

Plan P2 - 30 min 

Plan P3 - 60 min 

- Then we need to force optimizer to user Plan P1 via SQL profiling 

- Even after SQL profiling still query running with Plan P3 and taking 1 hour then engage oracle support 

dynamic perf view 

- v$sql 

- v$logops 

- v$session 

AWR report

- ASH report 

- ADDM resport 

- SQL tuning report