Tuesday, May 11, 2010

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

10 comments:

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

    I still do not understand why RO and offline TBLSPC were missing?

    Could you check the same (create control file to trace) after adding an new table space and one table in it? Then it should be there!!

    THX in advance for asking you such a thing?

    ReplyDelete
  2. Hi Damir,

    excerpt from control file trace:

    ...
    RECOVER DATABASE USING BACKUP CONTROLFILE

    -- Database can now be opened zeroing the online logs.
    ALTER DATABASE OPEN RESETLOGS;

    -- Files in normal offline tablespaces are now named.
    ALTER DATABASE RENAME FILE 'MISSING00006'
    TO '/oracle/oradata/test10/demo_off.dbf';

    -- Files in read-only tablespaces are now named.
    ALTER DATABASE RENAME FILE 'MISSING00005'
    TO '/oracle/oradata/test10/demo_ro.dbf';

    -- Online the files in read-only tablespaces.
    ALTER TABLESPACE "DEMO_RO" ONLINE;
    -- Commands to add tempfiles to temporary tablespaces.
    -- Online tempfiles have complete space information.
    -- Other tempfiles may require adjustment.
    ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/test10/temp01.dbf'
    SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
    -- End of tempfile additions.


    When I said that demo_ro.dbf and demo_off.dbf files were missing, I ment that they were missing from CREATE CONTROLFILE statement.

    After initial database opening with resetlogs opinion there will be 'MISSINGnnnn' entries for read only and offline normal files.

    As it is noted in script I must rename those entries to make files accessible.

    Regards,
    Marko

    ReplyDelete
  3. Marko,

    What I wanted to say is: If you create a tablespace and then create backup control file to trace...then this "new" tablespace is there?
    When you place offline and again backup controlfile to trace...it is gone?
    This is my ask...

    ReplyDelete
  4. Yes, when you create new tablespace and create backup controlfile to trace - then new tablespace will be there.

    If you alter this new tablespace to be offline or read only and then create backup controlfile to trace it will be left out from CREATE CONTROLFILE statement. Oracle will add MISSINGnnnn entry for this tablespace and you should rename that entry to make tablespace accessible.

    Enjoy,
    Marko

    ReplyDelete
  5. Wery cool and thank you for clearing this issue out. never thought it would work like this...but now I know!
    ;-)

    ReplyDelete
  6. Marko,
    If you know....I'm tired of testing...
    If you put tablespace offline and then do RMAN full backup, will RMAN backup that tablespace? Suppose not?
    THX

    ReplyDelete
  7. Hi,

    RMAN will backup read only and offline tablespaces. Just tested RMAN backup & restore for this two tablespaces.

    Regards,
    Marko

    ReplyDelete
  8. I think this surprise you as well (after fact with control file)..

    However THX,
    Damir Vadas
    http://damir-vadas.blogspot.com

    ReplyDelete
  9. Yes,

    this behavior was unexpected and new for me.
    That is why I tested all this.

    Best way to learn something new :)

    Enjoy,
    Marko

    ReplyDelete
  10. VERY NICE example Marko! Helped me out greatly.

    Much appreciated !!!

    ReplyDelete