Sunday, April 30, 2017

Reverse Proxy Configuration

Case History: Implementing a Reverse Proxy Alone in a DMZ Configuration - R12 (Doc ID 726953.1)

1. Create a directory for External Tier INSTANCE TOP

cd /u01/appsr12/inst/apps
mkdir SID_appshost_external

2.  Create a new Context File for the Virtually External Release 12 Web Node

cd $COMMON_TOP/clone/bin
perl adclonectx.pl contextfile=/u01/appsr12/inst/apps/SID_appshost/appl/admin/SID_appshost.xml outfile=/home/appltest/external_env/SID_appshost_external.xml

Target System Hostname (virtual or normal) [appshost] : appshost_external

Target System Database SID : SID

Target System Database Server Node [appshost_external] : dbhost.xxxx.com

Target System Base Directory : /u02/appsr12



Target System Instance Home Directory [/u01/appsr12/inst] : /u01/appsr12/inst

Username for the Applications File System Owner [appldeb1] : appltest

Group for the Applications File System Owner [dba] :

Target System Root Service [enabled] :

Target System Web Entry Point Services [enabled] :

Target System Web Application Services [enabled] :

Target System Batch Processing Services [enabled] : disabled

Target System Other Services [disabled] :

Do you want to preserve the Display [appshost:1.0] (y/n)  : n

Target System Display [appshost_external:0.0] :

Do you want the the target system to have the same port values as the source system (y/n) [y] ? : n

Target System Port Pool [0-99] : 6

3. Edit External Context File

a)Update value for server_ip to ==> SID_appshost_external

from
server_ip_address oa_var="s_server_ip_address"
to
server_ip_address oa_var="s_server_ip_address">appshost_external

b) Update s_dbport to correct value ==> 20101

4. Check s_config_home and make sure to be pointing to External Instance top

 grep s_config_home $CONTEXT_FILE
       
5. Make sure below directories exist or else create them, pointing to External Tier

s_ora_config_home -- /u01/appsr12/inst/apps/SID_appshost_external/ora
s_logs_dir -- /u01/appsr12/inst/apps/SID_appshost_external/logs
s_pids_dir -- /u01/appsr12/inst/apps/SID_appshost_external/pids

6. Check and confirm below values in External Context File.

 egrep -e 's_url_protocol|s_local_url_protocol' $CONTEXT_FILE
    egrep -e 's_webentryurlprotocol' $CONTEXT_FILE
egrep 's_webport|s_active_webport|s_webentryhost|s_webentrydomain' $CONTEXT_FILE
    egrep 's_login_page|s_external_url' $CONTEXT_FILE
        
7. Run adconfig.sh with the external tier context file. This will create all the directories under new INST_TOP. Verify and confirm.




8. Update profile options to server-resp for external host.

cd $FND_TOP
SQL> @./patch/115/sql/txkChangeProfH.sql SERVRESP


9. Run adautocfg.sh on External and then on internal env's.

source $APPL_TOP/APPSSID_appshost_external.env
adautocfg.sh

source $APPL_TOP/APPSSID_appshost.env
adautocfg.sh

10. Check SERVER_ADDRESS in fnd_nodes for external tier -- should be pointing to external hostname(appshost_external):

NODE_NAME                      SERVER_ADDRESS                 HOST                      DOMAIN
------------------------------ ------------------------------ ------------------------- -------------------
AUTHENTICATION                 *
appshost_EXTERNAL        appshost_external        appshost_external   xxxx.com
dbhost                                                dbhost            xxxx.com
appshost                 10.150.8.210                   appshost            xxxx.com
       

Apps password not working

Someone changed apps password using 'alter user' which is not a good practice.

Now during a clone, password change is not working using FNDCPASS.


1) SEC_CASE_SENSITIVE_LOGON parameter is already set to FALSE

2) select fnd_web_sec.validate_login('GUEST','ORACLE') from dual;

FND_WEB_SEC.VALIDATE_LOGIN('GUEST','ORACLE')
--------------------------------------------------------------------------------
Y
3) There is no error on FNDCPASS but password change is not successful.



So, this is what we did as a fix,


Changed apps password to default apps/apps -- using 'alter user'.

Updated password in FND_ORACLE_USERID to apps using encrypted value.

and then re-tried FNDCPASS, this time it was successful.


EBSPROD> create table FND_ORACLE_USERID_BAK as (select * from FND_ORACLE_USERID);

Table created.

EBSPROD> create table FND_USER_BAK as (select * from FND_USER);

Table created.


SQL> ALTER USER apps IDENTIFIED BY apps;

User altered.

SQL> ALTER USER applsys IDENTIFIED BY apps;

