Physical Undo datafile deleted
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Physical Undo datafile deleted

  1. #1
    Join Date
    Aug 2001
    Location
    cuddapah
    Posts
    145

    Physical Undo datafile deleted

    !hello sir,

    Iam using oracle9i databse on win-2000.

    Database runing in Noarachive log mode.
    Database is in shutdown state.
    undomanagent AUTO.
    Physicaly i delete undo datafile using OS COMMAND.
    Can i get database back .I have no backup . No need of that undo datafile . I want to bring database back to normal stat. can i.
    I try lot but i fail to get back to normal state.


    akram shaik
    OCA
    akram_tri_a@yahoo.com

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Code:
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area  105978600 bytes
    Fixed Size                   453352 bytes
    Variable Size              83886080 bytes
    Database Buffers           20971520 bytes
    Redo Buffers                 667648 bytes
    Database mounted.
    ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
    ORA-01110: data file 2: 'D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF'
    
    SQL> alter database datafile 'D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF' offline;
    
    Database altered.
    
    SQL> alter database open;
    
    Database altered.
    
    SQL> alter tablespace UNDOTBS1 
    add datafile 'D:\ORACLE\ORADATA\TEST\UNDOTBS02.DBF' size 200M;
    
    Tablespace altered.
    
    SQL> alter database datafile 'D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF' 
    offline drop;
    
    Database altered.
    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area  105978600 bytes
    Fixed Size                   453352 bytes
    Variable Size              83886080 bytes
    Database Buffers           20971520 bytes
    Redo Buffers                 667648 bytes
    Database mounted.
    Database opened.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Aug 2001
    Location
    cuddapah
    Posts
    145
    Thanks . I forgot these steps to perfrom.

    Thank Q very much.Thomasps

  4. #4
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    You might get one more problem while doing any transaction- this is because ( I assume) the undo segments defined are refering to the lost file. I created another undo tablespace and changed the parameter undo_tablespace to the new one and problem resolved.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

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