签到成功

知道了

CNDBA社区CNDBA社区

Oracle 18c 单实例 RUR 从18.3.0 升级到18.3.1 操作手册

2018-11-15 17:05 3191 0 原创 Oracle 18c
作者： dave
本文链接：https://www.cndba.cn/dave/article/3138

1. 18c 补丁版本说明

Oracle 从18c 开始，版本号和补丁结构发生了明显的变化，新的补丁体系里只有RU和RUR的概念。版本号也只有前三位有效，如下图：

  • 第一位数字是Oracle数据库的主版本号，比如Oracle 18c，12c。 从Oracle 18c开始，第一个数字表示Oracle数据库版本发布的最初的年份，比如2018年是Oracle 18c(18.0.0.0.0)的最初发布年份。
  • 第二位数字是Oracle RU（Release Update）的发布季度。比如18.3。
  • 第三位数字是Oracle RUR（release updates revision）的发布季度，比如18.1.1，18.2.1，18.3.0。

所以从18c 开始，我们打补丁，实际上打的RU或者RUR。 本文档我们看Oracle 18c中如何打RUR的补丁。 即从18.3.0 升级到18.3.1。 具体的Patch可以从MOS上下载。https://www.cndba.cn/dave/article/3138

RU如下：

RUR如下：https://www.cndba.cn/dave/article/3138

2. 当前DB环境

[dave@www.cndba.cn ~]$ lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 14-NOV-2018 04:52:28

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=18cDG1)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                14-NOV-2018 02:20:50
Uptime                    0 days 2 hr. 31 min. 37 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/18.3.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/18cDG1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=18cDG1)(PORT=1522)))
Services Summary...
Service "cndba" has 1 instance(s).
  Instance "cndba", status UNKNOWN, has 1 handler(s) for this service...
Service "dave" has 1 instance(s).
  Instance "cndba", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[dave@www.cndba.cn ~]$

[dave@www.cndba.cn ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Nov 14 04:58:25 2018
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> select banner_full from v$version;

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

SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 DAVE               READ WRITE NO
SQL>

3. 开始打RUR

Oracle patch的压缩包里都会有一个readme 文件，可以查看该文件，了解打Patch的步骤。

3.1 检查OPatch工具版本

在Oracle 18c的RUR，OPatch的版本必须大于12.2.0.1.14. 最新版本的Opatch可以从MOS 6880880上下载。 目前最新版是OPatch 12.2.0.1.16 for DB 18.x releases (OCT 2018)。

[dave@www.cndba.cn OPatch]$ pwd
/u01/app/oracle/product/18.3.0/db_1/OPatch
[dave@www.cndba.cn OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.14

OPatch succeeded.
[dave@www.cndba.cn OPatch]$

18c中默认版本就是12.2.0.1.14，所以不用升级Opatch，如果要申请，可以参考我的博客：
Oracle 更新 OPatch 工具版本 的方法 说明
https://www.cndba.cn/cndba/dave/article/1353

3.2 检查Patch 是否冲突

[dave@www.cndba.cn ~]$ cd /u01/software/
[dave@www.cndba.cn software]$ ls
p28507480_180000_Linux-x86-64---DB.zip
[dave@www.cndba.cn software]$ unzip p28507480_180000_Linux-x86-64---DB.zip 
Archive:  p28507480_180000_Linux-x86-64---DB.zip
   creating: 28507480/
  inflating: 28507480/README.html    
 extracting: 28507480/README.txt     
   creating: 28507480/files/
……
[dave@www.cndba.cn software]$ ls
28507480  p28507480_180000_Linux-x86-64---DB.zip  PatchSearch.xml
[dave@www.cndba.cn software]$
[dave@www.cndba.cn software]$ cd 28507480/
[dave@www.cndba.cn 28507480]$ /u01/app/oracle/product/18.3.0/db_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.14
Copyright (c) 2018, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/18.3.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/18.3.0/db_1/oraInst.loc
OPatch version    : 12.2.0.1.14
OUI version       : 12.2.0.4.0
Log file location : /u01/app/oracle/product/18.3.0/db_1/cfgtoollogs/opatch/opatch2018-11-14_06-07-12AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
[dave@www.cndba.cn 28507480]$

3.3 应用patch

对于DG环境可以先应用备库，在应用主库，对于RAC环境，可以每次应用一个节点，这样可以避免停机时间，但如果是单实例，那么在应用之前，必须关闭数据库和监听。https://www.cndba.cn/dave/article/3138https://www.cndba.cn/dave/article/3138

[dave@www.cndba.cn 28507480]$ lsnrctl stop

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 14-NOV-2018 06:13:18

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=18cDG1)(PORT=1522)))
The command completed successfully
[dave@www.cndba.cn 28507480]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Nov 14 06:13:22 2018
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> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
[dave@www.cndba.cn 28507480]$


