on 03-11-2015 5:18 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.