EasyReliableDBA

Software Training and dumps for Exam are available on www.easyreliable.com To download dumps. Please visit Links and Just pay $5/Rs300 http://easyreliable.com/easydumps/ This blog is Oracle DBA blog which cover Oracle RAC(10g,11g ,12c and 19c), Dataguard, RMAN,Backup&Recovery,Troubleshooting and Performance Tuning and other topics like Oracle Cloud,MongoDB,Golden Gate and Exadata.Please subscribe below to get update on my blog.We also provide Online SoftwareTraining and DBA Consulting

Thursday, 23 September 2021

Azure Expressrout-connect azure network with onpromise

Extend an on-premises network using VPN

Bastion

Azure Stack

Virtual Machines

Virtual Network

VPN Gateway

This reference architecture shows how to extend a network from on premises or from Azure Stack into an Azure virtual network, using a site-to-site virtual private network (VPN). Traffic flows between the on-premises network and Azure through an IPSec VPN tunnel or through the Azure Stack multitenant VPN gateway.

Hybrid network spanning on-premises and Azure infrastructures

Download a Visio file of this architecture.

Reference deployment

This deployment creates two resource groups; the first holds a mock on-premises network, the second a set of hub and spoke networks. The mock on-premises network and the hub network are connected using Azure Virtual Network gateways to form a site-to-site connection. This configuration is very similar to how you would connect your on-premises datacenter to Azure.

This deployment can take up to 45 minutes to complete. The recommended deployment method is using the portal option found below.

Use the following button to deploy the reference using the Azure portal.

Once the deployment has been completed, verify site-to-site connectivity by looking at the newly created connection resources. While in the Azure portal, search for 'connections' and note that the status of each connection.

Screenshot showing the status of connections.

The IIS instance found in the spoke network can be accessed from the virtual machine located in the mock on-prem network. Create a connection to the virtual machine using the included Azure Bastion host, open a web browser, and navigate to the address of the application's network load balancer.

For detailed information and additional deployment options, see the ARM Templates used to deploy this solution.

Secure Hybrid Network

Architecture

The architecture consists of the following components.

On-premises network. A private local-area network running within an organization.
Azure Stack. A network environment on an Azure Stack tenant subscription, running within an organization. The Azure Stack VPN gateway sends encrypted traffic across a public connection to virtual IP (VIP) addresses and includes the following components:
- Gateway subnet. A special subnet required to deploy the VPN Gateway on Azure Stack.
- Local network gateway. Indicates the target IP of the VPN gateway in Azure, as well as the address space of the Azure virtual network.
- Site-to-site VPN tunnel. The connection type (IPSec) and the key shared with the Azure VPN Gateway to encrypt traffic.
VPN appliance. A device or service that provides external connectivity to the on-premises network. The VPN appliance may be a hardware device, or it can be a software solution such as the Routing and Remote Access Service (RRAS) in Windows Server 2012. For a list of supported VPN appliances and information on configuring them to connect to an Azure VPN gateway, see the instructions for the selected device in the article About VPN devices for Site-to-Site VPN Gateway connections.
Virtual network. The cloud application and the components for the Azure VPN gateway reside in the same virtual network.
Azure VPN gateway. The VPN gateway service enables you to connect the virtual network to the on-premises network through a VPN appliance or to connect to Azure Stack through a site-to-site VPN tunnel.. The VPN gateway includes the following elements:
- Virtual network gateway. A resource that provides a virtual VPN appliance for the virtual network. It is responsible for routing traffic from the on-premises network to the virtual network.
- Local network gateway. An abstraction of the on-premises VPN appliance. Network traffic from the cloud application to the on-premises network is routed through this gateway.
- Connection. The connection has properties that specify the connection type (IPSec) and the key shared with the on-premises VPN appliance to encrypt traffic.
- Gateway subnet. The virtual network gateway is held in its own subnet, which is subject to various requirements, described in the Recommendations section below.
Cloud application. The application hosted in Azure. It might include multiple tiers, with multiple subnets connected through Azure load balancers. For more information about the application infrastructure, see Running Windows VM workloads and Running Linux VM workloads.
Internal load balancer. Network traffic from the VPN gateway is routed to the cloud application through an internal load balancer. The load balancer is located in the front-end subnet of the application.
Bastion. Azure Bastion allows you to log into VMs in the virtual network through SSH or remote desktop protocol (RDP) without exposing the VMs directly to the internet. If you lose connectivity through the VPN, you can still use Bastion to manage the VMs in the virtual network.

Friday, 10 September 2021

Rollback conflict patch and apply PSU Patch in Oracle 12C RAC Environment Step by Step

In this document, In Two Node RAC enviroment while applying PSU patch on RAC Database Home, we encounter with conflict patch, we follow below steps

A) rollback conflict patch manually

B) Apply PSU using opatchauto on oracle home

C) apply overlay patch manually

Patch Overview

Types of Oracle Patches

Oracle regularly makes patches available to upgrade features, enhance security, or fix problems with supported software. The major types of patches are:

· Interim patches - contain a single bug fix or a collection of bug fixes provided as required

· Interim patches for security bug fixes - contain customer-specific security bug fixes

· Diagnostic patches - intended to help diagnose or verify a fix or a collection of bug fixes

· Bundle Patch Updates (BPUs) - a cumulative collection of fixes for a specific product or component

· Patch Set Updates (PSUs) - a cumulative collection of high impact, low risk, and proven fixes for a specific product or component and Security Patch Updates

· Security Patch Updates (SPU) - a cumulative collection of security bug fixes. SPUs were formerly known as Critical Patch Updates (CPU).

