升级参考（参阅官档）：

1.决定oracle升级方法和11gORACLE_HOME新目录:使用dbua图形界面升级、ORACLE_HOME为/oracle/app/oracle11g/11.2/db_home

2.升级前制定源库的备份策略，使用dbua升级在图形界面也会有备份提示。

3.如果实际升级需进行Performance Testing for Upgrading Oracle Database性能测试，包括Database Replay、SPA、SPM.

4.升级数据库做压力测试Volume and Load Stress Testing for Upgrading Oracle Database，省略

5.注意升级前做好各种检查、准备，然后测试升级过程，升级后还需要进行测试比对升级前后数据库的各方面差异。

6.升级Oracle数据库的系统注意事项和要求：

升级到新版本的Oracle软件时，操作系统要求可能已更改。如果需要，请在升级Oracle数据库之前升级操作系统，参考： Oracle Database Installation Guide

7.安装oracle11g软件（out place of upgrade)

注意：源库和目标库必须是相同用户才可以用dbua进行安装，否则只能进行手工升级。

Oracle recommends that you run the Pre-Upgrade Information Tool before you upgrade using DBUA, so that you can preview the types of items DBUA checks. (See "Using the Pre-Upgrade Information Tool".) You can then run DBUA independently after the installation is complete.

Oracle建议您在使用DBUA升级之前运行预升级信息工具，以便您可以预览DBUA检查项的类型。（请参阅"使用预升级信息工具"。）安装完成后，可以独立运行DBUA。

8.使用升级前信息工具:Using the Pre-Upgrade Information Tool

在Oracle Database 11g第2版（11.2）和任何必需的修补程序安装软件后，Oracle建议您在将数据库升级到新版本之前分析数据库。这是通过从要升级的数据库的环境运行预升级信息工具来完成的。预升级信息工具是Oracle Database 11g第2版（11.2）软件附带的SQL脚本。如果您手动升级，这是必需的步骤; 否则，catupgrd.sql脚本会以错误的方式终止。如果要使用DBUA进行升级，还建议运行预升级信息工具，以便可以预览DBUA检查的项目。

See Also:

Note 884522.1 "How to Download and Run Oracle's Database Pre-Upgrade Utility" available from My Oracle Support at https://support.oracle.com, which contains the latest version of the Pre-Upgrade Information Tool. Oracle strongly recommends that you use the latest version of this script available in Note 884522.1.

To run the Pre-Upgrade Information Tool 运行预升级信息工具

1.Log in to the system as the owner of the environment of the database being upgraded.

重要：升级前信息工具必须复制到必须从要升级的数据库的环境中运行。

2.Start SQL*Plus.

3.Connect to the database instance as a user with SYSDBA privileges.

4.Set the system to spool results to a log file for later analysis:

SQL> SPOOL upgrade_info.log

5.Run the Pre-Upgrade Information Tool:

SQL> @$11g_ORACLE_HOME/rdbms/admin/utlu112i.sql

6.Turn off the spooling of script results to the log file:

SQL> SPOOL OFF

Check the output of the Pre-Upgrade Information Tool in upgrade_info.log.

预升级信息工具显示有关数据库可能升级问题的警告，显示了在安装Oracle Database 11g第2版软件（11.2）之后运行预升级信息工具生成的报告除了工具显示的警告外，您必须解决在执行升级之前升级前信息工具输出中描述的任何错误。

For invalid objects or invalid components, Oracle recommends running the the utlrp.sql before starting the upgrade as a means to minimize the number of invalid objects and components marked with WARNING.

对于无效对象或无效组件，Oracle建议您在开始升级之前运行utlrp.sql，以尽量减少标有"WARNING"的无效对象和组件的数量。

9.升级前后查询数据字典状态：

您可以通过运行dbupgdiag.sql脚本来收集有关数据字典的当前状态的升级和迁移诊断信息。该脚本可以在源数据库升级之前和升级后的数据库上以SYS用户身份升级后在SQL * Plus中运行。

To show the current state of the dictionary, execute a SQL query similar to the following example:

SQL> spool /tmp/regInvalid.out

SQL> set echo on

-- query registry

SQL> set lines 80 pages 100

SQL> select substr(comp_id,1,15) comp_id,substr(comp_name,1,30)

comp_name,substr(version,1,10) version,status

from dba_registry order by modified;

To query invalid objects, execute a SQL query similar to:

SQL> select substr(owner,1,12) owner,substr(object_name,1,30) object,substr(object_type,1,30) type, status

