Search This Blog

Wednesday, 25 August 2021

CELLCLI COMMANDS

 

 => To check asmmode status (should be online before activity) 

[root@abc3pcelabc03 ~]# cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome
         DATA1_CD_00_abc3PCELabc03     ONLINE  Yes
         DATA1_CD_01_abc3PCELabc03     ONLINE  Yes
         DATA1_CD_02_abc3PCELabc03     ONLINE  Yes
        
=> To inactivate all griddisk under cellnode 3:


[root@abc3pcelabc03 ~]# cellcli -e alter griddisk all inactive
GridDisk DATA1_CD_00_abc3PCELabc03 successfully altered
GridDisk DATA1_CD_01_abc3PCELabc03 successfully altered
GridDisk DATA1_CD_02_abc3PCELabc03 successfully altered
GridDisk DATA1_CD_03_abc3PCELabc03 successfully altered
GridDisk DATA1_CD_04_abc3PCELabc03 successfully altered

=> Check griddisk status (it should be offline)
=> To check asmmode status (should be offline after inactive gridisk activity)


[root@abc3pcelabc03 ~]# cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome
         DATA1_CD_00_abc3PCELabc03     OFFLINE         Yes
         DATA1_CD_01_abc3PCELabc03     OFFLINE         Yes
         DATA1_CD_02_abc3PCELabc03     OFFLINE         Yes
         DATA1_CD_03_abc3PCELabc03     OFFLINE         Yes
         DATA1_CD_04_abc3PCELabc03     OFFLINE         Yes
        

[root@abc3pcelabc03 ~]# cellcli -e list griddisk
         DATA1_CD_00_abc3PCELabc03     inactive
         DATA1_CD_01_abc3PCELabc03     inactive
         DATA1_CD_02_abc3PCELabc03     inactive
         DATA1_CD_03_abc3PCELabc03     inactive
         DATA1_CD_04_abc3PCELabc03     inactive
        
=> connect to asm and check cellcheck:

PATH                                                         FAILGROUP                      MODE_ST HEADER_STATU STATE
------------------------------------------------------------ ------------------------------ ------- ------------ --------
o/191.100.09.99;191.100.09.110/DATA1_CD_00_abc3pcelabc01    abc3PCELabc01                 ONLINE  MEMBER       NORMAL
o/191.100.09.99;191.100.09.110/DATA1_CD_00_abc3pcelabc01    abc3PCELabc01                 ONLINE  MEMBER       NORMAL
o/191.100.09.99;191.100.09.110/DATA1_CD_01_abc3pcelabc01    abc3PCELabc01                 ONLINE  MEMBER       NORMAL

 

[root@abc3pdbabc01 ~]# ssh abc3pcelabc03
Last login: Thu Jun 11 21:45:40 2020 from 10.53.5.130
[root@abc3pcelabc03 ~]# cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome
         DATAC1_CD_00_abc3PCELabc03     OFFLINE         Yes
         DATAC1_CD_01_abc3PCELabc03     OFFLINE         Yes
         DATAC1_CD_02_abc3PCELabc03     OFFLINE         Yes
         DATAC1_CD_03_abc3PCELabc03     OFFLINE         Yes
         DATAC1_CD_04_abc3PCELabc03     OFFLINE         Yes
         DATAC1_CD_05_abc3PCELabc03     OFFLINE         Yes
         DATAC1_CD_06_abc3PCELabc03     OFFLINE         Yes
         
[root@abc3pcelabc03 ~]# cellcli -e list griddisk
         DATAC1_CD_00_abc3PCELabc03     inactive
         DATAC1_CD_01_abc3PCELabc03     inactive
         DATAC1_CD_02_abc3PCELabc03     inactive
         DATAC1_CD_03_abc3PCELabc03     inactive


[root@abc3pcelabc03 ~]# cellcli -e alter griddisk all active
GridDisk DATAC1_CD_00_abc3PCELabc03 successfully altered
GridDisk DATAC1_CD_01_abc3PCELabc03 successfully altered
GridDisk DATAC1_CD_02_abc3PCELabc03 successfully altered
GridDisk DATAC1_CD_03_abc3PCELabc03 successfully altered
GridDisk DATAC1_CD_04_abc3PCELabc03 successfully altered

