Discoverer

Database Replace Function to change profile option values

Posted on July 30, 2008. Filed under: Configuration, Database, DBA/Admin Tips, Discoverer, R12, Scripts, TroubleShooting | Tags: , , |

Heres an example of using the replace() to update profile_option_value column in fnd_profile_option_values table.

Scenario: Imagine you did a clone from DEV to TEST and in DEV there were lot of profile option values set at the responsibility and user level for the discoverer viewer url which came as is to TEST. Now you want to change the values in fnd_profile_option_values to refer to TEST.

DEV Discoverer Viewer URL: https://discodev.mydomain.com/discoverer/viewer?Connect=%5BAPPS_
SECURE]

TEST Discoverer VIewer URL: https://discotest.mydomain.com/discoverer/viewer?Connect=%5BAPPS_SECURE%5D

SQL to change the values

1. create table fnd_povbk as select * from fnd_profile_option_values; [  backup just in case you mess up ]

update fnd_profile_option_values set profile_option_value = replace(profile_option_value,’https://discodev.mydomain.com’,’https://discotest.mydomain.com’) where profile_option_value like ‘https://discodev.mydomain.com%’;

TADA !!! values replaced.

cheers,
OraclePitStop

Read Full Post | Make a Comment ( None so far )

What is EUL_DUMVW_YEEHA ?

Posted on August 1, 2007. Filed under: Discoverer |

In our Production 11i environment, we have setup database
read-only roles for all the developers that has limited
access to APPS schema objects. This way, only the DBA team
can perform code migrations in PROD since they have the
APPS password.

The read-only role just has “select” on ALL of APPS’s
views and synonyms. To incorporate future views and
synonyms, there is also a trigger created
to grant select on any views/synonyms that gets created
in APPS schema to the read-only role. The trigger
would run a database job to execute this task.

Trigger looks like…

***********
l_str := ‘execute immediate “grant select on ‘ || ora_dict_obj_name || ‘ to apps_ro”;’;
dbms_job.submit(l_job, REPLACE(l_str, ‘”‘, ””));
***********

Given the above scenario, when i was reviewing the alert.log
i found messages like below.

**********
Errors in file < bdump location > /prod_j000_29503.trc
ORA-12012: error on auto execute of job 1688
ORA-00942: table or view does not exist
ORA-06512: at line 1
**********

The trace file didn’t have much info so i looked at
the dba_jobs table. The ‘what’ column had

*********
execute immediate “grant select on EUL_DUMVW_YEEHA to apps_ro”
*********

Initially i ignored it as a one-off incident and removed the
job from the dba_jobs table.

exec dbms_job.remove(job=>1688);

However, the job kept resubmitting itself. So,i had to fix this.
I didn’t have a clue of the object “EUL_DUMVW_YEEHA” other than the
obvious fact that it was related to discoverer. The object as such
does not exist in our APPS schema at all.
I googled for this object and didnt get any links !! Hence this post 🙂

I searched in metalink and realized it is a dummy view created
when using Disco 3i (as in our case) and hence my trigger picks this up
to grant select on my APPS read only role.
For now, i have added a line in the trigger to ignore this object.

If you have more ideas on this object, why it gets created etc, please leave a comment.

Thanks,
OraclePitstop team.

Read Full Post | Make a Comment ( 1 so far )

Discoverer4i Troubleshooting

Posted on July 26, 2007. Filed under: Discoverer |

Below are the errors which we had encountered post upgrade of 
Discoverer4i wherein the discoverer component doesn't startup. 
This post will be a running document, wherein it will be updated 
as and when we encounter a new issue and obviously a solution 
to that 🙂

First -> Make sure Jserv is up and running. 

Issue 1 
OAD Fatal Error in oad.log

$ORACLE_HOME/discwb4/util > vi oad.log
               "oad.log" 3 lines, 184 characters
               OAD: Fatal Error [Cross-device link].

Solution

Make sure VBROKER_ADM environment variable is set up 
correctly.


Issue 2

Unable to create repository file: 
$ORACLE_HOME/vbroker/adm/impl_dir/impl_rep. in oad.log

Solution

Add this line in $ORACLE_HOME/discwb4/discwb4.sh
            TMPDIR=/dchpgi/applmgr/product/806/discwb4/tmp 
            export TMPDIR


Issue 3

DISCOVERER VIEWER is not working.
Abstract: DISCOVERER VIEWER DOES NOT WORK WITH THE 
PARAMETER "SHOW_GRAPHS=TRUE" 

Solution

The file $IAS_ORACLE_HOME/Apache/Apache/htdocs/
disco4iv/html/disco4iv.html has to have the entry 
show_graphs="false" instead of show_graphs="true". 
This is the fix and the relevant bug is 2664219.


Issue 4

DISCOVERER VIEWER  Page is blank and also hanging for long time

Solution

Correct the entry for the TNS in discwb.sh to point to correct 
TNS_ADMIN value. 
echo $TNS_ADMIN can be used to get the value.
Bounce the apache and start the services.


Issue 5

INTERNAL SERVER ERROR on Viewer Page

Solution

Check these values
wrapper.classpath=$IAS_ORACLE_HOME/Apache/Apache/htdocs/
disco4iv/disco4iv.jar was added in  viewer4i.properties file  
before the translator.zip

It should be in two separate lines

wrapper.classpath=$IAS_ORACLE_HOME/Apache/Apache/htdocs/
disco4iv/disco4iv.jar
wrapper.classpath=$IAS_ORACLE_HOME/sqlj/lib/translator.zip


Issue 6

Unable to find the Locator.ior file. Error message in 
Discoverer viewer page

Solution

vi $IAS_ORACLE_HOME/Apache/Apache/htdocs/disco4iv/html/
disco4iv.xml
Locator name="Hostname.domainname_PortOracleDiscoverer
Locator4" 
path="$IAS_ORACLE_HOME/Apache/Apache/htdocs/discwb4/
applet/"/>

Modify the Alias (file or path) to the Real URL:

$IAS_ORACLE_HOME//Apache/Apache/htdocs/disco4iv/html/
disco4iv.xml
servlet.Viewer.code=oracle.discoiv.Disco4iv
servlet.Viewer.initArgs=config=file:$IAS_ORACLE_HOME/
Apache/Apache/htdocs/disco4iv/html/disco4iv.xml


Issue 7

Discoverer BIS report not working.

Solution

To resolve errors accessing Discoverer BIS report 
(login as SYSADMIN, on Selfservice application -> 
Disco BIS Report)
Add FND_DEBUG_DIR=$APPLCSF/tmp ; 
export FND_DEBUG_DIR in $ORACLE_HOME/discwb4/discwb.sh 
and bounce discoverer services.


Issue 8

Unable to start locator session while starting discoverer services

Solution

Go to $ORACLE_HOME/discwb4
Verify values in discwb4.sh
vi discwb4.sh
INSTANCE_NAME=Hostname.DomainName_Port
TMPDIR=$ORACLE_HOME/discwb4/tmp
Export TMPDIR
Save and exit the file
cd $VBROKER_ADM
pwd
$ORACLE_HOME/vbroker/adm
cd impl_dir
mv impl_rep impl_rep_bkp
mv impl_rep~ impl_rep~_bkp
Run Registerall.sh will create these new folders.
ls
README README.txt impl_rep impl_rep_bkp impl_rep~ 
impl_rep~_bkp


Issue 9

Unable to start Gatekeeper services

Solution

Go to $IAS_ORACLE_HOME/Apache/htdocs/discwb4/applet 
directory
Check exterior_port in 
$ORACLE_HOME/vbroker/bin/gatekeeper.properties and 
$APACHE_TOP/Apache/htdocs/discwb4/applet/
gatekeeper.properties
Check the gatekeeper.log file for errors like 
'unable to bind to port' or other errors.
Fix the error and restart the gatekeeper.



cheers,
OraclePitStop Team
Read Full Post | Make a Comment ( None so far )

Liked it here?
Why not try sites on the blogroll...