RAC Patching and Upgradation

Patching and Upgrading Oracle Database :

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

Patching and upgrading an Oracle Database are essential activities for maintaining a secure, stable,

and high-performing database environment. Here’s a detailed breakdown of both processes with

practical scenarios.

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

PATCHING IN RAC :

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

omparison Table

Feature OPatch OPatchAuto

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

Automation Manual Automated

Target Environments Standalone or minimal setups Complex setups (RAC, Grid)

Service Handling Manual (stop/start required) Automatic

Ease of Use Requires manual steps Simplifies patching

Scope Single Oracle Home Multi-node environments

Prerequisite Checks Manual or guided Fully automated

1. Patching Oracle Database

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

Oracle provides regular patches to fix bugs, address security vulnerabilities, and enhance database

features. These patches are applied using tools like opatch and opatchauto.

Types of Patches :

1. One-off Patches: Target specific issues.

2. Patch Set Updates (PSUs): Includes critical fixes and security updates.

3. Release Updates (RUs): Includes functional enhancements, fixes, and security patches.

4. Release Update Revisions (RURs): Address regressions in previous RUs.

5. Critical Patch Updates (CPUs): Quarterly updates focusing on security vulnerabilities.

Patching Using OPatch

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

OPatch is a command-line utility used to apply one-off patches or bundle patches.

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

DB HOME PATCHING :

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

1)Stop DB home as root user and Listener

ORACLE_HOME=/u01/app/oracle/product/19/db_2

/u01/app/oracle/product/19/db_2 /bin/srvctl stop home -o /u01/app/oracle/product/19/db_2 -s /u01/pacthes/db.state -n db1

2)Download the patches from Oracle support and unzip in folder Grant permissions on patch dir as root

chown -R oracle:oinstall /u01/pacthes

3)As a oracle prechecks

su - oracle

/u01/app/oracle/product/19/db_2/Opatch/opatch version

cd /u01/app/oracle/product/19/db_2

mv OPatch OPatch_bkp

unzip p6880880_230000_Linux-x86-64.zip -d /u01/app/oracle/product/19/db_2

/u01/app/19c/grid/Opatch/opatch version

/u01/app/19c/grid/Opatch/opatch lsinv | grep applied

/u01/app/19c/grid/Opatch/opatch lsinv | grep desc

/u01/app/19c/grid/Opatch/opatch lspatches -oh /u01/app/oracle/product/19/db_2

conflict check :

/u01/app/oracle/product/19/db_2/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/patches/36352207/36352352 -oh /u01/app/oracle/product/19/db_2

system space checks :

/u01/app/oracle/product/19/db_2/OPatch/opatch prereq CheckSystemSpace -phBaseDir /u01/patches/36352207/36352352 -oh /u01/app/oracle/product/19/db_2

4)Apply patch

/u01/app/oracle/product/19/db_2/OPatch/opatch apply -oh /u01/app/oracle/product/19/db_2 -local -silent /u01/patches/36352207/36352352

5)Post verification

/u01/app/19c/grid/OPatch/opatch version

/u01/app/19c/grid/OPatch/opatch lsinv | grep applied

/u01/app/19c/grid/OPatch/opatch lsinv | grep desc

/u01/app/19c/grid/OPatch/opatch lspatches -oh /u01/app/oracle/product/19/db_2

6)start ORACLE_HOME as root

ORACLE_HOME=/u01/app/oracle/product/19/db_2 /u01/app/oracle/product/19/db_2 /bin/srvctl start home -o /u01/app/oracle/product/19/db_2 -s /u01/pacthes/db.state -n db1

ROLLBACK :

/u01/app/oracle/product/19/db_2/OPatch/opatch rollback -id 36352352

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

DB PATCHING :

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

1)Validate components and objects :

column comp_name format a40

column version format a12

column status format a15

select comp_name,version,status from dba_registry;

column owner format a15

column object_name format a40

column object_type format a20

select owner,object_name,object_type where status =" INVALID" and owner

like '%SYS%' order by object_type,owner,object_name;

fixup :

@?/rdbms/admin/utlprp.sql

2) Run the database patch

cd $ORACLE_HOME/opatch

./datapatch -verbose

(It will update the database history and metadata with new patch )

3)Verfication postpatch steps :

set pagesize 1000;

set linesize 1000;

col status for a10;

col action_time for a10;

col description for a55;

select patch_id,status,action_time,description from dba_registry_sqlpatch;

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

GI HOME PATCHING:

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

1)stop GI home and listener - database as root user

/u01/app/19c/grid/bin/crsctl stop crs

2)Unlock the GI home as root user (To change all ownership of grid_home root to oracle - oinstall)

/u01/app/19c/grid/crs/install/rootcrs.sh -prepatch

Benefits of -prepatch :

Shuts Down Oracle Clusterware Stack: Prepares the Node for Patching: Isolates the Node: Leaves Other Nodes Running:

Controlled Downtime: Ensures minimal downtime by allowing the patch to be applied on one node while the rest of the cluster is operational.

Safety: Prevents patching-related disruptions by properly shutting down and isolating the node.

Cluster Stability: Maintains cluster integrity by ensuring that cluster communication is not disrupted.

Rolling Patching: It is used for rolling patching, where one node is patched at a time to minimize downtime.

3)Grant permissions patch directory as root

chown -R oracle:oinstall /u01/patches

4)As grid user prechecks

su - oracle

cd /u01/app/19c/grid

mv OPatch OPatch_bkp

unzip p6880880_230000_Linux-x86-64.zip -d /u01/app/19c/grid/ (opatch version upgradation)

/u01/app/19c/grid/Opatch/opatch version

/u01/app/19c/grid/Opatch/opatch lsinv | grep applied

/u01/app/19c/grid/Opatch/opatch lsinv | grep desc

/u01/app/19c/grid/Opatch/opatch lspatches -oh /u01/app/19c/grid

conflict checks :

/u01/app/19c/grid/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/patches/36352207/36115667 -oh /u01/app/19c/grid

System space checks :

/u01/app/19c/grid/OPatch/opatch prereq CheckSystemSpace -phBaseDir /u01/patches/36352207/36115667 -oh /u01/app/19c/grid

5) Apply patches :

/u01/app/19c/grid/OPatch/opatch apply -oh /u01/app/19c/grid -local -silent /u01/patches/36352207/36115667

6)Post verification :

/u01/app/19c/grid/OPatch/opatch version

/u01/app/19c/grid/OPatch/opatch lsinv | grep applied

/u01/app/19c/grid/OPatch/opatch lsinv | grep desc

/u01/app/19c/grid/Oatch/opatch lspatches -oh /u01/app/19c/grid

