How to Import from Excel to Oracle with SQL Developer

How to Import from Excel to Oracle with SQL Developer:

===================================

This question comes up about as frequently as the ‘how do I export data to XLS?’ question. It’s pretty simple once you’ve ran through the process a few times. But you may be here because you’ve never ran through the process before.
This post will step you from beginning to end. You should be prepared to import data to an existing table from Excel with confidence after reading this post. You can use SQL Developer to create a new table for your Excel data, but that will be covered in a subsequent post.
Warning: This post has a LOT of pictures.
For our example I’ll be using HR.EMPLOYEES to build the XLS file. I have created a blank copy of the table in another schema and want to import the data from my excel file over.

Note: We’ve updated this feature for version 4.1!

Step 0: The Empty Oracle Table and your Excel File

You have an Oracle table and you have one or more Excel files.

Data here but not there!?!
You do know how to view multiple objects at once in SQL Developer, right?

Step 1: Mouse-right click – Import Data

Yes, it's that easy.

Step 2: Select your input (XLS) file

Yes, we also support XLSX, CSV, etc

Step 3: Verify the data being read

Mind the headers!
Does your Excel file have column headers? Do we want to treat those as a row to the table? Probably not. The default options take care of this. You can also choose to preview more than 100 rows.
Here’s what it looks like if you uncheck the ‘Header’ box
Sometimes you may want the column headers as a row in the table?
Sometimes your Excel file has multiple headers, or you may need to only import a certain subset of the spreadsheet. Use the ‘Skip Rows’ option to get the right data.

Step 4: Create a script or import automatically

Script or do it for me?
For this exercise the ‘Insert’ method will be used.

Step 5:

Choose the Excel columns to be imported
You may have an Excel file with 100 columns but your table only has 30. This is where you tell SQL Developer what columns are to be used for the import. You can also change up the column order, which may make the next step a bit easier.

Step 6:


If you’re not paying attention and just letting the wizard guide you home, then now is the time to wake up. There’s a good chance the column order of the Excel file won’t match the definition of your table. This is where you will tell SQL Developer what columns in the spreadsheet match up to what columns in the Oracle table.

Step 7: Verify your settings

Hit the ‘verify’ button. Fix any mistakes.
Ruh roh raggy!
SQL Developer is telling you it doesn’t know how to reconcile the data for this DATE column. We need to know what the DATE FORMAT is.
So we need to go back to the Column definition wizard and inspect the HIRE_DATE column settings.

You need to look at how the dates are stored in the spreadsheet and write them in terms that Oracle can understand. This will be used on the INSERTs via a TO_DATE() function that will turn your Excel string into an actual DATE value.
After correcting this, go back to the Verification screen and see if that fixes the problem.

Step 8:

Everything looks right!
Click on the ‘Finish’ button.

Step 9: Verify the import look at your new table data

The data is there and the dates look right!
Note the ‘Log’ panel. SQL Developer is processing the records in batches of 50. No errors and the data is there!

Just a few more pointers when it comes to Excel

Concurrent Troubleshooting

Concurrent Troubleshooting:

=======================

 

R12 Opp(output Post Processor) and Workflow Mailer is down:

=============================================== 

When i see the Status OPP Manger and Workflow Mailer from Concurrent–>Manager–>Administer Screen. I see below status
oppwf
Solution :
• Ensure Concurrent:GSM Enabled profile is set to ‘Y’
• Verify Service Manager status in Administer Form.
• Verify Service Manager Definition.
• Ensure FNDSM Entries available in FND_CONCURRENT_QUEUES Table
• FNDSM entry should be correct in Tnsnames.ora file and tnsping  FNDSM_hostname should work fine.
• Then Bounce the Services.
Ensure Concurrent:GSM Enabled profile is set to ‘Y’
stat
all
all2

ORA-06512: at “APPS.FND_CP_FNDSM”, line 29 Concurrent Manger not starting:

===============================================

