🎉 25,000 Subscribers on YouTube! 🎉

🎉 25,000 Subscribers on YouTube! 🎉
Today, I'm incredibly grateful to celebrate a milestone that once felt like a distant dream—25,000 amazing subscribers on my YouTube channel! 🙏❤️

This achievement isn't just a number. It's a community of learners, professionals, students, and technology enthusiasts who have trusted me to be part of their learning journey.

When I started creating content, my goal was simple:

Make Oracle Database concepts practical, easy to understand, and accessible to everyone.

Over the years, we've explored together:
✅ Oracle Database Administration (DBA)
✅ Oracle RAC
✅ Data Guard
✅ RMAN Backup & Recovery
✅ Performance Tuning
✅ ASM
✅ High Availability & Disaster Recovery
✅ Real-world Production Scenarios
✅ Interview Preparation
✅ Career Guidance
✅ Live Training Sessions
…and much more.

Every subscriber, every view, every comment, every like, and every question has motivated me to create better content and continue sharing knowledge.

A heartfelt THANK YOU to:
- My YouTube subscribers for your continuous support.
- My LinkedIn network for encouraging and sharing my content.
- My students from around the world who trusted me with their learning journey.
- My colleagues, mentors, friends, and family for believing in me throughout this journey.

This milestone belongs to all of you.
The journey doesn't stop here.

Over the coming months, I plan to bring even more value through:
🚀 Advanced Oracle Database content
🚀 Oracle 23ai/26ai and modern database technologies
🚀 More production troubleshooting videos
🚀 Hands-on labs and demonstrations
🚀 Certification guidance
🚀 Career-focused interview preparation
🚀 Live Q&A sessions and technical deep dives

My mission remains unchanged:

To help thousands of database professionals build stronger careers through quality technical education.

Thank you for being part of this incredible journey. Here's to the next milestone—50K subscribers! 🎯

If my content has helped you in your career, I'd love to hear your story in the comments. Your feedback inspires me to keep creating.

📺 YouTube: Mallik034 – Vismo Technologies

#25KSubscribers #YouTube #Milestone #ThankYou #Oracle #OracleDBA #OracleRAC #DataGuard #RMAN #Database #DatabaseAdministration #OracleACE #Learning #KnowledgeSharing #CareerGrowth #Technology #VismoTechnologies #Mallik034 #ContinuousLearning

🚀 Oracle RAC & ASM | DB File System, ASM Architecture, External, Normal, High Redundancy

RAC DB file system structure 

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

1. Spfile ---- will be in ASM diskgroup and shared by both DB instance 

srvctl config database -d DEVDB 

SQL> show paremeter spfile 

Spfile: +DATA/DEVDB/PARAMETERFILE/spfile.268.1228381485

pfile ---- will be in OS storage and not-shared by both DB instance 

node1 - initDEVDB1.ora - /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs

node2 - initDEVDB2.ora - /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs

create pfile from spfile;

create spfile from pfile;

2. Controlfile ---- will be in ASM diskgroup and shared by both DB instance  

select name from v$controlfile;

SQL> select name from v$controlfile;

NAME

--------------------------------------------------------------------------------

+DATA/DEVDB/CONTROLFILE/current.261.1228381237

+RECO/DEVDB/CONTROLFILE/current.256.1228381237

3. Datafiles: (system, sysaux, undo, temp, users)

---- will be in ASM diskgroup

---- Some datafiles shared by both DB instance

---- Some datafiles non-shared by both DB instance

system, sysaux, users, temp ---- will be in ASM diskgroup and shared by both DB instance

undo --------- will be in ASM diskgroup but not shared by both DB instance

select name from v$datafile;

select name from v$tempfile;

SQL> select name from v$controlfile;

NAME

--------------------------------------------------------------------------------

+DATA/DEVDB/CONTROLFILE/current.261.1228381237

+RECO/DEVDB/CONTROLFILE/current.256.1228381237

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

+DATA/DEVDB/DATAFILE/system.257.1228381099

+DATA/DEVDB/DATAFILE/sysaux.258.1228381145

+DATA/DEVDB/DATAFILE/undotbs1.259.1228381169

+DATA/DEVDB/DATAFILE/undotbs2.265.1228381415

+DATA/DEVDB/DATAFILE/users.260.1228381171

SQL> select name from v$tempfile;

NAME

--------------------------------------------------------------------------------

+DATA/DEVDB/TEMPFILE/temp.264.1228381245

SQL>

4. redolog file

--------- will be in ASM diskgroup but not shared by both DB instance

select group#, member from v$logfile;

SQL> select group#, member from v$logfile;

    GROUP# MEMBER

---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

         2 +DATA/DEVDB/ONLINELOG/group_2.263.1228381241

         2 +RECO/DEVDB/ONLINELOG/group_2.258.1228381241

         1 +DATA/DEVDB/ONLINELOG/group_1.262.1228381241

         1 +RECO/DEVDB/ONLINELOG/group_1.257.1228381241

         3 +DATA/DEVDB/ONLINELOG/group_3.266.1228381483

         3 +RECO/DEVDB/ONLINELOG/group_3.259.1228381483

         4 +DATA/DEVDB/ONLINELOG/group_4.267.1228381483

         4 +RECO/DEVDB/ONLINELOG/group_4.260.1228381483

8 rows selected.

SQL> select GROUP#,THREAD# from v$log;

    GROUP#    THREAD#

---------- ----------

         1          1

         2          1

         3          2

         4          2

5. Archive logs 

--------- will be in ASM diskgroup but not shared by both DB instance

DEVDB1 DEDVB2 

1 - thread #1 3 - thread #2

2 - thread #1 4 - thread #2

archive log list

show parameter recovery 

convert database from No Archive Mode to Archive Mode - MOUNT MODE

convert database from Archive Mode to No Archive Mode - MOUNT MODE

srvctl status database -d DEVDB

srvctl stop database -d DEVDB

srvctl status database -d DEVDB

sqlplus / as sysdba >>> DEVDB1/DEVDB2

startup mount;

alter database archivelog; 

--- alter database noarchivelog; 

shut immediate

srvctl start database -d DEVDB

srvctl status database -d DEVDB

6. password file 

--------- will be in ASM diskgroup and shared by both DB instance

srvctl config database -d DEVDB 

Password file: +DATA/DEVDB/PASSWORD/pwddevdb.256.1228381081

passwordfile ---- will be in OS storage and not-shared by both DB instance 

node1 - orapwDEVDB1 - /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs

node2 - orapwDEVDB2 - /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs

- sysdba remote authentication 

- DR/stsandby sync 

https://mallik034.blogspot.com/2022/08/how-to-copy-password-file-from-asm-to-fs.html

pwget --dbuniquename DEVDB

pwcopy '+DATA/DEVDB/PASSWORD/pwddevdb.256.1228381081' '/tmp/orapwDEVDB'

7. diagnostic files

--------- will be in local storage and not shared by both DB instance

$ORACLE_BASE/diag -> /u01/app/oracle/diag 

rdbms - DB diagnostic files

/u01/app/oracle/diag/rdbms/devdb/DEVDB1/trace

/u01/app/oracle/diag/rdbms/devdb/DEVDB2/trace

alert log 

