Mô tả:-
Trong bài viết này tôi sẽ hướng dẫn cách apply phiên bản mới nhất Oracle 19c Database Release Update Patch 32904851: DATABASE RELEASE UPDATE 19.12.0.0.0
Môi trường là single instancedatabase.
Ghi chú:
1) Đọc kỹ file readme trong Patch 32904851 – Database Release Update 19.12.0.0.210720
2) Download patch p32904851_190000_Linux-x86-64.zip
3) Đảm bảo phiên bản opatch tối thiểu là 12.2.0.1.25
Bước 1: Download Patch từ oracle support
p32904851_190000_Linux-x86-64.zip
p6880880_122010_Linux-x86-64.zip
Download from My Oracle Support patch 6880880
Bước 2: Copy patch tới DB Server
[oracle@Prod22 19c]$ cd /u02/JULY_PATCH_2021_dbaviet
[oracle@Prod22 JULY_PATCH_2021_dbaviet]$ ls -lrt
total 1486104
-rwxr-xr-x. 1 oracle oinstall 1399784341 Jul 27 18:43 p32904851_190000_Linux-x86-64.zip
-rwxr-xr-x. 1 oracle oinstall 121981878 Jul 27 18:45 p6880880_122010_Linux-x86-64.zip
Bước 3: Upgrade Opatch Tool từ 12.2.0.1.23 tới 12.2.0.1.25
[oracle@Prod22 OPatch]$ cd /u02/app/oracle/product/19.0.0/dbhome_1/OPatch
[oracle@Prod22 OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.23
OPatch succeeded.
[oracle@Prod22 OPatch]$ cd /u02/JULY_PATCH_2021_dbaviet
[oracle@Prod22 JULY_PATCH_2021_dbaviet]$ ls -lrt
total 1486104
-rwxr-xr-x. 1 oracle oinstall 1399784341 Jul 27 18:43 p32904851_190000_Linux-x86-64.zip
-rwxr-xr-x. 1 oracle oinstall 121981878 Jul 27 18:45 p6880880_122010_Linux-x86-64.zip
[oracle@Prod22 JULY_PATCH_2021_dbaviet]$ cp p6880880_122010_Linux-x86-64.zip $ORACLE_HOME
[oracle@Prod22 JULY_PATCH_2021_dbaviet]$ cd $ORACLE_HOME
[oracle@Prod22 dbhome_1]$ mv OPatch/ OPatch_BKP
[oracle@oraem01 19c]$ unzip p6880880_122010_Linux-x86-64.zip
Archive:p6880880_122010_Linux-x86-64.zip
creating: OPatch/
inflating: OPatch/emdpatch.pl
creating: OPatch/oracle_common/
creating: OPatch/oracle_common/modules/
inflating: OPatch/oracle_common/modules/com.oracle.glcm.common-logging_1.6.5.0.jar
inflating: OPatch/oracle_common/modules/common-logging-config.jar
creating: OPatch/oplan/
[oracle@Prod22 ~]$ cd $ORACLE_HOME/OPatch
[oracle@Prod22 OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.25
OPatch succeeded.
Bước 4: Kiểm tra trạng thái Patches trước khi apply sử dụng query
Kiểm tra trạng thái các thành phần và invalid:
Component check:
SET LINESIZE 500
SET PAGESIZE 1000
SET SERVEROUT ON
SET LONG 2000000
COLUMN action_time FORMAT A12
COLUMN action FORMAT A10
COLUMN comments FORMAT A30
COLUMN description FORMAT A60
COLUMN namespace FORMAT A20
COLUMN status FORMAT A10
SELECT TO_CHAR(action_time, ‘YYYY-MM-DD’) AS action_time,action,status,
description,patch_id FROM sys.dba_registry_sqlpatch ORDER by action_time;
col comp_id for a10
col version for a11
col status for a10
col comp_name for a37
select comp_id,comp_name,version,status from dba_registry;
Xác định Invalid Objects trước khi patching
COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = ‘INVALID’
ORDER BY owner, object_type, object_name;
Bước 5: Shutdown Database và Listener
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.11.0.0.0
[oracle@Prod22 ~]$ lsnrctl stop
LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 27-JUL-2021 18:58:13
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Prod22)(PORT=1521)))
The command completed successfully
Bước 6: Backup ORACLE_HOME và Database (để sau Rollback )
cd $ORACLE_HOME
tar -cvf oracle_home_27JUL_2021.tar $ORACLE_HOME
Kết nối tới rman take để thực hiệnfull backup
$ rman target /
RMAN> backup database plus archivelog;
Bước 7: Apply RU patch trên ORACLE_HOME 19c
oracle@Prod22 JULY_PATCH_2021_dbaviet]$ unzip p32904851_190000_Linux-x86-64.zip
oracle@Prod22 JULY_PATCH_2021_dbaviet]$ ls -lrt
total 1487056
drwxr-xr-x. 5 oracle oinstall 76 Jul 20 14:51 32904851
-rw-rw-r–. 1 oracle oinstall 972397 Jul 23 18:41 PatchSearch.xml
-rwxr-xr-x. 1 oracle oinstall 1399784341 Jul 27 18:43 p32904851_190000_Linux-x86-64.zip
-rwxr-xr-x. 1 oracle oinstall 121981878 Jul 27 18:45 p6880880_122
[oracle@Prod22 ~]$ export ORACLE_HOME=/u02/app/oracle/product/19.0.0/dbhome_1
[oracle@Prod22 ~]$ export PATH=/u02/app/oracle/product/19.0.0/dbhome_1/OPatch:$PATH
[oracle@Prod22 ~]$ opatch version
OPatch Version: 12.2.0.1.25
OPatch succeeded.
Login tới thư mục Patch:
[oracle@Prod22 32904851]$ pwd
/u02/JULY_PATCH_2021_dbaviet/32904851
[oracle@Prod22 32904851]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.25
Copyright (c) 2021, Oracle Corporation. All rights reserved.
Oracle Home : /u02/app/oracle/product/19.0.0/dbhome_1
Central Inventory : /u02/app/oraInventory
from : /u02/app/oracle/product/19.0.0/dbhome_1/oraInst.loc
OPatch version : 12.2.0.1.25
OUI version : 12.2.0.7.0
Log file location : /u02/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2021-07-27_19-11-19PM_1.log
Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 32904851
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 = ‘/u02/app/oracle/product/19.0.0/dbhome_1’)
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files…
Applying interim patch ‘32904851’ to OH ‘/u02/app/oracle/product/19.0.0/dbhome_1’
ApplySession: Optional component(s) [ oracle.network.gsm, 19.0.0.0.0 ] , [ oracle.rdbms.ic, 19.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 19.0.0.0.0 ] , [ oracle.tfa, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.options.olap.api, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 19.0.0.0.0 ] , [ oracle.jdk, 1.8.0.191.0 ] not present in the Oracle Home or a higher version is found.
Patching component oracle.perlint, 5.28.1.0.0…
Patching component oracle.rdbms.locator, 19.0.0.0.0…
Patching component oracle.rdbms.rsf, 19.0.0.0.0…
Patching component oracle.rdbms.util, 19.0.0.0.0…
Patching component oracle.rdbms, 19.0.0.0.0…
Patching component oracle.assistants.acf, 19.0.0.0.0…
Patching component oracle.assistants.deconfig, 19.0.0.0.0…
Patching component oracle.assistants.server, 19.0.0.0.0…
Patching component oracle.buildtools.rsf, 19.0.0.0.0…
Patching component oracle.ctx, 19.0.0.0.0…
Patching component oracle.dbjava.ic, 19.0.0.0.0…
Patching component oracle.dbjava.jdbc, 19.0.0.0.0…
Patching component oracle.dbjava.ucp, 19.0.0.0.0…
Patching component oracle.duma, 19.0.0.0.0…
Patching component oracle.javavm.client, 19.0.0.0.0…
Patching component oracle.ldap.owm, 19.0.0.0.0…
Patching component oracle.ldap.rsf, 19.0.0.0.0…
Patching component oracle.marvel, 19.0.0.0.0…
Patching component oracle.network.rsf, 19.0.0.0.0…
Patching component oracle.oracore.rsf, 19.0.0.0.0…
Patching component oracle.precomp.common.core, 19.0.0.0.0…
Patching component oracle.rdbms.dbscripts, 19.0.0.0.0…
Patching component oracle.rdbms.deconfig, 19.0.0.0.0…
Patching component oracle.rdbms.oci, 19.0.0.0.0…
Patching component oracle.rhp.db, 19.0.0.0.0…
Patching component oracle.sdo, 19.0.0.0.0…
Patching component oracle.mgw.common, 19.0.0.0.0…
Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0…
Patching component oracle.precomp.common, 19.0.0.0.0…
Patching component oracle.precomp.lang, 19.0.0.0.0…
Patching component oracle.jdk, 1.8.0.201.0…
Patch 32904851 successfully applied.
Sub-set patch [32545013] has become inactive due to the application of a super-set patch [32904851].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u02/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2021-07-27_19-11-19PM_1.log
OPatch succeeded.
Bước 8: Bật Database và Listener
[oracle@Prod22 32904851]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 27-JUL-2021 19:32:23
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Starting /u02/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait…
TNSLSNR for Linux: Version 19.0.0.0.0 – Production
System parameter file is /u02/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u02/app/oracle/diag/tnslsnr/Prod22/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Prod22)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Prod22)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 – Production
Start Date 27-JUL-2021 19:32:23
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u02/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u02/app/oracle/diag/tnslsnr/Prod22/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Prod22)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary…
Service “oradbwr” has 1 instance(s).
Instance “oradbwr”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
[oracle@Prod22 32904851]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Tue Jul 27 19:32:32 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2936008960 bytes
Fixed Size 8900864 bytes
Variable Size 469762048 bytes
Database Buffers 2449473536 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
Bước 9: Thực thi các bước sau khi patch và chạy lệnh datapatch
[oracle@Prod22 ~]$ cd $ORACLE_HOME/OPatch
[oracle@Prod22 OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.12.0.0.0 Production on Tue Jul 27 19:34:38 2021
Copyright (c) 2012, 2021, Oracle. All rights reserved.
Log file for this invocation: /u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_14075_2021_07_27_19_34_38/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 32399816 (OJVM RELEASE UPDATE: 19.11.0.0.210420 (32399816)):
Binary registry: Installed
SQL registry: Applied with errors on 18-JUN-21 10.09.16.972121 PM
Current state of release update SQL patches:
Binary registry:
19.12.0.0.0 Release_Update 210716141810: Installed
SQL registry:
Applied 19.11.0.0.0 Release_Update 210413004009 with errors on 18-JUN-21 10.09.16.785930 PM
Adding patches to installation queue and performing prereq checks…done
Installation queue:
No interim patches need to be rolled back
Patch 32545013 (Database Release Update : 19.11.0.0.210420 (32545013)):
Rollback from 19.11.0.0.0 Release_Update 210413004009 to 19.1.0.0.0 Feature Release
Patch 32904851 (Database Release Update : 19.12.0.0.210720 (32904851)):
Apply from 19.1.0.0.0 Feature Release to 19.12.0.0.0 Release_Update 210716141810
The following interim patches will be applied:
32399816 (OJVM RELEASE UPDATE: 19.11.0.0.210420 (32399816))
Installing patches…
Patch installation complete. Total patches installed: 3
Validating logfiles…done
Patch 32545013 rollback: SUCCESS
logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/32545013/24175065/32545013_rollback_ORADBWR_2021Jul27_19_37_33.log (no errors)
Patch 32904851 apply: SUCCESS
logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/32904851/24343243/32904851_apply_ORADBWR_2021Jul27_19_51_53.log (no errors)
Patch 32399816 apply: SUCCESS
logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/32399816/24099425/32399816_apply_ORADBWR_2021Jul27_19_35_41.log (no errors)
SQL Patching tool complete on Tue Jul 27 20:10:59 2021
Bước 10: Sau khi apply RU patch, kiểm tra DBA_REGISTRY_SQLPATCH
SET LINESIZE 500
SET PAGESIZE 1000
SET SERVEROUT ON
SET LONG 2000000
COLUMN action_time FORMAT A12
COLUMN action FORMAT A10
COLUMN patch_type FORMAT A10
COLUMN description FORMAT A32
COLUMN status FORMAT A10
COLUMN version FORMAT A10
spool check_patches_19c.txt
select CON_ID,
TO_CHAR(action_time, ‘YYYY-MM-DD’) AS action_time,
PATCH_ID,
PATCH_TYPE,
ACTION,
DESCRIPTION,
SOURCE_VERSION,
TARGET_VERSION
from CDB_REGISTRY_SQLPATCH
order by CON_ID, action_time, patch_id;
spool off
Bước 11: Kiểm tra opatch lsinventory và list các patches applied trong ORACLE_HOME
[oracle@Prod22 ~]$ opatch lspatches
32904851;Database Release Update : 19.12.0.0.210720 (32904851)
32399816;OJVM RELEASE UPDATE: 19.11.0.0.210420 (32399816)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
OPatch succeeded.
Recompile invalid Objects
SQL> select count(*) from all_objects where status = ‘INVALID’ group by owner;
COUNT(*)
———-
134
1
331
40
2
19
65
3
1
9 rows selected.
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
————————————————————————————————————————————————————————————————————————————————————————————————————
COMP_TIMESTAMP UTLRP_BGN 2021-07-27 19:57:57
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> Use the following queries to track recompilation progress:
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> 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> 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> 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> 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_%’;
PL/SQL procedure successfully completed.
TIMESTAMP
————————————————————————————————————————————————————————————————————————————————————————————————————
COMP_TIMESTAMP UTLRP_END 2021-07-27 20:01:32
DOC> The following query reports the number of invalid objects.
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.
OBJECTS WITH ERRORS
——————-
84
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.
ERRORS DURING RECOMPILATION
—————————
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL> select count(*) from all_objects where status = ‘INVALID’ group by owner;
no rows selected
Hy vọng hữu ích với bạn.
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile: 0902912888
⚡️ Skype: tranbinh48ca
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
=============================
học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,khóa học pl/sql, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle weblogic, middleware, hoc solaris, hoc linux, hoc aix, unix, oracle oca, oracle ocp, oracle ocm
