Automic Workload Automation

  • Private Community

  • 1.  UC_REORG Procedure

    Posted Jul 07, 2017 05:00 AM
    Hello,

    Our DB team tried to run manually updated UC_REORG procedure

    They've got this error. Please advise

    SQL> exec GAEP.UC_REORG;
    BEGIN GAEP.UC_REORG; END;

          *
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'UC_REORG'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored 


  • 2.  UC_REORG Procedure

    Posted Jul 07, 2017 06:54 AM
    Hello Krum Ganev,

    you shouldn't run the procedure manually. Instead automic recommends using the ucybdbre tool or partitioning with ILM.

    Matthias


  • 3.  UC_REORG Procedure

    Posted Jan 05, 2018 12:34 PM
    How to execute the Oracle procedure 'UC_REORG' please check out following post on our community 'How to schedule the UC_REORG procedure in database'


  • 4.  Re: UC_REORG Procedure

    Posted Jan 04, 2019 06:32 AM

    Hi Andi,

     

    The link you have posted is wrong. It redirctes you to a page that has nothing to do with UC_REORG.

     

    Best regards

    Keld.



  • 5.  UC_REORG Procedure

    Posted Jan 05, 2018 12:43 PM
    MatthiasSchelp - I believe the UC_REORG is different from the ucybrbre tool.

    The UC_REORG should only apply to Oracle and the MQ tables if I recall correctly 


  • 6.  UC_REORG Procedure

    Posted Jan 05, 2018 12:48 PM
    Michael_Pirson absolutely correct. Therefore check out the post which I mentioned.

    Personally I prefer to reorganize a database manually and execute a database statement one after another. Than it's fully under control if something goes wrong.


  • 7.  UC_REORG Procedure

    Posted Jan 09, 2018 03:51 AM
    Hi folks,

    Sorry for the confusion. I didn't remember that correctly. You're absolutely right. UC_REORG does get triggered via MQ_CHECK_TIME in UC_SYSTEM_SETTINGS.

    Krum_Ganev
    I would still suggest not to run it manually. The procedure is more of a "general pupose" function. That means it is not restricted to MQ tables by default. Therefore it does take the tablename as a parameter (and thats also what your error message says). The correct statement should be this ...

    exec GAEP.UC_REORG('<table_name_here>');

    Matthias




  • 8.  UC_REORG Procedure

    Posted Jan 13, 2018 02:23 AM
    Hello All and thanks for your inputs.
    We are using AE v11.2.2hf+5 on Linux with Oracle DB on AIX .
    Back in July we experienced System Freeze. After an incident to Automic Vendor (    INC00135416) they advised us to update the UC_REORG procedure and alternate the values for MQ_CHECK_TIME and MQ_BLOCK_SIZE.

    The solution by the Support 

    To prevent WPs freezing you need to disable the MQ* reorg from being triggered automatically by AE, go to Client 0 > UC_SYSTEM_SETTINGS > disable the MQ_CHECK_TIME by setting the value to 0. 

    UC_SYSTEM_SETTINGS: 
    MQ_CHECK_TIME set the value to 0 

    Have the DBA implement the attached UC_REORG stored procedure. 
    Run this outside of Automic, on the DB side, in intervals during off-peak hours that would not interfere with jobs in Production. 

    The stored procedure may not be seen from the Automation Engine side. 

    You will need to connect to Oracle via a separate client - e.g. SQL developer to view itThe stored procedure may not be seen from the Automation Engine side. You will need to connect to Oracle via a separate client - e.g. SQL developer to view it. 

    ORA-10634: Segment is already being shrunk 
    Cause: Only one invocation of shrink can be in progress on a segment at any time 
    Action: Reissue the command after the first shrink is over 

    To avoid an ORA-10634 the UC_REORG has been improved.

    Due that reason we've tried to run the UC_REORG procedure manually.
    Currently we have the following settings : 
    MQ_BLOCK_COUNT - 256
    MQ_CHECK_TIME - 6000

    We are still experiencing WP freezes but according last few open tickets they are due known bug with Agent Host Groups.
    I am still wondering if we need to increase the numbers even more.
    We have 15 WP(13WP, 1DWP, 1JWP) per node (2 node system)
    We have around 400 000 job executions daily and the system is still growing.



  • 9.  UC_REORG Procedure

    Posted Jan 15, 2018 10:09 AM
    Hi Krum Ganev,

    We've had that same issue last summer (Known error 00008839 if i'm correct) and replaced the UC_REORG procedure with one that Antoine Sauteron gave us, but left MQ_CHECK_TIME and MQ_BLOCK_COUNT untouched. So it still get's triggered automatically. So far we didn't have any issues. As this only happened once to us this may or may not tell the problem is gone. You can also disable the auto trigger via MQ_BLOCK_COUNT=0 and run the procedure manualy (see my corrected 'exec' statement above).

    Our MQ_CHECK_TIME is 3600 and MQ_BLOCK_COUNT is not set, so its 64 by default. If you raise the values the issue only get's more unlikely but isn't gone.

    Regards, Matthias

    [Edit: Forgot to mention we have a V.10 so this may not apply to your V.11]


  • 10.  Re: UC_REORG Procedure

    Posted Jan 14, 2019 06:03 AM

    Hi,

    If you run the procedure from an SQL job in the Automation Engine like this (Form tab of the SQL job):

     

    begin
       DBMS_OUTPUT.ENABLE(1000000);
       uc4admin.uc_reorg;
    end;

     

    you end up with the same error as Krum_Ganev has reported, so obviously you need to supply some parameter. If you add the table name like this:

     

    begin
       DBMS_OUTPUT.ENABLE(1000000);
       uc4admin.uc_reorg('MQ1RWP');
    end;

     

    the SQL procedure runs fine.

     

    Now, according to Support, UC_REORG is only supposed/designed to reorganize the MQ tables and is triggered when you set MQ_CHECK_TIME to any other value than '0'.

    Is it possible to reorganize other tables than MQ* with the UC_REORG procedure? will it work ?

     

    /Keld.