探索Oracle之数据库升级二11.2.0.3升级到11.2.0.4完整步骤
探索之升级二
11.2.0.3升级到11.2.0.4完整步骤
这篇文章主要是记录下单实例环境下Oracle 11.2.0.1升级到11.2.0.3的过程，当然RAC的升级是会有所不同。但是他们每个版本之间升级步骤都是差不多的，先升级Database
Software，再升级Oracle Instance。
Oracle 11.2.0.4的Patchset No:下载需要有Oracle
Support才可以。
Patchset包含有7个文件，关于这七个文件的作用，详见如下链接：
我们升级Database，只需要其中的第一个和第二文件即可。将2个文件解压缩后就可以执行升级操作了。
升级前准备：
1、查看数据库和操作相关信息:
[root@db01 ~]# uname -a
db01 2.6.18-308.el5 #1SMP Fri Jan 27 17:17:51 EST
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)
[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) ,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$
2、备份数据库
[oracle@db01 ~]$ rman target/
Recovery Manager: Release11.2.0.3.0 - Production on Fri Oct 3 21:43:04 2014
Copyright (c) ,Oracle and/or its affiliates.
All rightsreserved.
connected to targetdatabase: WOO (DBID=)
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=
input archived log thread=1sequence=16 RECID=2 STAMP=
input archived log thread=1sequence=17 RECID=3 STAMP=
input archived log thread=1sequence=18 RECID=4 STAMP=
input archived log thread=1sequence=19 RECID=5 STAMP=
input archived log thread=1sequence=20 RECID=6 STAMP=
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=TAG307comment=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//o1_mf_1_15_b2x9rz0z_.arcRECID=1 STAMP=
archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog//o1_mf_1_16_b2x9s05l_.arcRECID=2 STAMP=
archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog//o1_mf_1_17_b2x9s2nx_.arcRECID=3 STAMP=
archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog//o1_mf_1_18_b2x9s2od_.arcRECID=4 STAMP=
archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog//o1_mf_1_19_b2x9s4dr_.arcRECID=5 STAMP=
archived log filename=/DBSoft/fast_recovery_area/WOO/archivelog//o1_mf_1_20_b2x9zbz2_.arcRECID=6 STAMP=
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//o1_mf_nnndf_TAG309_b2x9zfm5_.bkptag=TAG309 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//o1_mf_ncsnf_TAG309_b2xb2qlm_.bkptag=TAG309 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=
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=TAG456comment=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//o1_mf_1_21_b2xb2rsf_.arcRECID=7 STAMP=
Finished backup at 03-OCT-14
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-:26
Copyright (c) ,Oracle.
All rights reserved.
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completedsuccessfully
SQL& host emctl stopdbconsole
Oracle Enterprise Manager11g Database Control Release 11.2.0.3.0
Copyright (c) Oracle 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
0 01:46 pts/0
00:00:00 grep ora
3、备份老的ORACLE_HOME和oraInventory
[root@db01 ~]#tar –cvfproduct.zip /DBSoft/product/
[root@db01 ~]#tar –cvforaInventory.zip /DBSoft/product/oraInventory/
4、上传并解压缩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 oinstall Oct
3 06:55p040_Linux-x86-64_1of7.zip
-rw-r--r-- 1 oracle oinstall Oct
3 06:54p040_Linux-x86-64_2of7.zip
[oracle@db01 ~]$ unzipp040_Linux-x86-64_1of7.zip
[oracle@db01 ~]$ unzipp040_Linux-x86-64_2of7.zip
5、 开始安装
6、 执行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 ~]#
7、至此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/
8、 拷贝监听配置文件
[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
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
9、执行预升级脚本检查
[oracle@db01 dbs]$ sqlplus /as sysdba
SQL*Plus: Release 11.2.0.4.0Production on Sat Oct 4 02:26:01 2014
Copyright (c) ,Oracle.
All rights reserved.
Connected to an idleinstance.
ORACLE instance started.
Total System Global Area bytes
Fixed Size
2254704 bytes
Variable Size
Database Buffers
Redo Buffers
5373952 bytes
Database mounted.
Database opened.
SQL&@?/rdbms/admin/utlu112i.sql
---执行升级前检查
Oracle Database 11.2Pre-Upgrade Information Tool 10-04-:38
Script Version: 11.2.0.4.0Build: 001
**********************************************************************
**********************************************************************
--& version:
11.2.0.3.0
--& compatible:
11.2.0.0.0
--& blocksize:
--& 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
--& Oracle Packages andTypes
--& JServer JAVA VirtualMachine [upgrade]
--& Oracle XDK for
--& Oracle WorkspaceManager
--& OLAP Analytic Workspace
[upgrade] VALID
--& OLAP Catalog
--& EM Repository
--& Oracle Text
--& Oracle XMLDatabase
--& Oracle Java Packages
--& OracleinterMedia
--& Spatial
--& ExpressionFilter
--& Rule Manager
--& Oracle ApplicationExpress
... APEX will only beupgraded if the version of APEX in
... the target Oracle homeis higher than the current one.
--& Oracle OLAP API
**********************************************************************
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_
**********************************************************************
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:
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.
修改不满足项
SQL& show parametershared
----------------------------------------------- ------------------------------
hi_shared_memory_address
max_shared_servers
shared_memory_address
shared_pool_reserved_size
big integer 8M
shared_pool_size
big integer 160M
shared_server_sessions
shared_servers
SQL& alter system setshared_pool_size=800m scope=
System altered.
SQL& shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL& startup mountl
SP2-0714: invalidcombination of STARTUP options
ORACLE instance started.
Total System Global Area bytes
Fixed Size
2256712 bytes
Variable Size
Database Buffers
Redo Buffers
Database mounted.
SQL& alter
###创建一个回滚点
SQL& create restore pointmaclean_rollback guarante
Restore point created.
SQL& select * fromv$restore_
SCN DATABASE_INCARNATION# GUASTORAGE_SIZE
------------------------------- --- ------------
---------------------------------------------------------------------------
RESTORE_POINT_TIME
------------------------------------------------------------------------------
--------------------------------------------------------------------------------
04-OCT-14 02.41.43.AM
MACLEAN_ROLLBACK
再次运行检查：
SQL&@?/rdbms/admin/utlu112i.sql
Oracle Database 11.2Pre-Upgrade Information Tool 10-04-:20
Script Version: 11.2.0.4.0Build: 001
**********************************************************************
**********************************************************************
--& version:
11.2.0.3.0
--& compatible:
11.2.0.0.0
--& blocksize:
--& 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:
/DBSoft/fast_recovery_area
--& limit:
--& reclaim:
--& files:
WARNING: --& FlashbackRecovery Area Set.
Please ensureadequate disk space
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
--& Oracle Packages andTypes
--& JServer JAVA VirtualMachine
--& Oracle XDK forJava
--& Oracle WorkspaceManager
--& OLAP AnalyticWorkspace
--& OLAP Catalog
--& EM Repository
--& Oracle Text
--& Oracle XMLDatabase
--& Oracle JavaPackages
--& OracleinterMedia
--& Spatial
--& ExpressionFilter
--& Rule Manager
--& Oracle ApplicationExpress
... APEX will only beupgraded if the version of APEX in
... the target Oracle homeis higher than the current one.
--& Oracle OLAP API
**********************************************************************
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_
**********************************************************************
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:
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& shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area bytes
Fixed Size
2256712 bytes
Variable Size
Database Buffers
Redo Buffers
Database mounted.
Database opened.
SQL& set echo on
SQL& spool/home/oracle/upgrade.log
03:28:37 SQL&@?/rdbms/admin/catupgrd.sql
---该脚本会运行十分钟左右
Oracle Database 11.2Post-Upgrade Status Tool
Elapsed Time
Oracle Server
11.2.0.4.0
JServer JAVA Virtual Machine
11.2.0.4.0
Oracle Workspace Manager
11.2.0.4.0
OLAP Analytic Workspace
11.2.0.4.0
OLAP Catalog
11.2.0.4.0
Oracle OLAP API
11.2.0.4.0
Oracle Enterprise Manager
11.2.0.4.0
Oracle XDK
11.2.0.4.0
Oracle Text
11.2.0.4.0
Oracle XML Database
11.2.0.4.0
Oracle Database JavaPackages
11.2.0.4.0
Oracle Multimedia
11.2.0.4.0
11.2.0.4.0
Oracle Expression Filter
11.2.0.4.0
Oracle Rules Manager
11.2.0.4.0
Oracle Application Express
3.2.1.00.12
Final Actions
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 complete.
04:01:36 SQL&
04:01:36 SQL&
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 bug - 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脚本编译失效对象：
12、运行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) ,Oracle.
All rights reserved.
Connected to an idleinstance.
SQL& startup
ORACLE instance started.
Total System Global Area bytes
Fixed Size
2256712 bytes
Variable Size
Database Buffers
Redo Buffers
Database mounted.
Database opened.
SQL& @?/rdbms/admin/utlrp
--------------------------------------------------------------------------------
COMP_TIMESTAMPUTLRP_BGN
The following PL/SQL block invokesUTL_RECOMP to recompile invalid
objects in the database. Recompilation timeis proportional to the
number of invalid objects in the database,so this command may take
a long time to execute on a database with alarge number of invalid
Use the following queries to trackrecompilation progress:
1. Query returning the number of invalidobjects remaining. This
number should decrease with time.
SELECT COUNT(*) FROM obj$ WHERE statusIN (4, 5, 6);
2. Query returning the number of objectscompiled so far. This number
should increase with time.
SELECT COUNT(*) FROMUTL_RECOMP_COMPILED;
This script automatically chooses serial orparallel recompilation
based on the number of CPUs available(parameter cpu_count) multiplied
by the number of threads per CPU (parameterparallel_threads_per_cpu).
On RAC, this number is added across all RACnodes.
UTL_RECOMP uses DBMS_SCHEDULER to createjobs for parallel
recompilation. Jobs are created withoutinstance affinity so that they
can migrate across RAC nodes. Use thefollowing queries to verify
whether UTL_RECOMP jobs are being createdand run correctly:
1. Query showing jobs created by UTL_RECOMP
SELECT job_name FROM dba_scheduler_jobs
WHERE job_name like'UTL_RECOMP_SLAVE_%';
2. Query showing UTL_RECOMP jobs that arerunning
SELECT job_name FROMdba_scheduler_running_jobs
WHERE job_name like'UTL_RECOMP_SLAVE_%';
PL/SQL proceduresuccessfully completed.
--------------------------------------------------------------------------------
COMP_TIMESTAMPUTLRP_END
DOC& The following queryreports the number of objects that have compiled
DOC& with errors.
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.
OBJECTS WITH ERRORS
-------------------
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.
ERRORS DURING RECOMPILATION
---------------------------
Function created.
PL/SQL proceduresuccessfully completed.
Function dropped.
PL/SQL proceduresuccessfully completed.
该脚本耗时约为3分钟左右。
13、至此数据库已经升级完成，查看各版本号：
SQL& select comp_name,status,version fromdba_server_registry
------------------------------------------------------ ------------------------------
11.2.0.3.0
Oracle ApplicationExpress
3.2.1.00.12
Oracle EnterpriseManager
11.2.0.4.0
OLAP Catalog
11.2.0.4.0
11.2.0.4.0
Oracle Multimedia
11.2.0.4.0
Oracle XML Database
11.2.0.4.0
Oracle Text
11.2.0.4.0
Oracle ExpressionFilter
11.2.0.4.0
Oracle Rules Manager
11.2.0.4.0
Oracle WorkspaceManager
11.2.0.4.0
Oracle Database CatalogViews
11.2.0.4.0
Oracle Database Packages andTypes
11.2.0.4.0
JServer JAVA VirtualMachine
11.2.0.4.0
Oracle XDK
11.2.0.4.0
Oracle Database JavaPackages
11.2.0.4.0
OLAP Analytic Workspace
11.2.0.4.0
Oracle OLAP API
11.2.0.4.0
18 rows selected.
14、检查无效对象：
SQL& select * fromdba_objects where status !='VALID';
no rows selected
15、升级成功后删除原来的目录，通过DBCA重建EM
[oracle@db01 /]$ rm -rf/DBSoft/Product/11.2.3/
手工创建EM资料库:
####emca -repos drop
[oracle@db01 /]$ emca -reposdrop
STARTED EMCA at Oct 4,
EM Configuration Assistant,Version 11.2.0.3.0 Production
Copyright (c) ,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, :53 AMoracle.sysman.emcp.EMConfig perform
INFO: This operation isbeing logged at /DBSoft/cfgtoollogs/emca/woo/emca__06_11_41.log.
Oct 4, :53 AMoracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EMrepository (this may take a while) ...
Oct 4, :37 AMoracle.sysman.emcp.EMReposConfig invoke
INFO: Repositorysuccessfully dropped
Enterprise Managerconfiguration completed successfully
FINISHED EMCA at Oct 4,
####emca -repos create
[oracle@db01 /]$ emca -reposcreate
STARTED EMCA at Oct 4,
EM Configuration Assistant,Version 11.2.0.3.0 Production
Copyright (c) ,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, :20 AM oracle.sysman.emcp.EMConfigperform
INFO: This operation isbeing logged at /DBSoft/cfgtoollogs/emca/woo/emca__06_14_07.log.
Oct 4, :21 AMoracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EMrepository (this may take a while) ...
Oct 4, :57 AMoracle.sysman.emcp.EMReposConfig invoke
INFO: Repositorysuccessfully created
Enterprise Managerconfiguration completed successfully
FINISHED EMCA at Oct 4,
###emca -config dbcontrol db
[oracle@db01 /]$ emca-config dbcontrol db
STARTED EMCA at Oct 4,
EM Configuration Assistant,Version 11.2.0.3.0 Production
Copyright (c) ,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, :49 AMoracle.sysman.emcp.EMConfig perform
INFO: This operation isbeing logged at /DBSoft/cfgtoollogs/emca/woo/emca__06_24_04.log.
Oct 4, :50 AMoracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping DatabaseControl (this may take a while) ...
Oct 4, :54 AMoracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploadingconfiguration data to EM repository (this may take a while) ...
Oct 4, :53 AMoracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configurationdata successfully
Oct 4, :57 AMoracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing DatabaseControl (this may take a while) ...
Oct 4, :03 AMoracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Controlsecured successfully.
Oct 4, :03 AMoracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting DatabaseControl (this may take a while) ...
Oct 4, :23 AMoracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Controlstarted successfully
Oct 4, :23 AMoracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO:&&&&&&&&&&& The Database Control URL ishttps://db01:5500/em &&&&&&&&&&&
Oct 4, :25 AMoracle.sysman.emcp.EMDBPostConfig invoke
************************
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,
[oracle@db01 /]$
16、至此，升级以及全部完成。
(window.slotbydup=window.slotbydup || []).push({
id: '2467140',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467141',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467142',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467143',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467148',
container: s,
size: '1000,90',
display: 'inlay-fix'