EBS R12 Database upgrade 11.1.0.7 to 11.2.0.4

EBS R12.1.3 - Database Upgrade 11.1.0.7 to 11.2.0.4

·         Interoperability Notes EBS 12.0 and 12.1 with Database 11gR2 (Doc ID 1058763.1)

·         Complete Checklist to Upgrade the Database to 11gR2 Using DBUA (Doc ID 870814.1)

·         Patchset 13390677 – Oracle Database 11.2.0.4 Software

Apps Tier– Oracle EBS R12.1.3 on Oracle Linux EL 6.7 64bit

Database Tier– Database 11.1.0.7 on Oracle Linux EL 6.7 64bit

Instance Name: VIS

Accounts – applmgr (Apps Tier) and oravis (DB tier)

Host Names: ebsapps.oracle (Apps tier) and ebsdb.oracle(db tier)

Required Patches download:

·                  Patch 8919489

·                  Patch 9738085

·                  Patch 9852070

·                  Patch 12686610

11.2.0.4 – Patches:

·                  Patch 4247037

·                  Patch 17402822

·                  Patch 17468141

·                  Patch 17501296

·                  Patch 17892268

·                  Patch 19393542

·                  Patch 9151516 (Optional for EDI Gate way)

Application Environment set to applmgr user (VIS_ebsapps.env)

Database Envinoment set to oravis user(VIS_ebsdb.env)

Check OraTab:

$cat /etc/oraTab – On DB Tier

Ignore this, if it available it should be “NO” as auto start

Auto Config Check:

·         Validate the patch applied status,

