热门标签
热门文章
- 1JQL语法及Python查询 Jira issue信息
- 22017年上海交通大学软件学院暑期夏令营机试题目_上海交通大学软件学院 夏令营 机考题
- 3STM32智能小车之 循迹_stm32寻迹小车为什么不能直角转弯
- 4SpringBoot依赖之Spring Data Redis一有序集合Sorted Set
- 5kafka2.7.0集群搭建_kafka 2.7
- 6Unity游戏开发实战：C#脚本编程全攻略与深度优化指南_c# unity
- 7Ubuntu无法解析域名DNS指向127.0.0.53问题处理
- 8自然语言处理 - word2vec模型
- 9vue2.0之axios使用详解_vue2 axios 引入直接用吗
- 1006_Linux中如何让程序重启后自动启动
当前位置: article > 正文
oracle升级
作者：人工智能uu | 2024-08-22 00:39:58
赞
踩
oracle升级
11.2.0.4从11g升级到19.11版本，单实例
一、数据库配置变化
因为19c的基础版本是19.3，无法直接从11.2.0.4直接升级到19.11
| 当前环境 | 目标环境 | |
| /u01/app/oracle/product/11g/db_1 | /u01/app/oracle/product/19.11/db_1 | |
| 11.2.0.4 | 19.11 |
二、具体步骤
2.1下载19c安装包，并解压
uzip LINUX.X64_193000_db_home.zip -d /u01/app/oracle/product/19.11/db_12.2安装19.3的软件
- cd $ORACLE_HOME
- ./runInstaller
过一段时间，oracle会弹出root执行界面，复制语句进入teminal执行
执行root文件记录如下
- [root@localhost orasoft]# /u01/app/oracle/product/19.11/db_1/root.sh
- Performing root user operation.
-
- The following environment variables are set as:
- ORACLE_OWNER= oracle
- ORACLE_HOME= /u01/app/oracle/product/19.11/db_1
-
- Enter the full pathname of the local bin directory: [/usr/local/bin]:
- The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)
- [n]: y
- Copying dbhome to /usr/local/bin ...
- The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
- [n]: y
- Copying oraenv to /usr/local/bin ...
- The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
- [n]: y
- Copying coraenv to /usr/local/bin ...
-
- Entries will be added to the /etc/oratab file as needed by
- Database Configuration Assistant when a database is created
- Finished running generic part of root script.
- Now product-specific root actions will be performed.
- Oracle Trace File Analyzer (TFA - Standalone Mode) is available at :
- /u01/app/oracle/product/19.11/db_1/bin/tfactl
-
- Note :
- 1. tfactl will use TFA Service if that service is running and user has been granted access
- 2. tfactl will configure TFA Standalone Mode only if user has no access to TFA Service or TFA is not installed
2.3 执行预检查
1.直接在dbua上查看预检查信息
遇到以下问题：
1.Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is desupported and will be automatically marked as OPTION OFF during the database upgrade if present. Oracle recommends removing OLAP Catalog (OLAP AMD) before database upgrade. This step can be manually performed before the upgrade to reduce downtime.
解决方法solution
Cause : The OLAP Catalog component, AMD, exists in the database. Action : Remove OLAP Catalog by running the 11.2.0.4.0 SQL script $ORACLE_HOME/olap/admin/catnoamd.sql script.
- [oracle@single06 ~]$ sqlplus / as sysdba
-
- SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 19 13:47:12 2021
-
- Copyright (c) 1982, 2013, Oracle. All rights reserved.
-
-
- Connected to:
- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
-
- SQL> @$ORACLE_HOME/olap/admin/catnoamd.sql;
-
- Synonym dropped.
-
- 。。。。
2.Starting with Oracle Database 12c, the local Enterprise Manager Database Control does not exist anymore. The repository will be removed from your database during the upgrade. This step can be manually performed before the upgrade to reduce downtime.
解决方法solution
Cause : The database has an Enterprise Manager Database Control repository. Action : 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.0 ORACLE_HOME.
Step 1: If database control is configured, stop EM Database Control, using the following command
$> emctl stop dbconsole
Step 2: Connect to the database using the SYS account AS SYSDBA
SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql
Without the set echo and serveroutput commands, you will not be able to follow the progress of the script.
先将19c的oracle home中的rdbms/admin/emremove.sql cp 到11g的对应位置
- [oracle@single06 admin]$ emctl stop dbconsole
- Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name.
- [oracle@single06 admin]$ sqlplus / as sysdba
-
- SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 19 14:00:03 2021
-
- Copyright (c) 1982, 2013, Oracle. All rights reserved.
-
-
- Connected to:
- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
-
- SQL> SET ECHO ON;
- SQL> SET SERVEROUTPUT ON;
- SQL> @emremove.sql
- SQL> Rem
- SQL> Rem $Header: rdbms/admin/emremove.sql /main/5 2017/05/28 22:46:05 stanaya Exp $
- 。。。。
- Dropping synonym : ECM_UTIL ...
- Dropping synonym : EMD_MNTR ...
- Dropping synonym : MGMT$ALERT_ANNOTATIONS ...
- Dropping synonym : MGMT$ALERT_CURRENT ...
- Dropping synonym : MGMT$ALERT_HISTORY ...
- Dropping synonym : MGMT$ALERT_NOTIF_LOG ...
- Dropping synonym : MGMT$APPLIED_PATCHES ...
- Dropping synonym : MGMT$APPLIED_PATCHSETS ...
- Dropping synonym : MGMT$APPL_PATCH_AND_PATCHSET ...
- Dropping synonym : MGMT$AUDIT_LOG ...
- 。。。
3.Starting with Oracle Database Release 18, APEX is not upgraded automatically as part of the database upgrade. Refer to My Oracle Support Note 1088970.1 for information about APEX installation and upgrades.
解决方法solution
Cause : The database contains APEX version 3.2.1.00.12. Upgrade APEX to at least version 18.2.0.00.12. Action : Upgrade Oracle Application Express (APEX) manually before the database upgrade.
提示：现在很多客户都不适用apex组件，可以直接删除或者直接忽略
手动删除：
@$ORACLE_HOME/apex/apxremov.sql
- [oracle@single06 admin]$ echo $ORACLE_HOME
- /u01/app/oracle/product/11g/db_1
- [oracle@single06 admin]$ sqlplus / as sysdba
-
- SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 19 14:13:37 2021
-
- Copyright (c) 1982, 2013, Oracle. All rights reserved.
-
-
- Connected to:
- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
-
- SQL> @$ORACLE_HOME/apex/apxremov.sql
- ...Removing Application Express
- old 1: alter session set current_schema = &APPUN
- new 1: alter session set current_schema = APEX_030200
-
- Session altered.
-
- 。。。。
4.清空回收站
- SQL> purge recyclebin;
-
- Recyclebin purged.
5.执行utlrp.sql脚本并检查sys/system用户下是否存在不可忽略的失效对象
失效对象将对升级时间产生巨大影响，甚至会直接引起宕机
解决方法：
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
--编译无效对象脚本utlrp.sql
- SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
-
- TIMESTAMP
- --------------------------------------------------------------------------------
- COMP_TIMESTAMP UTLRP_BGN 2021-08-19 14:24:55
-
- 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 2021-08-19 14:24:56
-
- DOC> The following query reports the number of objects that have compiled
- DOC> with errors.
- 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 errors 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>#
-
- ERRORS DURING RECOMPILATION
- ---------------------------
- 0
-
-
- Function created.
-
-
- PL/SQL procedure successfully completed.
-
-
- Function dropped.
-
-
- PL/SQL procedure successfully completed.
- select 'Alter '||object_type||' '||owner||'.'||object_name||' compile;' from dba_objects where status = 'INVALID';
- 查看无效对象
6.执行预检查文本
- [oracle@single06 ~]$ /u01/app/oracle/product/11g/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.11/db_1/rdbms/admin/preupgrade.jar FILE TEXT DIR output_dir
- ==================
- PREUPGRADE SUMMARY
- ==================
- /home/oracle/output_dir/preupgrade.log
- /home/oracle/output_dir/preupgrade_fixups.sql
- /home/oracle/output_dir/postupgrade_fixups.sql
-
- Execute fixup scripts as indicated below:
-
- Before upgrade:
-
- Log into the database and execute the preupgrade fixups
- @/home/oracle/output_dir/preupgrade_fixups.sql
-
- After the upgrade:
-
- Log into the database and execute the postupgrade fixups
- @/home/oracle/output_dir/postupgrade_fixups.sql
-
- Preupgrade complete: 2021-08-19T15:28:01
- SQL> show parameter process;
-
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- aq_tm_processes integer 1
- cell_offload_processing boolean TRUE
- db_writer_processes integer 1
- gcs_server_processes integer 0
- global_txn_processes integer 1
- job_queue_processes integer 1000
- log_archive_max_processes integer 4
- processes integer 150
- processor_group_name string
- SQL> alter system set processes=300;
- alter system set processes=300
- *
- ERROR at line 1:
- ORA-02095: specified initialization parameter cannot be modified
-
-
- SQL> alter system set processes=300 scope=spfile;
-
- System altered.
-
- [oracle@single06 ~]$ sqlplus / as sysdba
-
- SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 19 15:28:59 2021
-
- Copyright (c) 1982, 2013, Oracle. All rights reserved.
-
-
- Connected to:
- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
-
- SQL> @/home/oracle/output_dir/preupgrade_fixups.sql
- Executing Oracle PRE-Upgrade Fixup Script
-
- Auto-Generated by: Oracle Preupgrade Script
- Version: 19.0.0.0.0 Build: 1
- Generated on: 2021-08-19 15:27:59
-
- For Source Database: SINGLE06
- Source Database Version: 11.2.0.4.0
- For Upgrade 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. mv_refresh NO Informational only.
- Further action is optional.
- 3. mv_refresh NO Informational only.
- Further action is optional.
- 4. pre_fixed_objects YES None.
- 5. tablespaces_info NO Informational only.
- Further action is optional.
- 6. exf_rul_exists NO Informational only.
- Further action is optional.
- 7. min_archive_dest_size NO Informational only.
- Further action is optional.
- 8. rman_recovery_version NO Informational only.
- Further action is optional.
-
- The fixup scripts have been run and resolved what they can. However,
- there are still issues originally identified by the preupgrade that
- have not been remedied and are still present in the database.
- Depending on the severity of the specific issue, and the nature of
- the issue itself, that could mean that your database is not ready
- for upgrade. To resolve the outstanding issues, start by reviewing
- the preupgrade_fixups.sql and searching it for the name of
- the failed CHECK NAME or Preupgrade Action Number listed above.
- There you will find the original corresponding diagnostic message
- from the preupgrade which explains in more detail what still needs
- to be done.
-
- PL/SQL procedure successfully completed.
7.查看预检查日志，操作其中推荐步骤
7.1.检查物化视图
SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8; 7.2
- log
- (AUTOFIXUP) Gather stale data dictionary statistics prior to database
- upgrade in off-peak time using:
-
- EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
-
- 执行：
- SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
-
- PL/SQL procedure successfully completed.
2.4 进入oracle home并执行dbua
升级成功。
升级后：
10. To identify directory objects with symbolic 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.
- SQL> @$ORACLE_HOME/rdbms/admin/utldirsymlink.sql
- No DIRECTORY OBJECTS with symlinks found.
-
- PL/SQL procedure successfully completed.
-
- No errors.
三、19.3升级到19.11
3.1 步骤：
解压包p32545008_190000_Linux-x86-64.zip到32545013
解压Opatch 12.2.1.25到db_home
查阅readme文件确定db需要的包为32545013和32579761
进入32579761目录，查阅readme.html文件
执行：
- 冲突预检查
- 关闭数据库和监听
- 执行apply操作
由于32579761包并没有readme文件，于是按照上一步操作为其打补丁
3.2 报错
- [oracle@single06 32545013]$ $ORACLE_HOME/OPatch/opatch apply
- Oracle Interim Patch Installer version 12.2.0.1.25
- Copyright (c) 2021, Oracle Corporation. All rights reserved.
-
-
- Oracle Home : /u01/app/oracle/product/19.11/db_1
- Central Inventory : /u01/app/oraInventory
- from : /u01/app/oracle/product/19.11/db_1/oraInst.loc
- OPatch version : 12.2.0.1.25
- OUI version : 12.2.0.7.0
- Log file location : /u01/app/oracle/product/19.11/db_1/cfgtoollogs/opatch/opatch2021-08-24_10-24-06og
-
- Verifying environment and performing prerequisite checks...
- Prerequisite check "CheckActiveFilesAndExecutables" failed.
- The details are:
-
-
- Following active executables are not used by opatch process :
- /u01/app/oracle/product/19.11/db_1/bin/oracle
-
- Following active executables are used by opatch process :
-
- UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.
- Log file location: /u01/app/oracle/product/19.11/db_1/cfgtoollogs/opatch/opatch2021-08-24_10-24-06Ag
-
- OPatch failed with error code 73
发现为关闭数据库和监听，导致仍然存在活动文件，打补丁失败。于是重新关闭数据库和监听，成功打补丁。
- SQL> select * from user_mviews;
-
- OWNER MVIEW_NAME CONTAINER_NAME QUERY QUERY_LEN U UPDATE_LOG MASTER_ROLLBACK_SEG MASTER_LINK R REWRITE_C REFRES REFRESH_ BUILD_MOD FAST_REFRESHABLE LAST_REF LAST_REFRESH_DATE STALENESS AFTER_FAST_REFRESH U U U U U U COMPILE_STATE U STALE_SINCE NUM_PCT_TABLES NUM_FRESH_PCT_REGIONS NUM_STALE_PCT_REGIONS
- ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ---------- - ------------------------------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- - --------- ------ -------- --------- ------------------ -------- ------------------ ------------------- ------------------- - - - - - - ------------------- - ------------------ -------------- --------------------- ---------------------
- SCOTT PERSON01_MV_FAST01 PERSON01_MV_FAST01 SELECT "REMOTE_PERSON01"."PID" "PID","REMOTE_PERSON01"."NAME" "NAME","REMOTE_PER 309 N @"MYLINK_FROMSINGLE06" N GENERAL DEMAND FAST IMMEDIATE DIRLOAD_DML FAST 31-AUG-21 UNDEFINED UNDEFINED N N N N N N VALID N 0
- SCOTT PERSON01_MV_COMPLETE01 PERSON01_MV_COMPLETE01 SELECT "REMOTE_PERSON01"."PID" "PID","REMOTE_PERSON01"."NAME" "NAME","REMOTE_PER 309 N @"MYLINK_FROMSINGLE06" N GENERAL DEMAND COMPLETE IMMEDIATE DIRLOAD_DML COMPLETE 31-AUG-21 UNDEFINED UNDEFINED N N N N N N VALID N 0
- SCOTT PERSON01_MV_FORCE01 PERSON01_MV_FORCE01 SELECT "REMOTE_PERSON01"."PID" "PID","REMOTE_PERSON01"."NAME" "NAME","REMOTE_PER 309 N @"MYLINK_FROMSINGLE06" N GENERAL DEMAND FORCE IMMEDIATE DIRLOAD_DML FAST 31-AUG-21 UNDEFINED UNDEFINED N N N N N N VALID N 0
- SCOTT PERSON01_MV_NERVER01 PERSON01_MV_NERVER01 SELECT "REMOTE_PERSON01"."PID" "PID","REMOTE_PERSON01"."NAME" "NAME","REMOTE_PER 309 N @"MYLINK_FROMSINGLE06" N GENERAL DEMAND FORCE IMMEDIATE DIRLOAD_DML COMPLETE 31-AUG-21 UNDEFINED UNDEFINED N N N N N N VALID N 0
- SCOTT ADDRESS01_MV_COMPLETE01 ADDRESS01_MV_COMPLETE01 SELECT "LOCAL_ADDRESS01"."AID" "AID","LOCAL_ADDRESS01"."ADDRESS1" "ADDRESS1","LO 325 N N GENERAL DEMAND COMPLETE IMMEDIATE DIRLOAD_DML COMPLETE 31-AUG-21 FRESH FRESH N N N N N N VALID N 0
- SCOTT ADDRESS01_MV_NERVER01 ADDRESS01_MV_NERVER01 SELECT "LOCAL_ADDRESS01"."AID" "AID","LOCAL_ADDRESS01"."ADDRESS1" "ADDRESS1","LO 325 N N GENERAL DEMAND FORCE IMMEDIATE DIRLOAD_DML COMPLETE 31-AUG-21 NEEDS_COMPILE NEEDS_COMPILE N N N N N N NEEDS_COMPILE N 30-AUG-21 0
- SCOTT ADDRESS_PERSON01_COMPLETE01 ADDRESS_PERSON01_COMPLETE01 select p.pid as id, p.name, p.name2, a.address1,a.address2 from scott.remote_per 151 N N GENERAL DEMAND COMPLETE IMMEDIATE NO COMPLETE 31-AUG-21 FRESH NA N N N N N N VALID N 0
- SCOTT ADDRESS_PERSON01_FORCE01 ADDRESS_PERSON01_FORCE01 select p.pid as id, p.name, p.name2, a.address1,a.address2 from scott.remote_per 151 N N GENERAL DEMAND FORCE IMMEDIATE NO COMPLETE 31-AUG-21 FRESH NA N N N N N N VALID N 0
- SCOTT ADDRESS_PERSON01_FAST01 ADDRESS_PERSON01_FAST01 select p.pid as id, p.name, p.name2, a.address1,a.address2 from scott.remote_per 151 N N GENERAL DEMAND FORCE IMMEDIATE NO COMPLETE 31-AUG-21 FRESH NA N N N N N N VALID N 30-AUG-21 0
- SCOTT ADDRESS_PERSON01_NERVER01 ADDRESS_PERSON01_NERVER01 select p.pid as id, p.name, p.name2, a.address1,a.address2 from scott.remote_per 151 N N GENERAL DEMAND FORCE IMMEDIATE NO COMPLETE 31-AUG-21 NEEDS_COMPILE NEEDS_COMPILE N N N N N N NEEDS_COMPILE N 30-AUG-21 0
-
- 10 rows selected.
-
- SQL> spool off;
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/人工智能uu/article/detail/1013950
推荐阅读
相关标签