When i checked concurrent manager log under $APPLCSF/log/<SID>.mgr
I see below error:
Cause: cleanup_node failed due to ORA-01427: single-row subquery returns more than one row
ORA-06512: at “APPS.FND_CP_FNDSM”, line 29
ORA-06512: at line 1.
The SQL statement being executed at the time of Routine AFPEIM encountered an error while starting concurrent manager STANDARD with library /dev/applmgr/R12/apps/apps_st/appl/fnd/12.0.0/bin/FNDLIBR.
Check that your system has enough resources to start a concurrent manager process. Contact your syst : 08-OCT-2013 00:30:51
Starting IEU_WL_CS Concurrent Manager : 08-OCT-2013 00:30:51
Could not initialize the Service Manager FNDSM_apps01_dev. Verify that apps01 has been registered for concurrent processing.
ORACLE error 1427 in cleanup_node
Cause: cleanup_node failed due to ORA-01427: single-row subquery returns more than one row
ORA-06512: at “APPS.FND_CP_FNDSM”, line 29
ORA-06512: at line 1.
The SQL statement being executed at the time of
Routine AFPEIM encountered an error while starting concurrent manager IEU_WL_CS with library /dev/applmgr/R12/apps/apps_st/appl/fnd/12.0.0/bin/FNDLIBR.
Solution
———-
sqlplus apps/apps
sql>exec fnd_conc_clone.setup_clean;
commit;
sql>@cmclean.sql
Started the concurrent manager on the application tier and it worked

Concurrent Processing – R12 Output Post Processor Service Not Coming Up:

------------------------------------------------------------------------------

Reason :
If Service Manager for the node is not running.  Possible cause might be service manager definition is missing under
Concurrent ->Manager ->Define form. If the Service Manager is not present/defined for a particular node,then this causes all the services provided by Service Manager like OPP,WF etc.. not to work.
1. Shutdown all the services.
——Below Step 2 will create  Service Manager “FNDSM”——-
2. Log in as applmgr
cd to $FND_TOP/patch/115/sql
Run the script: afdcm037.sql
3. Relink FNDSM and FNDLIBR executables as mentioned below:
$ adrelink.sh force=y link_debug=y “fnd FNDLIBR”
$ adrelink.sh force=y link_debug=y “fnd FNDSM”
4. Run cmclean.sql
5. Start up the managers/services

Managers down – Status “System Hold, Fix Manager”:

Managers down – Status “System Hold, Fix Manager”:

=============================================

Concurrent Managers Status shows  “System Hold, Fix Manager”  in Concurrent–>Manager–>Administer Screen form.

sys

Solution:
• Ensure Concurrent :GSM Enabled profile is set to ‘Y’
• Run $FND_TOP/patch/115/sql/afdcm037.sql
• Go to $FND_TOP/bin
adrelink.sh force=y “fnd FNDLIBR”
adrelink.sh force=y “fnd FNDSM”
adrelink.sh force=y “fnd FNDFS”
adrelink.sh force=y “fnd FNDCRM”
• Run cmclean.sql
• Start Application Service (adstrtal.sh)

Upgradation from R12.1.1 to R12.1.3


R12.1.1 to R12.1.3 Upgrade:

====================== 

In this post i am going to list out the steps to upgrade R12.1.1 to R12.1.3

NOTE: Please make a note that i have not included pre and post patch steps while applying patches. Please go-through the patch README’s carefully while applying patches…….

Step1: 

Upgrade Database from 11.1.0.7 to 11gr2. I have upgraded from 11.1.0.7 to 11.2.0.2. 

Following are the steps to upgrade 11.1.0.7 to 11.2.0.2 on R12.1.1

About Oracle Database Release Numbers Oracle Database Upgrade Guide describes moving between different releases of Oracle Database. Starting with Oracle9i Release 2 (9.2), maintenance releases of Oracle Database are denoted by a change to the second digit of a release number. In earlier releases, the third digit indicated a particular maintenance release.
Figure 1–2 illustrates each part of a release number and what it represents.
1
Presently the database version is 11.1.0.72

