Pages

Friday, March 24, 2023

[Result.addErrorDescription:771] PRVG-0449 : Proper soft limit for maximum stack size was not found on node "xxxx" [Expected >= "10240" ; Found = "8192"].

 

We usually get this error in installations and sometimes on adcfgclone when it is running through any automation scripts in Solaris systems.

In Installations, you will see as below.

 Installation of Oracle 12.2.0.1 on Oracle Linux reports prerequisite check failure for maximum stack size as seen below. The check does not succeed despite clicking "Fix & Check Again" OUI option and running the fixup script as "root" user

 


Modify the resource limits to meet the requirement and take operating system specific measures to ensure that the corrected value takes effect for the current user before retrying this check.

Time being on your session where you want to launch the installation.

 

To adjust the maximum stack size soft limit use:

1. For the current shell:

# ulimit -Ss 10240

 

Or to update the values in system files to make it permanent.

 

In the file : /etc/security/limits.conf , adjust the value for the below parameter.

 

Oracle            soft               stack             10240

 

The sessions needs to be relaunched for the fix-ups to be effective. For example, if it is a VNC sessions then the user has to close it and Re-Login to create a new session where the new stack limits will be loaded.

 

On the other case the issue on adcfgclone when it run through automated scripts on Solaris server.

 

Check if the user able to connect to same user with password less authentication from same session.

If yes, that could be the cause, Remove the entries from authorized keys file (authorized_keys) so the user will not connect to self-session.

 

[oracust@xxxxx]$ ssh oracust@db3301

Last login: Tue Mar 21 10:18:45 2023 from 10.134.156.141

 

|-----------------------------------------------------------------|

| This system is for the use of authorized users only.            |

| Individuals using this computer system without authority, or in |

| excess of their authority, are subject to having all of their   |

| activities on this system monitored and recorded by system      |

| personnel.                                                      |

|                                                                 |

| In the course of monitoring individuals improperly using this   |

| system, or in the course of system maintenance, the activities  |

| of authorized users may also be monitored.                      |

|                                                                 |

| Anyone using this system expressly consents to such monitoring  |

| and is advised that if such monitoring reveals possible         |

| evidence of criminal activity, system personnel may provide the |

| evidence of such monitoring to law enforcement officials.       |

|-----------------------------------------------------------------|

 

/etc/profile[52]: ulimit: 65536: limit exceeded [Insufficient privileges]

[oracust@xxxxxx]$

 

After removing authorized keys for self connection, It will be like

 

[oracust@xxxxx]$ ssh oracust@xxxxxx

Password:

[oracust@xxxxxx]$

Monday, November 21, 2022

How to Create,Start,Modify,Stop and Delete services in listener in Oracle Database 19c

 This article describes how to create database services in single instance databases using the DBMS_SERVICE package.

In a multitenant database services can be created in a CDB or a PDB. In these examples we will just create them in the CDB, so it looks similar to a non-CDB instance.


Create a Service:

We create a new service using the CREATE_SERVICE procedure. There are two overloads allowing you to amend a number of features of the service. One overload accepts an parameter array, while the other allows you to set some parameters directly. The only mandatory parameters are the the SERVICE_NAME and the NETWORK_NAME, which represent the internal name of the service in the data dictionary and the name of the service presented by the listener respectively.

BEGIN

  DBMS_SERVICE.create_service(

    service_name => 'my_new_service',

    network_name => 'my_new_service'

  );

END;

/

We can display information about existing services using the {CDB|DBA|ALL}_SERVICES views. We can see if the service is started by checking the {G}V$ACTIVE_SERVICES view.


COLUMN name FORMAT A30

COLUMN network_name FORMAT A30

SELECT name,network_name FROM   dba_services ORDER BY 1;

NAME                           NETWORK_NAME

------------------------------ ------------------------------

my_new_service                 my_new_service


SELECT name,network_name FROM   v$active_services ORDER BY 1;

No rows selected


Start a Service:

The START_SERVICE procedure starts an existing service, making it available for connections via the listener.

BEGIN

  DBMS_SERVICE.start_service(

    service_name => 'my_new_service'

  );

END;

/

We can see the service is now active.

SELECT name,network_name FROM   v$active_services ORDER BY 1;

NAME                           NETWORK_NAME

------------------------------ ------------------------------

my_new_service                 my_new_service


Modify a Service: 

The MODIFY_SERVICE procedure allows us to alter parameters of an existing service. Like the CREATE_SERVICE procedure, there are two overloads allowing you to amend a number of features of the service. One overload accepts an parameter array, while the other allows you to set some parameters directly.

BEGIN

  DBMS_SERVICE.modify_service(

    service_name => 'my_new_service',

    goal         => DBMS_SERVICE.goal_throughput

  );

END;

/

Stop a Service:

The STOP_SERVICE procedure stops an existing service, so it is no longer available for connections via the listener.

BEGIN

  DBMS_SERVICE.stop_service(

    service_name => 'my_new_service'

  );

END;

/

The service is still present, but it is no longer active.


COLUMN name FORMAT A30

COLUMN network_name FORMAT A30

SELECT name,network_name FROM   dba_services ORDER BY 1;

NAME                           NETWORK_NAME

------------------------------ ------------------------------

my_new_service                 my_new_service


SELECT name,network_name FROM   v$active_services ORDER BY 1;

No rows selected


Delete a Service:

The DELETE_SERVICE procedure removes an existing service.

BEGIN

  DBMS_SERVICE.delete_service(

    service_name => 'my_new_service'

  );

END;

/


We can see it's not longer listed as an available service.


COLUMN name FORMAT A30

COLUMN network_name FORMAT A30

SELECT name, network_name FROM   dba_services ORDER BY 1;

No rows selected


Disconnect Sessions:

The DISCONNECT_SESSION procedure disconnects all sessions currently connected to the service. The disconnection can take one of three forms, indicated by package constants.


POST_TRANSACTION : Sessions disconnect once their current transaction ends with a commit or rollback. This is the default value (0).

IMMEDIATE : Sessions disconnect immediately. Value (1).

NOREPLAY : Sessions disconnect immediately, and are flagged not to be replayed by application continuity. Value (2).

Here is an example of its usage.


BEGIN

  DBMS_SERVICE.disconnect_session(

   service_name      => 'my_new_service',

   disconnect_option => DBMS_SERVICE.immediate

  );

END;

/

Tuesday, July 9, 2019

Check and Validate stuck workflow WF_NOTIFICATION



Introduction:

Workflow notification mailer is an important component of the application framework and notifications should be delivered in timely manner for smooth functioning of the business.
Workflow notification mailer sends the e-mail notification if all of following are true. 
               --Notification status is OPEN or CANCELED  in WF_NOTFICATIONS
               --Notification mail_status is MAIL or INVALID in WF_NOTIFICATIONS

The message makes two stops before it is sent as e-mail.

Send Event Raised -> Enqueued to WF_DEFERRED Queue --> Processed by Deferred Agent Listener -->  Enqueued to WF_NOTIFICATION_OUT Queue --> Processed by Notification Mailer and sent as e-mail;

 


Queries to check stuck notifications:


select count(*) from wf_notifications where status = 'OPEN' and mail_status = 'MAIL' and (sysdate - begin_date)*24*60 > 20;
select notification_id from wf_notifications where status = 'OPEN' and mail_status = 'MAIL' and (sysdate - begin_date)*24*60 > 20;
set linesize 200
set pagesize 100
select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+);


Troubleshooting/Resolution Steps:

When we get this alert, please perform the below troubleshooting checks / steps to resolve this
=> Ensure that below workflow components are up:
Below three services are used in notification delivery.  We need to ensure these are up. 
       Workflow Deferred Agent Listener 
       Workflow Deferred Notification Agent Listener 
       Notification Mailer

Login to applications as SYSADMIN user, Select System Administrator -> Oracle Application Manager -> Workflow

Check the status of Notification Mailers and it should be up.
If the mailers are down, please check the mailers log files and start the mailers. 
Click on Agent Listeners to see the status of “Workflow Deferred Agent Listener “ and “Workflow Deferred Notification Agent Listener “

Mailer Log files: Login to CM node,  check  $APPLCSF/$APPLLOG/FNDCPGSC*.txt  files. Start the above components if they are down. 

Check the Notification mailers status from back end.

