您还可以使用以下方式登录
当前位置：&>&&>&&>& > 探索Oracle之数据库升级二11.2.0.3升级到11.2.0.4完整步骤
探索Oracle之数据库升级二11.2.0.3升级到11.2.0.4完整步骤
探索Oracle之数据库升级二 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 -aLinux db01 2.6.18-308.el5 #1SMP Fri Jan 27 17:17:51 EST
x86_64 x86_64 GNU/Linux[root@db01 ~]# lsb_release-aLSB 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-noarchDistributor ID:RedHatEnterpriseServerDescription:
Red Hat Enterprise Linux Server release 5.8(Tikanga)Release:
5.8Codename:
Tikanga[root@db01 ~]# su - oracle-c &sqlplus / as sysdba&;SQL*Plus: Release 11.2.0.3.0Production on Fri Oct 3 21:32:02 2014Copyright (c) ,Oracle.
All rights reserved. Connected to:Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP,Data Mining and Real Application Testing optionsSQL& select name fromv$ NAME---------WOOSQL&
2、备份数据库[oracle@db01 ~]$ rman target/ Recovery Manager: Release11.2.0.3.0 - Production on Fri Oct 3 21:43:04 2014Copyright (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-14current log archivedusing target databasecontrol file instead of recovery catalogallocated channel:ORA_DISK_1channel ORA_DISK_1: SID=149device type=DISKchannel ORA_DISK_1: startingarchived log backup setchannel ORA_DISK_1:specifying archived log(s) in backup setinput 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-14channel ORA_DISK_1: finishedpiece 1 at 03-OCT-14piecehandle=/DBBackup/Phycal/full_01pk5knb_1_1.bak tag=TAG307comment=NONEchannel ORA_DISK_1: backupset complete, elapsed time: 00:00:02channel 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-14Starting backup at 03-OCT-14using channel ORA_DISK_1channel ORA_DISK_1: startingfull datafile backup setchannel ORA_DISK_1:specifying datafile(s) in backup setinput datafile filenumber=00001 name=/DBData/woo/system01.dbfinput datafile filenumber=00002 name=/DBData/woo/sysaux01.dbfinput datafile filenumber=00005 name=/DBData/woo/example01.dbfinput datafile filenumber=00003 name=/DBData/woo/undotbs01.dbfinput datafile filenumber=00004 name=/DBData/woo/users01.dbfchannel ORA_DISK_1: startingpiece 1 at 03-OCT-14channel ORA_DISK_1: finishedpiece 1 at 03-OCT-14piecehandle=/DBSoft/fast_recovery_area/WOO/backupset//o1_mf_nnndf_TAG309_b2x9zfm5_.bkptag=TAG309 comment=NONEchannel ORA_DISK_1: backupset complete, elapsed time: 00:01:45channel ORA_DISK_1: startingfull datafile backup setchannel ORA_DISK_1:specifying datafile(s) in backup setincluding current controlfile in backup setincluding current SPFILE inbackup setchannel ORA_DISK_1: startingpiece 1 at 03-OCT-14channel ORA_DISK_1: finishedpiece 1 at 03-OCT-14piecehandle=/DBSoft/fast_recovery_area/WOO/backupset//o1_mf_ncsnf_TAG309_b2xb2qlm_.bkptag=TAG309 comment=NONEchannel ORA_DISK_1: backupset complete, elapsed time: 00:00:01Finished backup at 03-OCT-14 Starting backup at 03-OCT-14current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: startingarchived log backup setchannel ORA_DISK_1:specifying archived log(s) in backup setinput archived log thread=1sequence=21 RECID=7 STAMP=channel ORA_DISK_1: startingpiece 1 at 03-OCT-14channel ORA_DISK_1: finishedpiece 1 at 03-OCT-14piecehandle=/DBBackup/Phycal/full_04pk5kqo_1_1.bak tag=TAG456comment=NONEchannel ORA_DISK_1: backupset complete, elapsed time: 00:00:01channel 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 RMAN& 3、停止数据库###停止数据库SQL& shutdown immediateDatabase 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###停止EMSQL& host emctl stopdbconsoleOracle 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|greporaroot
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 ~]$ lltotal 2489644drwxr-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 byDatabase ConfigurationAssistant when a database is createdFinished 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.4woo:/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]$ lssamples
shrept.lst[oracle@db01 admin]$ cp -r/DBSoft/Product/11.2.3/db_1/network/admin/* $ORACLE_HOME/network/admin[oracle@db01 admin]$ lslistener.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 2014Copyright (c) ,Oracle.
All rights reserved.Connected to an idleinstance. SQL&ORACLE instance started.Total System Global Area bytesFixed Size
2254704 bytesVariable Size
bytesDatabase Buffers
bytesRedo Buffers
5373952 bytesDatabase mounted.Database opened.SQL&@?/rdbms/admin/utlu112i.sql
---执行升级前检查Oracle Database 11.2Pre-Upgrade Information Tool 10-04-:38Script 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
VALID--& Oracle Packages andTypes
VALID--& JServer JAVA VirtualMachine [upgrade]
VALID--& Oracle XDK forJava
VALID--& Oracle WorkspaceManager
VALID--& OLAP Analytic Workspace
[upgrade] VALID--& OLAP Catalog
VALID--& EM Repository
VALID--& Oracle Text
VALID--& Oracle XMLDatabase
VALID--& Oracle Java Packages
VALID--& OracleinterMedia
VALID--& Spatial
VALID--& ExpressionFilter
VALID--& Rule Manager
VALID--& Oracle ApplicationExpress
VALID... APEX will only beupgraded if the version of APEX in... the target Oracle homeis higher than the current one.--& Oracle OLAP API
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 toupgrading the database.To gather dictionarystatistics execute the following commandwhile connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_ **********************************************************************Oracle recommends removingall hidden parameters prior to upgrading.To view existing hiddenparameters execute the following commandwhile 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 commandswhile 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. 10、
修改不满足项SQL& show parametershared NAME
VALUE----------------------------------------------- ------------------------------hi_shared_memory_address
0max_shared_servers
integershared_memory_address
0shared_pool_reserved_size
big integer 8Mshared_pool_size
big integer 160Mshared_server_sessions
integershared_servers
1SQL& alter system setshared_pool_size=800m scope= System altered. SQL& shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL& startup mountlSP2-0714: invalidcombination of STARTUP optionsSQL&ORACLE instance started.Total System Global Area bytesFixed Size
2256712 bytesVariable Size
bytesDatabase Buffers
bytesRedo Buffers
bytesDatabase mounted.SQL& alter
###创建一个回滚点SQL& create restore pointmaclean_rollback guarante Restore point created. SQL& select * fromv$restore_
SCN DATABASE_INCARNATION# GUASTORAGE_SIZE------------------------------- --- ------------TIME---------------------------------------------------------------------------RESTORE_POINT_TIME
PRE------------------------------------------------------------------------------NAME--------------------------------------------------------------------------------
-OCT-14 02.41.43.AM
YESMACLEAN_ROLLBACK 再次运行检查：SQL&@?/rdbms/admin/utlu112i.sqlOracle Database 11.2Pre-Upgrade Information Tool 10-04-:20Script 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:
MB--& files:
11WARNING: --& FlashbackRecovery Area Set.
Please ensureadequate disk space
inrecovery 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
VALID--& Oracle Packages andTypes
VALID--& JServer JAVA VirtualMachine
VALID--& Oracle XDK forJava
VALID--& Oracle WorkspaceManager
VALID--& OLAP AnalyticWorkspace
VALID--& OLAP Catalog
VALID--& EM Repository
VALID--& Oracle Text
VALID--& Oracle XMLDatabase
VALID--& Oracle JavaPackages
VALID--& OracleinterMedia
VALID--& Spatial
VALID--& ExpressionFilter
VALID--& Rule Manager
VALID--& Oracle ApplicationExpress
VALID... APEX will only beupgraded if the version of APEX in... the target Oracle homeis higher than the current one.--& Oracle OLAP API
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 toupgrading the database.To gather dictionarystatistics execute the following commandwhile connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_**********************************************************************Oracle recommends removingall hidden parameters prior to upgrading.To view existing hiddenparameters execute the following commandwhile 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 commandswhile 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 immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL&ORACLE instance started.Total System Global Area bytesFixed Size
2256712 bytesVariable Size
bytesDatabase Buffers
bytesRedo Buffers
bytesDatabase mounted.Database opened.SQL& set echo onSQL& spool/home/oracle/upgrade.logSQL&03:28:37 SQL&@?/rdbms/admin/catupgrd.sql
---该脚本会运行十分钟左右……..Oracle Database 11.2Post-Upgrade Status Tool
10-04-:36.Component
Elapsed TimeName
HH:MM:SS.Oracle Server.
11.2.0.4.0
00:08:38JServer JAVA Virtual Machine.
11.2.0.4.0
00:04:26Oracle Workspace Manager.
11.2.0.4.0
00:00:25OLAP Analytic Workspace.
11.2.0.4.0
00:01:27OLAP Catalog.
11.2.0.4.0
00:00:32Oracle OLAP API.
11.2.0.4.0
00:00:19Oracle Enterprise Manager.
11.2.0.4.0
00:06:25Oracle XDK.
11.2.0.4.0
00:00:26Oracle Text.
11.2.0.4.0
00:00:24Oracle XML Database.
11.2.0.4.0
00:01:46Oracle Database JavaPackages.
11.2.0.4.0
00:00:08Oracle Multimedia.
11.2.0.4.0
00:01:43Spatial.
11.2.0.4.0
00:05:34Oracle Expression Filter.
11.2.0.4.0
00:00:06Oracle Rules Manager.
11.2.0.4.0
00:00:07Oracle Application Express.
3.2.1.00.12Final Actions.
00:00:00Total Upgrade Time: 00:32:35 PL/SQL proceduresuccessfully completed. 04:01:36 SQL& 04:01:36 SQL& SETSERVEROUTPUT OFF04:01:36 SQL& SET VERIFYON04: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& DOC04:01:54 DOC&#######################################################################04:01:54DOC&#######################################################################04:01:54 DOC&04:01:54 DOC&
The above sql script is the final step ofthe upgrade. Please04:01:54 DOC&
review any errors in the spool log file. Ifthere are any errors in04:01:54 DOC&
the spool file, consult the Oracle DatabaseUpgrade Guide for04:01:54 DOC&
troubleshooting recommendations.04:01:54 DOC&04:01:54 DOC&
Next restart for normal operation, and thenrun utlrp.sql to04: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 to04:01:54 DOC&
upgrade, then please run the DBMS_DSTpackage.
DBMS_DST will upgrade04:01:54 DOC&
TIMESTAMP WITH TIME ZONE data to use thelatest time zone file shipped04: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 off04:01:54 SQL& SETERRORLOGGING OFF;04:01:54 SQL& 04:01:54 SQL& REM END OFCATUPGRD.SQL04: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 order04:01:54 SQL& REM
to connect to the upgraded db.04:01:54 SQL& exitDisconnected from OracleDatabase 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith 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 2014Copyright (c) ,Oracle.
All rights reserved. Connected to an idleinstance. SQL& startupORACLE instance started. Total System Global Area bytesFixed Size
2256712 bytesVariable Size
bytesDatabase Buffers
bytesRedo Buffers
bytesDatabase mounted.Database opened.SQL& @?/rdbms/admin/utlrp TIMESTAMP--------------------------------------------------------------------------------COMP_TIMESTAMPUTLRP_BGN
04:14:57DOC&
The following PL/SQL block invokesUTL_RECOMP to recompile invalidDOC&
objects in the database. Recompilation timeis proportional to theDOC&
number of invalid objects in the database,so this command may takeDOC&
a long time to execute on a database with alarge number of invalidDOC&
objects.DOC&DOC&
Use the following queries to trackrecompilation progress:DOC&DOC&
1. Query returning the number of invalidobjects remaining. ThisDOC&
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 numberDOC&
should increase with time.DOC&
SELECT COUNT(*) FROMUTL_RECOMP_COMPILED;DOC&DOC&
This script automatically chooses serial orparallel recompilationDOC&
based on the number of CPUs available(parameter cpu_count) multipliedDOC&
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 parallelDOC&
recompilation. Jobs are created withoutinstance affinity so that theyDOC&
can migrate across RAC nodes. Use thefollowing queries to verifyDOC&
whether UTL_RECOMP jobs are being createdand run correctly:DOC&DOC&
1. Query showing jobs created by UTL_RECOMPDOC&
SELECT job_name FROM dba_scheduler_jobsDOC&
WHERE job_name like'UTL_RECOMP_SLAVE_%';DOC&DOC&
2. Query showing UTL_RECOMP jobs that arerunningDOC&
SELECT job_name FROMdba_scheduler_running_jobsDOC&
WHERE job_name like'UTL_RECOMP_SLAVE_%';DOC&# PL/SQL proceduresuccessfully completed.
TIMESTAMP--------------------------------------------------------------------------------COMP_TIMESTAMPUTLRP_END
04:15:34 DOC& The following queryreports the number of objects that have compiledDOC& with errors.DOC&DOC& If the number ishigher than expected, please examine the errorDOC& messages reportedwith each object (using SHOW ERRORS) to see if theyDOC& point to systemmisconfiguration or resource constraints that must beDOC& fixed beforeattempting to recompile these objects.DOC&# OBJECTS WITH ERRORS-------------------
0 DOC& The following queryreports the number of errors caught duringDOC& recompilation. Ifthis number is non-zero, please query the errorDOC& messages in thetable UTL_RECOMP_ERRORS to see if any of these errorsDOC& are due tomisconfiguration or resource constraints that must beDOC& fixed before objectscan compile successfully.DOC&# ERRORS DURING RECOMPILATION---------------------------
Function created. PL/SQL proceduresuccessfully completed. Function dropped. PL/SQL proceduresuccessfully completed.SQL& 该脚本耗时约为3分钟左右。 13、至此数据库已经升级完成，查看各组件版本号： SQL& select comp_name,status,version fromdba_server_registry COMP_NAME
VERSION------------------------------------------------------ ------------------------------OWB
11.2.0.3.0Oracle ApplicationExpress
3.2.1.00.12Oracle EnterpriseManager
11.2.0.4.0OLAP Catalog
11.2.0.4.0Spatial
11.2.0.4.0Oracle Multimedia
11.2.0.4.0Oracle XML Database
11.2.0.4.0Oracle Text
11.2.0.4.0Oracle ExpressionFilter
11.2.0.4.0Oracle Rules Manager
11.2.0.4.0Oracle WorkspaceManager
11.2.0.4.0Oracle Database CatalogViews
11.2.0.4.0Oracle Database Packages andTypes
11.2.0.4.0JServer JAVA VirtualMachine
11.2.0.4.0Oracle XDK
11.2.0.4.0Oracle Database JavaPackages
11.2.0.4.0OLAP Analytic Workspace
11.2.0.4.0Oracle 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 -reposdropSTARTED EMCA at Oct 4,
AMEM Configuration Assistant,Version 11.2.0.3.0 ProductionCopyright (c) ,Oracle.
All rights reserved. Enter the followinginformation:Database SID: wooListener port number: 1521Password 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)]: yOct 4, :53 AMoracle.sysman.emcp.EMConfig performINFO: This operation isbeing logged at /DBSoft/cfgtoollogs/emca/woo/emca__06_11_41.log.Oct 4, :53 AMoracle.sysman.emcp.EMReposConfig invokeINFO: Dropping the EMrepository (this may take a while) ...Oct 4, :37 AMoracle.sysman.emcp.EMReposConfig invokeINFO: Repositorysuccessfully droppedEnterprise Managerconfiguration completed successfullyFINISHED EMCA at Oct 4,
AM####emca -repos create [oracle@db01 /]$ emca -reposcreate STARTED EMCA at Oct 4,
AMEM Configuration Assistant,Version 11.2.0.3.0 ProductionCopyright (c) ,Oracle.
All rights reserved. Enter the followinginformation:Database SID: wooListener port number: 1521Password for SYS user:
Password for SYSMANuser:
Do you wish to continue?[yes(Y)/no(N)]: yOct 4, :20 AM oracle.sysman.emcp.EMConfigperformINFO: This operation isbeing logged at /DBSoft/cfgtoollogs/emca/woo/emca__06_14_07.log.Oct 4, :21 AMoracle.sysman.emcp.EMReposConfig createRepositoryINFO: Creating the EMrepository (this may take a while) ...Oct 4, :57 AMoracle.sysman.emcp.EMReposConfig invokeINFO: Repositorysuccessfully createdEnterprise Managerconfiguration completed successfullyFINISHED EMCA at Oct 4,
AM ###emca -config dbcontrol db[oracle@db01 /]$ emca-config dbcontrol db STARTED EMCA at Oct 4,
AMEM Configuration Assistant,Version 11.2.0.3.0 ProductionCopyright (c) ,Oracle.
All rights reserved. Enter the followinginformation:Database SID: wooDatabase Control is alreadyconfigured for the database wooYou have chosen to configureDatabase Control for managing the database wooThis will remove theexisting configuration and the default settings and perform a freshconfigurationDo you wish to continue?[yes(Y)/no(N)]: yListener 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 settingsDatabase ORACLE_HOME................ /DBSoft/Product/11.2.4/db_1Local hostname................ db01Listener ORACLE_HOME................ /DBSoft/Product/11.2.4/db_1Listener port number................ 1521Database SID................ wooEmail address fornotifications ...............Outgoing Mail (SMTP) serverfor notifications ...............-----------------------------------------------------------------Do you wish to continue?[yes(Y)/no(N)]: yOct 4, :49 AMoracle.sysman.emcp.EMConfig performINFO: This operation isbeing logged at /DBSoft/cfgtoollogs/emca/woo/emca__06_24_04.log.Oct 4, :50 AMoracle.sysman.emcp.util.DBControlUtil stopOMSINFO: Stopping DatabaseControl (this may take a while) ...Oct 4, :54 AMoracle.sysman.emcp.EMReposConfig uploadConfigDataToRepositoryINFO: Uploadingconfiguration data to EM repository (this may take a while) ...Oct 4, :53 AMoracle.sysman.emcp.EMReposConfig invokeINFO: Uploaded configurationdata successfullyOct 4, :57 AMoracle.sysman.emcp.util.DBControlUtil secureDBConsoleINFO: Securing DatabaseControl (this may take a while) ...Oct 4, :03 AMoracle.sysman.emcp.util.DBControlUtil secureDBConsoleINFO: Database Controlsecured successfully.Oct 4, :03 AMoracle.sysman.emcp.util.DBControlUtil startOMSINFO: Starting DatabaseControl (this may take a while) ...Oct 4, :23 AMoracle.sysman.emcp.EMDBPostConfig performConfigurationINFO: Database Controlstarted successfullyOct 4, :23 AMoracle.sysman.emcp.EMDBPostConfig performConfigurationINFO:&&&&&&&&&&& The Database Control URL ishttps://db01:5500/em &&&&&&&&&&&Oct 4, :25 AMoracle.sysman.emcp.EMDBPostConfig invokeWARNING: ************************
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 successfullyFINISHED EMCA at Oct 4,
AM[oracle@db01 /]$ 16、至此，升级以及全部完成。 就爱阅读www.92to.com网友整理上传,为您提供最全的知识大全,期待您的分享，转载请注明出处。
欢迎转载：
相关推荐：