FAQ
Hi All,

Trying to clone our PROD to a test database on the same machine for testing
our 734-->8i upgrade, and of course we're encountering the
"can't-start-the-new-without-shutting-down-the-old" database problem (ie,
ORA-9782 on the CREATE CONTROLFILE command). We've worked around this in
the past by scheduling a time to bring down PROD.

I know that this is the safest, most reliable way (besides NOT doing this on
a production box...but I have no choice), but believe it or not, I actually
saw a reference on a Metalink forum to rename the sgadefSID.dbf file, start
up the new database, then rename the file to its original name. Obviously
sgadef gets created at instance startup as a memory map, but it doesn't look
like it's used for anything after that (it's certainly doesn't seem to be
written to). I do know that you can get an octal dump of the file to
determine the shared memory ID for whatever reason.

Everything else I've heard/seen about this file has pretty much said DON'T
TOUCH IT!!! But if the instance only uses it at instance startup, is there
any harm in renaming it temporarily to get the other database up? If
so...why?

And have any of you tried renaming or deleting the file in your cloning (or
other) efforts? Did it blow up, or work?

TIA,

Steve

Steve Morrow
IT/Tech Support
University of South Florida
phone: 813-974-5519
smorrow_at_admin.usf.edu

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Morrow, Steve
INET: SMorrow_at_admin.usf.edu

Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