trace file 

trm file 

incident files 

core-dump files 

autifile files

Spfile & pfile 

Controlfile 

datafile 

redo logs 

archive logs 

password file 

diagnostic file 

ASM architecture 

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

Types of database 

standalone 

- standalone without ASM (NFS/SAN/NAS)

- standalone with ASM (ASM)

RAC

- RAC database (ASM)

- RAC onenode database (ASM) 

1. setup 2 servers  (oraclelab1 & oraclelab2)

2. Download 19c GI software and install on node1, as part of installation it will copy to other node2 (./gridSetp.sh)

- +ASM1 & +ASM2 + ASM storage 

- Clusterware 

3. Download 19c DB software and install on node1, as part of installation it will copy to other node2 (./runInstaller)

- Oracle Home (RAC Oracle Home)

4. create database - DEVDB (./dbca) (NFS/ASM) 

- DEVDB1 & DEVDB2 

Why oracle recommend to run database on ASM?

1. Oracle Restart capability

2. Mirroring & Stripping 

3. High speed Read and Write 

4. ACFS (similar to NFS)  

Prio to 10g:

================

cluster software installation 

node1: node2:

/u01/cluster /u01/cluster 

asm software installation 

node1: node2:

/u01/asm /u01/asm 

oracle software installation 

node1: node2:

/u01/dbhome_1 /u01/dbhome_1 

In 10g and post 

GI software = cluster software + ASM software 

GI software Installation (cluster + ASM)

node1: node2:

/u01/grid /u01/grid 

oracle software installation 

node1: node2:

/u01/dbhome_1 /u01/dbhome_1 

GI -> Cluster + ASM 

ASM

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

- ASM Instance 

- ASM Storage 

Database Architecture = DEVDB 

==================================

oracle architecture = Instance + Database (datafiles / storage files) 

Instance = logical structure (Memory + BG process)

Memory = SGA + PGA 

BG process = Mandatory BG process (SMON, PMON, DBWR, LGWR, CKPT) + Non-Mandatory BG Process (Arc, MMON, JobQ etc) 

Database (datafiles / storage files)  = physical structure 

- system , sysaux, undo, temp, users 

ASM Instance only Architecture:

==================================

- core fundamental understanding of oracle architecture 

- Instance only architecture 

Instance = logical structure (Memory + BG process)

Memory = PGA + SGA (shared pool, large pool, BBC, LBC, Java Pool)  

BG process = Mandatory BG process (SMON, PMON, DBWR, LGWR, CKPT) + Non-Mandatory BG Process

ASM specific memory + ASM specific BG process 

SGA - ASM cache

- It will keep track of all ASM diskgroups and ASM disks  - metadata information 

RBAL (ARBn) - rebalance 

GMON - disk level operation (disk add/drop)

MARK - allocating AUs 

PZ9n - gv$

Onnn - ASM to ASM exchanging message 

ASMB - bridge between ASM & database 

ora_asmb_DEVDB1

asm_asmb_+ASM1

ora_asmb_DEVDB2

asm_asmb_+ASM2

Database:

==========

select instance_name, status from v$instance;

select name, open_mode from v$database;

v$instance v$database 

shutdown NA NA

nomount STARTED NA

mount MOUNTED MOUNTED

open OPEN READ WRITE

ASM:

==========

select instance_name, status from v$instance;

select name, open_mode from v$database;

v$instance v$database 

shutdown NA NA

nomount STARTED NA

mount NA NA

open NA NA

DB: DEVDB

===========

srvctl stop database -d DEVDB -o immediate/abort/normal/transactional

srvctl start database -d DEVDB -o nomount/mount/open 

srvctl status database -d DEVDB 

srvctl config database -d DEVDB 

srvctl stop database -d DEVDB

srvctl start database -d DEVDB

On all RAC nodes

sqlplus / as sysdba

SQL> shutdown immediate/abort/normal/transactional

SQL> startup nomount/mount/open;

ASM: +ASM1/+ASM2 

=========

srvctl stop asm -o immediate/abort/normal/transactional (abort) (srvctl stop asm)

srvctl start asm -o nomount/mount/open (srvctl start asm)

srvctl status asm

srvctl config asm

srvctl stop asm

srvctl start asm

On all RAC nodes

sqlplus / as sysasm

SQL> shutdown immediate/abort/normal/transactional (abort)

SQL> startup nomount/mount/open;

Spfile & pfile - Yes

Controlfile - NA

datafile - NA

redo logs  - NA

archive logs - NA

password file - YES

diagnostic file - YES

1. spfile  ---- will be in ASM diskgroup and shared by both ASM instance 

srvctl config asm 

SQL> show parameter spfile

SQL> show parameter spfile

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      +OCR/oracle-clu/ASMPARAMETERFI

                                                 LE/registry.253.1228302843

+OCR/oracle-clu/ASMPARAMETERFILE/registry.253.1228302843

pfile ---- will be in OS storage and not-shared by both ASM instance 

node1 - init+ASM1.ora - /u01/app/19.0.0.0/grid/dbs

node2 - init+ASM2.ora - /u01/app/19.0.0.0/grid/dbs

create pfile from spfile;

create spfile from pfile;

2. password file 

--------- will be in ASM diskgroup and shared by both ASM instance

srvctl config asm

Password file: +OCR/orapwASM

--------- will be in local storage and not shared by both DB instance

node1 - $GRID_HOME/dbs/orapw+ASM1

node2 - $GRID_HOME/dbs/orapw+ASM2

pwcopy '+OCR/orapwASM' '/tmp/orapwASM'

pwcopy '/tmp/orapw+ASM' '+OCR/orapwASM'

If password is missing - Then recreate password file 

orapwd file='+OCR/orapwASM' password=<password>

3. diagnostic files

--------- will be in local storage and not shared by both DB instance

$ORACLE_BASE/diag -> /u01/app/oracle/diag 

asm - DB diagnostic files

/u01/app/oracle/diag/asm/+asm/+ASM1/trace

/u01/app/oracle/diag/asm/+asm/+ASM2/trace

alert log 

trace file 

trm file 

incident files 

core-dump files 

autifile files

ASM diskgroup administration 

- create DG

- modify DG (add / drop)

- Drop DG 

mirroring / redundancy 

External 

Normal

High 

striping 

ASM storage:

============

Database logical structure:

======================================

Physical - DBA can see datafiles (OS/NFS/ASM/SAN/NAS)

-> tablespace 

-> segments 

-> extents 

-> datablock (these datablock are created with 8k size at the time of database creation)

logical understanding for ASM:

===============================

ASM file -> made up of ASM extents -> These ASM extents are made up of ASM AU

These AU size defined at the time of Diskgroup creation (These AU size is smallest unit of ASM storage) 

ASM file kept under ASM diskgroup -> ASM diskgroups are nothing but ASM storages (OCR, DATA, RECO) 

These diskgroups are made-up using ASM disks (ASMDISK1, ASMDISK2, ASMDISK3) 

mirroring / redundancy 

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

Mirroring & Stripping 

=======================

Mirroring ( Redundancy or Fault tolerance)

- External (1 original + No Duplicate copy)

- Normal (1 original + 1 Duplicate)

- High (1 original + 2 Duplicate)

We DBA can decide at the time of diskgroup creation 

DATA, RECO, OCR - we create on Day_2 as external redundancy (./asmca - GUI)

with default AU - 4MB 

We can create these diskgroups manually using sqlplus / as sysasm command line 

- create DG

- modify DG (add / drop)

- Drop DG 

1. CREATE DISKGROUP

========================

- external redundancy

- normal redundancy 

- high redundancy  

brw-rw----. 1 root disk 8,  64 Mar 25 22:50 sde

brw-rw----. 1 root disk 8,  80 Mar 25 22:50 sdf

brw-rw----. 1 root disk 8,  96 Mar 25 22:50 sdg

brw-rw----. 1 root disk 8, 112 Mar 25 22:50 sdh

brw-rw----. 1 root disk 8, 128 Mar 25 22:50 sdi

brw-rw----. 1 root disk 8, 144 Mar 25 22:50 sdj

brw-rw----. 1 root disk 8, 160 Mar 25 22:50 sdk

brw-rw----. 1 root disk 8, 176 Mar 25 22:50 sdl

brw-rw----. 1 root disk 8, 192 Mar 25 22:50 sdm

brw-rw----. 1 root disk 8, 208 Mar 25 22:50 sdn

fdisk /dev/sde

fdisk /dev/sdf

fdisk /dev/sdg

fdisk /dev/sdh

fdisk /dev/sdi

fdisk /dev/sdj

fdisk /dev/sdk

fdisk /dev/sdl

fdisk /dev/sdm

fdisk /dev/sdn

Command (m for help): n

Command (m for help): w

oracleasm createdisk D1 /dev/sde1

oracleasm createdisk D2 /dev/sdf1

oracleasm createdisk D3 /dev/sdg1

oracleasm createdisk D4 /dev/sdh1

oracleasm createdisk D5 /dev/sdi1

oracleasm createdisk D6 /dev/sdj1

oracleasm createdisk D7 /dev/sdk1

oracleasm createdisk D8 /dev/sdl1

oracleasm createdisk D9 /dev/sdm1

oracleasm createdisk D10 /dev/sdn1

External - No copy (only original copy) (min 1 Fail groups)

Normal - 1 Original + 1 Duplicate copy (min 2 Fail groups)

High - 1 Original + 2 Duplicate copy (min 3 Fail groups)

create diskgroup

- GUI - ./asmca (/u01/app/19.0.0.0/grid/bin) - +DATA +RECO

- 4MB AU 

- command line in ASM 

- 1MB/2MB/4MB AU

CREATE DISKGROUP dgroupA NORMAL REDUNDANCY 

DISK '/dev/oracleasm/disks/D1'; -------------- fail 

CREATE DISKGROUP dgroupA HIGH REDUNDANCY  

DISK '/dev/oracleasm/disks/D1',

'/dev/oracleasm/disks/D2'; -------------- fail

CREATE DISKGROUP dgroupA NORMAL REDUNDANCY 

DISK '/dev/oracleasm/disks/D1',

'/dev/oracleasm/disks/D2';

CREATE DISKGROUP dgroupB HIGH REDUNDANCY  

DISK '/dev/oracleasm/disks/D3',

'/dev/oracleasm/disks/D4',

'/dev/oracleasm/disks/D5';

CREATE DISKGROUP dgroupC EXTERNAL REDUNDANCY  

DISK '/dev/oracleasm/disks/D6';

CREATE DISKGROUP dgroupC EXTERNAL REDUNDANCY  

DISK '/dev/oracleasm/disks/D6',

   '/dev/oracleasm/disks/D7';

CREATE DISKGROUP dgroupA NORMAL REDUNDANCY 

DISK '/dev/oracleasm/disks/D1',

'/dev/oracleasm/disks/D2';

CREATE DISKGROUP dgroupA NORMAL REDUNDANCY 

FAILGROUP FG1

DISK '/dev/oracleasm/disks/D1'

FAILGROUP FG2 

DISK '/dev/oracleasm/disks/D2';

CREATE DISKGROUP dgroupA NORMAL REDUNDANCY 

FAILGROUP FG1

DISK '/dev/oracleasm/disks/D1' NAME DGA_D1

FAILGROUP FG2 

DISK '/dev/oracleasm/disks/D2' NAME DGA_D2;

CREATE DISKGROUP dgroupA NORMAL REDUNDANCY AU_SIZE=1/2/4/8

FAILGROUP FG1

DISK '/dev/oracleasm/disks/D1' NAME DGA_D1

FAILGROUP FG2 

DISK '/dev/oracleasm/disks/D2' NAME DGA_D2;

CREATE DISKGROUP dgroupB HIGH REDUNDANCY  

DISK '/dev/oracleasm/disks/D3',

'/dev/oracleasm/disks/D4',

'/dev/oracleasm/disks/D5';

CREATE DISKGROUP dgroupB HIGH REDUNDANCY 

FAILGROUP FG1

DISK '/dev/oracleasm/disks/D3' NAME DGB_D3

FAILGROUP FG2 

DISK '/dev/oracleasm/disks/D4' NAME DGB_D4

FAILGROUP FG3 

DISK '/dev/oracleasm/disks/D5' NAME DGB_D5;

CREATE DISKGROUP dgroupB HIGH REDUNDANCY AU_SIZE=1/2/4/8

FAILGROUP FG1

DISK '/dev/oracleasm/disks/D3' NAME DGB_D3

FAILGROUP FG2 

DISK '/dev/oracleasm/disks/D4' NAME DGB_D4

FAILGROUP FG3 

DISK '/dev/oracleasm/disks/D5' NAME DGB_D5;

DB:

===

v$ - dynamic perf views (coming from memory - SGA) 

dba_ - data dictionary view (coming from datafiles system) 

ASM: 

=====

v$ - dynamic perf views (ASM will not support dba_)

v$asm_diskgroup 

v$asm_disk

2. ALTER DISKGROUP

========================

a. When diskgroup is full - We add new disks (Multiple of 2 disks NR, Multiple of 3 disks HR) 

b. When a disk is bad or poor performance - we replace the disks 

- add disks from DG

- drop disks from DG 

dgroupA - NR

dgroupB - HR

dgroupC - ER

ALTER DISKGROUP dgroupA ADD DISK

  '/dev/oracleasm/disks/D8',

  '/dev/oracleasm/disks/D9';

ALTER DISKGROUP dgroupB ADD DISK

  '/dev/oracleasm/disks/D8',

  '/dev/oracleasm/disks/D9',

  '/dev/oracleasm/disks/D10';

ALTER DISKGROUP dgroupC ADD DISK

  '/dev/oracleasm/disks/D8';

dgroupA

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

           4 DGA_D1                         FG1                            /dev/oracleasm/disks/D1

           4 DGA_D2                         FG2                            /dev/oracleasm/disks/D2

ALTER DISKGROUP dgroupA ADD DISK

  '/dev/oracleasm/disks/D8',

  '/dev/oracleasm/disks/D9';

dgroupA

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

           4 DGA_D1                         FG1                            /dev/oracleasm/disks/D1

           4 DGROUPA_0003                   DGROUPA_0003                   /dev/oracleasm/disks/D9

           4 DGA_D2                         FG2                            /dev/oracleasm/disks/D2

           4 DGROUPA_0002                   DGROUPA_0002                   /dev/oracleasm/disks/D8

