转载

【PATCH】Oracle12c升级补丁初体验

环境介绍： rhel7.3 x64 Oracle12.2.0.1

首先，我们看一下当前数据库环境：

点击(此处)折叠或打开

SQL> select NAME, DECODE(CDB, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option ?" , OPEN_MODE, CON_ID from V$DATABASE;
NAME Multitenant Option ? OPEN_MODE CON_ID
-------------------- -------------------------- -------------------- ----------
FIRSOUL Multitenant Option enabled READ WRITE 0
SQL> col open_time for a40
SQL> select con_id,dbid,name,open_mode,open_time,creation_time from v$pdbs;
CON_ID DBID NAME OPEN_MODE OPEN_TIME CREATION_TIME
---------- ---------- -------------------- ---------- ---------------------------------------- -------------------
2 1858591033 PDB$SEED READ ONLY 13-AUG-17 03.04.04.538 PM +08:00 2017-03-09 21:22:34
3 839809039 FIRSOULDBPDB MOUNTED 2017-03-09 21:26:54
4 4175248251 FIRSOUL01 READ WRITE 13-AUG-17 03.04.12.357 PM +08:00 2017-03-14 10:18:37

我们可以使用一下命令去检查一下数据库是否更新过补丁：

点击(此处)折叠或打开

SQL> col action_time format a30
SQL> col namespace for a15
SQL> col comments for a40
SQL> col version for a15
SQL> col action for a10
SQL> select action_time,action,namespace,version,comments from dba_registry_history;
ACTION_TIME ACTION NAMESPACE VERSION COMMENTS
------------------------------ ---------- --------------- --------------- ----------------------------------------
BOOTSTRAP DATAPATCH 12.2.0.1 RDBMS_12.2.0.1.0_LINUX.X64_170125
SQL> select patch_id,patch_uid,version,flags,action,status,action_time,description,bundle_series from dba_registry_sqlpatch;
no rows selected -- 请注意，12c之后多了这个视图，而且12.2.0.1 之后，补丁跟新后，也会更新到该视图中

再次通过opatch命令检查：

点击(此处)折叠或打开

[oracle@test12 OPatch]$ ./opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2017, Oracle Corporation. All rights reserved.
Oracle Home : /oracle/app/oracle/product/12.2.0/dbhome_1
Central Inventory : /oracle/app/oraInventory
from : /oracle/app/oracle/product/12.2.0/dbhome_1/oraInst.loc
OPatch version : 12.2.0.1.6 --版本太低，需要下载安装在
OUI version : 12.2.0.1.4
Log file location : /oracle/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatch/opatch2017-08-13_15-22-54PM_1.log
Lsinventory Output file location : /oracle/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2017-08-13_15-22-54PM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: test12
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@test12 OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.6
OPatch succeeded.

检查完毕，开始折腾
下载并上传相关补丁包，此处忽略

更新OPatch目录，也就是更新它的版本

点击(此处)折叠或打开

[oracle@test12 soft]$ unzip p6880880_122010_Linux-x86-64.zip -d $ORACLE_HOME$ORACLE_HOME/OPatch/opatch version^C
[oracle@test12 soft]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.9
OPatch succeeded.
[oracle@test12 soft]$ $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME
Oracle Interim Patch Installer version 12.2.0.1.9
Copyright (c) 2017, Oracle Corporation. All rights reserved.
Oracle Home : /oracle/app/oracle/product/12.2.0/dbhome_1
Central Inventory : /oracle/app/oraInventory
from : /oracle/app/oracle/product/12.2.0/dbhome_1/oraInst.loc
OPatch version : 12.2.0.1.9 -- OPatch版本提高了
OUI version : 12.2.0.1.4
Log file location : /oracle/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatch/opatch2017-08-13_15-28-01PM_1.log
Lsinventory Output file location : /oracle/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2017-08-13_15-28-01PM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: test12
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.
Installed Products (128):
Assistant Common Files 12.2.0.1.0
BLASLAPACK Component 12.2.0.1.0
Buildtools Common Files 12.2.0.1.0
Cluster Verification Utility Common Files 12.2.0.1.0
Database Configuration and Upgrade Assistants 12.2.0.1.0
Database Migration Assistant for Unicode 12.2.0.1.0
Database SQL Scripts 12.2.0.1.0
Database Workspace Manager 12.2.0.1.0
DB TOOLS Listener 12.2.0.1.0
Deinstallation Tool 12.2.0.1.0
Enterprise Edition Options 12.2.0.1.0
Expat libraries 2.0.1.0.3
Generic Connectivity Common Files 12.2.0.1.0
Hadoopcore Component 12.2.0.1.0
HAS Common Files 12.2.0.1.0
HAS Files for DB 12.2.0.1.0
Installation Common Files 12.2.0.1.0
Installation Plugin Files 12.2.0.1.0
Installer SDK Component 12.2.0.1.4
JAccelerator (COMPANION) 12.2.0.1.0
Java Development Kit 1.8.0.91.0
LDAP Required Support Files 12.2.0.1.0
OLAP SQL Scripts 12.2.0.1.0
Oracle Advanced Analytics 12.2.0.1.0
Oracle Advanced Security 12.2.0.1.0
Oracle Application Express 12.2.0.1.0
Oracle Bali Share 11.1.1.6.0
Oracle Call Interface (OCI) 12.2.0.1.0
Oracle Clusterware RDBMS Files 12.2.0.1.0
Oracle Configuration Manager 12.1.2.0.0
Oracle Configuration Manager Client 10.3.2.1.0
Oracle Configuration Manager Deconfiguration 10.3.1.0.0
Oracle Context Companion 12.2.0.1.0
Oracle Core Required Support Files 12.2.0.1.0
Oracle Core Required Support Files for Core DB 12.2.0.1.0
Oracle Database 12c 12.2.0.1.0
Oracle Database 12c 12.2.0.1.0
Oracle Database 12c Multimedia Files 12.2.0.1.0
Oracle Database Deconfiguration 12.2.0.1.0
Oracle Database Gateway for ODBC 12.2.0.1.0
Oracle Database Utilities 12.2.0.1.0
Oracle Database Vault option 12.2.0.1.0
Oracle DBCA Deconfiguration 12.2.0.1.0
Oracle Extended Windowing Toolkit 11.1.1.6.0
Oracle Globalization Support 12.2.0.1.0
Oracle Globalization Support 12.2.0.1.0
Oracle Globalization Support For Core 12.2.0.1.0
Oracle Help for Java 11.1.1.7.0
Oracle Help Share Library 11.1.1.7.0
Oracle Ice Browser 11.1.1.7.0
Oracle Internet Directory Client 12.2.0.1.0
Oracle Java Client 12.2.0.1.0
Oracle JDBC Server Support Package 12.2.0.1.0
Oracle JDBC/OCI Instant Client 12.2.0.1.0
Oracle JDBC/THIN Interfaces 12.2.0.1.0
Oracle JFC Extended Windowing Toolkit 11.1.1.6.0
Oracle JVM 12.2.0.1.0
Oracle JVM For Core 12.2.0.1.0
Oracle Label Security 12.2.0.1.0
Oracle LDAP administration 12.2.0.1.0
Oracle Locale Builder 12.2.0.1.0
Oracle Message Gateway Common Files 12.2.0.1.0
Oracle Multimedia 12.2.0.1.0
Oracle Multimedia Client Option 12.2.0.1.0
Oracle Multimedia Java Advanced Imaging 12.2.0.1.0
Oracle Multimedia Locator 12.2.0.1.0
Oracle Multimedia Locator Java Required Support Files 12.2.0.1.0
Oracle Multimedia Locator RDBMS Files 12.2.0.1.0
Oracle Net 12.2.0.1.0
Oracle Net Listener 12.2.0.1.0
Oracle Net Required Support Files 12.2.0.1.0
Oracle Net Services 12.2.0.1.0
Oracle Netca Client 12.2.0.1.0
Oracle Notification Service 12.2.0.1.0
Oracle Notification Service for Instant Client 12.2.0.1.0
Oracle ODBC Driver 12.2.0.1.0
Oracle ODBC Driverfor Instant Client 12.2.0.1.0
Oracle OLAP 12.2.0.1.0
Oracle OLAP API 12.2.0.1.0
Oracle OLAP RDBMS Files 12.2.0.1.0
Oracle One-Off Patch Installer 12.2.0.1.6
Oracle Partitioning 12.2.0.1.0
Oracle Programmer 12.2.0.1.0
Oracle R Enterprise Server Files 12.2.0.1.0
Oracle RAC Deconfiguration 12.2.0.1.0
Oracle RAC Required Support Files-HAS 12.2.0.1.0
Oracle Real Application Testing 12.2.0.1.0
Oracle Recovery Manager 12.2.0.1.0
Oracle Scheduler Agent 12.2.0.1.0
Oracle Security Developer Tools 12.2.0.1.0
Oracle Spatial and Graph 12.2.0.1.0
Oracle SQL Developer 12.2.0.1.0
Oracle Starter Database 12.2.0.1.0
Oracle Text 12.2.0.1.0
Oracle Text ATG Language Support Files 12.2.0.1.0
Oracle Text Required Support Files 12.2.0.1.0
Oracle Universal Connection Pool 12.2.0.1.0
Oracle Universal Installer 12.2.0.1.4
Oracle USM Deconfiguration 12.2.0.1.0
Oracle Wallet Manager 12.2.0.1.0
Oracle XML Development Kit 12.2.0.1.0
Oracle XML Query 12.2.0.1.0
oracle.swd.commonlogging 13.3.0.0.0
oracle.swd.opatchautodb 12.2.0.1.5
oracle.swd.oui.core.min 12.2.0.1.4
Parser Generator Required Support Files 12.2.0.1.0
Perl Interpreter 5.22.0.0.0
Perl Modules 5.22.0.0.0
PL/SQL 12.2.0.1.0
PL/SQL Embedded Gateway 12.2.0.1.0
Platform Required Support Files 12.2.0.1.0
Precompiler Common Files 12.2.0.1.0
Precompiler Common Files for Core 12.2.0.1.0
Precompiler Required Support Files 12.2.0.1.0
Precompilers 12.2.0.1.0
RDBMS Required Support Files 12.2.0.1.0
RDBMS Required Support Files for Instant Client 12.2.0.1.0
Required Support Files 12.2.0.1.0
Secure Socket Layer 12.2.0.1.0
SQL*Plus 12.2.0.1.0
SQL*Plus Files for Instant Client 12.2.0.1.0
SQL*Plus Required Support Files 12.2.0.1.0
SQLJ Runtime 12.2.0.1.0
SSL Required Support Files for InstantClient 12.2.0.1.0
Tracle File Analyzer 12.2.0.1.0
XDK Required Support Files 12.2.0.1.0
XML Parser for Java 12.2.0.1.0
XML Parser for Oracle JVM 12.2.0.1.0
There are 128 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.

产品清单检查，及冲突检查

点击(此处)折叠或打开

$ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME
create a.txt
/home/oracle/soft/26129945/25983138
$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /home/oracle/soft/a.txt

停止数据库及相关服务，开始应用补丁，这里的补丁集也有GI的，单机环境，所以只选择数据库的即可

点击(此处)折叠或打开

cd /home/oracle/soft/26129945/25983138
$ORACLE_HOME/OPatch/opatch apply

应用完毕后，我们查看一下数据库字典信息，发现什么都没有……

opatch检查显示更新完毕，但对于数据库来说，没用……

点击(此处)折叠或打开

[oracle@test12 25983138]$ $ORACLE_HOME/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.9
Copyright (c) 2017, Oracle Corporation. All rights reserved.
Oracle Home : /oracle/app/oracle/product/12.2.0/dbhome_1
Central Inventory : /oracle/app/oraInventory
from : /oracle/app/oracle/product/12.2.0/dbhome_1/oraInst.loc
OPatch version : 12.2.0.1.9
OUI version : 12.2.0.1.4
Log file location : /oracle/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatch/opatch2017-08-13_15-37-24PM_1.log
Lsinventory Output file location : /oracle/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2017-08-13_15-37-24PM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: test12
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 25983138 : applied on Sun Aug 13 15:35:57 CST 2017
Unique Patch ID: 21358214
Patch description: "DATABASE BUNDLE PATCH: 12.2.0.1.170620 (25983138)"
Created on 18 Jun 2017, 23:01:52 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
--------------------------------------------------------------------------------
OPatch succeeded.

其实等于修改后没有将sql文件更新到数据库中
下面开始更新sql文本

点击(此处)折叠或打开

首先打开所有pdb，当然你不想也没办法
alter pluggable database all open;
开始应用sql
[oracle@test12 OPatch]$ ./datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Sun Aug 13 15:41:24 2017
Copyright (c) 2012, 2017, Oracle. All rights reserved.
Log file for this invocation: /oracle/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_6995_2017_08_13_15_41_24/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 DBBP:
ID 170620 in the binary registry and not installed in any PDB
Adding patches to installation queue and performing prereq checks...
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED FIRSOULDBPDB FIRSOUL01
Nothing to roll back
The following patches will be applied:
25983138 (DATABASE BUNDLE PATCH 12.2.0.1.170620)
Installing patches...
Patch installation complete. Total patches installed: 4
Validating logfiles...
Patch 25983138 apply (pdb CDB$ROOT): SUCCESS
logfile: /oracle/app/oracle/cfgtoollogs/sqlpatch/25983138/21358214/25983138_apply_FIRSOUL_CDBROOT_2017Aug13_15_41_45.log (no errors)
Patch 25983138 apply (pdb PDB$SEED): SUCCESS
logfile: /oracle/app/oracle/cfgtoollogs/sqlpatch/25983138/21358214/25983138_apply_FIRSOUL_PDBSEED_2017Aug13_15_42_16.log (no errors)
Patch 25983138 apply (pdb FIRSOULDBPDB): SUCCESS
logfile: /oracle/app/oracle/cfgtoollogs/sqlpatch/25983138/21358214/25983138_apply_FIRSOUL_FIRSOULDBPDB_2017Aug13_15_42_16.log (no errors)
Patch 25983138 apply (pdb FIRSOUL01): SUCCESS
logfile: /oracle/app/oracle/cfgtoollogs/sqlpatch/25983138/21358214/25983138_apply_FIRSOUL_FIRSOUL01_2017Aug13_15_42_16.log (no errors)
SQL Patching tool complete on Sun Aug 13 15:48:33 2017

下次查看数据库字典

点击(此处)折叠或打开

SQL> col action_time format a30
SQL> col namespace for a15
SQL> col comments for a40
SQL> col version for a15
SQL> col action for a10
SQL> select action_time,action,namespace,version,comments from dba_registry_history;
ACTION_TIME ACTION NAMESPACE VERSION COMMENTS
------------------------------ ---------- --------------- --------------- ----------------------------------------
BOOTSTRAP DATAPATCH 12.2.0.1 RDBMS_12.2.0.1.0_LINUX.X64_170125
依然不显示…… 什么情况

哦，对了，Oracle12c还有一个视图…… 补丁已更新：DATABASE BUNDLE PATCH 12.2.0.1.170620

点击(此处)折叠或打开

SQL>
SQL> col status for a8
SQL> col description for a40
SQL> col version for a10
SQL> select patch_id,patch_uid,version,action,status,action_time,description from dba_registry_sqlpatch;
PATCH_ID PATCH_UID VERSION ACTION STATUS ACTION_TIME DESCRIPTION
---------- ---------- ---------- ---------- -------- ------------------------------ ----------------------------------------
25983138 21358214 12.2.0.1 APPLY SUCCESS 13-AUG-17 03.48.32.294218 PM DATABASE BUNDLE PATCH 12.2.0.1.170620
1 row selected.