单实例环境下Oracle 11.2.0.3升级到11.2.0.4的过程

最新推荐文章于 2022-07-26 19:20:21 发布

csdn20140422

最新推荐文章于 2022-07-26 19:20:21 发布

阅读量529

点赞数

文章标签：数据库

本文是单实例环境下Oracle 11.2.0.3升级到11.2.0.4的过程，先升级Database Software，再升级Oracle Instance。
Oracle 11.2.0.4的Patchset No:13390677，可以直接从Oracle Support下载到。
# 后跟命令表示以操作系统下root用户操作;
$ 后跟命令表示以操作系统下oracle用户操作;

升级前准备：
1、查看数据库相关信息:
SQL> select name from v$database;
NAME
------------------
ORA11G

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

2、备份数据库
可以参考该博文进行数据库备份：数据库备份http://blog.itpub.net/29439655/viewspace-1988549/

###停止数据库
SQL> shutdown immediate

###停止监听
SQL> host lsnrctl stop

###停止EM
SQL> host emctl stopdbconsole

###查看oracle进程，检查是否已经停止完毕
# ps -ef|grepora

备份老的ORACLE_HOME和oraInventory
#tar –cvf product.zip /u01/app/oracle/product/
#tar -cvf oraInventory.zip /u01/app/oraInventory/

3、安装数据库软件
目标版本是11.2.0.4，为目标版本创建相应的主目录。
$ mkdir -p /u01/app/oracle/product/11.2.0.4/dbhome_1
将安装介质上传服务器，并解压。

通过命令行的方式安装数据库软件。可以直接参考博文：http://blog.itpub.net/29439655/viewspace-1988530/
生成响应文件模板，将database/response文件夹下的db_install.rsp文件复制一份出来，重命名为dbupg.rsp，对里面的参数进行修改。
$ vi /home/oracle/dbupg.rsp
#--------------------------------------------------------------------
#以下参数根据实际情况更改,一般也无需更改
oracle.install.option=UPGRADE_DB
ORACLE_HOSTNAME=dbbackup
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
SELECTED_LANGUAGES=en,zh_CN
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oinstall
DECLINE_SECURITY_UPDATES=true

$ ./runInstaller -silent -noconfig -ignorePrereq -responseFile /home/oracle/dbupg.rsp
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 248510 MB Passed
Checking swap space: must be greater than 150 MB. Actual 8191 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-02-14_05-20-01PM. Please wait ...[oracle@dbbackup database]$ You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2016-02-14_05-20-01PM.log
The installation of Oracle Database 11g was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2016-02-14_05-20-01PM.log' for more details.
As a root user, execute the following script(s):
1. /u01/app/oracle/product/11.2.0.4/dbhome_1/root.sh
Successfully Setup Software.
##########################################################
Start Database Upgrade Assistant to upgrade the database.
##########################################################
# /u01/app/oracle/product/11.2.0.4/dbhome_1/root.sh
Check /u01/app/oracle/product/11.2.0.4/dbhome_1/install/root_dbbackup_2016-02-14_17-24-19.log for the output of root script

至此11.2.0.4的软件就已经装完了，修改Oracle环境变量

4、修改环境变量及参数
$ vi .bash_profile
-------修改如下行,将11.2.0.3改成11.2.0.4即可
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1
$ source .bash_profile

$ vi /etc/oratab
-------修改如下行11.2.3为11.2.4
ora11g:/u01/app/oracle/product/11.2.0.4/dbhome_1:N

5、配置监听

