OPatch, Mainting patches in Oracle Database

OPatch is an Oracle utility that assists us to apply interim patches to Oracle’s rdbms software amd clusterware. We can find opatch utility in $ORACLE_HOME/Opatch directory. 

For example, let we apply patch number 11114547  to our 11.2.0.4 database.

Applying Patch:

1- Backup Oracle Home directory.

       $ tar -cf ora11g.tar ora11g

2- Download patch file p11114547_10205_Linux-x86-64.zip via metalink. And copy it to database server.

3- Unzip the patch file.

       $ unzip p11114547_10205_Linux-x86-64.zip

4- Apply patch with opatch utility.

       $ cd 11114547

       $ ORACLE_HOME/OPatch/opatch apply

To see list of applied patches :

        $ORACLE_HOME/OPatch/opatch lsinventory

For example:

        $ORACLE_HOME/OPatch/opatch lsinventory

   Invoking OPatch 11.2.0.4.9

Oracle Interim Patch Installer version 11.2.0.4.9

Copyright (c) 2011, Oracle Corporation. All rights reserved.

Oracle Home : /oracle/ora10g

Central Inventory : /oracle/oraInventory

from : /etc/oraInst.loc

OPatch version : 11.2.0.4.9

OUI version : 11.2.0.5.0

OUI location : /oracle/ora10g/oui

Log file location : /oracle/ora10g/cfgtoollogs/opatch/opatch2017-09-14_12-13-12PM.log

Patch history file: /oracle/ora10g/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /oracle/ora10g/cfgtoollogs/opatch/lsinv/lsinventory2017-09-14_12-13-12PM.log

Installed Top-level Products (3):

Oracle Database 10g 10.2.0.1.0

Oracle Database 10g Release 2 Patch Set 3 10.2.0.4.0

Oracle Database 10g Release 2 Patch Set 4 10.2.0.5.0

There are 3 products installed in this Oracle Home.

Interim patches (2) :

Patch 8943287 : applied on Fri Oct 21 20:39:46 EEST 2011

Unique Patch ID: 12722995

Created on 23 Aug 2010, 11:45:16 hrs PST8PDT

Bugs fixed:

8943287

Rollback applied patch:

Sometimes a patch is applied to the system may need to take back because of its effect. In this case, rollback is performed as follows.

$ORACLE_HOME/OPatch/opatch rollback -id 11114547

Gather diagnostic Information using TFA in Oracle Database

Oracle Trace File Analyzer (TFA) provides a number of diagnostic tools in a single bundle, making it easy to gather diagnostic information about the Oracle database and clusterware, which in turn helps with problem resolution when dealing with Oracle Support.

If possible we should install Oracle Trace File Analyzer as root. This will give you the highest capabilities. If Oracle Trace File Analyzer is already installed, reinstalling will perform an upgrade to the existing location. If it is not already installed, the recommended location is /opt/oracle.tfa

To install as root:

Download the appropriate Oracle Trace File Analyzer zip, copy to required machine and unzip.

Run the installTFA command: $ ./installTFA

To install as an ORACLE_HOME owner use the –extractto option. This tells Oracle Trace File Analyzer where to install to. The installer includes a JVM, but if you want to use one already installed use the –javahome option to point to it.

./installTFA -extractto -javahome

If we do not want to use ssh, we can install on each host using a local install. Then we use tfactl syncnodes to generate and deploy the relevant SSL certificates.

Now the time to collect TFA.

Please run TFA which collects all clusterware logs from all nodes (needs to be done as root from node1's gi_home): 

TFA from each node: 

==> TFA Collector- The Preferred Tool for Automatic or ADHOC Diagnostic Gathering Across All Cluster Nodes ( Doc ID 1513912.1 ) 

Examples: 

/bin/tfactl diagcollect -all -from "" -to "" 

/bin/tfactl diagcollect -all -since [2d|8h] 

/bin/tfactl diagcollect -for "" <--- 12hrs="" after="" and="" before="" collect="" font="" given="" nbsp="" the="" time="" will="">

Generate Incident Report in Oracle Database

The Automatic Diagnostics Repository (ADR) is a hierarchical file-based repository for handling diagnostic information. 

Directory structure is as:

$ADR_BASE/diag/rdbms/{DB-name}/{SID}/alert
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/cdump
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/hm
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/incident
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/trace
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/{others}

To generate incident report quickly, we can follow the below steps:

adrci> show problem
adrci> show incident

adrci> show incident -mode detail -p "incident_id=incident_no" 
adrci> ips create package problem <problem_id>correlate all
adrci> ips generate package  in "/tmp"

Oracle RAC Scan Listener - (2)

To get scan name details:

These commands will have to executed by grid user.

bash-4.3$ srvctl config scan SCAN name: SMDCMBDBL, Network: 1/XX.XX.10.0/255.255.255.0/nxge7 SCAN VIP name: scan1, IP: /xx.xx.xx.106/10.54.10.106 SCAN VIP name: scan2, IP: /xx.xx.xx.108/10.54.10.108 SCAN VIP name: scan3, IP: /xx.xx.xx.107/10.54.10.107

bash-4.3$ srvctl config scan_listener SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521 SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521 SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521

To get the status of the scan listener:

bash-4.3$ srvctl status  scan_listener SCAN Listener LISTENER_SCAN1 is enabled SCAN listener LISTENER_SCAN1 is running on node kdbpprp2 SCAN Listener LISTENER_SCAN2 is enabled SCAN listener LISTENER_SCAN2 is running on node kdbpprp1 SCAN Listener LISTENER_SCAN3 is enabled SCAN listener LISTENER_SCAN3 is running on node kdbpprp1

The above command shows the hostname on which the 3 different scan listeners are running.

DNS performs the initial load balancing while scan name is called. As 3 IPs are assigned with the
scan name initially though that can be extended later. While clients send request to DNS, DNS
provided three different IP on different request. I.e while we are trying to fetch the DNS with
scan name, we get IP addresses order in different order.

paul@DBBL-ITDD-PAUL:~$ nslookup kdbpprp Server: 127.0.1.1 Address: 127.0.1.1#53 Name: kdbpprp.dutchbanglabank.com Address: XX.XX.XXX.162 Name: kdbpprp.dutchbanglabank.com Address: XX.XX.XXX.161 Name: kdbpprp.dutchbanglabank.com Address: XX.XX.XXX.163 paul@DBBL-ITDD-PAUL:~$ nslookup kdbpprp Server: 127.0.1.1 Address: 127.0.1.1#53 Name: kdbpprp.dutchbanglabank.com Address: XX.XX.XXX.161 Name: kdbpprp.dutchbanglabank.com Address: XX.XX.XXX.163 Name: kdbpprp.dutchbanglabank.com Address: XX.XX.XXX.162 paul@DBBL-ITDD-PAUL:~$ nslookup kdbpprp Server: 127.0.1.1 Address: 127.0.1.1#53 Name: kdbpprp.dutchbanglabank.com Address: XX.XX.XXX.163 Name: kdbpprp.dutchbanglabank.com Address: XX.XX.XXX.162 Name: kdbpprp.dutchbanglabank.com Address: XX.XX.XXX.161 paul@DBBL-ITDD-PAUL:~$

Command to get status for the specific listener scan.

bash-4.3$ lsnrctl  status LISTENER_SCAN2 LSNRCTL for HPUX: Version 11.2.0.4.0 - Production on 29-MAR-2018 14:50:55 Copyright (c) 1991, 2013, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))) STATUS of the LISTENER ------------------------ Alias                     LISTENER_SCAN2 Version                   TNSLSNR for HPUX: Version 11.2.0.4.0 - Production Start Date                24-OCT-2017 17:57:55 Uptime                    155 days 20 hr. 52 min. 59 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Listener Parameter File   /orafs/app/11.2.0/grid/network/admin/listener.ora Listener Log File         /orafs/app/11.2.0/grid/log/diag/tnslsnr/kdbpprp1/listener_scan2/alert/log.xml Listening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN2)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX.XX.XXX.163)(PORT=1521))) Services Summary... Service "agentdb" has 2 instance(s).  Instance "agentdb11", status READY, has 1 handler(s) for this service...  Instance "agentdb12", status READY, has 1 handler(s) for this service... Service "agentdb1XDB" has 2 instance(s).  Instance "agentdb11", status READY, has 1 handler(s) for this service...  Instance "agentdb12", status READY, has 1 handler(s) for this service... The command completed successfully

Node VIP: Each Database node in Real Application cluster environment has one node IP and one Node VIP
address, The main difference between these two is Node VIP can move to any other system in case
if current owning system is down but Node IP can't do that. When ever a new connection request is made
the SCAN listener listening on a SCAN IP address and the SCAN port is contracted on a client's behalf.
Because all services on the cluster are registered with the SCAN listener, the SCAN listener replies with the
address of the local listener as Node VIP address on the least-loaded node (Each scan listener keeps updated
cluster load statistics) and connection is routed to that node.

Local Listener on the Database server is registered with Node VIP and Node IP address.

bash-4.3$ lsnrctl status listener LSNRCTL for HPUX: Version 11.2.0.4.0 - Production on 29-MAR-2018 14:59:48 Copyright (c) 1991, 2013, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for HPUX: Version 11.2.0.4.0 - Production Start Date                24-OCT-2017 18:06:15 Uptime                    155 days 20 hr. 53 min. 33 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Listener Parameter File   /orafs/app/11.2.0/grid/network/admin/listener.ora Listener Log File         /orafs/app/grid/diag/tnslsnr/kdbpprp1/listener/alert/log.xml Listening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX.XX.XXX.61)(PORT=1521)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX.XX.XXX.60)(PORT=1521))) Services Summary... Service "+ASM" has 1 instance(s).  Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "agentdb" has 1 instance(s).  Instance "agentdb11", status READY, has 1 handler(s) for this service... Service "agentdb1XDB" has 1 instance(s).  Instance "agentdb11", status READY, has 1 handler(s) for this service... The command completed successfully

