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

$