Tag Archives: rollback patch in database 12c

ROLLBACK RU from DATABASE 12.2

ROLLBACK DATABASE RELEASE UPDATE from Oracle Database 12.2.0.1

Goal: We are going to ROLLBACK DATABASE RELEASE UPDATE: 12.2.0.1.170718 (26123830) from 12.2 Oracle Database Home

Contents
___________________________________________________________________________________________________

1. Environment
2. Check existing patches
3. Shutdown ALL PDBs and CDB
4. Stop LISTENER which is running from Oracle Database Home
5. Rollback patch
6. List Patches
7. Startup CDB and ALL PDBs
8. Startup LISTENER
9. Rollback SQL changes (datapatch)
10. Check Registry Status (dba_registry_sqlpatch)
____________________________________________________________________________________________________


1. Environment

Platform   : Linuxx86_64
Server IP  : 192.168.2.101
DB Version : Oracle 12.2.0.1.0, File system: Normal, RAC: Non-RAC
CDB Name   : CDB2
PDB Name   : PDB1,PDB2,PDB3,PDB4
Oracle Home: /u01/app/oracle/product/12.2.0.1


2. Check existing patches

[oracle@localhost ~]$ /u01/app/oracle/product/12.2.0.1/OPatch/opatch lsinv -oh /u01/app/oracle/product/12.2.0.1 | grep -i "Database Release Update"
Patch description:  "DATABASE RELEASE UPDATE: 12.2.0.1.170718 (26123830)"
[oracle@localhost ~]$

[oracle@localhost 26123830]$ /u01/app/oracle/product/12.2.0.1/OPatch/opatch lsinv -oh  /u01/app/oracle/product/12.2.0.1
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.2.0.1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.2.0.1/oraInst.loc
OPatch version    : 12.2.0.1.6
OUI version       : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/opatch2018-07-16_16-16-43PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/lsinv/lsinventory2018-07-16_16-16-43PM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: localhost
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 12c                                                  12.2.0.1.0
There are 1 products installed in this Oracle Home.


Interim patches (1) :

Patch  26123830     : applied on Mon Jul 16 16:13:29 CEST 2018
Unique Patch ID:  21405588
Patch description:  "DATABASE RELEASE UPDATE: 12.2.0.1.170718 (26123830)"
   Created on 7 Jul 2017, 00:33:59 hrs PST8PDT
   Bugs fixed:
     23026585, 24336249, 24929210, 24942749, 25036474, 25110233, 25410877
     25417050, 25427662, 25459958, 25547901, 25569149, 25600342, 25600421
     25606091, 25655390, 25662088, 24385983, 24923215, 25099758, 25429959
     25662101, 25728085, 25823754, 22594071, 23665623, 23749454, 24326846
     24334708, 24560906, 24573817, 24578797, 24609996, 24624166, 24668398
     24674955, 24744686, 24811725, 24827228, 24831514, 24908321, 24976007
     25184555, 25210499, 25211628, 25223839, 25262869, 25316758, 25337332
     25455795, 25457409, 25539063, 25546608, 25612095, 25643931, 25410017
     22729345, 24485174, 24509056, 24714096, 25329664, 25410180, 25607726
     25957038, 25973152, 26024732, 24376878, 24589590, 24676172, 23548817
     24796092, 24907917, 25044977, 25736747, 25766822, 25856821, 25051628
     24534401, 24835919, 25050160, 25395696, 25430120, 25616359, 25715167
     25967985



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

OPatch succeeded.
[oracle@localhost 26123830]$


3. Shutdown ALL PDBs and CDB

[oracle@localhost ~]$ . oraenv
ORACLE_SID = [CDB2] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@localhost ~]$

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 16 20:19:28 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


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

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT <-----
SQL>
SQL> ALTER PLUGGABLE DATABASE ALL CLOSE;

Pluggable database altered.

SQL>
SQL> SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> EXIT
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@localhost ~]$
[oracle@localhost ~]$ ps -ef | grep pmon
oracle   26328  3363  0 20:20 pts/0    00:00:00 grep pmon
[oracle@localhost ~]$


4. Stop LISTENER which is running from Oracle Database Home