from dba_objects where status <> 'VALID'order by owner, type;

SQL> spool off

SQL> set echo off

10.Verifying That Materialized View Refreshes Have Completed确认物化视图已经更新完成

To determine if there are any materialized view refreshes still in progress

Run the following query：

SQL> SELECT * FROM sys.obj$ o, sys.user$ u, sys.sum$ s

WHERE o.type# = 42 AND bitand(s.mflags, 8) = 8;

11.Ensuring That No Files Need Media Recovery确认没有介质恢复

To get a list of files that require media recovery

Issue the following statement:

SQL> SELECT * FROM v$recover_file;

12.Ensuring That No Files Are in Backup Mode确认没有备份在进行

To get a list of files in backup mode

Issue the following statement:

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

13.Synchronizing a Standby Database with the Primary Database同步主备库

省略................................

14.Purging the Database Recycle Bin清空回收站

To empty the database recycle bin

Issue the following command:

SQL> PURGE dba_recyclebin

15.启用DBUA升级数据库

先临时设置ORACLE_HOME和PATH环境变量到你想要升级的版本的对应位置：

如果数据库实例未运行，DBUA将尝试使用默认的初始化参数文件启动实例。如果失败，DBUA会提示您提供正确的初始化参数文件的名称或启动实例。如果实例已启动并运行，则DBUA将连接到该实例；

如果终止升级，但不恢复数据库，则无法重新启动DBUA。相反，您必须按照手动升级Oracle数据库中的说明继续执行手动（命令行）升级；

如果手动恢复数据库（不使用DBUA），则在启动DBUA之前，请删除位于ORACLE_HOME/cfgtoollogs/dbua/logs/目录中的Welcome_SID.txt文件。该文件的存在表明DBUA这是一个重新运行的操作。

您安装了Oracle Database 11g第2版（11.2）并指定您正在升级现有数据库，然后DBUA自动启动。但是，如果没有指定要升级现有数据库，则可以在安装完成后独立启动DBUA。

升级过程中，DBUA显示每个组件的升级进度。 DBUA写入详细的跟踪和日志文件，并生成一个完整的HTML报告供以后参考。为了增强安全性，DBUA会自动锁定升级后的数据库中的新用户帐户。然后，DBUA继续在新的Oracle主目录中创建新的配置文件（参数和侦听器文件）。在所有升级前的步骤完成之前，DBUA才会开始升级。

注意：从安装了新数据库软件的OracleORACLE_HOME目录启动DBUA

16.升级后更新任务

16.1升级前后查询数据字典状态：

您可以通过运行dbupgdiag.sql脚本来收集有关数据字典的当前状态的升级和迁移诊断信息。该脚本可以在源数据库升级之前和升级后的数据库上以SYS用户身份升级后在SQL * Plus中运行。

To show the current state of the dictionary, execute a SQL query similar to the following example:

SQL> spool /tmp/regInvalid.out

SQL> set echo on

-- query registry

SQL> set lines 80 pages 100

SQL> select substr(comp_id,1,15) comp_id,substr(comp_name,1,30)

comp_name,substr(version,1,10) version,status

from dba_registry order by modified;

To query invalid objects, execute a SQL query similar to:

SQL> select substr(owner,1,12) owner,substr(object_name,1,30) object,substr(object_type,1,30) type, status

from dba_objects where status <> 'VALID'order by owner, type;

SQL> spool off

SQL> set echo off

检查发现有失效对象重新编译：

[oracle@rhel bin]$ find /oracle/app/oracle11g/11.2/db_home -name utlrp*

/oracle/app/oracle11g/11.2/db_home/rdbms/admin/utlrp.sql

[oracle@rhel bin]$

SQL> @/oracle/app/oracle11g/11.2/db_home/rdbms/admin/utlrp.sql

省略.............................

16.2升级后运行opatch命令

16.3指定oracle11g ORACLE_HOME和PATH环境变量

手动升级需指定，dbua也需要执行执行自动转换。

16.4 After you upgrade Oracle Database to the new release, you must ensure that your oratab file and any client scripts that set the value of ORACLE_HOME point to the new Oracle home that is created for the new Oracle Database 11g release. Although DBUA automatically points oratab to the new Oracle home, client scripts must be checked no matter which method you use to upgrade.