Local_Listener: This is a database parameter which is used to provide detail of local listener running on
database node itself. In Real Application Cluster environment this has Node VIP address as value.

When a new connection request is made my SCAN Listener, This address is returned to the SCAN Listener and
then connection is made to database local listener. The difference between SCAN Listener and Local Listener
is SCAN listener runs corosponding to SCAN VIP's while Local Listener runs with Node VIP or Node IP address.
SCAN Listener can move to another database node in case of node failure but local Listener doesn't have this
kind of behavior.

SQL> show parameter local_listener NAME                                 TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener                       string (ADDRESS=(PROTOCOL=TCP)(HOST=XX.XX.XXX.60)(PORT=1521))

Remote_Listener: Each database instance has this parameter set to SCAN NAME of the cluster environment.
SCAN NAME in turn has one or more IP address further called SCAN VIP and each SCAN VIP has SCAN Listener
running on it, so finally each database instance register itself with all SCAN Listeners running across the
clusterware.

SQL> show parameter remote_listener NAME                                 TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_listener                      string kdbpprp:1521

The solo purpose of remote_listener parameter is to register each database instance with all SCAN Listeners
in RAC. This provide information on what services are being provided by the instance,the current load, and a
recommendation on how many incoming connections should be directed to the instance.

Stopping scan listener:

srvctl stop scan_listener [-scannumber ordinal_number] [-force] Example: srvctl stop scan_listener -scannumber 1

Relocate scan listener:

bash-4.3$ srvctl status scan SCAN VIP scan1 is enabled SCAN VIP scan1 is running on node kdbpbrp2 SCAN VIP scan2 is enabled SCAN VIP scan2 is running on node kdbpbrp1 SCAN VIP scan3 is enabled SCAN VIP scan3 is running on node kdbpbrp1 srvctl relocate scan -i -n   Example: srvctl relocate scan -i 3 -n kdbpbrp2 bash-4.3$ srvctl status scan SCAN VIP scan1 is enabled SCAN VIP scan1 is running on node kdbpbrp2 SCAN VIP scan2 is enabled SCAN VIP scan2 is running on node kdbpbrp1 SCAN VIP scan3 is enabled SCAN VIP scan3 is running on node kdbpbrp2

Scan Listener of Oracle RAC

SCAN: (Single Client Access Name) -- It allows clients to access whole cluster/grid through one name, rather specifying all VIP addresses individually. It is an automatic load balancing tool that uses a relatively primitive least-recently-loaded algorithm. 

Basically SCAN Listeners are introduced to create another connection handler on top of existing node listeners to overcome the failover and load balancing issues till 11gR1 architecture for RAC. Till 11gR1 Node listeners were defined on VIPs to facilitate NACK (Negative Acknowledgement) to resolve the issue of TCP timeouts.

SCAN Requirement: To be able to get SCAN Functionality, We need some service through which one name is resolved to multiple IPs/hosts. This is what we often call "DNS", Domain name service or if you don't have DNS in your environment then you can use Oracle's "GNS (Grid Naming Service)"

SCAN Implementation/Configuration: In Our DNS We defined a ScanName and assigned three Virtual IPs e.g (OraDbScan=X.X.X.7, X.X.X.8 and X.X.X.9), Now when we do nslookup OraDbScan from our client it will give us list of three IPs specified as above and important thing, every time you do nslookup it will give you the IP addresses in round robin, So every request to resolve the OraDbScan name will give different IP every time. One kind of load balancing at first place to reach to SCAN Listener so requests will be distributed on available SCAN Listeners.

SCAN Usage: During Oracle Grid Installation, Specify mentioned IPs, Each VIP Listener on each node will register itself will Every SCAN Listener on whatever node the SCAN Listener is running.

Number of RAC Nodes: 4 

Physical IPs: 4, One for each node 

Private IPs: at least 4, One for each node (You can have two for each for redundancy) 

Virtual IPs: 4, One assigned to each node [Word "assigned" is important] 

Number of VIP Listeners: 4, Each Bound to certain Node [word "bound"/"assigned"] 

Number of SCAN Listeners: 3[ Still if you feel that default 3 SCAN listeners are not enough for your environment then you have an option to add few more scan listeners.], Grid will decide which nodes SCAN Listeners will be Allocated 

Client Connection String: Mentioning the name of SCAN "OraDbScan"

Connection Establishment: Client sends request to DNS using SCAN Name "OraDbScan", DNS will return one of three IPs, Client Sends request on that IP to the SCAN Listener, the SCAN Listener will route the request to one of the VIP Listeners among 4 VIP listeners in this case because each VIP listener has already registered itself with every SCAN Listener. Finally direct connection is established between client and one of the VIP Listeners. 

You see flexibility, adding/removing as many nodes in Grid/RAC doesn't require any change.