ORACLE 11.2.0.1升级到11.2.0.3 - xiaozhao1 - 博客园
最近听了李光老师的关于oracle的升级公开课，深有感悟，之前一直想自己测试的，没有下定决心，这几天自己在虚拟机上测试了一下，测试的过程如下，当然这个只是一些基本的步骤，实际的生产环境我想比这个复杂的多了，但是不用急，慢慢来，循序渐进吧。。。
1. 数据库情况
单实例非ASM存储
ORACLE_SID ： orcl
ORACLE_HOME: /u01/app/oracle/product/11.2.0/dbhome_1
1. 数据库原始状态
[oracle@rhel6 tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 26 16:44:37 2014
Copyright (c) , Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL& create table test as select * from user_
Table created.
SQL& select count(1)
----------
SQL&set linesize 150
SQL&set pagesize 9999
SQL& col comp_name format a40
SQL& SELECT COMP_NAME, VERSION, STATUS FROM 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.1.0&&&&&&&&&&&&VALID
OLAP Catalog&&&&&&&&&&&&&&&& 11.2.0.1.0&&&&&&&&&&&&VALID
Spatial &&&&&&&&&&&&&&&& 11.2.0.1.0&&&&&&&&&&&&VALID
Oracle Multimedia&&&&&&&&&&&& 11.2.0.1.0&&&&&&&&&&&&VALID
Oracle XML Database&&&&&&&&&&&& 11.2.0.1.0&&&&&&&&&&&&VALID
Oracle Text&&&&&&&&&&&&&&&& 11.2.0.1.0&&&&&&&&&&&&VALID
Oracle Expression Filter&&&&&&&& 11.2.0.1.0&&&&&&&&&&&&VALID
Oracle Rules Manager&&&&&&&&&&&& 11.2.0.1.0&&&&&&&&&&&&VALID
Oracle Workspace Manager&&&&&&&& 11.2.0.1.0&&&&&&&&&&&&VALID
Oracle Database Catalog Views&&&&&&&& 11.2.0.1.0&&&&&&&&&&&&VALID
Oracle Database Packages and Types&&&& 11.2.0.1.0&&&&&&&&&&&&VALID
JServer JAVA Virtual Machine&&&&&&&& 11.2.0.1.0&&&&&&&&&&&&VALID
Oracle XDK&&&&&&&&&&&&&&&& 11.2.0.1.0&&&&&&&&&&&&VALID
Oracle Database Java Packages&&&&&&&& 11.2.0.1.0&&&&&&&&&&&&VALID
OLAP Analytic Workspace &&&&&&&& 11.2.0.1.0&&&&&&&&&&&&VALID
Oracle OLAP API &&&&&&&&&&&& 11.2.0.1.0&&&&&&&&&&&&VALID
18 rows selected.
数据库保持运行状态：
解压命令：
unzip p030_Linux-x86-64_1of7.zip -d /tmp && unzip p030_Linux-x86-64_2of7.zip -d /tmp
1. 升级Oracle database 软件
开始安装：
执行到76%的时候弹出如下窗口：
[root@rhel6 ~]# /u01/app/oracle/product/11.2.0.4/dbhome_1/root.sh
Performing root user operation for Oracle 11g
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/11.2.0.4/dbhome_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/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@rhel6 ~]#
1. 升级Instance
&&&&&& 升级Instance 有两种方法，第一种是使用dbua工具，第二种是手工执行脚本。
1. 使用DBUA 工具
操作界面：
该步骤可以在告警日志中查看相关信息：
Sun Sep 28 16:16:31 2014
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch
Autotune of undo retention is turned on.
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1
System name:&&&&Linux
Node name:&&&&rhel6.5
Release:&&&&2.6.32-431.el6.x86_64
Version:&&& SMP Sun Nov 10 22:19:54 EST 2013
Machine:&&&&x86_64
VM name:&&&&VMWare Version: 6
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileorcl.ora
System parameters with non-default values:
processes = 1000
shared_pool_size = 252M
java_pool_size = 252M
memory_target = 1G
control_files = "/u01/app/oracle/oradata/orcl/control01.ctl"
control_files = "/u01/app/oracle/oradata/orcl/control02.ctl"
db_block_size = 8192
compatible = "11.2.0.0.0"
undo_tablespace = "UNDOTBS1"
recyclebin = "OFF"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
local_listener = "LISTENER_ORCL"
audit_file_dest = "/u01/app/oracle/admin/orcl/adump"
audit_trail = "DB"
db_name = "orcl"
open_cursors = 300
diagnostic_dest = "/u01/app/oracle"
Sun Sep 28 16:16:38 2014
PMON started with pid=2, OS id=26426
Sun Sep 28 16:16:38 2014
PSP0 started with pid=3, OS id=26428
Sun Sep 28 16:16:39 2014
VKTM started with pid=4, OS id=26430 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Sun Sep 28 16:16:39 2014
GEN0 started with pid=5, OS id=26434
Sun Sep 28 16:16:39 2014
DIAG started with pid=6, OS id=26436
Sun Sep 28 16:16:39 2014
DBRM started with pid=7, OS id=26438
Sun Sep 28 16:16:39 2014
DIA0 started with pid=8, OS id=26440
Sun Sep 28 16:16:39 2014
MMAN started with pid=9, OS id=26442
Sun Sep 28 16:16:39 2014
DBW0 started with pid=10, OS id=26444
Sun Sep 28 16:16:39 2014
LGWR started with pid=11, OS id=26446
Sun Sep 28 16:16:39 2014
CKPT started with pid=12, OS id=26448
Sun Sep 28 16:16:39 2014
SMON started with pid=13, OS id=26450
Sun Sep 28 16:16:39 2014
RECO started with pid=14, OS id=26452
Sun Sep 28 16:16:39 2014
MMON started with pid=15, OS id=26454
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Sun Sep 28 16:16:39 2014
MMNL started with pid=16, OS id=26456
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /u01/app/oracle
Sun Sep 28 16:16:58 2014
ALTER DATABASE MOUNT
Sun Sep 28 16:17:03 2014
Successful mount of redo thread 1, with mount id
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Sun Sep 28 16:17:06 2014
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process MMNL
Stopping background process MMON
License high water mark = 1
All dispatchers and shared servers shutdown
ALTER DATABASE CLOSE NORMAL
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ALTER DATABASE DISMOUNT
Shutting down archive processes
Archiving is disabled
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Sun Sep 28 16:17:10 2014
Stopping background process VKTM
Sun Sep 28 16:17:12 2014
Instance shutdown complete
Sun Sep 28 16:17:14 2014
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch
Autotune of undo retention is turned on.
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1
System name:&&&&Linux
Node name:&&&&rhel6.5
Release:&&&&2.6.32-431.el6.x86_64
Version:&&& SMP Sun Nov 10 22:19:54 EST 2013
Machine:&&&&x86_64
VM name:&&&&VMWare Version: 6
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileorcl.ora
System parameters with non-default values:
processes = 1000
shared_pool_size = 252M
java_pool_size = 252M
memory_target = 1G
control_files = "/u01/app/oracle/oradata/orcl/control01.ctl"
control_files = "/u01/app/oracle/oradata/orcl/control02.ctl"
db_block_size = 8192
compatible = "11.2.0.0.0"
undo_tablespace = "UNDOTBS1"
recyclebin = "OFF"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
local_listener = "LISTENER_ORCL"
audit_file_dest = "/u01/app/oracle/admin/orcl/adump"
audit_trail = "DB"
db_name = "orcl"
open_cursors = 300
diagnostic_dest = "/u01/app/oracle"
Sun Sep 28 16:17:17 2014
PMON started with pid=2, OS id=26627
Sun Sep 28 16:17:17 2014
PSP0 started with pid=3, OS id=26629
Sun Sep 28 16:17:18 2014
VKTM started with pid=4, OS id=26631 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Sun Sep 28 16:17:18 2014
GEN0 started with pid=5, OS id=26635
Sun Sep 28 16:17:18 2014
DIAG started with pid=6, OS id=26637
Sun Sep 28 16:17:18 2014
DBRM started with pid=7, OS id=26639
Sun Sep 28 16:17:18 2014
DIA0 started with pid=8, OS id=26641
Sun Sep 28 16:17:18 2014
MMAN started with pid=9, OS id=26643
Sun Sep 28 16:17:18 2014
DBW0 started with pid=10, OS id=26645
Sun Sep 28 16:17:18 2014
LGWR started with pid=11, OS id=26647
Sun Sep 28 16:17:18 2014
CKPT started with pid=12, OS id=26649
Sun Sep 28 16:17:18 2014
SMON started with pid=13, OS id=26651
Sun Sep 28 16:17:18 2014
RECO started with pid=14, OS id=26653
Sun Sep 28 16:17:18 2014
MMON started with pid=15, OS id=26655
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Sun Sep 28 16:17:18 2014
MMNL started with pid=16, OS id=26657
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /u01/app/oracle
Sun Sep 28 16:17:25 2014
ALTER DATABASE MOUNT
Sun Sep 28 16:17:29 2014
Successful mount of redo thread 1, with mount id
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Sun Sep 28 16:17:30 2014
ALTER DATABASE OPEN MIGRATE
Thread 1 opened at log sequence 3
Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Sep 28 16:17:31 2014
SMON: enabling cache recovery
[26688] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start: end: diff:1260 (12 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is WE8MSWIN1252
Updating 11.2.0.1.0 NLS parameters in sys.props$
-- adding 11.2.0.3.0 NLS parameters.
Stopping background process MMNL
Stopping background process MMON
Starting background process MMON
Starting background process MMNL
Sun Sep 28 16:17:40 2014
MMON started with pid=15, OS id=26690
ALTER SYSTEM enabl
Sun Sep 28 16:17:40 2014
MMNL started with pid=16, OS id=26692
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
Autotune of undo retention is turned off.
ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;
ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;
ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=MEMORY;
Resource Manager disabled during database migration: plan '' not set
ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY;
ALTER SYSTEM SET recyclebin='OFF' DEFERRED SCOPE=MEMORY;
Resource Manager disabled during database migration
Sun Sep 28 16:17:41 2014
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN MIGRATE
Sun Sep 28 16:17:44 2014
Starting background process CJQ0
Sun Sep 28 16:17:45 2014
CJQ0 started with pid=20, OS id=26694
点击YES，后
后边有报告，完成即可。
同样，如果升级过程中出现问题，我们可以关闭数据库后，重新运行dbua 来重新升级上次失败的组件，如下图：
1. 手动升级
1. 第一步 utlu112i.sql
安装最新的 11.2 RDBMS 软件之后，用以前的 ORACLE_HOME 中启动 11.2.0.1实例，对正在运行的以前的实例执行 11.2.0.4 的$ORACLE_HOME/rdbms/admin/utlu112i.sql 脚本，并将输出 spool 至一个文件。
[oracle@rhel6 database]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 26 17:27:46 2014
Copyright (c) , Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL& spool /home/oracle/utlu112i_log.log
SQL& @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 09-26-:37
Script Version: 11.2.0.3.0 Build: 001
**********************************************************************
**********************************************************************
--& name:&&&& ORCL
--& 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 the current environment]
**********************************************************************
--& SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 683 MB
--& SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 438 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: 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:
WARNING: --& "sga_target" needs to be increased to at least 412 MB
--& 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]
**********************************************************************
-- 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: --& 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 11.2.0.1.0 database timezone version
.... to the latest version which comes with the new release.
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_
**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.
To view existing non-default events execute the following commands
while connected AS SYSDBA:
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& spool off
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rhel6 database]$
1. 第二步 DBUPGDIAG.SQL
[oracle@rhel6 share-D]$ cd /tmp/
[oracle@rhel6 tmp]$ ll dbupgdiag.sql
-rwxr-xr-x. 1 oracle oinstall 23931 Sep 26 17:43 dbupgdiag.sql
[oracle@rhel6 tmp]$ pwd
[oracle@rhel6 tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 26 17:44:31 2014
Copyright (c) , Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL& @/tmp/dbupgdiag.sql
Enter location for Spooled output:
Enter value for 1: /home/oracle
26_Sep_ .log
&&&&&&&&&&&& *** Start of LogFile ***
Oracle Database Upgrade Diagnostic Utility&&&& 09-26-:54
===============
===============
===============
Database Name
===============
===============
Database Uptime
===============
16:01 26-SEP-14
=================
Database Wordsize
=================
This is a 64-bit database
================
Software Version
================
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE&&&&11.2.0.1.0&&&&Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
=============
Compatibility
=============
Compatibility is set as 11.2.0.0.0
================
Archive Log Mode
================
Database log mode&&&& No Archive Mode
Automatic archival&&&& Disabled
Archive destination&&&& /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 1
Current log sequence&&&& 2
================
Auditing Check
================
NAME&&&&&&&&&&&&&&&& TYPE&&&& VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest &&&&&&&& string&&&& /u01/app/oracle/admin/orcl/adu
&&&&&&&&&&&&&&&&&&&&&&&& mp
audit_sys_operations&&&&&&&& boolean&&&& FALSE
audit_syslog_level&&&&&&&& string
audit_trail&&&&&&&&&&&& string&&&& DB
================
Cluster Check
================
NAME&&&&&&&&&&&&&&&& TYPE&&&& VALUE
------------------------------------ ----------- ------------------------------
cluster_database&&&&&&&& boolean&&&& FALSE
cluster_database_instances&&&& integer&&&& 1
DOC&################################################################
DOC& If CLUSTER_DATABASE is set to TRUE, change it to FALSE before
DOC& upgrading the database
DOC&################################################################
===========================================
Tablespace and the owner of the aud$ table
===========================================
OWNER&&&& TABLESPACE_NAME
------------ ------------------------------
SYS&&&& SYSTEM
============================================================================
count of records in the sys.aud$ table where dbid is null- Standard Auditing
============================================================================
============================================================================================
count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed
============================================================================================
select count(*) from system.aud$ where dbid is null
ERROR at line 1:
ORA-00942: table or view does not exist
=============================================================================
count of records in the sys.fga_log$ when dbid is null, Fine Grained Auditing
=============================================================================
==========================================
Oracle Label Security is installed or not
==========================================
Oracle Label Security is NOT installed at database level
================
Number of AQ Records in Message Queue Tables
================
SYS - ALERT_QT - 0
SYS - AQ$_MEM_MC - 0
SYS - AQ_EVENT_TABLE - 0
SYS - AQ_PROP_TABLE - 0
SYS - KUPC$DATAPUMP_QUETAB - 0
SYS - SCHEDULER$_EVENT_QTAB - 0
SYS - SCHEDULER$_REMDB_JOBQTAB - 0
SYS - SCHEDULER_FILEWATCHER_QT - 0
SYS - SYS$SERVICE_METRICS_TAB - 0
SYSMAN - MGMT_LOADER_QTABLE - 0
SYSMAN - MGMT_NOTIFY_INPUT_QTABLE - 0
SYSMAN - MGMT_NOTIFY_QTABLE - 0
SYSMAN - MGMT_PAF_MSG_QTABLE_1 - 0
SYSMAN - MGMT_PAF_MSG_QTABLE_2 - 0
SYSMAN - MGMT_TASK_QTABLE - 27
SYSTEM - DEF$_AQCALL - 0
SYSTEM - DEF$_AQERROR - 0
WMSYS - WM$EVENT_QUEUE_TABLE - 0
================
Time Zone version
================
================
Local Listener
================
LISTENER_ORCL
================
Default and Temporary Tablespaces By User
================
USERNAME&&&&&&&& TEMPORARY_TABLESPACE DEFAULT_TABLESPACE
---------------------------- ---------------------- ----------------------
SYS&&&&&&&&&&&& TEMP&&&&&&&& SYSTEM
SYSTEM&&&&&&&&&&&& TEMP&&&&&&&& SYSTEM
OUTLN&&&&&&&&&&&& TEMP&&&&&&&& SYSTEM
MGMT_VIEW&&&&&&&& TEMP&&&&&&&& SYSTEM
FLOWS_FILES&&&&&&&& TEMP&&&&&&&& SYSAUX
MDSYS&&&&&&&&&&&& TEMP&&&&&&&& SYSAUX
ORDSYS&&&&&&&&&&&& TEMP&&&&&&&& SYSAUX
EXFSYS&&&&&&&&&&&& TEMP&&&&&&&& SYSAUX
DBSNMP&&&&&&&&&&&& TEMP&&&&&&&& SYSAUX
WMSYS&&&&&&&&&&&& TEMP&&&&&&&& SYSAUX
APPQOSSYS&&&&&&&& TEMP&&&&&&&& SYSAUX
APEX_030200&&&&&&&& TEMP&&&&&&&& SYSAUX
OWBSYS_AUDIT&&&&&&&& TEMP&&&&&&&& SYSAUX
ORDDATA &&&&&&&& TEMP&&&&&&&& SYSAUX
CTXSYS&&&&&&&&&&&& TEMP&&&&&&&& SYSAUX
ANONYMOUS&&&&&&&& TEMP&&&&&&&& SYSAUX
SYSMAN&&&&&&&&&&&& TEMP&&&&&&&& SYSAUX
XDB&&&&&&&&&&&& TEMP&&&&&&&& SYSAUX
ORDPLUGINS&&&&&&&& TEMP&&&&&&&& SYSAUX
OWBSYS&&&&&&&&&&&& TEMP&&&&&&&& SYSAUX
SI_INFORMTN_SCHEMA&&&& TEMP&&&&&&&& SYSAUX
OLAPSYS &&&&&&&& TEMP&&&&&&&& SYSAUX
SCOTT&&&&&&&&&&&& TEMP&&&&&&&& USERS
ORACLE_OCM&&&&&&&& TEMP&&&&&&&& USERS
XS$NULL &&&&&&&& TEMP&&&&&&&& USERS
MDDATA&&&&&&&&&&&& TEMP&&&&&&&& USERS
DIP&&&&&&&&&&&& TEMP&&&&&&&& USERS
APEX_PUBLIC_USER&&&& TEMP&&&&&&&& USERS
SPATIAL_CSW_ADMIN_USR&&&& TEMP&&&&&&&& USERS
SPATIAL_WFS_ADMIN_USR&&&& TEMP&&&&&&&& USERS
================
Component Status
================
Comp ID Component&&&&&&&&&&&& Status Version&&&& Org_Version Prv_Version
------- ---------------------------------- --------- -------------- -------------- --------------
AMD&&&&OLAP Catalog&&&&&&&&&&&& VALID 11.2.0.1.0
APEX&&&&Oracle Application Express&&&& VALID 3.2.1.00.10
APS&&&&OLAP Analytic Workspace &&&& VALID 11.2.0.1.0
CATALOG Oracle Database Catalog Views&&&& VALID 11.2.0.1.0
CATJAVA Oracle Database Java Packages&&&& VALID 11.2.0.1.0
CATPROC Oracle Database Packages and Types VALID 11.2.0.1.0
CONTEXT Oracle Text&&&&&&&&&&&& VALID 11.2.0.1.0
EM&&&&Oracle Enterprise Manager&&&& VALID 11.2.0.1.0
EXF&&&&Oracle Expression Filter&&&& VALID 11.2.0.1.0
JAVAVM&&&&JServer JAVA Virtual Machine&&&& VALID 11.2.0.1.0
ORDIM&&&&Oracle Multimedia&&&&&&&& VALID 11.2.0.1.0
OWB&&&&OWB&&&&&&&&&&&&&&&& VALID 11.2.0.1.0
OWM&&&&Oracle Workspace Manager&&&& VALID 11.2.0.1.0
RUL&&&&Oracle Rules Manager&&&&&&&& VALID 11.2.0.1.0
SDO&&&&Spatial &&&&&&&&&&&& VALID 11.2.0.1.0
XDB&&&&Oracle XML Database&&&&&&&& VALID 11.2.0.1.0
XML&&&&Oracle XDK&&&&&&&&&&&& VALID 11.2.0.1.0
XOQ&&&&Oracle OLAP API &&&&&&&& VALID 11.2.0.1.0
======================================================
List of Invalid Database Objects Owned by SYS / SYSTEM
======================================================
Number of Invalid Objects
------------------------------------------------------------------
There are no Invalid Objects
DOC&################################################################
DOC& If there are no Invalid objects below will result in zero rows.
DOC&################################################################
no rows selected
================================
List of Invalid Database Objects
================================
Number of Invalid Objects
------------------------------------------------------------------
There are no Invalid Objects
DOC&################################################################
DOC& If there are no Invalid objects below will result in zero rows.
DOC&################################################################
no rows selected
======================================================
Count of Invalids by Schema
======================================================
==============================================================
Identifying whether a database was created as 32-bit or 64-bit
==============================================================
DOC&###########################################################################
DOC& Result referencing the string 'B023' ==& Database was created as 32-bit
DOC& Result referencing the string 'B047' ==& Database was created as 64-bit
DOC& When String results in 'B023' and when upgrading database to 10.2.0.3.0
DOC& (64-bit) , For known issue refer below articles
ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While
DOC&&&&&&&&& Upgrading Or Patching Databases To 10.2.0.3
ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and
DOC&&&&&&&&& OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6
DOC&###########################################################################
Metadata Initial DB Creation Info
-------- -----------------------------------
B047&&&& Database was created as 64-bit
===================================================
Number of Duplicate Objects Owned by SYS and SYSTEM
===================================================
Counting duplicate objects ....
----------
=========================================
Duplicate Objects Owned by SYS and SYSTEM
=========================================
Querying duplicate objects ....
OBJECT_NAME&&&&&&&&&&&&&&&& OBJECT_TYPE&&&&&&&&&&&&&&&& SUBOBJECT_NAME&&&&&&&& OBJECT_ID
---------------------------------------- ---------------------------------------- ------------------------------ ----------
AQ$_SCHEDULES&&&&&&&&&&&&&&&& TABLE&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 5612
AQ$_SCHEDULES_PRIMARY&&&&&&&&&&&& INDEX&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 5613
DBMS_REPCAT_AUTH&&&&&&&&&&&& PACKAGE&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 8435
DBMS_REPCAT_AUTH&&&&&&&&&&&& PACKAGE BODY&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 11896
DOC&################################################################################
DOC& If any objects found please follow below article.
How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
DOC& Read the Exceptions carefully before taking actions.
DOC&################################################################################
========================
Password protected roles
========================
DOC&################################################################################
DOC& In version 11.2 password protected roles are no longer enabled by default so if
DOC& an application relies on such roles being enabled by default and no action is
DOC& performed to allow the user to enter the password with the set role command, it
DOC& is recommended to remove the password from those roles (to allow for existing
DOC& privileges to remain available). For more information see:
: What Roles Can Be Set as Default for a User?
DOC&################################################################################
Querying for password protected roles ....
Password protected Role Assigned by default to user
------------------------------ ------------------------------
OWB$CLIENT&&&&&&&& OWBSYS
SPATIAL_CSW_ADMIN&&&& SPATIAL_CSW_ADMIN_USR
================
JVM Verification
================
================================================
Checking Existence of Java-Based Users and Roles
================================================
DOC&################################################################################
DOC& There should not be any Java Based users for database version 9.0.1 and above.
DOC& If any users found, it is faulty JVM.
DOC&################################################################################
User Existence
---------------------------
No Java Based Users
DOC&###############################################################
DOC& Healthy JVM Should contain Six Roles.
DOC& If there are more or less than six role, JVM is inconsistent.
DOC&###############################################################
------------------------------
There are 6 JAVA related roles
------------------------------
JAVA_DEPLOY
JAVAUSERPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVADEBUGPRIV
JAVA_ADMIN
=========================================
List of Invalid Java Objects owned by SYS
=========================================
There are no SYS owned invalid JAVA objects
DOC&#################################################################
DOC& Check the status of the main JVM interface packages DBMS_JAVA
DOC& and INITJVMAUX and make sure it is VALID.
DOC& If there are no Invalid objects below will result in zero rows.
DOC&#################################################################
no rows selected
DOC&#################################################################
DOC& If the JAVAVM component is not installed in the database (for
DOC& example, after creating the database with custom scripts), the
DOC& next query will report the following error:
DOC& select dbms_java.longname('foo') "JAVAVM TESTING" from dual
DOC& ERROR at line 1:
DOC& ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier
DOC& If the JAVAVM component is installed, the query should succeed
DOC& with 'foo' as result.
DOC&#################################################################
JAVAVM TESTING
---------------
===================================
Oracle Multimedia/InterMedia status
===================================
Oracle Multimedia/interMedia is installed and listed with the following version: 11.2.0.1.0 and status: VALID
Checking for installed Database Schemas...
ORDSYS user exists.
ORDPLUGINS user exists.
MDSYS user exists.
SI_INFORMTN_SCHEMA user exists.
ORDDATA user exists.
Checking for Prerequisite Components...
JAVAVM installed and listed as valid
XDK installed and listed as valid
XDB installed and listed as valid
Validating Oracle Multimedia/interMedia...(no output if component status is valid)
PL/SQL procedure successfully completed.
&&&&&&&&&&&& *** End of LogFile ***
Upload db_upg_diag_orcl_26_Sep_.log from "/home/oracle" directory
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlrp.sql
1. 第三步 备份
禁用所有批处理和 cron 作业，然后执行数据库的完整备份。
1. 第四步 干净的关闭数据库
[oracle@rhel6 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Sep 28 09:11:46 2014
Copyright (c) , Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rhel6 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Sep 28 09:16:52 2014
Copyright (c) , Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Database closed.
Database dismounted.
ORACLE instance shut down.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rhel6 ~]
1. 第五步 配置目标 11.2.0.3 ORACLE_HOME
拷贝$ORACLE_HOME/dbs 至 新目录 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
拷贝$ORACLE_HOME/network/admin 至 新目录 /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/
[oracle@rhel6 ~]$ cd $ORACLE_HOME/dbs
[oracle@rhel6 dbs]$ ll
total 3580
-rw-r-----. 1 oracle oinstall 3635200 Jul 20 17:50 arch1_32_.dbf
-rw-rw----. 1 oracle oinstall 1544 Sep 26 17:14 hc_DBUA0.dat
-rw-rw----. 1 oracle oinstall 1544 Sep 28 09:17 hc_orcl.dat
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 982 Jul 21 14:28 initorclasm.ora
-rw-r-----. 1 oracle oinstall 24 Jul 20 17:46 lkORCL
-rw-r-----. 1 oracle oinstall 1536 Sep 26 16:00 orapworcl
-rw-r-----. 1 oracle oinstall 2560 Sep 28 09:10 spfileorcl.ora
[oracle@rhel6 dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@rhel6 dbs]$ ll /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
[oracle@rhel6 dbs]$ cp * /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
[oracle@rhel6 dbs]$ ll /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
total 3580
-rw-r-----. 1 oracle oinstall 3635200 Sep 28 09:31 arch1_32_.dbf
-rw-r-----. 1 oracle oinstall 1544 Sep 28 09:31 hc_DBUA0.dat
-rw-r-----. 1 oracle oinstall 1544 Sep 28 09:31 hc_orcl.dat
-rw-r--r--. 1 oracle oinstall 2851 Sep 28 09:31 init.ora
-rw-r-----. 1 oracle oinstall 982 Sep 28 09:31 initorclasm.ora
-rw-r-----. 1 oracle oinstall 24 Sep 28 09:31 lkORCL
-rw-r-----. 1 oracle oinstall 1536 Sep 28 09:31 orapworcl
-rw-r-----. 1 oracle oinstall 2560 Sep 28 09:31 spfileorcl.ora
[oracle@rhel6 dbs]$ cd ..
[oracle@rhel6 dbhome_1]$ cd network/admin/
[oracle@rhel6 admin]$ ll
-rw-r--r--. 1 oracle oinstall 497 Jul 20 17:38 listener.ora
-rw-r--r--. 1 oracle oinstall 981 Jul 20 17:02 listener.ora_bk
drwxr-xr-x. 2 oracle oinstall 4096 May 18 17:01 samples
-rw-r--r--. 1 oracle oinstall 187 May 7 2007 shrept.lst
-rw-r--r--. 1 oracle oinstall 203 Jul 19 22:33 sqlnet.ora
-rw-r--r--. 1 oracle oinstall 1318 Sep 26 16:01 tnsnames.ora
[oracle@rhel6 admin]$ cp -R * /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/
[oracle@rhel6 admin]$
export EDITOR=vi
export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib/:/usr/lib
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
[oracle@rhel6 ~]$ vi .bash_profile
[oracle@rhel6 ~]$ source .bash_profile
[oracle@rhel6 ~]$
1. 第六步 开始升级
sqlplus " / as sysdba "SQL& spool /tmp/upgrade.logSQL& startup upgradeSQL& set echo onSQL& @$ORACLE_HOME/rdbms/admin/catupgrd.SQL& spool offSQL& Shutdown immediate
catupgrd.sql 该脚本花费时间较长，大约30分钟
DOC&#######################################################################
DOC&#######################################################################
DOC& The above sql script is the final step of the upgrade. Please
DOC& review any errors in the spool log file. If there are any errors in
DOC& the spool file, consult the Oracle Database Upgrade Guide for
DOC& troubleshooting recommendations.
DOC& Next restart for normal operation, and then run utlrp.sql to
DOC& recompile any invalid application objects.
DOC& If the source database had an older time zone version prior to
DOC& upgrade, then please run the DBMS_DST package. DBMS_DST will upgrade
DOC& TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped
DOC& with Oracle.
DOC&#######################################################################
DOC&#######################################################################
SQL& Rem Set errorlogging off
SQL& SET ERRORLOGGING OFF;
SQL& REM END OF CATUPGRD.SQL
SQL& REM bug
- Exit current sqlplus session at end of catupgrd.sql.
SQL& REM This forces user to start a new sqlplus session in order
SQL& REM to connect to the upgraded db.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
日志压缩后仍然很大，这里就不贴出来了。。。。
[oracle@rhel6 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 28 10:56:16 2014
Copyright (c) , Oracle. All rights reserved.
Connected to an idle instance.
SQL& startup
ORACLE instance started.
Total System Global Area
Fixed Size&&&&&&&& 2235208 bytes
Variable Size&&&&&&&&
Database Buffers&&&&
Redo Buffers&&&&&&&& 5541888 bytes
Database mounted.
Database opened.
SQL& @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlu112s.sql
Oracle Database 11.2 Post-Upgrade Status Tool&&&&&&&&09-28-:11
Component&&&&&&&&&&&&&&&&Current Version&&&& Elapsed Time
Name&&&&&&&&&&&&&&&&&&&&Status&&&& Number&&&& HH:MM:SS
Oracle Server
.&&&&&&&&&&&&&&&&&&&& VALID 11.2.0.3.0 00:14:09
JServer JAVA Virtual Machine
.&&&&&&&&&&&&&&&&&&&& VALID 11.2.0.3.0 00:05:55
Oracle Workspace Manager
.&&&&&&&&&&&&&&&&&&&& VALID 11.2.0.3.0 00:00:44
OLAP Analytic Workspace
.&&&&&&&&&&&&&&&&&&&& VALID 11.2.0.3.0 00:00:51
OLAP Catalog
.&&&&&&&&&&&&&&&&&&&& VALID 11.2.0.3.0 00:01:09
Oracle OLAP API
.&&&&&&&&&&&&&&&&&&&& VALID 11.2.0.3.0 00:00:41
Oracle Enterprise Manager
.&&&&&&&&&&&&&&&&&&&& VALID 11.2.0.3.0 00:03:35
Oracle XDK
.&&&&&&&&&&&&&&&&&&&& VALID 11.2.0.3.0 00:00:41
Oracle Text
.&&&&&&&&&&&&&&&&&&&& VALID 11.2.0.3.0 00:00:39
Oracle XML Database
.&&&&&&&&&&&&&&&&&&&& VALID 11.2.0.3.0 00:03:05
Oracle Database Java Packages
.&&&&&&&&&&&&&&&&&&&& VALID 11.2.0.3.0 00:00:30
Oracle Multimedia
.&&&&&&&&&&&&&&&&&&&& VALID 11.2.0.3.0 00:04:49
.&&&&&&&&&&&&&&&&&&&& VALID 11.2.0.3.0 00:02:22
Oracle Expression Filter
.&&&&&&&&&&&&&&&&&&&& VALID 11.2.0.3.0 00:00:14
Oracle Rules Manager
.&&&&&&&&&&&&&&&&&&&& VALID 11.2.0.3.0 00:00:12
Oracle Application Express
.&&&&&&&&&&&&&&&&&&&& VALID 3.2.1.00.10
Gathering Statistics
.&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 00:03:09
Total Upgrade Time: 00:42:58
PL/SQL procedure successfully completed.
