29 December, 2011

Does a STARTUP MOUNT verify datafiles ?

There seems to be a misunderstanding that a MOUNT actually verifies datafiles.
A STARTUP MOUNT  (or STARTUP NOMOUNT followed by ALTER DATABASE MOUNT) does *NOT* read the datafiles and/or verify them.  It does read the controlfile(s).

Here's a simple test :

I have a tablespace with a datafile that is "ONLINE".
SQL> create tablespace X_TBS datafile '/tmp/X_TBS.dbf' size 50M;

Tablespace created.

SQL> create table hemant.X_TBS (col_1) tablespace X_TBS
  2  as select rownum from dual connect by level < 100;

Table created.

SQL> 

SQL> select file#, status, name 
  2  from v$datafile
  3  where name like '%X_TBS%';

     FILE# STATUS  NAME
---------- ------- ----------------------------------------
        14 ONLINE  /tmp/X_TBS.dbf

SQL> select file#, status, name
  2  from v$datafile_header
  3  where name like '%X_TBS%';

     FILE# STATUS  NAME
---------- ------- ----------------------------------------
        14 ONLINE  /tmp/X_TBS.dbf

SQL> select owner, segment_name, bytes/1024
  2  from dba_segments
  3  where tablespace_name = 'X_TBS';

OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES/1024
----------
HEMANT
X_TBS
        64


SQL> 
I now shutdown the database instance and remove the datafile :
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !ls /tmp/X_TBS.dbf
/tmp/X_TBS.dbf

SQL> !rm /tmp/X_TBS.dbf

SQL> !ls /tmp/X_TBS.dbf
ls: /tmp/X_TBS.dbf: No such file or directory

SQL> 
Does the STARTUP MOUNT succeed ?
SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1337720 bytes
Variable Size             213911176 bytes
Database Buffers          314572800 bytes
Redo Buffers                5840896 bytes
Database mounted.
SQL>
SQL> shutdown
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1337720 bytes
Variable Size             213911176 bytes
Database Buffers          314572800 bytes
Redo Buffers                5840896 bytes
SQL> alter database mount;

Database altered.

SQL> 
Can the file be listed ? Yes. However, V$DATAFILE_HEADER no longer shows the name ! The query on V$DATAFILE_HEADER does cause Oracle to "look" for the file but it does NOT cause a failure. It simply finds it "missing".
SQL> select file#, status, name
  2  from v$datafile
  3  where file#=14;

     FILE# STATUS  NAME
---------- ------- ----------------------------------------
        14 ONLINE  /tmp/X_TBS.dbf

SQL> select file#, status, name
  2  from v$datafile_header
  3  where file#=14;

     FILE# STATUS  NAME
---------- ------- ----------------------------------------
        14 ONLINE

SQL> 
When does Oracle attempt to access the datafile ?
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 14 - see DBWR trace file
ORA-01110: data file 14: '/tmp/X_TBS.dbf'


SQL> 
Even as the OPEN failed with an ORA-01157, the datafile is present in the controlfile :
SQL> select file#, status, name
  2  from v$datafile
  3  where file#=14;

     FILE# STATUS  NAME
---------- ------- ----------------------------------------
        14 ONLINE  /tmp/X_TBS.dbf

SQL> select file#, status, name
  2  from v$datafile_header
  3  where file#=14;

     FILE# STATUS  NAME
---------- ------- ----------------------------------------
        14 ONLINE

SQL> 
I hope that I have you convinced that a MOUNT does NOT verify the datafiles. If you are still not convinced, read the Backup and Recovery documentation about how to do a FULL DATABASE RESTORE and RECOVER -- where you restore the controlfile and mount the database before you even restore datafiles. How would the MOUNT succeed with the controlfile alone ?

.
.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.
.
Now, here's something more. You'd understand this if you understand how RECOVER works. Do NOT try this if you are not sure about how I was able to "recreate" the datafile. Do NOT try this if you do not know how data is inserted in the X_TBS table when the database is in NOARCHIVELOG mode.
SQL> alter database create datafile 14 as '/tmp/new_X_TBS.dbf';

Database altered.

SQL> recover datafile 14;
Media recovery complete.
SQL> select file#, status, name
  2  from v$datafile
  3  where file#=14;

     FILE# STATUS  NAME
---------- ------- ----------------------------------------
        14 ONLINE  /tmp/new_X_TBS.dbf

SQL> select file#, status, name
  2  from v$datafile_header
  3  where file#=14;

     FILE# STATUS  NAME
---------- ------- ----------------------------------------
        14 ONLINE  /tmp/new_X_TBS.dbf

SQL> alter database open;

Database altered.

SQL> select tablespace_name from dba_tables
  2  where owner = 'HEMANT'
  3  and table_name = 'X_TBS';

TABLESPACE_NAME
------------------------------
X_TBS

SQL> 
SQL> select file_name from dba_data_files
  2  where file_id=14;

FILE_NAME
--------------------------------------------------------------------------------
/tmp/new_X_TBS.dbf

SQL>
SQL> select count(*) from hemant.X_TBS;
select count(*) from hemant.X_TBS
                            *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 14, block # 131)
ORA-01110: data file 14: '/tmp/new_X_TBS.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option


SQL> 

Happy simulating and testing on your own database.
.
.
.

5 comments:

Anonymous said...

feeds not working...
i want to follow ur blog..

Hemant K Chitale said...

The feeds are supposed to work and I do believe that there others who do receive the feeds.
I have also tested the RSS feed in Mozilla firefox.

Hemant K Chitale

Jesse said...

Hemant,

Tested this hypothesis on a 11gR2 database. The database DOES read the controlfile and verify the file header. I'm absolutely sure of that.

Here it is:

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select name from v$datafile_header;

NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/rodin2/system01.dbf
/home/oracle/app/oracle/oradata/rodin2/sysaux01.dbf
/home/oracle/app/oracle/oradata/rodin2/undotbs01.dbf
/home/oracle/app/oracle/oradata/rodin2/users01.dbf
/home/oracle/app/oracle/oradata/rodin2/example01.dbf

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !mv /home/oracle/app/oracle/oradata/rodin2/system01.dbf /home/oracle/app/oracle/oradata/rodin2_temp

SQL> startup mount
ORACLE instance started.

Total System Global Area 997953536 bytes
Fixed Size 2219512 bytes
Variable Size 851444232 bytes
Database Buffers 138412032 bytes
Redo Buffers 5877760 bytes
Database mounted.
SQL> select name from v$datafile_header;

NAME
---------------------------------------------------------------------------------------------

/home/oracle/app/oracle/oradata/rodin2/sysaux01.dbf
/home/oracle/app/oracle/oradata/rodin2/undotbs01.dbf
/home/oracle/app/oracle/oradata/rodin2/users01.dbf
/home/oracle/app/oracle/oradata/rodin2/example01.dbf
-------------------------
Notice that the only file_header missing is the system datafile that I moved to a temporary location. I subsequently moved the system datafile back to its original location and the database reflects this in the mount stage and opens properly.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> !mv /home/oracle/app/oracle/oradata/rodin2_temp/system01.dbf /home/oracle/app/oracle/oradata/rodin2

SQL> startup mount
ORACLE instance started.

Total System Global Area 997953536 bytes
Fixed Size 2219512 bytes
Variable Size 851444232 bytes
Database Buffers 138412032 bytes
Redo Buffers 5877760 bytes
Database mounted.

SQL> select name from v$datafile_header;

NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/rodin2/system01.dbf
/home/oracle/app/oracle/oradata/rodin2/sysaux01.dbf
/home/oracle/app/oracle/oradata/rodin2/undotbs01.dbf
/home/oracle/app/oracle/oradata/rodin2/users01.dbf
/home/oracle/app/oracle/oradata/rodin2/example01.dbf

SQL> alter database open;

Database altered.

Hemant K Chitale said...

Jesse,
In my opinion, you have demonstrated that a query on V$DATAFILE_HEADER actually verifies the datafiles.
This is a step that is different from and after the actual MOUNT DATABASE.

Also see the discussion in this forums thread : https://forums.oracle.com/forums/thread.jspa?threadID=2326350


Hemant K Chitale

Anonymous said...

If you've both hot n cold backups.which one will u use for recovering a database n y?