Online table reorg fails with ORA-01400

[Former Member]

Hello,

I am trying to perform an online table reorganisation using brspace for a number of tables with already existing materialized views.

These materialized views are used for Oracle replication based on customer statement. The environment has the productive database and also a shadow database hosted on a different server.

The online table reorg fails with the bellow message.

BR1124I Starting 'online' reorganization of table SAPR3.Z370DLCH ...

BR0280I BRSPACE time stamp: 2014-07-20 09.12.38

BR0301E SQL error -1400 at location tab_onl_reorg-39, SQL statement:

'BEGIN DBMS_REDEFINITION.START_REDEF_TABLE (UNAME => '"SAPR3"', ORIG_TABLE => '"Z370DLCH"', INT_TABLE => '"Z370DLCH#$"', OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_PK); END;'

ORA-01400: cannot insert NULL into ("SYS"."MLOG_REFCOL$"."OLDEST")

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

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

ORA-06512: at line 1

BR0280I BRSPACE time stamp: 2014-07-20 09.12.38

BR1106E Reorganization of table SAPR3.Z370DLCH failed

Since the brspace reorganisation uses DBMS_REDEFINITION I imagine that the reorg fails because a new materialized view cannot be created.

Is there a solution to reorganize this king of tables that already have materialized views created?

We do not have the option to use "brspace -u / -f tbreorg -t <table_name> -a cleanup" since customer does not want us to touch the existing materialized views.

Thank you veru much for your time.

[Former Member]

Hi Samuel,

I hope you are able to recover the table (restore ?)  and retry.

Please check OSS note 2034605, patch 38 is available.

Ineke

[Former Member]

From your original error message, and from the message you are getting now, I gather that table "SYS"."MLOG_REFCOL$" actually is needed for reorg using SYS.DBMS_REDEFINITION and brspace.
Dropping it definitely was not a wise decision.

Sorry for being the bearer of bad news.

[Former Member]

Hello,

I have done this as a test in the quality system of the landscape and not in the productive one.

We have the posibility to export the table from the productive system and import it to the Quality system.

I will try today the brtools upgrade as a solution to exclude this possible cause as well.

[Former Member]

Well, trying in quality system first definitely was a wise decision!
Now, in a system where the table is still there, please post the output of:
desc "SYS"."MLOG_REFCOL$"

However I am not sure whether recreating or importing the table will be sufficient.
There may be dependecies with other tables, like foreign keys, that you will have to consider as well.

regards

[Former Member]

Thank you again all for taking the time to help me on this.

Bellow is the desc for the MLOG_REFCOL$ table.

SQL> desc MLOG_REFCOL$

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

MOWNER                                    NOT NULL VARCHAR2(30)

MASTER                                    NOT NULL VARCHAR2(30)

COLNAME                                   NOT NULL VARCHAR2(30)

OLDEST                                    NOT NULL DATE

FLAG                                      NOT NULL NUMBER(38)

OLDEST_SCN                                         NUMBER

SQL>

[Former Member]

That's like what I had expected.
In our systems (Oracle 11.2.0.3) we have this:
Name          Null?    Typ
------------- -------- ---------------
MOWNER        NOT NULL VARCHAR2(30)
MASTER        NOT NULL VARCHAR2(30)
COLNAME       NOT NULL VARCHAR2(30)
OLDEST                 DATE
FLAG                   NUMBER(38)
OLDEST_SCN             NUMBER

So you have got more of the 'NOT NULL'. They came with Oracle 11.2.0.4 I presume.
And the error message is about the 'NOT NULL' for column OLDEST.

I agree with Nick Loy; the latest version of brspace should take care of it.
So please try that!

regards

[Former Member]

Hello again,

We are still in the same situation after the brtools upgrade.

I consider opening a sap call and ask for a resolution for this problem directly from SAP.

Thank you again for your help with this problem.

I now have brtools 7.20 PL 38

release note	1428529
kernel release	720
patch date	2014-06-25
patch level	38
make platform	rs6000_64
make codeline	720_REL
make mode	OCI_102
make date	Jul  9 2014

BR0280I BRSPACE time stamp: 2014-07-24 15.43.21

BR1101I Starting 'online' table reorganization...

BR0280I BRSPACE time stamp: 2014-07-24 15.43.21

BR1124I Starting 'online' reorganization of table SAPR3.Z370DLCH ...

BR0280I BRSPACE time stamp: 2014-07-24 15.43.22

BR0301E SQL error -1400 at location tab_onl_reorg-39, SQL statement:

'BEGIN DBMS_REDEFINITION.START_REDEF_TABLE (UNAME => '"SAPR3"', ORIG_TABLE => '"Z370DLCH"', INT_TABLE => '"Z370DLCH#$"', OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_PK); END;'

ORA-01400: cannot insert NULL into ("SYS"."MLOG_REFCOL$"."OLDEST")

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

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

ORA-06512: at line 1

BR0280I BRSPACE time stamp: 2014-07-24 15.43.23

BR1106E Reorganization of table SAPR3.Z370DLCH failed

[Former Member]

So it seems even the latest version of brspace can't handle the new 'NOT NULL' restriction.
I think a SAP support call will be a good idea, in order to point the SAP developers to it.

regards

[Former Member]

Paste the output of desc SAPR3.Z370DLCH;

Regards,

Nick Loy

[Former Member]

SQL> desc SAPR3.Z370DLCH

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

MANDT                                     NOT NULL VARCHAR2(3)

VBELN                                     NOT NULL VARCHAR2(10)

VBELV                                     NOT NULL VARCHAR2(10)

STAWN                                     NOT NULL VARCHAR2(17)

VRKME                                     NOT NULL VARCHAR2(3)

GEWEI                                     NOT NULL VARCHAR2(3)

WAERK                                     NOT NULL VARCHAR2(5)

WAERK_DM                                  NOT NULL VARCHAR2(5)

KURSK                                     NOT NULL NUMBER(9,5)

PRSDT                                     NOT NULL VARCHAR2(8)

NETWR                                     NOT NULL NUMBER(15,2)

NETWR_DM                                  NOT NULL NUMBER(15,2)

LFIMG                                     NOT NULL NUMBER(13,3)

BRGEW                                     NOT NULL NUMBER(13,3)

NTGEW                                     NOT NULL NUMBER(13,3)

VERDAT                                    NOT NULL VARCHAR2(8)

VERZEIT                                   NOT NULL VARCHAR2(6)

UNAME                                     NOT NULL VARCHAR2(12)

SORTB                                     NOT NULL VARCHAR2(5)

SORTZT                                    NOT NULL VARCHAR2(6)

LZONE                                     NOT NULL VARCHAR2(10)

KUNNR                                     NOT NULL VARCHAR2(10)

KUNN2                                     NOT NULL VARCHAR2(10)

BSTNK                                     NOT NULL VARCHAR2(20)

LFDAT                                     NOT NULL VARCHAR2(8)

SODATUM                                   NOT NULL VARCHAR2(8)

SOZEIT                                    NOT NULL VARCHAR2(6)

Z3KORR                                    NOT NULL VARCHAR2(1)

PKSNR                                     NOT NULL VARCHAR2(10)

KARNR                                     NOT NULL VARCHAR2(4)

SAMLS                                     NOT NULL VARCHAR2(1)

PKLFD                                     NOT NULL VARCHAR2(5)

ISTDATUM                                  NOT NULL VARCHAR2(8)

ISTZEIT                                   NOT NULL VARCHAR2(6)

UPSSTATUS                                 NOT NULL VARCHAR2(5)

UPSGRUND                                  NOT NULL VARCHAR2(5)

TRACKING                                  NOT NULL VARCHAR2(35)

SPEDITEUR                                 NOT NULL VARCHAR2(20)

SERVICEART                                NOT NULL VARCHAR2(5)

[Former Member]

Hi,

Is it the full list of filelds? I couldn't find any NULL filed in this but error is showing that!

Regards,

Nick Loy

[Former Member]

Hello,

Yes, those are all the fields of the table.I do not think that this is related to any LONGRAW, RAW data from any of the tables.

After dropping the MLOG_REFCOL$ table which was not in the data dictionary and according to customer was not used for anything, I now have a new error.

Both the initial error and the new one are general errors.I receive them for any table that I try to reorganise.

BR0280I BRSPACE time stamp: 2014-07-22 16.19.12

BR1101I Starting 'online' table reorganization...

BR0280I BRSPACE time stamp: 2014-07-22 16.19.12

BR1124I Starting 'online' reorganization of table SAPR3.SEOCOMPODF ...

BR0280I BRSPACE time stamp: 2014-07-22 16.19.31

BR0301E SQL error -942 at location tab_onl_reorg-39, SQL statement:

'BEGIN DBMS_REDEFINITION.START_REDEF_TABLE (UNAME => '"SAPR3"', ORIG_TABLE => '"SEOCOMPODF"', INT_TABLE => '"SEOCOMPODF#$"', OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_PK); END;'

ORA-00942: table or view does not exist

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

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

ORA-06512: at line 1

BR0280I BRSPACE time stamp: 2014-07-22 16.19.31

BR1106E Reorganization of table SAPR3.SEOCOMPODF failed

Thank you again for your time.

[Former Member]

Hi,

Did you apply the latest SBP and the BRTools?

Update them and try to reorg the tables.

Regards,

Nick Loy

[former_member182657]

Hi,

Kindly share the database details,meanwhile kindly follow SAP Note   1831679 - Problems when doing online reorg of PCL2 table

Regards,

Gaurav

[Former Member]

I think all my troubles come from the bellow

SQL> select table_name from dba_tables where table_name like '%REFCOL%';

TABLE_NAME

------------------------------

MLOG_REFCOL$

SQL>

[Reagan]

Could you supply the version of the Oracle database and the bundle patch installed on system?

Try applying the latest SBP and see if that helps.

RB

[Former Member]

Hello,

We have SBP 11.2.0.4.1 201402 on our system.

[former_member188883]

Hi Samuel,

What is the path level of dba tools ?

Regards,

Deepak Kori

[Former Member]

Hello again,

Sap kernel is 7.21 PL 201, and the dbatools version bellow.

sap04381:p31adm 5> brtools -V

BR0651I BRTOOLS 7.20 (30)

release note               1428529

kernel release             720

patch date                 2013-01-31

patch level                30

make platform              rs6000_64

make codeline              720_REL

make mode                  OCI_102

make date                  Feb  4 2013

We are planning a kernel upgrade for this system at the beginning of August to 7.21 PL 316 and most probably we will also update brtools to patch level 38.