1 2 Previous Next 15 Replies Latest reply: Nov 20, 2012 9:03 AM by Pavan Kumar RSS

    Conected idle instance if i set oracle_sid=orcl which is not actual my sid

    923095
      Hello Team,

      My original ORACLE_SID=PROD

      ENV:- UNIX sOLARIS

      NOW IF I SET oracle_sid=orcl (which is wrong) and connect to sqlplus...--Connected to idle instnace--agreed!!

      since sid is different so how it did connected to sqlplus (i mean having wrong sid means wrong oracle_home/no oracle_home, so how did sqlplus got launched).
      Any other OS level file is accessed to get connected to sqlplus??
        • 2. Re: Conected idle instance if i set oracle_sid=orcl which is not actual my sid
          Osama_Mustafa
          Sqlplus is Binary File located in $ORACLE_HOME/bin its consider as command line , When you set ORACLE_SID , oracle will start looking for spfile for this instance to read the instance parameter that enable to start database , if nothing found Error will appear .
          • 3. Re: Conected idle instance if i set oracle_sid=orcl which is not actual my sid
            724207
            On LINUX/UNIX Solaris, setting ORACLE_SID will create an oracle instance.
            Which on Windows, you have to do with the ORADIM utility.

            So when you set ORACLE_SID, if it does not exists then oracle creates it and after logging to sqlplus, you can startup with the pfile.
            • 4. Re: Conected idle instance if i set oracle_sid=orcl which is not actual my sid
              P.Forstmann
              Osama_mustafa wrote:
              When you set ORACLE_SID , oracle will start looking for spfile for this instance to read the instance parameter that enable to start database , if nothing found Error will appear .
              I dont't think this is correct: this what SQL*Plus STARTUP statement will do but if you don't use STARTUP statement and just want to connect to a local instance defined by ORACLE_SID and ORACLE_HOME SQL*Plus is not going to read SPFILE/FILE because it does not need to do it.

              ORACLE_SID and ORACLE_HOME are used by SQL*Plus to compute a shared memory key that is used to access database instance SGA: that is all what SQL*Plus needs to know in order to access a local instance (i.e. without Oracle Net).

              Here a quick test case with 11.2.0.1 EE on Oracle Linux 5.8:
              [oracle@lx01 dbs]$ echo $ORACLE_SID
              DB112
              [oracle@lx01 dbs]$ echo $ORACLE_HOME
              /u01/app/oracle/product/11.2.0/EE
              [oracle@lx01 dbs]$ ls -al $ORACLE_HOME/dbs/s*
              -rw-r----- 1 oracle oinstall 9748480 Jan 29  2012 /u01/app/oracle/product/11.2.0/EE/dbs/snapcf_DB112.f
              -rw-r----- 1 oracle oinstall    3584 Nov 19 08:53 /u01/app/oracle/product/11.2.0/EE/dbs/spfileDB112.ora.new
              [oracle@lx01 dbs]$ ls -al $ORACLE_HOME/dbs/i*
              ls: /u01/app/oracle/product/11.2.0/EE/dbs/i*: No such file or directory
              [oracle@lx01 dbs]$ sqlplus test/test
              
              SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 19 08:59:10 2012
              
              Copyright (c) 1982, 2009, Oracle.  All rights reserved.
              
              
              Connected to:
              Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
              With the Partitioning, OLAP, Data Mining and Real Application Testing options
              
              SQL> select status from v$instance;
              
              STATUS
              ------------
              OPEN
              
              SQL> exit
              Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
              With the Partitioning, OLAP, Data Mining and Real Application Testing options
              Edited by: P. Forstmann on 19 nov. 2012 08:36

              Edited by: P. Forstmann on 19 nov. 2012 08:57
              • 5. Re: Conected idle instance if i set oracle_sid=orcl which is not actual my sid
                P.Forstmann
                SK_dba wrote:
                On LINUX/UNIX Solaris, setting ORACLE_SID will create an oracle instance.
                Sorry but this is not correct, as already said, this is what SQL*Plus STARTUP statement will do but SQL*Plus will not do this by default.
                • 6. Re: Conected idle instance if i set oracle_sid=orcl which is not actual my sid
                  Niket Kumar
                  On LINUX/UNIX Solaris, setting ORACLE_SID will create an oracle instance.
                  Which on Windows, you have to do with the ORADIM utility.
                  you can change above statement. ORACLE_SID will help sqlplus to connect to appropriate instance.
                  • 7. Re: Conected idle instance if i set oracle_sid=orcl which is not actual my sid
                    Osama_Mustafa
                    Thank you for Clarify But When Instance Startup it will start looking For pfile/Spfile for reading memory Usage , Which is necessary to startup right ?
                    • 8. Re: Conected idle instance if i set oracle_sid=orcl which is not actual my sid
                      P.Forstmann
                      Yes: at startup SQL*Plus needs to read SPFILE/PFILE in order to set all instance parameters that have non default settings.
                      • 10. Re: Conected idle instance if i set oracle_sid=orcl which is not actual my sid
                        P.Forstmann
                        You can try to get all files accessed by SQL*Plus with a tracing tool. For example, on Linux you can try:
                        $ strace sqlplus <user>/<password> 2>/tmp/sqlplus.trc
                        $ grep  'access\|stat\|open' /tmp/sqlplus.trc
                        You will see a lof of files from ORACLE_HOME but no SPFILE/PFILE.
                        • 11. Re: Conected idle instance if i set oracle_sid=orcl which is not actual my sid
                          923095
                          Guys i appreciate abt ur view, But my question is why:-

                          2. Whn i set wrong oracle_sid in unix environment--it connects to sqlplus with connected to idle instance???? as the sid which i gave doenot exist.

                          This will never happen in window environment as it will through tns protocol adapter error if i set wrond sid


                          Line 2 show that its reading some parameters to coonect/find sqlplus..what are those??
                          • 12. Re: Conected idle instance if i set oracle_sid=orcl which is not actual my sid
                            Osama_Mustafa
                            Linux is Case sensitive OS Otherwise windows is not
                            • 13. Re: Conected idle instance if i set oracle_sid=orcl which is not actual my sid
                              923095
                              hostname:/oracle_home> export ORACLE_SID=DBAS (INCORRECT SID)
                              hostname:/oracle_home> sqlplus / as sysdba

                              SQL*Plus: Release 11.1.0.7.0 - Production on Tue Nov 20 09:15:00 2012

                              Copyright (c) 1982, 2008, Oracle. All rights reserved.

                              Connected to an idle instance.

                              SQL>


                              *****************************************************************************************


                              hostname:/oracle_home> export ORACLE_SID=prod (Correct SID)
                              hostname:/oracle_home> sqlplus / as sysdba

                              SQL*Plus: Release 11.1.0.7.0 - Production on Tue Nov 20 09:16:01 2012

                              Copyright (c) 1982, 2008, Oracle. All rights reserved.


                              Connected to:
                              Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
                              With the Partitioning, OLAP, Data Mining and Real Application Testing options
                              SQL> select open_mode from v$database;

                              OPEN_MODE
                              ----------
                              READ WRITE


                              can u figure out what i m trying to say..difference between correct/incorrect sid's and relation between incorrect sid-idle instance..m not talking about pfile/spfile!!
                              • 14. Re: Conected idle instance if i set oracle_sid=orcl which is not actual my sid
                                EdStevens
                                >
                                <snip>
                                can u figure out what i m trying to say..difference between correct/incorrect sid's and relation between incorrect sid-idle instance..m not talking about pfile/spfile!!
                                The terminology 'connected to idle instance' --- with that word 'connected' could be misleading, depending on one's own understanding. In nix, untill you issue a 'startup' there really is no instance.  Go back and look at the definition of an instance.  From the Concepts manual:  "A database instance is a set of memory structures that manage database files".  Until you issue a startup, there are no memory structures.  Well, not in nix. In Windows, there is is this concept of the 'service' ...

                                So, in nix, when you issue a 'connect', sqlplus will attempt* to locate a service identified by the value of ORACLE_SID. If not found, he can only assume you haven't started it yet, so will tell you you are 'connected' (probably a poor choice of words) to an idle instance. At this point, he really has no way of knowing if you have created any database structures for ORACLE_SID=fubar.

                                But in Windows we have the windows service, which does have to be running even before we can issue a startup. So in that case, even if the database has been shut down, a 'connect' has to at least be able to find the windows service, OracleServiceFUBAR. If he can't find even that, then you get the error.
                                1 2 Previous Next