参考文档：
Interoperability Notes EBS 12.0 and 12.1 with Database 11gR2 (文档 ID 1058763.1)
升级前准备：
1、查看数据库和操作系统相关信息：
[root@uat ~]# lsb_release -a
LSB Version: :core-4.1-ia32:core-4.1-noarch:printing-4.1-ia32:printing-4.1-noarch
Distributor ID: RedHatEnterpriseServer
Description: Red Hat Enterprise Linux Server release 7.0 (Maipo)
Release: 7.0
Codename: Maipo
[root@uat ~]# su - oracle
Last login: Thu Jul 6 14:50:54 CST 2017 on pts/0
[oracle@uat ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 6 16:44:31 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
------------------
ORCL
SQL> exit;
2、给数据库做全备份：
[oracle@uat ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jul 6 16:44:58 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1476421486)
RMAN> run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
backup full tag 'dbfull' format '/u01/backup/full_%u_%s_%p.dbf' database include current controlfile;
backup format '/u01/backup/arch%u_%s_%p' archivelog all delete input;
release channel c1;
release channel c2;
release channel c3;
delete obsolete;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=43 device type=DISK
allocated channel: c2
channel c2: SID=39 device type=DISK
allocated channel: c3
channel c3: SID=40 device type=DISK
Starting backup at 06-JUL-17
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/oracle/oradata/orcl/system01.dbf
input datafile file number=00004 name=/u01/oracle/oradata/orcl/users01.dbf
channel c1: starting piece 1 at 06-JUL-17
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00003 name=/u01/oracle/oradata/orcl/undotbs01.dbf
channel c2: starting piece 1 at 06-JUL-17
channel c3: starting full datafile backup set
channel c3: specifying datafile(s) in backup set
including current control file in backup set
channel c3: starting piece 1 at 06-JUL-17
channel c3: finished piece 1 at 06-JUL-17
piece handle=/u01/backup/full_03s8mc5c_3_1.dbf tag=DBFULL comment=NONE
channel c3: backup set complete, elapsed time: 00:00:23
channel c3: starting full datafile backup set
channel c3: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c3: starting piece 1 at 06-JUL-17
channel c3: finished piece 1 at 06-JUL-17
piece handle=/u01/backup/full_04s8mc6s_4_1.dbf tag=DBFULL comment=NONE
channel c3: backup set complete, elapsed time: 00:00:16
channel c2: finished piece 1 at 06-JUL-17
piece handle=/u01/backup/full_02s8mc5c_2_1.dbf tag=DBFULL comment=NONE
channel c2: backup set complete, elapsed time: 00:02:41
channel c1: finished piece 1 at 06-JUL-17
piece handle=/u01/backup/full_01s8mc5b_1_1.dbf tag=DBFULL comment=NONE
channel c1: backup set complete, elapsed time: 00:03:24
Finished backup at 06-JUL-17
Starting backup at 06-JUL-17
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=948641299
channel c1: starting piece 1 at 06-JUL-17
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=2 STAMP=948646274
channel c2: starting piece 1 at 06-JUL-17
channel c1: finished piece 1 at 06-JUL-17
piece handle=/u01/backup/arch05s8mcc3_5_1 tag=TAG20170706T165114 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:07
channel c1: deleting archived log(s)
archived log file name=/u01/oracle/fast_recovery_area/ORCL/archivelog/2017_07_06/o1_mf_1_5_dovsnlyy_.arc RECID=1 STAMP=948641299
channel c2: finished piece 1 at 06-JUL-17
piece handle=/u01/backup/arch06s8mcc4_6_1 tag=TAG20170706T165114 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:07
channel c2: deleting archived log(s)
archived log file name=/u01/oracle/fast_recovery_area/ORCL/archivelog/2017_07_06/o1_mf_1_6_dovyj1jn_.arc RECID=2 STAMP=948646274
Finished backup at 06-JUL-17
released channel: c1
released channel: c2
released channel: c3
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
no obsolete backups found
RMAN>
3、停止数据库
[oracle@uat ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 6 16:56:11 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
停止监听
[oracle@uat ~]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 06-JUL-2017 16:59:51
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
[oracle@uat ~]$
停止EM
SQL> host emctl stopdbconsole;
4、备份旧版本的ORACLE_HOME和oraInventory
[root@uat oracle]# tar -cvf product.zip product/
[root@uat oracle]# tar -cvf oraInventory.zip oraInventory/
5、上传Oracle database 11.2.0.4安装介质
上传至/home下
chown -R oracle:oinstall database/
chmod -R 700 database/
6、安装Oracle database 11.2.0.4
本次安装采用静默安装：
1、创建安装目录
mkdir -p /u02
chown -R oracle:oinstall /u02
chmod -R 775 /u02
2、修改/etc/oraInst.loc文件，内容如下：
inventory_loc=/u02/oracle/oraInventory
inst_group=oinstall
更改文件的权限
chown oracle:oinstall /etc/oraInst.loc
chmod 664 /etc/oraInst.loc
3、复制相应的文件模板
mkdir rsp
cp /home/database/response/* /home/oracle/rsp/
设置响应文件权限
su - root
chmod 700 /home/oracle/rsp/*.rsp
chown -R oracle:oinstall /home/oracle/rsp/
4、修改安装Oracle软件的响应文件/home/rsp/db_install.rsp
vi /home/rsp/db_install.rsp
oracle.install.option=INSTALL_DB_SWONLY //29 行 安装类型
ORACLE_HOSTNAME=uat //37 行 主机名称
UNIX_GROUP_NAME=oinstall //42 行 安装组
INVENTORY_LOCATION=/u02/oracle/oraInventory //42行 INVENTORY目录
SELECTED_LANGUAGES=en,zh_CN,zh_TW //86行 选择语言
ORACLE_HOME=/u02/oracle/product/11.2.0/db_1 //91 行 oracle_home
ORACLE_BASE=/u02/oracle //96 行 oracle_base
oracle.install.db.InstallEdition=EE //107 行 oracle版本
oracle.install.db.isCustomInstall=true //108行 自定义安装
oracle.install.db.DBA_GROUP=dba //154行 dba用户组
oracle.install.db.OPER_GROUP=oinstall //160行 oper用户组
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE //189行 数据库类型
oracle.install.db.config.starterdb.globalDBName=orcl //194行 globalDBName
oracle.install.db.config.starterdb.SID=orcl //199行 SID
oracle.install.db.config.starterdb.characterSet=ZHS16GBK//213行字符集
oracle.install.db.config.starterdb.memoryLimit=512 //229行 自动管理内存的最小内存(M)
oracle.install.db.config.starterdb.password.ALL=oracle //262行 设定所有数据库用户使用同一个密码
DECLINE_SECURITY_UPDATES=true //400行 设置安全更新
进行静默安装Oracle软件
cd /home/database
./runInstaller -silent -force -responseFile /home/oracle/rsp/db_install.rsp
安装中，如果提示[WARNING]不必理会，此时安装程序仍在进行，如果出现[FATAL]，则安装程序已经停止了。
查看安装日志信息了解安装进度.
[oracle@uat database]$ ./runInstaller -silent -force -responseFile /home/oracle/rsp/db_install.rsp
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 13293 MB Passed
Checking swap space: must be greater than 150 MB. Actual 4044 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-07-06_05-41-53PM. Please wait ...[oracle@uat database]$ [WARNING] [INS-13014] Target environment do not meet some optional requirements.
CAUSE: Some of the optional prerequisites are not met. See logs for details. null
ACTION: Identify the list of failed prerequisite checks from the log: null. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
You can find the log of this install session at:
/u02/oracle/oraInventory /logs/installActions2017-07-06_05-41-53PM.log
[oracle@uat database]$ The installation of Oracle Database 11g was successful.
Please check '/u02/oracle/oraInventory /logs/silentInstall2017-07-06_05-41-53PM.log' for more details.
As a root user, execute the following script(s):
1. /u02/oracle/product/11.2.0/db_1/root.sh
Successfully Setup Software.
[oracle@uat database]$
5、执行 /u02/oracle/product/11.2.0/db_1/root.sh
[root@uat ~]# /u02/oracle/product/11.2.0/db_1/root.sh
Check /u02/oracle/product/11.2.0/db_1/install/root_uat.oracle.com_2017-07-06_17-54-40.log for the output of root script
[root@uat ~]#
6、修改 .bash_profile文件中的$ORACLE_HOME
[oracle@uat ~]$ vim .bash_profile
将$ORACLE_BASE路径修改为新安装的数据库路径
export ORACLE_BASE=/u02/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=orcl
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG=AMERICAN_AMERICA.zhs16gbk
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
使设置生效
[oracle@uat ~]$ source .bash_profile
[oracle@uat ~]$ cd $ORACLE_HOME
[oracle@uat db_1]$ pwd
/u02/oracle/product/11.2.0/db_1
[oracle@uat db_1]$
7、配置监听
因为是数据库升级因此不需要进行重新配置监听
将旧版本的监听文件拷贝一份即可
[oracle@uat ~]$ cp -r /u01/oracle/product/11.2.0/db_1/network/admin/* $ORACLE_HOME/network/admin
[oracle@uat ~]$ cd $ORACLE_HOME/network/admin
[oracle@uat admin]$ ls
listener.ora samples shrept.lst sqlnet.ora tnsnames.ora
8、执行预升级脚本检查
[oracle@uat ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 7 11:18:47 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u02/oracle/product/11.2.0/db_1/dbs/initorcl.ora'
SQL> exit;
Disconnected
[oracle@uat ~]$ cd /u02/oracle/product/11.2.0/db_1/dbs/
[oracle@uat dbs]$ ls
init.ora
拷贝一份pfile文件并重命名为initorcl.ora，再次打开数据库：
[oracle@uat dbs]$ cp init.ora initorcl.ora
[oracle@uat dbs]$ ls
init.ora initorcl.ora
[oracle@uat ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 7 11:21:22 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade;
ORA-48108: invalid value given for the diagnostic_dest init.ora parameter
ORA-48140: the specified ADR Base directory does not exist [/u02/oracle/product/11.2.0/db_1/dbs/<ORACLE_BASE>]
ORA-48187: specified directory does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
SQL> exit;
Disconnected
再次报错：
将旧版本文件下的dbs路径下的文件全部拷贝过来：
[oracle@uat ~]$ cd /u01/oracle/product/11.2.0/db_1/dbs
[oracle@uat dbs]$ ls
hc_orcl.dat init.ora lkORCL orapworcl spfileorcl.ora
[oracle@uat dbs]$ cp /u01/oracle/product/11.2.0/db_1/dbs/* /u02/oracle/product/11.2.0/db_1/dbs
[oracle@uat dbs]$ cd
再次连接数据库
[oracle@uat ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 7 11:26:00 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 536874104 bytes
Database Buffers 289406976 bytes
Redo Buffers 2392064 bytes
Database mounted.
Database opened.
SQL>
可以成功启动；
执行升级前检查：
SQL> @?/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 07-07-2017 11:33:03
Script Version: 11.2.0.4.0 Build: 001
**********************************************************************
Database:
**********************************************************************
--> name: ORCL
--> version: 11.2.0.3.0
--> compatible: 11.2.0.0.0
--> blocksize: 8192
--> platform: Linux x86 64-bit
--> timezone file: V14
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 917 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 620 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
**********************************************************************
Flashback: ON
**********************************************************************
FlashbackInfo:
--> name: /u01/oracle/fast_recovery_area
--> limit: 4122 MB
--> used: 105 MB
--> size: 4122 MB
--> reclaim: 0 MB
--> files: 4
WARNING: --> Flashback Recovery Area Set. Please ensure adequate disk space in recovery areas before performing an upgrade.
**********************************************************************
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]
**********************************************************************
-- No obsolete parameters found. No changes are required.
**********************************************************************
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
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle Application Express [upgrade] VALID
... APEX will only be upgraded if the version of APEX in
... the target Oracle home is higher than the current one.
--> Oracle OLAP API [upgrade] VALID
**********************************************************************
Miscellaneous Warnings
**********************************************************************
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.
WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER APEX_030200 has dependent objects.
**********************************************************************
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.
**********************************************************************
SQL>
修改不满足项：
SQL> show parameter shared
hi_shared_memory_address integer 0
max_shared_servers integer
shared_memory_address integer 0
shared_pool_reserved_size big integer 6M
shared_pool_size big integer 0
shared_server_sessions integer
shared_servers integer 1
SQL> show parameter sga
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 796M
sga_target big integer 0
SQL> show parameter memory
hi_shared_memory_address integer 0
memory_max_target big integer 796M
memory_target big integer 796M
shared_memory_address integer 0
SQL> show parameter pga
pga_aggregate_target big integer 0
由于设置了设置了sga_max_size、和memory_max_target的最大大小，因此不需要修改。
如果有需改的地方，修改之后，记得再次进行检查。
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 536874104 bytes
Database Buffers 289406976 bytes
Redo Buffers 2392064 bytes
Database mounted.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38713: Flashback Database logging is already turned on.
报错原因是因为：database flashback已经开启了。
SQL> create restore point maclean_rollback guarantee flashback database;
SQL> select * from v$restore_point;
1082500 2 YES 52428800
07-JUL-17 03.31.17.000000000 PM
YES
MACLEAN_ROLLBACK
9、执行升级操作
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 536874104 bytes
Database Buffers 289406976 bytes
Redo Buffers 2392064 bytes
Database mounted.
Database opened.
SQL> set echo on
SQL> spool /home/oracle/upgrade.log
SQL> set time on
15:35:27 SQL> @?/rdbms/admin/catupgrd.sql
15:37:15 SQL> Rem
15:37:15 SQL> Rem $Header: rdbms/admin/catupgrd.sql /st_rdbms_11.2.0/3 2011/05/18 15:07:25 cmlim Exp $
15:37:15 SQL> Rem
15:37:15 SQL> Rem catupgrd.sql
15:37:15 SQL> Rem
15:37:15 SQL> Rem Copyright (c) 1999, 2011, Oracle and/or its affiliates.
15:37:15 SQL> Rem All rights reserved.
15:37:15 SQL> Rem
15:37:15 SQL> Rem NAME
15:37:15 SQL> Rem catupgrd.sql - CATalog UPGraDe to the new release
15:37:15 SQL> Rem
........................................
........................................
Commit complete.
16:37:23 SQL>
16:37:23 SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
16:37:39 SQL>
16:37:39 SQL>
16:37:39 SQL> DOC
16:37:39 DOC>#######################################################################
16:37:39 DOC>#######################################################################
16:37:39 DOC>
16:37:39 DOC> The above sql script is the final step of the upgrade. Please
16:37:39 DOC> review any errors in the spool log file. If there are any errors in
16:37:39 DOC> the spool file, consult the Oracle Database Upgrade Guide for
16:37:39 DOC> troubleshooting recommendations.
16:37:39 DOC>
16:37:39 DOC> Next restart for normal operation, and then run utlrp.sql to
16:37:39 DOC> recompile any invalid application objects.
16:37:39 DOC>
16:37:39 DOC> If the source database had an older time zone version prior to
16:37:39 DOC> upgrade, then please run the DBMS_DST package. DBMS_DST will upgrade
16:37:39 DOC> TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped
16:37:39 DOC> with Oracle.
16:37:39 DOC>
16:37:39 DOC>#######################################################################
16:37:39 DOC>#######################################################################
16:37:39 DOC>#
16:37:39 SQL>
16:37:39 SQL> Rem Set errorlogging off
16:37:39 SQL> SET ERRORLOGGING OFF;
16:37:39 SQL>
16:37:39 SQL> REM END OF CATUPGRD.SQL
16:37:39 SQL>
16:37:39 SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.
16:37:39 SQL> REM This forces user to start a new sqlplus session in order
16:37:39 SQL> REM to connect to the upgraded db.
16:37:39 SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
以上catupgrd.sql脚本运行了一个小时左右，执行完之后会shutdown immediate 数据库，这个时候要重启数据库运行utlrp.sql脚本编译失效对象。
10、运行utlrp.sql编译失效对象
[oracle@uat dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 7 16:43:14 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 734006392 bytes
Database Buffers 92274688 bytes
Redo Buffers 2392064 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlrp
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2017-07-07 16:44:27
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2017-07-07 16:46:55
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
该脚本大约运行3分钟左右。
11、至此数据库升级已经完成，查看各组版本号：
SQL> select comp_name,status,version from dba_server_registry;
COMP_NAME STATUS VERSION
---------------------------------------- -------------------- ------------------------------
OWB VALID 11.2.0.3.0
Oracle Application Express VALID 3.2.1.00.12
Oracle Enterprise Manager VALID 11.2.0.4.0
OLAP Catalog VALID 11.2.0.4.0
Spatial VALID 11.2.0.4.0
Oracle Multimedia VALID 11.2.0.4.0
Oracle XML Database VALID 11.2.0.4.0
Oracle Text VALID 11.2.0.4.0
Oracle Expression Filter VALID 11.2.0.4.0
Oracle Rules Manager VALID 11.2.0.4.0
Oracle Workspace Manager VALID 11.2.0.4.0
Oracle Database Catalog Views VALID 11.2.0.4.0
Oracle Database Packages and Types VALID 11.2.0.4.0
JServer JAVA Virtual Machine VALID 11.2.0.4.0
Oracle XDK VALID 11.2.0.4.0
Oracle Database Java Packages VALID 11.2.0.4.0
OLAP Analytic Workspace VALID 11.2.0.4.0
Oracle OLAP API VALID 11.2.0.4.0
18 rows selected.
12、检查无效对象
SQL> select * from dba_objects where status !='VALID';
no rows selected
SQL>
13、升级成功后删除原来的目录，通过EMCA重建EM
删除EM
emca -repos drop
创建EM
emca -repo create
emca -config dbcontrol db
至此数据库升级完成。
(笔者原创文章，转载请注明出处：https://blog.csdn.net/LFCuiYs）谢谢！
5123
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