The Upgrade process is divided into pre-upgrade, Upgrade and Post Upgrade.

1.Pre-Upgrade

Step-1
Check for oratab entry in /etc/oratab
3

Install 11gR2(11.2.0.2) Software only in new location
Create new location
mkdir –p /u01/oracle/RACDB/db/tech_st/11.2.0
Parallely Run Gather Dictionary Stats
4 5

Once we finish above. Let’s start installing 11.2.0.2 Oracle Software in new location /u01/oracle/RACDB/tech_st/db/11.2.0/
*Note: Don’t set DB environment during the installation of 11.2.0.2 Software.*
Download
p10098816_112020_Linux-x86-64_1of7.zip p10098816_112020_Linux-x86-64_2of7.zip unzip both the files in same location
Install 11gR2 Pre-req patches using YUM
yum install oracle-rdbms-server-11gR2-preinstall 6 Execute runinstaller
7

8 9 10 11 12 13 14 15 16 17 18 19 20
Now go to /tmp/CVU_11.2.0.2
And execute ./runfixup.sh
21 22 23 24 25 26

Now take a count of Invalid Objects
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where STATUS=’INVALID’;

27
I will execute utlrp.sql or below package to compile invalid objects
exec utl_recomp.recomp_parallel(10);
28

29
From the above Screenshot we are going to apply
  • 11g Release 2 interoperability patch for Release 12.1 (9062910)
  • 12.1 TXK Delta 3 patch (8919489)
  • 9868229
  • 10163753
  • 11071569
Put Application under Maintenance using adadmin and start applying the above patches
Apply patch 6400501 (conditional)
If you are on a UNIX/Linux platform, apply the 10.1.0.5 version of patch 6400501 to the iAS 10.1.2 Applications tier Oracle home.
Note: For Linux x86-64 environments, download the Linux 32-bit version of the patch as the Applications tier Oracle home is 32-bit.

Update application tier context file with new database listener port number (conditional)
The new 11.2.0 Oracle home uses its own database listener for the database instance, replacing the current database listener. Use the Context Editor to update the following variables in the Applications context file on each application tier server node to reflect the 11.2.0 configuration:
Variable Name Value
s_dbhost New database hostname
s_dbdomain New database domain name
s_db_serv_sid New database SID
s_dbport New database listener port
s_apps_jdbc_connect_descriptor NULL

Database Installation:
Prepare to create the 11.2.0 Oracle home
The 11.2.0 Oracle home must be installed on the database server node in a different directory than the current Oracle home Read Chapters 1 and 2 of the Oracle Database Installation Guide 11g Release 2 (11.2) for your platform. also read Chapter 1 and the “System Considerations and Requirements” section of Chapter 3 of the Oracle Database Upgrade Guide 11g Release 2 (11.2). Make sure you thoroughly understand the installation and upgrade processes. Perform any step that is relevant for your environment.

