Friday, May 5, 2017

New and changed features in 12.2.6

Few of the new features that are interesting and useful, for DBA's.

Delta Synchronization of File Systems -- Need to check this how fast it can sync and what are the options.
=======================================
The fastest way to synchronize the two application tier file systems, the new option of delta synchronization uses your choice of third-party utility to synchronize the file systems by copying files as applicable from the source directory to the destination directory, optionally ignoring any files and directories you may decide to specify in an exclusion file

$ adop phase=prepare sync_mode=delta


 Enhanced Pairsfile Support
 ===========================

 The pairsfile for the 'dualfs' option of adcfgclone.pl has been enhanced to support entries for the patch file system context variables. The context variable names for the patch file system should be prefixed with 'patch_'.

 Ex: patch_s_wls_oacoreport=7235

 Support For Longer Node Names in Rapid Clone -- This can come really useful now..
 =============================================

 The Rapid Clone utility now supports node names of up to sixty-three (63) characters.

 Standard Request Submission Enhancements -- At last, few useful things on daily tasks for functional and end-users
 =========================================

 The standard request submission (SRS) View Requests window has the enhancements listed below. These changes simplify the navigation required to perform these steps and/or reduce the number of mouse-clicks needed.

    Auto Refresh check box - When this box is selected, the form will refresh the list of requests after a specified interval measured in seconds. Use the profile option "Concurrent: Auto-refresh View Request Timer (secs)" to set this interval.
   
    Rerun Request button - This new button will resubmit the selected request with exactly the same parameters, after confirmation. Note that you cannot use this button to rerun non-SRS requests.
   
    Copy Single Request and Copy Request Set buttons - these buttons allow you to copy a single request or request set, respectively. A list of values is provided to select the request or request set.
   
    Submit a New Request and Submit New Request Set - These buttons allow you to submit a new request or submit a new request set, respectively. These buttons are also added to the Find Requests window.

   
    New Profile option for this --
    Concurrent: Auto-refresh View Request Timer (secs)
   
    This profile option sets the interval, in seconds, for refreshing the View Request window based on a timer, if the Auto Refresh box is checked. The default value is 300, if no value is given. The value 0 disables Auto Refresh.
   
Support for Platform Migration
================================   
Platform migration is now certified with Oracle E-Business Suite Release 12.2

2048954.1, Application Tier Platform Migration with Oracle E-Business Suite Release 12.2.

Thursday, May 4, 2017

EBS UPGRADE from 11i(11.5.10.2) to 12.2.5 or 12.2.6 and DB from 11.2.0.4 to 12c

Below Pre-upgrade steps on 12.2 servers can be done well in advance before the actual upgrade downtime.

Pre-upgrade Steps on 12.2
==========================
1. Install R12.2 File system
   a) Run startCD/Disk1/rapidwiz/bin/buildStage.sh
      to Build the stage area of the software from the zip files that are downloaded from edelivery.
   b) Run rapidwiz and choose options as below on the relevant screen
       -- Upgrade to Oracle EBS R12.2.0
       -- Create Upgrade Filesytem
2. Run ETCC on MT
        checkMTpatch.sh
3. Apply The patches recommended by ETCC to
        -- Forms Home
        -- WebTier Home
        -- Oracle Common Home
        -- Weblogic Server
4. Install 12c DB Oracle Home.
5. Run ETCC on DB
        checkDBpatch.sh
6. Apply the recommended patches to 12c DB home

Pre-Upgrade Steps on 11i
=========================
1. One way to start-off, is to take a full backup of Prod and perform pre-upgrade steps directly on Prod.

Otherwise

2. Take a fresh clone of 11i Prod instance.(Recommended process compared to above)

3. Build a DR from the new clone to the R12.2 DB server where R12 instance will be built.
For 11g Home, Use a different location other than the 12c Home installed above.

The concept of DR is nothing related to upgrade but since,
rsync or copy of DB(datafiles) from 11i to R12 will delay the upgrade process. This can be avoided using the DR.

Do Not Activate the Standby database yet.

==== Upgrade Window starts here ============
=====Make a note of all the timings for each step from here on.=========

4. Apply the Minimum Baseline patches on 11i required for the upgrade.
 Merge and apply all the patches to save time.(except AD patches- don't merge AD with others )

5. Handover the instance to Technical and functional teams for their pre-upgrade steps.
 SLA Pre Upgrade program to be completed by them by this point.

6. Other Major pre-upgrade steps include
a) Disable AOL Audit Taril - Run this program
b) Run Gather Schema Statistics for ALL schemas
c) Collect information of DBA DIRECTORIES and DB LINKS.
d) Disable Custom triggers,indexes,constraints.
e) Add datafiles to APPS and SYSTEM tablespaces.


