different name of directory and file in it

[Former Member]

Hello

I did an mistake (different name of directory and file in it) and typed a command like:

ALTER TABLESPACE psapsr3 ADD DATAFILE u2018/oracle/KL1/sapdata3/sr3_16/sr3.data15u2019 SIZE 20000M

AUTOEXTEND ON NEXT 1000M MAXSIZE 30000M;

It is a test system. I only doubt if I have to reinstall everything from scratch or I can continue

II wander what is the maximum size XYZ of data file which can be issued by a command:

ALTER TABLESPACE psapsr3 ADD DATAFILE u2018/oracle/KL1/sapdata3/sr3_16/sr3.data16u2019 SIZE XYZM

AUTOEXTEND ON NEXT 1000M MAXSIZE XXXXM;

Thank you in advance

[Former Member · Accepted solution]

Thank you very much for the answer

I would have just one more question.

Could it be that due to the fact one file system ( /oracle/KL1/sapdata2) gets full while other have a lot of space import (homogeneous system copy) gets stucked.

I doubt in this but still?

[stefan_koehler · Accepted solution]

Hello Jan,

the import can be "stuck" - if the archive log filesystem (or flash recovery area) is filled up top 100%.

If your "data" filesystem is full and oracle tries to extend a data file you usually get an ORA-01237 or/and ORA-01653.

Regards

Stefan

[volker_borowski2 · Accepted solution]

Hi Stefan,

it might suprise you, but you can get an archiver stuck for a full sapdata as well.

This can happen, if a controlfile (i.e. on sapdata1) can't grow to insert the entry for the archivelog into the history.

There have been some old installations, distributing the controlfile to sapdata1,2,3.

Volker

[stefan_koehler · Accepted solution]

Hello Volker,

oh really? Thanks for that information - i have never seen this behavior even if all control files were located in sapdata file systems.

The data file extend size is usually large and if it fails - there will be some free MB in the filesystem for extending the control file with a few KBs ... also all 3 file systems need to be filled up to 100% the same time - normally if the update process fails and is disabled with an ORA-01237 or/and ORA-01653 you take a look at it

Thanks for adding this information too.

Regards

Stefan

[volker_borowski2 · Accepted solution]

Hi Stefan,

no, a single sapdata is enough for that. The controlfiles would not be in sync, so one member stopped from growing is sufficiant to stuck the system. In general it hits sapdata1 with that condition, when a sort operation blows up temp sparse files.

So you would not see anything in DB02 changed at all, and the filesystem is suddenly full after a big temp operation.

I agree, it is a rare condition (got that only once in 15 years sapbasis).

I was lucky enough to look into the alertlog, got the archiver stuck message, and checked the filesystems, but ...

... found sapdata1 to be 100%. It was right after oracle 8->9, so I wasn't really aware of saprse tempfiles then.

Volker

[stefan_koehler]

Hello Jan,

no need to "reinstall" anything - you can go on with the current file location. To be honest i also never understood why SAP creates subdirectories for every data-/tempfile (maybe some historical reasons).

But if you want to correct it anyway - you can proceed this way:

SQL> shutdown immediate;
shell> mv /oracle/KL1/sapdata3/sr3_16/sr3.data15 /oracle/KL1/sapdata3/sr3_16/sr3.data16
SQL> startup mount;
SQL> alter database rename file '/oracle/KL1/sapdata3/sr3_16/sr3.data15' to '/oracle/KL1/sapdata3/sr3_16/sr3.data16';
SQL> alter database open;

The maximum datafile size depends on the database blocksize:

Regards

Stefan