Home » RDBMS Server » Backup & Recovery » UNDO Tablespace deleted from OS level (Windows XP, !0g, 10.2.0.1)
UNDO Tablespace deleted from OS level [message #580459] Sun, 24 March 2013 23:53 Go to next message
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 -


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
Re: UNDO Tablespace deleted from OS level [message #580460 is a reply to message #580459] Mon, 25 March 2013 00:55 Go to previous messageGo to next message
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 -

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


Waiting for your reply...
Re: UNDO Tablespace deleted from OS level [message #580464 is a reply to message #580460] Mon, 25 March 2013 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: UNDO Tablespace deleted from OS level [message #580467 is a reply to message #580464] Mon, 25 March 2013 01:58 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Michel,

Apologize...

My error is as below.

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
Re: UNDO Tablespace deleted from OS level [message #580472 is a reply to message #580467] Mon, 25 March 2013 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just register to the site.

Regards
Michel
Re: UNDO Tablespace deleted from OS level [message #580473 is a reply to message #580467] Mon, 25 March 2013 02:56 Go to previous messageGo to next message
John Watson
Messages: 4693
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:
select t.name,d.name,d.file#,d.status from v$tablespace t join v$datafile d using(ts#);

Re: UNDO Tablespace deleted from OS level [message #580475 is a reply to message #580473] Mon, 25 March 2013 03:00 Go to previous messageGo to next message
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
Re: UNDO Tablespace deleted from OS level [message #580478 is a reply to message #580475] Mon, 25 March 2013 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: UNDO Tablespace deleted from OS level [message #580482 is a reply to message #580478] Mon, 25 March 2013 04:17 Go to previous messageGo to next message
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
Re: UNDO Tablespace deleted from OS level [message #580576 is a reply to message #580482] Mon, 25 March 2013 23:19 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Michel / John,

Below is my error...

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
Re: UNDO Tablespace deleted from OS level [message #580586 is a reply to message #580576] Tue, 26 March 2013 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: UNDO Tablespace deleted from OS level [message #580616 is a reply to message #580586] Tue, 26 March 2013 03:54 Go to previous message
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
Previous Topic: Databae Cloning
Next Topic: block_change_tracking change
Goto Forum:
  


Current Time: Sat Oct 25 08:44:35 CDT 2014

Total time taken to generate the page: 0.09595 seconds