ORA-00304: requested INSTANCE_NUMBER is busy

ORA-00304: requested INSTANCE_NUMBER is busy

On starting upon instance  in 2 Node RAC Database,We got the below error message.

SQL> startup;
ORA-00304: requested INSTANCE_NUMBER is busy

We have identified the background sessions are still hanging at the OS level.

Solution:

Solution is to Kill the hanging sessions at the os level and restart the instance.

ps -ef | grep -i DATABASE_SID

kill -9 pid1 pid2

after killing the hanging session , we are able to startup the instance without any issues.

How to find the Weblogic Admin Console URL Location in Oracle OEM?

 We can find the details from setupinfo.txt file. This file is created during the installation time.

 Location: $ORACLE_HOME/oms/install/setupinfo.txt 

 Output: 

Use the following URL to access:

        1. Enterprise Manager Cloud Controls URL: https://test.oracle.com:7802/em
        2. Admin Server URL: https://test.oracle.com:7102/console

The following details need to be provided during the additional OMS install:

        1. Admin Server Hostname: test.oracle.com

        2. Admin Server Port: 7102

How to find the OPP log file location in oracle application 11i/R12.1 and R12.2

Below script to find the OPP log file location in oracle E-Business Suite.

Script:

SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
AND fcpp.action_type = 6
AND fcpp.concurrent_request_id = &&request_id;

Growth of oracle database size in day, week and month wise

Below query to get the database size growth in day, week and month wise.

Query:

set linesize 210
set linesize 210
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a11
COL "Used in %" FORMAT a11
COL "Free in %" FORMAT a11
COL "Database Name" FORMAT a13
COL "Free Space" FORMAT a12
COL "Growth DAY" FORMAT a11
COL "Growth WEEK" FORMAT a12
COL "Growth DAY in %" FORMAT a16
COL "Growth WEEK in %" FORMAT a16
SELECT
(select min(creation_time) from v$datafile) "Create Time",
(select name from v$database) "Database Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.K / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.K / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.K / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*30,2) || ' MB' "Growth MONTH",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*30,3) || '% MB' "Growth MONTH in %"
FROM (SELECT BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG) USED,
(SELECT SUM(BYTES) AS K FROM dba_free_space) FREE
GROUP BY FREE.K;

Query to get the Custom Tables List in oracle application

Below query to get the list of custom tables in oracle application.

Query:

select object_name,
   object_type
from all_objects
where 1=1
and object_type='TABLE'
and status='VALID'
and object_name like 'XXCUST%';

Query to get list of Custom Form List in oracle application

Below query to get the list of Custom Form List in oracle application.

Query:

SELECT  DISTINCT forms.form_name,
         formstl.user_form_name,
         func.function_name,
         func.user_function_name,
         fm.menu_name,
         menu.prompt menu_prompt,
         menu.description,
         restl.responsibility_name
        -- FORMSTL.language
  FROM   fnd_form FORMS,
         fnd_form_tl FORMSTL,
         fnd_form_functions_VL FUNC,
         fnd_menu_entries_VL MENU,
         FND_MENUS FM,
         fnd_responsibility RES,
         fnd_responsibility_tl RESTL
 WHERE     1=1
         and forms.form_id = formstl.form_id
         and func.form_id = forms.form_id
         and menu.function_id = func.function_id
         and menu.menu_id=fm.menu_id
         and res.menu_id = menu.menu_id
         and res.responsibility_id = restl.responsibility_id
         and UPPER(forms.form_name) like 'XXCUST%'      
         order by 1;

Query to get list of Custom Concurrent Programs in oracle applications

Below query to get the list of Custom Concurrent Programs in oracle applications.

Query:

SELECT   DISTINCT fcp.user_concurrent_program_name "Concurrent Program Name",
fa.application_name,
fcp.concurrent_program_name conc_short_name,
decode( fef.execution_method_code, 'H','Host',
'I', 'PLSQL Stored Procedure',
'P', 'Report',
'L', 'SQL Loader',
'Q','SQL*Plus',
'A','Spawned',
'K','Java Concurrent Program',
'B','Request Set Stage Function',
'S','Immediate',
'J','Java Stored Procedure',
'M','Multi-Language Function',
fef.execution_method_code) "execution method",
fef.executable_name,
fcp.description "Concurrent Program Description",
fef.executable_name"Executable Name",
fef.description "Executable Description",
fef.execution_file_name ,
fcp.enabled_flag
,FCP.CREATION_DATE
FROM fnd_executables_form_v fef,
                fnd_concurrent_programs_VL fcp,
                fnd_application_tl fa
