PSAPTEMP tablespace

HI all,

I am trying to run an Import of the client and its getting stuck, so before running the import i started the Database Optimizer Statistics and when i checked the system log i was getting ORA-25153: Temporary Tablespace is Empty and then i checked DB02 and saw PSAPTEMP did not have any space allocated ,as i have restored this system and created a new Control file , so in the process the PSAPTEMP tablespace has affected.

PSAPTEMP 0,00 0,00 0 NO 0,00 0,00 0 0 0 0 ONLINE TEMPORARY

Can someone please help me resolve this issue.

Thanks

SAP Managed Tags:
Windows Server

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (0)

Answers (4)

I am thankful to all who contributed ..

Really appreciate all the help.

Cheers.

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Were you able to solve your problem?

Regards

Uday

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi,

For better understanding & help to resolve the issue please let me know,

How did you restore your system?? means using BR*Tools or manually???

Did you use Offline backup to restore the system??

Thanks,

Shambo

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi Shambo,

I have used manual procedure to restore the backup and created control files with Alter Database backup controlfile to trace, and the executed that as @C:\abc.txt which created new control files in OrigLogA , B and Sapdata1.

and i have used Offline backup.

and for this file name , can u pls confirm me how to give the file name and the full path ie for Windows.

Thanks

Hi,

Check control trace file which you have generated on your source system. See the path of temp file.

& Also see the statement like

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '<Drive_Leter>:\oracle\<Source_SID>\sapdata4\sr3700_16\temp01.data' SIZE 2120M REUSE AUTOEXTEND ON NEXT 20971520 MAXSIZE 10000M;

Now copy the temp file from the your offline backup which you have restored to path *F:\oracle\QAS\sapdata4\sr3700_16\*

Now go to sql prompt & execute below query

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE 'F:\oracle\QAS\sapdata4\sr3700_16\temp01.data' SIZE 2120M REUSE AUTOEXTEND ON NEXT 20971520  MAXSIZE 10000M;

commit;

This should resolve your pproblem.

Thanks

Shambo

Hi Shambo,

Thanks a lot for the reply,

Well , i have checked that control trace file , and the also checked the path of the Temp file , it was in sapdata2 , and then i executed the SQL statement that u have asked me to run.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> ALTER TABLESPACE PSAPTEMP ADD TEMPFILE'F:\ORACLE\QAS\SAPDATA2\TEMP_1\TEMP.D

ATA1' SIZE 2120M REUSE AUTOEXTEND ON NEXT 20971520 MAXSIZE 10000M;

Tablespace altered.

SQL>

SQL> commit;

Commit complete.

From DB02 :PSAPTEMP 0,00 0,00 0 NO 0,00 0,00 0 0 0 0 ONLINE TEMPORARY

Do i need to re start the system for this to take affect..as after running this i went to DB02 and that was still PSAPTEMP 0 Size.

Thanks .

HI Shambo,

The good news is , when i have tried to check the tablespace PSAPTEMP , its showing the change after running the Alter tablespace statement. But sadly its not showing that in DB02. Would anyone know what could be the reason for that.

Thanks

Now copy the temp file from the your offline backup which you have restored to path **F:\oracle\QAS\sapdata4\sr3700_16\**

Did you copy temp file from offlice backup to the said path before executing the script?

Try by restarting the database once.

Please execute the below query & paste the output.

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

Thanks,

Shambo

Hi Shambo,

Thanks for Prompt response , well i did not restore that file from offline backup as it was already there , and the correct path for that was,

'F:\ORACLE\QAS\SAPDATA2\TEMP_1\TEMP.DATA1' <the one i mentioned earlier was only example path> and when i saw this datafile , i executed the SQL statement u asked me to run..

And also , i need to make confirm from u , the size we mentioned , and when will it extend itself and other paramters of that SQL statement please. Thanks

I ran the command and this is the output.

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Jul 8 11:56:46 2009

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> select file_name,tablespace_name from dba_data_files where tablespace_name

='PSAPTEMP';

no rows selected

SQL>

Thanks

Go to DB02 & click on refresh button. It will refreshes the database & then check wheather datafile is added or not.

Please paste the alter statement from your trace file.

shambo

Hi Shambo,

I really appreciate all ur help.

I have pressed refresh at DB02 and its running a background job now..and is active will let u know soon. and secondly i m pasting the Alter Tablespace from the Control Trace file

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE 'F:\ORACLE\QAS\SAPDATA2\TEMP_1\TEMP.DATA1'
     SIZE 2000M REUSE AUTOEXTEND ON NEXT 20971520  MAXSIZE 10000M;

But let me clear onething , when i generated the control file with this specific Trace file , this is the content from Trace file that i have used ,which doesnt include this Tablespace.

Startup nomount

CREATE CONTROLFILE SET DATABASE "QAS" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 255
    MAXLOGMEMBERS 3
    MAXDATAFILES 254
    MAXINSTANCES 50
    MAXLOGHISTORY 11680
