| tablespace recovery [message #56670] |
Wed, 16 April 2003 05:30  |
psmyth
Messages: 81
Registered: October 2002
|
Member |
|
|
I have a customer who 'accidentally' deleted a tablespace.
The underlying datafile still exists.
Anyone know of a 'quick' way of getting the db to open the tablespace again?
I have tried some tests on a play database, but haven't got anywhere:
-running a 'create controlfile' recovery fails because although the datafile is now known to the database again, it still doesn't know about the tablespace it belongs to.
-creating the tablespace again, and specifying the datafile with 'reuse' does, as you probably expect, overwrite the datafile header and you are left with an 'empty' tablespace.
I can't find anything in metalink relating to this (well, how many people 'accidentally' drop a tablespace!).
They have been able to recover from an export, but I figure there must be some simple way to do this... nothings been lost bar the tablespace details in the database... should be able to add it again somehow.
So if you've done this before, please let me know, as I'm feeling somewhat dumb here ;-)
cheers
paul
|
|
|
|
| Re: tablespace recovery [message #56672 is a reply to message #56670] |
Wed, 16 April 2003 06:20  |
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
|
Senior Member |
|
|
Hi,
what about to shutdown DB, copy the originally file to another directory and start up . Than create tablespace again, stop the Database , move old datafile back on originally point and start up the database ??
This may be an dirty way, and I havn't tryed this before .....
ciao
Uwe
|
|
|
|
| Re: tablespace recovery [message #56673 is a reply to message #56672] |
Wed, 16 April 2003 07:02 |
psmyth
Messages: 81
Registered: October 2002
|
Member |
|
|
nice idea... and worth trying... but the RDBMS is a bit too smart to let me get away with that:
upon opening the database...
.
.
.
Database mounted.
ORA-01122: database file 3 failed verification check
ORA-01110: data file 3: '/u01/oracle/oradata/bluhippo/data01.dbf'
ORA-01203: wrong incarnation of this file - wrong creation SCN
And the only workaround for that one is to... restore from backup, which puts me back at square one.
Thanks for the idea though, I like the way you think ;-)
cheers
paul
|
|
|
|
| Re: tablespace recovery [message #56699 is a reply to message #56670] |
Thu, 17 April 2003 06:15 |
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
|
Senior Member |
|
|
Hi Paul,
just take a look on this thread .... here's one suggestion to use the "old" datafile after deleting the tblsp.
Registering is for free
They use the REUSE Clause, but I did not know if the file is empty after this
regards
Uwe
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