将Oracle数据库升级到新版本后，必须确保您的oratab文件和设置ORACLE_HOME值的任何客户端脚本指向为新Oracle Database 11g版本创建的新Oracle主目录。尽管DBUA自动将oratab指向新的Oracle主目录，但无论使用哪种方法进行升级，都必须检查客户端脚本。

16.5如果预升级信息工具指示您在完成数据库升级后升级时区文件，则使用DBMS_DST PL/SQL软件包升级时区文件。

16.6升级Oracle数据库后升级由DBMS_STATS软件包创建的统计表

如果您使用DBMS_STATS.CREATE_STAT_TABLE过程创建统计信息表，请通过运行以下过程来升级这些表：

EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE（'scott'，'stat_table'）;

环境准备：

一、磁盘空间不足，用以安装oracle11g软件（重启操作系统，使 Linux 系统识别刚刚添加的磁盘）

[root@rhel ~]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda3 25G 10G 14G 43% /

/dev/sda1 190M 12M 169M 7% /boot

tmpfs 1006M 0 1006M 0% /dev/shm

[root@rhel ~]#

--添加后检查：

[root@rhel ~]# fdisk -l

Disk /dev/sda: 32.2 GB, 32212254720 bytes

255 heads, 63 sectors/track, 3916 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sda1 * 1 25 200781 83 Linux

/dev/sda2 26 547 4192965 82 Linux swap / Solaris

/dev/sda3 548 3916 27061492+ 83 Linux

Disk /dev/sdb: 32.2 GB, 32212254720 bytes

255 heads, 63 sectors/track, 3916 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdb doesn't contain a valid partition table

[root@rhel ~]# fdisk

对新添加磁盘进行分区：

[root@rhel ~]# fdisk /dev/sdb

Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel

Building a new DOS disklabel. Changes will remain in memory only,

until you decide to write them. After that, of course, the previous

content won't be recoverable.

The number of cylinders for this disk is set to 3916.

There is nothing wrong with that, but this is larger than 1024,

and could in certain setups cause problems with:

1) software that runs at boot time (e.g., old versions of LILO)

2) booting and partitioning software from other OSs

(e.g., DOS FDISK, OS/2 FDISK)

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): m

Command action

a toggle a bootable flag

b edit bsd disklabel

c toggle the dos compatibility flag

d delete a partition

l list known partition types

m print this menu

n add a new partition

o create a new empty DOS partition table

p print the partition table

q quit without saving changes

s create a new empty Sun disklabel

t change a partition's system id

u change display/entry units

v verify the partition table

w write table to disk and exit

x extra functionality (experts only)

Command (m for help): n

Command action

e extended

p primary partition (1-4)

p

Partition number (1-4): 1

First cylinder (1-3916, default 1): 1

Last cylinder or +size or +sizeM or +sizeK (1-3916, default 3916):

Using default value 3916

Command (m for help): w

The partition table has been altered!

Calling ioctl() to re-read partition table.

Syncing disks.

[root@rhel ~]#

查看已分区的磁盘：

[root@rhel ~]# fdisk -l

Disk /dev/sda: 32.2 GB, 32212254720 bytes

255 heads, 63 sectors/track, 3916 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sda1 * 1 25 200781 83 Linux

/dev/sda2 26 547 4192965 82 Linux swap / Solaris

/dev/sda3 548 3916 27061492+ 83 Linux

Disk /dev/sdb: 32.2 GB, 32212254720 bytes

255 heads, 63 sectors/track, 3916 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sdb1 1 3916 31455238+ 83 Linux

[root@rhel ~]#

格式化已分区的磁盘：将/dev/sdb1格式化为文件系统

[root@rhel ~]# mkfs -t ext3 /dev/sdb1

mke2fs 1.39 (29-May-2006)

Filesystem label=

OS type: Linux

Block size=4096 (log=2)

Fragment size=4096 (log=2)

3932160 inodes, 7863809 blocks

393190 blocks (5.00%) reserved for the super user

First data block=0

Maximum filesystem blocks=4294967296

240 block groups

32768 blocks per group, 32768 fragments per group

16384 inodes per group

Superblock backups stored on blocks:

32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,

4096000

Writing inode tables: done

Creating journal (32768 blocks): done

Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 39 mounts or

180 days, whichever comes first. Use tune2fs -c or -i to override.

[root@rhel ~]#

挂载文件系统：先创建挂载点

[root@rhel ~]# mkdir /oracle

[root@rhel ~]#

最后再挂载磁盘

[root@rhel /]# mount /dev/sdb1 /oracle

