重庆思庄Oracle、Redhat认证学习论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

搜索
查看: 417|回复: 0

[升级] oracle11.2.0.4升级到19.3.0.0

[复制链接]
发表于 2020-9-26 14:44:30 | 显示全部楼层 |阅读模式
一、环境
1.oracle版本
SQL>select * from v$version;
BANNER
--------------------------------------------------------------------------------
OracleDatabase 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQLRelease 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS forLinux: Version 11.2.0.4.0 - Production
NLSRTLVersion 11.2.0.4.0 - Production
2.环境变量
[oracle@oracle~]$ env |grep ORA
ORACLE_SID=orcl
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
3.存储和内存资源
[oracle@oracle~]$ df -h
Filesystem             Size  Used Avail Use% Mounted on
/dev/mapper/rhel-root   50G  22G   29G  44% /
devtmpfs               897M     0 897M   0% /dev
tmpfs                   12G  210M  12G   2% /dev/shm
tmpfs                  912M  9.1M 903M   1% /run
tmpfs                  912M     0 912M   0% /sys/fs/cgroup
/dev/sda1             1014M  173M 842M  18% /boot
/dev/mapper/rhel-home   27G  33M   27G   1% /home
tmpfs                  183M   12K 183M   1% /run/user/0
二、准备
1.传输19c压缩包
[oracle@oracle~]$ mkdir -p /u01/app/oracle/product/19.3.0/db_1
[oracle@oracle~]$ cd /u01/app/oracle/product/19.3.0/db_1
[oracle@oracledb_1]$ ll
total2987996
-rw-r--r--1 oracle oinstall 3059705302 May  6  2019 LINUX.X64_193000_db_home.zip
2.解压文件
[oracle@oracledb_1]$ unzip LINUX.X64_193000_db_home.zip
3.查看系统参数是否需要修改
638285f6ee25369147.png
三、安装oracle19.3.0.0数据库软件
212365f6ee25ce5950.png
712495f6ee2662f987.png
157865f6ee26eaadd7.png
373365f6ee279cefda.png
730175f6ee282570f2.png
236155f6ee289728bb.png
586475f6ee29206b1e.png
安装需要的包
407425f6ee29fa0aaf.png
616635f6ee2a633032.png
658015f6ee2ac6d5c8.png
四、执行升级前的脚本
[oracle@oracledb_1]$ /u01/app/oracle/product/19.3.0/db_1/jdk/bin/java -jar/u01/app/oracle/product/19.3.0/db_1/rdbms/admin/preupgrade.jar
==================
PREUPGRADESUMMARY
==================
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
Executefixup scripts as indicated below:
Beforeupgrade:
Log intothe database and execute the preupgrade fixups
@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
Afterthe upgrade:
Log intothe database and execute the postupgrade fixups
@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
Preupgradecomplete: 2020-09-23T10:01:12
SQL>@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
ExecutingOracle PRE-Upgrade Fixup Script
Auto-Generatedby:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0Build: 1
Generatedon:            2020-09-23 10:01:08
ForSource Database:     ORCL
SourceDatabase Version: 11.2.0.4.0
ForUpgrade to Version:  19.0.0.0.0
Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied   Further DBA Action
------  ------------------------  ---------- --------------------------------
    1. parameter_min_val         NO          Manual fixup recommended.
    2. em_present                NO          Manual fixup recommended.
    3. amd_exists                NO          Manual fixup recommended.
    4. apex_manual_upgrade       NO          Manual fixup recommended.
    5. mv_refresh                NO          Informational only.
                                             Further action is optional.
    6. dictionary_stats          YES         None.
    7. trgowner_no_admndbtrg     YES         None.
    8. mv_refresh                NO          Informational only.
                                             Further action is optional.
    9.  pre_fixed_objects         YES         None.
   10. tablespaces_info          NO          Informational only.
                                             Further action is optional.
   11. exf_rul_exists            NO          Informational only.
                                             Further action is optional.
   12. rman_recovery_version     NO          Informational only.
                                             Further action is optional.
