Oracle数据库的升级(10gR2至11gR2)

今天对Oracle数据库升级做了大致了解，还是很多东西值得考虑的。

首先，升级前的准备就很多工作需要做：

1. 测试和准备， 主要是应用的做压力测试。因为版本升级，优化器可能使SQL性能回退（选择性能更差的执行计划），可以结合database replay做压力测试，SPA模拟环境变化的影响，以及SPM对执行计划做固定。

2. 升级的方法， 主要是图形界面DBUA（新目录的路径）来完成，相对简单；手工升级（手工执行脚本、DBUA只不过将这些手工动作代替而已、新目录路径）；EXPDP（源库路径）/IMPDP（新库路径）等等。

其中，以下几种方法可以支持滚动升级：

可以看得出，升级的方案多样，根据不同的环境而定。

3. 升级前，务必将新库路径下$ORACLE_HOME/rdbms/admin/utlu112i.sql脚本拷至源库执行，主要是检查当前环境（如表空间大小，隐含参数，警告或建议）是否合适做升级，我们可以根据警告和建议做相应调整。

以下粘出单机数据库的升级实验步骤，供参数，由10.2.0.5升级至11.2.0.3.

时间允许的话,将做RAC的升级并更新文章

======================================================================================================

一、源库--备份

run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;
backup AS BACKUPSET full tag 'db_full' format '/backup/db_%d_%T_%s_%U.bak' database include current controlfile;
sql 'alter system archive log current';
release channel ch4;
release channel ch3;
release channel ch2;
release channel ch1;
}

SQL> archive log list;
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        /arch/ora10g
Oldest online log sequence    49
Next log sequence to archive  51
Current log sequence        51

二、源库 -- 运行脚本utlu112i.sql,做升级前检查
[oracle@tivoli02 backup]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 26 20:27:42 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> spool /tmp/upgrade_info.log
@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlu112i.sql

打印内容如下:
================
SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 01-26-2015 20:34:56
Script Version: 11.2.0.3.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name:    ORA10G
--> version:    10.2.0.5.0
--> compatible:    10.2.0.5.0
--> blocksize:    8192
--> platform:    Linux x86 64-bit
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 643 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 433 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.

--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> sql_trace    10.2    DEPRECATED
--> background_dump_dest  11.1    DEPRECATED  replaced by  "diagnostic_dest"
--> user_dump_dest  11.1    DEPRECATED  replaced by  "diagnostic_dest"
.

**********************************************************************
Components: [The following database components 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
--> EM Repository  [upgrade]  VALID
--> Oracle XML Database  [upgrade]  VALID
--> Oracle Java Packages  [upgrade]  VALID
--> Oracle interMedia  [upgrade]  VALID
--> Expression Filter  [upgrade]  VALID
--> Rule Manager  [upgrade]  VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.5.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER PUBLIC has 234 INVALID objects.
.... USER TEST has 3 INVALID objects.
.... USER SYSTEM has 2 INVALID objects.
.... USER SYSMAN has 210 INVALID objects.
.... USER SYS has 198 INVALID objects.
.... USER WMSYS has 6 INVALID objects.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
WARNING: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
 PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle bin is empty.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:

    EXECUTE dbms_stats.gather_dictionary_stats;

**********************************************************************
Oracle recommends removing all hidden parameters prior to upgrading.

To view existing hidden parameters execute the following command
while connected AS SYSDBA:

    SELECT name,description from SYS.V$PARAMETER WHERE name
 LIKE '\_%' ESCAPE '\'

Changes will need to be made in the init.ora or spfile.

**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.

To view existing non-default events execute the following commands
while connected AS SYSDBA:
  Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
      WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE'

  Trace Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
      WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'

Changes will need to be made in the init.ora or spfile.

**********************************************************************
Oracle recommends examining audit tables AUD$ and FGA_LOG$ before
upgrading the database.

This database has 0 rows in AUD$ and 23 rows in FGA_LOG$ that
will be updated during the database upgrade from 10.2.0.5.0.

During this upgrade, null DBIDs in AUD$ and FGA_LOG$ will be updated
with non-null values.

The upgrade downtime could be affected if there are many rows to update.
If downtime is a concern, the audit update could be done manually prior
to upgrading the database.

Please refer to My Oracle Support Note 1329590.1 titled "How to
Pre-Process SYS.AUD$ Records Pre-Upgrade From 10.1 or Later to 11.2".

**********************************************************************

<<<<<<<<<根据输出结果,做相对就的调整...此处省略

三、upgrading
[oracle@tivoli02 ~]$ env | grep ORA
ORACLE_SID=ora10g
ORACLE_BASE=/u01/app/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@tivoli02 ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 26-JAN-2015 20:41:52

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/tivoli02/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tivoli02)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tivoli02)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                    LISTENER
Version                  TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                26-JAN-2015 20:41:52
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level              off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File  /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File        /u01/app/oracle/diag/tnslsnr/tivoli02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tivoli02)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@tivoli02 ~]$ which lsnrctl
/u01/app/oracle/product/11.2.0/db_1/bin/lsnrctl

sqlpus / as sysdba
startup upgrade
SQL> SPOOL upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql  ---about 30 minutes
SQL> STARTUP
SQL> @?/rdbms/admin/utlu112s.sql
SQL> @?/rdbms/admin/catuppst.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> SELECT count(*) FROM dba_invalid_objects;
SQL> SELECT distinct object_name FROM dba_invalid_objects;
<<<<<<如果有失效对象，需要手工重编译