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