select * from ad_bugs where bug_number in( '8919489', '9738085', '9852070',’12686610')

·         If not applied,

Login Apps tier with applmgr account and enable maintenance mode

$ adadmin -

·         Apply below patches with adpath, follow the sequence

$/mnt/software/Patches/9738085    

$ adpatch

o    Patch 8919489 – Should be applied with 12.1.3 upgrade patch set

After Apply, complete bellow process:

1.1. On the Application Tier (as the APPLMGR user):

        1.1.1. Source the environment file.

        1.1.2. Create the appsutil.zip file by executing:

               $ADPERLPRG $AD_TOP/bin/admkappsutil.pl

               (On Windows: %ADPERLPRG% %AD_TOP%\bin\admkappsutil.pl)

               This will create appsutil.zip under $INST_TOP/admin/out

   1.2.    On the Database Tier (as the ORACLE user):

        1.2.1. Copy or FTP the appsutil.zip file to the

        1.2.2. cd $ORACLE_HOME

        1.2.3. $unzip -o appsutil.zip

        1.2.4. Run AutoConfig by executing:               $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/adautocfg.sh

·         Apply Patch 9738085

$/mnt/software/Patches/9738085   

$ adpatch

·         Patch 9852070

$/mnt/software/Patches/9852070   

$ adpatch

·         Patch 12686610

$/mnt/software/Patches/12686610   

$ adpatch

-This will run the autoconfig

Validate all patch logs for errors and warnings.

Apply patch:

·         Apply patch 6400501 (conditional):

Apply with Opatch –Check Read me

Stop Services:

·         Stop Applications – Apps tier

$ cd $ADMIN_SCRIPTS_HOME

$ adstpall.sh apps/apps

·         Shutdown database – Database tier

Sqlplus / as sysdba

Sql> shutdown immediate

·         Stop listeners – Database tier

$ lsnrctl stop

·         Take context file backup

Apps tier:

$ cp $INST_TOP/appl/admin/VIS_ebsapps.xml $INST_TOP/appl/admin/VIS_ebsapps.xml_beforedbupdate

DB Tier:

·         $cp $ORACLE_HOME/appsutil/VIS_ebsdb.xml $ORACLE_HOME/appsutil/VIS_ebsdb.xml_beforedbupdate

11gR4 Install – Software Only:

·         Remove the environment file (env) from bash_profile for oravis user if you are going to use same account for 11.2.0.4 database (recommended to oravis account, because EBS Database was installed by using same account. All other files are owned by same account)

$vi .bash_profle

Comment bellow line

# . /u01/oraR12/VIS/db/tech_st/11.1.0/VIS_ebsdb.env

Save and Exit

Logout and login back to oravis account.

$echo $ORACLE_HOME

Above command should return nothing

·         Use the VNS session or install at server location, you should not lose the session while upgrading the database, it takes time based on your database size, Vision instance is around 200 GB.

·         Create new directory

mkdir /u01/oraR12/VIS/db/tech_st/11.2.0

$chmod 775 –r  /u01/oraR12/VIS/db/tech_st/11.2.0

·         Set following variables

$export ORACLE_BASE=/u01/oraR12/VIS/db/tech_st

$export ORACLE_SID=VIS

$export ORACLE_HOME=/u01/oraR12/VIS/db/tech_st/11.2.0

$export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH

$export LD_LIBRARY_PATH=$ORACLE_HOME/lib

$export PERL5LIB=$ORACLE_HOME/perl/lib/5.10.:$ORACLE_HOME/perl/lib/site_perl/5.10.0

$export  OPTACH_HOME=$ORACLE_HOME/OPatch

·         Install 11gR4 -  software only with “oravis” account

$cd /mnt/software/software/database – database software location

$./runInstaller

Ignore Step1 & 2

Step3: Select “Install database software only”

Step6: Select “Enterprice Edition” – Vision Instance installed on EE

·         Verify Installation, log file will be located at oraInventory/log/

Install Screens:

Welcome Screen: 

Create nls/data/9idata directory:

·         Execute following command on new database 11.2.0.4

$ perl $ORACLE_HOME/nls/data/old/cr9idata.pl

·         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. $export ORA_NLS10=$ORACLE_HOME/nls/data/9idata

Apply Additional 11.2.0.4 RDMS Patches:

·         Opatch - Apply additional 11.2.0.4 RDBMS patches – Database tier

$cd $HOME/Patches – Make sure opatch is in path

o    Patch 4247037

o    Patch 17402822

o    Patch 17468141

o    Patch 17501296

§  Postinstallation

§  $cd 17501296

§  $ sqlplus /nolog

§  SQL> CONNECT / AS SYSDBA

§  SQL> @postinstall.sql

§  Check the output for any errors

o    Patch 17892268

o    Patch 19393542

·         Set new optatch path:

$cd $ORACLE_HOME/OPatch

$export PATH=$PATH:$ORACLE_HOME/OPatch

·         Apply 4247037 Opatch:

$cd /mnt/software/RDBMS_patches/4247037

$opatch apply

·         Apply 17402822 Opatch:

$cd /mnt/software/RDBMS_patches/17402822

$opatch apply

·         Apply 17468141 Opatch:

$cd /mnt/software/RDBMS_patches/17468141

$opatch apply

·         Apply 17501296 Opatch:

$cd /mnt/software/RDBMS_patches/17501296

$opatch apply

·         Apply 17892268 Opatch:

$cd /mnt/software/RDBMS_patches/17892268

$opatch apply

·         Verify patch application status

Back up old TNS:

Need to check:

$cp -r /u01/oraR12/VIS/db/tech_st/11.1.0/network/admin/VIS_ebsdb /u01/oraR12/VIS/db/tech_st/11.2.0/network/admin/

Start Old Database:

·         Login with oravis account

$ ./appsora.env

$sqlplus / as sysdba

SQL> startup

Note: Do not start the listeners and make sure all application services are down.

Pre Upgrade Process:

·         Copy utlu112i.sql (From new database):

$Login with oravis and set new database 11.2.0.4 env values

$cp $ORACLE_HOME/rdbms/admin/utlu112i.sql $HOME/

$cd $HOME

$ ./appsora.env (from bash_profile)

$ sqlplus '/ as sysdba'

SQL> spool upgrade_info.log

SQL> @utlu112i.sql

SQL> spool off

Review the Issues

Issues:

·         WARNING: --> Your recycle bin contains 3011 object(s).                                             

.... It is REQUIRED that the recycle bin is empty prior to upgrading                               

.... your database.  The command:                                                                  

PURGE DBA_RECYCLEBIN                                                                        

.... must be executed immediately prior to executing your upgrade.  

Sol:     SQL> purge dba_recyclebin;

·         Remove hidden parameters:

To view existing hidden parameters execute the following command while connected AS SYSDBA:                                                    

SQL>hidden_parameters.log

SQL> SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' ;

SQL> spool off                                            

SQL>

Create pfile from spfile and commend out all the lines starting with _

Shutdown the database

Create spfile from pfile

comment all hidden parameters

Startup the database

Run the sql above and make sure it returns no rows.

·         Review events:

Oracle recommends reviewing any defined events prior to upgrading. To view existing non-default events execute the following commands while connected AS SYSDBA:

Events: 

SQL> SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2 WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE' ;

no rows selected

SQL> SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2 WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE' ;

no rows selected

SQL>

·         Invalid Objects recompile – Source database

SQL> select count(1) from dba_objects where status='INVALID';

If it returns more than zero

SQL> connect "/ as sysdba"

SQL> @utlrp.sql

Note: If any others issues needs to resolve before upgrading.

·         Optimizer Statistics:

$ sqlplus "/as sysdba"

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

Note: Do not shutdown source database and new database

Listeners Stop:

·         Stop Listeners – Source Database

$lsnrctl stop

·         Remove Local Listeners from Source Database

$sqlplus / as sysdba

SQL>create pfile from spfile;

$cd $ORACLE_HOME/dbs

$ls -lrt

$vi initVIS.ora

And remove line from pfile

*.local_listener='VIS'

Save and exit

·         oratab file edit  it should be old oracle home path and additionally end must be Y

$vi /etc/oratab

VIS: /u01/oraR12/VIS/db/tech_st/11.1.0:Y

·         Save and exit

Database Upgrade -11.2.0.4:

·         Set following values:

$export ORACLE_BASE=/u01/oraR12/VIS

$export ORACLE_SID=VIS

$export ORACLE_HOME=/u01/oraR12/VIS/db/tech_st/11.2.0

$export LD_LIBRARY_PATH=$ORACLE_HOME/lib;

$export TNS_ADMIN=/u01/oraR12/VIS/db/tech_st/11.2.0/network/admin/VIS_ebsdb

$export ORA_NLS10=$ORACLE_HOME/nls/data/9idata

$PERL5LIB=/u01/oraR12/VIS/db/tech_st/11.2.0/perl/lib/5.10.0:/u01/oraR12/VIS/db/tech_st/11.2.0/perl/lib/site_perl/5.10.0:$ORACLE_HOME/appsutil/perl

$export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin

$export PATH=$ORACLE_HOME/perl:$ORACLE_HOME/perl/lib:$ORACLE_HOME/perl/bin:$PATH

·         Invoke the dbua

$cd $ORACLE_HOME/bin

./dbua

Follow the screens.

Validate all log files

Screens:

After Upgrade:

·         After complete update restarts the database:

$sqlplus / as sysdba

SQL> shutdown immediate

SQL> startup

SQL> exit

·         Listeners Validation:

$lsnrctl stop VIS

$lsnrctl start VIS

·         Run adgrants.sql:

If it is not updated one copy $APPL_TOP/admin/adgrants.sql from apps tier to DB tier

$cp $HOME < adgrants copied oravis user home location >

$sqlplus / as sysdba

·         SQL>@adgrants.sql apps

Database Validation:

 

CTXSYS Changes:

·         Copy the adctxprv.sql file to db tier from apps tier

Login Apps tier copy file from $AD_TOP/patch/115/sql/ location

Copy to oravis home location on db tier (through winscp or others)

Login to db tier with

$cd $HOME

Execute: sqlplus apps/[APPS password] @adctxprv.sql \ [SYSTEM password] CTXSYS

$sqlplus apps/apps @adctxprv.sql \ manager CTXSYS

·         Set CTXSYS parameter:

$sqlplus / as sysdba

SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public');

Validate Workflow Rule:

·         Copy the wfaqupfix.sql file to db tier from apps tier

Login Apps tier copy file from $FND_TOP/patch/115/sql/ location

Copy to oravis home location on db tier (through winscp or others)

Login to db tier with

$cd $HOME

Execute: sqlplus [APPS user]/[APPS password] @wfaqupfix.sql\ [APPLSYS user] [APPS user]

$sqlplus apps/apps @ wfaqupfix.sql \ applsys apps

Copy Autoconfig:

·         Copy appsutil.zip from apps tier, if not exist create new one

Login to apps tier with applmgr account

$perl $AD_TOP/bin/admkappsutil.pl

appsutil.zip will be created

Copy appsutil.zip to oravis home location on db tier (through winscp or others)

Login to db tier with oravis

$cd $ORACLE_HOME or /u01/oraR12/VIS/db/tech_st/11.2.0

$cp $HOME/appsutil.

DB JRE Copy:

·         Copy JRE folder from source database to new database

Login to db tier with oravis

$cp -r /u01/oraR12/VIS/db/tech_st/11.1.0/appsutil/jre /u01/oraR12/VIS/db/tech_st/11.2.0/appsutil/

New Context File:

·         Generate the Database Context File

$perl $ORACLE_HOME/appsutil/bin/adbldxml.pl

Required apps password

Note: Error - Perl lib version (5.10.0) doesn't match executable version (v5.10.1) at Workaround - $export ORACLE_HOME=/u01/oraR12/VIS/db/tech_st/11.2.0 export $PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/site_perl/5.10.0:$ORACLE_HOME/appsutil/perl export PATH=$ORACLE_HOME/perl:$ORACLE_HOME/perl/lib:$ORACLE_HOME/perl/bin:$PATH

DB Auto Config:

·         Auto Config on new database

Login to apps tier with applmgr account

$cd $ADMIN_SCRIPTS_HOME

 $adstpall.sh apps/apps

Login to db tier with oravis

$cd $ORACLE_HOME/appsutil/scripts

$adautocfg.sh

OR

·         Run adconfig  on the Database tier from xml file (it will create environment file for db tire )

$ORACLE_HOME/appsutil/bin/adconfig.sh contextfile=/u01/oraR12/VIS/db/tech_st/11.2.0/appsutil/VIS_ebsdb.xml

·         Stop All services on DB tier

$sqlplus / as sysdba

SQL> shutdown immediate

SQL> exit

$lsnrctl stop

·         Start all services on DB tier

$sqlplus / as sysdba

SQL> startup

SQL> exit

$lsnrctl start

Note: Database is started with all new configuration

Gather statistics for SYS schema:

·         Copy the adstats.sql file to db tier from apps tier

Login Apps tier copy file from $APPL_TOP/admin/ location

Copy to oravis home location on db tier (through winscp or others)

Login to db tier with oravis account

$cd $HOME

$ sqlplus "/ as sysdba"

SQL> alter system enable restricted session;

SQL> @adstats.sql

$ sqlplus "/ as sysdba"

SQL> alter system disable restricted session;

SQL> exit;

Apps Autoconfig:

·         Auto config on Apps tier

$login to apps tier with applmgr account

$cd $ADMIN_SCRIPTS_HOME

$adstpall.sh apps/apps

$adautocfg.sh

$adstrtal.sh apps/apps

Note: Apps tier and DB Tier started with all new configuration