RedHat 8.5 下oracle 19.3升级至19.13

字数 18398阅读 3130评论 1赞 4

1.查看当前DB环境

[root@node01 ~]# cat /etc/redhat-release 
    Red Hat Enterprise Linux release 8.5 (Ootpa)
    [root@node01 ~]# su - oracle
    [oracle@node01 ~]$ sqlplus -V
    
    SQL*Plus: Release 19.0.0.0.0 - Production
    Version 19.3.0.0.0
    
    [oracle@node01 ~]$ sqlplus / as sysdba
    
    SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 1 19:59:42 2022
    Version 19.3.0.0.0
    
    Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    
    Connected to an idle instance.
    
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area 2516582152 bytes
    Fixed Size                  9137928 bytes
    Variable Size             536870912 bytes
    Database Buffers         1962934272 bytes
    Redo Buffers                7639040 bytes
    Database mounted.
    Database opened.
    SQL> show pdbs;     
    
        CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
             2 PDB$SEED                       READ ONLY  NO
             3 PDB01                          MOUNTED
    SQL> alter session set container=PDB01;
    
    Session altered.
    
    SQL> alter pluggable database open;
    
    Pluggable database altered.
    
    SQL> show pdbs;
    
        CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
             3 PDB01                          READ WRITE NO

2.MOS上下载Oracle补丁  
19.13 DB的RU 编号是：33192793，GI的RU编号是： 33182768。  
p33182768_190000_Linux-x86-64.zip  
p33192793_190000_Linux-x86-64.zip

3 开始安装RU补丁

3.1安装OPatch

[oracle@db01 32545013]$ cd $ORACLE_HOME/OPatch/
    [oracle@db01 OPatch]$ ./opatch version
    OPatch Version: 12.2.0.1.17
    
    OPatch succeeded.
    [oracle@db01 32545013]$ cd $ORACLE_HOME
    [oracle@db01 dbhome_1]$ mv OPatch/ OPatchbak 
    [oracle@db01 dbhome_1]$ unzip /tmp/p6880880_190000_Linux-x86-64.zip -d $ORACLE_HOME
    [oracle@db01 OPatch]$ cd /u01/app/oracle/product/19.3.0/dbhome_1/OPatch
    [oracle@db01 OPatch]$ ./opatch version
    OPatch Version: 12.2.0.1.27
    
    OPatch succeeded.

3.2 检查Patch是否冲突

[oracle@node01 tmp]$ unzip p33192793_190000_Linux-x86-64.zip
    [oracle@node01 33192793]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
    Oracle Interim Patch Installer version 12.2.0.1.27
    Copyright (c) 2022, Oracle Corporation.  All rights reserved.
    
    PREREQ session
    
    Oracle Home       : /u01/app/oracle/product/19.3.0/dbhome_1
    Central Inventory : /u01/app/oraInventory
       from           : /u01/app/oracle/product/19.3.0/dbhome_1/oraInst.loc
    OPatch version    : 12.2.0.1.27
    OUI version       : 12.2.0.7.0
    Log file location : /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2022-01-01_20-23-30PM_1.log
    
    Invoking prereq "checkconflictagainstohwithdetail"
    
    Prereq "checkConflictAgainstOHWithDetail" passed.
    
    OPatch succeeded.

3.3 应用patch  
如果是物理DG，那么主备库都需要安装Patch，可以先在备库安装，再安装主库，操作步骤可以参考：Document 278641.1。  
对于RAC 环境，可以使用OPatch rolling 方式来安装，这样没有停机时间，具体操作可以参考：Document 244241.1。  
对于单实例，必须关闭待升级ORACLE HOME关联的所有实例和监听，并且包括退出所有的sqlplus窗口。  
关闭实例和监听，并退出sqlplus。

[oracle@db01 33192793]$ lsnrctl stop
    
    LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-OCT-2021 21:53:53
    
    Copyright (c) 1991, 2021, Oracle.  All rights reserved.
    
    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    The command completed successfully
    SQL>    shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.

