Trying to lose a datafile.

Chen Shapira wrote me a suggestion regarding losing a data file.

“Try moving a data file while the DB is up and running, and try to make Oracle "notice"
that it is gone.  On Linux, Oracle does not notice anything! You can move a datafile
and all selects, updates, inserts, will work. The OS prevents the DB from seeing
the change. But I noticed that your test system is on windows, so I'm curious
whether it is the same.”

Once my company’s  operator TARed the rest of the database into the system tablespace datafile.
Oracle ‘noticed’!  Are you sure that you really lost the file? Was there some sort of mirroring
of the file?

Anyway, here is what happened when I tried to lose a data file while the DB is up on a Windows based DB…

SQL> select name from v$datafile;

NAME
-------------------------------------------------------------------------------
C:\ORACLE\ORA10\ORADATA\DB10\SYSTEM01.DBF
C:\ORACLE\ORA10\ORADATA\DB10\UNDOTBS01.DBF
C:\ORACLE\ORA10\ORADATA\DB10\SYSAUX01.DBF
C:\ORACLE\ORA10\ORADATA\DB10\O1_MF_USERS_35OQZZ4H_.DBF
C:\ORACLE\ORA10\ORADATA\DB10\EXAMPLE01.DBF

SQL> host ren C:\ORACLE\ORA10\ORADATA\DB10\O1_MF_USERS_35OQZZ4H_.DBF users.dbf
The process cannot access the file because it is being used by another process.

SQL> host del C:\ORACLE\ORA10\ORADATA\DB10\O1_MF_USERS_35OQZZ4H_.DBF
C:\ORACLE\ORA10\ORADATA\DB10\O1_MF_USERS_35OQZZ4H_.DBF

The process cannot access the file because it is being used by another process.

SQL> host ren C:\ORACLE\ORA10\ORADATA\DB10\EXAMPLE01.DBF example02.dbf
The process cannot access the file because it is being used by another process.

Also a straight O/S effort:
C:\Windows\System32>del C:\ORACLE\ORA10\ORADATA\DB10\O1_MF_USERS_35OQZZ4H_.DBF

C:\ORACLE\ORA10\ORADATA\DB10\O1_MF_USERS_35OQZZ4H_.DBF
The process cannot access the file because it is being used by another process.

Moving files in Mount State

On Feb 3, I posted “Startup steps review” . Chen Shapira commented

And just for completeness -
You can move data files when in "alter database mount"
Recovery happens on "alter database open"

Which prompts me to try it out. I hadn’t thought about this. Here is how I worked through Chen’s information about moving data files while in the mount stage in my toy database.

I start off with the datafile EXAMPLE01.DBF

SQL> SELECT NAME FROM V$DATAFILE WHERE NAME LIKE '%EXAMPLE%';

NAME
--------------------------------------------------------------------------------
C:\ORACLE\ORA10\ORADATA\DB10\EXAMPLE01.DBF

I 'lose' the file - by shutting down the database, renaming the file in the OS and
bringing the database back to mount.

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> host ren c:\Oracle\ora10\oradata\db10\EXAMPLE01.DBF EXAMPLE01_X.DBF

SQL> host dir c:\Oracle\ora10\oradata\db10\E*

Directory of c:\Oracle\ora10\oradata\db10

02/11/2008 11:36 AM 104,865,792 EXAMPLE01_X.DBF

1 File(s) 104,865,792 bytes

SQL> startup mount

ORACLE instance started.

Total System Global Area 201326592 bytes
Fixed Size 1289700 bytes
Variable Size 150995484 bytes
Database Buffers 46137344 bytes
Redo Buffers 2904064 bytes
Database mounted.

I was surprised to see, that in the mount state Oracle takes the file names from the control file, but doesn’t check the operating system to see if they really exist.

SQL> SELECT NAME FROM V$DATAFILE WHERE NAME LIKE '%EXAMPLE%';

NAME
--------------------------------------------------------------------------------
C:\ORACLE\ORA10\ORADATA\DB10\EXAMPLE01.DBF

It is on the open that Oracle checks reality.

SQL> alter database open;

alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\ORACLE\ORA10\ORADATA\DB10\EXAMPLE01.DBF'

And as Chen says – you can move files when in mount phase - so I correct the problem that I made when I 'lost' the file:

SQL> alter database rename file 'C:\ORACLE\ORA10\ORADATA\DB10\EXAMPLE01.DBF' to 'c:\Oracle\ora10\oradata\db10\EXAMPLE01_X.DBF';

Database altered.

SQL> SELECT NAME FROM V$DATAFILE WHERE NAME LIKE '%EXAMPLE%';

NAME
--------------------------------------------------------------------------------
C:\ORACLE\ORA10\ORADATA\DB10\EXAMPLE01_X.DBF

And just to look at everything since I am demonstrating:

SQL> alter database backup controlfile to trace;
Database altered.

Now the control file knows where the lost file is. The abbreviated contents of the trace are:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DB10" NORESETLOGS NOARCHIVELOG
...
LOGFILE
......

DATAFILE
...
'C:\ORACLE\ORA10\ORADATA\DB10\EXAMPLE01_X.DBF'

Now I can open the database and all is well:

SQL> alter database open;

Database altered.

SQL> SELECT NAME FROM V$DATAFILE WHERE NAME LIKE '%EXAMPLE%';

NAME
--------------------------------------------------------------------------------
C:\ORACLE\ORA10\ORADATA\DB10\EXAMPLE01_X.DBF

Startup steps review

Do you remember what happens as an instance starts? I didn't. So, to review...

Assuming that you issue the start command in steps:

SQL> STARTUP NOMOUNT;

1 Oracle first opens and reads the initialization parameter file.

2 The memory areas associated with the database instance are allocated. Their size was specified in the parameter file. These allocated memory areas and parameters include the following:

• SGA_TARGET parameter sets the amount of memory available to the SGA.
  
• DB_CACHE_SIZE (default block size) &DB_nK_CACHE_SIZE (non-default block size)
• SHARED_POOL_SIZE (contains the Library Cache & Data Dictionary Cache)
• LOG_BUFFER
  
• LARGE_POOL_SIZE ,
  
• JAVA_POOL_SIZE, & STREAMS_POOL_SIZE
  
• DB_KEEP_CACHE_SIZE & DB_RECYCLE_CACHE_SIZE

3 Oracle background processes are started. In most basic terms this includes the following Oracle background processes:

• DBWR - Database Writer process writes dirty buffers from the database block cache to the data files.
• CKPT - Writes checkpoint information to control files and data file headers.
• LGWR - Log Writer process writes the log buffers out to the redo logs.
• PMON - Process Monitor process recovers failed process resources.
• SMON - System Monitor process recovers after instance failure and monitors temporary segments and extents.
• ARCH - (Optional) Archive process writes filled redo logs to the archive log location(s).
• MMON - The Oracle 10g background process to collect statistics for the Automatic Workload Repository (AWR).

Together, these processes and the associated allocated memory are called the Oracle instance. Once the instance has started successfully, the database is considered to be in the nomount stage.

SQL> alter database mount;

1. When the startup command enters the mount stage, it opens and reads the control file.
2. From the control file Oracle determines the location of the datafiles, but does not yet open them.

SQL> alter database open;

When Oracle opens the database, it

1. accesses all of the datafiles associated with the database. If a file is missing it is reported.

2. makes sure that all of the database datafiles are consistent.

And now the database is ready for use.