DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

Thread: exceed limit of datafile numbers

  1. #11
    Join Date
    Aug 2000
    Posts
    194
    I guess, your create control file didn't go succesfully.

    (check whether ur original conrol fle is overwritten)

    Shutdown the DB with abort option, if you cannot shutdown it with "immedite".

    startup the DB; --needed only if u use "shutdown ABORT"

    Shutdown the DB in Normal/Immediate mode; -- needed only if the last shutdown was not normal/immediate

    Move/rename the old Control file (OS command) --just in case needed

    Startup nomount ;

    issue the create controlfile ;

    Alter database mount ;

    alter database open ;

  2. #12
    Join Date
    Oct 2000
    Posts
    80
    The problem is with the REUSE clause of the create controlfile statement. Increasing maxdatafiles increases the size of the new controlfile and it cannot reuse the file already on disk.
    Kill the instance
    Do a cold backup
    Delete the old control file at OS level
    Delete the reuse clause from your trace file/script.
    Run the script.

  3. #13
    Join Date
    Sep 2000
    Posts
    31
    Hi guys,
    I followed these steps:
    Kill the instance (test & prod)
    Do a cold backup
    Delete the old control file at OS level
    Delete the reuse clause from your trace file/script.
    Run the script.
    And i got these errors:
    SQLDBA> connect internal
    Connected.
    setenv oracle_sid test
    sqldba lmode=y
    SQLDBA> connect internal
    SQLDBA> shutdown immediate
    SQLDBA> @recreate_arc_ctrl.sql
    ORACLE instance started.
    CREATE CONTROLFILE USE DATABASE "TEST" NORESETLOGS NOARCHIVELOG
    *
    ORA-01967: invalid option for CREATE CONTROLFILE
    ORA-01507: database not mounted
    ORA-01507: database not mounted

    SQLDBA> alter database mount;
    Statement processed.
    SQLDBA> alter database open;
    Statement processed.
    SQLDBA> select * from v$database;
    NAME CREATED LOG_MODE CHECKPOINT ARCHIVE_CH
    --------- -------------------- ------------ ---------- ----------
    PROD 02/08/96 15:46:15 NOARCHIVELOG 1.9260E+12 1.9260E+12
    1 row selected.

    I guess my server does not recognize my test instance anymore. Is it crashed? Please help.

    Thanks,
    Jn1971 :(

  4. #14
    Join Date
    Oct 2000
    Posts
    80
    Delete the word "USE" from your create controlfile command.
    Also edit the script to add a direct reference to your inittest.ora parameter file as follows:
    startup nomount pfile=.................

  5. #15
    Join Date
    Sep 2000
    Posts
    31
    Hi Jdoyle,
    I did what you suggested but i still get an error:
    error in identifying file '....../dbora.dbf'.

    I think my test instance is crashed. I have daily backup is sent to server's tape. I have not done restoring bk before. Could you tell me how i retract it out? Your help is appreciated very much.

    Jn1971

  6. #16
    Join Date
    Aug 2000
    Posts
    194
    do you have the particular file .../dbora.dbf in the particular location as pointed by the error message?

    If you move the file somewhere else, edit the controlfile accordingly to have that file. (Make sure u r not using the file from another DB)


  7. #17
    Join Date
    Oct 2000
    Posts
    80
    Some clarification is in order here.
    Remember, always, ONE THING AT A TIME.
    First, you have two databases, PROD and TEST. The problem with the maxdatafiles exceeded was, I believe, with your PROB db, yes? Assuming this is your production database it must be your first priority.
    Re: PROD
    Is this database working?
    Have you done the alterations to the controlfile trace dump, and is your specified parameter file path correct and pointing to an existing file?

    Re: TEST; several questions:
    Is this DB on the same machine as PROD?
    Is it running in ARCHIVELOG mode?
    What is your environment, OS, etc.
    What exactly are you trying to do with test? We cannot deal with recovery issues just yet, and perhpas may not need to. You probably won't need to restore from tape.

    Get back to me on this.

  8. #18
    Join Date
    Sep 2000
    Posts
    31
    Hi Jdoyle,


    Re: PROD
    Is this database working?
    Have you done the alterations to the controlfile trace dump, and is your specified parameter file path correct and pointing to an existing file?


    Yes my prod instance is on and working fine.
    I altered the controlfile trace dump as suggested. My parameter file path correct and pointing to an existing file.


    Re: TEST; several questions:
    Is this DB on the same machine as PROD?
    Is it running in ARCHIVELOG mode?
    What is your environment, OS, etc.
    What exactly are you trying to do with test? We cannot deal with recovery issues just yet, and perhpas may not need to. You probably won't need to restore from tape.

    Yes the test db is on the same machine as PROD.
    It is not running in ARCHIVELOG mode.
    my OS is unix. Oracle version is 7.1.4
    Since i have a exceed maxdatafile number, I am trying to modify the maxdatafile number on the test instance 1st and if it works i'll do it on the PROD one.

    I'd like to tell you this:

    I checked audit files and trace files of the test instance: i note that : the test instance is off @ 9am this morning when i ran the recreate_controlfile_script first time.
    Everytime i try to connect to the test db i got an error:
    ora-01033: oracle initialization or shutdown in progress.
    Everytime i try to bounce test db (after setenv oracle_sid test)
    server always shutdown and startup the PROD db not the TEST. Its seem to me that the TEST instance is hanging now.

    I hope you get the pictrue of my problem.

    Thanks,
    Jn1971
    jn

  9. #19
    Join Date
    Oct 2000
    Posts
    80
    Have you been able in the past to run both instances on the same machine?
    Kill the test instance using OS commands. I don't think you'll be able to shut it down using Oracle commands such as SVRMGRL>SHUTDOWN ABORT, but you can try this first. Use OS commands to verify that all TEST processes are down.
    BTW, I did all of the following for my own database and it worked for me.

    1.) Move all control files for the test database to another location for temporary storage. Make sure no control files exist in the locations specified in the parameter file.
    2.) edit the trace dump file, remove all the garbage at the top and remove the startup nomount command. The first operational command in the file should be the CREATE CONTROLFILE statement
    3.) SVRMGRL>Startup nomount pfile=inittest.ora (or whatever the complete path and filename for your TEST initialization parameter file is.)
    Does this work?
    4.) svrmgrl>@recreate_control_file_script

    This should do it for you.

  10. #20
    Join Date
    Aug 2000
    Posts
    194
    if each shutdown brings down the prod, that seems like the environment variable is not getting set properly.

    Depends on the "shell" you may have to use "set" or "export" ORACLE_SID. Once it is set, echo $ORACLE_SID before starting the instance and see the value is displaying properly (whatever you set)

    Since u r duplicating the PROD db, u have to change the filenames in ur control file to point to the new datafiles and also you may have to copy the files from the PROD to the new location before creating the control file. (I believe u already did that).


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