转自:http://blog.csdn.net/wuweilong/article/details/41627189
说明:
这篇文章主要是记录下单实例环境下Oracle 11.2.0.1升级到11.2.0.3的过程,当然RAC的升级是会有所不同。但是他们每个版本之间升级步骤都是差不多的,先升级Database Software,再升级Oracle Instance。
Oracle 11.2.0.4的Patchset No:19852360下载需要有Oracle Support才可以。
Patchset包含有7个文件,关于这七个文件的作用,详见如下链接:
我们升级Database,只需要其中的第一个和第二文件即可。将2个文件解压缩后就可以执行升级操作了。
升级前准备:
1、查看数据库和操作系统相关信息:
- [root@db01 ~]# uname -a
- Linux db01 2.6.18-308.el5 #1SMP Fri Jan 27 17:17:51 EST 2012 x86_64 x86_64 x86_64 GNU/Linux
- [root@db01 ~]# lsb_release-a
- LSB Version: :core-4.0-amd64:core-4.0-ia32:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-ia32:printing-4.0-noarch
- Distributor ID:RedHatEnterpriseServer
- Description: Red Hat Enterprise Linux Server release 5.8(Tikanga)
- Release: 5.8
- Codename: Tikanga
- [root@db01 ~]# su - oracle-c "sqlplus / as sysdba";
- SQL*Plus: Release 11.2.0.3.0Production on Fri Oct 3 21:32:02 2014
- Copyright (c) 1982, 2011,Oracle. All rights reserved.
- Connected to:
- Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, OLAP,Data Mining and Real Application Testing options
- SQL> select name fromv$database;
- NAME
- ---------
- WOO
- SQL>
2、备份数据库
- [oracle@db01 ~]$ rman target/
- Recovery Manager: Release11.2.0.3.0 - Production on Fri Oct 3 21:43:04 2014
- Copyright (c) 1982, 2011,Oracle and/or its affiliates. All rightsreserved.
- connected to targetdatabase: WOO (DBID=4199461782)
- RMAN> backup databaseplus archivelog delete input format '/DBBackup/Phycal/full_%U.bak';
- Starting backup at 03-OCT-14
- current log archived
- using target databasecontrol file instead of recovery catalog
- allocated channel:ORA_DISK_1
- channel ORA_DISK_1: SID=149device type=DISK
- channel ORA_DISK_1: startingarchived log backup set
- channel ORA_DISK_1:specifying archived log(s) in backup set
- input archived log thread=1sequence=15 RECID=1 STAMP=860017183
- input archived log thread=1sequence=16 RECID=2 STAMP=860017184
- input archived log thread=1sequence=17 RECID=3 STAMP=860017186
- input archived log thread=1sequence=18 RECID=4 STAMP=860017186
- input archived log thread=1sequence=19 RECID=5 STAMP=860017188
- input archived log thread=1sequence=20 RECID=6 STAMP=860017387
- channel ORA_DISK_1: startingpiece 1 at 03-OCT-14
- channel ORA_DISK_1: finishedpiece 1 at 03-OCT-14
- piecehandle=/DBBackup/Phycal/full_01pk5knb_1_1.bak tag=TAG20141003T214307comment=NONE
- channel ORA_DISK_1: backupset complete, elapsed time: 00:00:02
- channel ORA_DISK_1: deletingarchived log(s)
- archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_15_b2x9rz0z_.arcRECID=1 STAMP=860017183
- archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_16_b2x9s05l_.arcRECID=2 STAMP=860017184
- archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_17_b2x9s2nx_.arcRECID=3 STAMP=860017186
- archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_18_b2x9s2od_.arcRECID=4 STAMP=860017186
- archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_19_b2x9s4dr_.arcRECID=5 STAMP=860017188
- archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_20_b2x9zbz2_.arcRECID=6 STAMP=860017387
- Finished backup at 03-OCT-14
- Starting backup at 03-OCT-14
- using channel ORA_DISK_1
- channel ORA_DISK_1: startingfull datafile backup set
- channel ORA_DISK_1:specifying datafile(s) in backup set
- input datafile filenumber=00001 name=/DBData/woo/system01.dbf
- input datafile filenumber=00002 name=/DBData/woo/sysaux01.dbf
- input datafile filenumber=00005 name=/DBData/woo/example01.dbf
- input datafile filenumber=00003 name=/DBData/woo/undotbs01.dbf
- input datafile filenumber=00004 name=/DBData/woo/users01.dbf
- channel ORA_DISK_1: startingpiece 1 at 03-OCT-14
- channel ORA_DISK_1: finishedpiece 1 at 03-OCT-14
- piecehandle=/DBSoft/fast_recovery_area/WOO/backupset/2014_10_03/o1_mf_nnndf_TAG20141003T214309_b2x9zfm5_.bkptag=TAG20141003T214309 comment=NONE
- channel ORA_DISK_1: backupset complete, elapsed time: 00:01:45
- channel ORA_DISK_1: startingfull datafile backup set
- channel ORA_DISK_1:specifying datafile(s) in backup set
- including current controlfile in backup set
- including current SPFILE inbackup set
- channel ORA_DISK_1: startingpiece 1 at 03-OCT-14
- channel ORA_DISK_1: finishedpiece 1 at 03-OCT-14
- piecehandle=/DBSoft/fast_recovery_area/WOO/backupset/2014_10_03/o1_mf_ncsnf_TAG20141003T214309_b2xb2qlm_.bkptag=TAG20141003T214309 comment=NONE
- channel ORA_DISK_1: backupset complete, elapsed time: 00:00:01
- Finished backup at 03-OCT-14
- Starting backup at 03-OCT-14
- current log archived
- using channel ORA_DISK_1
- channel ORA_DISK_1: startingarchived log backup set
- channel ORA_DISK_1:specifying archived log(s) in backup set
- input archived log thread=1sequence=21 RECID=7 STAMP=860017496
- channel ORA_DISK_1: startingpiece 1 at 03-OCT-14
- channel ORA_DISK_1: finishedpiece 1 at 03-OCT-14
- piecehandle=/DBBackup/Phycal/full_04pk5kqo_1_1.bak tag=TAG20141003T214456comment=NONE
- channel ORA_DISK_1: backupset complete, elapsed time: 00:00:01
- channel ORA_DISK_1: deletingarchived log(s)
- archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog/2014_10_03/o1_mf_1_21_b2xb2rsf_.arcRECID=7 STAMP=860017496
- Finished backup at 03-OCT-14
- RMAN>
3、停止数据库
- ###停止数据库
- SQL> shutdown immediate
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- ###停止监听
- SQL> host lsnrctl stop
- LSNRCTL for Linux: Version11.2.0.3.0 - Production on 04-OCT-2014 01:39:26
- Copyright (c) 1991, 2011,Oracle. All rights reserved.
- Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
- The command completedsuccessfully
- ###停止EM
- SQL> host emctl stopdbconsole
- Oracle Enterprise Manager11g Database Control Release 11.2.0.3.0
- Copyright (c) 1996, 2011Oracle Corporation. All rights reserved.
- <a target=_blank href="https://db01:1158/em/console/aboutApplication">https://db01:1158/em/console/aboutApplication</a>
- Stopping Oracle EnterpriseManager 11g Database Control ...
- ###查看oracle进程,检查是否已经停止完毕
- [root@db01 ~]# ps -ef|grepora
- root 4971 4944 0 01:46 pts/0 00:00:00 grep ora
4、备份老的ORACLE_HOME和oraInventory
- [root@db01 ~]#tar –cvfproduct.zip /DBSoft/product/
- [root@db01 ~]#tar –cvforaInventory.zip /DBSoft/product/oraInventory/
5、上传并解压缩Oracle Database 11.2.0.4安装介质
- [oracle@db01 ~]$ ll
- total 2489644
- drwxr-xr-x 2 oracle oinstall 4096 Oct 3 04:06 Desktop
- -rw-r--r-- 1 oracle oinstall1395582860 Oct 3 06:55p13390677_112040_Linux-x86-64_1of7.zip
- -rw-r--r-- 1 oracle oinstall1151304589 Oct 3 06:54p13390677_112040_Linux-x86-64_2of7.zip
- [oracle@db01 ~]$ unzipp13390677_112040_Linux-x86-64_1of7.zip
- [oracle@db01 ~]$ unzipp13390677_112040_Linux-x86-64_2of7.zip
6、 开始安装
6.1 将文件解压缩后进入安装目录执行./runInstall
6.2 取消Oracle支持选项,点击Next
6.3 选择最后一个选项"skip software update" 点击Next
6.4 选择最后一个选项"Upgrade an existing database" 后执行Next
6.5 选择所有语言,后点击Next
6.6 选择要升级的数据库版本,后点击Next
6.7 选择新版本的数据库软件安装目录,后点击Next
6.8 选择数据库所属用户组,后点击Next
6.9 察看数据库配置信息后,点击Install开始进行新版本软件安装
6.10 安装进度,这个过程将会持续15分钟左右
6.11 弹出对话框要求执行/DBSoft/Product/11.2.4/db_1/root.sh 脚本
7、 执行root.sh脚本
- [root@db01 ~]#/DBSoft/Product/11.2.4/db_1/root.sh
- Performing root useroperation for Oracle 11g
- The following environmentvariables are set as:
- ORACLE_OWNER= oracle
- ORACLE_HOME= /DBSoft/Product/11.2.4/db_1
- Enter the full pathname ofthe local bin directory: [/usr/local/bin]:
- The contents of"dbhome" have not changed. No need to overwrite.
- 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 ConfigurationAssistant when a database is created
- Finished running genericpart of root script.
- Now product-specific rootactions will be performed.
- Finished product-specificroot actions.
- [root@db01 ~]#
6.12 执行完脚本之后继续运行,提示配置监听,选择Cancel,稍后复制即可
6.13 提示监听配置失败,点击ok即可
6.14 有报错不用管,我们直接Next即可
6.15 至此软件安装完成,点击Close关闭安装界面
至此软件安装完成,但是并不代表数据库就已经升级完成了。
8、至此11.2.0.4的软件就已经装完了,修改Oracle环境变量
- [root@db01 ~]# su - oracle
- [oracle@db01 ~]$ vi.bash_profile -------修改如下行,将11.2.3改成11.2.4即可
- export ORACLE_HOME=$ORACLE_BASE/Product/11.2.4/db_1
- [oracle@db01 ~]$ vi /etc/oratab -------修改如下行11.2.3为11.2.4
- woo:/DBSoft/Product/11.2.4/db_1:N
- [oracle@db01 admin]$ cp/DBSoft/Product/11.2.3/db_1/dbs/* /DBSoft/Product/11.2.4/db_1/dbs/
9、 拷贝监听配置文件
- [oracle@db01 admin]$ source~/.bash_profile
- [oracle@db01 admin]$ pwd
- /DBSoft/Product/11.2.3/db_1/network/admin
- [oracle@db01 admin]$ cd$ORACLE_HOME/network/admin
- [oracle@db01 admin]$ ls
- samples shrept.lst
- [oracle@db01 admin]$ cp -r/DBSoft/Product/11.2.3/db_1/network/admin/* $ORACLE_HOME/network/admin
- [oracle@db01 admin]$ ls
- listener.ora samples shrept.lst sqlnet.ora tnsnames.ora
10、执行预升级脚本检查
- [oracle@db01 dbs]$ sqlplus /as sysdba
- SQL*Plus: Release 11.2.0.4.0Production on Sat Oct 4 02:26:01 2014
- Copyright (c) 1982, 2013,Oracle. All rights reserved.
- Connected to an idleinstance.
- SQL> startup upgrade;
- ORACLE instance started.
- Total System Global Area2037673984 bytes
- Fixed Size 2254704 bytes
- Variable Size 1811941520 bytes
- Database Buffers 218103808 bytes
- Redo Buffers 5373952 bytes
- Database mounted.
- Database opened.
- SQL>@?/rdbms/admin/utlu112i.sql ---执行升级前检查
- Oracle Database 11.2Pre-Upgrade Information Tool 10-04-2014 02:27:38
- Script Version: 11.2.0.4.0Build: 001
- .
- **********************************************************************
- Database:
- **********************************************************************
- --> name: WOO
- --> version: 11.2.0.3.0
- --> compatible: 11.2.0.0.0
- --> blocksize: 8192
- --> platform: Linux x86 64-bit
- --> timezone file: V14
- .
- **********************************************************************
- Tablespaces: [makeadjustments in the current environment]
- **********************************************************************
- --> SYSTEM tablespace isadequate for the upgrade.
- .... minimum required size:917 MB
- --> SYSAUX tablespace isadequate for the upgrade.
- .... minimum required size:646 MB
- --> UNDOTBS1 tablespaceis adequate for the upgrade.
- .... minimum required size:400 MB
- --> TEMP tablespace isadequate for the upgrade.
- .... minimum required size:60 MB
- .
- **********************************************************************
- Flashback: OFF
- **********************************************************************
- **********************************************************************
- Update Parameters: [UpdateOracle Database 11.2 init.ora or spfile]
- Note: Pre-upgrade tool wasrun on a lower version 64-bit database.
- **********************************************************************
- --> If Target Oracle is32-Bit, refer here for Update Parameters:
- WARNING: -->"shared_pool_size" needs to be increased to at least 236 MB
- .
- --> If Target Oracle is64-Bit, refer here for Update Parameters:
- WARNING: -->"shared_pool_size" needs to be increased to at least 472 MB
- .
- **********************************************************************
- Renamed Parameters: [UpdateOracle Database 11.2 init.ora or spfile]
- **********************************************************************
- -- No renamed parametersfound. No changes are required.
- .
- **********************************************************************
- Obsolete/DeprecatedParameters: [Update Oracle Database 11.2 init.ora or spfile]
- **********************************************************************
- -- No obsolete parametersfound. No changes are required
- .
- **********************************************************************
- Components: [The followingdatabase components will be upgraded or installed]
- **********************************************************************
- --> Oracle CatalogViews [upgrade] VALID
- --> Oracle Packages andTypes [upgrade] VALID
- --> JServer JAVA VirtualMachine [upgrade] VALID
- --> Oracle XDK forJava [upgrade] VALID
- --> Oracle WorkspaceManager [upgrade] VALID
- --> OLAP Analytic Workspace [upgrade] VALID
- --> OLAP Catalog [upgrade] VALID
- --> EM Repository [upgrade] VALID
- --> Oracle Text [upgrade] VALID
- --> Oracle XMLDatabase [upgrade] VALID
- --> Oracle Java Packages [upgrade] VALID
- --> OracleinterMedia [upgrade] VALID
- --> Spatial [upgrade] VALID
- --> ExpressionFilter [upgrade] VALID
- --> Rule Manager [upgrade] VALID
- --> Oracle ApplicationExpress [upgrade] VALID
- ... APEX will only beupgraded if the version of APEX in
- ... the target Oracle homeis higher than the current one.
- --> Oracle OLAP API [upgrade] VALID
- .
- **********************************************************************
- Miscellaneous Warnings
- **********************************************************************
- WARNING: --> Your recyclebin is turned on and currently contains no objects.
- .... Because it is REQUIREDthat the recycle bin be empty prior to upgrading
- .... and your recycle bin isturned on, you may need to execute the command:
- PURGE DBA_RECYCLEBIN
- .... prior to executing yourupgrade to confirm the recycle bin is empty.
- WARNING: --> Databasecontains schemas with objects dependent on DBMS_LDAP package.
- .... Refer to the 11gUpgrade Guide for instructions to configure Network ACLs.
- .... USER APEX_030200 hasdependent objects.
- .
- **********************************************************************
- Recommendations
- **********************************************************************
- Oracle recommends gatheringdictionary statistics prior to
- upgrading the database.
- To gather dictionarystatistics execute the following command
- while connected as SYSDBA:
- EXECUTE dbms_stats.gather_dictionary_stats;
- **********************************************************************
- Oracle recommends removingall hidden parameters prior to upgrading.
- To view existing hiddenparameters execute the following command
- while connected AS SYSDBA:
- SELECT name,description fromSYS.V$PARAMETER WHERE name
- LIKE '\_%' ESCAPE '\'
- Changes will need to be madein the init.ora or spfile.
- **********************************************************************
- Oracle recommends reviewingany defined events prior to upgrading.
- To view existing non-defaultevents 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' ANDisdefault='FALSE'
- Changes will need to be madein the init.ora or spfile.
11、 修改不满足项
- SQL> show parametershared
- NAME TYPE VALUE
- ----------------------------------------------- ------------------------------
- hi_shared_memory_address integer 0
- max_shared_servers integer
- shared_memory_address integer 0
- shared_pool_reserved_size big integer 8M
- shared_pool_size big integer 160M
- shared_server_sessions integer
- shared_servers integer 1
- SQL> alter system setshared_pool_size=800m scope=spfile;
- System altered.
- SQL> shutdown immediate
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SQL> startup mountl
- SP2-0714: invalidcombination of STARTUP options
- SQL> startup mount;
- ORACLE instance started.
- Total System Global Area2872786944 bytes
- Fixed Size 2256712 bytes
- Variable Size 2634023096 bytes
- Database Buffers 218103808 bytes
- Redo Buffers 18403328 bytes
- Database mounted.
- SQL> alter databaseflashback on;
- ###创建一个回滚点
- SQL> create restore pointmaclean_rollback guarantee flashback database;
- Restore point created.
- SQL> select * fromv$restore_point;
- SCN DATABASE_INCARNATION# GUASTORAGE_SIZE
- ------------------------------- --- ------------
- TIME
- ---------------------------------------------------------------------------
- RESTORE_POINT_TIME PRE
- ------------------------------------------------------------------------------
- NAME
- --------------------------------------------------------------------------------
- 1187867 2 YES 52428800
- 04-OCT-14 02.41.43.000000000AM
- YES
- MACLEAN_ROLLBACK
- 再次运行检查:
- SQL>@?/rdbms/admin/utlu112i.sql
- Oracle Database 11.2Pre-Upgrade Information Tool 10-04-2014 03:26:20
- Script Version: 11.2.0.4.0Build: 001
- .
- **********************************************************************
- Database:
- **********************************************************************
- --> name: WOO
- --> version: 11.2.0.3.0
- --> compatible: 11.2.0.0.0
- --> blocksize: 8192
- --> platform: Linux x86 64-bit
- --> timezone file: V14
- .
- **********************************************************************
- Tablespaces: [makeadjustments in the current environment]
- **********************************************************************
- --> SYSTEM tablespace isadequate for the upgrade.
- .... minimum required size:917 MB
- --> SYSAUX tablespace isadequate for the upgrade.
- .... minimum required size:646 MB
- --> UNDOTBS1 tablespaceis adequate for the upgrade.
- .... minimum required size:400 MB
- --> TEMP tablespace isadequate for the upgrade.
- .... minimum required size:60 MB
- .
- **********************************************************************
- Flashback: ON
- **********************************************************************
- FlashbackInfo:
- --> name: /DBSoft/fast_recovery_area
- --> limit: 4122 MB
- --> used: 1490 MB
- --> size: 4122 MB
- --> reclaim: 1097.734375 MB
- --> files: 11
- WARNING: --> FlashbackRecovery Area Set. Please ensureadequate disk space inrecover
- y areas before performing anupgrade.
- .
- **********************************************************************
- Update Parameters: [UpdateOracle Database 11.2 init.ora or spfile]
- Note: Pre-upgrade tool wasrun on a lower version 64-bit database.
- **********************************************************************
- --> If Target Oracle is32-Bit, refer here for Update Parameters:
- -- No update parameterchanges are required.
- .
- --> If Target Oracle is64-Bit, refer here for Update Parameters:
- -- No update parameterchanges are required.
- .
- **********************************************************************
- Renamed Parameters: [UpdateOracle Database 11.2 init.ora or spfile]
- **********************************************************************
- -- No renamed parametersfound. No changes are required.
- .
- **********************************************************************
- Obsolete/DeprecatedParameters: [Update Oracle Database 11.2 init.ora or spfile]
- **********************************************************************
- -- No obsolete parametersfound. No changes are required
- .
- **********************************************************************
- Components: [The followingdatabase components will be upgraded or installed]
- **********************************************************************
- --> Oracle CatalogViews [upgrade] VALID
- --> Oracle Packages andTypes [upgrade] VALID
- --> JServer JAVA VirtualMachine [upgrade] VALID
- --> Oracle XDK forJava [upgrade] VALID
- --> Oracle WorkspaceManager [upgrade] VALID
- --> OLAP AnalyticWorkspace [upgrade] VALID
- --> OLAP Catalog [upgrade] VALID
- --> EM Repository [upgrade] VALID
- --> Oracle Text [upgrade] VALID
- --> Oracle XMLDatabase [upgrade] VALID
- --> Oracle JavaPackages [upgrade] VALID
- --> OracleinterMedia [upgrade] VALID
- --> Spatial [upgrade] VALID
- --> ExpressionFilter [upgrade] VALID
- --> Rule Manager [upgrade] VALID
- --> Oracle ApplicationExpress [upgrade] VALID
- ... APEX will only beupgraded if the version of APEX in
- ... the target Oracle homeis higher than the current one.
- --> Oracle OLAP API [upgrade] VALID
- .
- **********************************************************************
- Miscellaneous Warnings
- **********************************************************************
- WARNING: --> Your recyclebin is turned on and currently contains no objects.
- .... Because it is REQUIREDthat the recycle bin be empty prior to upgrading
- .... and your recycle bin isturned on, you may need to execute the command:
- PURGE DBA_RECYCLEBIN
- .... prior to executing yourupgrade to confirm the recycle bin is empty.
- WARNING: --> Databasecontains schemas with objects dependent on DBMS_LDAP package.
- .... Refer to the 11gUpgrade Guide for instructions to configure Network ACLs.
- .... USER APEX_030200 hasdependent objects.
- .
- **********************************************************************
- Recommendations
- **********************************************************************
- Oracle recommends gatheringdictionary statistics prior to
- upgrading the database.
- To gather dictionarystatistics execute the following command
- while connected as SYSDBA:
- EXECUTE dbms_stats.gather_dictionary_stats;
- **********************************************************************
- Oracle recommends removingall hidden parameters prior to upgrading.
- To view existing hiddenparameters execute the following command
- while connected AS SYSDBA:
- SELECT name,description fromSYS.V$PARAMETER WHERE name
- LIKE '\_%' ESCAPE '\'
- Changes will need to be madein the init.ora or spfile.
- **********************************************************************
- Oracle recommends reviewingany defined events prior to upgrading.
- To view existing non-defaultevents 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' ANDisdefault='FALSE'
- Changes will need to be madein the init.ora or spfile.
12、执行升级操作
- SQL> shutdown immediate
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SQL> startup upgrade;
- ORACLE instance started.
- Total System Global Area2872786944 bytes
- Fixed Size 2256712 bytes
- Variable Size 2634023096 bytes
- Database Buffers 218103808 bytes
- Redo Buffers 18403328 bytes
- Database mounted.
- Database opened.
- SQL> set echo on
- SQL> spool/home/oracle/upgrade.log
- SQL> set time on;
- 03:28:37 SQL>@?/rdbms/admin/catupgrd.sql ---该脚本会运行十分钟左右
- ……..
- Oracle Database 11.2Post-Upgrade Status Tool 10-04-2014 04:01:36
- .
- Component Current Version Elapsed Time
- Name Status Number HH:MM:SS
- .
- Oracle Server
- . VALID 11.2.0.4.0 00:08:38
- JServer JAVA Virtual Machine
- . VALID 11.2.0.4.0 00:04:26
- Oracle Workspace Manager
- . VALID 11.2.0.4.0 00:00:25
- OLAP Analytic Workspace
- . VALID 11.2.0.4.0 00:01:27
- OLAP Catalog
- . VALID 11.2.0.4.0 00:00:32
- Oracle OLAP API
- . VALID 11.2.0.4.0 00:00:19
- Oracle Enterprise Manager
- . VALID 11.2.0.4.0 00:06:25
- 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:46
- Oracle Database JavaPackages
- . VALID 11.2.0.4.0 00:00:08
- Oracle Multimedia
- . VALID 11.2.0.4.0 00:01:43
- Spatial
- . VALID 11.2.0.4.0 00:05:34
- Oracle Expression Filter
- . VALID 11.2.0.4.0 00:00:06
- Oracle Rules Manager
- . VALID 11.2.0.4.0 00:00:07
- Oracle Application Express
- . VALID 3.2.1.00.12
- Final Actions
- . 00:00:00
- Total Upgrade Time: 00:32:35
- PL/SQL proceduresuccessfully completed.
- 04:01:36 SQL>
- 04:01:36 SQL> SETSERVEROUTPUT OFF
- 04:01:36 SQL> SET VERIFYON
- 04:01:36 SQL> commit;
- Commit complete.
- 04:01:36 SQL>
- 04:01:36 SQL> shutdownimmediate;
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- 04:01:54 SQL>
- 04:01:54 SQL>
- 04:01:54 SQL> DOC
- 04:01:54 DOC>#######################################################################
- 04:01:54DOC>#######################################################################
- 04:01:54 DOC>
- 04:01:54 DOC> The above sql script is the final step ofthe upgrade. Please
- 04:01:54 DOC> review any errors in the spool log file. Ifthere are any errors in
- 04:01:54 DOC> the spool file, consult the Oracle DatabaseUpgrade Guide for
- 04:01:54 DOC> troubleshooting recommendations.
- 04:01:54 DOC>
- 04:01:54 DOC> Next restart for normal operation, and thenrun utlrp.sql to
- 04:01:54 DOC> recompile any invalid application objects.
- 04:01:54 DOC>
- 04:01:54 DOC> If the source database had an older timezone version prior to
- 04:01:54 DOC> upgrade, then please run the DBMS_DSTpackage. DBMS_DST will upgrade
- 04:01:54 DOC> TIMESTAMP WITH TIME ZONE data to use thelatest time zone file shipped
- 04:01:54 DOC> with Oracle.
- 04:01:54 DOC>
- 04:01:54DOC>#######################################################################
- 04:01:54 DOC>#######################################################################
- 04:01:54 DOC>#
- 04:01:54 SQL>
- 04:01:54 SQL> Rem Seterrorlogging off
- 04:01:54 SQL> SETERRORLOGGING OFF;
- 04:01:54 SQL>
- 04:01:54 SQL> REM END OFCATUPGRD.SQL
- 04:01:54 SQL>
- 04:01:54 SQL> REM bug12337546 - Exit current sqlplus session at end of catupgrd.sql.
- 04:01:54 SQL> REM This forces user to start a newsqlplus session in order
- 04:01:54 SQL> REM to connect to the upgraded db.
- 04:01:54 SQL> exit
- Disconnected from OracleDatabase 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
- With the Partitioning, OLAP,Data Mining and Real Application Testing options
以上catupgrd.sql脚本运行了50分钟左右,执行完之后会shutdown immediate数据库。这个时候我们将要重启数据库运行utlrp.sql脚本编译失效对象:
13、运行utlrp.sql编译失效对象
- [oracle@db01 dbs]$ sqlplus /as sysdba
- SQL*Plus: Release 11.2.0.4.0Production on Sat Oct 4 04:11:22 2014
- Copyright (c) 1982, 2013,Oracle. All rights reserved.
- Connected to an idleinstance.
- SQL> startup
- ORACLE instance started.
- Total System Global Area 2872786944bytes
- Fixed Size 2256712 bytes
- Variable Size 2634023096 bytes
- Database Buffers 218103808 bytes
- Redo Buffers 18403328 bytes
- Database mounted.
- Database opened.
- SQL> @?/rdbms/admin/utlrp
- TIMESTAMP
- --------------------------------------------------------------------------------
- COMP_TIMESTAMPUTLRP_BGN 2014-10-04 04:14:57
- DOC> The following PL/SQL block invokesUTL_RECOMP to recompile invalid
- DOC> objects in the database. Recompilation timeis 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 alarge number of invalid
- DOC> objects.
- DOC>
- DOC> Use the following queries to trackrecompilation progress:
- DOC>
- DOC> 1. Query returning the number of invalidobjects remaining. This
- DOC> number should decrease with time.
- DOC> SELECT COUNT(*) FROM obj$ WHERE statusIN (4, 5, 6);
- DOC>
- DOC> 2. Query returning the number of objectscompiled so far. This number
- DOC> should increase with time.
- DOC> SELECT COUNT(*) FROMUTL_RECOMP_COMPILED;
- DOC>
- DOC> This script automatically chooses serial orparallel recompilation
- DOC> based on the number of CPUs available(parameter cpu_count) multiplied
- DOC> by the number of threads per CPU (parameterparallel_threads_per_cpu).
- DOC> On RAC, this number is added across all RACnodes.
- DOC>
- DOC> UTL_RECOMP uses DBMS_SCHEDULER to createjobs for parallel
- DOC> recompilation. Jobs are created withoutinstance affinity so that they
- DOC> can migrate across RAC nodes. Use thefollowing queries to verify
- DOC> whether UTL_RECOMP jobs are being createdand 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 arerunning
- DOC> SELECT job_name FROMdba_scheduler_running_jobs
- DOC> WHERE job_name like'UTL_RECOMP_SLAVE_%';
- DOC>#
- PL/SQL proceduresuccessfully completed.
- TIMESTAMP
- --------------------------------------------------------------------------------
- COMP_TIMESTAMPUTLRP_END 2014-10-04 04:15:34
- DOC> The following queryreports the number of objects that have compiled
- DOC> with errors.
- DOC>
- DOC> If the number ishigher than expected, please examine the error
- DOC> messages reportedwith each object (using SHOW ERRORS) to see if they
- DOC> point to systemmisconfiguration or resource constraints that must be
- DOC> fixed beforeattempting to recompile these objects.
- DOC>#
- OBJECTS WITH ERRORS
- -------------------
- 0
- DOC> The following queryreports the number of errors caught during
- DOC> recompilation. Ifthis number is non-zero, please query the error
- DOC> messages in thetable UTL_RECOMP_ERRORS to see if any of these errors
- DOC> are due tomisconfiguration or resource constraints that must be
- DOC> fixed before objectscan compile successfully.
- DOC>#
- ERRORS DURING RECOMPILATION
- ---------------------------
- 0
- Function created.
- PL/SQL proceduresuccessfully completed.
- Function dropped.
- PL/SQL proceduresuccessfully completed.
- SQL>
该脚本耗时约为3分钟左右。
14、至此数据库已经升级完成,查看各组件版本号:
- SQL> select comp_name,status,version fromdba_server_registry
- COMP_NAME STATUS VERSION
- ------------------------------------------------------ ------------------------------
- OWB VALID 11.2.0.3.0
- Oracle ApplicationExpress VALID 3.2.1.00.12
- Oracle EnterpriseManager 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 ExpressionFilter VALID 11.2.0.4.0
- Oracle Rules Manager VALID 11.2.0.4.0
- Oracle WorkspaceManager VALID 11.2.0.4.0
- Oracle Database CatalogViews VALID 11.2.0.4.0
- Oracle Database Packages andTypes VALID 11.2.0.4.0
- JServer JAVA VirtualMachine VALID 11.2.0.4.0
- Oracle XDK VALID 11.2.0.4.0
- Oracle Database JavaPackages 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.
15、检查无效对象:
- SQL> select * fromdba_objects where status !='VALID';
- no rows selected
16、升级成功后删除原来的目录,通过EMCA重建EM
- [oracle@db01 /]$ rm -rf/DBSoft/Product/11.2.3/
- 手工创建EM资料库:
- ####emca -repos drop
- [oracle@db01 /]$ emca -reposdrop
- STARTED EMCA at Oct 4, 20146:11:41 AM
- EM Configuration Assistant,Version 11.2.0.3.0 Production
- Copyright (c) 2003, 2011,Oracle. All rights reserved.
- Enter the followinginformation:
- Database SID: woo
- Listener port number: 1521
- Password for SYS user:
- Password for SYSMANuser:
- ----------------------------------------------------------------------
- WARNING : While repositoryis dropped the database will be put in quiesce mode.
- ----------------------------------------------------------------------
- Do you wish to continue?[yes(Y)/no(N)]: y
- Oct 4, 2014 6:11:53 AMoracle.sysman.emcp.EMConfig perform
- INFO: This operation isbeing logged at /DBSoft/cfgtoollogs/emca/woo/emca_2014_10_04_06_11_41.log.
- Oct 4, 2014 6:11:53 AMoracle.sysman.emcp.EMReposConfig invoke
- INFO: Dropping the EMrepository (this may take a while) ...
- Oct 4, 2014 6:13:37 AMoracle.sysman.emcp.EMReposConfig invoke
- INFO: Repositorysuccessfully dropped
- Enterprise Managerconfiguration completed successfully
- FINISHED EMCA at Oct 4, 20146:13:37 AM
- ####emca -repos create
- [oracle@db01 /]$ emca -reposcreate
- STARTED EMCA at Oct 4, 20146:14:07 AM
- EM Configuration Assistant,Version 11.2.0.3.0 Production
- Copyright (c) 2003, 2011,Oracle. All rights reserved.
- Enter the followinginformation:
- Database SID: woo
- Listener port number: 1521
- Password for SYS user:
- Password for SYSMANuser:
- Do you wish to continue?[yes(Y)/no(N)]: y
- Oct 4, 2014 6:14:20 AM oracle.sysman.emcp.EMConfigperform
- INFO: This operation isbeing logged at /DBSoft/cfgtoollogs/emca/woo/emca_2014_10_04_06_14_07.log.
- Oct 4, 2014 6:14:21 AMoracle.sysman.emcp.EMReposConfig createRepository
- INFO: Creating the EMrepository (this may take a while) ...
- Oct 4, 2014 6:17:57 AMoracle.sysman.emcp.EMReposConfig invoke
- INFO: Repositorysuccessfully created
- Enterprise Managerconfiguration completed successfully
- FINISHED EMCA at Oct 4, 20146:17:57 AM
- ###emca -config dbcontrol db
- [oracle@db01 /]$ emca-config dbcontrol db
- STARTED EMCA at Oct 4, 20146:24:04 AM
- EM Configuration Assistant,Version 11.2.0.3.0 Production
- Copyright (c) 2003, 2011,Oracle. All rights reserved.
- Enter the followinginformation:
- Database SID: woo
- Database Control is alreadyconfigured for the database woo
- You have chosen to configureDatabase Control for managing the database woo
- This will remove theexisting configuration and the default settings and perform a freshconfiguration
- Do you wish to continue?[yes(Y)/no(N)]: y
- Listener ORACLE_HOME [/DBSoft/Product/11.2.4/db_1 ]:
- Password for SYS user:
- Password for DBSNMPuser:
- Password for SYSMANuser:
- Email address fornotifications (optional):
- Outgoing Mail (SMTP) serverfor notifications (optional):
- -----------------------------------------------------------------
- You have specified thefollowing settings
- Database ORACLE_HOME................ /DBSoft/Product/11.2.4/db_1
- Local hostname................ db01
- Listener ORACLE_HOME................ /DBSoft/Product/11.2.4/db_1
- Listener port number................ 1521
- Database SID................ woo
- Email address fornotifications ...............
- Outgoing Mail (SMTP) serverfor notifications ...............
- -----------------------------------------------------------------
- Do you wish to continue?[yes(Y)/no(N)]: y
- Oct 4, 2014 6:24:49 AMoracle.sysman.emcp.EMConfig perform
- INFO: This operation isbeing logged at /DBSoft/cfgtoollogs/emca/woo/emca_2014_10_04_06_24_04.log.
- Oct 4, 2014 6:24:50 AMoracle.sysman.emcp.util.DBControlUtil stopOMS
- INFO: Stopping DatabaseControl (this may take a while) ...
- Oct 4, 2014 6:24:54 AMoracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
- INFO: Uploadingconfiguration data to EM repository (this may take a while) ...
- Oct 4, 2014 6:25:53 AMoracle.sysman.emcp.EMReposConfig invoke
- INFO: Uploaded configurationdata successfully
- Oct 4, 2014 6:25:57 AMoracle.sysman.emcp.util.DBControlUtil secureDBConsole
- INFO: Securing DatabaseControl (this may take a while) ...
- Oct 4, 2014 6:26:03 AMoracle.sysman.emcp.util.DBControlUtil secureDBConsole
- INFO: Database Controlsecured successfully.
- Oct 4, 2014 6:26:03 AMoracle.sysman.emcp.util.DBControlUtil startOMS
- INFO: Starting DatabaseControl (this may take a while) ...
- Oct 4, 2014 6:26:23 AMoracle.sysman.emcp.EMDBPostConfig performConfiguration
- INFO: Database Controlstarted successfully
- Oct 4, 2014 6:26:23 AMoracle.sysman.emcp.EMDBPostConfig performConfiguration
- INFO:>>>>>>>>>>> The Database Control URL ishttps://db01:5500/em <<<<<<<<<<<
- Oct 4, 2014 6:26:25 AMoracle.sysman.emcp.EMDBPostConfig invoke
- WARNING:
- ************************ WARNING ************************
- Management Repository hasbeen placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in thefile: /DBSoft/Product/11.2.4/db_1/db01_woo/sysman/config/emkey.ora. Ensure thisfile is backed up as the encrypted data will become unusable if this file islost.
- ***********************************************************
- Enterprise Managerconfiguration completed successfully
- FINISHED EMCA at Oct 4, 20146:26:25 AM
- [oracle@db01 /]$