Author: Apps DBA

How to Resolve FRM-92101 error while launching forms in Oracle EBS 12.2.x versions?

In EBS 12.2.6 Environment,
Launching Forms fails with Error FRM-92101 intermittently.
FRM-92101: There was a
failure in the Forms Server during startup. This could happen due to invalid
configuration. Please look into the web-server log file for details
 
Solution
1.      
Quick workaround for this issue is to clear the
browser cache, restart the browser and launch the forms session again.
OR
2.      
Check all the required lib files are installed
or not and try relinking the forms executables to resolve the issue.
       OR
3.      
This is a bug with Internet Explorer and
R12.2.X. Contact Oracle support to provide a fix.

How to Enable Database Vault in Oracle database Version 11gR1?



Steps to enable Database Vault in Oracle Database 11gR1:


1. In the command prompt, use DVCA utility to enable Oracle Database Vault.


e.g.


dvca -action enable 
  -oh /u01/app/oracle/product/11.1.0/db_1
  -service conn_alias 
  -instance orcl 
  -dbname orcl 
  -owner_account lbrown_dvowner 
  -logfile dvcalog.txt 


Enter SYS password: sys_password
Enter owner password: owner_password


Parameter Description:
======================


-service is the name of the database specifier. The specifier can be a connect descriptor or net service name. 


-instance is the name of the database instance.


-dbname is the database name.


-sys_passwd is the SYS password. If you use a cleartext password on the command line, you must include the nodecrypt option. If you omit the password, DVCA prompts you for it. Preferably, omit the password and then enter it interactively when prompted.


-owner_account is the Oracle Database Vault Owner account name.


-owner_passwd is the Oracle Database Vault Owner account password. If you use a cleartext password on the command line, you must include the nodecrypt option. If you omit the password, DVCA prompts you for it. Preferably, omit the password and then enter it interactively when prompted.


-logfile is an optional flag to specify a log file name and location. You can enter an absolute path, or enter a path that is relative to the location of the $ORACLE_HOME/bin directory.


-silent is the option to run in command line mode. This option is required if you are not running DVCA in an xterm window.


-nodecrypt is the option to read plaintext passwords.


-lockout is the flag to use to disable SYSDBA operating system authentication.


2. Stop the database, Database Control console process, and listener.


Enable the Oracle Database Vault option as follows:
 The make command enables both Oracle Database Vault (dv_on) and Oracle Label Security (lbac_on).
 You must enable Oracle Label Security before you can use Database Vault.


cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_on lbac_on


cd $ORACLE_HOME/bin
relink all


3.Restart the database, Database Control console process, and listener.


4. Verify Oracle Database Vault and Oracle Label Security are enabled.


SELECT * FROM V$OPTION WHERE PARAMETER = ‘Oracle Label Security’;


SELECT * FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;

EBS 12.2.6 login Page is taking long time to load

EBS Login Performance Issues:


After providing username and password in the login page of EBS 12.2.6, loading home page is taking very long time to load.


weblogic console url shows that one of the oacore servers, oacore_server2 is in WARNING state due to Max number of Stuck Threads reached.


Error Message from oacore log
=============================