/u01/app/19c/grid/.patch_storage/36115667_Dec_16_2023_08_49_53

7) lock and start GI as root

/u01/app/19c/grid/crs/install/rootcrs.sh -postpatch

8)Verify crs status :

crsctl check crs

ROLLBACK PATCH:

/u01/app/19c/grid/OPatch/opatch rollback -id 36115667

log loc : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch

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

Patching Using OPatchAuto :

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

OPatchAuto automates the patching of Oracle Grid Infrastructure (GI) and databases.

1. Pre-requisites:

o Ensure root user permissions.

o Download the GI patch.

2. Steps to Apply a Patch as ROOT :

export GRID_HOME=/u01/app/19c/grid

export path=$GRID_HOME/Opatch:$PATH

Verfication patch:

# /u01/app/19c/grid/OPatch/opatchauto apply /u01/patches/36352207/36115667 -analyze

(No conflict and systemspace opatchauto will take care all )

Run opatchauto as root:

Apply patch :

# u01/app/19c/grid/OPatch/opatchauto apply /u01/patches/36352207/36115667

Yes, it is possible to apply a patch to both Grid Infrastructure Home (GI_HOME) and Oracle Database Home (ORACLE_HOME) using opatch auto in a single command. However, you need to use the -all flag with opatch auto to apply the patch across multiple homes in one command.

export GRID_HOME=/u01/app/19c/grid

export path=$GRID_HOME/Opatch:$PATH

Note : First it will do prechecks on grid and oracle home.

bringing down the db services and cluster.start with oracle home binary patching then grid home. post checks on oracle and grid homes.

once completed start the cluster and db automatically.

$GRID_HOME/OPatch/opatchauto apply -analyze /u01/patches/36352207/

$GRID_HOME/OPatch/opatch auto -all /u01/patches/36352207/

logs location = /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatchauto

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

Single instance db patching 19.3 - 19.25( 2521164.1 doc for 19c patch )

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

Step:-1 Download the Patch from oracle support

p31771877_190000_Linux-x86-64.zip

p6880880_200000_Linux-x86-64.zip

Critical patch update (CPU) program APR 2024 patch availability documents (db only)

https://updates.oracle.com/download/6880880.html (optach download link)

Step:-3 Upgrade Opatch Tool from 12.2.0.1.17 to 12.2.0.1.23

[oracle@oraem01 OPatch]$ cd /usr/local/oracle/19c/OPatch/

[oracle@oraem01 OPatch]$ ./opatch version

OPatch Version: 12.2.0.1.17

OPatch succeeded.

[oracle@oraem01 OPatch]$ cd /usr/local/oracle/soft

[oracle@oraem01 soft]$ cp p6880880_200000_Linux-x86-64.zip /usr/local/oracle/19c/

[oracle@oraem01 19c]$ pwd

/usr/local/oracle/19c

[oracle@oraem01 19c]$ mv OPatch/ OPatch_BKP

[oracle@oraem01 19c]$ unzip p6880880_200000_Linux-x86-64.zip

Archive: p6880880_200000_Linux-x86-64.zip

creating: OPatch/

inflating: OPatch/emdpatch.pl

creating: OPatch/oracle_common/

creating: OPatch/oracle_common/modules/

inflating: OPatch/oracle_common/modules/com.oracle.glcm.common-logging_1.6.5.0.jar

inflating: OPatch/oracle_common/modules/common-logging-config.jar

creating: OPatch/oplan/

[oracle@oraem01 19c]$ cd OPatch

[oracle@oraem01 OPatch]$ ./opatch version

OPatch Version: 12.2.0.1.23

OPatch succeeded.

Step:-4 Check Patches status before apply using below query

SET LINESIZE 500

SET PAGESIZE 1000

SET SERVEROUT ON

SET LONG 2000000

COLUMN action_time FORMAT A12

COLUMN action FORMAT A10

COLUMN comments FORMAT A30

COLUMN description FORMAT A60

COLUMN namespace FORMAT A20

COLUMN status FORMAT A10

SELECT TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time,action,status,

description,patch_id FROM sys.dba_registry_sqlpatch ORDER by action_time;

ACTION_TIME ACTION STATUS DESCRIPTION PATCH_ID

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

2024-11-29 APPLY SUCCESS Database Release Update : 19.3.0.0.190416 (29517242) 29517242

SQL> col comp_id for a10

col version for a11

col status for a10

col comp_name for a37

select comp_id,comp_name,version,status from dba_registry;SQL> SQL> SQL> SQL>

COMP_ID COMP_NAME VERSION STATUS

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

CATALOG Oracle Database Catalog Views 19.0.0.0.0 VALID

CATPROC Oracle Database Packages and Types 19.0.0.0.0 VALID

RAC Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF

JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 VALID

XML Oracle XDK 19.0.0.0.0 VALID

CATJAVA Oracle Database Java Packages 19.0.0.0.0 VALID

APS OLAP Analytic Workspace 19.0.0.0.0 VALID

XDB Oracle XML Database 19.0.0.0.0 VALID

OWM Oracle Workspace Manager 19.0.0.0.0 VALID

CONTEXT Oracle Text 19.0.0.0.0 VALID

ORDIM Oracle Multimedia 19.0.0.0.0 VALID

SDO Spatial 19.0.0.0.0 VALID

XOQ Oracle OLAP API 19.0.0.0.0 VALID

OLS Oracle Label Security 19.0.0.0.0 VALID

DV Oracle Database Vault 19.0.0.0.0 VALID

COLUMN object_name FORMAT A30

SELECT owner,

object_type,

object_name,

status

FROM dba_objects

WHERE status = 'INVALID'

ORDER BY owner, object_type, object_name;

no rows selected

Step 5:- Shutdown Database and Listener

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

Version 19.3.0.0.0

[oracle@oraem01 19c]$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 24-NOV-2020 03:17:22

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraem01)(PORT=1521)))

The command completed successfully

Step:-6 Take Backup of ORACLE_HOME and Database (Rollback plan)

cd $ORACLE_HOME

tar -cvf oracle_home_24Nov_2020.tar $ORACLE_HOME

Connect with rman take full backup

Step:-7 Apply RU patch on ORACLE_HOME 19c

[oracle@oraem01 soft]$ unzip 36912597_190000_Linux-x86-64.zip

[oracle@oraem01 soft]$ cd 36912597

[oracle@oraem01 36912597]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1

[oracle@oraem01 36912597]$ export PATH=/u01/app/oracle/product/19.0.0/dbhome_1/OPatch:$PATH

