OraFAQ Forum: Backup & Recovery » UNDO Tablespace deleted from OS level

Home » RDBMS Server » Backup & Recovery » UNDO Tablespace deleted from OS level (Windows XP, !0g, 10.2.0.1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

UNDO Tablespace deleted from OS level [message #580459]

Sun, 24 March 2013 23:53

ishika_20
Messages: 339
Registered: December 2006
Location: delhi

Senior Member

Dear All,

Operating System - WindowXP
oracle version 10.2.0.1

I was learning some recovery part in my home laptop. Database is in Archivelog, flashback mode. All of sudden, i deleted it from OS level with out taking backup of it.

When i tried to open database, it failed to start. Database is in mount mode.
while trying to open, it gives message -

Code: [Select all] [Show/ hide]

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\DB\SYSTEM01.DBF'

I tried to create on file named "UNDOTBS01.DBF" but oracle is not recognizing it.
Suggest me how can i recover it?

Regards,
Ishika

Report message to a moderator

Re: UNDO Tablespace deleted from OS level [message #580460 is a reply to message #580459]

Mon, 25 March 2013 00:55

ishika_20
Messages: 339
Registered: December 2006
Location: delhi

Senior Member

I have not taken RMAN backup or hot back. Database is in archivelog mode.

If I would taken RMAN backup then below link would help me -

Code: [Select all] [Show/ hide]

http://allappsdba.blogspot.in/2012/04/recovery-scenario-during-loss-of-undo.html

Waiting for your reply...

Report message to a moderator

Re: UNDO Tablespace deleted from OS level [message #580464 is a reply to message #580460]

Mon, 25 March 2013 01:35

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Maybe but your error message shows you lost a SYSTEM file not an UNDO file.
Anyway, the recovery method is the same one for both.

A great entry point to any lost case is the following; Marco gives recovery scenario for almost all possible cases (if not all).

Regards
Michel

Report message to a moderator

Re: UNDO Tablespace deleted from OS level [message #580467 is a reply to message #580464]

Mon, 25 March 2013 01:58

ishika_20
Messages: 339
Registered: December 2006
Location: delhi

Senior Member

Dear Michel,

Apologize...

My error is as below.

Code: [Select all] [Show/ hide]

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\DB\UNDOTBS01.DBF'

Your provided link is asking password. Can you please provide link which can be open directly?

Regards,
Ishika

Report message to a moderator

Re: UNDO Tablespace deleted from OS level [message #580472 is a reply to message #580467]

Mon, 25 March 2013 02:39

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Just register to the site.

Regards
Michel

Report message to a moderator

Re: UNDO Tablespace deleted from OS level [message #580473 is a reply to message #580467]

Mon, 25 March 2013 02:56

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Quote:

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file ORA-01110: data file 1: 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\DB\UNDOTBS01.DBF'

I was under the impression that datafile number 1 is ALWAYS the first datafile of the system tablespace. I am of course open to correction on this, but I would hace said that what you are showing is impossible (unless you have been renaming files in a most confusing manner). Can you confirm the situation with this query:

Code: [Select all] [Show/ hide]

select t.name,d.name,d.file#,d.status from v$tablespace t join v$datafile d using(ts#);

Report message to a moderator

Re: UNDO Tablespace deleted from OS level [message #580475 is a reply to message #580473]

Mon, 25 March 2013 03:00

ishika_20
Messages: 339
Registered: December 2006
Location: delhi

Senior Member

Dear John,

I can able to reply you by tomorrow, as it has happened on my personal laptop.

Regards
Ishika

Report message to a moderator

Re: UNDO Tablespace deleted from OS level [message #580478 is a reply to message #580475]

Mon, 25 March 2013 03:16

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I was under the impression that datafile number 1 is ALWAYS the first datafile of the system tablespace.

This is correct and this is what happens when people fake their result and lie to us.

Regards
Michel

Report message to a moderator

Re: UNDO Tablespace deleted from OS level [message #580482 is a reply to message #580478]

Mon, 25 March 2013 04:17

ishika_20
Messages: 339
Registered: December 2006
Location: delhi

Senior Member

Dear Michel,

First - Thanks for providing such a nice link. I will try to resolve my problem with the provided link.

Second - You are correct !!! As i remember the error but don't have the ORA- error list. So i Copied it from other site to explain you my problem. Error is same like SYSTEM tablespace.

Inconvenience cause is regretted.

Regards,
Ishika

Report message to a moderator

Re: UNDO Tablespace deleted from OS level [message #580576 is a reply to message #580482]

Mon, 25 March 2013 23:19

ishika_20
Messages: 339
Registered: December 2006
Location: delhi

Senior Member

Dear Michel / John,

Below is my error...

Code: [Select all] [Show/ hide]

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF'


	
RMAN> restore datafile 2;

Starting restore at 25-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/25/2013 20:22:24
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 2 found to restore


RMAN> restore TABLESPACE UNDOTBS1;

Starting restore at 25-MAR-13
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/25/2013 20:45:11
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 2 found to restore

Since, i have not taken backup. So restore & recover will also not work.

As database is in mount stage, Alter database datafile 2offline is also not working. Now, suggest me how can I restore it? Is there any way or its total loss ???

Regards,
Ishika

Report message to a moderator

Re: UNDO Tablespace deleted from OS level [message #580586 is a reply to message #580576]

Tue, 26 March 2013 01:22

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You cannot, your database is lost.
There are some internal ways to recover with transaction loss but, as I said, they are internal and should be used only with the backing of Oracle support.
Now you learn one thing: always make a backup after creating a database, always use archivelog mode and backup the archived logs.

Regards
Michel

Report message to a moderator

Re: UNDO Tablespace deleted from OS level [message #580616 is a reply to message #580586]

Tue, 26 March 2013 03:54

ishika_20
Messages: 339
Registered: December 2006
Location: delhi

Senior Member

Dear Michel,

Thank you very much for your prompt response...

Also, I will thank you for providing me such a great link (dba-village).

I learn recovery part from the link. If i have valid RMAN backup, i can able to restore & recover the data-files. Else there would be total loss.

Thanks a ton...

Regards,
Ishika

Report message to a moderator

Previous Topic:	Databae Cloning
Next Topic:	block_change_tracking change

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 22:35:11 CDT 2025