set linesize 200
     set pagesize 100
     select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+);


Check the recipient details:

Please execute the below query to find out the recipient details for the notification_id which came in the alert:
select recipient_role,notification_id,status,mail_status from wf_notifications where notification_id = &notif_id ;
Then, execute the below query to check if recipient role has valid email address and mail preference:
SELECT email_address, nvl(WF_PREF.get_pref(name, 'MAILTYPE'), notification_preference) FROM wf_roles WHERE name = '&recipient_role';
Recipient can receive notification only when notification preference is set to anything other than  'QUERY' / 'DISABLED' / 'SUMMARY' / 'SUMHTML' 
Recipient can receive notification only if he/she has valid email address. 
The above query gives details about both. 
If the email address is not specified: Need to update the source [ either FND_USER or PER ] with correct email address. We will have to ask the user to update the recipient to have valid email address. 
If Notification preference is not having correct value: We need to update the user to have correct preference for that userid.  Preferences can be updated by the respective user by login to ERP, clicking on Preferences and updating the notification preference.

Test the notification Mailer

If mailer is up and recipient has proper setups, then perform a test notification to see if the test notification are delivered properly.

a. Ensure that user has valid email address.  If not, please set the email address in Security -> User -> Define form 
b. Do a test notification to user.
Login to EBS as SYSADMIN -> System Administrator [ Responsibility ] -> Oracle Application Manager -> Workflow -> Click on button next to “Notification Mailer” -> Click on View Details -> Test Mailer -> Give username as recipient Role. 

After this, we should receive the notification email. If we get the notification email, then outbound notification mailer is working fine.  Issue could be specific to the notification.  If we are not getting notification emails, then there could be an issue with mailers itself. 

If we do not receive test notification emails [ even after correct email address and preference ], then check the notification mailer log files for any errors.  You may restart the notification mailer and see if the notification can send the notifications. 

Check the workflow Queue Tables

Get Additional details about the notification id for which alert is triggered:

Run $FND_TOP/sql/wfmlrdbg.sql  for the notification id reported in the alert. This script takes notification_id as its argument. Please run this from APPS user on application node. This will create a html in the current working directory with all the details about the notifications:

Please download the html from the server and check for the below: 

• Notification Recipient Role Information
• WF_DEFERRED Queue Status  [PROCESSED in WF_DEFERRED - The message is enqueued to WF_NOTIFICATION_OUT,  READY in WF_DEFERRED - Check if Deferred Agent Listener is running  ]
• WF_ERROR Queue Status
• Error Notification(s)
• WF_NOTIFICATION_OUT Queue Status  [PROCESSED in WF_NOTIFICATION_OUT - The message is sent as e-mail,  READY in WF_NOTIFICATION_OUT - Check if Notification Mailer is running  ]






Thursday, December 21, 2017

ADZDEXRPT.sql Fails 'AD_ZD_PREP' Invalid or with With Error: ORA-06508: PL/SQL: could not find APPS.AD_ZD_PREPAD_ZD_PREP Due To Missing Package 'xdb_migrateschema' or PLS-00201: identifier 'SYS.DBMS_OBJECTS_APPS_UTILS'

Issue:

AD_ZD_PREP Invalid while running script: $AD_TOP/sql/ADZDEXRPT.sql as part of EBS 12.2 upgrade

SQL> select owner,object_name,object_type,status from dba_objects where object_name like 'AD_ZD%' and status ='INVALID';

OWNER           OBJECT_NAME                    OBJECT_TYPE         STATUS
--------------- ------------------------------ ------------------- -------
APPS            AD_ZD_PREP                     PACKAGE BODY        INVALID

SQL> alter package AD_ZD_PREP compile body;

Warning: Package Body altered with compilation errors.

SQL> sho error
Errors for PACKAGE BODY AD_ZD_PREP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
297/6    PL/SQL: Statement ignored
297/6    PLS-00201: identifier 'SYS.DBMS_OBJECTS_APPS_UTILS' must be
         declared

633/4    PL/SQL: Statement ignored
633/4    PLS-00201: identifier 'SYS.DBMS_OBJECTS_APPS_UTILS' must be
         declared

715/6    PL/SQL: Statement ignored
715/6    PLS-00201: identifier 'SYS.DBMS_OBJECTS_APPS_UTILS' must be
         declared

1081/4   PL/SQL: Statement ignored
1081/17  PLS-00201: identifier 'SYS.DBMS_OBJECTS_APPS_UTILS' must be
         declared
       
Solution:

I.
Check if the latest R12.TXK.C.Delta patch applied on instance.
If yes, Please run the adgrants from the latest R12.TXK.C.Delta patch to grant privilages to APPS user
If not, Please apply the patch and check.

Recompile the package and check.

II.
For issue: Error: PL/SQL: ORA-00942: table or view does not exist
sys.xdb$moveSchemaTab;

Sol:

1. Confirm if the package 'xdb_migrateschema' exists using the following select statement:

select owner, object_name from dba_objects where object_name like upper ('xdb_migrateschema');

If it does NOT exist, then use the following scripts to create it and confirm results again using the above query:

conn / as sysdba

@?/rdbms/admin/dbmsxdbschmig.sql
@?/rdbms/admin/prvtxdbschmig.plb

2. Run adgrants.sql.
3. Recompile the package AD_ZD_PREP and ensure it compiles successfully.
4. Continue with the upgrade and confirm online patching is successfully enabled.

III.

If you see the issue with the package: XDB_MIGRATESCHEMA invalid.

Sol:

Connect database sys as sysdba user

SQL> @?/md/admin/catnomgdidcode.sql

and

SQL> @?/md/admin/catmgdidcode.sql

Run adgrants.sql

Compile package : XDB_MIGRATESCHEMA
Compile package : AD_ZD_PREP

IV.

If you see the issue with the package: SYS.DBMS_OBJECTS_APPS_UTILS not exist

SQL> select owner, object_name, object_type from dba_objects where object_name='DBMS_OBJECTS_APPS_UTILS';
no rows selected

1. Please refer to Note 1594274.1 (Oracle E-Business Suite Release @ 12.2: Consolidated List of Patches and Technology Bug Fixes) to find the corresponding patch that delivers the missing bug fixes.

2. If an overlay patch is needed for any particular patch listed, the footnote for that patch also applies to the overlay patch.

3. At a minimum ensure that the following bug fixes are not missing in your instance:

17421340
17487358
12749597
14692386

4. If the above patches are not applied, apply them in a test environment with guidance from Note 1594274.1.

5. Retest the issue.


OR

Connect DB as sys user and execute below.

cd $ORACLE_HOME/rdbms/admin/

SQL> @dbmsobj.sql

select owner, object_name, object_type from dba_objects where object_name='DBMS_OBJECTS_APPS_UTILS';

Compile Invalid packages and test.

Friday, December 12, 2014

Data Dictionary Curruption error in R12.2 adop

For Seeded table mismatch in any phase
==============================
Example

[apsid@host001 <RUN> ]$ adop phase=finalize

Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:

 Please wait. Validating credentials...


RUN file system context file: /sid/inst/fs2/inst/apps/sid_host001/appl/admin/sid_host001.xml
PATCH file system context file: /sid/inst/fs1/inst/apps/sid_host001/appl/admin/sid_host001.xml
Execute SYSTEM command : df /sid/applmgr/fs1

************* Start of  session *************
 version: 12.2.0
 started at: Wed Oct 08 2014 11:33:11

APPL_TOP is set to /sid/applmgr/fs2/EBSapps/appl
  There is already a session which is incomplete. Details are:
        Session Id            :   47
        Prepare phase status  :   COMPLETED
        Apply phase status    :   ATLEAST ONE PATCH IS ALREADY APPLIED
        Cutover  phase status :   NOT COMPLETED
        Abort phase status    :   NOT COMPLETED
        Session status        :   FAILED
  Will continue with previous session
yes
  [START 2014/10/08 11:34:32] adzdoptl.pl run
    ADOP Session ID: 47
    Phase: finalize
    Log file: /sid/applmgr/fs_ne/EBSapps/log/adop/47/adop_20141008_113227.log
    [START 2014/10/08 11:34:42] finalize phase
      [UNEXPECTED]Dictionary Corrupted:
      [START 2014/10/08 11:34:56] Data Dictionary Curruption Details
        APPS            INV_MWB_CG_TRANSFER            V_20140912_2110 APPS            INV_MWB_CG_TRANSFER            V_20131106_0451 TS mismatch: 08-OCT-14 10:53:56 08-NOV-13 05:44:00
      [END   2014/10/08 11:34:57] Data Dictionary Curruption Details
      NOTE: Please contact Oracle Support and request them to open a bug against
            Oracle Application Install(166), Component Online Patching(OP).
      [UNEXPECTED]Finalize phase completed with errors/warnings. Please check logfiles
      Log file: /sid/applmgr/fs_ne/EBSapps/log/adop/47/adop_20141008_113227.log

adop exiting with status = 1 (Fail)


D_OWNER         D_NAME                         D_EDITION                 P_OWNER         P_NAME                         P_EDITION                 REASON
--------------- ------------------------------ ------------------------- --------------- ------------------------------ ------------------------- --------------------------------------------------
APPS            INV_MWB_CG_TRANSFER            V_20140912_2110           APPS            INV_MWB_CG_TRANSFER            V_20131106_0451           TS mismatch: 08-OCT-14 10:53:56 08-NOV-13 05:44:00

Elapsed: 00:00:12.00




Solution
======