· System Patch - contains several sub-patches in a format that can be used by OPatchAuto.

· Merge Label Request (MLR) - a merge of two or more fixes. MLR creation requires a label for the new set of merged code and a Patch Set

OPatch consists of patching utilities that help ensure your Oracle software stays current and secure. The utilities are:

· OPatch: A Java-based utility that enables the application and rollback of patches to Oracle software.

· OPatchauto: A patch orchestration tool that generates patching instructions specific to your target configuration and then uses OPatch to perform the patching operations without user intervention. Specifically, OPatchAuto can:

1. Perform pre-patch checks.

2. Apply the patch

3. Start and stop the running servers.

4. Perform post-patch checks.

5. Roll back patches when patch deinstallation is required.

· OPatchAuto automates patch application to the Grid Infrastructure (GI) cluster, by applying patches to both the GI and the managed Oracle Real Application Cluster (RAC) homes.

Prerequisite
============

1) Download Patch From Oracle Support
2) Check Free Space in Clusterware Home file system (approx free space 15-22 GB and /tmp file system
¨ Check ACFS File Systems if it is available on node ,Please unmount it before starting patching clusterware or stopping clusterware
3) Check clusterware and database component status
ps -ef|grep pmon
ps -ef|grep tns
crsctl check crs
crsctl status resource -t
crsctl query crs activeversion
crsctl query crs releaseversion
crsctl query crs softwareversion
./crsctl check crs
ps -ef|grep d.bin

4) Check opatch version and recommended to have latest opatch and download it from oracle support (6880880) and unzip it in Grid Home after renaming Old Opatch folder
We must use the OPatch utility version 12.2.0.1.17 or later to apply this patch for all platforms
5) run opatch lsinventory from grid home to check consistency and corruption of inventory
6) unzip downloaded patch from Grid user
7) run analyze command using opatchauto from root user after setting proper path, we must carefully give grid home path

9) apply patch from opatchauto on grid home from Root user
10)verify patch details on Grid Home
11)OPatch maintains logs for apply, rollback, and lsinventory operations.

OPatch Log files are located in

ORACLE_HOME/cfgtoollogs/opatch.

Each log file is tagged with the time stamp of the operation.

Each time you run OPatch, a new log file is created.

OPatch maintains an index of processed commands-and log files in the opatch_history.txt file

RAC Database Patching

There is two node RAC Servers and, on these server, One Test Database is running. While running Pre-check analyzed command on Database. It failed with conflict patch

Summary Step

Step 1: Roll back conflicting patch(es)

20243268 : EM QUERY WITH SQL_ID 4RQ83FNXTF39U PERFORMS POORLY ON ORACLE 12C RELATIVE TO 11G

Step 2: Apply Candidate Patch

Patch 32495126: GRID INFRASTRUCTURE PATCH SET UPDATE 12.1.0.2.210420

README - https://updates.oracle.com/Orion/Services/download?type=readme&aru=24181982

Step 3: Apply remaining additional overlay patch(es)

Patch Name: 20243268

Abstract: EM QUERY WITH SQL_ID 4RQ83FNXTF39U PERFORMS POORLY ON ORACLE 12C RELATIVE TO 11G

Download patch from :

Patch Link: https://updates.oracle.com/download/20243268.html

Select release : 12.1.0.2.210420

Select platform : Linux_x86-64

Note : For rollback, installation, and post-installation instructions refer to README of the respective patches.

Reference: Database Patch conflict resolution ( Doc ID 1321267.1 )

Details step by step

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

Environment Details

Database Running on Two Nodes

[root@RACNODE2 OPatch]#

[root@RACNODE2 OPatch]# ps -ef|grep pmon

root 10266 25030 0 09:58 pts/0 00:00:00 grep pmon

oracrs 10664 1 0 Jul19 ? 00:02:06 asm_pmon_+ASM2

oracrs 12980 1 0 Jul19 ? 00:01:24 mdb_pmon_-MGMTDB

TESTDB 13492 1 0 Jul19 ? 00:02:50 ora_pmon_TESTDB2

[root@RACNODE2 OPatch]#

[root@RACNODE1 staging]# ps -ef|grep pmon

root 5837 21329 0 10:00 pts/0 00:00:00 grep pmon

oracrs 10300 1 0 Jul19 ? 00:02:42 asm_pmon_+ASM1

TESTDB 11926 1 0 Jul19 ? 00:03:15 ora_pmon_TESTDB1

[root@RACNODE1 staging]#

1) Change patch owner if it is not owned by grid owner and unzip patch and provide 775 owners (precaution)

chown -R oracrs:oinstall 2020_patch

chown oracrs:oinstall p32495126_122010_AIX64-5L.zip

unzip p32495126_122010_AIX64-5L.zip
chmod -R 775 30116802

[TESTDB@RACNODE2 OPatch]$ ls -ltr /staging/gridapr2021patch/32495126

total 28

drwxrwxr-x 5 oracrs dba 4096 Apr 17 09:52 31978884

drwxrwxr-x 4 oracrs dba 4096 Apr 17 09:53 26983807

drwxrwxr-x 29 oracrs dba 4096 Apr 17 09:55 32328635

drwxrwxr-x 2 oracrs dba 4096 Apr 17 09:56 automation

drwxrwxr-x 5 oracrs dba 4096 Apr 17 09:56 31704029

-rwxrwxr-x 1 oracrs dba 0 Apr 17 09:57 README.txt

-rwxrwxr-x 1 oracrs dba 0 Apr 17 09:57 README.html

-rwxrwxr-x 1 oracrs dba 4991 Apr 17 10:57 bundle.xml

