| Lesson 4 | Media recovery with noarchivelog mode |
| Objective | Discuss media recovery options with noarchivelog mode. |
Media recovery with noarchivelog mode in Oracle
You have some options to recover your noarchivelog mode database in certain media failure situations.
Let us review a situation where the database loses a tablespace. If you lose a tablespace the database is unusable until the tablespace has been dropped and recreated or the entire database has been restored from the last full backup.
One possible option
Remember that we discussed in previous module that some of the tablespaces in a typical database do not necessarily need to be backed up.
Let us say that the disk containing the
TEMPORARY tablespace was damaged. Click the View Image button to see what we received when we have tried to open the database. The following simulation illustrates how you can recover your database:
- Startup server manager by typing |||(S1)svrmgr30|||(S0) at the DOS prompt.
- DOS prompt,
- |||(S0)Please check your entry and try again.
- svrmgr30 OR SQL* Plus
- Connect to the database as the user internal by entering |||(S1)connect internal|||(S0) at the SVRMGR> prompt, and hitting return.
Media recovery with noarchivelog mode
- Startup server manager by typing svrmgr at the DOS prompt.
- Connect to the database as the user internal by entering connect internal at the SVRMGR> prompt, and hitting return.
- You are now prompted for a password. In this case, the password is "oracle". Since the password does not echo and the cursor doesn't move forward when you enter the password, just click the space next to the Password prompt to move to the next screen.
- Mount the database by entering startup mount at the SVRMGR> prompt and hitting return. This starts the instance and mounts the database, but it does not open the database. If we try to open the database now we will get an error.
- We must take the temporary tablespace offline and drop it. At the same time we must also update the control file to indicate that this file is no longer used. Enter the following command at the SVRMGR> prompt and hit return: alter database datafile 'c:\oracle8\database\tmp1orc1.ora' offline drop;
- Now that the control file is not expecting to use tmplorc1.ora, we can open the database with the following command after the SVRMGR> prompt and hitting return: alter database open;
- We need to drop the logical entry for the temporary_data tablespace. Enter the following command after the SVRMGR> prompt and then hit return: drop tablespace temporary_data including contents;
- We can now recreate the lost tablespace on the repaired or alternate disk drive by entering the following command after the SVRMGR> prompt and hitting return: create tablespace temporary_data datafile ‘c:\oracle8\database\tmp1orc1.ora’;
- Now we will shutdown the instance and start it back up to make sure we can bring the database up. First let's shut down the instance. Enter the following command after the SVRMGR> prompt and hit return: shutdown normal;
- Start the instance up again by typing startup after the SVRMGR> prompt and hitting return.
- The instance started up successfully. You have completed the simulation. This is the end of the simulation.
TEMPORARY, INDEX, and USER are potential candidates but SYSTEM and DATA are not reasonable candidates for restoration.
A second possible alternative
One other alternative is to use the Export utility between full backups to export critical
data tablespaces. You can then use the Import utility to restore lost data. This is an incomplete recovery option since any transactions since the last export are lost, but it gives you a method for recovering some of your data. We will cover Export/Import in a later module.
Media Recovery with Noarchivelog Mode - Exercise
Click the Exercise link below to complete an ordering applet.
Media Recovery with Noarchivelog Mode - Exercise
The next lesson is about archivelog mode.
Media Recovery with Noarchivelog Mode - Exercise
The next lesson is about archivelog mode.