cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

2 Tables missing after reorg ONLINE [11.2.0.2]

Go to solution
Former Member

on ‎03-27-2012 1:58 PM

0 Kudos

Dear All,

we encounter a problem during the reorg of the Quality system.

The release is "SBP 11.2.0.2.3 201110 containing CPUJul2011", the brtools 7.20 (18)

The brspace command brspace -p initICQ.sap -s 20 -l E -U -f tbreorg -t J*,K*,M*,N*,O*

The reorganization failed for 6 tables:

SAPSR3.O2APPLT

SAPSR3.OBJH

SAPSR3.OAPRE

SAPSR3.OXT_SYSTYPES

SAPSR3.OXT_PICTURE

SAPSR3.NWBC_CONFIG


But now two of those tables (SAPSR3.OAPRE,SAPSR3.NWBC_CONFIG) are no longer present on the DB.

794 - SAPSR3 NWBC_CONFIG NO 1 0 0 0:0

831 - SAPSR3 OAPRE NO 1 0 0 0:0

The log of brspace

####

BR0280I BRSPACE thread 2: time stamp: 2012-03-26 19.30.55

BR1124I Starting 'online' reorganization of table SAPSR3.NWBC_CONFIG ...BR0280I BRSPACE thread 1: time stamp: 2012-03-26 19.31.10

BR1932I Evaluation procedure 'GET_INDEX_COMPR_ORA' called successfull for index SAPSR3.NULLCOLS~0, number of columns to compress: 1

BR0280I BRSPACE thread 1: time stamp: 2012-03-26 19.31.19

BR1105I Table SAPSR3.NULLCOLS reorganized successfully

BR0280I BRSPACE thread 1: time stamp: 2012-03-26 19.31.19

BR1124I Starting 'online' reorganization of table SAPSR3.O2APPL ...

BR0280I BRSPACE thread 2: time stamp: 2012-03-26 19.33.02

BR0301E SQL error -4031 in thread 2 at location ddl_tab_create-10, SQL

statement:

'FETCH curs_6'

'SELECT DBMS_METADATA.GET_DDL ('TABLE', 'NWBC_CONFIG', 'SAPSR3') FROM DUAL'

