This discussion is archived
11 Replies Latest reply: Dec 29, 2007 8:36 PM by Maran Viswarayar RSS

instance name is different

Pavan DBA Expert
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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