DGA_D1 -> /dev/oracleasm/disks/D1 --- BAD or poor performance 

-> We need to replace this disks

ALTER DISKGROUP dgroupA DROP DISK 'D1'; ---- fail  

ALTER DISKGROUP dgroupA DROP DISK '/dev/oracleasm/disks/D1'; ---- fail 

ALTER DISKGROUP dgroupA DROP DISK 'DGA_D1'; - Works 

ALTER DISKGROUP dgroupA ADD DISK

  '/dev/oracleasm/disks/D10';

- While dropping disk we need provide disk name whatever defined inside ASM

- While adding disk we need provide disk path

Rebalance:

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

2026-03-26T11:06:08.789202+05:30

SUCCESS: ALTER DISKGROUP dgroupA ADD DISK

  '/dev/oracleasm/disks/D8',

  '/dev/oracleasm/disks/D9'

2026-03-26T11:06:10.916065+05:30

NOTE: Attempting voting file refresh on diskgroup DGROUPA

NOTE: Refresh completed on diskgroup DGROUPA. No voting file found.

2026-03-26T11:06:10.929921+05:30

NOTE: starting rebalance of group 4/0x77d02569 (DGROUPA) at power 1

NOTE: starting process ARBA

Starting background process ARBA

2026-03-26T11:06:10.947553+05:30

ARBA started with pid=43, OS id=29381

NOTE: starting process ARB0

Starting background process ARB0

2026-03-26T11:06:10.964242+05:30

ARB0 started with pid=45, OS id=29383

NOTE: assigning ARBA to group 4/0x77d02569 (DGROUPA) to compute estimates

NOTE: assigning ARB0 to group 4/0x77d02569 (DGROUPA) with 1 parallel I/O

2026-03-26T11:06:11.120833+05:30

NOTE: F1X0 on disk 3 (fmt 1) relocated at fcn 0.17: AU 0 -> AU 2

NOTE: 03/26/26 11:06:10 DGROUPA.F1X0 copy 3 relocating from 65534:4294967294 to 3:2 at FCN 0.17

2026-03-26T11:06:12.172574+05:30

NOTE: stopping process ARB0

NOTE: stopping process ARBA

NOTE: Starting expel slave for group 4/0x77d02569 (DGROUPA)

2026-03-26T11:06:12.207183+05:30

NOTE: GroupBlock outside rolling migration privileged region

NOTE: requesting all-instance membership refresh for group=4

2026-03-26T11:06:12.234663+05:30

NOTE: membership refresh pending for group 4/0x77d02569 (DGROUPA)

2026-03-26T11:06:12.236496+05:30

GMON querying group 4 at 78 for pid 26, osid 4844

2026-03-26T11:06:12.237285+05:30

SUCCESS: refreshed membership for 4/0x77d02569 (DGROUPA)

2026-03-26T11:06:13.973090+05:30

SUCCESS: rebalance completed for group 4/0x77d02569 (DGROUPA)

SQL> show parameter power

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

asm_power_limit                      integer     1

monitor and check rebalance operation 

- desc v$asm_operation;

INST_ID OPERA STAT POWER SOFAR EST_WORK EST_RATE EST_MINUTES

------- ----- ---- ----- ----- -------- -------- -----------

1       REBAL RUN   1    516   53736       2012  26

DB -> 450+ parameters 

ASM -> 70+ / 80+ parameters 

v$parameter

3. DROP DISKGROUP

========================

drop diskgroup dgroupA;

drop diskgroup dgroupB;

drop diskgroup dgroupC;

drop diskgroup DATA;

1. ASM Architecture:

- Instance only Architecture

- INSTANCE + Storage 

2. DB supports v$ & dba_ but ASM support v$

v$asm_diskgroup 

v$asm_disk

v$asm_operation;

DB - Spfile (480+ parameters static or dynamic) 

ASM - Spfile (80+ parameters static or dynamic) 

- asm_power_limit

3. ASM specific memory & ASM specific BG process 

ASM cache 

ASMB

4. How to increase rebalance 

asm_power_limit

5. How to monitor rebalance / estimate 

- desc v$asm_operation;

6. connecting to DB, ASM, ASM storage 

sqlplus / as sysdba

sqlplus / as sysasm

asmcmd -p

7. ASM storage structure / ASM logical structure

AU, ASM Extents, ASM file, ASM disks, ASM diskgroup 

8. DB_BLOCK_SIZE Vs AU 

9. ASM advantages 

- Redundancy (External, Normal, High) 

- Fail Group 

- Striping 

- High speed read and write (Multiple parallel IO)

- Autorestart feature 

10. DB start-up sequence and ASM startup sequence 

11. ASM diskgroup administration 

create / alter / drop 

12. ASM, DB maintenance - Bounce 

HAS (os process / os daemon) -> CRS -> ASM -> DB 

crsctl check has

crsctl stop has

crsctl start has

crsctl check crs

crsctl stop crs

crsctl start crs

srvctl status asm 

srvctl stop asm 

srvctl start asm 

srvctl status database -d DEVDB 

srvctl stop database -d DEVDB 

srvctl start database -d DEVDB 

13. change diskgroup redundancy? 

DEVDB -> +DATA (External Redundancy)

Change redundancy of DATA diskgroup to NORMAL / HIGH? 

- No 

14. change AU size of a diskgroup?

DEVDB -> +DATA (AU=4MB)

Change AU size of DATA diskgroup to 8MB?

- No

15. change diskgroup name? 

DEVDB -> +DATA groups

Change name of DATA diskgroup DATAC? 

- Possible (This need downtime for DEVDB)

DATA -> DATAC 

16. How to map asm disks to diskgroup?

/dev/oracleasm/disks/ASMDISK1 - OCR

/dev/oracleasm/disks/ASMDISK2 - DATA

/dev/oracleasm/disks/ASMDISK3 - RECO

ASMCMD [+DATA] > lsdsk -G OCR

ASMCMD [+DATA] > lsdsk -G DATA

ASMCMD [+DATA] > lsdsk -G RECO

select GROUP_NUMBER,NAME,FAILGROUP,PATH from V$ASM_DISK order by GROUP_NUMBER;

select GROUP_NUMBER,NAME from V$ASM_DISKGROUP order by GROUP_NUMBER;

17. How to map asm disks to OS disks?

/dev/oracleasm/disks/ASMDISK1 - /dev/sdb1

/dev/oracleasm/disks/ASMDISK2 - /dev/sdc1

/dev/oracleasm/disks/ASMDISK3 - /dev/sdd1

oracleasm querydisk -d ASMDISK1

oracleasm querydisk -d ASMDISK2

oracleasm querydisk -d ASMDISK3

[oracle@oraclelab1 dev]$ ll sd*

brw-rw----. 1 root disk 8,   0 Mar 20 11:30 sda

brw-rw----. 1 root disk 8,   1 Mar 20 11:30 sda1

brw-rw----. 1 root disk 8,   2 Mar 20 11:30 sda2

brw-rw----. 1 root disk 8,  16 Mar 20 11:31 sdb

