Recovering the Database After the Loss
of the SYSTEM or UNDO
Tablespace Datafile
In this case, we will just restore the
tablespaces or datafiles that were lost. Of course,because these are critical tablespace objects, the
database itself is down. After restoring the datafiles that were lost (do not
restore any datafiles that are intact), recover the databasefollowing these steps:
1. Log into the database as SYS.
2. Mount the database with the startup mount
command.
3. For recovery, you have two options. You can use
the recover tablespace or the recoverdatafile command to recover the datafiles
that were lost. It’s kind of up to you which one you want to use (we like the
recover tablespace command in this situation more).
4. The recover database command will recommend to
you the correct archived redo log to apply. At the prompt, type AUTO; the
recover database command automatically starts applying all redo until the
database is recovered.
5. Once database
recovery is complete, the recover database command will return you to the SQL prompt. You can then issue the alter
database open command to open the database for business.
{
Recovery of the UNDO Tablespace
There are cases where the UNDO tablespace can be recovered online. If the database was shut down in a consistent manner before the UNDO tablespace was lost, it may be that all you will need to do is take the UNDO tablespace datafiles offline (you won’t be able to take the tablespace itself offline) and then open the database.Oracle has a default SYSTEM tablespace that would be used in this case, when the database initially comes up. You could then just create a new UNDO tablespace and drop the old one. This might be a quicker recovery method in some cases.
}
If the tablespace/datafile you
lost is not associated with the SYSTEM or UNDO tablespaces,
then you are in luck. You don’t
even need to shut down the database to recover! All you
need to do is take the datafiles
offline, restore the impacted datafiles, recover the datafiles
(or the tablespace), and bring
them back online.
The nice thing about this is if
your users are not using the tablespace, they will never
know there was a problem. If the
users are using the tablespace, they will be impacted only
if they try to use the datafiles
that are offline (which is one good reason in some cases to
take just datafiles offline
rather than the whole tablespace).
The first question is, How do you
know which datafiles are missing? There are a couple
of things that will give you a
clue. First of all, your users will start getting these messages:
SQL> select * from corporate.stockvalue;
select * from corporate.stockvalue
ERROR at line 1:
ORA-00376: file 5 cannot be read
at this time
ORA-01110: data file 5: ‘D:\APP\ADMINISTRATOR\ORADATA\TEST\AXPERT04.DBF’
You can also look at the V$RECOVER_FILE
view for more information on datafiles that
need recovery. Here is an example
of such a query:
SQL> select * from
v$recover_file;
FILE#
ONLINE ONLINE_ ERROR CHANGE#
TIME
----------
------- -------
--------------------
---------- ---------
5 ONLINE ONLINE FILE NOT FOUND 0
{
Missing Datafiles
Don’t expect that
these errors indicating datafiles are missing will always show up in thealert
log. Sometimes they will (for example, on database startup), but often they
won’t (for example, when a query fails because a datafile is offline). If you
want to monitor for this problem reliably, then the V$RECOVER_FILE view is the
way to go.
}
So, here is the general recovery
process from such an error. In this case we assume the
database is up and running:
1. Take the
datafile offline using the alter database datafile offline command as
shown here:
alter database datafile ‘D:\APP\ADMINISTRATOR\ORADATA\TEST\AXPERT04.DBF’
offline;
As an alternative, you can use FILE_ID
as shown in this example:
alter database datafile 5
offline;
FILE_ID will appear in the error
message, or you can use the FILE_ID column of
DBA_DATA_FILES or the FILE# column
in the V$DATAFILE view.
2. Restore the
missing datafiles.
3. Restore all
archived redo logs that will be needed for recovery. This would be all
archived redo logs generated from
the beginning of the backup image you restored
in step 2.
Warning
When you are restoring backup files, never restore backed-up online
redo
logs over the existing online redo logs. This is so important.we did
not even back up the online redo logs. Restoring old online redo logs over your
existing ones will lead to data loss. Fair warning!
4. Recover the missing datafiles with the recover
datafile or recover tablespace command.
5. Bring the datafiles or the tablespace online with
the alter database or alter tablespace ommand.So, what do you do if your
database was down and you discover the files are lost when you start it up?
That’s simple too.
1. Log in as SYS and start up the database. If a
datafile is missing, you will get an error message that looks something like
this:
SQL> startup
ORACLE instance started.
Total System Global Area 397557760 bytes
Fixed Size 1333452 bytes
Variable Size 289408820 bytes
Database Buffers 100663296 bytes
Redo Buffers 6152192 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see
DBWR trace file
ORA-01110: data file 5: ‘D:\APP\ADMINISTRATOR\ORADATA\TEST\AXPERT04.DBF’
2. It may be that you are missing more than datafile
4, since Oracle will alert you toonly the first datafile that it finds missing.
Use the V$RECOVER_FILE, V$DATAFILE,and V$TABLESPACE views to determine exactly
which datafiles are missing and which tablespaces they are associated with, as
shown in this example:
SQL> select b.name ts_name, a.error, c.name
datafile
2 from v$recover_file a, v$tablespace b, v$datafile
c
3 where a.file#=c.file# and b.ts#=c.ts#
SQL> /
TS_NAME ERROR DATAFILE
--------- -------------------- ------------------------------------
AXPERT FILE NOT FOUND D:\APP\ADMINISTRATOR\ORADATA\TEST\AXPERT04.DBF
3. Review the
results of the query. As long as the missing datafiles are not part of the SYSTEM
or UNDO tablespace, you can
simply take those datafiles offline and open the database. The
intent will be to recover those
tablespaces/datafiles with the database open. First use the alter database datafile offline command
to take the tablespaces offline:
SQL> alter database datafile ‘D:\APP\ADMINISTRATOR\ORADATA\TEST\AXPERT04.DBF’ offline;
Database altered.
4. Next, open the
database with the alter database open command:
alter database open
5. Now restore the
database backup datafiles from your hot backup media.
6. Restore all
archived redo logs that will be needed for recovery. You will need to restore
all archived redo logs generated
from the beginning of the backup image you restored in step 2.
Figuring
Out Which Archived Redo Logs You Need
If you need to figure out exactly
which archived redo logs you need to restore your backup (so, perhaps, you can restore
those files off of backup media), you can use the V$RECOVER_FILE and the V$LOG_HISTORY views.
The V$RECOVER_FILE view provides the last change number (in the CHANGE# column)
present in the file(s) needing recovery. The V$LOG_HISTORY view will tell you which archived
redo logs the changes are in. Here is an example:
ORA-01157: cannot identify/lock
data file 5 - see DBWR trace file
ORA-01110: data file 5: ‘D:\APP\ADMINISTRATOR\ORADATA\TEST\AXPERT04.DBF’
SQL> host copy AXPERT04.DBF.BKP
AXPERT04.DBF
1 file(s) copied.
SQL> Select a.file#,
a.change#, b.first_change#, b.next_change#, b.sequence#
2 From v$recover_file a, v$log_history b Where
a.change#<=b.next_change#;
FILE# CHANGE#
FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
---------- ---------- -------------
------------ ----------
5 1418889
1417349 1438925 20
7. Recover the datafiles or tablespaces using the
recover datafile or recover tablespace command.
SQL> recover datafile 5;
8. Bring the datafiles or tablespaces online using
the alter database datafile online or alter tablespace online command. Once you
have done this, you have recovered the missing tablespace datafiles and your
database is back to normal.
SQL>Alter database datafile 5 online;
