11g: Bulk Insert Example

I happen to work recently on a scenario where I had to inset close to 46 Million records . I had to perform a BULK COLLECT (Insert) script so that I dont get ORA-01555: Snapshot too old error.

DECLARE
cursor c1 is
SELECT *
FROM DS.TIME_TEMP_TBLE;

TYPE TYPE_TB1 IS TABLE OF DS.TIME_TARGET_TBLE%ROWTYPE;
TARG_TB1 TYPE_TB1;

l_limit number := 1000000;
BEGIN

OPEN c1;
LOOP
                Fetch c1 BULK COLLECT INTO TARG_TB1 LIMIT l_limit;

                FORALL i IN 1..TARG_TB1.COUNT
                insert into DS.TIME_TARGET_TBLE
                values TARG_TB1(i);
 COMMIT;
        EXIT WHEN c1%ROWCOUNT < l_limit;
END LOOP;
CLOSE c1;
END;
/
COMMIT;
EXIT;

Other different options are to use FOR instead of  FORALL, or use

FORALL i IN TARG_TB1.first..TARG_TB1.last 

From my testing, I felt that using COUNT was faster.

R12.x - ORA-20100: Error: FND_FILE failure. Unable to create file, lxxxxxxx.tmp in the directory, /yyy/zzzz/abc

Concurrent Request Set and Progam results in the following error:

ORA-20100: Error: FND_FILE failure. Unable to create file, lxxxxxx.tmp in the directory, /yyy/zzzz/abc

Oracle Applications produces temporary PL/SQL output files used in concurrent processing. These files are written to a location on the database server node specified by the APPLPTMP environment setting. As the temporary files placed in this directory may contain context sensitive information, it should be a secure directory on the database server node with read and write access for the database server owner. In a multi-node system, the directory defined by APPLPTMP does not need to exist on the application tier servers. In my environment, APPLPTMP= /u03/sqlcom/outbound.

There are many scenarios for this error and I will try to visit most of the cases here.

1. Check the location for APPLPTMP  and confirm that is not 100% full in storage.

2. Set APPLTMP to the first directory in utl_file_dir parameter as FND_FILE writes to the first directory in utl_file_dir.

 SELECT value FROM  v$parameter WHERE name = 'utl_file_dir';

3.  Database Read/Write Check :  If you can write a file using UTL_FILE, which is used by FND_FILE.

set serveroutput on 
DECLARE 
  file_location VARCHAR2(256) := '<first entry on utl_file_dir>'; 
  file_name VARCHAR2(256) := 'utlfile_TEST1.lst'; 
  file_text VARCHAR2(256) := 'THIS IS A TEST'; 
  file_id UTL_FILE.file_type; 
BEGIN 
  file_id := UTL_FILE.fopen(file_Location, file_name, 'W'); 
  UTL_FILE.put_line(file_id, file_text); 
  UTL_FILE.fclose(file_id); 
EXCEPTION 
  WHEN UTL_FILE.INVALID_PATH 
  THEN dbms_output.put_line('Invalid path ' || SQLERRM); 
    WHEN OTHERS 
  THEN dbms_output.put_line('Others '|| SQLCODE || ' ' || SQLERRM); 
END; 
/

If this fails, the issue is on database.

4. Check if you can write a file directly using FND_FILE, which is the package used by the Application. From sqlplus, connected as the apps user, run:

exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST');

If this test works, it would indicate that FND_FILE is working fin.

5. Make sure the file that the concurrent program is trying to create already exists on the APPLPTMP location. Also please verify the permissions on the APPLPTMP directory.

6. If still having problems, your SPFILE may be corrupt or just restart the database. 

 - Shutdown all the Application Services

 - Verify the spfile for the utl_file_dir parameter

 - Restart the database

7. If you decide to change the location of APPLPTMP, Make sure to run autoconfig after changing the APPLPTMP value in the  autoconfig context file.

Reference:

Concurrent Processing - Troubleshooting Concurrent Request ORA-20100 errors in the request logs (Doc ID 261693.1)

R12.2.x Rapid Clone: Application configuration 'perl adcfgclone.pl appsTier' : Oracle Homes are unregistered from the global inventory

Performing Rapid Clone on the application server resulted in an error within few minutes that I triggered 'adcfgclone.pl'. The issue is with Inventory.

