ASM 磁盘组的的scrip
之前经常用如下方式进行查询：
步骤 1 以oracle用户登录系统。
步骤 2 执行如下命令改变ORACLE_SID环境变量。
$ export ORACLE_SID=+ASM1【1或者2】
需要通过ps -ef|grep ASM 确认实例名称
步骤 3 执行如下命令进入ASM管理状态。
$ asmcmd -p
步骤 4 执行如下命令查看存储空间。
$ ASMCMD> lsdg
[@more@]
最近发现如下的简易方式：
export ORACLE_SID=+ASM【1或者2】
需要通过ps -ef|grep ASM 确认实例名称
sqlplus "/as sysdba"
select name,total_mb,free_mb from v$asm_diskgroup;
或
select name,TYPE,total_mb,free_mb,((total_mb-free_mb)/total_mb)*100 as usage from v$asm_diskgroup;
how to add asm_disk in aix:
http://blog.itpub.net/21605631/viewspace-734488/
http://blog.itpub.net/32980/viewspace-1057131/
SELECT SUBSTR(D.NAME,1,16) AS ASMDISK, D.MOUNT_STATUS, D.STATE,D.PATH,
DG.NAME AS DISKGROUP FROM V$ASM_DISKGROUP DG, V$ASM_DISK D
WHERE DG.GROUP_NUMBER = D.GROUP_NUMBER;
sample 1:
1) As Oracle or Grid OS user, run the next commands on each node (if ASMLIB API is used):
$> id
$> /etc/init.d/oracleasm listdisks
$> /etc/init.d/oracleasm querydisk <each disk from previous output>
Note: All the disks must be visible from each node.
2) Confirm the disks are visible (I/O) at OS level (as oracle or grid OS user) from each node:
$> ls -l <disk name>
$> dd if=<raw/character or block partition device in question> of=/dev/null count=100 bs=8192
Example:
$> dd if=/dev/raw/raw1 of=/dev/null count=100 bs=8192 # raw device
$> dd if=/dev/oracleasm/disks/PRODA07 of=/dev/null count=100 bs=8192 # ASMLIB device
$> dd if=/dev/rdsk/c1t3d0s6 of=/dev/null count=100 bs=8192 # raw device
3) Verify that kfed can read on the candidate disk(s) at OS level (as oracle or grid OS user) from each node:
Example:
$> kfed read /dev/oracleasm/disks/PRODA07
$> kfed read /dev/rdsk/c1t3d0s6
4) Create a new temporary diskgroup:
5) Check if the diskgroup is created and mounted:
6) If so, then manually mount it on the other ASM instance(s):
SQL> SELECT STATE, NAME FROM V$ASM_DISKGROUP;
7) If the previous steps had success and the TEST diskgroup can be successfully mounted (on each ASM instance), then drop the TEST diskgroup as follow:
SQL> DROP DISKGROUP TEST; --(from the ASM instance, which the diskgroup is still mounted).
8) Then the candidate disk is ready to be added to the desired diskgroup:
These are the shell scripts to monitor sgastat and rebalance progress.
1. sgastat.sh <---------------
#!/bin/sh
#for (( i=0; i < 1440 ; i++ )) ;
while true
do
sqlplus -s / as sysasm <<EOF
set pagesize 1000
set feedback off
set head off
select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS' ), decode(pool, NULL, 'SGA', pool)||','||name||','||bytes from v\$sgastat
/
EOF
sleep 60
done
-------
$ grep 'shared pool,free memory' /tmp/sgastat.out
2. rebalance.sh <---------------
#!/bin/sh
#for (( i=0; i < 1440 ; i++ )) ;
while true
do
echo -n "Time:" `date` ;
sqlplus -s / as sysasm <<EOF
set linesize 200
set feedback off
set head off
select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS' ), v.* from v\$asm_operation v
/
EOF
sleep 60
done
-------
$ grep 'REBAL RUN' /tmp/rebalance.out
###################
SPOOL ASM<#>_GENERIC_ASM_METADATA.html
-- ASM VERSIONS 10.1, 10.2, 11.1, 11.2, 12.1 & 12.2
SET MARKUP HTML ON
SET ECHO ON
SET PAGESIZE 200
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SELECT 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , SYSDATE " " FROM DUAL;
SELECT 'INSTANCE NAME: ==)> ' , INSTANCE_NAME " " FROM V$INSTANCE;
SELECT 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " FROM V$SESSION WHERE PROGRAM LIKE '%SMON%';
SELECT * FROM V$INSTANCE;
SELECT * FROM GV$INSTANCE;
SELECT * FROM V$ASM_DISKGROUP;
SELECT GROUP_NUMBER, DISK_NUMBER, MOUNT_STATUS, HEADER_STATUS, MODE_STATUS, STATE, OS_MB, TOTAL_MB, FREE_MB, NAME, FAILGROUP, PATH
FROM V$ASM_DISK ORDER BY GROUP_NUMBER, FAILGROUP, DISK_NUMBER;
SELECT * FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;
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;
SELECT * FROM V$ASM_CLIENT;
SELECT DG.NAME AS DISKGROUP, SUBSTR(C.INSTANCE_NAME,1,12) AS INSTANCE,
SUBSTR(C.DB_NAME,1,12) AS DBNAME, SUBSTR(C.SOFTWARE_VERSION,1,12) AS SOFTWARE,
SUBSTR(C.COMPATIBLE_VERSION,1,12) AS COMPATIBLE
FROM V$ASM_DISKGROUP DG, V$ASM_CLIENT C
WHERE DG.GROUP_NUMBER = C.GROUP_NUMBER;
SELECT * FROM V$ASM_ATTRIBUTE;
SELECT * FROM V$ASM_OPERATION;
SELECT * FROM GV$ASM_OPERATION;
SELECT * FROM V$VERSION;
SELECT * FROM V$ASM_ACFSSNAPSHOTS;
SELECT * FROM V$ASM_ACFSVOLUMES;
SELECT * FROM V$ASM_FILESYSTEM;
SELECT * FROM V$ASM_VOLUME;
SELECT * FROM V$ASM_VOLUME_STAT;
SELECT * FROM V$ASM_USER;
SELECT * FROM V$ASM_USERGROUP;
SELECT * FROM V$ASM_USERGROUP_MEMBER;
SELECT * FROM V$ASM_DISK_IOSTAT;
SELECT * FROM V$ASM_DISK_STAT;
SELECT * FROM V$ASM_DISKGROUP_STAT;
SELECT * FROM V$ASM_TEMPLATE;
SHOW PARAMETER
SHOW SGA
!echo "SELECT '" > /tmp/GPNPTOOL.SQL 2> /dev/null
! $ORACLE_HOME/bin/gpnptool get >> /tmp/GPNPTOOL.SQL 2>> /dev/null
!echo "' FROM DUAL;" >> /tmp/GPNPTOOL.SQL 2>> /dev/null
! cat /tmp/GPNPTOOL.SQL
SET ECHO OFF
--DISPLAYS INFORMATION ABOUT THE CONTENTS OF THE SPFILE.
SELECT * FROM V$SPPARAMETER ORDER BY 2;
SELECT * FROM GV$SPPARAMETER ORDER BY 3;
--DISPLAYS INFORMATION ABOUT THE INITIALIZATION PARAMETERS THAT ARE CURRENTLY IN EFFECT IN THE INSTANCE.
SELECT * FROM V$SYSTEM_PARAMETER ORDER BY 2;
SELECT * FROM GV$SYSTEM_PARAMETER ORDER BY 3;
-- 12C ACFS VIEWS
SELECT * FROM V$ASM_ACFS_ENCRYPTION_INFO;
SELECT * FROM V$ASM_ACFSREPL;
SELECT * FROM V$ASM_ACFSREPLTAG;
SELECT * FROM V$ASM_ACFS_SEC_ADMIN;
SELECT * FROM V$ASM_ACFS_SEC_CMDRULE;
SELECT * FROM V$ASM_ACFS_SEC_REALM;
SELECT * FROM V$ASM_ACFS_SEC_REALM_FILTER;
SELECT * FROM V$ASM_ACFS_SEC_REALM_GROUP;
SELECT * FROM V$ASM_ACFS_SEC_REALM_USER;
SELECT * FROM V$ASM_ACFS_SEC_RULE;
SELECT * FROM V$ASM_ACFS_SEC_RULESET;
SELECT * FROM V$ASM_ACFS_SEC_RULESET_RULE;
SELECT * FROM V$ASM_ACFS_SECURITY_INFO;
SELECT * FROM V$ASM_ACFSTAG;
-- 12C ASM AUDIT VIEWS
SELECT * FROM V$ASM_AUDIT_CLEAN_EVENTS;
SELECT * FROM V$ASM_AUDIT_CLEANUP_JOBS;
SELECT * FROM V$ASM_AUDIT_CONFIG_PARAMS;
SELECT * FROM V$ASM_AUDIT_LAST_ARCH_TS;
-- 12C ASM ESTIMATE VIEW
SELECT * FROM V$ASM_ESTIMATE;
SELECT * FROM GV$ASM_ESTIMATE;
SPOOL OFF
EXIT
########### sample 0
How To Gather & Backup ASM/ACFS Metadata In A Formatted Manner version 10.1, 10.2, 11.1, 11.2, 12.1 and 12.2? (文档 ID 470211.1)
Solution
Script #1 (Generic ASM metadata):
Script #2 (ASM Files & ASM Alias):
Script #3 (ASM Full Path Alias Directory):
Script #4 (ASMCMD commands to gather complementary metadata information):
Example Output (Script #1):
Example Output (Script #3):
Example Output (Script #4):
References
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.2.0.1 [Release 10.1 to 12.2]
Exadata Database Machine X2-2 Hardware - Version All Versions to All Versions [Release All Releases]
Information in this document applies to any platform.
***Checked for relevance on 30-Apr-2010***
GOAL
You can use the SQLPLUS “SET MARKUP HTML ON” to generate a formatted report from the existing ASM metadata and also using ASMCMD commands to gather complementary metadata information, which can be used as well as an ASM metadata backup or as a report for Oracle Support to troubleshot ASM issues.
Ask Questions, Get Help, And Share Your Experiences With This Article
Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts?
Click here to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article.
Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for Database Install/Upgrade.
SOLUTION
1) Connect to your ASM instance(s) and execute the first 3 scripts below (on each node if this is RAC configuration).
2) Also, execute the script #4 (located below) through ASMCMD to gather complementary metadata information (on each node if this is RAC configuration).
Note 1: Scripts #1, #2 & #3 could report the <<< ORA-01219: database not open: queries allowed on fixed tables/views only >>> error on some specific queries, due to some V$ASM_* performance views are only present on early ASM releases, therefore this errors can be ignored depending the ASM release.
Note: 2: This is the same case with the Script # 4 (ASMCMD commands), which could not interpret some ASMCMD commands, due to the some commands/options are not present on early releases.
Script #1 (Generic ASM metadata):
-- Author: Esteban D. Bernal
-- Property: Oracle Corporation
SPOOL ASM<#>_GENERIC_ASM_METADATA.html
-- ASM VERSIONS 10.1, 10.2, 11.1, 11.2, 12.1 & 12.2
SET MARKUP HTML ON
SET ECHO ON
SET PAGESIZE 200
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SELECT 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , SYSDATE " " FROM DUAL;
SELECT 'INSTANCE NAME: ==)> ' , INSTANCE_NAME " " FROM V$INSTANCE;
SELECT 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " FROM V$SESSION WHERE PROGRAM LIKE '%SMON%';
SELECT * FROM V$INSTANCE;
SELECT * FROM GV$INSTANCE;
SELECT * FROM V$ASM_DISKGROUP;
SELECT GROUP_NUMBER, DISK_NUMBER, MOUNT_STATUS, HEADER_STATUS, MODE_STATUS, STATE, OS_MB, TOTAL_MB, FREE_MB, NAME, FAILGROUP, PATH
FROM V$ASM_DISK ORDER BY GROUP_NUMBER, FAILGROUP, DISK_NUMBER;
SELECT * FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;
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;
SELECT * FROM V$ASM_CLIENT;
SELECT DG.NAME AS DISKGROUP, SUBSTR(C.INSTANCE_NAME,1,12) AS INSTANCE,
SUBSTR(C.DB_NAME,1,12) AS DBNAME, SUBSTR(C.SOFTWARE_VERSION,1,12) AS SOFTWARE,
SUBSTR(C.COMPATIBLE_VERSION,1,12) AS COMPATIBLE
FROM V$ASM_DISKGROUP DG, V$ASM_CLIENT C
WHERE DG.GROUP_NUMBER = C.GROUP_NUMBER;
SELECT * FROM V$ASM_ATTRIBUTE;
SELECT * FROM V$ASM_OPERATION;
SELECT * FROM GV$ASM_OPERATION;
SELECT * FROM V$VERSION;
SELECT * FROM V$ASM_ACFSSNAPSHOTS;
SELECT * FROM V$ASM_ACFSVOLUMES;
SELECT * FROM V$ASM_FILESYSTEM;
SELECT * FROM V$ASM_VOLUME;
SELECT * FROM V$ASM_VOLUME_STAT;
SELECT * FROM V$ASM_USER;
SELECT * FROM V$ASM_USERGROUP;
SELECT * FROM V$ASM_USERGROUP_MEMBER;
SELECT * FROM V$ASM_DISK_IOSTAT;
SELECT * FROM V$ASM_DISK_STAT;
SELECT * FROM V$ASM_DISKGROUP_STAT;
SELECT * FROM V$ASM_TEMPLATE;
SHOW PARAMETER
SHOW SGA
!echo "SELECT '" > /tmp/GPNPTOOL.SQL 2> /dev/null
! $ORACLE_HOME/bin/gpnptool get >> /tmp/GPNPTOOL.SQL 2>> /dev/null
!echo "' FROM DUAL;" >> /tmp/GPNPTOOL.SQL 2>> /dev/null
! cat /tmp/GPNPTOOL.SQL
SET ECHO OFF
--DISPLAYS INFORMATION ABOUT THE CONTENTS OF THE SPFILE.
SELECT * FROM V$SPPARAMETER ORDER BY 2;
SELECT * FROM GV$SPPARAMETER ORDER BY 3;
--DISPLAYS INFORMATION ABOUT THE INITIALIZATION PARAMETERS THAT ARE CURRENTLY IN EFFECT IN THE INSTANCE.
SELECT * FROM V$SYSTEM_PARAMETER ORDER BY 2;
SELECT * FROM GV$SYSTEM_PARAMETER ORDER BY 3;
-- 12C ACFS VIEWS
SELECT * FROM V$ASM_ACFS_ENCRYPTION_INFO;
SELECT * FROM V$ASM_ACFSREPL;
SELECT * FROM V$ASM_ACFSREPLTAG;
SELECT * FROM V$ASM_ACFS_SEC_ADMIN;
SELECT * FROM V$ASM_ACFS_SEC_CMDRULE;
SELECT * FROM V$ASM_ACFS_SEC_REALM;
SELECT * FROM V$ASM_ACFS_SEC_REALM_FILTER;
SELECT * FROM V$ASM_ACFS_SEC_REALM_GROUP;
SELECT * FROM V$ASM_ACFS_SEC_REALM_USER;
SELECT * FROM V$ASM_ACFS_SEC_RULE;
SELECT * FROM V$ASM_ACFS_SEC_RULESET;
SELECT * FROM V$ASM_ACFS_SEC_RULESET_RULE;
SELECT * FROM V$ASM_ACFS_SECURITY_INFO;
SELECT * FROM V$ASM_ACFSTAG;
-- 12C ASM AUDIT VIEWS
SELECT * FROM V$ASM_AUDIT_CLEAN_EVENTS;
SELECT * FROM V$ASM_AUDIT_CLEANUP_JOBS;
SELECT * FROM V$ASM_AUDIT_CONFIG_PARAMS;
SELECT * FROM V$ASM_AUDIT_LAST_ARCH_TS;
-- 12C ASM ESTIMATE VIEW
SELECT * FROM V$ASM_ESTIMATE;
SELECT * FROM GV$ASM_ESTIMATE;
SPOOL OFF
EXIT
Script #2 (ASM Files & ASM Alias):
-- Author: Esteban D. Bernal
-- Property: Oracle Corporation
spool asm<#>_alias+files.html
-- ASM VERSIONS 10.1, 10.2, 11.1, 11.2, 12.1 & 12.2
SET MARKUP HTML ON
set echo on
set pagesize 200
COLUMN BYTES FORMAT 9999999999999999
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , sysdate " " from dual;
select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';
select * from v$asm_alias;
select * from v$asm_file;
show parameter asm
show parameter cluster
show parameter instance_type
show parameter instance_name
show parameter spfile
show sga
spool off
exit
Script #3 (ASM Full Path Alias Directory):
-- Author: Esteban D. Bernal
-- Property: Oracle Corporation
spool asm<#>_full_path_alias_directory.html
-- ASM VERSIONS 10.1, 10.2, 11.1, 11.2, 12.1 & 12.2
SET MARKUP HTML ON
set echo on
set pagesize 200
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , sysdate " " from dual;
select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';
SELECT CONCAT('+'||GNAME, SYS_CONNECT_BY_PATH(ANAME, '/'))
FULL_PATH, SYSTEM_CREATED, ALIAS_DIRECTORY, FILE_TYPE
FROM ( SELECT B.NAME GNAME, A.PARENT_INDEX PINDEX,
A.NAME ANAME, A.REFERENCE_INDEX RINDEX,
A.SYSTEM_CREATED, A.ALIAS_DIRECTORY,
C.TYPE FILE_TYPE
FROM V$ASM_ALIAS A, V$ASM_DISKGROUP B, V$ASM_FILE C
WHERE A.GROUP_NUMBER = B.GROUP_NUMBER
AND A.GROUP_NUMBER = C.GROUP_NUMBER(+)
AND A.FILE_NUMBER = C.FILE_NUMBER(+)
AND A.FILE_INCARNATION = C.INCARNATION(+)
)
START WITH (MOD(PINDEX, POWER(2, 24))) = 0
CONNECT BY PRIOR RINDEX = PINDEX;
spool off
exit
Script #4 (ASMCMD commands to gather complementary metadata information):
################################################################################################
### Script Name: asmcmd_script.sh ###
################################################################################################
### The next script generates additional ASM metadata information thru the ASMCMD interface ###
################################################################################################
### Author: Esteban D. Bernal ###
################################################################################################
### Property: Oracle Corporation ###
################################################################################################
echo "ASMCMD commands to gather complementary metadata information:" > /tmp/asmcmd_script.out 2> /tmp/asmcmd_script.out
echo "==================================" >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo " " >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
asmcmd -p ls -ls >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo "==================================" >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo " " >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
asmcmd -p lsattr >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo "==================================" >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo " " >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
asmcmd -p lsct >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo "==================================" >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo " " >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
asmcmd -p lsdg >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo "==================================" >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo " " >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
asmcmd -p lsdsk >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo "==================================" >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo " " >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
asmcmd -p lsof >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo "==================================" >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo " " >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
asmcmd -p lsod >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo "==================================" >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo " " >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
asmcmd -p iostat >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo "==================================" >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo " " >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
asmcmd -p dsget >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo "==================================" >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo " " >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
asmcmd -p lsop >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo "==================================" >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo " " >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
asmcmd -p spget >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo "==================================" >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo " " >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
asmcmd -p lstmpl >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo "==================================" >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo " " >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
asmcmd -p lsusr >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo "==================================" >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo " " >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
asmcmd -p lsgrp >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo "==================================" >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo " " >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
asmcmd -p lspwusr >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo "==================================" >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo " " >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
asmcmd -p volinfo -a >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo "==================================" >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
echo " " >> /tmp/asmcmd_script.out 2>> /tmp/asmcmd_script.out
##############################################################################################################
Note: On Windows environments the “2> /tmp/asmcmd_script.out” or “2>> /tmp/asmcmd_script.out” statements is not required, thus it can be omitted.
From 12.1.0.2, command "asmcmd -p volinfo -a" should be replaced to "asmcmd -p volinfo --all"
############## sample 2 scirpt 2:
Useful SQL statement
SQL1： How v$asm_file.space is calculated from v$asm_file.bytes depending on striping and redundancy scheme of a diskgroup.
- Please note that space allocation on ASM diskgroup is based on v$asm_file.space, not from v$asm_file.bytes.
-------------------------
set linesize 240
col g_n format 999
col f_n format 9999
col bytes format 999,999,999,999
col space format 999,999,999,999
col a_i format 999
col blocks format 999,999
col block_size format 999,999
col ftype format a16
col dir format a3
col s_created format a10
col full_alias_path format a60
col striped format a6
col rdun format a6
col au format 99999999
col calculated format 999,999,999,999
select x.gnum g_n,
x.filnum f_n,
substr(x.full_alias_path,1, 60) full_alias_path,
f.striped,
f.redundancy rdun,
f.bytes,
f.space,
case when calculated / x.au > 60 then calculated + 3 * x.au -- Over 60 direct extents consideration
else calculated
end calculated
from ( SELECT gnum,filnum,au, concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
from ( SELECT g.name gname,
g.allocation_unit_size au,
a.parent_index pindex,
a.name aname,
a.reference_index rindex,
a.group_number gnum,
a.file_number filnum
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex) x,
(select f.group_number gnum,
f.file_number filnum,
f.type ftype ,
f.bytes,
f.space,
f.blocks,
f.block_size,
f.striped,
f.redundancy,
case f.striped when 'FINE'
then ceil(((f.blocks * f.block_size + 1) / g.allocation_unit_size) / 8)
* decode(f.redundancy, 'HIGH', 3 , 'MIRROR', 2, 'UNPROT', 1 ) * 8 * g.allocation_unit_size
else ceil((f.blocks * f.block_size + 1) / g.allocation_unit_size)
* decode(f.redundancy, 'HIGH', 3 , 'MIRROR', 2, 'UNPROT', 1 ) * g.allocation_unit_size
end calculated
from v$asm_file f , v$asm_diskgroup g
where f.group_number = g.group_number
and g.name='ARCHIVE_LOG' ---DG name
order by f.group_number,file_number) f
where x.filnum != 4294967295
and x.gnum=f.gnum and x.filnum=f.filnum
order by full_alias_path
/
-------------------------
SQL2. How v$recovery_file_dest.space_used is calculated from v$asm_file.bytes.
-------------------------
set linesize 140
col g_n format 999
col f_n format 9999
col bytes format 999,999,999,999
col space format 999,999,999,999
col a_i format 999
col blocks format 999,999
col block_size format 999,999
col ftype format a16
col dir format a3
col s_created format a10
col full_alias_path format a40
col striped format a6
col redundancy format a10
col au format 99999999
col size_au format 999,999,999,999
break on report
compute sum of size_au on report
select x.gnum g_n,
x.filnum f_n,
substr(x.full_alias_path,1, 40) full_alias_path,
f.ftype,
f.bytes,
ceil((f.bytes + 1 ) / x.au ) * x.au size_au, -- take into account only AU boundary of actual bytes
f.space,
f.striped,
f.redundancy
from (select gname, gnum,filnum, au, concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
from ( select g.name gname,
g.allocation_unit_size au,
a.parent_index pindex,
a.name aname,
a.reference_index rindex,
a.group_number gnum,
a.file_number filnum
from v$asm_alias a, v$asm_diskgroup g
where a.group_number = g.group_number)
start with (mod(pindex, power(2, 24))) = 0 connect by prior rindex = pindex
) x,
(select f.group_number gnum,
f.file_number filnum,
f.type ftype ,
f.bytes,
f.space,
f.redundancy,
f.striped
from v$asm_file f
order by f.group_number,file_number
) f
where x.filnum != 4294967295
and x.gname = '&DB_RECOVERY_FILE_DEST' -- Put the diskgroup name seen in db_recovery_file_dest
and x.gnum=f.gnum
and x.filnum=f.filnum
/
---------------------------------
SQL3. v$asm_template - striping and redundancy information for ASM files.
---------------------------
set linesize 140
col g_n format 99
col e_n format 999
col name format a20
select group_number g_n,
entry_number e_n,
name,
redundancy,
stripe,
system
from v$asm_template
order by g_n, e_n
/
---------------------------
SQL4. SQL for querying v$recovery_file_dest
---------------------------------
set linesize 140
col name format a20
col space_limit format 999,999,999,999
col space_used format 999,999,999,999
col space_reclaimable format 999,999,999,999
col number_of_files format 999,999,999,999
select name,
space_limit,
space_used,
space_reclaimable,
number_of_files
from v$recovery_file_dest
/
---------------------------
SQL5. asmfile create_date checking
This is an intended behavior implemented in 10.2. File modification date is always rounded off to nearest hour in past, update is performed only if previous timestamp is different from current value after rounding off. 只显示整数时间点，可能与实际时间有一小时的差异
---------------------------------
select name from v$archived_log
asmcmd ls -ltr +ARCHIVE_LOG/rc/archivelog/2018_10_26/
/
#### sample 4
asmcmd_script.OUT
OUTPUT: asmcmd_script.OUT
ASMCMD commands to gather complementary metadata information:
================================== $ asmcmd -p ls -ls --- display extended file information
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 4096 1048576 40944 18471 10236 4117 0 N DATA/
MOUNTED EXTERN N 512 4096 1048576 40952 31211 0 31211 0 N FRA2/
MOUNTED NORMAL N 512 4096 4194304 6132 4960 2044 1458 0 Y OCR/
MOUNTED NORMAL N 512 4096 1048576 2046 1746 0 873 0 N TEST_NEW/
================================== Attributes for Diskgroup : DATA
$ asmcmd lsattr -G DATA -l
Name Value
access_control.enabled FALSE
access_control.umask 066
au_size 1048576
cell.smart_scan_capable FALSE
compatible.asm 11.2.0.0.0
compatible.rdbms 10.1.0.0.0
disk_repair_time 3.6h
sector_size 512
------------------------------------------
Attributes for Diskgroup : FRA2
$ asmcmd lsattr -G FRA2 -l
Name Value
access_control.enabled FALSE
access_control.umask 066
au_size 1048576
cell.smart_scan_capable FALSE
compatible.asm 11.2.0.0.0
compatible.rdbms 11.2.0.0.0
disk_repair_time 3.6h
sector_size 512
------------------------------------------
Attributes for Diskgroup : OCR
$ asmcmd lsattr -G OCR -l
Name Value
access_control.enabled FALSE
access_control.umask 066
au_size 4194304
cell.smart_scan_capable FALSE
compatible.asm 11.2.0.0.0
compatible.rdbms 11.2.0.0.0
disk_repair_time 3.6h
sector_size 512
------------------------------------------
Attributes for Diskgroup : TEST_NEW
$ asmcmd lsattr -G TEST_NEW -l
Name Value
access_control.enabled FALSE
access_control.umask 066
au_size 1048576
cell.smart_scan_capable FALSE
compatible.asm 11.2.0.0.0
compatible.rdbms 10.1.0.0.0
disk_repair_time 3.6h
sector_size 512
------------------------------------------
================================== $ asmcmd -p lsct --- Lists information about current Oracle ASM clients from the V$ASM_CLIENT view
DB_Name Status Software_Version Compatible_version Instance_Name Disk_Group
+ASM CONNECTED 11.2.0.4.0 11.2.0.4.0 +ASM1 OCR
+ASM CONNECTED 11.2.0.4.0 11.2.0.4.0 +ASM1 DATA
grac4 CONNECTED 11.2.0.4.0 11.2.0.4.0 grac41 DATA
grac4 CONNECTED 11.2.0.4.0 11.2.0.4.0 grac41 FRA2
================================== $ asmcmd -p lsdg --- List Diskgroup details
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 4096 1048576 40944 18471 10236 4117 0 N DATA/
MOUNTED EXTERN N 512 4096 1048576 40952 31211 0 31211 0 N FRA2/
MOUNTED NORMAL N 512 4096 4194304 6132 4960 2044 1458 0 Y OCR/
MOUNTED NORMAL N 512 4096 1048576 2046 1746 0 873 0 N TEST_NEW/
================================== $ asmcmd -p lsdsk --- List ASM disk details
Path
/dev/asm_data_10G_disk0
/dev/asm_data_10G_disk1
/dev/asm_data_10G_disk2
/dev/asm_data_10G_disk3
/dev/asm_ocr_11204_2G_disk1
/dev/asm_ocr_11204_2G_disk2
/dev/asm_ocr_11204_2G_disk3
/dev/asm_test_1G_disk1
/dev/asm_test_1G_disk2
/dev/asmdisk_fra1
/dev/asmdisk_fra2
================================== $ asmcmd -p lsof --- Lists the open files of the local clients
DB_Name Instance_Name Path
+ASM +ASM1 +ocr.255.4294967295
grac4 grac41 +data/grac4/controlfile/current.260.826111693
grac4 grac41 +data/grac4/datafile/sysaux.257.826111425
grac4 grac41 +data/grac4/datafile/system.256.826111425
grac4 grac41 +data/grac4/datafile/undotbs1.258.826111425
grac4 grac41 +data/grac4/datafile/undotbs2.264.826111755
grac4 grac41 +data/grac4/datafile/undotbs3.268.826220083
grac4 grac41 +data/grac4/datafile/users.259.826111427
grac4 grac41 +data/grac4/onlinelog/group_1.274.852650227
grac4 grac41 +data/grac4/onlinelog/group_2.273.852651533
grac4 grac41 +data/grac4/onlinelog/group_3.272.852652849
grac4 grac41 +data/grac4/onlinelog/group_4.266.852652635
grac4 grac41 +data/grac4/onlinelog/group_5.270.852652251
grac4 grac41 +data/grac4/onlinelog/group_6.269.852652289
grac4 grac41 +data/grac4/tempfile/temp.263.826111719
grac4 grac41 +fra2/grac4/controlfile/current.338.852722827
grac4 grac41 +fra2/grac4/onlinelog/group_1.285.852650241
grac4 grac41 +fra2/grac4/onlinelog/group_2.298.852651537
grac4 grac41 +fra2/grac4/onlinelog/group_3.318.852652859
grac4 grac41 +fra2/grac4/onlinelog/group_4.294.852652647
grac4 grac41 +fra2/grac4/onlinelog/group_5.305.852652263
grac4 grac41 +fra2/grac4/onlinelog/group_6.306.852652301
================================== $ asmcmd -p lsod --- Lists the open Oracle ASM disks
Instance Process OSPID Path
1 oracle@grac41.example.com (DBW0) 5200 /dev/asm_data_10G_disk0
1 oracle@grac41.example.com (DBW0) 5200 /dev/asm_data_10G_disk1
1 oracle@grac41.example.com (DBW0) 5200 /dev/asm_data_10G_disk2
1 oracle@grac41.example.com (DBW0) 5200 /dev/asm_data_10G_disk3
1 oracle@grac41.example.com (DBW0) 5200 /dev/asm_ocr_11204_2G_disk1
1 oracle@grac41.example.com (DBW0) 5200 /dev/asm_ocr_11204_2G_disk2
1 oracle@grac41.example.com (DBW0) 5200 /dev/asm_ocr_11204_2G_disk3
1 oracle@grac41.example.com (DBW0) 5200 /dev/asm_test_1G_disk1
1 oracle@grac41.example.com (DBW0) 5200 /dev/asm_test_1G_disk2
1 oracle@grac41.example.com (DBW0) 5200 /dev/asmdisk_fra1
1 oracle@grac41.example.com (DBW0) 5200 /dev/asmdisk_fra2
1 oracle@grac41.example.com (GMON) 5210 /dev/asm_data_10G_disk0
1 oracle@grac41.example.com (GMON) 5210 /dev/asm_data_10G_disk1
1 oracle@grac41.example.com (GMON) 5210 /dev/asm_data_10G_disk2
1 oracle@grac41.example.com (GMON) 5210 /dev/asm_data_10G_disk3
1 oracle@grac41.example.com (GMON) 5210 /dev/asm_ocr_11204_2G_disk1
1 oracle@grac41.example.com (GMON) 5210 /dev/asm_ocr_11204_2G_disk2
1 oracle@grac41.example.com (GMON) 5210 /dev/asm_ocr_11204_2G_disk3
1 oracle@grac41.example.com (GMON) 5210 /dev/asm_test_1G_disk1
1 oracle@grac41.example.com (GMON) 5210 /dev/asm_test_1G_disk2
1 oracle@grac41.example.com (GMON) 5210 /dev/asmdisk_fra1
1 oracle@grac41.example.com (GMON) 5210 /dev/asmdisk_fra2
1 oracle@grac41.example.com (LGWR) 5202 /dev/asm_data_10G_disk0
1 oracle@grac41.example.com (LGWR) 5202 /dev/asm_data_10G_disk1
1 oracle@grac41.example.com (LGWR) 5202 /dev/asm_data_10G_disk2
1 oracle@grac41.example.com (LGWR) 5202 /dev/asm_data_10G_disk3
1 oracle@grac41.example.com (LGWR) 5202 /dev/asm_ocr_11204_2G_disk1
1 oracle@grac41.example.com (LGWR) 5202 /dev/asm_ocr_11204_2G_disk2
1 oracle@grac41.example.com (LGWR) 5202 /dev/asm_ocr_11204_2G_disk3
1 oracle@grac41.example.com (LGWR) 5202 /dev/asm_test_1G_disk1
1 oracle@grac41.example.com (LGWR) 5202 /dev/asm_test_1G_disk2
1 oracle@grac41.example.com (LGWR) 5202 /dev/asmdisk_fra1
1 oracle@grac41.example.com (LGWR) 5202 /dev/asmdisk_fra2
1 oracle@grac41.example.com (RBAL) 5208 /dev/asm_data_10G_disk0
1 oracle@grac41.example.com (RBAL) 5208 /dev/asm_data_10G_disk1
1 oracle@grac41.example.com (RBAL) 5208 /dev/asm_data_10G_disk1
1 oracle@grac41.example.com (RBAL) 5208 /dev/asm_data_10G_disk2
1 oracle@grac41.example.com (RBAL) 5208 /dev/asm_data_10G_disk3
1 oracle@grac41.example.com (RBAL) 5208 /dev/asm_ocr_11204_2G_disk1
1 oracle@grac41.example.com (RBAL) 5208 /dev/asm_ocr_11204_2G_disk2
1 oracle@grac41.example.com (RBAL) 5208 /dev/asm_ocr_11204_2G_disk3
1 oracle@grac41.example.com (RBAL) 5208 /dev/asmdisk_fra1
1 oracle@grac41.example.com (RBAL) 5208 /dev/asmdisk_fra1
1 oracle@grac41.example.com (TNS V1-V3) 25030 /dev/asm_data_10G_disk0
1 oracle@grac41.example.com (TNS V1-V3) 6023 /dev/asm_data_10G_disk0
1 oracle@grac41.example.com (TNS V1-V3) 6027 /dev/asm_data_10G_disk0
1 oracle@grac41.example.com (TNS V1-V3) 25030 /dev/asm_data_10G_disk1
1 oracle@grac41.example.com (TNS V1-V3) 6023 /dev/asm_data_10G_disk1
1 oracle@grac41.example.com (TNS V1-V3) 6027 /dev/asm_data_10G_disk1
1 oracle@grac41.example.com (TNS V1-V3) 25030 /dev/asm_data_10G_disk2
1 oracle@grac41.example.com (TNS V1-V3) 6023 /dev/asm_data_10G_disk2
1 oracle@grac41.example.com (TNS V1-V3) 6027 /dev/asm_data_10G_disk2
1 oracle@grac41.example.com (TNS V1-V3) 25030 /dev/asm_data_10G_disk3
1 oracle@grac41.example.com (TNS V1-V3) 6023 /dev/asm_data_10G_disk3
1 oracle@grac41.example.com (TNS V1-V3) 6027 /dev/asm_data_10G_disk3
1 oracle@grac41.example.com (TNS V1-V3) 25030 /dev/asm_ocr_11204_2G_disk1
1 oracle@grac41.example.com (TNS V1-V3) 5764 /dev/asm_ocr_11204_2G_disk1
1 oracle@grac41.example.com (TNS V1-V3) 6023 /dev/asm_ocr_11204_2G_disk1
1 oracle@grac41.example.com (TNS V1-V3) 6027 /dev/asm_ocr_11204_2G_disk1
1 oracle@grac41.example.com (TNS V1-V3) 25030 /dev/asm_ocr_11204_2G_disk2
1 oracle@grac41.example.com (TNS V1-V3) 6023 /dev/asm_ocr_11204_2G_disk2
1 oracle@grac41.example.com (TNS V1-V3) 6027 /dev/asm_ocr_11204_2G_disk2
1 oracle@grac41.example.com (TNS V1-V3) 25030 /dev/asm_ocr_11204_2G_disk3
1 oracle@grac41.example.com (TNS V1-V3) 5764 /dev/asm_ocr_11204_2G_disk3
1 oracle@grac41.example.com (TNS V1-V3) 6023 /dev/asm_ocr_11204_2G_disk3
1 oracle@grac41.example.com (TNS V1-V3) 6027 /dev/asm_ocr_11204_2G_disk3
1 oracle@grac41.example.com (TNS V1-V3) 25030 /dev/asm_test_1G_disk1
1 oracle@grac41.example.com (TNS V1-V3) 6023 /dev/asm_test_1G_disk1
1 oracle@grac41.example.com (TNS V1-V3) 25030 /dev/asm_test_1G_disk2
1 oracle@grac41.example.com (TNS V1-V3) 6023 /dev/asm_test_1G_disk2
1 oracle@grac41.example.com (TNS V1-V3) 25030 /dev/asmdisk_fra1
1 oracle@grac41.example.com (TNS V1-V3) 6023 /dev/asmdisk_fra1
1 oracle@grac41.example.com (TNS V1-V3) 25030 /dev/asmdisk_fra2
1 oracle@grac41.example.com (TNS V1-V3) 6023 /dev/asmdisk_fra2
================================== $ asmcmd -p iostat --- Displays I/O statistics for Oracle ASM disks in mounted disk groups
Group_Name Dsk_Name Reads Writes
DATA DATA_0000 11907802112 984553472
DATA DATA_0001 2966020096 2555700736
DATA DATA_0002 6684483072 2632183296
DATA DATA_0003 5571596800 2595161088
FRA2 FRA2_0000 13695417856 4735993856
FRA2 FRA2_0001 13467484160 3110062080
OCR DISK1 5160960 32256
OCR DISK2 684032 24576
OCR DISK3 727552 32256
TEST_NEW TEST_0000 2641920 129859584
TEST_NEW TEST_0001 1482752 129859584
================================== $ asmcmd -p dsget --- Retrieves the discovery diskstring value that is used by the Oracle ASM instance and its clients
parameter:/dev/asm*
profile:/dev/asm*
================================== $ asmcmd -p lsop --- Lists the current operations on a disk group in an Oracle ASM instance
Group_Name Dsk_Num State Power EST_WORK EST_RATE EST_TIME
================================== $ asmcmd -p spget --- Retrieves the location of the Oracle ASM SPFILE from the GPnP profile
+OCR/grac4/asmparameterfile/spfileASM.ora
================================== Templates for Diskgroup : DATA
$ asmcmd lstmpl -G DATA
Group_Name Group_Num Name
DATA 1 ARCHIVELOG
DATA 1 ASMPARAMETERFILE
DATA 1 AUTOBACKUP
DATA 1 BACKUPSET
DATA 1 CHANGETRACKING
DATA 1 CONTROLFILE
DATA 1 DATAFILE
DATA 1 DATAGUARDCONFIG
DATA 1 DUMPSET
DATA 1 FLASHBACK
DATA 1 FLASHFILE
DATA 1 OCRFILE
DATA 1 ONLINELOG
DATA 1 PARAMETERFILE
DATA 1 TEMPFILE
DATA 1 XTRANSPORT
DATA 1 XTRANSPORT BACKUPSET
------------------------------------------
Templates for Diskgroup : FRA2
$ asmcmd lstmpl -G FRA2
Group_Name Group_Num Name
FRA2 2 ARCHIVELOG
FRA2 2 ASMPARAMETERFILE
FRA2 2 AUTOBACKUP
FRA2 2 BACKUPSET
FRA2 2 CHANGETRACKING
FRA2 2 CONTROLFILE
FRA2 2 DATAFILE
FRA2 2 DATAGUARDCONFIG
FRA2 2 DUMPSET
FRA2 2 FLASHBACK
FRA2 2 FLASHFILE
FRA2 2 OCRFILE
FRA2 2 ONLINELOG
FRA2 2 PARAMETERFILE
FRA2 2 TEMPFILE
FRA2 2 XTRANSPORT
FRA2 2 XTRANSPORT BACKUPSET
------------------------------------------
Templates for Diskgroup : OCR
$ asmcmd lstmpl -G OCR
Group_Name Group_Num Name
OCR 3 ARCHIVELOG
OCR 3 ASMPARAMETERFILE
OCR 3 AUTOBACKUP
OCR 3 BACKUPSET
OCR 3 CHANGETRACKING
OCR 3 CONTROLFILE
OCR 3 DATAFILE
OCR 3 DATAGUARDCONFIG
OCR 3 DUMPSET
OCR 3 FLASHBACK
OCR 3 FLASHFILE
OCR 3 OCRFILE
OCR 3 ONLINELOG
OCR 3 PARAMETERFILE
OCR 3 TEMPFILE
OCR 3 XTRANSPORT
OCR 3 XTRANSPORT BACKUPSET
------------------------------------------
Templates for Diskgroup : TEST_NEW
$ asmcmd lstmpl -G TEST_NEW
Group_Name Group_Num Name
TEST_NEW 4 ARCHIVELOG
TEST_NEW 4 ASMPARAMETERFILE
TEST_NEW 4 AUTOBACKUP
TEST_NEW 4 BACKUPSET
TEST_NEW 4 CHANGETRACKING
TEST_NEW 4 CONTROLFILE
TEST_NEW 4 DATAFILE
TEST_NEW 4 DATAGUARDCONFIG
TEST_NEW 4 DUMPSET
TEST_NEW 4 FLASHBACK
TEST_NEW 4 FLASHFILE
TEST_NEW 4 OCRFILE
TEST_NEW 4 ONLINELOG
TEST_NEW 4 PARAMETERFILE
TEST_NEW 4 TEMPFILE
TEST_NEW 4 XTRANSPORT
TEST_NEW 4 XTRANSPORT BACKUPSET
------------------------------------------
================================== List ASM users in Diskgroup : DATA
$ asmcmd lsusr -G DATA
User_Num OS_ID OS_Name
------------------------------------------
List ASM users in Diskgroup : FRA2
$ asmcmd lsusr -G FRA2
User_Num OS_ID OS_Name
------------------------------------------
List ASM users in Diskgroup : OCR
$ asmcmd lsusr -G OCR
User_Num OS_ID OS_Name
------------------------------------------
List ASM users in Diskgroup : TEST_NEW
$ asmcmd lsusr -G TEST_NEW
User_Num OS_ID OS_Name
------------------------------------------
================================== $ asmcmd -p lsgrp -a --- Lists all Oracle ASM user groups
DG_Name Grp_Name Owner Members
================================== $ asmcmd -p lspwusr --- List the users from the local Oracle ASM password file
Username sysdba sysoper sysasm
SYS TRUE TRUE TRUE
ASMSNMP TRUE FALSE FALSE
================================== List ASM users in Diskgroup : DATA
$ asmcmd -p volinfo -G DATA -a
diskgroup DATA has no volumes or is not mounted
------------------------------------------
List ASM users in Diskgroup : FRA2
$ asmcmd -p volinfo -G FRA2 -a
diskgroup FRA2 has no volumes or is not mounted
------------------------------------------
List ASM users in Diskgroup : OCR
$ asmcmd -p volinfo -G OCR -a
diskgroup OCR has no volumes or is not mounted
------------------------------------------
List ASM users in Diskgroup : TEST_NEW
$ asmcmd -p volinfo -G TEST_NEW -a
diskgroup TEST_NEW has no volumes or is not mounted
------------------------------------------
Execution Example:
[grid@asmlnx2 ~]$ . oraenv
ORACLE_SID = [+ASM] ? +ASM
The Oracle base remains unchanged with value /u01/app/grid
[grid@asmlnx2 ~]$
[grid@asmlnx2 ~]$ ./asmcmd_script.sh
[grid@asmlnx2 ~]$
Output file:
[grid@asmlnx2 ~]$ ls -l /tmp/asmcmd_script.out
-rw-r--r-- 1 grid oinstall 15873 Jun 6 11:03 /tmp/asmcmd_script.out
#### ######AIX:
###两个节点执行#####
chown opdb:dba rhdiskpower9
chown opdb:dba rhdiskpower10
chown opdb:dba rhdiskpower11
chown opdb:dba rhdiskpower12
chown opdb:dba rhdiskpower13
chown opdb:dba rhdiskpower14
chown opdb:dba rhdiskpower15
chown opdb:dba rhdiskpower16
chown opdb:dba rhdiskpower17
#####两个节点上执行####################
chdev -l hdiskpower9 -a reserve_policy=no_reserve
chdev -l hdiskpower10 -a reserve_policy=no_reserve
chdev -l hdiskpower11 -a reserve_policy=no_reserve
chdev -l hdiskpower12 -a reserve_policy=no_reserve
chdev -l hdiskpower13 -a reserve_policy=no_reserve
chdev -l hdiskpower14 -a reserve_policy=no_reserve
chdev -l hdiskpower15 -a reserve_policy=no_reserve
chdev -l hdiskpower16 -a reserve_policy=no_reserve
chdev -l hdiskpower17 -a reserve_policy=no_reserve
#######在两个节点上执行##################
mknod /dev/asm_disk11 c 41 9
mknod /dev/asm_disk12 c 41 10
mknod /dev/asm_disk13 c 41 11
mknod /dev/asm_disk14 c 41 12
mknod /dev/asm_disk15 c 41 13
mknod /dev/asm_disk16 c 41 14
mknod /dev/asm_disk17 c 41 15
mknod /dev/asm_disk18 c 41 16
mknod /dev/asm_disk19 c 41 17
######两个节点执行############
chown opdb:dba /dev/asm_disk11
chown opdb:dba /dev/asm_disk12
chown opdb:dba /dev/asm_disk13
chown opdb:dba /dev/asm_disk14
chown opdb:dba /dev/asm_disk15
chown opdb:dba /dev/asm_disk16
chown opdb:dba /dev/asm_disk17
chown opdb:dba /dev/asm_disk18
chown opdb:dba /dev/asm_disk19
alter diskgroup db_DATA add disk '/dev/asm_disk11','/dev/asm_disk12' rebalance power 4 nowait;
#####next day 1/9 running action:
alter diskgroup db_DATA add disk '/dev/asm_disk13','/dev/asm_disk14','/dev/asm_disk15' rebalance power 4 nowait;
alter diskgroup db_DATA add disk '/dev/asm_disk16','/dev/asm_disk17','/dev/asm_disk18','/dev/asm_disk19' rebalance power 4 nowait;
select MOUNT_STATUS,HEADER_STATUS,name,path,total_mb,free_mb from v$asm_disk;
select GROUP_NUMBER,OPERATION,POWER,ACTUAL,SOFAR,EST_WORK,EST_MINUTES from gv$asm_operation;
##############Linux:
###两个节点执行#####
ls -ltr /dev/emcpoweri
###两个节点执行#####
root:
chown grid:oinstall /dev/emcpoweri
ls -ltr /dev/emcpoweri
修改文件：
/etc/udev/rules.d/50-udev.rules
# more 50-udev.rules
SUBSYSTEM=="block",KERNEL=="emcpowera",GROUP="oinstall",OWNER="grid",MODE="0660"
SUBSYSTEM=="block",KERNEL=="emcpowerb",GROUP="oinstall",OWNER="grid",MODE="0660"
SUBSYSTEM=="block",KERNEL=="emcpowerc",GROUP="oinstall",OWNER="grid",MODE="0660"
SUBSYSTEM=="block",KERNEL=="emcpowerd",GROUP="oinstall",OWNER="grid",MODE="0660"
SUBSYSTEM=="block",KERNEL=="emcpowere",GROUP="oinstall",OWNER="grid",MODE="0660"
SUBSYSTEM=="block",KERNEL=="emcpowerf",GROUP="oinstall",OWNER="grid",MODE="0660"
SUBSYSTEM=="block",KERNEL=="emcpowerg",GROUP="oinstall",OWNER="grid",MODE="0660"
SUBSYSTEM=="block",KERNEL=="emcpowerh",GROUP="oinstall",OWNER="grid",MODE="0660"
SUBSYSTEM=="block",KERNEL=="emcpoweri",GROUP="oinstall",OWNER="grid",MODE="0660"
/app/crmo/app/11.2.0/grid/bin/crsctl stop res -all
###These errors (ORA-15032, ORA-15027) are expected when shutting down HAS and when the ASM spfile resides in an ASM disk group.
##ignore
start_udev
###############
###NOTE:Running a "start_udev" will cause the network hotplug action to be applied to every interface configuration file on the host which does not have
###HOTPLUG=no set.
###This will activate any interface which does not have HOTPLUG=no set, regardless of the ONBOOT setting.
###If you would like to avoid this then please ensure that each ifcfg file has HOTPLUG=no set.
##/app/crmo/app/11.2.0/grid/bin/crsctl stop crs
###/app/crmo/app/11.2.0/grid/bin/crsctl stop cluster
/app/crmo/app/11.2.0/grid/bin/crsctl start res -all
/app/crmo/app/11.2.0/grid/bin/crsctl status res -all
/app/crmo/app/11.2.0/grid/bin/crsctl status res -t -init
####
#reboot
######DONE
grid:
kfed read /dev/emcpoweri
ls -ltr /dev/emcpoweri
Note: If the disk is not a FORMER disk, then kfed on new disks will return: kfbh.type: 0 ; 0x002: KFBTYP_INVALID and that is expected.
sqlplus " /as sysasm"
alter diskgroup DATA add disk '/dev/emcpoweri' rebalance power 4 nowait;
set linesize 500
select * from v$asm_diskgroup;
##第一阶段File extents relocation 同步400G 到200G 的磁盘 ，需要50小时
select MOUNT_STATUS,HEADER_STATUS,name,path,total_mb,free_mb from v$asm_disk;
###Wed Mar 07 22:10:47 2018
##Wed Mar 07 22:48:19 2018
##GMON querying group 1 at 16 for pid 18, osid 9396
###SUCCESS: refreshed membership for 1/0xd3e85b70 (DATA)
select GROUP_NUMBER,OPERATION,POWER,ACTUAL,SOFAR,EST_WORK,EST_MINUTES from gv$asm_operation;
###第二阶段 20分钟
##Wed Mar 07 23:12:06 2018
###NOTE: stopping process ARB0
###SUCCESS: rebalance completed for group 1/0xd3e85b70 (DATA)
###Disk compacting use 30 minutes
###tail -f ASM2_arb0_58591.trc
######kfed issue
KFED – ERROR!!! could not initialize the diag context
ERROR!!! could not initialize the diag context
uid=503(grid) gid=501(oinstall) groups=501(oinstall),504(asmadmin),505(asmoper),506(asmdba)
-rwxrwxrwx 1 grid oinstall 2097152000 Jul 5 22:14 /u02/oradata/asm_disk0
CAUSE
kfed executable will need create a directory for its own trace files under <GridHome>/log/diag/asmtool and/or <GridBase>/diag/asmtool. If kfed firstly was used as root user, this directory - <GridHome>/log/diag/asmtool and/or <GridBase>/diag/asmtoolis created as root. The following kfed run by other than root user will not be able to create a directory under <GridHome>/log/diag/asmtool.
drwxrwxrwx 2 grid oinstall 4096 Jul 5 15:29 asm
drwxr-xr-x 3 root root 4096 Jul 6 11:17 asmtool << This directory is owned by root and there is no write permissin for other users.
drwxr-xr-x 5 oracle asmadmin 4096 Jun 20 09:16 rdbms
drwxrwxrwx 3 grid oinstall 4096 Jun 15 18:04 tnslsnr
或者:
in grid:
truss kfed
statx("/c/igfs/oracleapp/diag", 0x0FFFFFFFFFFFD070, 176, 0) Err#2 ENOENT
mkdir("/c/igfs/oracleapp/diag", 0775) Err#13 EACCES
kioctl(1, 22528, 0x0000000000000000, 0x0000000000000000) = 0
ERROR!!! could not initialize the diag context
kwrite(1, " E R R O R ! ! ! c o u".., 48) = 48
SOLUTION
Change the owner for <GridHome>/log/diag/asmtool and/or <GridBase>/diag/asmtool ( based on your GridHome version ) directory as grid user.
# chown grid:oinstall $ORACLE_HOME/log/diag/asmtool
And as grid user, the following command will show the correct kfed output.
$kfed read /u02/oradata/asm_disk0 | more
kfbh.endian: 1 ; 0x000: 0x01
kfbh.hard: 130 ; 0x001: 0x82
kfbh.type: 1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt: 1 ; 0x003: 0x01
kfbh.block.blk: 0 ; 0x004: T=0 NUMB=0x0
kfbh.block.obj: 2147483648 ; 0x008: TYPE=0x8 NUMB=0x0
kfbh.check: 1175212953 ; 0x00c: 0x460c5399
kfbh.fcn.base: 0 ; 0x010: 0x00000000
kfbh.fcn.wrap: 0 ; 0x014: 0x00000000
kfbh.spare1: 0 ; 0x018: 0x00000000
kfbh.spare2: 0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr: ORCLDISK ; 0x000: length=8
或者
mkdir /c/igfs/oracleapp/diag
chown gird:oinstall /c/igfs/oracleapp/diag
################
ASMLIB可能带来的缺点:
- 对于多路径设备(multipathing)需要在/etc/sysconfig/oracleasm-_dev_oracleasm配置文件中设置ORACLEASM_SCANORDER及ORACLEASM_SCANEXCLUDE，以便ASMLIB能找到正确的设备文件，具体可以参考Metalink Note<How To Setup ASM & ASMLIB On Native Linux Multipath Mapper disks? [ID 602952.1]>
- 因为ASM INSTANCE使用ASMLIB提供的asm disk，所以增加了额外的层面
- 每次Linux Kernel更新，都需要替换新的ASMLIB包
- 增加了因人为错误造成宕机downtime的可能
- 使用ASMLIB意味着要花费更多时间去创建和维护
- 因为ASMLIB的存在，可能引入更多的bug，这是我们最不想看到的
- 使用ASMLIB创建的disk，其disk header并不会和普通的asm disk header有什么不同，仅仅是在头部多出了ASMLIB的属性空间。
结论:
我个人的观点是尽可能不要使用ASMLIB，当然这不是DBA个人所能决定的事情。另一方面这取决于个人习惯，在rhel 4的早期发行版本中没有提供udev这样的设备管理服务，这导致在rhel 4中大量的ASM+RAC组合的系统使用ASMLIB , 经网友指出udev 作为kernel 2.6的新特性被引入，在rhel4的初始版本中就已经加入了udev绑定服务，但是在rhel4时代实际udev的使用并不广泛(In Linux 2.6, a new feature was introduced to simplify device management and hot plug capabilities. This feature is called udev and is a standard package in RHEL4 or Oracle
Enterprise Linux 4 (OEL4) as well as Novell’s SLES9 and SLES10.)。如果是在RHEL/OEL 5中那么你已经有充分的理由利用udev而放弃ASMLIB。
ASM Specific
- Use ASMLib for ease of management and for increased performance provided by the ASMLib Async I/O driver.
Note: For RedHat Enterprise Linux 6 (beginning with 6.4) the kernel driver package 'kmod-oracleasm' is available directly from RedHat, and can be installed from the "RHEL Server Supplementary (v. 6 64-bit x86_64)" channel on RedHat Network (RHN). Updates to this module will be provided by RedHat. Additional information may be found in Document 1089399.1.
- When using ASMLib in a multipath environment ensure that ASMLib has been configured properly such that the multipath device is used for access to the devices. This is achieved by editing the /etc/sysconfig/oracleasm configuration file and modifying the ORACLEASM_SCANORDER to include a valid prefix entry from /proc/partitions (e.g. "dm","emcpower"). A common misconfiguration is to have ORACLEASM_SCANORDER" set to "/dev/mapper/". "/dev/mapper: are aliases which will NOT exist in /proc/partitions. See Document 602952.1 for details.
- If you are NOT or are unable to use ASMLib please refer to Document 357472.1 for guidance on how to configure UDEV and/or Device Mapper to manage Clusterware and ASM devices.
- On Linux 7 systems using FCOE and ASMLib, ensure FCOE service starts before ASMLib. See Document 2065945.1 and Document 2043733.1 for additional details.
- #################### 6
3. Add the following ifcfg-* files in /etc/sysconfig/network-scripts/
#cat /etc/sysconfig/network-scripts/ifcfg-bond0
DEVICE=bond0
IPADDR=xx.xx.xx.xx.
NETMASK=x.x.x.x
BROADCAST=x.x.x.x
ONBOOT=YES
BOOTPROTO=none
USERCTL=no
MTU=65520#cat /etc/sysconfig/network-scripts/ifcfg-ib0
DEVICE=ib0
USERCTL=no
ONBOOT=yes
MASTER=bond0
BOOTPROTO=none
SLAVE=yes
HOTPLUG=no
CONNECTED_MODE=yes
MTU=65520#cat /etc/sysconfig/network-scripts/ifcfg-ib2
DEVICE=ib2
USERCTL=no
ONBOOT=yes
MASTER=bond0
BOOTPROTO=none
SLAVE=yes
PRIMARY=yes
HOTPLUG=no
CONNECTED_MODE=yes
MTU=6552021:28:00:01:3e:86:f0
转到底部最新文章
- Installing IPython using pip on CentOS
- 获取Map API Key
- 【李婶小教程】（SE_lab3）
- Project support for both iOS 6 and iOS 7
- Entity Framework Core 2.0 入门简介
- Android逆向工程
- rethinking imageNet pre-training
- 牛客网练习赛44-B（快速幂+模拟）
- 【机器学习】粗糙集属性约简算法与mRMR算法的本质区别
- 年中总结大会--总结学习笔记， 技术部IT部门年中总结报告该怎么写
- hiho#1457 重复旋律7 求子串和 后缀自动机
- win7_32下编译FFmpeg
- 【jdbc】【c3p0】c3p0三种配置方式【整理】
- idea教程视频以及常用插件整理
- group by多字段分组
- Karma+Jasmine测试环境搭建
- [转] 前端开发利器--Brackets 的七种武器和旁门左道
- Druid对比Hadoop
- tomcat本身的lib目录都有哪些jar包
- swift中代理的使用