سلام

انواع محتلفی patch داریم که توضیحاتش در پست دیگه داده میشه .

 امروز میخواهیم patch RUR رو روی دیتابیس 18.3 میزنیم تا نسخه به 18.3.2 ارتقا پیدا کنه 

ابتدا که وارد sqlplusبشیم نسخه دیتابیس رو هم میکه با هم ببینیم

[oracle@testupgradedb ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Mar 17 22:50:42 2019
Version 18.3.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL>
خب ما میخواهیم patch شماره 28790643 را بروی دیتابیس خودمون اعمال کنیم



خب ببینیم ورژن دیتابیس چنده 

[oracle@testupgradedb ~]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Mon Mar 18 02:14:14 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL>
خب وقتی read me patch رو میخونیم میگه که باید opatch utility ما حداقل 12.2.0.1.14 باشه که این opatch  ما این ورژن هست
[oracle@testupgradedb  ~]$ /oracle/product/18.3/db_1/OPatch/opatch version
OPatch Version: 12.2.0.1.14

OPatch succeeded.

خب patch دانلود شده رو به سرور خودمون انتقال میدم و unzip  میکنیم
 برای شورع کار باید ابتدا پیش نیاز های اون رو بررسی کنیم
[oracle@testupgradedb 28790643]$ /oracle/product/18.3/db_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.14
Copyright (c) 2019, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /oracle/product/18.3/db_1
Central Inventory : /oracle/oraInventory
   from           : /oracle/product/18.3/db_1/oraInst.loc
OPatch version    : 12.2.0.1.14
OUI version       : 12.2.0.4.0
Log file location : /oracle/product/18.3/db_1/cfgtoollogs/opatch/opatch2019-03-18_02-06-19AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

خب میبینیم که مشکلی نداریم .در read me  patch گفته شده که باید دیتابیس و لیسنر پاین باشه 
[oracle@testupgradedb 28790643]$ lsnrctl  stop

[oracle@testupgradedb 28790643]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Mar 18 02:38:19 2019
Version 18.3.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
حالا وقت apply  کردن patch هست
[oracle@testupgradedb 28790643]$ /oracle/product/18.3/db_1/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.14
Copyright (c) 2019, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/product/18.3/db_1
Central Inventory : /oracle/oraInventory
   from           : /oracle/product/18.3/db_1/oraInst.loc
OPatch version    : 12.2.0.1.14
OUI version       : 12.2.0.4.0
Log file location : /oracle/product/18.3/db_1/cfgtoollogs/opatch/opatch2019-03-18_02-48-44AM_1.log

Verifying environment and performing prerequisite checks...
Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:


Following executables are active :
/oracle/product/18.3/db_1/lib/libclntsh.so.18.1
/oracle/product/18.3/db_1/lib/libsqlplus.so
UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.
Log file location: /oracle/product/18.3/db_1/cfgtoollogs/opatch/opatch2019-03-18_02-48-44AM_1.log

OPatch failed with error code 73
اگه در زدن patch  با خطای برخورد کردید بذونید که library های اوراکل در حال استاده هست من به این صورت حل کردم
[oracle@testupgradedb ~]$ fuser /oracle/product/18.3/db_1/lib/libsqlplus.so
/oracle/product/18.3/db_1/lib/libsqlplus.so:  5020m
[oracle@testupgradedb ~]$ ps aux|grep 5020
oracle    4404  0.0  0.0 103388  2204 pts/0    S+   02:50   0:00 grep 5020
oracle    5020  0.0  0.3 117704 21944 pts/2    S+   Mar17   0:00 sqlplus   as sysdba
[oracle@testupgradedb ~]$ kill -9 5020
[oracle@testupgradedb ~]$ fuser /oracle/product/18.3/db_1/lib/libsqlplus.so

خب حالا patch apply رو میزنیم جهت اعمال شدن patch
[oracle@testupgradedb 28790643]$ /oracle/product/18.3/db_1/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.14
Copyright (c) 2019, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/product/18.3/db_1
Central Inventory : /oracle/oraInventory
   from           : /oracle/product/18.3/db_1/oraInst.loc
OPatch version    : 12.2.0.1.14
OUI version       : 12.2.0.4.0
Log file location : /oracle/product/18.3/db_1/cfgtoollogs/opatch/opatch2019-03-18_02-51-08AM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   28790643

Do you want to proceed? [y|n]                                                                                                                               
Y
User Responded with: Y
All checks passed.

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


Is the local system ready for patching? [y|n]
Y
User Responded with: Y
Backing up files...
Applying interim patch '28790643' to OH '/oracle/product/18.3/db_1'
ApplySession: Optional component(s) [ oracle.network.cman, 18.0.0.0.0 ] , [ oracle.net.cman, 18.0.0.0.0 ] , [ oracle.assistants.asm, 18.0.0.0.0 ] , [ oracle.ons.daemon, 18.0.0.0.0 ] , [ oracle.crs, 18.0.0.0.0 ] , [ oracle.assistants.usm, 18.0.0.0.0 ] , [ oracle.tfa, 18.0.0.0.0 ] , [ oracle.assistants.server.oui, 18.0.0.0.0 ] , [ oracle.has.crs, 18.0.0.0.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.ctx.rsf, 18.0.0.0.0...

Patching component oracle.ons, 18.0.0.0.0...

Patching component oracle.nlsrtl.rsf, 18.0.0.0.0...

Patching component oracle.dbjava.ucp, 18.0.0.0.0...

Patching component oracle.sdo.locator, 18.0.0.0.0...

Patching component oracle.nlsrtl.rsf.core, 18.0.0.0.0...

Patching component oracle.xdk, 18.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 18.0.0.0.0...

Patching component oracle.rdbms.rsf, 18.0.0.0.0...

Patching component oracle.rdbms.util, 18.0.0.0.0...

Patching component oracle.rdbms.rman, 18.0.0.0.0...

Patching component oracle.xdk.rsf, 18.0.0.0.0...

Patching component oracle.network.client, 18.0.0.0.0...

Patching component oracle.rdbms.rsf.ic, 18.0.0.0.0...

Patching component oracle.assistants.deconfig, 18.0.0.0.0...

Patching component oracle.oraolap.dbscripts, 18.0.0.0.0...

Patching component oracle.xdk.parser.java, 18.0.0.0.0...

Patching component oracle.rdbms.deconfig, 18.0.0.0.0...

Patching component oracle.assistants.server, 18.0.0.0.0...

Patching component oracle.rdbms.install.plugins, 18.0.0.0.0...

Patching component oracle.network.rsf, 18.0.0.0.0...

Patching component oracle.install.deinstalltool, 18.0.0.0.0...

Patching component oracle.sdo.locator.jrf, 18.0.0.0.0...

Patching component oracle.oracore.rsf, 18.0.0.0.0...

Patching component oracle.assistants.acf, 18.0.0.0.0...

Patching component oracle.ctx.atg, 18.0.0.0.0...

Patching component oracle.dbjava.jdbc, 18.0.0.0.0...

Patching component oracle.rdbms, 18.0.0.0.0...

Patching component oracle.dbjava.ic, 18.0.0.0.0...

Patching component oracle.server, 18.0.0.0.0...

Patching component oracle.rdbms.crs, 18.0.0.0.0...

Patching component oracle.sqlplus, 18.0.0.0.0...

Patching component oracle.sdo, 18.0.0.0.0...

Patching component oracle.network.listener, 18.0.0.0.0...

Patching component oracle.rdbms.oci, 18.0.0.0.0...

Patching component oracle.ctx, 18.0.0.0.0...

Patching component oracle.precomp.rsf, 18.0.0.0.0...

Patching component oracle.sqlplus.ic, 18.0.0.0.0...

Patching component oracle.javavm.client, 18.0.0.0.0...

Patching component oracle.ldap.owm, 18.0.0.0.0...

Patching component oracle.ldap.security.osdt, 18.0.0.0.0...

Patching component oracle.precomp.lang, 18.0.0.0.0...

Patching component oracle.precomp.common, 18.0.0.0.0...
Patch 28790643 successfully applied.
Sub-set patch [28090523] has become inactive due to the application of a super-set patch [28790643].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /oracle/product/18.3/db_1/cfgtoollogs/opatch/opatch2019-03-18_02-51-08AM_1.log

OPatch succeeded.
حالا لیسنر رو start میکنیم
[oracle@testupgradedb 28790643]$ lsnrctl start

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 18-MAR-2019 02:58:03

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

Starting /oracle/product/18.3/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Log messages written to /oracle/oracle_base/diag/tnslsnr/testupgradedb/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testupgradedb)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                18-MAR-2019 02:58:03
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /oracle/oracle_base/diag/tnslsnr/testupgradedb/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testupgradedb)(PORT=1521)))
The listener supports no services
The command completed successfully
دیتابیس را هم start میکنیم
[oracle@testupgradedb 28790643]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Mar 18 02:59:04 2019
Version 18.3.2.0.0

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1962930464 bytes
Fixed Size                  8659232 bytes
Variable Size             570425344 bytes
Database Buffers         1375731712 bytes
Redo Buffers                8114176 bytes
Database mounted.
Database opened.
SQL>
فقط در ادامه ما باید datapatch رو هم طبق گفته readme patch اعمال کنیم


