| 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 -
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  |
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 #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.
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 #580473 is a reply to message #580467] |
Mon, 25 March 2013 02:56 |
John Watson
Messages: 8960
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 #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
|
|
|
|
| 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...
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 #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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
