f



Urgent help needed. Datafile dropped from OS. Please help.

Hi all
Somebody just dropped an Oracle datafile from OS commands in our
development RAC. The tablespace isn't that important but i am afraid
RAC may be down. Please suggest how can I get rid of this tablespace.
I am on 10.2.0.4 RAC environment with two nodes, in noarchivelog mode.
When I to drop the tablespace, it gives me this error:
ORA-01116: error in opening database file 24
ORA-01110: data file 24: '/oracle/undo01/S0031001_X.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory

Please help. please help.

gtcol
0
cx4gtcol (10)
11/18/2008 2:14:39 PM
comp.databases.oracle.server 22978 articles. 1 followers. Post Follow

9 Replies
17963 Views

Similar Articles

[PageSpeed] 8

dbagtcol wrote:

> Hi all
> Somebody just dropped an Oracle datafile from OS commands in our
> development RAC. The tablespace isn't that important but i am afraid
> RAC may be down. Please suggest how can I get rid of this tablespace.
> I am on 10.2.0.4 RAC environment with two nodes, in noarchivelog mode.
> When I to drop the tablespace, it gives me this error:
> ORA-01116: error in opening database file 24
> ORA-01110: data file 24: '/oracle/undo01/S0031001_X.dbf'
> ORA-27041: unable to open file
> Linux-x86_64 Error: 2: No such file or directory


Oh boy! That's the undo tablespace! You say that "tablespace isn't that
important"? You have never done  DB recovery, have you? Here are some
products that may come handy in the future:

http://www.symantec.com/business/netbackup
http://www.emc.com/products/detail/software/networker.htm
http://www-01.ibm.com/software/tivoli/products/storage-mgr/
http://tinyurl.com/5caksw
http://www.tandbergdata.com/us/en/products/search-result/?action=2&ref=169

This time you are, as Lewis Black would put it, red, white and messed up.

-- 
http://mgogala.freehostia.com
0
gogala.mladen (1153)
11/18/2008 2:29:41 PM
On Nov 18, 9:14=A0am, dbagtcol <cx4gt...@gmail.com> wrote:
> Hi all
> Somebody just dropped an Oracle datafile from OS commands in our
> development RAC. The tablespace isn't that important but i am afraid
> RAC may be down. Please suggest how can I get rid of this tablespace.
> I am on 10.2.0.4 RAC environment with two nodes, in noarchivelog mode.
> When I to drop the tablespace, it gives me this error:
> ORA-01116: error in opening database file 24
> ORA-01110: data file 24: '/oracle/undo01/S0031001_X.dbf'
> ORA-27041: unable to open file
> Linux-x86_64 Error: 2: No such file or directory
>
> Please help. please help.
>
> gtcol

Check to see if the tablespace is still defined to the system.  If it
is then use the alter tablespace drop datafile command to drop the
missing file.

Next use drop tablespace including contents to remove the tablespace.

HTH -- Mark D Powell --
0
Mark.Powell (1630)
11/18/2008 2:36:44 PM
On Tue, 18 Nov 2008 06:36:44 -0800, Mark D Powell wrote:

> Check to see if the tablespace is still defined to the system.  If it is
> then use the alter tablespace drop datafile command to drop the missing
> file.
> 
> Next use drop tablespace including contents to remove the tablespace.

Have you noticed that the file name contains the word "undo"?

-- 
Mladen Gogala
http://mgogala.freehostia.com
0
gogala.mladen (1153)
11/18/2008 2:38:53 PM
On Nov 18, 9:14=A0am, dbagtcol <cx4gt...@gmail.com> wrote:
> Hi all
> Somebody just dropped an Oracle datafile from OS commands in our
> development RAC. The tablespace isn't that important but i am afraid
> RAC may be down. Please suggest how can I get rid of this tablespace.
> I am on 10.2.0.4 RAC environment with two nodes, in noarchivelog mode.
> When I to drop the tablespace, it gives me this error:
> ORA-01116: error in opening database file 24
> ORA-01110: data file 24: '/oracle/undo01/S0031001_X.dbf'
> ORA-27041: unable to open file
> Linux-x86_64 Error: 2: No such file or directory
>
> Please help. please help.
>
> gtcol

Open a service request with Oracle Support.
Don't waste time and don't make any more changes without getting a
good backup of what you have.
Btw, some existing oracle processes on the server may still have
handles on the file, so you might still be able to recover the dropped
file.
Have your users NOT log off of their sessions.

-bdbafh
0
lsshepler (2)
11/18/2008 8:49:37 PM
"dbagtcol" <cx4gtcol@gmail.com> a �crit dans le message de news: 
044b0c9a-6708-4782-9428-95e943d7ed90@s1g2000prg.googlegroups.com...
> Hi all
> Somebody just dropped an Oracle datafile from OS commands in our
> development RAC. The tablespace isn't that important but i am afraid
> RAC may be down. Please suggest how can I get rid of this tablespace.
> I am on 10.2.0.4 RAC environment with two nodes, in noarchivelog mode.
> When I to drop the tablespace, it gives me this error:
> ORA-01116: error in opening database file 24
> ORA-01110: data file 24: '/oracle/undo01/S0031001_X.dbf'
> ORA-27041: unable to open file
> Linux-x86_64 Error: 2: No such file or directory
>
did you try to comment undo_tablespace in int.ora
and start the database ? 