[oracle@localhost ~]$ ps -ef | grep tns
root        22     2  0 13:45 ?        00:00:00 [netns]
oracle   21278     1  0 18:47 ?        00:00:00 /u01/app/oracle/product/12.2.0.1/bin/tnslsnr LISTENER -inherit
oracle   26361  3363  0 20:22 pts/0    00:00:00 grep tns
[oracle@localhost ~]$
[oracle@localhost ~]$ lsnrctl stop LISTENER

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-JUL-2018 20:22:35

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
The command completed successfully
[oracle@localhost ~]$
[oracle@localhost ~]$ ps -ef | grep tns
root        22     2  0 13:45 ?        00:00:00 [netns]
oracle   26365  3363  0 20:22 pts/0    00:00:00 grep tns
[oracle@localhost ~]$


5. Rollback patch

[oracle@localhost ~]$ /u01/app/oracle/product/12.2.0.1/OPatch/opatch rollback -id 26123830 -oh /u01/app/oracle/product/12.2.0.1 -local
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.2.0.1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.2.0.1/oraInst.loc
OPatch version    : 12.2.0.1.6
OUI version       : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/opatch2018-07-16_20-26-34PM_1.log


Patches will be rolled back in the following order:
   26123830
The following patch(es) will be rolled back: 26123830

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/12.2.0.1')


Is the local system ready for patching? [y|n]
y <----
User Responded with: Y

Rolling back patch 26123830...

RollbackSession rolling back interim patch '26123830' from OH '/u01/app/oracle/product/12.2.0.1'

Patching component oracle.oracore.rsf, 12.2.0.1.0...

Patching component oracle.ctx, 12.2.0.1.0...

Patching component oracle.rdbms.deconfig, 12.2.0.1.0...

Patching component oracle.rdbms.rsf, 12.2.0.1.0...
Deleting "kgcs.o" from archive "/u01/app/oracle/product/12.2.0.1/lib/libgeneric12.a"

Patching component oracle.rdbms.rman, 12.2.0.1.0...

Patching component oracle.rdbms, 12.2.0.1.0...

Patching component oracle.rdbms.util, 12.2.0.1.0...

Patching component oracle.rdbms.dbscripts, 12.2.0.1.0...

Patching component oracle.tfa, 12.2.0.1.0...

Patching component oracle.rdbms.rsf.ic, 12.2.0.1.0...
RollbackSession removing interim patch '26123830' from inventory
Log file location: /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/opatch2018-07-16_20-26-34PM_1.log

OPatch succeeded.
[oracle@localhost ~]$


6. List Patches

[oracle@localhost ~]$ /u01/app/oracle/product/12.2.0.1/OPatch/opatch lsinv -oh /u01/app/oracle/product/12.2.0.1 | grep -i "Database Release Update"
[oracle@localhost ~]$

[oracle@localhost ~]$ /u01/app/oracle/product/12.2.0.1/OPatch/opatch lsinv -oh /u01/app/oracle/product/12.2.0.1
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.2.0.1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.2.0.1/oraInst.loc
OPatch version    : 12.2.0.1.6
OUI version       : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/opatch2018-07-16_20-34-33PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/12.2.0.1/cfgtoollogs/opatch/lsinv/lsinventory2018-07-16_20-34-33PM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: localhost
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 12c                                                  12.2.0.1.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


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

OPatch succeeded.
[oracle@localhost ~]$


7. Startup CDB and ALL PDBs

[oracle@localhost ~]$ . oraenv
ORACLE_SID = [CDB2] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 16 20:37:27 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  8793208 bytes
Variable Size             503317384 bytes
Database Buffers         1056964608 bytes
Redo Buffers                7983104 bytes
Database mounted.
Database opened.
SQL>
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT <------
SQL>
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL>
SQL> col name for a30
SQL> select name,open_mode,con_id,dbid from v$containers;

NAME                           OPEN_MODE      CON_ID       DBID
------------------------------ ---------- ---------- ----------
CDB$ROOT                       READ WRITE          1  680230459
PDB$SEED                       READ ONLY           2 2993936271
PDB1                           READ WRITE          3  627484885
PDB2                           READ WRITE          4  891811039
PDB3                           READ WRITE          5  424568091
PDB4                           READ WRITE          6 2306285303

6 rows selected.

SQL>


8. Startup LISTENER

[oracle@localhost ~]$ ps -ef | grep tns
root        22     2  0 13:45 ?        00:00:00 [netns]
oracle   28991  3363  0 20:42 pts/0    00:00:00 grep tns
[oracle@localhost ~]$
[oracle@localhost ~]$ lsnrctl start LISTENER

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-JUL-2018 20:42:49

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.2.0.1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                16-JUL-2018 20:42:50
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@localhost ~]$
[oracle@localhost ~]$ ps -ef | grep tns
root        22     2  0 13:45 ?        00:00:00 [netns]
oracle   29004     1  0 20:42 ?        00:00:00 /u01/app/oracle/product/12.2.0.1/bin/tnslsnr LISTENER -inherit
oracle   29007  3363  0 20:43 pts/0    00:00:00 grep tns
[oracle@localhost ~]$