[oracle@oraem01 36912597]$ opatch version - opatch version -jdk $ORACLE_HOME/jdk

OPatch Version: 12.2.0.1.23

OPatch succeeded.

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

[oracle@db1 OPatch]$ ./opatch version

OPatch Version: 12.2.0.1.43

OPatch succeeded.

[oracle@db1 OPatch]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/Patch/36912597

Oracle Interim Patch Installer version 12.2.0.1.43

PREREQ session

Oracle Home : /u01/app/oracle/product/19.0.0/dbhome_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc

OPatch version : 12.2.0.1.43

OUI version : 12.2.0.7.0

Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-12-18_09-10-14AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

[oracle@db1 OPatch]$ $ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseDir /u01/Patch/36912597

Oracle Interim Patch Installer version 12.2.0.1.43

PREREQ session

Oracle Home : /u01/app/oracle/product/19.0.0/dbhome_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc

OPatch version : 12.2.0.1.43

OUI version : 12.2.0.7.0

Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-12-18_09-16-08AM_1.log

Invoking prereq "checksystemspace"

Prereq "checkSystemSpace" passed.

OPatch succeeded.

[oracle@oraem01 31771877]$ opatch apply

Oracle Interim Patch Installer version 12.2.0.1.23

Oracle Home : /usr/local/oracle/19c

Central Inventory : /u01/app/oracle/oraInventory

from : /usr/local/oracle/19c//oraInst.loc

OPatch version : 12.2.0.1.23

OUI version : 12.2.0.7.0

Log file location : /usr/local/oracle/19c/cfgtoollogs/opatch/opatch2020-11-24_03-24-11AM_1.log

Verifying environment and performing prerequisite checks…

OPatch continues with these patches: 31771877

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 = ‘/usr/local/oracle/19c’)

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

User Responded with: Y

Patching component oracle.jdk, 1.8.0.201.0...

Patch 36912597 successfully applied.

Sub-set patch [29517242] has become inactive due to the application of a super-set patch [36912597].

Please refer to Doc ID 2161861.1 for any possible further required actions.

Log file location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-11-30_16-00-10PM_1.log

OPatch succeeded.

[oracle@orcl 36912597]$

Step:-8 Startup the Database and Listener

Step:-9 Execute post patch steps and run datapatch command

[oracle@orcl OPatch]$ ./datapatch -verbose

SQL Patching tool version 19.25.0.0.0 Production on Sat Nov 30 16:10:04 2024

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_20031_2024_11_30_16_10_04/sqlpatch_invocation.log

Connecting to database...OK

Gathering database info...done

Note: Datapatch will only apply or rollback SQL fixes for PDBs

that are in an open state, no patches will be applied to closed PDBs.

Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation

(Doc ID 1585822.1)

Warning: PDB PDB is in mode MOUNTED and will be skipped.

Bootstrapping registry and package to current versions...done

Error: prereq checks failed!

verify_queryable_inventory returned ORA-20001: Latest xml inventory is not loaded into table

Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 and/or the invocation log

/u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_20031_2024_11_30_16_10_04/sqlpatch_invocation.log

for information on how to resolve the above errors.

SQL Patching tool complete on Sat Nov 30 16:10:12 2024

Step:-10 After applying RU patch,Check the DBA_REGISTRY_SQLPATCH

SET LINESIZE 500

SET PAGESIZE 1000

SET SERVEROUT ON

SET LONG 2000000

COLUMN action_time FORMAT A12

COLUMN action FORMAT A10

COLUMN patch_type FORMAT A10

COLUMN description FORMAT A32

COLUMN status FORMAT A10

COLUMN version FORMAT A10

spool check_patches_19c.txt

select CON_ID,

TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time,

PATCH_ID,

PATCH_TYPE,

ACTION,

DESCRIPTION,

SOURCE_VERSION,

TARGET_VERSION

from CDB_REGISTRY_SQLPATCH

order by CON_ID, action_time, patch_id;

spool off

Step:-11 Check opatch lsinventory and list of patches applied in ORACLE_HOME

[oracle@oraem01 ~]$ cd $ORACLE_HOME/OPatch

[oracle@orcl OPatch]$ ./opatch lspatches -jdk $ORACLE_HOME/jdk

36912597;Database Release Update : 19.25.0.0.241015 (36912597)

29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

OPatch succeeded.

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Dec 1 18:02:16 2024

Version 19.25.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.25.0.0.0

SQL> select owner, count(*) count from all_objects where status = 'INVALID' group by owner;

no rows selected

SQL>

SQL> @?/rdbms/admin/utlrp.sql

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

Run opatchauto:

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

For a standalone database:

bash

Copy code

$ORACLE_HOME/OPatch/opatchauto apply /u01/Patch/36912597

Verfication patch:

#/u01/app/19c/grid/OPatch/opatchauto apply /u01/patches/36352207/36115667 -analyze

(No conflict and systemspace opatchauto will take care all )

Apply patch :

/u01/app/oracle/product/19.0.0/dbhome_1/OPatch/opatchauto apply /u01/Patch/36912597

Interim patches (2) :

Patch 36912597 : applied on Wed Dec 18 10:40:26 PST 2024

Unique Patch ID: 25871884

Patch description: "Database Release Update : 19.25.0.0.241015 (36912597)"

Created on 11 Oct 2024, 06:47:24 hrs UTC

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

#Oracle 11.2.0.1 to 11.2.0.4 Upgrade via Manual Method

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

#### Prepare for Upgrade

1. Take DB backup via RMAN.

2. Take `ORACLE_HOME`, OraInventory backup via OS level copy.

3. Run the `utlrp.sql` script to validate invalid objects and components:

```

exec dbms_stats.gather_database_stats;

exec dbms_stats.gather_dictionary_stats;

exec dbms_stats.gather_fixed_objects_stats;

```

#### Install 11.2.0.4

1. Download and install 11.2.0.4 into a different Oracle Home location.

2. Run the pre-upgrade utility found under 11.2.0.4 Home:

`11.2.0.4_HOME/rdbms/admin/utlu112i.sql` (Run pre-upgrade fixup script)

3. Review and fix all issues reported by the above script.

4. Shutdown the DB, Listener, EMC.

#### Start the Upgrade

1. Copy `DBS` and `NETWORK` folders from old O_H to new O_H.

2. Set the environment variable `O_H` to the new Oracle Home location.

3. Start the DB in `STARTUP UPGRADE` mode.

4. Run the upgrade utility to upgrade the database:

`11.2.0.4_HOME/rdbms/admin/catupgrd.sql`

#### Post Upgrade Steps

1. Start up the DB normally. Ensure it is started with the new Oracle Home.

