oracle版本升级步骤

2024-06-05 13:38
文章标签 oracle 步骤 版本升级

本文主要是介绍oracle版本升级步骤,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

大致步骤:

 1,核实数据库和操作系统相关信息 

  • 检查升级条件
  • 安装12c数据库软件
  • 执行dbua升级db或者采用手工执行命令的方式,但根据官方资料看,建议放弃这个想法,DBUA 真的简化了很多,如果手工执行,会多很多步骤,这样会增加出错的概率。(这里用的手工命令)
  • 检查数据库状态和无效对象
     
[oracle@zg3 soft]$ uname -a
Linux zg3 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
[oracle@zg3 soft]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 15 16:08:44 2018Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select name from v$database;NAME
---------
ORCL
SQL> select * from v$version;BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> col comp_name for a35
SQL> col version for a20
SQL> col name for a20
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
COMP_NAME                        VERSION              STATUS
------------------------------ -------------------- ----------------------
OWB                            11.2.0.3.0           VALID
Oracle Application Express     3.2.1.00.12          VALID
Oracle Enterprise Manager      11.2.0.3.0           VALID
OLAP Catalog                   11.2.0.3.0           VALID
Spatial                        11.2.0.3.0           VALID
Oracle Multimedia              11.2.0.3.0           VALID
Oracle XML Database            11.2.0.3.0           VALID
Oracle Text                    11.2.0.3.0           VALID
Oracle Expression Filter       11.2.0.3.0           VALID
Oracle Rules Manager           11.2.0.3.0           VALID
Oracle Workspace Manager       11.2.0.3.0           VALID
Oracle Database Catalog Views  11.2.0.3.0           VALID
Oracle Database Packages and Types 11.2.0.3.0       VALID
JServer JAVA Virtual Machine   11.2.0.3.0           VALID
Oracle XDK                     11.2.0.3.0            VALID
Oracle Database Java Packages  11.2.0.3.0           VALID
OLAP Analytic Workspace        11.2.0.3.0           VALID
Oracle OLAP API                11.2.0.3.0           VALID18 rows selected.
SQL> SELECT name, value FROM v$parameter WHERE name = 'compatible';

 2, rman对数据库执行全备

[oracle@zg3 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sat Sep 15 16:34:01 2018Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: ORCL (DBID=1514683624)RMAN> backup database plus archivelog delete input format '/oracle/back/full_%U.dbf';
…………………………

 

3,关闭数据库和监听

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@zg3 ~]$ lsnrctl stopLSNRCTL for Linux: Version 11.2.0.3.0 - Production on 15-SEP-2018 16:40:45Copyright (c) 1991, 2011, Oracle.  All rights reserved.Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
[oracle@zg3 ~]$ ps -ef |grep ora

4,备份ORACLE_HOME和oraInventory

tar –cvf oraInventory.zip /oracle/app/oraInventory/
tar –cvf product.zip /oracle/app/oracle/product/

5,上传并解压11.2.0.4安装包

[root@zg3 oracle]# cd database/
[root@zg3 database]# ls
install  readme.html  response  rpm  runInstaller  sshsetup  stage  welcome.html

6,开始安装11.2.0.4软件

 

 

 这里替换了软件目录

 

 

[root@zg3 ~]# /oracle/app/oraInventory2/orainstRoot.sh 
Changing permissions of /oracle/app/oraInventory2.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.Changing groupname of /oracle/app/oraInventory2 to oinstall.
The execution of the script is complete.
[root@zg3 ~]# /oracle/app/oracle/product/11.2.0/db_2/root.sh 
Performing root user operation for Oracle 11g The following environment variables are set as:ORACLE_OWNER= oracleORACLE_HOME=  /oracle/app/oracle/product/11.2.0/db_2Enter 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]: yCopying dbhome to /usr/local/bin ...
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.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.
Finished product-specific root actions.

(监听配置这一步根据具体情况,可以选择直接将原版本的监听文件拷贝过来)

到这里11.2.0.4的软件就已经安装完成,但是还需要进行后续的升级操作。监听配置完会自动跳转下面的界面,这里直接关闭,跳过报错,手动进行后续的操作。