$ cd <COMMON_TOP>/clone/bin
$ perl adcfgclone.pl appsTier

Please note that 'fs2' is my RUN Edition in my environment.

Error from adcfgclone.pl:
......
FMW Pre-requisite check log file location : /wms/app/oracle/fs2/EBSapps/comn/clone/FMW/logs/prereqcheck.log

Running: /wms/app/oracle/fs2/EBSapps/comn/clone/FMW/t2pjdk/bin/java -classpath /wms/app/oracle/fs2/EBSapps/comn/clone/prereq/webtier/Scripts/ext/jlib/engine.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/prereq/webtier/oui/jlib/OraPrereq.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/prereq/webtier/oui/jlib/OraPrereqChecks.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/prereq/webtier/oui/jlib/OraInstaller.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/prereq/webtier/oui/jlib/OraInstallerNet.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/prereq/webtier/oui/jlib/srvm.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/prereq/webtier/Scripts/ext/jlib/ojdl.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/prereq/webtier/Scripts/ext/jlib/ojdl2.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/prereq/webtier/Scripts/ext/jlib/ojdl-log4j.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/prereq/webtier/oui/jlib/xmlparserv2.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/prereq/webtier/oui/jlib/share.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/jlib/java oracle.apps.ad.clone.util.FMWOracleHomePreReqCheck -prereqCheckFMW -e /wms/app/oracle/fs2/inst/apps/BERRY_PINK1/appl/admin/BERRY_PINK1.xml -stage /wms/app/oracle/fs2/EBSapps/comn/clone -log /wms/app/oracle/fs2/EBSapps/comn/clone/FMW/logs/prereqcheck.log

Beginning application tier Apply - Fri Jan  9 10:11:06 2015

/wms/app/oracle/fs2/EBSapps/comn/clone/bin/../jre/bin/java -Xmx600M -DCONTEXT_VALIDATED=true -Doracle.installer.oui_loc=/oui -classpath /wms/app/oracle/fs2/EBSapps/comn/clone/jlib/xmlparserv2.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/jlib/ojdbc6.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/jlib/java:/wms/app/oracle/fs2/EBSapps/comn/clone/jlib/oui/OraInstaller.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/jlib/oui/ewt3.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/jlib/oui/share.jar:/wms/app/oracle/fs2/FMW_Home/webtier/../Oracle_EBS-app1/oui/jlib/srvm.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/jlib/ojmisc.jar:/wms/app/oracle/fs2/FMW_Home/wlserver_10.3/server/lib/weblogic.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/jlib/obfuscatepassword.jar  oracle.apps.ad.clone.ApplyAppsTier -e /wms/app/oracle/fs2/inst/apps/BERRY_PINK1/appl/admin/BERRY_PINK1.xml -stage /wms/app/oracle/fs2/EBSapps/comn/clone    -showProgress -nopromptmsg
Log file located at /wms/app/oracle/fs2/inst/apps/BERRY_PINK1/admin/log/clone/ApplyAppsTier_01091011.log
  |      0% completed
ERROR while running Apply...
Fri Jan  9 10:11:08 2015

 ERROR: Failed to execute /wms/app/oracle/fs2/EBSapps/comn/clone/bin/adclone.pl

 Please check logfile.
[applwms@PINK1 bin]$

Logfile (ApplyAppsTier_01091011.log):

#############################################################
Started ApplyAppsTier at Fri Jan 09 10:11:07 CST 2015
Version:
        ApplyAppsTier.java      :       120.11.12020000.6
#############################################################

Below Oracle Homes are already registered in the global inventory:
/wms/app/oracle/fs2/FMW_Home/Oracle_EBS-app1
/wms/app/oracle/fs2/FMW_Home/webtier
/wms/app/oracle/fs2/FMW_Home/oracle_common

Exiting Cloning...

Ensure that the above Oracle Homes are unregistered from the global inventory "/wms/app/oracle/oraInventory/ContentsXML/inventory.xml" and re-run adcfgclone.pl script

Solution:
Go to /wms/app/oracle/fs2/EBSapps/10.1.2/oui/bin (or $ORACLE_HOME/oui/bin) and run the following to unregister the Oracle Homes from the inventory.