[root@abc3pcelabc03 ~]# cellcli -e list griddisk
         DATAC1_CD_00_abc3PCELabc03     active
         DATAC1_CD_01_abc3PCELabc03     active
         DATAC1_CD_02_abc3PCELabc03     active
         DATAC1_CD_03_abc3PCELabc03     active
         DATAC1_CD_04_abc3PCELabc03     active
       
[root@abc3pcelabc03 ~]# cellcli -e list griddisk attributes name,asmmodestatus
         DATAC1_CD_00_abc3PCELabc03     SYNCING
         DATAC1_CD_01_abc3PCELabc03     SYNCING
         DATAC1_CD_02_abc3PCELabc03     SYNCING
         DATAC1_CD_03_abc3PCELabc03     SYNCING


[root@abc3pcelabc03 ~]# cellcli -e list griddisk attributes name,asmmodestatus
         DATAC1_CD_00_abc3PCELabc03     SYNCING
         DATAC1_CD_01_abc3PCELabc03     SYNCING
         DATAC1_CD_02_abc3PCELabc03     SYNCING
         DATAC1_CD_03_abc3PCELabc03     SYNCING
         
[root@abc3pcelabc03 ~]# date
Thu Jun 11 22:17:23 +08 2020
[root@abc3pcelabc03 ~]# cellcli -e list griddisk attributes name,asmmodestatus
         DATAC1_CD_00_abc3PCELabc03     SYNCING
         DATAC1_CD_01_abc3PCELabc03     SYNCING
         DATAC1_CD_02_abc3PCELabc03     SYNCING
         DATAC1_CD_03_abc3PCELabc03     SYNCING
         DBFS_DG_CD_02_abc3pcelabc03    ONLINE
         DBFS_DG_CD_03_abc3pcelabc03    ONLINE
         DBFS_DG_CD_04_abc3pcelabc03    ONLINE
         

[root@abc3pcelabc03 ~]# cellcli -e list griddisk attributes name,asmmodestatus
         DATAC1_CD_00_abc3PCELabc03     ONLINE
         DATAC1_CD_01_abc3PCELabc03     ONLINE
         DATAC1_CD_02_abc3PCELabc03     ONLINE
         DATAC1_CD_03_abc3PCELabc03     ONLINE

 [root@abc3pdbabc01 ~]# ssh abc3pcelabc03
Last login: Fri May 29 00:46:39 2020 from abc3pdbabc01.intra2.wda.gov.sg

[root@abc3pcelabc03 ~]#
[root@abc3pcelabc03 ~]# cellcli -e create griddisk DATA1_CD_01_abc3pcelabc03  celldisk=CD_01_abc3pcelabc03, offset=32M, size=6.1015625T
GridDisk DATA1_CD_01_abc3pcelabc03 successfully created
[root@abc3pcelabc03 ~]# cellcli -e create griddisk DATA1_CD_02_abc3pcelabc03  celldisk=CD_02_abc3pcelabc03, offset=32M, size=6.1015625T
GridDisk DATA1_CD_02_abc3pcelabc03 successfully created
[root@abc3pcelabc03 ~]# cellcli -e create griddisk DATA1_CD_03_abc3pcelabc03  celldisk=CD_03_abc3pcelabc03, offset=32M, size=6.1015625T
GridDisk DATA1_CD_03_abc3pcelabc03 successfully created

[root@abc3pcelabc03 ~]#
[root@abc3pcelabc03 ~]#
[root@abc3pcelabc03 ~]# cellcli -e list griddisk attributes name, asmdiskgroupName, status, asmmodestatus, asmdeactivationoutcome
         DATA1_CD_00_abc3pcelabc03                     active  UNUSED  Yes
         DATA1_CD_01_abc3pcelabc03                     active  UNUSED  Yes
         DATA1_CD_02_abc3pcelabc03                     active  UNUSED  Yes
         DATA1_CD_03_abc3pcelabc03                     active  UNUSED  Yes
         DS_DG_CD_02_abc3pcelabc03    DS_DG         active  ONLINE  Yes
         DS_DG_CD_03_abc3pcelabc03    DS_DG         active  ONLINE  Yes
         REC1REC1_CD_00_abc3pcelabc03     REC1          active  ONLINE  Yes
         REC1_CD_01_abc3pcelabc03     REC1          active  ONLINE  Yes
         REC1_CD_02_abc3pcelabc03     REC1          active  ONLINE  Yes
         REC1_CD_03_abc3pcelabc03     REC1          active  ONLINE  Yes
         REC1_CD_04_abc3pcelabc03     REC1          active  ONLINE  Yes