User altered.

Below is the value for encrypted apps password --


SQL> update apps.FND_ORACLE_USERID set ENCRYPTED_ORACLE_PASSWORD = 'ZGA34EA20B5C4C9726CC95AA9D49EA4DBA8EDB705CB7673E645EED570D5447161491D78D444554655B87486EF537ED9843C8' where ORACLE_USERNAME in ('APPS', 'APPLSYS');

2 rows updated.

SQL> commit;

Commit complete.

Wednesday, April 26, 2017

Track session activity in DB

What is a session doing in DB?


*** this sql can not use on version < 10G ***
select 'Session ID=' ||SID ||'(Inst '||INST_ID||') IS BLOCKED BY Session ID='|| BLOCKING_SESSION || '(Inst '||BLOCKING_INSTANCE||')' from gv$session where BLOCKING_SESSION is not null;
... OK!!! no blocking, time to use username to find out more about that session, investigation and answer user -)
SQL> select sid, serial#, username, program, event, p1, status, sql_id from v$session where username='APPS';

SID SERIAL# USERNAME PROGRAM EVENT P1 STATUS SQL_ID
---------- ---------- -------- ----------------------------------- ------------------------------ -- -------- -------------
45 43 DEMO sqlplus@linuxclient (TNS V1-V3) direct path read 4 ACTIVE ckg31y8rg6psa
That showed SID, EVENT, STATUS and SQL_ID, then checked about SQL statement.
SQL> select sql_text, disk_reads, buffer_gets, cpu_time from v$sqlstats where sql_id='ckg31y8rg6psa';

SQL_TEXT DISK_READS BUFFER_GETS CPU_TIME
---------------------------------------------------------------------- ---------- ----------- ----------
select id, sum (id + 10000)/1 from tb_test where id >1000 group by id 23530 23312 95802415
Checked Xplan:
SQL> select * from table(dbms_xplan.display_cursor('f9zzgqzd1gdwg','','typical'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID ckg31y8rg6psa, child number 0
-------------------------------------
select id, sum (id + 10000)/1 from tb_test where id >1000 group by id

Plan hash value: 2744048799
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 856 (100)| |
| 1 | HASH GROUP BY | | 1179K| 14M| 856 (7)| 00:00:11 |
|* 2 | TABLE ACCESS FULL| TB_TEST | 1179K| 14M| 811 (2)| 00:00:10 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID">1000)
Enabled trace on that session:
SQL> exec dbms_monitor.session_trace_enable(45,43);

PL/SQL procedure successfully completed.

SQL> select sql_trace from v$session where sid=45 and serial# =43;

SQL_TRACE
--------
ENABLED

SQL> select p.spid, p.tracefile from v$session s, v$process p where s.paddr = p.addr and s.sid=45;

SPID TRACEFILE
------------------------ -------------------------------------------------------
970 /oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_970.trc
Checked from trace file, what that session was doing.
$ tail -f /oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_970.trc
WAIT #5417428: nam='direct path read' ela= 120 file number=4 first dba=617 block cnt=15 obj#=81188 tim=1298022456269076
Or used "tkprof" to make trace report file.
$ tkprof /oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_970.trc out.prf sys=no
Example:
SQL ID: ckg31y8rg6psa Plan Hash: 2744048799

select id, sum (id + 10000)/1
from
tb_test where id >1000 group by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.05 0.07 398 80 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 68875 25.20 26.06 5384 5709 0 1033097
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 68879 25.25 26.13 5782 5789 0 1033097
What is the answer? - that session was waiting at 'direct path read' event on file_id=4:
SQL> select tablespace_name from dba_data_files where file_id=4;

TABLESPACE_NAME
------------------------------
USERS
and SQL in that session was doing "TABLE ACCESS FULL" and fetched many rows.

then disabled trace on that session:
SQL> exec dbms_monitor.session_trace_disable(45,43);

PL/SQL procedure successfully completed.

SQL> select sql_trace from v$session where sid=45 and serial# =43;

SQL_TRACE
--------
DISABLED

Log switches trend

SET PAGESIZE 90
SET LINESIZE 150
set heading on
column "00:00" format 9999
column "01:00" format 9999
column "02:00" format 9999
column "03:00" format 9999
column "04:00" format 9999
column "05:00" format 9999
column "06:00" format 9999
column "07:00" format 9999
column "08:00" format 9999
column "09:00" format 9999
column "10:00" format 9999
column "11:00" format 9999
column "12:00" format 9999
column "13:00" format 9999
column "14:00" format 9999
column "15:00" format 9999
column "16:00" format 9999
column "17:00" format 9999
column "18:00" format 9999
column "19:00" format 9999
column "20:00" format 9999
column "21:00" format 9999
column "22:00" format 9999
column "23:00" format 9999
SELECT * FROM (
SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0), '99')) "00:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0), '99')) "01:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0), '99')) "02:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0), '99')) "03:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0), '99')) "04:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0), '99')) "05:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0), '99')) "06:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0), '99')) "07:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0), '99')) "08:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0), '99')) "09:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0), '99')) "10:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0), '99')) "11:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0), '99')) "12:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0), '99')) "13:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0), '99')) "14:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0), '99')) "15:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0), '99')) "16:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0), '99')) "17:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0), '99')) "18:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0), '99')) "19:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0), '99')) "20:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0), '99')) "21:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0), '99')) "22:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0), '99')) "23:00"
    FROM V$LOG_HISTORY
       WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
          GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
  ) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
  );

