11 Replies Latest reply: Dec 29, 2007 10:36 PM by Maran Viswarayar RSS

    instance name is different

    Pavan DBA
      Hi i am having a database 9.2.0.1 on windows xp. i changed my instance name by changing the parameter in pfile from "test" to "test1". my database is using pfile not spfile.

      when i query following i got wondered

      SQL> show parameter instance_name

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      instance_name string test1


      SQL> select instance_name from v$instance;

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


      i want to know why both are showing different values?

      thanks in advance
        • 1. Re: instance name is different
          153119
          Hi,

          1 you shouldn't be using a pfile.
          2 You did bounce the instance after the chance? instance_name is a static parameter.
          Also : show parameter will always use your session parameters. It will not read the pfile.

          --
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: instance name is different
            247514
            It's not enough to change Instance Name (SID) by simply modifying that parameter. There's a procedure to follow, check metalink doc

            How to Determine and Rename/Change DB_NAME or ORACLE_SID/Instance Name
            Doc ID: Note:15390.1

            It's basically the ORACLE_SID environment variable you use to start database determine the current instance name.
            • 3. Re: instance name is different
              611828
              2 You did bounce the instance after the chance?
              instance_name is a static parameter.
              Also : show parameter will always use your session
              parameters. It will not read the pfile.
              Hi, I understand where you coming from but for the sake of enlightment (btw I couldn t recreate this problem!!!) - when I do that I do become always the actual info (with me, testdb instead of testdb01) so I am guessing that he must have bounced the db;

              then again if he did then he shouldn t be having to see the test name on v$instance ?!??!?! whatelse, do you think might have happened ?
              (I am just curious, because I couldn t "replay" that error on my testsystem!)
              • 4. Re: instance name is different
                Maran Viswarayar
                What i suspect if


                SQL>show parameter instance_name

                display the parameters of INIT.ora settings

                Select instance_name from v$instance is the the one from the OS level if you have created you instance as TEST1 the query will reflect the same TEST1

                1.init.ora
                2.Oracleservice(assume you are in Windows)
                • 5. Re: instance name is different
                  611828
                  SQL>show parameter instance_name

                  display the parameters of INIT.ora settings
                  If you bounced the db then the new name should appear ! But since the old name appeared, I wonder whatelse might have gone wrong!?!?!?
                  • 6. Re: instance name is different
                    611828
                    ahhh .. never mind .. I mixed his question up!!! I ment, I mixed the names up!! my bad!

                    Never mind!
                    • 7. Re: instance name is different
                      Maran Viswarayar
                      Check the post

                      The new name has appeared but when he queries from select instance_name from v$instance it has not changed


                      recreate the insatance with new name and see the second one also would have changed
                      • 8. Re: instance name is different
                        Pavan DBA
                        hi maran, consider the following

                        show parameter will always use your session parameters. It will not read the pfile. - by sybrand

                        show parameter - displays parameters of init.ora settings

                        which is true?if if show parameter command is not reading pfile from where it gets the session parameters?
                        • 9. Re: instance name is different
                          Maran Viswarayar
                          if if show parameter command is not reading pfile from where it gets the session parameters?
                          it reads from the pfile which is a init.ora file but it is not actually reading from the parameter file rather it gives the settings that are set by the init.ora during startup and also the dynamic values ..I am not 100% sure here

                          But for your issue did you check my earlier posts

                          stop the DB change the instance in the pfile and start the DB

                          SQL>show parameter instance_name

                          you will see the latest one you have changed but when you query from select instance_name from v$instance it is going to show you instance name created during the DB creation

                          clear or not?
                          • 10. Re: instance name is different
                            Pavan DBA
                            yes i checked and now am trying to create an instance.

                            one small doubt. what are FIXED SIZE and VARIABLE SIZE stands for while we starting instance?

                            Message was edited by:
                            pavan kumar
                            • 11. Re: instance name is different
                              Maran Viswarayar
                              FIXED Size:

                              By Tom Kyte
                              It is compiled into Oracle binary itself during the installation time .It contains set of variables that points to other components of SGA and varibales that contains values for various params

                              We cannot have any control on this param and is OS dependent

                              Variable size= shared pool+largepoo+javapool

                              Message was edited by:
                              Maran Viswarayar