Patch Deinstallaion (GI Home & DB Home) /Rolling Back the Patch from an Oracle RAC Home Installation Manually

Oracle sends patches each quarter or against any bug reported by end user. In this blog we will demonstrate how to deinstall or rolling back oracle patch provided by oracle on 16 Oct 2019, Patch applied to GI Home and DB Home. Execute the following on each node of the cluster in non-shared CRS and DB home environment to rollback the patch.

First of all we have to check the patch number which are applied to GI home and DB home, by issuing command:

1- $ GI_HOME/OPatch/Opatch lsinventory

It will display all the main patches, subpatches and the bugs fixed by that patch:

similarly from oracle home we should issue the same commad to check the patches number:
$ ORACLE_HOME/OPatch/opatch lsinventory

2- Stop the CRS managed resources running from DB homes.
$ <ORACLE_HOME>/bin/srvctl stop home -o <ORACLE_HOME> -s <status file location> -n <node name>

$ /u01/app/11.2.0/grid/bin/srvctl stop home -o /u01/app/11.2.0/grid -s /tmp/statedeinstall.txt -n racnode2

now check the status:

Now bring down all the services using command :
3- Run the pre root script.
 If this is a GI Home, as the root user execute:
 # <GI_HOME>/crs/install/rootcrs.pl -unlock

#

/u01/app/11.2.0/grid/crs/install/rootcrs.pl -unlock

4- Now rolling back the Patches :

As the GI home owner execute:
 $ <GI_HOME>/OPatch/opatch rollback -local -id <OCW Components_number> -oh <GI_HOME>

$ /u01/app/11.2.0/grid/OPatch/opatch rollback -local -id  29938455 -oh /u01/app/11.2.0/grid       

$ <GI_HOME>/OPatch/opatch rollback -local -id <ACFS Components_number> -oh <GI_HOME>

$/u01/app/11.2.0/grid/OPatch/opatch rollback -local -id  29509309 -oh /u01/app/11.2.0/grid

$ <GI_HOME>/OPatch/opatch rollback -local -id <DB_PSU_number> -oh <GI_HOME>

$ /u01/app/11.2.0/grid/OPatch/opatch rollback -local -id  29913194 -oh /u01/app/11.2.0/grid

 -------------------------------DB Home Patches Deinstallation------------------------------------------

 5- Run the pre script for DB component of the patch.

 As the database home owner execute:
 $ <UNZIPPED_PATCH_LOCATION>/<GI_PSU_number>/<OCW Components_number>/custom/server/<OCW Components_number>/custom/scripts/prepatch.sh -dbhome <ORACLE_HOME>

$ /u01/30070097/29938455/custom/server/29938455/custom/scripts/prepatch.sh -dbhome /u01/app/oracle/product/11.2.0


-- Roll back the DB patch from the database home.
 As the database home owner execute:
 $ <ORACLE_HOME>/OPatch/opatch rollback -local -id <OCW Components_number> -oh <ORACLE_HOME>

$ /u01/app/oracle/product/11.2.0/OPatch/opatch rollback -local -id /u01/30070097/29938455/ -oh /u01/app/oracle/product/11.2.0

$ <ORACLE_HOME>/OPatch/opatch rollback -local -id <DB_PSU_number> -oh <ORACLE_HOME>

$/u01/app/oracle/product/11.2.0/OPatch/opatch rollback -local -id 29913194 -oh /u01/app/oracle/product/11.2.0

6-  Run the post script for DB component of the patch.

 As the database home owner execute:

 $ <UNZIPPED_PATCH_LOCATION>/<GI_PSU_number>/<OCW Components_number>/custom/server/<OCW Components_number>/custom/scripts/postpatch.sh -dbhome <ORACLE_HOME>

 $/u01/30070097/29938455/custom/server/29938455/custom/scripts/postpatch.sh -dbhome /u01/app/oracle/product/11.2.0

/**************************DONE*********************************/

7. Run the post script.

 As the root user execute:

 # <GI_HOME>/rdbms/install/rootadd_rdbms.sh

 $ /u01/app/11.2.0/grid/rdbms/install/rootadd_rdbms.sh

If this is a GI Home, as the root user execute:

 # <GI_HOME>/crs/install/rootcrs.pl -patch

$ /u01/app/11.2.0/grid/crs/install/rootcrs.pl -patch

 Now checking the Database instance status after the patches are being successfully deinstalled.
Both nodes are up & running:

Checking the GI Home for patches, check patch inventory

$ /u01/app/11.2.0/grid/OPatch/opatch lsinventory

 There is no interim patch installed.

Now checking the DB home:

$ /u01/app/oracle/product/11.2.0/OPatch/opatch lsinventory

No interim patch have been installed , one done on all nodes, run Patch Post-Deinstallation Instructions for an Oracle RAC Environment, that are:


Loged in to DB:

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @catbundle_PSU_<database SID PREFIX>_ROLLBACK.sql
SQL> QUIT


In an Oracle RAC environment, the name of the rollback script will have the format catbundle_PSU_<database SID PREFIX>_ROLLBACK.sql.



cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @utlrp.sql

Check the log file for any errors. The log file is found in $ORACLE_BASE/cfgtoollogs/catbundle and is named catbundle_PSU_<database SID>_ROLLBACK_<TIMESTAMP>.log where TIMESTAMP is of the form YYYYMMMDD_HH_MM_SS. If there are errors, see Known Issues.

Creating PDBs from Seed in Container Databases

Creation of PDBs:
Pluggable database is created inside the container database and PDBs has its own directory with datafiles in it, and with 12cr2 onwards it can have its undo datafile. There is no specific controlfile or spfile for the portable database. Creation of Portable / pluggable databases in container databases (CDB) can be done in 3 different ways. i.e

1. DBCA
2.  SQL through sql command
3. SQL Developer

Here in this blog i'll create portable databases through dbca and through sql script.

1- Database Configuration Assistant (DBCA).

    As in normal databases installation and configurations, we invoke dbca and create databases similarly while creating portable database we will use dbca:

$ dbca

Pluggable database name:

Creation of pluggable database is started and it will take less than a minute:

Now database is created with name PDB2_1, let check physically the location where the database folders and files are created:

Eventhough in dbca you can specify the location where you want to create the portable database, but default location can be seen in above images is $ORACLE_BASE/oradata/cdb2/PORTABLE_DB_NAME as shown in above image.

We can view the datafile created in new pluggable database.

After all checking we have to create local net service to connect/login to newly created pluggable database, which can be done through netca:

All images are self explanatory here.

Name of service through which pluggable database can be access:

After creation of service we can check the new pluggable database by loggin in container database, if we query name,open_mode from v$pdbs, it will be noted that newly created pluggable database is in read and write mode, one thing to be noted here, that by default when you start the cdb pluggable database is in mount state, but when you create pluggable database through DBCA, it will create not only database for you but will also open it for you once the creation is completed.

2- SQL through sql command
    Now creating new pluggable database through sql script manually, first we have to create a directory manually as show below:

$ mkdir -p $ORACLE_BASE/oradata/cdb2/pdb2

After creation of directory login to cdb and run create command mentioning the pdb name ,admin user and location where the pdb is to be created as below:

SQL> create pluggable database pdb2 admin user adminuser identified by oracle roles=(CONNECT) create_file_dest='/u01/app/oracle/oradata/cdb2/pdb2';

Above command will take less than a minute and will create the scripted PDB.

Portable database created through sql script will be in mounted state after creation, so we will have to open the database manually.

Open the new created portable database:

Now to connect to PDB2 we net to have local net service name, which can be done through netca and explain/shown above.