[dave@www.cndba.cn 28507480]$ /u01/app/oracle/product/18.3.0/db_1/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.14
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/18.3.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/18.3.0/db_1/oraInst.loc
OPatch version    : 12.2.0.1.14
OUI version       : 12.2.0.4.0
Log file location : /u01/app/oracle/product/18.3.0/db_1/cfgtoollogs/opatch/opatch2018-11-14_06-18-59AM_1.log

OPatch failed to lock and get an Inventory Session for the given Oracle Home /u01/app/oracle/product/18.3.0/db_1
Possible causes are:
   No read or write permission to $ORACLE_HOME, cannot create $ORACLE_HOME/.patch_storage
   No read or write permission to $ORACLE_HOME/.patch_storage
   OPatch cannot proceed further because system will not be able to backup files, or read the backup area for rollback/restore.
OPatchSession cannot load inventory for the given Oracle Home /u01/app/oracle/product/18.3.0/db_1. Possible causes are:
   No read or write permission to ORACLE_HOME/.patch_storage
   Central Inventory is locked by another OUI instance
   No read permission to Central Inventory
   The lock file exists in ORACLE_HOME/.patch_storage
   The Oracle Home does not exist in Central Inventory

UtilSession failed: IPMRWServices::verifyPatchStorageDirectory() cannot read or write to /u01/app/oracle/product/18.3.0/db_1/.patch_storage
Log file location: /u01/app/oracle/product/18.3.0/db_1/cfgtoollogs/opatch/opatch2018-11-14_06-18-59AM_1.log

OPatch failed with error code 73
[dave@www.cndba.cn 28507480]$

这里应用报错：没有权限操作：$ORACLE_HOME/.patch_storage

[dave@www.cndba.cn 28507480]$ ll -lart $ORACLE_HOME |grep root
-rwxr-x--x.  1 oracle oinstall    10 Feb  7  2018 root.sh.old.3
-rwxr-x--x.  1 oracle oinstall    10 Feb  7  2018 root.sh.old.1
-rwx--x--x.  1 oracle oinstall   786 Feb  7  2018 root.sh.old
-rwx--x--x.  1 oracle oinstall   638 Jul 18 13:18 root.sh.old.2
drwxr-xr-x.  3 root   root        28 Jul 18 13:39 .opatchauto_storage
drwxr-xr-x.  7 root   root       262 Jul 18 13:44 .patch_storage
-rwx------.  1 oracle oinstall   610 Aug 26 03:38 root.sh
[dave@www.cndba.cn 28507480]$

检查发现权限确实是root，修改成oracle 在执行成功：https://www.cndba.cn/dave/article/3138

[dave@www.cndba.cn 28507480]$ ll -lart $ORACLE_HOME |grep root
-rwxr-x--x.  1 oracle oinstall    10 Feb  7  2018 root.sh.old.3
-rwxr-x--x.  1 oracle oinstall    10 Feb  7  2018 root.sh.old.1
-rwx--x--x.  1 oracle oinstall   786 Feb  7  2018 root.sh.old
-rwx--x--x.  1 oracle oinstall   638 Jul 18 13:18 root.sh.old.2
-rwx------.  1 oracle oinstall   610 Aug 26 03:38 root.sh
[dave@www.cndba.cn 28507480]$