SQL> select du.name d_owner, d.name d_name, d.defining_edition d_edition,
       pu.name p_owner, p.name p_name, p.defining_edition p_edition,
  2    3        case
  4           when p.status not in (1, 2, 4) then 'P Status: ' || to_char(p.status)
  5        else 'TS mismatch: ' ||
  6             to_char(dep.p_timestamp, 'DD-MON-YY HH24:MI:SS') || ' ' ||
  7             to_char(p.stime, 'DD-MON-YY HH24:MI:SS')
  8        end reason
  9  from sys."_ACTUAL_EDITION_OBJ" d,
 10       sys.user$ du,
 11       sys.dependency$ dep,
 12       sys."_ACTUAL_EDITION_OBJ" p,
 13       sys.user$ pu
 14  where d.obj# = dep.d_obj#
 15    and d.owner# = du.user#
 16    and p.obj# = dep.p_obj#
 17    and p.owner# = pu.user#
 18    and d.status = 1                                    -- Valid dependent
  and bitand(dep.property, 1) = 1                     -- Hard dependency
 19   20    and d.subname is null                               -- !Old type version
 21    and not(p.type# = 32 and d.type# = 1)               -- Index to indextype
 22    and not(p.type# = 29 and d.type# = 5)               -- Synonym to Java
 23    and not(p.type# in(5, 13) and d.type# in (2, 55))   -- TABL/XDBS to TYPE
 24    and (p.status not in (1, 2, 4) or p.stime <> dep.p_timestamp)
 25  /

D_OWNER                        D_NAME
------------------------------ ------------------------------
D_EDITION                      P_OWNER
------------------------------ ------------------------------
P_NAME                         P_EDITION
------------------------------ ------------------------------
REASON
--------------------------------------------------------------------
APPS                           INV_MWB_CG_TRANSFER
V_20140912_2110                APPS
INV_MWB_CG_TRANSFER            V_20131106_0451
TS mismatch: 08-OCT-14 10:53:56 08-NOV-13 05:44:00

SQL> @$AD_TOP/patch/115/sql/adzddtsfix.sql
exec dbms_utility.invalidate(794147,NULL,0);
exec sys.utl_recomp.recomp_parallel


SQL> exec dbms_utility.invalidate(794147,NULL,0);

PL/SQL procedure successfully completed.

SQL> exec sys.utl_recomp.recomp_parallel

PL/SQL procedure successfully completed.

SQL> select du.name d_owner, d.name d_name, d.defining_edition d_edition,
       pu.name p_owner, p.name p_name, p.defining_edition p_edition,
  2    3        case
  4           when p.status not in (1, 2, 4) then 'P Status: ' || to_char(p.status)
  5        else 'TS mismatch: ' ||
  6             to_char(dep.p_timestamp, 'DD-MON-YY HH24:MI:SS') || ' ' ||
  7             to_char(p.stime, 'DD-MON-YY HH24:MI:SS')
      end reason
  8    9  from sys."_ACTUAL_EDITION_OBJ" d,
 10       sys.user$ du,
 11       sys.dependency$ dep,
 12       sys."_ACTUAL_EDITION_OBJ" p,
 13       sys.user$ pu
 14  where d.obj# = dep.d_obj#
  and d.owner# = du.user#
 15   16    and p.obj# = dep.p_obj#
 17    and p.owner# = pu.user#
 18    and d.status = 1                                    -- Valid dependent
 19    and bitand(dep.property, 1) = 1                     -- Hard dependency
 20    and d.subname is null                               -- !Old type version
 21    and not(p.type# = 32 and d.type# = 1)               -- Index to indextype
 22    and not(p.type# = 29 and d.type# = 5)               -- Synonym to Java
 23    and not(p.type# in(5, 13) and d.type# in (2, 55))   -- TABL/XDBS to TYPE
 24    and (p.status not in (1, 2, 4) or p.stime <> dep.p_timestamp)
 25  /

no rows selected


For Custom Objects
==================
Inform to custom schema owner to check and drop those objects

Friday, June 13, 2014

opatch not working to apply April PSU patch:17572728 in weblogic 12.1.2.0.0 : setHomeDirs.sh: No such file or directory

Component :  Weblogic 12c
version: 12.1.2.0.0

Whenever we try to apply PSU on weblogic 12c (12.1.2), we have to apply the patch under MW_HOME (middleware home) ex: /sid/fmw/product/12  as ORACLE_HOME.

Issue:
======

$ /sid/fmw/product/12/OPatch/opatch lsinventory -jdk $JAVA_HOME
/sid/fmw/product/12/oracle_common/common/bin/setHomeDirs.sh: line 16: /sid/fmw/product/12/utils/config/10.3/setHomeDirs.sh: No such file or directory

Solution
========
If the directory  /sid/fmw/product/12/utils/config/10.3 is not created , first create the directory

and copy the setHomeDirs.sh as below

cp /sid/fmw/product/12//inventory/Templates/oracle_common/common/bin/setHomeDirs.sh /sid/fmw/product/12/utils/config/10.3


Issue 2:
========

./opatch lsinventory -detail -jdk $JAVA_HOME

Exception in thread "main" java.lang.NoClassDefFoundError: com/oracle/cie/gdr/external/InventoryException
        at oracle.opatch.OPatch.main(OPatch.java:548)
Caused by: java.lang.ClassNotFoundException: com.oracle.cie.gdr.external.InventoryException
        at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
        at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:425)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:358)
        ... 1 more
       
 Solution
 ========

 This issue occurs mostly if you try to apply higher version of OPatch in the lower version home.
 In my case , Have got this issue because i tried to update my OPatch with 13.1.0.0 , but this is only supported for 11.1.0.9
 You can get the version by reverting to older version and check the opatch lsinventory -detail -jdk $JAVA_HOME

 Ex: 

 bash-4.1$ ./opatch lsinventory -detail -jdk $JAVA_HOME
 Oracle Interim Patch Installer version 11.1.0.9.0
 Copyright (c) 2011, Oracle Corporation.  All rights reserved.


 Oracle Home       : /sid/fmw/product/12/oracle_common
 Central Inventory : /sid/fmw/oraInventory
    from           : /sid/fmw/product/12/oracle_common/oraInst.loc
 OPatch version    : 11.1.0.9.0
 OUI version       : 11.1.0.9.0
 OUI location      : /sid/fmw/product/12/oracle_common/oui
 Log file location : /sid/fmw/product/12/oracle_common/cfgtoollogs/opatch/opatch2014-06-12_00-15-11AM_1.log

 Patch history file: /sid/fmw/product/12/oracle_common/cfgtoollogs/opatch/opatch_history.txt


 OPatch detects the Middleware Home as "/sid/fmw/product/12"

 Lsinventory Output file location : /sid/fmw/product/12/oracle_common/cfgtoollogs/opatch/lsinv/lsinventory2014-06-12_00-15-11AM.txt

 --------------------------------------------------------------------------------
 Installed Top-level Products (1):

 Oracle AS Common Toplevel Component                                  11.1.1.2.0
There are 1 products installed in this Oracle Home.

Friday, March 7, 2014

Multiple Sets of Books is not compatible with MultiOrg

adadmin error
Error in addgbc():
Multiple Sets of Books is not compatible with MultiOrg



The issue is caused by the following setup:

Duplicate entries for BSDK Bestseller application in FND_PRODUCT_INSTALLATIONS table.

+++++++++++++++++++++++++++++++++++++++++++++++++++++

When the following queries are executed, two entries are found for the same application in FND_PRODUCT_INSTALLATIONS table with same application name and application id but different oracle id.

1 . SELECT APPLICATION_ID,COUNT(*)
FROM FND_PRODUCT_INSTALLATIONS
GROUP BY APPLICATION_ID
HAVING COUNT(*) > 1;
----------------------------------------------------------
Upon execution the result will be as follows:

APPLICATION_ID COUNT(*)
-------------- ----------
20023 2

----------------------------------------------------------

2. SELECT FPI.APPLICATION_ID, FPI.ORACLE_ID, FAL.APPLICATION_NAME
FROM FND_PRODUCT_INSTALLATIONS FPI, FND_APPLICATION_TL FAL
WHERE FPI.APPLICATION_ID= "<--this value will be obtained from the above query-->"
AND FPI.APPLICATION_ID = FAL.APPLICATION_ID;
----------------------------------------------------------
Upon execution the result will be as follows:-

APPLICATION_ID  ORACLE_ID APPLICATION_NAME
-------------- ---------- --------------------------------------------------
         20023        900 XXX Custom Application
         20023      20044 XXX Custom Application


Note: The data over here is customer specific, it may be different for case to case basis.
--------------------------------------------------------


Solution

SELECT LAST_UPDATE_DATE, APPLICATION_ID, STATUS, ORACLE_ID FROM
FND_PRODUCT_INSTALLATIONS WHERE APPLICATION_ID = <--this value is customer specific-->;

NOTE: There will be two duplicate entries in the FND_PRODUCT_INSTALLATIONS table.

SQL> /

LAST_UPDATE_DATE   APPLICATION_ID S  ORACLE_ID
------------------ -------------- - ----------
12-SEP-13                   20023 I        900
12-SEP-13                   20023 I      20044

3. In the FND_PRODUCT_INSTALLATIONS table, check for the STATUS = 'L' for each application which from the above query when executed.

4. Remove the application which has values for STATUS = 'L' and application_id same as the other one.

(STATUS =>, I means installed, L means custom, N means not installed, and S means installed as shared product in FND_PRODUCT_INSTALLATIONS table).

5. Take backup of FND_PRODUCT_INSTALLATIONS table (either with export or with create table)

6. SQL> delete from FND_PRODUCT_INSTALLATIONS where APPLICATION_ID= 20023 and ORACLE_ID=20044;

7. SQL> SELECT FPI.APPLICATION_ID, FPI.ORACLE_ID, FAL.APPLICATION_NAME FROM FND_PRODUCT_INSTALLATIONS FPI, FND_APPLICATION_TL FAL WHERE FPI.APPLICATION_ID=20023 AND FPI.APPLICATION_ID = FAL.APPLICATION_ID;

    APPLICATION_ID  ORACLE_ID APPLICATION_NAME
    -------------- ---------- --------------------------------------------------
             20023        900 XXX Custom Application
   
    SQL> SELECT APPLICATION_ID,COUNT(*)
    FROM FND_PRODUCT_INSTALLATIONS
    GROUP BY APPLICATION_ID
    HAVING COUNT(*) > 1;  2    3    4
   
    no rows selected

8. SQL> commit

Reference Note id: Error in addgbc(): Multiple Sets of Books is not compatible with MultiOrg (Doc ID 1163458.1)



Saturday, January 11, 2014

ORACLE R12.2 ADOP LOGFILES and STATUS CHECK

To debug Online Patching issues which utilize the adop (AD Online Patching) tool we need to collect adop log files located in the <INSTALL BASE>/fs_ne/EBSapps/log/adop directory
e.g. /u01/PROD/fs_ne/EBSapps/log/adop

Each cycle of adop creates a subdirectory corresponding to the patch session ID, e.g.

/u01/PROD/fs_ne/EBSapps/log/adop/1
/u01/PROD/fs_ne/EBSapps/log/adop/2
etc..etc

When running adop the on-screen terminal output will mention which adop session ID  is in use.
e.g. /u01/PROD/fs_ne/EBSapps/log/adop/9/apply_20121011_024437

STEP 1:  Obtain all of the log files within the session ID directory. It is best to obtain a zip of the entire directory.

e.g. obtain a zip of /u01/PROD/fs_ne/EBSapps/log/adop/9

The session ID directory will contain:

a) A trace file for each phase;
e.g.
adop_20130316_085026.log 
adop_20130316_091340.log 
adop_20130316_210950.log 

b) Logs grouped in phase directories
e.g.
prepare_20130316_085026
apply_20130316_091340 
cutover_20130316_210950 

You can see the timestamps match between each log directory and the respective trace file.
prepare_20130316_085026 directory matches adop_20130316_085026.log
apply_20130316_091340 directory matches adop_20130316_091340.log
etc etc

The same goes for fs_clone activities. i.e. you will see something like this
fs_clone_20130319_233614 --> this is a directory
adop_20130319_233614.log

In almost all cases, when debugging an adop failure the patch log directory will contain the information you need to determine root cause
e.g.
/u01/PROD/fs_ne/EBSapps/log/adop/2/cutover_20130316_210950/

In some cases the onscreen error will indicate which subroutine has failed. You will likely find a sub directory containing log files which matches the failing routine. This should be where you focus your attention
e.g.
/u01/PROD/fs_ne/EBSapps/log/adop/2/cutover_20130316_210950/VIS_ufc/TXK_CTRL_forceshutdown_Sat_Mar_16_21_14_02_2013

STEP 2: Run the following SQL statements
This will show you the status for each adop phase along with its corresponding session id. This is effectively a history of online patching in an environment.

a) select ADOP_SESSION_ID,PREPARE_STATUS,APPLY_STATUS,FINALIZE_STATUS,CUTOVER_STATUS,CLEANUP_STATUS,ABORT_STATUS,STATUS,ABANDON_FLAG,NODE_NAME from AD_ADOP_SESSIONS order by ADOP_SESSION_ID;

Note:
       Y denotes that the phase is done
       N denotes that the phase has not been completed
       X denotes that the phase is not applicable
       R denotes that the phase is running (in progress)
       F denotes that the phase has failed
       P (is applicable only to APPLY phase) denotes at least one patch is already applied for the session id
       C denotes that the status of this ADOP session has completed

Note: Numerical statuses are only relevant for the cutover phase

STEP 3: Check the current status of the adop cycle
Source the run filesystem environment file and run command
adop -status

usage
adop -status  generates a summary report
adop -status <sessionID> generates a summary report for that session ID
adop -status -detail generates a detailed report

General Problems

A.1 General problems with Finalize phase and Abort command
For phase=finalize issues run the following command and attach the adzdshowlog.out file generated:
sqlplus <apps_schema_name>/<apps_Schema_password> @$AD_TOP/sql/ADZDSHOWLOG.sql
Note: the contents of the table will be truncated every time cleanup/prepare phase is run.
Older log information is stored in adzdshowlog.out

or

select * from ad_zd_logs order by log_sequence desc;

A.2 'Duplicate keys found' during Finalize

ERROR at line 1:
ORA-20001: Error: while calling ad_zd.finalize .ORA-01452: cannot CREATE UNIQUE
INDEX; duplicate keys found
ORA-06512: at line 8

Run the following:

sqlplus <apps_schema_name>/<apps_Schema_password> @$AD_TOP/sql/ADZDSHOWLOG.sql

The output will highlight the unique index which is failing. You can then use the following sql to identify the duplicate key values

select <list of columns for which unique index creation failed>, count(*)
from <schema_name>.<table_name> group by <list of columns again> having count(*)>1
e.g.
select REPRESENTATION_CODE, TRX_NUMBER#2, ORG_ID, count(*) from OKL.OKL_TRX_CONTRACTS_ALL group by REPRESENTATION_CODE, TRX_NUMBER#2, ORG_ID having count(*)>1

A.3 Problems with Online Enablement
For failed attempts at Online Enablement i.e. failures of patch 13543062, gather the following information:
1. Patch log
2. Worker logs
3. Output of
select * from ad_zd_logs order by log_sequence desc;

4. output from:
@$AD_TOP/sql/ADZDSHOWDDLS.sql

If enablement appears to be having performance issues or seems to have hanged you can run the following script to determine if enablement is progressing or has hanged:
select count(1) from ad_zd_logs;
Run the scripts every 5 or  10 minutes. If the count is increasing then enablement is progressing.If enablement is progressing but very slowly ensure the DB initialization parameters are set as per the DB 11.2.0.3 and 12.2 requirements.

A.4 To show which patches were applied in each ADOP_SESSION_ID (patching cycle)

select * from ad_adop_session_patches order by end_date desc;

or

set pagesize 200;
set linesize 160;
column adop_session_id format 999999999999;
column bug_number format a15;
column status format a15;
column applied_file_system_base format a23;
column patch_file_system_base format a23;
column adpatch_options format a15;
column node_name format a15;
column end_date format a15;
column clone_status format a15;

select ADOP_SESSION_ID, BUG_NUMBER, STATUS, APPLIED_FILE_SYSTEM_BASE, PATCH_FILE_SYSTEM_BASE, ADPATCH_OPTIONS, NODE_NAME, END_DATE, CLONE_STATUS
from ad_adop_session_patches
order by end_date desc;

Note: STATUS
N - Not Applied In the current node but applied in other nodes
R - Patch Application is going on.
H - Patch failed in the middle. (Hard Failure)
F - Patch failed in the middle but user tried to skip some failures.
S - Patch Application succeeded after skipping the failed jobs.
Y - Patch Application succeeded.
C - Reserved for clone and config_clone. Indicates clone completed

A.5 Issues with FS_CLONE

a) select ADOP_SESSION_ID,BUG_NUMBER,CLONE_STATUS,STATUS,NODE_NAME from AD_ADOP_SESSION_PATCHES order by ADOP_SESSION_ID;
b) sqlplus <apps_schema_name>/<apps_Schema_password> @$AD_TOP/sql/ADZDSHOWLOG.sql
c) zip of files in fs1/EBSapps/comn/clone/FMW/logs, fs2/EBSapps/comn/clone/FMW/logs. These log files are also useful for clone issues during the prepare phase. ( this captures cloning failures for WLS)
d) zip of directory $APPLRGF/TXK/ohsCloneLog ( this captures cloning failures for OHS )