ORA-04031: unable to allocate 184 bytes of shared memory ("shared pool","SELECT /*+rule*/

SYS_XMLGEN(...","SQLA^f71b2ae9","qersos:qersoAllocate")

ORA-06512: at "SYS.DBMS_METADATA", line 4018

ORA-06512: at "SYS.DBMS_METADATA", line 5843

ORA-06512: at line 1

BR0280I BRSPACE thread 2: time stamp: 2012-03-26 19.33.02

BR1119E Creation of DDL statements for table SAPSR3.NWBC_CONFIG failed

BR0280I BRSPACE thread 2: time stamp: 2012-03-26 19.33.02

BR1124I Starting 'online' reorganization of table SAPSR3.O2APPLT ...

BR0280I BRSPACE thread 1: time stamp: 2012-03-26 19.33.29

BR0301E SQL error -4031 in thread 1 at location ddl_tab_create-10, SQL

statement:

'FETCH curs_6'

'SELECT DBMS_METADATA.GET_DDL ('TABLE', 'O2APPL', 'SAPSR3') FROM DUAL'

ORA-04031: unable to allocate 232 bytes of shared memory ("shared

pool","select /*+ rule */

bucket_cn...","SQLA^337fc737","pqctx:kkfdParallelContextGet")

ORA-06512: at "SYS.DBMS_METADATA", line 4018

ORA-06512: at "SYS.DBMS_METADATA", line 5843

ORA-06512: at line 1

BR0280I BRSPACE thread 1: time stamp: 2012-03-26 19.33.29

BR1119E Creation of DDL statements for table SAPSR3.O2APPL failed

####

####

BR0280I BRSPACE thread 2: time stamp: 2012-03-26 19.39.20

BR1124I Starting 'online' reorganization of table SAPSR3.OAPRE ...

BR0280I BRSPACE thread 1: time stamp: 2012-03-26 19.39.20

BR1124I Starting 'online' reorganization of table SAPSR3.OAPRI ...

BR0280I BRSPACE thread 1: time stamp: 2012-03-26 19.39.33

BR1926I Small index SAPSR3.OAPRI~0 will not be compressed

BR0280I BRSPACE thread 1: time stamp: 2012-03-26 19.39.42

BR1105I Table SAPSR3.OAPRI reorganized successfully

BR0280I BRSPACE thread 1: time stamp: 2012-03-26 19.39.42

BR1124I Starting 'online' reorganization of table SAPSR3.OBJH ...

BR0280I BRSPACE thread 1: time stamp: 2012-03-26 19.39.54

BR0301E SQL error -4031 in thread 1 at location ddl_tab_create-10, SQL

statement:

'FETCH curs_6'

'SELECT DBMS_METADATA.GET_DDL ('TABLE', 'OBJH', 'SAPSR3') FROM DUAL'

ORA-04031: unable to allocate 56 bytes of shared memory ("shared

pool","SELECT /*+rule*/ SYS_XMLGEN(...","SQLA^99130d4f","idndef*[]:

qkexrPackName")

ORA-06512: at "SYS.DBMS_METADATA", line 4018

ORA-06512: at "SYS.DBMS_METADATA", line 5843

ORA-06512: at line 1

BR0280I BRSPACE thread 1: time stamp: 2012-03-26 19.39.54

BR1119E Creation of DDL statements for table SAPSR3.OBJH failed

BR0280I BRSPACE thread 1: time stamp: 2012-03-26 19.39.54

BR1124I Starting 'online' reorganization of table SAPSR3.OBJLAN ...

BR0280I BRSPACE thread 2: time stamp: 2012-03-26 19.39.54

BR0301E SQL error -4031 in thread 2 at location ddl_tab_create-10, SQL

statement:

'FETCH curs_6'

'SELECT DBMS_METADATA.GET_DDL ('TABLE', 'OAPRE', 'SAPSR3') FROM DUAL'

ORA-04031: unable to allocate 1024 bytes of shared memory ("shared

pool","SELECT /*+rule*/

SYS_XMLGEN(...","SQLA^f71b2ae9","KGHSC_ALLOC_BUF:buf")

ORA-06512: at "SYS.DBMS_METADATA", line 4018

ORA-06512: at "SYS.DBMS_METADATA", line 5843

ORA-06512: at line 1

BR0280I BRSPACE thread 2: time stamp: 2012-03-26 19.39.54

BR1119E Creation of DDL statements for table SAPSR3.OAPRE failed

The Alertlog

Mon Mar 26 19:14:39 2012

Incremental checkpoint up to RBA [0x5daa.211e1.0], current log tail at RBA [0x5daa.29cb9.0]

Mon Mar 26 19:31:32 2012

Errors in file /oracle/ICQ/saptrace/diag/rdbms/icq/ICQ/trace/ICQ_mmon_3041.trc  (incident=540129):

ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select value$ from sys.props...","SQLA","tmp")

Incident details in: /oracle/ICQ/saptrace/diag/rdbms/icq/ICQ/incident/incdir_540129/ICQ_mmon_3041_i540129.trc

Mon Mar 26 19:31:32 2012

Errors in file /oracle/ICQ/saptrace/diag/rdbms/icq/ICQ/trace/ICQ_ora_21293.trc  (incident=541633):

ORA-04031: unable to allocate 232 bytes of shared memory ("shared pool","select /*+ rule */ bucket_cn...","SQLA^337fc737","pqctx:kkfdParallelContextGet")

Incident details in: /oracle/ICQ/saptrace/diag/rdbms/icq/ICQ/incident/incdir_541633/ICQ_ora_21293_i541633.trc

Mon Mar 26 19:31:48 2012

Non critical error ORA-48913 caught while writing to trace file "/oracle/ICQ/saptrace/diag/rdbms/icq/ICQ/trace/ICQ_ora_21294.trc"

Error message: ORA-48913: Writing into trace file failed, file size limit [10240000] reached

Writing to the above trace file is disabled for now on...

Mon Mar 26 19:31:49 2012

Is it possibile that the ora-04031 caused the loss of there two tables?

Thanks and regards,

Cristian


Show replies
Show replies
Answer

Accepted Solutions (1)

Accepted Solutions (1)

stefan_koehler
Active Contributor
‎03-28-2012 5:32 PM
0 Kudos

Hello Cristian,

well i don't think that any table is gone, because of the ORA-04031 occurs while getting the DDL statement to create the interim table. DBMS_REDEFINITION was not run anway as you can see by the log files:

2012-03-26 19.39.20    BR1124I Starting 'online' reorganization of table SAPSR3.OAPRE ...
2012-03-26 19.39.54     BR0301E SQL error -4031 in thread 2 at location ddl_tab_create-10, SQL
2012-03-26 19.39.54      BR1119E Creation of DDL statements for table SAPSR3.OAPRE failed

Could you please verify, if the tables are still present on the database by running the following SQL query

shell> su - ora<SID>
shell> sqlplus / as sysdba
SQL> set linesize 500
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; 
SQL> select OBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, CREATED, LAST_DDL_TIME 
           from DBA_OBJECTS where owner = 'SAPSR3' and object_name in ('OAPRE','NWBC_CONFIG');

Orkun: I guess you mean SHARED_POOL_SIZE, increasing the database cache size would not fix that problem.

Regards

Stefan

Show replies
Show replies
Former Member
‎03-28-2012 8:13 PM
0 Kudos

>> I guess you mean SHARED_POOL_SIZE, increasing the database cache size would not fix that problem.

Sorry, I confused the threads 😕

Answers (3)

Answers (3)

Former Member
‎03-29-2012 1:14 PM
0 Kudos

Dear all,

thanks a lot for your reply. After some checks, the tables are present in the dba table, but a feature of oracle11 causes that the empty tables, after the reorg are not displayed ( on DB02). These are shown only when powered by the data.

Show replies
Show replies
volker_borowski2
Active Contributor
‎03-31-2012 12:56 PM
0 Kudos

Hi,

whenever an online reorg fails, esp. with such a severe error, you should use the reorg function "cleanup" from brspace to ensure all objects related to rdbms_redefinition are reset correctly, before you try to restart anything.

You should use "cleanup" as well, if a failed object has a red check in SE14 after an online reorg.

Volker

Former Member
‎03-29-2012 9:46 AM
0 Kudos 

Is it possibile that the ora-04031 caused the loss of there two tables?

Very unlikely, but if you have deferred_segment_creation set to true, then it is highly likely to "loose" the segment. This is a new feature of Oracle 11g, the segment will be created the first time data is inserted into the table. So this is wanted behavior. The table is still existing, you can query dba_tables (or dba_objects as suggested) to see it.

Cheers Michael


Show replies
Show replies
Former Member
‎03-28-2012 1:03 PM
0 Kudos

Hi Cristian,

You are facing with this problem because of the memory configuration. Try to increase DB_CACHE_SIZE parameter. If it is not work, then try increase SGA_TARGET and SGA_MAX_SIZE.

Best regards,

Orkun Gedik

Show replies
Show replies
Ask a Question