EBS R12 Database upgrade 11.2.0.4 to 12.1.0.2

Database Upgrade 11gR4 to 12cR1

EBS R12.1.3 Database Upgrade 11.2.0.4 to 12.1.0.2

1.     https://blogs.oracle.com/stevenChan/entry/12_1_0_2_db

2.     Interoperability Notes EBS 12.0 or 12.1 with RDBMS 12cR1 (Doc ID 1524398.1)

3.     Oracle 12c 12.1.0.2 Software – Patch 21419221 (1, 2  and 9 of 10 zip files are required)

Environment:

Apps Tier– Oracle EBS R12.1.3 on Oracle Linux EL 6.7 64bit (3 GB Mem, 120 GB Storage)

Database Tier– Database 11.2.0.4 on Oracle Linux EL 6.7 64bit (4GB Mem, 400 GB Storage)

Instance Name: VIS

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

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

Application Environment set to applmgr user (VIS_ebsapps.env)

Database Envinoment set to oravis user(VIS_ebsdb.env)

/software NFS Mount – 120 GB

Check OraTab:

·         cat /etc/oraTab

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

Stop Apps Services:

·         Stop Applications – Apps tier

$ cd $ADMIN_SCRIPTS_HOME

$ adstpall.sh apps/apps

·         Enable Maintenance  Mode

$adadmin

Option 5 and 1

Interoperability Patches:

·         Apply Patch 8796558 – WMS Module

·         Apply Patch 9239090 - Oracle E-Business Suite 12.1.3 upgrade, it’s not required.

·         Apply Patch 12923944

Required Patches:

1.     8502056 - R12.AD.B.delta.2

SQL> select * from ad_bugs where bug_number = '8502056';

2.     7651091 - R12.ATG_PF.B.delta.2

SQL> select * from ad_bugs where bug_number = '7651091';

$adpatch

u12923944.drv

·         Apply Patch 13473483

$adpatch

u13473483.drv

·         Apply Patch 16289505 - 12c Release 1 interoperability patch for Release 12.1

Required Patches:

1.     9239089 - R12.AD.B.delta.3

SQL> select * from ad_bugs where bug_number = ' 9239089';

2.     8919491 - R12.ATG_PF.B.delta.3

SQL> select * from ad_bugs where bug_number = '8919491';

$adpatch

u16289505.drv

·         Apply Patch 18843706 - apply only if you have applied a Recommended Patch Collection (RPC) patch

$adpatch

u18843706.drv

·         Apply Patch 19558309

$adpatch

u19558309.drv

·         Disable Maintenance Mode

$adadmin

Options 5 and 2

Stop DB Services:

·         Verify current Versions

SQL>select banner from v$version;

SQL>select release_name from apps.fnd_product_groups;

·         Shutdown database – Database tier

Sqlplus / as sysdba

Sql> shutdown immediate

·         Stop listeners – Database tier

$ lsnrctl stop

Screen:

12.1.0.2 Install – Software Only:

·         Take context file backup

·         Remove the appsora.env from bash_profile

·         Set following variables

$ export PATH=$PATH:$HOME/bin

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

$export ORACLE_SID=VIS

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

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

$export LD_LIBRARY_PATH=$ORACLE_HOME/lib

$export PERL5LIB=$ORACLE_HOME/perl/lib/5.8.3:$ORACLE_HOME/perl/lib/site_perl/5.8.3

$export  OPTACH_HOME=$ORACLE_HOME/OPatch

·         Install 12.1.0.2 -  software only with “oravis” account

$ cd /mnt/software/DB12R1/database/install

$./runInstaller

Ignore Step1 & 2

Step3: Select “Install database software only”

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

·         Verify Installation

Issues:

Error:

INS-10102 – Installer has detected that an invalid inventory pointer location file was specified

Installer is looking for additional information in a central Inventory.  Parameter "inst_group" is missing from the file "/etc/oraInst.loc"

Cause:
Installer is looking for additional information in a central Inventory.  Parameter "inst_group" is missing from the file "/etc/oraInst.loc"

Solution:
Add the valid group  for "inst_group" in "/etc/oraInst.loc"

inventory_loc=/u01/oraR12/oraInventory

inst_group=dba

Install Screens:

Install Examples CD:

·         $ cd /mnt/software/DB12R1/ examples /install

$./runInstaller

Verify Installation

Install Screens:

Create nls/data/9idata directory:

·         Execute following command

$ 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 12c Oracle home.

Apply Additional 12.1.0.2 RDMS Patches:

