سلام
انواع محتلفی 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
موفق و پیروز باشید.