WHERE fcp.application_id=fef.application_id
and fa.application_id=fcp.application_id
AND fef.executable_id=fcp.executable_id
AND UPPER(fcp.user_concurrent_program_name) like 'XXCUST%'
ORDER BY 4;

Primary database error ORA-07217: sltln: environment variable cannot be evaluated

Error:

Primary database throw below error while trying to transport archive log to standby database

ALTER SYSTEM SET log_archive_dest_state_2='enable' SCOPE=BOTH;
Errors in file /oracle/diag/rdbms/<oracle_sid>/trace/<oracle_sid>arc2_2894.trc:
ORA-07217: sltln: environment variable cannot be evaluated.
ARC2: FAL archive failed with error 7217. See trace for details
Errors in file /oracle/diag/rdbms/mdmprd/mdmprd/trace/<oracle_sid>_arc2_2894.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing

Fix:

$ORACLE_SID needs to be replaced by proper value

Modify log_archive_format to correct value and restart the redo transport.

ORA-04024: self-deadlock detected while trying to mutex pin cursor

Error:

Database errors when trying to open with the following:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00604: error occurred at recursive SQL level 4
ORA-04024: self-deadlock detected while trying to mutex pin cursor 0x2CD828600

Fix:

Apply the below patch to resolve this issue.

Patch 21372829.

fs_clone Error 1 occurred while Executing txkADOPValidation script

Error:

Lines #(73-77):
    [PROCEDURE] Calling: /u01/app/applmgr/TEST/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPValidations.pl
    [EVENT]     Log: /u01/app/applmgr/TEST/fs_ne/EBSapps/log/adop/4/fs_clone_20170427_171727/TEST_test
    [UNEXPECTED]Error occurred running "perl /u01/app/applmgr/TEST/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPValidations.pl  -contextfile=/u01/app/applmgr/TEST/fs1/inst/apps/TEST_test/appl/admin/TEST_test.xml -patchctxfile=/u01/app/applmgr/TEST/fs2/inst/apps/TEST_test/appl/admin/TEST_test.xml -phase=fs_clone -logloc=/u01/app/applmgr/TEST/fs_ne/EBSapps/log/adop/4/fs_clone_20170427_171727/TEST_test -promptmsg=hide"
    [UNEXPECTED]Error 1 occurred while Executing txkADOPValidation script on test
[PROCEDURE] [START 2017/04/27 17:18:23] Unlocking sessions table

Lines #(74-78):
    [EVENT]     Log: /u01/app/applmgr/TEST/fs_ne/EBSapps/log/adop/4/fs_clone_20170427_171727/TEST_test
    [UNEXPECTED]Error occurred running "perl /u01/app/applmgr/TEST/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPValidations.pl  -contextfile=/u01/app/applmgr/TEST/fs1/inst/apps/TEST_test/appl/admin/TEST_test.xml -patchctxfile=/u01/app/applmgr/TEST/fs2/inst/apps/TEST_test/appl/admin/TEST_test.xml -phase=fs_clone -logloc=/u01/app/applmgr/TEST/fs_ne/EBSapps/log/adop/4/fs_clone_20170427_171727/TEST_test -promptmsg=hide"
    [UNEXPECTED]Error 1 occurred while Executing txkADOPValidation script on test
[PROCEDURE] [START 2017/04/27 17:18:23] Unlocking sessions table
[STATEMENT] Unlocking ad_adop_sessions table for test with wait interval of 60 seconds and number of tries 2

Fix:
To fix this issue test the following to upload the missing PATCH context file to the database:

1. Get the value of your patch context file.

    1.1. Source your PATCH file system

    1.2. Echo the value of $CONTEXT_FILE and take note of it. You will use this value in step #3.

2. Source the RUN filesystem

3. Execute the following command on the run filesystem to upload the patch context file to the database.

$ADJVAPRG oracle.apps.ad.autoconfig.oam.CtxSynchronizer \
action=upload \
contextfile=/u01/app/applmgr/TEST/fs2/inst/apps/TEST_test/appl/admin/TEST_test.xml \
logfile=/tmp/patchctxupload.log

Note: the context file parameter should point to the physical location of the patch context file in your system

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

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

Oracle Application R12 forms not opening in Linux

When we are trying to access forms in Oracle apps R12.1 in Linux got the below error.

Error:

Fix:

When you done with the plugin installation, issue about:plugins and check whether your installed jre plugin is showing in mozilla. if it does you can access the forms. 

To know the recommended jre plugin version issue the below : 
grep plugin $CONTEXT_FILE 
<!-- JDK plugins --> 
<sun_plugin_ver oa_var="s_sun_plugin_ver">1.6.0_07</sun_plugin_ver> 
<sun_plugin_type oa_var="s_sun_plugin_type">jdk</sun_plugin_type> 

Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.18) Gecko/20110621 Oracle/3.6-1.0.1.el5_6 Firefox/3.6.18 

Download and install the below : (Make sure you os bit version when you download) http://www.oracle.com/technetwork/java/javase/downloads/java-archive-downloads-javase6-419409.html#jre-6u33-oth-JPR

Install ---> jre-6u33-linux-x64.bin 

Since my operating ssystem were 64 bit we must create soft link to /usr/lib64 folder instead of /usr/lib 
go to /usr/lib64/mozilla/plugins 

Create softlink as below : 
ln -s /home/oracle/jre1.6.0_33/lib/amd64/libnpjp2.so . 

libnpjp2.so -> /oracle/jre1.6.0_33/lib/amd64/libnpjp2.so 

Now you  can able to access the forms in Linux.

error while loading shared libraries: libXtst.so.6: cannot open shared object file: No such file or directory

Error:

Concurrent program error with the following message.

error while loading shared libraries: libXtst.so.6: cannot open shared object file: No such file or directory.

Fix:

Install the following RPM's to resolve the issue.

yum install libXext.x86_64
yum install libXrender.x86_64
yum install libXtst.x86_64

GConf Error: Failed to contact configuration server; some possible causes are that you need to enable TCP/IP networking for ORBi

Error:

GConf Error: Failed to contact configuration server; some possible causes are that you need to enable TCP/IP networking for ORBit, or you have stale NFS locks due to a system crash. See http://projects.gnome.org/gconf/ for information. (Details - 1: Failed to get connection to session: Failed to connect to socket /tmp/dbus-QS6NzEDBEh: Connection refused)

In the /var/log/messages

gconfd (root-21343): Bad permissions 777 on directory /tmp/gconfd-root
gconfd (root-21343): Failed to get lock for daemon, exiting: Directory /tmp/gconfd-root has a problem, gconfd can't use it

Fix:

1.Change permissions to 700  on directory /tmp/gconfd-root
2. Restart the VNC server.

RC-50004: Fatal: Error occurred in ApplyDatabase:

While installing application R12.1.1, we got the below error.

Error:

RC-50004: Fatal: Error occurred in ApplyDatabase:

Control file creation failed

Cannot execute configure of database using RapidClone

RW-50010: Error: - script has returned an error:   1
RW-50004: Error code received when running external process.  Check log file for details.
Running Database Install Driver for TEST instance

Additional errors recorded in the "addlnctl.txt"

lsnrctl: error while loading shared libraries: /u01/DEV/db/tech_st/11.1.0/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied

Fix:

Following steps should be followed:

a) Switch SELinux from the default "Enforcing" mode that it is running in, to the "Permissive" mode. Contact Unix/Linux Admin for the same.

b) Clean up the File System (DB Tier, Application Tier & Inventory). Review the Note: How To Locate the Log Files and Troubleshoot Rapid Install for Release 12.0.x and 12.1.x (Doc ID 452120.1) for Cleanup instructions.

c) Run Rapidwiz.

IO Error: Got minus one from a read call vendor code 17002

When we are trying to connect database by using Sql developer, we got below error.

Error:

IO Error: Got minus one from a read call vendor code 17002

Fix:

You can follow these steps to avoid the error:

1. Go to directory $ORACLE_HOME/network/admin
2. Make sure you have backup of sqlnet.ora
3. Modify sqlnet.ora file with the parameter: (tcp.validnode_checking = no)
4. If you don't want to disable this, you can put the machine names as follows:  tcp.invited_nodes=(machine1, machine2)
5. Bounce the listener or reload the listner (lsnrctl reload)

License Manager Showing Asia/Pacific Localizations (JA) as 'Not Licensed'?

License Manager Showing Asia/Pacific Localizations (JA) as 'Not Licensed'.

How to get it Licensed ?

Please do the following:

• Go to SYSADMIN responsibility / Oracle Application manager / License Manager / Country Specific Localization.
• Select Singapore
• Retest your issue.

Below link to know Licensed enable or not.

http://krishnasudagani.blogspot.in/2017/04/how-to-find-asiapacific-localizations.html 

How to find Asia/Pacific Localizations (JA) Installed or not in Oracle E-Business Suite (R12.1 / R12.2)

Below script to find the Asia/Pacific Localizations (JA) Installed or not in Oracle E-Business Suite instance.