[oracle@testupgradedb OPatch]$ ./datapatch -verbose
SQL Patching tool version 18.0.0.0.0 Production on Mon Mar 18 03:00:44 2019
Copyright (c) 2012, 2018, Oracle.  All rights reserved.

Log file for this invocation: /oracle/oracle_base/cfgtoollogs/sqlpatch/sqlpatch_9150_2019_03_18_03_00_44/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
Interim patch 27923415 (OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)):
  Binary registry: Installed
  SQL registry: Applied successfully on 17-MAR-19 04.03.58.637986 AM

Current state of release update SQL patches:
  Binary registry:
    18.3.2.0.0 Release_Update_Revision 1811212344: Installed
  SQL registry:
    Applied 18.3.0.0.0 Release_Update 1806280943 successfully on 17-MAR-19 04.00.21.459114 AM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  No interim patches need to be rolled back
  Patch 28790643 (Database Release Update Revision : 18.3.2.0.190115 (28790643)):
    Apply from 18.3.0.0.0 Release_Update 1806280943 to 18.3.2.0.0 Release_Update_Revision 1811212344
  No interim patches need to be applied

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

Validating logfiles...done
Patch 28790643 apply: SUCCESS
  logfile: /oracle/oracle_base/cfgtoollogs/sqlpatch/28790643/22605424/28790643_apply_ORCLTEST_2019Mar18_03_01_00.log (no errors)