验证：

[root@rhel /]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda3 25G 10G 14G 43% /

/dev/sda1 190M 12M 169M 7% /boot

tmpfs 1006M 0 1006M 0% /dev/shm

/dev/sdb1 30G 173M 28G 1% /oracle

[root@rhel /]#

--设置自动挂载/dev/sdb1设备：
[root@rhel ~]# vi /etc/fstab
LABEL=/                 /                       ext3    defaults        1 1
LABEL=/boot             /boot                   ext3    defaults        1 2
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts gid=5,mode=620 0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
LABEL=SWAP-sda2         swap                    swap    defaults        0 0
/dev/sdb1              /oracle                  ext3    defaults        0 0
~
"/etc/fstab" 8L, 608C written
[root@rhel ~]#

磁盘加载成功！！！

二、建立oracle11g数据库软件的ORACLE_HOME目录，然后上传oracle11g的Patchsets

[root@rhel /]# mkdir -p /oracle/app/oracle11g/11.2/db_home

[root@rhel ~]# chown oracle:oinstall -R /oracle

[root@rhel ~]# chmod 755 -R /oracle

上传；

[oracle@rhel ~]$ cd /oracle

[oracle@rhel oracle]$ mkdir soft

[oracle@rhel soft]$

[oracle@rhel soft]$ ls

dbupgdiag.sql p13390677_112040_Linux_x86_64_1of7.zip p13390677_112040_Linux_x86_64_2of7.zip utlu112i_11204_009.sql

[oracle@rhel soft]$

[oracle@rhel soft]$ unzip p13390677_112040_Linux_x86_64_1of7.zip

[oracle@rhel soft]$ unzip p13390677_112040_Linux_x86_64_2of7.zip

升级步骤；

安装oracle11g数据库软件：使用xshell启图形

[root@rhel oracle11g]# /tmp/CVU_11.2.0.4.0_oracle/runfixup.sh

Response file being used is :/tmp/CVU_11.2.0.4.0_oracle/fixup.response

Enable file being used is :/tmp/CVU_11.2.0.4.0_oracle/fixup.enable

Log file location: /tmp/CVU_11.2.0.4.0_oracle/orarun.log

Setting Kernel Parameters...

net.ipv4.ip_local_port_range = 9000 65500

[root@rhel oracle11g]#

[root@rhel oracle11g]# /oracle/app/oracle11g/11.2/db_home/root.sh

Performing root user operation for Oracle 11g

The following environment variables are set as:

ORACLE_OWNER= oracle

ORACLE_HOME= /oracle/app/oracle11g/11.2/db_home

Enter the full pathname of the local bin directory: [/usr/local/bin]:

The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)

[n]:

The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)

[n]:

The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)

[n]:

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Finished product-specific root actions.

[root@rhel oracle11g]#

--运行预升级信息工具（即utlu112i.sql）

[oracle@rhel ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Oct 24 06:32:16 2017

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 upgrade_info.log

SQL> @/oracle/soft/utlu112i_11204_009.sql

Oracle Database 11.2 Pre-Upgrade Information Tool 10-24-2017 06:34:50

Script Version: 11.2.0.4.0 Build: 009

.

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

Database:

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

--> name: ORCL

--> version: 10.2.0.5.0

--> compatible: 10.2.0.1.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: 1005 MB

--> UNDOTBS1 tablespace is adequate for the upgrade.

.... minimum required size: 400 MB

--> SYSAUX tablespace is adequate for the upgrade.

.... minimum required size: 735 MB

--> TEMP tablespace is adequate for the upgrade.

.... minimum required size: 60 MB

--> EXAMPLE tablespace is adequate for the upgrade.

.... minimum required size: 69 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:

WARNING: --> "sga_target" needs to be increased to at least 596 MB

.

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

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]

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

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

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

--> Data Mining [upgrade] VALID

--> Expression Filter [upgrade] VALID

--> Rule Manager [upgrade] VALID

--> Oracle OLAP API [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: --> 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;

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

SQL>

SQL> SPOOL OFF

SQL>

根据upgrade_info.log内容整改：

SQL> show parameter sga

lock_sga boolean FALSE

pre_page_sga boolean FALSE

sga_max_size big integer 572M

sga_target big integer 572M

SQL>

SQL> alter system set sga_target=600M SCOPE=SPFILE;

SQL> alter system set sga_max_size=600M SCOPE=SPFILE;

--检查确认物化视图已经更新完成

SQL> SELECT * FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) = 8;

