Help required, LOB troubles

Toad for Oracle
1

Sorry to “spam” the group like this, but I need some advice.

Oracle Database 11.2.0.2 on SLES Linux 11.

I have a table with a LOB in it. The lob has a lob index (SYS_blah$$)
and is corrupted. The database is throwing ora-600 [something] and the
Ora-600 analyser at MOS tells me to “drop and rebuild the affected
indexes” which are identified by an error from “analyze index xxxxx
validate structure”. This I have done.

But, I have two corrupt indexes, one small (56 Gb) and one a tad larger

  • 1.5 Tb. (Yes, Tb!) And I need to rebuild them.

Of course, you can’t rebuild a lob index of this kind, so the solution
is to move the lob segment out to a new tablespace and then, back again.
As follows:

ALTER TABLE big_reports
MOVE LOB (REPORTDATA)
STORE AS (TABLESPACE norman);

ALTER TABLE big_reports
MOVE LOB (REPORTDATA)
STORE AS (TABLESPACE lob01);

So, I managed to do this on the smaller index, all 56Gb of it. It took
about an hour to move out and back again. In between moves, I
revalidated the structure, and thankfully, all was well. It bodes well
for the biggie!

Unfortunately, I do not have at the moment, anywhere near enough free
space to hold a 1.5 Tb index. The server doesn’t either, so I can’t
steal any space to build a tablespace.

Given that I have got a rebuild rate of about 100 Gb per hour, I’m
looking at 16 hours of rebuilding time - unless I only move it once,
where I guess it will be about 8 hours. Anyway, users can’t use the
database during this time.

I’m about to leave for home now, I’ve been here 12 hours already today,
as no-one in the disc department is around to offer me oodles of space
to build a tablespace in. However, as you nice people in the US and
places westward of where I am are just (!) coming online, I was hoping
that you could have a think and see if you know of any new features in
11.2 that I haven’t found yet in the LOB INDEX rebuilding department.

Other information - the database is not using ASSM so
SECUREFILES/FASTFILES is a no-no, shame. We are stuck with BASICFILES
(or slow files?) I’m afraid.

The tablespaces in question, amongst others, were imported from a 9i
Standard Edition database recently using Transportable Tablespaces which
were converted using the following circuitous route:

  • Clone 9i database & upgrade to 10g Enterprise Edition. (On HPUX 64 bit
    server)

  • Export tablespaces as transportable (what a wonderful tool - 1.5 TB
    imports in 5 mins!) - did all the stuff with COMPATIBLE and READ ONLY
    etc.

  • Transport files on tape, to new data centre.

  • Loaded onto disc and used 11.2 RMAN to CONVERT DATAFILE from HPUX 64
    to Linux 64.

  • Imported the converted files successfully. (As mentioned above, in 5
    mins!)

The system has been running overnight and all today since then with no
errors.

I’m resigned to the long and tortuous “rebuild” process of moving
between tablespaces and back again, but I’m hoping someone has tried and
tested methods I don’t know about.

Cheers,
Norm. [TeamSlightlyUpTheCreek]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

2

I’m assuming you realize that you can do the “alter table … move lob …
store as (tablespace xxx)” in the same tablespace as the original so you only
have to do the move once. Doesn’t help you get the space, but it could cut down
on the time.

Since Oracle isn’t very good at recovering space in LOBs when rows are deleted
or their LOBs are nulled out (with BASICFILES, anyway). So it may be that you’re
actually using a lot less than that 1.5TB – the rest is older copies of the lob
data. You may need much less than the 1.5TB to do the rebuild. I had one like
that where the before was 34GB and the after was 8GB.

I wouldn’t count on it, though. You might be able to estimate real size by
summing the total length of the lob columns and multiplying by 3 or so to
include the indexing. Haven’t tried.

None of which helps very much. And copying pieces of the data or exporting with
DP probably won’t work because of the corruption. I’m assuming the data didn’t
remain static since the last useful backup. I think you may need all the extra
space, temporarily.

Our LOBs are much smaller, but I’ve put in a monthly job to look at available
space and “move” the LOB segments in their existing tablespaces (or notify me if
space is getting low), just to reclaim space.

– jim

3

Hi James,

I'm assuming you realize that you can do the "alter table .... move lob
... store as (tablespace xxx)" in the same tablespace as the original so
you only have to do the move once. Doesn't help you get the space, but
it could cut down on the time.
Yes indeed, I am aware, but thanks anyway. My problem is that I have
tried that in the past and it didn't seem to rebuild. I know it will
double the time taken to move it out and back, but at least I know
that it will actually rebuild.

I've looked at the stats on this lobindex and it seems to be only using
53% of the reported space - so, only 860 GB instead of 1.6 Tb (It's
bigger than at first thought). Still pretty big though.

Since Oracle isn't very good at recovering space in LOBs when rows are
deleted or their LOBs are nulled out (with BASICFILES, anyway). So it
may be that you're actually using a lot less than that 1.5TB
See above! :wink:

I wouldn't count on it, though. You might be able to estimate real size
by summing the total length of the lob columns and multiplying by 3 or
so to include the indexing. Haven't tried.
analyze index validate structure;
select name, btree_space, used_space pct_used from index_stats;

Seems to work reliably.

Thanks for your input, much appreciated.

--
Cheers,
Norm. [TeamT]

4

Comma missing, changes everything!

select name, btree_space, used_space pct_used from index_stats;

It should be:

select name, btree_space, used_space, pct_used from index_stats;

Comma added between used_space and pct_used. :frowning:

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

5

Hi,

You might want to post your question to

comp.databases.oracle.server

There are some heavy duty DBA-s there eager to answer difficult problems
which are properly described.

K

6

Here is an example:

begin
recon.pk_idw_recon.pr_recon_results(p_run_id => :p_run_id,
p_summary_report_sql => :p_summary_report_sql);
end;

the first variable is IN, the second is OUT. I would like to view the result in SE. Is it possible?
PL/SQL developer has this capability.

I use TOAD version 10.6.1.3

Many thanks,
George