missing datafile
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: missing datafile

  1. #1
    Join Date
    Oct 2002
    Posts
    391

    missing datafile

    we did a offline backup. backup was successful. however, in our script, we forget to include a newly added datafile into the backups. therefore, the successful backup is short of one missing data file. my question is that is it possible to restored this offline backup in another box considering that a datafile is not present.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you will have to offline that datafile and drop the tablespace it was part of

  3. #3
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Yes you can. Only problem would be, you will need to drop that datafile (using alter database datafile 'name' offline drop) after doing the database mount. Then you can open it normally.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  4. #4
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Yes you can. Only problem would be, you will need to drop that datafile (using alter database datafile 'name' offline drop) after doing the database mount. Then you can open it normally.
    Amar, what will happen to the tablespace and the objects in it..?

    Say table A got 3 extents. The latest extent is in the new data file which we are droping.? So what will happened to the table? And the tablespace..?

    Regards,

    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  5. #5
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by Thomasps
    Amar, what will happen to the tablespace and the objects in it..?

    Say table A got 3 extents. The latest extent is in the new data file which we are droping.? So what will happened to the table? And the tablespace..?

    Regards,

    Thomas
    The moment you try to hit the block on the missing data file it will throw an error.

    Thats one way to bring up the database, but yes if its the only datafile of that tablespace then you need to drop that tablespace as well.

    If more than one datafiles belong to that tablespace then you can perform selects and inserts on those tables which do not belong to that dropped datafile. Only those blocks will not be accessible which were on that droppped datafile.

    Code:
    sys@ACME.WORLD> create tablespace drop_me datafile 'D:\ORACLE\ORADATA\ACME\drop_me.dbf' size 2M;
    
    Tablespace created.
    
    sys@ACME.WORLD> create table drop_me_tab (x int) tablespace drop_me;
    
    Table created.
    
    sys@ACME.WORLD> insert into drop_me_tab values(&number);
    Enter value for number: 1
    old   1: insert into drop_me_tab values(&number)
    new   1: insert into drop_me_tab values(1)
    
    1 row created.
    
    sys@ACME.WORLD> /
    Enter value for number: 2
    old   1: insert into drop_me_tab values(&number)
    new   1: insert into drop_me_tab values(2)
    
    1 row created.
    
    sys@ACME.WORLD> /
    Enter value for number: 3
    old   1: insert into drop_me_tab values(&number)
    new   1: insert into drop_me_tab values(3)
    
    1 row created.
    
    sys@ACME.WORLD> /
    Enter value for number: 4
    old   1: insert into drop_me_tab values(&number)
    new   1: insert into drop_me_tab values(4)
    
    1 row created.
    
    sys@ACME.WORLD> /
    Enter value for number: 5
    old   1: insert into drop_me_tab values(&number)
    new   1: insert into drop_me_tab values(5)
    
    1 row created.
    
    sys@ACME.WORLD> /
    Enter value for number: 6
    old   1: insert into drop_me_tab values(&number)
    new   1: insert into drop_me_tab values(6)
    
    1 row created.
    
    sys@ACME.WORLD> commit;
    
    Commit complete.
    
    sys@ACME.WORLD> alter tablespace drop_me add datafile 'D:\ORACLE\ORADATA\ACME\drop_me01.dbf' size 2M;
    
    Tablespace altered.
    
    sys@ACME.WORLD> alter database datafile 'D:\ORACLE\ORADATA\ACME\drop_me01.dbf' offline drop;
    
    Database altered.
    
    sys@ACME.WORLD> select * from drop_me_tab;
    
             X
    ----------
             1
             2
             3
             4
             5
             6
    
    6 rows selected.
    
    sys@ACME.WORLD> alter tablespace drop_me add datafile 'D:\ORACLE\ORADATA\ACME\drop_me02.dbf' size 2M;
    
    sys@ACME.WORLD> select name,file# from v$datafile where name like '%DROP_ME%';
    
    NAME                                          FILE#
    ---------------------------------------- ----------
    D:\ORACLE\ORADATA\ACME\DROP_ME.DBF               30
    D:\ORACLE\ORADATA\ACME\DROP_ME01.DBF             31
    D:\ORACLE\ORADATA\ACME\DROP_ME02.DBF             32
    
    sys@ACME.WORLD> declare
      2  x number;
      3  begin
      4  for x in 1..10000 loop
      5  insert into drop_me_tab values (x);
      6  end loop;
      7  end;
      8  /
    
    PL/SQL procedure successfully completed.
    
    sys@ACME.WORLD> commit;
    
    Commit complete.
    
    sys@ACME.WORLD> select segment_name,tablespace_name,file_id from dba_extents where segment_name like 'DROP_ME%';
    
    SEGMENT_NAME                             TABLESPACE_NAME                   FILE_ID
    ---------------------------------------- ------------------------------ ----------
    DROP_ME_TAB                              DROP_ME                                30
    .
    .
    .
    DROP_ME_TAB                              DROP_ME                                30
    DROP_ME_TAB                              DROP_ME                                32
    
    17 rows selected.
    
    sys@ACME.WORLD> alter database datafile 'D:\ORACLE\ORADATA\ACME\drop_me02.dbf' offline drop;
    
    Database altered.
    
    sys@ACME.WORLD> select count(*) from drop_me_tab;
    select count(*) from drop_me_tab
                         *
    ERROR at line 1:
    ORA-00376: file 32 cannot be read at this time
    ORA-01110: data file 32: 'D:\ORACLE\ORADATA\ACME\DROP_ME02.DBF'
    
    sys@ACME.WORLD> select * from drop_me_tab where rownum < 20;
    
             X
    ----------
             1
             2
             3
             4
             5
             6
             7
             8
             1
             2
             3
             4
             5
             6
             7
             8
             9
            10
            11
    
    19 rows selected.
    
    sys@ACME.WORLD> insert into drop_me_tab values(20);
    
    1 row created.
    
    sys@ACME.WORLD> commit;
    
    Commit complete.
    
    sys@ACME.WORLD>
    Last edited by adewri; 08-12-2003 at 03:01 AM.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  6. #6
    Join Date
    May 2002
    Posts
    2,645

    Re: missing datafile

    Originally posted by yls177
    we did a offline backup. backup was successful. however, in our script, we forget to include a newly added datafile into the backups. therefore, the successful backup is short of one missing data file.
    How or why would you call that a successful backup? Forgetting a datafile during a backup is a "successful" backup? Wow.

  7. #7
    Join Date
    Oct 2002
    Posts
    391
    a successful backup cos, we have a script which reads in the list of filesystems from a file. what happened is that this file was not updated to reflect the latest filesystem. so what happens is that the backup automatiaclly will not backup it since the filesystem file did not register the new filesystem.


    anyway....


    the good news is we managed to get it up and running without offline any datafile.

    steps
    1) copy the datafile to the restored database
    2) startup
    3) of course, there will be errros
    4) re-create control file
    5) recover database using backup controlfile until cancel
    6) will prompt for which online log file
    7) alter dataabse open resetlogs;
    8) and it works
    9) smile

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by yls177

    9) smile
    you will start crying after a while when data in that lost datafile is needed

  9. #9
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Originally posted by yls177
    a successful backup cos, we have a script which reads in the list of filesystems from a file. what happened is that this file was not updated to reflect the latest filesystem. so what happens is that the backup automatiaclly will not backup it since the filesystem file did not register the new filesystem.
    You should redesign your script to get the datafile list from the database.
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  10. #10
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Originally posted by yls177
    a successful backup cos, we have a script which reads in the list of filesystems from a file.
    Hard-coding... ekkkk. ...SQLBacktrack by any chance?
    Last edited by grjohnson; 08-13-2003 at 07:46 AM.
    OCP 8i, 9i DBA
    Brisbane Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width