Chaitanya Oracle Dba Blog : 08/14/20

Oracle ASM

Introduction:

Oracle introduced ASM (Automatic storage management) with 10 g version ,ASM is a logical volume Manager owned by oracle and it helps to create logical volumes on physical disks to store the database and its components and it widely used in Real Application Cluster,ASM is built on OMF,ASM acts as a buit in oracle volume manager by handling striping and mirroring functions ,previously managed by third party tools,is a Poratable volume manager to manage pool of shared disks,ASM is an instance not a database will have separate SGA and set of bg process.

Raw Devices

OCFS2 ----->Orcale clusterd File system

ASM------->Automatic Storage Management

is a part grid of infrastructure software in 10g ,we have separate home for ASM installed from 11g, one single home for clusterware and ASM with grid infra home

eg : /u01/app/11.2.0/grid

/u01/app/12.2.0/grid

Manages oracle data files (eg:DATA) spreads data across disks for faster performance and work load balance

Note: Oracle recommends to manage with minimum 4 disks for each disk group,supports online disks reconfiguration and rebalancing provides adjustable rebalncing speed,for every node,we have ASM instance default installed while grid software

rac1 --->+ASM1

rac2---->+ASM2

ps -ef |grep pmon

To view the information in grid will usse performance views with gv$

gv$session

gv$process

gv$instance

gv$database

for finding information on every instance using global/grid views

number of nodes -4

node-1

node-2

node-3

node-4

Number of ASM Instances :4(eg: +ASM1,+ASM2,+ASM3,+ASM4)

Number of ASM Instance on each node :1(eg: on rac1,+ASM1)

Number of RDBMS Instances :3 prod,dev.test

Number of RDBMS Instances on each node :3(prod1,dev1,test1)

Each database number of instance on 4 node? 4

eg:2dbs,each db has 4 instances if 4 nodes

prod(DB)-->prod1,prod2,prod3,prod4(Instances)

Test(DB)--->test1,test2,test3,test4

eg:

3 databases in a cluster

rac1----> +ASM1,prod1,dev1,test1

rac2---->+ASM2,prod2,dev2,test2

sql>select inst_id,instance_number,host_name,status from gv$instance;

grid infrastructure

clusterware|ASM

cluster--clusterware

storage--ASM--automatic storage management

Linux/oradata-->dbfiles--->managed linux os--->notshared--->single instance accessed

rac1

rac2

rac3

rac4

access-->shared storage across multiple nodes/server with in a cluster

asmdisk1,asmdisk2 -disk1,disk2--drivers--asmdrivers--boot/list

one more cluster -it own his shared storage ASM is a volume manager to manage pool of disks

using logical storage unit as disk group associated with one or more physical disks

two disk groups

DATA--->dbfiles,redo,controfile,spfile,ocr,vote

FRA---->archives,backupsets

min 4 disks avail for each disk group

DATA---->asmdisk1,asmdisk2,asmdisk3(3 dsiks)can add more

FRA ----->asmdisk4,asmdisk5(2 disks)

online---->add disk without downtime spreads data across disks forfatsre performance can be rebalanced by adding disks/removing disks

ASM is an instance,not a database on everynode,default one ASM instance runs/starts on a clusterware startup

connect and verify the instances status of every node with in a cluster from any one single instance using gv$instance

select inst_id,instance_name,instance_number,status from gv$instance;

number of rdbms instances on 4 nodes

rac1-->prod1,dev1,test1

rac2--->prod2,dev2,test2

rac3--->prod3,dev3,test3

rac4--->prod4,dev4,test4

including ASM -->overall on a 4 node cluster with 3 database 3 rdbms*4 nodes=12 inst+4 ASM=16 instances

Log in to asm

Tools

asmca - configuration assistant

ascmd - command line

$asmcmd

mkdir

xhost +

su - oracle

.grid_env

$asmca

create diskgroups/add disks/drop disks

mount diskgroups/Dismount diskgroup/local

Oracle recommends to manage with min three diskgroups

DATA - datafiles,redo,control,ocr,voting disk

FRA - fast recovery area - rman,archives,flashback logs,redo

,control files

Each diskgroup may have one or more disks

Brief few views in ASM

v$asm_file

v$asm_diskgroups

V$asm_disks

v$asm_operation

v$asm_disk_stat

v$asm_client

sql>select hot_used_mb,cold_used_mb,free_mb,total_mb,name,label,path from v$asm_disk;

sqlplus '/as sysasm'

SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage FROM v$asm_diskgroup;

How to configure ASM diskstring to discover the disks while boot?

sql>alter system set asm_diskstring='/dev/oracleasm/disks/*' sid='*' scope=spfile;