brw-rw----. 1 root disk 8,  17 Mar 20 11:31 sdb1

brw-rw----. 1 root disk 8,  32 Mar 20 11:31 sdc

brw-rw----. 1 root disk 8,  33 Mar 20 11:31 sdc1

brw-rw----. 1 root disk 8,  48 Mar 20 11:31 sdd

brw-rw----. 1 root disk 8,  49 Mar 20 11:31 sdd1

brw-rw----. 1 root disk 8,  64 Mar 26 10:43 sde

brw-rw----. 1 root disk 8,  65 Mar 26 10:44 sde1

brw-rw----. 1 root disk 8,  80 Mar 26 10:43 sdf

brw-rw----. 1 root disk 8,  81 Mar 26 10:44 sdf1

brw-rw----. 1 root disk 8,  96 Mar 26 10:43 sdg

brw-rw----. 1 root disk 8,  97 Mar 26 10:44 sdg1

brw-rw----. 1 root disk 8, 112 Mar 26 10:43 sdh

brw-rw----. 1 root disk 8, 113 Mar 26 10:44 sdh1

brw-rw----. 1 root disk 8, 128 Mar 26 10:43 sdi

brw-rw----. 1 root disk 8, 129 Mar 26 10:44 sdi1

brw-rw----. 1 root disk 8, 144 Mar 26 10:43 sdj

brw-rw----. 1 root disk 8, 145 Mar 26 10:44 sdj1

brw-rw----. 1 root disk 8, 160 Mar 26 10:44 sdk

brw-rw----. 1 root disk 8, 161 Mar 26 10:44 sdk1

brw-rw----. 1 root disk 8, 176 Mar 26 10:44 sdl

brw-rw----. 1 root disk 8, 177 Mar 26 10:44 sdl1

brw-rw----. 1 root disk 8, 192 Mar 26 10:44 sdm

brw-rw----. 1 root disk 8, 193 Mar 26 10:44 sdm1

brw-rw----. 1 root disk 8, 208 Mar 26 10:44 sdn

brw-rw----. 1 root disk 8, 209 Mar 26 10:44 sdn1

[oracle@oraclelab1 dev]$ oracleasm querydisk -d ASMDISK1

Disk "ASMDISK1" is a valid ASM disk on device [8,17]

[oracle@oraclelab1 dev]$

[oracle@oraclelab1 dev]$ oracleasm querydisk -d ASMDISK2

Disk "ASMDISK2" is a valid ASM disk on device [8,33]

[oracle@oraclelab1 dev]$

[oracle@oraclelab1 dev]$ oracleasm querydisk -d ASMDISK3

Disk "ASMDISK3" is a valid ASM disk on device [8,49]

[oracle@oraclelab1 dev]$

18. diskgrup creation 

Creation DG - using command line 

Creation DG - using command GUI (./asmca) 

19. Why oracle recommend to run database on ASM?

- Redundancy (External, Normal, High) 

- Fail Group 

- Striping 

- High speed read and write (Multiple parallel IO)

- Autorestart feature

- ACFS (similar to NFS) 

Striping 

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

- fine grained (CF) 

- course grained (Remaining all) 

Disk headers 

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

HEADER_STATUS

Oracle DBA Tutorial | Users, Schemas, Privileges, Roles, Profiles, Quotas & User Security

Users or schemas:

====================

DEVDB - Lab1

TESTDB - Lab2 

New database:

- 36+ users user will be created 

- SYS - master account (DBA accounts)

- Any admin activity can be done + Maintenance activity 

- SYSTEM - master account (DBA accounts)

- Any admin activity can be done

SYS user password will be stored in password file 

Rest all user password stored inside database 

all user can connect to database except SYS 

sqlplus <user-name>/<password>

sys user can connect using OS authentication via sysdba or sysoper  

sqlplus / as sysdba

or sys user can connect using passwordfile authentication via sysdba or sysoper  

sqlplus sys/MAllik123##@DEVDB as sysdba

While creating DB we provide the password for sys and system 

By querying dba_users 

Ideal way to identify sys password is using TNS connection 

sqlplus / as sysdba -> OS authentication 

sqlplus sys/Mallik as sysdba -> OS authentication (wrong password) 

sqlplus sys/Mallik123## as sysdba -> OS authentication (correct password) 

sqlplus sys/Mallik123@DEVDB as sysdba -> password file authentication (wrong password) 

sqlplus sys/Mallik123##@DEVDB as sysdba -> password file authentication (correct password) 

Rest all users:

sqlplus <user-name>/<password>

sqlplus system/Mallik123

sqlplus mallik/mallik

sqlplus hr/hr

sqlplus system/Mallik123@DEVDB

sqlplus mallik/mallik@DEVDB

sqlplus hr/hr@DEVDB

When we create user account 

- We give username and password 

(create user user1 identified by user1;)

- assigned with default tablespace (USERS)

- default temp tablespace (TEMP)

- any object created by him placed under that USERS tablespace 

any user create = called then as schema 

Mallik user = Mallik schema 

hr user = hr schema 

john user = john schema 

system - user 

system - schema 

collection of all the object created by a user is called schema 

sys - user 

sys - schema 

collection of all the object created by a user is called schema 

mallik - user 

mallik - schema 

collection of all the object created by a user is called schema 

hr - user 

hr - schema 

collection of all the object created by a user is called schema 

sqlplus hr/hr

sqlplus mallik/mallik

sqlplus user1/user1 

sqlplus hr/hr@DEVDB

sqlplus mallik/mallik@DEVDB

sqlplus user1/user1@DEVDB

The moment we create any user inside database, That will be brand new user with 0 privileges 

- As DBA we need to assign / provide required privileges

create user user1 identified by user1;

create user user2 identified by user2;

sqlplus user1/user1

sqlplus user2/user2

privileges:

===========

system : perform some action inside database

- connect to database -> grant create session to user1; 

- create table -> grant create table to user1;

object : perform some action on existing object 

HR user - DEPARTMENTS Table 

- user1 wants to read HR.DEPARTMENTS -> grant select on HR.DEPARTMENTS to USER1;

Role:

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

Managing user privileges is always challenging for DBA  

Role - group of privileges 

In my organisation -> HR department 

HR - Dept

EMPLOYEES

DEPARTMENTS

LOCATIONS

COUNTRIES

user1 - joined to HR department 

He need select/read access on those table 

grant select on HR.EMPLOYEES to user1;

grant select on HR.DEPARTMENTS to user1;

grant select on HR.LOCATIONS to user1;

grant select on HR.COUNTRIES to user1;

user1:

select * from HR.EMPLOYEES;

select * from HR.DEPARTMENTS;

select * from HR.LOCATIONS;

select * from HR.COUNTRIES;

user2- joined to HR department  

He need select/read access on those table 

grant select on HR.EMPLOYEES to user2;

grant select on HR.DEPARTMENTS to user2;

grant select on HR.LOCATIONS to user2;

grant select on HR.COUNTRIES to user2;

user2:

select * from HR.EMPLOYEES;

select * from HR.DEPARTMENTS;

select * from HR.LOCATIONS;

select * from HR.COUNTRIES;

user3 - joined to HR department  

He need select/read access on those table 

grant select on HR.EMPLOYEES to user3;