Once the Pre-upgrade steps on 11i are completed by Tech/Func teams, we can ACTIVATE the DR.

DB Upgrade to 12c
==================
1. Ensure DR is in sync with the primary and Activate the Standby database using 11g Home.
2. Perform the pre-DB upgrade steps on 11g home.
a)Copy the below sql's from 12c Home to 11g Home and execute them.

Set ENV to 11g Home and run
SQL> @preupgrd.sql
SQL> @utluppkg.sql 


Preupgrd.sql will create below files.

preupgrade.log : The results of all the checks performed.Check and perform any manual steps that are advised.
preupgrade_fixups.sql : A fixup script that should be run before the upgrade.
postupgrade_fixups.sql : A fixup script that should be run after the upgrade.

b) Run preupgrade_fixups.sql and any manual steps recommended.

3. Perform the DB upgrade using DBUA.

Set ENV to 12c Oracle home.
$ cd $ORACLE_HOME/bin
$ ./dbua.sh

Choose options as below where applicable on the DBUA screens
    -- Upgrade Oracle database
    -- Check and Provide Source Database Home location
    -- Makre sure Pre-req checks are successful
    -- Choose -- Recompile Invalids during post-upgrade and
    -- Choose -- Upgrade timezone Data
    -- Choose -- I have my own backup and restore strategy
   

4. Run postupgrade_fixups.sql
5. Run utlu121s.sql to confirm upgrade went fine.
6. Run adgrants.sql and adstats.sql(Copy from $APPL_TOP/admin)
7. Run Datapatch to update ETCC patches inside Database

cd $ORACLE_HOME/OPatch
./datapatch

8. Delete Source Node information from Database
As APPS user
SQL> exec fnd_conc_clone.setup_clean;
9. Run Autoconfig

12.2.0 Upgrade
===============
1. Update any concurrent requests that are pending scheduled to Hold status.
2. Disable Archive log and set SGA,PGA,SESSIONS and PROCESSES as required for upgrade.
3. Run adgncons.sql from patch 13435302 and perform steps recommended.
4.
a) Merge and Apply the pre-install patches
R12.1 and 12.2 Oracle E-Business Suite Preinstall Patches Report [Video] (Doc ID 1448102.2)

b) Apply the Consolidated Upgrade Patch (CUP) for Release 12.2.0 (Patch 22742071:12.2.0)
in pre-installation mode on the Run Edition File System.

b) Merge and Apply AD CUP7 patches
 Apply the AD Upgrade Patch for Release 12.2 (Patch 10117518)
 by merging it with the latest Consolidated Upgrade Patch (CUP) for AD (Patch 22742061:R12.AD.C).

Oracle E-Business Suite Release Notes, Release 12.2 (Doc ID 1320300.1) -- For latest CUP7 and AD CUP7 patches

5. Merge preinstall patches with Upgrade Driver
cd $AU_TOP/patch/115/driver
admrgpch -d . -preinstall -master u10124646.drv

6. Apply 12.2 upgrade patch driver

cd $AU_TOP/patch/115/driver

time adpatch options=nocopyportion,nogenerateportion flags=nologging driver=u_merged.drv logfile=u_r122merged_`echo  $TWO_TASK`_`date '+%d%B%Y_%H_%M_%S'`.log workers=16 
(Provide batch size =30000)

Workers and batchsize depending on the number of CPU's on DB node.

7. Configure Domains to enable Managed Servers
 Run rapidwiz and choose options as below wherever applicable
 -- Upgrade to EBS R12.2.0
 -- Configure Upgraded R12.2.0 and prvoide the full path to Context File location

8.
a) Start the application services.
b) Wait and make sure all upgrade related concurrent requests are completed.
c) Stop the application services.

Online Patching Enablement
===========================
1. Apply latest Online Patching Enablement Readiness Report Patch 22071026.
2. Run Online Patching Readines report - $AD_TOP/sql/ADZDPSUM.sql.Review the generated Report and fix any issues reported.
3. Run below scripts located at $AD_TOP/sql
ADZDPMAN.sql
ADZDDBCC.sql
ADZDEXRPT.sql
4. Add tablespaces as needed
perl $AD_TOP/bin/adzdreport.pl apps
Choose     3.  Other generic reports
and then  choose   3.  Free space in important tablespaces
5. Make sure AD_ZD objects are all valid.
select owner,object_name,object_type from dba_objects where object_name like 'AD_ZD_%'  and  status='INVALID';
6. Apply the Online Patching Enablement patch 13543062 in hotpatch mode on Run FS.

When AutoPatch prompts with the following question, answer "Y"
This Patch seems to have been applied already.
Would you like to continue anyway  [N] ?   Y

7.
Run Online Patching Status Report,ADZDEXRPT.sql after Online Enablement Patch
Run the Readiness Scripts again
Run the Online Patching Database Compliance Checker report,ADZDDBCC.sql to check for coding standards violations.

12.2.5 Upgrade
===============
1. Copy adgrants.sql from patch 22123818 to DB HOME and run as sysdba.
2. Merge and Apply Bundle fixes and AD.DELTA.7 patches. Apply them in Downtime mode.
R12.AD.C.DELTA.7 PATCH - 20745242
BUNDLE FIXES FOR R12.AD.C.DELTA.7 - 21841288
BUNDLE FIXES II FOR R12.AD.C.DELTA.7 - 22123818
adop phase=apply patches=20745242,21841288,22123818 apply_mode=downtime merge=yes
3. Apply Patch 20784380:R12.TXK.C.Delta.7 merged with Bundle fixes

BUNDLE FIXES FOR R12.TXK.C.DELTA.7 - 21846184
BUNDLE FIXES II FOR R12.TXK.C.DELTA.7 - 22363475
TXK CONSOLIDATED PATCH FOR STARTCD 12.2.0.51 - 22495069

4. Generate and copy appsutil.zip to DB home and unzip using -o option.
5. Run Autoconfig on DB.
6. Apply 12.2.5 Upgrade patch in downtime mode.
adop phase=apply patches=19676458 apply_mode=downtime
7. Apply Online help patch 19676460 in downtime.
8. Apply Latest Apps CPU Patch
9. Run Patch Wizard and Apply recommended patches
10. Startup application services and wait for completion of any upgrade requests and shutdown services.

In case, you want to go with 12.2.6 upgrade instead of 12.2.5 below are the patches required.

 
For 12.2.6 Upgrade
===================

AD.8 and TXK.8 are the pre-requisites for 12.2.6.

R12.AD.C.Delta.8 (Patch 21841299) and
R12.TXK.C.Delta.8 (Patch 21830810 )
21900901 -  ORACLE E-BUSINESS SUITE 12.2.6 RELEASE UPDATE PACK


Oracle E-Business Suite Applications DBA and Technology Stack Release Notes for R12.AD.C.Delta.8 and R12.TXK.C.Delta.8 (Doc ID 2159750.1)
2114016.1 - 'Oracle E-Business Suite Release 12.2.6 Readme'.

However,
The latest RUPs are R12.AD.C.Delta.9 (Patch 25178222) and R12.TXK.C.Delta.9 (Patch 25180736).

Upgrade JRE if required
=========================

1. unzip p22961063_18077_WINNT.zip
2. mv jre-8u77-windows-i586.exe j2se18077.exe
3. cp j2se18077.exe $COMMON_TOP/webapps/oacore/util/javaplugin
4. Provide 644 permissions to j2se18077.exe                                                                                                    
5. $FND_TOP/bin/txkSetPlugin.sh 18077 ( This will run Autoconfig and will set the new Jre in the Env)

Register any CUSTOM Products
============================
1. Copy the CUSTOM TOP directory from 11i to 12.2 APPL_TOP.
2. Rename the 11.5.0 directory under CUSTOM_TOP to 12.0.0
3. cd 3636980/izu/admin
mv izuprod.txt customprod.txt
mv izuterr.txt customterr.txt
cp newprods.txt $APPL_TOP/admin
cp customterr.txt $APPL_TOP/admin
cp customprod.txt $APPL_TOP/admin
cd $APPL_TOP/admin
vi customprod.txt
Replace all ocurrence of izu with custom  ,IZU with CUSTOM and 278 with 20081

vi customterr.txt
Replace all ocurrence of izu with custom  ,IZU with CUSTOM and 278 with 20081 and  Oracle_Support_Diagnostic_Tools with "CUSTOM".

vi newprods.txt and make
product=custom
oracle_schema=custom

20081 - Product ID should work most of the times,
else check the existing used ones and provide an unused product ID.

select ORACLE_ID,ORACLE_USERNAME from fnd_oracle_userid where ORACLE_ID > 20000;

3. Run adsplice
This runs autoconfig and sets the Env with New product Top (CUSTOM_TOP)

4. Add  entries for CUSTOM_TOP in Adop_sync.drv file  under APPL_TOP_NE
rsync -zr %s_current_base%/EBSapps/appl/custom %s_other_base%/EBSapps/appl
rsync -zr %s_current_base%/EBSapps/comn/java/classes/custom  %s_other_base%/EBSapps/comn/java/classes