2. Run the `utlrp.sql` script to validate invalid objects.

3. Run the post-upgrade script:

`11.2.0.4_HOME/rdbms/admin/utlu112s.sql`

4. Change the compatibility parameter to 11.2.0.4:

```

SQL> alter system set compatible='11.2.0.4.0' scope=spfile;

```

5. Check the version details post-upgrade:

```

SQL> select comp_name, version, status from dba_registry;

```

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

2. Upgrading Oracle Database

Upgrading involves transitioning to a newer database version to leverage new features and improve

performance.

Upgrade Paths

• Direct upgrades are supported from:

o 12c (12.1.0.2, 12.2.0.1) to 19c.

o Older versions require an intermediate upgrade.

Steps to Upgrade Oracle Database (12c to 19c)

1. Pre-upgrade Steps:

o Check Compatibility: Use the Database Pre-Upgrade Tool:

$ORACLE_HOME/jdk/bin/java -jar preupgrd.jar

o Backup the Database: Perform a full RMAN backup:

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

o Gather Statistics:

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

o Disable Invalid Objects: Resolve invalid objects using utlrp.sql.

2. Perform the Upgrade:

o Install 19c Software: Install the Oracle 19c binaries in a new Oracle Home directory.

o Run DBUA (Database Upgrade Assistant):

$ORACLE_HOME/bin/dbua

Follow the GUI prompts to upgrade the database.

o Manually Upgrade Using Command Line:

STARTUP UPGRADE;

@?/rdbms/admin/catctl.pl -n 4 -d . catupgrd.sql

3. Post-upgrade Steps:

o Recompile Objects:

@$ORACLE_HOME/rdbms/admin/utlrp.sql

o Update Compatibility:

ALTER SYSTEM SET COMPATIBLE = '19.0.0';

o Test the Database: Validate using application-specific queries and performance

benchmarks.

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

Database Upgradation Steps For RAC 12C TO 19C

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

Step 1: Create a new 19c oracle database home and provide permission for both nodes

mkdir -p /u01/app/oracle/product/19C/dbhome

chown -R oracle:oinstall /u01

chmod -R 775 /u01

mkdir -p /u01/app/oracle/product/19C/dbhome

chown -R oracle:oinstall /u01

chmod -R 775 /u01

Step 2: We download the oracle database 19c software and uploaded zip file into the new 19c home location (one node of RAC)

cd /u01/app/oracle/product/19C/dbhome

Copy Oracle 19C Software

Unzip it

cd /u01/app/oracle/product/19C/dbhome

Copy Oracle 19C Software

Unzip it

Step 3: Install oracle 19c software only in one node of RAC environment for upgradation

Cd $ORACLE_HOME

./runInstaller ------------------------- Install software only 1st node only

Setup only software

Step 4: Check the Invalid Objects if there have any invalid objects compile them before the upgrade.

$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 5 16:12:57 2023

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

$ SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)

----------

Step 5: Create the required directory where store the upgradation script

mkdir -p /home/oracle/preupgrade -- 1st Node only

chown -R oracle:oinstall /home/oracle/preupgrade

chmod -R 775 /u01/spool/preupgrade

mkdir -p /home/oracle/preupgrade -- 1st Node only

chown -R oracle:oinstall /home/oracle/preupgrade

chmod -R 775 /u01/spool/preupgrade

Step 6: Execute the Pre-upgrade script in 1st node only

/u01/app/oracle/product/12C/dbhome/jdk/bin/java -jar /u01/app/oracle/product/19C/dbhome/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/preupgrade/

Note: Carefully view the pre-upgrade log and take care of the issues.

Step 7: GATHER DICTIONARY STATS

Before the upgrade process, gather stats. One of the recommendations is to export the stats as well.

sqlplus / as sysdba

SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

sqlplus / as sysdba

Step 8: Purge Recycle Bin

Before the upgrade process, empty the recycle bin.

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

Step 9: RUN PREUPGRADE FIXUP SQL

SQL> @/home/oracle/preupgrade/preupgrade_fixups.sql

Step 10: Verify Archive Destination Size

Ensure you have enough free space in db_recovery_file_dest and make changes to the parameter db_recovery_file_dest_size if needed increase size.

SQL> archive log list;

SQL> show parameter db_recovery_file_dest

Step 11: Stop Database Listener and Check the Flashback Status

# srvctl stop LISTENER – run from grid user

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

Enable flashback database using the following procedure

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=20G;

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+FRA’;

SQL> alter database flashback on;

Step 12: Create a restore point before the upgrade issue.

SQL> create restore point PRE_UPGRADE guarantee flashback database;

Step 13: Oracle database upgrade for RAC environment.

Shutdown database of both nodes

copy spfile/create pfile and password file to new 19c oracle home.

COPY [SPFILE/PASSWORD] FILES FROM 12C TO 19C ORACLE HOME.

create pfile and store it in 19c home

sqlplus / as sysdba

create pfile=’/u01/app/oracle/product/19c/dbhome/dbs/initTESTDB1.ora’ from spfile;

NOTE: In pfile edit one parameter to cluster_database=False

Shutdown database & listener

srvctl stop LISTENER -- from grid user

srvctl stop database -d TESTDB -o immediate -- from oracle user

Step 14: Start database in upgrade from 19c home

export ORACLE_HOME=/u01/app/oracle/product/19C/dbhome

$ which sqlplus

/u01/app/oracle/product/19c/db_1/bin/sqlplus

sqlplus / as sysdba

SQL>startup upgrade

SQL> select status from v$instance;

Status

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

OPEN MIGRATE

Now run dbupgrade from 19c home

cd $ORACLE_HOME/bin

$ ./dbupgrade -- 1st node only & 2nd node will be down state

export ORACLE_HOME=/u01/app/oracle/product/19C/dbhome

Step 15: Post-Upgrade Activities

After the upgrade start the database from 19c home sqlplus / as sysdba

Step 16: Post upgrade run utlrp.sql to compile an invalid object

cd $ORACLE_HOME/rdbms/admin

SQL> @utlrp.sql

Step 17: Run the post-upgrade script

cd /home/oracle/preupgrade

SQL> @postupgrade_fixups.sql

Step 18: Upgrade timezone

SQL> select version from v$timezone_file;

VERSION

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

cd $ORACLE_HOME/rdbms/admin

SQL> @utltz_upg_check.sql

When it executed without any error run the below sql script

SQL> @utltz_upg_apply.sql

Now Run the script utlusts.sql

Now run the script catuppst.sql

SQL> @catuppst.sql

Rerun post upgrade fixup sql to check where exit any error or not

