Patch set download.
1. 11203 patch 10404530 for Linux x86-64.
2. Latest CPU Patch 14275605 from OTN.
3. Latest OPatch 6880880 from OTN.
Step 1.
Install the 11020 RDBMS binaries into new Oracle_home.
Run the root.sh as as root user.
Step 2.
Unzip the downloaded Patches and Update the OPatch version and install the CPU patch .
[oracle@box1 OPatch]$ pwd
/u01/app/oracle/product/11.2.0.3/db_1/OPatch
[oracle@box1 OPatch]$ cd /u01/11g_64_upg_patch/14275605/
[oracle@box1 14275605]$ echo $PATH
/u01/app/oracle/product/11.2.0.3/db_1/bin:/usr/sbin:/u01/app/oracle/product/12.1.0/db_1/bin:/usr/sbin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin
[oracle@box1 14275605]$ export PATH=/u01/app/oracle/product/11.2.0.3/db_1/bin:/usr/sbin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/u01/app/oracle/product/11.2.0.3/db_1/OPatch
[oracle@box1 14275605]$ echo $PATH
/u01/app/oracle/product/11.2.0.3/db_1/bin:/usr/sbin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/u01/app/oracle/product/11.2.0.3/db_1/OPatch
[oracle@box1 14275605]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.5
Copyright (c) 2013, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0.3/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.3/db_1/oraInst.loc
OPatch version : 11.2.0.3.5
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/opatch2013-09-03_01-14-26AM_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/lsinv/lsinventory2013-09-03_01-14-26AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.3.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.
[oracle@box1 14275605]$ opatch apply
Oracle Interim Patch Installer version 11.2.0.3.5
Copyright (c) 2013, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0.3/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.3/db_1/oraInst.loc
OPatch version : 11.2.0.3.5
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/opatch2013-09-03_01-24-57AM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 13343438 13696216 13923374 14275605
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:
You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/11.2.0.3/db_1')
Is the local system ready for patching? [y|n]
Y
User Responded with: Y
Backing up files...
Applying sub-patch '13343438' to OH '/u01/app/oracle/product/11.2.0.3/db_1'
Patching component oracle.rdbms.rsf, 11.2.0.3.0...
Patching component oracle.rdbms, 11.2.0.3.0...
Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...
Verifying the update...
Applying sub-patch '13696216' to OH '/u01/app/oracle/product/11.2.0.3/db_1'
Patching component oracle.rdbms.rsf, 11.2.0.3.0...
Patching component oracle.rdbms, 11.2.0.3.0...
Patching component oracle.sdo.locator, 11.2.0.3.0...
Patching component oracle.sysman.console.db, 11.2.0.3.0...
Patching component oracle.sysman.oms.core, 10.2.0.4.4...
Verifying the update...
Applying sub-patch '13923374' to OH '/u01/app/oracle/product/11.2.0.3/db_1'
ApplySession: Optional component(s) [ oracle.network.cman, 11.2.0.3.0 ] not present in the Oracle Home or a higher version is found.
Patching component oracle.rdbms.rsf, 11.2.0.3.0...
Patching component oracle.rdbms, 11.2.0.3.0...
Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...
Patching component oracle.network.rsf, 11.2.0.3.0...
Patching component oracle.network.listener, 11.2.0.3.0...
Patching component oracle.sysman.console.db, 11.2.0.3.0...
Verifying the update...
Applying sub-patch '14275605' to OH '/u01/app/oracle/product/11.2.0.3/db_1'
ApplySession: Optional component(s) [ oracle.precomp.lang, 11.2.0.3.0 ] not present in the Oracle Home or a higher version is found.
Patching component oracle.network.client, 11.2.0.3.0...
Patching component oracle.network.rsf, 11.2.0.3.0...
Patching component oracle.precomp.common, 11.2.0.3.0...
Patching component oracle.rdbms, 11.2.0.3.0...
Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...
Patching component oracle.rdbms.rman, 11.2.0.3.0...
Patching component oracle.rdbms.rsf, 11.2.0.3.0...
Patching component oracle.rdbms.util, 11.2.0.3.0...
Verifying the update...
OPatch found the word "warning" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg'
/u01/app/oracle/product/11.2.0.3/db_1/precomp/lib/env_precomp.mk:2160: warning: ignoring old commands for target `pcscfg.cfg'
/u01/app/oracle/product/11.2.0.3/db_1/precomp/lib/ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg'
/u01/app/oracle/product/11.2.0.3/db_1/precomp/lib/env_precomp.mk:2160: warning: ignoring old commands for target `pcscfg.cfg'
Composite patch 14275605 successfully applied.
OPatch Session completed with warnings.
Log file location: /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/opatch2013-09-03_01-24-57AM_1.log
OPatch completed with warnings.
[oracle@box1 14275605]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.5
Copyright (c) 2013, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0.3/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.3/db_1/oraInst.loc
OPatch version : 11.2.0.3.5
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/opatch2013-09-03_01-32-52AM_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/lsinv/lsinventory2013-09-03_01-32-52AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.3.0
There are 1 products installed in this Oracle Home.
Interim patches (1) :
Patch 14275605 : applied on Tue Sep 03 01:27:42 IST 2013
Unique Patch ID: 15367368
Patch description: "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Created on 3 Oct 2012, 18:38:19 hrs PST8PDT
Sub-patch 13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch 13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch 13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"
Bugs fixed:
14480676, 13566938, 13419660, 10350832, 13632717, 14063281, 12919564
13624984, 13430938, 13467683, 13588248, 13420224, 14548763, 13080778
12646784, 13804294, 12861463, 12834027, 13377816, 13036331, 12880299
14664355, 13499128, 14409183, 12998795, 12829021, 13492735, 12794305
13503598, 10133521, 12718090, 13742433, 12905058, 12401111, 13742434
13257247, 12849688, 13362079, 12950644, 13742435, 13464002, 12917230
13923374, 12879027, 14613900, 12585543, 12535346, 14480675, 12588744
11877623, 14480674, 13916709, 12847466, 13773133, 14076523, 13649031
13340388, 13366202, 13528551, 13981051, 12894807, 13343438, 12582664
12748240, 12797765, 13385346, 12923168, 13384182, 13612575, 13466801
13484963, 12971775, 11063191, 13772618, 13070939, 12797420, 13035804
13041324, 12976376, 11708510, 13742437, 13737746, 14062795, 13035360
12693626, 13742438, 13326736, 13332439, 14038787, 14062796, 12913474
13001379, 14390252, 13099577, 13370330, 13059165, 14062797, 14275605
9873405, 13742436, 9858539, 14062794, 13358781, 12960925, 13699124
12662040, 9703627, 12617123, 13338048, 12938841, 12658411, 12620823
12845115, 12656535, 14062793, 12678920, 12764337, 13354082, 13397104
14062792, 13250244, 12594032, 9761357, 12612118, 13742464, 13550185
13457582, 13527323, 12780983, 12583611, 13502183, 12780098, 13705338
13696216, 13476583, 11840910, 13903046, 13572659, 13718279, 13554409
13657605, 13103913, 14063280
--------------------------------------------------------------------------------
OPatch succeeded.
Step 3. Go under the New oracle home directory as below
'/u01/app/oracle/product/11.2.0.3/db_1/rdbms/admin/'
[oracle@box1 admin]$ pwd
/u01/app/oracle/product/11.2.0.3/db_1/rdbms/admin
[oracle@box1 admin]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1/
[oracle@box1 admin]$ sqlplus "/as sysdba"
SQL> @utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 09-03-2013 02:01:46
Script Version: 11.2.0.3.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: DB11G
--> 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: 694 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 474 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
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 78 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- 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_stats;
**********************************************************************
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.
**********************************************************************
/u01/app/oracle/product/11.2.0.3/db_1/rdbms/admin
[oracle@box1 admin]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1/
[oracle@box1 admin]$ sqlplus "/as sysdba"
SQL> @utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 09-03-2013 02:01:46
Script Version: 11.2.0.3.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: DB11G
--> 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: 694 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 474 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
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 78 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- 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_stats;
**********************************************************************
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.
**********************************************************************
Check the database timezone.
SQL> select version from v$timezone_file;
11
11
SQL> PURGE DBA_RECYCLEBIN;
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
SQL> SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
2 WHERE UPPER(name) ='EVENT' AND isdefault='FALSE';
SQL> SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
2 WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE';
Step 5.
Download the script dbupgdiag.sql from Oracle support Note. 556610.1
run that script to verify that all the components in dba_registry are valid.
And no invalid data dictionary objects exist in dba_objects.
SQL> @/u01/dbupgdiag.sql
Enter location for Spooled output:
Enter value for 1: /tmp
03_Sep_2013_0451 .log
db11g_
*** Start of LogFile ***
Oracle Database Upgrade Diagnostic Utility 09-03-2013 16:51:14
===============
Hostname
===============
box1.oracle.com
===============
Database Name
===============
DB11G
===============
Database Uptime
===============
16:27 03-SEP-13
=================
Database Wordsize
=================
This is a 64-bit database
================
Software Version
================
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.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 USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 52
Current log sequence 54
================
Auditing Check
================
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/db11g/ad
ump
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>
DOC> If CLUSTER_DATABASE is set to TRUE, change it to FALSE before
DOC> upgrading the database
DOC>
DOC>################################################################
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
============================================================================
0
============================================================================================
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
=============================================================================
0
==========================================
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 - 3
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 - 28
SYSTEM - DEF$_AQCALL - 0
SYSTEM - DEF$_AQERROR - 0
WMSYS - WM$EVENT_QUEUE_TABLE - 0
================
Time Zone version
================
11
================
Local Listener
================
================
Default and Temporary Tablespaces By User
================
USERNAME TEMPORARY_TABLESPACE DEFAULT_TABLESPACE
---------------------------- ---------------------- ----------------------
MGMT_VIEW TEMP SYSTEM
SYS TEMP SYSTEM
SYSTEM TEMP SYSTEM
DBSNMP TEMP SYSAUX
SYSMAN TEMP SYSAUX
OUTLN TEMP SYSTEM
FLOWS_FILES TEMP SYSAUX
MDSYS TEMP SYSAUX
ORDSYS TEMP SYSAUX
EXFSYS 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
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
BI TEMP USERS
PM TEMP USERS
MDDATA TEMP USERS
IX TEMP USERS
SH TEMP USERS
DIP TEMP USERS
OE TEMP USERS
APEX_PUBLIC_USER TEMP USERS
HR 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.3.0 11.2.0.1.0 11.2.0.1.0
APEX Oracle Application Express VALID 3.2.1.00.10
APS OLAP Analytic Workspace VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
CATALOG Oracle Database Catalog Views VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
CATJAVA Oracle Database Java Packages VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
CATPROC Oracle Database Packages and Types VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
CONTEXT Oracle Text VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
EM Oracle Enterprise Manager VALID 11.2.0.3.0 11.2.0.1.0
EXF Oracle Expression Filter VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
JAVAVM JServer JAVA Virtual Machine VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
ORDIM Oracle Multimedia VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
OWB OWB VALID 11.2.0.1.0
OWM Oracle Workspace Manager VALID 11.2.0.3.0 11.2.0.1.0
RUL Oracle Rules Manager VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
SDO Spatial VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
XDB Oracle XML Database VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
XML Oracle XDK VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
XOQ Oracle OLAP API VALID 11.2.0.3.0 11.2.0.1.0 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>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#
no rows selected
================================
List of Invalid Database Objects
================================
Number of Invalid Objects
------------------------------------------------------------------
There are no Invalid Objects
DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#
no rows selected
======================================================
Count of Invalids by Schema
======================================================
==============================================================
Identifying whether a database was created as 32-bit or 64-bit
==============================================================
DOC>###########################################################################
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
DOC>
DOC> Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While
DOC> Upgrading Or Patching Databases To 10.2.0.3
DOC> Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and
DOC> OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6
DOC>
DOC>###########################################################################
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 ....
COUNT(1)
----------
4
=========================================
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>################################################################################
DOC>
DOC> If any objects found please follow below article.
DOC> Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
DOC> Read the Exceptions carefully before taking actions.
DOC>
DOC>################################################################################
DOC>#
========================
Password protected roles
========================
DOC>
DOC>################################################################################
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:
DOC>
DOC> Note 745407.1 : What Roles Can Be Set as Default for a User?
DOC>
DOC>################################################################################
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>################################################################################
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>
DOC>################################################################################
DOC>#
User Existence
---------------------------
No Java Based Users
DOC>
DOC>###############################################################
DOC>
DOC> Healthy JVM Should contain Six Roles.
DOC> If there are more or less than six role, JVM is inconsistent.
DOC>
DOC>###############################################################
DOC>#
Role
------------------------------
There are 6 JAVA related roles
Roles
ROLE
------------------------------
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>#################################################################
DOC>
DOC> Check the status of the main JVM interface packages DBMS_JAVA
DOC> and INITJVMAUX and make sure it is VALID.
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>#################################################################
DOC>#
no rows selected
DOC>
DOC>#################################################################
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>
DOC> select dbms_java.longname('foo') "JAVAVM TESTING" from dual
DOC> *
DOC> ERROR at line 1:
DOC> ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier
DOC>
DOC> If the JAVAVM component is installed, the query should succeed
DOC> with 'foo' as result.
DOC>
DOC>#################################################################
DOC>#
JAVAVM TESTING
---------------
foo
*** End of LogFile ***
Upload db_upg_diag_db11g_03_Sep_2013_0451.log from "/tmp" directory
Enter location for Spooled output:
Enter value for 1: /tmp
03_Sep_2013_0451 .log
db11g_
*** Start of LogFile ***
Oracle Database Upgrade Diagnostic Utility 09-03-2013 16:51:14
===============
Hostname
===============
box1.oracle.com
===============
Database Name
===============
DB11G
===============
Database Uptime
===============
16:27 03-SEP-13
=================
Database Wordsize
=================
This is a 64-bit database
================
Software Version
================
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.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 USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 52
Current log sequence 54
================
Auditing Check
================
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/db11g/ad
ump
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>
DOC> If CLUSTER_DATABASE is set to TRUE, change it to FALSE before
DOC> upgrading the database
DOC>
DOC>################################################################
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
============================================================================
0
============================================================================================
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
=============================================================================
0
==========================================
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 - 3
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 - 28
SYSTEM - DEF$_AQCALL - 0
SYSTEM - DEF$_AQERROR - 0
WMSYS - WM$EVENT_QUEUE_TABLE - 0
================
Time Zone version
================
11
================
Local Listener
================
================
Default and Temporary Tablespaces By User
================
USERNAME TEMPORARY_TABLESPACE DEFAULT_TABLESPACE
---------------------------- ---------------------- ----------------------
MGMT_VIEW TEMP SYSTEM
SYS TEMP SYSTEM
SYSTEM TEMP SYSTEM
DBSNMP TEMP SYSAUX
SYSMAN TEMP SYSAUX
OUTLN TEMP SYSTEM
FLOWS_FILES TEMP SYSAUX
MDSYS TEMP SYSAUX
ORDSYS TEMP SYSAUX
EXFSYS 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
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
BI TEMP USERS
PM TEMP USERS
MDDATA TEMP USERS
IX TEMP USERS
SH TEMP USERS
DIP TEMP USERS
OE TEMP USERS
APEX_PUBLIC_USER TEMP USERS
HR 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.3.0 11.2.0.1.0 11.2.0.1.0
APEX Oracle Application Express VALID 3.2.1.00.10
APS OLAP Analytic Workspace VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
CATALOG Oracle Database Catalog Views VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
CATJAVA Oracle Database Java Packages VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
CATPROC Oracle Database Packages and Types VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
CONTEXT Oracle Text VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
EM Oracle Enterprise Manager VALID 11.2.0.3.0 11.2.0.1.0
EXF Oracle Expression Filter VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
JAVAVM JServer JAVA Virtual Machine VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
ORDIM Oracle Multimedia VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
OWB OWB VALID 11.2.0.1.0
OWM Oracle Workspace Manager VALID 11.2.0.3.0 11.2.0.1.0
RUL Oracle Rules Manager VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
SDO Spatial VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
XDB Oracle XML Database VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
XML Oracle XDK VALID 11.2.0.3.0 11.2.0.1.0 11.2.0.1.0
XOQ Oracle OLAP API VALID 11.2.0.3.0 11.2.0.1.0 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>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#
no rows selected
================================
List of Invalid Database Objects
================================
Number of Invalid Objects
------------------------------------------------------------------
There are no Invalid Objects
DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#
no rows selected
======================================================
Count of Invalids by Schema
======================================================
==============================================================
Identifying whether a database was created as 32-bit or 64-bit
==============================================================
DOC>###########################################################################
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
DOC>
DOC> Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While
DOC> Upgrading Or Patching Databases To 10.2.0.3
DOC> Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and
DOC> OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6
DOC>
DOC>###########################################################################
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 ....
COUNT(1)
----------
4
=========================================
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>################################################################################
DOC>
DOC> If any objects found please follow below article.
DOC> Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
DOC> Read the Exceptions carefully before taking actions.
DOC>
DOC>################################################################################
DOC>#
========================
Password protected roles
========================
DOC>
DOC>################################################################################
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:
DOC>
DOC> Note 745407.1 : What Roles Can Be Set as Default for a User?
DOC>
DOC>################################################################################
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>################################################################################
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>
DOC>################################################################################
DOC>#
User Existence
---------------------------
No Java Based Users
DOC>
DOC>###############################################################
DOC>
DOC> Healthy JVM Should contain Six Roles.
DOC> If there are more or less than six role, JVM is inconsistent.
DOC>
DOC>###############################################################
DOC>#
Role
------------------------------
There are 6 JAVA related roles
Roles
ROLE
------------------------------
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>#################################################################
DOC>
DOC> Check the status of the main JVM interface packages DBMS_JAVA
DOC> and INITJVMAUX and make sure it is VALID.
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>#################################################################
DOC>#
no rows selected
DOC>
DOC>#################################################################
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>
DOC> select dbms_java.longname('foo') "JAVAVM TESTING" from dual
DOC> *
DOC> ERROR at line 1:
DOC> ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier
DOC>
DOC> If the JAVAVM component is installed, the query should succeed
DOC> with 'foo' as result.
DOC>
DOC>#################################################################
DOC>#
JAVAVM TESTING
---------------
foo
*** End of LogFile ***
Upload db_upg_diag_db11g_03_Sep_2013_0451.log from "/tmp" directory
Step 6.
Shutdown the database gracefully.
Step 7.
--> Copy the pfile/spfile , password file from old Oracle home to new Oracle home.
--> Copy the listener and tnsname file from old oracle network directory to new one.
Also update the TNSname.ora file with the new Oracle_home.
--> Update the Oracle bash profile for the new Oracle_home.
--> Update the /etc/oratab with the new Oracle_home.
Start the listener under new Oracle home network directory.
Step 8.
Connect the database through sqlplus and issue the startup upgrade.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 2037673984 bytes
Fixed Size 2229824 bytes
Variable Size 1224739264 bytes
Database Buffers 805306368 bytes
Redo Buffers 5398528 bytes
Database mounted.
Database opened.
ORACLE instance started.
Total System Global Area 2037673984 bytes
Fixed Size 2229824 bytes
Variable Size 1224739264 bytes
Database Buffers 805306368 bytes
Redo Buffers 5398528 bytes
Database mounted.
Database opened.
Go to the dir '/u01/app/oracle/product/11.2.0.3/rdbms/admin'
and run the catupgrd.sql script.
sql>@catupgrd.sql
And Check the script output for any error. If there is any then try to fix it.
Restart the database in normal mode.
sql>@catuppst.sql
sql>@utlrp.sql
Again run the downloaded dbupgdiag.sql script to crosscheck if there is any error left due to the database upgrade.
sql>@dbupgdiag.sql
SQL> select version from v$instance;
VERSION
-----------------
11.2.0.3.0
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
DB11G READ WRITE
VERSION
-----------------
11.2.0.3.0
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
DB11G READ WRITE
Now the database is upgraded to version 11203.
Next step is to upgrade the RDBMS DST version which I'll show you in my next thread.
References.
OTN Doc 1276368.1: Complete checklist for out-of-place manual upgrade...
OTN Doc 556610.1: Script to collect DB Upgrade/Migrate diagnostic information.
No comments:
Post a Comment