| ORA-01219 database not open [message #137362] |
Wed, 14 September 2005 17:48  |
mitra fatolahi
Messages: 38
Registered: October 2002
|
Member |
|
|
Hello everyone,
We have Oracle9i software installed on one drive
and the data files on a different drive.
We needed to reformat the drive on which we had the data file.
We stopped all Oracle9i services, copied the datafile directory to a different drive, reformatted the drive on which we had the data files, then copied back the data files to the same drive. Restarted all Oracle9i services. Logged in to the Enterprise Manager, tried to open tablespaces or any other nodes got ORA-01219 database not open error message.
Right-click on the database name, clicked Start prompted with the error message that had the following errors:
ORA-01113 file 10 needs media recover,
ORA-01110 datafile 10: '1:
\Oracle\oradata\SSDB4\SSTBS_1.ora'
Please advise.
Thank you,
Mitra
|
|
|
|
| Re: ORA-01219 database not open [message #137430 is a reply to message #137362] |
Thu, 15 September 2005 03:02  |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
Seems like you all right, can you post the final errors from the alert log to try to get more detail?
Cheers
Jim
EDIT When you say that you stopped the services, did you shutdown the database? Simply stopping the services effectively crashes the db and we would expect a media recovery requirement. Presumably you still have a copy of these datafiles. Try startup in Mounted and then
RECOVER DATABASE.
[Updated on: Thu, 15 September 2005 03:05] Report message to a moderator |
|
|
|
|
|
|
|
| Re: ORA-01219 database not open [message #138053 is a reply to message #137430] |
Mon, 19 September 2005 11:27 |
mitra fatolahi
Messages: 38
Registered: October 2002
|
Member |
|
|
Hello,
You asked how I stopped Oracle services before copying the data files to another drive and reformating the drive. I stopped all Oracle services using "Services window", reformated the drive, copied back the files to the same drive, and then started the Oracle services again using Services window.
After reading your reply to my question, it seems that I had not shut down the oracle database properly.
I am going through Backup and Recovery documentation and don't seem to find any simple instruction for beginners like myself as how to do what you had suggested: "Try startup in Mounted and then RECOVER DATABASE."
Could you please be more specific and write me the steps and which tool to use to do media recovery?
By the way you asked for more detail info of the error messages:
ORA-01219: database not open: queries allowed on fixed tables/views only
ORA-01113: file 10 needs media recovery
ORA-01110:data file 10: '1:
\ORACLE\ORADATA\SSDB4\SSTBS_1.ORA'
I appreciate the help!
Thank you,
Mitra
|
|
|
|
|
|
|
|
|
|
|
|
| Re: ORA-01219 database not open [message #138271 is a reply to message #138195] |
Wed, 21 September 2005 01:42 |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
hi,
I dont know the mode of your database .. is it in archive mode or no archive mode.
but you can try this step to recove your database ..
Restore all the file back to is location i.e. at the location where it was present before you had format that drive
Now connect to your database as
sys/change_on_install as sysdba
I hope your password fo sys is change_on_install as u mentioned that you are new on oracle database that means u had not change the default password of sys and efault password of sys is change_on_install.
Now
Issue following commands :
SQL>Startup mount.
• You will see an error ora-1113:file 10 needs media recovery
SQL>Recover database.
SQL>Alter database open.
Note :- I hope your redolog file is ok and and not over written ...as to recove the database all the scn,log etc. are present in your current redo log files & if this is not the case then your database must be in archive log mode.  or you have to do incomplete recovery.
follow below step for incomplete recovery
Issue following commands :
SQL>Startup mount.
• You will see an error ORA-01113: file 10 needs media recovery
Note :- From your message posted i can see file 10 need media recovery so i am using 10 in below written query
SQL>Recover database.•
Ret : suggested = carriage return
• File name = ‘path of file’
• Auto = automatically applying all logs
• Cancel = cancel recovery
type any of the above option at SQL>
Oracle is NOT able to perform media recovery. So database will NOT be recovered.
Solution:
Delete current data files and apply from backup.
o SQL>ALTER DATABASE DATAIFLE 10 OFFLINE DROP;
o SQL> ALTER DATABASE OPEN;
----------------------------------------------------
Hope my above information will be usefull to you 
Regards
Always Friend Sunilkumar
[Updated on: Wed, 21 September 2005 01:46] Report message to a moderator |
|
|
|
| Re: ORA-01219 database not open [message #138594 is a reply to message #138271] |
Thu, 22 September 2005 10:24 |
mitra fatolahi
Messages: 38
Registered: October 2002
|
Member |
|
|
Thank you everyone for responding to my question.
I followed the instruction and connected to Oracle9i sqlplus using sys login as sysdba and was able to start the server in nomount state and recover the database.
My oracle9i database was not in archive mode (test environment).
Thank god, I had not and did not need to reformat the drive on which we had the redo log files. For future reference, I am curious to know that if I had NOT have the redo log files, and in my case that I did not have the database in the archive mode, would I had been able to Recover the database successfully?
Again thank you for all the help!
Mitra
|
|
|
|
| Re: ORA-01219 database not open [message #138605 is a reply to message #138594] |
Thu, 22 September 2005 11:19 |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
hi,
Its our pleasure to help you ... And One thing more always feel free to ask question related to oracle database whenever you are in trouble... we will try our best to solve it .
Regards
Always friend sunilkumar
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
