reorganization of tsp with datafiles with brtools

[Former Member]

Some suggestion from the guru please.

I have an oracle 10g Sap database where I should reorganize a full tablespace, we can call it tsp_old.

I read already note 646681 .

I try to run the online reorganization from tsp_old to tsp_new according to note note 646681 point II, but the performances are very bad; neverthless some table has been already moved to tsp_new.

As this is a test system I can do all the reorganizazion with Sap shutdowm.

I do not want to do the LONG to LOB conversion.

The performances with online reorganization are very bad, almost one day for each table. The size of the tsp_old is several hundreds of GB and I cannot loose other time.

So I'm thinking to kill the running online reorganization, and to run the following command (as for note 737598) :

brspace -u / -f tbreorg -t "*" -a cleanup

in order to cleanup all the temporary objects/materialized views created during the online reorganization.

Then I have to choices to complete the reorganization:

1) perform an online reorganization from tsp_new to tsp_old to move back all the already move tables, and then perform a full export, drop the tsp_old and then to create it again and performing the import into tsp_old

2) or, preferred, to run the export of the remaining tables presents into tsp_old, and import them it into the tsp_new alredy existent.

The tsp_new was created as for note 646681 , so it has already all the table definitions.

Then I will do:

drop the tsp_old

rename tsp_new as tsp_old

I need to clarify if with the export/import method all the LONG tables will be managed correctly and if there is some particular action to set into the brtools for them.

regards

[former_member204746 · Accepted solution]

what is the size of your tablespace to reorg?

I reorg'ed PSAPBTABD which was 320GB and PSABTABI which was 280GB. and it took roughly 30 hours total... and this was on 32-bit hardware on Windows 2003 and Oracle 9i.

I had to reorg a few tables offline, this took roughly an hour.

The worse case I had to rerog was tablespace PSAPCLUD. it contains mostly tables with LONG RAW with a total of 45GB... it took 8 hours to export/import.

provide a bit more info about your setup.

Make sure that PSAPUNDO and PSAPTEMP are big enough.

[Former Member · Accepted solution]

tsp_old = PSAPBTADB = 700 GB

I discovered in this tsp a very old "orphan incarnation" that is blocking my test upgrade. As for note 19519 it seems the only way to clean it is to reorganize it.

It's not a problem to spend some day to do the reorg, fortunally I do not have time constrains.

The hardware is 4cpu 32 bit Windows, quite faster as I see (online reorg a part)

My dubts are:

_ exp utility will manage the LONG row correctly ?

_ exp utility will download completely all the objects from the tsp_old ?

_ imp utility will allow me to import the dump coming from tsp_old into tsp_new ?

Any advise ?

regards

[former_member204746 · Accepted solution]

_ exp utility will manage the LONG row correctly ?

yes, no problem. if you can, you can use EXPDP and IMPDP which is usually a bit faster than EXP/IMP.

_ exp utility will download completely all the objects from the tsp_old ?

yes.

_ imp utility will allow me to import the dump coming from tsp_old into tsp_new ?

yes. SAP note 646681 explains the process

This being said, are you planing or reorg'ing this tablespace often? If so, long2lob conversion is a wise choice. And good this about it, the conversion can be run online! yes online, even for the conversion! Are you afraid about that 30% decrease of performance on this type of table? I would like you to know that all new SAP installations use LOBs instead of LONG RAW.

[Former Member · Accepted solution]

thnks for the feedback.

As it will be the first time for me witg brtools I would like to ask you some detail.

First, I read note 646681.

What I'm trying to do:

I have a tsp_old with normal tables and tables with Long fields.

The tsp_new exist and contains some normal tables already migrated from tsp_old with the tbreorg (offline method as for note 1080376).

Note 646681 at point "4) Creating DDL statements for tables with LONG fields" says to run some commad specific for tables with LONG field.

I would like to clarify:

a) if I need to run this command, or more simply go ahead exporting all the tables still in tsp_old.

b) If I need these commands, If I can run them while the tsp_old still contains normal tables or I have to first export the normal tables and then run the command at point 4) and then export the tables with LONG field.

c) In which way I can check the tsp_old does not contains any objects before to drop it.

regards

[former_member204746 · Accepted solution]

a) if I need to run this command, or more simply go ahead exporting all the tables still in tsp_old.

You need to run them if you want the imports to be imported in the right tablespace

b) If I need these commands, If I can run them while the tsp_old still contains normal tables or I have to first export the normal tables and then run the command at point 4) and then export the tables with LONG field.

wait at then end of the process. you run this command after the export but before you drop its contents.

c) In which way I can check the tsp_old does not contains any objects before to drop it.

select count (segment_name) from dba_segments where tablespace_name='tsp_old';

[Former Member · Accepted solution]

tnaks for the feedback.

I read agina note 646681 and I found in remark 14) usefull suggestions.

My situation is I filled up the tsp_new, but I cannot extend it anymore;

I have still tables to reorganize into the tsp_old

I have still indexes to be moved into the tsp_new (yes I did a mistake I'm not using a dedicated tsp_new for the indexes).

The steps at remark 14) does not make differences between tables and tables with LONG field: at point) it just export all of them.

That for me solve the problem to manage these tables.

The problem is that the scenario presented by the note is different :

I have already a tsp_new containg the tab data class of the tsp_old.

I do not understand why I should move the tables listed at pont 3 first into the ausiliary tablespace. Why I cannoy export them as the others ?

I'm aware at the end , after the import into tsp_new and after the rename tsp_new --> tsp_old, I will have to move the indexes to PSAPBTABI.

regards

[former_member204746 · Accepted solution]

you need to do the following steps (That is for PSAPBTABD with PSAPBTAB2D as Reorg tablespace and PSAPBTAB2I as its index tablespace):

brspace -c force -f tbreorg -s PSAPBTABD -t "*" -n PSAPBTAB2D -i PSAPBTAB2I -d only_tab

brspace -c force -f tbreorg -s PSAPBTABD -t "*" -n PSAPBTAB2D -i PSAPBTAB2I -d only_ind

brspace -c force -f tbreorg -s PSAPBTABD -t "*" -n PSAPBTAB2D -i PSAPBTAB2I -d only_dep

stopsap r3

brspace -c force -f tbexport -s PSAPBTABD -t "*" -r yes -i no -c no -g no -e no

sqlplus / as sysdba

select count (segment_name) from dba_segments where tablespace_name='PSAPBTABD';

should find the same number as found LONG RAW tables

MAKE SURE THAT EXPORT FILE AS BEEN CREATED

brspace -c force -f tsdrop -t PSAPBTABI -f

brspace -c force -f tsdrop -t PSAPBTABD -f

sqlplus / as sysdba

@<SAPDATA_HOME>/sapreorg/<work_dir1>/ddl.sql

increase PSAPUNDO to largest table

brspace -f tbimport -y full -r yes -i no -c no -g no -n yes -m no -p 4

sqlplus / as sysdba

@<SAPDATA_HOME>/sapreorg/<work_dir2>/ddl.sql

@<SAPDATA_HOME>/sapreorg/<work_dir3>/ddl.sql

brspace -c force -f tsalter -a rename -t PSAPBTAB2D -n PSAPBTABD

brspace -c force -f tsalter -a rename -t PSAPBTAB2I -n PSAPBTABI

startsap r3

brconnect -u / -c -f stats -t PSAPBTABD -f collect -p 2

brconnect -u / -c -f stats -t PSAPBTABI -f collect -p 2