Archive for May, 2008

SQL Script to generate privileges to be granted

Posted on May 29, 2008. Filed under: DBA/Admin Tips, Scripts, User | Tags: , , , |

Imagine you want to grant same exact privileges of existing user A to User B.

Login as a user who has a dba privilege and run below command to generate the list of privileges to be granted.

select
‘grant ‘ || privilege || ‘ on ‘ || owner || ‘.’ || table_name || ‘ to USER B;’
from
dba_tab_privs
where
grantee = upper(‘&GRANTEE_NAME’);

GRANTEE_NAME is USER A.

You can also check this link (https://oraclepitstop.wordpress.com/2008/05/29/sql-script-to-list-oject-privileges-granted-to-a-particular-user/) to list all the privileges granted to a particular user.

cheers,
OraclePitStop

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

SQL Script to list object privileges granted to a particular user

Posted on May 29, 2008. Filed under: DBA/Admin Tips, Scripts, User | Tags: , , , |

Login to database as sys / system / or any user who had dba privileges and run below command

select grantor || ‘ granted ‘ || privilege || ‘ on ‘ || table_name || ‘ owned by ‘|| owner || ‘ to ‘|| grantee
from dba_tab_privs where grantee like upper(‘&GRANTEE_NAME’) order by privilege;

where GRANTEE_NAME is the user you want to find out the privileges.

Even though the view name is dba_tab_privs, it lists privileges on all objects including tables, views, procedures, etc.,

Sample Output

APPS granted DELETE on FND_USER_RESP_GROUPS owned by APPS to XXXXXX
APPLSYS granted DELETE on FND_USER owned by APPLSYS to XXXXXX;

cheers,
OraclePitStop

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

R12 RUP4 Issue (6435000) with ce6157444.sql / ce5928260.sql

Posted on May 28, 2008. Filed under: R12 |

Last week, when i was applying RUP4 (6435000), i faced couple of issues:

1) Apps account was getting locked for no reason. After running for 2 to 3 hrs, patch would start failing with message “apps account is locked”. This was quite wierd and we didnt face this issue applying RUP4 in other environments. Simple solution is to login as sysdba and issue “alter user apps unlock account” and restarting patch from where it left.

2) Patch may fail with the following error in the worker log file.

—————

sqlplus -s APPS/***** @/swafc/applmgr/afcuat/apps/apps_st/appl/ce/12.0.0/patch/115/sql/ce5928260.sql
DECLARE
*
ERROR at line 1:
ORA-29282: invalid file ID
ORA-06512: at “SYS.UTL_FILE”, line 774
ORA-06512: at line 106
ORA-29283: invalid file operation
ORA-06512: at “SYS.UTL_FILE”, line 475
ORA-29283: invalid file operation
———————

This issue was seen in 2 environments (dev1 and dev2) where we applied RUP4. Both the environments were on the same server and the “utl_file_dir” on both environments had “/usr/tmp” as its first entry. What happens is, when the script runs for the first time, it creates a log file under /usr/tmp with <filename>.log and when we run the script again on the same server we get the above issue.

Solution is to (re)move the log file from /usr/tmp and rerun the script again either manually or restart via patch.

Hope this tip would be useful.

Cheers,

Oracle PitStop

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

How to compile a single jsp file in Oracle Applications 11i/R12

Posted on May 28, 2008. Filed under: Apps Technology Stack, DBA/Admin Tips, R12, Scripts | Tags: , , , , |

You can follow below steps to compile a single jsp file (seeded or custom) in Oracle Applications 11i/R12.

1. Copy the jsp file to $COMMON_TOP/webapps/oacore/html (this location translates to $OA_HTML).

Placing the file under $OA_HTML enables end users to access the file using the url http://hostname.domain:port/OA_HTML/%5Bjspfile.jsp%5D

2. export PATH=$PATH:$FND_TOP/patch/115/bin
3. ojspCompile.pl – -compile -s ‘[jspfile.jsp]’ (no space between the two dashes before compile)

No need to bounce apache server after compiling the file to access the new content.

Below is the command line help for ojspCompile.pl command

ojspCompile.jsp command help

syntax: ./ojspCompile.pl COMMAND {ARGS}
COMMAND –compile update dependency, compile delta
–create rebuild entire dependency file
-delta.out update dependency, list delta to file
-dep.out update dependency, output heirarchy to file

ARGS -s matching condition for JSPs filenames
-p number of parallel compilations
-log to override logfile from ojspCompile.conf
You are
recommended to set the log file location
outside of any network file system shared (NFS) area/drive.
-conf to override ojspCompile.conf
–retry retry previously failed compilation attempts
–flush forces recompilation of all parent JSPs
–quiet do not provide an actively running progress meter
–fast instantly fail jsps that are *possibly* invalid

example1: ojspCompile.pl –compile -s ‘jtf%’ -p 20 –retry
example2: ojspCompile.pl –compile -s ‘jtflogin.jsp,jtfavald.jsp’ –flush
example3: ojspCompile.pl –compile –fast –quiet

cheers,
OraclePitStop

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

Offtopic: Windows Vista Update failed with error 80070005

Posted on May 24, 2008. Filed under: Operating System, Windows | Tags: , , |

I have a Vista Home Premium edition and for no reason the windows update failed with error 80070005 one day. There was no new applications installed and i dont remember changing any settings and the worst part is i dont get to find a solution on the vendor website.

I tried all sort of options provided in this microsoft forum link but nothing worked.

Thanks to Aaron Stebner for the wonderful post. (http://blogs.msdn.com/astebner/archive/2006/09/04/739820.aspx) which just worked like a charm. I just followed the steps mentioned on this blog, and voila the error vanished.

cheers,
OraclePitStop

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

srvctl command line options

Posted on May 22, 2008. Filed under: RAC | Tags: , , |

Thought this might be helpful.

Usage: srvctl [-V]
Usage: srvctl add database -d -o [-m ] [-p ] [-A /netmask] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY}] [-s ] [-n ] [-y {AUTOMATIC | MANUAL}]
Usage: srvctl add instance -d -i -n
Usage: srvctl add service -d -s -r “” [-a “”] [-P ]
Usage: srvctl add service -d -s -u {-r “” | -a “”}
Usage: srvctl add nodeapps -n -o -A /netmask[/if1[|if2|…]]
Usage: srvctl add asm -n -i -o [-p ]
Usage: srvctl config database
Usage: srvctl config database -d [-a] [-t]
Usage: srvctl config service -d [-s ] [-a] [-S ]
Usage: srvctl config nodeapps -n [-a] [-g] [-o] [-s] [-l]
Usage: srvctl config asm -n
Usage: srvctl config listener -n
Usage: srvctl disable database -d
Usage: srvctl disable instance -d -i “”
Usage: srvctl disable service -d -s “” [-i ]
Usage: srvctl disable asm -n [-i ]
Usage: srvctl enable database -d
Usage: srvctl enable instance -d -i “”
Usage: srvctl enable service -d -s “” [-i ]
Usage: srvctl enable asm -n [-i ]
Usage: srvctl getenv database -d [-t “”]
Usage: srvctl getenv instance -d -i [-t “”]
Usage: srvctl getenv service -d -s [-t “”]
Usage: srvctl getenv nodeapps -n [-t “”]
Usage: srvctl modify database -d [-n <db_name] [-o ] [-m ] [-p ] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY}] [-s ] [-y {AUTOMATIC | MANUAL}]
Usage: srvctl modify instance -d -i -n
Usage: srvctl modify instance -d -i {-s | -r}
Usage: srvctl modify service -d -s -i -t [-f]
Usage: srvctl modify service -d -s -i -r [-f]
Usage: srvctl modify service -d -s -n -i [-a ] [-f]
Usage: srvctl modify asm -n -i [-o ] [-p ]
Usage: srvctl relocate service -d -s -i -t [-f]
Usage: srvctl remove database -d [-f]
Usage: srvctl remove instance -d -i [-f]
Usage: srvctl remove service -d -s [-i ] [-f]
Usage: srvctl remove nodeapps -n “” [-f]
Usage: srvctl remove asm -n [-i ] [-f]
Usage: srvctl setenv database -d {-t =[,=,…] | -T =}
Usage: srvctl setenv instance -d [-i ] {-t “=[,=,…]” | -T “=”}
Usage: srvctl setenv service -d [-s ] {-t “=[,=,…]” | -T “=”}
Usage: srvctl setenv nodeapps -n {-t “=[,=,…]” | -T “=”}
Usage: srvctl start database -d [-o ] [-c | -q]
Usage: srvctl start instance -d -i “” [-o ] [-c | -q]
Usage: srvctl start service -d [-s “” [-i ]] [-o ] [-c | -q]
Usage: srvctl start nodeapps -n
Usage: srvctl start asm -n [-i ] [-o ] [-c | -q]
Usage: srvctl start listener -n [-l ]
Usage: srvctl status database -d [-f] [-v] [-S ]
Usage: srvctl status instance -d -i “” [-f] [-v] [-S ]
Usage: srvctl status service -d [-s “”] [-f] [-v] [-S ]
Usage: srvctl status nodeapps -n
Usage: srvctl status asm -n
Usage: srvctl stop database -d [-o ] [-c | -q]
Usage: srvctl stop instance -d -i “” [-o ] [-c | -q]
Usage: srvctl stop service -d [-s “” [-i ]] [-c | -q] [-f]
Usage: srvctl stop nodeapps -n
Usage: srvctl stop asm -n [-i ] [-o ] [-c | -q]
Usage: srvctl stop listener -n [-l ]
Usage: srvctl unsetenv database -d -t “”
Usage: srvctl unsetenv instance -d [-i ] -t “”
Usage: srvctl unsetenv service -d [-s ] -t “”
Usage: srvctl unsetenv nodeapps -n -t “”

cheers,
OraclePitStop

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

Oracle Applications – 11i / R12 – Missing Patches Analysis

Posted on May 21, 2008. Filed under: DBA/Admin Tips, Patch | Tags: , , , |

Every organisation has more than one oracle apps instance for different purpose viz., PROD, TEST, DEV etc. and generating a report on missing apps patches on the available instances is a pain if it has to be done manually.

Often, the TEST instances will have more patches when compared to PROD, reason being first the patch will have to be tested on a TEST instance for resolving an issue on PROD or DEV, then it will be moved to DEV and by the time the patch gets applied to PROD it will be a month or so, or may be even more than that depending on the management policies and severity of the issue for which the patch is applied on TEST or DEV.

Now lets say you want to compare DEV instance based on the patches applied to TEST instance.

1. Login to TEST instance as apps user
2. SQL Command: create table patch_list as (select distinct patch_name from ad_applied_patches);
3. Now PATCH_LIST table has all the patches that are applied to TEST instance.
4. Generate an insert script for the available patches.

SQL Command: spool patch_numbers.sql
SQL Command: select ‘insert into patch_list values(”’ || patch_name ||”’);’ from patch_list;
SQL Command: spool off

5. Login to the DEV instance as apps user
6. SQL Command: create table patch_list (patch_name varchar2(120));
7. Run patch_numbers.sql in this instance. This will populate the PATCH_LIST table with the TEST instance patches.
8. Run below sql to get the missing patches in DEV instance.

SQL Command: set heading off pages 0

SQL Command: select patch_list.* from patch_list LEFT JOIN ad_applied_patches ON patch_list.patch_name = ad_applied_patches.patch_name where ad_applied_patches.patch_name IS NULL;

The resulting output is the missing patches on DEV instance.

Above information can also be achieved without creating the PATCH_LIST table if DB_LINK is used. If using DB_LINK dont forget to modify the above query with correct column and table names.

cheers,
OraclePitStop.

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

Diagnostics link is missing on the web page?

Posted on May 16, 2008. Filed under: Diagnostics, R12, TroubleShooting | Tags: , |

The diagnostics link on the self service page is controlled by two profile options “FND:Diagnostics” & “Personalize Self-Service Defn”

If the value is set to YES (default in R12) the link will appear if its set to NO it wont.

for more information check metalink note id: 400042.1

cheers,
OraclePitStop

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

How to identify to which mid-tier you are/will be connected to on a multi-node R12 environment?

Posted on May 16, 2008. Filed under: Apps Technology Stack, R12, RAC | Tags: , , , |

Imagine you have a multi-node Oracle Apps R12 setup with 2 node RAC. How would you identify to which server you are/will be connected to when you issue the url. Obviously when you have multi-node setup, you will be accessing the login page using a virtual URL.

1. Open Internet Explorer or Firefox
2. Type in the url eg: https://my.apps.com
3. This should bring the login page (AppsLogin)
4. Click on “About this page” — bottom left
5. Click on “Page Context” (3rd tab)

Here you will be able to see the following details:

1. Database HostName
2. DBC file being accessed
3. JDBC Port
4. SID being used to connect to the database

The DBC file being accessed proivdes the expanded full path of the dbc file (ie., $FND_SECURE or $INST_TOP/appl/fnd/12.0.0/.dbc). The expanded path will have the hostname. That is the mid-tier you are connected to.

You can also click on “Technology Components” & “Java System Properties” to gather more information.

cheers,
OraclePitStop

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

How to copy / migrate printer definitions to a new instance using FNDLOAD

Posted on May 15, 2008. Filed under: Configuration, printer | Tags: , , , , , , |

Imagine below situation:

1. Target had been cloned from source
2. Couple of new printers had been configured on source after the clone.
3. Now you want the same printer configurations on target instance similar to source.

Options:

1. Perform the steps manually on target instance
2. Clone target from source
3. Use FNDLOAD (Generic Loader Utility)

We will discuss FNDLOAD option in this post.

How to download all printer definitions

1. Download the printer definitions from source

FNDLOAD apps/ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppinf.lct source_printer_def.ldt FND_PRINTER

2. Connect to target instance
3. Get the source printer definition file (source_printer_def.ldt) from source (ftp, scp or any method that you like)
4. Create a backup of the target system printer information
4.1. Connect to sqlplus as apps user and below commands

create table fnd_printer_bk as (select * from fnd_printer);
select printer_name from fnd_printer_bk;

5. Now upload the printer definitions into the target system

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcppinf.lct source_printer_def.ldt FND_PRINTER

SQL> select printer_name from fnd_printer;

Above sql should list the new printers that are uploaded.

How to download a specific printer definition

FNDLOAD apps/ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppinf.lct source_printer_def.ldt FND_PRINTER PRINTER_NAME=[printer name]

This is one of the functionality of FNDLOAD. Refer to this metalink note : 274667.1 for other features of FNDLOAD.

You can follow this link to migrate forms, menus & responsibilities.

Happy Printer Migrations !!!

cheers,
OraclePitStop

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

« Previous Entries

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