Sample Code to create Logon triggers to trace particular sessions

create or replace trigger set_trace after logon on database

   begin

    if user  in ('SA') then

     execute immediate 'alter session set timed_statistics=true';

     execute immediate 'alter session set max_dump_file_size=unlimited';

     execute immediate 'alter session set sql_trace=true';

    end if;

   exception

    when others then 

    null;

  end;

  /

SQL> exec dbms_monitor.session_trace_enable(248);

PL/SQL procedure successfully completed.

SQL> -- execute some sql in the other session

SQL> -- turn off trace

SQL> exec dbms_monitor.session_trace_disable(248);

Capturing details in custom tables through triggers

CREATE OR REPLACE TRIGGER orders_after_delete
AFTER DELETE
    ON orders
    FOR EACH ROW

DECLARE
    v_username varchar2(10);

BEGIN

    -- Find username of person performing the DELETE on the table
    SELECT user INTO v_username  FROM dual;

    -- Insert record into audit table
    INSERT INTO orders_audit
     ( order_id,
       quantity,
       cost_per_item,
       total_cost,
       delete_date,
       deleted_by)
    VALUES
     ( :old.order_id,
       :old.quantity,
       :old.cost_per_item,
       :old.total_cost,
       sysdate,
       v_username );

END;

OK, one final way to set and get trace, is to use the SQL*Plus AUTOTRACE facilities. There are a few settings that you can use.

These are as follows:

set autotrace off - The default - no output

set autotrace on explain - This shows only the optimizer path

set autotrace on statistics - This only shows SQL statistics

set autotrace on - Includes both of the above

set autotrace traceonly - As above but the query output is not displayed

How to check EBS application URL is working from unix (at OS level)

/usr/bin/curl "http://server01.adbank.int:8050/OA_HTML/AppsLogin" |head -n 1 | grep "HTTP/1.[01] [23].."

How to Enable Table level Auditing in Oracle Database

Below DB parameter needs to be set

Show parameter audit_trail=db

audit insert, update, delete on schema.tablename by ACCESS;

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE     BY user
 BY ACCESS;

audit insert, update, delete on apps.xyz by ACCESS;

Once Audit is enabled the audit trail trace file can viewed to see the transaction being recorded to trace.

Also at database level following query can give the details of transactions being done on the table for which audit has been enabled

select os_username,username,userhost,owner,obj_name,action_name,timestamp from dba_audit_object
where owner='APPS' and obj_name like 'XYZ'

Script To Create EBS User from Backend (Oracle Apps API)

set serveroutput on

Declare
 lc_user_name                          VARCHAR2(100)   := 'USER123';
   lc_user_password                  VARCHAR2(100)   := 'oracle123';
   ld_user_start_date                  DATE                      := trunc(sysdate);
   ld_user_end_date                   VARCHAR2(100)  := NULL;
   ld_password_date                  VARCHAR2(100)  := trunc(sysdate);
   ld_password_lifespan_days  NUMBER              := 90;
   ln_person_id                             NUMBER              := NULL;
   lc_email_address                     VARCHAR2(100) := 'AJIT.KUMAR@injazat.com';

BEGIN
  fnd_user_pkg.createuser
  (  x_user_name                            => lc_user_name,
     x_owner                                    => NULL,
     x_unencrypted_password     => lc_user_password,
     x_start_date                              => ld_user_start_date,
     x_end_date                               => ld_user_end_date,
     x_password_date                    => ld_password_date,
     x_password_lifespan_days   => ld_password_lifespan_days,
     x_employee_id                        => ln_person_id,
     x_email_address                     => lc_email_address
 );
 COMMIT;
EXCEPTION
       WHEN OTHERS THEN
                       ROLLBACK;
                       DBMS_OUTPUT.PUT_LINE(SQLERRM);
                       DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
BEGIN
fnd_user_pkg.addresp ('USER123','SYSADMIN','SYSTEM_ADMINISTRATOR','STANDARD','Add Responsibility to USER using pl/sql',SYSDATE,SYSDATE + 100);
commit;
dbms_output.put_line('Responsibility Added Successfully');
exception
        WHEN others THEN
                dbms_output.put_line(' Responsibility is not added due to ' || SQLCODE || substr(SQLERRM, 1, 100));
                ROLLBACK;
END;

Commonly Used Oracle RAC Commands

srvctl status database -d uat

srvctl status listener

srvctl config scan

srvctl status scan_listener

srvctl config listener -a

srvctl stop instance -i uat1 -d uat

lsnrctl stop uat1

lsnrctl start uat1

srvctl start database -d uat

srvctl start instance -i uat1 -d uat

srvctl modify database -d uat -s mount -r PHYSICAL_STANDBY

srvctl start database -d uat -o mount

crs_stats -t 

Database CPU Patching in 2 Node Oracle RAC (Cluster environment)

opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Node 1(Instance1):
==================
srvctl stop instance -i uat1 -d uat
lsnrctl stop uat1

cd /home/dbuat/CPU_PATCH_APR2018/27338049
opatch apply

Sqlplus / as sysdba
startup
@catbundle.sql psu apply
shut immediate

srvctl start instance -i uat1 -d uat

Node 2 (Instance2):
==================
srvctl stop instance -i uat2 -d uat
lsnrctl stop uat2

cd /home/dbuat/CPU_PATCH_APR2018/27338049
opatch apply

srvctl start instance -i uat2 -d uat