grant select on HR.DEPARTMENTS to user3;

grant select on HR.LOCATIONS to user3;

grant select on HR.COUNTRIES to user3;

user3:

select * from HR.EMPLOYEES;

select * from HR.DEPARTMENTS;

select * from HR.LOCATIONS;

select * from HR.COUNTRIES;

ROLE - group of privileges 

=======

create roles HR_ROLE;

grant select on HR.EMPLOYEES to HR_ROLE;

grant select on HR.DEPARTMENTS to HR_ROLE;

grant select on HR.LOCATIONS to HR_ROLE;

grant select on HR.COUNTRIES to HR_ROLE;

grant HR_ROLE to user1;

grant HR_ROLE to user2;

grant HR_ROLE to user3;

Quota:

=======

When we create user account 

- We give username and password 

- create user user1 identified by user1;

- create user user2 identified by user2;

- assigned with default tablespace (USERS)

- default temp tablespace (TEMP)

- any object created by him placed under that USERS tablespace 

user1/user2/user3

- assigned with default tablespace (USERS)

user1

T1/TEST1/TABLE1 - some random tables and inserting 1M records / 2M records 

USERS - user.dbf - 32GB 

Once this USERS tablespace - user.dbf datafile is full - 32GB 

- then it will impact all user use1/user2/user3 or any user who has USERS as default tablespace 

Quota is of restricting space usage under the tablespace for a user 

any user created default gets unlimited tablespace 

alter user user1 quota 1G on USERS;

alter user user1 quota 2G on USERS;

dba_ts_quotas

SELECT TABLESPACE_NAME,

BYTES / 1024 / 1024 "UTILIZIED_SPACE",

MAX_BYTES / 1024 / 1024 "QUOTA_ALLOCATED"

FROM dba_ts_quotas

WHERE username = 'USER1';

alter user USER2 quota unlimited on USERS;

SELECT TABLESPACE_NAME,

BYTES / 1024 / 1024 "UTILIZIED_SPACE",

MAX_BYTES / 1024 / 1024 "QUOTA_ALLOCATED"

FROM dba_ts_quotas

WHERE username = 'USER2';

Profiles:

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

17 resource 

restrict usage of SGA 

restrict usage of PGA 

restrict usage of CPU 

Set some password policy 

dba_profiles 

-> DEFAULT profile

select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles order by PROFILE;

any user created will be assigned with this DEFAULT profile

select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles where PROFILE='DEFAULT';

CREATE PROFILE EBS_APPLN LIMIT

PASSWORD_LIFE_TIME 45

PASSWORD_GRACE_TIME 7

PASSWORD_REUSE_TIME 3

PASSWORD_REUSE_MAX 5

FAILED_LOGIN_ATTEMPTS 3

PASSWORD_LOCK_TIME 1

CPU_PER_CALL 5000

PRIVATE_SGA 250K

LOGICAL_READS_PER_CALL 2000;

alter user USER1 profile EBS_APPLN;

Any users:

- system privileges 

- object privileges 

- role privileges 

- quota 

- profile 

User creation:

=====================

Option1: - Best practice 

create user user_1 identified by user_1

default tablespace USERS 

TEMPRORY TABLESPACE temp 

profile EBS_ppln 

quota unlimited 

roles ;

Option2:

create user user_1 identified by user_1;

Option3:

create user user_1 identified by user_1

profile EBS_APPLN;

Option4:

create user user_1 identified by user_1

profile EBS_APPLN

quota 1G on USERS;

Option5:

create user user_1 identified by user_1

profile EBS_APPLN

quota 1G on TEST1

default tablespace TEST1;

alter user user_1 

profile / quota / tablespace / ROLE

What all privileges we can assign to user?

- system 

- object

- role  

Can I get what all privileges assigned for USER1? 

Can you create user3 and assigned same privileges as of user1; 

Then we DBA need to get all the privileges assigned for USER1

- system 

- object

- role

- DBA_SYS_PRIVS

- DBA_TAB_PRIVS

- dba_role_privs

SELECT * FROM DBA_SYS_PRIVS WHERE grantee IN ('USER1');

SELECT * FROM DBA_TAB_PRIVS WHERE grantee IN ('USER1');

SELECT * FROM dba_role_privs WHERE grantee IN ('USER1');

SELECT * FROM DBA_SYS_PRIVS WHERE grantee IN ('USER2');

SELECT * FROM DBA_TAB_PRIVS WHERE grantee IN ('USER2');

SELECT * FROM dba_role_privs WHERE grantee IN ('USER2');

HR:

+++++++++

SELECT * FROM DBA_SYS_PRIVS WHERE grantee IN ('HR');

SELECT * FROM DBA_TAB_PRIVS WHERE grantee IN ('HR');

SELECT * FROM dba_role_privs WHERE grantee IN ('HR');

We can get all privileges of a role

+++++++++++

SELECT * FROM DBA_SYS_PRIVS WHERE grantee IN ('HR_ROLE');

SELECT * FROM DBA_TAB_PRIVS WHERE grantee IN ('HR_ROLE');

SELECT * FROM dba_role_privs WHERE grantee IN ('HR_ROLE');

🎉 Excited to share that I've been recognized as an Oracle ACE for the 5th consecutive year (2026)! 🏆🙌

🎉 Excited to share that I've been recognized as an Oracle ACE for the 5th consecutive year (2026)! 🏆🙌

What started as a passion for learning Oracle technologies has grown into a rewarding journey of teaching, mentoring, blogging, creating YouTube content, and contributing to the Oracle community.

A big thank you to the Oracle ACE Program, my students, colleagues, friends, and the Oracle community for your continued support and encouragement. 🙏

📜 Proud to share my Oracle ACE 2026 certificate!

🔹 My Oracle ACE Profile: https://ace.oracle.com/ords/ace/profile/mallik034
🔹 Oracle ACE Program: https://ace.oracle.com/ords/r/ace/oracle-aces/home
🔹 My Technical Blog: https://mallik034.blogspot.com/
🔹 My YouTube Sessions: https://www.youtube.com/mallik034
🔹 My LinkedIn: https://www.linkedin.com/in/mallik034/

And yes... I'm also keeping an eye on my inbox for those legendary ACE goodies! 🎁😄

Here's to another year of learning, sharing, and giving back to the Oracle community. 🚀

#OracleACE #ACE2026 #OracleCommunity #OracleDatabase #OracleRAC #KnowledgeSharing #TechLeadership #Mentoring #YouTube #Blogging #5YearsStrong #Gratitude #CommunityDriven 🚀🏆

🚀 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

Oracle RAC Cluster Administration | What is GPNPD in Oracle RAC? | Oracle RAC Tools & Interview Q&A

crsctl start crs/has

--------------------

+ASM -> +OCR/oracle-clu/ASMPARAMETERFILE/REGISTRY.253.1228302843

-> its has to start ASM 

-> ASM to start it has to read Spfile (+OCR/oracle-clu/ASMPARAMETERFILE/REGISTRY.253.1228302843)

-> Where as Spfile is inside ASM diskgroup 

-> In order to read Spfile which is inside ASM diskgroup, ASM diskgroup has to be mounted 

-> In order to mount ASM diskgroup, ASM has to be up and running 