Thefixup scripts have been run and resolved what they can. However,
thereare still issues originally identified by the preupgrade that
have notbeen remedied and are still present in the database.
Dependingon the severity of the specific issue, and the nature of
theissue itself, that could mean that your database is not ready
forupgrade.  To resolve the outstandingissues, start by reviewing
thepreupgrade_fixups.sql and searching it for the name of
thefailed CHECK NAME or Preupgrade Action Number listed above.
Thereyou will find the original corresponding diagnostic message
from thepreupgrade which explains in more detail what still needs
to bedone.
PL/SQL 过程已成功完成。
[oracle@oracledb_1]$ cd /u01/app/oracle/cfgtoollogs/orcl/preupgrade/
[oracle@oraclepreupgrade]$ cat preupgrade.log
Reportgenerated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0Build: 1 on 2020-09-23T10:01:12
Upgrade-Toversion: 19.0.0.0.0
=======================================
Statusof the database prior to upgrade
=======================================
      Database Name:  ORCL
     Container Name:  Not Applicable in Pre-12.1 database
       Container ID:  Not Applicable in Pre-12.1 database
            Version:  11.2.0.4.0
     DB Patch Level:  No Patch Bundle applied
         Compatible:  11.2.0.4.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  14
  Database log mode:  NOARCHIVELOG
           Readonly:  FALSE
            Edition:  EE
  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID        
  JServer JAVA Virtual Machine           [to be upgraded]  VALID        
  Oracle XDK for Java                    [to be upgraded]  VALID        
  Oracle Workspace Manager               [to be upgraded]  VALID        
  OLAP Analytic Workspace                [to be upgraded]  VALID        
  Oracle Enterprise Manager Repository   [to be upgraded]  VALID        
  Oracle Text                            [to beupgraded]  VALID         
  Oracle XML Database                    [to be upgraded]  VALID        
  Oracle Java Packages                   [to be upgraded]  VALID        
  Oracle Multimedia                      [to be upgraded]  VALID        
  Oracle Spatial                         [to be upgraded]  VALID        
  Expression Filter                      [to be upgraded]  VALID        
  Rule Manager                           [to be upgraded]  VALID        
  Oracle OLAP API                        [to be upgraded]  VALID        
