Mục đích:-

Trong bài việt này tôi sẽ hướng dẫnác bạn thủ tục apply Oracle Database 19c mới nhất là phiên bản Update 19.9.0.0.201020 (Patch 31771877)

Môi trường thực hiện là instance database đơn

Chú ý:-

1)Download patch p31771877_190000_Linux-x86-64.zip

2)Đọc lại file readme của Patch 31771877 – Database Release Update 19.9.0.0.201020

3)Đảm bảo opatch tối thiểu 12.2.0.1.19

Bước 1: Download Patch 31771877 từ oracle support

p31771877_190000_Linux-x86-64.zip

p6880880_200000_Linux-x86-64.zip

Download patch 6880880 từ trang My Oracle Support

Bước 2: Copy patch tới DB Server

[oracle@oraem01 soft]$ ls -lrt p31771877_190000_Linux-x86-64.zip p6880880_200000_Linux-x86-64.zip

-rwxrwxr-x. 1 oracle oinstall 1424692091 Nov 21 23:57 p31771877_190000_Linux-x86-64.zip

-rwxrwxr-x. 1 oracle oinstall 122119435 Nov 22 00:04 p6880880_200000_Linux-x86-64.zip

Bước 3: Upgrade Opatch Tool từ 12.2.0.1.17 lên 12.2.0.1.23

[oracle@oraem01 OPatch]$ cd /usr/local/oracle/19c/OPatch/

[oracle@oraem01 OPatch]$ ./opatch version

OPatch Version: 12.2.0.1.17

OPatch succeeded.

[oracle@oraem01 OPatch]$ cd /usr/local/oracle/soft

[oracle@oraem01 soft]$ cp p6880880_200000_Linux-x86-64.zip /usr/local/oracle/19c/

[oracle@oraem01 19c]$ pwd

/usr/local/oracle/19c

[oracle@oraem01 19c]$ mv OPatch/ OPatch_BKP

[oracle@oraem01 19c]$ unzip p6880880_200000_Linux-x86-64.zip

Archive: p6880880_200000_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@oraem01 19c]$ cd OPatch

[oracle@oraem01 OPatch]$ ./opatch version

OPatch Version: 12.2.0.1.23

OPatch succeeded.

Bước 4: Kiểm tra trạng thái Patch trước khi apply sử dụng query bên dưới

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 các Invalid Objects trước khi patch:

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.3.0.0.0

[oracle@oraem01 19c]$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 24-NOV-2020 03:17:22

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraem01)(PORT=1521)))

The command completed successfully

Bước 6 Backup ORACLE_HOME và Database (Để Rollback)

cd $ORACLE_HOME

tar -cvf oracle_home_24Nov_2020.tar $ORACLE_HOME

Connect with rman take full backup

Bước7: Apply RU patch trên ORACLE_HOME 19c

[oracle@oraem01 soft]$ unzip p31771877_190000_Linux-x86-64.zip

[oracle@oraem01 soft]$ cd 31771877

[oracle@oraem01 31771877]$ export ORACLE_HOME=/usr/local/oracle/19c/

[oracle@oraem01 31771877]$ export PATH=/usr/local/oracle/19c/OPatch:$PATH

[oracle@oraem01 31771877]$ opatch version

OPatch Version: 12.2.0.1.23

OPatch succeeded.

[oracle@oraem01 31771877]$ opatch apply

Oracle Interim Patch Installer version 12.2.0.1.23

Oracle Home : /usr/local/oracle/19c

Central Inventory : /u01/app/oracle/oraInventory

from : /usr/local/oracle/19c//oraInst.loc

OPatch version : 12.2.0.1.23

OUI version : 12.2.0.7.0

Log file location : /usr/local/oracle/19c/cfgtoollogs/opatch/opatch2020-11-24_03-24-11AM_1.log

Verifying environment and performing prerequisite checks…

OPatch continues with these patches: 31771877

Do you want to proceed? [y|n]

User Responded with: Y

All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = ‘/usr/local/oracle/19c’)

Is the local system ready for patching? [y|n]