[TESTDB@RACNODE2 OPatch]$ exit

2) Stop Database on Node1 and take backup of oracle home

srvctl stop instance -d TESTDB -i TESTDB1 -o immediate

Take Backup of Database Home from instance owner

Backup home

cd /u01/oracle/TESTDB/product/12.1.0.2

tar -cvf /staging/TESTDB_home_03augnod2.tar .

du -sg /staging/TESTDB_home_03aug.tar

Backup for inventory

cd /u02/oracle/oraInventory

tar -cvf /staging/grid_inventory3aug.tar .

./opatch lsinventory >/staging/inventory_3001.log

Start Database instance

srvctl start instance -d TESTDB -i TESTDB1

3) Pre-Check on Node 1

[root@RACNODE1 OPatch]# export PATH=$PATH:/u01/oracle/TESTDB/product/12.1.0.2/OPatch

cd /u01/oracle/TESTDB/product/12.1.0.2/OPatch

./opatch version

df -hP /u01/oracle/TESTDB/product/12.1.0.2/OPatch/

df -hP /tmp

ls -ltr /staging/gridapr2021patch/32495126

4) run analyze command from root user as below

[root@RACNODE2 OPatch]# /u01/oracle/TESTDB/product/12.1.0.2/OPatch/opatchauto apply /staging/gridapr2021patch/32495126 -analyze -oh /u01/oracle/TESTDB/product/12.1.0.2

OPatchauto session is initiated at Tue Aug 3 09:19:59 2021

System initialization log file is /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatchautodb/systemconfig2021-08-03_09-20-12AM.log.

Session log file is /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatchauto/opatchauto2021-08-03_09-20-43AM.log

The id for this session is UQNX

Executing OPatch prereq operations to verify patch applicability on home /u01/oracle/TESTDB/product/12.1.0.2

Patch applicability verified successfully on home /u01/oracle/TESTDB/product/12.1.0.2

Executing patch validation checks on home /u01/oracle/TESTDB/product/12.1.0.2

Patch validation checks successfully completed on home /u01/oracle/TESTDB/product/12.1.0.2

Verifying SQL patch applicability on home /u01/oracle/TESTDB/product/12.1.0.2

SQL patch applicability verified successfully on home /u01/oracle/TESTDB/product/12.1.0.2

OPatchAuto successful.

--------------------------Summary--------------------------------

Analysis for applying patches has failed:

Host:RACNODE2

RAC Home:/u01/oracle/TESTDB/product/12.1.0.2

Version:12.1.0.2.0

Analysis for patches has failed.

==Following patches FAILED in analysis for apply:

Patch: /staging/gridapr2021patch/32495126/32328635

Log: /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatchauto/core/opatch/opatch2021-08-03_09-21-11AM_1.log

Reason: Failed during Analysis: CheckConflictAgainstOracleHome Failed, [ Prerequisite Status: FAILED, Prerequisite output:

Summary of Conflict Analysis:

There are no patches that can be applied now.

Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :

20243268, 32328635

Following patches are not required, as they are subset of the patches in Oracle Home or subset of the patches in the given list :

31704029

Whole composite patch Conflicts/Supers ...

==Following patches were SKIPPED:

Patch: /staging/gridapr2021patch/32495126/26983807

Reason: This patch is not applicable to this specified target type - "rac_database"

Patch: /staging/gridapr2021patch/32495126/31978884

Reason: This patch is not applicable to this specified target type - "rac_database"

Patch: /staging/gridapr2021patch/32495126/31704029

Log: /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatchauto/core/opatch/opatch2021-08-03_09-21-11AM_1.log

Reason: /staging/gridapr2021patch/32495126/31704029 is not required to be applied to oracle home /u01/oracle/TESTDB/product/12.1.0.2

OPATCHAUTO-72053: Analysis for the patches failed.

OPATCHAUTO-72053: Command execution failed.

OPATCHAUTO-72053: Please check the summary for more details.

OPatchauto session completed at Tue Aug 3 09:22:54 2021

Time taken to complete the session 2 minutes, 56 seconds

[root@RACNODE2 OPatch]#

Implementation Steps

Stop Database instance and rollback conflict patch as below

[TESTDB@RACNODE1 OPatch]$ srvctl stop instance -d TESTDB -i TESTDB2 -o immediate

[TESTDB@RACNODE1 OPatch]$

[TESTDB@RACNODE1 OPatch]$ ps -ef|grep pmon

oracrs 10300 1 0 Jul19 ? 00:02:44 asm_pmon_+ASM1

TESTDB 21428 15455 0 14:47 pts/0 00:00:00 grep pmon

[TESTDB@RACNODE1 OPatch]$ ./opatch rollback -id 20243268

Oracle Interim Patch Installer version 12.2.0.1.23

Oracle Home : /u01/oracle/TESTDB/product/12.1.0.2

Central Inventory : /u02/oracle/oraInventory

from : /u01/oracle/TESTDB/product/12.1.0.2/oraInst.loc

OPatch version : 12.2.0.1.23

OUI version : 12.1.0.2.0

Log file location : /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatch/opatch2021-08-03_14-47-30PM_1.log

Patches will be rolled back in the following order:

20243268

The following patch(es) will be rolled back: 20243268

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u01/oracle/TESTDB/product/12.1.0.2')

Is the local system ready for patching? [y|n]

User Responded with: Y

Rolling back patch 20243268...

RollbackSession rolling back interim patch '20243268' from OH '/u01/oracle/TESTDB/product/12.1.0.2'

Patching component oracle.rdbms, 12.1.0.2.0...

RollbackSession removing interim patch '20243268' from inventory

Log file location: /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatch/opatch2021-08-03_14-47-30PM_1.log

OPatch succeeded.

After rollback conflict patch start database and run analyze command as part of pre-check

a) stop Database on one node

