Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Friday, January 2, 2009

Cannot reconnect to gateway

Yesterday, Anand Reddy called me and told me that adrepctl.sh was unable to start the reports server. This was the error:

adrepctl.sh start

Cannot reconnect to gateway
Cause: Application Object Library is unable to reconnect to your gateway
ORACLE account after you unsuccessfully attempted to sign-on.
Action: Check that your gateway environment variable is set correctly.
starting Reports Server for OSSTEST1 on port 7079.

Cannot reconnect to gateway
Cause: Application Object Library is unable to reconnect to your gateway
ORACLE account after you unsuccessfully attempted to sign-on.
Action: Check that your gateway environment variable is set correctly.
adrepctl.sh: exiting with status 0'

Metalink Note 261584.1 has a solution for this problem.

The cause is wrong Entry for TWO_TASK in the dbc file under the $FND_TOP/secure directory. I checked the dbc file I found that TWO_TASK=Database instead of erp11i. Instead of manually modifying the dbc file, I decided to regenerate it.

$COMMON_TOP/admin/install/adgendbc.sh

adgendbc.sh started at Thu Jan 1 01:39:02 EST 2009


Enter the APPS username: apps

Enter the APPS password:

SQL*Plus: Release 8.0.6.0.0 - Production on Thu Jan 1 01:39:26 2009

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter value for 1: Enter value for 2: Enter value for 3: Connected.
Updated profile option value - 1 row(s) updated
Application Id : 0
Profile Name : APPS_DATABASE_ID
Level Id : 10001
New Value : erp11i_erp11i
Old Value : erp11i_erp11i

PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options


Params=fnd_jdbc_stmt_cache_free_mem=TRUE
fnd_jdbc_buffer_min=1
fnd_jdbc_buffer_max=5
fnd_jdbc_buffer_decay_interval=300
fnd_jdbc_buffer_decay_size=5
fnd_jdbc_usable_check=false
fnd_jdbc_context_check=true
fnd_jdbc_plsql_reset=false


Unique constraint error (00001) is OK if key already exists
Application server ID already exists for this host - loading..
ADD executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc

Trying to update information ...

java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

UPDATE call failed with exit code 1
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fnd_jdbc_buffer_max=5
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_size=5 fnd_jdbc_usable_check=false
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

DBC generation failed with exit code 1

Generating /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

DBC generation failed with exit code 1
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fnd_jdbc_buffer_max=5
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_size=5 fnd_jdbc_usable_check=false
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

DBC generation failed with exit code 1

Generating /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

DBC generation failed with exit code 1
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fnd_jdbc_buffer_max=5
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_size=5 fnd_jdbc_usable_check=false
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

DBC generation failed with exit code 1

Updating Server Security Authentication
java.sql.SQLException: Io exception: Invalid number format for port number
Database connection to jdbc:oracle:thin:@host_name:port_number:database failed
Updating Server Security Authentication failed with exit code 1
adgendbc.sh exiting with status 1
ERRORCODE = 1 ERRORCODE_END
$

I decided to check which sql was failing in the package:

1 select line,text
2 from dba_source
3 where line between 356 and 367
4 and name='FND_APP_SERVER_PKG'
5* AND TYPE='PACKAGE BODY'
SQL> /

LINE
----------
TEXT
--------------------------------------------------------------------------------
356
-- the value of columns that determines a Desktop Node.

357
select SUPPORT_CP, SUPPORT_FORMS, SUPPORT_WEB, SUPPORT_ADMIN,

358
SUPPORT_DB, PLATFORM_CODE


LINE
----------
TEXT
--------------------------------------------------------------------------------
359
into l_support_cp, l_support_forms, l_support_web, l_support_admin,

360
l_support_db, l_platform

361
from fnd_nodes


LINE
----------
TEXT
--------------------------------------------------------------------------------
362
where server_address = p_address;

363


364
IF SQL%notfound THEN


LINE
----------
TEXT
--------------------------------------------------------------------------------
365
RAISE no_data_found;

366
END IF;

367



12 rows selected.

1* select node_name,server_address from fnd_nodes
SQL> /

NODE_NAME SERVER_ADDRESS
------------------------------ ------------------------------
erp11i 192.168.10.1
APPS11I 192.168.10.1
APACHE11I 192.168.22.13
AUTHENTICATION *

As you can see the nodes ERP11i and APPS11I have the same server_address. That is the reason for ORA-01422: exact fetch returns more than requested number of rows. To correct it, I updated the correct server_address for erp11i.

SQL> update fnd_nodes
2 set server_address='192.168.21.1'
3 where node_name='erp11i';

1 row updated.

SQL> commit;

Commit complete.

I moved all existing dbc files in $FND_SECURE to a backup directory

cd $FND_SECURE
mkdir back
mv * back


$ ./adgendbc.sh

adgendbc.sh started at Thu Jan 1 02:00:39 EST 2009


Enter the APPS username: apps

Enter the APPS password:

SQL*Plus: Release 8.0.6.0.0 - Production on Thu Jan 1 02:00:46 2009

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter value for 1: Enter value for 2: Enter value for 3: Connected.
Updated profile option value - 1 row(s) updated
Application Id : 0
Profile Name : APPS_DATABASE_ID
Level Id : 10001
New Value : erp11i_erp11i
Old Value : erp11i_erp11i

PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64 bit Production
With the Partitioning, OLAP and Data Mining options


Params=fnd_jdbc_stmt_cache_free_mem=TRUE
fnd_jdbc_buffer_min=1
fnd_jdbc_buffer_max=5
fnd_jdbc_buffer_decay_interval=300
fnd_jdbc_buffer_decay_size=5
fnd_jdbc_usable_check=false
fnd_jdbc_context_check=true
fnd_jdbc_plsql_reset=false


Unique constraint error (00001) is OK if key already exists
ADD executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erpaai.dbc

Trying to update information ...

cp: cannot access /erp11i/erpapp/appl/fnd/11.5.0/secure/template.dbc
Changed GUEST_USER_PWD to GUEST/ORACLE
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_ erp11i.dbc
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fn d_jdbc_buffer_max=5
Changed GUEST_USER_PWD to GUEST/ORACLE
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_ erp11i.dbc
Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_s ize=5 fnd_jdbc_usable_check=false
Changed GUEST_USER_PWD to GUEST/ORACLE
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
Changed GUEST_USER_PWD to GUEST/ORACLE
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc

Generating /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc
cp: cannot access /erp11i/erpapp/appl/fnd/11.5.0/secure/template.dbc
Unable to locate /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc

DBC generation failed with exit code 1
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fn d_jdbc_buffer_max=5
Unable to locate /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc

Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_size=5 fnd_jdbc_usable_check=false
Unable to locate /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc

Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
Unable to locate /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc

DBC generation failed with exit code 1

Generating /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
cp: cannot access /erp11i/erpapp/appl/fnd/11.5.0/secure/template.dbc
DBC argument specified, ignoring SECURE_PATH
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fn d_jdbc_buffer_max=5
DBC argument specified, ignoring SECURE_PATH
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_size=5 fnd_jdbc_usable_check=false
DBC argument specified, ignoring SECURE_PATH
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
DBC argument specified, ignoring SECURE_PATH
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc

Updating Server Security Authentication
AUTHENTICATION OFF executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
adgendbc.sh exiting with status 1
ERRORCODE = 1 ERRORCODE_END

adgendbc.sh failed as it could not find template.dbc file in $FND_SECURE. So I moved it back into $FND_SECURE from backup:

mv $FND_SECURE/back/template.dbc $FND_SECURE

I re-ran adgendbc.sh:

$ ./adgendbc.sh

adgendbc.sh started at Thu Jan 1 02:03:02 EST 2009


Enter the APPS username: apps

Enter the APPS password:

SQL*Plus: Release 8.0.6.0.0 - Production on Thu Jan 1 02:03:09 2009

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter value for 1: Enter value for 2: Enter value for 3: Connected.
Updated profile option value - 1 row(s) updated
Application Id : 0
Profile Name : APPS_DATABASE_ID
Level Id : 10001
New Value : erp11i_erp11i
Old Value : erp11i_erp11i

PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options


Params=fnd_jdbc_stmt_cache_free_mem=TRUE
fnd_jdbc_buffer_min=1
fnd_jdbc_buffer_max=5
fnd_jdbc_buffer_decay_interval=300
fnd_jdbc_buffer_decay_size=5
fnd_jdbc_usable_check=false
fnd_jdbc_context_check=true
fnd_jdbc_plsql_reset=false


Unique constraint error (00001) is OK if key already exists
Application server ID already exists for this host - loading..
ADD executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc

Trying to update information ...

Changed GUEST_USER_PWD to GUEST/ORACLE
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fnd_jdbc_buffer_max=5
Changed GUEST_USER_PWD to GUEST/ORACLE
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_size=5 fnd_jdbc_usable_check=false
Changed GUEST_USER_PWD to GUEST/ORACLE
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
Changed GUEST_USER_PWD to GUEST/ORACLE
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc

Generating /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc
DBC argument specified, ignoring SECURE_PATH
Changed GUEST_USER_PWD to GUEST/ORACLE
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fnd_jdbc_buffer_max=5
DBC argument specified, ignoring SECURE_PATH
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc
Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_size=5 fnd_jdbc_usable_check=false
DBC argument specified, ignoring SECURE_PATH
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc
Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
DBC argument specified, ignoring SECURE_PATH
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc

Generating /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
DBC argument specified, ignoring SECURE_PATH
Changed GUEST_USER_PWD to GUEST/ORACLE
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fnd_jdbc_buffer_max=5
DBC argument specified, ignoring SECURE_PATH
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_size=5 fnd_jdbc_usable_check=false
DBC argument specified, ignoring SECURE_PATH
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
DBC argument specified, ignoring SECURE_PATH
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc

Updating Server Security Authentication
AUTHENTICATION OFF executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
adgendbc.sh exiting with status 0
ERRORCODE = 0 ERRORCODE_END
$


$ adrepctl.sh start

You are running adrepctl.sh version 115.33

starting Reports Server for erp11i on port 7005.

adrepctl.sh: exiting with status 0

$

2 comments:

jporacle said...

Hi
Thanks for the helpful tips above.
I need to recreate my dbc file as am getting the error :

"Cannot reconnect to gateway

Cause: Application Object Library is unable to reconnect to your gateway ORACLE account after you unsuccessfully attempted to sign-on."

However, not sure what parameters are required when running adgendbc.sh. Could you please advise?

Rathi_KG said...

this resolved my issue.. good doc