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)
____________________________________________________________________________________________________
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
[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]$
[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 ~]$
[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 ~]$
[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 ~]$
[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>
[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 ~]$
[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