unable to extend temp segment by 128 in tablespace.

[Former Member]

Hi all,

Once after DB refresh we are facing few issues in our server.

Work: Company code deletion process.

So we have a test server with a system copy from a production server with same sid and everything but different

Host name.

We are now in 2nd test cycle.

2nd cycle began with DB refresh from prod server.

Now we are facing issue with psaptemp tablespace.

Developers are running rsanaora std program , in sm37 it shows finished after 2hours but in sm21 it shows

ORA-01652: unable to extend temp segment by 128 in tablespace.

At first we had 10GB PSAPTEMP after getting this error we extended to 22GB and now also getting same error.

Kb usage and percentage usage are in '0' only.

Table-CKIS

size of this table-143GB

table maxextends - unlimited and

CKIS~0 - unlimited and also checked by setting it with 1600.

Job of this std program is to re-build the indexes.

What will be the route cause for these issues.

I have a doubt about post DB refresh activities.

but done all activities.

Is that any issue raise with post DB activities?

In SM21 it shows its a basis issue.

We are checking it from many ways.

Thanks in Advance for kings,

Kutty

[Former Member]

thanks for all

[Former Member]

hi all,

We found one issue...

After DB refresh we added the data file in psaptemp as it doesnt have after DB restore and

after we faced same issue so we just dropped tablespace and recreated psaptemp with 10GB in the same path as it

is in prod.

But we hope its the only issue.

It seems it is not recognizing temp data files.

There is some problem in header.

It seems there is no temp datafiles in oracle but it shows its there.

Definitely there will be an issue with header.

I dont know how to carry this issue with header.

Thanks,

Kutty

[Former Member]

Hi Kutty,

>> It seems there is no temp datafiles in oracle but it shows its there.

Firstly, could you verify the status of PSAPTEMP is online over DB02 -> Space -> Tablespaces -> Overview, by updating information (F8 key) or by using Br*Tools?

Please find how to refresh PSAPTEMP, below;

CREATE TEMPORARY TABLESPACE "PSAPTEMP1" TEMPFILE '/oracle/<SID>/sapdata<x>/temp_1/TEMP.DATA1' SIZE <size>M reuse autoextend off;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE PSAPTEMP1;

DROP TABLESPACE PSAPTEMP INCLUDING CONTENTS;

CREATE TEMPORARY TABLESPACE "PSAPTEMP" TEMPFILE '/oracle/<SID>/sapdata<x>/temp_1/TEMP.DATA2' SIZE <size>M reuse autoextend off;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE PSAPTEMP;

DROP TABLESPACE PSAPTEMP1 INCLUDING CONTENTS;

Then, check whether PSAPTEMP is online or not.

Best regards,

Orkun Gedik

[Former Member]

Additionally, please note that PSAPTEMP tablespace should be at least twice as large as the largest index.

Check the Note 659946 - FAQ: Temporary tablespaces, and find the title ORA-01652

Best regards,

Orkun Gedik

[Former Member]

Hi Gedik,

Superb responds yaar...

Definitely it will be helpfull for me.

but whats the sql query to check whether psaptemp is online or not?

I have done ur requested steps.

I am confused in checking online psaptemp.

Kutty

[Former Member]

Hi Orkun,

How to check whether psaptemp datafiles which i have added now is being

updated in control file and oracle header.?

How to check online psaptemp file?

BR,

Kutty

[Former Member]

Hi Kutty,

Firstly, could you verify the status of PSAPTEMP
 is online over DB02 -> Space -> Tablespaces -> Overview,
 by updating information (F8 key) or by using Br*Tools?

I already referred how to check PSAPTEMP whether online or not, at my previous message

One another way to check it, execute the statement, below on SQLplus;

select status from dba_tablespaces where tablespace_name = 'PSAPTEMP';

Additionally, you can check the datafile(s) belong the the PSAPTEMP whether online or not, by executing sql statement, below;

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

If they are offline, you can bring them online or recreate PSAPTEMP from the scratch, by using the sql statements that I've sent you at my previous message.

I hope that everything is clear, at this stage.

Best regards,

Orkun Gedik

[Former Member]

Hi Orkun,

Your answer was very helpful for my issue but i couldnt fix that issue as PSAPTEMP has not

updated in control file as well as in oracle header.

As its a Quality system i am restoring again now.

The process is going on now.

FYI,

My environment:

Same SID,OS,DB but different Hostname.

I have post DB refresh activity in my mind but kindly refresh me what are the post activity should take place

from your point of view so that issue should not arise again.

Definitely it will be helpful for me to go-ahead for secure process.

BR,

Kutty

Edited by: prathapkutty on Aug 8, 2011 9:26 AM

[Former Member]

>> I have post DB refresh activity in my mind but kindly refresh me what are the post activity should take place

from your point of view so that issue should not arise again.

Hi Kutty,

I have no idea, what files you restored in order to open the database, but find the major tasks, below;

1) Create the control files and open database, respectively

sqlplus u201C/as sysdbau201D
startup nomount
@<path>control.sql
shutdown
startup mount
recover database using backup controlfile until cancel; --> if you have offline redologs to be applied
alter database open resetlogs;
shutdown
startup
exit

2) Create PSAPTEMP, if you didn't restore its datafile(s)

3) Create/Change OP$ users

4) SAP application related tasks

Best regards,

Orkun Gedik

Edited by: Orkun Gedik on Aug 8, 2011 10:51 AM

[Former Member]

Hi Orkun,

Here what is the necessary to create control as SID's are same in source and target system.

And shall i use below note to create temp datafile?

600513 - ORA-25153 after recovery due to missing tempfiles

BR,

Kutty

Edited by: prathapkutty on Aug 8, 2011 10:04 AM

[Former Member]

>> Here what is the necessary to create control as SID's are same in source and target system.

>> And shall i use below note to create control file?

>> Same SID,OS,DB but different Hostname.

No, you don't need to create controlfiles. But, I am creating them when I refresh the system. This is my personal point of view.

Best regards,

Orkun Gedik

[Former Member]

Hi orkun,

Can i have the detailed steps for the post DB refresh activity with SQL commands , So that i can have a

checklist keenly to find the route cause now atleast.

BR,

Kutty

[Former Member]

Hi Kutty,

I already noted the minimum number of steps in order to open the database, in my previous message. You may apply different steps in order to open the SAP system regarding your policy, but I'll provide mandatory steps;

I assume that you have same SID;

To open the database, without restoring PSAPTEMP datafile(s);

sqlplus u201C/as sysdbau201D
startup mount
<if you have offline redologs to be applied>
recover database using backup controlfile until cancel;
</if you have offline redologs to be applied>
alter database open resetlogs;
shutdown
startup
exit

To create PSAPTEMP;

CREATE TEMPORARY TABLESPACE "PSAPTEMP1" TEMPFILE '/oracle/<SID>/sapdata<x>/temp_1/TEMP.DATA1' SIZE <size>M reuse autoextend off;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE PSAPTEMP1;
DROP TABLESPACE PSAPTEMP INCLUDING CONTENTS;
CREATE TEMPORARY TABLESPACE "PSAPTEMP" TEMPFILE '/oracle/<SID>/sapdata<x>/temp_1/TEMP.DATA2' SIZE <size>M reuse autoextend off;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE PSAPTEMP;
DROP TABLESPACE PSAPTEMP1 INCLUDING CONTENTS;

Then, you should create OPS$ users.

I hope that everything is clear.

Best regards,

Orkun Gedik

[Former Member]

Hi Orkun an all,

I have fixed the issue by updating the PSAPTEMP in control file by creating psaptemp using

BRTOOLS , Before that i have recreated control file.

Now everything is going fine.

I am now re-building all the largest Index in my Database.

Main monitoring will be with the index that we wants to re-building, we need to monitor the

growth of the tablespace of particular index and need to keep temp twice the size of

largest index.

If anyone facing this same issue you can very well post your thread as open.

Once again thanks for all and Orkun....

BR,

Kutty