$ cd $ORACLE_HOME/oui/bin
$ ./runInstaller -silent -deinstall REMOVE_HOMES={"/wms/app/oracle/fs2/FMW_Home/Oracle_EBS-app1"}
$ ./runInstaller -silent -deinstall REMOVE_HOMES={"/wms/app/oracle/fs2/FMW_Home/oracle_common"}
$ ./runInstaller -silent -deinstall REMOVE_HOMES={"/wms/app/oracle/fs2/FMW_Home/webtier"}

You may have to do the same when you build PATCH Edition (fs1 in my case).

R12.2.x - Application Clone 'perl adcfgclone.pl appsTier' : ..clone/FMW/pasteBinary.sh results in ERROR: Script failed, exit code 255

While performing rapid clone of R12.2.3 environment, I faced an error while running adcfgclone.pl. I performed the below step and it resulted in error :

$ cd <COMMON_TOP>/clone/bin
$ perl adcfgclone.pl appsTier

Please note that 'fs2' is the RUN Edition on my environment.
Error from adcfgclone.pl:
..


Beginning application tier Apply - Fri Jan 16 10:59:52 2015

/wms/app/oracle/fs2/EBSapps/comn/clone/bin/../jre/bin/java -Xmx600M -DCONTEXT_VALIDATED=true -Doracle.installer.oui_loc=/oui -classpath /wms/app/oracle/fs2/EBSapps/comn/clone/jlib/xmlparserv2.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/jlib/ojdbc6.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/jlib/java:/wms/app/oracle/fs2/EBSapps/comn/clone/jlib/oui/OraInstaller.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/jlib/oui/ewt3.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/jlib/oui/share.jar:/wms/app/oracle/fs2/FMW_Home/webtier/../Oracle_EBS-app1/oui/jlib/srvm.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/jlib/ojmisc.jar:/wms/app/oracle/fs2/FMW_Home/wlserver_10.3/server/lib/weblogic.jar:/wms/app/oracle/fs2/EBSapps/comn/clone/jlib/obfuscatepassword.jar  oracle.apps.ad.clone.ApplyAppsTier -e /wms/app/oracle/fs2/inst/apps/BERRY_PINK1/appl/admin/BERRY_PINK1.xml -stage /wms/app/oracle/fs2/EBSapps/comn/clone    -showProgress -nopromptmsg
Log file located at /wms/app/oracle/fs2/inst/apps/BERRY_PINK1/admin/log/clone/ApplyAppsTier_01161059.log
  |      0% completed
ERROR while running Apply...
Fri Jan 16 11:04:01 2015

 ERROR: Failed to execute /wms/app/oracle/fs2/EBSapps/comn/clone/bin/adclone.pl

 Please check logfile.
[applwms@PINK1 bin]$

Log file (ApplyAppsTier_01161059.log): 

[applwms@PINK1 fmwT2PApply]$ cat /wms/app/oracle/fs2/inst/apps/BERRY_PINK1/admin/log/clone/ApplyAppsTier_01161059.log
#############################################################
Started ApplyAppsTier at Fri Jan 16 10:59:53 CST 2015
Version:
        ApplyAppsTier.java      :       120.11.12020000.6
#############################################################

START: Creating the FMW Home from ApplyAppsTier
FMW JDK version in use: 1.7.0_25

START: Creating FMW Home.
Running /wms/app/oracle/fs2/EBSapps/comn/clone/FMW/pasteBinary.sh -javaHome /wms/app/oracle/fs2/EBSapps/comn/util/jdk64 -al /wms/app/oracle/fs2/EBSapps/comn/clone/FMW/FMW_Home.jar -tl /wms/app/oracle/fs2/FMW_Home -invPtrLoc /etc/oraInst.loc -ldl /wms/app/oracle/fs2/inst/apps/BERRY_PINK1/admin/log/clone/fmwT2PApply -silent true -executeSysPrereqs false
Script Executed in 247686 milliseconds, returning status 255
ERROR: Script failed, exit code 255
[applwms@PINK1 fmwT2PApply]$

This doesn't help the error we are getting much unless you check CLONE2015-01-16_10-59-54AM.log and CLONE2015-01-16_10-59-54AM.error files. These files are located on /wms/app/oracle/fs2/inst/apps/BERRY_PINK1/admin/log/clone/fmwT2PApply.

