Today when I was trying to re-create the em repository after 10.2.0.2 to 10.2.0.4 upgrade with the command “emca -deconfig dbcontrol db -repos create” it failed with error stack below.
Jan 19, 2009 5:11:53 PM oracle.sysman.emcp.EMReposConfig invoke SEVERE: Error creating the repository
Jan 19, 2009 5:11:53 PM oracle.sysman.emcp.EMReposConfig invoke INFO: Refer to the log file at /oracle/product/ora10204/cfgtoollogs/emca/*****\emca_repos_create_<date>.log for more details.
Jan 19, 2009 5:11:53 PM oracle.sysman.emcp.EMConfig perform SEVERE: Error creating the repository
Then I check the error log, and I found the stack below
Create SYSMAN user.
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist
ORA-06512: at line 11
While it creates the sysman user it cannot find a procedure and fails. To find out which procedure it was looking for I applied metalink and quick Search on metalink took me the note ID 331938.1
The note says problem is caused by the missing procedure dbms_shared_pool (no idea why it was missing on a brand new 10.2.0.2 db)
To solve the problem
1- Create the package
2- Give permission to package
grant execute on dbms_shared_pool to sysman; grant execute on dbms_shared_pool to dba;
3- follow the re-creation steps from the beginning including dropping sysman role and droppping the existing configurations
4- re run the command “emca -config dbcontrol db -repos create”
worked like charm on my case.