Wednesday, November 26, 2008

10gR2 EMCA FAILS WITH ERROR ORA-06502

There is a bug during the create of the EM Repository.
When hostname exceeds 32 characters emca will fail with
ORA-06502
PL/SQL: numeric or value error: character string buffer too small

Verify if this is the case.

sqlplus / as sysdba
SQL> set line 200
SQL> select host_name,length(host_name),instance_name from v$instance where rownum=1;
SQL>exit;

If length hostname is more than 32 chars you hitting this bug.

In order to bypass this bug apply the following steps:

1) Drop the repository with emca
emca -deconfig dbcontrol db -repos drop

2) cd to $ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/self_monitor/

3) Make a copy of self_monitor_post_creation.sql
cp self_monitor_post_creation.sql self_monitor_post_creation.sql.bak

4) Edit the script and change the l_host_name from varchar2(32) to varchar2(128).
Note: The l_host_name variable is declared 2 times in the script.

5) Create repository with emca
emca -config dbcontrol db -repos create

And you are done.

1 comment:

  1. I ran into this issue today - Thanks for the solution!!! Worked great for us.

    ReplyDelete