0
nobody (5159)
11/18/2008 8:53:35 PM
dbagtcol wrote:
> Hi all
> Somebody just dropped an Oracle datafile from OS commands in our
> development RAC. The tablespace isn't that important but i am afraid
> RAC may be down. Please suggest how can I get rid of this tablespace.
> I am on 10.2.0.4 RAC environment with two nodes, in noarchivelog mode.
> When I to drop the tablespace, it gives me this error:
> ORA-01116: error in opening database file 24
> ORA-01110: data file 24: '/oracle/undo01/S0031001_X.dbf'
> ORA-27041: unable to open file
> Linux-x86_64 Error: 2: No such file or directory
> 
> Please help. please help.
> 
> gtcol
Sounds like you have lost the undo tablespace (assuming that it is the 
only one, and that the filenames mean anything at all). You are not in 
archivelog mode.

Restore your latest cold backup. Changes made since then will be lost.

Then mount the database and take a full cold RMAN backup. Then put the 
database into archivelog mode and take a full hot RMAN backup. Look at 
the documentation about including controlfile and spfile.

Then take regular backups of database plus archivelog.

The backup and recovery guide on Tahiti will be useful.

Palooka
0
nobody (5159)
11/18/2008 9:55:38 PM
On Nov 18, 1:55=A0pm, Palooka <nob...@nowhere.com> wrote:
> dbagtcol wrote:
> > Hi all
> > Somebody just dropped an Oracle datafile from OS commands in our
> > development RAC. The tablespace isn't that important but i am afraid
> > RAC may be down. Please suggest how can I get rid of this tablespace.
> > I am on 10.2.0.4 RAC environment with two nodes, in noarchivelog mode.
> > When I to drop the tablespace, it gives me this error:
> > ORA-01116: error in opening database file 24
> > ORA-01110: data file 24: '/oracle/undo01/S0031001_X.dbf'
> > ORA-27041: unable to open file
> > Linux-x86_64 Error: 2: No such file or directory
>
> > Please help. please help.
>
> > gtcol
>
> Sounds like you have lost the undo tablespace (assuming that it is the
> only one, and that the filenames mean anything at all). You are not in
> archivelog mode.
>
> Restore your latest cold backup. Changes made since then will be lost.
>
> Then mount the database and take a full cold RMAN backup. Then put the
> database into archivelog mode and take a full hot RMAN backup. Look at
> the documentation about including controlfile and spfile.
>
> Then take regular backups of database plus archivelog.
>
> The backup and recovery guide on Tahiti will be useful.
>
> Palooka

0
joel-garry (4553)
11/18/2008 11:50:50 PM
On Nov 18, 6:38 am, Mladen Gogala <gogala.mla...@gmail.com> wrote:
> On Tue, 18 Nov 2008 06:36:44 -0800, Mark D Powell wrote:
> > Check to see if the tablespace is still defined to the system.  If it is
> > then use the alter tablespace drop datafile command to drop the missing
> > file.
>
> > Next use drop tablespace including contents to remove the tablespace.
>
> Have you noticed that the file name contains the word "undo"?

Have you noticed the word "development" in the OP?  :-)

This will give some weight to the argument that even development
databases are somebodies production, but even so, there is more
latitude for development environments, right?  There ought to be some
procedures for this sort of meltdown.  Privilege given to developers
is another canonball of waxy worms.

On the other hand, the lesson they really need to learn is they need
some capable DBA if they are using RAC!

jg
--
@home.com is bogus.
"Nobody was trapped on the escalator." - Local ABC affiliate
newsreader makes joke that no one gets, after story about 5 people
injured on Universal City Walk escalator reversal.  This distracts
other newsreader, who then says "Moron college students... more on the
college students who..."
0
joel-garry (4553)
11/18/2008 11:51:53 PM
On Nov 18, 6:38=A0am, Mladen Gogala <gogala.mla...@gmail.com> wrote:
> On Tue, 18 Nov 2008 06:36:44 -0800, Mark D Powell wrote:
> > Check to see if the tablespace is still defined to the system. =A0If it=
 is
> > then use the alter tablespace drop datafile command to drop the missing
> > file.
>
> > Next use drop tablespace including contents to remove the tablespace.
>
> Have you noticed that the file name contains the word "undo"?
>
> --

This is a test post to see if the removing mladen's link blocks the
post.

It seems google groups has a problem with innocuous links now.

jg
--
@home.com is bogus.
what a stupid reason to block a post.
0
joel-garry (4553)
11/19/2008 12:03:33 AM
Reply: