DBATL reorganization with brspace failed ORA-12008, ORA-01410, ORA-06512

[Former Member]

Dear all,

We are using SAP R/3 4.6C, with 4.6D_EXT kernel 2271, Oracle 9.2.0.5

and HP-UX 11.23.

We plan to migrate Oracle into version 10g.

As a strongly recommendation from the SAP guide for Oracle migration is

the Oracle migration of the tablespaces from dictionary into locally managed.

I finished the migration on the test environmetn, using oracle nativ commands for export, brspace for drop, sapdba for recreate and oracle import. But, some problems with SAP data classes appeared in the tables TAORA, IAORA, TSORA and others.

Now, I started the procedure with brtools like in the Note <b>646681</b> -

Reorganizing tables with BRSPACE especially as in the <b>Remark 14</b> from

the note.

First I created Oracle scripts for export and import for every

tablespace (except System, PSAPROLL and PSAPTEMP) then I shutdown the

SAP instance,

Then created PSAPAUX?? tablespaces for every tablespace like:

brspace -f tscreate -t PSAPAUXDD -s 100 -a yes -m 1024 -i 10 -d both -l

PSAPDDICD

Then I moved the tables like in the step 3 from the Remark 14:

brspace -f tbreorg -t TAORA,DDART,DARTT,SVERS,MLICHECK,DDNTT -n

PSAPAUXDD

brspace -f tbreorg -t DD09L,DD02L,IAORA,TSORA,IGORA,TGORA -n PSAPAUXES

brspace -f tbreorg -t DBATL,DBAML -n PSAPAUXST

brspace -f tbreorg -t SDBAD -n PSAPAUXPR

<u>after this command the following error occurred</u>:

BR0301W SQL error -600 at location BrsDblogOpen-6

<b>ORA-00600</b>: internal error code, arguments: [kcbnew_3], [1], [], [], [],

[], [], []

brspace -f tbreorg -t SDBAH -n PSAPAUXBT

Started the export for all.

Drop tablespace PSAPSTABD and PSAPSTABI (warning about the same ORA-

00600 occurred)

Recreate the tablespaces and import script finished <b>OK</b>.

When I tried to reorganized the tables DBATL,DBAML with the following

command

brspace -f tbreorg -s PSAPAUXST -t "DBATL,DBAML" -n PSAPSTABD -i

PSAPSTABI

DBAML was successfully moved into PSAPSTABD but DBATL not:

BR1124I Starting reorganization of table SAPR3.DBATL ...

BR0280I BRSPACE time stamp: 2007-03-08 12.25.32

BR0301E SQL error -12008 at location tab_onl_reorg-20

ORA-12008: error in materialized view refresh path

ORA-01410: invalid ROWID

ORA-06512: at "SYS.DBMS_REDEFINITION", line 8

ORA-06512: at "SYS.DBMS_REDEFINITION", line 146

ORA-06512: at line 1

BR0280I BRSPACE time stamp: 2007-03-08 12.25.32

BR1106E Reorganization of table SAPR3.DBATL failed

I started the <b>sapdba</b> tool and tried to reorganize the table DBATL from PSAPAUXST into the right tbs PSAPSTABD, sapdba also said ORA-01410: invalid ROWID but as warning.

I ignore the warning and the reorganization finished successfully.

After this I continue with the procedure for migration of the tablespaces with export, move tables in AUX tablespace if needed, drop, recreate,

import, and move back tables if they were moved.

Everything now is done successfully!!!

The alert log doesn't have any ORA errors now.

My opinion is because of tbreorg of the DBATL table like in the Note 646681 - Reorganizing tables with BRSPACE especially as in the Remark 14 from the note, the ORA errors occurred. When I moved back the table into the corresponding tablespace PSAPSTABD the errors disappear.

Did anyone performed the Oracle dmts into lmts migration and used reorg of table DBATL? Did anyone got ORA-errors after this?

Thank you in advance.

Many regards,

Ruzica Kalkasliev

[Former Member · Accepted solution]

Hi,

This 600 Error is occured in 9205 When segments reside in locally managed tablespaces with automatic segment space management (ASSM) there is a possibility that they may become corrupted.

ORA-600 [kcbnew_3] can occur when segment advisor has been running on an instance as it can read blocks into the cache as CURRENT when it should have read them as CR.

And ORA-01410 might be becoz of index or block corruption.You can drop and recreate index when u face this error.

Vinod

[Former Member · Accepted solution]

Hello Vinod,

How I know if it is a block corruption or not?

I did dbverify on all datafiles everything went successfully.

Also I did:

SQL> analyze table sapr3.DBATL validate structure cascade online;

Table analyzed.

When I reorganized the table DBATL with sapdba tool the ORA-01410 still appeared but, after the reorganization in the alert.log the ORA-600 disappeared.

Should I reorganize this table to the AUX tablespace when performing dmts into lmts migration for PSAPBTABD?

This weekend I need to do this for the production instance.

Thanks,

Ruzica Kalkaslev

[Former Member · Accepted solution]

Hi,

I also did as in the <b>Note 23345 - Consistency check of ORACLE database</b>

sapt> exp sapr3/sap tables=DBATL file=NUL: buffer=3000000

Export: Release 9.2.0.5.0 - Production on Thu Mar 15 10:54:23 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production

With the Partitioning option

JServer Release 9.2.0.5.0 - Production

Export done in WE8DEC character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table DBATL 83 rows exported

Export terminated successfully without warnings.

Ruzica

[Former Member · Accepted solution]

Hi,

Dbverify and Export u had done.So I guess there shd not be Corruption.

You are Abs rght.

I think it is not specific to any table.Plz have a look on note 764015.

Might be Bug

Vinod

[Former Member · Accepted solution]

Hello,

Maybe it's Oracle bug!

I had read this and the related notes and it seams that the <b>problem</b> arise when I move the table DBATL from the tablespace PSAPSTABD, which is at first dictionary managed, into PSAPAUXST (newly created tbs with the data cladss like psapstabd). My problem ocured in the test system which was the most fresh homogenous system copy from the production. I checked everything in production system dbverify of all datafiles, export and analyze for this table, all is OK.

Do you have experience with the procedure for migration of dmts into lmts with brspace?

Thanks,

Ruzica

[former_member204746 · Accepted solution]

your last reorg of table DBATL failed, you still have a materialized view on it.

try this:

brspace -f tbreorg -t DBATL,DBAML -n PSAPAUXST -a cleanup

sqlplus "/ as sysdba"

select table_name from dba_tables where table_name like 'DBA%';

drop materialized view sapr3.DBATL;

exit;

try reorg again.

[Former Member · Accepted solution]

Hi Eric,

No, after the problem with brspace, I did the reorg with <b>sapdba</b> tool and the table DBATL successfully was moved in the tablespace PSAPSTABD after I recreate and import its data.

On the test system now everything is fine, but I'm afraid to do this on the production system (which is my scheduled task for the weekend).

I don't understand why only on this table the problem ocured.

Thanks,

Ruzica

[former_member204746 · Accepted solution]

if you are really worried about this table, stop SAP, export this table and import it manually. This will work and wil lnot cause problems you would have with online reorgs.

[Former Member · Accepted solution]

I do everything offline.

I follow the SAP note <b>Note 646681 - Reorganizing tables with BRSPACE</b>:

<i><b>3</b>. If the <reorg_tsp> tablespace contains any of the following tables: SDBAH,SDBAD,DBAML,<b>DBATL</b>,MLICHECK,TGORA,IGORA,TSORA,TAORA, IAORA,SVERS,DD02L,DD09L,DDNTT,DDART,DARTT or SAPLIKEY (SAPLIKEY is only available in NetWeaver 2004s or higher), then you should move them to the tablespace <aux_tsp> by online reorganization using BRSPACE:

brspace -f tbreorg -t <table_list> -n <aux_tsp></i>

<u>I do</u>: stop sap, start db, create aux tbs, move related tables into aux tbs, export tbs, drop tsp, recreate tbs, import data and indexes, move back the tables in the tbs, drop aux tbs, start statistics, restart db and start r3.

Ruzica

[former_member204746 · Accepted solution]

from the note, you are doing an ONLINE reorg, even if you are shutting down SAP.

DBATL is needed by DB13.

Check the SAP note again to do an OFFLINE Reorg, this means export the table and re-import it after wards. The note explain the process for LONG RAW tables and it is pretty straightforward.

[Former Member · Accepted solution]

This procedure, in the Note 646681, Remark 14 is named as an offline reorganization with BRSPACE.

Only the specific tables need to be reorganized in the new tablespace by online reorganization, because example DBATL is also needed for the brspace job logs.

<b>MY question is:</b>

Maybe it will be no problem if I don't move DBATL into aux tablespace but to proceed with the procedure to export the PSAPSTABD tablespace (in which the table exists), drop tablespace, recreate and import data and indexes???

I did this on another SAP instance and it went ok.

[former_member204746 · Accepted solution]

the 12 tables you mentioned earlier are mostly "live" tables when BRTOOLS are used. So, reorganizing them can cause problems such as the one you had.

This is why I highly recommend to do them while SAP is down and use brspace or Oracle tools to export/import these contents. Also check if you are using the latest BRSPACE, I remember that using patch level 18 was really slowing down the reorg process in a factor of 3 to 1.

if you export all contents from PSAPSTABD and the re-import them, this will work as it does not affect those "live" tables. So, if your system is small, this can be a good strategy. If you can't handle long downtimes, just do an export/import of those 12 tables in another tablespace, this will go quite fast as they are quite small. Then, you can do online reorgs whenever you want to PSAPSTAB2D, drop PSAPSTABD and rename PSAPSTAB2D to PSAPSTABD. The drop/rename steps will also need downtime.

I hope this helps.

[Former Member · Accepted solution]

Thanks Eric,

I got two days downtime, for this weekend.

I think export/import will be ok.

Many regards,

Ruzica