CX_SY_OPEN_SQL_DB !!

[former_member613886]

Dear Experts ,

while copying org objects in tx ec02 am facing error below attached

i searched alot on sap notes to solve the issue but no luck

please provide me with any reference to solve this issue

thanks alot

Yasser

EC02.txt.zip

[Former Member · Accepted solution]

Hello Yasser,

1. Please turn off AUTOEXTEND on your tablespaces/datafiles.

2. Please check your filesystem space (df -g). Is sapdata2 close to or at 100% ?

Your problem can occur when you have "autoextend on" and you end up with a overallocation on the filesystem.

KR,

Amerjit

[former_member613886 · Accepted solution]

Dear Amerjit,

could you please tell me what's the effect of turning off autoextend ??

concerning file system it's 45 % used only while when checking tablespace from db02

it's 96% used

can you please guide me to solve this issue as i don't know if it's a tablespace issue or should i disable auto extend as u told me !?.

thanks ,

yasser

[Former Member · Accepted solution]

Hello Yasser,

The effect of turning of autoextend is more manual intervention/management of your tablespace freespace. Easily mitigated if you have good monitoring of your DB/Tablespaces.

Please try these queries.

1. List the state of the datafiles for PSAPSR3740 (pls post here)

select file_name, status from dba_data_files where tablespace_name = 'PSAPSR3740' ;

2. List all datafiles in sapdata2 that are autoextensible (pls post here).

select FILE_NAME, MAXBYTES/1024/1024, AUTOEXTENSIBLE from dba_data_files where file_name like '%sapdata2%' ;

3. The space required in the filesystem if all autoextensible datafiles grew to their maxsize.

select round(sum(MAXBYTES/1024/1024/1024)) as GB from dba_data_files where AUTOEXTENSIBLE  = 'YES' and file_name like '%sapdata2%' ;

If the sum of the above statement is greater than the size of sapdata2 then you have overallocation.

4. Depending on the result of step (3), turn off autoextend for the datafiles found in sapdata2.

eg: alter database datafile '/oracle/EGD/sapdata2/sr3740_5/sr3740.data5' autoextend off ;

Repeat for all datafiles found in step (2)

5. Manually extend one datafile for PSAPSR3740

eg: alter database datafile '/oracle/EGD/sapdata2/sr3740_5/sr3740.data5' resize 30G ;

Additionally, I would suggest you run dbv for '/oracle/EGD/sapdata2/sr3740_5/sr3740.data5'

KR,

Amerjit

[former_member613886 · Accepted solution]

1. 

FILE_NAME

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

STATUS

---------

/oracle/EGD/sapdata2/sr3740_1/sr3740.data1

AVAILABLE

/oracle/EGD/sapdata2/sr3740_2/sr3740.data2

AVAILABLE

/oracle/EGD/sapdata2/sr3740_3/sr3740.data3

AVAILABLE

FILE_NAME

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

STATUS

---------

/oracle/EGD/sapdata2/sr3740_4/sr3740.data4

AVAILABLE

/oracle/EGD/sapdata2/sr3740_5/sr3740.data5

AVAILABLE

/oracle/EGD/sapdata2/sr3740_6/sr3740.data6

AVAILABLE

FILE_NAME

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

STATUS

---------

/oracle/EGD/sapdata2/sr3740_7/sr3740.data7

AVAILABLE

/oracle/EGD/sapdata2/sr3740_8/sr3740.data8

AVAILABLE

/oracle/EGD/sapdata2/sr3740_9/sr3740.data9

AVAILABLE

FILE_NAME

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

STATUS

---------

/oracle/EGD/sapdata2/sr3740_10/sr3740.data10

AVAILABLE

/oracle/EGD/sapdata2/sr3740_11/sr3740.data11

AVAILABLE

/oracle/EGD/sapdata2/sr3740_12/sr3740.data12

AVAILABLE

FILE_NAME

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

STATUS

---------

/oracle/EGD/sapdata2/sr3740_13/sr3740.data13

AVAILABLE

/oracle/EGD/sapdata2/sr3740_14/sr3740.data14

AVAILABLE

/oracle/EGD/sapdata2/sr3740_15/sr3740.data15

AVAILABLE

FILE_NAME

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

STATUS

---------

/oracle/EGD/sapdata2/sr3740_16/sr3740.data16

AVAILABLE

/oracle/EGD/sapdata2/sr3740_17/sr3740.data17

AVAILABLE

/oracle/EGD/sapdata2/sr3740_18/sr3740.data18

AVAILABLE

18 rows selected.

2.

FILE_NAME

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

MAXBYTES/1024/1024 AUT

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

/oracle/EGD/sapdata2/sr3740_18/sr3740.data18

             10000 YES

/oracle/EGD/sapdata2/sr3740_17/sr3740.data17

             10000 YES

/oracle/EGD/sapdata2/sr3740_16/sr3740.data16

             10000 YES

FILE_NAME

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

MAXBYTES/1024/1024 AUT

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

/oracle/EGD/sapdata2/sr3740_15/sr3740.data15

             10000 YES

/oracle/EGD/sapdata2/sr3740_14/sr3740.data14

             10000 YES

/oracle/EGD/sapdata2/sr3740_13/sr3740.data13

             10000 YES

FILE_NAME

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

MAXBYTES/1024/1024 AUT

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

/oracle/EGD/sapdata2/sr3740_12/sr3740.data12

             10000 YES

/oracle/EGD/sapdata2/sr3740_11/sr3740.data11

             10000 YES

/oracle/EGD/sapdata2/sr3740_10/sr3740.data10

             10000 YES

FILE_NAME

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

MAXBYTES/1024/1024 AUT

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

/oracle/EGD/sapdata2/sr3740_9/sr3740.data9

             10000 YES

/oracle/EGD/sapdata2/sr3740_8/sr3740.data8

             10000 YES

/oracle/EGD/sapdata2/sr3740_7/sr3740.data7

             10000 YES

FILE_NAME

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

MAXBYTES/1024/1024 AUT

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

/oracle/EGD/sapdata2/sr3740_6/sr3740.data6

             10000 YES

/oracle/EGD/sapdata2/sr3740_5/sr3740.data5

             10000 YES

/oracle/EGD/sapdata2/sr3740_4/sr3740.data4

             10000 YES

FILE_NAME

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

MAXBYTES/1024/1024 AUT

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

/oracle/EGD/sapdata2/sr3740_3/sr3740.data3

             10000 YES

/oracle/EGD/sapdata2/sr3740_2/sr3740.data2

             10000 YES

/oracle/EGD/sapdata2/sr3740_1/sr3740.data1

             10000 YES

18 rows selected.

3.

        GB

----------

       176

[Former Member · Accepted solution]

HI Yasser,

Thanks for the info. So is your sapdata2 >= 176Gb ?

Amerjit

[former_member613886 · Accepted solution]

Hi Amerjit ,

on executing df -g

file system for sapdata2 is 80 G with 45.58 free and 44 % used

am i on the right track to get sapdata2 size ??

or should i look for something else in the system

thanks ,

Yasser

[Former Member · Accepted solution]

Hello Yasser,

I'd just like to follow the overallocation thought through to the end.

Looking at your output only PSAPSR3740 is in SAPDATA2.

Could you please turn of autoextend on all the datafiles for PSAPSR3740 using the SQL statement I provided in step (4) in a previous reply. Kindly note, you must do it for *all* datafiles of PSAPSR3740.

After that, please resize one of the PSAPSR3740 datafile manually to 20GB. See SQL in step (5).

After you have done that, please try your failing transaction (EC02) again.

Former Member has suggested the latest SBP for Oracle 12 which is a good idea irrespective of what I'm asking you to try.

Please try the above and let us know how you get on.

Good luck and Kindest Regards,

Amerjit

[former_member613886 · Accepted solution]

Hello Amer ,

i just  resized PSAPSR3740 with 30GB then i turned off autoextend for all data files

still having same dump in ec02

waiting your kind replies

thanks in advance

yasser

[Former Member · Accepted solution]

Hi Yasser,

Could you upload the latest dump.

Is it always on the same datafile ?

If I could ask you to try two more things for me.

1. Please run a dbv on the file in question as I mentioned previously.

