oraclelab1 - DEVDB (Running in OS file system)
oraclelab2 - TESTDB (Running in OS ASM file system)
Oracle Architecture:
===================================
Instance + Database
Instance = Logical structure (Memory + BG Process)
Database = Physical Structure (Datafiles)(OS FS / ASM FS)
Instance: Logical structure (Memory + BG Process)
=========
Memory + BG Process
Memory = SGA + PGA
BG Process = Mandatory BG Process + Non-Mandatory BG Process
Memory = SGA + PGA
====================================
SGA
====
System / Shared Global Area
Shared Pool, Large Pool, Stream Pool, JAVA Pool, Data Buffer Cache, Log buffer cache
SGA_TARGET=3GB
PGA
=====
Program / Private Global Area
stack space, Cursor Area, sort Area, temp Area, bitmap/bittree Area, UGA
PGA_AGGREGATE_LIMIT=2GB
./dbca
- DEVDB
- TESTDB
Memory management:
AMM
+++++
MEMORY_TARGET=10G
- dynamically SGA & PAG memory component will be assigned
SGA=8G/6G
PGA=2G/4G
ASMM (Default)
+++++
SGA_TARGET=3GB
- dynamically all memory component will be assigned
PGA_AGGREGATE_LIMIT=2GB
- dynamically all memory component will be assigned
BG Process = Mandatory BG Process + Non-Mandatory BG Process
==========================================================
Mandatory BG Process
SMOM
PMON
DBWR
LGWR
CKPT
Non-Mandatory BG Process
Arch
Reco (Distributed Tx, In-doubt transaction)
Jq
MMON (AMM, ASMM)
MMNL (AWR)
etc....
clint C1:
=====================
select * from EMP;
1 min - Query exaction took
Parsing - 30 sec
Execution - 30 sec
Parsing: - query running slow
- syntax check
- symmetic check
- permission check
This query converted as SQLID
select * from EMP; -> 1a1a1a
Why query went for parsing?
- Query executed for first time
Hard Parsing:
select * from EMP;
- Oracle will create SQL ID (1a1a1a) for these SQL queries
Memory > SGA > Shared Pool
- Library cache
- Will holds SQL ID
- Data dictionary cache
- Will hold metadata or table (EMP) structure information
Cache miss?
- User requested data is not in the database buffer cache
- Then SP will go to datafile and get the EMP data and put into buffer cache
clint C2:
=====================
15 sec - Query exaction took
Soft parsing - making use of already generated SQL ID
Cache Hit: Data is already available on BC
LGWR - log writer
- writes data from Log buffer cache to redo log file
- 1/3 full of log buffer cache
- every 3 sec
- commit happens
DBWR - DB writer
- writes data from DB buffer cache to datafiles
- 1/3 full of DB buffer cache
- whenever checkpoint happens
Dirty Buffer - New data or modified block in the buffer cache
SMON - System monitor
- recovering incase of instance crash
- roll forward committed Tx by reading redo logs
- rollback uncommitted Tx by reading redo logs
PMON - Process monitor
- Keep a track of all user session whenever any user session broke due to network issue
- Any user session reached ideal session timeout (ideal session timeout = 2hours)
- PMON will cleanup all resource help by those user session like any lock or any metadata
CKPT - ckeckpoint
- Maintain database consistency state for recovery
- Main job of CKPT is to update latest SCN number into Datafiles and Controfliles header
- SCN number - system change number
- every transaction in my database is will generate these SCNs
SCN - system change number
Instance = Logical structure (Memory + BG Process)
BG Process = Mandatory BG Process + Non-Mandatory BG Process
==========================================================
Mandatory BG Process
SMOM
PMON
DBWR
LGWR
CKPT
Non-Mandatory BG Process
Arch
Reco (Distributed Tx, In-doubt transaction)
Jq
MMON (AMM, ASMM)
MMNL (AWR)
etc....
Database = Physical Structure (Datafiles)(OS FS / ASM FS)
How select query works?
- for the first time
- second time
How select insert works?
How select update works?
How select delete works?
Instance = Logical structure (Memory + BG Process)
Database = Physical Structure (Datafiles)(OS FS / ASM FS)
Database = Physical Structure (Datafiles/storage files) (OS FS/ ASM FS)
DEVDB - OS FS
TESTDB - ASM FS
- SYSTEM Datafile - Stores all data dictionary and Dynamic perf view or base table / metadata
- SYSAUX Datafile - Database Stats
- UNDO Datafile - Old values / Old records / Previous value (undo_rentention=15min)
- TEMP Datafile - Temp operation
- USERS Datafile - Actual End user data
select name from v$datafile;
select file_name from dba_data_files;
select NAME from v$tempfile;
select FILE_NAME from dba_temp_files;
Instance + Database
====================
Instance = Memory + BG Process
Memory= SGA+PGA
BG Process=mandatory+Non-Mandatory
Database=datafile
1. Instance will start
- Along with memory and BG process will get started
2. That instance will be mapped to database
pfile / spfile:
======================
- This is the fixt file will be used to start my database instance
- Instance structure
pfile - parameter file
spfile - server parameter file
pfile - txt file
spfile - binary file
- Database instance properties we are going to define in spfile/pfile
- Its key value pair
database_name=DEVDB
instance_name=DEVDB
SGA=3G
PGA=2G
controlfiles
many parameter we defined
supported 450+ parameter
spfile / pfile is a key value pair file which has
variable and value assigned to it
database_name=DEVDB
instance_name=DEVDB
SGA=3G
PGA=2G
./dbca
TESTDB
DEVDB
how to check spfile?
How to check whether DB is running with pfile / spfile?
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/19.0.0
.0/dbhome_1/dbs/spfileDEVDB.or
a
SQL>
/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/spfileDEVDB.ora
/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initDEVDB.ora
You can create pfile from Spfile
You can create Spfile from pfile
v$parameter
Oracle recommends to run database / database instance using Spfile
Why?
- We can make use of dynamic parameters
- Wd can change these dynamic parameter without bouncing or restart database
When we start DB
-> It will look for Spfile
-> if not found it will look for pfile
-> if not found database instance will not start
controlfile:
========================
- binary file
- end with .ctl
[oracle@oraclelab1 dbs]$ cat initDEVDB.ora
*.control_files=
'/u01/app/oracle/oradata/DEVDB/controlfile/o1_mf_ntwl2o59_.ctl',
'/u01/app/oracle/fast_recovery_area/DEVDB/controlfil
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DEVDB/controlfile/o1_mf_ntwl2o59_.ctl
/u01/app/oracle/fast_recovery_area/DEVDB/controlfile/o1_mf_ntwl2o6o_.ctl
multiplexing of CF - duplicate copies of CF
We call it has heart of your database.....
Controlfile is binary file
Why we call it has heart of my database?
Because CF knows everything about my database
- DB name
- Instance name
- when db created
- Oracle home
- SCN number
- DB ID
- archive log information
- How many archives are generated
- Where are my datafile
- Where my redo logs
everything.....
Datafiles:
==================================
- binary file
- ends with .dbf
How your instance/database know where are datafile/redo log?
- Controlfile will give those information
1. End user data will be stored in these datafile
2. Core based tables or metadata tables will be stored on few datafiles
- SYSTEM Datafile - Stores all data dictionary and Dynamic perf view or base table / metadata
- SYSAUX Datafile - Database Stats
- UNDO Datafile - Old values / Old records / Previous value
- TEMP Datafile - Temp operation
- USERS Datafile - Actual End user data
select name from v$datafile;
select name from v$tempfile;
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_ntwkz4g1_.dbf
/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_ntwl07kk_.dbf
/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_ntwl10n5_.dbf
/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_ntwl11pb_.dbf
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_temp_ntwl2w50_.tmp
Redo log:
===========
- binary file
- ends with .log
How your instance/database know where are redo log file?
- Controlfile will give those information
- Transaction information
- end user transaction information which was written into redo buffer cache, These transaction from redo buffer cache will be written these redo log with help LGWR BG process
select member from v$logfile;
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DEVDB/onlinelog/o1_mf_3_ntwl2qf6_.log
/u01/app/oracle/fast_recovery_area/DEVDB/onlinelog/o1_mf_3_ntwl2r8g_.log
/u01/app/oracle/oradata/DEVDB/onlinelog/o1_mf_2_ntwl2qdm_.log
/u01/app/oracle/fast_recovery_area/DEVDB/onlinelog/o1_mf_2_ntwl2qsq_.log
/u01/app/oracle/oradata/DEVDB/onlinelog/o1_mf_1_ntwl2qct_.log
/u01/app/oracle/fast_recovery_area/DEVDB/onlinelog/o1_mf_1_ntwl2ql5_.log
Multiplexing of Redo log: - duplicate copy of redo logs
Archive logs:
==============
- Binary file
- Ends with .arc
- These are copy of your redo logs
- Arch background process will generates these arch logs
How to find whether DB is in archive log mode or not?
archive log list
show parameter recovery
How to enable to disable archive log mode?
Enable:
=============
1. shutdown DB
SQL> shut immediate;
2. start in mount mode
SQL> startup mount;
3. enable archive log mode
SQL> alter database archivelog;
4. open database
SQL> alter database open;
disable:
=================
1. shutdown DB
SQL> shut immediate;
2. start in mount mode
SQL> startup mount;
3. disable archive log mode
SQL> alter database noarchivelog;
4. open database
SQL> alter database open;
diagnostic file
======================
DBA maintenance or administration or health check
- alert log (regular used)
- trace file
- trm file
- incident file
- core dump file
- audit file
alert log:
+++++++++++++++
[oracle@oraclelab1 dbs]$ locate alert_DEVDB.log
/u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/alert_DEVDB.log
v$diag_info
select NAME,VALUE from v$diag_info;
Diag Trace = /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace
locate alert_DEVDB.log
tail -f /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/alert_DEVDB.log
2026-03-16T10:51:47.022511+05:30
PMON (ospid: ): terminating the instance due to ORA error
2026-03-16T10:51:47.022662+05:30
Cause - 'Instance is being terminated due to fatal process death (pid: 24, ospid: 11922, SMON)'
2026-03-16T10:51:47.024035+05:30
System state dump requested by (instance=1, osid=11877 (PMON)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/DEVDB_diag_11897.trc
2026-03-16T10:51:47.383565+05:30
Dumping diagnostic data in directory=[cdmp_20260316105147], requested by (instance=1, osid=11877 (PMON)), summary=[abnormal instance termination].
2026-03-16T10:51:48.525835+05:30
Instance terminated by PMON, pid = 11877
trace file & trm file:
+++++++++++++++++++++++
trace - xml
trm - binary
Diag Trace = /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace
Password File:
==============
Binary file
location of password file:
Oracle_Home/dbs/orapwDEVDB
/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapwDEVDB
Why password file?
- sysdba remote login authentication
- DR/Standby database sync with prod
sqlplus / as sysdba >>>>>>>>>>>>>>>>>>>>> OS authentication
sqlplus sys/Mallik123@DEVDB as sysdba >>>>>>>>> Password file authentication
sqlplus mallik/mallik@DEVDB >>>>>>>>> Database authentication
In case password file missing or corrupted?
orapwd file=orapwDEVDB
1. Spfile / pfile missing or corrupted?
- No impact on my database
- We will not be able to start DB next time
2. CF missing or corrupted?
- DB will crash
3. datafile missing or corrupted?
- DB may or may not crash
- SYSTEM Datafile - Stores all data dictionary and Dynamic perf view or base table / metadata
- SYSAUX Datafile - Database Stats
- UNDO Datafile - Old values / Old records / Previous value (undo_rentention=15min)
- TEMP Datafile - Temp operation
- USERS Datafile - Actual End user data
4. Redo missing or corrupted?
- DB may or may not crash
5. Archive missing or corrupted?
- No impact on my database
6. diagnostic file missing or corrupted?
DBA maintenance or administration or health check
- alert log (regular used)
- trace file
- trm file
- incident file
- core dump file
- audit file
- No impact on my database
7. password file missing or corrupted?
- No impact on my database
Regards,
Mallikarjun / Vismo Technologies
WhatsApp: +91 9880616848 / +91 9036478079
Cell: +91 9880616848 / +91 9036478079
Email: mallikarjun.ramadurg@gmail.com / vismotechnologies@gmail.com
