34689人阅读
Advanced Knowledge（186）
&Oracle 11.2.0.1 单实例升级到11.2.0.3。 Oracle 升级的步骤都差不多。 先升级Oracle software，然后升级Oracle instance。&Oracle 11.2.0.2的patchset No 是：。 关于该Patchset 的下载地址，参考我之前的blog：&&&&&& & & & &&&&&&&& Oracle11.2.0.3 的Patchset no是：，下载地址：& & & && & & &&patchset 有7个文件，关于这7个文件的不同作用，参考：&&&&&& &&&&&& & & & & & & &&我们升级DB， 只需要其中的第一个和第二个文件。将2个文件解压缩，然后将第二个文件的内容copy到第一个文件的对应目录里。即将2个文件合成一个文件。&&一.查看DB 相关的信息&我们安装的是64位的DB。&[oracle@dave ~]$ sqlplus /&SQL*Plus: Release 11.2.0.1.0 Production onWed Sep 28 10:23:51 2011&Copyright (c) , Oracle.& All rights reserved.&&Connected to:Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Miningand Real Application Testing options&SQL& select name from v$&NAME---------ANQING&&&二.升级的相关说明2.1 Patchset 的说明这部分内容copy 自patchset的readme 文档。&Oracle Databasepatch set, release 11.2.0.3（11.2.0.2） is a full installation of the Oracle Database software. This meansthat you do not need to install Oracle Database 11g&Release 2 (11.2.0.1)before installing Oracle Database 11g&Release 2 (11.2.0.3/11.2.0.2).&&&&&& Oracle 11.2.0.2 和 11.2.0.3 的patchset都是完整的安装包。所以可以直接用来安装。&Note the following changes with the newpatch set packaging:（1）Newinstallations consist of installing the most recent patch set, rather thaninstalling a base release and then upgrading to a patch release.（2）Directupgrades from previous releases to the most recent patch set are supported.（3）Out-of-placepatch set upgrades recommended, in which you install the patch set into a new,separate Oracle home. In-place upgrades are supported, but not recommended.&2.1.1 In-Place UpgradeThis feature enablesyou to upgrade an existing installation of Oracle database to Oracle Database11g&Release 2 (11.2.0.3) into the same Oracle home directory by replacingthe existing database files. The patch set application requires more downtimeand is not recommended. This upgrade type requires less disk space.将升级文件安装到原来的Oracle home 目录，这样可以节省磁盘空间，但是需要更多的downtime。 这种并不推荐。&2.1.2 Out-of-Place UpgradeThis featureenables you to install Oracle Database 11g&Release 2 (11.2.0.3) in a new,separate Oracle home directory. After the installation is complete, the datafrom the database in the old Oracle home directory is migrated to the databasein the new Oracle home directory.可以将Oracle Database 安装的新的OracleHome directory，当安装结束后，旧的database 会迁移到新的Oracle homedirectory。&Out-of-placeupgrade process is similar to Oracle database upgrade from one version toanother and is the recommended type. The benefits of this upgrade type is thatthe patch set application requires much less downtime, and is safer when thepatch set application must be aborted. This upgrade type requires more diskspace to accommodate two Oracle home directories simultaneously.&&&&&& 这种方法是oracle推荐的方式，它可以降低downtime 时间，也相对更安全。但是这种方法需要2倍的地盘空间，不过对与现在白菜价硬盘来说，这点空间也不算什么了。&2.2 升级步骤Oracle 10g的Readme上有详细的升级步骤，但是11g的Readme上并没有详细的升级说明，需要参考官方文档。&（1）&&& 备份数据库（2）&&& 运行patchset，升级oracle 软件（3）&&& 准备新的ORACLE_HOME（4）&&& 运行dbua 或者脚本升级实例（5）&&& 检查升级后的版本信息和无效对象&官网地址：Upgrading to theNew Release&我这里也是一个单实例的升级，相对较简单，如果是RAC的升级，估计还要费劲一点，11gR2RAC 升级后面会继续进行测试。&&三.具体升级操作3.1 备份DB我这里是测试环境，就不进行备份，如果是生产环境，切记要先备份，后操作。如果是In-Place Upgrade，同时备份一下整个ORACLE_HOME目录。 如果升级有问题，还可以还原回来。&重点是如下目录：ORACLE_HOME/dbsORACLE_HOME/network/adminORACLE_HOME/hostname_dbnameORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_hostname_dbname&&3.2 升级Oracle database 软件运行Patchset 的runInstaller。 &&注意这里，我们选择第三个选项，skip software update，因为我们的patchset已经下载过了，如果选择第二个选项，会遇到如下错误，&INS-20704 Thelocation provided is not in the expected directory structure&Oracle 给的解决方式是用如下命令来执行：./runInstaller –downloadUpdates&但是这个命令需要收费的Metalink帐号。 MOS 的相关文档：How To Download The Latest Updates AndPatches Using 11.2.0.2 OUI [ID ]Error: INS-20704 While Installing 11.2.0.2with &Use pre-downloaded software updates& Option [ID ]&这里要注意，我们之前讲过11gR2的Patchset 可以直接用来安装。在这个界面就可以选择操作类型，这个功能比较方便，从而也导致patchset 越来越大，我们这里选择upgrade an existing database。&&注意这里的安装位置，我之前的安装目录是11.2.0. 这里我改成了11.2.0.3. 即将oracle 安装到其他位置，这样可以减少宕机时间，也是oracle 推荐的方法。&&注意这里的Inventory directory，这里用来存放安装文件的，我们之前安装过DB，所以这个目录已经存在，2种解决方法，删除之前的目录，二放到其他目录，我这里将安装文件存放到其他目录。&&&[root@dave oraInventory]#/u01/app/oraInventory2/orainstRoot.shChanging permissions of/u01/app/oraInventory2.Adding read,write permissions for group.Removing read,write,execute permissions forworld.&Changing groupname of/u01/app/oraInventory2 to oinstall.The execution of the script is complete.[root@dave oraInventory]#/u01/app/oracle/product/11.2.0.3/db_1/root.shPerforming root user operation for Oracle11g &The following environment variables are setas:&&&ORACLE_OWNER= oracle&&&ORACLE_HOME=&/u01/app/oracle/product/11.2.0.3/db_1&Enter the full pathname of the local bindirectory: [/usr/local/bin]: &&Copying dbhome to /usr/local/bin ...&&Copying oraenv to /usr/local/bin ...&&Copying coraenv to /usr/local/bin ...&Entries will be added to the /etc/oratabfile as needed byDatabase Configuration Assistant when adatabase is createdFinished running generic part of rootscript.Now product-specific root actions will beperformed.Finished product-specific root actions.&&这里会提示我们配置监听，因为监听之前已经存在，所以这里取消监听配置。&&我们点击下一步时，会提示一些配置失败，是否继续，我们点是。 结束Oracle database 软件的安装。&&从上面来看，如果没有错误，在监听配置完毕会，会自动调用DBUA 来升级我们的实例。 这里我们配置失败，正好验证下手工升级实例。&3.3 配置新的ORACLE_HOME这部分工作，主要是准备新的ORACLE_HOME，因为我们的Oracle database 安装到了新的位置，所以我们需要配置一下相关的信息。&这部分即使在上面自动调用了DBUA，我们也还是需要进行操作的。 &After backing upthe database to be upgraded, prepare the new Oracle home in a new location. Dothis for any release of Oracle Database for which you are upgrading, whetherthe database is release 11.2 or earlier. Starting with Oracle Database11g&Release 2 (11.2.0.2), you install the Oracle Grid Infrastructure andOracle Database software into a new Oracle home instead of applying a patch setto the existing Oracle home.&To prepare the new Oracle home：&1. Copyconfiguration files from the Oracle home of the database being upgraded to thenew Oracle Database 11g&Release 2 (11.2) Oracle home:（1）If your parameter file resideswithin the old environment's Oracle home, then copy it to the new Oracle home.By default, Oracle looks for the parameter file inthe&ORACLE_HOME/dbs&directory on Linux or UNIX platforms and inthe&ORACLE_HOME\database&directory on Windows operating systems. Theparameter file can reside anywhere you wish, but it should not reside in theold environment's Oracle home after you upgrade to Oracle Database11g&Release 2 (11.2).（2）If your parameter file resideswithin an Oracle ASM instance, then back up the parameter file using thefollowing command:CREATE pfileFROMIf you must downgrade the database andyour&SPFILE&resided within Oracle ASM, then the parameter file mustbe restored before the downgrade.（3）If your parameter file is atext-based initialization parameter file with eitheran&IFILE&(include file) or a&SPFILE&(server parameter file)entry, and the file specified in the&IFILE&or&SPFILE&entryresides within the old environment's Oracle home, then copy the file specifiedby the&IFILE&or&SPFILE&entry to the new Oracle home. Thefile specified in the&IFILE&or&SPFILE&entry containsadditional initialization parameters.（4）If you have a password file thatresides within the old environment's Oracle home, then move or copy thepassword file to the Oracle Database 11g&Release 2 (11.2) Oracle home.The name andlocation of the password file are operating system-specific. On Linux or UNIXplatforms, the default password file is&orapwsid, located intheORACLE_HOME/dbs&directory. On Windows operating systems, the defaultpassword file is&pwdsid.ora, located inthe&ORACLE_HOME\database&directory. In both cases,&sid&isyour Oracle instance ID.（5）If you are upgrading a clusterdatabase and your&initdb_name.ora&file resides within the oldenvironment's Oracle home, then move or copy theinitdb_name.ora&file tothe new Oracle home.Note:If you areupgrading a cluster database, then perform this step on all nodes in which thiscluster database has instances configured.&2.Adjust your parameterfile in Oracle Database 11g&Release 2 (11.2) by completing the followingsteps:（1）Removeobsolete initialization parameters and adjust deprecated initializationparameters. Certain parameters are obsolete in Oracle Database 11g&Release2 (11.2), while other parameters have become deprecated. Remove all obsoleteparameters from any parameter file that starts an Oracle Database11g&Release 2 (11.2) instance. Obsolete parameters might cause errors inOracle Database 11g&Release 2 (11.2). Also, alter any parameter whosesyntax has changed in the new release.The Pre-UpgradeInformation Tool displays any deprecated parameters and obsolete parameters itfinds in the&Deprecated Parameters&and&ObsoleteParameters&sections, respectively.（2）Make surethe&COMPATIBLE&initialization parameter is properly set for OracleDatabase 11g&Release 2 (11.2). The Pre-Upgrade Information Tool displays awarning in the Database section if&COMPATIBLE&is not properly set.（3）Adjust the values of the initializationparameters to at least the minimum values indicated by the Pre-UpgradeInformation Tool.（4）Make sure allpath names in the parameter file are fully specified. You should not haverelative path names in the parameter file.（5）If the parameter file containsan&IFILE&entry, then change the&IFILE&entry in theparameter file to point to the new location of the include file that youspecified in Step&.&.Then, edit the file specified in the&IFILE&entry in the same way thatyou edited the parameter file in Step&&throughStep&.（6）If you are upgrading a clusterdatabase, then modify the&initdb_name.ora&file in the same way thatyou modified the parameter file.Make sure yousave all of the files you modified after making these adjustments.&3.If you are upgrading acluster database, then set the&CLUSTER_DATABASE&initializationparameter to&false. After the upgrade, you mustset this initialization parameter back to&TRUE.&4. 修改Oracle的.bash_profile 文件&&&&&& 我们重新指定了ORACLE_HOME，需要更新到.bash_profile 里。&5. 修改/etc/oratab，该目录下也有ORACLE_HOME[oracle@dave db_1]$ cat /etc/oratabanqing:/u01/app/oracle/product/11.2.0.3/db_1:N具体操作步骤：[oracle@dave dbs]$ pwd/u01/app/oracle/product/11.2.0/db_1/dbs[oracle@dave dbs]$ cp * /u01/app/oracle/product/11.2.0.3/db_1/dbs/&[oracle@dave admin]$ pwd/u01/app/oracle/product/11.2.0/db_1/network/admin[oracle@dave admin]$ lslistener07.bak& samples&&&&sqlnet07.bak&tnsnames07.baklistener.ora&&&&&&&&&&&&&&& shrept.lst& sqlnet.ora&&&&&&&&&&&&&&& tnsnames.ora[oracle@dave admin]$ cp -R * /u01/app/oracle/product/11.2.0.3/db_1/network/admin&[oracle@dave admin]$ cat /home/oracle/.bash_profile|grep ORACLE_HOMEORACLE_HOME=$ORACLE_BASE/product/11.2.0.3/db_1; export ORACLE_HOMETNS_ADMIN=$ORACLE_HOME/network/exportTNS_ADMINPATH=$ORACLE_HOME/bin:$PATH; export PATHLD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/export LD_LIBRARY_PATHCLASSPATH=$ORACLE_HOME/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/export CLASSPATH&3.4 Pre-Upgrade Information Tool 说明After you haveinstalled the software for Oracle Database 11g&Release 2 (11.2) and anyrequired patches, you should analyze your database before upgrading it to thenew release. This is done by running the Pre-Upgrade Information Tool from theenvironment of the database you are to upgrade. The Pre-Upgrade InformationTool is a SQL script included with Oracle Database 11g&Release 2 (11.2)software. This is a required step if you ar otherwise, thecatupgrd.sql&script terminates with errors. Running the Pre-UpgradeInformation Tool is also recommended if you are upgrading with DBUA, so thatyou can preview the items that DBUA checks.&To run the Pre-Upgrade Information Tool&1.&&&&&Log in to the system as theowner of the environment of the database being upgraded.Important:The Pre-UpgradeInformation Tool must be copied to and must be run from the environment of thedatabase being upgraded.注意，这里必须调用新的ORACLE_HOME 下脚本。2.&&&&&Start SQL*Plus.3.&&&&&Connect to the databaseinstance as a user with&SYSDBA&privileges.4.&&&&&Set the system to spool resultsto a log file for later analysis:SQL& SPOOL upgrade_info.log5.&&&&&Run the Pre-Upgrade InformationTool:SQL&@$11g_ORACLE_HOME/rdbms/admin/utlu112i.sql6.&&&&&Turn off the spooling of scriptresults to the log file:SQL& SPOOL OFF&脚本运行结果如下：SQL&@/u01/app/oracle/product/11.2.0.3/db_1/rdbms/admin/utlu112i.sqlOracle Database 11.2 Pre-UpgradeInformation Tool 09-28-:36Script Version: 11.2.0.3.0 Build: 001.**********************************************************************Database:**********************************************************************--& name:&&&&&&&&& ANQING--& version:&&&&&& 11.2.0.1.0--& compatible:&&& 11.2.0.0.0--& blocksize:&&&& 8192--& platform:&&&&& Linux x86 64-bit--& timezone file: V11.**********************************************************************Tablespaces: [make adjustments in thecurrent environment]**********************************************************************--& SYSTEM tablespace is adequate forthe upgrade..... minimum required size: 686 MB--& SYSAUX tablespace is adequate forthe upgrade..... minimum required size: 484 MB--& UNDOTBS1 tablespace is adequate forthe upgrade..... minimum required size: 400 MB--& TEMP tablespace is adequate for theupgrade..... minimum required size: 60 MB--& EXAMPLE tablespace is adequate forthe upgrade..... minimum required size: 78 MB.**********************************************************************Flashback: OFF********************************************************************************************************************************************Update Parameters: [Update Oracle Database11.2 init.ora or spfile]Note: Pre-upgrade tool was run on a lowerversion 64-bit database.**********************************************************************--& If Target Oracle is 32-Bit, referhere for Update Parameters:-- No update parameter changes arerequired..&--& If Target Oracle is 64-Bit, referhere for Update Parameters:-- No update parameter changes arerequired..**********************************************************************Renamed Parameters: [Update Oracle Database11.2 init.ora or spfile]**********************************************************************-- No renamed parameters found. No changesare required..**********************************************************************Obsolete/Deprecated Parameters: [UpdateOracle Database 11.2 init.ora or spfile]**********************************************************************-- No obsolete parameters found. No changesare required.&**********************************************************************Components: [The following databasecomponents will be upgraded or installed]**********************************************************************--& Oracle Catalog Views&&&&&&&& [upgrade]& VALID--& Oracle Packages and Types&&& [upgrade]&VALID--& JServer JAVA Virtual Machine[upgrade]& VALID--& Oracle XDK for Java&&&&&&&&& [upgrade]& VALID--& Oracle Workspace Manager&&&& [upgrade]&VALID--& OLAP Analytic Workspace&&&&& [upgrade]&VALID--& OLAP Catalog&&&&& &&&&&&&&&&&[upgrade]& VALID--& EM Repository&&&&&&&&&&&&&&& [upgrade]& VALID--& Oracle Text&&&&&&&&&&&&&&&&& [upgrade]& VALID--& Oracle XML Database&&&&&&&&& [upgrade]& VALID--& Oracle Java Packages&&&&&&&& [upgrade]& VALID--& Oracle interMedia&&&&&& &&&&&[upgrade]&VALID--& Spatial&&&&&&&&&&&&&&&&&&&&& [upgrade]& VALID--& Expression Filter&&&&&&&&&&& [upgrade]& VALID--& Rule Manager&&&&&&&&&&&&&&&& [upgrade]& VALID--& Oracle Application Express&& [upgrade]&VALID... APEX will only be upgraded if theversion of APEX in... the target Oracle home is higher thanthe current one.--& Oracle OLAP API&&&&&&&&&&&&& [upgrade]& VALID.**********************************************************************Miscellaneous Warnings**********************************************************************WARNING: --& Database is using atimezone file older than version 14..... After the release migration, it isrecommended that DBMS_DST package.... be used to upgrade the 11.2.0.1.0database timezone version.... to the latest version which comes withthe new release.WARNING: --& Your recycle bin is turnedon and currently contains no objects..... Because it is REQUIRED that therecycle bin be empty prior to upgrading.... and your recycle bin is turned on, youmay need to execute the command:&&&&&&&PURGE DBA_RECYCLEBIN.... prior to executing your upgrade toconfirm the recycle bin is empty.WARNING: --& Database contains schemaswith objects dependent on DBMS_LDAP package..... Refer to the 11g Upgrade Guide forinstructions to configure Network ACLs..... USER APEX_030200 has dependentobjects..*********************************************************************Recommendations*********************************************************************Oracle recommends gathering dictionarystatistics prior to upgrading the database.To gather dictionary statistics execute thefollowing command while connected as SYSDBA:&&&&EXECUTE dbms_stats.gather_dictionary_&*********************************************************************Oracle recommends reviewing any definedevents prior to upgrading.&To view existing non-default events executethe following commandswhile connected AS SYSDBA:&Events:&&&SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2&&&&&WHERE& UPPER(name) ='EVENT'AND& isdefault='FALSE'&&Trace Events:&&&SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2&&&&&WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'&Changes will need to be made in theinit.ora or spfile.&*********************************************************************SQL&&关于这个结果的具体含义，官方文档有详细的说明。&3.5 升级Instance &&&&&& 升级Instance 有两种方法，第一种是使用dbua工具，第二种是手工执行脚本。 &3.5.1 使用DBUA 工具3.5.1.1 DBUA 工具在升级instance前会检查如下选项：（1）&&&&&&&Invalid user accounts or roles（2）&&&&&&&Invalid data types or invalidobjects（3）&&&&&&&Desupported character sets（4）&&&&&&&Adequate resources, includingrollback segments, tablespaces, and free disk space（5）&&&&&&&Missing SQL scripts needed forthe upgrade（6）&&&&&&&Listener running (if OracleEnterprise Manager Database Control upgrade or configuration is requested)（7）&&&&&&&Oracle Database software linkedwith Database Vault option&3.5.1.2 &DBUA 的一些注意事项（1）Important:&If you terminatethe upgrade, but do not restore the database, then you cannot restart DBUA. Instead,you must continue with a manual (command line) upgrade as described in&.（2）DBUA can be used to upgrade from earlierOracle Database 11g&patch releases as well as from earlier major OracleDatabase releases on both Oracle RAC databases and Oracle Databasesingle-instance databases. The procedure to upgrade patch releases is nodifferent from the normal upgrade procedure.（3）You must run the Oracle NetConfiguration Assistant before running DBUA. See&.If you are upgradingan existing Oracle Enterprise Manager Database Control or if you areconfiguring a new Oracle Enterprise Manager Database Control for the new OracleDatabase 11g&release, then the listener must be running before upgradingOracle Enterprise Manager. If DBUA detects that an Oracle Enterprise Managerupgrade or configuration is requested, and DBUA does not see a listenerrunning, then it prompts you and starts the default listener in either of thesecases.（4）If you upgrade a cluster databaseusing DBUA, then you must make surethe&CLUSTER_DATABASE&initialization parameter is set to&TRUE.（5）If the database instance is notrunning, then DBUA tries to start the instance with the default initializationparameter file. If that fails, then DBUA prompts you to provide the name of thecorrect initialization parameter file or to start the instance. If the instanceis up and running, then DBUA connects to it.（6）If you terminate the upgrade, butdo not restore the database, then you cannot restart DBUA until you start upthe existing database in&UPGRADE&mode using the new Oracle Database11g&server. You cannot go back to the original server unless you restoreyour database.For Oracle RAC,you cannot re-run DBUA once you terminate the upgrade. If you need to re-runthe upgrade, then you must run DBUA from the restored backup of the databaseyou are upgrading.（7）If you restore your databasemanually (not using DBUA), then remove the&Welcome_SID.txt&file,which is located in the&ORACLE_HOME/cfgtoollogs/dbua/logs/ directory,before starting DBUA. The presence of this file indicates to DBUA that this isa re-run operation.&DBUA 工具比较简单，直接下一步的操作。这里就不多少了。&3.5.2 手工使用命令升级实例&1.Shut down the instance:SQL& SHUTDOWNIMMEDIATE&2.If your operatingsystem is Windows, then complete the following steps:（1）Stopthe&OracleServiceSID&Oracle service of the database you areupgrading, where&SID&is the instance name. For example, ifyour&SID&is&ORCL, then enter the following at a command prompt:C:\& NET STOPOracleServiceORCL（2）Delete the Oracle service at a command prompt using&ORADIM.If your&SID&is&ORCL, thenenter the following command:C:\& ORADIM-DELETE -SID ORCL（3）Create the Oracle Database11g&Release 2 (11.2) service at a command prompt usingthe&ORADIM&command of the new Oracle Database release:C:\& ORADIM-NEW -SID SID -SYSPWD PASSWORD -MAXUSERS USERS&&&&-STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA&This syntax includes thefollowing variables:
Description
same SID name as the SID of the database you are upgrading.
password for the new Oracle Database 11g&Release 2 (11.2) database instance.
This is the password for the user connected with&SYSDBA&privileges. The&-SYSPWD&option is not required. If you do not specify it, then operating
system authentication is used, and no password is required.
default Oracle Database 11g&Release 2 (11.2) security settings
are in place, then passwords must be at least eight characters, and passwords
such as&welcome&and&oracle&are not allowed. See&&for more information.
maximum number of users who can be granted&SYSDBA&and&SYSOPER&privileges.
ORACLE_HOME
Oracle Database 11g&Release 2 (11.2) Oracle home
directory. Ensure that you specify the full path name with the&-PFILE&option, including the drive letter of the Oracle home directory. &For example, ifyour&SID&is&ORCL, your&password&(SYSPWD)is&TWxy5791, the maximum number of users (MAXUSERS) is 10, andtheORACLE_HOME&directory is&C:\ORACLE\PRODUCT\11.2.0\DB, then enterthe following command:C:\& ORADIM-NEW -SID ORCL -SYSPWD TWxy5791 -MAXUSERS 10-STARTMODE AUTO-PFILE C:\ORACLE\PRODUCT\11.2.0\DB\DATABASE\INITORCL.ORAORADIM&writes a log file tothe&ORACLE_HOME\database&directory.&3. If your operatingsystem is Linux or UNIX, then perform the following checks:（1）Your&ORACLE_SID&is set correctly（2）The&oratab&file points toyour Oracle Database 11g&Release 2 (11.2) Oracle home（3）The following environment variablespoint to the Oracle Database 11g&Release 2 (11.2) directories:ORACLE_HOMEPATH（4）Any scripts that clients use to setthe&ORACLE_HOME&value must point to the new Oracle home.&4. Log in to the system as the owner of theOracle Database 11g&Release 2 (11.2) Oracle home directory.&5.At a system prompt, change tothe&ORACLE_HOME/rdbms/admin directory.&&&&&& --这里是新的ORACLE HOME6. Start SQL*Plus.7.Connect to the database instance as auser with&SYSDBA&privileges.8.Start the instanceby issuing the following command:SQL& STARTUPUPGRADE&Upgrade 关键字的作用：The&UPGRADE&keywordenables you to open a database based on an earlier Oracle Database release. It also restricts logons to&AS SYSDBA&sessions,disables system triggers, and performs additional operations that prepare theenvironment for the upgrade.&You might berequired to use the&PFILE&option to specify the location of your initializationparameter file.Once thedatabase is started in upgrade mode, only queries on fixed views executewithout errors until after the&catupgrd.sql&script is run. Beforerunning catupgrd.sql, queries on any other view or the use of PL/SQL returns anerror.The followingare common errors that might occur when attempting to start the new OracleDatabase 11g&Release 2 (11.2) database. Some of these errors are writtento the alert log and not to your session. If you receive any of these errors,then issue the&SHUTDOWN ABORT&command to shut down the database andcorrect the problem.&: thevalue for parameter compatible is not supported by this releaseThe&COMPATIBLE&initializationparameter is set to a value less than&10.0.0.:database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADEThe&CLUSTER_DATABASE&initializationparameter is set to&TRUE&instead of&FALSE.:database must be opened with UPGRADE optionThe&STARTUP&command was issuedwithout the&UPGRADE&keyword.: logfile size xxxx blocks is less than minimum 8192 blocksA redo log file size is less than 4 MB:&If errors appearlisting obsolete initialization parameters, then make a note of the obsoleteinitialization parameters and continue with the upgrade. Remove the obsoleteinitialization parameters the next time you shut down the database.&9. Perform this step onlyif you are upgrading from Oracle9i&Release 2 (9.2). Otherwise, skip to thenext step.Createa&SYSAUX&tablespace. In the new Oracle Database 11g&release,the&SYSAUX&tablespace is used to consolidate data from a number oftablespaces that were separate in previous releases.The&SYSAUX&tablespace must becreated with the following mandatory attributes:（1）&&&&&&&ONLINE（2）&&&&&&&PERMANENT（3）&&&&&&&READ WRITE（4）&&&&&&&EXTENT MANAGEMENT LOCAL（5）&&&&&&&SEGMENT SPACE MANAGEMENT AUTO&The Pre-UpgradeInformation Tool provides an estimate of the minimum required size forthe&SYSAUX&tablespace under the SYSAUX Tablespace section.&can be used to determine an optimal size forthe&SYSAUX&tablespace.&Table 3-4 Guidelines for Sizing the SYSAUXTablespace
Number of CPUs
concurrently active sessions
Number of user
objects (tables and indexes)
Estimated SYSAUX
size at steady state with default configuration
5 GB &The followingSQL statement would create a 500 MB&SYSAUX&tablespace for thedatabase:SQL& CREATE TABLESPACE sysaux DATAFILE'sysaux01.dbf'&&&&&&&&SIZE 500M REUSE&&&&&&&&EXTENT MANAGEMENT LOCAL&&&&&&&&SEGMENT SPACE MANAGEMENT AUTO&&&&&&&&ONLINE;&10. Set the system to spool results to alog file for later verification of success:SQL& SPOOL upgrade.log&11.Run the Pre-Upgrade Information Tool byexecuting the&utlu112i.sql&script:SQL& @?/rdbms/admin/utlu112i.sql&12. Run the&catupgrd.sql&script:SQL& @?/rdbms/admin/catupgrd.sql这个脚本才是真正的升级脚本，前面搞了那么多，就为了执行这个脚本。&还有一点要注意，就是执行这个脚本会产生大量的归档文件， 平均3分钟产生40M的归档。该脚本运行了80分钟。 而在Oracle10g下，运行该脚本在40分钟左右。时间增加了一倍。增加了升级系统的宕机时间。&Note:If you did not run the Pre-Upgrade Information Tool,the&catupgrd.sql&script terminates with one of the following errors:&: tableor view does not exist:&TZ_VERSION&: invalid identifier:invalid number &If you receiveany of these errors, issue the&SHUTDOWN ABORT&statement, revert tothe original Oracle home directory, and run the Pre-Upgrade Information Tool (utlu112i.sql)as described in&.&The&catupgrd.sql&scriptdetermines which upgrade scripts must be run, runs them, and then shuts downthe database. You must run the script in the OracleDatabase 11g&Release 2 (11.2) environment.&The upgrade script creates and alters certain data dictionary tables. It also upgrades orinstalls the following database components in the new Oracle Database11g&Release 2 (11.2) database:（1）&&&&&&&Oracle Database Catalog Views（2）&&&&&&&Oracle Database Packages andTypes（3）&&&&&&&JServer JAVA Virtual Machine（4）&&&&&&&Oracle Database Java Packages（5）&&&&&&&Oracle XDK（6）&&&&&&&Oracle Real ApplicationClusters（7）&&&&&&&Oracle Workspace Manager（8）&&&&&&&Oracle Multimedia（9）&&&&&&&Oracle XML Database（10）&&& OLAP Analytic Workspace（11）&&& Oracle OLAP API（12）&&& OLAP Catalog（13）&&& Oracle Text（14）&&& Spatial（15）&&& Oracle Data Mining（16）&&& Oracle Label Security（17）&&& Messaging Gateway（18）&&& Oracle Expression Filter（19）&&& Oracle Rules Manager（20）&&& Oracle Enterprise Manager Repository（21）&&& Oracle Database Vault（22）&&& Oracle Application Express&13. Restart the instance to reinitializethe system parameters for normal operation.SQL& STARTUP&This restart, following the database shutdown performed as part ofthe&catupgrd.sql&script, flushes all caches, clears buffers,and performs other housekeeping activities. These measures are an importantfinal step to ensure the integrity and consistency of the newly upgraded OracleDatabase software.Catupgrd.sql 脚本里会执行shutdown命令。&14.Run&utlu112s.sql,the Post-Upgrade Status Tool, which provides a summary of the upgrade at theend of the spool log. You can run&utlu112s.sql&any time aftercompleting the upgrade. See&&for more information.&Run&utlu112s.sql&todisplay the results of the upgrade as follows:SQL& @?/rdbms/admin/utlu112s.sql&If thePost-Upgrade Status Tool returns errors or shows components that arenot&VALID&or not the most recent release, then see&&for more information.示例：SQL&@?/rdbms/admin/utlu112s.sql.Oracle Database11.2 Post-Upgrade Status Tool&&&& &09-29-:19.Component&&&&&&&&&&&& &Current&&&&&Version&&&& Elapsed TimeName&&&&&&&&&&&&&&&&& &Status&&&&&&Number&&&&& HH:MM:SS.Oracle Server&&&&&&&&&&& &VALID&&&&&11.2.0.3.0& 00:44:22JServer JAVAVirtual Machine VALID&&&&& 11.2.0.3.0& 00:00:00Oracle WorkspaceManager &&& VALID&&&&& 11.2.0.3.0& 00:02:07OLAP AnalyticWorkspace &&&& VALID&&&&& 11.2.0.3.0& 00:00:00OLAP Catalog&&&&&&&&&&& &&&&&&&&&&&&& VALID&&&&& 11.2.0.3.0& 00:00:00Oracle OLAP API&&&&&&&&&&&&&&&&&&&& VALID&&&&& 11.2.0.3.0& 00:01:45OracleEnterprise Manager&&&&&& VALID&&&&& 11.2.0.3.0& 00:00:00Oracle XDK&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
VALID&&&&& 11.2.0.3.0& 00:00:00Oracle Text&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
VALID&&&&& 11.2.0.3.0& 00:00:00Oracle XMLDatabase&&&&&&&&&&&&&
VALID&&&&& 11.2.0.3.0& 00:00:00Oracle DatabaseJava Packages&&&&&& VALID&&&&& 11.2.0.3.0& 00:00:00OracleMultimedia&&&&&&&&&&&&&&&&&&&
VALID&&&&& 11.2.0.3.0& 00:10:50Spatial&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& VALID&&&&& 11.2.0.3.0& 00:17:19OracleExpression Filter&&&&&&&&&&&
VALID&&&&& 11.2.0.3.0& 00:00:48Oracle RulesManager&&&&&&&&&&&&&&
VALID&&&&& 11.2.0.3.0& 00:00:46OracleApplication Express&&&&&& VALID&&& &&&3.2.1.00.10GatheringStatistics&&&& &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& &&&&&& 00:07:08Total UpgradeTime: 01:25:42&PL/SQL proceduresuccessfully completed.&该脚本会显示升级各个组件所花的时间，这里显示我们升级组件花了85分钟。&&15. Run&catuppst.sql, located inthe&ORACLE_HOME/rdbms/admin&directory, to perform upgrade actions that do not require the database to be in UPGRADE&mode:SQL& @?/rdbms/admin/catuppst.sql&This may generate thefollowing informational messages:&Generating apply and rollback scripts...Check the following file for errors:Apply script: .*Rollback script: .*Executing script file...Updating registry...Check the following log file for errors: &Generating apply and rollback scripts...Check the following file for errors:.../cfgtoollogs/catbundle/catbundle_PSU_*.logApply script:.../rdbms/admin/catbundle_PSU_*_APPLY.sqlRollback script:.../rdbms/admin/catbundle_PSU_*_ROLLBACK.sqlExecuting script file... &Updating registry...Check the following log file for errors:.../cfgtoollogs/catbundle/catbundle_PSU_*.log&16. Run&utlrp.sql&to recompile any remaining stored PL/SQL and Java code.SQL& @?/rdbms/admin/utlrp.sql&&&&&& 该脚本运行需要花30分种。 都很费时间啊。&&Verify that all expectedpackages and classes are valid:SQL& SELECTcount(*) FROM dba_invalid_SQL& SELECTdistinct object_name FROM dba_invalid_&Note:If thepre-upgrade information tool detected&INVALID&objects and populatedthe&registry$sys_inv_objs&andregistry$nonsys_inv_objs&tables,then execute&ORACLE_HOME/rdbms/admin/utluiobj.sql&to display onlythose objects which are newly invalid because of the upgrade process.The&utluiobj.sql&script only displays objects that arenowINVALID&but which were&VALID&before the upgrade.&17. Exit SQL*Plus.&18.If you are upgrading a cluster databasefrom releases 10.2, 11.1, or 11.2.0.1, then upgrade the database configurationin Oracle Clusterware using the following command:$ srvctl upgradedatabase -d db-unique-name -o oraclehome&where&db-unique-name&isthe database name assigned to it (not the instance name),and&oraclehome&is the Oracle home location in which the database isbeing upgraded.&19. 最后调用一下dbca 重建一下OEM。&&&&&& 如果dbca 显示OEM 已经配置过，rm 掉如下2个目录，再次运行就ok了。ORACLE_HOME/hostname_dbnameORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_hostname_dbname至此，通过命令升级操作结束。&&如果在升级过程中遇到问题，可以重新执行升级脚本，步骤如下：1.Shut down the database as follows:& & SQL& SHUTDOWNIMMEDIATE2.Restart the databasein&UPGRADE&mode:& &SQL& STARTUPUPGRADE3.Set the system to spool results to a logfile for later verification of success:& &SQL& SPOOLupgrade.log4.Rerun&catupgrd.sql:& &SQL&@catupgrd.sqlNote:You can rerunthe&catupgrd.sql&script as many times as necessary. The first timeyou run the script, there should be no error messages returned. If you rerunthe script, then the&&messageis displayed. You can safely ignore this message.5.Rerun&utlu112s.sql:& &SQL&@utlu112s.sql&&最后验证一下各个组件的版本和状态：SQL& SELECT COMP_NAME, VERSION, STATUSFROM SYS.DBA_REGISTRY;&COMP_NAME&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& VERSION&&&&&&&&&&&&&&&&&&&&&&& STATUS---------------------------------------------------------------------- ----------------------OWB&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&11.2.0.1.0&&&&&&&&&&&&&&&&&&&& VALIDOracle Application Express&&&&&&&&&&&&&& 3.2.1.00.10&&&&&&&&&&&&&&&&&&& VALIDOracle Enterprise Manager&&&&&&&&&&&&&&& 11.2.0.3.0&&&&&&&&&&&&&&&&&&&& VALIDOLAP Catalog&&&&&&&&&&&&&&&& &&&&&&&&&&&&11.2.0.3.0&&&&&&&&&&&&&&&&&&&& VALIDSpatial&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&11.2.0.3.0&&&&&&&&&&&&&&&&&&&&VALIDOracle Multimedia&&&&&&&&&&&&&&&&&&&&&&& 11.2.0.3.0&&&&&&&&&&&&&&&&&&&& VALIDOracle XML Database&&&&&&&&&&&&&&&&&&&&& 11.2.0.3.0&&&&&&&&&&&&&&&&&&&& VALIDOracle Text&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 11.2.0.3.0&&&&&&&&&&&&&&&&&&&& VALIDOracle Expression Filter&&&&&&&&&&&&&&&& 11.2.0.3.0&&&&&&&&&&&&&&&&&&&& VALIDOracle Rules Manager&&&&&&&&&&&&&&&&&&&& 11.2.0.3.0&&&&&&&&&&&&&&&&&&&& VALIDOracle Workspace Manager&&&&&&&&&&&&&&&& 11.2.0.3.0&&&&&&&&&&&&&&&&&&&& VALID&COMP_NAME&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& VERSION&&&&&&&&&&&&&&&&&&&&&&& STATUS---------------------------------------------------------------------- ----------------------Oracle Database Catalog Views&&&&&&&&&&& 11.2.0.3.0&&&&&&&&&&&&&&&&&&&& VALIDOracle Database Packages and Types&&&&&& 11.2.0.3.0&&&&&&&&&&&&&&&&&&&& VALIDJServer JAVA Virtual Machine&&&&&&&&&&&& 11.2.0.3.0&&&&&&&&&&&&&&&&&&&& VALIDOracle XDK&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 11.2.0.3.0&&&&&&&&&&&&&&&&&&&& VALIDOracle Database Java Packages&&&&&&&&&&& 11.2.0.3.0&&&&&&&&&&&&&&&&&&&& VALIDOLAP Analytic Workspace&&& &&&&&&&&&&&&&&11.2.0.3.0&&&&&&&&&&&&&&&&&&&& VALIDOracle OLAP API&&&&&&&&&&&&&&&&&&&&&&&&& 11.2.0.3.0&&&&&&&&&&&&&&&&&&&& VALID&18 rows selected.&&小结：&&&&&& 我升级这个单实例使用的默认参数。 内存参数如下：&SQL& show parameter memory&NAME&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& TYPE&&&&&&& VALUE----------------------------------------------- ------------------------------hi_shared_memory_address&&&&&&&&&&&& integer&&&& 0memory_max_target&&&&&&&&&&&&&&&&&&& big integer 1000Mmemory_target&&&&&&&&&&&&&&&&&&&&&&& big integer 1000Mshared_memory_address&&&&&&&&&&&&&&& integer&&&& 0&SQL& show parameter java&NAME&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& TYPE&&&&&&& VALUE----------------------------------------------- ------------------------------java_jit_enabled&&&&&&&&&&&&&&&&&&&& boolean&&&& TRUEjava_max_sessionspace_size&&&&&&&&&& integer&&&& 0java_pool_size&&&&&&&&&&&&&&&&&&&&&& big integer 0java_soft_sessionspace_limit&&&&&&&& integer&&& &0&SQL& select * from v$&NAME&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& BYTES RES-------------------------------- -------------Fixed SGA Size&&&&&&&&&&&&&&&&&&&&& 2234960 NoRedo Buffers&&&&&&&&&&&&&&&&&&&&&&& 5656576 NoBuffer Cache Size&&&&&&&&&&&&&&&&
YesShared Pool Size&&&&&&&&&&&&&&&&&
YesLarge Pool Size&&&&&&&&&&&&&&&&&&&& 4194304 YesJava Pool Size&&& &&&&&&&&&&&&&&&&& YesStreams Pool Size&&&&&&&&&&&&&&&&&&&&&&&& 0 YesShared IO Pool Size&&&&&&&&&&&&&&&
YesGranule Size&&&&&&&&&&&&&&&&&&&&&&& 4194304 NoMaximum SGA Size&&&&&&&&&&&&&&&&
NoStartup overhead in Shared Pool&&&
No&NAME&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& BYTES RES-------------------------------- -------------Free SGA Memory Available&&&&&&&& &&在这种配置下：catupgrd.sql 跑了85分钟，utlrp.sql 跑了30分钟。然后加上备份的时间，即使采用out-of-place upgrade的方式来升级，安装Oracle database 的时间可以省掉，但总的升级时间还是较10g有增加。脚本120分钟，备份1个小时，然后冗余1-2小时，申请停机的时间至少需要4个小时，系统耗不起啊。 &不过朋友说增加Java_Pool 可以减少运行脚本的时间，这块还没有测试，有朋友测试的话可以试试这种方法。&&&&&&&-------------------------------------------------------------------------------------------------------Blog： http://blog.csdn.net/tianlesoftware Weibo: /tianlesoftwareEmail: dvd.DBA1 群：(满);&& DBA2 群：(满)&&DBA3 群：(满)&&DBA 超级群：(满);& DBA4 群：(满) DBA5群： (满)&DBA6 群：(满)&& DBA7 群：(满)&&DBA8 群：DBA 超级群2：& DBA9群：&&&& 聊天 群：(满)--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
日，CNDBA社区正式上线测试，欢迎来CNDBA社区学习交流！
积分：98712
积分：98712
排名：第6名
原创：1003篇
转载：87篇
评论：1471条
安徽DBA俱乐部，俱乐部整合安徽地区的IT资源，现有成员已经包含安徽大部分IT公司，俱乐部除了资讯信息分享之外，也会定期举行线下活动。欢迎安徽地区的DBA 加入.
，备注，加群必须注明籍贯，该群只对安徽地区开放。
注意:加群必须注明表空间和数据文件关系
不要重复加群
----------------------
CNDBA_1: k群，大量空闲)
CNDBA_2: (满)
CNDBA_5: (满)
CNDBA_7: （满）
(1)(1)(1)(4)(4)(10)(13)(6)(1)(1)(5)(1)(1)(1)(5)(6)(5)(1)(3)(5)(5)(3)(15)(21)(5)(5)(3)(21)(15)(9)(5)(26)(33)(7)(26)(35)(31)(22)(21)(34)(43)(17)(95)(51)(30)(51)(54)(41)(22)(28)(24)(24)(21)(14)(23)(24)(10)(12)(23)(29)(39)}