eg: dbv file='/oracle/EGD/sapdata2/sr3740_5/sr3740.data5' feedback=0 logfile=EGT_DBV.log

Please check the logfile for failing/corrupt pages afterwards (post output here).

2. Please ask your sys admin (AIX) to run a 'fsck' on the sapdata2 filesystem and also to run 'errpt -a' to see if i/o errors are being logged in the system log.

KR,

Amerjit

[former_member613886 · Accepted solution]

Hello ,

it's same dump , nothing changed and it's on the same datafile

After DBV command :

DBV-00600: Fatal Error - [28] [27063] [110] [0]

log file DBVERIFY: Release 12.1.0.2.0 - Production on Tue Nov 3 08:58:37 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.


DBVERIFY - Verification starting : FILE = /oracle/EGD/sapdata2/sr3740_5/sr3740.data5

nothing helpful in the log file

[Former Member · Accepted solution]

Hi Yasser,

Now this starts getting more complicated and less easy to resolve via a forum (need to be in front of screen with hands on keyboard).

1. Did your systems team run a 'fsck' on the sapdata2 filesystem ?

2. Could you run the 'dbv' on another datafile on the sapadata2 filesystem. Does this dbv run without any problems ?

3. In parallel, please open a message with those lovely people at SAP.

KR,

Amerjit

[former_member613886 · Accepted solution]

Hello Amerjit ,

i noticed in db02 while checking tablespace that '/oracle/EGD/sapdata2/sr3740_5/sr3740.data5'

has maxsize = 0 and maxblocks = 0 .

seems that this datafile don't allow any data to be inserted in

please advice for any workaround with this issue .

thanks

yasser

[Former Member · Accepted solution]

Hello Yasser,

Did your AIX team run a 'fsck' on the filesystem  and did you try a 'dbv' on another datafile in the same filesystem ?

Based on what you've tried so far it does seem like you have a corrupt/damaged datafile and if that is the case you will have to restore the datafile from the last backup where you didn't have a problem with the datafile and perform a recovery of the datafile.

KR,

Amerjit

[Former Member]

Hello Yasser,

I see the following error in the dump shared by you:

Database error text: SQL message: ORA-01115: IO error reading block from file

(block # ) ORA-01110: data file 25:  '/oracle/EGD/sapdata2/sr3740_5/sr3740.data5'

ORA-27072: File I/O error IBM AIX RISC System/6000 Error: 5: I/O error

I found a few Notes but they are not relevant for your database version i.e. 12.1.0.2.0.

546006 - Problems with Oracle due to operating system errors

147397 - ora-1115+ora-27072 for process cancel. with ctrl c

980152 - DB reports I/O error after inst of AIX 5.3 TL 05, 06, 07, 08

Kindly check if this is a permission issue and provide full permission to /oracle/EGD/sapdata2/sr3740_5/sr3740.data5 file.

If there is a patch set available for your database version please apply the same if above doesn't work.

Meanwhile, you should also log this with SAP.

Thanks.

Best Regards,

Anita

[former_member613886]

Hello Anita ,

seems to be not a permission issue as i give permission to specified directories

concerning patches could u please give me any reference on how to apply them on AIX

thanks alot for your response

yasser

[Former Member]

Hello Yasser,

Please refer to the following Note:

1915316 - Database: Patches for 12.1.0.2

It contains all details including procedure to be followed.

Thanks.

Best Regards,

Anita

[former_member613886]

Hello Anita ,

i will download patches for applying but i don't have MOPatch on my server side

where can i get it and what's procedures of starting on on server

sorry but I don't know how to proceed with that

thanks

yasser

[Former Member]

Hello Yasser,

You can download MOPatch from the following path:

https://support.sap.com/software/databases.html

Oracle -> Database Patches -> ORACLE PATCHES -> ORACLE PATCHES 12.1.0.2 -> # OS independent -> MOPatch 2.1.21

The procedure for starting the tool is detailed in the Readme file in the following path:

https://support.sap.com/software/databases.html

Oracle -> Database Patches -> ORACLE PATCHES -> ORACLE PATCHES 12.1.0.2 -> AIX 64bit -> SAP Bundle Patch 12.1.0.2.4 - 201509V2 - Readme

Thanks.

Best Regards,

Anita