Archive for July, 2008

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

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

Active Users concurrent program never completes after clone

Posted on July 28, 2008. Filed under: Apps Technology Stack, Configuration, DBA/Admin Tips, Diagnostics, R12, TroubleShooting | Tags: , , |

Recently we did a clone (R12). Everything worked as expected, except for Active Users Concurrent program which never completed when submitted. The status remains Running – Normal for hours together. But “Prints environment variable values” concurrent program completed within no time.

This is how the issue was approached and resolved.

1. This is not a concurrent manager (FNDLIBR) issue, since the concurrent managers came up without any problem. – This eliminates concurrent manager to be part of the problem.
2. How about running some other concurrent program viz., Users of a responsibility or Active Users of a Responsibility. Same behaviour as Active Users – request never completes.

3. Now it is clear that the problem is with concurrent programs which has (Oracle Reports) .rdf defined as an executable, since “Prints environment variable values” completed sucessfully.

Thinking it could be a clone issue, i recompiled all the reports using adadmin, still the same issue.

To get more information, i enabled trace for Active users concurrent program (How to enable trace)and resubmitted it. Strange, it didnt even generate a trace file, but a session had been established on the database. Checking for the sql being run by the session, returns nothing.

hmmm now what …

4. Decided to run the report manually from the command prompt. (How to)

Atlast ….

Error: [cache directory location] does not exist.

Where is this defined ?

Checking $INST_TOP/ora/10.1.2/reports/conf/rwbuilder.conf revealed that the cacheDir value defined (same as the missing directory) in this file did not physically exists on the server. Created the cache directory and

Re-ran the report manually from command prompt, Guess what, the active users concurrent request completed sucessfully and even generated a trace file.

cheers,
OraclePitStop.

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

How to execute / run a report manually from command prompt

Posted on July 28, 2008. Filed under: DBA/Admin Tips, R12, TroubleShooting | Tags: , |

Eg. Active Users

rwrun report=FNDSCURS.pdf userid=apps/[passwd] desformat=xml destype=file desname=AU.xml

cheers,
OraclePitStop

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

How to enable / generate trace for a concurrent program

Posted on July 28, 2008. Filed under: DBA/Admin Tips, Diagnostics, R12 | Tags: , , |

Login as Sysadmin -> System Administrator -> Concurrent : Program -> Define
Press F11 -> Enter the COncurrent Program Name (Eg. Active Users) -> Press Ctrl + F11
Enable the check box “Enable Trace” above “Copy To” button.
Click on Save.
Close the window.
Select Requests -> Run
Submit the concurrent request.

After completion or during execution of the request, you should see a trace file generated on the database server under udump directory.

SQL to identify the trace file

select oracle_process_id from fnd_concurrent_requests where request_id=[request_id];

DB Node
cd $ORACLE_HOME/admin/[SID]_[hostname]/udump
ls -latr *[oracle_process_id]*.*

P.S: Dont forget to disable the trace 🙂

cheers,
OraclePitStop.

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

How to submit a concurrent program from command / shell prompt

Posted on July 28, 2008. Filed under: DBA/Admin Tips, Operating System, R12 | Tags: , , , , , |

We can submit concurrent programs from OS command / shell prompt using CONCSUB utility. Heres the full length syntax:

CONCSUB <APPS username>/<APPS password> \
<responsibility application short name> \
<responsibility name> \
<username> \
[WAIT=N|Y|<n seconds>] \
CONCURRENT \
<program application short name> \
<program name> \
[PROGRAM_NAME=”<description>”] \
[REPEAT_TIME=<resubmission time>] \
[REPEAT_INTERVAL= <number>] \
[REPEAT_INTERVAL_UNIT=< resubmission unit>] \
[REPEAT_INTERVAL_TYPE=< resubmission type>] \
[REPEAT_END=<resubmission end date and time>] \
[NLS_LANGUAGE=<language of the request>] \
[NLS_TERRITORY=<territory of the request>] \
[START=<date>] \
[IMPLICIT=< type of concurrent request> \
[<parameter 1> … <parameter n>]

Examples:

Active Users

CONCSUB apps/[apps_pwd] SYSADMIN “System Administrator” SYSADMIN WAIT=N CONCURRENT FND FNDSCURS

Prints Environment Variables

CONCSUB apps/[apps_pwd] SYSADMIN “System Administrator” SYSADMIN WAIT=N CONCURRENT FND FNDPRNEV APPL_TOP

cheers,
OraclePitStop

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

Ad-hoc tips (fbackup/frecover commands in HP-UX)

Posted on July 22, 2008. Filed under: DBA/Admin Tips |

We use HP-UX for running one of our 11i instances and i manage the backups using fbackup and frecover commands. I am sharing the syntax for some of the basic commands. Hope it would help.

======== Fbackup =========

(1)
Create a defaults file (eg, defaults.prod) and include/exclude file systems to be backed up/excluded.

$ cat defaults.prod
i /u02/oracle
e /u02/oracle/proddb/8.1.6
i /u07/oracle/proddata

This will backup all of /u02/oracle but would skip proddb/8.1.6

(2)
Create a configuration file (eg, config.prod) and define configuration parameters for the backup

$cat config.prod

blocksperrecord 256
records 32
checkpointfreq 1024
readerprocesses 6
maxretries 5
retrylimit 5000000
filesperfsm 2000

(3) Use following simple fbackup command

fbackup -f <device name, eg /dev/rmt/c5t2d0BESTb>  -uv0g defaults.prod -c config.prod
===========Frecover ===========

frecover -r -v -f <device name>  ====> For a full restore

frecover -x -e <directories/filenames to exclude> -f <device name> ===> For excluding certain files during extract !!

frecover -x -i /u01/oracle/prodappl/fnd/11.5.0/log/l4043065.req -f /dev/rmt/c5t4d0BESTb  ==> To extract one file !!

Thanks,

OPS team

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

How to migrate responsibility / menu / form (personalizations) from one ERP apps (11i / R12) instance to another using FNDLOAD?

Posted on July 20, 2008. Filed under: Custom, DBA/Admin Tips, R12 | Tags: , , , |

Below are the commands

===========================
RESPONSIBILITY
===========================

For migrating a responsibility we need the responsibility key value.

Steps to get the RESPONSIBILITY KEY VALUE

Login as Sysadmin
System Administrator -> Security : Responsibility -> Define
Press F11 -> Enter the Responsibility Name -> Get the responsibility key value from the Responsibility Key Text Field.

To Download

FNDLOAD apps/[APPS_PWD] 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct [give some name].ldt FND_RESPONSIBILITY RESP_KEY=”[RESPONSIBILITY_KEY_VALUE]”

To Upload

FNDLOAD apps/[APPS_PWD] 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct [name given above].ldt

===========================
MENU
===========================

To Download

FNDLOAD apps/[APPS_PWD] 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct [give some name].ldt MENU MENU_NAME=”[MENU_NAME]”

To Upload

FNDLOAD apps/[APPS_PWD] 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct [name given above].ldt

=======================
FORMS PERSONALIZATIONS
=======================

For migrating the forms personalizations we need the function name that is associated with the form.

Steps to get the form function name (eg. Users Form)

Login as Sysadmin
System Administrator -> Security : User -> Define
Once the form opens up
On the Tool Bar Click Help -> Diagnostics -> Custom Code -> Personalize
This will open a new form with function name. This is the value we require.

You can perform simillar step for any form that you want to migrate by opening the form to be migrated.

To Download

FNDLOAD apps/[APPS_PWD] 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct [give some name].ldt FND_FORM_CUSTOM_RULES function_name=”[FUNCTION_NAME]”

To Upload

FNDLOAD apps/[APPS_PWD] 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct [give some name].ldt
=======================
PRINTER DEFINITIONS
=======================

You can follow this link for migrating printer definitions.

Happy Migrating !!!

cheers,
OraclePitStop.

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

Ad-hoc Tips (HIT and MISS Ratios)

Posted on July 17, 2008. Filed under: Scripts |

Got few useful scripts to check HIT and MISS ratios… Wanted to share the same. Check out below scripts.

prompt
prompt =========================
prompt DATA DICTIONARY HIT RATIO
prompt =========================
prompt Should be higher than 90 else increase shared_pool_size in init.ora prompt

column “Data Dict. Gets” format 999,999,999
column “Data Dict. cache misses” format 999,999,999
select sum(gets) “Data Dict. Gets”,
sum(getmisses) “Data Dict. cache misses”,
trunc((1-(sum(getmisses)/sum(gets)))*100) “DATA DICT CACHE HIT RATIO”
from v$rowcache;

prompt
prompt =========================
prompt LIBRARY CACHE MISS RATIO
prompt =========================
prompt (If > 1 then increase the shared_pool_size in init.ora)
prompt
column “LIBRARY CACHE MISS RATIO” format 99.9999
column “executions” format 999,999,999
column “Cache misses while executing” format 999,999,999
select sum(pins) “executions”, sum(reloads) “Cache misses while executing”,
(((sum(reloads)/sum(pins)))) “LIBRARY CACHE MISS RATIO”
from v$librarycache;

prompt
prompt =========================
prompt Library Cache Section
prompt =========================
prompt hit ratio should be > 70, and pin ratio > 70 . . .
prompt

column “reloads” format 999,999,999
select namespace, trunc(gethitratio * 100) “Hit ratio”,
trunc(pinhitratio * 100) “pin hit ratio”, reloads “reloads”
from v$librarycache;

Thanks,

OraclePitStop Team

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

Ad-hoc tips (High Water Mark)

Posted on July 16, 2008. Filed under: DBA/Admin Tips |

This is another tip which i used recently.

Whenever, we get alerted about a tablespace reaching threshold levels, our immediate action would be to add a datafile. In some cases, we can resize/shrink the datafiles of the tablespaces to gain space. This can be done by knowing the high-water mark of the datafile.

Metalink Note : 130866.1 has a script that can be run against a database to know HWM of ALL datafiles of ALL tablespaces.

Note: Edit the script to change “binary_integer” declaration to “number”.

Output will be like below

=================
Tablespace:  BIGPARTYWEEK1 Datafile: /ots1/app/oracle/product/8.1.7/dbs/bw1.dbf
Can be resized uptil: 49152 Bytes, Actual size: 1040384
.
Tablespace:  IFS_LOB_N Datafile: /ots3/oradata/v817/oradata/v817/ifs_lob_n.dbf
Can not be resized, no free space at end of file.
==================

I ran this script against my Apps database and did shrink few datafiles
from SYSTEM and other tablespaces.

PS: Since this calculates for ALL datafiles, it took around 5 mts
in my 11i10 instance.

Hope this tip was useful.

Thanks,
OraclePitStop Team

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

Ad-hoc Tips (Database Links)

Posted on July 16, 2008. Filed under: DBA/Admin Tips |

Hope the Apps DBA community is doing great and is (as always) ever busy.This post is a small tip that might be of use.

Occasionally,in small companies (and most often in big companies),
there will be a necessity to create DB links between Apps database
and some legacy/standalone Oracle database.

We obviously don’t want them to
connect to our database as “apps” user via a DB link which is highly
insecure. So,in this case, it is a good idea to understand the list
of custom/apps objects that the legacy database need access to and
grant privileges ONLY to those objects. Here are the steps that we followed.

–> Got the list of objects (custom/standard) they need access to
–> Created a new database user for them
–> Grant SELECT on the objects to the new user
–> As the new user, create synonyms for the apps/custom objects.
–> Gave this new user (and tnsnames entry) to the other DBA team

This way, you can secure your objects against the misuse of DB links.

Other better ways? Please comment !!!

Thanks,
OraclePitStop team

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

« Previous Entries

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