[TESTDB@RACNODE1 OPatch]$

[TESTDB@RACNODE1 OPatch]$ srvctl start instance -d TESTDB -i TESTDB1

[TESTDB@RACNODE1 OPatch]$

logout

[root@RACNODE1 april202_patch]# ps -ef|grep pmon

oracrs 10300 1 0 Jul19 ? 00:02:44 asm_pmon_+ASM1

TESTDB 22265 1 0 14:48 ? 00:00:00 ora_pmon_TESTDB1

root 23112 8467 0 14:49 pts/0 00:00:00 grep pmon

[root@RACNODE1 april202_patch]#

[root@RACNODE1 april202_patch]# cd /u01/oracle/TESTDB/product/12.1.0.2/OPatch

b) check patch version

[root@RACNODE1 OPatch]# ./opatch version

OPatch Version: 12.2.0.1.23

OPatch succeeded.

[root@RACNODE1 OPatch]# df -hP /u01/oracle/TESTDB/product/12.1.0.2/OPatch/

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/vg_app-lv_u01 50G 22G 26G 46% /u01

[root@RACNODE1 OPatch]# df -hP /tmp

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/vg_root-lv_tmp 2.0G 14M 1.8G 1% /tmp

[root@RACNODE1 OPatch]# ls -ltr /staging/gridapr2021patch/32495126

total 28

drwxrwxr-x 5 oracrs dba 4096 Apr 17 09:52 31978884

drwxrwxr-x 4 oracrs dba 4096 Apr 17 09:53 26983807

drwxrwxr-x 29 oracrs dba 4096 Apr 17 09:55 32328635

drwxrwxr-x 2 oracrs dba 4096 Apr 17 09:56 automation

drwxrwxr-x 5 oracrs dba 4096 Apr 17 09:56 31704029

-rwxrwxr-x 1 oracrs dba 0 Apr 17 09:57 README.txt

-rwxrwxr-x 1 oracrs dba 0 Apr 17 09:57 README.html

-rwxrwxr-x 1 oracrs dba 4991 Apr 17 10:57 bundle.xml

c) run analyze command

[root@RACNODE1 OPatch]# /u01/oracle/TESTDB/product/12.1.0.2/OPatch/opatchauto apply /staging/gridapr2021patch/32495126 -analyze -oh /u01/oracle/TESTDB/product/12.1.0.2

OPatchauto session is initiated at Tue Aug 3 14:50:58 2021

System initialization log file is /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatchautodb/systemconfig2021-08-03_02-51-10PM.log.

Session log file is /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatchauto/opatchauto2021-08-03_02-51-38PM.log

The id for this session is ASTJ

Executing OPatch prereq operations to verify patch applicability on home /u01/oracle/TESTDB/product/12.1.0.2

Patch applicability verified successfully on home /u01/oracle/TESTDB/product/12.1.0.2

Executing patch validation checks on home /u01/oracle/TESTDB/product/12.1.0.2

Patch validation checks successfully completed on home /u01/oracle/TESTDB/product/12.1.0.2

Verifying SQL patch applicability on home /u01/oracle/TESTDB/product/12.1.0.2

SQL patch applicability verified successfully on home /u01/oracle/TESTDB/product/12.1.0.2

OPatchAuto successful.

---------------------------Summary-------------------------------

Analysis for applying patches has completed successfully:

Host:RACNODE1

RAC Home:/u01/oracle/TESTDB/product/12.1.0.2

Version:12.1.0.2.0

==Following patches were SKIPPED:

Patch: /staging/gridapr2021patch/32495126/26983807

Reason: This patch is not applicable to this specified target type - "rac_database"

Patch: /staging/gridapr2021patch/32495126/31978884

Reason: This patch is not applicable to this specified target type - "rac_database"

Patch: /staging/gridapr2021patch/32495126/31704029

Log: /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatchauto/core/opatch/opatch2021-08-03_14-52-10PM_1.log

Reason: /staging/gridapr2021patch/32495126/31704029 is not required to be applied to oracle home /u01/oracle/TESTDB/product/12.1.0.2

==Following patches were SUCCESSFULLY analyzed to be applied:

Patch: /staging/gridapr2021patch/32495126/32328635

Log: /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatchauto/core/opatch/opatch2021-08-03_14-52-10PM_1.log

OPatchauto session completed at Tue Aug 3 14:53:48 2021

Time taken to complete the session 2 minutes, 51 seconds

Since We have rollback conflict patch. Now Analyze command/precheck is not showing any error. So, we can apply Patch on Database Home

[root@RACNODE1 OPatch]# id

uid=0(root) gid=0(root) groups=0(root)

[root@RACNODE1 OPatch]# ps -ef|grep pmon

oracrs 10300 1 0 Jul19 ? 00:02:44 asm_pmon_+ASM1

TESTDB 22265 1 0 14:48 ? 00:00:00 ora_pmon_TESTDB1

root 28126 8467 0 14:54 pts/0 00:00:00 grep pmon

[root@RACNODE1 OPatch]#

while database is up and running, we can apply PSU on oracle RAC home. Database will stop and start automatically as below command

/u01/oracle/TESTDB/product/12.1.0.2/OPatch/opatchauto apply /staging/gridapr2021patch/32495126 -oh /u01/oracle/TESTDB/product/12.1.0.2

OPatchauto session is initiated at Tue Aug 3 14:54:30 2021