修改环境变量:

[root@zg3 ~]# su - oracle
[oracle@zg3 ~]$ vi .bash_profile 
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_2  --改成新的目录
[oracle@zg3 ~]$ source ~/.bash_profile 
[oracle@zg3 ~]$ echo $ORACLE_HOME
/oracle/app/oracle/product/11.2.0/db_2
[oracle@zg3 ~]$ vi /etc/oratab
orcl:/oracle/app/oracle/product/11.2.0/db_2:N  --改成新的目录

拷贝监听文件和参数文件:

[oracle@zg3 ~]$ cp /oracle/app/oracle/product/11.2.0/db_1/network/admin/* /oracle/app/oracle/product/11.2.0/db_2/network/admin/
[oracle@zg3 ~]$ cp /oracle/app/oracle/product/11.2.0/db_1/dbs/* /oracle/app/oracle/product/11.2.0/db_2/dbs/

执行预升级脚本检查

[oracle@zg3 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 15 17:54:47 2018Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to an idle instance.SQL> startup upgrade;
ORACLE instance started.Total System Global Area 4375998464 bytes
Fixed Size                  2260328 bytes
Variable Size             956301976 bytes
Database Buffers         3405774848 bytes
Redo Buffers               11661312 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlu112i.sql  --执行升级检查
Oracle Database 11.2 Pre-Upgrade Information Tool 09-15-2018 17:56:45
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name:          ORCL
--> version:       11.2.0.3.0
--> compatible:    11.2.0.0.0
--> blocksize:     8192
--> platform:      Linux x86 64-bit
--> timezone file: V14
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 917 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 624 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No obsolete parameters found. No changes are required
.**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
--> Oracle XDK for Java          [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
--> OLAP Analytic Workspace      [upgrade]  VALID
--> OLAP Catalog                 [upgrade]  VALID
--> EM Repository                [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Spatial                      [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
--> Oracle Application Express   [upgrade]  VALID
... APEX will only be upgraded if the version of APEX in
... the target Oracle home is higher than the current one.
--> Oracle OLAP API              [upgrade]  VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle bin is empty.
WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER APEX_030200 has dependent objects.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:EXECUTE dbms_stats.gather_dictionary_stats;**********************************************************************
Oracle recommends removing all hidden parameters prior to upgrading.To view existing hidden parameters execute the following command
while connected AS SYSDBA:SELECT name,description from SYS.V$PARAMETER WHERE nameLIKE '\_%' ESCAPE '\'Changes will need to be made in the init.ora or spfile.**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.To view existing non-default events execute the following commands
while connected AS SYSDBA:Events:SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE'Trace Events:SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'Changes will need to be made in the init.ora or spfile.

升级之前执行 EXECUTE dbms_stats.gather_dictionary_stats;收集统计信息,缩短升级时间。而且需要清空回收站PURGE DBA_RECYCLEBIN;

开启闪回,手动创建还原点

 

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 4375998464 bytes
Fixed Size                  2260328 bytes
Variable Size             956301976 bytes
Database Buffers         3405774848 bytes
Redo Buffers               11661312 bytes
Database mounted.
SQL>alter database flashback on;
SQL> create restore point up_rollback guarantee flashback database;
SQL> select * from v$restore_point;1048597                     2 YES     52428800
15-SEP-18 06.07.00.000000000 PMYES
UP_ROLLBACK
SQL> shutdown immediate;
SQL> startup upgrade;
SQL>@?/rdbms/admin/utlu112i.sql   --再次运行检查

执行升级操作:

SQL> set echo on
SQL> spool /oracle/upgrade.log
SQL> set time on
18:12:02 SQL> @?/rdbms/admin/catupgrd.sql
………………

 运行utlrp.sql编译失效对象

SQL> startup
ORACLE instance started.Total System Global Area 4375998464 bytes
Fixed Size                  2260328 bytes
Variable Size            1023410840 bytes
Database Buffers         3338665984 bytes
Redo Buffers               11661312 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlrp
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2018-09-15 18:46:01DOC>   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  2018-09-15 18:46:43DOC> 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
-------------------0DOC> 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
---------------------------0Function created.PL/SQL procedure successfully completed.Function dropped.PL/SQL procedure successfully completed.

至此数据库已经升级完成,查看各组件版本号:

SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;COMP_NAME                      VERSION              STATUS
------------------------------ -------------------- ----------------------
OWB                            11.2.0.3.0           VALID
Oracle Application Express     3.2.1.00.12          VALID
Oracle Enterprise Manager      11.2.0.4.0           VALID
OLAP Catalog                   11.2.0.4.0           VALID
Spatial                        11.2.0.4.0           VALID
Oracle Multimedia              11.2.0.4.0           VALID
Oracle XML Database            11.2.0.4.0           VALID
Oracle Text                    11.2.0.4.0           VALID
Oracle Expression Filter       11.2.0.4.0           VALID
Oracle Rules Manager           11.2.0.4.0           VALID
Oracle Workspace Manager       11.2.0.4.0           VALID
Oracle Database Catalog Views  11.2.0.4.0           VALID
Oracle Database Packages and Types 11.2.0.4.0           VALID
JServer JAVA Virtual Machine   11.2.0.4.0           VALID
Oracle XDK                     11.2.0.4.0           VALID
Oracle Database Java Packages  11.2.0.4.0           VALID
OLAP Analytic Workspace        11.2.0.4.0           VALID
Oracle OLAP API                11.2.0.4.0           VALID18 rows selected.

处理OWB版本问题:

SQL> @?/owb/UnifiedRepos/clean_owbsys.sql
SQL> @?/owb/UnifiedRepos/cat_owb.sql
SQL> @?/owb/UnifiedRepos/reset_owbcc_home.sql
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
COMP_NAME                           VERSION              STATUS
----------------------------------- -------------------- ----------------------
OWB                                 11.2.0.4.0           VALID
Oracle Application Express          3.2.1.00.12          VALID
Oracle Enterprise Manager           11.2.0.4.0           VALID
OLAP Catalog                        11.2.0.4.0           VALID
Spatial                             11.2.0.4.0           VALID
Oracle Multimedia                   11.2.0.4.0           VALID
Oracle XML Database                 11.2.0.4.0           VALID
Oracle Text                         11.2.0.4.0           VALID
Oracle Expression Filter            11.2.0.4.0           VALID
Oracle Rules Manager                11.2.0.4.0           VALID
Oracle Workspace Manager            11.2.0.4.0           VALID
Oracle Database Catalog Views       11.2.0.4.0           VALID
Oracle Database Packages and Types  11.2.0.4.0           VALID
JServer JAVA Virtual Machine        11.2.0.4.0           VALID
Oracle XDK                          11.2.0.4.0           VALID
Oracle Database Java Packages       11.2.0.4.0           VALID
OLAP Analytic Workspace             11.2.0.4.0           VALID
Oracle OLAP API                     11.2.0.4.0           VALID18 rows selected.

检查无效对象

SQL> select * from dba_objects where status<>'VALID';no rows selected

升级成功后删除原来的目录,通过EMCA重建EM  (不用EM可忽略)

[oracle@zg3 app]$ ls
oracle  oraInventory  oraInventory2
[oracle@zg3 app]$ pwd
/oracle/app
[oracle@zg3 app]$ rm -rf oraInventory
[oracle@zg3 11.2.0]$ pwd
/oracle/app/oracle/product/11.2.0
[oracle@zg3 11.2.0]$ rm -rf db_1

删除restore point

SQL> select * from v$restore_point;SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME                                                                        RESTORE_POINT_TIME                                                        PRE NAME
---------- --------------------- --- ------------ --------------------------------------------------------------------------- --------------------------------------------------------------------------- --- --------------------1048597                     2 YES    629145600 15-SEP-18 06.07.00.000000000 PM                                                              YES UP_ROLLBACKSQL> drop  restore point up_rollback;Restore point dropped.
SQL> select * from v$restore_point;no rows selected

 

这篇关于oracle版本升级步骤的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1033210

相关文章

mac安装nvm(node.js)多版本管理实践步骤

《mac安装nvm(node.js)多版本管理实践步骤》:本文主要介绍mac安装nvm(node.js)多版本管理的相关资料,NVM是一个用于管理多个Node.js版本的命令行工具,它允许开发者在... 目录NVM功能简介MAC安装实践一、下载nvm二、安装nvm三、安装node.js总结NVM功能简介N

Nginx实现动态封禁IP的步骤指南

《Nginx实现动态封禁IP的步骤指南》在日常的生产环境中,网站可能会遭遇恶意请求、DDoS攻击或其他有害的访问行为,为了应对这些情况,动态封禁IP是一项十分重要的安全策略,本篇博客将介绍如何通过NG... 目录1、简述2、实现方式3、使用 fail2ban 动态封禁3.1 安装 fail2ban3.2 配

Nginx设置连接超时并进行测试的方法步骤

《Nginx设置连接超时并进行测试的方法步骤》在高并发场景下,如果客户端与服务器的连接长时间未响应,会占用大量的系统资源,影响其他正常请求的处理效率,为了解决这个问题,可以通过设置Nginx的连接... 目录设置连接超时目的操作步骤测试连接超时测试方法:总结:设置连接超时目的设置客户端与服务器之间的连接

Debezium 与 Apache Kafka 的集成方式步骤详解

《Debezium与ApacheKafka的集成方式步骤详解》本文详细介绍了如何将Debezium与ApacheKafka集成,包括集成概述、步骤、注意事项等,通过KafkaConnect,D... 目录一、集成概述二、集成步骤1. 准备 Kafka 环境2. 配置 Kafka Connect3. 安装 D

Spring AI集成DeepSeek的详细步骤

《SpringAI集成DeepSeek的详细步骤》DeepSeek作为一款卓越的国产AI模型,越来越多的公司考虑在自己的应用中集成,对于Java应用来说,我们可以借助SpringAI集成DeepSe... 目录DeepSeek 介绍Spring AI 是什么?1、环境准备2、构建项目2.1、pom依赖2.2

oracle DBMS_SQL.PARSE的使用方法和示例

《oracleDBMS_SQL.PARSE的使用方法和示例》DBMS_SQL是Oracle数据库中的一个强大包,用于动态构建和执行SQL语句,DBMS_SQL.PARSE过程解析SQL语句或PL/S... 目录语法示例注意事项DBMS_SQL 是 oracle 数据库中的一个强大包,它允许动态地构建和执行

Goland debug失效详细解决步骤(合集)

《Golanddebug失效详细解决步骤(合集)》今天用Goland开发时,打断点,以debug方式运行,发现程序并没有断住,程序跳过了断点,直接运行结束,网上搜寻了大量文章,最后得以解决,特此在这... 目录Bug:Goland debug失效详细解决步骤【合集】情况一:Go或Goland架构不对情况二:

SpringBoot+MyBatis-Flex配置ProxySQL的实现步骤

《SpringBoot+MyBatis-Flex配置ProxySQL的实现步骤》本文主要介绍了SpringBoot+MyBatis-Flex配置ProxySQL的实现步骤,文中通过示例代码介绍的非常详... 目录 目标 步骤 1:确保 ProxySQL 和 mysql 主从同步已正确配置ProxySQL 的

nginx部署https网站的实现步骤(亲测)

《nginx部署https网站的实现步骤(亲测)》本文详细介绍了使用Nginx在保持与http服务兼容的情况下部署HTTPS,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值... 目录步骤 1:安装 Nginx步骤 2:获取 SSL 证书步骤 3:手动配置 Nginx步骤 4:测

Springboot 中使用Sentinel的详细步骤

《Springboot中使用Sentinel的详细步骤》文章介绍了如何在SpringBoot中使用Sentinel进行限流和熔断降级,首先添加依赖,配置Sentinel控制台地址,定义受保护的资源,... 目录步骤 1: 添加 Sentinel 依赖步骤 2: 配置 Sentinel步骤 3: 定义受保护的