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.
.
.
.

DROP TABLESPACE INCLUDING CONTENTS drops segments

As reported in a few posts earlier,  DDLs always commit when beginning execution, even if they subsequently fail execution.
Here is a demonstration of a DROP TABLESPACE INCLUDING CONTENTS  causing segments in the tablespace to be dropped even if the DROP TABLESPACE fails.  Furthermore, segments dropped by a DROP TABLESPACE are no longer in the recyclebin !

SQL> create tablespace TBS_2_DROP datafile '/tmp/tbs_2_drop.dbf' size 100M ;

Tablespace created.

SQL> 
SQL> connect hemant/hemant
Connected.
SQL> -- create table and populate it
SQL> create table TABLE_IN_TBS_2_DROP  (col_1 number, col_2 char(50))
  2  tablespace TBS_2_DROP
  3  /

Table created.

SQL> 
SQL> insert into TABLE_IN_TBS_2_DROP
  2  select rownum, to_char(rownum)
  3  from dual
  4  connect by level < 501
  5  /

500 rows created.

SQL> 
SQL> create index NDX_T_I_T_2_D on TABLE_IN_TBS_2_DROP (col_1) tablespace TBS_2_DROP;

Index created.

SQL> 
SQL> -- now connect AS SYSDBA
SQL> connect / as sysdba
Connected.
SQL> 
SQL> select segment_name, segment_type, bytes/1048576
  2  from dba_segments
  3  where tablespace_name = 'TBS_2_DROP'
  4  /

SEGMENT_NAME         SEGMENT_ BYTES/1048576
-------------------- -------- -------------
TABLE_IN_TBS_2_DROP  TABLE            .0625
NDX_T_I_T_2_D        INDEX            .0625

SQL> 
SQL> alter tablespace TBS_2_DROP begin backup;

Tablespace altered.

SQL> 
SQL> drop tablespace TBS_2_DROP including contents;
drop tablespace TBS_2_DROP including contents
*
ERROR at line 1:
ORA-01150: cannot prevent writes - file 14 has online backup set
ORA-01110: data file 14: '/tmp/tbs_2_drop.dbf'


SQL> 
SQL> select segment_name, segment_type, bytes/1048576
  2  from user_segments
  3  where tablespace_name = 'TBS_2_DROP'
  4  /

no rows selected

SQL> 
SQL> select object_name, original_name, type
  2  from dba_recyclebin
  3  where owner = 'HEMANT'
  4  /

no rows selected

SQL>

Although the DROP TABLESPACE failed with an ORA-1150 error, it did execute the recursive sqls drop segments present in the Tablespace. And these segments are not recoverable now !
.
.
.

(Off-Topic) How NOT to make a chart

These are charts which convey information with no meaning.
.
.
.

AIOUG Sangam '11 photographs

Photographs of the two-day AIOUG Sangam '11 conference are now accessible from www.aioug.org

.

.

Published with Blogger-droid v2.0.2

AIOUG Sangam 11 content

These are the two Topics that I presented at AIOUG Sangam '11 at Bangalore on 09th and 10th December 2011 :

The Role of the DBA :

Article :  "The Role of the DBA"

Presentation :  "The Role of the DBA"


Partitioning :

Article :  "Partitioning Tables and Indexing Them".  This contains additional examples that could not be included in the Presentation.

Presentation :  "Partitioning Tables and Indexing Them".

Published with Blogger-droid v2.0