Oracle 9i - Media Recovery

Posted on 2011-10-21
Last Modified: 2012-06-21
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.
Question by:fidoly
    LVL 6

    Expert Comment

    by:Greg Clough
    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


    recover database until time ...

    Open in new window

    The final step after an incomplete recovery is:

    alter database open resetlogs

    Open in new window

    LVL 6

    Accepted Solution

    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?

    Author Closing Comment

    Your suggestion lead me to this publication

    All is well now.  thank you.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Suggested Solutions

    Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now