Oracle EBS R12.2 : Check Ports in Run Edition and Patch Edition

Check Context Files Version are the same in Database


select path,status,serial_number,version
  from apps.fnd_oam_context_files
 where name not in ('TEMPLATE','METADATA') and (status is null or status !='VALID')
order by path, version;


Check Ports in Run Edition

SELECT  extractValue(XMLType(TEXT),'//host[@oa_var="s_hostname"]'),
extractValue(XMLType(TEXT),'//oacore_server_ports'),
extractValue(XMLType(TEXT),'//forms_server_ports'),
extractValue(XMLType(TEXT),'//oafm_server_ports'),
extractValue(XMLType(TEXT),'//forms-c4ws_server_ports'),
extractValue(XMLType(TEXT),'//oaea_server_ports')
from apps.fnd_oam_context_files
where name not in ('TEMPLATE','METADATA')
and (status is null or status !='H')
and EXTRACTVALUE(XMLType(TEXT),'//file_edition_type')='run'
and CTX_TYPE = 'A';


Check Ports in Patch Edition

SELECT  extractValue(XMLType(TEXT),'//host[@oa_var="s_hostname"]'),
extractValue(XMLType(TEXT),'//oacore_server_ports'),
extractValue(XMLType(TEXT),'//forms_server_ports'),
extractValue(XMLType(TEXT),'//oafm_server_ports'),
extractValue(XMLType(TEXT),'//forms-c4ws_server_ports'),
extractValue(XMLType(TEXT),'//oaea_server_ports')
from apps.fnd_oam_context_files
where name not in ('TEMPLATE','METADATA')
and (status is null or status !='H')
and EXTRACTVALUE(XMLType(TEXT),'//file_edition_type')='patch'
and CTX_TYPE = 'A';

Trace Forms Enable – Oracle EBS R12

Enable Trace from Forms

Help -> Diagnostic -> Trace ->

we have many options available (default is ‘No Trace’). We can enable tracing by selecting on of the options from here. ‘Regular Trace’ gives the least information and ‘Trace with Binds and Waits’ (level 12) gives maximum information. 

Note: Be careful while enabling SQL trace with wait and binds as this will make the trace file huge.

So after we fill all the values in the fields, we can start the tracing so that the initial select statement does not come in trace file.

When we enable the trace it will give the trace file location (This location will be the location of USER_DUMP_DESTINATION parameter of database).

Disable Trace:

After you save the form you can stop tracing by selecting ‘No Trace’ again from Help -> Diagnostic -> Trace -> No Trace

Use tkprof to covert trace file in readable format.

$ tkprof dv1_ora_35782850.trc dv1_ora_35782850.txt sys=no explain=apps/*******sort='(prsela,exeela,fchela)'

Ref:  Oracle E-Business SQL Trace and TKPROF Guide(1674024.1) 

Metalink note ID 373548.1

Enable Tracing For The Concurrent Manager  Program 
Responsibility: System Administrator
Navigate: Concurrent > Program > Define
Query Concurrent Program
Select the Enable Trace Checkbox 

Turn On Tracing
Responsibility: System Administrator
Navigate: Profiles > System
Query Profile Option Concurrent: Allow Debugging
Set profile to Yes

Run Concurrent Program With Tracing Turned On    
Please login to the Responsibility that runs the Concurrent Program 
In the Submit Request Screen click on Debug Options (B)
Select the Checkbox for SQL Trace

Find Trace File Name

  Run the following SQL to find out the Raw trace name and location for the concurrent program.  The SQL prompts the user for the request id 

SELECT
    req.request_id
    ,req.logfile_node_name node
    ,req.oracle_Process_id
    ,req.enable_trace
    ,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
    ,prog.user_concurrent_program_name
    ,execname.execution_file_name
    ,execname.subroutine_name
    ,phase_code
    ,status_code
    ,ses.SID
    ,ses.serial#
    ,ses.module
    ,ses.machine
    FROM
    fnd_concurrent_requests req
    ,v$session ses
    ,v$process proc
    ,v$parameter dest
    ,v$parameter dbnm
    ,fnd_concurrent_programs_vl prog
    ,fnd_executables execname
    WHERE 1=1
    AND req.request_id = &request --Request ID

/opt/oracle/enable/admin/ENBL1/udump  --> trace file location



/* Query for Identifying correct trace file for request id */

set lines 800
set pages 400

PROMPT IDENTIFY CONCURRENT REQUEST FILE
PROMPT From Bug.3211206
PROMPT Use the following query to identify the correct trace file:
PROMPT where "request" is the concurrent request id for the inventory transaction 
PROMPT worker. 

SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name: 
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running') 
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from apps.fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, apps.fnd_concurrent_programs_vl prog, 
apps.fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;




3. TKPROF Trace File

Once you have obtained the Raw trace file you need to generate TKPROF.

output_file: tkprof out file


$ tkprof sys=no explain=apps/ sort=’(prsela,exeela,fchela)’ print=10

Other ways to take trace:
SQL> ALTER SESSION SET sql_trace=TRUE;
SQL> ALTER SESSION SET sql_trace=FALSE;

SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);
SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' ');

SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' ');

Happy Learning!

Oracle TDE encryption verification

For TDE tablespace  encryption, the following SQL statement lists all encrypted tablespaces with their encryption  algorithm and corresponding, encrypted, data files

SELECT t.name "TSName", e.encryptionalg "Algorithm", d.file_name
"File Name"
FROM
v$tablespace t, v$encrypted_tablespaces e, dba_data_files d
WHERE
t.ts# = e.ts# and t.name = d.tablespace_name;

Query to lists the table owner, tables within encrypted tablespaces, and the encryption algorithm:

 SELECT a.owner "Owner", a.table_name "Table Name", e.encryptionalg
"Algorithm"
FROM
dba_tables a, v$encrypted_tablespaces e
WHERE
a.tablespace_name in (select t.name from v$tablespace t,
v$encrypted_tablespaces e where t.ts# = e.ts#);

Happy Learning!

EBS R12.2: ADOP Online Patching - Set Up Secure Shell(ssh) on Application Tier Nodes

In EBS R12.2.x, ADOP online patching cycle in a muti-node environment will fail if the passwordless ssh is not enabled.

In a multi-node environment, adop commands are invoked by a user on the primary node. Internally, adop uses Secure Shell (ssh) to automatically execute required patching actions on all secondary nodes. You must set up passwordless ssh connectivity from the primary node to all secondary nodes.

Principles

The ssh-keygen command is used to generate a private/public key pair. The private key is for the node from where all the remote nodes will subsequently be accessible by an ssh login that requires no password. The public key must be copied to each remote node's <User Home Dir>/.ssh directory.

In essence, the sequence is as follows:

1. The following command initiates the creation of the key pair:

$ ssh-keygen -t rsa

Note: The <Enter> key should be pressed instead of a passphrase being entered.

2. The private key is saved in <User Home Dir>/.ssh/id_rsa.

Tip: As this read-only file is used to decrypt all correspondence encrypted with the public key, its contents must not be shared with anyone.

3. The public key is saved in <User Home Dir>/.ssh/id_rsa.pub.
4. The contents of the public key are then copied to the <User Home Dir>/.ssh /authorized_keys file on the systems you subsequently wish to ssh to without being prompted for a password.

The following example demonstrates the steps:

1.  $ ssh-keygen -t rsa

2.  Generating public/private rsa key pair.

3.  Enter file in which to save the key (/u01/user2/.ssh/id_rsa):<Enter>

4.  Enter passphrase:<Enter>

5.  Enter same passphrase again:<Enter>

6.  Your identification has been saved in /u01/user2/.ssh/id_rsa.

7.  Your public key has been saved in /u01/user2/.ssh/id_rsa.pub.

8.  The key fingerprint is: 16:d0:e2:dd:37:2f:8e:d5:59:3e:12:9d:2f:12:1e:5a

9.  $ scp -pr /u01/user2/.ssh/id_rsa.pub \

10.user2@system1:/u01/user2/.ssh/authorized_keys

11.user2@system1's password:<password>

12. id_rsa.pub 100% 398 0.4KB/s 00:00

13. $ ssh user2@system1

Note: If you receive this message, it can safely be ignored: Warning: untrusted X11 forwarding setup failed: xauth key data not generated Warning: No xauth data; using fake authentication data for X11 forwarding.

Once this has been done for the relevant operating system account on all nodes - that is, ssh can log in from the primary node to each secondary node without entering a password - so you are ready to run adop on multiple application tier nodes. It must be run on at least the master (admin) node: from there, it will attempt to contact all the other application tier nodes that are part of the same Oracle E-Business Suite instance, and will run the required steps remotely on those nodes.

Tip: If you change the password for the relevant operating system account on one or more nodes, you must regenerate the ssh credentials either using the $AD_TOP/patch/115/bin/txkRunSSHSetup.pl script, or your own native solution if you prefer.

The txkRunSSHSetup.pl script has a -help option that shows relevant usage options.

For example, a basic command to enable ssh would be:

$ perl $AD_TOP/patch/115/bin/txkRunSSHSetup.pl enablessh -contextfile=<CONTEXT_FILE> -hosts=h1,h2,h3$

To verify ssh operation:

$ perl $AD_TOP/patch/115/bin/txkRunSSHSetup.pl verifyssh -contextfile=<CONTEXT_FILE> -hosts=h1,h2,h3 \

-invalidnodefile=<filename to report ssh verification failures>

To disable ssh:

$ perl $AD_TOP/patch/115/bin/txkRunSSHSetup.pl disablessh \

-contextfile=<CONTEXT_FILE> -hosts=h1,h2,h3 \

Happy Learning!

FND Debug log messages for a User in EBS R12.

FND Debug log messages for a User in EBS R12.

 Set the following profiles at the user level:
 FND: Debug Log Enabled = YES
 FND: Debug Log Filename = NULL
 FND: Debug Log Level = STATEMENT
 FND: Debug Log Module = % 

Make a note of the log sequence for the user before performing the activity (SEQUENCE_1).

select max(log_sequence)

from

fnd_log_messages fnd, fnd_user fu

where fnd.user_id = fu.user_id

and fu.user_name = '&USER_NAME'

order by log_sequence desc;

Once the testing is completed, run the above query and make note of the log sequence (SEQUENCE_2).

Run the following sql - substituting the two low and high sequences.

SQL>select fnd.*

from

fnd_log_messages fnd, fnd_user fu

where

fu.user_id = fnd.user_id and

fu.user_name = '&USER_NAME' and

fnd.log_sequence > &SEQUENCE_1 and

fnd.log_sequence < &SEQUENCE_2

order by fnd.log_sequence asc;

Happy Learning!

ADOP Prepare phase in EBS R12.2

In the PREPARE phase, ADOP does a series of validations and then prepares the system for an online patching cycle. Here is a step-by-step details when we run :

adop phase=prepare

• Performing verification of parameters
  • Sourcing the Run Edition environment
  • Validating system setup…
  • Determining admin node
    • Node registry is valid.
  • Performing database sanity checks
  • Acquire lock on sessions table
  • Checking for pending adop sessions
    • active hotpatch session…
    • active cleanup session
    • active FS_CLONE session
  • Staging new adop session…
    • Checking if node “<node_name>” previously failed
    • New row inserted into ad_adop_sessions table with session id : 100
  • Unlocking sessions table
• createPatchCtxFile()
  • check if patch context file exists, else generate it (from where?)
  • Checking the status of phase

select  count(1)
  from  ad_adop_sessions
 where  adop_session_id=100
   and (prepare_status in ('N','R'))
   and  node_name=’node_name’;

It should return 1. Anything else, it will fail.

Then, it validates the configuration on the current node using txkADOPValidation script

$AD_TOP12.0.0/patch/115/bin/txkADOPValidations.pl -contextfile=<context>.xml -patchctxfile=<p_context>.xml -phase=prepare -logloc=file.log -promptmsg=hide

It Creates 3 files txkADOPValidations.log, txkADOPValidations.error & ADOPValidations_detailed.log

Some of the validations done are:

• All the application tier nodes have entry in FND_NODES table
• All application tier context files (on both run and patch file systems) have correct entries in FND_OAM_CONTEXT_FILES table
• Value set for JDK_TOP and FMW_JDK_TOP are valid
• All Oracle inventory checks were successful
• APPL_TOP name is set correctly for the current node
• OHS configuration files are in sync with the latest AutoConfig templates
• Product addition for all custom product tops is correct
• Checks for Admin Server information in context files of both the file systems are validated successfully across all nodes.
• Validated multi-node setup
• Custom Product Tops have been configured properly across all nodes
• All required ports for patch file system are available
• Domain is not locked by any WLS user
• Patch file system WebLogic domain edit lock is not enabled
• The value of s_jdbc_connect_descriptor is not NULL
• The hostname and s_hostname have the same value
• s_ohs_installed is correctly set
• s_current_base is in sync across all nodes
• All managed servers status are valid
• File Edition values are set correctly in context files
• The values of s_current_base and s_other_base are set correctly on both RUN and PATCH filesystems

Next, ADOP Verifies data dictionary and updates status. 

• Executing steps for prepare phase…
• Updating ADOP session status
• Updating session process id

update ad_adop_sessions
   set status='R'
 where adop_session_id = 100 
   and appltop_id = 2103 
   and node_name='<node_name>'

• Updating ADOP session status
• Updating session dates
• Updating session status

  • Updating prepare_status=R for session_id : 100

• Check for number of edition and issue warning if there are more than 20. Below is a sample.

[WARNING]   There are 56 editions. We recommend no more than 20 for performance reasons.
[WARNING]   Please run actualize_all followed by cleanup in full mode to remove unused editions.

The next step is to ensure that the system is ready for PREPARE phase of ADOP. This is done using txkADOPPreparePhaseSanityCheck.pl

• Validating system is ready to prepare
• Validating Configuration

  • $AD_TOP/12.0.0/patch/115/bin/txkADOPPreparePhaseSanityCheck.pl

    • updatePatchApplTop()
    • Validate context file…
    • Validate PATCH context file…
    • Validate APPS schema credentials
    • Validate SYSTEM user credentials
    • Validate mode
    • Validate Action
    • Validate console log key
    • checkPrivileges() – Checking whether Filesystem directories have READ/WRITE access
    • checkFileEdition()
    • checkDiskSpaceOn<OS>()
  • Detecting configuration changes that must be synchronized.

If there are changes since last fs_clone or if the fs_clone was not run after the previous cutover, then now is the time. This is check done using the perl script adConfigChangeDetector.pl

$AD_TOP/12.0.0/patch/115/bin/adConfigChangeDetector.pl -detectConfigChanges
Contextfile = <file_name>.xml
promptmsg = hide
log =<path_to_log>.log
hostname = <host_name>

If configuration changes are detected you will see

“Configuration changes detected.”

If configuration changes are detected, the process then begin do an FS_CLONE internally within the prepare phase.

• Checking if there are pending cleanup actions
• Validating database is ready to prepare
  • Deleting rows from ad_patches_tables table
  • Performing DB Sanity checks
  • Checking if edition enabled users exist

• Generating report for important tablespaces $AD_TOP/12.0.0/sql/ADZDSHOWTS.sql

• Start Apps listener
• Submit ADZDPATCH concurrent program
• Creating Database Patch Edition
• Synchronizing patch filesystem with run filesystem

  • $AD_TOP/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl

• Perform CONFIG_CLONE
• Run FS Clone
• Run AutoConfig on Patch Edition file system
• Validate configuration
• Synchronize Snapshots
• Run Edition Reports
• Update ADOP Status in table
• Generate AD_ZD_LOGS Report
• Stop Services on Patch file system

Happy Learning!

Steps to change the weblogic password in EBS R12.2

1.Stop the EBS suite application services.

   Source Run filesystem environment file.

   sh  $ADMIN_SCRIPTS_HOME/adstpall.sh

2. Start only the admin server

sh $ADMIN_SCRIPTS_HOME/adadminsrvctl.sh start

3. Login to the weblogic admin console with the Source weblogic password

3. Clicking the Lock and Edit in the Change Center

Click the Domain

Click Security tab à Update the NodeManager with Target environment weblogic Password.

Hit the Save button once the Nodemanager password is changed

4. Activate changes in Change center

5. Click the Security Realms under Domain Structure

6. Click Myrealm

7. Click User Groups tab and select the Weblogic in the Users list.

8.  Provide the Target environment weblogic password and save.

After this we need to edit the configuration file "boot.properties" for all managed and admin server. Update the password field with the target environment weblogic  password

Take a backup of boot.properties file of all the managed servers.

$EBS_DOMAIN_HOME/servers/AdminServer/security/boot.properties

$EBS_DOMAIN_HOME/servers/forms-c4ws_server1/security/boot.properties …It has actual password (clear text)

$EBS_DOMAIN_HOME/servers/oaea_server1/security/boot.properties

$EBS_DOMAIN_HOME/servers/forms_server1/security/boot.properties

$EBS_DOMAIN_HOME/servers/oacore_server1/security/boot.properties

$EBS_DOMAIN_HOME/servers/oafm_server1/security/boot.properties

Move the directory  data to data_OLD in all the managed server.

Example:

$EBS_DOMAIN_HOME/servers/oafm_server1/data to $EBS_DOMAIN_HOME/servers/oafm_server1/data_OLD

Edit the boot.properties in all the managed server with plain text password of target environment weblogic password. This will get encrypted after starting the managed server.

Vi boot.properties

Username=weblogic

Password=<<Target environment weblogic password>>

Note: Instead of editing boot.properties in each managed server, There is a alternate way to copy boot.properties from Admin server which has correct encrypted target weblogic password  to all the managed server.

9. Start the application services.

sh $ADMIN_SCRIPTS_HOME/adstrtal.sh 

Happy Learning!