[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 643
  • Last Modified:

Oracle 9i - Media Recovery

Here is the situation.

An upgrade of an application modified the data files on an Oracle 9i.  I have restored the datafiles from backup, but when starting the database it gives me the following…

Database mounted.
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u01/SRV1/users/users01.dbf'

Most recommendations I have seen propose to do a:  recover datafile '/u01/SRV1/users/users01.dbf'

Seems pretty straight forward but I have some questions before I attempt to do this.

1.  Will the datafile change in any way or this only affects the control file?
2.  Are the any other commands needed after the restore?
3.  Should I shutdown the database before running the “recover”
4.  Is there a better procedure  you can advise?
5.  I restored 6 datafiles in total.  Should I leave the others datafiles alone, or should also do a recover on them?

Many thanks.
0
fidoly
Asked:
fidoly
  • 2
1 Solution
 
Greg CloughSenior Oracle DBACommented:
If you have the time then I always recommend:

1) Shutdown the database cold
2) Copy EVERYTHING to some place else (Datafiles, Controlfiles, RedoLogs, etc.)

This way if you have a problem during the recovery, you can at least get back to where you are now and try again.

For the recovery, if you simply issue:

recover database
alter database open

Open in new window


That will know from the controlfile which datafiles need recovery, and apply the archive/redo logs to bring it all up-to-date.

To explicitly answer your questions:

1.  Will the datafile change in any way or this only affects the control file?

The datafile and controlfile will change during recovery, as oracle applies blocks from the archivelogs.


2.  Are the any other commands needed after the restore?

Depends on whether you are doing a complete recovery, or to a point-in-time.


3.  Should I shutdown the database before running the “recover”

You will need to shutdown the database and then mount it before you can do a recovery.


4.  Is there a better procedure  you can advise?

Depends on what you are trying to achieve.  If it's full recovery, then what you propose sounds sensible.


5.  I restored 6 datafiles in total.  Should I leave the others datafiles alone, or should also do a recover on them?

All datafiles will need to be recovered to the same point-in-time, or in Oracle parlance "SCN" (System Change Number).  Is your intention to revert your database back to a point in time before the application did the damage, or are you trying to recover the entire database back and retrieve ALL transactions?

If you want a partial recovery, then it's best if you restore all of the datafiles (after taking a backup like I said in the first part of the post), and then do either:

recover database until cancel

Open in new window

or

recover database until time ...

Open in new window

The final step after an incomplete recovery is:

alter database open resetlogs

Open in new window

0
 
Greg CloughSenior Oracle DBACommented:
Actually I just re-read your post, and it appears that you want to restore the entire database back to before the application upgrade.  The best way to do this is:

1) Backup what you currently have, just to be safe
2) Restore everything. Datafiles, Controlfiles, Redologs, etc.
3) Start the database as normal, it won't require recovery

This assumes that you did a cold backup of the database and included the controlfiles and redologs.  Can you tell me how you did the backup?  Did you shut it down and do a full cold backup, was it done using "alter tablespace begin backup", or with rman?
0
 
fidolyAuthor Commented:
Your suggestion lead me to this publication http://myracle.wordpress.com/2008/01/11/recover-database-without-control-files-and-redo-log-files/

All is well now.  thank you.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now