LOGFILE
  GROUP 1 (
    'D:\ORACLE\QAS\ORIGLOGA\LOG_G11M1.DBF',
    'D:\ORACLE\QAS\MIRRLOGA\LOG_G11M2.DBF'
  ) SIZE 50M,
  GROUP 2 (
    'D:\ORACLE\QAS\ORIGLOGB\LOG_G12M1.DBF',
    'D:\ORACLE\QAS\MIRRLOGB\LOG_G12M2.DBF'
  ) SIZE 50M,
  GROUP 3 (
    'D:\ORACLE\QAS\ORIGLOGA\LOG_G13M1.DBF',
    'D:\ORACLE\QAS\MIRRLOGA\LOG_G13M2.DBF'
  ) SIZE 50M,
  GROUP 4 (
    'D:\ORACLE\QAS\ORIGLOGB\LOG_G14M1.DBF',
    'D:\ORACLE\QAS\MIRRLOGB\LOG_G14M2.DBF'
  ) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  'F:\ORACLE\QAS\SAPDATA1\SYSTEM_1\SYSTEM.DATA1',
  'F:\ORACLE\QAS\SAPDATA3\UNDO_1\UNDO.DATA1',
  'F:\ORACLE\QAS\SAPDATA1\SYSAUX_1\SYSAUX.DATA1',
                ;;;;;;;;;;;; More in between;;;;;;;;
  'F:\ORACLE\QAS\SAPDATA1\SR3_1\SR3.DATA1',
  'F:\ORACLE\QAS\SAPDATA1\SR3_2\SR3.DATA2',
  'F:\ORA
  'F:\ORACLE\QAS\SAPDATA1\SR3USR_1\SR3USR.DATA1'
CHARACTER SET UTF8
;

Hi Shambo,

Good news is that , the job has finished and it has updated the PSAPTEMP (thanks to u).

PSAPSR3	41.020,00	16,94	100	YES	200.000,00	158.996,94	21	20	156.893	197.589	ONLINE	PERMANENT
PSAPSR3700	55.480,00	266,13	100	YES	160.000,00	104.786,13	35	16	801	10.599	ONLINE	PERMANENT
PSAPSR3USR	20,00	18,13	9	YES	10.000,00	9.998,13	0	1	29	29	ONLINE	PERMANENT
PSAPTEMP	2.120,00	2.120,00	0	YES	10.000,00	10.000,00	0	1	0	0	ONLINE	TEMPORARY
PSAPUNDO	9.120,00	8.966,56	2	YES	10.000,00	9.846,56	2	1	11	34	ONLINE	UNDO
SAPTEMP	10.500,00	10.500,00	0	NO	10.500,00	10.500,00	0	1	0	0	ONLINE	TEMPORARY
SYSAUX	260,00	19,31	93	YES	10.000,00	9.759,31	2	1	875	1.810	ONLINE	PERMANENT
SYSTEM	840,00	7,13	99	YES	10.000,00	9.167,13	8	1	1.204	2.768	ONLINE	PERMANENT

But i think , trying this process , i have created another SAPTEMP temporary Tablespace as well, Should i leave it like that , or delete it , can u pls tell me how would i delete it.

For the size of PSAPTEMP tablespace , i have used the size that u sent me and not the one that I have sent u from Control trace file..just FYI only..

Thanks for help..

Hi,

We did not use "create tablespace statement" so SAPTEMP is not due to our execution of query. It maybe due to previously you tryed to add datafile & insted of that you had created SAPTEMP tablespace mistakenly.

So don't delete that tablespace let it be as it is.

Now try to import your client. It should work.

If this resolve your issue then please rewad

Thanks,

Shambo

Ya, while starting the database from generated control file we only select SATRTUP NOMOUNT.....CHARACTER SET UTF8 rest part we delete.

This statement is not required while we start the database.\

thanks,

shambo

If you wish use brtools to drop SAPTEMP temporary tablespace. But having that there should not cause any problem but you will loose that much space there.

Hi Shambo,

Thanks for the reply..i have already rewarded. now that being said, due to client import , i have tried to do a Remote Client copy and while running that i have got a list of Tables that do not match within the PRD and QAS systems.

I have also tried to exclude these tables from SCC9 , Edit Expert Settings and then Exclude, but its still not adding and i m getting

Table doesnt Exist,

A900
A910
A911
FAGLFLEXC
FAGLFLEXD
KOTH902
KOTN900
KOTN901
KOTN903
KOTN904
ZDAILY_PLAN
ZFERTMAT
ZLIPS
ZLIPS2
ZLIPS_NEW
ZLOG_D0_TBL

Please help in this regard.

Thanks

Hi,

Please close this thread if your PSAPTEMP problem is resolved.

See my reply for this problem in your already opened thread

Shambo

Add datafiles to tablespace PSAPTEMP through brtools

Regards

Uday

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi Uday,

Thanks for ur reply, Would u be helpful enough to tell me how can i do that.

cheers.

Hi ,

Please refer http://help.sap.com/saphelp_nw70/helpdata/en/1b/d20f6fe45a9e43b1856ea1b52c9612/content.htm

Regards,

Nibu

Hi Nibu,

I have gone to the link that u have attached..Its really helpful link..But i m stuck when i giving the path for the datafile to be added , i have mentioned the size, the increase parameter , and extension., i am on Windows 2003 64 bit, Oracle 10.2.0.2 and ECC 6.0 just for the better understanding ..When i m giving the path in option 3 of Table space extend,

temp01 'F:\oracle\QAS\sapdata4\sr3700_16\temp01.data'] , i think i m making some mistake here. has it to be / or \

and do i need to create some folder in one of the datafiles, as i m getting some mkdir failed.

Please help me in this regard.

Folder up to datafiles need to be created manually.

In ur case folder should exist upto F :\oracle \QAS\sapdata4\sr3700_16 must be there.

Check if any permission missing issues and if so give all necessary permissions

Please send the error text if still the issue persists.

Regards,

Nibu .

Ask a Question

Top Q&A Solution Author

User

Count