cluster is able to start ASM instance with help of ora.gpnpd

ora.gpnpd -> cluster resource will read gpnpd profile 

gpnpd profile -> has ASM Spfile location / disk group information / disk information  

ora.gpnpd -> will help reading the disk header of ASM diskgroup where Spfile is located using kfod or kfed utility 

[oracle@oraclelab1 ~]$ which kfed

/u01/app/19.0.0.0/grid/bin/kfed

[oracle@oraclelab1 ~]$ which kfod

/u01/app/19.0.0.0/grid/bin/kfod

/u01/app/19.0.0.0/grid/gpnp/profiles/peer/profile.xml

ps -ef|grep smon

ps -ef|grep tns

ps -ef|grep d.bin

crsctl stat res -t 

crsctl stat res -t -init 

crsctl stop cluster -all

crsctl start cluster -all

crsctl check cluster -all 

crsctl stop crs 

crsctl start crs 

crsctl check crs 

crsctl stop has 

crsctl start has 

crsctl check has 

crsctl disable has 

crsctl enable has 

crsctl disable crs 

crsctl enable crs 

runclufy.sh - ASM/cluster installation precheck 

clufy 

olsnodes 

srvctl - ASM / DB / listener / services / scan 

srvctl config scan

srvctl status scan

srvctl status scan_listener

srvctl status database -d DEVDB

srvctl stop database -d DEVDB

srvctl start database -d DEVDB

srvctl status instance -i DEVDB1 -d DEVDB

srvctl stop instance -i DEVDB1 -d DEVDB

srvctl start instance -i DEVDB1 -d DEVDB

srvctl status instance -i DEVDB2 -d DEVDB

srvctl stop instance -i DEVDB2 -d DEVDB

srvctl start instance -i DEVDB2 -d DEVDB

srvctl status listener -l LISTENER

srvctl stop listener -l LISTENER

srvctl start listener -l LISTENER

srvctl status listener -l LISTENER -n oraclelab1

srvctl stop listener -l LISTENER -n oraclelab1

srvctl start listener -l LISTENER -n oraclelab1

OLR & OCR:

=========

ocrconfig 

ocrconfig -local

ocrcheck 

ocrcheck -local

ocrdump 

ocrdump -local

How to find the OLR/OCR location?

[oracle@oraclelab2 oracle]$ cat /etc/oracle/ocr.loc

ocrconfig_loc=+OCR/oracle-clu/OCRFILE/registry.255.1228302853

local_only=FALSE

[oracle@oraclelab2 oracle]$ cat /etc/oracle/olr.loc

olrconfig_loc=/u01/app/oracle/crsdata/oraclelab2/olr/oraclelab2_19.olr

crs_home=/u01/app/19.0.0.0/grid

Votedisks

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

crsctl 

crsctl query css votedisk

crsctl 

crsctl check has 

crsctl stop has 

crsctl start has 

crsctl check crs 

crsctl stop crs 

crsctl start crs 

crsctl enabled crs/has

crsctl disable crs/has

What is GPNP Profile and What it contains?

/u01/app/19.0.0.0/grid/gpnp/profiles/peer/profile.xml

- has ASM Spfile location / disk group information / disk information

kfed read /dev/oracleasm/disks/ASMDISK1 | grep -E 'vfstart|vfend'

Regards,

Mallikarjun / Vismo Technologies

WhatsApp: +91 9880616848 / +91 9036478079

Cell: +91 9880616848 / +91 9036478079

Email: mallikarjun.ramadurg@gmail.com / vismotechnologies@gmail.com

Oracle RAC Cluster Maintenance | RAC Startup Sequence | Startup Troubleshooting Guide | Interview Q&A

Clusterware Maintenance: (Bouncing) & Database Maintenance: (Bouncing) 

======================================================================

HAS -> CRS -> ASM -> DB 

DB: oracle (DEVDB1/DEVDB2)

=== 

Node1:

srvctl status instance -i DEVDB1 -d DEVDB

srvctl stop instance -i DEVDB1 -d DEVDB

srvctl start instance -i DEVDB1 -d DEVDB

sqlplus / as sysdba

shut immediate

startup

Node2:

srvctl status instance -i DEVDB2 -d DEVDB

srvctl stop instance -i DEVDB2 -d DEVDB

srvctl start instance -i DEVDB2 -d DEVDB

sqlplus / as sysdba

shut immediate

startup

Node1 & Node2: Entire DB downtime 

srvctl status database -d DEVDB

srvctl stop database -d DEVDB

srvctl start database -d DEVDB

from both the nodes

sqlplus / as sysdba

shut immediate

startup

ASM: oracle (+ASM1/+ASM2)

====

node1:

srvctl status asm 

srvctl stop asm

srvctl start asm

node2:

srvctl status asm

srvctl stop asm

srvctl start asm

CRS: root (+ASM1/+ASM2)

=====

node1:

crsctl check crs 

crsctl stop crs 

crsctl start crs 

node2:

crsctl check crs 

crsctl stop crs 

crsctl start crs 

HAS: root (+ASM1/+ASM2)

====

node1:

crsctl check has 

crsctl stop has 

crsctl start has 

node2:

crsctl check has 

crsctl stop has 

crsctl start has 

Cluster Start-Up sequence:

==========================

crsctl stop crs/has

crsctl start crs/has

crsctl stat res -t -init 

-> Few cluster resource are not starting 

-> cssd is not coming up 

-> gpnpd is not comping up 

What is the reason? 

- No answer until we check those respective logs 

Question where are these logs?

-> $ORACLE_BASE/diag -> /u01/app/oracle/diag

- cluster resource logs -> /u01/app/oracle/diag/crs (/u01/app/oracle/diag/crs/oraclelab1/crs/trace)

- asm logs -> /u01/app/oracle/diag/asm (/u01/app/oracle/diag/asm/+asm/+ASM1/trace) 

- database logs -> /u01/app/oracle/diag/rdbms (/u01/app/oracle/diag/rdbms/devdb/DEVDB1/trace)

- listener log -> /u01/app/oracle/diag/tnslsnr/oraclelab1

cluster startup troubleshooting 

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

crsctl stop crs/has

crsctl start crs/has

crsctl stat res -t -init 

- cluster resource logs -> /u01/app/oracle/diag/crs (/u01/app/oracle/diag/crs/oraclelab1/crs/trace)

Cluster StartUp sequence:

==========================

OHAS -> LEVEL1 -> LEVEL2 -> LEVEL3 -> LEVEL4

OHASD -> oracle high availability service 

HAS is not starting? (Oracle High availability service demon) 

- OLR is missing or corrupted  (Oracle local registry) 

cssd / cssdmonitor - cluster synchronization service demon 

CSSD is not starting?

- voting disks are missing or corrupted 

CRSD - cluster ready service 

- read OCR file (Oracle Central registry)

CRSD is not starting?

- OCR is missing or corrupted  (Oracle Central registry) 

What is OLR, OCR, Votedisk?  

where they are located? 

Regards,

Mallikarjun / Vismo Technologies

WhatsApp: +91 9880616848 / +91 9036478079

Cell: +91 9880616848 / +91 9036478079

Email: mallikarjun.ramadurg@gmail.com / vismotechnologies@gmail.com

