Contents
Introduction Overview of Steps to handle a Corruption Corruption due to NOLOGGING or UNRECOVERABLE (1) Determine the Extent of the Corruption Problem (2) Replace or Move Away from Suspect Hardware (3) Which Objects are Affected ? Options for various Segment Types: CACHE , CLUSTER , INDEX PARTITION INDEX , ROLLBACK , TABLE PARTITION , TABLE , TEMPORARY , Other Segment Type , No Segment (4) Choosing a Recovery Option (4A) Database Recovery Datafile Recovery , Database Recovery , After Complete Recovery (4B) Recreating Indexes (4C) Salvaging Data from Tables (4D) Last Options All SQL statements here are for use in Server Manager (or SQL*Plus in 8.1) when connected as a SYSDBA user. (Eg: "connect / as sysdba" or "connect internal")Introduction
This article discusses how to handle one or more block corruptions on an Oracle datafile and describes the main actions to take to deal with them. Please read the complete article before taking any action. Note: If the problem is an ORA-1578 on STARTUP then please contact your local support center for advice referencing [NOTE:106638.1] - this note is not visible to customers but the relevant steps from it can be supplied by an experienced support analyst.You may be referred to this article from many places for many forms of error - it is important that you have the following information for each corrupt block:
Eg: For the ORA-1578 error: ORA-01578: ORACLE data block corrupted (file # 7, block # 12698) ORA-01110: data file 22: '/oracle1/oradata/V816/oradata/V816/users01.dbf' then: <AFN> is "22" (from the ORA-1110 portion of the error) <BL> is "12698" (from the "block #" in the ORA-1578) <FILENAME> is '/oracle1/oradata/V816/oradata/V816/users01.dbf' For other errors (ORA-600 , ORA-1498 etc...) the above values should either be given to you by Oracle Support, or be given to you from the article which covers the relevant error.
- An absolute FILE NUMBER of the file containing the corrupt block. Referred to as "<AFN>" in this article.
- The file name of the file containing the corrupt block. Referred to as "<FILENAME>" in this article. ( If you know the FILE NUMBER but not its name then V$DATAFILE can be used to get the file name: SELECT name FROM v$datafile WHERE file#=<AFN>; )
- The BLOCK NUMBER of the corrupt block in that file. Referred to as "<BL>" in this article.
- The database block size. Referred to as "<DB_BLOCK_SIZE>" in this article. ( eg: SHOW PARAMETER db_block_size )
Overview of Steps to handle a Corruption
There are many possible causes of a block corruption including: - Bad IO hardware / firmware - OS problems - Oracle problems - Recovering through "UNRECOVERABLE" or "NOLOGGING" database actions (in which case ORA-1578 is expected behaviour - see below) The point in time when an Oracle error is raised may be much later than when any corruption initially occurred. As the root cause is not usually known at the time the corruption is encountered, and as in most cases the key requirement is to get up and running again, then the steps used tackle corruption problems in this article are: 1) Determine the extent of the corruption problems and also determine if the problems are permanent or transient. If the problem is widespread or the errors move about then focus on identifying the cause first (check hardware etc..). This is important as there is no point recovering a system if the underlying hardware is faulty. 2) Replace or move away from any faulty or suspect hardware. 3) Determine which database objects are affected. 4) Choose the most appropriate database recovery / data salvage option. For all steps above it is sensible to collect evidence and document exactly what actions are being taken. The 'Evidence>>' tags in this article list the information which should be collected to assist with identifying the root cause of the problem. Corruption due to NOLOGGING or UNRECOVERABLE If a NOLOGGING (or UNRECOVERABLE) operation is performed on an object and the datafile containing that object is subsequently recovered then the data blocks affected by the NOLOGGING operation are marked as corrupt and will signal an ORA-1578 error when accessed. In Oracle8i an ORA-26040 is also signalled ("ORA-26040: Data block was loaded using the NOLOGGING option" ) which makes the cause fairly obvious, but earlier releases have no additional error message. If a block is corrupt due to recovery through a NOLOGGING operation then you can use this article from Section 3 "Which Objects are Affected ?" onwards but note that: (a) Recovery cannot retrieve the NOLOGGING data (b) No data is salvagable from inside the block(1) Determine the Extent of the Corruption Problem
Whenever a corruption error occurs note down the FULL error message/s and look in the instance alert log and trace files for any associated errors. It is important to do this first to assess whether this is a single block corruption, an error due to an UNRECOVERABLE operation or a more severe issue. It is a good idea to scan affected files (and any important files) with DBVERIFY to check for other corruptions in order to determine the extent of the problem. For details of using DBVERIFY see [NOTE:35512.1] Once you have determined a list of corrupt file/block combinations then the steps below can be used to help determine what action can be taken. Evidence>> - Record the original error in full, along with details of the application which encountered the error. - Save an extract from the alert log from a few hours before the FIRST recorded problem up to the current point in time. - Save any tracefiles mentioned in the alert log. - Record any recent OS problems you have encountered. - Note if you are using any special features - Eg: ASYNC IO, fast write disk options etc.. - Record your current BACKUP position (Dates, Type etc...) - Note if your database is in ARCHIVELOG mode or not Eg: Issue "ARCHIVE LOG LIST" in Server Manager.(2) Replace or Move Away from Suspect Hardware
The vast majority of corruption problems are caused by faulty hardware. If there is a hardware fault or a suspect component then it is sensible to either repair the problem, or make disk space available on a separate disk sub-system prior to proceeding with a recovery option. IMPORTANT: If there are multiple errors (which are NOT due to NOLOGGING) OR You have OS level errors against the affected file OR The errors are transient and keep moving about then there is little point proceeding until the underlying problem has been addressed or space is available on alternative disks. Get your hardware vendor to check the system over and contact Oracle Support with details of all errors. You can move datafiles about using the following steps: 1. Make sure the file to be relocated is either OFFLINE or the instance is in the MOUNT state (not open) 2. Restore (or copy) the datafile to its new location eg: /newlocation/myfile.dbf 3. Tell Oracle the new location of the file. eg: ALTER DATABASE RENAME FILE '/oldlocation/myfile.dbf' TO '/newlocation/myfile.dbf';(3) Which Objects are Affected ?
It is best to determine which objects are affected BEFORE making any decisions about how to recover - this is because the corruption/s may be on object/s which can easily be re-created. Eg: For a corruption on a 5 row lookup table it may be far quicker to drop and recreate the table than to perform a recovery. For each corruption collect the information in the following table. The steps to do this are explained below.
| Original Error |
Absolute File# <AFN> |
Relative File# <RFN> |
Block# <BL> |
Tablespace | Segment Type |
Segment Owner.Name |
Related Objects |
Recovery Options |
The notes below will help you fill in this table for each corruption.
"Original Error"
This is the error as initially reported.
Eg: ORA-1578 / ORA-1110 , ORA-600 with all arguments etc..
"Absolute File#", "Relative File#" and "Block#"
The File# and Block# should have been given to you either by the
error, by Oracle Support, or by the steps in an error article which
directed you to this article.
In Oracle7: Use the same file number for both the "Absolute File#"
and the "Relative File#"
In Oracle8: The absolute and relative file numbers are often the
same but can differ (especially if the database has
been migrated from Oracle7). It is important to get
the correct numbers for <AFN> and <RFN>
or you may end up salvaging the wrong object !!
An ORA-1578 reports the RELATIVE file number, with the
ABSOLUTE file number given in the accompanying ORA-1110
error. For ORA-600 errors you should be told an absolute
file number.
The following query will show the absolute and relative
file numbers for datafiles in the database:
SELECT tablespace_name, file_id "AFN", relative_fno "RFN"
FROM dba_data_files;
"Segment Type", "Owner", "Name" and "Tablespace"
The following query will tell you the object TYPE , OWNER and NAME of
a segment given the absolute file number "<AFN>" and block number "<BL>" of the
corrupt block - the database must be open in order to use this query:
SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = <AFN>
and <BL> between block_id AND block_id + blocks - 1
;
"Related Objects" and Possible "Recovery Options" by SEGMENT_TYPE:
The related objects and recovery options which can be used depend on the
SEGMENT_TYPE. The additional queries and possible recovery options are
listed below for each of the most common segment types.
CACHE CLUSTER INDEX PARTITION
INDEX ROLLBACK TABLE PARTITION
TABLE TEMPORARY Some other Segment Type
"no rows" from the query
CACHE
- If the segment type is CACHE recheck you have entered the SQL
and parameters correctly.
If you get the same result contact Oracle support with all
information you have.
Options:
The database is likely to require recovery.
{Continue} {Back to Segment List}
CLUSTER
- If the segment is a CLUSTER determine which tables it contains.
Eg:
SELECT owner, table_name
FROM dba_tables
WHERE owner='<SEGMENT-OWNER>'
AND cluster_name='<SEGMENT-NAME>'
;
Options:
If the OWNER is "SYS" then contact Oracle support with all details.
The database is likely to require recovery.
For non dictionary clusters possible options include:
Database recovery
OR Salvage data from all tables in the cluster
THEN Recreate the cluster and all its tables
As the cluster may contain a number of tables then it is best to
collect information for each table in the cluster before making a
decision.
{Collect TABLE information} {Back to Segment List}
INDEX PARTITION
- If the segment is an INDEX PARTITION note the NAME and OWNER
and then determine which partition is affected thus:
SELECT partition_name
FROM dba_extents
WHERE file_id = <AFN>
AND <BL> BETWEEN block_id AND block_id + blocks - 1
;
then continue below as if the segment was an INDEX segment.
Options:
Index partitions can be rebuilt using:
ALTER INDEX xxx REBUILD PARTITION ppp;
INDEX
- If the segment is an INDEX then if the OWNER is "SYS" contact
Oracle support with all details.
For a non-dictionary INDEX or INDEX PARTITIONs find out which table
the INDEX is on:
Eg:
SELECT table_owner, table_name
FROM dba_indexes
WHERE owner='<SEGMENT-OWNER>'
AND index_name='<SEGMENT-NAME>'
;
and determine if the index supports a CONSTRAINT:
Eg: SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints
WHERE owner='<TABLE-OWNER>'
AND constraint_name='<INDEX-NAME>'
;
Possible values for CONSTRAINT_TYPE are:
P The index supports a primary key constraint.
U The index supports a unique constraint.
If the INDEX supports a PRIMARY KEY constraint (type "P") then
check if the primary key is referenced by any foreign key constraints:
Eg:
SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints
WHERE r_owner='<TABLE-OWNER>'
AND r_constraint_name='<INDEX-NAME>'
;
Options:
If the OWNER is "SYS" then contact Oracle support with all details.
The database is likely to require recovery.
For non dictionary indexes possible options include:
Database recovery
OR Recreate the index (with any associated constraint
disables/enables)
(take care with the REBUILD option as described in
"Recreating Indexes" below)
{Continue} {Back to Segment List}
ROLLBACK
- If the segment is a ROLLBACK segment contact Oracle support as
rollback segment corruptions require special handling.
Options:
The database is likely to require recovery.
{Continue} {Back to Segment List}
TABLE PARTITION
- If the segment is a TABLE PARTITION note the NAME and OWNER
and then determine which partition is affected thus:
SELECT partition_name
FROM dba_extents
WHERE file_id = <AFN>
AND <BL> BETWEEN block_id AND block_id + blocks - 1
;
then continue below as if the segment was a TABLE segment.
Options:
If all corruptions are in the same partition then one option
at this point is to EXCHANGE the corrupt partition with an
empty TABLE - this can allow the application to continue (without
access to the data in the corrupt partition) whilst any good
data can then be extracted from the table.
TABLE
- If the OWNER is "SYS" then contact Oracle support with all details.
The database is likely to require recovery.
For a non-dictionary TABLE or TABLE PARTITIONs find out which
INDEXES exist on the TABLE:
Eg:
SELECT owner, index_name, index_type
FROM dba_indexes
WHERE table_owner='<SEGMENT-OWNER>'
AND table_name='<TABLE-NAME>'
;
and determine if there is any PRIMARY key on the table:
Eg: SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints
WHERE owner='<TABLE-OWNER>'
AND table_name='<SEGMENT-NAME>'
AND constraint_type='P'
;
If there is a primary key then check if this is referenced by any
foreign key constraints:
Eg:
SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints
WHERE r_owner='<TABLE-OWNER>'
AND r_constraint_name='<CONSTRAINT-NAME>'
;
Options:
If the OWNER is "SYS" then contact Oracle support with all details.
The database is likely to require recovery.
For non dictionary tables possible options include:
Database recovery
OR Salvage data from the table (or partition)
THEN Recreate the table (or partition)
{Continue} {Back to Segment List}
TEMPORARY
- If the segment type is TEMPORARY then the corruption does not
affect a permanent object. Check if the tablespace where the
problem occurred is being used as a TEMPORARY tablespace thus:
SELECT count(*) FROM dba_users
WHERE temporary_tablespace='<TABLESPACE-NAME>'
;
Options:
If this is a TEMPORARY_TABLESPACE then it may be possible
to create a NEW temporary tablespace and switch all users
to that tablespace then DROP the problem tablespace.
If this is not a temporary tablespace then the block should
not be read again and should get re-formatted next time the
block is used - the error should not repeat PROVIDED any
underlying cause has been cured.
No restore is normally required, although if the disk is
suspect and the tablespace contains useful data then a
database recovery of the affected file/s may be wise.
{Continue} {Back to Segment List}
Some other SEGMENT_TYPE
- If the segment type returned is not covered above then contact
Oracle support for advice with all information collected so far.
{Continue} {Back to Segment List}
"no rows returned"
- If there appears to be no extent containing the corrupt block
then first double check the figures used in the query. If you
are sure the file and block are correct and do not appear as
belonging to an object in DBA_EXTENTS then:
- If the database you are now querying is from a different
point in time to the datafile with the error then the
problem object may have been dropped.
- If the error you are investigating was reported by DBVERIFY
then DBV checks all blocks regardless of whether they
belong to an object or not.
Options:
An error on an UNUSED Oracle block can be ignored as Oracle will
create a new block image should the block need to be used so any
existing problem on the block will never get read.
{Continue} {Back to Segment List}
Evidence>>
- For each corrupt block it is also a good idea to collect
the following physical evidence if there is a need to try
and identify the actual cause of the corruption:
i) An operating system HEX dump of the bad block
On UNIX:
dd if=<FILENAME> bs=<DB_BLOCK_SIZE> skip=<BL> count=1 | od -x
^^^^^^^^ ^^^^^^^^^^^^^^ ^^^
Eg:
dd if=ts11.dbf bs=4k skip=1223 count=3 | od -x > dump.out
On VMS:
DUMP/BLOCKS=(start:XXXX,end:YYYY)/out=dump.out <FILENAME>
Where XXXX=Operating system block number (in 512 byte chunks)
To calculate this multiply the block number reported by
'<DB_BLOCK_SIZE>/512'.
ii) If you are in ARCHIVELOG mode make a safe copy of the archived
log files around the time of the error, and preferably for a few
hours before the error was reported. These may be required later.
{Continue} {Back to Segment List}
(4) Choosing a Recovery Option
The best recovery option now depends on the objects affected. The notes
in Section (3) above should have highlighted the main options available
for each affected object. The actual recovery method chosen may include
a mix or one or more methods thus:
Is Complete Recovery an option ?
In order for complete recovery to be an option the following
must be true:
- The database is in ARCHIVELOG mode
(The "ARCHIVE LOG LIST" command shows Archivelog Mode)
- You have a good backup of affected files
- All ARCHIVELOGS are available from the time of the backup
to the current point in time
- The current online log/s are available and intact
- The errors are NOT due to recovery through a NOLOGGING operation
When the above criteria are satisfied then then complete recovery
is usually the preferred option
*BUT NOTE*
(a) If the rollback of a transaction has seen a corrupt block on an
object other than the rollback segment itself then UNDO may
have been discarded. In this case you may need to rebuild
indexes / check data integrity AFTER the recovery completes.
(b) If the files to be recovered contain data from NOLOGGING
operations performed since the last backup then those blocks
will be marked corrupt.
If database recovery has already been performed and the corruption
is still there then either the backup contains a corruption, the
underlying fault is still present or the problem is replaying
through redo. In these cases you will need to choose some other
recovery option.
See "(4A) Complete Recovery" for complete recovery steps.
Can the object be Dropped or Re-created without needing
to extract any data from the object itself ?
It may be possible to lose the object, or to recreate it from
a script / recent export. Once an object is dropped then blocks
in that object are marked as "free" and will be re-formatted when
the block gets allocated to a new object. It is advisable to RENAME
rather than DROP a table unless you are absolutely sure that you
do not need any data in it.
In the case of a table partition then only the affected partition
needs to be dropped. eg: ALTER TABLE ... DROP PARTITION ...
If the corruption affects the partition segment header, or the
file containing the partition header is offline, then DROP
PARTITION may fail. In this case it may still be possible to
drop the partition by first exchanging it with a table of the
same definition.
eg: ALTER TABLE .. EXCHANGE PARTITION .. WITH TABLE ..;
The most common object which can be re-created is an index.
Always address TABLE corruptions before INDEX problems on a table.
See "(4B) Recreating Indexes" for more details.
Is it required to salvage data before recreating the object ?
If the problem is on a critical application table which is
regularly updated then it may be required to salvage as much
data from the table as possible, then recreate the table.
See "(4C) Salvaging Data from Tables" for more details.
Last Options
Are any of the following possible ?
Recovery to an old point-in-time (via point in time recovery)
OR Restore of a COLD backup from before the corruption
OR Use of an existing export file
See "(4D) Last Options" for more details.
(4A) Complete Recovery
If the database is in ARCHIVELOG mode and you have a good backup
of the affected files then recovery is usually the preferred option.
This is not GUARANTEED to clear a problem, but is effective for the
majority of corruption issues. If recovery re-introduces the problem
then return to the list of options above and choose another method.
You can either perform datafile recovery (which can be done while the
rest of the database is still up and running), or database recovery
(which requires the database to be taken down).
Datafile Recovery
~~~~~~~~~~~~~~~~~~
Datafile recovery of a file involves the following steps. If there are
several files repeat the steps for each file or see "Database Recovery"
below. These steps can be used if the database is either OPEN or MOUNTED.
OFFLINE the affected data file
eg: ALTER DATABASE DATAFILE 'name_of_file' OFFLINE;
Copy it to a safe location (in case the backup is bad)
Restore the latest backup of the file onto a GOOD disk
Check the restored file for obvious corruptions with DBVERIFY
For details of using DBVERIFY see [NOTE:35512.1]
Assuming the restored file is OK the RENAME the datafile to the
NEW location (if different from the old location)
eg: ALTER DATABASE RENAME FILE 'old_name' TO 'new_name';
Recover the datafile
eg: RECOVER DATAFILE 'name_of_file';
Online the file/s
eg: ALTER DATABASE DATAFILE 'name_of_file' ONLINE;
{Continue}
Database Recovery
~~~~~~~~~~~~~~~~~
Database recovery generally involves the following steps:
Shutdown Abort
Copy the current copy of all files to be recovered to a safe location
Restore the backup files to a GOOD disk location
DO NOT RESTORE THE CONTROL FILES or REDO LOGS
Check restored files with DBVERIFY
For details of using DBVERIFY see [NOTE:35512.1]
Startup MOUNT
Rename any relocated files
eg: ALTER DATABASE RENAME FILE 'old_name' TO 'new_name';
Ensure all required files are online
eg: ALTER DATABASE DATAFILE 'name_of_file' ONLINE;
Recover the database
eg: RECOVER DATABASE
Open the database
eg: ALTER DATABASE OPEN;
After a Complete Recovery
~~~~~~~~~~~~~~~~~~~~~~~~~~
Once a complete recovery has been performed it is advisable to check the
database before allowing it to be used:
- Run "ANALYZE <table_name> VALIDATE STRUCTURE CASCADE"
against each problem object to check for table/index mis-matches.
If there has been any UNDO discarded this may show a mismatch
requiring indexes to be re-created.
- Check the logical integrity of data in the table at application level.
(4B) Recreating Indexes
If the corrupt object is a user INDEX you can simply drop and
re-create it PROVIDED the underlying table is not also corrupt.
If the underlying table is also corrupt it is advisable to sort out
the TABLE before recreating any indexes.
If the information collected shows that the index has dependent FOREIGN
KEY constraints then you will need to do something like this:
- ALTER TABLE <child_table> DISABLE CONSTRAINT <fk_constraint>;
for each foreign key
- Rebuild the primary key using
ALTER TABLE <table> DISABLE CONSTRAINT <pk_constraint>;
DROP INDEX <index_name>;
CREATE INDEX <index_name> .. with appropriate storage clause
ALTER TABLE <table> ENABLE CONSTRAINT <pk_constraint>;
- Enable the foreign key constraints
ALTER TABLE <child_table> ENABLE CONSTRAINT <fk_constraint>;
For an index partition you can:
ALTER INDEX ... REBUILD PARTITION ...;
Notes:
(1) It is important not to REBUILD a non-partitioned corrupt index
using an "ALTER INDEX .. REBUILD" command as this will usually
try to build the new index from the existing index segment,
which contains a corrupt block.
"ALTER INDEX ... REBUILD ONLINE" and "ALTER INDEX ... REBUILD
PARTITION ..." do not build the new index from the old index
segment and so can be used.
(2) Create INDEX can use the data from an existing index
if the new index is a sub-set of the columns in the
existing index. Hence if you have 2 corrupt indexes drop
them BOTH before re-creating them.
(3) Be sure to use the correct storage details when recreating indexes.
(4C) Salvaging Data from Tables
If the corrupt object is a TABLE or CLUSTER it must be understood
that the data within the corrupt block is lost. Some of the data
may be salvageable from a HEX dump of the block, or from columns
covered by indexes.
Important:
As it may be required to salvage data in the corrupt
block from the indexes it is a good idea NOT to drop
any existing index until any required data has been
extracted.
There are many ways to get data out of a table which contains a
corrupt block. Choose the most appropriate method as detailed
below. The aim of these methods is to extract as much data as
possible from the table blocks which can be accessed. It is
usually a good idea to RENAME the corrupt table so that the new
object can be created with the correct name.
Eg: RENAME <emp> TO <emp_corrupt>;
Methods of extracting data from a corrupt table AROUND the corrupt block
(1) From Oracle 7.2 onwards, including Oracle 8.0 and 8.1, it is
possible to SKIP over corrupt blocks in a table.
This is by far the simplest option to extract table data and is
discussed in:
Extracting data using SKIP_CORRUPT_BLOCKS or Event 10231 [NOTE:33405.1]
Note that this event can only be used if the block "wrapper" is
marked corrupt. Eg: If the block reports ORA-1578.
(2) From Oracle 7.1 onwards you can use a ROWID range scan.
The syntax for this is a little tricky but it is possible
to select around a corrupt block using a ROWID hint.
As the format of ROWIDs is different in Oracle7 and Oracle8
there are 2 articles which discuss this:
Using ROWID Range Scans to extract data in Oracle8 [NOTE:61685.1]
Using ROWID Range Scans to extract data in Oracle7 [NOTE:34371.1]
(3) If there is a primary key you can select table data via this index.
It may also be possible to select some of data via any other index.
This can be slow and time consuming and is only normally needed
for Oracle 7.0 releases. This method is described in [NOTE:34371.1]
(which also describes the ROWID range scans)
(4) There are various salvage programs / PLSQL scripts which can be
used to salvage data from a table. These can take longer to set
up and use than the above methods but can often cope with various
kinds of corruption besides an ORA-1578.
As these methods typically require much hand-holding from
support then some of these articles may not be visible to customers.
These require Pro*C to be available and an understanding of how
to build Pro*C executables:
SALVAGE.PC for Oracle8.1 [NOTE:97357.1]
SALVAGE.PC for Oracle7 [NOTE:2077307.6]
These requires manual interaction:
SALVAGE.SQL for Oracle7/8 [NOTE:2064553.4]
SALVAGE.SQL for Oracle7/8 [NOTE:28308.1]
This is only possible in Oracle8i.
The aim is to mark the block as corrupt and then use the
SKIP_CORRUPT table attribute to extract the table data:
Use DBMS_REPAIR to mark the block corrupt [NOTE:68013.1]
Extracting data from the corrupt block itself
As the corrupt block itself is "corrupt" then any data extracted
from the block should be treated as suspect. The main methods
of getting the rows from the corrupt block itself are:
- Oracle Support can use a tool which attempts to interpret
the block contents.
- Use any existing indexes on the table to extract data for
columns covered by the index where the ROWID falls inside the
corrupt block. This is described towards the end of the ROWID
range scan articles mentioned above:
For Oracle8/8i see [NOTE:61685.1]
For Oracle7 see [NOTE:34371.1]
(4D) Last Options
Whatever sort of block the problem occurred one possible option
is to recover the whole database to a point in time BEFORE the
corruption appeared. The difficulty with this option is that it is not
always possible to know when the problem first appeared.
DBVERIFY can be often be used to check a restored file for corruptions.
For details of using DBVERIFY see [NOTE:35512.1]
This section outlines the absolute final options available for recovering
a database. If you have come here then one or more of the following
have happened:
You have lost a "vital" datafile (or have a corruption on it)
and have no useful backup of the problem file/s
and are either not in ARCHIVELOG mode OR do not have all archivelogs
since the file was first created
Last chance:
Please note if you have lost all copies of a datafile but DO still have
the ARCHIVE logs from when the file was first created it is still possible
to recover the file.
Eg:
ALTER DATABASE CREATE DATAFILE '....' [as '...'] ;
RECOVER DATAFILE '....'
ALTER DATABASE DATAFILE '....' ONLINE;
If you are in this scenario try to recover the datafile using these
steps before proceeding below.
If you have reached this line there are no options left to recover to
the current point in time. It is advisable to shutdown the instance and
take a BACKUP of the current database NOW in order to provide a fall-back
position if the chosen course of action fails. (Eg: if you find your backup
is bad)
The outline options available are:
Revert to an old COLD backup
- eg: If in NOARCHIVELOG mode
Point in time recovery to an older point in time that is consistent
- requires a good backup and any necessary archive logs
- ALL files have to be restored and the whole DB rolled forward
to a suitable point in time.
Rebuild of DB from some logical export / copy
- Requires there to already be a good logical backup of the database
- NB: You have to RE-CREATE the database for this option.
.