System initialization log file is /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatchautodb/systemconfig2021-08-03_02-54-39PM.log.

Session log file is /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatchauto/opatchauto2021-08-03_02-55-03PM.log

The id for this session is 79I2

Executing OPatch prereq operations to verify patch applicability on home /u01/oracle/TESTDB/product/12.1.0.2

Patch applicability verified successfully on home /u01/oracle/TESTDB/product/12.1.0.2

Executing patch validation checks on home /u01/oracle/TESTDB/product/12.1.0.2

Patch validation checks successfully completed on home /u01/oracle/TESTDB/product/12.1.0.2

Verifying SQL patch applicability on home /u01/oracle/TESTDB/product/12.1.0.2

SQL patch applicability verified successfully on home /u01/oracle/TESTDB/product/12.1.0.2

Preparing to bring down database service on home /u01/oracle/TESTDB/product/12.1.0.2

Successfully prepared home /u01/oracle/TESTDB/product/12.1.0.2 to bring down database service

Bringing down database service on home /u01/oracle/TESTDB/product/12.1.0.2

Following database(s) and/or service(s) are stopped and will be restarted later during the session: TESTDB

Database service successfully brought down on home /u01/oracle/TESTDB/product/12.1.0.2

Performing prepatch operation on home /u01/oracle/TESTDB/product/12.1.0.2

Perpatch operation completed successfully on home /u01/oracle/TESTDB/product/12.1.0.2

Start applying binary patch on home /u01/oracle/TESTDB/product/12.1.0.2

Binary patch applied successfully on home /u01/oracle/TESTDB/product/12.1.0.2

Performing postpatch operation on home /u01/oracle/TESTDB/product/12.1.0.2

Postpatch operation completed successfully on home /u01/oracle/TESTDB/product/12.1.0.2

Starting database service on home /u01/oracle/TESTDB/product/12.1.0.2

Database service successfully started on home /u01/oracle/TESTDB/product/12.1.0.2

Preparing home /u01/oracle/TESTDB/product/12.1.0.2 after database service restarted

No step execution required.........

Trying to apply SQL patch on home /u01/oracle/TESTDB/product/12.1.0.2

SQL patch applied successfully on home /u01/oracle/TESTDB/product/12.1.0.2

OPatchAuto successful.

---------------------------Summary--------------------------------

Patching is completed successfully. Please find the summary as follows:

Host:RACNODE1

RAC Home:/u01/oracle/TESTDB/product/12.1.0.2

Version:12.1.0.2.0

Summary:

==Following patches were SKIPPED:

Patch: /staging/gridapr2021patch/32495126/26983807

Reason: This patch is not applicable to this specified target type - "rac_database"

Patch: /staging/gridapr2021patch/32495126/31978884

Reason: This patch is not applicable to this specified target type - "rac_database"

Patch: /staging/gridapr2021patch/32495126/31704029

Log: /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatchauto/core/opatch/opatch2021-08-03_14-55-35PM_1.log

Reason: /staging/gridapr2021patch/32495126/31704029 is not required to be applied to oracle home /u01/oracle/TESTDB/product/12.1.0.2

==Following patches were SUCCESSFULLY applied:

Patch: /staging/gridapr2021patch/32495126/32328635

Log: /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatchauto/core/opatch/opatch2021-08-03_15-07-27PM_1.log

OPatchauto session completed at Tue Aug 3 15:11:34 2021

Time taken to complete the session 17 minutes, 4 seconds

Now PSU has applied successfully, and we need to apply overlay patch

which is provided by oracle support

Stop database and Apply remaining additional overlay patch(es)

a) stop database on one node

[root@RACNODE1 OPatch]#

[root@RACNODE1 OPatch]# ps -ef|grep pmon

TESTDB 7563 1 0 15:09 ? 00:00:00 ora_pmon_TESTDB1

oracrs 10300 1 0 Jul19 ? 00:02:44 asm_pmon_+ASM1

root 11750 8467 0 15:12 pts/0 00:00:00 grep pmon

[root@RACNODE1 OPatch]# su - TESTDB

[TESTDB@RACNODE1 ~]$ su - TESTDB

Password:

[TESTDB@RACNODE1 ~]$ id

uid=500(TESTDB) gid=200(dba) groups=200(dba),212(asmadmin),213(asmdba)

[TESTDB@RACNODE1 ~]$

[TESTDB@RACNODE1 ~]$ srvctl stop instance -d TESTDB -i TESTDB1 -o immediate

[TESTDB@RACNODE1 ~]$

[TESTDB@RACNODE1 ~]$ id

uid=500(TESTDB) gid=200(dba) groups=200(dba),212(asmadmin),213(asmdba)

[TESTDB@RACNODE1 ~]$ . oraenv

ORACLE_SID = [TESTDB1] ? TESTDB

The Oracle base remains unchanged with value /u01/oracle/TESTDB

[TESTDB@RACNODE1 ~]$ export ORACLE_SID=TESTDB1

[TESTDB@RACNODE1 ~]$ cd $ORACLE_HOME

[TESTDB@RACNODE1 12.1.0.2]$ cd OPatch

[TESTDB@RACNODE1 OPatch]$ pwd

/u01/oracle/TESTDB/product/12.1.0.2/OPatch

b) set environment and go in patch location and apply patch

[TESTDB@RACNODE1 OPatch]$ export PATH=$PATH:/u01/oracle/TESTDB/product/12.1.0.2/OPatch

[TESTDB@RACNODE1 staging]$ cd april202_patch

[TESTDB@RACNODE1 april202_patch]$ ls -ltr

total 117248