you can check  for fsclone issues in  Prepare phase error link

Other key things to consider in multi web node instances.

- SSH should be enabled to allow communication from the primary node to the secondary nodes. Use txkRunSSHSetup.pl to enable SSH.

- If SSH is not enabled pay very special attention to the specific requirements for running adop in this case (see the maintenance guide)

- When patching ensure you copy patches to the patch home directory of all application tiers e.g. fs_ne/EBSapps/patch

- When debugging issues make sure to you review the adop logs on all application tiers

ORACLE R12.2 ADOP ERRORS

adop errors at fsclone Stage.

Error :

1. Error occurred while executing <perl /sid/applmgr/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl
2. Error occurred while CONFIG_CLONE Patch File System from Run File System using command

Check the adop logfile for errors, and also verify the errors in fsclone logfiles
location : /sid/applmgr/fs_ne/EBSapps/log/adop/<adop session id>/prepare_20140110_003210/ohs1/TXK_SYNC_migrate_Fri_Jan_10_00_40_18_2014

Solutions for different issues.

Error 1:  (/sid/applmgr/fs_ne/EBSapps/log/adop/<adop session id>/adop_20140110_003210.log)

Running [filesystem]/fs1/EBSapps/comn/clone/FMW/pasteBinary.sh -javaHome [filesystem]/fs1/EBSapps/comn/clone/FMW/t2pjdk -al [filesystem]/fs1/EBSapps/comn/clone/FMW/FMW_Home.jar -tl [filesystem]/fs2/FMW_Home -invPtrLoc /etc/oraInst.loc -ldl [filesystem]/fs1/EBSapps/comn/clone/FMW/logs -silent true -executeSysPrereqs false
Script Executed in 7200015 milliseconds, returning status -1
Script timed out.

Solution

1. Open a new terminal.

2. Execute :
      export TIMEDPROCESS_TIMEOUT=-1
      export CONFIG_JVM_ARGS="-Xms1024m -Xmx2048m"

3. Execute :
      adop phase=fs_clone

Error 2:  (/sid/R122UPGR/fs1/EBSapps/comn/clone/FMW/logs)

FSCloneStageAppsTier_<datestamp>.log, the following error will be seen:

/d01/R122UPGR/fs1/FMW_Home/oracle_common/bin/copyBinary.sh -javaHome /d01/R122UPGR/fs1/EBSapps/comn/clone/FMW/t2pjdk -al /d01/R122UPGR/fs1/EBSapps/comn/clone/FMW/FMW_Home.jar -smw /d01/R122UPGR/fs1/FMW_Home -ldl /d01/R122UPGR/fs1/EBSapps/comn/clone/FMW/logs -invPtrLoc /etc/oraInst.loc -silent true
Script Executed in 28420 milliseconds, returning status 255
Script failed, exit code 255

In:  /d01/R122UPGR/fs1/EBSapps/comn/clone/FMW/logs the following error is seen:

SEVERE : Dec 4, 2013 6:17:41 PM - ERROR - CLONE-20275   Insufficient space to create /tmp/CLONINGCLIENT3005869153400434600

Solution

Create space in /tmp or assign more disk storage to /tmp.
OR
assign the diffrent mountpoint as temp directory

export T2P_JAVA_OPTIONS="-Djava.io.tmpdir=/<sid>/applmgr/temp"

Error 3 (/sid/applmgr/fs_ne/EBSapps/log/adop/<adop session id>/prepare_20140110_003210/ohs1/TXK_SYNC_migrate_Fri_Jan_10_00_40_18_2014/wlsTechStack_FSCloneStageAppsTier_01100041.log)

Edit session is enabled in domain. Current Editor = weblogic
Please activate all of your changes before proceed clone

Solution

Login to weblogic console, in home page click on Release configuration button and rerun the adop preprae phase

Error 4 (/sid/applmgr/fs_ne/EBSapps/log/adop/<adop session id>/adop_20140110_003210.log)

adop=prepare encountering [ERROR] Error: While executing on all other nodes

Solution

Since the all nodes are not currently implemented the issue is cause by this autoconfig variable setting.

<shared_file_system oa_var="s_shared_file_system">false </shared_file_system>

It seems your half way to enabling another app node hence this setting

Since there is no extra app node registered.
What happens is that adop then looks for another app node and cannot find the node and it fails .

Please change the setting to false, run autoconfig, and re-test adop prepare

Issue should be resolved

Sunday, December 8, 2013

How to Clone Oracle Inventory if existing Oracle Inventory corrupted

 Cloning Oracle Inventory

perl <ORACLE_HOME>/clone/bin/clone.pl ORACLE_BASE=/sid/oracle/product ORACLE_HOME=/sid/oracle/product/112 ORACLE_HOME_NAME=<home name>

If you get error like 'Oracle home already exist' first detach the home with the below syntax and try clone.pl script.

cd $ORACLE_HOME/oui/bin
./runInstaller -silent -detachHome -invPtrLoc /sid/oracle/product/112/oraInst.loc ORACLE_HOME="/sid/oracle/product/112" ORACLE_HOME_NAME="home name"

You can get the existing home name in inventory.xml (under /sid/oracle/oraInventory/ContentsXML)

Example

-bash-3.2$ ./runInstaller -silent -detachHome -invPtrLoc /sid/oracle/product/112/oraInst.loc ORACLE_HOME="/sid/oracle/product/112" ORACLE_HOME_NAME="home name"

Starting Oracle Universal Installer...


Checking swap space: must be greater than 500 MB.   Actual 283059 MB    Passed

The inventory pointer is located at /sid/oracle/product/112/oraInst.loc

The inventory is located at /sid/oracle/oraInventory
'DetachHome' was successful.
-bash-3.2$



-bash-3.2$ perl /sid/oracle/product/112/clone/bin/clone.pl ORACLE_BASE=/sid/oracle/product ORACLE_HOME=/sid/oracle/product/112 ORACLE_HOME_NAME=<home name>

./runInstaller -clone -waitForCompletion  "ORACLE_BASE=/sid/oracle/product"
"ORACLE_HOME=/sid/oracle/product/112" "ORACLE_HOME_NAME=home name"
-silent -noConfig -nowait
Starting Oracle Universal Installer...


Checking swap space: must be greater than 500 MB.   Actual 283058 MB    Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-12-07_01-40-37AM. Please
wait ...Oracle Universal Installer, Version 11.2.0.3.0 Production

Copyright (C) 1999, 2011, Oracle. All rights reserved.


You can find the log of this install session at:

 /sid/oracle/oraInventory/logs/cloneActions2013-12-07_01-40-37AM.log

....................................................................................................
 100% Done.



Installation in progress (Saturday, December 7, 2013 1:40:57 AM CST)

...............................................................................                   
                             79% Done.
Install successful


Linking in progress (Saturday, December 7, 2013 1:41:27 AM CST)

Link successful


Setup in progress (Saturday, December 7, 2013 1:44:02 AM CST)
Setup successful


End of install phases.(Saturday, December 7, 2013 1:44:32 AM CST)

WARNING:A new inventory has been created in this session. However, it has not yet been registered
as the central inventory of this system.

To register the new inventory please run the script '/sid/oracle/oraInventory/orainstRoot.sh'
with root privileges.

If you do not register the inventory, you may not be able to update or patch the products you
installed.
The following configuration scripts need to be executed as the "root" user.

/sid/oracle/oraInventory/orainstRoot.sh
/sid/oracle/product/112/root.sh

To execute the configuration scripts:
    1. Open a terminal window

    2. Log in as "root"
    3. Run the scripts


The cloning of OraDb11203_ was successful.

Please check '/sid/oracle/oraInventory/logs/cloneActions2013-12-07_01-40-37AM.log' for more details.

ORA-01031: insufficient privileges error when connecting as SYSDBA

SYSDBA OS Authentication

The OS authentication is the process of verifying the identity of the user connecting to the database with the information managed by the OS. An OS user is able to use this authentication method if the following conditions are met:

1. The  user is a member of a special group.
2. the OS authentication is allowed by the server settings(sqlnet.authentication_services is set correctly)

The OS user should belong to the OSDBA group in order to login as sysdba. On Unix the default name of these group is dba. On Windows the name of the group is ORA_DBA.  

On Unix Parameter sqlnet.authentication_services must be set to (ALL) or to (BEQ, <other values>) for this to work. On Windows this parameter must be set to (NTS).

3. Check the value of the SQLNET.AUTHENTICATION_SERVICES parameter in file $ORACLE_HOME/network/admin/SQLNET.ORA

On Unix

This parameter should not be set if no strong authentication method is used. If such a method is being used then set the parameter to one of the following values:

SQLNET.AUTHENTICATION_SERVICES = (ALL)
or
SQLNET.AUTHENTICATION_SERVICES = (BEQ,<the strong auth method>)
Where  <the strong auth method> can be any combination of the following values: TCPS, KERBEROS5, RADIUS

NOTE: If the definition of SQLNET.AUTHENTICATION_SERVICES is preceded by a leading blank space, an ORA-1031 error may occur

On Windows

This parameter should be set to NTS:
SQLNET.AUTHENTICATION_SERVICES = (NTS)

If needed you can add other strong authentication methods besides NTS:
SQLNET.AUTHENTICATION_SERVICES = (NTS,TCPS)


SYSDBA Password File Authentication


The credentials provided when connecting remotely as sysdba are compared to the contents of the passwordfile.
Password file authentication is enabled by setting the database parameter remote_login_password file to "shared" or "exclusive".

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

1. Check whether the OS user is part of the OSDBA group.

A. See what are the groups of the user:

[oracle@seclin4 ~]$ id
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba) context=user_u:system_r:unconfined_t

B. See what is the OSDBA group defined in file $ORACLE_HOME/rdbms/lib/config.[cs]

$ id
$ cd $ORACLE_HOME/rdbms/lib
$ cat config.c or config.s (depending on platform)
 If they do not match output from id command then the solution is:

2) The 'dba' group in the file config.s is set to 'xxx' but your primary group is 'yyy', so to solve the problem, please do the following:
    a)  shutdown all databases running from this home

    b)  $ cd $ORACLE_HOME/rdbms/lib

    c)  $ vi config.c

Example for Linux:

[oracle@seclin4 ~]$ cat $ORACLE_HOME/rdbms/lib/config.c

/* SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access. */
/* Refer to the Installation and User's Guide for further information. */

/* IMPORTANT: this file needs to be in sync with
rdbms/src/server/osds/config.c, specifically regarding the
number of elements in the ss_dba_grp array.
*/

#define SS_DBA_GRP "dba"
#define SS_OPER_GRP "dba"
#define SS_ASM_GRP ""

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};

Example for AIX:

[celcaix3]/grdbms/64bit/app/oracle/product/1120/rdbms/lib> cat config.s
# SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access.
# Refer to the Installation and User's Guide for further information.

.rename H.10.NO_SYMBOL{TC},""
.rename H.11.NO_SYMBOL{TC},""
.rename H.12.NO_SYMBOL{TC},""
.rename H.13.NO_SYMBOL{RO},""
.rename H.14.NO_SYMBOL{RO},""
.rename H.15.NO_SYMBOL{RO},""
.rename H.16.ss_dba_grp{TC},"ss_dba_grp"

.lglobl H.13.NO_SYMBOL{RO}
.lglobl H.14.NO_SYMBOL{RO}
.lglobl H.15.NO_SYMBOL{RO}
.globl ss_dba_grp{RW}

# .text section

# .data section

.toc
T.16.ss_dba_grp:
.tc H.16.ss_dba_grp{TC},ss_dba_grp{RW}
T.10.NO_SYMBOL:
.tc H.10.NO_SYMBOL{TC},H.13.NO_SYMBOL{RO}
T.11.NO_SYMBOL:
.tc H.11.NO_SYMBOL{TC},H.14.NO_SYMBOL{RO}
T.12.NO_SYMBOL:
.tc H.12.NO_SYMBOL{TC},H.15.NO_SYMBOL{RO}
.csect ss_dba_grp{RW}, 3
.llong H.13.NO_SYMBOL{RO}
.llong H.14.NO_SYMBOL{RO}
.llong H.15.NO_SYMBOL{RO}
# End csect ss_dba_grp{RW}
.csect H.13.NO_SYMBOL{RO}, 3
.string "dba"
# End csect H.13.NO_SYMBOL{RO}
.csect H.14.NO_SYMBOL{RO}, 3
.string "dba"
# End csect H.14.NO_SYMBOL{RO}
.csect H.15.NO_SYMBOL{RO}, 3
.string ""
# End csect H.15.NO_SYMBOL{RO}
.llong 0x00000000

# .bss section


Example for Solaris:

        .section        ".text",#alloc,#execinstr
/* 0x0000          7 */         .file   "x.c"
        .section        ".data",#alloc,#write
/* 0x0000          9 */         .global ss_dba_grp
/* 0x0000         10 */         .align  8
                       .global ss_dba_grp
                       ss_dba_grp:
/* 0x0000         17 */         .align  8
/* 0x0000         18 */         .xword  (.L12+0)
/* 0x0004         24 */         .align  8
/* 0x0004         25 */         .xword  (.L13+0)
/* 0x0008         26 */         .type   ss_dba_grp,#object
/* 0x0008         27 */         .size   ss_dba_grp,16
        .section        ".rodata1",#alloc
/* 0x0008         13 */         .align  8
                       .L12:
/* 0x0008         15 */         .ascii  "dba\0"
/* 0x0014         20 */         .align  8
                       .L13:
/* 0x0014         22 */         .ascii  "dba\0"


C) now remove the existing config.o:

                  mv config.o config.old

D)  Check if  updated config.c/config.s can be relinked successfully by running below command        

            $ make -f ins_rdbms.mk config.o
            $ ls -al config.o

E) relink oracle to rebuild config.o and store it inside the oracle executable:

           $ make -f ins_rdbms.mk ioracle

F) Try login '/ as sysdba' again.


On Windows

When using OS authentication on Windows the OS user must be a member of one of the following two groups:

ORA_DBA
ORA_<%ORACLE_SID%>_DBA
The membership to the second group allows the OS user to use OS authentication while connecting to the instance with the name %ORACLE_SID%> only.