Note: sid='*' to apply in every instance with in a cluster.

SQL> alter system set asm_diskgroups=DATA sid='*';

Mounting diskgroups in ASM

sql>alter diskgroup all mount;

ASM - DiskGroups

set wrap off

set lines 155 pages 9999

col "Group Name" for a6 Head "Group|Name"

col "Disk Name" for a10

col "State" for a10

col "Type" for a10 Head "Diskgroup|Redundancy"

col "Total GB" for 9,990 Head "Total|GB"

col "Free GB" for 9,990 Head "Free|GB"

col "Imbalance" for 99.9 Head "Percent|Imbalance"

col "Variance" for 99.9 Head "Percent|Disk Size|Variance"

col "MinFree" for 99.9 Head "Minimum|Percent|Free"

col "MaxFree" for 99.9 Head "Maximum|Percent|Free"

col "DiskCnt" for 9999 Head "Disk|Count"

prompt

prompt ASM Disk Groups

prompt ===============

SELECT g.group_number "Group"

, g.name "Group Name"

, g.state "State"

, g.type "Type"

, g.total_mb/1024 "Total GB"

, g.free_mb/1024 "Free GB"

, 100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance"

, 100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance"

, 100*(min(d.free_mb/d.total_mb)) "MinFree"

, 100*(max(d.free_mb/d.total_mb)) "MaxFree"

, count(*) "DiskCnt"

FROM v$asm_disk d, v$asm_diskgroup g

WHERE d.group_number = g.group_number and

d.group_number <> 0 and

d.state = 'NORMAL' and

d.mount_status = 'CACHED'

GROUP BY g.group_number, g.name, g.state, g.type, g.total_mb, g.free_mb

ORDER BY 1

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

ASM - Disk in Use

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

prompt ASM Disks In Use

prompt ================

col "Group" for 999

col "Disk" for 999

col "Header" for a9

col "Mode" for a8

col "State" for a8

col "Created" for a10 Head "Added To|Diskgroup"

--col "Redundancy" for a10

--col "Failure Group" for a10 Head "Failure|Group"

col "Path" for a19

--col "ReadTime" for 999999990 Head "Read Time|seconds"

--col "WriteTime" for 999999990 Head "Write Time|seconds"

--col "BytesRead" for 999990.00 Head "GigaBytes|Read"

--col "BytesWrite" for 999990.00 Head "GigaBytes|Written"

col "SecsPerRead" for 9.000 Head "Seconds|PerRead"

col "SecsPerWrite" for 9.000 Head "Seconds|PerWrite"

select group_number "Group"

, disk_number "Disk"

, header_status "Header"

, mode_status "Mode"

, state "State"

, create_date "Created"

--, redundancy "Redundancy"

, total_mb/1024 "Total GB"

, free_mb/1024 "Free GB"

, name "Disk Name"

--, failgroup "Failure Group"

, path "Path"

--, read_time "ReadTime"

--, write_time "WriteTime"

--, bytes_read/1073741824 "BytesRead"

--, bytes_written/1073741824 "BytesWrite"

, read_time/reads "SecsPerRead"

, write_time/writes "SecsPerWrite"

from v$asm_disk_stat

where header_status not in ('FORMER','CANDIDATE','PROVISIONED')

order by group_number

, disk_number

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

ASM - File Types in Diskgroups

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

Prompt File Types in Diskgroups

Prompt ========================

col "File Type" for a16

col "Block Size" for a5 Head "Block|Size"

col "Gb" for 9990.00

col "Files" for 99990

break on "Group Name" skip 1 nodup

select g.name "Group Name"

, f.TYPE "File Type"

, f.BLOCK_SIZE/1024||'k' "Block Size"

, f.STRIPED

, count(*) "Files"

, round(sum(f.BYTES)/(1024*1024*1024),2) "Gb"

from v$asm_file f,v$asm_diskgroup g

where f.group_number=g.group_number

group by g.name,f.TYPE,f.BLOCK_SIZE,f.STRIPED

order by 1,2;

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

ASM - Instances currently accessing these diskgroups

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

prompt Instances currently accessing these diskgroups

prompt ==============================================

col "Instance" form a8

select c.group_number "Group"

, g.name "Group Name"

, c.instance_name "Instance"

from v$asm_client c

, v$asm_diskgroup g

where g.group_number=c.group_number

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

ASM - Assigned ASM disks and their paths

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

prompt Assigned ASM disks and their paths

prompt ==============================

col "Disk Size" form a9

select header_status "Header"

, mode_status "Mode"

, path "Path"