$ cp /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/* $ORACLE_HOME/dbs
$ cp -r /u01/app/oracle/product/11.2.0.3/dbhome_1/network/admin/* /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/

6、升级前环境检查
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 14 17:39:58 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 2505338880 bytes
Fixed Size 2255832 bytes
Variable Size 603980840 bytes
Database Buffers 1879048192 bytes
Redo Buffers 20054016 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 02-15-2016 10:06:04
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: ORA11G
--> 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: 619 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: ON
**********************************************************************
FlashbackInfo:
--> name: /u01/app/oracle/fast_recovery_area
--> limit: 4122 MB
--> used: 1166 MB
--> size: 4122 MB
--> reclaim: 0 MB
--> files: 4
WARNING: --> Flashback Recovery Area Set. Please ensure adequate disk space in recover
y areas before performing an upgrade.
.
**********************************************************************
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 name
LIKE '\_%' 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$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'

Trace Events:
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'
Changes will need to be made in the init.ora or spfile.
**********************************************************************

7、执行升级操作
SQL> set echo on
SQL> spool /home/oracle/upgrade.log
SQL> set time on;
10:10:37 SQL> @?/rdbms/admin/catupgrd.sql
.....
Oracle Database 11.2 Post-Upgrade Status Tool 02-15-2016 10:43:10
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.4.0 00:10:01
JServer JAVA Virtual Machine
. VALID 11.2.0.4.0 00:03:10
Oracle Workspace Manager
. VALID 11.2.0.4.0 00:00:29
OLAP Analytic Workspace
. VALID 11.2.0.4.0 00:00:55
OLAP Catalog
. VALID 11.2.0.4.0 00:00:42
Oracle OLAP API
. VALID 11.2.0.4.0 00:00:17
Oracle Enterprise Manager
. VALID 11.2.0.4.0 00:05:42
Oracle XDK
. VALID 11.2.0.4.0 00:00:26
Oracle Text
. VALID 11.2.0.4.0 00:00:24
Oracle XML Database
. VALID 11.2.0.4.0 00:01:47
Oracle Database Java Packages
. VALID 11.2.0.4.0 00:00:09
Oracle Multimedia
. VALID 11.2.0.4.0 00:01:36
Spatial
. VALID 11.2.0.4.0 00:04:08
Oracle Expression Filter
. VALID 11.2.0.4.0 00:00:06
Oracle Rules Manager
. VALID 11.2.0.4.0 00:00:06
Oracle Application Express
. VALID 3.2.1.00.12
Final Actions
. 00:00:00
Total Upgrade Time: 00:30:05

PL/SQL procedure successfully completed.

10:43:10 SQL>
10:43:10 SQL> SET SERVEROUTPUT OFF
10:43:10 SQL> SET VERIFY ON
10:43:10 SQL> commit;

Commit complete.

10:43:10 SQL>
10:43:10 SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
10:43:20 SQL>
10:43:20 SQL>
10:43:20 SQL> DOC
10:43:20 DOC>#######################################################################
10:43:20 DOC>#######################################################################
10:43:20 DOC>
10:43:20 DOC> The above sql script is the final step of the upgrade. Please
10:43:20 DOC> review any errors in the spool log file. If there are any errors in
10:43:20 DOC> the spool file, consult the Oracle Database Upgrade Guide for
10:43:20 DOC> troubleshooting recommendations.
10:43:20 DOC>
10:43:20 DOC> Next restart for normal operation, and then run utlrp.sql to
10:43:20 DOC> recompile any invalid application objects.
10:43:20 DOC>
10:43:20 DOC> If the source database had an older time zone version prior to
10:43:20 DOC> upgrade, then please run the DBMS_DST package. DBMS_DST will upgrade
10:43:20 DOC> TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped
10:43:20 DOC> with Oracle.
10:43:20 DOC>
10:43:20 DOC>#######################################################################
10:43:20 DOC>#######################################################################
10:43:20 DOC>#
10:43:20 SQL>
10:43:20 SQL> Rem Set errorlogging off
10:43:20 SQL> SET ERRORLOGGING OFF;
10:43:20 SQL>
10:43:20 SQL> REM END OF CATUPGRD.SQL
10:43:20 SQL>
10:43:20 SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.
10:43:20 SQL> REM This forces user to start a new sqlplus session in order
10:43:20 SQL> REM to connect to the upgraded db.
10:43:20 SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

以上catupgrd.sql脚本运行了40分钟左右，执行完之后会shutdown immediate数据库。
8、运行utlrp.sql脚本编译失效对象
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 15 10:46:28 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2505338880 bytes
Fixed Size 2255832 bytes
Variable Size 687866920 bytes
Database Buffers 1795162112 bytes
Redo Buffers 20054016 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlrp
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2016-02-15 10:46:51

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 2016-02-15 10:47:28

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.

9、至此数据库已经升级完成，查看各组件版本号：
SQL> col version format a20
SQL> col comp_name format a40
SQL> col status format a10
SQL> select comp_name,status,version from dba_server_registry;
COMP_NAME STATUS VERSION
---------------------------------------- ---------- --------------------
OWB VALID 11.2.0.3.0
Oracle Application Express VALID 3.2.1.00.12
Oracle Enterprise Manager VALID 11.2.0.4.0
OLAP Catalog VALID 11.2.0.4.0
Spatial VALID 11.2.0.4.0
Oracle Multimedia VALID 11.2.0.4.0
Oracle XML Database VALID 11.2.0.4.0
Oracle Text VALID 11.2.0.4.0
Oracle Expression Filter VALID 11.2.0.4.0
Oracle Rules Manager VALID 11.2.0.4.0
Oracle Workspace Manager VALID 11.2.0.4.0
Oracle Database Catalog Views VALID 11.2.0.4.0
Oracle Database Packages and Types VALID 11.2.0.4.0
JServer JAVA Virtual Machine VALID 11.2.0.4.0
Oracle XDK VALID 11.2.0.4.0
Oracle Database Java Packages VALID 11.2.0.4.0
OLAP Analytic Workspace VALID 11.2.0.4.0
Oracle OLAP API VALID 11.2.0.4.0
18 rows selected.

10、检查无效对象
SQL> select * from dba_objects where status !='VALID';
no rows selected

11、启动监听
$ lsnrctl start
$ lsnrctl status

12、升级成功后删除原来的目录，通过EMCA重建EM
[oracle@db01 /]$ rm -rf/DBSoft/Product/11.2.3/
手工创建EM资料库:
####emca -repos drop
[oracle@db01 /]$ emca -reposdrop
####emca -repos create
[oracle@db01 /]$ emca -reposcreate
###emca -config dbcontrol db
[oracle@db01 /]$ emca-config dbcontrol db
至此，升级已经全部完成。

参考博文：http://blog.youkuaiyun.com/wuweilong/article/details/41627189

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29439655/viewspace-1988896/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29439655/viewspace-1988896/