User Responded with: Y

Backing up files…

Applying interim patch ‘31771877’ to OH ‘/usr/local/oracle/19c’

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.options.olap.awm, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.oraolap.mgmt, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.assistants.usm, 19.0.0.0.0 ] , [ oracle.assistants.asm, 19.0.0.0.0 ] , [ oracle.sqlj, 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.rdbms.rsf, 19.0.0.0.0…

Patching component oracle.rdbms, 19.0.0.0.0…

Patching component oracle.rdbms.util, 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.dbtoolslistener, 19.0.0.0.0…

Patching component oracle.ldap.rsf, 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.rdbms.dbscripts, 19.0.0.0.0…

Patching component oracle.rdbms.deconfig, 19.0.0.0.0…

Patching component oracle.sdo, 19.0.0.0.0…

Patching component oracle.sdo.locator.jrf, 19.0.0.0.0…

Patching component oracle.sqlplus, 19.0.0.0.0…

Patching component oracle.usm.deconfig, 19.0.0.0.0…

Patching component oracle.rdbms.crs, 19.0.0.0.0…

Patching component oracle.oraolap, 19.0.0.0.0…

Patching component oracle.precomp.rsf, 19.0.0.0.0…

Patching component oracle.javavm.client, 19.0.0.0.0…

Patching component oracle.precomp.common.core, 19.0.0.0.0…

Patching component oracle.network.client, 19.0.0.0.0…

Patching component oracle.rdbms.scheduler, 19.0.0.0.0…

Patching component oracle.ctx.atg, 19.0.0.0.0…

Patching component oracle.marvel, 19.0.0.0.0…

Patching component oracle.oraolap.dbscripts, 19.0.0.0.0…

Patching component oracle.ovm, 19.0.0.0.0…

Patching component oracle.bali.ice, 11.1.1.7.0…

Patching component oracle.ctx.rsf, 19.0.0.0.0…

Patching component oracle.rdbms.lbac, 19.0.0.0.0…

Patching component oracle.rdbms.oci, 19.0.0.0.0…

Patching component oracle.odbc, 19.0.0.0.0…

Patching component oracle.rdbms.rman, 19.0.0.0.0…

Patching component oracle.nlsrtl.rsf, 19.0.0.0.0…

Patching component oracle.rdbms.install.plugins, 19.0.0.0.0…

Patching component oracle.rdbms.drdaas, 19.0.0.0.0…

Patching component oracle.ldap.rsf.ic, 19.0.0.0.0…

Patching component oracle.ldap.security.osdt, 19.0.0.0.0…

Patching component oracle.network.listener, 19.0.0.0.0…

Patching component oracle.ons, 19.0.0.0.0…

Patching component oracle.rdbms.dv, 19.0.0.0.0…

Patching component oracle.javavm.server, 19.0.0.0.0…

Patching component oracle.xdk, 19.0.0.0.0…

Patching component oracle.ldap.owm, 19.0.0.0.0…

Patching component oracle.dbdev, 19.0.0.0.0…

Patching component oracle.rdbms.install.common, 19.0.0.0.0…

Patching component oracle.sdo.locator, 19.0.0.0.0…

Patching component oracle.duma, 19.0.0.0.0…

Patching component oracle.sqlplus.ic, 19.0.0.0.0…

Patching component oracle.xdk.rsf, 19.0.0.0.0…

Patching component oracle.xdk.parser.java, 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 31771877 successfully applied.

Sub-set patch [29517242] has become inactive due to the application of a super-set patch [31771877].

Please refer to Doc ID 2161861.1 for any possible further required actions.

Log file location: /usr/local/oracle/19c/cfgtoollogs/opatch/opatch2020-11-24_03-24-11AM_1.log

OPatch succeeded.

Bước 8: Bật Database và Listener

[oracle@oraem01 31771877]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 24-NOV-2020 04:27:34

Starting /usr/local/oracle/19c//bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 19.0.0.0.0 – Production

System parameter file is /usr/local/oracle/19c/network/admin/listener.ora

Log messages written to /usr/local/oracle/diag/tnslsnr/oraem01/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraem01.oriental.com)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraem01.oriental.com)(PORT=1521)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 19.0.0.0.0 – Production

