Search My Oracle Blog

Custom Search

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?

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016