5. Upload the New context_file to DB

Source the RUN filesystem                                                                                                                                       
$ADJVAPRG oracle.apps.ad.autoconfig.oam.CtxSynchronizer action=upload contextfile=Enter Full Patch Context XML File Location logfile=/tmp/patchctxupload.log

Run the following query and it should return one entry for the recently uploaded context file.

select distinct(PATH) from FND_OAM_CONTEXT_FILES where NAME not in ('TEMPLATE','METADATA','config.txt') and CTX_TYPE='A' and (status is null or upper(status) in ('S','F')) and EXTRACTVALUE(XMLType(TEXT),'//file_edition_type') = 'patch';


Configure Central Inventory
============================

The concept of central inventory will be very useful in cases where there are multiple instances on the same server.
 Normally, inventory is placed in a location which is commonly shared by all the instances on that server. Such configuration poses a lot of problems  during clones. But enabling central inventory moves the inventory to a location specific to each instance, normally, under APPS BASE location for that instance.         

Below process moves the inventory files from existing location to a instance specific location.                                                                                                                                                1. 1. Edit the context file and set the value of the context variable s_ebs_central_inventory to 'true'.
2. Run AutoConfig.
3. Run the following command:
$ perl $FND_TOP/patch/115/bin/txkMigrateInventory.pl -contextfile=$CONTEXT_FILE

Add or Delete Managed servers as required
==========================================
Add MS
======
$ perl /patch/115/bin/adProvisionEBS.pl \
ebs-create-managedserver -contextfile= \
-managedsrvname=oacore_server2 -servicetype=oacore \
-managedsrvport=7203 -logfile=/TXK/addMS_oacoreserver2.log

Start newly added Managed server
admanagedsrvctl.sh start oacore_server2

Delete MS
==========
Stop the server
admanagedsrvctl.sh stop oacore_server2

$ perl /patch/115/bin/adProvisionEBS.pl \
ebs-delete-managedserver \
-contextfile= -managedsrvname=oacore_server2 \
-servicetype=oacore -logfile=/TXK/delMS_oacoreserver2.log

 $ perl /patch/115/bin/txkSetAppsConf.pl -contextfile= \
-configoption=removeMS -oacore=testserver.example.com:7205

Post-Upgrade steps
==================
Keep Concurrent programs on hold as required
Disable alerts if not desired,
Enable triggers that are disabled before upgrade
Create DB LINKS
Ensure the softlinks are updated with right locations
Ensure DBA_DIRECTORIES point to correct directories
Enable audit trail on R122
Configure WorkFlow
Compile Invalids and schedule periodically
Bring up Application Services
Run and Schedule weeky Gather Schema statistics for ALL schemas with estimate percent 40.
Schedule other Purge and workflow background processes requests
Startup application services and Clear Cache
Run fs_clone
Run adpreclone.pl on DB and Apps

SLA Update
===========
If SLA data is not upgraded completely during upgrade window, this can be done after upgrade is completed as follows:

1. Update profile option as required or in steps of 6 months -- SLA: Initial Date for Historical Upgrade
2. Run xla5584908.drv
     adop phase=apply options=nocopyportion patchtop=$XLA_TOP/patch/115  patches=driver:xla5584908.drv hotpatch=yes

    

Wednesday, May 3, 2017

Password changes in 12.2.5



Weblogic Password change:
======================
                                                                                                                                                   
Start the admin server

 $ADMIN_SCRIPTS_HOME/adadminsrvctl.sh start                  
  perl $FND_TOP/patch/115/bin/txkUpdateEBSDomain.pl  -action=updateAdminPassword -contextfile=
                         
enter the WLS Admin Password:
Enter the new WLS Admin Password:   ********
Enter the APPS user password:                         
                                                                                                                                                                                                                 
=============================================================================
                                                                                                                                                                              
APPS Password change:
=====================                                                                                                                                                             
Requires below two steps:                                                                                                                                         
1)                                                                                                                                                                                               
AFPASSWD -c apps@SID-s APPLSYS
Enter the ORACLE password of Application Object Library 'APPSUSER':(Current pwd)
Enter the password for your 'SYSTEM' ORACLE schema:
Enter new password for user:  ****** (New password)
Verify new password for user:   *******       
                                                                                                                                                           
2)                                                                                                                                                                                                     
 cd $FND_TOP/patch/115/bin
 perl txkManageDBConnectionPool.pl                                                                                                                                             
 Enter Your Choice : updateDSPassword
Enter the full path of Applications Context File
Enter weblogic admin server password:
Enter the APPS user password: ***** (new password)

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>