cancel
Showing results for 
Search instead for 
Did you mean: 

ORA-1654 despite free space

mario_bisonti2
Participant
0 Kudos

Hallo.

My system SAP is:

SAP ECC 6.00

Kernel 7.21 EXT UC 64-bit

Oracle 11.2.0.3

Today my SAP system hangs (update deactivated, users stuck)  at 16:37

In the Oracle alert log I see:

Archived Log entry 283680 added for thread 1 sequence 223167 ID 0x36faced0 dest 1:

Wed Mar 11 16:31:30 2015

Completed checkpoint up to RBA [0x367c0.2.10], SCN: 6823273164

Wed Mar 11 16:37:28 2015

ORA-1654: unable to extend index SAPSR3.S033~0 by 8192 in tablespace               PSAPSR3

Wed Mar 11 16:40:00 2015

Incremental checkpoint up to RBA [0x367c0.208d8.0], current log tail at RBA [0x367c0.350ae.0]

but in PSAPSR3 there was near 7Gb free so I don't understand this.

I checked it by DB02 transaction and brtools : same result.

After I added  8GB , and after that I reactivated the update by SM13 transaction, the problem was solved.

In the attachmnt space.jpg you can see the size occupation.

Note that in the August 2014 I executed a partial conversion to IOT tables following :

"Oss1856270-Performance improvements for tables with single unique index." so I obtain near 50GB free.

Do you know if there are some misunderstanding wrong interpratation on the free space?

Could you help me?

Thanks a lot

Mario

Accepted Solutions (1)

Accepted Solutions (1)

stefan_koehler
Active Contributor
0 Kudos

Hi Mario,

not quite sure where this list (file FreeExtent.txt) comes from, but it looks like some SAP DBAcockpit query i am not aware of. However there seems to be a little misconception about the ORA-1654 unit.

>> As you can see in the FreeExtent.txt, you see more freespace larger than 8192 bytes requested.

You don't need 8192 bytes, you need 65536 kb (= 64 MB = 8 k * 8192 blocks). This is related to the LMT / AUTOALLOCATE extent allocation algorithm (segment size > 1 GB = 64 MB extent size). So i assume the index "SAPSR3.S033~0" is larger than 1 GB.

The first question would be: Did the data files 60 and 59 also exist when the ORA-1654 error occurred or did you add these afterwards? Let's assume that these files were added afterwards.


If there is still not enough contiguous free space (in your case 64 MB), Oracle can allocate less, but multiple extents to some degree ("free extent search algorithm with extent alignment"). It works with "desired and minimum acceptable" units. This behavior can be controlled with event "60060". When extent alignment is turned on, alignment size is the stripe size of the storage volume - if it is turned off, the alignment (of starting block of free extent ) enforced is across 1MB boundary. The extent alignment it is turned on by default.


shell$ oerr ora 60060

60060, 00000, "extent allocation alignment event"

// *Document: NO

// *Cause:    This is an internal debugging event, and must be enabled

//            only under the supervision of Oracle Support Services or

//            development.

// *Action:   N/A

... it becomes even more tricky if you work on ASM, but your data files are located inside a file system if i get you right.

Regards

Stefan


mario_bisonti2
Participant
0 Kudos

Hallo Stefan, you are right.

Space requested was 64MB and there wasn't extents with size >= 64 MB before I added file 59 and 60

I don't understant how could I retrieve the alignment size ( stripe size of the sorage volume ?) that Oracle requested.

When you speak about alignment size, did you intend Allocation Unit (AU) as I read in this Oracle related stuff: ASM AU Size And LMT AUTOALLOCATE  ?

Furthermore, do you suggest me to disable alignment by means of the event 60060 ?

Thanks a lot for your big support.

Mario

stefan_koehler
Active Contributor
0 Kudos

Hi Mario,

yes, this blog article by Randolf Geist is about the extent alignment in case of ASM and a larger AU size. This is what i have mentioned with the last phrase in my previous answer.

