Media Recovery Locks

What is the deal with all of those strange media recovery locks (v$lock.type=MR) held by the Database Writer process in our Oracle databases? It seems slightly strange that Oracle would have media recovery locks on a database that is open and fully functional. Or does it?

These media recovery locks do serve a meaningful purpose. When a data file is online and read/write the control file and data file states, alone, would not prevent recovery from being run against that data file. Consider the database that has been shutdown with the ABORT option. The state of the file headers and the corresponding control file information for the database files do not change as a result of an ABORT. No checkpoint is taken to update the file headers and no control file updates are made. The instance is simply terminated; the file header and its corresponding control file information appear just as it did when the database was still open. The replay of committed transactions and the rolling back of uncommitted data is later handled by crash recovery and post-open transaction recovery.

However, it is a perfectly legitimate proposition to take an aborted instance, mount it and issue the RECOVER DATABASE command to make the database consistent. In fact, after you issue the RECOVER DATABASE command you can take a backup of the database. So, if I can issue a RECOVER DATABASE command against data files that are in “aborted” status, what would prevent me from invoking the recovery engine while the database was open? You guessed it, the media recovery (MR) locks held by the Database Writer. Some very strange things, indeed, might ensue if we could start recovery against an online file.

It has been my experience, for an open database, that the Database Writer does not relinquish the MR lock for a data file until the file is taken offline with or without the tablespace. With the data file offline, file media recovery is now a legal operation. When a recovery session is created to recover a data file, that process acquires the MR lock for the applicable data file. This makes complete sense, as that is the session responsible for the recovery. Even if the database is only mounted the recovery session holds the MR lock until that recovery session is cancelled or complete. This prevents multiple recovery sessions from doing the same recovery. If RECOVERY_PARALLELISM is set to a non-zero value the MR lock is still held by the SQL*Plus or RMAN session that started the recovery.

Let’s look at a quick example. This really requires two sessions to show the MR lock activity. The sessions are differentiated by the color of the output.

sys@10g:SQL> @test.sql
sys@10g:SQL> select a.sid,
2 substr(a.program,1,25) program,
3 b.file#,
4 c.type
5 from v$session a,
6 v$datafile b,
7 v$lock c
8 where a.sid = c.sid and
9 b.file# = c.id1 and
10 c.type = 'MR';

SID PROGRAM FILE# TY
---------- ------------------------- ---------- --
167 oracle@machine (DBW0) 1 MR
167 oracle@machine (DBW0) 2 MR
167 oracle@machine (DBW0) 3 MR
167 oracle@machine (DBW0) 4 MR
167 oracle@machine (DBW0) 5 MR
167 oracle@machine (DBW0) 6 MR
167 oracle@machine (DBW0) 7 MR
167 oracle@machine (DBW0) 8 MR


8 rows selected.

sys@10g:SQL>
sys@10g:SQL> select file_name
2 from dba_data_files
3 where tablespace_name = 'USERS';

FILE_NAME
--------------------------------------------------------------------------------
/ora/data001/10g/datafile/o1_mf_users_1pdkdtz5_.dbf

sys@10g:SQL>
sys@10g:SQL> alter tablespace users begin backup;

Tablespace altered.

sys@10g:SQL>
sys@10g:SQL> !cp /ora/data001/10g/datafile/o1_mf_users_1pdkdtz5_.dbf
/ora/data001/10g/datafile/o1_mf_users_1pdkdtz5_.dbf.bkp

sys@10g:SQL>
sys@10g:SQL> alter tablespace users end backup;

Tablespace altered.

sys@10g:SQL>
sys@10g:SQL> alter system switch logfile;

System altered.

sys@10g:SQL> alter system switch logfile;

System altered.

sys@10g:SQL> alter system switch logfile;

System altered.

sys@10g:SQL> alter tablespace users offline;

Tablespace altered.

sys@10g:SQL> !cp /ora/data001/10g/datafile/o1_mf_users_1pdkdtz5_.dbf.bkp
/ora/data001/10g/datafile/o1_mf_users_1pdkdtz5_.dbf

sys@10g:SQL> recover tablespace users;
ORA-00279: change 12173563 generated at 03/24/2006 14:49:59 needed for thread 1
ORA-00289: suggestion : /ora/arch/10g/1_87_584918376.dbf
ORA-00280: change 12173563 for thread 1 is in sequence #87


Specify log: {=suggested filename AUTO CANCEL}

sys@10g:SQL> select a.sid,
2 substr(a.program,1,25) program,
3 b.file#,
4 c.type
5 from v$session a,
6 v$datafile b,
7 v$lock c
8 where a.sid = c.sid and
9 b.file# = c.id1 and
10 c.type = 'MR';

SID PROGRAM FILE# TY
---------- ------------------------- ---------- --
167 oracle@machine (DBW0) 8 MR
167 oracle@machine (DBW0) 2 MR
167 oracle@machine (DBW0) 3 MR
167 oracle@machine (DBW0) 1 MR
167 oracle@machine (DBW0) 5 MR
167 oracle@machine (DBW0) 6 MR
167 oracle@machine (DBW0) 7 MR
153 sqlplus@machine (TNS V1- 4 MR

8 rows selected.

Log applied.
Media recovery complete.

sys@10g:SQL> select a.sid,
2 substr(a.program,1,25) program,
3 b.file#,
4 c.type
5 from v$session a,
6 v$datafile b,
7 v$lock c
8 where a.sid = c.sid and
9 b.file# = c.id1 and
10 c.type = 'MR';

SID PROGRAM FILE# TY
---------- ------------------------- ---------- --
167 oracle@machine (DBW0) 1 MR
167 oracle@machine (DBW0) 2 MR
167 oracle@machine (DBW0) 3 MR
167 oracle@machine (DBW0) 5 MR
167 oracle@machine (DBW0) 6 MR
167 oracle@machine (DBW0) 7 MR
167 oracle@machine (DBW0) 8 MR

7 rows selected.

sys@10g:SQL> alter tablespace users online;

Tablespace altered.

sys@10g:SQL> select a.sid,
2 substr(a.program,1,25) program,
3 b.file#,
4 c.type
5 from v$session a,
6 v$datafile b,
7 v$lock c
8 where a.sid = c.sid and
9 b.file# = c.id1 and
10 c.type = 'MR';

SID PROGRAM FILE# TY
---------- ------------------------- ---------- --
167 oracle@machine (DBW0) 1 MR
167 oracle@machine (DBW0) 2 MR
167 oracle@machine (DBW0) 3 MR
167 oracle@machine (DBW0) 4 MR
167 oracle@machine (DBW0) 5 MR
167 oracle@machine (DBW0) 6 MR
167 oracle@machine (DBW0) 7 MR
167 oracle@machine (DBW0) 8 MR

8 rows selected.

As you can see, the MR lock was acquired by the recovery session and was not relinquished until the tablespace was brought online. At that point the Database Writer assumed ownership of the MR lock for the file in question (file_id=4). In this manner Oracle can ensure that file media recovery is only being performed by a single recovery session; and that recovery cannot be invoked against an online file. These MR locks are indeed annoying as they pollute our otherwise clean v$lock dynamic performance view. But, they serve a very vital function in preserving the integrity of our data.