no rows selected

--检查确认没有文件需要回复

SQL> SELECT * FROM v$recover_file;

no rows selected

SQL>

---确认没有备份在进行：

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

no rows selected

--清空回收站：

SQL> PURGE dba_recyclebin;

DBA Recyclebin purged.

--升级前后查询数据字典状态：

[oracle@rhel soft]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Oct 24 06:59:41 2017

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> @/oracle/soft/dbupgdiag.sql

省略…………..

*** End of LogFile ***

not spooling currently

Upload db_upg_diag_orcl_24_Oct_2017_0700.log from "dbupgdiag.sql" direc

SQL>

SQL> spool /tmp/regInvalid.out

SQL> set echo on

SQL> set lines 80 pages 100

SQL> select substr(comp_id,1,15) comp_id,substr(comp_name,1,30)

2 comp_name,substr(version,1,10) version,status

3 from dba_registry order by modified;

COMP_ID COMP_NAME VERSION STATUS

--------------- ------------------------------ ---------- -----------

EM Oracle Enterprise Manager 10.2.0.5.0 VALID

CATPROC Oracle Database Packages and T 10.2.0.5.0 VALID

CATALOG Oracle Database Catalog Views 10.2.0.5.0 VALID

XML Oracle XDK 10.2.0.5.0 VALID

ODM Oracle Data Mining 10.2.0.5.0 VALID

CONTEXT Oracle Text 10.2.0.5.0 VALID

EXF Oracle Expression Filter 10.2.0.5.0 VALID

OWM Oracle Workspace Manager 10.2.0.5.0 VALID

JAVAVM JServer JAVA Virtual Machine 10.2.0.5.0 VALID

CATJAVA Oracle Database Java Packages 10.2.0.5.0 VALID

RUL Oracle Rule Manager 10.2.0.5.0 VALID

XDB Oracle XML Database 10.2.0.5.0 VALID

ORDIM Oracle interMedia 10.2.0.5.0 VALID

APS OLAP Analytic Workspace 10.2.0.5.0 VALID

XOQ Oracle OLAP API 10.2.0.5.0 VALID

SDO Spatial 10.2.0.5.0 VALID

AMD OLAP Catalog 10.2.0.5.0 VALID

17 rows selected.

SQL> select substr(owner,1,12) owner,substr(object_name,1,30) object,substr(object_type,1,30) type, status

2 from dba_objects where status <> 'VALID'order by owner, type;

no rows selected

SQL> spool off

SQL> set echo off

SQL>

--启用dbua进行升级:使用xshell启图形

先临时设置ORACLE_HOME和PATH环境变量到你想要升级的版本的对应位置

根据报错，整改：

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

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

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

[oracle@rhel soft]$ export PATH=/oracle/app/oracle11g/11.2/db_home/bin:$PATH

[oracle@rhel soft]$ mkdir -p /oracle/location

[oracle@rhel soft]$

[oracle@rhel soft]$ emdwgrd -save -sid orcl -path /oracle/location/

Enter sys password for database orcl?

Database Unique Name : orcl

Tue Oct 24 08:12:41 2017 - Validating DB Connection to orcl ... pass

Tue Oct 24 08:12:41 2017 - Verify EM DB Control files ... pass

ENV var EM_REMCP not defined, check if rcp or scp is configured.

RCP = /usr/bin/rcp -rp, REMSH = /usr/bin/rsh

shared = 0

Tue Oct 24 08:12:41 2017 - Creating directory ... created

Tue Oct 24 08:12:42 2017 - Stopping DB Control ... stopped

Tue Oct 24 08:12:43 2017 - Saving DB Control files

... saved

Tue Oct 24 08:13:00 2017 - Recompiling invalid objects

... recompiled

Tue Oct 24 08:13:15 2017 - Exporting sysman schema for orcl

... exported

Tue Oct 24 08:14:25 2017 - DB Control was saved successfully.

Tue Oct 24 08:14:25 2017 - Starting DB Control

... started

Tue Oct 24 08:15:59 2017 - Dump directory was dropped successfully.

[oracle@rhel soft]$

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

Upgrade DetailsThe following is a summary of the steps performed during the database upgrade. Log files for all the steps, as well as this summary, are available at "/oracle/app/oracle11g/cfgtoollogs/dbua/orcl/upgrade3".

