EBS -- Apps Tech Interface on Linux

These interface is written by bash. It is designed to ease the system administration. Through this interface, application stop/start, db stop/start, session kill operations, patching operations and all related admin operations can be done by clicking the menu buttons.

->Click here to read more

Database - Best Practice For Upgrading To Latest Patchset With CPU/PSU And One-off Patches

To minimize downtime that is caused by patching and upgrading Oracle RDBMS, consider the following upgrade methodology;

--First update the software, then update the database--

Here are the steps based on the above methodology:

• Install the patchset (suppose 11.2.0.3)
• Apply the latest PSU or CPU
• Apply any mandatory/recommended/required patches for the environment (gather this information from -> known issues and alerts of  10.2.0.4 for example, or related application documentation, interoperability notes)
• Upgrade the database --> Actual upgrade (for example catupgrd.sql) and execute post installation instructions
• execute PSU or CPU post installation instructions. 
• execute post installation instructions of mandatory/recommended/required patches that are applied above (if any)

Note that: The post installation instructions of PSU/CPU and any other patches applied after the upgrade, should be executed not only on upgraded databases, these instructions should be executed on newly created databases which are created by using dbca sample templates (general,transaction processing, data warehouse).  Because these templates are based on the pre-created data files.(not patchted , not psu applied)

If database is created using custom option or sql "Create database.." , no need to execute the post install instructions of PSU/CPU or any other patches, as these creation methods actually create the database using existing/updated/patched binaries in Oracle Home .

Kpslice in memory, without downtime Linux Kernel Upgrade

How Linux is Built > nice and clean.

RAC - What is Oracle RAC?

I like this slide of Oracle , because it s actually basically and cleanly summarizes the redundancy offered in Oracle RAC.
The related presentation is also very good. I suggest you to read that.
Oracle RAC 11g Release 2 Client Connections by Markus Michalewicz, Oracle.

Database - About SCAN --11GR2

Scan is introduced in 11G2 Grid infrastructe distributed with Oracle Database.
Scan is configured in the installation of the Grid Infrastructure, which is an Oracle Home that contains Oracle ASM and Oracle Clusterware.
SCAN stands for Single Client Access Name. It s a RAC feature and provides single hostname for the client to access databases in the cluster.
By using scan, Clients will use one and only hostname (scan name) to connect databases in the cluster. This also means, even if a new node is added to the cluster or a node removed from the cluster, client's connection strings (for example: tns entries in tnsnames.ora) will not need to be changed or modified.
So one simple connection string (like EZConnect) will provide cluster access.
SCAN provides Failover and load balancing for the connections, through its integrated conceptual design.

Scan concepts works like below;

• Tree associated scan name and ip's are registered in DNS.
• DNS query, issued by Client, will return 3 ip address like a list. ( The order of the ip address in the list are determined by the Round Robin algorithm used in DNS)
• Client will use one of the ip addresses returned from server, and send a connection request. If first ip address returns an error, client will continue with the second one, and so on.
• Connection request will be sent to the associated Scan listener.(Specified by the REMOTE_LISTENER parameter like : remote_listener=test.host.com:1521)
• Scan listener will take the request and direct it to the least crowded Instance through the Local listener of that Instance.   (specified by the LOCAL_LISTENER parameter like local_listener=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.9)(PORT=1521))))   
• Local listener will be listening the requests for the instance registered to it. Local listener will be listening on the Vip configured on its node. Vip configuration is needed because, it is an important concept for being redundant.So the actual connection to the database will be made by the local listener.. 
• If  a node fails, related vip's will be migrated to surviving nodes. Clients will encounter an error immeditely. They will be aware of this failover, and reconnect to the surviving nodes without making any changes.

This process works if Client and Db versions are 11GR2. For older versions; scan can not be used -- for example client will not be able to handle 3 ip addresses returned from DNS.)

For oder versions something like the following tns should be used;


TEST =(DESCRIPTION=
(ADDRESS_LIST= (LOAD_BALANCE=on)(FAILOVER=ON)
 (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.10)(PORT=1521))
 (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.11)(PORT=1521))
 (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.12)(PORT=1521)))
 (CONNECT_DATA=(SERVICE_NAME= test.host.com)))

The table in following picture shows the scan connectivity ...

Note that, failover process have different types like preconnect or base. I will explain these methods and their sub types in my next post. Failover process that I will explain in my next POST will be TAF (Transparent Application Failover). It can be configured on client or server sid.

Bash - download using bash

Bash can be used to download files from command line.
Here is an example.

TEST_HOSTNAME='downloadmirror01.org'
exec {HTTP_FD}<>/dev/tcp/${TEST_HOSTNAME}/80
echo -ne 'GET /tmp/file.tar.gz HTTP/1.1\r\nHost: '\
${TEST_HOSTNAME}'\r\nUser-Agent: '\
'bash/'${BASH_VERSION}'\r\n\r\n' >&${HTTP_FD}
sed -e '1,/^.$/d' <&${HTTP_FD} >file.tar.gz


Explanation 
We use exec to connect and assign the connection to file descriptor.
Echo transfers the command(http request) to the download server using file descriptor.
sed skips the http headers sent by the sever, and output the resuilt into file.tar.gz.

EBS - Manually Registering Nodes

If autoconfig somehow fails to register or does not register/remove , you can manually register/remove nodes,servers and systems using fnd_net_services package. Note that, this procedure method should not be the preferred method for adding/removing the nodes and servers. It should be used under certain circumstances. (For example : Cloning a TEST instance and there is no time to investigate why autoconfig and postclone do not register the db node)

Fnd_net_services package has following functions and procedures.

Fnd_net_service package spec defition in R11:

procedure register_dbnode(SystemName varchar2,
ServerName varchar2,
SystemOwner varchar2,
SystemCSINumber varchar2,
DatabaseName varchar2,
InstanceName varchar2,
InstanceNumber varchar2,
ListenerPort varchar2,
ClusterDatabase varchar2,
ServiceName varchar2,
RemoteListenerName varchar2,
LocalListenerName varchar2,
HostName varchar2,
Domain varchar2,
OracleHomePath varchar2,
OracleHomeVersion varchar2,
OracleHomeName varchar2,
InterconnectName varchar2,
InstanceSid varchar2,
platform varchar2,
alt_service_instance_1 varchar2 default null,
alt_service_instance_2 varchar2 default null,
alt_service_instance_3 varchar2 default null,
alt_service_instance_4 varchar2 default null,
alt_service_instance_5 varchar2 default null,
alt_service_instance_6 varchar2 default null,
alt_service_instance_7 varchar2 default null,
alt_service_instance_8 varchar2 default null,
VirtualHostName varchar2 default null
);

procedure register_appnode(SystemName in varchar2,
ServerName in varchar2,
SystemOwner in varchar2,
SystemCSINumber in varchar2,
HostName in varchar2,
Domain in varchar2,
RPCPort in varchar2,
PriOracleHomePath in varchar2,
PriOracleHomeVersion in varchar2,
PriOracleHomeName in varchar2,
AuxOracleHomePath in varchar2,
AuxOracleHomeVersion in varchar2,
AuxOracleHomeName in varchar2,
ApplTopPath in varchar2,
ApplTopName in varchar2,
SharedApplTop in varchar2,
ToolsInstanceAlias in out nocopy varchar2,
WebInstanceAlias in out nocopy varchar2,
ToolsDatabaseAlias in out nocopy varchar2,
WebDatabaseAlias in out nocopy varchar2,
SidDefaultAlias in out nocopy varchar2,
JDBCSid in out nocopy varchar2,
isFormsNode in varchar2 default 'Y',
isCPNode in varchar2 default 'Y',
isWebNode in varchar2 default 'Y',
isAdminNode in varchar2 default 'Y',
platform in varchar2,
forceMissingAliases
in varchar2 default 'N'
);

procedure remove_dbNode ( SystemName in varchar2,
ServerName in varchar2,
DatabaseName in varchar2,
InstanceName in varchar2,
Domain in varchar2
);

procedure remove_AppNode ( SystemName in varchar2,
ServerName in varchar2
);

procedure remove_Server ( SystemName in varchar2,
ServerName in varchar2
);

procedure remove_System ( SystemName in varchar2 );

procedure show ( SystemName in varchar2);

procedure show_tnsalias ( p_info in varchar2,
p_tns_alias_guid in raw );
end FND_NET_SERVICES;/


Example: Suppose you cloned the PROD instance as TEST instance. Clone was successful, but you are not able to login the new instance. You query the fnd_nodes table and see that db node is not registered..
connect as apps user, run the fnd_net_services.register_dbnode procedure as follows;

EXEC fnd_net_services.register_dbnode('TEST',
'hosterman_TEST_DB',
'Oracle Apps',
'N/A',
'TEST',
'TEST',
'0',
'1531',
'N',
'TEST',
'TEST_REMOTE',
'TEST_LOCAL',
'uygulama',
'domainerman.com.tr',
'/prodapp/TEST/testdb/10.2.0',
'db1020',
'APPS-DB',
'',
'TEST',
'HP-UX',
'',
'',
'',
'',
'',
'',
'',
''
);

This will register hosterman as db node..

EBS -- difference between ar60run and ar60runb

Both of them are Oracle Reports executables.

The difference is ;

ar60runb: used for bitmapp reports like PS and PDF
ar60run: used for character reports like Text

If the output format of the report/concurrent program defined as "Text", then Oracle EBS/Apps will use ar60run to handle the request. For any other format, ar60runb will be used by Oracle EBS/Apps.

EBS - Retrieve EBS Application User and EBS Apps Schema paswords

There are times, when you will need to retrieve apps schema password or any EBS application user passwords.

To achieve this;
Supposing you are the admin and you have the apps password;

Connect to database with APPS user;

And create a package (get_pwd) with the following statement;
CREATE OR REPLACE PACKAGE get_pwd AS FUNCTION decrypt (KEY IN VARCHAR2,VALUE IN VARCHAR2)
RETURN VARCHAR2;
END get_pwd;
/

