oratop utility for oracle database performance tuning

oratop utility for oracle database performance tuning 

[oracle@pri ~]$ ./oratop -h
oratop: Release 14.1.2
Usage:
         oratop [ [Options] [Logon] ]

         Logon:
                {username[/password][@connect_identifier] | / }
                [AS {SYSDBA|SYSOPER}]

                connect_identifier:
                     o Net Service Name, (TNS) or
                     o Easy Connect (host[:port]/[service_name])
         Options:
             -d : real-time (RT) wait events, section 3 (default is Cumulative)
             -k : FILE#:BLOCK#, section 4 lt is (EVENT/LATCH)
             -m : MODULE/ACTION, section 4 (default is USERNAME/PROGRAM)
             -s : SQL mode, section 4 (default is process mode)
             -c : database service mode (default is connect string)
             -f : detailed format, 132 columns (default: standard, 80 columns)
             -b : batch mode (default is text-based user interface)
             -n : maximum number of iterations (requires number)
             -i : interval delay, requires value in seconds (default: 5s)
             -v : oratop release version number
             -h : this help

[oracle@pri ~]$

ln -s /u01/app/oracle/product/18.0.0/db/lib/libclntshcore.so.18.1 /u01/app/oracle/product/18.0.0/db/lib/libclntshcore.so.12.1


[oracle@pri ~]$ ./oratop / as sysdba
./oratop: error while loading shared libraries: libclntsh.so.12.1: cannot open shared object file: No such file or directory
[oracle@pri ~]$


ln -s /u01/app/oracle/product/18.0.0/db/lib/libclntshcore.so.18.1 /u01/app/oracle/product/18.0.0/db/lib/libclntsh.so.12.1



[oracle@pri ~]$ chmod 777 oratop.RDBMS_11.2_LINUX_X64
[oracle@pri ~]$ ./oratop.RDBMS_11.2_LINUX_X64 / as sysdba

oratop: Release 14.1.2 Production on Fri Feb 15 18:32:24 2019
Copyright (c) 2011, Oracle.  All rights reserved.

Connecting ...
Oracle 18c - IND 18:32:27 up: 0.4h,   1 ins,    0 sn,   0 us, 1.7G mt, 10.0% db
ID %CPU LOAD %DCU   AAS  ASC  ASI  ASW  AST IOPS %FR   PGA UTPS UCPS SSRT  %DBT
 1    0    0    0     9    0    0    0    0    3  15  254M    0    0    0   100

EVENT (C)                        TOT WAITS   TIME(s)  AVG_MS  PCT    WAIT_CLASS
db file sequential read               6389        96    15.1   56      User I/O
DB CPU                                            36           21
db file scattered read                 360        15    43.3    9      User I/O
external table read                      1        15 15072.8    9      User I/O
control file parallel write            577         9    17.2    6    System I/O

ID   SID     SPID USR PROG S  PGA SQLID/BLOCKER OPN  E/T STA STE EVENT/*LA  W/T
 1    60     3836 SYS orat D 5.1M 7qj5jsdnpsn1a SEL    0 ACT CPU cpu runqu   8u

oratop - Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1)

How to identify the Master Node in RAC

How to identify the Master Node in RAC
============================================

In RAC only Masternode is responsible for take backup of OCR.

[grid@rac1 bin]$ ./oclumon manage -get MASTER

Master = rac1
[grid@rac1 bin]$


[root@rac1 bin]# ./ocrconfig -manualbackup

rac2     2019/02/02 22:49:23     +DATA:/rac/OCRBACKUP/backup_20190202_224923.ocr.327.999211765     70732493
[root@rac1 bin]#

[root@rac1 bin]# ./ocrconfig -showbackup

rac2     2018/12/12 20:09:04     +DATA:/rac/OCRBACKUP/backup00.ocr.283.994709331     70732493

rac2     2018/12/12 20:09:04     +DATA:/rac/OCRBACKUP/day.ocr.284.994709345     70732493

rac2     2018/12/12 20:09:04     +DATA:/rac/OCRBACKUP/week.ocr.285.994709347     70732493

rac2     2019/02/02 22:49:23     +DATA:/rac/OCRBACKUP/backup_20190202_224923.ocr.327.999211765     70732493
[root@rac1 bin]#
[root@rac1 bin]# ./ocrconfig -showbackup auto

rac2     2018/12/12 20:09:04     +DATA:/rac/OCRBACKUP/backup00.ocr.283.994709331     70732493

rac2     2018/12/12 20:09:04     +DATA:/rac/OCRBACKUP/day.ocr.284.994709345     70732493

rac2     2018/12/12 20:09:04     +DATA:/rac/OCRBACKUP/week.ocr.285.994709347     70732493
[root@rac1 bin]#
[root@rac1 bin]# ./ocrconfig -showbackup manual

rac2     2019/02/02 22:49:23     +DATA:/rac/OCRBACKUP/backup_20190202_224923.ocr.327.999211765     70732493
[root@rac1 bin]#



[grid@rac1 trace]$ cat ocssd.trc |grep 'master node'
2019-01-21 12:39:42.772 :    CSSD:2787034880: clssgmCMReconfig: GM master node for incarnation 443540379 is node rac1, number 1, with birth incarnation 443540379, the old master is 65535 and new master is 1
2019-01-21 12:39:42.976 :    CSSD:2787034880: clssgmCMReconfig: reconfiguration successful, incarnation 443540379 with 2 nodes, local node number 1, master node rac1, number 1
2019-01-21 12:39:49.250 :    CSSD:2788611840: clssgmCMReconfig: GM master node for incarnation 443540380 is node rac1, number 1, with birth incarnation 443540379, the old master is 1 and new master is 1
2019-01-21 12:39:49.255 :    CSSD:2788611840: clssgmCMReconfig: reconfiguration successful, incarnation 443540380 with 1 nodes, local node number 1, master node rac1, number 1
2019-01-21 12:48:05.570 :    CSSD:832739072: clssgmCMReconfig: GM master node for incarnation 443540883 is node rac1, number 1, with birth incarnation 443540883, the old master is 65535 and new master is 1
2019-01-21 12:48:05.571 :    CSSD:832739072: clssgmCMReconfig: reconfiguration successful, incarnation 443540883 with 1 nodes, local node number 1, master node rac1, number 1
2019-01-21 13:13:57.075 :    CSSD:834316032: clssgmCMReconfig: GM master node for incarnation 443540884 is node rac1, number 1, with birth incarnation 443540883, the old master is 1 and new master is 1
2019-01-21 13:13:59.038 :    CSSD:834316032: clssgmCMReconfig: reconfiguration successful, incarnation 443540884 with 2 nodes, local node number 1, master node rac1, number 1
2019-01-22 23:46:52.347 :    CSSD:2292303616: clssgmCMReconfig: GM master node for incarnation 443666809 is node rac1, number 1, with birth incarnation 443666809, the old master is 65535 and new master is 1
2019-01-22 23:46:52.350 :    CSSD:2292303616: clssgmCMReconfig: reconfiguration successful, incarnation 443666809 with 1 nodes, local node number 1, master node rac1, number 1
2019-02-02 21:20:12.712 :    CSSD:4233729792: clssgmCMReconfig: GM master node for incarnation 444608391 is node <null>, number 2, with birth incarnation 444608390, the old master is 65535 and new master is 2
2019-02-02 21:20:13.044 :    CSSD:4233729792: clssgmCMReconfig: reconfiguration successful, incarnation 444608391 with 2 nodes, local node number 1, master node rac2, number 2
[grid@rac1 trace]$



[grid@rac1 trace]$ cat crsd.trc |grep 'master'
2019-02-02 21:21:34.370 :  OCRMAS:1811937024: th_master_check_hashids_helper: Comparing device hash IDs between local and master.
2019-02-02 21:21:34.370 :  OCRMAS:1811937024: th_master_check_hashids_helper: Local dev (987031272, 1028247821, 0, 0, 0)
2019-02-02 21:21:34.370 :  OCRMAS:1811937024: th_master_check_hashids_helper: Master dev (987031272, 1028247821, 0, 0, 0)
2019-02-02 21:21:34.370 :  OCRMAS:1811937024: th_connect_master: Using GIPC type to connect
2019-02-02 21:21:34.370 :  OCRMAS:1811937024: th_connect_master:10: Master host name [rac2]
2019-02-02 21:21:34.370 :  OCRMAS:1811937024: proath_connect_master: Attempting to connect to master at address [rac2:1494-d8b5-fb40-8a6a]
2019-02-02 21:21:34.826 :  OCRMAS:1811937024: proath_master: SUCCESSFULLY CONNECTED TO THE MASTER
2019-02-02 21:21:34.826 :  OCRMAS:1811937024: th_master: NEW OCR MASTER IS 2
2019-02-02 21:21:34.829 :  OCRSRV:2758700032: th_reg_master_change: Master change callback registered. Client:[1]
2019-02-02 21:21:34.829 :  OCRSRV:2758700032: th_reg_master_change: Notified master change
2019-02-02 21:21:34.829 :  OCRAPI:2758700032: a_reg_master_change: Registered master change callback. flags:[4]
2019-02-02 21:21:34.842 : CRSMAIN:2758700032:  Registering for mastership change events...
2019-02-02 21:21:34.842 :  OCRSRV:2758700032: th_reg_master_change: Master change callback registered. Client:[0]
2019-02-02 21:21:34.842 :  OCRSRV:2758700032: th_reg_master_change: Notified master change
2019-02-02 21:21:34.843 :  OCRAPI:2758700032: a_reg_master_change: Registered master change callback. flags:[2]
2019-02-02 21:24:24.651 :UiServer:1595901696: {1:60436:2} Master change notification has received. New master: 2
[grid@rac1 trace]$
[grid@rac1 trace]$

[grid@rac1 trace]$ pwd
/u01/app/grid/product/18.0.0.0/grid_base/diag/crs/rac1/crs/trace
[grid@rac1 trace]$

Oracle Cluster Registry (OCR) and Oracle Local Registry (OLR) - OCRCHECK : Oracle Cluster Registry Check utility in RAC

Oracle Cluster Registry (OCR) and Oracle Local Registry (OLR)

ocrcheck  command in RAC
==============================



[root@rac1 bin]# ./ocrcheck -help
Name:
        ocrcheck - Displays health of Oracle Cluster/Local Registry.

Synopsis:
        ocrcheck [-config | -backupfile <backupfilename>] [-details] [-local]

  -config       Displays the configured locations of the Oracle Cluster Registry.
                This can be used with the -local option to display the configured
                location of the Oracle Local Registry
  -details      Displays detailed configuration information.
  -local        The operation will be performed on the Oracle Local Registry.
  -backupfile <backupfilename>  The operation will be performed on the backup file.

Notes:
        * This command for Oracle Cluster Registry is not supported from a Leaf node.

[root@rac1 bin]#

OCR (Oracle Cluster Registry) information
================================================

The Oracle Clusterware (Oracle Grid Infrastructure GI stack in 11gR2) uses OCR to manage resources and node membership information. 
It contains the following information shared across the nodes in the cluster

ASM diskgroups, volumes, filesystems, and instances
RAC databases and instances information
SCAN listeners and local listeners
SCAN VIPs and Local VIPs
Nodes and node applications

User defined resources


[root@rac1 bin]# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     491684
         Used space (kbytes)      :      84796
         Available space (kbytes) :     406888
         ID                       : 1718087688
         Device/File Name         :      +DATA
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded

[root@rac1 bin]#




[root@rac1 bin]#
[root@rac1 bin]# ./ocrcheck -config
Oracle Cluster Registry configuration is :
         Device/File Name         :      +DATA
[root@rac1 bin]# ./ocrcheck -details
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     491684
         Used space (kbytes)      :      84796
         Available space (kbytes) :     406888
         ID                       : 1718087688
         Device/File Name         : +DATA/rac/OCRFILE/registry.255.993952649
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded

[root@rac1 bin]#


[root@rac1 bin]# cat /etc/oracle/ocr.loc
#Device/file +DATA getting replaced by device +DATA/rac/OCRFILE/registry.255.993952649
ocrconfig_loc=+DATA/rac/OCRFILE/registry.255.993952649
local_only=false[root@rac1 bin]#

[root@rac1 bin]#


OLR(Oracle Local Registry) Information 
=========================================
Its contains node-specific information required by OHASD . Every node has its own dedicated OLR file.(not shared between the nodes)


[root@rac1 bin]# ./ocrcheck -local
Status of Oracle Local Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     491684
         Used space (kbytes)      :      83164
         Available space (kbytes) :     408520
         ID                       :  161118435
         Device/File Name         : /u01/app/grid/product/18.0.0.0/grid/cdata/rac1.olr
                                    Device/File integrity check succeeded

         Local registry integrity check succeeded

         Logical corruption check succeeded

[root@rac1 bin]#


[root@rac1 bin]#
[root@rac1 bin]# ./ocrcheck  -local -details
Status of Oracle Local Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     491684
         Used space (kbytes)      :      83164
         Available space (kbytes) :     408520
         ID                       :  161118435
         Device/File Name         : /u01/app/grid/product/18.0.0.0/grid/cdata/rac1.olr
                                    Device/File integrity check succeeded

         Local registry integrity check succeeded

         Logical corruption check succeeded

[root@rac1 bin]#

[root@rac1 bin]# cat /etc/oracle/olr.loc
olrconfig_loc=/u01/app/grid/product/18.0.0.0/grid/cdata/rac1.olr
crs_home=/u01/app/grid/product/18.0.0.0/grid
orplus_config=FALSE

[root@rac1 bin]#