UNIX

Automate CPU utilization metrics (from SAR)

Table has to be created for capturing the CPU Utilization and save it to Database.

CREATE TABLE "SCOTT"."UTILIZATIONS" 
   ("DATE_ID" DATE DEFAULT SYSDATE, 
	"TIME_ID" VARCHAR2(15 BYTE), 
	"CPU" VARCHAR2(5 BYTE), 
	"USER_PCT" NUMBER(4,2), 
	"NICE_PCT" NUMBER(4,2), 
	"SYSTEM_PCT" NUMBER(4,2), 
	"IOWAIT_PCT" NUMBER(4,2), 
	"STEAL_PCT" NUMBER(4,2), 
	"IDLE_PCT" NUMBER(4,2), 
	"INST_NAME" VARCHAR2(10 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SCOTT_DATA" ;
  
sar.ctl is the control file for loading the Text file (Utilizations) to Database.  
cat <strong>/u01/app/oracle/DBA/tasks/sardata/sar.ctl </strong> 

load data
infile '/u01/app/oracle/DBA/tasks/sardata/sar.txt' 
append
into table UTILIZATIONS
fields terminated by "," 
trailing nullcols(
DATE_ID DATE "DD-MM-YYYY",
TIME_ID,
CPU,
USER_PCT,
NICE_PCT,
SYSTEM_PCT,
IOWAIT_PCT,
STEAL_PCT,
IDLE_PCT,
INST_NAME CONSTANT ORCL12)


$ cat <strong>/u01/app/oracle/DBA/tasks/sardata/sarcollect.sh</strong>
cd /u01/app/oracle/DBA/tasks/sardata
cd /u01/app/oracle/DBA/tasks/sardata
export ORACLE_SID=orcl12
export ORACLE_HOME=/u01/app/oracle/product/12102
export PATH=/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/u01/app/oracle/product/12102/bin
export LC_TIME="POSIX"
####(For A Particular Date Use the following Line by multiplying 86400 * No.of days 
##################sar -f /var/log/sa/sa19 | grep -v 'Average'|awk '{ print strftime("%d/%m/%Y",systime()-172800), $0; fflush() }' > /u01/app/oracle/DBA/tasks/sardata/sa.txt
sar -f /var/log/sa/sa$(date +%d -d yesterday) | grep -v 'Average'|awk '{ print strftime("%d/%m/%Y",systime()-86400), $0; fflush() }' > /u01/app/oracle/DBA/tasks/sardata/sa.txt
sed -i -e 1,3d /u01/app/oracle/DBA/tasks/sardata/sa.txt
sed -i -e '$d' /u01/app/oracle/DBA/tasks/sardata/sa.txt
sed 's/ \{1,\}/,/g' /u01/app/oracle/DBA/tasks/sardata/sa.txt > /u01/app/oracle/DBA/tasks/sardata/sar.txt
cp /u01/app/oracle/DBA/tasks/sardata/sar.txt /u01/app/oracle/DBA/tasks/sardata/log/sar`date -dy '+%m%d%y'`.log
sqlldr scott/tiger123 control=sar.ctl bindsize=512000 rows=100000 errors=10000
rm sar.txt

Query for getting CPU Utilization from Database Query.
SELECT DATE_ID,TIME_ID,(100-IDLE_PCT) AS UTILIZED_CPU_PCT FROM SEPHORA.UTILIZATIONS WHERE INST_NAME='ORCL12' ORDER BY 1,2;

The same can be used for multi node RAC Database, for different time frames to see how the utilization is.

CREATE TABLE "SCOTT"."UTILIZATIONS"

("DATE_ID" DATE DEFAULT SYSDATE,

"TIME_ID" VARCHAR2(15 BYTE),

"CPU" VARCHAR2(5 BYTE),

"USER_PCT" NUMBER(4,2),

"NICE_PCT" NUMBER(4,2),

"SYSTEM_PCT" NUMBER(4,2),

"IOWAIT_PCT" NUMBER(4,2),

"STEAL_PCT" NUMBER(4,2),

"IDLE_PCT" NUMBER(4,2),

"INST_NAME" VARCHAR2(10 BYTE)

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SCOTT_DATA" ;

sar.ctl is the control file for loading the Text file (Utilizations) to Database.

cat <strong>/u01/app/oracle/DBA/tasks/sardata/sar.ctl </strong>

load data

infile '/u01/app/oracle/DBA/tasks/sardata/sar.txt'

append

into table UTILIZATIONS

fields terminated by ","

trailing nullcols(

DATE_ID DATE "DD-MM-YYYY",

TIME_ID,

CPU,

USER_PCT,

NICE_PCT,

SYSTEM_PCT,

IOWAIT_PCT,

STEAL_PCT,

IDLE_PCT,

INST_NAME CONSTANT ORCL12)

$ cat <strong>/u01/app/oracle/DBA/tasks/sardata/sarcollect.sh</strong>

cd /u01/app/oracle/DBA/tasks/sardata

export ORACLE_SID=orcl12

export ORACLE_HOME=/u01/app/oracle/product/12102

export PATH=/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/u01/app/oracle/product/12102/bin

export LC_TIME="POSIX"

####(For A Particular Date Use the following Line by multiplying 86400 * No.of days

##################sar -f /var/log/sa/sa19 | grep -v 'Average'|awk '{ print strftime("%d/%m/%Y",systime()-172800), $0; fflush() }' > /u01/app/oracle/DBA/tasks/sardata/sa.txt

sar -f /var/log/sa/sa$(date +%d -d yesterday) | grep -v 'Average'|awk '{ print strftime("%d/%m/%Y",systime()-86400), $0; fflush() }' > /u01/app/oracle/DBA/tasks/sardata/sa.txt

sed -i -e 1,3d /u01/app/oracle/DBA/tasks/sardata/sa.txt

sed -i -e '$d' /u01/app/oracle/DBA/tasks/sardata/sa.txt

sed 's/ \{1,\}/,/g' /u01/app/oracle/DBA/tasks/sardata/sa.txt > /u01/app/oracle/DBA/tasks/sardata/sar.txt

cp /u01/app/oracle/DBA/tasks/sardata/sar.txt /u01/app/oracle/DBA/tasks/sardata/log/sar`date -dy '+%m%d%y'`.log

sqlldr scott/tiger123 control=sar.ctl bindsize=512000 rows=100000 errors=10000

rm sar.txt

Query for getting CPU Utilization from Database Query.

SELECT DATE_ID,TIME_ID,(100-IDLE_PCT) AS UTILIZED_CPU_PCT FROM SEPHORA.UTILIZATIONS WHERE INST_NAME='ORCL12' ORDER BY 1,2;

The same can be used for multi node RAC Database, for different time frames to see how the utilization is.

VI EDITOR COMMANDS

2 years ago by DayakerUNIX

VI EDITOR COMMANDS $vi file1 Default mode will escape mode If u want switch into insert mode We have commands I : Current position insert mode A : at the end of the line insert mode a: next to the current position insert mode s:… Read More

Solaris 11 : Creating ZFS filesystem and setting up NFS server

2 years ago by Ajayram GopuUNIX

Scenario : Use 3 disks with 1Tb each and making as one Volume group of 3TB creating a ZFS filesystem . Use the filesystem and share the disk using NFS service. Setting up ZFS filesystem using 3 disks 1. Check for free disk using… Read More

SSH keyless login with 2048 bit RSA key

2 years ago by Ajayram GopuUNIX

1. Login to Server A and Generate key ( You can generate RSA or DSA key ). I am demonstration RSA key with 2048 bit hostAuser@hostA:~$ ssh-keygen -t rsa -b 2048 Generating public/private rsa key pair.Enter file in which to save the key (/export/home/hostAuser/.ssh/id_rsa):[enter] Enter passphrase… Read More