SQL Patching tool complete on Mon Mar 18 03:01:28 2019
خب در زمان apply کردن patch  ممکنه که Object های invalid شده باشن که ما با این دستور recompaile میکنیم
[oracle@testupgradedb OPatch]$ !sql
sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Mar 18 03:04:59 2019
Version 18.3.2.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.2.0.0

SQL>  @?/rdbms/admin/utlrp

Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2019-03-18 03:05:02

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2019-03-18 03:05:03

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>       logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL>

و با دستور زیر چک میکنیم invalid object داریم یا نه
SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
         0

SQL>
خب برای چک کردن اخرین patch  های اعمالی به dba_registry_sqlpatch select   میزنیم
SQL> select STATUS,DESCRIPTION,SOURCE_VERSION,TARGET_VERSION from dba_registry_sqlpatch
  2  ;

STATUS                    DESCRIPTION                                                                                          SOURCE_VERSION  TARGET_VERSION
------------------------- ---------------------------------------------------------------------------------------------------- --------------- ---------------
SUCCESS                   Database Release Update : 18.3.0.0.180717 (28090523)                                                 18.1.0.0.0      18.3.0.0.0
SUCCESS                   OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)                                                      18.3.0.0.0      18.3.0.0.0
SUCCESS                   Database Release Update Revision : 18.3.2.0.190115 (28790643)                                        18.3.0.0.0      18.3.2.0.0

چک کردن ورژن دیتابیس
SQL> select BANNER,BANNER_FULL from v$version;

BANNER                                                                           BANNER_FULL
-------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production           Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
                                                                                 Version 18.3.2.0.0





موفق و پیروز باشید.