However this extent alignment was introduced with 11g in general. You can check MOS ID #1539699.1 for further details, if you have "My Oracle Support" access.

>> Furthermore, do you suggest me to disable alignment by means of the event 60060 ?

It is a trade-off between "space wastage" and efficiency of search/select information during insert/select operations. It also depends on your I/O pattern, but I/O splitting can mostly be "ignored" in case of classic SAP OLTP systems. Oracle itself provides this event as a solution (MOS ID #1539699.1), but as this is a SAP system i would open a SAP SR and get their approval, otherwise SAP support can be very picky in case of issues in the future (just my experience).

Regards

Stefan

P.S.: Your issue can also be related to corrupt LMT bitmaps, but this is speculation only.

Answers (2)

Answers (2)

JamesZ
Advisor
Advisor
0 Kudos


Hi Mario,

"ORA-1654: unable to extend index SAPSR3.S033~0 by 8192 in tablespace               PSAPSR3" means you do not have continuous free space for PSAPSR3 to extend. To solve this issue, you can perform reorganization as per SAP note 646681 by using brtools, because you have enough noncontinuous free space.


Another quick way is to add data file to the tablespace.


cheers,

James



mario_bisonti2
Participant
0 Kudos

Our tablespace PSAPSR3 is LMTS and ASSM as you can see by the following select.

SQL> SELECT tablespace_name, contents, extent_management,

  2              allocation_type, next_extent

  3       FROM dba_tablespaces;

TABLESPACE_NAME                CONTENTS  EXTENT_MAN ALLOCATIO NEXT_EXTENT

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

SYSTEM                         PERMANENT LOCAL      SYSTEM

PSAPUNDO                       UNDO      LOCAL      SYSTEM

SYSAUX                         PERMANENT LOCAL      SYSTEM

PSAPTEMP                       TEMPORARY LOCAL      UNIFORM       1048576

PSAPSR370                      PERMANENT LOCAL      SYSTEM

PSAPSR3700                     PERMANENT LOCAL      SYSTEM

PSAPSR3USR                     PERMANENT LOCAL      SYSTEM

PSAPSR3                        PERMANENT LOCAL      SYSTEM

SQL> select tablespace_name, extent_management, segment_space_management

  2      from dba_tablespaces

  3      where segment_space_management = 'AUTO';

TABLESPACE_NAME                EXTENT_MAN SEGMEN

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

SYSAUX                         LOCAL      AUTO

PSAPSR370                      LOCAL      AUTO

PSAPSR3700                     LOCAL      AUTO

PSAPSR3USR                     LOCAL      AUTO

PSAPSR3                        LOCAL      AUTO

As you can see in the FreeExtent.txt, you see more freespace larger than 8192 bytes requested.

Thanks

former_member185954
Active Contributor
0 Kudos

Hello Mario,

What you experienced, isn't supposed to happen.

Maybe the filesystems on which these data files existed were full or have other data files that are competing for autoextension.

Regards,

Siddhesh

mario_bisonti2
Participant
0 Kudos

Datafiles aren't set to autoextend because we want to have more control about the increasing of database.

Filesystem wasn't full.

former_member185954
Active Contributor
0 Kudos

I can't think of anything else, but I am keen to listen to what other experts say.

former_member185954
Active Contributor
0 Kudos

Hello Mario,

Looking at your screenshot, I can see that you have LMTS set as your next extent size is zero.

Why you would reach this situation will be determined by your datafile distribution, freespace in each datafile and next extent required by your tables that are being updated.

Check the following note

599694 - LMTS autoallocate: Extent allocation

if you already know this, please ignore my message

Regards,

Siddhesh

mario_bisonti2
Participant
0 Kudos

Thanks Siddesh.

Tablespace is LMTS, so you see NextExtent(MB)=0

It is the normal behaviour on this type of tablespace.

Thanks.