Note: This didn't go very smoothly, so these are RAW NOTES, but in the end I did fix it!!! (scenario 3 below). The notes from the original thread were a bit vague / wrong details, so this is at least a bit more to go on.
In the course of trying to startup an Oracle database you might get an error like:
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: 'D:\ORACLE_DATA\CUSTOMER\DB.ORA'
This has to do with Oracle looking for a data file associated with one of its databases that it can't get to any more. There are 3 general cases:
1: The file is there, but the OS has it locked by some other process, or there's a permission issue, etc.
Fix: operating system issue, permissions, or reboot to clear processes, etc.
2: The file is not there, it was deleted, but you want to recover it!
Fix: See this thread and scroll down to Sept 15 2005 7:12 posting. I did not try this.
3: The file is not there, it was deleted, and you don't care. It's an old database that somebody knew was obsolete, so they just deleted it. Oracle doesn't like that!
Fix: See the same thread but scroll a bit up to the Sept 15 6:46 posting.
# 3 is the case I'm in. The data was old and was blown away. I just need to tell Oracle not to worry about it.
This is what I'm trying, from this thread (6:46 posting)
Before you start, you need to know where Oracle stores its control files. And to find that out, I looked in the ora.ini file, so you'll need to find that.
Oracle ini I found:
D:\oracle\admin\(dbname)\pfile\init.ora.1111200217029
Look in there to find the control_file location, on line 34 I have:
control_files=("D:\oracle_data\stack\CONTROL01.CTL", "D:\oracle_data\stack\CONTR
OL02.CTL", "D:\oracle_data\stack\CONTROL03.CTL")
So the "control files" mentioned in the thread I mentioned above are in D:\oracle_data\stack. We'll need to know this later.
(from Command Prompt, with sqlplus in path d:\oracle\ora92\bin)
sqlplus /nolog
(then inside SQL)
alter database backup controlfile to trace;
OR is it...
alter database backup controlfile to trace as 'some/path' REUSE;
I think the "reuse" parameter has them take out all the cruft, so that you can rerun it.
(in another command window)
Looked in file system, found D:\oracle\admin\stack\udump\stack_ora_700.trc Use DIR /OD to see the most recent files, and compare against current system time.
(then in ANOTHER command window)
cd D:\oracle_data\stack
(remember, this was where we found the control files in an earlier step)
mkdir ctrl_bak
copy *.ctl ctrl_bak\
del *.ctl
In the second command window, with the trc file, make a copy of it. CALL THE ORIGINAL stack_ora_700.trc.BAK. We will be modifying stack_ora_700.trc
It needs to not have a bunch of cruft comments in it (I think that's what the reuse does).
And then on the first line you need to ADD this: (see this page, search for controlfile)
connect / as sysdba
BTW If you wanted to do that on the command line it would be:
sqlplus "/ as sysdba"
Now from a command prompt we will restore the control file from this human readable backup:
sqlplus /nolog @stack_ora_700.trc
Not sure you need this next one ....
(now back in SQL)
startup nomount;
I eventually had to reboot. But after that it worked!
You can also check the control files directory and notice that your 3 .CTL files are back.
Comments