Oracle Database Discussions

Hi
This follows on from a post yesterday and the help I received from a guru here.

After db restore, receive this error in Autoconfig

Uploading Context file and its templates to the database...FAILED
StackTrace:
java.lang.Exception: oracle.apps.ad.autoconfig.oam.InDbCtxFileException: Excepti
on : Error executng BEGIN fnd_gsm_util.append_ctx_fragment(:1,:2,:3); END;: 1; O
racle error -25153: ORA-25153: Temporary Tablespace is Empty has been detected i
n FND_GSM_UTIL.APPEND_CTX_FRAGMENT.


We added a temp file, eventually, and got the db up.

I would like to know how to avoid this in the future. I think there is something wrong with my control file creation script (taken from our Prod system). It ends this way

ALTER TABLESPACE TEMP ADD TEMPFILE '/oradbi2/oracle/testdata/tmp04.dbf'
SIZE 2040M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradbi2/oracle/testdata/tmp03.dbf'
SIZE 2040M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradbi2/oracle/testdata/tmp02.dbf'
SIZE 2040M REUSE AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradbi2/oracle/testdata/tmp01.dbf'
SIZE 2000M REUSE AUTOEXTEND OFF;

Perhaps this would result in my having no temp files in the new TEMP tablespace? I really dont know ....

For the record, tempfiles should, or should not be, autoextend?

I am still worried why OEM could not see any of these temp files, yet they were there on the server.

I would like to craft my controlfile creation script so that it works without giving me any failures.

Many thanks,
DA

This is the return from the alert log at the moment we were running the ALTER TABLESPACE TEMP ADD TEMPFILE commands from the controlfile

ORA - 1109 = db not open

Does this suggest that we should add the temp files after opening the db?

ALTER DATABASE OPEN RESETLOGS

ORA-1507 signalled during: ALTER DATABASE OPEN RESETLOGS...

Tue Nov 11 17:52:20 2008

ALTER TABLESPACE TEMP ADD TEMPFILE '/oradbi2/oracle/testdata/tmp04.dbf'

SIZE 2040M REUSE AUTOEXTEND OFF

ORA-1109 signalled during: ALTER TABLESPACE TEMP ADD TEMPFILE '/oradbi2/oracl...

Tue Nov 11 17:52:20 2008

ALTER TABLESPACE TEMP ADD TEMPFILE '/oradbi2/oracle/testdata/tmp03.dbf'

SIZE 2040M REUSE AUTOEXTEND OFF

ORA-1109 signalled during: ALTER TABLESPACE TEMP ADD TEMPFILE '/oradbi2/oracl...
Tue Nov 11 17:52:20 2008

ALTER TABLESPACE TEMP ADD TEMPFILE '/oradbi2/oracle/testdata/tmp02.dbf'

SIZE 2040M REUSE AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M

ORA-1109 signalled during: ALTER TABLESPACE TEMP ADD TEMPFILE '/oradbi2/oracl...
Tue Nov 11 17:52:20 2008

ALTER TABLESPACE TEMP ADD TEMPFILE '/oradbi2/oracle/testdata/tmp01.dbf'

SIZE 2000M REUSE AUTOEXTEND OFF

ORA-1109 signalled during: ALTER TABLESPACE TEMP ADD TEMPFILE '/oradbi2/oracl...
Tue Nov 11 17:52:34 2008

Shutting down instance: further logons disabled

Shutting down instance (immediate)

Standard input

=================

I should add:

Alter running the @controlfile.sql, I always see the “control file created”. But then I always do this:



RECOVER DATABASE USING BACKUP CONTROLFILE UNITL CANCEL



I then cancel immediately



I then use the ALTER DATABASE OPEN RESETLOGS and the dn opens fine.



Perhaps at this stage, before running atutoconfig, I should then run the ALTER TABLESPACE TEMP ADD TEMPFILE commands ? ? ?

DA

Edited by: Dan A on Nov 12, 2008 11:24 AM