foalabs

How To Assign AME[Approval Management Roles/Responsiblities] Access to Any User in Oracle Apps 1) Login in as "Sysadmin"   2) Navigate to User Management Responsibility > User Find the user to whom you want to give access. Click Update. 3) Click Assign Roles   4) Query and Select -       Approvals Management Administrator -       Approvals Management Business Analyst  Provide relevant justification and Click save. 5) a. Go into the responsibility: Functional Administrator b. Navigate to Grants Tab > Create Grant Give a name to the grant. Enter description. Select grantee type as specific user. Select the user in grantee lov field. Select AME_TRANSACTION_TYPES Object from lov. c. Click next. Select all_rows in "Data Context Type". d. Click next. Select "AME Calling Applications" from Set lov. e. Click finish. f. After performing above steps, Make sure concurrent manager is up. Bounce apache. g. Retest the issue. Now navigate to...

ORA-01207: file is more recent than control file - old control file Database showed the control file is older, When checked found some DBA has crashed the PROD box. Boom !!! Database was mounted and when tried to start getting below error: ORA-01122: database file 76 failed verification check ORA-01110: data file 76: '+PROD/prod/datafile/apps_ts_tx_idx.343.1028735351' ORA-01207: file is more recent than control file - old control file Check the files required recovery. select name,open_mode from v$database;  NAME      OPEN_MODE --------- -------------------- PROD       MOUNTED SYS@PROD> select * from v$recover_file;      FILE# ONLINE  ONLINE_ ---------- ------- ------- ERROR                                                                CHANGE# -----------------------...

 How to Setup Local NTP server in Linux 7 Network Time Protocol   ( NTP ) – This is a protocol which runs over port 123 UDP at Transport Layer and allows computers to synchronize time over networks for an accurate time.  This is majorly used when working with the cluster system, Oracle RAC, Master-Slave setups. We will see in this post how to set up the NTP Server and Client Part 1- NTP server Preparation 1) Install ntp package: We need to install the ntp if not already available. yum install ntp 2) Edit the NTP configuration vi /etc/ntp.conf 3) Comment pool for server sync (Used in case of internet) and Add local server 127.127.1.0 # Use public servers from the pool.ntp.org project. # Please consider joining the pool (http://www.pool.ntp.org/join.html). #server 0.rhel.pool.ntp.org iburst #server 1.rhel.pool.ntp.org iburst #server 2.rhel.pool.ntp.org iburst #server 3.rhel.pool.ntp.org iburst server 127.127.1.0 4) Restrict to a range of IP to which access is required. Ad...

Oracle Reports - Program exited with status 255 for EBS in Windows Issue: When running Active Users or other reports getting below error on Windows-based EBS instance. "Program exited with status 255 Cause: The program terminated, returning status code 255. Action: Check your installation manual for the meaning of this code on this operating system. Concurrent Manager encountered an error while running Oracle*Report for your concurrent request Request ID." Cause: This happens when before cloning proper cleanup is not done. Duplicate value is repeated multiple times in "REPORTS_CLASSPATH" and "REPORTS_PATH" because of multiple re-installations of "Oracle Developer Suite" in the same "ORACLE_HOME" without proper cleanup before the subsequent clone. Solution: 1) Open regedit in Windows 2) Navigate  HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_<instancename>__TOOL S and remove duplicate values for " REPORTS_CLASSPATH" ...

Query to Find High Water Mark for a Given Table/All Table in Oracle Script: Please note  The first parameter you need to pass as a single  Table name or ALL for all tables in the schema   The second Parameter would be passed as Owner. SET SERVEROUTPUT ON SET VERIFY OFF DECLARE   CURSOR cu_tables IS     SELECT a.owner,            a.table_name     FROM   dba_tables a     WHERE  a.table_name = Decode(Upper('&&Table_Name'),'ALL',a.table_name,Upper('&&Table_Name'))     AND    a.owner      = Upper('&&Table_Owner')      AND    a.partitioned='NO'     AND    a.logging='YES' order by table_name;   op1  NUMBER;   op2  NUMBER;   op3  NUMBER;   op4  NUMBER;   op5  NUMBER;   op6  NUMBER;   op7  NUMBER; BEGIN   Dbms_Output.Disable;...

 Query to shrink Datafiles and Reclaim unused Space in Oracle Steps to Shrink the datafiles set verify off set pages 1000 column file_name format a50 word_wrapped column smallest format 999,990 heading "Smallest|Size|Poss." column currsize format 999,990 heading "Current|Size" column savings  format 999,990 heading "Poss.|Savings" break on report Step1: To Check Database block size column value new_val blksize select value from v$parameter where name = 'db_block_size'; Step2: Check how much space can be reclaimed  select file_name,        ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,        ceil( blocks*&&blksize/1024/1024) currsize,        ceil( blocks*&&blksize/1024/1024) -        ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings from dba_data_files a,      ( select file_id, max(block_id+blocks-1) hwm          from d...

Query to find Oracle Process consuming most of the Memory and CPU Session consuming the most memory SELECT a.username, a.osuser, a.program, b.spid,b.pga_used_mem, a.sid, a.serial# ,a.module,a.logon_time,a.terminal FROM v$session a, v$process b WHERE a.paddr = b.addr order by b.pga_used_mem desc Session Consuming Most CPU select     ss.username,     se.SID ,    VALUE/100 cpu_usage_seconds from    v$session ss,     v$sesstat se,     v$statname sn where    se.STATISTIC# = sn.STATISTIC# and    NAME like '%CPU used by this session%' and     se.SID  =  ss.SID and     ss.status='ACTIVE' and     ss.username is not null order by 3 desc

 How to identify a filesystem type in Linux System There are multiple ways to determine the file system type in a Linux Based Environment 1) df -Th for this to work the filesystem should be mounted 2) fsck -N /dev/device Example:  fsck -N /dev/sdb2 3) lsblk -f Displays the block device with filesystem type details 4) mount |grep -i "device" Example: mount| grep -i "sdb2" 5) blkid /dev/device Example: blkid /dev/sdb2 6) cat /etc/fstab This would only work if the filesystem is updated in fstab.

 How to perform Oracle 11g Data Guard Switchover and Switchback  Switchover: Before performing switchover, please  verify the state of the data guard on both the instances by following SQL queries:  SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';  SQL> SELECT sequence#, first_time, next_time, applied  FROM v$archived_log  ORDER BY sequence#;    SQL> select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';  DEST_NAME  --------------------------------------------------------------------------------  STATUS ERROR  --------- -----------------------------------------------------------------  LOG_ARCHIVE_DEST_2  VALID  SQL> select message from v$dataguard_status; Note: This command will give you an appropriate message about the data guard current status. Kick-Off switchover activity by issuing the following  commands: On Primary database: Step-1 Conn...

 Cron to Schedule a Script on Last Day of Month 00 01 28-31 * * [ "$(date +%d -d tomorrow)" = "01" ] && /script.sh At 01:00 on every day-of-month from 28 through 31 00--> Minutes when you want to run 01--> Hour when the job needs to be executed 28-31--> 28,28,29 and 31’st of each month *--> All month of year *-->All day of week /script.sh--> Script name which needs to be executed [ "$(date +%d -d tomorrow)" = "01" ]--> Logic which will test if next date is 01

 Query to find Plan Hash Values for a SQLID in Oracle SELECT DISTINCT sql_id, plan_hash_value FROM dba_hist_sqlstat dhs,     (     SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap     FROM dba_hist_snapshot ss     WHERE ss.begin_interval_time BETWEEN (SYSDATE - &No_Days) AND SYSDATE     ) s WHERE dhs.snap_id BETWEEN s.min_snap AND s.max_snap   AND dhs.sql_id IN ( '&SQLID')

 How to get Oracle SQL output in HTML format and send as a Mail Body  SQL Script to get the output as HTML SET MARKUP HTML ON SPOOL ON set termout off set pages 999 set pagesize 999 set feedback off SET MARKUP HTML ON TABLE "class=sysaud cellspacing=2 border='2' width='95%' align='center' " ENTMAP OFF spool fnd_debug_report.html set pagesize 1000 set pause off set linesize 150 prompt prompt FND Debug Profile Enabled Report  prompt prompt /* Below can be any query based on your requirement*/ SELECT upper(sys_context('USERENV','DB_NAME')) "DB_NAME",fpot.user_profile_option_name NAME,          DECODE (fpov.level_id,                  10001, 'Site',                  10002, 'Application',                  10003, 'Responsibility',                  10004, 'User',     ...

 sendmail errors: Deferred: local mailer (/usr/bin/rmail) exited with EX_TEMPF (HP UX) Error: While checking the mailq below messages are being seen.                 /var/spool/mqueue (25 requests) -----Q-ID----- --Size-- -----Q-Time----- ------------Sender/Recipient----------- 084Fe2V7000352*     229 Fri Sep  4 23:40 root                                          root 084FZ0EN019660      229 Fri Sep  4 23:35 root                  (Deferred: local mailer (/usr/bin/rmail) exited with EX_TEMPF)                                          root 084FU0lB009058      229 Fri Sep  4 23:30 root               ...

FNDCPASS error in R12 - Error in password verification for APPS Issue: While changing password using FNDCPASS getting below error. +----------------------------------------------------------------------------+ Working... Error in password verification for APPS +---------------------------------------------------------------------------+ Concurrent request completed Solution: SQL> show parameter case NAME                                 TYPE        VALUE ------------------------------------ ----------- -------------------- sec_case_sensitive_logon             boolean     TRUE SQL> alter system set sec_case_sensitive_logon=FALSE; System altered. Re-try FNDCPASS now