升级数据库的要点：

 

1.停应用，监听，实例(集群停crs） 就是停数据库的所有资源，以免影响数据库的升级，我们同事有一次由于没有停掉所有资源，导致升级失败。

 

2.备份oracle home 集群还要备份 crs home

 

3.1安装软件

3.2运行相应的脚本

3.3 升级Instance

       升级Instance 有两种方法，第一种是使用dbua工具，第二种是手工执行脚本。

 

3.3.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 "ManuallyUpgrading the Database".

（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 "Usingthe Oracle Net Configuration Assistant".

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.3.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:

Variable	Description
SID	The same SID name as the SID of the database you are upgrading.
PASSWORD	The 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. If the 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 Oracle Database Security Guide for more information.
USERS	The maximum number of users who can be granted SYSDBA and SYSOPER privileges.
ORACLE_HOME	The 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.ORA

ORADIM 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_HOME

PATH

（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 HOME

6. 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.

 

ORA-00401: thevalue for parameter compatible is not supported by this release

The COMPATIBLE initializationparameter is set to a value less than 10.0.0.

ORA-39701:database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE

The CLUSTER_DATABASE initializationparameter is set to TRUE instead of FALSE.

ORA-39700:database must be opened with UPGRADE option

The STARTUP command was issuedwithout the UPGRADE keyword.

ORA-00336: logfile size xxxx blocks is less than minimum 8192 blocks

A 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.Table3-4 can be used to determine an optimal size forthe SYSAUX tablespace.

 

Table 3-4 Guidelines for Sizing the SYSAUXTablespace

Factor	Small	Medium	Large
Number of CPUs	2	8	32
Number of concurrently active sessions	5	20	100
Number of user objects (tables and indexes)	500	5,000	50,000
Estimated SYSAUX size at steady state with default configuration	500 MB	2 GB	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:

 

ORA-00942: tableor view does not exist

ORA-00904:"TZ_VERSION": invalid identifier

ORA-01722: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 "Usingthe Pre-Upgrade Information Tool".

 

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 "Aboutthe Post-Upgrade Status Tool" 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 "Troubleshootingthe Upgrade" for more information.

示例：

SQL>@?/rdbms/admin/utlu112s.sql

.Oracle Database11.2 Post-Upgrade Status Tool      09-29-2011 12:05:19

.Component              Current     Version     Elapsed Time

Name                   Status      Number      HH:MM:SS

.Oracle Server             VALID     11.2.0.3.0  00:44:22

JServer JAVAVirtual Machine VALID      11.2.0.3.0  00:00:00

Oracle WorkspaceManager     VALID      11.2.0.3.0  00:02:07

OLAP AnalyticWorkspace      VALID      11.2.0.3.0  00:00:00

OLAP Catalog                          VALID      11.2.0.3.0  00:00:00

Oracle OLAP API                     VALID      11.2.0.3.0  00:01:45

OracleEnterprise Manager       VALID      11.2.0.3.0  00:00:00

Oracle XDK                              VALID      11.2.0.3.0  00:00:00

Oracle Text                                VALID      11.2.0.3.0  00:00:00

Oracle XMLDatabase              VALID      11.2.0.3.0  00:00:00

Oracle DatabaseJava Packages       VALID      11.2.0.3.0  00:00:00

OracleMultimedia                    VALID      11.2.0.3.0  00:10:50

Spatial                                       VALID      11.2.0.3.0  00:17:19

OracleExpression Filter            VALID      11.2.0.3.0  00:00:48

Oracle RulesManager               VALID      11.2.0.3.0  00:00:46

OracleApplication Express       VALID       3.2.1.00.10

GatheringStatistics                                                         00:07:08

Total 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_*.log

Apply script:

…/rdbms/admin/catbundle_PSU_*_APPLY.sql

Rollback script:

…/rdbms/admin/catbundle_PSU_*_ROLLBACK.sql

Executing 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_objects;

SQL> SELECTdistinct object_name FROM dba_invalid_objects;

 

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_dbname

ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_hostname_dbname

至此，通过命令升级操作结束。

 

 

如果在升级过程中遇到问题，可以重新执行升级脚本，步骤如下：

1.Shut down the database as follows:

    SQL> SHUTDOWNIMMEDIATE

2.Restart the databasein UPGRADE mode:

   SQL> STARTUPUPGRADE

3.Set the system to spool results to a logfile for later verification of success:

   SQL> SPOOLupgrade.log

4.Rerun catupgrd.sql:

   SQL>@catupgrd.sql

Note:

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 ORA-00001 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                     VALID

Oracle Application Express               3.2.1.00.10                    VALID

Oracle Enterprise Manager                11.2.0.3.0                     VALID

OLAP Catalog                             11.2.0.3.0                     VALID

Spatial                                 11.2.0.3.0                    VALID

Oracle Multimedia                        11.2.0.3.0                     VALID

Oracle XML Database                      11.2.0.3.0                     VALID

Oracle Text                              11.2.0.3.0                     VALID

Oracle Expression Filter                 11.2.0.3.0                     VALID

Oracle Rules Manager                     11.2.0.3.0                     VALID

Oracle Workspace Manager                 11.2.0.3.0                     VALID

 

COMP_NAME                                VERSION                        STATUS

———————————————————————- ———————-

Oracle Database Catalog Views            11.2.0.3.0                     VALID

Oracle Database Packages and Types       11.2.0.3.0                     VALID

JServer JAVA Virtual Machine             11.2.0.3.0                     VALID

Oracle XDK                               11.2.0.3.0                     VALID

Oracle Database Java Packages            11.2.0.3.0                     VALID

OLAP Analytic Workspace                  11.2.0.3.0                     VALID

Oracle OLAP API                          11.2.0.3.0                     VALID

 

18 rows selected.

 

 

小结：

       我升级这个单实例使用的默认参数。 内存参数如下：

 

SQL> show parameter memory

 

NAME                                 TYPE        VALUE

———————————————– ——————————

hi_shared_memory_address             integer     0

memory_max_target                    big integer 1000M

memory_target                        big integer 1000M

shared_memory_address                integer     0

 

SQL> show parameter java

 

NAME                                 TYPE        VALUE

———————————————– ——————————

java_jit_enabled                     boolean     TRUE

java_max_sessionspace_size           integer     0

java_pool_size                       big integer 0

java_soft_sessionspace_limit         integer     0

 

SQL> select * from v$sgainfo;

 

NAME                                  BYTES RES

——————————– ————-

Fixed SGA Size                      2234960 No

Redo Buffers                        5656576 No

Buffer Cache Size                 318767104 Yes

Shared Pool Size                  276824064 Yes

Large Pool Size                     4194304 Yes

Java Pool Size                     71303168 Yes

Streams Pool Size                         0 Yes

Shared IO Pool Size                33554432 Yes

Granule Size                        4194304 No

Maximum SGA Size                 1043886080 No

Startup overhead in Shared Pool    71970864 No

 

NAME                                  BYTES RES

——————————– ————-

Free SGA Memory Available         364904448

 

 

在这种配置下：catupgrd.sql 跑了85分钟，utlrp.sql 跑了30分钟。然后加上备份的时间，即使采用out-of-place upgrade的方式来升级，安装Oracle database 的时间可以省掉，但总的升级时间还是较10g有增加。

脚本120分钟，备份1个小时，然后冗余1-2小时，申请停机的时间至少需要4个小时，系统耗不起啊。

 

不过朋友说增加Java_Pool 可以减少运行脚本的时间，这块还没有测试，有朋友测试的话可以试试这种方法。