DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: File is missed during the control file creation

  1. #1
    Join Date
    Aug 2000
    Posts
    194
    Hi All,

    I am trying to test standby database on the same machine. After activating the standby machine, I can not mount the database as it requires the lock file same as the database name (ie, lk<db_name>). Since this file is already used by the primary database, I have to create the control file with a new database name. (say "Create control file set database new_db resetlogs ...da dada")

    The problem is with the "Create Controlfile"

    I have the following situation. Please let me know how to resolve this.

    I created the control file with all the data files, except one. (I purposely did this, as I want to test, wht happens if someone forget to include some of the datafiles)

    I open the database (I can open only with the resetlogs)

    Now, I select from the table which is part of the omitted datafile.

    I get the following error, as expected,
    ORA-00376: file 12 cannot be read at this time
    ORA-01111: name for data file 12 is unknown - rename to correct file
    ORA-01110: data file 12: '/oracle/product/8.1.6/dbs/MISSING00012'

    Here is my question:

    1, is there anyway to find which file was the original file?
    (ie. whats the name of the original file of "/oracle/product/8.1.6/dbs/MISSING00012")

    2, Once I have the file name by some means, how do make it part of the database? Is it possible at all, since I did the "resetlogs"?

    3, What is the correct way of creating a control file, so that even if I miss the datafile the first time I should be able to add the file later (may be by recreating the controlfile) and should be able to get the data back?.

    Is it possble after doing the "resetlogs"?. If not, how to
    avoid the "resetlogs" until I make sure all the datafiles are
    in place?.

    How do I make sure all the datafiles are in place before
    doing the "resetlogs"?

    Note: I have done many control file recreation, but haven't tried anything with "RESETLOGS" and "MISSING" filename.

    Please advise me of the right way.

  2. #2
    Join Date
    Aug 2000
    Posts
    194
    Hello!

    Anyone got any ideas on this???

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    I'm little confused with your question. Can you explain a little more what are you trying to do?

    Are you talking about a standby database as an "automatic standby", where standby is in continuous recovery mode (or tempraryly opened at read only)? If this is true, I don't understand what do you mean by "creating a controlfile". Are you done this manualy? The controlfile for stanby database is a special controlfile and should be crated on the primary database by using
    ALTER DATABASE CREATE STANDBY CONTROLFILE;

    Also, it is not possible to simply open standby database. You can temporarilly open it in read only mode, but as soon as you open it in read-write, it becomes a normal database, it can never be standby again. In any case, for protecting you not to open the standby db in read-write by mistake, there is a special command you have to use to open it:
    ALTER DATABASE ACTIVATE STANDBY DATABASE;

    Also, the RESETLOGS option is in contradiction with the concept of the standby database, it is simply not applicable to standby database.

    Please clariffy if I've missed your point,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Aug 2000
    Posts
    194
    Hi jmodic:

    Let me explain (atleasr try) exactly what I am trying to do.

    My objective is to test Standby database on the same node as the primary database. At this point "Automatic Standby" is not an option for me as our DB is Oracle Standard Edition 8.1.6.

    Timeframe 1: Time to set the Stadby DB

    1, I copied the datafiles from primary to STBY

    2, Started the Standby Database in nomount with the initSTBY.ora

    3, Alter database mount standby database ;

    4, Alter database open read only ; -- No problem untill here


    Timeframe 2: Time to activate the Standby DB

    1, Shutdown the Standby DB

    2, Startup nomount pfile=initSTBY.ora

    3, Alter database mount standby database

    4, alter database activate standby database -- Activated successfully

    5, Shutdown immediate ;

    6, Startup ; -- now I get the following error message "ORA-01102: cannot mount database in EXCLUSIVE mode "

    This is because Oracle checks the lk<db_name> to see whether the database is already mounted. In the case of standby database both primary and standby have the same <db_name>. Note, I set the "lock_name_space" parameter in the initSTBY.ora, but doesn't seem to be effective once the standby is activated.

    OK, I talked to ORACLE and they send me a doccument which recommends to create the control file once the Standby DB is activated.

    Now, the question is on the "CREATE CONTROLFILE". Forget about the Standby Database. My objective is just to Create the Standby Database with "NEW" Database Name.

    Here goes the Steps:

    Assume I do not have the "CREATE CONTROLFILE" created by the "Alter database backup controlfile to trace"

    1, I create the Contro file manually.

    STARTUP NOMOUNT pfile=/path/initSTBY.ora
    CREATE CONTROLFILE SET DATABASE "STBY" RESETLOGS ARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 254
    MAXINSTANCES 8
    MAXLOGHISTORY 904
    LOGFILE
    GROUP 1 '/path/redo01.log' SIZE 10000K,
    GROUP 2 '/path/redo02.log' SIZE 10000K,
    GROUP 3 '/path/redo03.log' SIZE 10000K,
    DATAFILE
    '/path/system01.dbf',
    '/path/tools01.dbf',
    '/path/rbs01.dbf',
    '/path/rbs02.dbf',
    '/path/teststby01.dbf'
    CHARACTER SET WE8ISO8859P1
    ;

    Note: At this point I missed to include the file
    '/path/teststby02.dbf' in the DATAFILE clause

    Also, "NORESETLOGS" is not an option as I am renaming the DATABSE name.

    2, I open the Database (with Resetlogs)

    3, query the table (which is part of the missed file), gives the error
    ORA-00376: file 12 cannot be read at this time
    ORA-01111: name for data file 12 is unknown - rename to correct file
    ORA-01110: data file 12: '/oracle/product/8.1.6/dbs/MISSING00012'

    I guess now my previously posted quesions make sence.

    for reference, the prev posted questions again:

    1, is there anyway to find which file was the original file?
    (ie. whats the name of the original file of "/oracle/product/8.1.6/dbs/MISSING00012")

    2, Once I have the file name by some means, how do make it part of the database? Is it possible at all, since I did the "resetlogs"?

    3, What is the correct way of creating a control file, so that even if I miss the datafile the first time I should be able to add the file later (may be by recreating the controlfile) and should be able to get the data back?.

    Is it possble after doing the "resetlogs"?. If not, how to
    avoid the "resetlogs" until I make sure all the datafiles are
    in place?.

    How do I make sure all the datafiles are in place before
    doing the "resetlogs"?


    Finally on JMODIC'S response:
    -----------------------------------

    1, yes, I initially created the standby controlfile using "ALTER DATABASE CREATE STANDBY CONTROLFILE; "

    //"Also, the RESETLOGS option is in contradiction with the concept of the standby database, it is simply not applicable to standby database"//

    The above is not true. When you activate the database it anyway issues a "RESETLOGS". You can query the v$database after activating the DB to confirm this. Thats the reason, oracle doccument says, you cannot apply anymore logs from the primary to standby once the Standby is activated. (now the prev standby is ur primary DB and u may have to create another standby based on the new primary)

    If you are talking about the "Graceful Switchover", you are absolutely correct. There is no question of "RESETLOGS".

    I tried this first, but again the problem is, I can not have the same "DB_NAME" on the same machine (different INSTNACE names). I will deal with this latter, as I have too much at this time.

    ORACLE 8.1.6 Standard Edition on Linux 6.2


    Wow! Such a Llllloonng posting....

    Thanks for your response....

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Hi, gsprince!

    Yes, now it all makes much more sence, now I understand what you are trying to do. I'm affraid, however, I will not be of much help with your problem. But as you were saying you are *testing* your standby on the same server. So the easiest way of performing the testing of activating the standby is to imitate the real situation: simply shut down your primary database, as in real life the only time you try to activate the standby is when your primary has crashed and gone :-). But I suppose this is not an option for you, probably you have to have your primary db up and running all the time. Another easy workaround would be to get another server and place your standby there (getting a tiny server for testing purposes should not be of too much problem nowadays). I'm affraid I've not suggested you anything you haven't known before, but in your position would simply find an easiest way around this problem and I think one of the above suggestion is easier to acchive than to find a solution to your ORA problem...

    //Jm: "Also, the RESETLOGS option is in contradiction with the concept of the standby database, it is simply not applicable to standby database"//
    \\Gs: "The above is not true. When you activate the database it anyway issues a "RESETLOGS"....\\

    Yes, sure you are right, I was just not aware of what are you trying to do. I ment to say that once you open it with RESETLOGS it can't be used as a standby anymore. I was under the impression you were trying to open it somehow, then close it and put it back into standby database again....

    And now to your questions.

    1, is there anyway to find which file was the original file? (ie. whats the name of the original file of "/oracle/product/8.1.6/dbs/MISSING00012")

    I don't think there is a way for this. All the information Oracle gets about datafiles it gets from the controlfile, so if you forgot to include one of datafiles in your CREATE CONTROLFILE there is now way it could know about it. If Oracle would know the name of a missing file it would name it in ORA message (at least I hope it would be so kind).

    2, Once I have the file name by some means, how do make it part of the database? Is it possible at all, since I did the "resetlogs"?

    No, I don't think it is possible. The only way would be to restore all files from a backup created prior to RESTLOGS and open the database with RESETLOGS again...

    3, What is the correct way of creating a control file, so that even if I miss the datafile the first time I should be able to add the file later (may be by recreating the controlfile) and should be able to get the data back?.

    You can't add missed datafiles to a database once it is open. Of course, if you opened with NORESETLOGS you could allways restore all backup datafiles again and do a media recovery and no data will be lost. But the only proper way in such situation is to have a *valid* database layout recorded befor the emergency situation occures, so that you don't miss the datafile in CREATE CONTROLFILE in the first place. That is why BACKUP CONTROLFILE TO TRACE should be a part of *each and every* proper periodical backup scenario.

    HTH (although I think I didn't say anything you haven't allready knew),
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Aug 2000
    Posts
    194
    Thanks Jm:

    I sure am getting a new server for the testing. Thought of trying something nasty before getting the server.

    I wanted to try on the same machine, because we have few databases on different nodes with the same DB_NAME (were created sometime ago by someone). I was wondering whether its posible to have less standby nodes compared to the production nodes, so that I can put 2 or 3 databases on the same standby node.

    Now, I may have to rename some of the databases differently so that they can be on the same standby machine(I hate the resetlogs while renaming the DB) or I should have one standby node for each primary node.

    Once again thanks for sharing your thoughts.

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