The Database can be restored by running the following script "/oracle/app/oracle11g/admin/orcl/backup/orcl_restore.sh" from the backup location.NOTE: This operation will remove all the upgrade changes made by the Database Upgrade Assistant.

Post UpgradeA persistent initialization parameter file (spfile) has been created at the following location: /oracle/app/oracle11g/11.2/db_home/dbs/spfileorcl.ora.

DBUA升级完成！！！！！！

验证：

--更改oracle用户参数文件：

[oracle@rhel ~]$ vi .bash_profile

#ORACLE_HOME=$ORACLE_BASE/product/10.2.0.1/db_1; export ORACLE_HOME

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

TMP=/tmp; export TMP

TMPDIR=$TMP; export TMPDIR

#ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

#ORACLE_HOME=$ORACLE_BASE/product/10.2.0.1/db_1; export ORACLE_HOME

ORACLE_BASE=/oracle/app/oracle11g

ORACLE_HOME=$ORACLE_BASE/11.2/db_home; export ORACLE_HOME

ORACLE_SID=orcl; export ORACLE_SID

ORACLE_TERM=xterm; export ORACLE_TERM

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

".bash_profile" 24L, 759C written

[oracle@rhel ~]$

[oracle@rhel ~]$ source .bash_profile

[oracle@rhel ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 24 10:01:01 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

---升级后做更新任务

[oracle@rhel soft]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 24 10:06:04 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> !pwd

/oracle/soft

SQL> !ls

database dbupgdiag.sql p13390677_112040_Linux_x86_64_1of7.zip p13390677_112040_Linux_x86_64_2of7.zip sqlnet.log utlu112i_11204_009.sql

SQL> @dbupgdiag.sql

Enter location for Spooled output:

Enter value for 1: /oracle/soft

24_Oct_2017_1011 .log

orcl_

*** Start of LogFile ***

*** End of LogFile ***

Upload db_upg_diag_orcl_24_Oct_2017_1011.log from "/oracle/soft" directory

SQL>

SQL> select substr(owner,1,12) owner,substr(object_name,1,30) object,substr(object_type,1,30) type, status

2 from dba_objects where status <> 'VALID'order by owner, type;

OWNER OBJECT TYPE STATUS

------------ ------------------------------ ------------------- -------

SH FWEEK_PSCAT_SALES_MV MATERIALIZED VIEW INVALID

SQL> alter materialized view SH.FWEEK_PSCAT_SALES_MV compile;

Materialized view altered.

SQL> select substr(owner,1,12) owner,substr(object_name,1,30) object,substr(object_type,1,30) type, status from dba_objects where status <> 'VALID'order by owner, type;

no rows selected

SQL>

--检查opatch工具升级情况：

[oracle@rhel soft]$ opatch lsinventory

Oracle Interim Patch Installer version 11.2.0.3.4

Oracle Home : /oracle/app/oracle11g/11.2/db_home

Central Inventory : /u01/app/oracle/oraInventory

from : /oracle/app/oracle11g/11.2/db_home/oraInst.loc

OPatch version : 11.2.0.3.4

OUI version : 11.2.0.4.0

Log file location : /oracle/app/oracle11g/11.2/db_home/cfgtoollogs/opatch/opatch2017-10-24_10-23-49AM_1.log

Lsinventory Output file location : /oracle/app/oracle11g/11.2/db_home/cfgtoollogs/opatch/lsinv/lsinventory2017-10-24_10-23-49AM.txt

--------------------------------------------------------------------------------

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.4.0

There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

--------------------------------------------------------------------------------

OPatch succeeded.

[oracle@rhel soft]$

--检查/etc/oratab文件

[oracle@rhel soft]$ cat /etc/oratab

#

# This file is used by ORACLE utilities. It is created by root.sh

# and updated by the Database Configuration Assistant when creating

# a database.

# A colon, ':', is used as the field terminator. A new line terminates

# the entry. Lines beginning with a pound sign, '#', are comments.

#

# Entries are of the form:

# $ORACLE_SID:$ORACLE_HOME::

#

# The first and second fields are the system identifier and home

# directory of the database respectively. The third filed indicates

# to the dbstart utility that the database should , "Y", or should not,

# "N", be brought up at system boot time.

#

# Multiple entries with the same $ORACLE_SID are not allowed.

#

orcl:/oracle/app/oracle11g/11.2/db_home:N

[oracle@rhel soft]$

完成！！！！！！！！！！！！！！！！

（同机）10G升级11G：10.2.0.5升到11.2.0.4