Skip to main content
Solved

Problems upgrading IFS database from Oracle 12c to 19c

W
Sidekick (Customer)
Forum|alt.badge.img+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

Best answer by kwperichost

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

View original
Did this topic help you find an answer to your question?

8 replies

W
Sidekick (Customer)
Forum|alt.badge.img+9
  • woprhowe
  • Author
  • Sidekick (Customer)
  • 55 replies
  • March 28, 2024

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
Do Gooder (Customer)
Forum|alt.badge.img+2
  • kwperichost
  • Do Gooder (Customer)
  • 7 replies
  • Answer
  • April 2, 2024

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
Sidekick (Customer)
Forum|alt.badge.img+9
  • woprhowe
  • Author
  • Sidekick (Customer)
  • 55 replies
  • April 2, 2024

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
Do Gooder (Customer)
Forum|alt.badge.img+2
  • kwperichost
  • Do Gooder (Customer)
  • 7 replies
  • April 2, 2024

PM sent.

Rusiiiru
Sidekick (Partner)
Forum|alt.badge.img+9
  • Rusiiiru
  • Sidekick (Partner)
  • 46 replies
  • February 8, 2025

Hi ​@kwperichost 

 

Could you please share it with me too? or possible to post here

 

Best Regards,

Rusiru

https://www.linkedin.com/in/rusiruathukorala/
kwperichost
Do Gooder (Customer)
Forum|alt.badge.img+2
  • kwperichost
  • Do Gooder (Customer)
  • 7 replies
  • February 10, 2025

Hi Rusiru,

I’d be glad to share what I have done.  Do you mean just the data pump method / settings I used?  Or the entire process I used?

Eric

Rusiiiru
Sidekick (Partner)
Forum|alt.badge.img+9
  • Rusiiiru
  • Sidekick (Partner)
  • 46 replies
  • February 10, 2025

Hi ​@kwperichost 

I would like to know the entire process that you have used.

Best Regards,

Rusiru

https://www.linkedin.com/in/rusiruathukorala/
kwperichost
Do Gooder (Customer)
Forum|alt.badge.img+2
  • kwperichost
  • Do Gooder (Customer)
  • 7 replies
  • February 10, 2025

​@Rusiiiru,

OK, well let’s start with the data pump export and import process.  Please see attachment.

I’m also attaching a sample SQL script we used to recreate the “Batch_SYS.Process_Batch_Schedule_(0)” database process that was missing after the migration from 12c to 19c.

One of many missing db processes.

I used SQL Developer to see what the job looked like on the old database, which helped me figure out how to create it on the new database.

 

As mentioned in a previous post, I had to first recreate RUN_JOB and the PROCESS_ALL_PENDING (shown in above image in brackets).  Those statements are also included in the attachment.

Let me know what other questions you may have, although I may not be able to respond again until tomorrow.

Eric

Rusiiiru
1 person likes this
  • Rusiiiru
    Rusiiiru

Reply


Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings