1 2 Previous Next 16 Replies Latest reply: Dec 14, 2012 11:26 AM by 955912 RSS

    ORACLE_SID , DB_NAME , INSTANCE_NAME

    955912
      Hello DBAs

      Greeting to all ;

      Some confusions with ORACLE DB Creation.

      MY CONFUSIONS :-

      I did n't mention ORACLE_SID name in 'init.ora' file.

      I am very clear with ORACLE_SID=INSTANCE_NAME i.e. INSTANCE_NAME defaults to ORACLE_SID.

      *[oracle@localhost ~]$ echo $ORACLE_SID*
      test

      SQL> select instance_name from v$instance;

      INSTANCE_NAME
      ----------------
      test

      SQL> select name from v$database;

      NAME
      ---------
      TEST

      This is my confusion , when they may differ ? ( SID_NAME , DB_NAME )
      ORACLE_SID may be or may not be the same as the database name.

      Someone said :-

      " DB_NAME value should be the same as the value of the ORACLE_SID environment variable "
      but i disagree above statement.

      Regards
      Thiyagusham .G

      Edited by: 952909 on Dec 4, 2012 11:39 AM

      Edited by: 952909 on Dec 4, 2012 11:40 AM

      Edited by: 952909 on Dec 4, 2012 11:41 AM

      Edited by: 952909 on Dec 6, 2012 5:55 AM

      Edited by: 952909 on Dec 12, 2012 6:07 AM
        • 1. Re: ORACLE_SID
          vlethakula
          ORACLE_SID and DB_NAME need not be same.

          For eg: ORACLE_SID=test and DB_NAME=xyz is valid.

          Your background process would get started with ORACLE_SID name i.e., ora_pmon_TEST (ORACLE_SID=TEST now)

          And you need to have pfile or spfile with naming convention init$ORACLE_SID.ora/spfile$ORACLE_SID.ora ; In this case initTEST.ora

          And ORACLE_SID is case sensitive
          • 2. Re: ORACLE_SID
            955912
            Hi;

            Ya i got valid point " ORACLE_SID and DB_NAME need not be same"

            Already i asked slightly different , when they differ ?

            I did n't understand your reply.

            init.ora"  => There is no instance name and SID name.

            Following answers  are  correct ?

            1. INSTANCE_NAME defaults to ORACLE_SID. i.e
            Instance_name , SID name should be same.

            I have following Databases in my system

            DB names are test , prod, drprod ,drtest , tommy

            Here Confusion :-

            sid_name: amigo
            DB name: tommy

            In above mentioned  case  sid , db both are different :

            2 If i set export ORACLE_SID=amigo
            how it exactly connect with tommy database.

            Regards
            Thiyagusham .G

            Edited by: 952909 on Dec 4, 2012 1:13 PM

            Edited by: 952909 on Dec 4, 2012 1:14 PM

            Edited by: 952909 on Dec 4, 2012 1:14 PM
            • 3. Re: ORACLE_SID
              Osama_Mustafa
              You can know what is the different by Review this amazing answer by tom :
              http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:533422350794
              • 4. Re: ORACLE_SID
                P.Forstmann
                952909 wrote:
                Here Confusion :-

                sid_name: amigo
                DB name: tommy

                In above mentioned  case  sid , db both are different :

                2 If i set export ORACLE_SID=amigo
                how it exactly connect with tommy database.
                When connecting to a local instance Oracle is only using ORACLE_SID and ORACLE_HOME environment variables to locate a locally running instance:
                - this instance name must match ORACLE_SID environment variable
                - this instance must use executables using current ORACLE_HOME setting.

                Oracle does not use DB_NAME to connect to an instance. Never.

                See also Oracle System Identifier in Concepts Guide: http://docs.oracle.com/cd/E11882_01/server.112/e25789/startup.htm#CNCPT89037.

                Edited by: P. Forstmann on 4 déc. 2012 22:40
                • 5. Re: ORACLE_SID
                  vlethakula
                  q:export ORACLE_SID=amigo
                  how it exactly connect with tommy database

                  a: Ok, when your instance starts it reads the pfile and loads those parametrs into memory. So db_name is set to AMIGO and loaded. And in your control files also DB_NAME is stored not ORACLE_SID.

                  IMO, Instance memory+background processes are started with SID name. And in all datafile headers and control file your DB_NAME is stored. Your instance access datafiles and controlfiles

                  Edited by: vlethakula on Dec 4, 2012 1:41 PM
                  • 6. Re: ORACLE_SID
                    955912
                    Hi ;

                    Ya . Thanks for given link. Already i referred that link before posting questions ..
                    I has some confusions so that i asked questions here ... OK .. Leave it !

                    Please Clarify this

                    SID = identifies the database instance (database name + instance number).
                    So if database name is xxxxx and instance number is xx , then your SID is xx.
                    • 7. Re: ORACLE_SID
                      955912
                      Hi ;

                      Ya . Thanks for given link. Already i referred that link before posting questions ..
                      I has some confusions so that i asked questions here ... OK .. Leave it !

                      Link doesn't say exact answer for my question

                      sid_name: amigo
                      DB name: tommy

                      In above mentioned case sid , db both are different :

                      2 If i set export ORACLE_SID=amigo
                      how it exactly connect with tommy database.


                      Please Clarify this

                      SID = identifies the database instance (database name + instance number).
                      So if database name is xxxxx and instance number is xx , then your SID is xx.
                      • 8. Re: ORACLE_SID
                        955912
                        Hi ;

                        Ya . Thanks for given link. Already i referred that link before posting questions ..
                        I had some confusions so that i asked questions here ... OK .. Leave it !

                        Link doesn't say exact answer for my question

                        sid_name: amigo
                        DB name: tommy

                        In above mentioned case sid , db both are different :

                        2 If i set export ORACLE_SID=amigo
                        how it exactly connect with tommy database.


                        Please Clarify this

                        SID = identifies the database instance (database name + instance number).
                        So if database name is xxxxx and instance number is xx , then your SID is xx.

                        Edited by: 952909 on Dec 4, 2012 1:53 PM
                        • 9. Re: ORACLE_SID
                          Osama_Mustafa
                          You describe wrong

                          What you mean like the following let assume that

                          DB_NAME PARAMETER = ORCL
                          INSTANCE_NUMBER = 1

                          SID will be ORCL1
                          • 10. Re: ORACLE_SID
                            Osama_Mustafa
                            SQL> show parameter db_name
                            
                            NAME                                 TYPE        VALUE
                            ------------------------------------ ----------- ------------------------------
                            db_name                              string      orcl
                            SQL> 
                            SQL> 
                            SQL> show parameter instance_number
                            
                            NAME                                 TYPE        VALUE
                            ------------------------------------ ----------- ------------------------------
                            instance_number                      integer     0
                            
                            SQL> select instance_name from v$instance ;
                            
                            INSTANCE_NAME
                            ----------------
                            orcl
                            • 11. Re: ORACLE_SID
                              955912
                              Hi vlethakula      ;

                              However thanks for all reply from you !

                              From Your Reply :-
                              I don't understand your answer .

                              So db_name is set to AMIGO and loaded. And in your control files also DB_NAME is stored not ORACLE_SID.
                              IMO : Instance memory+background processes are started with SID name i.e [ instance started as amigo]
                              all datafile headers and control file your DB_NAME is stored. Your instance access datafiles and controlfiles

                              As per your reply  my ques is
                              If instance (IMO) started as amigo , how amigo finds datafiles and controlfiles for tommy DATABASE ?

                              FYI :
                              I red from oracle docs following information
                              When  the  instance  is started,  the control  file  is updated with the  current  ORACLE_SID

                              Regards ;
                              Thiyagusham .G

                              Edited by: 952909 on Dec 4, 2012 2:26 PM

                              Edited by: 952909 on Dec 4, 2012 2:27 PM

                              Edited by: 952909 on Dec 4, 2012 2:27 PM
                              • 12. Re: ORACLE_SID
                                955912
                                Hi to all ;


                                My bash_profile setting was   ORACLE_SID=TSH1; export ORACLE_SID

                                Before  connecting ORACLE  DB

                                +*[oracle@localhost ~]$ env |grep ORA*+

                                ORACLE_SID=TSH1
                                ORACLE_BASE=/u01/app/oracle
                                ORACLE_TERM=xterm
                                ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1

                                After connecting ORACLE DB

                                *[oracle@localhost ~]$ env | grep ORA*

                                ORACLE_SID=test
                                ORACLE_BASE=/u01/app/oracle
                                ORACLE_TERM=xterm
                                ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1

                                See here ,

                                I have only one bash profile , but 5 databases ?

                                Originally what does tsh1?
                                tsh1 => is it common for all databases ?

                                Edited by: 952909 on Dec 4, 2012 3:23 PM

                                Edited by: 952909 on Dec 4, 2012 3:25 PM
                                • 13. Re: ORACLE_SID
                                  jgarry
                                  Assuming your databases are all running try this:

                                  ps -ef|grep dbw

                                  (on some OS that would be ps -aux)

                                  This should show you a bunch of things like
                                  oracle 2219 1 0 Sep 18 ? 6:51 ora_dbw0_TTST
                                  for each of your instances.

                                  You should also have the things documented in your oratab file so you can switch environments easily, but that is not strictly necessary. Different OS's may put it in different places, like /etc/oratab or /opt something.

                                  Note also other things may set the environment variables, like /etc/profile or /etc/bashrc (note the lack of leading dots). Exporting is necessary to propagate into subshells, too, sometimes that gets missed, accidentally or on purpose (as with crons).

                                  Please be sure and tell us what you think a database is, non-Oracle engines are strange.
                                  • 14. Re: ORACLE_SID
                                    955912
                                    Hi to all ;

                                    Still having some more confusions ...

                                    i am getting confusion about  following answer
                                    instance must use executables using current ORACLE_HOME setting.

                                    May i know what are the executables involving here ?
                                    i mean when SID=amigo ,DB_name=tommy when connnecting database.

                                    where SID information's considered when DB connecting ?

                                    What i know , SID & Instance ,Ctrl file involved when connecting DB.
                                    Control file checks info about database . SID information's a not updated in control file.
                                    1 2 Previous Next