CREATE OR REPLACE PACKAGE BODY get_pwd
AS
FUNCTION decrypt (
KEY IN VARCHAR2,
VALUE IN VARCHAR2)
RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
/

Then by using get_pwd fuction created above, passwords can be decyrpted and retrieved.

-- TO GET APPS PASSWORD

Retrieving Apps Password Terminal Screenshot

Query :

SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD')) FROM DUAL)), usertable.encrypted_foundation_password) FROM DUAL) AS apps_password FROM fnd_user usertable WHERE usertable.user_name LIKE UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD') ,1 , INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/') - 1 ) FROM DUAL))

-- TO GET PASSWORD OF A USER

EBS Application user passwords can also be decyrpted and retrieved.

Query:

SELECT
usertable.user_name
,(SELECT
get_pwd.decrypt(UPPER((
SELECT(
SELECT
get_pwd.decrypt(UPPER(
(SELECT upper(fnd_profile.value('GUEST_USER_PWD')) FROM dual)
)
,usertable.encrypted_foundation_password)
FROM dual
) AS apps_password
FROM
fnd_user usertable
WHERE
usertable.user_name LIKE upper(
(SELECT
substr(fnd_profile.value('GUEST_USER_PWD'),1,instr(fnd_profile.value('GUEST_USER_PWD'),'/')-1)
FROM dual)
)
)
),usertable.encrypted_user_password)
FROM
dual
) AS encrypted_user_password
FROM
fnd_user usertable
WHERE
usertable.user_name LIKE upper(:b1)

Using EBS with Google Chrome

This chrome extension can be used to fix the Forms Startup in google chrome.
EBS R12 Forms can be displayed without FRM-92129 error.

Link->https://chrome.google.com/webstore/detail/oracle-ebs-r1211i-enablem/ekkagabmggbmpmncofhgkfigmeldifnc/details

Tested and verified..

Investigating EBS NLS Translation and NLS Data Load problems

EBS translation is done by applying NLS translation patches using adpatch.
This process actually uploads the NLS Data to the database via FNDLOAD utility.
FNDLOAD uploads the data in the .ldt files to the translation tables (_TL)
FNDLOAD uses the controlfiles (.lct) to gather the information for  the data structure.
Using NLS dAta in ldt files are uploaded to the TL tables, actually this process updates the rows that belongs to translated Language.


For example;

ICX_CAT_ATTRIBUTES_TL table contains  a record in TR for every attributes.

1 TR TR Küçük Resim Kalemin küçük resim görüntüsü (GIF veya JPEG formatı)
1 US US Thumbnail Image Thumbnail image of the item (GIF or JPEG format)


This TR records are generated by adadmin's Maintain Multi-lingual tables option.
This options creates the records for each of the languages that does not have records in the _TL tables.
So FNDLOAD actually updates this records..
Maintain Multi-lingual tables option creates records based on the licenced languages in the Oracle Applications.
so that's why, before running Multi-lingual tables, the language should be licensed using OAM.

So for NLS translation following actions should be taken in sequence.

1) License the new language
2) Maintain Multi-Lingual tables
3) Apply Nls translation patch.


Nls translation in EBS becomes a problematic process in some cases.

For example, data is in the ldt , nls translation patch is applied, but the relevant text is still appearing in English .
Or, translation is done, but translated text is invalid.

In the kind of situation an investigation is required and following method should be followed to investigate and fix the translation problem.

First of all, NLS patching steps that I mentioned above should be checked. If all the steps are done, applied and no problems encountered;
then Ldt files are a good place to start investigation.

Ldt files are the cores for files in this nls translation process, as they are where the nls data resides actually.
First of all, the ldt file/files that belongs to the problematic interface/form should be found.
To find problematic ldt files, finding the module name of the form/menu etc.. will be very helpful . Module/product name can be found using System Administrator-> Application -> Menu(for example)
After finding the module/product name , cd to $PRODUCT_TOP/patch/115/import/US (also search the $PRODUCT_TOP/admin/import directory recursively) and find the ldt file using OS tools. (like ls , grep ,cat)
When the US ldt file is found (it is actually ldt file of US language), ldt file of corresponding Language can be found easily by changing the current working directory to $PRODUCT_TOP/patch/115/import/(LANGUAGE) and using the language independent data in US ldt file.
For example, if it s a menu; find the menu name in US ldt and locate the TR ldt file by using grep -i 'FND_NAVSITE4.0' * command in New Language directory. -- ldt files are generally have the same name for US and other languages..

After finding the actual ldt, its contents can be checked with Os tools ( like vi)
If the translation is not correct, file can be edited to reflect the tranlation and loaded with FNDLOAD again.
For example;

FNDLOAD APPS/APPS 0 Y UPLOAD \
   @FND:patch/115/import/afsload.lct \
   @FND:patch/115/import/ESA/afnlnlg.ldt - UPLOAD_MODE=NLS WARNINGS=TRUE


If the translation is already correct, uploading the data can solve the translation problem.. Because, there could be a problem in NLS patching process for that ldt file.

After uploading the data ; application services should be restarted..