ETL Phases

Oracle BI Applications ETL processes includes three main phases: SDE, SIL, and PLP.

• SDE stands for Source Dependent Extract. In this phase, SDE tasks extract data from the source system and SDS and stage it in staging tables. SDE tasks are source specific.

• SIL stands for Source Independent Load. Load tasks transform and port the data from staging tables to base fact or dimension tables. SIL tasks are source independent.
• PLP stands Post Load Process. PLP tasks are only executed after the dimension and fact tables are populated. A typical usage of a PLP task is to transform data from a base fact table and load it into an aggregate table. PLP tasks are source independent.

Customizing the WebLogic JVM heap size

How to customize Java Virtual Machine Settings in Oracle WebLogic Server

To achieve the best performance of the application and avoid performance bottlenecks problems ( “OutOfMemory”) you need to tune your Java Virtual Machine.
After fresh install of WebLogic Server and create a Domain (WebLogic or SOA or Forms or OBIEE etc.) you may set some properties such as Java “Heap size”, tune Java “Garbage Collection” and WebLogic Server start options.
Tune JVM settings
set the Variable USER_MEM_ARGS for the Admin Server and each Managed Server to tune the JVM Parameters. For Example:

USER_MEM_ARGS="-Xms1g -Xmx3g -XX:+UseParNewGC -XX:+UseConcMarkSweepGC -XX:NewSize=1g"

Where:

• Xms1g: JVM initial heap size: In this example: 1 GB
• Xmx3g: JVM maximal heap size: The heap can grow to 3 GB
• -XX:+UseParNewGC: Uses parallel version of a younger generation

You can change the default JVM heap size to fit the needs of your deployment.

The default JVM heap size for WebLogic is 3GB. The size is set in the setDomainEnv.sh file for Linux or setDomainEnv.cmd for Windows, which is in the $DOMAIN_HOME/bin directory. The heap size is set with the -Xmx option.

To change the WebLogic JVM heap size:

1. Open the setDomainEnv file in a text editor.
2. Search for this comment line:
   For Linux:
   

   # IF USER_MEM_ARGS the environment variable is set, use it to override ALL MEM_ARGS values

   For Windows:
   

   @REM IF USER_MEM_ARGS the environment variable is set, use it to override ALL MEM_ARGS values

3. Immediately after the comment line, add one of these lines:
   For Linux:
   

   export USER_MEM_ARGS="-Xms128m -Xmx3072m ${MEM_DEV_ARGS} ${MEM_MAX_PERM_SIZE}"

   For Windows:
   

   set USER_MEM_ARGS=-Xms128m -Xmx3072m %MEM_DEV_ARGS% %MEM_MAX_PERM_SIZE%

4. Save the file.
5. Re-start WebLogic Server.      

"Create or Replace view" execution fails with "ORA-01720: grant option does not exist"

Developer complains that he can't run CREATE OR REPLACE VIEW for a particular view. He is getting error "ORA-01720: Grant Option Does Not Exist"
I searched online and didn’t find solution that fix my issue. Everyone was getting this error with a GRANT statement. I turned to My Oracle Support and found excellent note which saved my time.

Cause: view has incorrect grants.

Solution:
Remove all grants on the view or Drop and recreate the view

Reference: Post Upgrade To 11.2.0.4, "create or replace view" execution fails with "ORA-01720: Grant Option Does Not Exist" (Doc ID 1628033.1)

How to switch RUN File System and Patch File System in R12.2

The existence of the dual file system has implications for patches that change the system configuration. Depending on the specific situation, configuration changes can be made to either the run file system or the patch file system.

Sourcing Environment for run File system:

. /u01/app/EBSapps.env RUN

Sourcing Environment for patch File system:

. /u01/app/EBSapps.env patch

  

How to Synchronize FND_NODES, ADOP_VALID_NODES, and FND_OAM_CONTEXT_FILES in 12.2 When ADOP Phase=Prepare Fails with Error

How to Synchronize FND_NODES, ADOP_VALID_NODES, and FND_OAM_CONTEXT_FILES in 12.2 When ADOP Phase=Prepare Fails with Error

After a recent change or addition to the 12.2 E-Business Suite application tiers, adop phase=prepare fails with the following error:

Error
$ adop phase=prepare

Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:

Validating credentials...
Initializing.
Run Edition context : /u01/app/fs1/inst/apps/SID_erpnode/appl/admin/SID_erpnode.xml
Patch edition context: /u01/app/fs2/inst/apps/ SID_erpnode /appl/admin/SID_erpnode.xml
Patch file system free space: 26.63 GB

Validating system setup.
Node registry is valid.

Checking for existing adop sessions.
Continuing with existing session [Session ID: 10].
Session Id : 10
Prepare phase status : NOT COMPLETED
Apply phase status : NOT COMPLETED
Cutover phase status : NOT COMPLETED
Abort phase status : NOT COMPLETED
Session status : FAILED

===========================================================================
ADOP (C.Delta.9)
Session ID: 10
Node: erpnode
Phase: prepare
Log: /u01/app/fs_ne/EBSapps/log/adop/10/20171012_132509/adop.log
===========================================================================

Validating configuration on node: [erpnode].
Log: /u01/app/fs_ne/EBSapps/log/adop/10/20171012_132509/prepare/validate/erpnode
[WARNING]: There could be issues while validating the ports used for E-Business Suite instance against ports used in /etc/services. Refer the log file for more details.
[WARNING]: Found invalid cross references in FS config files.
[ERROR]: The value of s_patch_service_name is not set correctly in atleast one of the context files.
[UNEXPECTED]Error occurred running "perl /u01/app/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPValidations.pl -contextfile=/u01/app/fs1/inst/apps/SID_erpnode/appl/admin/SID_erpt_erpnode.xml -phase=prepare -logloc=/u01/app/fs_ne/EBSapps/log/adop/10/20171012_132509/prepare/validate/erpnode -promptmsg=hide"
[UNEXPECTED]Error 1 occurred while Executing txkADOPValidation script on erpnode

[STATEMENT] Please run adopscanlog utility, using the command

"adopscanlog -latest=yes"

to get the list of the log files along with snippet of the error message corresponding to each log file.

Cause
There is a synchronization error between fnd_nodes, adop_valid_nodes, and fnd_oam_context_files.

Solution
Due to the method required for "cleaning out" / "re-synchronizing" the following tables, it is EXPECTED / REQUIRED that the Applications have been shutdown.
The only thing running should be the Database Tier.
Note: A full backup should also be taken before any testing begins.

Test the following steps in a development instance, and then migrate accordingly once the desired result is confirmed:

1. Backup the fnd_oam_context_files, fnd_nodes, and adop_valid_nodes tables in the EBS env:

 sqlplus applsys/pwd

 create table fnd_oam_context_files_bkp as select * from fnd_oam_context_files;
create table fnd_nodes_bk as select * from fnd_nodes;
create table adop_valid_nodes_bk as select * from adop_valid_nodes;

2. Truncate the following tables:

truncate table fnd_oam_context_files;

truncate table fnd_nodes;

truncate table adop_valid_nodes;

3. Run AutoConfig on the DB tier
Confirm Autoconfig completes successfully

4. Run Autoconfig on the run file system.
    Source the Environment File to switch to run file system
    . /u01/app/EBSapps.env RUN

Confirm Autoconfig completes successfully

5. Run Autoconfig on the patch file system
   Source the Environment File to switch to patch file system
   . /u01/app/EBSapps.env patch

 Due to the method required for "cleaning out" / "re-synchronizing" the following tables, it is           EXPECTED / REQUIRED that the Applications have been shutdown.
 The only thing running should be the Database Tier.
 Before running Autoconfig on the patch file system the ebs_login trigger MUST be disabled
 After the successful completion of Autoconfig the ebs_login trigger MUST be re-enabled.
 This needs to be done as the SYSTEM schema user.
   a. Disable the ebs_login trigger using the following SQL.
     SQL> alter trigger ebs_logon disable;
  At this time Run autoconfig with the patch env sourced.
  Make sure Autoconfig completes ok
   b. Enable the ebs_login trigger using the following SQL.
  SQL> alter trigger ebs_logon enable;

6. After Autoconfig has been run successfully on all nodes, run the following two (2) queries in order to verify the tables have been correctly populated:
SQL> select node_id, platform_code, support_db D, support_cp C, support_admin A,
support_forms F, support_web W, node_name, server_id, server_address, domain, webhost, virtual_ip, status from fnd_nodes order by node_id;

SQL> select NAME,VERSION,PATH, STATUS from FND_OAM_CONTEXT_FILES;

Check adop with prepare phase. This time it should complete successfully.
$ adop phase=prepare

The prepare phase completed successfully.
adop exiting with status = 0 (Success)

Reference: Doc ID 2064223.1

Linux Command Reference

    Tools to Monitor Linux PerformanceLinux

     How to Close and Open Ports with iptables 

     How to Reset "root" Password with GRUB on Enterprise Linux

     Forcefully unmount a Linux disk partition

     Get Tape Serial Num in Linux

     Mount Windows CDROM in Linux

     VNC in LinuxLinux

Validating Parameters in Oracle Reports using PL/SQL

Parameters can be populated and validated using various srw pl/sql triggers.

The following gives examples of:

Validation trigger in parameter spread sheet 

Before parameter form trigger

After parameter form trigger

Before report trigger   

Examples of validation triggers on the property sheet for parameter PARAM_SAL. 

Query: select * from emp where sal > :PARAM_SAL

These functions validate just this one trigger. The validation occurs when 

the user hits next field after inputting a value for the parameter. When the 

trigger is failed it returns to the parameter form.

Example 1:

This trigger aborts the report execution if no rows match the query criteria 

once the user has entered a value for param_sal.

function PARAM_SALValidTrigger return boolean is

hold_count number(4);

hold_sal  number(10);

begin

  hold_sal := :param_sal;

  select count(*) into hold_count from emp where sal > hold_sal; 

  if hold_count = 0 then

     srw.message(001,'this report returns no employees');

     raise srw.program_abort;

  end if;

  return(true);

end;

Example 2

In this trigger the users value for param_sal is compared to the maximum 

salary in the EMP table. If it is greater the report execution is aborted.

example query for your report: select * from emp where sal >= :parm_sal

function PARAM_SALValidTrigger return boolean is

hold_max number(10);

begin

  select max(sal) into hold_max from emp;

  if :param_sal > hold_max then

     srw.message(002,'SAL must be equal to or less than MAX(SAL)= '||

     to_char(hold_max));

     raise srw.program_abort;

  end if;

  return(true);

end;

Example 3

'Before parameter form' triggers can be used set up the environment for the

report e.g. create a table. It can also be used to supply default parameter 

values.

This function populates the initial value of the parameter param_sal with the 

lowest salary value from the emp table.

function BeforePForm return boolean is

min_sal number(10);

begin

  select min(sal) into min_sal from emp;

  :param_sal := min_sal;

  return(true);

end;

Example 4

'After parameter form' triggers can be used to validate a combination of 

parameters. Failing results in a return to the PARAMETER FORM.

Query: select * from emp where job=:jb and deptno=:dt

function  AfterPForm return boolean is

begin

  if (:dt = 20) and (:jb = 'MANAGER') then 

     srw.message(003,'cannot report on Managers in Dept 20');

     raise srw.program_abort;

  end if;

  return(true);

end;

Example 5

'Before report triggers' can be used to validate a combination of parameters.

The example below is the same as the after parameter form trigger above 

other than on failure return is passed to the MAIN MENU.

A 'Before Report Trigger' is executed right before formatting the report,

that is after initializing all internal structures, opening all SQL cursors

etc. In other words, after 'compiling' the report definition.

A second use of this trigger may be to launch a number of other reports

using the SRW.RUN_REPORT procedure.

function BeforeReport return boolean is

begin

  if (:dt = 20) and (:jb = 'MANAGER') then 

     srw.message(004,'cannot report on Managers in Dept 20');

     raise srw.program_abort;

  end if;

  return(true);

end;