Re: Created a tablespace in another database file

"Peter van Rijn" <p.vanrijnREMOVE_at_THISzhew.nl> wrote in message news:uvba62os4goeed_at_corp.supernews.com...
> Just tried the following:
>
> [1] created tablespace peter and datafile peter.dbf
> [2] connected to second instance/database
> [3] tried to create tablespace peter and use same datafile
> [4] ERRORS:
> ORA-01119: error in creating database file '/oradata/ght/peter.dbf'
> ORA-27086: skgfglk: unable to lock file - already in use
> HP-UX Error: 13: Permission denied
> Additional information: 8
> [5] shutdown first instance and tried step [3] again
> [6] tablespace succesfully created
> [7] started first instance again
> [8] ERROR:
> Database mounted.
> ORA-01157: cannot identify/lock data file 23 - see DBWR trace file
> ORA-01110: data file 23: '/oradata/ght/peter.dbf'
>
> Ergo: you can reuse a datafile that is being used by a second database, IF
> that database is down. If you did this the first database cannot be
started
> again.
>
> Solution:
>
> in first database:
>
> alter database datafile 'xxxxx' offline drop;
> alter database open;
> drop tablespace yyyy;
> create tablespace yyyy datafile 'the_right_path_to_datafile' size ...
>
> in secodn database:
>
> drop tablespace yyyy;
>
>
> regards,
> Peter
>

I'm unsure what the OP has actually done but I can only assume it's something such as Peter as highlighted, which could also plausibly happen with the database opened but the datafiles offline in question offline (and of course via the use of the REUSE clause)

Problem with the above recovery is that you'll lose all the data in the datafile.

The last database to "acquire the datafile" should be hunky dory as it was the last database to update the file header. All data as currently put in here by the last database should be fine as this file now belongs to this database.

The previous database that had it's datafile "nicked" unfortunately has lost it's data but should recoverable by restoring the datafile from backup and recovering the poor thing.

I've got to admit it's never happened to me before but hey, there's always the first time.

Cheers

Richard Received on Tue Dec 10 2002 - 05:49:21 CST