Script:

SELECT SUBSTR(a.application_name, 1,40) product_name
, a.application_id
, SUBSTR(application_short_name, 1,4) short_name,
DECODE(b.status,'I','INSTALLED',
DECODE(b.status,'S','SHARED',
DECODE(b.status,'N','NOTINSTALLED',b.status))) status
FROM fnd_application_vl a, fnd_product_installations b
WHERE a.application_id = b.application_id
and a.application_short_name like Upper('ja')
and upper(a.application_name) like upper('%asia%')
ORDER BY 1;

Output:

adop fs_clone phase fails after upgrading FMW to 11.1.1.7

The adop fs_clone phase fails after upgrading FMW to 11.1.1.7 with the following error:

Error:

Log file located at /u01/ebs/apps/fs1/inst/apps/ebs/admin/log/clone/FSCloneStageAppsTier_11270942.log
Exception in thread "main" java.lang.NoSuchMethodError: oracle.jdbc.driver.PhysicalConnection.getDefaultAutoRefetch()Z
at oracle.jdbc.driver.OracleStatement.(OracleStatement.java:762)
at oracle.jdbc.driver.T4CStatement.(T4CStatement.java:1255)
at oracle.jdbc.driver.T4CDriverExtension.allocateStatement(T4CDriverExtension.java:48)
at oracle.jdbc.driver.PhysicalConnection.createStatement(PhysicalConnection.java:3460)
at oracle.jdbc.driver.PhysicalConnection.createStatement(PhysicalConnection.java:3425)
at oracle.apps.ad.clone.util.CloneUtil.getServerNamesFromDB(CloneUtil.java:950)
at oracle.apps.ad.clone.util.CloneUtil.getAllServerNamesFromDB(CloneUtil.java:903)
at oracle.apps.ad.clone.FSCloneStageAppsTier.doStage(FSCloneStageAppsTier.java:362)
at oracle.apps.ad.clone.FSCloneStageAppsTier.(FSCloneStageAppsTier.java:149)
at oracle.apps.ad.clone.FSCloneStageAppsTier.main(FSCloneStageAppsTier.java:667)

Fix:

To resolve the issue test the following steps in a development instance and the migrate accordingly:

1. Take a backup of any involved files.

2. Download clean version of the file ojdbc6.jar via the following link:

https://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html

3. Replace the corrupted ojdbc6.jar jdbc driver file.

4. Confirm the file is correct via the following commands:

•    java -jar ojdbc6.jar -getversion
•    md5sum ojdbc6.jar

5. Retest adop fs_clone and confirm it now progresses successfully.

URL validation failed. The error could have been caused through the use of the browser's navigation buttons ( the browser Back button or refresh, for example). If the error persists, Please contact system administrator.

When we are trying to access the Oracle E-Business Suite R12.2.5 forms in Internet Explorer 10, we are getting below error.

Error:

'URL validation failed. The error could have been caused through the use of the browser's navigation buttons ( the browser Back button or refresh, for example). If the error persists, Please contact system administrator.'

Fix:

There could be multiple reasons for the issue, but in our scenario adding this Login URL into trusted sites can resolved my issue. 

Navigation:

• Goto Internet Options
• Security
• Trusted sites
• Sites
• Add this website to the zone.

ORA-06512: at "APPS.HR_API_USER_HOOKS_UTILITY", line 891

When attempting to apply 12.2.6 patch: 21900901:oracle e-business suite 12.2.6 release update pack
the following error occurs.

ERROR:

(XX_HR_USER_HOOK_PKG.POSITION_BEFORE_UPDATE_BK2) The call_package does not ex

ist in the database. Code to carry out this hook call has not been created.

DELETE_POSITION(Business Process API) successful.

BEGIN hr_api_user_hooks_utility.clear_hook_report; END;

*

ERROR at line 1:

ORA-06501: PL/SQL: program error

ORA-06512: at "APPS.HR_API_USER_HOOKS_UTILITY", line 891

ORA-06512: at line 1

To implement the solution, please execute the following steps:

1. Run the following sql.

SQL> select CALL_PACKAGE, CALL_PROCEDURE, ENABLED_FLAG from HR_API_HOOK_CALLS;

This will provide you a list of the user hooks, procedures and enabled_flag values

From this table, update the user hook in question and set it to 'N.'

2. Run the following in sql:

cd $PER_TOP/admin/sql

Log into SQLPLUS as the APPS user

SQL> @hrahkall.sql

This will clear up the child process that are still enabled.

3. Restart the failed worker and allow the patch to complete.

4. Properly create your user hook