[root@abc3pcelabc03 ~]# cellcli -e LIST GRIDDISK ATTRIBUTES name,asmDiskGroupName,cachingpolicy,size,offset
         DATA1_CD_00_abc3pcelabc03                     default         6.1015625T              32M
         DATA1_CD_01_abc3pcelabc03                     default         6.1015625T              32M
         DATA1_CD_02_abc3pcelabc03                     default         6.1015625T              32M
         DATA1_CD_03_abc3pcelabc03                     default         6.1015625T              32M
         DATA1_CD_04_abc3pcelabc03                     default         6.1015625T              32M

         DS_DG_CD_02_abc3pcelabc03    DS_DG         default         33.796875G              7.1192474365234375T
         DS_DG_CD_03_abc3pcelabc03    DS_DG         default         33.796875G              7.1192474365234375T
         REC1_CD_00_abc3pcelabc03     REC1          none            1.01763916015625T       6.1016082763671875T
         REC1_CD_01_abc3pcelabc03     REC1          none            1.01763916015625T       6.1016082763671875T
----------------------------------------------------------------------------------------------------------------------------
[root@abc3pcelabc03 ~]# cellcli -e LIST celldisk attributes name, freespace
         CD_00_abc3pcelabc03    0
         CD_01_abc3pcelabc03    0
         CD_02_abc3pcelabc03    0
         CD_03_abc3pcelabc03    0
         FD_00_abc3pcelabc03    0
         FD_01_abc3pcelabc03    0
         FD_02_abc3pcelabc03    0
         FD_03_abc3pcelabc03    0
[root@abc3pcelabc03 ~]#

----------------------------------------------------------------------------------------------------------------------------
To activate griddisks:


cellcli -e list griddisk
cellcli -e alter griddisk all active

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

TO verify alert history:

cellcli -e list alerthistory 


[root@abc3pcelabc03 ~]# cellcli -e list alerthistory
         1_1     2020-01-11T11:12:42+08:00       critical        "Configuration check discovered the following problems:   Verify network configuration: /opt/oracle.cellos/ipconf -check-consistency -semantic -ignore-get-ilom-errors [Info]: ipconf command line: /opt/oracle.cellos/ipconf.pl -check-consistency -semantic -ignore-get-ilom-errors -nocodes [Info]: Verify that the configured values in the Exadata configuration file /opt/oracle.cellos/cell.conf agree with the actual values in use on this system ILOM IP address 10.53.0.96 must match 10.230.14.190 in /opt/oracle.cellos/cell.conf              : FAILED ILOM netmask 255.255.248.0 must match 255.255.255.0 in /opt/oracle.cellos/cell.conf              : FAILED ILOM gateway 10.53.0.1 matches 10.230.14.7 from /opt/oracle.cellos/cell.conf                     : FAILED ILOM DNS server 10.84.94.130 must match 10.230.20.21 in /opt/oracle.cellos/cell.conf             : FAILED ILOM first NTP server 10.84.94.130 must match 10.230.20.21 in /opt/oracle.cellos/cell.conf       : FAILED [Info]: Consistency check FAILED"
         2_1     2020-01-19T14:54:57+08:00       critical        "Internal USB status is degraded.  Affected USB Slots : 0"
         3_1     2020-01-21T03:10:02+08:00       critical        "ILOM has stopped responding, and did not reset after issuing reset commands."
         3_2     2020-05-28T20:35:27+08:00       clear           "ILOM is back to a normal state."
         7       2020-05-15T05:18:03+08:00       critical        "Read-only file system detection thread was hung. Cell was power cycled to restore access to the cell."


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

KFOD Commands:

==================