[dave@www.cndba.cn 28507480]$ /u01/app/oracle/product/18.3.0/db_1/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.14
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/18.3.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/18.3.0/db_1/oraInst.loc
OPatch version    : 12.2.0.1.14
OUI version       : 12.2.0.4.0
Log file location : /u01/app/oracle/product/18.3.0/db_1/cfgtoollogs/opatch/opatch2018-11-14_06-24-18AM_1.log

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

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 = '/u01/app/oracle/product/18.3.0/db_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '28507480' to OH '/u01/app/oracle/product/18.3.0/db_1'
ApplySession: Optional component(s) [ 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.network.cman, 18.0.0.0.0 ] , [ oracle.tfa, 18.0.0.0.0 ] , [ oracle.assistants.usm, 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.rdbms.deconfig, 18.0.0.0.0...

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

Patching component oracle.rdbms, 18.0.0.0.0...

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

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

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

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

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

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

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

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

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

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

Patching component oracle.ctx, 18.0.0.0.0...

Patching component oracle.server, 18.0.0.0.0...

Patching component oracle.xdk, 18.0.0.0.0...

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

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

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

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

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

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

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

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

Patching component oracle.precomp.common, 18.0.0.0.0...

Patching component oracle.sdo, 18.0.0.0.0...

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

Patching component oracle.ons, 18.0.0.0.0...

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

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

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

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

Patching component oracle.rdbms.rman, 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.sqlplus, 18.0.0.0.0...

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

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

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

Patching component oracle.oraolap.dbscripts, 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...
Patch 28507480 successfully applied.
Sub-set patch [28090523] has become inactive due to the application of a super-set patch [28507480].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/product/18.3.0/db_1/cfgtoollogs/opatch/opatch2018-11-14_06-24-18AM_1.log

OPatch succeeded.
[dave@www.cndba.cn 28507480]$

3.4 加载修改后的SQL到数据库

打补丁后会修改一下SQL，这些SQL还没有加载到数据库中，需要运行Datapatch工具将这些修改后的SQL重新加载到数据库中，如果是RAC环境，只需要在一个节点执行就可以了。

对于18c的CDB架构，执行如下步骤：

  1. sqlplus /nolog
  2. SQL> Connect / as sysdba
  3. SQL> startup
  4. SQL> alter pluggable database all open;
  5. SQL> quit
  6. cd $ORACLE_HOME/OPatch
  7. ./datapatch -verbose

datapatch命令只对打开的数据库生效，所有Oracle建议在执行该命令之前将CDB和所有的PDB都打开，一次更新掉。 但如果有部分PDB没有打开，也可以在打开之后，重新运行datapatch命令。

https://www.cndba.cn/dave/article/3138

我们这里模拟就是指打开CDB,关闭PDB。https://www.cndba.cn/dave/article/3138

[dave@www.cndba.cn 28507480]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Nov 14 06:51:57 2018
Version 18.3.1.0.0

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1191181104 bytes
Fixed Size            8895280 bytes
Variable Size          872415232 bytes
Database Buffers       33554432 bytes
Redo Buffers            7880704 bytes
In-Memory Area          268435456 bytes
Database mounted.
Database opened.
SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 DAVE               READ WRITE NO
SQL> alter pluggable database dave close;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 DAVE               MOUNTED
SQL>

[dave@www.cndba.cn ~]$ cd $ORACLE_HOME/OPatch
[dave@www.cndba.cn OPatch]$ ./datapatch -verbose
SQL Patching tool version 18.0.0.0.0 Production on Wed Nov 14 06:54:07 2018
Copyright (c) 2012, 2018, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_12428_2018_11_14_06_54_08/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

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)

Warning: PDB DAVE is in mode MOUNTED and will be skipped.
Bootstrapping registry and package to current versions...done
Determining current state...done
#这里也有提示，没有open的实例将被跳过。
Current state of interim SQL patches:
Interim patch 27923415 (OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)):
  Binary registry: Installed
  PDB CDB$ROOT: Applied successfully on 26-AUG-18 03.46.12.362385 AM
  PDB PDB$SEED: Applied successfully on 26-AUG-18 03.52.38.519926 AM

Current state of release update SQL patches:
  Binary registry:
    18.3.1.0.0 Release_Update_Revision 1809111630: Installed
  PDB CDB$ROOT:
    Applied 18.3.0.0.0 Release_Update 1806280943 successfully on 26-AUG-18 03.46.12.351709 AM
  PDB PDB$SEED:
    Applied 18.3.0.0.0 Release_Update 1806280943 successfully on 26-AUG-18 03.52.38.463802 AM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED
    No interim patches need to be rolled back
    Patch 28507480 (Database Release Update Revision : 18.3.1.0.181016 (28507480)):
      Apply from 18.3.0.0.0 Release_Update 1806280943 to 18.3.1.0.0 Release_Update_Revision 1809111630
    No interim patches need to be applied

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

Validating logfiles...done
Patch 28507480 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/28507480/22444948/28507480_apply_CNDBA_CDBROOT_2018Nov14_06_57_01.log (no errors)
Patch 28507480 apply (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/28507480/22444948/28507480_apply_CNDBA_PDBSEED_2018Nov14_07_02_21.log (no errors)
SQL Patching tool complete on Wed Nov 14 07:05:16 2018
[dave@www.cndba.cn OPatch]$

3.5 查看版本变化

datapatch 工具加载修改的SQL之后会将patch添加到dba_registry_sqlpatch试图中，以反应patch的应用情况。 查询试图可以看到patch的APPLY状态为”SUCCESS”。

SQL> select patch_id,patch_type,action,status,action_time,description from dba_registry_sqlpatch;

  PATCH_ID PATCH_TYPE    ACTION     STATUS       ACTION_TIME             DESCRIPTION
---------- ------------ -------- ------------ ------------------------------ -----------------------------------------------------------------
  28090523 RU        APPLY     SUCCESS      26-AUG-18 03.46.12.351709 AM   Database Release Update : 18.3.0.0.180717 (28090523)
  27923415 INTERIM    APPLY     SUCCESS      26-AUG-18 03.46.12.362385 AM   OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)
  28507480 RUR        APPLY     SUCCESS      14-NOV-18 07.05.00.089098 AM   Database Release Update Revision : 18.3.1.0.181016 (28507480)

前面2个是我们建库的时候安装的，最后一个是我们刚才安装的。 

SQL> col version for a30
SQL> select ACTION_TIME,VERSION,COMMENTS from dba_registry_history;

ACTION_TIME               VERSION                  COMMENTS
------------------------------ ------------------------------ ------------------------------------------------------------
                   18                  RDBMS_18.3.0.0.0DBRUR_LINUX.X64_180910
26-AUG-18 03.45.08.480341 AM   18.0.0.0.0              Patch applied from 18.1.0.0.0 to 18.3.0.0.0
26-AUG-18 03.46.09.656019 AM   18.3.0.0.180717OJVMRU          RAN jvmpsu.sql
26-AUG-18 03.46.09.749574 AM   18.3.0.0.180717OJVMRU          OJVM RU post-install
14-NOV-18 07.01.57.566558 AM   18.0.0.0.0              Patch applied from 18.3.0.0.0 to 18.3.1.0.0

3.6 单独处理未加载SQL的PDB

在之前加载的日志里，我们可以看到，PDB实例Dave 被跳过了。 所以我们这里需要单独对PDB加载SQL。

[dave@www.cndba.cn 28507480]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Nov 14 07:29:01 2018
Version 18.3.1.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.1.0.0
#测试CDB的版本已经升级到18.3.1了。

SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 DAVE               MOUNTED
SQL> alter pluggable database dave open;
Warning: PDB altered with errors.
#这里打开PDB有警告。
SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 DAVE               READ WRITE YES

查看alert log 日志：

DAVE(3):Database Characterset for DAVE is AL32UTF8
2018-11-14T07:31:08.627347-05:00
Violations: Type: 1, Count: 1
DAVE(3):***************************************************************
DAVE(3):WARNING: Pluggable Database DAVE with pdb id - 3 is
DAVE(3):         altered with errors or warnings. Please look into
DAVE(3):         PDB_PLUG_IN_VIOLATIONS view for more details.
DAVE(3):***************************************************************
2018-11-14T07:31:11.801193-05:00
DAVE(3):Opening pdb with no Resource Manager plan active
Pluggable database DAVE opened read write
2018-11-14T07:31:12.803831-05:00
Completed: alter pluggable database dave open

