Akhilesh pinged me today and told me that autoconfig was not working on a newly upgraded R12 instance. It was failing with these errors:
AutoConfig could not successfully execute the following scripts:
Directory: /r12ascp/erpapp/10.1.3/perl/bin/perl -I /r12ascp/erpapp/10.1.3/perl/lib/5.8.3 -I /r12ascp/erpapp/10.1.3/perl/lib/site_perl/5.8.3 -I /r12ascp/erpapp/appl/au/12.0.0/perl -I /r12ascp/erpapp/10.1.3/Apache/Apache/mod_perl/lib/site_perl/5.8.3/sun4-solaris-thread-multi /r12ascp/erpapp/inst/apps/r12ascp_r12ascp/admin/scripts/adexecsql.pl sqlfile=/r12ascp/erpapp/inst/apps/r12ascp_r12ascp/admin/install
afwebprf.sql INSTE8_PRF 1
amscmprf.sql INSTE8_PRF 1
amswebprf.sql INSTE8_PRF 1
cncmprf.sql INSTE8_PRF 1
csfadmprf.sql INSTE8_PRF 1
oksfrmprf.sql INSTE8_PRF 1
Directory: /r12ascp/erpapp/inst/apps/r12ascp_r12ascp/admin/install
ibywebprf.sh INSTE8_PRF 1
If we tried running any of the above scripts, they failed with this error:
SQLPLUS Executable : /r12ascp/erpapp/10.1.2/bin/sqlplus
SQL*Plus: Release 10.1.0.5.0 - Production on Mon Feb 4 12:04:27 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter value for 1: Enter value for 2: Enter value for 3: Connected.
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
ORA-06512: at "APPS.WF_EVENT", line 3600
ORA-06512: at "APPS.FND_WF_ENGINE", line 1025
ORA-06512: at "APPS.FND_PROFILE", line 3064
ORA-06512: at "APPS.FND_PROFILE", line 3514
ORA-06512: at "APPS.ADX_PRF_PKG", line 203
ORA-06512: at line 167
ORA-06512: at line 217
So I checked the code on line 3600 for wf_event package:
select line,text
from dba_source
where name='WF_EVENT'
and line between 3550 and 3610;
3600
wf_event.local_system_guid := hextoraw(wf_core.translate('WF_SYSTEM_GUID'));
I did another code search:
select line,text
from dba_source
where text like '%WF_SYSTEM_GUID%';
This was the result of the query:
select text into l_result from wf_resources where name='WF_SYSTEM_GUID' and
language='US';
SQL> select text from wf_resources where name='WF_SYSTEM_GUID' and
2 language='US';
TEXT
--------------------------------------------------------------------------------
CAFECAFE-0013-0001-0029-ABCDEFABCDEF
That was weird as that is the GUID used by Oracle JRE plugin 1.3.1.29.
I checked the actual GUID:
SQL> select guid from wf_systems;
GUID
--------------------------------
D37180AE23A4479AE04400212846C6CE
Updated the correct GUID:
SQL> update wf_resources
2 set text='D37180AE23A4479AE04400212846C6CE'
3 where name='WF_SYSTEM_GUID' and
4 language='US';
1 row updated.
SQL> commit;
Re-ran $INST_TOP/admin/install/adwebprf.sql apps appspassword ORACLE_SID
New error this time:
SQL> @afwebprf.sql apps appspassword r12ascp
Connected.
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.WF_EVENT", line 3602
ORA-06512: at "APPS.FND_WF_ENGINE", line 1025
ORA-06512: at "APPS.FND_PROFILE", line 3064
ORA-06512: at "APPS.FND_PROFILE", line 3514
ORA-06512: at "APPS.ADX_PRF_PKG", line 203
ORA-06512: at line 167
ORA-06512: at line 217
Checked the code again:
3602
wf_event.local_system_status := wf_core.translate('WF_SYSTEM_STATUS');
SQL> select text from wf_resources where name='WF_SYSTEM_STATUS' and
2 language='US';
TEXT
--------------------------------------------------------------------------------
CAFECAFE-0013-0001-0029-ABCDEFABCDEF
So I checked with query:
select count(*) from wf_resources where
text='CAFECAFE-0013-0001-0029-ABCDEFABCDEF';
1207 rows
select count(*) from wf_resources;
1775 rows
So I inquired about an existing good instance. No R12 instance was available, so I copied from an 11i instance with sqlplus COPY command:
First put the tnsnames.ora entry for the good instance in the DB $TNS_ADMIN/tnsnames.ora file
Then used the sqlplus copy command, which is the fastest way to copy data between two oracle databases:
SQL> copy from apps/****@11iascp to apps/****@r12ascp create wf_resources_d1 using select * from wf_resources
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table WF_RESOURCES_11i created.
1717 rows selected from apps@r12ascpdev.
1717 rows inserted into WF_RESOURCES_11i.
1717 rows committed into WF_RESOURCES_11i at apps@r12ascp.
Since there was a difference of 58 rows, I first copied those rows in a different table:
create table wf_resources_more as
( select * from wf_resources
where (name,type) in
(
select name,type from wf_resources
minus
select name,type from wf_resources_11i
);
Which created table wf_resources_more with 58 rows.
Then I delete those 58 rows from the wf_resources table:
delete wf_resources
where (name,type) in
(
select name,type from wf_resources
minus
select name,type from wf_resources_11i
);
Then I updated the table with 11i values, through a correlated update statement:
update wf_resources a
set a.text=(select text from wf_resources_11i b
where a.type||a.name = b.type||b.name)
That updated the 1717 common rows with correct values.
Then I inserted the extra 58 rows new in R12
insert into wf_resources (select * from wf_resources_more);
commit;
I ran adwebprf.sql again and it gave a new error:
SQL> @afwebprf.sql apps appspass r12ascp
Connected.
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "APPS.WF_EVENT", line 3604
ORA-06512: at "APPS.FND_WF_ENGINE", line 1025
ORA-06512: at "APPS.FND_PROFILE", line 3064
ORA-06512: at "APPS.FND_PROFILE", line 3514
ORA-06512: at "APPS.ADX_PRF_PKG", line 203
ORA-06512: at line 167
ORA-06512: at line 217
Again I checked code on line 3604 of WF_EVENT:
select name into wf_event.local_system_name
3605
from wf_systems
3606
where guid = wf_event.local_system_guid;
Then I realized that after the correlated update, the wf_resources table now had the GUID of 11i system:
SQL> select name,text
2 from wf_resources
3 where name='WF_SYSTEM_GUID';
NAME
------------------------------
TEXT
--------------------------------------------------------------------------------
WF_SYSTEM_GUID
CA43363B52162863E04400212846C6CE
So I updated it again:
I checked the actual GUID:
SQL> select guid from wf_systems;
GUID
--------------------------------
D37180AE23A4479AE04400212846C6CE
Updated the correct GUID:
SQL> update wf_resources
2 set text='D37180AE23A4479AE04400212846C6CE'
3 where name='WF_SYSTEM_GUID' and
4 language='US';
1 row updated.
SQL> commit;
Re-ran $INST_TOP/admin/install/adwebprf.sql apps appspassword ORACLE_SID
SQL> @afwebprf.sql apps appsapss1 r12ascp
Connected.
[ APPS_WEB_AGENT ]
Application Id : 0
Profile Value : http://r12ascp.justanexample.com:8004/pls/r12ascp
Level Name: SITE
INFO : Updated/created profile option value.
.
Deleted : 0
[ APPS_SERVLET_AGENT ]
Application Id : 0
Profile Value : http://r12ascp.justanexample.com:8004/OA_HTML
Level Name: SITE
INFO : Updated/created profile option value.
.
Deleted : 0
[ APPS_JSP_AGENT ]
Application Id : 0
Profile Value : http://r12ascp.justanexample.com:8004
Level Name: SITE
INFO : Updated/created profile option value.
.
Deleted : 0
[ APPS_FRAMEWORK_AGENT ]
Application Id : 0
Profile Value : http://r12ascp.justanexample.com:8004
Level Name: SITE
INFO : Updated/created profile option value.
.
Deleted : 0
[ WF_MAIL_WEB_AGENT ]
Application Id : 0
Profile Value :
Level Name: SITE
INFO : Updated/created profile option value.
.
.
.
It succeeded.
I told Akhilesh about the result and asked him to run autoconfig. We tailed the log at
$INST_TOP/admin/log/02042015/adconfig.log
Adconfig succeeded without errors this time:
AutoConfig is exiting with status 0
AutoConfig execution completed on Mon Feb 4 20:17:58 2013
Time taken for AutoConfig execution to complete : 2 mins 38 secs
I was overjoyed, and so was Akhilesh. We still had 53 rows which had the value of text as:
CAFECAFE-0013-0001-0029-ABCDEFABCDEF
select count(*) from wf_resources
where text='CAFECAFE-0013-0001-0029-ABCDEFABCDEF';
53 rows.
We would investigate further, how this had happened. I suspect human error. Human error makes troubleshooting more interesting, just like human error makes chess more interesting. If it was a computer playing chess, it would not make any mistakes and win the game. It is the human errors which make chess and troubleshooting, an interesting pursuit.
AutoConfig could not successfully execute the following scripts:
Directory: /r12ascp/erpapp/10.1.3/perl/bin/perl -I /r12ascp/erpapp/10.1.3/perl/lib/5.8.3 -I /r12ascp/erpapp/10.1.3/perl/lib/site_perl/5.8.3 -I /r12ascp/erpapp/appl/au/12.0.0/perl -I /r12ascp/erpapp/10.1.3/Apache/Apache/mod_perl/lib/site_perl/5.8.3/sun4-solaris-thread-multi /r12ascp/erpapp/inst/apps/r12ascp_r12ascp/admin/scripts/adexecsql.pl sqlfile=/r12ascp/erpapp/inst/apps/r12ascp_r12ascp/admin/install
afwebprf.sql INSTE8_PRF 1
amscmprf.sql INSTE8_PRF 1
amswebprf.sql INSTE8_PRF 1
cncmprf.sql INSTE8_PRF 1
csfadmprf.sql INSTE8_PRF 1
oksfrmprf.sql INSTE8_PRF 1
Directory: /r12ascp/erpapp/inst/apps/r12ascp_r12ascp/admin/install
ibywebprf.sh INSTE8_PRF 1
If we tried running any of the above scripts, they failed with this error:
SQLPLUS Executable : /r12ascp/erpapp/10.1.2/bin/sqlplus
SQL*Plus: Release 10.1.0.5.0 - Production on Mon Feb 4 12:04:27 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter value for 1: Enter value for 2: Enter value for 3: Connected.
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
ORA-06512: at "APPS.WF_EVENT", line 3600
ORA-06512: at "APPS.FND_WF_ENGINE", line 1025
ORA-06512: at "APPS.FND_PROFILE", line 3064
ORA-06512: at "APPS.FND_PROFILE", line 3514
ORA-06512: at "APPS.ADX_PRF_PKG", line 203
ORA-06512: at line 167
ORA-06512: at line 217
So I checked the code on line 3600 for wf_event package:
select line,text
from dba_source
where name='WF_EVENT'
and line between 3550 and 3610;
3600
wf_event.local_system_guid := hextoraw(wf_core.translate('WF_SYSTEM_GUID'));
I did another code search:
select line,text
from dba_source
where text like '%WF_SYSTEM_GUID%';
This was the result of the query:
select text into l_result from wf_resources where name='WF_SYSTEM_GUID' and
language='US';
SQL> select text from wf_resources where name='WF_SYSTEM_GUID' and
2 language='US';
TEXT
--------------------------------------------------------------------------------
CAFECAFE-0013-0001-0029-ABCDEFABCDEF
That was weird as that is the GUID used by Oracle JRE plugin 1.3.1.29.
I checked the actual GUID:
SQL> select guid from wf_systems;
GUID
--------------------------------
D37180AE23A4479AE04400212846C6CE
Updated the correct GUID:
SQL> update wf_resources
2 set text='D37180AE23A4479AE04400212846C6CE'
3 where name='WF_SYSTEM_GUID' and
4 language='US';
1 row updated.
SQL> commit;
Re-ran $INST_TOP/admin/install/adwebprf.sql apps appspassword ORACLE_SID
New error this time:
SQL> @afwebprf.sql apps appspassword r12ascp
Connected.
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.WF_EVENT", line 3602
ORA-06512: at "APPS.FND_WF_ENGINE", line 1025
ORA-06512: at "APPS.FND_PROFILE", line 3064
ORA-06512: at "APPS.FND_PROFILE", line 3514
ORA-06512: at "APPS.ADX_PRF_PKG", line 203
ORA-06512: at line 167
ORA-06512: at line 217
Checked the code again:
3602
wf_event.local_system_status := wf_core.translate('WF_SYSTEM_STATUS');
SQL> select text from wf_resources where name='WF_SYSTEM_STATUS' and
2 language='US';
TEXT
--------------------------------------------------------------------------------
CAFECAFE-0013-0001-0029-ABCDEFABCDEF
So I checked with query:
select count(*) from wf_resources where
text='CAFECAFE-0013-0001-0029-ABCDEFABCDEF';
1207 rows
select count(*) from wf_resources;
1775 rows
So I inquired about an existing good instance. No R12 instance was available, so I copied from an 11i instance with sqlplus COPY command:
First put the tnsnames.ora entry for the good instance in the DB $TNS_ADMIN/tnsnames.ora file
Then used the sqlplus copy command, which is the fastest way to copy data between two oracle databases:
SQL> copy from apps/****@11iascp to apps/****@r12ascp create wf_resources_d1 using select * from wf_resources
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table WF_RESOURCES_11i created.
1717 rows selected from apps@r12ascpdev.
1717 rows inserted into WF_RESOURCES_11i.
1717 rows committed into WF_RESOURCES_11i at apps@r12ascp.
Since there was a difference of 58 rows, I first copied those rows in a different table:
create table wf_resources_more as
( select * from wf_resources
where (name,type) in
(
select name,type from wf_resources
minus
select name,type from wf_resources_11i
);
Which created table wf_resources_more with 58 rows.
Then I delete those 58 rows from the wf_resources table:
delete wf_resources
where (name,type) in
(
select name,type from wf_resources
minus
select name,type from wf_resources_11i
);
Then I updated the table with 11i values, through a correlated update statement:
update wf_resources a
set a.text=(select text from wf_resources_11i b
where a.type||a.name = b.type||b.name)
That updated the 1717 common rows with correct values.
Then I inserted the extra 58 rows new in R12
insert into wf_resources (select * from wf_resources_more);
commit;
I ran adwebprf.sql again and it gave a new error:
SQL> @afwebprf.sql apps appspass r12ascp
Connected.
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "APPS.WF_EVENT", line 3604
ORA-06512: at "APPS.FND_WF_ENGINE", line 1025
ORA-06512: at "APPS.FND_PROFILE", line 3064
ORA-06512: at "APPS.FND_PROFILE", line 3514
ORA-06512: at "APPS.ADX_PRF_PKG", line 203
ORA-06512: at line 167
ORA-06512: at line 217
Again I checked code on line 3604 of WF_EVENT:
select name into wf_event.local_system_name
3605
from wf_systems
3606
where guid = wf_event.local_system_guid;
Then I realized that after the correlated update, the wf_resources table now had the GUID of 11i system:
SQL> select name,text
2 from wf_resources
3 where name='WF_SYSTEM_GUID';
NAME
------------------------------
TEXT
--------------------------------------------------------------------------------
WF_SYSTEM_GUID
CA43363B52162863E04400212846C6CE
So I updated it again:
I checked the actual GUID:
SQL> select guid from wf_systems;
GUID
--------------------------------
D37180AE23A4479AE04400212846C6CE
Updated the correct GUID:
SQL> update wf_resources
2 set text='D37180AE23A4479AE04400212846C6CE'
3 where name='WF_SYSTEM_GUID' and
4 language='US';
1 row updated.
SQL> commit;
Re-ran $INST_TOP/admin/install/adwebprf.sql apps appspassword ORACLE_SID
SQL> @afwebprf.sql apps appsapss1 r12ascp
Connected.
[ APPS_WEB_AGENT ]
Application Id : 0
Profile Value : http://r12ascp.justanexample.com:8004/pls/r12ascp
Level Name: SITE
INFO : Updated/created profile option value.
.
Deleted : 0
[ APPS_SERVLET_AGENT ]
Application Id : 0
Profile Value : http://r12ascp.justanexample.com:8004/OA_HTML
Level Name: SITE
INFO : Updated/created profile option value.
.
Deleted : 0
[ APPS_JSP_AGENT ]
Application Id : 0
Profile Value : http://r12ascp.justanexample.com:8004
Level Name: SITE
INFO : Updated/created profile option value.
.
Deleted : 0
[ APPS_FRAMEWORK_AGENT ]
Application Id : 0
Profile Value : http://r12ascp.justanexample.com:8004
Level Name: SITE
INFO : Updated/created profile option value.
.
Deleted : 0
[ WF_MAIL_WEB_AGENT ]
Application Id : 0
Profile Value :
Level Name: SITE
INFO : Updated/created profile option value.
.
.
.
It succeeded.
I told Akhilesh about the result and asked him to run autoconfig. We tailed the log at
$INST_TOP/admin/log/02042015/adconfig.log
Adconfig succeeded without errors this time:
AutoConfig is exiting with status 0
AutoConfig execution completed on Mon Feb 4 20:17:58 2013
Time taken for AutoConfig execution to complete : 2 mins 38 secs
I was overjoyed, and so was Akhilesh. We still had 53 rows which had the value of text as:
CAFECAFE-0013-0001-0029-ABCDEFABCDEF
select count(*) from wf_resources
where text='CAFECAFE-0013-0001-0029-ABCDEFABCDEF';
53 rows.
We would investigate further, how this had happened. I suspect human error. Human error makes troubleshooting more interesting, just like human error makes chess more interesting. If it was a computer playing chess, it would not make any mistakes and win the game. It is the human errors which make chess and troubleshooting, an interesting pursuit.
Posts
