Shrink Lob Segments schedule job warning

Hi

Which UPD of IFS10 do You have and Oracle software version?

Hi @knepiosko ,

we have 19c oracle DB and ifs applications 10 update 10

Hi @knepiosko ,

above error seems to be resolve now ,now i got below error .do you what can be a reason for this? 

Thank You,

sara

Hi! I believe it is the other problem ‘unable to extend segment…. in tablespace IFSAPP_LOB that are causing this, which could be due to the ‘CONS’ section below.

i.e when shrinking the space it needs additional temporary space, so you may actually need to extend IFSAPP_LOB before you can shrink the objects inside it. 

from Oracle Doc ID 1451124.1

Regards

/Martin

Hi @Martin Larsson ,

I already added additional datafiles to IFSAPP_LOB ,see below image .We have more space in below datafiles ,but still shrink lob goes warning status

in alert log i can see below messages

2023-09-03T23:24:26.324448+02:00
ARC1 (PID:9021): Archived Log entry 102862 added for T-1.S-103069 ID 0xce9cf19f LAD:1
2023-09-03T23:24:55.756360+02:00
ORA-1691: unable to extend lobsegment IFSAPP.SYS_LOB0000347725C00013$$ by 1024 in tablespace IFSAPP_LOB (ospid 25663)
ORA-1691: unable to extend lobsegment IFSAPP.SYS_LOB0000347725C00013$$ by 1024 in tablespace IFSAPP_LOB (ospid 25663)
ORA-1691: unable to extend lobsegment IFSAPP.SYS_LOB0000347725C00013$$ by 1024 in tablespace IFSAPP_LOB (ospid 25663)
ORA-1691: unable to extend lobsegment IFSAPP.SYS_LOB0000347725C00013$$ by 1024 in tablespace IFSAPP_LOB (ospid 25663)
2023-09-03T23:25:20.153255+02:00
On demand 
MV IFSAPP.SHRINK_LOB_SEGMENTS_TAB was not refreshed successfully.
Number of MV refresh failures: 1.
Encountered error ORA-12008.
2023-09-03T23:25:28.067907+02:00

don't know why this issue occur. any ideas?🙂

Thank You

Sara

Hi! Can you look at your background job, and use RMB ‘Show Details..’
Then you will see the log of the which LOBs it tries to shrink.
Can you get information from there which LOB it fails on. Last line I assume.
You will also see actual values for ‘Allocated Lob Size’ and ‘Physical Lob Size’ for that LOB.
Would be interesting to see these values and compare to what is available in IFSAPP_LOB. 

Regards
/Martin 

Hi @Martin Larsson ,

here are details , highlight the jobs which status =  warning