Attention: For HP Itanium and HP PA RISC platforms using the NFS file system, choose a disk to install the 11g Oracle home separate from the database files. The mount options of the disk containing the database files must include forcedirectio. The 11g Oracle home (or any other binaries) must not include the forcedirectio mount option.
Install Oracle Database 11g Products from the 11g Examples CD
On the database server node, as the owner of the Oracle RDBMS file system and database instance, perform the tasks in section 4, “Download Oracle Database Examples” in the Oracle Database Examples CD Installation Guide.
In the Installation Types window, use the Product Languages button to select any languages other than American English that are used by your Applications database instance.
After the installation, make sure that:
The ORACLE_BASE environment variable must be set accordingly.
The ORACLE_HOME environment variable points to the new 11.2.0 Oracle home.
The PATH environment variable includes $ORACLE_HOME/bin and the directory where the new perl executable is located (usually $ORACLE_HOME/perl/bin).
The LD_LIBRARY_PATH environment variable includes $ORACLE_HOME/lib.
The PERL5LIB environment variable points to the directories where the new perl libraries are located (usually $ORACLE_HOME/perl/lib/[perl version] and $ORACLE_HOME/perl/lib/site_perl/[perl version])
ExamplesCD: p10098816_112020_Linux-x86-64_6of7.zip
Download and apply above examples CD on 11202 Database Software:
30 31 32 33 34
The ORACLE_BASE environment variable must be set accordingly.
The ORACLE_HOME environment variable points to the new 11.2.0 Oracle home.
The PATH environment variable includes $ORACLE_HOME/bin and the directory where the new perl executable is located (usually $ORACLE_HOME/perl/bin).
The LD_LIBRARY_PATH environment variable includes $ORACLE_HOME/lib.
The PERL5LIB environment variable points to the directories where the new perl libraries are located (usually $ORACLE_HOME/perl/lib/[perl version] and $ORACLE_HOME/perl/lib/site_perl/[perl version])

export ORACLE_BASE=/u01/oracle/RACDB/
export ORACLE_HOME=/u01/oracle/RACDB/db/tech_st/11.2.0
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=/u01/oracle/RACDB/db/tech_st/11.2.0/lib

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.
mkdir -p $ORACLE_HOME/nls/data/9idata
run the $ORACLE_HOME/nls/data/old/cr9idata.pl
[oracle@r12-rac1 ~]$ cd $ORACLE_HOME
[oracle@r12-rac1 11.2.0]$ cd nls/data/old/
[oracle@r12-rac1 old]$ perl cr9idata.pl
Directory /u01/oracle/RACDB/db/tech_st/11.2.0/nls/data/9idata already exist. Overwriting…
Copying files to /u01/oracle/RACDB/db/tech_st/11.2.0/nls/data/9idata…
Copy finished.
Please reset environment variable ORA_NLS10 to /u01/oracle/RACDB/db/tech_st/11.2.0/nls/data/9idata!
[oracle@r12-rac1 old]$ export ORA_NLS10=/u01/oracle/RACDB/db/tech_st/11.2.0/nls/data/9idata
35
Apply additional 11.2.0.2 RDBMS patches
Apply the following patches:
For all UNIX/Linux platforms:
4247037
10149223
10160615
10229719
12400751
12942119
13001379
13004894

Run utlu112i.sql on existing 11.1.0.7 oracle database
11.1.0.7—/u01/oracle/RACDB/db/tech_st/11.1.0
11.2.0.2—/u01/oracle/RACDB/db/tech_st/11.2.0


Copy utlu112i.sql from 11.2.0 Orace Home
cd /u01/oracle/RACDB/db/tech_st/11.2.0/rdbms/admin
cp utlu112i.sql $HOME

SQL> @utlu112i.sql Oracle Database 11.2 Pre-Upgrade Information Tool 11-12-2013 21:14:59 Script Version: 11.2.0.2.0 Build: 001 . ********************************************************************** Database: ********************************************************************** --> name: RACDB --> version: 11.1.0.7.0 --> compatible: 11.1.0 --> blocksize: 8192 --> platform: Linux x86 64-bit --> timezone file: V10 . ********************************************************************** Tablespaces: [make adjustments in the current environment] ********************************************************************** --> SYSTEM tablespace is adequate for the upgrade. .... minimum required size: 11003 MB --> CTXD tablespace is adequate for the upgrade. .... minimum required size: 16 MB --> ODM tablespace is adequate for the upgrade. .... minimum required size: 10 MB --> APPS_UNDOTS1 tablespace is adequate for the upgrade. .... minimum required size: 853 MB --> APPS_TS_TX_DATA tablespace is adequate for the upgrade. .... minimum required size: 5254 MB --> APPS_TS_QUEUES tablespace is adequate for the upgrade. .... minimum required size: 79 MB WARNING: --> SYSAUX tablespace is not large enough for the upgrade. .... currently allocated size: 446 MB .... minimum required size: 506 MB .... increase current size by: 60 MB .... tablespace is NOT AUTOEXTEND ENABLED. . ********************************************************************** 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] ********************************************************************** --> plsql_native_library_dir 11.2 OBSOLETE --> plsql_native_library_subdir_ 11.2 OBSOLETE . ********************************************************************** 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 --> Real Application Clusters [upgrade] INVALID --> OLAP Analytic Workspace [upgrade] VALID --> OLAP Catalog [upgrade] VALID --> Oracle Text [upgrade] VALID --> Oracle XML Database [upgrade] VALID --> Oracle Java Packages [upgrade] VALID --> Oracle interMedia [upgrade] VALID --> Spatial [upgrade] VALID --> Data Mining [upgrade] VALID --> 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.1.0.7.0 database timezone version .... to the latest version which comes with the new release. WARNING: --> Your recycle bin contains 1724 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. WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package. .... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs. .... USER APPS 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 removing all hidden parameters prior to upgrading. To view existing hidden parameters execute the following command while connected AS SYSDBA: SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' Changes will need to be made in the init.ora or spfile. ********************************************************************** 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. ********************************************************************** The DMSYS schema exists in the database. Prior to performing an upgrade Oracle recommends that the DMSYS schema, and its associated objects be removed from the database. Refer to the Oracle Data Mining Administration Guide for the instructions on how to perform this task. ********************************************************************** 1 WARNING: –> SYSAUX tablespace is not large enough for the upgrade.
…. currently allocated size: 446 MB
…. minimum required size: 506 MB
…. increase current size by: 60 MB
…. tablespace is NOT AUTOEXTEND ENABLED.


1
SOLUTION:  ALTER DATABASE DATAFILE'/u01/oracle/RACDB/db/apps_st/data/sysaux02.dbf' RESIZE 1G; 2 Comment the below parameters in init.ora file  --> plsql_native_library_dir     11.2       OBSOLETE --> plsql_native_library_subdir_ 11.2       OBSOLETE 36 3
WARNING: –> Your recycle bin contains 1724 object(s).
…. It is REQUIRED that the recycle bin is empty prior to upgrading
…. your database.  The command:
PURGE DBA_RECYCLEBIN
SOLUTION:
37

Find and Comment all hidden parameters prior to upgrading.
SELECT name,description from SYS.V$PARAMETER WHERE name LIKE ‘\_%’ ESCAPE ‘\’



38 39 40

2.Upgrade
Shutdown the 11.1.0.7 DATABASE open a new terminal

Set the new environment export ORACLE_BASE=/u01/oracle/RACDB/ export ORACLE_HOME=/u01/oracle/RACDB/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.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0

export ORA_NLS10=/u01/oracle/RACDB/db/tech_st/11.2.0/nls/data/9idata run dbua
42 43 44 45 46 47 48 49 50
Simultaneously Check for count of invalids, it will be reducing.
51

52
So we are done with the upgrade.
53
POST Upgrade

1.Copy $APPL_TOP/admin/adgrants.sql (adgrants_nt.sql for Windows) 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:
  1. $ sqlplus “/ as sysdba” @adgrants.sql (or adgrants_nt.sql)
[APPS schema name]
Note: Verify the usage of adgrants.sql in the adgrants.sql script. Older versions of adgrants.sql require the APPLSYS schema name parameter to be passed instead of APPS.
In this case i have copied the adgrants.sql in /tmp
54

2.Re-create grants and synonyms using adadmin and then compile apps schema this will help us in reducing the no.of invalids.
After Upgrade the no.of invalids
55