[oracle@node01 33192793]$ cd /tmp/33192793/
    [oracle@node01 33192793]$ $ORACLE_HOME/OPatch/opatch apply
    Oracle Interim Patch Installer version 12.2.0.1.27
    Copyright (c) 2022, Oracle Corporation.  All rights reserved.
    
    
    Oracle Home       : /u01/app/oracle/product/19.3.0/dbhome_1
    Central Inventory : /u01/app/oraInventory
       from           : /u01/app/oracle/product/19.3.0/dbhome_1/oraInst.loc
    OPatch version    : 12.2.0.1.27
    OUI version       : 12.2.0.7.0
    Log file location : /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2022-01-01_20-32-03PM_1.log
    
    Verifying environment and performing prerequisite checks...
    OPatch continues with these patches:   33192793  
    
    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/19.3.0/dbhome_1')
    
    
    Is the local system ready for patching? [y|n]
    y
    User Responded with: Y
    Backing up files...
      Applying interim patch '33192793' to OH '/u01/app/oracle/product/19.3.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.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , [ oracle.options.olap.api, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.network.cman, 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.perlint.expat, 2.0.1.0.4...
    
    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.odbc.ic, 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.sdo.locator.jrf, 19.0.0.0.0...
    
    Patching component oracle.sqlplus, 19.0.0.0.0...
    
    Patching component oracle.sqlplus.ic, 19.0.0.0.0...
    
    Patching component oracle.wwg.plsql, 19.0.0.0.0...
    
    Patching component oracle.rdbms.crs, 19.0.0.0.0...
    
    Patching component oracle.network.listener, 19.0.0.0.0...
    
    Patching component oracle.network.client, 19.0.0.0.0...
    
    Patching component oracle.ctx.rsf, 19.0.0.0.0...
    
    Patching component oracle.rdbms.scheduler, 19.0.0.0.0...
    
    Patching component oracle.rdbms.hs_common, 19.0.0.0.0...
    
    Patching component oracle.xdk, 19.0.0.0.0...
    
    Patching component oracle.ons.ic, 19.0.0.0.0...
    
    Patching component oracle.xdk.xquery, 19.0.0.0.0...
    
    Patching component oracle.rdbms.drdaas, 19.0.0.0.0...
    
    Patching component oracle.javavm.server, 19.0.0.0.0...
    
    Patching component oracle.ovm, 19.0.0.0.0...
    
    Patching component oracle.dbdev, 19.0.0.0.0...
    
    Patching component oracle.rdbms.dv, 19.0.0.0.0...
    
    Patching component oracle.precomp.rsf, 19.0.0.0.0...
    
    Patching component oracle.ctx.atg, 19.0.0.0.0...
    
    Patching component oracle.ldap.security.osdt, 19.0.0.0.0...
    
    Patching component oracle.ldap.client, 19.0.0.0.0...
    
    Patching component oracle.ldap.rsf.ic, 19.0.0.0.0...
    
    Patching component oracle.xdk.parser.java, 19.0.0.0.0...
    
    Patching component oracle.oraolap.dbscripts, 19.0.0.0.0...
    
    Patching component oracle.odbc, 19.0.0.0.0...
    
    Patching component oracle.sdo.locator, 19.0.0.0.0...
    
    Patching component oracle.dbtoolslistener, 19.0.0.0.0...
    
    Patching component oracle.ons, 19.0.0.0.0...
    
    Patching component oracle.oraolap.api, 19.0.0.0.0...
    
    Patching component oracle.rdbms.hsodbc, 19.0.0.0.0...
    
    Patching component oracle.rdbms.rman, 19.0.0.0.0...
    
    Patching component oracle.rdbms.install.common, 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.lbac, 19.0.0.0.0...
    
    Patching component oracle.mgw.common, 19.0.0.0.0...
    
    Patching component oracle.xdk.rsf, 19.0.0.0.0...
    
    Patching component oracle.oraolap, 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 33192793 successfully applied.
    Sub-set patch [29517242] has become inactive due to the application of a super-set patch [33192793].
    Please refer to Doc ID 2161861.1 for any possible further required actions.
    Log file location: /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2022-01-01_20-32-03PM_1.log
    
    OPatch succeeded.

