ora_sid question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: ora_sid question

  1. #1
    Join Date
    Aug 2000
    Posts
    163

    Wink

    Jeff,
    did you notice Khussain states that oracle_sid is the name of the database? We just determined in my previous post it would be the name of the instance.
    One database can have multiple instances, right? So one db name would be linked to multiple instances names.
    Also, in tnsnames.ora file ora_sid is it a db name or instance name?

  2. #2
    Join Date
    Dec 2000
    Posts
    43
    Instance name , DB name - its the same thing.

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    ORACLE_SID is the instance name. They typically are the same thing except possibly when parallel server is being used.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    No, one database can never be mounted by more than one instance! It is the other way around: one instance can have two or more databases opened at the same time, but only if this is paralell server configuration.

    In tnsnames.ora AFAIK there is no ora_sid entry. In There is only SID=... in the CONNECT_DATA entry of SQL*Net version 2.* and Net8 and this means SID as instance name, not database name. In 8i this was changed, there is no SID entry, it is replaced by SERVICE_NAME=..., which in this case means global database name (if service described in this tnsnames section is a database).

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    [QUOTE][i]Originally posted by jmodic [/i]
    [B]No, one database can never be mounted by more than one instance! .....
    [/B][/QUOTE]

    I should clarify this first statement in my reply: this was ment as a direct reply to mary's question, not as a follow up of the marist89 reply. While I was writing my reply two other replies occured which I was not aware of.

    I would not agree 100% with arshiah's answer - SID and database_name are two totaly different things. I would more agree with marist89 statment that they *tipically* have the same name in non-PS configuration. But even there they can be named totally differentlly....
    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
    Mar 2000
    Location
    CA
    Posts
    317
    In non OPS mode also, DB and Instance names can be different. Though on NON OPS mode, more than one database cannot be set up with one Instance
    Thanks
    Kishore Kumar

  7. #7
    Join Date
    Aug 2000
    Posts
    194
    "one instance can have two or more databases opened at the same time"

    Are u sure on this jmodic?. I was under the impression its the other way.

    BTW, I have non ops databases with different instance name and database names

  8. #8
    Join Date
    Jan 2001
    Posts
    318

    Red face

    I am not sure if I got what jmodic is saying.
    We have one database here and number of instances.
    Here is the text of the script that is used to create a database.
    CREATE database Prodution
    logfile
    group 1 ('C:$oraCdiskpath\prod_log1a.dbf','D:$oraDdiskpath\prod_log1b.dbf') size 10M,
    group 2 ('C:$oraCdiskpath\prod_log2a.dbf','D:$oraDdiskpath\prod_log2b.dbf') size 10M,
    group 3 ('C:$oraCdiskpath\account4_log3a.dbf','D:$oraDdiskpath\prod_log3b.dbf') size 10M
    datafile 'C:$oradcdiskpath\prod_system01.dbf' size 50M;

    Here is the text from the tnsnames.ora file:
    ORCL_SCOOTER.WORLD =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = NMP)(SERVER = SCOOTER)(PIPE = ORAPIPE))
    (CONNECT_DATA = (SID = ORCL))
    )

    SUP.WORLD =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = NMP)(SERVER = SCOOTER)(PIPE = ORAPIPE))
    (CONNECT_DATA = (SID = SUP))
    )

    Prod.WORLD =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = CLIFF)
    (PORT = 1521))
    (CONNECT_DATA = (SID = prod))
    )

    ORCL81.WORLD =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Oracle816)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = ORACLE81))
    )

    All these instances have the same database Production right ??? So one database has number of instances

    Thanks
    Sonali

  9. #9
    Join Date
    Aug 2000
    Posts
    163

    some of us are confused

    Looks like there is a confusion with how many instances the database can have and what the connect_string=sid in tnsnames.ora value really is.
    My tnsnames.ora looks different than Sonaliak's. I have the same connect_data=sid value but various aliases for connections and different IP addresses. What would it mean?
    Anyone here who really knows the subject?
    Here is a general example of tnsnames.ora file coming from Oracle.

    <alias>= [ (DESCRIPTION_LIST = # Optional depending on whether u have
    # one or more descriptions
    # If there is just one description, unnecessary ]
    (DESCRIPTION=
    [ (SDU=2048) ] # Optional, defaults to 2048
    # Can take values between 512 and 32K
    [ (ADDRESS_LIST= # Optional depending on whether u have
    # one or more addresses
    # If there is just one address, unnecessary ]
    (ADDRESS=
    [ (COMMUNITY=<community_name>) ]
    (PROTOCOL=tcp)
    (HOST=<hostname>)
    (PORT=<portnumber (1521 is a standard port used)>)
    )
    [ (ADDRESS=
    (PROTOCOL=ipc)
    (KEY=<ipckey (PNPKEY is a standard key used)>)
    )
    ]
    [ (ADDRESS=
    [ (COMMUNITY=<community_name>) ]
    (PROTOCOL=decnet)
    (NODE=<nodename>)
    (OBJECT=<objectname>)
    )
    ]
    ... # More addresses
    [ ) ] # Optional depending on whether ADDRESS_LIST is used or not
    [ (CONNECT_DATA=
    (SID=<oracle_sid>)
    [ (GLOBAL_NAME=<global_database_name>) ]
    )
    ]
    [ (SOURCE_ROUTE=yes) ]

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    [QUOTE][i]Originally posted by gsprince [/i]
    [B]"one instance can have two or more databases opened at the same time"

    Are u sure on this jmodic?. I was under the impression its the other way.

    BTW, I have non ops databases with different instance name and database names [/B][/QUOTE]

    My humble appologies to all for my nonsences in this thread! I don't know what I was thinking... Sure you are right, as was mary in the original question. So, the correct statement is:

    In OPS two or more instances can have one (common) database opened at the same time On the other hand, one instance can never open more than one database at the same time.

    Again, sorry for the confusion.

    What sonaliak's tnsnames files show are not in fact instances, they are "tnsnames alliases" or "service names" as they are now called in 8i (although they sometimes are refered as "instance names" or simmilar, even in Oracle documentation and help files). To avoid further confusion, I'm talking about SUP.WORLD, prod.WORLD etc from his tnsnames.ora. This are only aliases, pointing to actual databases. Sonaliak, you have four different databases with (most probably) four different instances, operating on theree different machines. Only one of them (the one on host Oracle816) is 8i, all other three are pre-8i releases. But your tnsnames alliase definitely are not pointing to the same database.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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