Thursday, February 7, 2019
Wednesday, January 23, 2019
'DB_UNKNOWN' directory was created when using asmcmd pwcopy (Doc ID 2329386.1)
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.2.0.1 and laterInformation in this document applies to any platform.
SYMPTOMS
'DB_UNKNOWN' directory was created when using asmcmd pwcopy to copy the password file.
ASMCMD> pwcopy /tmp/pworcl +DATAP/orcl/PASSWORD/pwdorcl
copying /tmp/pworcl -> +DATAP/orcl/PASSWORD/pwdorclASMCMD> ls -l
Type Redund Striped Time Sys Name
PASSWORD UNPROT COARSE NOV 16 16:00:00 N pwdorcl => +DATAP/DB_UNKNOWN/PASSWORD/pwddb_unknown.256.960224193
copying /tmp/pworcl -> +DATAP/orcl/PASSWORD/pwdorclASMCMD> ls -l
Type Redund Striped Time Sys Name
PASSWORD UNPROT COARSE NOV 16 16:00:00 N pwdorcl => +DATAP/DB_UNKNOWN/PASSWORD/pwddb_unknown.256.960224193
CHANGES
DB resource is not present on CRS when coping password file by pwcopy.
[grid@duke1 ~]$ crsctl stat res ora.orcl.db -t
CRS-2613: Could not find resource 'ora.orcl.db'.
CRS-2613: Could not find resource 'ora.orcl.db'.
CAUSE
This is expected behavior.
When DB resource is not present on CRS and "--dbuniquename" option is not specified in pwcopy command,
the password file will be created within 'DB_UNKNOWN' directory as pwcopy can not get DB name.
if "--dbuniquename" was specified, the password file will be created within the DB name directory that specified by "--dbuniquename" option.
When DB resource is present on CRS, 'DB_UNKNOWN' was not observed even "--dbuniquename" option is not specified in pwcopy command
as the DB name can be got from CRS.
SOLUTION
Specifie DB name by "--dbuniquename" option when DB resource is not present on CRS.
[grid@duke1 ~]$ asmcmd
ASMCMD> pwcopy --dbuniquename orcl /tmp/pworcl +DATAP/orcl/PASSWORD/pwdorcl
PRCD-1120 : The resource for database orcl could not be found.
PRCR-1001 : Resource ora.orcl.db does not exist
ASMCMD-9457: an internally issued srvctl command failed with errors
copying /tmp/pworcl -> +DATAP/orcl/PASSWORD/pwdorcl
ASMCMD-9453: failed to register password file as a CRS resourceNote: These errors are expected due to "orcl" is not present on CRS.
ASMCMD> ls -l
Type Redund Striped Time Sys Name
PASSWORD UNPROT COARSE NOV 16 17:00:00 N pwdorcl => +DATAP/orcl/PASSWORD/pwdorcl.256.960224879
PASSWORD UNPROT COARSE NOV 16 17:00:00 Y pwdorcl.256.960224879
ASMCMD> pwcopy --dbuniquename orcl /tmp/pworcl +DATAP/orcl/PASSWORD/pwdorcl
PRCD-1120 : The resource for database orcl could not be found.
PRCR-1001 : Resource ora.orcl.db does not exist
ASMCMD-9457: an internally issued srvctl command failed with errors
copying /tmp/pworcl -> +DATAP/orcl/PASSWORD/pwdorcl
ASMCMD-9453: failed to register password file as a CRS resourceNote: These errors are expected due to "orcl" is not present on CRS.
ASMCMD> ls -l
Type Redund Striped Time Sys Name
PASSWORD UNPROT COARSE NOV 16 17:00:00 N pwdorcl => +DATAP/orcl/PASSWORD/pwdorcl.256.960224879
PASSWORD UNPROT COARSE NOV 16 17:00:00 Y pwdorcl.256.960224879
OR
Copy password file by pwcopy after ran "srvctl add database" to add database to CRS.
[oracle@duke1 ~]$ srvctl add database -d orcl -o $ORACLE_HOME -n orcl -p "+DATAP/ORCL/PARAMETERFILE/spfile.268.959095489"
[oracle@duke1 ~]$ su - grid
[grid@duke1 ~]$ asmcmd
ASMCMD> pwcopy /tmp/pworcl +DATAP/orcl/PASSWORD/pwdorcl
copying /tmp/pworcl -> +DATAP/orcl/PASSWORD/pwdorcl
ASMCMD> ls -l
Type Redund Striped Time Sys Name
PASSWORD UNPROT COARSE NOV 16 17:00:00 N pwdorcl => +DATAP/orcl/PASSWORD/pwdorcl.256.960226829
PASSWORD UNPROT COARSE NOV 16 17:00:00 Y pwdorcl.256.960226829
[oracle@duke1 ~]$ su - grid
[grid@duke1 ~]$ asmcmd
ASMCMD> pwcopy /tmp/pworcl +DATAP/orcl/PASSWORD/pwdorcl
copying /tmp/pworcl -> +DATAP/orcl/PASSWORD/pwdorcl
ASMCMD> ls -l
Type Redund Striped Time Sys Name
PASSWORD UNPROT COARSE NOV 16 17:00:00 N pwdorcl => +DATAP/orcl/PASSWORD/pwdorcl.256.960226829
PASSWORD UNPROT COARSE NOV 16 17:00:00 Y pwdorcl.256.960226829
Monday, December 3, 2018
All about HugePages for Oracle Database
4.1.5 Restrictions for HugePages and Transparent HugePages Configurations
Review the following guidelines for HugePages and Transparent HugePages. Oracle recommends that you disable Transparent HugePages, because they may
causes delays in accessing memory that can result in node restarts in Oracle RAC environments, or performance issues or delays for Oracle Database
single instances. Oracle continues to recommend using standard HugePages for Linux. Transparent HugePages memory is enabled by default with Red Hat
Enterprise Linux 6, Red Hat Enterprise Linux 7, SUSE 11, Oracle Linux 6, and Oracle Linux 7 with earlier releases of Oracle Linux Unbreakable
Enterprise Kernel 2 (UEK2) kernels.
Transparent HugePages memory is disabled by default in later releases of UEK2 and later UEK kernels.
Note: Although Transparent HugePages is disable on UEK2 and later UEK kernels, Transparent HugePages may be enabled by default on your Linux system.
Transparent Hugepages are similar to standard HugePages. However, while standard HugePages allocate memory at startup, Transparent Hugepages memory uses the
khugepaged thread in the kernel to allocate memory dynamically during runtime,using swappable HugePages.
HugePages allocates non-swappable memory for large page tables using memorymapped files. HugePages are not enabled by default. If you enable HugePages, then
you should deduct the memory allocated to HugePages from the available RAM before calculating swap space. Refer to your distribution documentation and to Oracle
Technology Network and My Oracle Support for more information.
During Oracle Grid Infrastructure installation, the Grid Infrastructure Management Repository (GIMR) is configured to use HugePages. Because the Grid Infrastructure
Management Repository database starts before all other databases installed on the cluster, if the space allocated to HugePages is insufficient,
then the System Global Area (SGA) of one or more databases may be mapped to regular pages, instead of Hugepages, which can adversely affect performance.
Configure the HugePages memory allocation to a size large enough to accommodate the sum of the SGA sizes of all the databases you intend to install on
the cluster, as well as the Grid Infrastructure Management Repository.
For detailed explanation check the references.
To check transparent HugePages
[oracle@testrac1 dbs]$ egrep 'trans|thp' /proc/vmstat
nr_anon_transparent_hugepages 0
thp_fault_alloc 0
thp_fault_fallback 0
thp_collapse_alloc 0
thp_collapse_alloc_failed 0
thp_split 0
thp_zero_page_alloc 0
thp_zero_page_alloc_failed 0
To check HugePages Size
[oracle@testrac1 ~]$ grep Hugepagesize /proc/meminfo
Hugepagesize: 2048 kB
To check HugePage
[oracle@testrac1 dbs]$ grep HugePages /proc/meminfo
AnonHugePages: 0 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Add value to configuration file and apply it
[root@testrac1 ~]#vi /etc/sysctl.conf
# HugePages
vm.nr_hugepages =2864
[root@testrac1 ~]# sysctl -p
[root@testrac1 ~]# grep HugePages /proc/meminfo
AnonHugePages: 0 kB
HugePages_Total: 2864
HugePages_Free: 2864
HugePages_Rsvd: 0
HugePages_Surp: 0
References:
https://www.carajandb.com/en/blog/2016/7-easy-steps-to-configure-hugepages-for-your-oracle-database-server/
https://oracle-base.com/articles/linux/configuring-huge-pages-for-oracle-on-linux-64
https://www.thegeekdiary.com/centos-rhel-67-how-to-configure-hugepages/
https://docs.oracle.com/cd/E37670_01/E37355/html/ol_config_hugepages.html
https://docs.oracle.com/cd/E11882_01/server.112/e10839/appi_vlm.htm#UNXAR393
https://wadhahdaouehi.tn/2016/10/enable-hugepages-rhel-7ol-7-within-oracle-database-12c/
Metalinks :
https://mehmandba.blogspot.com/2018/05/hugepages-on-linux-what-it-is-and-what.html
https://mehmandba.blogspot.com/2018/05/oracle-linux-shell-script-to-calculate.html
Review the following guidelines for HugePages and Transparent HugePages. Oracle recommends that you disable Transparent HugePages, because they may
causes delays in accessing memory that can result in node restarts in Oracle RAC environments, or performance issues or delays for Oracle Database
single instances. Oracle continues to recommend using standard HugePages for Linux. Transparent HugePages memory is enabled by default with Red Hat
Enterprise Linux 6, Red Hat Enterprise Linux 7, SUSE 11, Oracle Linux 6, and Oracle Linux 7 with earlier releases of Oracle Linux Unbreakable
Enterprise Kernel 2 (UEK2) kernels.
Transparent HugePages memory is disabled by default in later releases of UEK2 and later UEK kernels.
Note: Although Transparent HugePages is disable on UEK2 and later UEK kernels, Transparent HugePages may be enabled by default on your Linux system.
Transparent Hugepages are similar to standard HugePages. However, while standard HugePages allocate memory at startup, Transparent Hugepages memory uses the
khugepaged thread in the kernel to allocate memory dynamically during runtime,using swappable HugePages.
HugePages allocates non-swappable memory for large page tables using memorymapped files. HugePages are not enabled by default. If you enable HugePages, then
you should deduct the memory allocated to HugePages from the available RAM before calculating swap space. Refer to your distribution documentation and to Oracle
Technology Network and My Oracle Support for more information.
During Oracle Grid Infrastructure installation, the Grid Infrastructure Management Repository (GIMR) is configured to use HugePages. Because the Grid Infrastructure
Management Repository database starts before all other databases installed on the cluster, if the space allocated to HugePages is insufficient,
then the System Global Area (SGA) of one or more databases may be mapped to regular pages, instead of Hugepages, which can adversely affect performance.
Configure the HugePages memory allocation to a size large enough to accommodate the sum of the SGA sizes of all the databases you intend to install on
the cluster, as well as the Grid Infrastructure Management Repository.
For detailed explanation check the references.
To check transparent HugePages
[oracle@testrac1 dbs]$ egrep 'trans|thp' /proc/vmstat
nr_anon_transparent_hugepages 0
thp_fault_alloc 0
thp_fault_fallback 0
thp_collapse_alloc 0
thp_collapse_alloc_failed 0
thp_split 0
thp_zero_page_alloc 0
thp_zero_page_alloc_failed 0
To check HugePages Size
[oracle@testrac1 ~]$ grep Hugepagesize /proc/meminfo
Hugepagesize: 2048 kB
To check HugePage
[oracle@testrac1 dbs]$ grep HugePages /proc/meminfo
AnonHugePages: 0 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Add value to configuration file and apply it
[root@testrac1 ~]#vi /etc/sysctl.conf
# HugePages
vm.nr_hugepages =2864
[root@testrac1 ~]# sysctl -p
[root@testrac1 ~]# grep HugePages /proc/meminfo
AnonHugePages: 0 kB
HugePages_Total: 2864
HugePages_Free: 2864
HugePages_Rsvd: 0
HugePages_Surp: 0
References:
https://www.carajandb.com/en/blog/2016/7-easy-steps-to-configure-hugepages-for-your-oracle-database-server/
https://oracle-base.com/articles/linux/configuring-huge-pages-for-oracle-on-linux-64
https://www.thegeekdiary.com/centos-rhel-67-how-to-configure-hugepages/
https://docs.oracle.com/cd/E37670_01/E37355/html/ol_config_hugepages.html
https://docs.oracle.com/cd/E11882_01/server.112/e10839/appi_vlm.htm#UNXAR393
https://wadhahdaouehi.tn/2016/10/enable-hugepages-rhel-7ol-7-within-oracle-database-12c/
Metalinks :
https://mehmandba.blogspot.com/2018/05/hugepages-on-linux-what-it-is-and-what.html
https://mehmandba.blogspot.com/2018/05/oracle-linux-shell-script-to-calculate.html
Friday, November 30, 2018
Oracle Database Related Kernel Parameters
We add kernel parameters to /etc/sysctl.conf manually or automatically with preinstall before Oracle installation started. Or we can modify these parameters after installation.
[root@rac1 fs]# cat /etc/sysctl.conf
# oracle-database-server-12cR2-preinstall setting for fs.file-max is 6815744
fs.file-max = 6815744
# oracle-database-server-12cR2-preinstall setting for kernel.sem is '250 32000 100 128'
kernel.sem = 250 32000 100 256
# oracle-database-server-12cR2-preinstall setting for kernel.shmmni is 4096
kernel.shmmni = 4096
# oracle-database-server-12cR2-preinstall setting for kernel.shmall is 1073741824 on x86_64
kernel.shmall = 1073741824
# oracle-database-server-12cR2-preinstall setting for kernel.shmmax is 4398046511104 on x86_64
kernel.shmmax = 4398046511104
# oracle-database-server-12cR2-preinstall setting for kernel.panic_on_oops is 1 per Orabug 19212317
kernel.panic_on_oops = 1
# oracle-database-server-12cR2-preinstall setting for net.core.rmem_default is 262144
net.core.rmem_default = 262144
# oracle-database-server-12cR2-preinstall setting for net.core.rmem_max is 4194304
net.core.rmem_max = 4194304
# oracle-database-server-12cR2-preinstall setting for net.core.wmem_default is 262144
net.core.wmem_default = 262144
# oracle-database-server-12cR2-preinstall setting for net.core.wmem_max is 1048576
net.core.wmem_max = 1048576
# oracle-database-server-12cR2-preinstall setting for net.ipv4.conf.all.rp_filter is 2
net.ipv4.conf.all.rp_filter = 2
# oracle-database-server-12cR2-preinstall setting for net.ipv4.conf.default.rp_filter is 2
net.ipv4.conf.default.rp_filter = 2
# oracle-database-server-12cR2-preinstall setting for fs.aio-max-nr is 1048576
fs.aio-max-nr = 1048576
# oracle-database-server-12cR2-preinstall setting for net.ipv4.ip_local_port_range is 9000 65500
net.ipv4.ip_local_port_range = 9000 65500
Apply kernel parameters without server restart.
/sbin/sysctl -p
or
sysctl -p /etc/sysctl.conf
We can also check the status of parameters in Linux with following commands:
Example: Check aio status - current and max
[oracle@rac2 ~]$ sysctl -a | grep aio
fs.aio-max-nr = 1048576 --> This is the max number of allowable concurrent requests.
fs.aio-nr = 2088704 --> This is the actual usage
[oracle@rac2 ~]$ cat /proc/sys/fs/aio-nr
2088704
As we can see from outputs actual usage is bigger than the max limit. So we have to optimize the kernel parameter. This is related to ASM and Oracle explained in following links:
Error related to this parameter: ORA-27090 - Unable to Reserve Kernel Resources for Asynchronous Disk I/O
Wednesday, October 24, 2018
ASMIOSTAT Script to collect iostats for ASM disks (Doc ID 437996.1)
In this Document
| Purpose |
| Requirements |
| Configuring |
| Instructions |
| Script |
| Sample Output |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.1.0.3 to 11.2.0.4 [Release 10.1 to 11.2]Information in this document applies to any platform.
PURPOSE
The OS command iostat is normally used to monitoring system input/output device load. This script will provide similar information like iostat but specific for the ASM disks.
For details about iostat (ie cumulative or not, and so on) please refer to the man of iostat.
For details about iostat (ie cumulative or not, and so on) please refer to the man of iostat.
REQUIREMENTS
We use v$asm_disk_stat instead of v$asm_disk because the information is exactly the same.
The only difference is v$asm_disk_stat is the information available in memory while v$asm_disk access the disks to re-collect some information. Since the information required doesn't require to "re-collect" it from the disks, v$asm_disk_stat is more appropriate here.
On Solaris, please use :
- the /usr/xpg4/bin/grep utility instead of /usr/bin/grep to avoid the following error:
"grep: illegal option -- q"
- the /usr/xpg4/bin/awk utility instead of /usr/bin/awk to avoid the following error:
"awk: syntax error near line 48"
The only difference is v$asm_disk_stat is the information available in memory while v$asm_disk access the disks to re-collect some information. Since the information required doesn't require to "re-collect" it from the disks, v$asm_disk_stat is more appropriate here.
On Solaris, please use :
- the /usr/xpg4/bin/grep utility instead of /usr/bin/grep to avoid the following error:
"grep: illegal option -- q"
- the /usr/xpg4/bin/awk utility instead of /usr/bin/awk to avoid the following error:
"awk: syntax error near line 48"
On other platforms, it could sometime fail due to Shell compatibility issues.
In such case, retry using another Shell (ie: Bash instead of Ksh)
We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it. This script is provided as an example. If it doesn't work, you must adapt it by yourself for your platform
In 10.1 asmcmd utility is not included:
asmcmd can be used against ASM versions 10gR1 (10.1.0.n) and 10gR2 (10.2.0.n). In ASM version 10.2 asmcmd is provided by default ASM installation.
To use asmcmd in ASM version 10.1 environment we can just copy relevant files from 10.2 installation into the 10.1
asmcmd can be used against ASM versions 10gR1 (10.1.0.n) and 10gR2 (10.2.0.n). In ASM version 10.2 asmcmd is provided by default ASM installation.
To use asmcmd in ASM version 10.1 environment we can just copy relevant files from 10.2 installation into the 10.1
CONFIGURING
Not required.
INSTRUCTIONS
Not required
CAUTION
This sample code is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
SCRIPT
NOTE: Check the attachments (asmiostat.zip) for the generic script and for platform specific scripts such as AIX/RHEL/HP UX refer (asmiostat_AIX_RHEL_HPUX.zip) attachment
#!/bin/ksh
#
# NAME
# asmiostat.sh
#
# DESCRIPTION
# iostat-like output for ASM
# $ asmiostat.sh [-s ASM ORACLE_SID] [-h ASM ORACLE_HOME] [-g Diskgroup]
# [-f disk path filter] [<interval>] [<count>]
#
# NOTES
# Creates persistent SQL*Plus connection to the +ASM instance implemented
# as a ksh co-process
#
# AUTHOR
# Doug Utzig
#
# MODIFIED
# dutzig 08/18/05 - original version
#
ORACLE_SID=+ASM
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
endOfOutput="_EOP$$"
typeset -u diskgroup
typeset diskgroup_string="Disk Group: All diskgroups"
typeset usage="
$0 [-s ASM ORACLE_SID] [-h ASM ORACLE_HOME] [-g diskgroup] [<interval>] [<count>]
Output:
DiskPath - Path to ASM disk
DiskName - ASM disk name
Gr - ASM disk group number
Dsk - ASM disk number
Reads - Reads
Writes - Writes
AvRdTm - Average read time (in msec)
AvWrTm - Average write time (in msec)
KBRd - Kilobytes read
KBWr - Kilobytes written
AvRdSz - Average read size (in bytes)
AvWrSz - Average write size (in bytes)
RdEr - Read errors
WrEr - Write errors
"
while getopts ":s:h:g:f" option; do
case $option in
s) ORACLE_SID="$OPTARG" ;;
h) ORACLE_HOME="$OPTARG"
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH ;;
g) diskgroup="$OPTARG"
diskgroup_string="Disk Group: $diskgroup" ;;
f) print '-f option not implemented' ;;
:) print "Option $OPTARG needs a value"
print "$usage"
exit 1 ;;
\?) print "Invalid option $OPTARG"
print "$usage"
exit 1 ;;
esac
done
shift OPTIND-1
typeset -i interval=${1:-10} count=${2:-0} index=0
#
# Verify interval and count arguments are valid
#
(( interval <=0 || count<0 )) && {
print 'Invalid parameter: <interval> must be > 0; <count> must be >= 0'
print "$usage"
exit 1
}
#
# Query to run against v$asm_disk_stat
#
if [[ -z $diskgroup ]]; then
query="select group_number, disk_number, name, path, reads, writes, read_errs, write_errs, read_time, write_time, bytes_read, bytes_written from v\$asm_disk_stat where group_number>0 order by group_number, disk_number;"
else
query="select group_number, disk_number, name, path, reads, writes, read_errs, write_errs, read_time, write_time, bytes_read, bytes_written from v\$asm_disk_stat where group_number=(select group_number from v\$asm_diskgroup_stat where name=regexp_replace('$diskgroup','^\+','')) order by group_number, disk_number;"
fi
#
# Check for version 10.2 or later
#
typeset version minversion=10.2
version=$($ORACLE_HOME/bin/exp </dev/null 2>&1 | grep "Export: " | sed -e 's/^Export: Release \([0-9][0-9]*\.[0-9][0-9]*\).*/\1/')
if ! (print "$version<$minversion" | bc >/dev/null 2>&1); then
print "$0 requires Oracle Database Release $minversion or later"
exit 1
fi
#############################################################################
#
# Fatal error
#----------------------------------------------------------------------------
function fatalError {
print -u2 -- "Error: $1"
exit 1
}
#############################################################################
#
# Drain all of the sqlplus output - stop when we see our well known string
#----------------------------------------------------------------------------
function drainOutput {
typeset dispose=${1:-'dispose'} output
while :; do
read -p output || fatalError 'Read from co-process failed [$0]'
if [[ $QUERYDEBUG == ON ]]; then print $output; fi
if [[ $output == $endOfOutput* ]]; then break; fi
[[ $dispose != 'dispose' ]] && print -- $output
done
}
#############################################################################
#
# Ensure the instance is running and it is of type ASM
#----------------------------------------------------------------------------
function verifyASMinstance {
typeset asmcmdPath=$ORACLE_HOME/bin/asmcmd
[[ ! -x $asmcmdPath ]] && fatalError "Invalid ORACLE_HOME $ORACLE_HOME: $asmcmdPath does not exist"
$asmcmdPath pwd 2>/dev/null | grep -q '^\+$' || fatalError "$ORACLE_SID is not an ASM instance"
}
#############################################################################
#
# Start the sqlplus coprocess
#----------------------------------------------------------------------------
function startSqlplus {
# start sqlplus, setup the env
$ORACLE_HOME/bin/sqlplus -s '/ as sysdba' |&
print -p 'whenever sqlerror exit failure' \
&& print -p "set pagesize 9999 linesize 9999 feedback off heading off" \
&& print -p "prompt $endOfOutput" \
|| fatalError 'Write to co-process failed (startSqlplus)'
drainOutput dispose
}
#############################################################################
#############################################################################
# MAIN
#----------------------------------------------------------------------------
verifyASMinstance
startSqlplus
#
# Loop as many times as requested or forever
#
while :; do
print -p "$query" \
&& print -p "prompt $endOfOutput" \
|| fatalError 'Write to co-process failed (collectData)'
stats=$(drainOutput keep)
print -- "$stats\nEOL"
index=index+1
(( count<index && count>0 )) && break
sleep $interval
done | \
awk '
BEGIN { firstSample=1
}
/^EOL$/ {
firstSample=0; firstLine=1
next
}
{
path=$4
if (path ~ /^ *$/) next
group[path]=$1; disk[path]=$2; name[path]=$3
reads[path]=$5; writes[path]=$6
readErrors[path]=$7; writeErrors[path]=$8
readTime[path]=$9; writeTime[path]=$10
readBytes[path]=$11; writeBytes[path]=$12
# reads and writes
readsDiff[path]=reads[path]-readsPrev[path]
writesDiff[path]=writes[path]-writesPrev[path]
# read errors and write errors
readErrorsDiff[path]=readErrors[path]-readErrorsPrev[path]
writeErrorsDiff[path]=writeErrors[path]-writeErrorsPrev[path]
# read time and write time
readTimeDiff[path]=readTime[path]-readTimePrev[path]
writeTimeDiff[path]=writeTime[path]-writeTimePrev[path]
# average read time and average write time in msec (data provided in csec)
avgReadTime[path]=0; avgWriteTime[path]=0
if ( readsDiff[path] ) avgReadTime[path]=(readTimeDiff[path]/readsDiff[path])*1000
if ( writesDiff[path]) avgWriteTime[path]=(writeTimeDiff[path]/writesDiff[path])*1000
# bytes and KB read and bytes and KB written
readBytesDiff[path]=readBytes[path]-readBytesPrev[path]
writeBytesDiff[path]=writeBytes[path]-writeBytesPrev[path]
readKb[path]=readBytesDiff[path]/1024
writeKb[path]=writeBytesDiff[path]/1024
# average read size and average write size
avgReadSize[path]=0; avgWriteSize[path]=0
if ( readsDiff[path] ) avgReadSize[path]=readBytesDiff[path]/readsDiff[path]
if ( writesDiff[path] ) avgWriteSize[path]=writeBytesDiff[path]/writesDiff[path]
if (!firstSample) {
if (firstLine) {
"date" | getline now; close("date")
printf "\n"
printf "Date: %s Interval: %d secs %s\n\n", now, '"$interval"', "'"$diskgroup_string"'"
printf "%-40s %2s %3s %8s %8s %6s %6s %8s %8s %7s %7s %4s %4s\n", \
"DiskPath - DiskName","Gr","Dsk","Reads","Writes","AvRdTm",\
"AvWrTm","KBRd","KBWr","AvRdSz","AvWrSz", "RdEr", "WrEr"
firstLine=0
}
printf "%-40s %2s %3s %8d %8d %6.1f %6.1f %8d %8d %7d %7d %4d %4d\n", \
path " - " name[path], group[path], disk[path], \
readsDiff[path], writesDiff[path], \
avgReadTime[path], avgWriteTime[path], \
readKb[path], writeKb[path], \
avgReadSize[path], avgWriteSize[path], \
readErrorsDiff[path], writeErrorsDiff[path]
}
readsPrev[path]=reads[path]; writesPrev[path]=writes[path]
readErrorsPrev[path]=readErrors[path]; writeErrorsPrev[path]=writeErrors[path]
readTimePrev[path]=readTime[path]; writeTimePrev[path]=writeTime[path]
readBytesPrev[path]=readBytes[path]; writeBytesPrev[path]=writeBytes[path]
}
END {
}
'
exit 0
#
# NAME
# asmiostat.sh
#
# DESCRIPTION
# iostat-like output for ASM
# $ asmiostat.sh [-s ASM ORACLE_SID] [-h ASM ORACLE_HOME] [-g Diskgroup]
# [-f disk path filter] [<interval>] [<count>]
#
# NOTES
# Creates persistent SQL*Plus connection to the +ASM instance implemented
# as a ksh co-process
#
# AUTHOR
# Doug Utzig
#
# MODIFIED
# dutzig 08/18/05 - original version
#
ORACLE_SID=+ASM
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
endOfOutput="_EOP$$"
typeset -u diskgroup
typeset diskgroup_string="Disk Group: All diskgroups"
typeset usage="
$0 [-s ASM ORACLE_SID] [-h ASM ORACLE_HOME] [-g diskgroup] [<interval>] [<count>]
Output:
DiskPath - Path to ASM disk
DiskName - ASM disk name
Gr - ASM disk group number
Dsk - ASM disk number
Reads - Reads
Writes - Writes
AvRdTm - Average read time (in msec)
AvWrTm - Average write time (in msec)
KBRd - Kilobytes read
KBWr - Kilobytes written
AvRdSz - Average read size (in bytes)
AvWrSz - Average write size (in bytes)
RdEr - Read errors
WrEr - Write errors
"
while getopts ":s:h:g:f" option; do
case $option in
s) ORACLE_SID="$OPTARG" ;;
h) ORACLE_HOME="$OPTARG"
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH ;;
g) diskgroup="$OPTARG"
diskgroup_string="Disk Group: $diskgroup" ;;
f) print '-f option not implemented' ;;
:) print "Option $OPTARG needs a value"
print "$usage"
exit 1 ;;
\?) print "Invalid option $OPTARG"
print "$usage"
exit 1 ;;
esac
done
shift OPTIND-1
typeset -i interval=${1:-10} count=${2:-0} index=0
#
# Verify interval and count arguments are valid
#
(( interval <=0 || count<0 )) && {
print 'Invalid parameter: <interval> must be > 0; <count> must be >= 0'
print "$usage"
exit 1
}
#
# Query to run against v$asm_disk_stat
#
if [[ -z $diskgroup ]]; then
query="select group_number, disk_number, name, path, reads, writes, read_errs, write_errs, read_time, write_time, bytes_read, bytes_written from v\$asm_disk_stat where group_number>0 order by group_number, disk_number;"
else
query="select group_number, disk_number, name, path, reads, writes, read_errs, write_errs, read_time, write_time, bytes_read, bytes_written from v\$asm_disk_stat where group_number=(select group_number from v\$asm_diskgroup_stat where name=regexp_replace('$diskgroup','^\+','')) order by group_number, disk_number;"
fi
#
# Check for version 10.2 or later
#
typeset version minversion=10.2
version=$($ORACLE_HOME/bin/exp </dev/null 2>&1 | grep "Export: " | sed -e 's/^Export: Release \([0-9][0-9]*\.[0-9][0-9]*\).*/\1/')
if ! (print "$version<$minversion" | bc >/dev/null 2>&1); then
print "$0 requires Oracle Database Release $minversion or later"
exit 1
fi
#############################################################################
#
# Fatal error
#----------------------------------------------------------------------------
function fatalError {
print -u2 -- "Error: $1"
exit 1
}
#############################################################################
#
# Drain all of the sqlplus output - stop when we see our well known string
#----------------------------------------------------------------------------
function drainOutput {
typeset dispose=${1:-'dispose'} output
while :; do
read -p output || fatalError 'Read from co-process failed [$0]'
if [[ $QUERYDEBUG == ON ]]; then print $output; fi
if [[ $output == $endOfOutput* ]]; then break; fi
[[ $dispose != 'dispose' ]] && print -- $output
done
}
#############################################################################
#
# Ensure the instance is running and it is of type ASM
#----------------------------------------------------------------------------
function verifyASMinstance {
typeset asmcmdPath=$ORACLE_HOME/bin/asmcmd
[[ ! -x $asmcmdPath ]] && fatalError "Invalid ORACLE_HOME $ORACLE_HOME: $asmcmdPath does not exist"
$asmcmdPath pwd 2>/dev/null | grep -q '^\+$' || fatalError "$ORACLE_SID is not an ASM instance"
}
#############################################################################
#
# Start the sqlplus coprocess
#----------------------------------------------------------------------------
function startSqlplus {
# start sqlplus, setup the env
$ORACLE_HOME/bin/sqlplus -s '/ as sysdba' |&
print -p 'whenever sqlerror exit failure' \
&& print -p "set pagesize 9999 linesize 9999 feedback off heading off" \
&& print -p "prompt $endOfOutput" \
|| fatalError 'Write to co-process failed (startSqlplus)'
drainOutput dispose
}
#############################################################################
#############################################################################
# MAIN
#----------------------------------------------------------------------------
verifyASMinstance
startSqlplus
#
# Loop as many times as requested or forever
#
while :; do
print -p "$query" \
&& print -p "prompt $endOfOutput" \
|| fatalError 'Write to co-process failed (collectData)'
stats=$(drainOutput keep)
print -- "$stats\nEOL"
index=index+1
(( count<index && count>0 )) && break
sleep $interval
done | \
awk '
BEGIN { firstSample=1
}
/^EOL$/ {
firstSample=0; firstLine=1
next
}
{
path=$4
if (path ~ /^ *$/) next
group[path]=$1; disk[path]=$2; name[path]=$3
reads[path]=$5; writes[path]=$6
readErrors[path]=$7; writeErrors[path]=$8
readTime[path]=$9; writeTime[path]=$10
readBytes[path]=$11; writeBytes[path]=$12
# reads and writes
readsDiff[path]=reads[path]-readsPrev[path]
writesDiff[path]=writes[path]-writesPrev[path]
# read errors and write errors
readErrorsDiff[path]=readErrors[path]-readErrorsPrev[path]
writeErrorsDiff[path]=writeErrors[path]-writeErrorsPrev[path]
# read time and write time
readTimeDiff[path]=readTime[path]-readTimePrev[path]
writeTimeDiff[path]=writeTime[path]-writeTimePrev[path]
# average read time and average write time in msec (data provided in csec)
avgReadTime[path]=0; avgWriteTime[path]=0
if ( readsDiff[path] ) avgReadTime[path]=(readTimeDiff[path]/readsDiff[path])*1000
if ( writesDiff[path]) avgWriteTime[path]=(writeTimeDiff[path]/writesDiff[path])*1000
# bytes and KB read and bytes and KB written
readBytesDiff[path]=readBytes[path]-readBytesPrev[path]
writeBytesDiff[path]=writeBytes[path]-writeBytesPrev[path]
readKb[path]=readBytesDiff[path]/1024
writeKb[path]=writeBytesDiff[path]/1024
# average read size and average write size
avgReadSize[path]=0; avgWriteSize[path]=0
if ( readsDiff[path] ) avgReadSize[path]=readBytesDiff[path]/readsDiff[path]
if ( writesDiff[path] ) avgWriteSize[path]=writeBytesDiff[path]/writesDiff[path]
if (!firstSample) {
if (firstLine) {
"date" | getline now; close("date")
printf "\n"
printf "Date: %s Interval: %d secs %s\n\n", now, '"$interval"', "'"$diskgroup_string"'"
printf "%-40s %2s %3s %8s %8s %6s %6s %8s %8s %7s %7s %4s %4s\n", \
"DiskPath - DiskName","Gr","Dsk","Reads","Writes","AvRdTm",\
"AvWrTm","KBRd","KBWr","AvRdSz","AvWrSz", "RdEr", "WrEr"
firstLine=0
}
printf "%-40s %2s %3s %8d %8d %6.1f %6.1f %8d %8d %7d %7d %4d %4d\n", \
path " - " name[path], group[path], disk[path], \
readsDiff[path], writesDiff[path], \
avgReadTime[path], avgWriteTime[path], \
readKb[path], writeKb[path], \
avgReadSize[path], avgWriteSize[path], \
readErrorsDiff[path], writeErrorsDiff[path]
}
readsPrev[path]=reads[path]; writesPrev[path]=writes[path]
readErrorsPrev[path]=readErrors[path]; writeErrorsPrev[path]=writeErrors[path]
readTimePrev[path]=readTime[path]; writeTimePrev[path]=writeTime[path]
readBytesPrev[path]=readBytes[path]; writeBytesPrev[path]=writeBytes[path]
}
END {
}
'
exit 0
SAMPLE OUTPUT
For every ASM disk, this is the output provided:
DiskPath - Path to ASM disk
DiskName - ASM disk name
Gr - ASM disk group number
Dsk - ASM disk number
Reads - Reads
Writes - Writes
AvRdTm - Average read time (in msec)
AvWrTm - Average write time (in msec)
KBRd - Kilobytes read
KBWr - Kilobytes written
AvRdSz - Average read size (in bytes)
AvWrSz - Average write size (in bytes)
RdEr - Read errors
WrEr - Write errors
DiskName - ASM disk name
Gr - ASM disk group number
Dsk - ASM disk number
Reads - Reads
Writes - Writes
AvRdTm - Average read time (in msec)
AvWrTm - Average write time (in msec)
KBRd - Kilobytes read
KBWr - Kilobytes written
AvRdSz - Average read size (in bytes)
AvWrSz - Average write size (in bytes)
RdEr - Read errors
WrEr - Write errors
Script Output
DiskPath - DiskName Gr Dsk Reads Writes AvRdTm AvWrTm KBRd KBWr AvRdSz AvWrSz RdEr WrEr /dev/asmdisk14 - DATA_0000 2 0 0 4 0.0 5.0 0 16 0 4096 0 0
/dev/asmdisk4 - DATA_0001 2 1 10 0 17.0 0.0 0 0 0 0 0 0
/dev/asmdisk17 - DATA_0002 2 2 0 1 0.0 0.0 0 0 0 512 0 0
/dev/asmdisk5 - DATA_0003 2 3 0 0 0.0 0.0 0 0 0 0 0 0
/dev/asmdisk16 - DATA_0004 2 4 3 3 6.7 3.3 0 0 0 0 0 0
/dev/asmdisk23 - DATA_0005 2 5 0 0 0.0 0.0 0 0 0 0 0 0
/dev/asmdisk4 - DATA_0001 2 1 10 0 17.0 0.0 0 0 0 0 0 0
/dev/asmdisk17 - DATA_0002 2 2 0 1 0.0 0.0 0 0 0 512 0 0
/dev/asmdisk5 - DATA_0003 2 3 0 0 0.0 0.0 0 0 0 0 0 0
/dev/asmdisk16 - DATA_0004 2 4 3 3 6.7 3.3 0 0 0 0 0 0
/dev/asmdisk23 - DATA_0005 2 5 0 0 0.0 0.0 0 0 0 0 0 0
Friday, October 5, 2018
EM 13c : How to Create an EM Administrator with Read Only Access to the Performance Pages of a Database Target? (Doc ID 2180307.1)
In this Document
| Goal |
| Solution |
APPLIES TO:
Enterprise Manager for Oracle Database - Version 13.1.1.0.0 and laterInformation in this document applies to any platform.
GOAL
Requirement is to create an EM user with Read only access to the database target but the user should be able to view the details in the Database target performance pages. However, the user should not be allowed to perform any performance activities in the database target.
Created a new EM Admin and granted the "Connect Target Read-only" privilege but accessing the Database load map as the new EM admin returns the below error:
Created a new EM Admin and granted the "Connect Target Read-only" privilege but accessing the Database load map as the new EM admin returns the below error:
Insufficient Privilege User XXXXXX does not have sufficient privileges to perform the operation. Operation: Access to Enterprise Manager management Page Required Privilege: View Database Performance Home Page Contact Enterprise Manager Administrator to get the privileges required to perform the operation.
What privileges should be granted to the EM Admin to be able to view only the performance pages?
SOLUTION
For the EM admin to be able to only view the DB performance pages, grant the privilege: View Database Performance Privilege Group. This includes the "Connect Target Read-only" privilege and the view privilege to access the performance details but will not allow the user to perform any changes.
This is a new privilege in 13c EM and the complete list of privileges are described in Oracle Enterprise Manager Cloud Control Online Documentation Library, Release 13.1, Enterprise Manager Cloud Control Security Guide
Appendix B - Privileges
Appendix B - Privileges
Follow the below steps:
- Login to the EM console as a super-administrator user.
- Navigate to Setup -> Security -> Administrator.
- Select the EM Admin created and click on Edit button.
- In the 'Target Privileges' page, scroll down and click on the edit icon in the 'Manage Target Privileges Grants' for the specific database target:
- Navigate to Setup -> Security -> Administrator.
- Select the EM Admin created and click on Edit button.
- In the 'Target Privileges' page, scroll down and click on the edit icon in the 'Manage Target Privileges Grants' for the specific database target:
- In the next page, search for "View Database Performance" in the search field.
- Select the privilege named 'View Database Performance Privilege Group':
- Save the details.
- Login to the EM console as the new EM Admin and verify if the DB target performance pages are accessible.
Subscribe to:
Posts (Atom)