Search Discussions

  • nlzanen1_at_EY.NL at Apr 11, 2001 at 9:08 am
    Hi

    We clone very frequent (once a week at least) on the same machine and
    never encountered that problem yet, What are the steps you are taking??

    Jack

    "Morrow,

    Steve" To: Multiple recipients of list ORACLE-L

    Subject: Temporarily Renaming the
    sgadefSID.dbf File
    Sent by:

    root_at_fatcity.c

    om

    10-04-2001

    22:35

    Please respond

    to ORACLE-L

    Hi All,

    Trying to clone our PROD to a test database on the same machine for testing
    our 734-->8i upgrade, and of course we're encountering the
    "can't-start-the-new-without-shutting-down-the-old" database problem (ie,
    ORA-9782 on the CREATE CONTROLFILE command). We've worked around this in
    the past by scheduling a time to bring down PROD.

    I know that this is the safest, most reliable way (besides NOT doing this
    on
    a production box...but I have no choice), but believe it or not, I actually
    saw a reference on a Metalink forum to rename the sgadefSID.dbf file, start
    up the new database, then rename the file to its original name. Obviously
    sgadef gets created at instance startup as a memory map, but it doesn't
    look
    like it's used for anything after that (it's certainly doesn't seem to be
    written to). I do know that you can get an octal dump of the file to
    determine the shared memory ID for whatever reason.

    Everything else I've heard/seen about this file has pretty much said DON'T
    TOUCH IT!!! But if the instance only uses it at instance startup, is there
    any harm in renaming it temporarily to get the other database up? If
    so...why?

    And have any of you tried renaming or deleting the file in your cloning (or
    other) efforts? Did it blow up, or work?

    TIA,

    Steve

    Steve Morrow
    IT/Tech Support
    University of South Florida
    phone: 813-974-5519
    smorrow_at_admin.usf.edu

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Morrow, Steve
    INET: SMorrow_at_admin.usf.edu

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message

    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
    uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
    vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
    derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
    Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
    volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
    voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
    verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
    worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.

    Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
    vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
    en het origineel en eventuele kopieën te verwijderen en te vernietigen.

    Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
    voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
    algemene voorwaarden worden u op verzoek kosteloos toegezonden.

    The information contained in this communication is confidential and is
    intended solely for the use of the individual or entity to whom it is
    addressed. You should not copy, disclose or distribute this communication
    without the authority of Ernst & Young. Ernst & Young is neither liable for
    the proper and complete transmission of the information contained in this
    communication nor for any delay in its receipt. Ernst & Young does not
    guarantee that the integrity of this communication has been maintained nor
    that the communication is free of viruses, interceptions or interference.

    If you are not the intended recipient of this communication please return
    the communication to the sender and delete and destroy all copies.

    In carrying out its engagements, Ernst & Young applies general terms and
    conditions, which contain a clause that limits its liability. A copy of
    these terms and conditions is available on request free of charge.

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: nlzanen1_at_EY.NL

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message

    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Natasha Batson at Apr 11, 2001 at 1:18 pm
    Hi Steve

    I have actually done exactly what you are thinking of trying several times
    over i.e. renamed the
    sgadefSID.dbf file when trying to clone a database. Once the clone was
    created I just renamed it to its
    original name with no problems.

    Regards
    Natasha

    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, April 10, 2001 4:35 PM

    Hi All,

    Trying to clone our PROD to a test database on the same machine for testing
    our 734-->8i upgrade, and of course we're encountering the
    "can't-start-the-new-without-shutting-down-the-old" database problem (ie,
    ORA-9782 on the CREATE CONTROLFILE command). We've worked around this in
    the past by scheduling a time to bring down PROD.

    I know that this is the safest, most reliable way (besides NOT doing this on
    a production box...but I have no choice), but believe it or not, I actually
    saw a reference on a Metalink forum to rename the sgadefSID.dbf file, start
    up the new database, then rename the file to its original name. Obviously
    sgadef gets created at instance startup as a memory map, but it doesn't look
    like it's used for anything after that (it's certainly doesn't seem to be
    written to). I do know that you can get an octal dump of the file to
    determine the shared memory ID for whatever reason.

    Everything else I've heard/seen about this file has pretty much said DON'T
    TOUCH IT!!! But if the instance only uses it at instance startup, is there
    any harm in renaming it temporarily to get the other database up? If
    so...why?

    And have any of you tried renaming or deleting the file in your cloning (or
    other) efforts? Did it blow up, or work?

    TIA,

    Steve

    Steve Morrow
    IT/Tech Support
    University of South Florida
    phone: 813-974-5519
    smorrow_at_admin.usf.edu

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Morrow, Steve
    INET: SMorrow_at_admin.usf.edu

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Natasha Batson
    INET: nbatson_at_neal-and-massy.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Morrow, Steve at Apr 11, 2001 at 4:39 pm
    I appreciate the responses I've gotten.

    Jack, we're running 734 and 8i on four different Sun boxes (all running
    Solaris 8). Only one has given us any problems. Otherwise, we can clone
    away on the other three either by one: shutting down the source database,
    copy the source datafiles to the new target filesystem, making the necessary
    mods to the new init.ora and create-controfile script (including new
    db_name, new paths, etc), bringing up the new database, then the source
    database, or two: restoring a backup from the source database to the same
    machine but different filesystem, recreating the controlfile, and starting
    it up. We haven't encountered the ORA-9782 on either of these as far as I
    can remember. Why we have the problem on this one box I have absolutely no
    idea.

    For the "problem" box, we restore the hot backup files to another
    filesystem, copy and modify the init.ora of the source, backup the
    controlfile to trace (of the source) and modify this for the target (new
    db_name and everything). We shut down the source database, run the
    create-controlfile script against the new (with ORACLE_SID set to the new),
    then startup the source. No problem.

    If we keep the source database up, then the create-controlfile script does a
    STARTUP NOMOUNT, then proceeds to the CREATE CONTROLFILE, only to bomb with:

    CREATE CONTROLFILE SET DATABASE "DVL5" RESETLOGS ARCHIVELOG

    *

    ORA-01503: CREATE CONTROLFILE failed
    ORA-01565: error in identifying file '/u01/oradata/DVL5/system01.dbf'
    ORA-09782: sfifi: another instance has the same database mounted.

    We have actually gotten around this once by rcp'ing the restored files to
    one of the other boxes, creating the controlfile there...then shutting down
    the database and copying all the files back to the "problem" box. In this
    case, we were able to start the database just fine. But now we don't have
    the space on the "temp" box to hold all the datafiles, so this isn't an
    option.

    Hope this makes sense to somebody. Assuming that it *is* the presence of
    the sgadef.dbf file, this is why I've asked if I can work around the error
    by renaming the file for the duration of bringing the new database up. I
    understand that we won't have to worry about this anymore in 8i.

    Thanks again!
    Steve

    -----Original Message-----
    Sent: Wednesday, April 11, 2001 5:06 AM
    To: Multiple recipients of list ORACLE-L

    Hi

    We clone very frequent (once a week at least) on the same machine and
    never encountered that problem yet, What are the steps you are taking??

    Jack



    "Morrow,

    Steve" To: Multiple recipients of list
    ORACLE-L
    Subject: Temporarily Renaming
    the sgadefSID.dbf File
    Sent by:

    root_at_fatcity.c

    om

    Hi All,

    Trying to clone our PROD to a test database on the same machine for testing
    our 734-->8i upgrade, and of course we're encountering the
    "can't-start-the-new-without-shutting-down-the-old" database problem (ie,
    ORA-9782 on the CREATE CONTROLFILE command). We've worked around this in
    the past by scheduling a time to bring down PROD.

    I know that this is the safest, most reliable way (besides NOT doing this
    on
    a production box...but I have no choice), but believe it or not, I actually
    saw a reference on a Metalink forum to rename the sgadefSID.dbf file, start
    up the new database, then rename the file to its original name. Obviously
    sgadef gets created at instance startup as a memory map, but it doesn't
    look
    like it's used for anything after that (it's certainly doesn't seem to be
    written to). I do know that you can get an octal dump of the file to
    determine the shared memory ID for whatever reason.

    Everything else I've heard/seen about this file has pretty much said DON'T
    TOUCH IT!!! But if the instance only uses it at instance startup, is there
    any harm in renaming it temporarily to get the other database up? If
    so...why?

    And have any of you tried renaming or deleting the file in your cloning (or
    other) efforts? Did it blow up, or work?

    TIA,

    Steve

    Steve Morrow
    IT/Tech Support
    University of South Florida
    phone: 813-974-5519
    smorrow_at_admin.usf.edu
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Morrow, Steve
    INET: SMorrow_at_admin.usf.edu

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Bill Gentry at Apr 11, 2001 at 9:05 pm
    Steve,
    When I was on 7.3.4, I used this procedure every time I cloned, which was
    at least weekly.
    I never had a problem with Production while doing this. After I cloned the
    files, I would rename the
    file, bring up the cloned instances, and then name the file back to the
    original name.
    The file was only with the new name for just a few minutes. I was told by
    those around me that users
    on the production database could still process, just those signing on had to
    retry in a few minutes.
    Since usually cloned the test database farily early in the morning [around
    6:30 am], there were very few
    attempting to sign on.

    After I migrated to 8i, this "file renaming" wasn't required.

    HTH,

    Bill Gentry
    DBA

    Allina Health System
    Minneapolis, MN 55403
    612-775-1190
    gentry_at_allina.com
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, April 10, 2001 3:35 PM
    Hi All,

    Trying to clone our PROD to a test database on the same machine for testing
    our 734-->8i upgrade, and of course we're encountering the
    "can't-start-the-new-without-shutting-down-the-old" database problem (ie,
    ORA-9782 on the CREATE CONTROLFILE command). We've worked around this in
    the past by scheduling a time to bring down PROD.

    I know that this is the safest, most reliable way (besides NOT doing this on
    a production box...but I have no choice), but believe it or not, I actually
    saw a reference on a Metalink forum to rename the sgadefSID.dbf file, start
    up the new database, then rename the file to its original name. Obviously
    sgadef gets created at instance startup as a memory map, but it doesn't look
    like it's used for anything after that (it's certainly doesn't seem to be
    written to). I do know that you can get an octal dump of the file to
    determine the shared memory ID for whatever reason.

    Everything else I've heard/seen about this file has pretty much said DON'T
    TOUCH IT!!! But if the instance only uses it at instance startup, is there
    any harm in renaming it temporarily to get the other database up? If
    so...why?

    And have any of you tried renaming or deleting the file in your cloning (or
    other) efforts? Did it blow up, or work?

    TIA,
    Steve
    _____________________________
    Steve Morrow
    IT/Tech Support
    University of South Florida
    phone: 813-974-5519
    smorrow_at_admin.usf.edu

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Morrow, Steve
    INET: SMorrow_at_admin.usf.edu

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Bill Gentry
    INET: gentry_at_allina.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Jesse, Rich at Apr 11, 2001 at 9:42 pm
    Hi Steve,

    Yes, you MUST rename the sgadefPROD.dbf file while the CREATE CONTROLFILE
    command is issued, or you'll get that error on 7.3. This is fixed in 8.0
    and up.

    The inability to do a daily hotbackup of our PROD db to TEST was a big
    enough problem for us that I logged a TAR. It was Oracle's solution
    (workaround) to rename the sgadef file. So, I figured my arse was covered
    in case of catastrophy. :)

    We were running a script daily that did a backup of PROD to TEST, renamed
    the sgadefPROD file, CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS,
    renamed the sgadefPROD file back, and attempted auto-recovery via RECOVER
    DATABASE USING BACKUP CONTROLFILE. Under 8, we're still doing the same,
    sans sgadef moves.

    Good luck!

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
    -----Original Message-----
    From: Morrow, Steve
    Sent: Wednesday, April 11, 2001 12:01
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Temporarily Renaming the sgadefSID.dbf File


    I appreciate the responses I've gotten.

    Jack, we're running 734 and 8i on four different Sun boxes
    (all running
    Solaris 8). Only one has given us any problems. Otherwise,
    we can clone
    away on the other three either by one: shutting down the
    source database,
    copy the source datafiles to the new target filesystem,
    making the necessary
    mods to the new init.ora and create-controfile script (including new
    db_name, new paths, etc), bringing up the new database, then
    the source
    database, or two: restoring a backup from the source
    database to the same
    machine but different filesystem, recreating the controlfile,
    and starting
    it up. We haven't encountered the ORA-9782 on either of
    these as far as I
    can remember. Why we have the problem on this one box I have
    absolutely no
    idea.

    For the "problem" box, we restore the hot backup files to another
    filesystem, copy and modify the init.ora of the source, backup the
    controlfile to trace (of the source) and modify this for the
    target (new
    db_name and everything). We shut down the source database, run the
    create-controlfile script against the new (with ORACLE_SID
    set to the new),
    then startup the source. No problem.

    If we keep the source database up, then the
    create-controlfile script does a
    STARTUP NOMOUNT, then proceeds to the CREATE CONTROLFILE,
    only to bomb with:

    CREATE CONTROLFILE SET DATABASE "DVL5" RESETLOGS ARCHIVELOG
    *
    ORA-01503: CREATE CONTROLFILE failed
    ORA-01565: error in identifying file
    '/u01/oradata/DVL5/system01.dbf'
    ORA-09782: sfifi: another instance has the same database mounted.

    We have actually gotten around this once by rcp'ing the
    restored files to
    one of the other boxes, creating the controlfile there...then
    shutting down
    the database and copying all the files back to the "problem"
    box. In this
    case, we were able to start the database just fine. But now
    we don't have
    the space on the "temp" box to hold all the datafiles, so
    this isn't an
    option.

    Hope this makes sense to somebody. Assuming that it *is* the
    presence of
    the sgadef.dbf file, this is why I've asked if I can work
    around the error
    by renaming the file for the duration of bringing the new
    database up. I
    understand that we won't have to worry about this anymore in 8i.

    Thanks again!
    Steve
    This message has been scanned for viruses with Trend Micro's Interscan VirusWall.

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Morrow, Steve at Apr 17, 2001 at 2:00 pm
    Good Morning!

    Just wanted to thank everybody for their responses and excellent feedback.
    Renaming the file worked like a charm. This allowed me to create the
    controlfile of the new database and bring it up w/o any problems.

    Now I know :-)

    Steve
    -----Original Message-----
    From: Morrow, Steve
    Sent: Tuesday, April 10, 2001 3:36 PM
    To: 'ORACLE-L_at_fatcity.com'
    Subject: Temporarily Renaming the sgadefSID.dbf File

    Hi All,

    Trying to clone our PROD to a test database on the same machine for
    testing our 734-->8i upgrade, and of course we're encountering the
    "can't-start-the-new-without-shutting-down-the-old" database problem (ie,
    ORA-9782 on the CREATE CONTROLFILE command). We've worked around this in
    the past by scheduling a time to bring down PROD.

    I know that this is the safest, most reliable way (besides NOT doing this
    on a production box...but I have no choice), but believe it or not, I
    actually saw a reference on a Metalink forum to rename the sgadefSID.dbf
    file, start up the new database, then rename the file to its original
    name. Obviously sgadef gets created at instance startup as a memory map,
    but it doesn't look like it's used for anything after that (it's certainly
    doesn't seem to be written to). I do know that you can get an octal dump
    of the file to determine the shared memory ID for whatever reason.

    Everything else I've heard/seen about this file has pretty much said DON'T
    TOUCH IT!!! But if the instance only uses it at instance startup, is
    there any harm in renaming it temporarily to get the other database up?
    If so...why?

    And have any of you tried renaming or deleting the file in your cloning
    (or other) efforts? Did it blow up, or work?

    TIA,
    Steve
    _____________________________
    Steve Morrow
    IT/Tech Support
    University of South Florida
    phone: 813-974-5519
    smorrow_at_admin.usf.edu
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Morrow, Steve
    INET: SMorrow_at_admin.usf.edu

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedApr 10, '01 at 8:52p
activeApr 17, '01 at 2:00p
posts7
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase