Missing or extra files in ControlFile

At the end of partial restore of database I've noticed entries MISSING00008, MISSING00009, etc. Oracle created those entries because some datafiles were found in data dictionary but not in new controlfile.


I didn't know that Oracle will add "MISSINGnnnn" entry for read only and offline files also.


Small demo case...

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Create read only tablespace with one table:

SQL> create tablespace DEMO_RO datafile '/oracle/oradata/test10/demo_ro.dbf' size 50M;

Tablespace created.

SQL> create table test_ro (id number, name varchar2(20)) tablespace DEMO_RO;

Table created.

SQL> insert into test_ro values (1,'Cartman');

1 row created.

SQL> commit;

Commit complete.

SQL> alter tablespace DEMO_RO read only;

Tablespace altered.

Create tablespace with one table and alter this tablespace with "offline normal" opinion:

SQL> create tablespace DEMO_OFF datafile '/oracle/oradata/test10/demo_off.dbf' size 50M;

Tablespace created.

SQL> create table test_off (id number, name varchar2(20)) tablespace DEMO_OFF;

Table created.

SQL> insert into test_off values (5,'Kenny');

1 row created.

SQL> commit;

Commit complete.

SQL> alter tablespace DEMO_OFF offline normal;

Tablespace altered.

Backup controlfile to trace and shutdown database:

SQL> alter database backup controlfile to  trace as  '/tmp/ctlfile.sql';

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Check controlfile script:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TEST10" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oracle/oradata/test10/redo01.log'  SIZE 50M,
  GROUP 2 '/oracle/oradata/test10/redo02.log'  SIZE 50M,
  GROUP 3 '/oracle/oradata/test10/redo03.log'  SIZE 50M
DATAFILE
  '/oracle/oradata/test10/system01.dbf',
  '/oracle/oradata/test10/undotbs01.dbf',
  '/oracle/oradata/test10/sysaux01.dbf',
  '/oracle/oradata/test10/users01.dbf'
CHARACTER SET UTF8
;

It is interesting to notice that newly created database files '/oracle/oradata/test10/demo_ro.dbf' and '/oracle/oradata/test10/demo_off.dbf' are missing.



I will delete existing controlfiles to force myself to create new ones.

$ rm control01.ctl control02.ctl control03.ctl

Create new controlfile based on script above and open database with resetlogs opinion:

$ sqlplus "/as sysdba"

SQL> @/tmp/ctlfile.sql
ORACLE instance started.

Total System Global Area  608174080 bytes
Fixed Size                  1268896 bytes
Variable Size             163578720 bytes
Database Buffers          436207616 bytes
Redo Buffers                7118848 bytes

Control file created.


SQL> alter database open resetlogs;

Database altered.

Excerpt from alert.log:

Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'DEMO_RO' #6 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'DEMO_OFF' #7 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #5 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00005' in the controlfile.
File #6 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00006' in the controlfile.
Dictionary check complete

During dictionary checking Oracle found that some files exist in data dictionary but not in controlfile.


To query database files:

SQL> col tablespace_name for a30
SQL> col file_name for a50
SQL> set lines 200
SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ --------------------------------------------------
USERS                          /oracle/oradata/test10/users01.dbf
SYSAUX                         /oracle/oradata/test10/sysaux01.dbf
UNDOTBS1                       /oracle/oradata/test10/undotbs01.dbf
SYSTEM                         /oracle/oradata/test10/system01.dbf
DEMO_RO                        /oracle/product/10.2.0/dbs/MISSING00005
DEMO_OFF                       /oracle/product/10.2.0/dbs/MISSING00006

6 rows selected.

I will try to get data from test tables:

SQL> select * from test_ro;
select * from test_ro
              *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/oracle/product/10.2.0/dbs/MISSING00005'


SQL> select * from test_off;
select * from test_off
              *
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/oracle/product/10.2.0/dbs/MISSING00006'

As expected - that operation failed. According to ORA-01111 error I have to rename datafile name to correct file and make it accessible.


Renaming:

SQL> alter database rename file 'MISSING00006'
  to '/oracle/oradata/test10/demo_off.dbf';
 
Database altered.


SQL> alter database rename file 'MISSING00005'
  to '/oracle/oradata/test10/demo_ro.dbf';
 
 
SQL> alter tablespace demo_ro online;

Tablespace altered.

SQL> alter tablespace demo_off online;

Tablespace altered.

Now to get data from test tables:

SQL> select * from test_ro;

        ID NAME
---------- --------------------
         1 Cartman

SQL> select * from test_off;

        ID NAME
---------- --------------------
         5 Kenny

I can make datafile accessible by renaming MISSINGnnnn entry only if datafile was offline normal or read only.



REFERENCES:
Managing Control Files