, lpad(round(os_mb/1024),7)||'Gb' "Disk Size"

from v$asm_disk

where header_status in ('MEMBER')

order by path

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

ASM - Free ASM disks and their paths

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

prompt Free ASM disks and their paths

prompt ==============================

col "Disk Size" form a9

select header_status "Header"

, mode_status "Mode"

, path "Path"

, lpad(round(os_mb/1024),7)||'Gb' "Disk Size"

from v$asm_disk

where header_status in ('PROVISIONED','FORMER')

order by path

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

ASM - ASM disks and their paths

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

prompt ASM disks and their paths

prompt ==============================

col "Disk Size" form a9

select header_status "Header"

, mode_status "Mode"

, path "Path"

, lpad(round(os_mb/1024),7)||'Gb' "Disk Size"

from v$asm_disk

where header_status in ('MEMBER','PROVISIONED','CANDIDATE','FORMER')

order by path

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

ASM - Current ASM disk operations

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

prompt Current ASM disk operations

prompt ===========================

select *

from v$asm_operation

views

-----

v$asm_operation

v$asm_disks

v$asm_diskgroups

v$asm_file

v$asm_client

v$asm_disk_stats

-- Check ASM diskgroup built on raw devices

set pages 50000 lines 120

col path for a15

select a.path,b.NAME from v$asm_disk a, v$asm_diskgroup b

where a.GROUP_NUMBER=b.GROUP_NUMBER and

a.path like '%raw%' ;

--check how the different disks of the diskgroups are utilized

select

instname,

dbname,

group_number,

failgroup,

disk_number,

reads,

writes

from v$asm_disk_iostat

order by 1,2,3,4,5,6

set pages 50000 lines 120

col path for a30

select disk_number,

mount_status,

header_status,

mode_status,

state,path

from v$asm_disk ;

set pages 50000 lines 120

select path,redundancy,total_mb,free_mb,failgroup from v$asm_disk where mount_status='CACHED' and header_status='MEMBER' and mode_status='ONLINE' and state='NORMAL'order by path;

Note : to mount diskgroup - use

sql>alter diskgroup all mount;

or specific diskgroup.

list from v$asm_diskgroup(name,state)

then

sql>alter diskgroup DATA mount;

Note: For ASM Instance , Will start and mount the diskgroups.

No DB will open or there in ASM.

gv$instance (instance_name,status)

using asmca - asm configuration assistant

To create diskgroup/adding/removing disks.

#xhost +

$su - oracle

$. grid_env

$asmca

Click on tab ( disk group)

-> Create/mount/dismount

Diskgroup Name : FRA

External

Choose - Disk2

AU - 4

Versions - 11.2.0.0.0

-? Adding disks :

righ click on diskgroup

then

choose - add disk.

Note : in FRA - fast recovery area

holds - backup sets,redo,control

archivelogs.

oracle recommends to manage with min two asm diskgroups.

DATA - to hold datafiles

FRA -

For every diskgroup , recommend with min 4 disks

for better performance with best practice.

with Allocation Unit (AU) - 4mb(best)

is a fundamental unit in which contiguous disk space is

allocated to asm files.

default - 1 , can have 2,4,8,16,32,64

To dismount - Diskgroups

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

right click - dismount on local node

Dismount on all nodes

Dropping a diskgroup

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

right click - drop diskgroup

Creating Diskgroup

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

sql>CREATE DISKGROUP fra EXTERNAL REDUNDANCY DISK 'ORCL:DISK2';

sql>select name,state from v$asm_diskgroup;

SQL> alter diskgroup FRA dismount;

sql>select name,state from v$asm_diskgroup;

SQL> alter diskgroup FRA mount;

sql>select name,state from v$asm_diskgroup;

SQL> DROP DISKGROUP FRA INCLUDING CONTENTS;

Mounting/Unmounting disk group.

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

SQL>alter diskgroup FRA mount;

SQL>alter diskgroup FRA dismount;

sql>alter diskgroup all mount;

sql>alter diskgroup all dismount;

Dropping Diskgroup:

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

SQL> DROP DISKGROUP FRA INCLUDING CONTENTS;

Add Disk 2

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

sql>CREATE DISKGROUP OCRvote EXTERNAL REDUNDANCY DISK 'ORCL:DISK2';

sql>ALTER DISKGROUP DATA ADD DISK 'ORCL:DISK2';

sql>select * from v$asm_operation; (on another terminal - monitor)

-- Drop a disk. if two disks are there

sql>ALTER DISKGROUP DATA DROP DISK DISK2;

sql>select * from v$asm_operation;

With wait - using runtime rebalance

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