SQL> @postupgrade_fixups.sql

Now check the invalid object

SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)

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

Upgradation is completed successfully so delete the restore point that created before upgrade

Set compatible parameter

SQL> show parameter compatible

NAME TYPE VALUE

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

compatible string 12.2.0

SQL> alter system set compatible='19.0.0' scope=spfilesid='*';

SQL>col COMP_ID for a10

col COMP_NAME for a40

col VERSION for a15

set lines 200 pages 100

select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

Now set a new database home for the second node of RAC and startup database for the second node

After completing the migration upgrade oracle home for RAC

Oracle database new home upgradation in oracle RAC infrastructure using the following command

srvctlconfig database -d TESTDB -- Show the database configuration

su – oracle

cd $ORACLE_HOME/bin

./srvctl upgrade database -d databasename -oraclehome 19c_new_home

./srvctl upgrade database -d TESTDB -oraclehome/u01/app/oracle/product/19C/dbhome

./srvctl status database -d TESTDB -- Check status of database

./srvctl stop database -d TESTDB -o immediate -- stop database of the cluster

./srvctl start database -d TESTDB -- Start database of cluster

The database has been successfully upgraded to 19c.

SQL> select name, open_mode, version from v$database, v$instance;

NAME OPEN_MODE VERSION

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

TESTDB READ WRITE 19.0.0.0.0

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

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

Oracle 19c Database Upgrade From 12.2.0.1 to 19.2.0.0 Using DBUA

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

[oracle@standby dbs]$ export ORACLE_SID=upgr

[oracle@standby dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 21 20:02:00 2019

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Ensure backup is complete before upgrade.

Empty Recycle bin.

Run Gather statistics to finish upgrade soon.

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

no rows selected

SQL> PURGE DBA_RECYCLEBIN ;

DBA_Recyclebin purged.

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

SQL> !

Stop the running 12c listener

lsnrctl stop LISTENER

Run preupgrade tool and fixups

Oracle strongly recommends that you run the Pre-Upgrade Information Tool before starting the upgrade with DBUA. Although DBUA runs the Pre-Upgrade Information Tool as part of the pre-requisite checks, it is good practice to run the tool ahead of time in order to analyze the database and take actions that can decrease downtime for upgrading.

Connect to 12.2.0.1 database environment as SYS,

Pre-upgrade checks :

Run the preupgrade tool .

[oracle@standby dbs]$ java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TEXT TERMINAL

Report generated by Oracle Database Pre-Upgrade Information Tool Version

19.0.0.0.0 Build: 1 on 2019-03-21T20:07:45

Upgrade-To version: 19.0.0.0.0

Run the preupgrade_fixup.sql

Before upgrade:

Log into the database and execute the preupgrade fixups

@/u01/app/oracle/cfgtoollogs/upgr/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups

@/u01/app/oracle/cfgtoollogs/upgr/preupgrade/postupgrade_fixups.sql

[oracle@standby dbs]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 21 20:13:03 2019

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> @/u01/app/oracle/cfgtoollogs/upgr/preupgrade/preupgrade_fixups.sql

Executing Oracle PRE-Upgrade Fixup Script

Preup Preupgrade

Action Issue Is

Number Preupgrade Check Name Remedied Further DBA Action

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

1. pre_fixed_objects YES None.

2. tablespaces_info NO Informational only.

Further action is optional.

3. rman_recovery_version NO Informational only.

Further action is optional.

Now run the dbua utility from 12c oracle_home location.

[oracle@standby ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1

[oracle@standby ~]$ export ORACLE_SID=upgr

[oracle@standby ~]$ export PATH=$ORACLE_HOME/bin:$PATH

[oracle@standby ~]$ dbua

DBUA performs some of the checks before actually starting the database upgrade. Some of the checks can be done manually to reduce downtime for the upgrade.

DBUA provides below options:

– Upgrade timezone.

– Gather dictionary statistics before upgrade.

– Make user tablespaces read only.

– Take RMAN backup before upgrade.

– Create Restore Point for Database Flashback

– Restore database backup to rollback upgrade

– Option to execute Custom scripts before and after upgrade

– show the location of DBUA logs and Alert log files.

– Option to upgrade existing listener to 18.x home or create a new listener in 18.x target home.

Steps -

1)select the Upgrade Oracle Database option, then click the “Next” button.

2)If the prerequisite checks highlight any issues, take the appropriate action to fix the issues. If all the prerequisite checks are passed, click the “Next” button.

3)Amend the upgrade options if necessary, then click the “Next” button.

4)Select the recovery options for use in the event of an upgrade failure, then click the “Next” button.

5)If the database is using the 12c listener, accept the defaults, so the listener will be upgraded. If you prefer to manually define a new listener, you can do that now too and click the “Next” button

6)Select the desired management options, then click the “Next” button

Post-upgrade checks:

Run “postupgrade_fixups.sql” to check post upgrade status.

SQL> @/u01/app/oracle/cfgtoollogs/upgr/preupgrade/postupgrade_fixups.sql

Session altered.

Now the database upgrade is complete and the database is ready for normal use.

SQL> select VERSION,name,open_mode from v$instance,v$database;

VERSION NAME OPEN_MODE

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

19.0.0.0.0 UPGR READ WRITE

SQL> SELECT * FROM v$timezone_file;

FILENAME VERSION CON_ID

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

timezlrg_32.dat 32 0

SQL> select TZ_VERSION from registry$database;

TZ_VERSION

----------

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

AutoUpgrade – Step-by-step

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

1)The new AutoUpgrade Utility – Download, documentation and supported versions

2)Create and adjust the config file for AutoUpgrade

3)Config file for AutoUpgrade – Advanced options

4)Config file for AutoUpgrade – Tweaking init parameters

5)AutoUpgrade: ANALYZE, FIXUPS, UPGRADE and DEPLOY modes

6)AutoUpgrade: Where do you find all the logfiles?

7)UPG: The AutoUpgrade Command Line Interface

8)Upgrading Multitenant databases with AutoUpgrade

9)Moving to a new server with AutoUpgrade

10)How to tweak the hidden settings in AutoUpgrade

11)AutoUpgrade and Data Guard, RAC, Restart and non-CDB to PDB

12)AutoUpgrade and Wallets

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

You get it when you install Oracle Database 19c (19.3) or newer. Or – and this is the recommended source, you download the most recent version from MOS Note: 2485457.1 – AutoUpgrade Tool:

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

root@oraclelab1 patches]# ll

total 11868216

-rw-r--r--. 1 root root 4822622 May 23 10:36 autoupgrade.jar

[root@oraclelab1 patches]# cp autoupgrade.jar /u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin/