[+ASM1@abc3pdbabc01]/u01/app/12.1.0.2/grid/bin $ kfod disks=all dscvgroup=true | grep -i abc3pcelabc01 |grep -i DATA1
   3:    6397952 Mb o/191.100.09.109;191.100.09.110/DATA1_CD_00_abc3pcelabc01 DATA1
   4:    6397952 Mb o/191.100.09.109;191.100.09.110/DATA1_CD_01_abc3pcelabc01 DATA1
   5:    6397952 Mb o/191.100.09.109;191.100.09.110/DATA1_CD_02_abc3pcelabc01 DATA1
   6:    6397952 Mb o/191.100.09.109;191.100.09.110/DATA1_CD_03_abc3pcelabc01 DATA1
   7:    6397952 Mb o/191.100.09.109;191.100.09.110/DATA1_CD_04_abc3pcelabc01 DATA1

[+ASM1@abc3pdbabc01]/u01/app/12.1.0.2/grid/bin $


[+ASM2@wdax3pdbadm02]/u01/app/12.1.0.2/grid/bin $ kfod disks=all dscvgroup=true | grep -i abc3pcelabc01 |grep -i DATA1
   1:    6397952 Mb o/191.100.09.109;191.100.09.110/DATA1_CD_00_abc3pcelabc01 DATA1
   2:    6397952 Mb o/191.100.09.109;191.100.09.110/DATA1_CD_01_abc3pcelabc01 DATA1
   3:    6397952 Mb o/191.100.09.109;191.100.09.110/DATA1_CD_02_abc3pcelabc01 DATA1
   4:    6397952 Mb o/191.100.09.109;191.100.09.110/DATA1_CD_03_abc3pcelabc01 DATA1
   5:    6397952 Mb o/191.100.09.109;191.100.09.110/DATA1_CD_04_abc3pcelabc01 DATA1

[+ASM2@wdax3pdbadm02]/u01/app/12.1.0.2/grid/bin $

EXADATA Commands & (ADDING DISK & DELETING DISK)

 

 IBSWITCH INFO:

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

ibhosts

ibswitches

LIST DISKS VIA KFOD:

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

kfod disks=all dscvgroup=true | grep -i abc3pceladm03 |grep -i datac1
kfod disks=all dscvgroup=true | grep -i abc3pceladm03

 List all Disks via CELLCLI:

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

cellcli -e list griddisk

cellcli -e list griddisk attributes name,asmmodestatus

list griddisk attributes name where asmdeactivationoutcome!='Yes'

cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome

cellcli -e list griddisk attributes name,asmmodestatus


[root@abc3pceladm03 log]# cellcli -e LIST GRIDDISK ATTRIBUTES name,asmDiskGroupName,cachingpolicy,size,offset
         DATA1_CD_00_abc3PCELADM03     DATA1          default         6.1015625T              32M
         DATA1_CD_01_abc3PCELADM03     DATA1          default         6.1015625T              32M
         DATA1_CD_02_abc3PCELADM03     DATA1          default         6.1015625T              32M
         DATA1_CD_03_abc3PCELADM03     DATA1          default         6.1015625T              32M
         DATA1_CD_04_abc3PCELADM03     DATA1          default         6.1015625T              32M
         DATA1_CD_05_abc3PCELADM03     DATA1          default         6.1015625T              32M
         DBFS_DG_CD_02_abc3pceladm03    DBFS_DG         default         33.796875G              7.1192474365234375T
         DBFS_DG_CD_03_abc3pceladm03    DBFS_DG         default         33.796875G              7.1192474365234375T

         RREC1_CD_00_abc3pceladm03     RREC1          none            1.01763916015625T       6.1016082763671875T
         RREC1_CD_01_abc3pceladm03     RREC1          none            1.01763916015625T       6.1016082763671875T
         RREC1_CD_02_abc3pceladm03     RREC1          none            1.01763916015625T       6.1016082763671875T
         RREC1_CD_03_abc3pceladm03     RREC1          none            1.01763916015625T       6.1016082763671875T
         RREC1_CD_04_abc3pceladm03     RREC1          none            1.01763916015625T       6.1016082763671875T

LIST DISKS FROM CELLCLI:

list grid disk from cellnode 3:
=>  ssh abc3pceladm03

 CellCLI> list griddisk
         DATAC1_CD_00_ABC3PCELADM03     active
         DATAC1_CD_01_ABC3PCELADM03     active
         DATAC1_CD_02_ABC3PCELADM03     active
         DATAC1_CD_03_ABC3PCELADM03     active

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