Interview Questions 7 - Multitenant Architecture - RAC CDB & RAC PDB

1. Normal database Vs CDB database (PDB) 

DEVDB: - Normal Database

======

Instance + Database

Instance = Memory (SGA+PGA) + BG Process (Mandatory+Non-Mandatory)

Database = Datafiles (System, Sysaux, Undo, Users, Temp)

spfile/pfile

controlfile

redologs/archivelogs 

passwordfile 

listener / tnsfiles / oraclehome 

DEVCDB: - Normal Database

======

Instance + Database

Instance = Memory (SGA+PGA) + BG Process (Mandatory+Non-Mandatory)

Database = Datafiles (System, Sysaux, Undo, Users, Temp)

spfile/pfile

controlfile

redologs/archivelogs 

passwordfile 

listener / tnsfiles / oraclehome 

PDB - Pluggable database (This PDB resides inside CDB) (Virtual Database)

- Only datafiles (System, Sysaux, Undo, Users, Temp)

2. Multitenant architecture 

- CDB + PDB 

3. CDB - container database 

4. PDB (virtual databases)

- only datafiles 

5. Normal database files 

Instance + Database

Instance = Memory (SGA+PGA) + BG Process (Mandatory+Non-Mandatory)

Database = Datafiles (System, Sysaux, Undo, Users, Temp)

spfile/pfile

controlfile

passwordfile 

redologs/archivelogs 

listener / tnsfiles / oraclehome 

6. CDB database files 

Instance + Database

Instance = Memory (SGA+PGA) + BG Process (Mandatory+Non-Mandatory)

Database = Datafiles (System, Sysaux, Undo, Users, Temp)

spfile/pfile

controlfile

passwordfile 

redologs/archivelogs 

listener / tnsfiles / oraclehome 

7. PDB database files 

- only datafiles 

8. Common user Vs local user

CDB - Common user - c##

PDB - local user

9. All the admin activates we perform at PDB level 

- user creation 

- tablespace creation 

- user management or administration 

- datafile addition and resize  

- many more 

10. listener and TNS 

- only 1 listener at Oracle Home pointing to CDB

- LREG bg process of CDB will register all CDB and PDBs into listener 

- We can have multiple tnsnames according to CDB or PDB 

11. How to connect to Normal Database 

- sqlplus / as sysdba 

12. How to connect to CDB Database 

- sqlplus / as sysdba 

13. How to connect to PDB Database 

- connect to CDB first and then connect to PDB 

- sqlplus / as sysdba 

- alter session set container=<PDB>

14. PDB open and close 

Close (shutdown) - MOUNTED

Open - READ / WRITE 

node1:

alter pluggable database PDB1 open;

alter pluggable database PDB1 close;

node2:

alter pluggable database PDB1 open;

alter pluggable database PDB1 close;

node1/node2:

alter pluggable database PDB1 open instances=all;

alter pluggable database PDB1 close instances=all;

alter pluggable database all open instances=all;

alter pluggable database all close instances=all;

15. save state:

alter pluggable database DEVPDB save state instances=all;

alter pluggable database PDB2 save state instances=all;

alter pluggable database PDB3 save state instances=all;

16. 19c new feature - directly connect to pdb using sqlplus / as sysdba 

export ORACLE_PDB_SID=DEVPDB

unset ORACLE_PDB_SID

17. connecting from one PDB to other PDB 

alter session set container=DEVPDB;

alter session set container=PDB3;

18. PDB level cloning 

- 5 options 

- Clone/Create PDB1 from PDB$SEED

- Clone/Create PDB2 from another DEVPDB (mallik)

- Clone/Create from a Remote CDB

- Plug a unplugged PDB

- Convert a non-CDB (Normal Database - DEVDB) to PDB

19. RMAN backup and Recovery:

- L0 & L1 -> DEVDB 

- L0 & L1 -> DEVCDB  

- We take backup at CDB level then underlining all PDBs will be backedup 

When we take CDB level backup all PDB datafiles will be backed-up 

We can use L0 & L1 backup taken at CDB level for clone / restore - restore 

- We clone / restore-recover CDB level

Can I take only PDB backup?

- Yes 

rman target / >>>> DEVCDB 

RMAN> BACKUP PLUGGABLE DATABASE DEVPDB format='/u01/backup/DEVPDB';

RMAN> BACKUP PLUGGABLE DATABASE PDB3 format='/u01/backup/PDB3';

Can I use these PDB backup to clone PDB?

- NO

What is the use of taking PDB backups? 

- In case of PDB crash, We can use those PDB backups to restore 

- We can use CDB backup to restore those PDBs 

rman target / >>>> DEVCDB 

RMAN> RUN {

ALTER PLUGGABLE DATABASE DEVPDB CLOSE instances=all;

RESTORE PLUGGABLE DATABASE DEVPDB backup location <CDB_backup_location>;

RESTORE PLUGGABLE DATABASE DEVPDB backup location <PDB_backup_location>;

RECOVER PLUGGABLE DATABASE DEVPDB;

ALTER PLUGGABLE DATABASE DEVPDB OPEN instances=all;

}

20. Standby / DR 

- CDB to CDB level DR build

- Archive log shipping will happens form CDB to CDB 

- MRP we start it at DR/standby side at CDB level 

21. Listener 

One oracle home -> We create CDB -> inside CDB we will be having PDBs 

-> That oracle Home will have listener (local listener) if not 

-> In case of RAC default listener will be at GI  

-> Inside oracle home network/admin we will have all tns detail for CDB & PDBs 

22. Spfile pfile / pfile - CDB level

Controlfile file - CDB level

redo log -> archive logs - CDB level

password file - CDB level

 

datafiles - CDB level + PDB level 

23. CDB startup vs PDB startup 

shutdown -> nomount -> mount - open 

close -> open 

24. DB services are cluster 

DEVDB 

- srvctl add service -d DEVDB -s DEVDB_HR_SERVCIE -r DEVDB1,DEVDB2 -P BASIC -e SESSION

- srvctl add service -d DEVCDB -s DEVCDB_HR_SERVCIE -r DEVDB1,DEVDB2 -P BASIC -e SESSION

- srvctl add service -d DEVCDB -P DEVPDB -s DEVPDB_HR_SERVCIE -r DEVDB1,DEVDB2 -P BASIC -e SESSION

DEVDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = scan.localdomain.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = DEVDB)

    )

  )

DEVDB_HR_SERVCIE =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = scan.localdomain.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = DEVDB_HR_SERVCIE)

    )

  )

DEVCDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = scan.localdomain.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = DEVCDB)

    )

  )

DEVCDB_HR_SERVCIE =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = scan.localdomain.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = DEVCDB_HR_SERVCIE)

    )

  )

DEVPDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = scan.localdomain.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = DEVPDB)

    )

  )

DEVPDB_HR_SERVCIE =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = scan.localdomain.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = DEVPDB_HR_SERVCIE)

    )

  )

Regards,

Mallikarjun / Vismo Technologies

WhatsApp: +91 9880616848 / +91 9036478079

Cell: +91 9880616848 / +91 9036478079

Email: mallikarjun.ramadurg@gmail.com / vismotechnologies@gmail.com