After running recreate grants and synonyms and compile apps schema the invalid count is
56
This is really good as we dont have any invalids before or after Upgrade. Even if there are any invalids it should be same when compared to before upgrade. If we get any invalids then we should raise an SR and work on it.
3.When we applied 11.2.0.2 additional patches there were some post patch steps, that has to be taken care.
a)  4247037
Post install steps:
cd $ORACLE_HOME/md/admin
connect to the database using sqlplus / as sysdba
sqlplus> @catmgdidcode
b) 9858539
Post install steps:
Run following files as sysdba
cd $ORACLE_HOME/rdbms/admin
@catnomtt.sql
@catnomta.sql
@dbmsmeta.sql
@dbmsmeti.sql
@dbmsmetu.sql
@dbmsmetb.sql
@dbmsmetd.sql
@dbmsmet2.sql
@catmeta.sql
@prvtmeta.plb
@prvtmeti.plb
@prvtmetu.plb
@prvtmetb.plb
@prvtmetd.plb
@prvtmet2.plb
@catmet2.sql
– recompile dbms_metadata_int to enable the diffing code
alter package dbms_metadata_int compile plsql_ccflags = ‘ku$xml_enabled:true';
– recompile dbms_metadata_util to enable the xmlschema load code
alter package dbms_metadata_util compile plsql_ccflags = ‘ku$xml_enabled:true';
5.Grant create procedure privilege on CTXSYS
Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as APPS and run the script using the following command:
$ sqlplus apps/[APPS password] @adctxprv.sql \
[SYSTEM password] CTXSYS
6.Set CTXSYS parameter
Use SQL*Plus to connect to the database as SYSDBA and run the following command:
$ sqlplus “/ as sysdba”
SQL> exec ctxsys.ctx_adm.set_parameter(‘file_access_role’, ‘public’);
7.Deregister the current database server (conditional)
If you plan to change the database port, host, SID, or database name parameter on the database server, you must also update AutoConfig on the database tier and deregister the current database server node.
Use SQL*Plus to connect to the database as APPS and run the following command:
$ sqlplus apps/[APPS password]
SQL> exec fnd_conc_clone.setup_clean;
  1. Implement and run AutoConfig:
Implement and run AutoConfig in the new Oracle home on the database server node. If the database listener of the new Oracle home is defined differently than the old Oracle home, you must also run AutoConfig on each application tier server node to update the system with the new listener.
On the applciation side run admkappsutil.pl to generate admkappsutil.zip file
copy this admkappsutil.zip to new 11.2.0.2 Oracle Home and unzip then run autoconfig this makes our new oracle home autoconfig enabled.
See Using AutoConfig to Manage System Configurations with Oracle E-Business Suite Release 12 on My Oracle Support for instructions on how to implement and run AutoConfig. Section 3.2 explains how to generate a context file on the database tier.
Shut down all processes, including the database and the listener, and restart them to load the new environment settings.
Attention: For Windows customers, after creating the XML context file, ensure that the variable s_dlsnname has the proper listener service name.

Attention: 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.
  1. Apply post-upgrade ECX patch
If you are on E-Business Suite Release 12.0, apply ECX Patch 9922442. If you are on E-Business Suite Release 12.1, apply Patch 9151516.
  1. Gather statistics for SYS schema
Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node. Note that adstats.sql has to be run in restricted mode. Use SQL*Plus to connect to the database as SYSDBA and use the following commands to run adstats.sql in restricted mode:
$ sqlplus “/ as sysdba”
SQL> alter system enable restricted session;
SQL> @adstats.sql
$ sqlplus “/ as sysdba”
SQL> alter system disable restricted session;
SQL> exit;
11.  Restart Applications server processes
Remove the Maintenance mode and Restart all the Application tier server processes that you shut down previously. Remember that the Oracle Net listener for the database instance, as well as the database instance itself, need to be started in the 11.2 Oracle home. Users may return to the system.
57


Now check the DB version from frontend…..
58 59

We are able to successfully upgrade the database from 11.1.0.7 to 11.2.0.2

 

 


Step2:

Put the application under maintainence using adadmin 

