UPDATE:
I have come to discover through my import log that the table being refenced in the “failing SQL” does not exist.
CURSOR job_args IS SELECT a.job_name job_name, a.argument_position position, a.anydata_value value FROM SYSTEM.SCHEDULER_JOB_ARGS_TMP a where a.owner='appowner' and a.job_name='F1JOB_XXX';
There is a table in both my source (12c) and destination (19c) databases “SYSTEM.SCHEDULER_JOB_ARGS_TBL”, leading me to believe that this is a bug in the data pump (typo between TMP and TBL???).
Further, in my Internet search, I found an Oracle Doc (# 2216347.1) that references a patch (#34194445) has a very similar error, but references a different table name “SYSTEM.SCHEDULER_PROGRAM_ARGS_TMP”, the table name “SYSTEM.SCHEDULER_PROGRAM_ARGS_TBL” also exists in both databases.
Unfortunately, since I am licensed for Oracle through IFS, I do not have a support ID and am unable to download any patches.
If I correct the table name in the SQL scripts and re-run them, they succeed and the database processes get created, but they still need to have “arguments” and “attributes”, so I wonder if the failure during this phase of the import leads to other aspects of the import not processing.
If anyone has any experience with this issue, or if it seems like I am barking up the wrong tree, please drop me a line as I would love to chat about this process.
Regards,
Bob
Hi Bob,
We ran into the same error messages during our 12c to 19c upgrade process. I just recently provided the following information to a partner, so if you're the customer they were working with this is probably old information. But just in case you or someone else can use it...
I believe that in Oracle 19c the Scheduler Programs RUN_JOB and PROCESS_ALL_PENDING have been phased out and there’s some other way to do it. However I was never able to find that info. So I tried re-enabling the old way.
Below is a script I had to run manually in SQL Developer to create the RUN_JOB and PROCESS_ALL_PENDING programs. Run these two statements individually as app owner (IFSAPP in our case).
-- Create RUN_JOB
BEGIN
DBMS_SCHEDULER.create_program(
program_name => 'IFSAPP.RUN_JOB',
program_action => 'IFSAPP.BATCH_SYS.RUN_JOB__',
program_type => 'STORED_PROCEDURE',
--number_of_arguments => 3,
number_of_arguments => 2,
comments => 'Processes background jobs',
enabled => FALSE);
DBMS_SCHEDULER.define_program_argument(
program_name => 'IFSAPP.RUN_JOB',
argument_name => 'JOB_ID_',
argument_position => 1,
argument_type => 'NUMBER',
out_argument => FALSE);
DBMS_SCHEDULER.define_program_argument(
program_name => 'IFSAPP.RUN_JOB',
argument_name => 'ACTION_',
argument_position => 2,
argument_type => 'VARCHAR2',
out_argument => FALSE);
/* DBMS_SCHEDULER.define_program_argument(
program_name => 'IFSAPP.RUN_JOB',
argument_name => 'BROKEN_',
argument_position => 3,
argument_type => 'PL/SQL BOOLEAN',
out_argument => FALSE); */
DBMS_SCHEDULER.ENABLE(name=>'IFSAPP.RUN_JOB');
END;
-- Create PROCESS_ALL_PENDING
BEGIN
DBMS_SCHEDULER.create_program(
program_name => 'IFSAPP.PROCESS_ALL_PENDING',
program_action => 'IFSAPP.TRANSACTION_SYS.PROCESS_ALL_PENDING__',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 4,
comments => 'Process all waiting jobs in a spcecific queue',
enabled => FALSE);
DBMS_SCHEDULER.define_program_argument(
program_name => 'IFSAPP.PROCESS_ALL_PENDING',
argument_name => 'QUEUE_ID_',
argument_position => 1,
argument_type => 'NUMBER',
out_argument => FALSE);
DBMS_SCHEDULER.define_program_argument(
program_name => 'IFSAPP.PROCESS_ALL_PENDING',
argument_name => 'QUEUE_DESC_',
argument_position => 2,
argument_type => 'VARCHAR2',
out_argument => FALSE);
DBMS_SCHEDULER.define_program_argument(
program_name => 'IFSAPP.PROCESS_ALL_PENDING',
argument_name => 'LANG_CODE_',
argument_position => 3,
argument_type => 'VARCHAR2',
out_argument => FALSE);
DBMS_SCHEDULER.define_program_argument(
program_name => 'IFSAPP.PROCESS_ALL_PENDING',
argument_name => 'JOB_',
argument_position => 4,
argument_type => 'NUMBER',
out_argument => FALSE);
DBMS_SCHEDULER.ENABLE(name=>'IFSAPP.PROCESS_ALL_PENDING');
END;
I then had to recreate a number of jobs via SQL Developer to get them to show up in the database processes list. I can provide information on that process as well if it would be helpful.
Hope this helps.
Eric
Yes sir, I did get your information through our partner (thank you for that) but what I am still wondering, is there a way to correct the failures (either during the data pump export or the import), and not have to rebuild all database processes “post import”. This is why I referenced the Oracle document that refers to a patch, as the error described is very similar to the failed SQL referenced in our import log. I was able to correct the table name in the failed SQL and it succeeds, but what I cannot do is correct this error in the data pump. Not knowing much about the inner workings of expdp.exe or impdp.exe, I am not sure if this is correctable, unless the patch referenced will resolve this and point the SQL script to the appropriate table, (in the hope that it will also also bring in the correct arguments as your scripts layout. I have 16 database processes that are not coming across through the export/import process. This lead me to additional questions that I hope you can answer. Where did you find the information for each of your processes such as argument_name, argument_position,argument_type, out_argument; etc.? I have been searching for a way to pull these from the source database, but having no luck. If you are so inclined, I would like to meet about this entire process so that I can begin to moved forward again. Please private message me if you have the time for a short meeting.
Kind regards,
Bob