How do I track NFS (network filesystem) client metrics (disk I/O) on Linux operating system?


You can use any one of the following tool/files to get stats about NFS on Linux operating systems:
The iostat command is used for monitoring system input/output device loading by observing the time the devices are active in relation to their average transfer rates. The iostat command generates reports that can be used to change system configuration to better balance the input/output load between physical disks.
Another recommended option is to use nfsstat command to displays statistics kept about NFS client and server activity, active user activity, exported directories, and mount information. In the absence of any options, nfsstat displays NFS client and server statistics.
/proc/net/rpc/nfsd - procfs-based interface to kernel NFS server statistics. Use cat command to see stats.
/proc/net/rpc/nfs - procfs-based interface to kernel NFS client statistics. Use cat command to see nfs client stats.
/proc/mounts - procfs-based interface to the mounted filesystems
iostat command syntax and examples

The -n option displays the NFS-directory statistic. Data displayed are valid only with kernels 2.6.17 and newer. This option is exclusive to the -x option:
# iostat -x -n

OR
# iostat -n

Sample outputs:

Linux 2.6.32-358.23.2.el6.x86_64 (server1.cyberciti.biz) 11/11/2013 _x86_64_ (8 CPU)
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.30    0.00    0.43    0.03    0.00   98.24
Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.04    91.47    0.19  100.13     7.12  1532.82    15.35     0.14    1.35   0.08   0.83
Say hello to nfstat command

The nfsstat displays statistics kept about NFS client and server activity:
# nfsstat

Sample outputs:

Fig.01: nfsstat in action
Fig.01: nfsstat in action
Show all information about all versions of NFS
# nfsstat -o all -234

OR
# nfsstat --verbose -234

Show all information about active versions of NFS
# nfsstat -o all

Show statistics for NFS version 3 server
# nfsstat --nfs --server -3

Show information about mounted NFS filesystems
# nfsstat -m

How to Enable 'FND Debug' from backend

Unable to launch login page with no errors in logfiles.
In such cases you should enabled 'FND Debug' from backend and bounce apache.
A hyperlink will appear on login page like "click here for more details"...
Click on link you can see exact error and can fix...


begin
 if fnd_profile.save(X_NAME => 'FND_DIAGNOSTICS',X_VALUE => 'Y',X_LEVEL_NAME => 'SITE') then
   dbms_output.put_line('Success');
 else
   dbms_output.put_line('Fail');
 end if;
commit;
end;
/

In my case regenerating jsps from adadmin helped.

How to increase PROCESSES initialization parameter:



1.    Login as sysdba
    sqlplus / as sysdba
 
2. Check Current Setting of Parameters
    sql> show parameter sessions
    sql> show parameter processes
    sql> show parameter transactions

3.    If you are planning to increase "PROCESSES" parameter you should also plan to increase "sessions and "transactions" parameters
    A basic formula for determining  these parameter values is as follows:
 
        processes=x
        sessions=x*1.1+5
        transactions=sessions*1.1
     
4.    These paramters can't be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instance.
    sql> alter system set processes=500 scope=spfile;
    sql> alter system set sessions=555 scope=spfile;
    sql> alter system set transactions=610 scope=spfile;
    sql> shutdown abort
    sql> startup


alter system set processes=500 scope=spfile;
    alter system set sessions=555 scope=spfile;
    alter system set transactions=610 scope=spfile;

show parameter sessions
    show parameter processes
    show parameter transactions

send an email from Linux with attachement

echo "Hi, Please find attached"| mail -a readme.txt -s "attached file" Amrutharao.d@gmail.com

Rebuild WorkFlow queues

Backup
=======
sqlplus apps/<apps_passwd> @$FND_TOP/sql/wfaqback.sql WF_DEFERRED

Drop DEFERRED Queues
====================