Start Date 24-NOV-2020 04:27:34

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /usr/local/oracle/19c/network/admin/listener.ora

Listener Log File /usr/local/oracle/diag/tnslsnr/oraem01/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraem01.oriental.com)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

The listener supports no services

The command completed successfully

[oracle@oraem01 31771877]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Tue Nov 24 04:27:48 2020

Version 19.9.0.0.0

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 9865000808 bytes

Fixed Size 12445544 bytes

Variable Size 1509949440 bytes

Database Buffers 8321499136 bytes

Redo Buffers 21106688 bytes

Database mounted.

Database opened.

Bước 9: Thực hiện các bước sau khi patch và chạy lệnh datapatch

[oracle@oraem01 OPatch]$ ./datapatch -verbose

SQL Patching tool version 19.9.0.0.0 Production on Wed Nov 25 06:43:52 2020

Log file for this invocation: /usr/local/oracle/cfgtoollogs/sqlpatch/sqlpatch_97770_2020_11_25_06_43_52/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:

No interim patches found

Current state of release update SQL patches:

Binary registry:

19.9.0.0.0 Release_Update 200930183249: Installed

SQL registry:

Applied 19.3.0.0.0 Release_Update 190410122720 with errors on 24-NOV-20 02.27.36.072667 AM

Adding patches to installation queue and performing prereq checks…done

Installation queue:

No interim patches need to be rolled back

Patch 29517242 (Database Release Update : 19.3.0.0.190416 (29517242)):

Rollback from 19.3.0.0.0 Release_Update 190410122720 to 19.1.0.0.0 Feature Release

Patch 31771877 (Database Release Update : 19.9.0.0.201020 (31771877)):

Apply from 19.1.0.0.0 Feature Release to 19.9.0.0.0 Release_Update 200930183249

No interim patches need to be applied

Installing patches…

Patch installation complete. Total patches installed: 2

Validating logfiles…done

Patch 29517242 rollback: SUCCESS

logfile: /usr/local/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_rollback_OEMPROD_2020Nov25_06_44_12.log (no errors)

Patch 31771877 apply: SUCCESS

logfile: /usr/local/oracle/cfgtoollogs/sqlpatch/31771877/23869227/31771877_apply_OEMPROD_2020Nov25_06_44_50.log (no errors)

SQL Patching tool complete on Wed Nov 25 06:47:20 2020

Bước 10: Sau khi 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à liệt kê các patch được apply cho ORACLE_HOME

[oracle@oraem01 ~]$ cd $ORACLE_HOME/OPatch

[oracle@oraem01 OPatch]$ ./opatch lspatches

31771877;Database Release Update : 19.9.0.0.201020 (31771877)

29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

OPatch succeeded.

[oracle@oraem01 OPatch]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Wed Nov 25 12:09:26 2020

Version 19.9.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

Version 19.9.0.0.0

SQL> select owner, count(*) count from all_objects where status = ‘INVALID’ group by owner;

OWNER COUNT

—— —-

SYSMAN 4

SQL> @?/rdbms/admin/utlrp.sql

Session altered.

TIMESTAMP

——————————————————————————–

COMP_TIMESTAMP UTLRP_BGN 2020-11-25 12:10:00

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 2020-11-25 12:10: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

——————-

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

—————————

Function created.

PL/SQL procedure successfully completed.

Function dropped.

PL/SQL procedure successfully completed.

SQL> select owner, count(*) count from all_objects where status = ‘INVALID’ group by owner;

no rows selected

Hy vọng hữu ích cho 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

=============================
Thủ tục Apply Patchingtrên Oracle Database 19c Release Update 19.9.0.0.201020, 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

Chủ Nhật, 14 tháng 11, 2021

Thủ tục Apply Patchingtrên Oracle Database 19c Release Update 19.9.0.0.201020

Mục đích:-

ĐỌC NHIỀU