Check whether the OS user is a member of any of these two local groups:

Get the name of the OS user:


D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN>echo %username%
dbadmin

Obtain the list of the members of the local group:


D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN>NET LOCALGROUP ORA_DBA
Alias name ORA_DBA
Comment Members can connect to the Oracle database as a DBA without a password
Members
-------------------------------------------------------------------------------
dbadmin
NT AUTHORITY\SYSTEM
The command completed successfully.

D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN>set oracle_sid=d1v10204

D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN>NET LOCALGROUP ORA_%ORACLE_SID%_DBA
Alias name ORA_d1v10204_DBA
Comment Members can connect to instance d1v10204 as a DBA without a password
Members
-------------------------------------------------------------------------------
dbadmin
The command completed successfully.

For more information please review the Nots Ids: 400459.1 , 730067.1

Monday, September 23, 2013

WFLOAD syntax for UPLOAD and DOWNLOAD

1. Navigate to the Submit Requests form in Oracle Applications to submit the Workflow Definitions Loader concurrent program. When you install and set up Oracle Applications and Oracle Workflow, your system administrator needs to add this concurrent program to a request security group for the responsibility that you want to run this program from.

2. Submit the Workflow Definitions Loader concurrent program as a request.

3. In the Parameters window, enter values for the following parameters:

Mode         Specify "Download" to download a process definition from the database to a flat file.
        Specify "Upgrade" to apply a seed data upgrade to a database from an input file. The Workflow Definitions Loader assumes the access level of the file's creator (seed data provider) and overwrites any objects protected at a level equal to or above the upgrade file's access level. The Loader program preserves any customizations made to customizable seed data in the database.
        Specify "Upload" to load a process definition from a flat file into the database. The upload mode is useful to someone who is developing a workflow process. It allows the developer to save definitions to the database without concern that accidental customizations to existing objects might prevent the upload of some process definition elements. The Workflow Definitions Loader uses the access level defined by the input file to upload the process definitions from the file and therefore will overwrite objects in the database that are protected at a level equal to or higher than that file's access level.
        Specify "Force" to force an upload of the process definitions from an input file to a database regardless of an object's protection level You should be certain that the process definition in the file is correct as it overwrites the entire process stored in the database. The Force mode is useful for fixing data integrity problems in a database with a known, reliable file backup.

File         Specify the full path and name of the file that you want to download a process definition to, or upgrade or upload a process definition from.

Item Type     If you set Mode to "Download", use the List button to choose the item type for the process definition you want to download.

4. Choose OK to close the Parameters window.

5. When you finish modifying the print and run options for this request, choose Submit to submit the request.

6. Rather than use the Submit Requests form, you can also run the Workflow Definitions Loader concurrent program from the command line by entering the following commands:

    To upgrade-- WFLOAD apps/pwd 0 Y UPGRADE file.wft

    To upload-- WFLOAD apps/pwd 0 Y UPLOAD file.wft

    To force-- WFLOAD apps/pwd 0 Y FORCE file.wft

    To download-- WFLOAD apps/pwd 0 Y DOWNLOAD file.wft ITEMTYPE1 [ITEMTYPE2 ...ITEMTYPEN]

    Replace apps/pwd with username and password to the APPS schema, replace file.wft with the file specification of a workflow process definition file, and replace ITEMTYPE1, ITEMTYPE2, ... ITEMTYPEN with the one or more item type(s) you want to download. You can also download all item types simultaneously by replacing ITEMTYPE1 with '*' (make sure you enclose the asterisk in single quotes).

Sunday, September 8, 2013

Responsibilities missing in Oracle Applications

Some Tested hints

1. Make sure that the latest ATG_RUP patch has been applied.
2. Run Concurrent Program System: Administrator Responsibility->Request->'Synchronize WF LOCAL tables'
3. Run Concurrent Program: 'Workflow Directory Services User/Role Validation'. (with the parameters 10000,Y,N,N and 10000,N,Y,N)
4. Restart the Workflow Agent Listeners: WF_DEFERRED and WF_JAVA_DEFERRED

Check the responsibilities for the user, if the issue still exist do the following steps

1). Run the following script to check if there is a discrepancy between the tables:

select ura.user_name, ura.role_name
from wf_local_user_roles ur, wf_user_role_assignments ura
where ur.user_name = ura.user_name
and ur.role_name = ura.role_name
and ura.relationship_id = -1
and ((ur.effective_start_date is null or ur.effective_start_date <>
ura.effective_start_date)
or (ur.effective_end_date is null or ur.effective_end_date <> ura.effective_end_date));

If the above script returns any rows, proceed to the next step.

2) One must take a backup of the two tables :

WF_LOCAL_USER_ROLES
and
WF_USER_ROLE_ASSIGNMENTS

3) Run the following update script to correct the discrepancy :

UPDATE WF_USER_ROLE_ASSIGNMENTS set effective_end_date = to_date(null)
where rowid in (select ura.rowid
                from wf_local_user_roles ur, wf_user_role_assignments ura
                where ur.user_name = ura.user_name
                  and ur.role_name = ura.role_name
                  and ura.relationship_id = -1
                  and ((ur.effective_start_date is null or ur.effective_start_date <>ura.effective_start_date)
                  or (ur.effective_end_date is null or ur.effective_end_date <>ura.effective_end_date)));


4) Run the "Workflow Directory Services User/Role Validation" concurrent program to resolve the issue.

5. Re-test the issue.

Thursday, August 29, 2013

SGA_TARGET,SGA_MAX_SIZE and SGA_TARGET vs SGA_MAX_SIZE

SGA_TARGET

SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:

    Buffer cache (DB_CACHE_SIZE)
    Shared pool (SHARED_POOL_SIZE)
    Large pool (LARGE_POOL_SIZE)
    Java pool (JAVA_POOL_SIZE)
   
If these automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.

The following pools are manually sized components and are not affected by Automatic Shared Memory Management:

    Log buffer
    Other buffer caches, such as KEEP, RECYCLE, and other block sizes
    Streams pool
    Fixed SGA and other internal allocations

The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.

SGA_MAX_SIZE

SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance.

     Initial size of SGA at startup, dependent on the sizes of different pools in the SGA, such as buffer cache, shared pool, large pool, and so on.

SGA_TARGET vs SGA_MAX_SIZE

SGA_MAX_SIZE sets the overall amount of memory the SGA can consume but is not dynamic. SGA_TARGET, a new 10g feature used for Automatic Shared Memory Management allows for dynamic resizing of SGA
SGA_MAX_SIZE parameter is the max allowable size to resize the SGA Memory area parameters. If the SGA_TARGET is set to some value then the Automatic Shared Memory Management (ASMM) is enabled, the SGA_TARGET value can be adjusted up to the SGA_MAX_SIZE parameter, not more than SGA_MAX_SIZE parameter value.
i.e. If SGA_MAX_SIZE=4GB and SGA_TARGET=2GB, later period of time, if you want you can resize your SGA_TARGET parameter to the value of SGA_MAX_SIZE i.e. 4GB, you can't resize the SGA_TARGET value to more than 4GB.
Usually, sga_max_size and sga_target will be the same value, but there may be times when you want to have the capability to adjust for peak loads. By setting the Oracle sga_max_size parameter higher than sga_target, you allow dynamic adjustment of the sga_target parameter.

Note that sga_max_size is for Oracle 10g only, and in 11g and beyond, Oracle automatic memory management is configured using the memory_target  and memory_max_target initialization parameters.  The memory_target parameter specifies the amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. The memory_max_target AMM parameter specifies the max size that memory_target may take.
You can enable and disable AMM by changing the value of these parameters to non-zero settings:
    sga_target
    sga_max_size
    pga_aggregate_target
    sga_max_size


Monday, May 20, 2013

Forms Servlet or Socket Mode

Many products within the Oracle E-Business Suite have screens that are built with Oracle Forms.  Oracle Forms can be run in either servlet mode or socket mode.  Apps 11i is based on Forms 6i and is configured to run in socket mode by default.  Apps 12 is based on Forms 10g and is configured to run in servlet mode by default.

Forms Servlet Mode

The Forms Listener Servlet is a Java servlet that delivers the ability to run Oracle Forms applications over HTTP and HTTPS connections. It manages the creation of a Forms Server Runtime process for each client, as well as network communications between the client and its associated Forms Server Runtime process.