3.4 加载变化的SQL到数据库  
安装补丁之后，还需要将有变化的SQL加载到数据库中，这里可以直接运行Datapatch工具将这些修改的SQL重新加载到数据库中，如果是RAC环境，只需要在一个节点执行就可以了。

[root@node01 ~]# su - oracle
    s[oracle@node01 ~]$ sqlplus / as sysdba
    
    SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 1 21:29:23 2022
    Version 19.13.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 2516582144 bytes
    Fixed Size                  9137920 bytes
    Variable Size             536870912 bytes
    Database Buffers         1962934272 bytes
    Redo Buffers                7639040 bytes
    Database mounted.
    Database opened.
    SQL> alter session set container = PDB01;
    
    Session altered.
    
    SQL> alter pluggable database open;
    
    Pluggable database altered.
    
    SQL> show pdbs;
    
        CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
             3 PDB01                          READ WRITE NO
    SQL> quit
    Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.13.0.0.0
    [oracle@node01 ~]$ cd $ORACLE_HOME/OPatch
    [oracle@node01 OPatch]$ ./datapatch -verbose
    SQL Patching tool version 19.13.0.0.0 Production on Sat Jan  1 21:31:00 2022
    Copyright (c) 2012, 2021, Oracle.  All rights reserved.
    
    Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_7938_2022_01_01_21_31_00/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:
      No interim patches found
    
    Current state of release update SQL patches:
      Binary registry:
        19.13.0.0.0 Release_Update 211004165050: Installed
      PDB CDB$ROOT:
        Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 30-DEC-21 07.56.27.706834 PM
      PDB PDB$SEED:
        Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 30-DEC-21 08.06.08.780937 PM
      PDB PDB01:
        Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 30-DEC-21 08.06.08.780937 PM
    
    Adding patches to installation queue and performing prereq checks...done
    Installation queue:
      For the following PDBs: CDB$ROOT PDB$SEED PDB01
        No interim patches need to be rolled back
        Patch 33192793 (Database Release Update : 19.13.0.0.211019 (33192793)):
          Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.13.0.0.0 Release_Update 211004165050
        No interim patches need to be applied
    
    Installing patches...
    Patch installation complete.  Total patches installed: 3
    
    Validating logfiles...done
    Patch 33192793 apply (pdb CDB$ROOT): SUCCESS
      logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33192793/24462514/33192793_apply_ORCL_CDBROOT_2022Jan01_21_32_13.log (no errors)
    Patch 33192793 apply (pdb PDB$SEED): SUCCESS
      logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33192793/24462514/33192793_apply_ORCL_PDBSEED_2022Jan01_21_45_04.log (no errors)
    Patch 33192793 apply (pdb PDB01): SUCCESS
      logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33192793/24462514/33192793_apply_ORCL_PDB01_2022Jan01_21_45_04.log (no errors)
    
    Automatic recompilation incomplete; run utlrp.sql to revalidate.
      PDBs: PDB01 PDB$SEED
    
    SQL Patching tool complete on Sat Jan  1 22:01:06 2022

3.5 处理无效对象

[oracle@node01 OPatch]$ sqlplus / as sysdba
    
    SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 1 22:02:24 2022
    Version 19.13.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.13.0.0.0
    
    SQL> @?/rdbms/admin/utlrp.sql
    
    Session altered.
    
    
    TIMESTAMP
    --------------------------------------------------------------------------------
    COMP_TIMESTAMP UTLRP_BGN              2022-01-01 22:02:27
    
    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              2022-01-01 22:02:31
    
    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.

redhat 8.5 oracle 19c

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

添加新评论1 条评论

2008010755软件开发工程师交通运输部规划研究院
2022-03-31 15:01

好厉害

Ctrl+Enter 发表

匿名评论

RedHat 8.5 下oracle 19.3升级至19.13

添加新评论1 条评论

作者其他文章

相关文章

相关问题

相关资料