|
一、环境 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.查看系统参数是否需要修改 三、安装oracle19.3.0.0数据库软件 安装需要的包 四、执行升级前的脚本 [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 六、检查验证 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.检查sys和system用户下无效对象 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 行。
| 
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2020-9-26 14:44:30