Veritas:

======

Backup list command:

bplist -C abcx212cpdb02-bak -t 4 -l -R / 

ASM DISKGROUP SIZE:

=================

set lines 300 pages 999
col COMPATIBILITY for a12
col DATABASE_COMPATIBILITY for a12
col NAME for a12
col state for a9
col type for a9
select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB/1024 "TOTAL GB",FREE_MB/1024 "FREE GB",((TOTAL_MB-FREE_MB)/1024) "USED GB",((total_mb-free_mb)/total_mb)*100 "%Used",OFFLINE_DISKS,COMPATIBILITY,DATABASE_COMPATIBILITY from v$aSQL> sm_diskgroup order by GROUP_NUMBER;


Database creation:

==============================================
DBCA:

nohup $ORACLE_HOME/bin/dbca -silent -createDatabase -templateName General_Purpose.dbc
-gdbName RCT  -sid RCT  -SysPassword We1come-SystemPassword We1come$
-emConfiguration NONE -redoLogFileSize 200  
-storageType ASM -asmSysPassword We1come$
-diskGroupName +DATAU1
-RECOVERYGROUPNAME +RECOU1
-characterSet AL32UTF8
-nationalCharacterSet AL32UTF8
-automaticMemoryManagement true
-totalMemory 2048  
-databaseType MULTIPURPOSE
-nodelist abcx212cudbadm01,abcx212cudbadm02 > RCT_creation.log &
============================

CHANGE RE BALANCE FROM 1 to 16:

GROUP_NUMBER OPERA PASS      STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE                                  CON_ID
------------ ----- --------- ---- ---------- ---------- ---------- ---------- ---------- ----------- -------------------------------------------- ----------
           3 REBAL RESYNC    DONE          1          1          0          0          0           0                                          0
           3 REBAL RESILVER  DONE          1          1          0          0          0           0                                          0
           3 REBAL REBALANCE RUN           1          1    2682423    2854811       1225         140                                          0
           3 REBAL COMPACT   WAIT          1          1          0          0          0           0                                          0

SYS@+ASM2> alter diskgroup REDOC1 rebalance power 16 nowait;

Diskgroup altered.

SYS@+ASM2> select * from v$asm_operation;

GROUP_NUMBER OPERA PASS      STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE                                  CON_ID
------------ ----- --------- ---- ---------- ---------- ---------- ---------- ---------- ----------- -------------------------------------------- ----------
           3 REBAL RESYNC    DONE         16         16          0          0          0           0                                          0
           3 REBAL RESILVER  DONE         16         16          0          0          0           0                                          0
           3 REBAL REBALANCE EST          16         16          0          0          0           0                                          0
           3 REBAL COMPACT   WAIT         16         16          0          0          0           0  


Disk repaire time:

===============
 col VALUE for a30;
 col NAME for a20;
 select dg.name,a.value from v$asm_diskgroup dg, v$asm_attribute a where  dg.group_number=a.group_number  and a.name='disk_repair_time';

 
 NAME                 VALUE
-------------------- ------------------------------
DATA1               800h
DB_DG              800h
REDOC1               800h



Thursday, 18 February 2021

SQL agent startup failed with "system cant find the path"

 

Changing the Dump Directory Location may SQL Agent to not start:

 error" sql agent"system cant find the path"

To fix this problem, is quite easy. Open the Registry Editor by typing regedit on cmd and go to the following key and validate if the directory has changed, or it is still pointing to the old one.

 

i have compared with sql configuration and regedit configuration.Then i found errorlog location is diffrent in regedit.

 

 

Monday, 23 November 2020

HOW SCAN LISTNER WILL KNOW THE LEAST LOAD NODE

The CRSd process starts orarootagent & oraagent processes,  

=> orarootagent is responsible for starting Node VIP, SCAN VIP and 

=> oraagent process is responsible for starting SCAN Listener and Node Listener.

LBA: LOAD BALANCE ADVISORY

With the help of LBA listener connection request to be re-routed. PMON process will records each node load and update to LBA.



Wednesday, 18 November 2020

