:: Issue while converting LONG2LOB conversion ::

[Former Member]

Hi,

We have to perform conversion from Long Raw column to New Lob (Secure file) for below 2 Tables in ECC system.

Also we have to include the respective indexes also in the same conversion.

Type	Object	Tablespace	Size KB	Size [GB]
TABLE	TST03	PSAPR3E	769,240,064	733.60
INDEX	TST03~0	PSAPR3E	4,264,448	4.07
TABLE	SOFFCONT1	PSAPR3E	1,360,615,424	1297.58
INDEX	SOFFCONT1~0	PSAPR3E	4,854,592	4.63

We could successfully convert the table TST03 and its index to Secure file LOB.

It took exactly 4 hours for this conversion.

We have used below command

brspace -p initRR1.sap -s 20 -l E -U -f tbreorg -a long2lob -s PSAPR3E -o SAPR3E -t TST03

However for the second table and its index it was running for very long period i.e. for 2-3 days.

We could not get anything suspicious in the initial analysis.

We have used below command

brspace -p initRR1.sap -s 20 -l E -U -f tbreorg -a long2lob -s PSAPR3E -o SAPR3E -t SOFFCONT1

The output of this was as below

===============================================================================================

BR0370I Directory /oracle/RR1/sapreorg/sejtsasr created

BR0280I BRSPACE time stamp: 2012-10-30 11.06.26

BR1101I Starting 'online' table reorganization...

BR0280I BRSPACE time stamp: 2012-10-30 11.06.26

BR1124I Starting 'online' reorganization of table SAPR3E.SOFFCONT1 ...

BR0280I BRSPACE time stamp: 2012-10-30 11.06.37

BR1932I Evaluation procedure 'GET_INDEX_COMPR_ORA' called successfully for index SAPR3E.SOFFCONT1~0, number of columns to compress: 3

scrbdefrmr207:rr1adm 60>

===============================================================================================

If we compare the sizes of these 2 tables and time needed to complete the re-org

TST03 Size 733 GB : Took 4 hours to finish.

SOFFCONT1 Size 1297.58 : Was running for 2-3 days and also it did not finish

Then we have cancelled the running processes from OS level.

Now when we try to run the long2lob conversion then, it throws below Error

===============================================================================================

BR0301W SQL error -23539 at location BrReorgCheck-9, SQL statement:

'BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE (UNAME => '"SAPR3E"', TNAME => '"SOFFCONT1"', OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_PK); END;'

ORA-23539: table "SAPR3E"."SOFFCONT1" currently being redefined

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

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

ORA-06512: at line 1

BR1111I Reorganization of table SAPR3E.SOFFCONT1 will be skipped

BR0280I BRSPACE time stamp: 2012-11-05 09.38.43

BR1113E All tables have been skipped for reorganization

===============================================================================================

Then we removed the .lock file for this table which was lying in the sapreorg folder.

Then also it threw same error as above

Then we ran the option Cleanup after aborted reorganization for this table.

However that also was running for couple of days.

We have cancelled the same.

Then we used stop option in the reorganization menu.

But that also did not work.

Kindly help to solve this issue.

Thank you, Regards,

Girish Garje

[Former Member]

Hi,

We have ran the cleanup and the issue got solved.

Now we could succeed in starting the Conversion.

However the conversion ran for almost 24 hours and then it ended with below Error

BR1932I Evaluation procedure 'GET_INDEX_COMPR_ORA' called successfully for index SAPR3E.SOFFCONT1~0, number of columns to compress: 3

BR0280I BRSPACE time stamp: 2012-11-24 17.39.04

BR0301E SQL error -3113 at location tab_onl_reorg-63, SQL statement:

'BEGIN DBMS_REDEFINITION.FINISH_REDEF_TABLE (UNAME => '"SAPR3E"', ORIG_TABLE => '"SOFFCONT1"', INT_TABLE => '"SOFFCONT1#$"'); END;'

ORA-03113: end-of-file on communication channel

Process ID: 23407

Session ID: 70 Serial number: 5559

BR0280I BRSPACE time stamp: 2012-11-24 17.39.04

BR0301W SQL error -3114 at location tab_onl_reorg-85, SQL statement:

'BEGIN DBMS_REDEFINITION.ABORT_REDEF_TABLE (UNAME => '"SAPR3E"', ORIG_TABLE => '"SOFFCONT1"', INT_TABLE => '"SOFFCONT1#$"'); END;'

ORA-03114: not connected to ORACLE

BR0280I BRSPACE time stamp: 2012-11-24 17.39.04

BR0301W SQL error -3114 at location tab_onl_reorg-86, SQL statement:

'DROP TABLE "SAPR3E"."SOFFCONT1#$" CASCADE CONSTRAINTS'

ORA-03114: not connected to ORACLE

BR0280I BRSPACE time stamp: 2012-11-24 17.39.04

BR0301W SQL error -3114 at location tab_onl_reorg-88, SQL statement:

'ALTER TABLE "SAPR3E"."SOFFCONT1" DROP PRIMARY KEY KEEP INDEX'

ORA-03114: not connected to ORACLE

BR0280I BRSPACE time stamp: 2012-11-24 17.39.04

BR1106E Reorganization of table SAPR3E.SOFFCONT1 failed

BR0280I BRSPACE time stamp: 2012-11-24 17.39.04

BR1102I Number of tables reorganized successfully: 0

BR1103E Reorganization failed for 1 table(s)

BR0280I BRSPACE time stamp: 2012-11-24 17.39.04

BR0301W SQL error -3114 at location brs_dblog_write-2, SQL statement:

'INSERT INTO SAP_SDBAD (BEG, FUNCT, SYSID, POS, LINE) VALUES ('20121123070731', 'tbr', 'RR1', '0000', 'A 0        1')'

ORA-03114: not connected to ORACLE

BR0325W Writing to database log failed

BR0280I BRSPACE time stamp: 2012-11-24 17.39.05

BR0301W SQL error -3114 at location thr_db_disconnect-1, SQL statement:

'COMMIT RELEASE'

ORA-03114: not connected to ORACLE

  BR0327W Disconnect from database instance RR11 failed

However we could successfully converted 1 table TST03 before this table successfully.

Would you help to solve this issue.

Thank you, Regards,

Girish Garje