ALTER DISKGROUP DATA ADD DISK

'ORCL:DISK2'

REBALANCE POWER 2 WAIT;

How to check consistency for metadata for all disks ?

ALTER DISKGROUP DATA CHECK ALL;

Drop Diskgroup using force.

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

If unable to mount ,use force

SQL> DROP DISKGROUP OCRVOTE FORCE;

ALTER DISKGROUP DATA ADD DISK

'/devices/diska5' NAME diska5,

'/devices/diska6' NAME diska6,

'/devices/diska7' NAME diska7,

'/devices/diska8' NAME diska8;

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

Creating of Diskgroup with Normal redundancy

CREATE DISKGROUP DATA NORMAL REDUNDANCY

FAILGROUP fg1 DISK '/devices/diskg1'

FAILGROUP fg2 DISK '/devices/diskg2'

QUORUM FAILGROUP fg3 DISK '/devices/diskg3'

ATTRIBUTE 'compatible.asm' = '11.2.0.0.0';

ALTER DISKGROUP data1 ADD DISK

'/devices/diskd*'

REBALANCE POWER 5 WAIT;

Explain With or with out rebalance operation while adding disk?

With out -

With option - then it runs with rebalance 5 and does not return until the rebalance operation is complete.

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

The size support by Type of redundancy

Each Diskgroup supports - size.

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

External - Supports up to 140PB from 11.1

till 10.1 - 16TB

Normal - 11.1 -? 23PB

10.1 -? 5.8TB

High - 11.1 -? 15PB

10.1 -? 3.9TB

ASM_POwer_limit supports up to 1024

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

ASM Commands - interactive mode.

-----------

$asmcmd

chdg Changes a disk group (add, drop, or rebalance).

chkdg Checks or repairs a disk group.

dropdg Drops a disk group.

iostat - Displays I/O statistics for disks.

lsattr - Lists the attributes of a disk group.

lsdg - Lists disk groups and their information.

lsdsk - Lists disks Oracle ASM disks.

lsod - Lists open devices.

md_backup - Creates a backup of the metadata of mounted disk groups.

md_restore - Restores disk groups from a backup of the metadata.

mkdg - Creates a disk group.

mount - Mounts a disk group.

The attribute can be set with SQL, ASMCMD, or ASMCA.

Brief few views to find information in ASM

v$asm_disk - Disk Utilization

v$asm_diskgroup

v$asm_client

v$asm_file

v$asm_disk_stat

v$asm_operation - For finding rebalacing status while add/remove disks

Tasks - ASM

-----------

Adding Diskgroup

Adding Disk to a Diskgroup

Dropping Disk

Dropping Diskgroup

Monitoring rebalancing operation

mounting/dismounting - diskgroups

ASM Instance - start/stop/status - using srvctl / sqlplus

Oracle recommends to manage with min two diskgroups.

DATA - to store datafiles,spfile,ocr/voting. ( while cluster install)

FRA - to store controlfiles,redo,archives,backupsets

OCRVOTE - to store ocr/vote if multiplexing.

asmca

asmcmd

Changing parameter in cluster nodes

use sid='*' to effect/change in all nodes of a cluster.

NON ASM to ASM

RAC Files

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

For Deinstalling - DB

Bring up cluster on all nodes - verify status

su - oracle

. db_env

cd $ORACLE_HOME/deinstall

./deinstall

Accept the inputs...done.

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

NOn asm - to ASM

deinstall Database

Bring down cluster services,crs

On rac1 ,

su - oracle

. db_env

/database/

./runInstaller

here choose file system(not asm)

dbname - prod

/oradata

sqlplus '/as sysdba'

shutdown immediate

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

ASM

instance

. grid_env

export ORACLE_HOME=/u01/app/11.2.0/grid/bin

export ORACLE_SID=+ASM1

Tools

----

asmca - configuration assistant - create diskgroups/drop / add disks/drop disks/mount/unmount

asmcmd - command line interpreter to manage files/diskgroups

ASM Views - brief information of ASM file/disks/diskgroups/status

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

v$asm_file

v$asm_diskgroups

V$asm_disks

v$asm_operation

v$asm_disk_stat

v$asm_client

ASM - Tasks

Monitor the usage of the diskgroup

Allocate disks to the diskgroup

Monitor rebalancing activity when disk was added/removed

List of diskgroups

list of files in diskgroup and its usage

diskgroup status - mounted/unmounted/online

Disks Provisioned/former/member - online/offline - status

Note: info on oracle ASM it my be differ from your environment production,development,testing

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME

Friday, August 14, 2020

Oracle ASM

ITIL Process

Blog Archive

Categories