-rwxrwxrwx 1 oracrs dba 118408624 Aug 14 2020 p6880880_122010_Linux-x86-64.zip

-rwxrwxrwx 1 TESTDB dba 821509 Aug 15 2020 p20243268_12102180417_Linux-x86-64.zip

drwxrwxr-x 4 TESTDB dba 4096 Mar 17 21:33 20243268

-rwxrwxr-x 1 TESTDB dba 821546 Aug 3 14:26 p20243268_12102210420_Linux-x86-64.zip

[TESTDB@RACNODE1 april202_patch]$ cd 20243268

[TESTDB@RACNODE1 20243268]$ opatch apply

Oracle Interim Patch Installer version 12.2.0.1.23

Oracle Home : /u01/oracle/TESTDB/product/12.1.0.2

Central Inventory : /u02/oracle/oraInventory

from : /u01/oracle/TESTDB/product/12.1.0.2/oraInst.loc

OPatch version : 12.2.0.1.23

OUI version : 12.1.0.2.0

Log file location : /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatch/opatch2021-08-03_15-19-41PM_1.log

Verifying environment and performing prerequisite checks...

OPatch continues with these patches: 20243268

Do you want to proceed? [y|n]

User Responded with: Y

All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u01/oracle/TESTDB/product/12.1.0.2')

Is the local system ready for patching? [y|n]

User Responded with: Y

Backing up files...

Applying interim patch '20243268' to OH '/u01/oracle/TESTDB/product/12.1.0.2

Patching component oracle.rdbms, 12.1.0.2.0...

Patch 20243268 successfully applied.

Log file location: /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatch/opatch2021-08-03_15-19-41PM_1.log

OPatch succeeded.

[TESTDB@RACNODE1 20243268]$

c) Start Database on Node1

[TESTDB@RACNODE1 20243268]$

[TESTDB@RACNODE1 20243268]$ srvctl start instance -d TESTDB -i TESTDB1

[TESTDB@RACNODE1 20243268]$ ps -ef|grep pmon

oracrs 10300 1 0 Jul19 ? 00:02:45 asm_pmon_+ASM1

TESTDB 15678 1 0 15:22 ? 00:00:00 ora_pmon_TESTDB1

TESTDB 16375 11835 0 15:22 pts/0 00:00:00 grep pmon

[TESTDB@RACNODE1 20243268]$

Now we have rollback conflict patch, apply PSU and apply overlay patch on one now

============Database patch apply on other node ======================

1) Patch location

drwxrwxrwx 4 oracrs dba 4096 Jun 18 13:39 gridJan2021patch

drwxrwxrwx 3 oracrs dba 4096 Jul 13 07:37 gridapr2021patch

-rw-r--r-- 1 root root 2734080 Jul 13 18:19 grid_inventory12jul.tar

-rw-r--r-- 1 TESTDB dba 11663810560 Aug 3 10:45 TESTDB_home_03augnod2.tar

-rw-r--r-- 1 root root 2754560 Aug 3 10:46 grid_inventory3aug.tar

drwxrwxrwx 2 oracrs dba 4096 Aug 3 14:27 april202_patch

[root@RACNODE2 staging]# cd april202_patch

[root@RACNODE2 april202_patch]# ls -ltr

total 3482344

-rwxr-xr-x 1 oracrs dba 3445855633 Aug 13 2020 p30805421_121020_Linux-x86-64.zip

-rwxr-xr-x 1 oracrs dba 118408624 Aug 13 2020 p6880880_122010_Linux-x86-64.zip

-rwxr-xr-x 1 TESTDB dba 821509 Aug 17 2020 p20243268_12102180417_Linux-x86-64.zip

-rw------- 1 eibm994 domain users 821546 Aug 3 14:27 p20243268_12102210420_Linux-x86-64.zip

[root@RACNODE2 april202_patch]# chown TESTDB:dba p20243268_12102210420_Linux-x86-64.zip

[root@RACNODE2 april202_patch]# ls -ltr

total 3482344

-rwxr-xr-x 1 oracrs dba 3445855633 Aug 13 2020 p30805421_121020_Linux-x86-64.zip

-rwxr-xr-x 1 oracrs dba 118408624 Aug 13 2020 p6880880_122010_Linux-x86-64.zip

-rwxr-xr-x 1 TESTDB dba 821509 Aug 17 2020 p20243268_12102180417_Linux-x86-64.zip

-rw------- 1 TESTDB dba 821546 Aug 3 14:27 p20243268_12102210420_Linux-x86-64.zip

[root@RACNODE2 april202_patch]# chmod 775 p20243268_12102210420_Linux-x86-64.zip

[root@RACNODE2 april202_patch]#

[root@RACNODE2 april202_patch]# ls -ltr

total 3482344

-rwxr-xr-x 1 oracrs dba 3445855633 Aug 13 2020 p30805421_121020_Linux-x86-64.zip

-rwxr-xr-x 1 oracrs dba 118408624 Aug 13 2020 p6880880_122010_Linux-x86-64.zip

-rwxr-xr-x 1 TESTDB dba 821509 Aug 17 2020 p20243268_12102180417_Linux-x86-64.zip

-rwxrwxr-x 1 TESTDB dba 821546 Aug 3 14:27 p20243268_12102210420_Linux-x86-64.zip

2) unzip from database instance owner

-rwxr-xr-x 1 oracrs dba 3445855633 Aug 13 2020 p30805421_121020_Linux-x86-64.zip

-rwxr-xr-x 1 oracrs dba 118408624 Aug 13 2020 p6880880_122010_Linux-x86-64.zip