[oracle@localhost ~]$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-JUL-2018 20:44:04

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                16-JUL-2018 20:42:50
Uptime                    0 days 0 hr. 1 min. 14 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=localhost)(PORT=5501))(Security=(my_wallet_directory=/u01/app/oracle/admin/CDB2/xdb_wallet))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=5511))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=localhost)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/CDB2/xdb_wallet))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=5510))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "6f205af172040e0ce055000000000001" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "6f205af172050e0ce055000000000001" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "6f205af172060e0ce055000000000001" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "6f2c146104472ecee055000000000001" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "CDB2" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "CDB2XDB" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "pdb2" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "pdb3" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
Service "pdb4" has 1 instance(s).
  Instance "CDB2", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@localhost ~]$

9. Rollback SQL changes (datapatch)

[oracle@localhost ~]$ /u01/app/oracle/product/12.2.0.1/OPatch/datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Mon Jul 16 20:48:13 2018
Copyright (c) 2012, 2017, Oracle.  All rights reserved.

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

Connecting to database...OK
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)
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series DBRU:
  Not installed in the binary registry and ID 170718 in PDB CDB$ROOT, ID 170718 in PDB PDB$SEED, ID 170718 in PDB PDB1, ID 170718 in PDB PDB2, ID 170718 in PDB PDB3, ID 170718 in PDB PDB4

Adding patches to installation queue and performing prereq checks...
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED PDB1 PDB2 PDB3 PDB4
    The following patches will be rolled back:
      26123830 (DATABASE RELEASE UPDATE 12.2.0.1.170718)
    Nothing to apply

Installing patches...
Patch installation complete.  Total patches installed: 6

Validating logfiles...
Patch 26123830 rollback (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26123830/21405588/26123830_rollback_CDB2_CDBROOT_2018Jul16_20_50_01.log (no errors)
Patch 26123830 rollback (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26123830/21405588/26123830_rollback_CDB2_PDBSEED_2018Jul16_20_52_39.log (no errors)
Patch 26123830 rollback (pdb PDB1): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26123830/21405588/26123830_rollback_CDB2_PDB1_2018Jul16_20_52_39.log (no errors)
Patch 26123830 rollback (pdb PDB2): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26123830/21405588/26123830_rollback_CDB2_PDB2_2018Jul16_20_52_38.log (no errors)
Patch 26123830 rollback (pdb PDB3): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26123830/21405588/26123830_rollback_CDB2_PDB3_2018Jul16_20_52_39.log (no errors)
Patch 26123830 rollback (pdb PDB4): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26123830/21405588/26123830_rollback_CDB2_PDB4_2018Jul16_20_57_05.log (no errors)
SQL Patching tool complete on Mon Jul 16 20:58:33 2018
[oracle@localhost ~]$


10. Check Registry Status

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 16 21:00:20 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


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

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT <------
SQL>

SET LINESIZE 500
SET PAGESIZE 1000
SET SERVEROUT ON
SET LONG 2000000

COLUMN action_time FORMAT A25
COLUMN action FORMAT A10
COLUMN bundle_series FORMAT A4 
COLUMN comments FORMAT A30
COLUMN description FORMAT A40
COLUMN namespace FORMAT A20
COLUMN status FORMAT A10
COLUMN version FORMAT A10

SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time,
 action,
 status,
 description,
 version,
 patch_id,
 bundle_series
 FROM   sys.dba_registry_sqlpatch
 ORDER by action_time;
 
Output
 
 ACTION_TIME               ACTION     STATUS     DESCRIPTION                              VERSION      PATCH_ID BUND
------------------------- ---------- ---------- ---------------------------------------- ---------- ---------- ----
16-JUL-2018 17:11:09      APPLY      SUCCESS    DATABASE RELEASE UPDATE 12.2.0.1.170718  12.2.0.1     26123830 DBRU
16-JUL-2018 20:58:16      ROLLBACK   SUCCESS    DATABASE RELEASE UPDATE 12.2.0.1.170718  12.2.0.1     26123830 DBRU

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala