Thursday, April 14, 2016

Audit Trail for Custom Table in Oracle Apps - Step By Step


Hi All ...

Here the step by step for Audit Trail for Custom Table in Oracle Apps

1. Register Custom Schema 
Navigate to System Administrator Menu/Security/ORACLE/Register

2. Ensure that Audit on the Application is Enabled
Navigate to System Administrator Menu Security/AuditTrail/Install

The owner of table XX_TABLE  is XX_SCHEMA. Hence query on 
XX_SCHEMA to ensure that Audit is enabled for this Application.


3. Register table,columns and primary key
Here the procedure to register the custom able,columns and primary key in Oracle Applications. Install the procedure on your DB and run the following:

begin
register_table('XX_TABLE','XX_SCHEMA'); /* (table name, table owner) */
commit;
end;

Procedure "register_table":

CREATE OR REPLACE PROCEDURE register_table (
table_name VARCHAR2,
application_short_name VARCHAR2
)
AS
status VARCHAR2 (10);

CURSOR c_columns ( p_table_name all_tab_columns.table_name%TYPE )
IS
SELECT column_name,
data_type,
data_length,
nullable,
ROWNUM,
data_precision,
data_scale
FROM all_tab_columns
WHERE table_name = p_table_name;

CURSOR c_constraints (p_table_name all_tab_columns.table_name%TYPE,
p_application_short_name all_tab_columns.owner%TYPE )
IS
SELECT constraint_name,
table_name,
status
FROM all_constraints
WHERE table_name = p_table_name AND owner = p_application_short_name AND constraint_type = 'P';

CURSOR c_constraint_columns (
p_table_name all_tab_columns.table_name%TYPE,
p_application_short_name all_tab_columns.owner%TYPE
)
IS
SELECT acc.constraint_name,
acc.column_name,
acc.POSITION
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.table_name = p_table_name
AND ac.constraint_type = 'P'
AND ac.owner = p_application_short_name;
BEGIN

DBMS_OUTPUT.put_line ('Registering Table '|| table_name ||'in application ' || application_short_name);

ad_dd.register_table (p_appl_short_name => application_short_name,
p_tab_name => table_name,
p_tab_type => 'T'
);

FOR r_columns IN c_columns (table_name)
LOOP

DBMS_OUTPUT.put_line ('Registering Column '|| r_columns.column_name);

ad_dd.register_column (p_appl_short_name => application_short_name,
p_tab_name => table_name,
p_col_name => r_columns.column_name,
p_col_seq => r_columns.ROWNUM,
p_col_type => r_columns.data_type,
p_col_width => r_columns.data_length,
p_nullable => r_columns.nullable,
p_translate => 'N',
p_precision => r_columns.data_precision,
p_scale => r_columns.data_scale
);

END LOOP;

FOR r_constraints IN c_constraints (table_name, application_short_name)
LOOP

DBMS_OUTPUT.put_line ('Creating Primary Key Constraint ' || r_constraints.constraint_name);

SELECT DECODE (r_constraints.status,
'ENABLED', 'Y',
'N'
)
INTO status
FROM DUAL;

ad_dd.register_primary_key (p_appl_short_name => application_short_name,
p_key_name => r_constraints.constraint_name,
p_tab_name => table_name,
p_description => 'Primary Key for Table '|| table_name,
p_key_type => 'D',
p_audit_flag => 'Y',
p_enabled_flag => status
);
END LOOP;

FOR r_constraint_columns IN c_constraint_columns (table_name, application_short_name)
LOOP

DBMS_OUTPUT.put_line ( 'Registering Primary Key Column '||
r_constraint_columns.column_name||
' for Constraint '||
r_constraint_columns.constraint_name);

ad_dd.register_primary_key_column (p_appl_short_name => application_short_name,
p_key_name => r_constraint_columns.constraint_name,
p_tab_name => table_name,
p_col_name => r_constraint_columns.column_name,
p_col_sequence => r_constraint_columns.POSITION
);
END LOOP;
END register_table;




4. Create Audit Group
Once you table registered,navigate to System Administrator Menu Security/AuditTrail/Groups 

Application Name: XX Custom Schema
Audit Group: XX Audit
Group State: Enabled

Now, add audit tables to this group[you can add as many tables]
User Table Name: XX_TABLE


5. Run Concurrent program “AuditTrail Update Tables”

This process can be run from System Administrator responsibility. It has no parameter. Running this process will create the Audit tables and the triggers that manage Audit data.


6. Ensure that Audit Tables have been created as expected
SELECT object_name, object_type
FROM all_objects
WHERE object_name LIKE 'XX_TABLE_A%'

OBJECT_NAME                            OBJECT_TYPE
--------------------------                      --------------------------
XX_TABLE_A                               TABLE
XX_TABLE_A                               SYNONYM
XX_TABLE_AC                            TRIGGER
XX_TABLE_AC1                          VIEW
XX_TABLE_AD                            TRIGGER
XX_TABLE_ADP                          PROCEDURE
XX_TABLE_AH                            TRIGGER
XX_TABLE_AI                              TRIGGER
XX_TABLE_AIP                            PROCEDURE
XX_TABLE_AT                             TRIGGER
XX_TABLE_AU                            TRIGGER
XX_TABLE_AUP                          PROCEDURE
XX_TABLE_AV1                           VIEW

Fine, this proves that the concurrent program in Step 5 did its job.
Optionally, you may run concurrent process “AuditTrail Report for Audit Group Validation” to validate the success of Audit Table/Trigger creation.

7. Add further columns for Audit Trail
By default Oracle will Audit Trail on all columns that are a part of first available Unique Index on XX_TABLE.
However further columns can be added to the Audit Trail. Lets say you wish to Audit Trail on Column Meaning too.
Navigate to System Administrator Menu Security/AuditTrail/Tables

You can add additional columns to audit trail and re-execute Step 5.
Please note that adding columns for Audit could have been done immediately after Step 4.

You are DONE...

Usefull notes:
How To Enable Auditing On A Table (Doc ID 1359749.1)
Unable to Enable Audit Trail for Custom Objects (Doc ID 433527.1)


Good Luck ...

Thursday, January 14, 2016

Configure Shared Appl Top in EBS R12 - Step by Step


Hi All ...

Here the step by step of configure Shared Appl Top  in EBS R12:

1.   Run adpreclone procedure in DB and APPL (on Main server):

·         On DB Tier:
        cd $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME
        perl adpreclone.pl dbTier
·         On APPL Tier:
         cd $INST_TOP/admin/scripts

         perl adpreclone.pl appsTier

2.   Stop APPL_TIER (on Main server)
3.   Add the F5 ip hosname.domain hostname to /etc/hosts on all servers.
4.   Connect the mount of the application tier to second appl server. 
5.   Add node (run the commands on second applserver ).
    cd $COMMON_TOP/clone/bin; (take a path from main appl node)
    perl adclonectx.pl addnode contextfile=<echo $CONTEXT_FILE from main node>
    *All servers need to be enabled
    perl <echo $AD_TOP/bin from main node >/adconfig.pl contextfile=<specify the path to the      new context file from previous step>
   Edit .bash_profile to point to new env files.
6.   Run AutoConfig in both nodes (main node need to be run as the last one).
7.   Edit Context file for 2 nodes

s_applcsf                            
Same on all nodes
s_fndreviverpiddir         
Same on all nodes
APPLDCP
ON
s_appltmp
Same on all nodes
s_temp
Same on all nodes
s_custom_file
Same is in main node
s_webentryhost 
F5 host
s_login_page 
F5 host
s_external_url
F5 host
8.   Run AutoConfig in both nodes (main node need to be run as the last one).
9.   Start APPL tier in both nodes (main and after that secondary).
10. Enable Internal Monitor Managers for both nodes:

Go to Concurrent manager > Define > Look for Internal Monitor% > Work Shiftes

 Work Shift:Standard
  Processes: 1 > Save
 Do the same for the second one

Activate both managers.

Query which user session is on which application servers:
 select nodes.node_name,usr.user_name,usr.user_id,ses.function_type
from icx_sessions ses,
     fnd_nodes nodes,
     fnd_user usr
where ses.node_id=nodes.node_id
and ses.guest='N'
and ses.user_id=usr.user_id
order by usr.user_id,nodes.node_name

You are done.
Good luck ...

Useful documents:
Sharing The Application Tier File System in Oracle E-Business Suite Release 12 (Doc ID 384248.1)
Using Load-Balancers with Oracle E-Business Suite Release 12 (Doc ID 380489.1).

Tuesday, July 21, 2015

How to download Oracle Software from Edelivery or Oracle Metalink by script


Hi All ...

Here the step by step guide how to download Oracle software with script.
Many of you already tried to do it from Oracle Edelivery site and know that you need to download one by one and it can take a lot of time. Also there are some scripts that can do it for you , but need to install some plug-ins to google chrome or Mozilla to collect the cookies.

This is the guide that I got from Oracle Support (thanks guys) and hope will make you life a little bit easy.

1. Log-in to http://support.oracle.com 
2. Go to Certifications tab

3. Choose the software,release and platform that  you want to download  and press Search
In my I want to download EBS 12.2.4 installation release for Oracle Linux 5

4. Click on Version on  Number of Releases / Versions column
 5. Choose the release. At this time you can re-choose another release
 6. Select the Product and press Download
 7. Accept the Agreement and press Next
8. Now you will get the all zips that you can download. You can download it one by one or just press on WGET options 
 9. Press on Download.sh to get the scripts and save it. Please pay attention, the script will work only for 8 hours after the creation
10. That's it. Just enter you oracle account password and you can start downloading.


TIP: the release can include alot of file that you will not use, so just remove or remark the WGET line form the script (for example all NLS packages) 

Good Luck ...