cancel
Showing results for 
Search instead for 
Did you mean: 

Lob segment Reorganization failure

Former Member
0 Kudos

Dear Experts,

Our Production Database(Oracle 11.2.0.3) is growing at a high rate.

We need to reorganize the highly growing tables. For a testing purpose we started in our test system.

During reorganization of SOFFCONT1 table(corresponding lob segment is SYS_LOB...7$$) in database, we get the below error:

We had tried increasing the PSAP<sid> tablespace by 5 GB but still its showing the same error.

Currently it is having 23 GB free space

Please suggest how do we go about it...

Regards

Imaan

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Please attach output of below commands (preferable as attached files):

brspace -f dbshow -c tbinfo -t SOFFCONT1

brspace -f dbshow -c tsinfo -s PSAPPRD

Former Member
0 Kudos

Dear Roman,

Please find the attached files with both outputs...

Regards

Imaan

Former Member
0 Kudos

You have only ~23GB of free space in tablespace, but at the same time ~69GB of LOB data in table SOFFCONT1. You need to add space to tablespace.

Former Member
0 Kudos

Dear Roman,

You are right, before Reorg to be done, the tablespace should contain minimum twice the size of table to be reorged.

As per link :

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/70cf010d-0b01-2d10-86a2-a0636c39a...

Thank you all for the guidance...

Answers (4)

Answers (4)

Former Member
0 Kudos

Hi,

  Please use the below command and reorg once again.

This should solve your issue.

brspace -u/-f tbreorg -t <table_name> -a cleanup

Regards

Yugandhar.

Former Member
0 Kudos

Dear Yugandhar,

You mean no need to add extra space in tablespace mentioned above and directly execute the above command.

Can you explain what this command will do...Will it provide the minimum space required for reorg which is twice the table size...

Thanks

Imaan

Reagan
Advisor
Advisor
0 Kudos

Hello

First update the BRTools if you are on a lower version or patch level.

Secondly refer to these SAP notes and delete the data from the table SOFFCONT1 which are no longer required before you do a table reorg.

1634908 - Reduce the size of table SOFFCONT1

966854 - Reorganization - New report

The best option would be to do an export and import of the table using BRTools.

Regards

RB

Former Member
0 Kudos

Dear Reagan,

Thanks for the information :

1-> Our brtools was recently upgraded to BRTOOLS 7.20 (33) with kernel 720 Ext.

2-> I have already executed the reorg reports as per SDN - http://scn.sap.com/thread/3178083

Our entries got reduced from 2,915,342 to 2,474,262.

Can you please let me know any notes for export & import of tables. Also please explain how this will help in reducing the data.

Regards

Imaan

Former Member
0 Kudos

Hi,

Take a look at the below blog:

http://scn.sap.com/docs/DOC-43948

Regards,

Nick Loy

former_member182657
Active Contributor
0 Kudos

Hi Imaan,

Please follow SAP Note   1431296 - LOB conversion and table compression with BRSPACE 7.20

Regards,

Gaurav

Former Member
0 Kudos

Thanks Gaurav,

Will have to go through the note in details as this is the first time we are doing a reorg..

Regards

Imaan

Former Member
0 Kudos

Hi,

What is your SBP version? There is known bug with brspace command (reorg).

Apply latest SBP and then retry.

We encountered this during our ACO enablement, we had to move this table to new table space without reorg to complete the activity.

Regards,

Nick Loy

Former Member
0 Kudos

Hi Nick,

It is

SBP 11.2.0.3.7 201309 containing CPUJul2013

Regards

Imaan

Former Member
0 Kudos

Hi,


It's a known bug hitting you now.

Try to reorg using -FPM option.

Regards,

Nick Loy

Former Member
0 Kudos

Hi Nick,

Please let me know if there is any note for this known issue...

Also, we are using reorg via brtools...so internal command executed is below: Can you let me know the menu path to execute with -FPM option

Inputs given by us is :

Regards

Imaan

Former Member
0 Kudos

Hi,

Unfortunately it is not reported in any of SAP notes (related ACO known issues).

There are solutions to apply:

1) Execute this and confirm the result:

brspace -u / -f tbreorg -a reorg -t SOFFCONT1 -ic sap_proc -l 2 -p 4 -e 16 -KST -FPM

2) Exp/Imp table using brspace

3) Update latest SBP and then do online reorg.

Regards,

Nick Loy

Former Member
0 Kudos

Hi Nick,

Tried with your options but same error...

ORA-01652: unable to extend temp segment by 8192 in tablespace PSAP<SID>

Regards

Imaan

Former Member
0 Kudos

Hi Imaan,

I am sure that same bug is hitting you, now you left with 2 solutions.

1) Update latest SBP and then try to reorg the SOFFCONT table

2) Exp/imp table using brsapce command

You must have got below errors in Oracle trace file.

ORA-03113 disconnection during reorganization of soffcont1 table

This error is typically linked to a segmentation fault on Oracle server side (ORA-07445 related to a memcpy routine) and it is resolved in latest SBP.

Regards,

Nick Loy

Former Member
0 Kudos

Hi Nick,


We had earlier tried with GLPCA table and it ran successfully.

But this table is currently very huge...We get below error

Thanks

Imaan

Former Member
0 Kudos

Hi Imaan,

As mentioned above, this bug is specific for SOFFCONT1 table.

I mentioned both the options that are available for you now, implement and update if any issue.

Regards,

Nick Loy