·         Opatch - Apply additional 12.1.0.2 RDBMS patches – Database tier

$cd /mnt/software/patches/12c – Make sure opatch is in path

Patch 6880880

Patch 19382851

Patch 19393542

Patch 19627012

Patch 19649152

Patch 20204035

Note: Do not run any of the post install instructions as those will be done after the upgrade. 

·         Set new optatch path:

$cd $ORACLE_HOME/OPatch

$export PATH=$PATH:$ORACLE_HOME/OPatch

·         Apply 19382851 Opatch:

$cd /mnt/software/patches/12c/19382851

$opatch apply

·         Apply 19393542 Opatch:

$cd /mnt/software/patches/12c/19393542

OPatch 12c Release 12.1.0.2.0 or the latest version

$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Installing in Offline Mode:

     Shutdown ORACLE_HOME

     $ opatch apply

     Start the services from the Oracle home

Installing in Online Mode:

     ORACLE_HOME up and running

     $ opatch apply online -connectString :::

$ opatch lsinventory

·         Apply 19627012 Opatch:

$cd /mnt/software/patches/12c/19627012

OPatch 12c Release 12.1.0.2.0 or the latest version

$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Installing in Offline Mode:

      Shutdown ORACLE_HOME

      $ opatch apply

      Start the services from the Oracle home

Installing in Online Mode:

      ORACLE_HOME up and running

      $ opatch apply online -connectString :::

$ opatch lsinventory

·         Apply 19649152 Opatch:

$cd /mnt/software/patches/12c/19649152

OPatch 12c Release 12.1.0.2.0 or the latest version

$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Installing in Offline Mode:

      Shutdown ORACLE_HOME

      $ opatch apply

      Start the services from the Oracle home

Installing in Online Mode:

      ORACLE_HOME up and running

      $ opatch apply online -connectString :::

$ opatch lsinventory

·         Apply 20204035 Opatch:

$cd /mnt/software/patches/12c/20204035

$opatch apply

Verify patch application status

Back up old TNS:

·         If you need to use old tns entries, execute bellow command

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

Start Source 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 in apps tier

Drop SYS.ENABLED$INDEXES (conditional):

·         If the SYS.ENABLED$INDEXES table exists, use SQL*Plus to connect to the database as SYSDBA and running the following command to drop it:

SQL> drop table sys.enabled$indexes;

Remove the MGDSYS schema (conditional):

·         If upgrading from an RDBMS version prior to 12c, on the old database server node, use SQL*Plus to connect to the database as SYSDBA and run the $ORACLE_HOME/md/admin/catnomgdidcode.sql script. This drops the MGDSYS schema.

$ sqlplus "/ as sysdba" @?/md/admin/catnomgdidcode.sql

Pre Upgrade Process:

·         Copy preupgrd.sql and utluppkg.sql (From new database):

$Login with oravis and set new database 12.1.0.2 env values

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

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

$cd $HOME

$ ./appsora.env (from bash_profile)

$ sqlplus '/ as sysdba'

SQL> spool utluppkg12c_info.log

SQL> @utluppkg.sql

SQL> spool off

SQL> spool upgrade12c_info.log

SQL> @ preupgrd.sql

SQL> spool off

SQL> exec utl_recomp.recomp_parallel(25);

SQL> exec DBMS_STATS.drop_stat_table('APPLSYS','FND_STATTAB');

SQL> exec DBMS_STATS.create_stat_table('APPLSYS','FND_STATTAB');

SQL> exec DBMS_STATS.upgrade_stat_table('APPLSYS','FND_STATTAB');

·         Invalid Objects recompile – Source database

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

If it returns more than zero

SQL> connect "/ as sysdba"

SQL> @utlrp.sql

·         Optimizer Statistics:

$ sqlplus "/as sysdba"

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

Note: Do not shutdown source database

Upgrade Check Failures:

1.     ERROR: --> RECYCLE_BIN not empty.

Solution: EXECUTE dbms_preup.purge_recyclebin_fixup;

2.     ERROR: --> SYSTEM.AUD$ (audit records) Move

Oracle requires that records in the audit table SYSTEM.AUD$ be moved to SYS.AUD$ prior to upgrading..

The Database has 0 rows in SYSTEM.AUD$ which will be moved during the upgrade.

The downtime during the upgrade will be affected if there are a large number of rows to be moved.

The audit data can be moved manually prior to the upgrade by using the script: rdbms/admin/olspreupgrade.sql which is part of the Oracle Database 12c software.

Please refer to the Label Security Administrator guide or the Database Upgrade guide.

Solution:

Copy “olspreupgrade.sql” from 12.1.0.2 ORACLE_HOME/ rdbms/admin to $HOME

$cd $HOME

$sqlplus / as sysdba <11gr4 database="">

SQL>@ olspreupgrade.sql

3.     WARNING: --> "DMSYS" schema exists in the database

Ignore this warning

The DMSYS schema (Oracle Data Mining) will be removed from the database during the database upgrade.

All data in DMSYS will be preserved under the SYS schema.

Refer to the Oracle Data Mining User's Guide for details.

4.     WARNING: --> Database contains INVALID objects prior to upgrade

Ignore this warning

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 unless there were over 5000. Use utluiobj.sql after the upgrade to identify any new invalid objects due to the upgrade.

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 N

$vi /etc/oratab

VIS: /u01/oraR12/VIS/db/tech_st/11.2.0:N

Save and exit

Database Upgrade -12.1.0.2:

·         Set following values:

$export ORACLE_BASE=/u01/oraR12/VIS

$export ORACLE_SID=VIS

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

$export LD_LIBRARY_PATH=$ORACLE_HOME/lib;

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

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

$PERL5LIB=/u01/oraR12/VIS/db/tech_st/12102/perl/lib/5.10.0 :/u01/oraR12/VIS/db/tech_st/12102/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 dbua

$cd $ORACLE_HOME/bin

./dbua

Follow the screens:

Validate all log files

After Upgrade:

·         After complete update restarts the database:

$sqlplus / as sysdba

SQL> shutdown immediate

SQL> startup

SQL> exit

Issues:

ora-01804: failure to initialize timezone information

If Time zone related patches were not applied, following changes are required in DB VIS_ebsdb.env file. Update with following changes

·         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.sql copied oravis user home location >

$sqlplus / as sysdba

SQL>@adgrants.sql apps

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

Gather statistics for SYS schema:

·         Execute “adgrants.sql” in database if not.

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> @ adgrants.sql

·         Gather statistics for SYS schema

$ sqlplus "/ as sysdba"

SQL> alter system enable restricted session;

SQL> @adstats.sql

$ sqlplus "/ as sysdba"

SQL> alter system disable restricted session;

SQL> exit

Deregister old db server:

·         NA

Create the new MGDSYS schema:

·         $ cd $ORACLE_HOME/rdbms/admin/catmgd.sql

$sqlplus / as sysdba

SQL> @catmgd.sql

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/12102

$cp $HOME/ appsutil.zip $ORACLE_HOME

$unzip -o appsutil.zip

·         After creating the appsutil directory and before running AutoConfig, copy orai18n.jar from $ORACLE_HOME/jlib to $ORACLE_HOME/jdk/jre/lib/ext

DB JRE Copy:

·         Copy JRE folder from source database to new database

-NA-

New Context File:

·         Generate the Database Context File (From Server or VNC Session)

$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

After creating the XML context file, ensure the variable s_jretop points to $ORACLE_HOME/jdk/jre and is not altered to any other value.

DB Auto Config:

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

$cd ORACLE_HOME/appsutil/bin/

$adconfig.sh contextfile=/u01/oraR12/VIS/db/tech_st/12102/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

·         After creating the XML context file, ensure the variable s_jretop points to $ORACLE_HOME/jdk/jre and is not altered to any other value.

/u01/oraR12/VIS/db/tech_st/12102/jdk/jre

/u01/oraR12/VIS/db/tech_st/12102/jdk/jre

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

Start Applications:

·         Login Apps Tier with applmgr

$cd $ADMIN_SCRIPTS_HOME

$adstrtal.sh apps/apps

·         Issues:

1.     APPS Account keep getting locked and Applications is not working

Solution:

Verify: show parameter sec_case_sensitive_logon

If it returns TRUE, change it back it to FALSE

alter system set sec_case_sensitive_logon=false;

alter user apps account unlock;

2.     ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instanceORACLE instance started

As of version 12.1.0.2 parameter sec_case_sensitive_logon is deprecated and its default value is TRUE, this means that if you set the parameter to FALSE, this error will be reported:

ORA-32004: obsolete or deprecated parameter(s)  specified for RDBMS instance

When the error occurs starting up Database: ORA-32004 error could be found, it can be safely ignored.

E-Business Suite Release 12.1, ensure that your sqlnet_ifile.ora has the line:

SQLNET.ALLOWED_LOGON_VERSION_SERVER = 8 (if the initialization parameter SEC_CASE_SENSITIVE_LOGON is set to FALSE)

Validations: