Introduction
The database upgradation process for 11G is same as other version of oracle ,
Either using DBUA or manually. I followed the manual process to accomplish the task.
.
Prerequisites
i). Apply ATG rollup patch 6 5903765
In my case instances was not updated to ATG roll up patch 6 , so I have applied the patch before proceeding .
SQL> select * from ad_applied_patches where PATCH_NAME=’5903765′;
no rows selected
Follow metalink note 444524.1 for applying the Rup 6 patch
ii).Developer 6i /forms patch set level should be 18 .Use the below script to find the patch set level
#!/bin/sh
########################################
#fpslevel.sh – Used to find out the forms patchset level
########################################
clear
PatchLevel=0
f60gen > /dev/null 2>&1
if [ $? -eq 127 ]
then
echo “Environment is not Set”
else
PatchLevel=`f60gen | grep ‘(Form Compiler)’ | grep -v Release | awk -F. ‘{print $5}’ `
echo ‘Forms Patchset Level :’ ` expr $PatchLevel – 9 `
fi
In my case the result was 18
[oa11gtst cidcsebu12 EBUDEV] $ ./patchset.sh
Forms Patchset Level : 18
If the patch set level is less than 18 then you to upgrade the patch set level to minimum 18.
Refer metalink Note 125767.1 for patch set upgradation .
iii). Latest Autoconfig patch 7011280
SQL> select * from ad_bugs where BUG_NUMBER=’7011280′;
no rows selected
In my case I have applied the above mentioned patch using adpatch utility. Please go through readme.txt before applying the patch .
.
Prepare to create the 11.1.0 Oracle home
i) Install 11.1.0 software in different location
Set up database environment for installation
Login as Unix os user
[oa11gtst cidcsebu12 ] $ ORACLE_BASE=/fh03/TST11G/oraapps
[oa11gtst cidcsebu12 ] $ export ORACLE_BASE
[oa11gtst cidcsebu12 ] $ ORACLE_HOME=$ORACLE_BASE/11.1.0
[oa11gtst cidcsebu12 ] $ export ORACLE_HOME
[oa11gtst cidcsebu12 ] $ ORACLE_SID=TST11G
[oa11gtst cidcsebu12 ] $ export ORACLE_SID
[oa11gtst cidcsebu12 ] $LD_LIBRARY_PATH=$ORACLE_HOME/lib
[oa11gtst cidcsebu12 ] $ export LD_LIBRARY_PATH
[oa11gtst cidcsebu12 ] $ PATH=$PATH:/usr/local/bin:/usr/ccs/bin:
/usr/sfw/bin:$ORACLE_HOME/bin
[oa11gtst cidcsebu12 ] $ export PATH
ii) Start the Oracle Installation
set display=<local IP of your machine >
iii) Select Installation method Click next
Note follow the instruction on the installation screen and click next
iv) Execute root.sh
v) End of Installation
.
Prepare/Perform to Install 11.1.0.7 patch set
i) Install 11.1.0.7 patch on top of the existing 11.1.0.6 Oracle Home .
# ls -ltr
total 3503232
-rwxrwxrwx 1 root root 125772 Oct 3 2008 README.html
drwxrwxrwx 6 root root 8192 Oct 3 2008 Disk1
-rwxrwxrwx 1 root root 1793502538 Apr 9 01:28 p6890831_111070_SOLARIS64.zip
root on cidcsebu12:</fs02/11.0.7.patch>
# pwd
/fs02/11.0.7.patch
root on cidcsebu12:</fs02/11.0.7.patch>
The installation process remain same using RunInstaller.sh .
.
Post Installation Task
i) Create nls/data/9idata directory
On the database server node, as the owner of the Oracle RDBMS file system and database instance, run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.
After creating the directory, make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you enable the 11g Oracle home.
ii)Apply Additional RDBMS patch in 11.1.0 Home
For all UNIX/Linux platforms, apply the following RDBMS patches:
• 6530141
• 6972189
• 7111245
• 7253531
• 7295298
• 7486407
Note :- The patch 7486407 has got post patch task , which can be done once your database in up and running after upgradation .
Login as Oracle owner set Oracle ENV and set PATH
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH: export PATH
Apply all the patches using opatch utility of Oracle .Please go thorough readme.txt
/fs02/11gpostpatch/6530141
oa11gtst on cidcsebu12:</fs02/11gpostpatch/6530141>
$ ls -ltr
total 16
drwxrwxrwx 4 root root 96 Nov 14 07:24 etc
drwxrwxrwx 3 root root 96 Nov 14 07:24 files
-rwxrwxrwx 1 root root 4980 Nov 14 07:24 README.txt
oa11gtst on cidcsebu12:</fs02/11gpostpatch/6530141>
$ opatch apply
Invoking OPatch 11.1.0.6.2
Oracle Interim Patch Installer version 11.1.0.6.2
Copyright (c) 2007, Oracle Corporation. All rights reserved.
Oracle Home : /fh03/TST11G/oraapps/11.1.0
Central Inventory : /fh03/TST11G/oraInventory
from : /var/opt/oracle/oraInst.loc
OPatch version : 11.1.0.6.2
OUI version : 11.1.0.7.0
OUI location : /fh03/TST11G/oraapps/11.1.0/oui
Log file location : /fh03/TST11G/oraapps/11.1.0/cfgtoollogs/opatch/opatch2009-04-22_00-32-36AM.log
ApplySession applying interim patch ‘6530141’ to OH ‘/fh03/TST11G/oraapps/11.1.0’
Running prerequisite checks…
OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = ‘/fh03/TST11G/oraapps/11.1.0’)
Is the local system ready for patching? [y|n]
Verifying the update…
Inventory check OK: Patch ID 6530141 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 6530141 are present in Oracle Home.
The local system has been patched and can be restarted.
OPatch succeeded.
—————-
The same process needs to follow to apply all the above patches.
.
Run the Pre-Upgrade Information Tool
i) Copy utlu111i.sql from New ORACLE_11GHome to temporary directory
$ ls -l utlu111i.sql
-rw-r–r– 1 oa11gtst dba 159224 Jul 31 2008 utlu111i.sql
oa11gtst on cidcsebu12:</fh03/TST11G/oraapps/11.1.0/rdbms/admin>
Login to your source 10G database as sysdba or system .
Database Environment set to : TST11G
Type ‘exit’ or ‘CTRL-D’ to return to previous shell and environment
[oa11gtst cidcsebu12 TST11G] $ sqlplus “/as sysdba”
SQL*Plus: Release 10.2.0.3.0 – Production on Wed Apr 22 02:12:57 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
Startup the database if it is shutdown
SQL> spool upgradeinfo.log
SQL> @utlu111i.sql
Miscellaneous Warnings
*********************************************************
WARNING: –> Database contains stale optimizer statistics.
…. Refer to the 11g Upgrade Guide for instructions to update
…. statistics prior to upgrading the database.
…. Component Schemas with stale statistics:
…. SYS
…. OLAPSYS
…. SYSMAN
…. CTXSYS
…. XDB
…. MDSYS
WARNING: –> Database contains INVALID objects prior to upgrade.
…. The list of invalid SYS/SYSTEM objects was written to
…. registry$sys_inv_objs.
…. The list of non-SYS/SYSTEM objects was written to
…. registry$nonsys_inv_objs.
…. Use utluiobj.sql after the upgrade to identify any new invalid
…. objects due to the upgrade.
…. USER SYS has 1 INVALID objects.
…. USER XXINV has 5 INVALID objects.
…. USER APPS has 1 INVALID objects.
…. USER XXONT has 4 INVALID objects.
…. USER XXPO has 1 INVALID objects.
WARNING: –> Database contains schemas with objects dependent on network
packages.
…. Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
…. USER XXMRP has dependent objects.
…. USER XXINV has dependent objects.
…. USER APPS has dependent objects.
…. USER XXONT has dependent objects.
…. USER XXBOM has dependent objects.
WARNING: –> EM Database Control Repository exists in the database.
…. Direct downgrade of EM Database Control is not supported. Refer to the
…. 11g Upgrade Guide for instructions to save the EM data prior to upgrade.
.
PL/SQL procedure successfully completed.
SQL> spool off
Tablespaces: [make adjustments in the current environment]
******************************************************
–> SYSTEM tablespace is adequate for the upgrade.
…. minimum required size: 7868 MB
–> APPS_UNDOTS1 tablespace is adequate for the upgrade.
…. minimum required size: 629 MB
–> TEMP tablespace is adequate for the upgrade.
…. minimum required size: 61 MB
–> APPS_TS_QUEUES tablespace is adequate for the upgrade.
…. minimum required size: 307 MB
–> APPS_TS_TX_DATA tablespace is adequate for the upgrade.
…. minimum required size: 3249 MB
–> CTXD tablespace is adequate for the upgrade.
…. minimum required size: 29 MB
–> ODM tablespace is adequate for the upgrade.
…. minimum required size: 15 MB
–> OLAP tablespace is adequate for the upgrade.
…. minimum required size: 32 MB
–> SYSAUX tablespace is adequate for the upgrade.
…. minimum required size: 1074 MB
Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************
–> “background_dump_dest” replaced by “diagnostic_dest”
–> “user_dump_dest” replaced by “diagnostic_dest”
–> “core_dump_dest” replaced by “diagnostic_dest”
Check the above report meticulously , because we need to change/adjust the parameter based on the report in 11g init.ora parameter or in 10G database level .
ii) Create pfile from Spfile and copy the pfile to new 11G_home/dbs
and comment user_dump_dest, core_dump_dest and background_dump_dest
Add the new parameter *.diagnostic_dest=<Oracle_Base>
Change the parameter *.compatible=’11.1.0′
iii) Copy Tnsname.ora and Listener.ora from 10G $TNS_ADMIN to New 11G TNS_ADMIN
and change $ORACLE_HOME location , SID ,Port and Host
Set the env TNS_ADMIN in New Oracle 11G and start the listener.
iv) Check the Timezone version in 10G
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> SELECT version FROM v$timezone_file;
VERSION
———-
4
The minimum version should be 4.
v) Check the Custom DB Links and create spool file .
SQL> SQL> SET LONG 9000
SQL> SELECT DBMS_METADATA.GET_DDL(‘DB_LINK’,a.db_link,a.owner) FROM dba_db_links
vi) Check Invalid objects in 10G .
SQL> spool invalid_pre.lst
SQL> select substr(owner,1,12) owner,
substr(object_name,1,30) object,
substr(object_type,1,30) type, status from
dba_objects where status <>’VALID’
2 3 4 5 ;
[oa11gtst cidcsebu12 TST11G] $ clear
[oa11gtst cidcsebu12 TST11G] $ pwd
/fh03/TST11G/oraapps/10.2.0/rdbms/admin
[oa11gtst cidcsebu12 TST11G] $ ls -l utlrp.sql
-rwxrwxr-x 1 oa11gtst dba 3221 Aug 15 2003 utlrp.sql
[oa11gtst cidcsebu12 TST11G] $ sqlplus “/as sysdba”
SQL*Plus: Release 10.2.0.3.0 – Production on Wed Apr 22 02:38:08 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> @utlrp.sql
For errors check
select * from UTL_RECOMP_ERRORS
vii) Shutdown Source Application services and Database and Take full backup
.
Database Upgradation
i) $ ls -l catupgrd.sql
-rw-r–r– 1 oa11gtst dba 4026 Apr 2 2007 catupgrd.sql
oa11gtst on cidcsebu12:</fh03/TST11G/oraapps/11.1.0/rdbms/admin>
$ sqlplus “/as sysdba”
SQL*Plus: Release 11.1.0.7.0 – Production on Thu Apr 23 00:54:49 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to an idle instance.
$ sqlplus “/as sysdba”
SQL*Plus: Release 11.1.0.7.0 – Production on Thu Apr 23 00:54:49 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1069252608 bytes
Fixed Size 2109352 bytes
Variable Size 427823192 bytes
Database Buffers 624951296 bytes
Redo Buffers 14368768 bytes
Database mounted.
Database opened.
SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> SPOOL OFF
Check patch.log after completion .
.
Post Upgradation Task
i) Shutdown the database and then startup the database once the upgradation complete.
Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.
SQL> @?/rdbms/admin/utlrp.sql
ii) Run adgrants.sql
Copy $APPL_TOP/admin/adgrants.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as SYSDBA and run the script using the following command:
SQL> startup restrict
ORACLE instance started.
Total System Global Area 1069252608 bytes
Fixed Size 2109352 bytes
Variable Size 427823192 bytes
Database Buffers 624951296 bytes
Redo Buffers 14368768 bytes
Database mounted.
SQL> @adstats.sql
Connected.
————————————————–
— adstats.sql started at 2009-04-24 01:06:55 —
Checking for the DB version and collecting statistics …
iii) Grant create procedure privilege on CTXSYS
Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node.
bash-3.00$ sqlplus apps/ppsdev10a
SQL*Plus: Release 8.0.6.0.0 – Production on Mon Apr 27 00:19:32 2009
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @adctxprv.sql
Connecting to SYSTEM
Enter value for 1: temdev10smay
SQL> @adctxprv.sql
Connecting to SYSTEM
Enter value for 1: temdev10smay
Connected.
Enter value for 2: CTXSYS
.
iv) Implement and Run Autoconfig.
Please follow my post Autoconfig on Database Tier here
Follow metalink note 165195.1 for Autoconfig .
v) SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
In the output of the preceding command, the status of all the components should be VALID for a successful upgrade.
.
Refer Metalink Note :- 452783.1 for upgradation
