##### To find ASM Diskgroup and Disks status
set lines 132
col name format a14
col PATH format a33
select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB/1024,FREE_MB/1024 from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TYPE TOTAL_MB FREE_MB V
------------ ------------------------------ ----------- ------ ---------- ---------- -
1 OCR_VOTE MOUNTED EXTERN 152999 152603 Y
2 DB_DATA MOUNTED EXTERN 812000 810198 N
In 11g,
set lines 132
col name format a14
col PATH format a33
select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB/1024,FREE_MB/1024, VOTING_FILES from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TYPE TOTAL_MB FREE_MB V
------------ ------------------------------ ----------- ------ ---------- ---------- -
1 OCR_VOTE MOUNTED EXTERN 152999 152603 Y
2 DB_DATA MOUNTED EXTERN 812000 810198 N
select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,VOTING_FILE,name,path from v$asm_disk;
GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE V NAME PATH
------------ ----------- ------- ------------ ------- -------- - -------------- ---------------------------------
1 0 CACHED MEMBER ONLINE NORMAL Y OCR_VOTE_0 /dev/oracleasm/disks/OCR_VOTE01
2 3 CACHED MEMBER ONLINE NORMAL N DB_DATA_3 /dev/oracleasm/disks/DB_DATA04
2 2 CACHED MEMBER ONLINE NORMAL N DB_DATA_2 /dev/oracleasm/disks/DB_DATA03
2 1 CACHED MEMBER ONLINE NORMAL N DB_DATA_1 /dev/oracleasm/disks/DB_DATA02
2 0 CACHED MEMBER ONLINE NORMAL N DB_DATA_0 /dev/oracleasm/disks/DB_DATA01
6 rows selected.
-------------------------------------------------------------------------------------------------------------
– To Find Out Disk Group Status/Discover/Size –
set lin 200 pages 100
col name for a20
col HEADER_STATU for a20
col path for a20
SELECT name, header_status, path,mode_status,state,os_mb,total_mb,hot_used_mb,cold_used_mb,failgroup from v$asm_disk
/
NAME HEADER_STATU PATH MODE_ST STATE OS_MB TOTAL_MB HOT_USED_MB COLD_USED_MB FAILGROUP
-------------------- ------------ -------------------- ------- -------- ---------- ---------- ----------- ------------ ------------------------------
CANDIDATE /dev/asmdisk7 ONLINE NORMAL 509 0 0 0
CANDIDATE /dev/asmdisk8 ONLINE NORMAL 1019 0 0 0
DISKDATAFILE MEMBER /dev/asmdisk1 ONLINE NORMAL 1019 1019 0 0 CONTROLLER1
DISKALLBACKUP MEMBER /dev/asmdisk3 ONLINE NORMAL 509 509 0 0 CONTROLLER1
DISKSYSFILES MEMBER /dev/asmdisk2 ONLINE NORMAL 509 509 0 0 CONTROLLER1
DISKBKP MEMBER /dev/asmdisk6 ONLINE NORMAL 509 509 0 0 CONTROLLER2
DISKBKP MEMBER /dev/asmdisk5 ONLINE NORMAL 509 509 0 0 CONTROLLER2
DISKBKP MEMBER /dev/asmdisk4 ONLINE NORMAL 509 509 0 0 CONTROLLER2
8 rows selected.
– Adding some more space Avl from Cadidate Disk:Candidate Show it can be used to add new diskgroup/file
SQL> ALTER DISKGROUP DATAFILE ADD DISK ‘/dev/asmdisk8' name DISKDATAFILE2;
Diskgroup altered.
—
SELECT name, type, total_mb, free_mb, required_mirror_free_mb, usable_file_mb FROM V$ASM_DISKGROUP;
NAME TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ------ ---------- ---------- ----------------------- --------------
BACKUP NORMAL 1018 914 0 457
DATAFILE NORMAL 2547 2433 52 1190
SYSTEM NORMAL 1018 914 0 457
SELECT dg.name AS diskgroup, SUBSTR(d.name,1,16) AS asmdisk, SUBSTR(dg.compatibility,1,12) AS asm_compat,
SUBSTR(dg.database_compatibility,1,12) AS db_compat FROM V$ASM_DISKGROUP dg, V$ASM_DISK d
WHERE dg.name in ('DATAFILE','SYSTEM', 'BACKUP') AND dg.group_number = d.group_number;
DISKGROUP ASMDISK ASM_COMPAT DB_COMPAT
------------------------------ ---------------- ------------ ------------
BACKUP DISKBKP 11.1.0.0.0 11.1.0.0.0
BACKUP DISKALLBACKUP 11.1.0.0.0 11.1.0.0.0
DATAFILE DISKDATAFILE 11.1.0.0.0 11.1.0.0.0
DATAFILE DISKBKP 11.1.0.0.0 11.1.0.0.0
DATAFILE DISKDATAFILE2 11.1.0.0.0 11.1.0.0.0
SYSTEM DISKBKP 11.1.0.0.0 11.1.0.0.0
SYSTEM DISKSYSFILES 11.1.0.0.0 11.1.0.0.0
– Some usefull SQL command used for Diskgroup Adminstration
SELECT GROUP_NUMBER, OPERATION, STATE FROM V$ASM_OPERATION;
SELECT group_number, os_name FROM V$ASM_USER;
– Drop and Alter
– To Drop Disk group
DROP DISKGROUP DATAFILE FORCE INCLUDING CONTENTS;
ALTER DISKGROUP DATAFILE ADD DISK ‘/dev/asmdisk8' name DISKDATAFILE2;
– To mount/unmount Diskgroup
ALTER DISKGROUP ALL DISMOUNT;
ALTER DISKGROUP ALL MOUNT;
ALTER DISKGROUP DATAFILE DISMOUNT;
ALTER DISKGROUP DATAFILE MOUNT;
-- Checking Metadata .
SQL> ALTER DISKGROUP DATAFILE CHECK ALL;
Diskgroup altered.
== ><======><====Command line utility asmcmd ====><===><
its similler to work on linux and solaris.
[oracle@node2 ~]$ asmcmd -V
asmcmd version 11.2.0.0.0
ASMCMD> ls
BACKUP/
DATAFILE/
SYSTEM/
ASMCMD> ls -l +
State Type Rebal Name
MOUNTED EXTERN N BACKUP/
MOUNTED EXTERN N DATAFILE/
MOUNTED EXTERN N SYSTEM/
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 1018 966 0 966 0 N BACKUP/
MOUNTED EXTERN N 512 4096 1048576 2038 822 0 822 0 N DATAFILE/
MOUNTED EXTERN N 512 4096 1048576 1018 627 0 627 0 N SYSTEM/
ASMCMD> pwd
+DATAFILE/DDB2
ASMCMD> lsdsk
Path
/dev/asmdisk1
/dev/asmdisk2
/dev/asmdisk3
/dev/asmdisk4
/dev/asmdisk5
/dev/asmdisk8
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y DATAFILE/
Y PARAMETERFILE/
Y TEMPFILE/
N spfileDDB2.ora => +DATAFILE/DDB2/PARAMETERFILE/spfile.261.774220061
N sysaux.dbf => +DATAFILE/DDB2/DATAFILE/SYSAUX.257.774219591
N system.dbf => +DATAFILE/DDB2/DATAFILE/SYSTEM.256.774219589
N temp.dbf => +DATAFILE/DDB2/TEMPFILE/TEMP.260.774219881
N undo.dbf => +DATAFILE/DDB2/DATAFILE/UNDOTBS1.258.774219591
N users.dbf => +DATAFILE/DDB2/DATAFILE/USERS.259.774219591
ASMCMD> ls -s
Block_Size Blocks Bytes Space Name
DATAFILE/
PARAMETERFILE/
TEMPFILE/
spfileDDB2.ora => +DATAFILE/DDB2/PARAMETERFILE/spfile.261.774220061
sysaux.dbf => +DATAFILE/DDB2/DATAFILE/SYSAUX.257.774219591
system.dbf => +DATAFILE/DDB2/DATAFILE/SYSTEM.256.774219589
temp.dbf => +DATAFILE/DDB2/TEMPFILE/TEMP.260.774219881
undo.dbf => +DATAFILE/DDB2/DATAFILE/UNDOTBS1.258.774219591
users.dbf => +DATAFILE/DDB2/DATAFILE/USERS.259.774219591
#Some More Command findout using help
ASMCMD> help
commands:
--------
md_backup, md_restore
lsattr, setattr
cd, cp, du, find, help, ls, lsct, lsdg, lsof, mkalias
mkdir, pwd, rm, rmalias
chdg, chkdg, dropdg, iostat, lsdsk, lsod, mkdg, mount
offline, online, rebal, remap, umount
dsget, dsset, lsop, shutdown, spbackup, spcopy, spget
spmove, spset, startup
chtmpl, lstmpl, mktmpl, rmtmpl
chgrp, chmod, chown, groups, grpmod, lsgrp, lspwusr, lsusr
mkgrp, mkusr, orapwusr, passwd, rmgrp, rmusr
volcreate, voldelete, voldisable, volenable, volinfo
volresize, volset, volstat
ASMCMD>
===============================
#########Viewing disks in disk groups with V$ASM_DISK
col ASMDISK for a15
set lines 200
SELECT SUBSTR(d.name,1,16) AS asmdisk, d.mount_status, d.state,
dg.name AS diskgroup FROM V$ASM_DISKGROUP dg, V$ASM_DISK d
WHERE dg.group_number = d.group_number;
###Query to find diskgroup name, asm diskname, disk path total space and free space in the disk group
SELECT SUBSTR(dg.name,1,16) AS diskgroup, SUBSTR(d.name,1,16) AS asmdisk,
d.PATH, d.total_mb/1024 as TOTAL_GB , d.free_mb/1024 as FREE_GB, d.state, SUBSTR(d.failgroup,1,16) AS failgroup
FROM V$ASM_DISKGROUP dg, V$ASM_DISK d WHERE dg.group_number = d.group_number;
No comments:
Post a Comment