-bash-4.1$ sqlplus apps/<apps_passwd> @$FND_TOP/patch/115/sql/wfevqued.sql applsys <apps_passwd> WF_DEFERRED

Drop Indexes
=============
-bash-4.1$ sqlplus applsys/<apps_passwd>
 drop index WF_ERROR_N1;
 drop index WF_JAVA_ERROR_N1;
 drop index WF_DEFERRED_N1;
 drop index WF_JAVA_DEFERRED_N1;

Utlrp on DB node
================
SQL> @?/rdbms/admin/utlrp.sql

Create the (deleted) queues
===========================

-bash-4.1$ sqlplus apps/<apps_passwd> @$FND_TOP/patch/115/sql/wfevquc2.sql applsys <apps_passwd> WF_DEFERRED apps 5 3600 86400

Create subscribers
==================
sqlplus apps/<apps_passwd> @$FND_TOP/patch/115/sql/wfbesqsubc.sql applsys <apps_passwd>

Re-enqueue backed up messages
=============================
-bash-4.1$ sqlplus apps/<apps_passwd> @$FND_TOP/sql/wfaqrenq.sql WF_DEFERRED

backup
======
sqlplus apps/<apps_passwd> @$FND_TOP/sql/wfaqback.sql WF_JAVA_ERROR

Drop DEFERRED Queues
====================

-bash-4.1$ sqlplus apps/<apps_passwd> @$FND_TOP/patch/115/sql/wfevqued.sql applsys <apps_passwd> WF_JAVA_ERROR

Create the (deleted) queues
===========================

-bash-4.1$ sqlplus apps/<apps_passwd> @$FND_TOP/patch/115/sql/wfjmsqc2.sql applsys <apps_passwd>
sqlplus apps/<apps_passwd> @$FND_TOP/patch/115/sql/wfbesqc.sql applsys <apps_passwd>

Create subscribers
==================
sqlplus apps/<apps_passwd> @$FND_TOP/patch/115/sql/wfbesqsubc.sql applsys <apps_passwd>

Re-enqueue backed up messages
=============================
-bash-4.1$ sqlplus apps/<apps_passwd> @$FND_TOP/sql/wfaqrenq.sql WF_JAVA_ERROR

backup
======
sqlplus apps/<apps_passwd> @$FND_TOP/sql/wfaqback.sql WF_ERROR

Drop DEFERRED Queues
====================

-bash-4.1$ sqlplus apps/<apps_passwd> @$FND_TOP/patch/115/sql/wfevqued.sql applsys <apps_passwd> WF_ERROR

Create the (deleted) queues
===========================

-bash-4.1$ sqlplus apps/<apps_passwd> @$FND_TOP/patch/115/sql/wfevquc2.sql applsys <apps_passwd> WF_ERROR apps 5 3600 86400

Create subscribers
==================
sqlplus apps/<apps_passwd> @$FND_TOP/patch/115/sql/wfbesqsubc.sql applsys <apps_passwd>

Re-enqueue backed up messages
=============================
-bash-4.1$ sqlplus apps/<apps_passwd> @$FND_TOP/sql/wfaqrenq.sql WF_ERROR

backup
======
sqlplus apps/<apps_passwd> @$FND_TOP/sql/wfaqback.sql WF_JAVA_DEFERRED

Drop DEFERRED Queues
====================

-bash-4.1$ sqlplus apps/<apps_passwd> @$FND_TOP/patch/115/sql/wfevqued.sql applsys <apps_passwd> WF_JAVA_DEFERRED

Create the (deleted) queues
===========================

-bash-4.1$ sqlplus apps/<apps_passwd> @$FND_TOP/patch/115/sql/wfbesqc.sql applsys <apps_passwd>

Create subscribers
==================
sqlplus apps/<apps_passwd> @$FND_TOP/patch/115/sql/wfbesqsubc.sql applsys <apps_passwd>

Re-enqueue backed up messages
=============================
-bash-4.1$ sqlplus apps/<apps_passwd> @$FND_TOP/sql/wfaqrenq.sql WF_JAVA_DEFERRED


Create index on corrid
======================

-bash-4.1$ sqlplus apps/<apps_passwd> @$FND_TOP/patch/115/sql/wfbesqidxc.sql applsys <apps_passwd> APPS_TS_QUEUES

Gather table statistics
=======================

-bash-4.1$ sqlplus apps/<apps_passwd> @$FND_TOP/patch/115/sql/wfhistc.sql applsys


Creates grants/synonyms
=======================

-bash-4.1$ sqlplus apps/<apps_passwd> @$FND_TOP/patch/115/sql/afwfqgnt.sql APPS <apps_passwd> APPLSYS <apps_passwd>