本文主要是介绍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 ora4，备份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.html6，开始安装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版本升级步骤的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