这里提示我们查看PDB_PLUG_IN_VIOLATIONS来查找告警的原因。

SQL> select name,cause,action,message from PDB_PLUG_IN_VIOLATIONS;

NAME         CAUSE    ACTION                            MESSAGE
------------ ---------- ------------------------------------------------------- ------------------------------------------------------------
PDB$SEED     SQL Patch    Call datapatch to install in the PDB or the CDB     Interim patch 27923415/22239273 (OJVM RELEASE UPDATE: 18.3.0
                                        .0.180717 (27923415)): Installed in the CDB but not in the P
                                        DB

PDB$SEED     SQL Patch    Call datapatch to install in the PDB or the CDB     '18.3.0.0.0 Release_Update 1806280943' is installed in the C
                                        DB but no release updates are installed in the PDB

DAVE         SQL Patch    Call datapatch to install in the PDB or the CDB     '18.3.1.0.0 Release_Update_Revision 1809111630' is installed
                                         in the CDB but '18.3.0.0.0 Release_Update 1806280943' is in
                                        stalled in the PDB

这里其实就是没有datapatch没有在相应的PDB上执行，并且也给到我们建议：Call datapatch to install in the PDB or the CDB。

在执行datapatch命令时，加上-pdbs参数指定特定的pdb就可以了：https://www.cndba.cn/dave/article/3138https://www.cndba.cn/dave/article/3138

[dave@www.cndba.cn OPatch]$ ./datapatch -help
SQL Patching tool version 18.0.0.0.0 Production on Wed Nov 14 07:37:06 2018
Copyright (c) 2012, 2018, Oracle.  All rights reserved.
……
-pdbs <pdb1,pdb2,...,pdbn>
  Only consider the specified list of PDBs for patching.  All other PDBs will not be patched
……

[dave@www.cndba.cn OPatch]$ ./datapatch -pdbs dave
SQL Patching tool version 18.0.0.0.0 Production on Wed Nov 14 07:38:46 2018
Copyright (c) 2012, 2018, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_15928_2018_11_14_07_38_46/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

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 interim SQL patches:
Interim patch 27923415 (OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)):
  Binary registry: Installed
  PDB DAVE: Applied successfully on 26-AUG-18 03.52.38.519926 AM

Current state of release update SQL patches:
  Binary registry:
    18.3.1.0.0 Release_Update_Revision 1809111630: Installed
  PDB DAVE:
    Applied 18.3.0.0.0 Release_Update 1806280943 successfully on 26-AUG-18 03.52.38.463802 AM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: DAVE
    No interim patches need to be rolled back
    Patch 28507480 (Database Release Update Revision : 18.3.1.0.181016 (28507480)):
      Apply from 18.3.0.0.0 Release_Update 1806280943 to 18.3.1.0.0 Release_Update_Revision 1809111630
    No interim patches need to be applied

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

Validating logfiles...done
Patch 28507480 apply (pdb DAVE): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/28507480/22444948/28507480_apply_CNDBA_DAVE_2018Nov14_07_40_15.log (no errors)
SQL Patching tool complete on Wed Nov 14 07:42:42 2018
[dave@www.cndba.cn OPatch]$

应用成功。

再重新打开PDB，也不再告警了：

SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 DAVE               READ WRITE YES
SQL> alter pluggable database dave close;

Pluggable database altered.

SQL> alter pluggable database dave open;
Pluggable database altered.

3.7 处理无效对象

最后一步就是处理无效对象，因为之前datapatch命令会加载SQL，这个过程可能会产生无效对象。 可以执行@utlrp.sql脚本处理这些无效对象：

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @utlrp.sql

至此RUR升级完成。https://www.cndba.cn/dave/article/3138

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的：当他回首往事的时候，他不会因为虚度年华而悔恨，也不会因为碌碌无为而羞耻；这样，在临死的时候，他就能够说：“我的整个生命和全部精力，都已经献给世界上最壮丽的事业....."

  • 2248
    原创
  • 3
    翻译
  • 557
    转载
  • 188
    评论
  • 访问：7224240次
  • 积分：4284
  • 等级：核心会员
  • 排名：第1名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