!IFS.COPYOBJECT
$LU=DeferredJobStatus
$VIEW=DEFERRED_JOB_STATUS
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=1
-$2:TEXT=Trying to shrink the lob FILE_DATA in table EDM_FILE_STORAGE_TAB. 2023-09-02 10.00.54 PM
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=2
-$2:TEXT=   Allocated Lob size: 27698.19 Mb Physical Lob Size: 25470.41 Mb.
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=3
-$2:TEXT=   Allocated Lob size when done: 28272.19 Mb.
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=4
-$2:TEXT=Trying to shrink the lob MESSAGE_TEXT in table FNDCN_MESSAGE_BODY_TAB. 2023-09-02 10.14.41 PM
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=5
-$2:TEXT=   Allocated Lob size: 297235.13 Mb Physical Lob Size: 144082.91 Mb.
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=6
-$2:TEXT=Shrinking of lob segment MESSAGE_TEXT in table FNDCN_MESSAGE_BODY_TAB failed, due to Oracle error ORA-12008: error in materialized view or zonemap refresh path
--ORA-01691: unable to extend lob segment IFSAPP.SYS_LOB0000347073C00013$$ by 1024 in tablespace IFSAPP_LOB
--ORA-06512: at "SYS.DBMS_REDEFINITION", line 113
--ORA-06512: at "SYS.DBMS_REDEFINITION", line 4363
--ORA-06512: at "SYS.DBMS_REDEFINITION", line 5753
--ORA-06512: at line 1
-- ORA-06512: at "SYS.DBMS_REDEFINITION", line 113
--ORA-06512: at "SYS.DBMS_REDEFINITION", line 4363
--ORA-06512: at "SYS.DBMS_REDEFINITION", line 5753
--ORA-06512: at line 1
--ORA-06512: at "IFSAPP.DATABASE_SYS", line 3787
--..
-$3:KEY_VALUE=
-$4:STATUS_TYPE=WARNING
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=7
-$2:TEXT=Trying to shrink the lob MESSAGE_VALUE in table FNDCN_MESSAGE_BODY_TAB. 2023-09-02 11.07.06 PM
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=8
-$2:TEXT=   Allocated Lob size: 30481.19 Mb Physical Lob Size: 24539.36 Mb.
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=9
-$2:TEXT=Shrinking of lob segment MESSAGE_VALUE in table FNDCN_MESSAGE_BODY_TAB failed, due to Oracle error ORA-12008: error in materialized view or zonemap refresh path
--ORA-01691: unable to extend lob segment IFSAPP.SYS_LOB0000347084C00013$$ by 1024 in tablespace IFSAPP_LOB
--ORA-06512: at "SYS.DBMS_REDEFINITION", line 113
--ORA-06512: at "SYS.DBMS_REDEFINITION", line 4363
--ORA-06512: at "SYS.DBMS_REDEFINITION", line 5753
--ORA-06512: at line 1
-- ORA-06512: at "SYS.DBMS_REDEFINITION", line 113
--ORA-06512: at "SYS.DBMS_REDEFINITION", line 4363
--ORA-06512: at "SYS.DBMS_REDEFINITION", line 5753
--ORA-06512: at line 1
--ORA-06512: at "IFSAPP.DATABASE_SYS", line 3787
--..
-$3:KEY_VALUE=
-$4:STATUS_TYPE=WARNING
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=10
-$2:TEXT=Trying to shrink the lob TEMPLATE in table FND_MODEL_DESIGN_TAB. 2023-09-02 11.58.16 PM
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=11
-$2:TEXT=   Allocated Lob size: 1440.19 Mb Physical Lob Size: 599.53 Mb.
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=12
-$2:TEXT=   Allocated Lob size when done: 1408.19 Mb.
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=13
-$2:TEXT=Trying to shrink the lob PDF in table PDF_ARCHIVE_TAB. 2023-09-02 11.59.03 PM
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=14
-$2:TEXT=   Allocated Lob size: 6714.13 Mb Physical Lob Size: 6014.46 Mb.
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=15
-$2:TEXT=   Allocated Lob size when done: 6741.19 Mb.
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=16
-$2:TEXT=Trying to shrink the lob ATTACHMENT in table RECEIVE_EINVOICE_ATTACH_TAB. 2023-09-03 12.04.50 AM
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=17
-$2:TEXT=   Allocated Lob size: 22656.19 Mb Physical Lob Size: 20761.6 Mb.
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=18
-$2:TEXT=   Allocated Lob size when done: 22622.19 Mb.
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=19
-$2:TEXT=Trying to shrink the lob ATTACHMENT in table SEND_EINVOICE_ATTACH_TAB. 2023-09-03 12.14.46 AM
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=20
-$2:TEXT=   Allocated Lob size: 5025.19 Mb Physical Lob Size: 4524.47 Mb.
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=21
-$2:TEXT=   Allocated Lob size when done: 5075.13 Mb.
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=22
-$2:TEXT=Trying to shrink the lob XML_HEADER in table XML_REPORT_ARCHIVE_TAB. 2023-09-03 12.16.44 AM
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=23
-$2:TEXT=   Allocated Lob size: 4563.19 Mb Physical Lob Size: 2039.68 Mb.
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=24
-$2:TEXT=   Allocated Lob size when done: 4490.13 Mb.
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=25
-$2:TEXT=Trying to shrink the lob DATA in table XML_REPORT_DATA_TAB. 2023-09-03 12.19.23 AM
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-
$RECORD=!
-$0:JOB_ID=109962904
-$1:LINE=26
-$2:TEXT=   Allocated Lob size: 3364.19 Mb Physical Lob Size: 1459.67 Mb.
-$3:KEY_VALUE=
-$4:STATUS_TYPE=INFO
-

so you mean compare it with available data?

Thank you

Sara

Hi!

Maybe because  MESSAGE_TEXT in table FNDCN_MESSAGE_BODY_TAB has allocated about 140GB.
and if I calculated correctly you have about 100GB free space in IFSAPP_LOB. 
Not sure exactly how much more is needed in IFSAPP_LOB to able to handle this quite big shrink.

Are you running the cleanup jobs for finished application messages. There is a scheduled task for that.
I was thinking whether we first could try to cleanup and after that do the shrink when there is less data to handle. Maybe that would work better?

If not I assume we need to add additional IFSAPP_LOB tablespace or consider other ways of shrinking the LOB.  

Hi @Martin Larsson ,

Yes we have clean up job ,may be i can add additional Datafile to IFSAPP_LOB .otherwise we have to look in to another way.

Thank You 

sara

Hi

Normally I schedule task to purge all messages(%) from any(Any) queues older than 360 days runing every day. This limits size of tables FNDCN_APPLICATION_MESSAGE_TAB and FNDCN_MESSAGE_BODY_TAB significantly.

Hi @knepiosko ,

Thank You for your reply, how can I setup setup that, is it setup from system parameter's or schedule database task window? can you send image of your task?

Thank You

Sara

Here is it:

HI @Martin Larsson@knepiosko ,

as you mentioned setup ‘cleanup of finished messages’ job and it runs everyday. but when i run shrink_lob_segments  job in non peak time it still gives above errors.so i disabled it, now i can see IFSAPP_LOB tablespace is growing hugely, i am adding 1 datafile (32GB) daily to IFSAPP_LOB tablespace.I have some questions

1. i already setup ‘cleanup of finished messages’ job and it’s running daily  but still why IFSAPP_LOB datafiles not clean .As i understand if we run this job it will delete old message values in ‘FNDCN_MESSAGE_BODY_TAB’  . but ,it keep same size and growing  fastly (add 1 datafile to IFSAPP_LOB tablespace daily).I can see these are the tables which keep lot of data in IFSAPP_LOB datafiles.

2. 

   What are other methods of shrink because i got above ‘warning’ error every time i run shrink job

Thank You

Supuni

Hello @sara19 ,

There is a separate database task used in IFS which is called “Shrink Lob Segments” to handle large sized LOBs. Normally we are not recommended to run the Shrink Lob Segments in app 10.  But, from Oracle 12C onwards, there’s a mechanism called “Oracle Advanced Compression” is available to handle large sized objects in a Database. 

Further, if you are using Oracle Enterprise Edition, you can enable "Advanced Compression" which will reclaim the space and will most certainly also improve the performance automatically via Oracle.

Also, you can refer the explanation given by Oracle:  https://oracle-base.com/articles/misc/alter-table-shrink-space-online

Can you disable the "Shrink Lob Segments" job and enable Advanced Compression mechanism in Oracle. It'll resolve your issue.

For more details refer bellow note as well.

We were able to identify why the "Shrink Lob Segments" job fails for table 'FNDCN_MESSAGE_BODY_TAB.

In IFS Applications 10 any LOBs are stored as Oracle "SecureFile" LOBs. The standard "ALTER TABLE tablename SHRINK SPACE" command that was used in Apps75 does not work for Secure File LOBS. IFS therefore has implemented shrinking of the LOB segments with "Oracle Redefinition" which is the only way to perform the Shrinking online without making the table unavailable.

The problem with Oracle Redefinition is that temporarily the disk space requirement nearly doubles (original table size + new shrunk table size). Unfortunately, there is nothing that IFS can do to remove this requirement.

It seems that you currently do not have enough disk space for the IFSAPP_LOB tablespace to allow shrinking the FNDCN_MESSAGE_BODY_TAB as the error that is received during the shrinking is the following:

ORA-01691: unable to extend lob segment IFSAPP.SYS_LOB0000347073C00013$$ by 1024 in tablespace IFSAPP_LOB

Hope this will resolve your issue. 

Thank you,

Salinda