🚀 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 🚀🏆