cp: overwrite ‘/u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin/autoupgrade.jar’? y

[root@oraclelab1 patches]# cd /u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin/

[root@oraclelab1 admin]# ll autoupgrade.jar

-rw-r--r--. 1 oracle oinstall 4822622 May 23 10:39 autoupgrade.jar

[root@oraclelab1 admin]# . oraenv

ORACLE_SID = [root] ? DEVDB

The Oracle base has been set to /u01/app/oracle

[root@oraclelab1 admin]# $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -version

build.version 22.3.220503

build.date 2022/05/03 11:55:20 -0400

build.hash 9e84e228

build.hash_date 2022/05/03 11:37:00 -0400

build.supported_target_versions 12.2,18,19,21

build.type production

[root@oraclelab1 admin]# $ORACLE_HOME/jdk/bin/java -version

java version "1.8.0_201"

[root@oraclelab1 admin]# cd /u01/patches/

[root@oraclelab1 patches]# mkdir 19c-autoupg

[root@oraclelab1 patches]# cd 19c-autoupg/

[root@oraclelab1 19c-autoupg]# ll

total 0

[root@oraclelab1 19c-autoupg]# export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[root@oraclelab1 19c-autoupg]# export PATH=$PATH:$ORACLE_HOME/jdk/bin

[root@oraclelab1 patches]# chown oracle: autoupgrade.jar

[root@oraclelab1 19c-autoupg]# $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -create_sample_file config

Created sample configuration file /u01/patches/19c-autoupg/sample_config.cfg

[root@oraclelab1 19c-autoupg]# ls -ltrh

total 8.0K

-rw-r--r--. 1 root root 5.8K May 23 10:41 sample_config.cfg

[root@oraclelab1 19c-autoupg]# cp sample_config.cfg DB2_config.cfg

[root@oraclelab1 19c-autoupg]# vi DB2_config.cfg

vi sample_config

Sample 1:

# Database number 1 - Full DB/CDB upgrade

upg1.log_dir=/u01/patches/19c-autoupg/DB2 # Path of the log directory for the upgrade job

upg1.sid=DB2 # ORACLE_SID of the source DB/CDB

upg1.source_home=/u01/app/oracle/product/12.2.0.1/dbhome_1 # Path of the source ORACLE_HOME

upg1.target_home=/u01/app/oracle/product/19.0.0.0/dbhome_1 # Path of the target ORACLE_HOME

upg1.start_time=NOW # Optional. [NOW | +XhYm (X hours, Y minutes after launch) | dd/mm/yyyy hh:mm:ss]

upg1.upgrade_node=oraclelab1.localdomain.com # Optional. To find out the name of your node, run the hostname utility. Default is ''localhost''

#upg1.run_utlrp=[yes|no] # Optional. Whether or not to run utlrp after upgrade

#upg1.timezone_upg=[yes|no] # Optional. Whether or not to run the timezone upgrade

#upg1.target_version=[12.2|18|19|21] # Oracle version of the target ORACLE_HOME. Only required when the target Oracle database version is 12.2

[root@oraclelab1 19c-autoupg]# ls -otrh

total 16K

-rw-r--r--. 1 oracle 5.8K May 23 10:41 sample_config.cfg

-rw-r--r--. 1 oracle 5.8K May 23 10:46 DB2_config.cfg

[root@oraclelab1 19c-autoupg]# su - oracle

Last login: Mon May 23 09:12:24 IST 2022 on pts/3

[oracle@oraclelab1 ~]$ . oraenv

ORACLE_SID = [oracle] ? DB2

The Oracle base has been set to /u01/app/oracle

[oracle@oraclelab1 ~]$ env |grep ORA

ORACLE_SID=DB2

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1

[oracle@oraclelab1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ env |grep ORA

ORACLE_SID=DB2

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oraclelab1 ~]$ export PATH=$PATH:$ORACLE_HOME/jdk/bin

[oracle@oraclelab1 ~]$ cd /u01/patches/19c-autoupg/

[oracle@oraclelab1 19c-autoupg]$ ll

total 16

-rw-r--r--. 1 oracle oinstall 5886 May 23 10:46 DB2_config.cfg

-rw-r--r--. 1 oracle oinstall 5905 May 23 10:41 sample_config.cfg

[oracle@oraclelab1 19c-autoupg]$ $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config DB2_config.cfg -mode ANALYZE

[oracle@oraclelab1 19c-autoupg]$ $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config DB2_config.cfg -mode DEPLOY

AutoUpgrade 22.3.220503 launched with default internal options

Processing config file ...

+--------------------------------+

| Starting AutoUpgrade execution |

+--------------------------------+

1 Non-CDB(s) will be analyzed

Type 'help' to list console commands

upg> lsj

+----+-------+---------+---------+-------+----------+-------+----------------------------+

+----+-------+---------+---------+-------+----------+-------+----------------------------+

+----+-------+---------+---------+-------+----------+-------+----------------------------+

Total jobs 1

upg> status

Config

User configuration file [/u01/patches/19c-autoupg/DB2_config.cfg]

General logs location [/u01/patches/19c-autoupg/autoupgrade/cfgtoollogs/upgrade/auto]

Mode [ANALYZE]

Jobs Summary

Total databases in configuration file [1]

Total Non-CDB being processed [1]

Total Containers being processed [0]

Jobs finished successfully [0]

Jobs finished/aborted [0]

Jobs in progress [1]

upg> tasks

Please check the summary report at:

/u01/patches/19c-autoupg/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html

/u01/patches/19c-autoupg/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log

upg> Job 101 completed

------------------- Final Summary --------------------

Numb

Post check -

er of databases [ 1 ]

Jobs finished [1]

Jobs failed [0]

Jobs restored [0]

Jobs pending [0]

---- Drop GRP at your convenience once you consider it is no longer needed ----

Drop GRP from DB2: drop restore point AUTOUPGRADE_9212_DB2122010

Post check -

SQL>

SQL> column comp_name format a40

column version format a12

column status format a15

select comp_name,version,status from dba_registry;SQL> SQL> SQL>

COMP_NAME VERSION STATUS

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

Oracle Database Catalog Views 19.0.0.0.0 VALID

Oracle Database Packages and Types 19.0.0.0.0 VALID

JServer JAVA Virtual Machine 19.0.0.0.0 VALID

Oracle XDK 19.0.0.0.0 VALID

Oracle Database Java Packages 19.0.0.0.0 VALID

OLAP Analytic Workspace 19.0.0.0.0 VALID

Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF

Oracle XML Database 19.0.0.0.0 VALID

Oracle Workspace Manager 19.0.0.0.0 VALID

Oracle Text 19.0.0.0.0 VALID

Oracle Multimedia 19.0.0.0.0 VALID

COMP_NAME VERSION STATUS

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

Spatial 19.0.0.0.0 VALID

Oracle OLAP API 19.0.0.0.0 VALID

Oracle Label Security 19.0.0.0.0 VALID

Oracle Database Vault 19.0.0.0.0 VALID

15 rows selected.

SQL> column owner format a15

column object_name format a40

column object_type format a20

select owner, object_name, object_type from dba_objects where status='INVALID' and owner like '%SYS%' order by object_type,owner,object_name;SQL> SQL> SQL>

no rows selected

SQL> select * from v$restore_point

2 ;

SCN DATABASE_INCARNATION# GUA STORAGE_SIZE

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

TIME

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

RESTORE_POINT_TIME PRE

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

NAME

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

PDB CLE PDB_INCARNATION# REP CON_ID

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

1429198 2 YES 1048576000

23-MAY-22 10.50.18.000000000 AM

YES

SCN DATABASE_INCARNATION# GUA STORAGE_SIZE

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

TIME

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

RESTORE_POINT_TIME PRE

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

NAME

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

PDB CLE PDB_INCARNATION# REP CON_ID

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

AUTOUPGRADE_9212_DB2122010

NO NO 0 NO 0

SQL> drop restore point AUTOUPGRADE_9212_DB2122010;

Restore point dropped.

SQL> select * from v$restore_point;

no rows selected

If Fails Restore it

