Forum Stats

  • 3,751,644 Users
  • 2,250,380 Discussions
  • 7,867,491 Comments

Discussions

control file

Deepak_DBA
Deepak_DBA Member Posts: 1,039
edited Oct 3, 2008 8:07PM in General Database Discussions
hi all,
i'm usin oracle 9i in rhel4.. in my test env.. i have a f1eo database..i was wokin in that .. unfortunately i deleted all my control files.. but the database was workin.. i gave the following command..
alter system switch logfile;
it does not show any err..i shut the database, but when i restart the db next day it shows cntrlfile missing.. luckily the deleted cntrl files was in my recyclebin.. i just restored it and opened the database.. the db was perfect.. what abt th result of the cmd i gave before the db shut.. what happen to the cntrl file.. where the information abt logswitch have been updated...

note - i don't have any multiplixed or controlfile trace.. as it was test Db there was no issue.. but i want to know what happen really..

thnx in advance,
regards,
Deepak...
Tagged:

Best Answer

  • jgarry
    jgarry Member Posts: 13,842
    Accepted Answer
    On unix style operating systems, deleting a file does not actually delete the file until all processes that are accessing the file are finished. So you delete the controlfile, Oracle is still happily updating it, but you can't see it in the directory where it was, the inode has been removed.

    When you shut down Oracle, the file goes away.

    When you start up Oracle, the file is gone. Oracle is not happy. You are lucky you have a recyclebin.
«1

Answers

  • 587671
    587671 Member Posts: 768
    "what abt th result of the cmd i gave before the db shut.. what happen to the cntrl file." , sorry please can you explain yourself better ?
  • Deepak_DBA
    Deepak_DBA Member Posts: 1,039
    hi,
    when i gave the cmd alter system switch logfile.. there was no controlfile physically.. then how the cmd switch logfile will procede, how will write the latest lcn in cntrl file..
  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    Hi,

    I understood the Problem you have specified. But as per the Scenario you mentioned, If it happens then, the DataBase would hang , since the DataBase would continue to Run until that time when it Process try to Change the Necessary Updates in Control File.

    As you said you have Performed the "alter system switch logfile;"

    In this Case it should Hang and should stopped / terminate manullay by User.

    One more I had tried to performed the same scenarii on my system.. but First of all could not able to delete
    Control Files...since it will ping out an Error Message stating that Control file is begin used my some person..

    Could not able to post the Screen shot.

    If you would like to know what happens exactly whne yuo Switch the Log file.
    Kindly go through thi link
    [http://www.ixora.com.au/tips/tuning/log_switch.htm]

    - Pavan Kumar N
  • Anand...
    Anand... Member Posts: 3,590
    Hi Pavan..

    I had done the same scenario few months back on RHEL4. I moved the controlfile to some other location, while the DB was up and running, and performed many alter system switch logfile.Log switched successfully.DB didn't hang.Shutdown immediate was also successfull but at startup i got the error.I tried to search a lot on it, but didn't get the answer.


    Anand
  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    Hi,

    Did you Check your Alert Log File..!!

    - Pavan Kumar N
  • Anand...
    Anand... Member Posts: 3,590
    Hi..

    Ya i had checked the alert log.No errors.I thought that DB will hang or atleat some error will be there in alert log, but was surprised to see, no errors.


    Anand
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    edited Sep 30, 2008 5:01PM
    Pavan,
    Pavan Kumar wrote:
    Hi,

    I understood the Problem you have specified. But as per the Scenario you mentioned, If it happens then, the DataBase would hang , since the DataBase would continue to Run until that time when it Process try to Change the Necessary Updates in Control File.

    As you said you have Performed the "alter system switch logfile;"

    In this Case it should Hang and should stopped / terminate manullay by User.
    The OP is right.
    The database is still running until bouncing, here below example on OEL4.6 :
    [[email protected]:/home/ora102]DEMO102$ sqlplus / as sysdba
    
    SQL*Plus: Release 10.2.0.4.0 - Production on Tue Sep 30 22:42:01 2008
    
    Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
    
    Connected to an idle instance.
    
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area  767557632 bytes
    Fixed Size                  1269832 bytes
    Variable Size             289411000 bytes
    Database Buffers          473956352 bytes
    Redo Buffers                2920448 bytes
    Database mounted.
    Database opened.
    SQL> select name from v$controlfile;
    
    NAME
    ------------------------------------
    /oradata2/DEMO102/ctl1/control01.ctl
    /oradata2/DEMO102/ctl2/control02.ctl
    /oradata2/DEMO102/ctl3/control03.ctl
    
    
    SQL> alter system switch logfile;
    
    System altered.
    
    SQL> !rm /oradata2/DEMO102/ctl3/control03.ctl
    
    SQL> alter system switch logfile;
    
    System altered.
    
    SQL> alter system switch logfile;
    
    System altered.
    
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area  767557632 bytes
    Fixed Size                  1269832 bytes
    Variable Size             289411000 bytes
    Database Buffers          473956352 bytes
    Redo Buffers                2920448 bytes
    ORA-00205: error in identifying control file, check alert log for more info
    
    
    SQL> !cp /oradata2/DEMO102/ctl2/control02.ctl /oradata2/DEMO102/ctl3/control03.ctl
    
    SQL> startup
    ORA-01081: cannot start already-running ORACLE - shut it down first
    SQL> startup force
    ORACLE instance started.
    
    Total System Global Area  767557632 bytes
    Fixed Size                  1269832 bytes
    Variable Size             289411000 bytes
    Database Buffers          473956352 bytes
    Redo Buffers                2920448 bytes
    Database mounted.
    Database opened.
    And nothing in alert.log.
    One more I had tried to performed the same scenarii on my system.. but First of all could not able to delete
    Control Files...since it will ping out an Error Message stating that Control file is begin used my some person..
    You maybe tried on Windows, did you ?

    Nicolas.
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    Deepak_DBA wrote:
    note - i don't have any multiplixed or controlfile trace.. as it was test Db there was no issue.. but i want to know what happen really..
    So, you mean you lost all your controlfile, right ?
    Did you check the udump directory if you have a backup of controlfile ?

    Nicolas.
  • jgarry
    jgarry Member Posts: 13,842
    Accepted Answer
    On unix style operating systems, deleting a file does not actually delete the file until all processes that are accessing the file are finished. So you delete the controlfile, Oracle is still happily updating it, but you can't see it in the directory where it was, the inode has been removed.

    When you shut down Oracle, the file goes away.

    When you start up Oracle, the file is gone. Oracle is not happy. You are lucky you have a recyclebin.
  • Anand...
    Anand... Member Posts: 3,590
    Hi..
    On unix style operating systems, deleting a file does not actually delete the file until all processes that are accessing the file are finished.
    Can you please elaborate more.I had permanently deleted the file, its not even in the recyclebin, I update few tables, wrote commit.now this commit will generate a SCN, which has to be written to the datafiles header and to the controlfile to maintain the consistency.But where will it write, where does oracle happily updates.Since, its a file some kind of IO will be done,file is not present so oracle should throw error.


    Anand
    Anand...
This discussion has been closed.