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