<Error> <WebLogicServer> <BEA-000337> <[STUCK] ExecuteThread: ’16’ for queue: ‘weblogic.kernel.Default (self-tuning)’ has been busy for “605” seconds working on the request “Workmanager: default, Version: 0, Scheduled=true, Started=true, Started time: 605929 ms
[
GET /OA_HTML/OA.jsp?page=/oracle/apps/per/perimage/webui/PerImgViewPG&transactionid=82152123&language_code=US&pCalledFrom=PERWSIMG&OAMC=N&oas=Wnrjc-sK-Ns6nz5o7H7HIg..&pPersonId=16520&payLegislationCode=AE&CallFromForm=’Y’ HTTP/1.1
Accept: */*
Accept-Language: en-US
User-Agent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 10.0; WOW64; Trident/7.0; .NET4.0C; .NET4.0E)
Accept-Encoding: gzip, deflate
Cookie: oracle.uix=0^^GMT+4:00^p; treemenu1=0; JSESSIONID=tg0hURm_tL89fEex9hDrVrvC1rLBjpTn3fc2pZ89o7Hpb7jR7gVi!222603901; JsessionIDForms=Lh0hURqNRRpfmiTouQ_0kF2MiYYn5x8j8cW5AhjSBMEyOEfvp1oa!-851768; DPRPROD=l6hjdVQuX3p1xvWrRtPjEW8SAj
ECID-Context: 1.005NsDH9rp8FCC0_zxo2yW0002km000083;kXjE
Connection: Keep-Alive
X-Forwarded-For: 10.11.32.120
Proxy-Client-IP: 10.11.32.120
X-WebLogic-KeepAliveSecs: 30
X-WebLogic-Force-JVMID: 222603901


]”, which is more than the configured time (StuckThreadMaxTime) of “600” seconds. Stack trace:
        java.net.SocketInputStream.socketRead0(Native Method)
        java.net.SocketInputStream.read(SocketInputStream.java:152)
        java.net.SocketInputStream.read(SocketInputStream.java:122)
        oracle.net.nt.MetricsEnabledInputStream.read(TcpNTAdapter.java:730)






Solution1
=====


Temporary workaround is to Restart the oacore servers to release the stuck threads


Solution2
========


1. Increase the value of paramter “StuckThreadMaxTime” TO 3600 Seconds from the weblogic Console.


Log in to WebLogic console 

Click on the Admin server >> Click on the Configuration tab >> Tuning tab and set the below values 

How to create a Database link in GL to transfer FSG reports?



Some times, to migrate FSG reports from one environment to the other, its easy to create a dblink between the source and target environemnts and transfer the reports.


STep-by-Step Process to create database link using General Ledger Responbility.


1. Ensure that network connectivity is open between the Source and Target environments.


2. Login to database node of source instance and copy the contents of tnsnames.ora




Extract from tnsnames.ora


TEST1=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=localhost.domain.com)(PORT=1524))
            (CONNECT_DATA=
                (SID=TEST1)
            )
        )


4. Add the above tns entry in the tnsnames.ora file on the application server node of Target instance (Where db link need to be created)




5. Format the tns entry into a single line as below




(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.domain.com)(PORT=1524))(CONNECT_DATA=(SID=TEST1)))




6. Navigate to General Ledger Superuser Reponsibility.


Click on SEtup->>System–> Database links


A form will popup –> Click on “New Database Link”


Input the below values


Database Name: TEST1
Description :Database link for FSG reports
Connect String:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.domain.com)(PORT=1524))(CONNECT_DATA=(SID=TEST1)))
DOMAIN Name: DOMAIN.COM
Apps Username: APPS
APPS Password: <Password>


7. Verify that db link is created


select * from dba_db_links;

How to create directories in Oracle Database?

Database Directories are required to read/write to a filesystem location from plsql code.


Below is the syntax to create database directory


SQL> create directory PLSQL_DIR AS ‘/oratmp/code’;


Directory created.


Grant Read/write privileges to Required Schema (e.g . APPS)


SQL> GRANT READ,WRITE ON DIRECTORY PLSQL_DIR TO APPS;


Grant succeeded.

How to send mail from Linux system

Use the below command to send mail from a Linux server to a user email-id:

# echo “this is the body of the email” | mailx -s “test mail” -r “From” -S smtp=”your-smtp” xyz@domain123.com

-s =         Specifies the subject.
-r =         Email sent from.
-S =        Specifies the smtp server.

We can also add the configuration in mailx config file  /etc/mail.rc

set smtp=your.smtp.server

set from=”from email address”

Then run the mailx command and then enter the body of the email, hit enter, and finally press Ctrl+D to deliver it.


# mailx -vvv -s “email subject”  external-email-address

<Body of the email>


Ctrl D to deliver the message

How to use coe_xfr_sql_profile.sql to Improve performance of a sql query?

Oracle SQL Performance tuning is one of the frequently reported issues in a Oracle Database administrator’s daily job.
Very often we notice that sql queries or concurrent Programs which were running within few minutes earlier are now taking hours to be completed.
Underlying reason is due to the plan_hash_value used by the query has been changed.
coe_xfr_sql_profile.sql is the script developed by Oracle Support which helps us to identify the plan_hash_value which will resolve the performance issue.


Steps to use the script coe_xfr_sql_profile.sql


1. Download the script to a temporary directory on the database server.
Script is also available in Oracle Metalink.


2. Before Running the coe_xfr_sql_profile.sql, we need to know the sql_id of the sql query which is taking longer time to complete than usual.


To find the sql_id use below query


2.1 If you know the database SID of the long running query.
select inst_id,sid,serial#,status,last_Call_et,sql_id from gv$session where sid= ‘&sessionid’;


2.2 If you know the query test, Use gv$sql and gv$sqlarea views to get the sql_id.


3. Login to sqlplus as sysdba and run coe_xfr_sql_profile.sql at the SQL prompt.


$ sqlplus “/as sysdba”
SQL> @coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)


Enter value for 1: 0a3f7vuks8d7y   (–this is the sql_id of long running sql)
PLAN_HASH_VALUE AVG_ET_SECS
————— ———–
1484137450 15.6
3622468234 4560.76


[Output shows the list of available plan hash values, which can be forced on to the sql query. We need to select the plan_hash_value with low ETA to resolve the performance issue]


Parameter 2:
PLAN_HASH_VALUE (required)


Enter value for 2: 1484137450 




++Based on the inputs provided, it generates a sql script with naming convention coe_xfr_sql_profile_0a3f7vuks8d7y_1484137450.sql as output


coe_xfr_sql_profile_<sql_id>_<plan_hash_value>.sql


4. Run the script as sysdba


SQL>coe_xfr_sql_profile_0a3f7vuks8d7y_1484137450.sql 


5. Verify that Performance issue is resolved by Re-running the sql.

How to check if Oracle Database Vault is enabled?



Below sql query can help us to identify if Oracle Database Vault is enabled or Disabled


SQL> SELECT * FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;


PARAMETER                                          VALUE        CON_ID
————————————————– ——– ———-
Oracle Database Vault                              FALSE             0




Database Vault is Disabled.