The desktop client sends HTTP requests and receives HTTP responses from the web server. The HTTP Listener on the web server acts as the network endpoint for the client, keeping other servers and ports from being exposed at the firewall.

Forms Socket Mode

Initial releases of the Oracle Forms Server product used a simple method for connecting the client to the server. The connection from the desktop client to the Forms Listener process was accomplished using a direct socket connection.  The direct socket connection mode was suitable for companies providing thin client access to Forms applications within their corporate local area networks. For the direct socket connection mode, the client had to be able to see the server and had to have permission to establish a direct network connection.

Although the direct socket connection mode is perfectly suited for deployments within a company’s internal network, it's not the best choice for application deployment via unsecured network paths via the Internet. A company connected to the Internet typically employs a strict policy defining the types of network connections that can be made by Internet clients to secure corporate networks. Permitting a direct socket connection from an external client exposes the company to potential risk because the true identity of the client can be hard to determine.

Servlet Mode Advantages

    HTTP and HTTPS traffic is easily recognizable by routers, while socket mode communications is generally considered suspect and treated on an exception basis.
    Existing networking hardware can be used to support basic functions such as load-balancing and packet encryption for network transit.
    More resilient to network and firewall reconfigurations.
    More robust: servlet connections can be reestablished if network connections drop unexpectedly for Forms, Framework, and JSP-based pages.
    Is the only supported method for generic Oracle Forms customers, and therefore is more thoroughly tested by the Forms and E-Business Suite product groups.
    Performance traffic can be monitored via tools like Oracle Real User Experience Insight (RUEI).
    Socket mode is not supported on Windows-based server platforms.

Socket Mode Advantages


    Uses up to 40% less bandwidth than Forms servlet mode.  This may be perceived by Wide Area Network (WAN) users as causing slower responsiveness, depending upon network latency.
    Uses fewer application-tier JVM resources than servlet mode, due to fewer TCP turns and lack of overhead associated with HTTP POST handling.

Switching Apps Deployments Between Modes

Due to its numerous advantages, Forms servlet mode is the preferred and recommended deployment model for Forms on the web.

There may be circumstances where you need to switch between the default Forms modes.  You might wish to switch your Oracle E-Business Suite Release 12 environment to socket mode to improve performance or reduce network load.  You might wish to switch your Apps 11i environment to servlet mode as part of your rollout to external web-based end-users outside of your organization.

If you're running Apps 11i and would like to switch to servlet mode, see:

    Using Forms Listener Servlet with Oracle Applications 11i (Note 201340.1)

If you're running Apps 12 and would like to switch to socket mode, see:

    Using Forms Socket Mode in Oracle Applications Release 12 (Note 384241.1)

Wednesday, February 27, 2013

AutoConfig Customization


Autoconfig is a very effective configuration management tool, but there are times when it falls short of meeting all our requirements and it becomes necessary to customize it. Following are some of the examples where you would need to customize autoconfig. You need to add another zone to the Jserv.  In this case you would have to customize oracle supplied autoconfig template which corresponds to jserv.properties configuration file, to add entries for another zone, you would also have to create a custom template for the properties file for this new custom zone. You may also need to add some custom context variables to the context file for this new custom template. You have some custom product tops. You would have to add these custom product top environment variables to the formservlet.ini file to access the custom forms. In this case you will have to customize the formservlet.ini’s autoconfig template to add your custom product top variables. You can construct these product tops based on the $APPL_TOP's context variable i.e.%s_at%/<custom_product_top> or you can create new context variable for each custom producttop. In that case you will have to add these to the context file as custom context variables. You have developed a custom application and you want autoconfig to maintain all the configuration files of this custom application. In this case you will have to create custom driver files, custom templates and probably some custom context variables. All the above examples show that there are three types of customizations that should satisfy most of the custom requirements:
Adding custom context variables to the context file
Customizing an AutoConfig template file delivered by Oracle.
Creating custom Autoconfig templates and custom Autoconfig driver.
Adding Custom Context Variables:
Adding a context variable to the context file means, adding a node in the context file with mandatory attribute oa_var and optional attributes oa_type, oa_enabled and scope. This node will have only one child node that will be a text node holding the value of the context variable. Autoconfig doesn’t care where or at what level this node is put in the file. So to make this work you can use any xml parser and add the node to the context file at any position you like and it will serve the purpose. But there are two drawbacks to this method. One this would cause maintenance problem since it will be difficult to differentiate between custom and seeded context variables and secondly oracle's software is not aware of this customization, which means whenever we run adbldxml.pl oradclonectx.pl to clone the context file, we will loose our custom entries. The first problem can be easily overcome by adding all custom context variables under a top level xml node oa_customized and starting all the custom node oa_var values from "c_". Now we can easily distinguish between custom and seeded context variables and it’s easy to maintain since we know where all the customizations lie in the context file. In the context file section, I mentioned that the context file is created by plugging in instance specific values into the context file template $AD_TOP/admin/adxmlctx.tmp. So adding xml node oa_customized and all our custom variables under oa_customized to this template would take care of the second problem too. Now let's look at different ways of adding custom context variables.

Oracle Application Manager:
Oracle Application Manager from version 2.1 onwards provides us with extensive ways to manipulate the context files. Updating the context variables is one of them, which we looked at earlier. It also provides us with the option of adding custom context variables. Adding custom context variables in OAM results in following sequence of events:
OAM saves the custom context variable details like name, attribute oa_var value, attribute oa_type value, title and description into table fnd_oam_context_custom.
OAM updates the existing row's status to 'H' (History) for adxmlctx.tmp infnd_oam_context_files.
OAM inserts a new row for adxmlctx.tmp with status 'S'. This new adxmlctx.tmp has ourcustom context variable added under node oa_customized.
OAM Requests FNDFS listener of the destination node to update the adxmlctx.tmp on thefile system.
OAM Repeats the process for all the context files with status='S' and context_type=’A’
Using perl module TXK::XML:
Oracle Application Manager is indeed a neat way to add custom context variables but there are a few problems. We have to add one variable at a time, so in our report server configuration file example, for replacing maxconnect, cachesize, maxidle, initengine and englife we will have to repeat the process 5 times. This can be quite cumbersome as the number of variables to be added increase. Secondly we need access to Oracle Applications but there are times (like configuring a clone) when we want to add custom context variables but OAM is unavailable. This calls for a programmatic method to add custom context variables.Using perl module TXK::XML we can write a small perl program to add many context variables atone shot without needing any access to Oracle Application Manager. For doing this we have to first load the context file and the context template file as TXK::XML object and then use add Node method of TXK::XML package(class for OOP enthusiasts) to add the custom nodes. The only thing to note is that add Node expects a reference to a hash containing all the information about the node to be added. I am going to show you a piece of code for adding custom variables. It will add the custom variables mentioned in the above example of reports server configuration file. Following table details the information about the context variables we are adding.
The only question that remains is, how do we know REP60_server.ora is the template for reportsserver configuration file. In this case I found out by “grepping” for the configuration file name in the driver files (mostly adtmpl.drv and fndtmpl.drv since they manage most of the configuration files).The third field in the template’s file entry in the product driver gives us the template name. Before ADX.F this was the only way to find template files for configuration files. With ADX.F, since oracle has decided to completely support autoconfig customizations, they have provided a script $AD_TOP/bin/adtmplreport.sh to find out the template names. This script in turn runs the javaclass oracle.apps.ad.tools.configuration.ATTemplateReport. This script is a generic script to generate report on all the autoconfig templates and corresponding configuration files. But it also provides a way to find out the template file from the given configuration file and vice versa. It alsoreports if there is a custom template for the given configuration file.

adtmplreport.sh contextfile=/u01/home/applHOPP/admin/HOPP_jinzo.xml \ target=/u01/app/oracle/product/config/HOPP_jinzo/8.0.6/reports60/server/REP60_HOPP.ora verbose#########################################################################Generating Report .....#########################################################################APPL_TOP Context[AD_TOP]TEMPLATE FILE : /u01/home/applHOPP/ad/11.5.0/admin/template/REP60_server.oraTARGET FILE : /u01/app/oracle/product/config/HOPP_jinzo/8.0.6/reports60/server/REP60_HOPP.ora