Thursday, August 11, 2016

Oracle Applications table suffix naming conventions

The simple rule of tables you would want to query:

No suffix (similar to a base table)

Base table. This is the primary table containing the base data - in theory language agnostic. An associated _TL table will have the translated display value (what will be displayed on the applications front end) for a specific code in the base table. If multi language has not been enabled there will be a one to one relationship between base and translation table. (see additional info below)
If Oracle apps has been configured for multi org then these tables will have the combined data of all operating units.
Views, in a lot of cases you may prefer to use the views which are a standardised of viewing data from more than one table
View with language information
View with resolved lookups instead of only showing the codes
    In some cases you might want to use these tables:
    Audit tables
    Audit view of what was changed, when it was changed and by who
    Tracked tables, each record associated with the same entity has a start and end date that won't overlap. Used in HR and Payroll.

    In most cases you will ignore unless there is a specfic requirements:
    Table with language, is associated with a base or unsuffixed tabled. Contains translations of terms (multi language) relating to the base table. One record in a base table could have many translations.

    Global temporary; data only visible by session owner. Will likely be populated when a process on the apps front end has been run, so most of the time you won't see any data in these tables.
    Is an interface or processing table, it will be populate with records that are to be processed into base and other tables.
    Analytical criteria sources; assuming if you have analytics enabled this ties the data to the associated analytic tables, otherwise they are not populated.
    History table; updated only when data from the related tables has been exported (via concurrent job I am assuming), you can trace back exactly what version / state the data was in at the time of export. Useful for analytics perhaps, when the data is being exported to populate a dimension for reporting.
    Standard interface or import table. Records are to be processed into base and other tables.
    Detail table, contains additional detail for a base table. E.g. address detail table will contain the extended geographic location details of the address table.

      Multi-language tables and views:
      _TL and _VL extend their associated tables with language information (Multi Language Support) if it is being used by Oracle Apps. For example the TL table contain one or more language indicators for a code so that when it is viewed on the front end it uses the associated values according to the correct language that Oracle Applications has been configured for.
      A simple example would be names of months, January could be code '1' and if language code = 'US' display value = 'January'; 'FR' display value = 'Janvier'; 'SP' display value = 'Enero etc.

      Monday, August 1, 2016

      Some Scheduled Requests Are Duplicated Or Stopped

      With Release 12.1.1, find that the submitted scheduled requests are getting duplicated or stop working.

      It is expected that the scheduled requests work fine with same schedule parameters.

      1. Navigate to Concurrent > Program > Define, query for "Workflow Background Process" program or any other program for which schedule is not working correctly.

      2. De-select the option "Restart on System Failure"

      3. Save the change and reschedule the request.

      Monday, July 11, 2016

      Change APPS password in R12.2

      1. Shut down the application tier services using the below script:


      2. Change the APPLSYS password using

      FNDCPASS apps/ 0 Y system/manager SYSTEM APPLSYS WELCOME

      3. Run autoconfig with the newly changed password.

      4. Start AdminServer using the $INST_TOP/admin/scripts/ script. Do not start any other application tier services.

      5. Change the "apps" password in WLS Datasource as follows:

      a. Log in to WLS Administration Console.
      b. Click Lock & Edit in Change Center.
      c. In the Domain Structure tree, expand Services, then select Data Sources.
      d. On the "Summary of JDBC Data Sources" page, select EBSDataSource.
      e. On the "Settings for EBSDataSource" page, select the Connection Pool tab.
      f. Enter the new password in the "Password" field.
      g. Enter the new password in the "Confirm Password" field.
      h. Click Save.
      i. Click Activate Changes in Change Center.

      6. Start all the application tier services using the below script


      7. Verify the WLS Datastore changes as follows:

      a. Log in to WLS Administration Console.
      b. In the Domain Structure tree, expand Services, then select Data Sources.
      c. On the "Summary of JDBC Data Sources" page, select EBSDataSource.
      d. On the "Settings for EBSDataSource" page, select Monitoring > Testing.
      e. Select "oacore_server1".
      f. Click Test DataSource
      g. Look for the message "Test of EBSDataSource on server oacore_server1 was successful".

      Weblogic password change in EBS 12.2

      Shutdown the application tier leaving admin server up and running.

      $ perl $FND_TOP/patch/115/bin/ -action=updateAdminPassword

      Program: started at Mon Jul 11 23:35:03 2016

      AdminServer will be re started after changing WebLogic Admin Password
      All Mid Tier services should be SHUTDOWN before changing WebLogic Admin Password
      Confirm if all Mid Tier services are in SHUTDOWN state. Enter "Yes" to proceed or anything else to exit: Yes

      Enter the full path of Applications Context File [DEFAULT - $CONTEXT_FILE]:
      Enter the WLS Admin Password:
      Enter the new WLS Admin Password:
      Enter the APPS user password:

      Finding Archivelog Names using the SCN

      How to find the Archivelog names using the SCN

      During database recovery,we may have a SCN number and need to know the archivelog names.

      set pages 300 lines 300
      col first_change# for 9,999,999,999
      col next_change# for 9,999,999,999

      alter session set nls_date_format='DD-MON-RRRR HH24:MI:SS';

      select name, thread#, sequence#, status, first_time, next_time, first_change#, next_change# from v$archived_log
      where between first_change# and next_change#;

      SEQUENCE# number usually shows up on the archivelog name.

      If you see 'D' in the STATUS column, the archive log has been deleted from the disk. You may need to restore it from the tape.

      rman target /

      list backup of archivelog from logseq= until logseq=

      restore archivelog from logseq= until logseq=;

      Tuesday, July 5, 2016

      Troubleshooting Workflow Notification Mailer Issues

      Find Workflow Notification Mailer is up and Running?

      SELECT component_name, component_status
      FROM fnd_svc_components
      WHERE component_type = ‘WF_MAILER’;

      Workflow log’s: FNDCPGSC*.txt under $APPLCSF/$APPLOG directory

      Find the Failed One’s?


      Check pending e-mail notification that was pending for process.

      Sql> SELECT COUNT(*), message_name FROM wf_notifications
      AND mail_status = ‘MAIL’
      GROUP BY message_name;

      Sql> SELECT * FROM wf_notifications
      AND mail_status = ‘SENT’
      ORDER BY begin_date DESC

      Check the Workflow notification has been sent or not?

      select mail_status, status from wf_notifications where notification_id=

      –If mail_status is MAIL, it means the email delivery is pending for workflow mailer to send the notification
      –If mail_status is SENT, its means mailer has sent email
      –If mail_status is Null & status is OPEN, its means that no need to send email as notification preference of user is “Don’t send email”
      –Notification preference of user can be set by user by logging in application + click on preference + the notification preference

      1. Verify whether the message is processed in WF_DEFERRED queue

      select * from$wf_deferred a where a.user_data.getEventKey()= ”
      – notification id

      2. If the message is processed successfully message will be enqueued to WF_NOTIFICATION_OUT queue, if it errored out it will be enqueued to WF_ERROR queue

      select wf.user_data.event_name Event_Name, wf.user_data.event_key Event_Key,
      wf.user_data.error_stack Error_Stack, wf.user_data.error_message Error_Msg
      from wf_error wf where wf.user_data.event_key = ‘
      To check what all mails have went and which all failed ?

      Select from_user,to_user,notification_id, status, mail_status, begin_date
      from WF_NOTIFICATIONS where status = ‘OPEN’;

      Select from_user, to_user, notification_id, status, mail_status,begin_date,USER_KEY,ITEM_KEY,MESSAGE_TYPE,MESSAGE_NAME begin_date
      from WF_NOTIFICATIONS where status = ‘OPEN’;

      Users complain that notifications are stuck ?

      Use the following query to check to see whatever the users are saying is correct

      SQL> select message_type, count(1) from wf_notifications
      where status=’OPEN’ and mail_status=’MAIL’ group by message_type;

      E.g o/p of query –

      MESSAGE_Type COUNT(1)
      ——– ———-
      POAPPRV 11 — 11 mails of Po Approval not sent —
      INVTROAP 12
      REQAPPRV 9
      WFERROR 45 — 45 mails have error

      If Mail not received by User ?

      from wf_users where DISPLAY_NAME=’xxx,yyy’ ;

      Status – Active
      Notification_preference-> Mailtext
      Email Address should not be null

      Notification not sent waiting to be mailed ?

      SQL> select notification_id, status, mail_status, begin_date from WF_NOTIFICATIONS
      where status = ‘OPEN’ and mail_status = ‘MAIL’;
      To debug the notification id ?

      run wfmlrdbg.sql

      Note: 1054215.1 – How to Check if the Workflow Mailer is Running
      Note: 415516.1 – How to Check Whether Notification Mailer is Working or Not
      Note: 831982.1 – 11i/R12 – A guide for troubleshoting Workflow Notification Emails – Inbound and Outbound
      Note: 1012344.7 – Notifications Not Being Sent In Workflow
      Note: 560472.1 – Workflow Mailers Not Sending Notifications
      Please see (Note: 753845.1 – How to Perform a Meaningful SMTP Telnet Test to Troubleshoot Java Mailer Issues), the same error is reported in this doc.

      Friday, June 17, 2016

      Oracle Apps R12.2 Log Files Location

      1. R12.2 Start/Stop Log Files:

      a) Startup/Shutdown error message text files like adstrtal.txt, adstpall, adapcctl.txt, adcmctl.txt…for services like oacore, forms, apache, opmn, weblogic admin server/node manager etc
      Log files for start/stop of services from $ADMIN_SCRIPTS_HOME
      Here is an image representing EBS R12.2 Service control (start/stop) logs:
      Green colour boxes indicates location of log files 

       2. Patching Log files:
      a) The Online Patching (ADOP) log files are located on the non-editioned file system (fs_ne), under :
      $NE_BASE/ EBSapps/ log/adop//__
      Notefs1 and fs2 are dual file system for software where as fs_ne contains non editioned objects 
      This log directory will contain patch logspatch worker logs and other patch related log files created for specific purposes.
      Also, some patch tasks may create separate log files in the same directory from where patch executable is started
      3. Log files for concurrent programs/managers
      $NE_BASE/ inst//logs/appl/conc/log
       Output files for concurrent programs/managers
      Here is an image representing EBS R12.2 Non-Editioned file system Logs:
      Patching Log file Location:
      4. Cloning related Log Files:
      a) Pre-clone log files in source instance
           I. Database Tier
          II. Application Tier
      $INST_TOP/admin/log/ (StageAppsTier_MMDDHHMM.log)
      b) Clone log files in target instance
          I. Database Tier
          II. Apps Tier

      5. AutoConfig log file:
      Autoconfig can be run on Application Tier or Database Tier
      a) Autoconfig Log Location on Application tier :
      b) Autoconfig Log Location on Database tier:
      $ORACLE_HOME/ appsutil/ log/ $CONTEXT_NAME/ /  *.log

      6.  Installation Related Logs:
      There are four main phases (in context of logs) during R12.2 installation, here is log location of all four phases .
      a) The Rapidwiz Configuration File is saved in below locations:
      / appsutil/ conf_.txt
      b) Pre-install system check logs:
      c) Database tier
      I. Main Installation log:
      RDBMS $ORACLE_HOME/ appsutil/ log/ $CONTEXT_NAME/ .log
      II. ORACLE HOME installation logs:
      RDBMS $ORACLE_HOME/ temp/ $CONTEXT_NAME/ logs/ *.log
      d) Application tier
      Both primary(Run) and Secondary Edition (Patch) file systems will each contain these logs:
          I. Main Installation Log:
          II. FMW and OHS TechStack Installation/Patching logs:
          III. Forms Oracle Home installation logs:
      Here is an image representing the R12.2 Installation logs

      6. Fusion Middleware (FMW_HOME) Log Files:
      a) Log files for OPMN and OHS processes: Below directory contains log files related OPMN process(opmn.log), OPMN Debug logs(debug.log), HTTP Transaction logs (access.log), security settings related logs.
      $IAS_ORACLE_HOME/ instances/ /diagnostics/logs
      b) Log files for weblogic node manager: Log file is generated by Node Manager and contains data for all domains that are controlled by Node Manager on a given physical machine.
      $FMW_HOME/ wlserver_10.3/ common/ nodemanager/ nmHome1/ nodemanager.log
      c) Log files for weblogic Oracle Management Service log file: Initial settings AdminServer and Domain level information is written in this log file.
      d) Log files for server processes initiated through weblogic: Stdout and stderr messages generated by the server instance (server instances like forms, oafm, oacore etc) at NOTICE severity level or higher are written by Weblogic Node Manager to below directory.
      $EBS_DOMAIN_HOME/ servers/ / logs/ .out
      Here is an image representing EBS R12.2 Fusion Middleware Log files:

      Wednesday, June 15, 2016


      Archive shipping from Primary to DR was not working.

      Checked alert log on DR site and it was waiting for an archive from Primary.

      On Primary following error was there in alert log :

      Error 1033 received logging on to the standby

      Re-created password file on Primary and copied it over to DR site to fix the issue.

      Tuesday, June 7, 2016

      Calculate ArchiveLog Size

      Archivelog size each day

      SQL> select trunc(COMPLETION_TIME) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME) order by 1;

      ---------- ----------
      2011-02-12 71797.87
      2011-02-13 75880.52
      2011-02-14 73569.37
      2011-02-15 76776.81
      2011-02-16 73959.86
      2011-02-17 69969.71
      2011-02-18 74677.10
      2011-02-19 75474.95
      2011-02-20 77967.07
      2011-02-21 67802.70

      Archivelog size each hour

      SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24';

      Session altered.

      SQL> select trunc(COMPLETION_TIME,'HH24') TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME,'HH24') order by 1;

      ------------- ----------
      2011-02-21 00 6396.65
      2011-02-21 01 2797.31
      2011-02-21 02 2010.45
      2011-02-21 03 1871.77
      2011-02-21 04 1481.5
      2011-02-21 05 2868.20
      2011-02-21 06 2363.89
      2011-02-21 07 4269.26
      2011-02-21 08 2469.08
      2011-02-21 09 3007.06
      2011-02-21 10 3561.97
      2011-02-21 11 2530.57
      2011-02-21 12 3509.08
      2011-02-21 13 3022.5
      2011-02-21 14 3514.97
      2011-02-21 15 4057.45
      2011-02-21 16 3021.27
      2011-02-21 17 4014.31
      2011-02-21 18 4011.66
      2011-02-21 19 4008.10
      2011-02-21 20 3015.46

      Thursday, June 2, 2016

      Apply Phase Options ABANDON and RESTART

      adop phase=apply patches=10721639 abandon=yes

      Used to specify whether to restart the previous run of adop. May be useful if the previous action had an error. Note: If there was an error in the previous run, and 'abandon' is not set to 'yes', the same parameters will be re-used that were used in the failed run.

      If you give a value for the 'restart' parameter, it cannot be the same as the value given for this parameter.

      adop phase=apply patches=10721639 restart=yes

      Used to specify whether to restart the previous run of ADOP. May be useful if the previous action had an error.

      If you give a value for the 'abandon' parameter, it cannot be the same as the value given for this parameter.

      The below option is not going to work

      adop phase=apply patches=10721639 restart=yes abandon=yes

      Tuesday, May 17, 2016

      Transporting a SQL Profile

      To transport a SQL profile:
      1. Connect SQL*Plus to the database with the appropriate privileges, and then use the CREATE_STGTAB_SQLPROF procedure to create a staging table to hold the SQL profiles.
        The following example creates my_staging_table in the dba1 schema:
            table_name  => 'my_staging_table'
        ,   schema_name => 'dba1' 
      2. Use the PACK_STGTAB_SQLPROF procedure to export SQL profiles into the staging table.
        The following example populates dba1.my_staging_table with the SQL profile my_profile:
            profile_name         => 'my_profile'
        ,   staging_table_name   => 'my_staging_table'
        ,   staging_schema_owner => 'dba1' 
      3. Move the staging table to the database where you plan to unpack the SQL profiles.
        Move the table using your utility of choice. For example, use Oracle Data Pump or a database link.
      4. On the database where you plan to import the SQL profiles, use UNPACK_STGTAB_SQLPROF to unpack SQL profiles from the staging table.
        The following example shows how to unpack SQL profiles in the staging table:
             replace            => true
        ,    staging_table_name => 'my_staging_table'

      Sunday, April 10, 2016

      Validating sysadmin password in 11i/R12

      Use Following query to validate wether sysadmin password that we are using is correct or not

      select fnd_web_sec.validate_login('SYSADMIN','')
      from dual;

      If it returns Y then sysadmin password is correct.
      If it returns N then sysadmin password that we are using is wrong.

      Oracle Apps Printer queries

      Printer Navigation steps

              Printer types > Navigate - Install - Printer - Types    FNDPRTYP
              Printer > Navigate -Install - Printer - Register        FNDPRMPR
              Print Styles > Navigate - Install - Printer - Style     FNDPRRPS
              Printer Driver > Navigate - Install - Printer - Driver  FNDPRMPD



      SPOOL_FLAG ,
      SRW_DRIVER ,