Help help

[chikkodi]

Hi,

I am getting the DBA gitters here.

On our development database, I have lost the temp datafile, temp01.dbf. and users datafile users01.dbf and I don't have a backup of it. Please dont ask me why I did this... It just happened before I realized what I was doing ...

Is there anyway I can recreate it ? I tried to ftp the temp01.dbf from another database of the same name, but its giving an error saying "not the same database id".

Please help !!

[contactrb]

No problem with Temp datafile.

Just drop the tablespace TEMP and recreate all the datafiles resides in that.

Did you allocate USERS tablespace to any user. I that is the case, if your database is running in archive mode, load the old user datafile and start the recovery based on tablespace .

[chikkodi]

Thanks for your input.

Here is what I want to do in order to recreate the temp datafile:
Mount the database
Take the tablespace offline
Open the database
Create the datafile

For that I started off like this:

svrmgrl> connect internal
connected
svrmgrl> startup nomount;
svrmgrl> alter database mount;
svrmgr>alter tablespace TEMP offline immediate;

here its giving me an error ORA-01109 - Database not open.

but if I try to open the database, it does not allow me to because of the missing datafile

Am I doing anything wrong here ?

[pando]

well you havent said if you are in archive log or not...

[chikkodi]

Hurray !

The following worked:

svrmgrl> connect internal
svrmgrl> startup nomount;
svrmgrl> alter database mount;
svrmgrl> alter database datafile '/data/dbname/temp01.dbf' offline;
svrmgrl> alter database datafile '/data/dbname/users01.dbf' offline;
svrmgrl> alter database create datafile '/data/dbname/temp01.dbf';
svrmgrl> alter database create datafile '/data/dbname/users01.dbf';
svrmgrl> alter database open;

As I did not have any users in the users tablespace, recreating the users tablespace will not affect me (hopefully.. you never know Oracle). Otherwise, I would have been in trouble.

Thanks for all your answers.
DBA gitters are gone ..... for now....