[oracle@oraclelab1 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Mon May 23 11:38:22 2022

Version 19.3.0.0.0

connected to target database: DB2 (DBID=2268890156)

RMAN> flashback database to AUTOUPGRADE_9212_DB2122010;

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

Oracle 11g to 12c Rolling Upgrade :

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

Upgrade Oracle 11g to 12c with minimal downtime using rolling upgrades.

A rolling upgrade allows you to perform database upgrade without having any noticeable downtime to the end users. There are multiple ways to perform rolling upgrade. But you must identify which one will work for your environment.

Types of Rolling Upgrade

Convert Physical Standby to Logical Standby

Switch to Logical Standby

Convert Logical Standby to Physical Standby

Types of Rolling Upgrades

Below are the two common methods used for performing Oracle rolling upgrades:

Method 1: Transient logical standby database

Convert existing physical standby to logical standby using KEEP IDENTITY clause

Upgrade Logical standby first

Return the Logical standby back to physical standby

Method 2: DBMS_ROLLING_UPGRADE package

Only available from 12c

Its automation of transient logical standby process :Convert Physical Standby to Logical Standby

Assumption: We assume that you already have a physical standby setup for a production database and you want to perform a rolling upgrade so that users are not impacted

CURRENT CONFIGURATION:

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

PRIMARY

=======

SID: prod

Role: Primary Database

Version: 11.2.0.4

STANDBY

=======

SID: prod_st

Role: Physical Standby Database

Version: 11.2.0.4

Create a Guaranteed Restore Point on both primary and physical standby before upgrade activity

On primary (prod):

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

SQL> create restore point pre_upgrade_pri guarantee flashback database;

On standby (prod_st):

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

SQL> alter database recover managed standby database cancel;

SQL> create restore point pre_upgrade_stb guarantee flashback database;

SQL> alter database recover managed standby database disconnect;

Build Log Miner directory on primary

begin

dbms_logstdby.build;

end;

Convert physical standby into logical standby

On standby (prod_st):

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

SQL> alter database recover managed standby database cancel;

SQL> shut immediate;

SQL> startup mount;

SQL> alter database recover to logical standby keep identity;

SQL> alter database open;

SQL> alter database start logical standby apply immediate;

SQL> select state from v$logstdby_state; --> Must see IDEL

NEW CONFIGURATION:

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

PRIMARY

=======

SID: prod

Role: Primary Database

Version: 11.2.0.4

STANDBY

=======

SID: prod_st

Role: Logical Standby Database

Version: 11.2.0.4

Stop SQL apply process on logical standby and create another restore point before starting upgrade

On primary (prod):

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

SQL> alter system set log_archive_dest_state_2=DEFER scope=memory;

On Logical Standby (prod_st):

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

SQL> alter database stop logical standby apply;

SQL> create restore point before_upgrade_lstb guarantee flashback database;

SQL> shutdown immediate;

Perform logical standby upgrade using DBUA or manual method. Note, users are still connected to prod, the original primary database. Once upgrade is completed, we will start the LSP from upgrade logical standby database

On primary (prod):

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

SQL> alter system set log_archive_dest_state_2=enable scope=memory;

On Upgraded Logical Standby (prod_st):

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

SQL> alter database start logical standby apply immediate;

CURRENT CONFIGURATION:

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

PRIMARY

=======

SID: prod

Role: Primary Database

Version: 11.2.0.4

STANDBY

=======

SID: prod_st

Role: Logical Standby Database

Version: 12.1.0.1 >> upgraded

Switch to Logical Standby

Perform switchover to upgraded logical standby

On primary (prod):

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

SQL> select switchover_status from v$database;

SQL> alter database commit to switchover to logical standby;

On Upgraded Logical Standby (prod_st):

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

SQL> select switchover_status from v$database;

SQL> alter database commit to switchover to logical primary;

CURRENT CONFIGURATION:

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

PRIMARY

=======

SID: prod

Role: Logical Standby Database

Version: 11.2.0.4

STANDBY

=======

SID: prod_st

Role: Primary Database

Version: 12.1.0.1 >> upgraded

Convert Logical Standby to Physical Standby

As the users are now connected to new primary (earlier upgraded logical standby), we will flashback the original primary to the restore point pre_upgrade_pri

On new primary – Earlier upgraded logical standby (prod_st):

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

SQL> alter system set log_archive_dest_state_2=defer scope=memory;

On new logical standby database – Earlier primary database (prod):

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

SQL> select database_role from v$database; >> must see LOGICAL STANDBY

SQL> shutdown immediate;

SQL> startup mount

SQL> flashback database to restore point pre_upgrade_pri;

SQL> shutdown immediate;

Start new logical standby database (prod) via new 12c ORACLE HOME and convert it into physical standby.

Copy parameter file, password file to new ORACLE HOME

Copy tnsnames.ora to new ORACLE HOME

On new logical standby database – Earlier primary database (prod):

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

SQL> startup mount

SQL> alter database convert to physical standby;

SQL> shutdown immediate;

SQL> startup mount

CURRENT CONFIGURATION:

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

PRIMARY

=======

SID: prod

Role: Physical Standby Database

Version: 12.1.0.1 >> upgraded

STANDBY

=======

SID: prod_st

Role: Primary Database

Version: 12.1.0.1 >> upgraded

Start MRP on new physical standby (prod)

On new primary – Earlier upgraded logical standby (prod_st):

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

SQL> alter system set log_archive_dest_state_2='ENABLE' scope=memory;

On new physical standby database – Earlier primary database (prod):

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

SQL> alter database recover managed standby database disconnect;

Monitor the alert log and see redo apply should upgrade the physical standby. Once upgrade is done, issue below command on physical standby to check the component versions

SQL> @?/rdbms/admin/utlu112s.sql

You can choose to perform a switchover at this stage to revert back to original configuration.

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

[oracle@db1 OPatch]$ export PATH=$ORACLE_HOME/OPatch:$PATH

[oracle@db1 OPatch]$ echo $ORACLE_HOME

/u01/app/19c/grid

[oracle@db1 OPatch]$ opatch lspatches

There are no Interim patches installed in this Oracle Home "/u01/app/19c/grid".

OPatch succeeded.

Critical patch update (CPU) program APR 2024 patch availability documents (db only)

https://updates.oracle.com/download/6880880.html (optach download link)

% mv p6880880_101000_SOLARIS64.zip $ORACLE_HOME

% cd $ORACLE_HOME

(If a former OPatch directory already exist, then rename it)

% mv Opatch OPatch_100057_OLD

% unzip p6880880_101000_SOLARIS64.zip

Archive: p6880880_101000_SOLARIS64.zip

Opatch latest version downloads :

[oracle@db1 OPatch]$ ./opatch version

OPatch Version: 12.2.0.1.26

OPatch succeeded.

3.3 Tools

This section contains the following:

Section 3.3.1 "Oracle OPatch"

3.3.1 Oracle OPatch

Minimum Product Requirements for Oracle OPatch

The CPU security vulnerabilities are fixed in the listed release and later releases. The Oracle OPatch downloads can be found at Patch 6880880.

Oracle database :

3.1.7.4 Oracle Database 19

Oracle GoldenGate :

3.1.9 Oracle GoldenGate

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

GI :Conflict precheck :

/u01/app/19c/grid/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/patches/36352207/36115667 -oh /u01/app/19c/grid

/u01/app/19c/grid/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/patches/36352207/36352352 -oh /u01/app/19c/grid

/u01/app/19c/grid/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/patches/36352207/36360754 -oh /u01/app/19c/grid

/u01/app/19c/grid/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/patches/36352207/36360764 -oh /u01/app/19c/grid

/u01/app/19c/grid/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/patches/36352207/36360767 -oh /u01/app/19c/grid

/u01/app/19c/grid/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/patches/36352207/36460255 -oh /u01/app/19c/grid

System space precheck :

/u01/app/19c/grid/OPatch/opatch prereq CheckSystemSpace -phBaseDir /u01/patches/36352207/36115667 -oh /u01/app/19c/grid

/u01/app/19c/grid/OPatch/opatch prereq CheckSystemSpace -phBaseDir /u01/patches/36352207/36352352 -oh /u01/app/19c/grid

/u01/app/19c/grid/OPatch/opatch prereq CheckSystemSpace -phBaseDir /u01/patches/36352207/36360754 -oh /u01/app/19c/grid

/u01/app/19c/grid/OPatch/opatch prereq CheckSystemSpace -phBaseDir /u01/patches/36352207/36360764 -oh /u01/app/19c/grid

/u01/app/19c/grid/OPatch/opatch prereq CheckSystemSpace -phBaseDir /u01/patches/36352207/36360767 -oh /u01/app/19c/grid

/u01/app/19c/grid/OPatch/opatch prereq CheckSystemSpace -phBaseDir /u01/patches/36352207/36460255 -oh /u01/app/19c/grid

Apply on GI :

/u01/app/19c/grid/OPatch/opatch apply -oh /u01/app/19c/grid -local -silent /u01/patches/36352207/36115667

/u01/app/19c/grid/OPatch/opatch apply -oh /u01/app/19c/grid -local -silent /u01/patches/36352207/36352352

/u01/app/19c/grid/OPatch/opatch apply -oh /u01/app/19c/grid -local -silent /u01/patches/36352207/36360754

/u01/app/19c/grid/OPatch/opatch apply -oh /u01/app/19c/grid -local -silent /u01/patches/36352207/36360764

/u01/app/19c/grid/OPatch/opatch apply -oh /u01/app/19c/grid -local -silent /u01/patches/36352207/36360767

/u01/app/19c/grid/OPatch/opatch apply -oh /u01/app/19c/grid -local -silent /u01/patches/36352207/36460255

DB Conflict Precheck :

/u01/app/oracle/product/19/db_2/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/patches/36352207/36352352 -oh /u01/app/oracle/product/19/db_2

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/Patch/36912597

/u01/app/oracle/product/19/db_2/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/patches/36352207/36360754 -oh /u01/app/oracle/product/19/db_2

System space precheck :

/u01/app/oracle/product/19/db_2/OPatch/opatch prereq CheckSystemSpace -phBaseDir /u01/patches/36352207/36352352 -oh /u01/app/oracle/product/19/db_2

$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseDir /u01/Patch/36912597

/u01/app/oracle/product/19/db_2/OPatch/opatch prereq CheckSystemSpace -phBaseDir /u01/patches/36352207/36360754 -oh /u01/app/oracle/product/19/db_2

Apply on DB :

/u01/app/oracle/product/19/db_2/OPatch/opatch apply -oh /u01/app/oracle/product/19/db_2 -local -silent /u01/patches/36352207/36352352

/u01/app/oracle/product/19/db_2/OPatch/opatch apply -oh /u01/app/oracle/product/19/db_2 -local -silent /u01/patches/36352207/36360754

ROLLBACK PATCH:

/u01/app/19c/grid/OPatch/opatch rollback -id 36115667

/u01/app/oracle/product/19/db_2/OPatch/opatch rollback -id 36352352

Manual VS OPatchauto

Verfication patch:

#/u01/app/19c/grid/OPatch/opatchauto apply /u01/patches/36352207/36115667 -analyze

(No conflict and systemspace opatchauto will take care all )

Apply patch :

#u01/app/19c/grid/OPatch/opatchauto apply /u01/patches/36352207/36115667

Search This Blog

ORACLE DATABASE

RAC Patching and Upgradation

Comments

Post a Comment

Popular posts from this blog

RAC NOTES

ORACLE CLOUD INFRASTRUCTURE

DATAPUMP (export import) IN ORACLE