Tablespace: PSAPTEMP # Tablespace files autoextend can cause file system overflow

[former_member204618]

Hi All,

We are seeing the above message after updating the kernel from 701 SP 125 to 720 SP 600.

I have checked the data files that make up PSAPTEMP and none are set to auto extend so I am confused as to what it is complaining about.

Tablespace name

Size(MB)

Free(MB)

Used(%)

Autoextend

Total size(MB)

Total free space(MB)

Total used (%)

#Files

#Segments

#Extents

Status

Contents

Compression

PSAPTEMP

11,264.00

11,262.00

0

NO

11,264.00

11,262.00

0

6

0

0

ONLINE

TEMPORARY

DISABLED

File name	File Id	Tablespace name	Size(MB)	#Blocks	Status	Rel. file number	Autoextensible	Maxsize(MB)	Maxblocks	Increment by	User size(MB)	User blocks
/oracle/SID/sapdata1/temp_1/temp.data1	1	PSAPTEMP	2,048.00	262,144	AVAILABLE	1	NO	0	0	0	2,047.00	262,016
/oracle/SID/sapdata1/temp_2/temp.data2	2	PSAPTEMP	2,048.00	262,144	AVAILABLE	2	NO	0	0	0	2,047.00	262,016
/oracle/SID/sapdata1/temp_3/temp.data3	3	PSAPTEMP	2,048.00	262,144	AVAILABLE	3	NO	0	0	0	2,047.00	262,016
/oracle/SID/sapdata3/temp_4/temp.data4	4	PSAPTEMP	3,072.00	393,216	AVAILABLE	4	NO	0	0	0	3,071.00	393,088
/oracle/SID/sapdata2/temp_5/temp.data5	5	PSAPTEMP	1,024.00	131,072	AVAILABLE	5	NO	0	0	0	1,023.00	130,944
/oracle/SID/sapdata2/temp_6/temp.data6	6	PSAPTEMP	1,024.00	131,072	AVAILABLE	6	NO	0	0	0	1,023.00	130,944

Any ideas how to resolve this or is it a bug?

Thanks

Craig

[ACE-SAP · Accepted solution]

Hello

This note explains that behavior

849484 - New CRITICAL_TABLESPACE check condition in BRCONNECT

For TEMP table spaces that Oracle creates as sparse files, the system additionally checks the currently assigned disk space with reference to the maximum disk space required (which is based on the file size defined in Oracle). This may lead to an alert even though the auto extend attribute is not set for any of the TEMP table space files.

What is your BR*Tools version ?

Why didn't you moved to 721 (or 712 Ext) kernel ?

Best regards

[former_member204618 · Accepted solution]

Hi Yves,

BRTools is 720 patch level 37

We didn't upgrade to 721 cause we are simply upgrading systems to the same kernel levels.  So 720 was used.  We can't use 721_EXT because we need an OS upgrade we are running HP-UX 11.23 721_EXT needs 11.31 and we are also still running Oracle 10.2, we'd need to upgrade this also to 11.2.

Are we saying that this is intended, that note is a little vague as to the reason why.  Should this be deactivated then?  None of our tablespaces/files are set to autoextend so I don't understand why it's complaining.

Thanks

Craig

[former_member188883 · Accepted solution]

Hi Craig,

Can you downgrade the SAP kernel to SP 500 and check. I have not see such issues with SP500 .

SP600 being the latest could have some bugs.

Also replace the dbatools SP37 with the one delivered with SP500.

Hope this helps.

Regards,

Deepak Kori

[ACE-SAP · Accepted solution]

Hi Craig,

Deactivating the alert could be the latest solution, but you should first check how the tempfiles are setup. If they are set as sparse files they might not use all the allocated space at FS level, but might grow up to it and cause an overflow...

To deactivate that behavior you should copy temp files with a specific cp option (--sparse=never) to deactivate the sparse attribute as explained in the here under notes (the 2nd one is for 11g only thus not valid for you).

Regards

548221 - Temporary Files are created as sparse files

Oracle creates files of temporary tablespaces as sparse files.

'Sparse' is a special property a file in Unix operating systems can have. Sparse files are files that can dynamically grow and shrink depending on their content. So many sparse files can use a common pool of free disk space. Also the creation of a sparse file is much faster than creation of a normal file.

Problems can occur if sparse files on the same disk grow in parallel and there is not sufficient free disk space for all to extend.

Usage of sparse files is not a bug. Therefore there is no possibility to tell Oracle not to use sparse files for the temporary tablespace if the operating system offers sparse file functionality.

1864212 - Forcing complete space allocation for temp files

By default, temp files are not initialized when the file is created and therefore the disk space is not pre-allocated in the file system.

[former_member204618 · Accepted solution]

Hi Yves,

I don't believe this is applicable in our case :-

-rw-r-----   1 orasid     dba        2147491840 Jun 28 06:01 /oracle/SID/sapdata1/temp_1/temp.data1
-rw-r-----   1 orasid     dba        2147491840 Jun 30 13:37 /oracle/SID/sapdata1/temp_2/temp.data2
-rw-r-----   1 orasid     dba        2147491840 Jun 30 13:37 /oracle/SID/sapdata1/temp_3/temp.data3
-rw-r-----   1 orasid     dba        1073750016 Jul  4 08:31 /oracle/SID/sapdata2/temp_5/temp.data5
-rw-r-----   1 orasid     dba        1073750016 Jul  4 05:51 /oracle/SID/sapdata2/temp_6/temp.data6
-rw-r-----   1 orasid     dba        3221233664 Jun 30 13:37 /oracle/SID/sapdata3/temp_4/temp.data4

Each file is created as specified in brtools on the file system, so won't grow bigger than it currently is.

Thanks

Craig

[ACE-SAP · Accepted solution]

Hi Craig,

Maybe sparse files does not exist on Hp-Ux, but in fact the files are set at FS level with the same size as in Oracle DDIC.

So this might be a bug of the BrTools version you are using even if it is the latest one available.

You could so disable that rule as stated in note 849484

Update sapsr3.DBCHECKORA Set ACTIVE='N' where TYPE='DBA' and PARAM='CRITICAL_TABLESPACE';

Commit;

-- Refresh buffer on every SAP instance => /$tab DBCHECKORA

Regards

[former_member204618 · Accepted solution]

Hi Yves,

I have deactivated this check condition for the time being.

I might re-enable if we decide to change to kernel 721 at a later date.

Cheers

Craig

[Former Member]

if you will use newest version of BR Tool you can be sure that temp file cant overload FS, new versions count with maximum size of files