Solved

Shrink Lob Segments schedule job warning

  • 11 August 2023
  • 15 replies
  • 650 views
S
Rank
Badge +5

Hi all,

we setup shrink lob segment schedule job and it was running well.

but now it goes to warning stage.i can see below error in background jobs.what can i do to solve this error?

 

Shrinking of lob segment FILE_DATA in table EDM_FILE_STORAGE_TAB failed, due to Oracle error ORA-06550: line 1, column 75:
PLS-00201: identifier 'DBMS_REDEFINITION' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
 ORA-06512: at "IFSAPP.DATABASE_SYS", line 3783

 here is schedule job 

Thank you

 

icon

Best answer by SALJLK 4 October 2023, 10:10

View original

15 replies

K
Rank
Userlevel 5
Badge +15

Hi

 

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

Piotr --> https://www.linkedin.com/in/piotr-skowronek-8425aa6/
S
Rank
Badge +5

Hi @knepiosko ,

 

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

L
Rank
Badge +8

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

M
Rank
Userlevel 2
Badge +5

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

L
Rank
Badge +8

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

M
Rank
Userlevel 2
Badge +5

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 

L
Rank
Badge +8

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

M
Rank
Userlevel 2
Badge +5

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.  

L
Rank
Badge +8

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

K
Rank
Userlevel 5
Badge +15

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.

Piotr --> https://www.linkedin.com/in/piotr-skowronek-8425aa6/
L
Rank
Badge +8

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

K
Rank
Userlevel 5
Badge +15

Here is it:

 

Piotr --> https://www.linkedin.com/in/piotr-skowronek-8425aa6/
S
Rank
Badge +5

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

SALJLK
Rank
Userlevel 2
Badge +5

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

~ Salinda ~
SALJLK
Rank
Userlevel 2
Badge +5

Hi @sara19 ,

 

When analyzing the logs, the reason for this there is not enough tablespace for IFS_LOB. At least make it 100GB. When we are analyzing the error log, we can see bellow error here.

 

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

 

In this case of an ORA-01691 error, the solution is to identify the name of the tablespace associated with the LOB segment and add space to the tablespace. Please check it with your DBA and increase the enough tablespace for this. 1024 is not enough for this.

 

We have suggested bellow options,

 

  1. You can enable “Oracle Advanced Compression” from oracle.
  2. You can enable "Shrink Lob Segments". You already did it. Please follow my above note and increase the table space and try it. Remember that, if you run this job for the first time, it will take a large amount of time (May be about two days) to complete.

 

When you schedule the “Shrink LOB Segments” database task to be run, please set it in a non-peak time when the users are not using the system. 

This job can be setup to run weekly or monthly according to the size of the data amount handled in your system. Hope this information helps.

 

I hope this will be resolve your issue.

Thank you,

Salinda

~ Salinda ~

Reply


Terms & ConditionsCookie settings