ALL ABOUT ORACLE: August 2018

Wednesday, August 8, 2018

FileSystem Monitoring shell script

#!/bin/bash -xv
#
export PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/home/oracle/bin::/mnt/mnt02/app/oracle/product/12.2.0.1/db_1/bin:/mnt/mnt02/app/oracle/product/12.2.0.1/db_1/OPatch

# To debug uncomment the below line
#set -x
#
# Set Environment Variables
#
TODAY="at $(date '+%H:%M on %d-%b-%y')"
OutputFilename=$(date +"%b_%d_%Y".html)
LastReport=/tmp/LastReport.txt
NowReport=/tmp/NowReport.txt
CurDate=`date +%D\ %T\ %Z`
#
# Set Alert Type according to Percentage
#
CriticalPercentage=90
WarningPercentage=85
NormalPercentage=70
#
# Get IP Address set from hosts file
#
IPADDRESS=`head -n 1 /etc/hosts | awk ' { print $1 }'`
#
# Remove Output File, if it already exists
#
if [ -f /tmp/${OutputFilename} ]; then
rm -f /tmp/${OutputFilename}
fi
if [ -f ${NowReport} ]; then
mv ${NowReport} ${LastReport}
cp /dev/null ${NowReport}
fi
#
# Find out Difference Previous and Current Report for each partition
#
df -Ph | grep -vE "^Filesystem|tmpfs|cdrom" | awk 'NR>0 && NF==6' | awk '{print $3}' > ${NowReport}
if [ -f ${LastReport} ]; then
DiffValue=(`awk '{ getline getdifference < "/tmp/LastReport.txt"; $1 -= getdifference; print }' /tmp/NowReport.txt`)
fi
#
# Defining HTML Table Format & Fields
#
(
echo '<HTML><HEAD><TITLE>Disk Usage Statistics</TITLE></HEAD>'
echo '<BODY>'
echo '<H3>Disk Usage Report for server - '$(uname -n) for ${IPADDRESS}'</H3>'
echo 'Report Generated '${TODAY}''
echo '<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=2 bordercolor=#DEDEE2>'
echo '<style>
body {
font-family: verdana;
font: 16px verdana, sans-serif;
}
tbody th { text-align:right; background: #F8F8FA; color:red}
</style>'
# echo '<TR BGCOLOR=\"#BBFFFF\"> <TH>Filesystem</TH> <TH>Total</TH> <TH>Disk Used</TH>
#<TH>Available</TH> <TH>Percentage Info</TH> <TH>Mounted On</TH> <TH>Critical Alert</TH>
#<TH>Report Date</TH> <TH>12 Hrs Difference </TH><//TR>'
echo '<TR BGCOLOR=\"#4863A0\"> <TH>Filesystem</TH> <TH>Total</TH> <TH>Disk Used</TH><TH>Available</TH> <TH>Percentage Info</TH> <TH>Mounted On</TH> <TH>Critical Alert</TH></TR>'

#
# Extract Disk Usage Information
# Suppress Listing of “FileSystem, tmpfs and cdrom” Information
#
ArryCount=0
df -Ph | grep -vE "^Filesystem|tmpfs|cdrom" | awk 'NR>0 && NF==6'|sort|while read FileSystem Size DiskUsed DiskFree DiskPercentUsed MountPoint
do
PERCENT=${DiskPercentUsed%%%}
#
# Calculate the Difference between previous run and current run
#
TDiffValue=(`awk '{ getline getdifference < "/tmp/LastReport.txt"; $1 -= getdifference; print }' /tmp/NowReport.txt`)
#
# Verify if disk usage is greater equal to than set threshold limit - 90%
#
if [[ ${PERCENT} -ge ${CriticalPercentage} ]];
then
COLOR=red
CRITICALALERT="Yes, Notify"
elif [ ${PERCENT} -ge ${WarningPercentage} ] && [ ${PERCENT} -le 90 ];
then
COLOR=orange
CRITICALALERT=No
else
COLOR=green
CRITICALALERT=NA
fi
echo '<TR><TD>'$FileSystem'</TD><TD ALIGN=RIGHT>'$Size'</TD>'
echo '<TD ALIGN=RIGHT>'$DiskUsed'</TD><TD ALIGN=RIGHT>'$DiskFree'</TD>'
echo '<TD><TABLE BORDER=0 CELLSPACING=3 CELLPADDING=0>'
echo '<TR><TD WIDTH='$((2 * $PERCENT))' BGCOLOR="'$COLOR'"></TD>'
echo '<TD WIDTH='$((2 * (100 - $PERCENT)))' BGCOLOR="gray"></TD>'
echo '<TD>'$DiskPercentUsed'</TD>'
echo '<TR></TABLE><TD>'$MountPoint'</TD>'
echo '<TD>'$CRITICALALERT'</TD>'
# echo '<TD>'`date`'</TD>'
3 echo '<TD>'${TDiffValue[ArryCount]} $in bytes$'</TD></TR>'
echo $DiskUsed >> `hostname`.usage.txt
ArryCount=$ArryCount+1
done
echo '</TABLE>'
echo ' '
echo '<TABLE BORDER=1 CELLSPACING=3 CELLPADDING=0>'
echo '<TR><TH FONT font-weight="bold">Legend Information</TH></TR>'
echo '<TR><TD FONT color="white" BGCOLOR="RED">Critical Alert</TD></TR>'
echo '<TR><TD FONT color="white" BGCOLOR="ORANGE">Warning Alert</TD></TR>'
echo '<TR><TD FONT color="white" BGCOLOR="GREEN">No Action Alert</TD></TR>'
echo '</TABLE>'
echo '<BODY></HTML>'
echo 'Report Generated by DBA Team'
) | tee `hostname`_${0##*/}.html
#
# Sending E-Mail Notification
#
(
echo To: "#DL-OracleSYSDBA@XXXX.com"
echo From: oracle@XXXXX.localdomain
echo "Content-Type: text/html; "
echo Subject: Disk Usage Report for server `hostname` 'for' $IPADDRESS
echo
cat `hostname`_${0##*/}.html
) | sendmail -t
echo -e "Report Generation is Completed... \n\a"

Sample Output

Different types of PATCH in Oracle

Interim Patch
Diagnostic Patch
Bundle Patch (BP)
Patch Set Update (PSU)
Security Patch Update (SPU)

How to Clear Cache on Linux?

Every Linux System has three options to clear cache without interrupting any processes or services.

1. Clear PageCache only.

# sync; echo 1 > /proc/sys/vm/drop_caches

2. Clear dentries and inodes.

# sync; echo 2 > /proc/sys/vm/drop_caches

3. Clear PageCache, dentries and inodes.

# sync; echo 3 > /proc/sys/vm/drop_caches

Explanation

sync will flush the file system buffer. Command Separated by ";" run sequentially. The shell wait for each command to terminate before executing the next command in the sequence. As mentioned in kernel documentation, writing to drop_cache will clean cache without killing any application/service, command echo is doing the job of writing to file.

As a root user

Tuesday, August 7, 2018

History of Exadata

First Oracle's exadata was released in 2008 known as hardware and software engineered to work together. Since than we have seen 6 release of exadata machine with improved the combination of hardware and software. Latest X5-2 released in 2015.

V1: The first Exadata was released in 2008 and labeled as V1. It was a combination of HP hardware and Oracle software. There were no flash cache used in this version. At that time Exadata was made specially for Data ware housing only. This product was not accepted widely as it was suffered from overheating.

V2: After V1 Oracle has launched Exadata V2 in 2009. It was a combination of Sun hardware and Oracle software. Here in V2 Oracle has upgraded storage cell with 384 GB of Exadata smart cache. Many o the customer has changed the Exadata from V to V2 because of the overheating problem in Exadata V1.

X2: This is the third edition of Oracle Exadata released in 2010.There are two version X2-2 stands for dual core CPUs and X2-8 stands for 8 core CPUs database server. This version is supporting both OLTP and Data warehouse. Also this has massive features compare to older Exadata in terms of CPUs, Memory, Compression, Smart scan etc...

X3: This is the forth edition of Exadata released in 2012. There is no much more difference between X2 and X3 except in CPU and RAM. This version has more RAM and CPU power then the previous one.

X4: It was released in 2013. In this version processing was increased to 2×12 core CPUs. It had the capacity to upgrade memory to 512GB in a compute node. Flash and disk storage was also increased. It featured a new model of high-capacity disk. 600GB disks were retired, and included 1.2TB, 10,000 RPM disks. These disks were a smaller form factor (2.5” vs 3.5”). The other big change with the X4-2 was the introduction of an active/active InfiniBand network connection. On the X4-2, Oracle broke the bonded connection and utilized each InfiniBand port independently. This allowed an increased throughput across the InfiniBand fabric.

X5: It was announced in early 2015, the sixth generation of Exadata. The X5-2 was a dramatic change in the platform, removing the high-performance Disk option in favor of an all-flash, NVMe (Non-Volatile Memory Express) model. High-capacity disk sizes remain the same at 4TB per disk. The size of the flash cards doubled to 6.4TB per storage server. Memory stayed consistent with a base of 256GB, upgradeable to 768 GB, and the CPU core count increased to 18 cores per socket. With X5, customers are allowed to purchase X5-2 with any configuration required—a base rack begins with two compute nodes and three storage servers.

Exadata Terms

HP - High Performance

HC - High Capacity

IOPS - IOs Per Second

LUN - Logical Unit Number

MS - Management Server

RS - Restart Server

Cellsrv – Cell Server

SATA - Serial Advanced Technology Attachment

SAS - Serial Attached SCSI

PDU - Power Distribution Unit

ILOM - Integrated Lights Out Manager

IB - InfiniBand

EHCC - Exadata Hybrid Columnar Compression

CU - Compression Unit

UDMs - User Defined Metrics

RDS - Reliable Data Socket protocol

SNMP - Simple Network Management Protocol

iDB - Intelligent Database protocol

EM - Enterprise Manager

EMGC - Enterprise Manager Grid Control

DBRM - Database Resource Manager

Friday, August 3, 2018

Blocking Session-2

set serveroutput on
BEGIN
dbms_output.enable(1000000);
for do_loop in (select session_id, a.object_id, xidsqn, oracle_username, b.owner owner,
b.object_name object_name, b.object_type object_type
FROM v$locked_object a, dba_objects b
WHERE xidsqn != 0
and b.object_id = a.object_id)
loop
dbms_output.put_line('.');
dbms_output.put_line('Blocking Session : '||do_loop.session_id);
dbms_output.put_line('Object (Owner/Name): '||do_loop.owner||'.'||do_loop.object_name);
dbms_output.put_line('Object Type : '||do_loop.object_type);
for next_loop in (select sid from v$lock
where id2 = do_loop.xidsqn
and sid != do_loop.session_id)
LOOP
dbms_output.put_line('Sessions being blocked : '||next_loop.sid);
end loop;
end loop;
END;
/

Output
=====
.
Blocking Session : 4929
Object (Owner/Name): SYS.OBJ$
Object Type : TABLE

PL/SQL procedure successfully completed.

Wednesday, August 1, 2018

Blocking Sessions

SELECT /*+ RULE */ 
 LPAD('--->',DECODE(A.request,0,0,5))||A.SID SID, B.serial#,
DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || 
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON, 
b.status, b.SCHEMANAME SCHEMA, 
DECODE(o.owner || '.' || o.object_name, '.', NULL, o.owner || '.' || o.object_name) OBJECT, o.object_type,
 b.osuser, b.machine, b.module, b.program, 
 DECODE(BLOCK, 0, NULL, 'BLOKER' ) || DECODE(request, 0, NULL, '-->WAITER' ) BLOKER,
 DECODE (A.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 
'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', A.lmode) lmode,
DECODE(TRUNC(ctime/86400), 0, TO_CHAR(TO_DATE(ctime, 'SSSSS'), 'HH24:MI:SS'), 
TRUNC(ctime/86400) || ' Days + ' || TO_CHAR(TO_DATE(ctime - (TRUNC(ctime/86400))*86400 , 'SSSSS'), 'HH24:MI:SS')) TIME, 
A.TYPE, 'alter system kill session ' || '''' || a.SID || ', ' || b.serial# ||'''' || ' immediate;' kill_session, 
DECODE(object_type, NULL, NULL, 'Dbms_Rowid.rowid_create(1, ' || row_wait_obj# || ', ' 
|| row_wait_file# ||', ' || row_wait_block#||', ' || row_wait_row# ||')') row_id 
 FROM v$lock A, v$session b, dba_objects o
 WHERE A.SID = b.SID 
  AND (lmode = 0 OR BLOCK = 1) 
  AND o.object_id (+) = DECODE(b.ROW_WAIT_OBJ#, -1, NULL, b.ROW_WAIT_OBJ#)
 ORDER BY A.id1,A.request;

Calculate Export dump size

The following query calculates how much table data each schema takes on your database

SELECT owner, ROUND(SUM(size_mb)) MBytes FROM 
(
SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name 
FROM DBA_SEGMENTS 
WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION') 
--AND TABLESPACE_NAME LIKE 'COSTE%' 
--AND SEGMENT_NAME LIKE 'OE_ORDER_LINES_ALL%' 
--AND partition_name LIKE 'USAGE_FCT_NEW%'
--AND OWNER = 'TARGET_DW' 
--AND ROUND(bytes/(1024*1024),2) > 1000)
) 
GROUP BY owner 
ORDER BY MBytes DESC;

Create a super user like DBA but with no administration power

Summary

Some time you have the requirement to create a user to the database that has access to all v$session, v$lock, dba_free_space and other dictionary views to monitor performance issues, sql tuning, and other DBA operations.

But you don't want to have real power to change things like a DBA can do.

The quick and easy word around is to grant the select_catalog_role role to the new user.

create user dba_monitor identified by dba_monitor;
grant connect, select_catalog_role to dba_monitor;

Now with the new user you can use tools like TOAD or Grid Control to monitor the database as a DBA but with no real power to change things

When did a user change his/her password?

SELECT NAME, ptime AS "LAST TIME CHANGED", ctime "CREATION TIME", ltime "LOCKED" 
FROM USER$ 
WHERE ptime IS NOT NULL 
ORDER BY ptime DESC;

Explain plan and statistics with SQL*Plus

SQL> set timing on
SQL> set autotrace traceonly explain
SQL> select * from dual;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> set autotrace traceonly statistics
SQL> /

Elapsed: 00:00:00.01

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
2 physical reads
0 redo size
522 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> set autotrace traceonly explain

SQL> /

Elapsed: 00:00:00.00

Execution Plan

----------------------------------------------------------

Plan hash value: 272002086

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------

Environment settings for SQL*PLUS - SET

Summary

SQL*Plus environment is controlled a big list of SQL*Plus system settings. You can change them by using the SET command as shown in the following list:

    * SET AUTOCOMMIT OFF - Turns off the auto-commit feature.
    * SET FEEDBACK OFF - Stops displaying the "27 rows selected." message at the end of the query output.
    * SET HEADING OFF - Stops displaying the header line of the query output.
    * SET LINESIZE 256 - Sets the number of characters per line when displaying the query output.
    * SET NEWPAGE 2 - Sets 2 blank lines to be displayed on each page of the query output.
    * SET NEWPAGE NONE - Sets for no blank lines to be displayed on each page of the query output.
    * SET NULL 'null' - Asks SQL*Plus to display 'null' for columns that have null values in the query output.
    * SET PAGESIZE 60 - Sets the number of lines per page when displaying the query output.
    * SET TIMING ON - Asks SQL*Plus to display the command execution timing data.
    * SET WRAP OFF - Turns off the wrapping feature when displaying query output.

Memory Notification: Library Cache Object loaded into SGA

Summary
In the Oracle10g a new heap checking mechanism, together with a new messaging system is introduced. This new mechanism reports memory allocations above a threshold in the alert.log, together with a tracefile in the udump directory. If you notice in your alert.log you may get messages like this:

Memory Notification: Library Cache Object loaded into SGA
Heap size 58689K exceeds notification threshold (51200K)

This message means that the threshold set by hidden parameter _kgl_large_heap_warning_threshold has been exceeded!

In certain situations this can be very helpful to inform you if large allocations have been done in the sga heap (shared pool). The notification mechanism allows you to troubleshoot memory allocation problems, which eventually will appear as the infamous ORA-4031.

If you don't have ORA-04031: unable to allocate x bytes of shared memory problems and don't want to appear the message in the alert log then you can increase the hidden parameter _kgl_large_heap_warning_threshold.

The default limit is set at 2048K. To find the value of this parameter execute

SELECT * FROM (
SELECT a.ksppinm AS parameter,
       a.ksppdesc AS description,
       b.ksppstvl AS session_value,
       c.ksppstvl AS instance_value
FROM   x$ksppi a,
       x$ksppcv b,
       x$ksppsv c
WHERE  a.indx = b.indx
AND    a.indx = c.indx
AND    a.ksppinm LIKE '/_%' ESCAPE '/'
ORDER BY a.ksppinm) 
WHERE parameter IN ('_kgl_large_heap_warning_threshold')

In the description of the parameter indicates: maximum heap size before KGL writes warnings to the alert log

Workaround
If you want to disappear the message from the alert.log increase the hidden parameter to something bigger, for example

ALTER SYSTEM SET "_kgl_large_heap_warning_threshold" = 89428800 SCOPE=SPFILE ;