DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, August 20, 2013

Error "ORA-0000: normal, successful completion" during Gather Schema Statistics for AP Schema



ISSUE:
---------

When attempting to run the concurrent program Gather Schema Statistics for AP schema the following error occurs.


ERROR
----------
ORA-0000: normal, successful completion
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
In GATHER_SCHEMA_STATS , schema_name= AP percent= 10 degree = 8 internal_flag= NOBACKUP
Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=AP.JE_FR_DAS_010***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #2: ERROR: While GATHER_TABLE_STATS:
object_name=AP.JE_FR_DAS_010_NEW***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #3: ERROR: While GATHER_TABLE_STATS:
object_name=AP.JG_ZZ_SYS_FORMATS_ALL_B***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+


SOLUTION
---------------
Perform the below action plan and re-run the Gather Stats and verify the results

Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them.
Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.

– identify duplicate rows
select table_name, column_name, count(*)
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1;

– Use above results on the following SQL to delete duplicates
delete from FND_HISTOGRAM_COLS where table_name = ‘&TABLE_NAME’ and column_name = ‘&COLUMN_NAME’ and rownum=1;

– Use following SQL to delete obsoleted rows
delete from FND_HISTOGRAM_COLS
where (table_name, column_name) in
(
select hc.table_name, hc.column_name
from FND_HISTOGRAM_COLS hc , dba_tab_columns tc
where hc.table_name =’&TABLE_NAME’
and hc.table_name= tc.table_name (+)
and hc.column_name = tc.column_name (+)
and tc.column_name is null
);

Monday, August 19, 2013

Adpreclone.pl fails with RC-50007: Could not create cloning stage area

Issue :
adpreclone.pl fails with RC-50007: Could not create cloning stage area.

Can see the entire log below.

# Calling StageAppsTechStack...
Evaluated internal cloning stage: /application_r12/apps/apps_st/comn/clone
Creating Staging Area
Creating bin directory
UNZIP used: UnZip 5.50 of 17 February 2002, by Info-ZIP.  Maintained by C. Spieler.  Send
Copying /application_r12/apps/apps_st/appl/ad/12.0.0/admin/template/adxmlctx.tmp to /application_r12/apps/apps_st/comn/clone//context/apps/adxmlctx.tmp
Creating html directory
Creating jre directory
Directory /application_r12/apps/apps_st/comn/clone/jre already exists.
Attempting to delete: /application_r12/apps/apps_st/comn/clone/jre
Failed to copy JRE from /application_r12/apps/tech_st/10.1.3/appsutil/jdk/jre to /application_r12/apps/apps_st/comn/clone/jre
Cloning Stage Area already exists, updating existing area.
Creating jlib directory
Copied /application_r12/apps/tech_st/10.1.3/lib/xmlparserv2.jar to /application_r12/apps/apps_st/comn/clone/jlib/xmlparserv2.jar
Copied /application_r12/apps/apps_st/comn/java/classes/oracle/apps/ad to /application_r12/apps/apps_st/comn/clone/jlib/java/oracle/apps
Copied /application_r12/apps/tech_st/10.1.3/jdbc/lib/ojdbc14.jar to /application_r12/apps/apps_st/comn/clone/jlib/ojdbc14.jar
Copying file from /application_r12/apps/apps_st/appl/ad/12.0.0/bin to /application_r12/apps/apps_st/comn/clone/bin
Copy OUI from /application_r12/apps/tech_st/10.1.3/oui/jlib to /application_r12/apps/apps_st/comn/clone//jlib/oui/
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/xmlparserv2.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/srvm.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/OraInstaller.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/OraInstallerNet.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/share.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/oneclick.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/ewt3.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/ewt3-nls.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/jewt4.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/jewt4-nls.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/help4.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/help4-nls.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/InstHelp.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/InstImages.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/oracle_ice.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/swingaccess.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/ewt3-swingaccess.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/classes12.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/prereq.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/ouica.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/deinstall.jar
Copy OUI from /application_r12/apps/tech_st/10.1.3/oui/lib to /application_r12/apps/apps_st/comn/clone//oui/
Could not generate new Clone stage base area


StackTrace -
java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at oracle.apps.ad.clone.util.CloneProcessor.run(CloneProcessor.java:67)
        at java.lang.Thread.run(Thread.java:662)

Caused by: java.lang.Exception: RC-50007: Could not create cloning stage area at /application_r12/apps/apps_st/comn/clone/


Cause :
Improper permission of jre under $IAS_ORACLE_HOME

Solution:
1. change the permission to 755 for the folder $IAS_ORACLE_HOME/appsutil/jdk/jre
Take a backup of $IAS_ORACLE_HOME/appsutil/jdk/jre
change the permission
chmod -R 755 $IAS_ORACLE_HOME/appsutil/jdk/jre
2. Take a backup and remove the clone folder from $COMMON_TOP

3. Run adpreclone.pl and retest the issue.


Sunday, August 18, 2013

Points to look for while writing SQL Code

Avoiding the Pitfalls of Database Programming - this is a very good material which every SQL developer should read. I'm sure when each developer reads this they can connect with all the mistakes they have done till now while coding

http://edu.ercess.co.in/ebooks/SQL/SQL-Antipatterns-Avoiding-the-Pitfalls-of-Database-Programming.pdf




Friday, August 16, 2013

Paper Presentation in AIOUG - Sangam'13




  




We , From Doyensys , are going to present paper in AIOUG - Sangam'13.

Below are the presenter and paper to be presented.

Presenter: Somu Chockalingam (Director , Doyensys).
Session :36309
Session Title: New Features of Oracle EBS Rel 12.2 from DB and Techstack perspective


Presenter: Beena Thomas (Principal Consultant , Doyensys).
Session :36362
Session Title: New Features of PL/SQL in Oracle 11g

 

Thursday, August 1, 2013

ORA-10873: file 1 needs to be either taken out of backup mode or media recovered

Scenario :

Database went down due to power fluctuation and during that time hot backup was running.So once we started the database we face the following issue.

Error:


ORA-10873: file 1 needs to be either taken out of backup mode or media recovered
ORA-01110: data file 1: '/system/PROD/system01.dbf'

Cause:

Database in backup mode while it went down.

Solution:

Step 1 : Sqlplus  ‘/as sysdba’
Step 2 : startup mount
Step 3 : Check backup file using below command.
            select * from V$BACKUP
Step 4 : The following command can be used to take all of the data files out of hot backup mode:
            ALTER DATABASE END BACKUP;
Step 5 : Alter database open