ASM 磁盘组的的scrip

转载

mb5fdb09f39fed1 2018-01-02 17:23:00

文章标签 运维帮 oracle sql sed html 文章分类 代码人生

之前经常用如下方式进行查询：

步骤 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):

# /etc/init.d/oracleasm scandisks #(only this command needs to be executed as root user)

$> 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:

$> id

$> 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:

$> kfed read <raw/character or block partition device in question>

Example:

$> kfed read /dev/raw/raw1

$> kfed read /dev/oracleasm/disks/PRODA07

$> kfed read /dev/rdsk/c1t3d0s6

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.

4) Create a new temporary diskgroup:

SQL> CREATE DISKGROUP TEST EXTERNAL REDUNDANCY DISK '<YOUR CANDIDATE DISK>';

5) Check if the diskgroup is created and mounted:

SQL> SELECT STATE, NAME FROM V$ASM_DISKGROUP;

6) If so, then manually mount it on the other ASM instance(s):

SQL> ALTER DISKGROUP TEST MOUNT;

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> alter diskgroup test dismount; --(from all the ASM instances, except from one).

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:

SQL> ALTER DISKGROUP <my production diskgroup> ADD DISK '<your candidate disk>';

Note: The same disks validation steps are also required for ASM Standalone configura sample 2:

These are the shell scripts to monitor sgastat and rebalance progress.

1. sgastat.sh <---------------

#!/bin/sh

#for (( i=0; i < 1440 ; i++ )) ;

while true

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

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