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? (Doc ID 470211.1)

How To Gather & Backup ASM/ACFS Metadata In A Formatted Manner version

 (Doc ID 470211.1)


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;

-- SPARSE Diskgroups VIEW

SELECT * FROM V$ASM_DISK_SPARSE;
SELECT * FROM V$ASM_DISKGROUP_SPARSE;

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
##############################################################################################################

From 12.1.0.2, command "asmcmd -p volinfo -a" should be replaced to "asmcmd -p volinfo --all"

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