There is a script named “dbmspool.sql” located in %ORACLE_HOME%/RDBMS/ADMIN that you need to run under the sys account (as sysdba). This script defines the “dbms_shared_pool” package and after running this script the various Agile packages will have access to that Oracle package.

Hi Kevin,

Thanks for your reply, I will try and update.

Thanks

Hi Kevin,

After running the script dbmspool.sql, I am able to remove one error out of 4 , but still another 3 packages showing errors, could you please help.
below packages shows errors, please check screen shot, your help is much appreciated.

AGILE9_UTILS
AGILE_SERVER_BOM_EXPANSION
AGILE_SERVER_BOM_ITERATION

Thanks

When you import a new database, the usual procedure is to drop the old user and create a new one. As part of the script to do that, there is the following :
declare
type_exists number;
sql_command varchar2(200);
begin
select count(1) into type_exists from user_objects where object_name = ‘T_NUMBERS’ and object_type=’TYPE’;
if (type_exists = 0) then
sql_command := ‘create type t_numbers as table of number(10)’;
execute immediate sql_command;
end if;
end;
/

grant all on t_numbers to agile;

 For whatever reason, this is missing, Run all of the above SQL in the sys account, and the error concerning T_NUMBERS will go away. All of this can be found in the USERAGILE.SQL script that is located in the D:oracle12adminagile9createagile directory where the RECREATEAGILE.BAT file is located.

Hi Kevin,
 
After resolving package errors, I am getting error ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE, when I check DBA_INDEXES some of the DOMIDX_OPSTATUS shows invalid, please see the screenshot for better understanding.

Thanks

There is a script to drop and recreate the various CTX (full text search) indexes in the database. it is named “agile9_ctx_recreate.sql”. It is located in the same directory as the “recreateagile.bat” script. Note that you should probably run the “agile9_fts_prefs.sql” and “agile9_fts_prefs_lexer_basic.sql” scripts in the CTX schema first (per what is done in the “recreateagile.bat” script).
 It should be able to get the “failed” indexes removed and then recreate them correctly. Try and run that script in the agile schema, and if things are no good, then make sure to run the 2 “fts_prefs” script first and to then run the drop command for each index first and make sure it gets drops. And of course, make sure the the CTX schema exists in the database instance.

Hi Kevin,

Thanks for update, oracle team has suggested below solution, is this needed?

If they say to do it, do it.    Did you even have the CTX schema created in your database instance??
 I am not specifically familiar with what they are asking you to do, but if those files are in fact not on your server where Oracle is installed, then they most certainly should be there. I suspect that  they have something to do with character sets, but other than that, no idea.

Hi Kevin,

Thanks for your update, I have CTXSYS schema in DB, but the issue is in CTXSYS schema, all these issues started when we imported latest data base file demp agile9_imp into our Agile schema earlier, with your help all those package errors were gone, but after that I see below errors in CTXSYS packages, the issue “oracle looping chain of synonyms” error, not sure what to do.
 

Thanks

Well, I have never seen that issue either. Contact Oracle about that one, but it looks like you have some synonyms that have multiple declarations that are different. Oracle would be much better at sorting that out than I would.