Blocking session & CPU & MEMEORY info:

 Blocking session

set serveroutput on
set feedback off
set linesize 300
 
declare
blockcount number;
sqlreport varchar(3000);
sqloutput varchar(5000);
v_rec varchar(3000);
 
cursor blk_curs is select B.USERNAME BUSER,B.SID BSID,B.SERIAL# BSERIAL,B.INST_ID BID,B.STATUS BSTAT,B.LAST_CALL_ET BLAST,W.USERNAME WUSER,W.SID WSID,W.SERIAL# WSERIAL,W.INST_ID WID
from (select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET from gv$session where BLOCKING_SESSION >0) W,
(select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv$session ) B
where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN');
cursor distval is select distinct B.SID CSID, B.USERNAME CUSER from (select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET
from gv$session where BLOCKING_SESSION >0) W, (select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv$session ) B
where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN');
cursor killcurs(v in varchar, b in number) is select SID ,serial# SERIAL,inst_id ,username ,to_char(logon_time,'''DD-MON-YYYY HH24-MI-SS''') OTIME,event,status ,machine ,round(LAST_CALL_ET/60,2) OMIN
FROM gv$session where username is not null and username=v and sid=b order by inst_id;
begin
DBMS_OUTPUT.put_line('Checking for blocking sessions on this database');
DBMS_OUTPUT.put_line('-----------------------------------------------');
select count(*) into blockcount from (select * from gv$session where BLOCKING_SESSION >0) W,
(select * from gv$session ) B where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN');
IF blockcount > 0 THEN
DBMS_OUTPUT.put_line('Found blocking sessions -> Fetching report for the same');
DBMS_OUTPUT.put_line('-------------------------------------------------------');
for v_rec in blk_curs LOOP
dbms_output.put_line(v_rec.BUSER ||' ('||v_rec.BSID||','||v_rec.BSERIAL||',@'||v_rec.BID||') is Currently '||v_rec.BSTAT||' for last '||v_rec.BLAST||' Sec and it''s BLOCKING user '|| v_rec.WUSER||' ('||v_rec.WSID||','||v_rec.WSERIAL||',@'||v_rec.WID||')');
end loop;
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('Further details on blocking sessions -> includes kill script of blocking session');
DBMS_OUTPUT.put_line('--------------------------------------------------------------------------------');
for v_rec2 in distval LOOP
for v_rec3 in killcurs(v_rec2.CUSER, v_rec2.CSID) loop
dbms_output.put_line('alter system kill session '''||v_rec3.SID|| ',' || v_rec3.SERIAL||',@'||v_rec3.inst_id|| ''' immediate; '||v_rec3.username||' '||v_rec3.OTIME||' '|| v_rec3.event||' '||v_rec3.status||' '||v_rec3.machine||' '||v_rec3.OMIN);
end loop;
end loop;
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('-');
ELSE
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('Hurrey !!! No blocking sessions found');
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('-');
END IF;
END;
/
================================== 
Top CPU Queries
 

select * from (SELECT dhst.sql_id,SQL_TEXT,X.CPU_TIME/1000000 CPU_Time_inSec,X.Num_Execs,round(((X.CPU_TIME/1000000)/X.Num_Execs),3) CPUPEREXEC, X.num_rows
FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME ,sum(executions_delta) as Num_Execs,sum(ROWS_PROCESSED_delta) as num_rows
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE begin_interval_time > =TO_DATE('25/APR/2019 20:50','DD-MON-YYYY HH24:MI')
AND END_INTERVAL_TIME<=TO_DATE('26/APR/2019 06:30','DD-MON-YYYY HH24:MI')
) GROUP BY DHSS.SQL_ID) X WHERE X.SQL_ID=DHST.SQL_ID ORDER BY X.CPU_TIME DESC
FETCH FIRST 50 ROWS ONLY)y
order by y.cpuperexec desc
 
 
================================== 
 
Top ELAPSED Queries : 
select sub.sql_id,
txt.sql_text,
parsing_schema_name,
sub.seconds_since_date as TOTALELAPSEDTIME,
sub.execs_since_date as NUmberofExecs,
sub.gets_rows_date as RowsProcessed,
round(sub.seconds_since_date / (sub.execs_since_date + 0.01), 3) avg_query_time
from ( -- sub to sort before top N filter
select sql_id,
g.parsing_schema_name,
round(sum(ELAPSED_TIME_delta) / 1000000) as seconds_since_date,
sum(executions_delta) as execs_since_date,
sum(buffer_gets_delta) as gets_since_date,
sum(ROWS_PROCESSED_delta) as gets_rows_date,
row_number() over (order by round(sum(elapsed_time_delta) / 1000000) desc) r
from dba_hist_snapshot natural
join dba_hist_sqlstat g
where begin_interval_time > =TO_DATE('02/APR/2019 07:50','DD-MON-YYYY HH24:MI')
AND END_INTERVAL_TIME<=TO_DATE('02/APR/2019 08:20','DD-MON-YYYY HH24:MI')
and parsing_schema_name = 'SCOTT'
group by sql_id, g.parsing_schema_name) sub
join dba_hist_sqltext txt on sub.sql_id = txt.sql_id
where r < 10
order by avg_query_time desc
 
================================== 
 TOP Elapsed Queries with Text :
 
select sub.sql_id,
txt.sql_text,
parsing_schema_name,
sub.seconds_since_date as TOTALELAPSEDTIME,
sub.execs_since_date as NUmberofExecs,
sub.gets_rows_date as RowsProcessed,
round(sub.seconds_since_date / (sub.execs_since_date + 0.01), 3) avg_query_time
from ( -- sub to sort before top N filter
select sql_id,
g.parsing_schema_name,
round(sum(ELAPSED_TIME_delta) / 1000000) as seconds_since_date,
sum(executions_delta) as execs_since_date,
sum(buffer_gets_delta) as gets_since_date,
sum(ROWS_PROCESSED_delta) as gets_rows_date,
row_number() over (order by round(sum(elapsed_time_delta) / 1000000) desc) r
from dba_hist_snapshot natural
join dba_hist_sqlstat g
where begin_interval_time > =TO_DATE('22/OCT/2019 10:40','DD-MON-YYYY HH24:MI')
AND END_INTERVAL_TIME<=TO_DATE('22/OCT/2019 14:00','DD-MON-YYYY HH24:MI')
and parsing_schema_name = 'SCOTT'
group by sql_id, g.parsing_schema_name) sub
join dba_hist_sqltext txt on sub.sql_id = txt.sql_id and SQL_TEXT like 'update%'
--where r < 50
order by avg_query_time desc
 
 =========================== 
 
These queries i have collected from KT EXPERTS:
 
https://www.ktexperts.com/category/oracle-dba/ 

Friday, 6 November 2020

oracle performace tunnning Basic

 when any users intimates us that

  1. This query is running slowly
  2. Daily, this query will retrieve data in 1 minute. But, today it is running since 2 hours.
  3. Load average is high
  4. CPU utilization of a process is high
  5. Users experiencing slowness

a.Get the top consuming PID’s . 

ps -eo pcpu,pid,user,args | sort -k 1 -r |head -10

b.  Now, you need to pick the SQL_ID mainly for proceeding further. You will be prompted for PID which you picked in above command.

set linesize 2000;
select s.sid,s.serial#, s.inst_id,p.spid, s.SQL_ID, t.SQL_TEXT, s.machine from gv$process p, gv$session s, gv$sqltext t where s.paddr = p.addr and p.spid=&processid and s.SQL_HASH_VALUE = t.HASH_VALUE;

 

c. 1. Using the sql_id, pick the tables involved in it. We are using explain plans.

select * from table(dbms_xplan.display_cursor(sql_id => ‘&SQL_ID’, format => ‘+ALLSTATS’));

c 2. Run below Oracle Provided script to get details. This is Sql Tuning Report. This report will give all the recommendations available. 

@?/rdbms/admin/sqltrpt.sql

Now, you will be having the tables list. Check when was latest time stamp of the table analyzed.

SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, TO_CHAR(LAST_ANALYZED, ‘MM/DD/YYYY HH24:MI:SS’) FROM DBA_TABLES WHERE TABLE_NAME =’&TABLE_NAME’;

 

=> If the stats are not up-to-date, we need to gather stats.

 

select table_name, subpartition_name, global_stats, last_analyzed, num_rows from dba_tab_subpartitions where table_name=’&Tablename’ and table_owner=’&owner’ order by 1, 2, 4 desc nulls last;

select TABLE_OWNER, TABLE_NAME, PARTITION_NAME, LAST_ANALYZED from DBA_TAB_PARTITIONS where TABLE_OWNER=’&owner’ and TABLE_NAME=’&Tablename’ order by LAST_ANALYZED;

 

If table has NO partitions and if stats need to be gathered, use this

EXEC dbms_stats.gather_table_stats(‘&Owner’,’&Table’,cascade=>TRUE);

If table has partitions, we can gather stats on whole table. But, its better to gather stats for recently created ten partitions, use below

begin
SYS.DBMS_STATS.gather_table_stats
(ownname => ‘&Table_Owner’,
tabname => ‘&Table_Name’,
PARTNAME => ‘&partition’,
estimate_percent => 0.001,
DEGREE => 24,
granularity => ‘PARTITION’,
CASCADE => TRUE,
stattab => NULL,
statid => NULL,
statown => NULL
);
END;
/

 

=================================================== 

Run SQL Tuning Adviosor for the sql_id

————————————–
Statement with SQL_ID captured is taking long time, we need to set best Execution Plan for the SQL_ID.
So, we need to submit to Oracle Tuning Advisor(sqltrpt.sql) and then check the FINDINGS SECTION for Findings and Recommendations for the SQL_ID.Recommendations from sqltrpt.sql will be providing the best Explain Plan. We can implement these profiles/index rebuild/… suggested after checking with the SME of the database.

Location: $ORACLE_HOME/rdbms/admin/sqltrpt.sql

$sqlplus “/as sysdba”

Query to see current running sqls
set pages 50000 lines 32767
col program format a40
col sql_text format a130
select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text from v$session b,v$sqlarea c
where b.sql_id=c.sql_id
/

Run SQL Tuning Advisor for the SQL_ID
SQL> @?/rdbms/admin/sqltrpt.sql

In case the recommendation is for creation of SQL PROFILE, sqltrpt.sql will provide the command too as below.

Command to Create and Implement SQL Profile in Oracle for the SQL_ID:
——————————————————————–

SQL> execute dbms_sqltune.accept_sql_profile(task_name => ‘<TASK_NAME>’,task_owner => ‘SYS’, replace => TRUE, FORCE_MATCH => TRUE);

If successful, you should see the following:
PL/SQL procedure successfully completed.

SQL> SELECT name, created, LAST_MODIFIED FROM dba_sql_profiles ORDER BY created DESC;

Command to Drop SQL Profile in Oracle for the SQL_ID:
—————————————————-

SQL> execute dbms_sqltune.drop_sql_profile(‘<SQL_PROFILE_NAME>’);

If successful, you should see the following:
PL/SQL procedure successfully completed.

SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC;

Command to Alter SQL Profile in Oracle for the SQL_ID:
—————————————————–

SQL> EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(‘<SQL_PROFILE_NAME>’,’STATUS’,’DISABLED’);

If successful, you should see the following:
PL/SQL procedure successfully completed.

SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC;

If you don’t know the name of the SQL Profile then use the below query
SQL> select NAME,SQL_TEXT from DBA_SQL_PROFILES where SQL_TEXT like ‘%SELECT%TABLE%NAME%’;

Query
—–
The SQL_ID is not stored with the profiles.
You can see if a statement is using a profile by querying v$sql where sql_profile is not null.

select sql_id, child_number, plan_hash_value plan_hash, sql_profile,
executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
sql_text
from v$sql s
where upper(sql_text) like upper(nvl(‘&sql_text’,sql_text))
and sql_text not like ‘%from v$sql where sql_text like nvl(%’
and sql_id like nvl(‘&sql_id’,sql_id)
and sql_profile like nvl(‘&sql_profile_name’,sql_profile)
and sql_profile is not null
order by 1, 2, 3

/

 I have used below blogs:

https://dba12c.wordpress.com/2015/04/30/query-is-running-slow-2/

https://dilipdba.blogspot.com/search/label/SQL%20to%20check%20Stale%20statistics

https://dba12c.wordpress.com/2017/04/24/oracle-local-registry-olr/