RMAN: Determining if Media Recovery Is Required

Андрей Волков

Андрей Волков

Системное, сетевое администрирование +DBA. И немного программист!))  Профиль автора.

The term media recovery means the restoration of files that have been lost or damaged, owing to the failure of the underlying storage media (usually a disk of some sort) or accidental removal of files. Usually, you know that media recovery is required through an error such as the following:

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/dbfile/O12C/system01.dbf'

The error may be displayed on your screen when performing DBA tasks, such as stopping and starting the database. Or, you might see such an error in a trace file or the alert.log file. If you don’t notice the issue right away, with a severe media failure, the database will stop processing transactions, and users will start calling you.

To understand how Oracle determines that media recovery is required, you must first understand how Oracle determines that everything is okay. When Oracle shuts down normally (IMMEDIATE, TRANSACTIONAL, NORMAL), part of the shutdown process is to flush all modified blocks (in memory) to disk, mark the header of each data file with the current SCN, and update the control file with the current SCN information.

Upon startup, Oracle checks to see if the SCN in the control file matches the SCN in the header of the data files. If there is a match, then Oracle attempts to open the data files and online redo log files. If all files are available and can be opened, Oracle starts normally. The following query compares the SCN in the control file (for each data file) with the SCN in the data file header:

SET LINES 132

COL name             FORM a40

COL status           FORM A8

COL file#            FORM 9999

COL control_file_SCN FORM 999999999999999

COL datafile_SCN     FORM 999999999999999

--

SELECT

 a.name

,a.status

,a.file#

,a.checkpoint_change# control_file_SCN

,b.checkpoint_change# datafile_SCN

,CASE

   WHEN ((a.checkpoint_change# - b.checkpoint_change#) = 0) THEN 'Startup Normal'

   WHEN ((b.checkpoint_change#) = 0)                        THEN 'File Missing?'

   WHEN ((a.checkpoint_change# - b.checkpoint_change#) > 0) THEN 'Media Rec. Req.'

   WHEN ((a.checkpoint_change# - b.checkpoint_change#) < 0) THEN 'Old Control File'

   ELSE 'what the ?'

 END datafile_status

FROM v$datafile        a -- control file SCN for datafile

    ,v$datafile_header b -- datafile header SCN

WHERE a.file# = b.file#

ORDER BY a.file#;

If the control file SCN values are greater than the data file SCN values, then media recovery is most likely required. This would be the case if you restored a data file from a backup, and the SCN in the restored data file had an SCN less than the data file in the current control file.

Image Tip  The V$DATAFILE_HEADER view uses the physical data file on disk as its source. The V$DATAFILE view uses the control file as its source.

You can also directly query the V$DATAFILE_HEADER for more information. The ERROR and RECOVER columns report any potential problems. For example, a YES or null value in the RECOVER column indicates that there is a problem:

SQL> select file#, status, error, recover from v$datafile_header;

Here is some sample output:

     FILE# STATUS  ERROR                REC

---------- ------- -------------------- ---

         1 ONLINE  FILE NOT FOUND

         2 ONLINE                       NO

         3 ONLINE                       NO
 

Вас заинтересует / Intresting for you:

RMAN: Specifying the Backup Us...
RMAN: Specifying the Backup Us... 2464 views Андрей Волков Sat, 29 Feb 2020, 10:14:03
RMAN: Using Online or Offline ...
RMAN: Using Online or Offline ... 1623 views Андрей Волков Sat, 29 Feb 2020, 10:01:33
RMAN: Checking for Corruption ...
RMAN: Checking for Corruption ... 26912 views Андрей Волков Thu, 30 Sep 2021, 11:57:27
How to Restore Oracle Archive ...
How to Restore Oracle Archive ... 2593 views Андрей Волков Mon, 31 Jan 2022, 17:49:22
Comments (0)
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Suggested Locations