Step3. 

Apply below patch
8919491.B                  Oracle Applications Technology 12.1.3 Product Family Release Update Pack

Step4

Apply Below Patch
17884289 R12.AD.B.Delta.4

Step5:

Apply Below Patch

9239090              R12                    ORACLE E-BUSINESS SUITE 12.1.3 RELEASE UPDATE PACK


Step6:

Apply Below Patch
9239095.                      R12Oracle E-Business Suite Online Help for 12.1.3 Release Update Pack


Step7:

Apply Below Patch

9817770.BPOST-R12.ATG_PF.B.DELTA.3 CONSOLIDATED PATCH

Step8: 

Apply Below patch

9966055.B1OFF:12.1.3:TRANSLATED VERSION OF FNDSCSGN NOT LAUNCHED

Step9:  

Run AutoConfig on the APPL_TOP.

Step10:

Run the admkappsutil.pl utility to create the file appsutil.zip in the <INST_TOP>/admin/out directory.Step9:

Run AutoConfig on the APPL_TOP.
perl <AD_TOP>/bin/admkappsutil.pl

Step11:

Copy or FTP the appsutil.zip file to the <RDBMS ORACLE_HOME>.

Step12:

Uncompress appsutil.zip under the <RDBMS ORACLE_HOME>.

Step13:

unzip the appsutil.zip  as below

unzip -o appsutil.zip

Step14: 

Run AutoConfig on the <RDBMS ORACLE_HOME>.

Step15:

Re-run adpreclone.pl on the database tier and the application tier. Since updates included in Oracle E-Business 
Suite Release 12.1.3 were applied to the system, adpreclone must be run again to apply the updates 
to the clone directory structures that were used during cloning.

perl adpreclone.pl dbTier
perl adpreclone.pl appsTier
This finishes the Application upgrade.
Start the Application Tier Services.
NOTE:After finishing the application upgrade. we need to apply product family patches.
(IN THIS UPGRADE I DIDNT APPLIED BELOW PRODUCT SPECIFIC PATCHES)
Patch Product Family Product Family Readme Documents
Oracle Applications Technology Release 12.1.3 (R12.ATG_PF.B.DELTA.3) Patch 8919491 Applications Technology (ATG_PF) Document 1066312.1
Oracle Customer Releationship Management Release 12.1.3 (R12.CC_PF.B.DELTA.3) Patch 9249345 Customer Relationship Management (CC_PF) Document 1081433.1
Oracle Financials Release 12.1.3 (R12.FIN_PF.B.DELTA.3) Patch 9147733 Financials (FIN_PF) Document 1081425.1
Oracle Human Resource Management System Release 12.1.3 (R12.HR_PF.B.DELTA.3) Patch 9114911 Human Resources (HR_PF) Document 1081427.1
Oracle E-Business Suite Diagnostics Release 12.1.3 (R12.IZU.B.DELTA.3) Patch 9239092 E-Business Suite Diagnostics (IZU) Document 1081428.1
Oracle Procurement Release 12.1.3  (R12.PRC_PF.B.DELTA.3) Patch 9249354 Procurement (PRC_PF) Document 1081431.1
Oracle Projects Release12.1.3 (R12.PJ_PF.B.DELTA.3) Patch 9147711 Projects (PJ_PF) Document 1081434.1
Oracle Supply Chain Management Release 12.1.3 (R12.SCM_PF.B.DELTA.3) Patch 9245674 Supply Chain Management (SCM_PF) Document 1081459.1
Oracle Value Chain Planning Release 12.1.3 (R12.SCP_PF.B.DELTA.3) Patch 9245525 Oracle Value Chain Planning (SCP_PF) Document 1081461.1
Posted May 7, 2014 by balaoracledba.com in 11i/R12, OracleAppsR12, Upgrade

 

Upgrade R12- 10.1.3.4 Oracle IAS(WebOH) to 10.1.3.5:

==========================================

  

In this post I will list out the steps required to Upgrade R12 10.1.3.4 Oracle Home to 10.1.3.5.

 

Step 1

Download OracleAS 10g Release 3 (10.1.3) Patch Set 5 (10.1.3.5.0)

Download OracleAS 10g Release 3 (10.1.3) Patch Set 5 (10.1.3.5.0) delivered via Patch 8626084 from My Oracle Support

 

Step 2

Download Environment Preparation Patch for OracleAS 10g Release 3 (10.1.3) Patch Set 5 (10.1.3.5.0)

Download patch 8920270 from My Oracle Support .
Also download latest opatch 6880880  for 10.1.3
Step 3
Take a cold backup of 10.1.3 Oracle Home and Inventory
cp -pr 10.1.3 10.1.3_bkp
Step 4
Shut down the application Tier
Step 5
Set your environment (Remember dont run the regular application env file). Instead go to $INST_TOP/ora/10.1.3 and u will find and env with name <SID>_DomainName.env set the env using this env file.
1
Before Upgrade the version is as below
use$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -version
2


Step 6:
unzip p8626084_101350_LINUX.zip
cd Disk1
execute ./runInstaller.sh
3
Here if you are trying to upgrade on Linux64bit Machine you will face an error, i suggest run below command
On Linux 64bit:
Use the following command to specify 32bit shell emulation, before running the installer as detailed below:
$ linux32 bash
then again execute the ./runInstaller.sh
4
Choose Oracle Home 10.1.3( Make sure on process is running from this OH.) all the application services should be down.
5
6type password as oafm
8 9 10Ignore if you see any error messages
12
Ignore the error Messages.
13
try opmnctl startall from command prompt to see whether you are able to start it successfully or not. again stop using opmnctl stopall as we need to apply other patches.


Step 7:
Unzip patch 8920270 (required patch for OracleAS 10g Release 3 (10.1.3) Patch Set 5 (10.1.3.5.0)) into an alternative temporary directory.
15
Go through the Readme.txt
1) Make a backup of the file $ORACLE_HOME/config/run_opatch_generic.sh
 cd $ORACLE_HOME/config
 cp run_opatch_generic.sh run_opatch_generic.sh.pre8920270
2) Edit the $ORACLE_HOME/config/run_opatch_generic.sh and add the line
"-ocmrf <$ORACLE_HOME>/config/ias.properties" immediately after the -silent
switch. i.e.
#!/bin/sh
#
#opatch apply -silent -oh <ORACLE_HOME> -invPtrLoc <OH/oraInst.loc> <opatch dir>#
# This file is really for SOLARIS only currently other platforms are
# using separate files.
<oracle_home>/OPatch/opatch apply -silent -ocmrf <oracle_home>/config/ias.properties -oh <oracle_home> -invPtrLoc <oracle_home>/oraInst.loc <oracle_home>/opatches/4601861
if [ "$?" != "0" ]; then
echo "OPatch did not complete successfully..."
exit 1
fi
3) If this is a GUI install then retry the configuration assistant "Run One-off Generic OPatches". For a failure in the silent install
a) Set the LD_LIBRARY_PATH
$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib
b) Set the PERL5LIB
export PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1:$PERL5LIB
 c) Run the configToolCommands
cd $ORACLE_HOME/cfgtoollogs
./configToolCommands > /tmp/configtoolcommands.out
d) Review /tmp/configtoolcommands.out.


 Step 8
Run AutoConfig


Step 9
Apply post upgrade patches
16


Step 10 Post-upgrade Tasks
Step a: Regenerate appsborg.zip and appsborg2.zip files
Run adadmin, and choose Generate product JAR files (with the no force option) to generate the appsborg.zip and appsborg2.zip files.
Step b :Verify the OracleAS 10g Release 3 (10.1.3) Patch Set 5 (10.1.3.5) Installation
17
This finishes the upgrade of 10.1.3 to 10.1.3.5.