-rwxr-xr-x 1 TESTDB dba 821509 Aug 17 2020 p20243268_12102180417_Linux-x86-64.zip

-rwxrwxr-x 1 TESTDB dba 821546 Aug 3 14:27 p20243268_12102210420_Linux-x86-64.zip

[TESTDB@RACNODE2 april202_patch]$

[TESTDB@RACNODE2 april202_patch]$ unzip p20243268_12102210420_Linux-x86-64.zip

Archive: p20243268_12102210420_Linux-x86-64.zip

creating: 20243268/

creating: 20243268/files/

creating: 20243268/files/lib/

creating: 20243268/files/lib/libserver12.a/

inflating: 20243268/files/lib/libserver12.a/qksht.o

inflating: 20243268/files/lib/libserver12.a/qksds.o

inflating: 20243268/files/lib/libserver12.a/kkocfb.o

inflating: 20243268/files/lib/libserver12.a/qksfm.o

inflating: 20243268/files/lib/libserver12.a/kkoads.o

inflating: 20243268/files/lib/libserver12.a/qkscr.o

inflating: 20243268/files/lib/libserver12.a/qksbg.o

inflating: 20243268/files/lib/libserver12.a/kkeds.o

inflating: 20243268/files/lib/libserver12.a/qsmmix.o

inflating: 20243268/files/lib/libserver12.a/kkoat.o

inflating: 20243268/files/lib/libserver12.a/kko.o

inflating: 20243268/files/lib/libserver12.a/kke.o

inflating: 20243268/README.txt

creating: 20243268/etc/

creating: 20243268/etc/config/

inflating: 20243268/etc/config/inventory.xml

inflating: 20243268/etc/config/actions.xml

[TESTDB@RACNODE2 april202_patch]$ ls -ltr

total 3482348

-rwxr-xr-x 1 oracrs dba 3445855633 Aug 13 2020 p30805421_121020_Linux-x86-64.zip

-rwxr-xr-x 1 oracrs dba 118408624 Aug 13 2020 p6880880_122010_Linux-x86-64.zip

-rwxr-xr-x 1 TESTDB dba 821509 Aug 17 2020 p20243268_12102180417_Linux-x86-64.zip

drwxrwxr-x 4 TESTDB dba 4096 Mar 17 21:33 20243268

-rwxrwxr-x 1 TESTDB dba 821546 Aug 3 14:27 p20243268_12102210420_Linux-x86-64.zip

[TESTDB@RACNODE2 april202_patch]$ cd 20243268

[TESTDB@RACNODE2 20243268]$ ps -ef|grep pmon

oracrs 10664 1 0 Jul19 ? 00:02:08 asm_pmon_+ASM2

oracrs 12980 1 0 Jul19 ? 00:01:25 mdb_pmon_-MGMTDB

TESTDB 19390 18639 0 16:10 pts/0 00:00:00 grep pmon

3) check Database and stop database on one node

[root@RACNODE2 april202_patch]#

[root@RACNODE2 april202_patch]# ps -ef|grep pmon

TESTDB 2231 1 0 10:46 ? 00:00:02 ora_pmon_TESTDB2

oracrs 10664 1 0 Jul19 ? 00:02:08 asm_pmon_+ASM2

oracrs 12980 1 0 Jul19 ? 00:01:25 mdb_pmon_-MGMTDB

root 18896 23855 0 15:23 pts/0 00:00:00 grep pmon

[root@RACNODE2 april202_patch]#

[root@RACNODE2 april202_patch]# su - TESTDB

[TESTDB@RACNODE2 ~]$ . oraenv

ORACLE_SID = [TESTDB2] ? TESTDB

The Oracle base remains unchanged with value /u01/oracle/TESTDB

[TESTDB@RACNODE2 ~]$ export ORACLE_SID=TESTDB2

[TESTDB@RACNODE2 ~]$

[TESTDB@RACNODE2 ~]$ srvctl stop instance -d TESTDB -i TESTDB2 -o immediate

[TESTDB@RACNODE2 ~]$ ps -ef|grep pmon

oracrs 10664 1 0 Jul19 ? 00:02:08 asm_pmon_+ASM2

oracrs 12980 1 0 Jul19 ? 00:01:25 mdb_pmon_-MGMTDB

TESTDB 20535 19161 0 15:26 pts/0 00:00:00 grep pmon

[TESTDB@RACNODE2 ~]$ cd $ORACLE_HOME

[TESTDB@RACNODE2 12.1.0.2]$ cd OPatch

[TESTDB@RACNODE2 OPatch]$ ./opatch version

OPatch Version: 12.2.0.1.23

OPatch succeeded.

4) Roll back conflict Patch on Database from database instance owner

[TESTDB@RACNODE2 OPatch]$ ./opatch rollback -id 20243268

Oracle Interim Patch Installer version 12.2.0.1.23

Oracle Home : /u01/oracle/TESTDB/product/12.1.0.2

Central Inventory : /u02/oracle/oraInventory

from : /u01/oracle/TESTDB/product/12.1.0.2/oraInst.loc

OPatch version : 12.2.0.1.23

OUI version : 12.1.0.2.0

Log file location : /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatch/opatch2021-08-03_15-27-13PM_1.log

Patches will be rolled back in the following order:

20243268

The following patch(es) will be rolled back: 20243268

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u01/oracle/TESTDB/product/12.1.0.2')

Is the local system ready for patching? [y|n]

User Responded with: Y

Rolling back patch 20243268...

RollbackSession rolling back interim patch '20243268' from OH '/u01/oracle/TESTDB/product/12.1.0.2'

