Q
Problem solve Get help with specific problems with your technologies, process and projects.

Changing file paths in a recovered Oracle database

Expert Brian Peasland helps a reader figure out how to successfully remap the file path for a database that had to be recovered from a dead server.

I have a problem in that our server died. This was a WINNT4 with Oracle 8i installed. We have managed to get the...

ORADATA directory containing (click for full size):

We have got a new server with Windows 2003 Server and Oracle 9 installed. We have created a new database. I have copied all the files from the old database into the new database directory. I can mount the database OK. I needed to change the location in the control files for the files from a F drive to a C drive location , as the old server had a data drive named F. I used the alter database rename file command, this worked successfully. I confirmed the name change by using the command select name from V$datafile; , and all the files are showing the correct path.

When I do a alter database open command , I get the following error (click for full size):


error message

I gather that when the files were renamed the file date changed. Is there anyway to fix this?

 

The first thing I would do is install Oracle 8i and attempt the recovery. The last time I tried to restore a database and upgrade at the same time, I ran into roadblocks. Oracle wouldn't let me open the database because the software was the wrong version and I couldn't do STARTUP UPGRADE because I needed to apply recovery. So in my opinion, it is best to do the restore with the same version if possible and then do the upgrade after the restore is complete.

You never mentioned how the backup of the database was created. Were you just able to yank the old drive out of the server that died and then copy the files? Was the database shut down cleanly (probably not since the server died)?

Is the database running in archivelog mode? If the database is not in archivelog mode, then you run the risk of not being able to restore what was a live database. If the database was in archivelog mode, then I would create a script to recreate the control file (CREATE CONTROLFILE). You can look up the syntax in the SQL Reference guide if needed. Use the new path(s) in the CREATE CONTROLFILE command. Then do the following:

  1. STARTUP NOMOUNT
  2. Create the controlfile.
  3. ALTER DATABASE RECOVER USING BACKUP CONTROLFILE UNTIL CANCEL;
  4. If needed, apply recovery using archived redo logs. You may also need to specify the online redo log files as well if the database crashed.
  5. CANCEL
  6. ALTER DATABASE OPEN RESETLOGS;
  7. SHUTDOWN IMMEDIATE
  8. Take a backup.

I would also recommend taking a backup of what you have before you start the process above. That way, if you run into problems you can revert back to this state.

This was last published in May 2011

Dig Deeper on Oracle database backup and recovery

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close