Log file (/wms/app/oracle/fs2/inst/apps/BERRY_PINK1/admin/log/clone/fmwT2PApply/CLONE2015-01-16_10-59-54AM.log): 

INFO : Jan 16, 2015 10:59:55 AM - CLONE-21012   The archive will be restored fully at /wms/app/oracle/fs2/FMW_Home.
INFO : Jan 16, 2015 10:59:55 AM - CLONE-21104   The number of Oracle homes and Middleware homes present in the archive is 3 and 1 respectively.
INFO : Jan 16, 2015 10:59:57 AM - CLONE-21060   No system prerequisites will be executed for the Oracle home /wms/app/oracle/fs2/FMW_Home/oracle_common/ as it is set to false.
INFO : Jan 16, 2015 10:59:57 AM - CLONE-21060   No system prerequisites will be executed for the Oracle home /wms/app/oracle/fs2/FMW_Home/webtier/ as it is set to false.
INFO : Jan 16, 2015 10:59:57 AM - CLONE-21060   No system prerequisites will be executed for the Oracle home /wms/app/oracle/fs2/FMW_Home/Oracle_EBS-app1/ as it is set to false.
INFO : Jan 16, 2015 10:59:57 AM - CLONE-21156   Extracting all the homes from the archive file. Extraction time will vary depending upon the archive size ...
INFO : Jan 16, 2015 10:59:57 AM - CLONE-21157   The size of the all homes are 5.117 GB.
INFO : Jan 16, 2015 11:00:11 AM - CLONE-21126   Operation is 13% complete, in "14" seconds.
INFO : Jan 16, 2015 11:00:38 AM - CLONE-21126   Operation is 20% complete, in "40" seconds.
INFO : Jan 16, 2015 11:01:25 AM - CLONE-21126   Operation is 30% complete, in "87" seconds.
INFO : Jan 16, 2015 11:01:47 AM - CLONE-21126   Operation is 40% complete, in "110" seconds.
INFO : Jan 16, 2015 11:02:07 AM - CLONE-21126   Operation is 50% complete, in "130" seconds.
INFO : Jan 16, 2015 11:02:43 AM - CLONE-21126   Operation is 60% complete, in "165" seconds.
INFO : Jan 16, 2015 11:03:14 AM - CLONE-21126   Operation is 70% complete, in "196" seconds.
INFO : Jan 16, 2015 11:03:51 AM - CLONE-21126   Operation is 80% complete, in "233" seconds.
INFO : Jan 16, 2015 11:04:00 AM - CLONE-21025   Restoring Middleware home at "/wms/app/oracle/fs2/FMW_Home/" from archive "/wms/app/oracle/fs2/EBSapps/comn/clone/FMW/FMW_Home.jar" started ...
INFO : Jan 16, 2015 11:04:01 AM - CLONE-21037   Time taken to restore all cloners from archive was "243" seconds.
INFO : Jan 16, 2015 11:04:01 AM - CLONE-21006   Total time taken by T2P process was 245 seconds.


Error File (/wms/app/oracle/fs2/inst/apps/BERRY_PINK1/admin/log/clone/fmwT2PApply/CLONE2015-01-16_10-59-54AM.error) :

SEVERE : Jan 16, 2015 11:04:00 AM - ERROR - CLONE-20275   Insufficient space to create /tmp/CLONINGCLIENT5418759569442312041.
SEVERE : Jan 16, 2015 11:04:00 AM - CAUSE - CLONE-20275   Minimum required space was at least "1,773" MB, but only "897" MB was available for use.
SEVERE : Jan 16, 2015 11:04:00 AM - ACTION - CLONE-20275   Make sure that the minimum required space is available for use.
SEVERE : Jan 16, 2015 11:04:00 AM - ERROR - CLONE-20233   Restoring Middleware home for sourceid "mwhome1@FMW_Home" has failed.
SEVERE : Jan 16, 2015 11:04:00 AM - CAUSE - CLONE-20233   An internal operation failed.
SEVERE : Jan 16, 2015 11:04:00 AM - ACTION - CLONE-20233   Check the clone log and error file for more details.
SEVERE : Jan 16, 2015 11:04:00 AM - ERROR - CLONE-20237   Restoring the sourceid "mwhome1@FMW_Home" has failed.
SEVERE : Jan 16, 2015 11:04:00 AM - CAUSE - CLONE-20237   An internal operation failed.
SEVERE : Jan 16, 2015 11:04:00 AM - ACTION - CLONE-20237   Check the clone log and error file for more details.
SEVERE : Jan 16, 2015 11:04:01 AM - ERROR - CLONE-20218   Cloning is not successful.
SEVERE : Jan 16, 2015 11:04:01 AM - CAUSE - CLONE-20218   An internal operation failed.
SEVERE : Jan 16, 2015 11:04:01 AM - ACTION - CLONE-20218   Provide the clone log and error file for investigation.

Solution:
/tmp is used by Oracle rapidclone to extract and build FMW Home on the target. Please make sure you have you have at least 2 GB of /tmp space on the target server.

R12.1.x ASCP : The executable file /../../bin/MSCMON for this concurrent program cannot be executed. Contact your system administrator or support representative.

We are in the process of implementing Advanced Supply Chain Planning (ASCP) for our Oracle E-Business Suite (EBS).  The Snapshot Monitor program (MSCMON) resulted in the below error.

Error:
The executable file /p01/oracle/apps/apps_st/appl/msc/12.0.0/bin/MSCMON for this concurrent program cannot be executed. Contact your system administrator or support representative. Verify that the execution path to the executable.

Operating System: Linux x86-64 bit
EBS Version: R12.1.2 with ASCP patches R12.1.3

Issue: Some of the executable were missing like MSCCPP, MSCMON, MSCNEW, MSCNSP, MSCNSPNM, MSCPCL, MSCPCP, MSCPDW, MSCPRG, MSCSDW, MSCSLD and MSCXGCAL from $MSC_TOP/bin. MSONEW was missing from $MSO_TOP/bin.

Solution:
Relink the MSC and MSO modules using adadmin and verify the executable are present.

ADADMIN
 - 2. Maintain Applications Files menu
    - 1. Relink Applications programs
       Do you wish to proceed with the relink [Yes] ?
       Enter the name of your Oracle Applications environment file below.
       File name [APPLE_vaidiyan1.env] :
       Enter list of products to link ('all' for all products) [all] : MSC
       Generate specific executables for each selected product [No] ? Yes
       Relink with debug information [No] ?
       Enter executables to relink, or enter 'all' [all] :

Repeat the same process for MSO.

In my case, adrelink failed for the MSC executables.

Error:

/p01/oracle/apps/apps_st/appl/mso/12.0.0/lib/libmso.a(msopomresconsume.o)
collect2: ld returned 1 exit status
make: *** [/p01/oracle/apps/apps_st/appl/msc/12.0.0/bin/MSCMON] Error 1
Done with link of msc executable 'MSCMON' on Mon Dec 15 12:15:27 CST 2014

Relink Solution:

This is a Bug 9907575: CHANGE ADRELINKNEW.SH FOR RELINK APS EXE ON OEL/RH 5.

1. Backup $AD_TOP/bin/adrelinknew.sh and open the file.
2. Search the string below in the LINUX section and modify it appropriately

CPP_LDFLAGS=' -L$(ORACLE_HOME)/lib -L$(ORACLE_HOME)/lib/stubs -lclntsh'
 
TO

CPP_LDFLAGS=' -L$(ORACLE_HOME)/lib -L$(ORACLE_HOME)/lib/stubs -lclntsh -Wl,--noinhibit-exec' 

3. Now relink MSC/MSO/MSR executables with adadmin.

Reference:
MSCNSP Memory Based Snapshot Errors Concurrent Program Cannot Be Executed (Doc ID 562672.1)
Unable to relink MSC/MSO executables on OEL/RHEL5 and also RedHat 4.9 (Doc ID 1273390.1)
ADRELINK of MSC and MSO executables fail (Doc ID 1492763.1)
MSC Relinking Error on Linux OEL5/RHEL5, Relink of module "MSCCPP" failed, "FEMCCE","MSCNEW","MSCMON". There was No MSC Relink Problem when Worked on RHEL 4 (Doc ID 1128486.1)    To Bottom   
R12: MSC Modules Error Out When Relinking with Adrelink (Doc ID 1345788.1)