Patching component oracle.rdbms, 12.1.0.2.0...

RollbackSession removing interim patch '20243268' from inventory

Log file location: /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatch/opatch2021-08-03_15-27-13PM_1.log

OPatch succeeded.

5) start database after patch rollback

[TESTDB@RACNODE2 OPatch]$ srvctl start instance -d TESTDB -i TESTDB2

[TESTDB@RACNODE2 OPatch]$ ps -ef|grep pmon

oracrs 10664 1 0 Jul19 ? 00:02:08 asm_pmon_+ASM2

oracrs 12980 1 0 Jul19 ? 00:01:25 mdb_pmon_-MGMTDB

TESTDB 21758 1 0 15:28 ? 00:00:00 ora_pmon_TESTDB2

TESTDB 22485 19161 0 15:29 pts/0 00:00:00 grep pmon

[TESTDB@RACNODE2 OPatch]$ cd /u01/oracle/TESTDB/product/12.1.0.2/OPatch

[TESTDB@RACNODE2 OPatch]$ ./opatch version

OPatch Version: 12.2.0.1.23

OPatch succeeded.

[TESTDB@RACNODE2 OPatch]$ df -hP /u01/oracle/TESTDB/product/12.1.0.2/OPatch/

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/vg_app-lv_u01 50G 20G 28G 42% /u01

[TESTDB@RACNODE2 OPatch]$ ls -ltr /staging/gridapr2021patch/32495126

total 28

drwxrwxr-x 5 oracrs dba 4096 Apr 17 09:52 31978884

drwxrwxr-x 4 oracrs dba 4096 Apr 17 09:53 26983807

drwxrwxr-x 29 oracrs dba 4096 Apr 17 09:55 32328635

drwxrwxr-x 2 oracrs dba 4096 Apr 17 09:56 automation

drwxrwxr-x 5 oracrs dba 4096 Apr 17 09:56 31704029

-rwxrwxr-x 1 oracrs dba 0 Apr 17 09:57 README.txt

-rwxrwxr-x 1 oracrs dba 0 Apr 17 09:57 README.html

-rwxrwxr-x 1 oracrs dba 4991 Apr 17 10:57 bundle.xml

[TESTDB@RACNODE2 OPatch]$ exit

logout

[root@RACNODE2 april202_patch]# cd /u01/oracle/TESTDB/product/12.1.0.2/OPatch

[root@RACNODE2 OPatch]# export PATH=$PATH:/u01/oracle/TESTDB/product/12.1.0.2/OPatch

[root@RACNODE2 OPatch]#

6) Run Analyze command as part of pre-check to apply PSU

[root@RACNODE2 OPatch]# /u01/oracle/TESTDB/product/12.1.0.2/OPatch/opatchauto apply /staging/gridapr2021patch/32495126 -analyze -oh /u01/oracle/TESTDB/product/12.1.0.2

OPatchauto session is initiated at Tue Aug 3 15:33:18 2021

System initialization log file is /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatchautodb/systemconfig2021-08-03_03-33-27PM.log.

Session log file is /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatchauto/opatchauto2021-08-03_03-33-49PM.log

The id for this session is 93W1

Executing OPatch prereq operations to verify patch applicability on home /u01/oracle/TESTDB/product/12.1.0.2

Patch applicability verified successfully on home /u01/oracle/TESTDB/product/12.1.0.2

Executing patch validation checks on home /u01/oracle/TESTDB/product/12.1.0.2

Patch validation checks successfully completed on home /u01/oracle/TESTDB/product/12.1.0.2

Verifying SQL patch applicability on home /u01/oracle/TESTDB/product/12.1.0.2

SQL patch applicability verified successfully on home /u01/oracle/TESTDB/product/12.1.0.2

OPatchAuto successful.

-------------------------Summary--------------------------------

Analysis for applying patches has completed successfully:

Host:RACNODE2

RAC Home:/u01/oracle/TESTDB/product/12.1.0.2

Version:12.1.0.2.0

==Following patches were SKIPPED:

Patch: /staging/gridapr2021patch/32495126/26983807

Reason: This patch is not applicable to this specified target type - "rac_database"

Patch: /staging/gridapr2021patch/32495126/31978884

Reason: This patch is not applicable to this specified target type - "rac_database"

Patch: /staging/gridapr2021patch/32495126/31704029

Log: /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatchauto/core/opatch/opatch2021-08-03_15-34-19PM_1.log

Reason: /staging/gridapr2021patch/32495126/31704029 is not required to be applied to oracle home /u01/oracle/TESTDB/product/12.1.0.2

==Following patches were SUCCESSFULLY analyzed to be applied:

Patch: /staging/gridapr2021patch/32495126/32328635

Log: /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatchauto/core/opatch/opatch2021-08-03_15-34-19PM_1.log

OPatchauto session completed at Tue Aug 3 15:35:53 2021

Time taken to complete the session 2 minutes, 36 seconds

[root@RACNODE2 OPatch]#

Implementation

7)Apply PSU patch on Database Home using opatchauto

[root@RACNODE2 OPatch]# /u01/oracle/TESTDB/product/12.1.0.2/OPatch/opatchauto apply /staging/gridapr2021patch/32495126 -oh /u01/oracle/TESTDB/product/12.1.0.2

OPatchauto session is initiated at Tue Aug 3 15:36:46 2021

System initialization log file is /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatchautodb/systemconfig2021-08-03_03-36-54PM.log.

Session log file is /u01/oracle/TESTDB/product/12.1.0.2/cfgtoollogs/opatchauto/opatchauto2021-08-03_03-37-15PM.log

The id for this session is 9VI5