==============
BEFOREUPGRADE
==============
  REQUIRED ACTIONS
  ================
  None
  RECOMMENDED ACTIONS
  ===================
  1. Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums.
      This action may be done now or whenstarting the database in upgrade mode
      using the 19 ORACLE HOME.
      
       Parameter                                 Currently  19 minimum
       ---------                                 ---------  ------------------
      *memory_target                             763363328          1203765248
       processes                                       150                 300
      
      Thedatabase upgrade process requires certain initialization parameters
      to meet minimum values.  The Oracle upgrade process itself has minimum
      values which may be higher and are markedwith an asterisk.  After
      upgrading, those asterisked parametervalues may be reset if needed.
  2. Remove the EM repository.
      
      - Copy the$ORACLE_HOME/rdbms/admin/emremove.sql script from the target
      19 ORACLE_HOME into the source 11.2.0.4.0ORACLE_HOME.
      
      Step 1: If database control isconfigured, stop EM Database Control,
      using the following command
      
        $> emctl stop dbconsole
      
      Step 2: Connect to the database using theSYS account AS SYSDBA
      
        SET ECHO ON;
        SET SERVEROUTPUT ON;
        @emremove.sql
      
      Without the set echo and serveroutputcommands, you will not be able to
      follow the progress of the script.
      
      The database has an Enterprise ManagerDatabase Control repository.
      
      Starting with Oracle Database 12c, thelocal Enterprise Manager Database
      Control does not exist anymore. Therepository will be removed from your
      database during the upgrade.  This step can be manually performed before
      the upgrade to reduce downtime.
  3. Remove OLAP Catalog by running the 11.2.0.4.0 SQL script
      $ORACLE_HOME/olap/admin/catnoamd.sqlscript.
      
      The OLAP Catalog component, AMD, existsin the database.
      
      Starting with Oracle Database 12c, theOLAP Catalog (OLAP AMD) is
      desupported and will be automaticallymarked as OPTION OFF during the
      database upgrade if present. Oraclerecommends removing OLAP Catalog
      (OLAP AMD) before database upgrade.  This step can be manually performed
      before the upgrade to reduce downtime.
  4. Upgrade Oracle Application Express (APEX) manually before the database
      upgrade.
      
      The database contains APEX version3.2.1.00.12. Upgrade APEX to at least
      version 18.2.0.00.12.
      
      Starting with Oracle Database Release 18,APEX is not upgraded
      automatically as part of the databaseupgrade. Refer to My Oracle Support
      Note 1088970.1 for information about APEXinstallation and upgrades.
  5. Please make sure that all the MVs are refreshed and sys.sumdelta$becomes
      empty before doing upgrade, unless youhave strong business reasons not
      to do so. You can usedbms_mview.refresh() to refresh the MVs except
      those stale ones  to be kept due to business need. If there areany stale
      MVs depending on changes insys.sumdelta$, do not truncate it, because
      doing so will cause wrong results afterrefresh.
      
      There is one or more non-fresh MV in thedatabase or sumdelta$ is not
      empty.
      
      Oracle recommends that all materializedviews (MV's) are refreshed before
      upgrading the database because this willclear the MV logs and the
      sumdelta$ table, and make the UPGRADE processfaster. If you choose to
      not refresh some MVs, the change data forthose MV's will be carried
      through the UPGRADE process. AfterUPGRADE, you can refresh the MV's and
      MV incremental refresh should work innormal cases.
  6.  (AUTOFIXUP)Gather stale data dictionary statistics prior to database
      upgrade in off-peak time using:
      
        EXECUTEDBMS_STATS.GATHER_DICTIONARY_STATS;
      
      Dictionary statistics do not exist or arestale (not up-to-date).
      
      Dictionary statistics help the Oracleoptimizer find efficient SQL
      execution plans and are essential forproper upgrade timing. Oracle
      recommends gathering dictionarystatistics in the last 24 hours before
      database upgrade.
      
      For information on managing optimizerstatistics, refer to the 11.2.0.4
      Oracle Database Performance Tuning Guide.
  7. (AUTOFIXUP) Directly grant ADMINISTER DATABASE TRIGGER privilege to the
      owner of the trigger or drop andre-create the trigger with a user that
      was granted directly with such. You canlist those triggers using: SELECT
      OWNER, TRIGGER_NAME FROM DBA_TRIGGERSWHERE
      TRIM(BASE_OBJECT_TYPE)='DATABASE' ANDOWNER NOT IN (SELECT GRANTEE FROM
      DBA_SYS_PRIVS WHERE PRIVILEGE='ADMINISTERDATABASE TRIGGER').
      
      There is one or more database triggerswhose owner does not have the
      right privilege on the database.
      
      The creation of database triggers must bedone by users granted with
      ADMINISTER DATABASE TRIGGER privilege.Privilege must have been granted
      directly.
  8. Please make sure that all the MVs are refreshed and sys.sumdelta$becomes
      empty before doing upgrade, unless youhave strong business reasons not
      todo so. You can use dbms_mview.refresh() to refresh the MVs except
      those stale ones  to be kept due to business need. If there areany stale
      MVs depending on changes insys.sumdelta$, do not truncate it, because
      doing so will cause wrong results afterrefresh.
      
      There is one or more non-fresh MV in thedatabase or sumdelta$ is not
      empty.
      
      Oracle recommends that all materializedviews (MV's) are refreshed before
      upgrading the database because this willclear the MV logs and the
      sumdelta$ table, and make the UPGRADEprocess faster. If you choose to
      not refresh some MVs, the change data forthose MV's will be carried
      through the UPGRADE process. AfterUPGRADE, you can refresh the MV's and
      MV incremental refresh should work innormal cases.
  9. (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.
      
      None of the fixed object tables have hadstats collected.
      
      Gathering statistics on fixed objects, ifnone have been gathered yet, is
      recommended prior to upgrading.
      
      For information on managing optimizerstatistics, refer to the 11.2.0.4
      Oracle Database Performance Tuning Guide.
  INFORMATION ONLY
  ================
  10. To help you keep track of your tablespaceallocations, the following
      AUTOEXTEND tablespaces are expected tosuccessfully EXTEND during the
      upgrade process.
      
                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      SYSAUX                             490 MB       743 MB
      SYSTEM                             750 MB      1180 MB
      TEMP                                29 MB       150 MB
      UNDOTBS1                            90 MB       446 MB
      
      Minimum tablespace sizes for upgrade areestimates.
  11. Run $ORACLE_HOME/rdbms/admin/catnoexf.sqllocated in the new Oracle
      Database Oracle home to remove both EXFand RUL.
      
      Expression Filter (EXF) or Rules Manager(RUL) exist in the database.
      
      Starting with Oracle Database release12.1, the Expression Filter (EXF)
      and Database Rules Manager (RUL) featuresare desupported, and are
      removed during the upgrade process.  This step can be manually performed
      before the upgrade to reduce downtime.
  12. Check the Oracle Backup and RecoveryUser's Guide for information on how
      to manage an RMAN recovery catalogschema.
      
      If you are using a version of therecovery catalog schema that is older
      than that required by the RMAN clientversion, then you must upgrade the
      catalog schema.
      
      It is good practice to have the catalogschema the same or higher version
      than the RMAN client version you areusing.
  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database ORCL
  which are identified above as BEFORE UPGRADE"(AUTOFIXUP)" can be resolved by
  executing the following
   SQL>@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
=============
AFTERUPGRADE
=============
  REQUIRED ACTIONS
  ================
  None
  RECOMMENDED ACTIONS
  ===================
  13. (AUTOFIXUP) If you use the -T option forthe database upgrade, then run
      $ORACLE_HOME/rdbms/admin/utluptabdata.sqlafter the upgrade is complete,
      to VALIDATE and UPGRADE any user tablesaffected by changes to
      Oracle-Maintained types.
      
      There are user tables dependent onOracle-Maintained object types.
      
      If the -T option is used to set usertablespaces to READ ONLY during the
      upgrade, user tables in those tablespaces,that are dependent on
      Oracle-Maintained types, will not beautomatically upgraded. If a type is
      evolved during the upgrade, any dependenttables need to be re-validated
      and upgraded to the latest type versionAFTER the database upgrade
      completes.
  14. Upgrade the database time zone file usingthe DBMS_DST package.
      
      The database is using time zone fileversion 14 and the target 19 release
      ships with time zone file version 32.
      
      Oracle recommends upgrading to thedesired (latest) version of the time
      zone file.  For more information, refer to"Upgrading the Time Zone File
      and Timestamp with Time Zone Data"in the 19 Oracle Database
      Globalization Support Guide.
  15. To identify directory objects withsymbolic links in the path name, run
     $ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
      Recreate any directory objects listed,using path names that contain no
      symbolic links.
      
      Some directory object path names maycurrently contain symbolic links.
      
      Starting in Release 18c, symbolic linksare not allowed in directory
      object path names used with BFILE datatypes, the UTL_FILE package, or
      external tables.
  16. (AUTOFIXUP) Gather dictionary statisticsafter the upgrade using the
      command:
      
        EXECUTEDBMS_STATS.GATHER_DICTIONARY_STATS;
      
      Oracle recommends gathering dictionarystatistics after upgrade.
      
      Dictionary statistics provide essentialinformation to the Oracle
      optimizer to help it find efficient SQLexecution plans. After a database
      upgrade, statistics need to bere-gathered as there can now be tables
      that have significantly changed duringthe upgrade or new tables that do
      not have statistics gathered yet.
  17. Gather statistics on fixed objects afterthe upgrade and when there is a
      representative workload on the systemusing the command:
      
        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
      
      This recommendation is given for allpreupgrade runs.
      
      Fixed object statistics provide essentialinformation to the Oracle
      optimizer to help it find efficient SQLexecution plans.  Those
      statistics are specific to the OracleDatabase release that generates
      them, and can be stale upon databaseupgrade.
      
      For information on managing optimizerstatistics, refer to the 11.2.0.4
      Oracle Database Performance Tuning Guide.
  INFORMATION ONLY
  ================
  18. Check the Oracle documentation for theidentified components for their
      specific upgrade procedure.
      
      The database upgrade script will notupgrade the following Oracle
      components:  OLAP Catalog,OWB
      
      The Oracle database upgrade scriptupgrades most, but not all Oracle
      Database components that may beinstalled.  Some components that are not
      upgraded may have their own upgradescripts, or they may be deprecated or
     obsolete.
  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database ORCL
  which are identified above as AFTER UPGRADE"(AUTOFIXUP)" can be resolved by
  executing the following
    SQL>@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
根据日志处理部分问题
五、使用DBUA升级
.bash_profile下修改ORACLE_HOME=$ORACLE_BASE/oracle/product/19.3.0/db_1

[oracle@oracle~]$ cd /u01/app/oracle/product/19.3.0/db_1/bin/
[oracle@oraclebin]$ dbua
40215f6ee2faa2c28.png
675545f6ee30259bd6.png
14635f6ee30ad18eb.png
31055f6ee310b2414.png
5165f6ee3171cecb.png
52825f6ee31cd0578.png
5165f6ee322f381b.png
719115f6ee32dd6a8b.png
六、检查验证
1.查看/etc/oratab
[oracle@oracle~]$ cat /etc/oratab
orcl:/u01/app/oracle/product/19.3.0/db_1:N
2.确认数据库版本
[oracle@oracle~]$ sqlplus / as sysdba
SQL*Plus:Release 19.0.0.0.0 - Production on 星期三 9 23 11:27:152020
Version19.3.0.0.0
Copyright(c) 1982, 2019, Oracle.  All rightsreserved.
连接到:
OracleDatabase 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version19.3.0.0.0
SQL>
3.运行升级后的脚本
SQL>@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
......
Auto-Generatedby:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0Build: 1
Generatedon:            2020-09-23 10:01:12
ForSource Database:     ORCL
SourceDatabase Version: 11.2.0.4.0
ForUpgrade to Version:  19.0.0.0.0
Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied   Further DBA Action
------  ------------------------  ---------- --------------------------------
   13. depend_usr_tables         YES        None.
   14. old_time_zones_exist      YES         None.
   15. dir_symlinks              YES         None.
   16. post_dictionary           YES         None.
   17. post_fixed_objects        NO          Informational only.
                                             Further action is optional.
   18. upg_by_std_upgrd          YES         None.
Thefixup scripts have been run and resolved what they can. However,
thereare still issues originally identified by the preupgrade that
have notbeen remedied and are still present in the database.
Dependingon the severity of the specific issue, and the nature of
theissue itself, that could mean that your database upgrade is not
fullycomplete.  To resolve the outstandingissues, start by reviewing
thepostupgrade_fixups.sql and searching it for the name of
thefailed CHECK NAME or Preupgrade Action Number listed above.
Thereyou will find the original corresponding diagnostic message
from thepreupgrade which explains in more detail what still needs
to bedone.
PL/SQL 过程已成功完成。
会话已更改。
4.检查syssystem用户下无效对象
SQL>  select owner,object_type,count(*) fromdba_objects where status='INVALID'
  2 group by owner,object_type order by owner,object_type;
未选定行
5.检查组件
SQL>select comp_id,status from dba_registry;
COMP_ID                        STATUS
--------------------------------------------------------------------------
CATALOG                        VALID
CATPROC                        VALID
JAVAVM                         VALID
XML                            VALID
CATJAVA                        VALID
APS                            VALID
RAC                            OPTION OFF
OWM                            VALID
CONTEXT                        VALID
XDB                            VALID
ORDIM                          VALID
COMP_ID                        STATUS
--------------------------------------------------------------------------
SDO                            VALID
XOQ                            VALID
APEX                           VALID
已选择 14 行。

回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛 ( 渝ICP备12004239号-4 )

GMT+8, 2022-1-20 22:00 , Processed in 0.106452 second(s), 24 queries .

重庆思庄学习中心论坛-重庆思庄科技有限公司论坛

© 2001-2020

快速回复 返回顶部 返回列表