Solved

Problems upgrading IFS database from Oracle 12c to 19c

  • 22 March 2024
  • 4 replies
  • 109 views
W
Rank
Userlevel 4
Badge +9

I have been working for several months to upgrade our databases from 12c to 19c, but I am stuck on a particular issue that I cannot seem to resolve. I chose the manual method (expdp/impdp) as this is what is endorsed by IFS and recommended by the consultants. We get 2500+ errors during the import, most of which, the consultants stated can be ignored as they are 12c objects that are no longer in 19c, however, there are a handful of errors that reference our app owner and I feel they are related:

Error during import:

dbms_scheduler.enable('"PROCESS_ALL_PENDING"');COMMIT; END;
ORA-39083: Object type PROCOBJ:""."RUN_JOB" failed to create with error:
ORA-06550: line 8, column 265:
PL/SQL: ORA-00942: table or view does not exist

Upon completion of the import phase, then installation of IFS, Batch Queues cannot be “Reinitialized” as they produce the error:

"appowner". "RUN_JOB" does not exist

Database processes list is all but empty and if you try to "Reactivate" the ones the do exist, same error:

"appowner". "RUN_JOB" does not exist

 

Is there anyone out in the community who has run into this issue? If not, has anyone used the manual upgrade path successfully who might be able to share a brief outline of the steps?

 

Regards,

Bob

icon

Best answer by kwperichost 2 April 2024, 16:11

View original

4 replies

W
Rank
Userlevel 4
Badge +9

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
  

kwperichost
Rank
Badge +1

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

W
Rank
Userlevel 4
Badge +9

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

kwperichost
Rank
Badge +1

PM sent.

Reply


Terms & ConditionsCookie settings