8 Replies Latest reply: Mar 20, 2013 8:51 AM by User374733 RSS

    Instance Name getting change on configuring Oracle Restart

    User374733
      Platform: RHEL 4u7 32-bit
      ASM Instance: 11gR2
      Database Instance: 10gR2
      =================
      SQL> sho parameter name

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      db_file_name_convert string
      db_name string TEST
      db_unique_name string TEST
      global_names boolean FALSE
      instance_name string DB10G
      lock_name_space string
      log_file_name_convert string
      service_names string TEST
      =================

      I did a "software only" installation of Oracle Database 10gR2 and then manually create a simple database with "create database..." command on the machine where 11gR2 ASM Instance was running. Then following link:

      http://docs.oracle.com/cd/E11882_01/server.112/e25494/restart002.htm#BABHHABA

      I configured my single instance database 10gR2 with Oracle Restart functionality. The database successfully configured with Oracle Restart but what I observe is that instance name changed to "TEST" from "DB10G".

      Can anyone of you please suggest me what could be the possible reason behind this. Thanks in advance.
        • 1. Re: Instance Name getting change on configuring Oracle Restart
          moreajays
          Hi,

          There could be two factors causing this .. one is your environment variable ORACLE_SID & other is the parameter files

          Check which profile being picked when database starts & have proper value for ORACLE_SID
          Identify Pfile/SPFILE & verify the instance name value specified

          Thanks,
          Ajay More
          http://www.moreajays.com
          • 2. Re: Instance Name getting change on configuring Oracle Restart
            User374733
            [oracle@linux1 ~]$ srvctl config database -d TEST
            Database unique name: TEST
            Database name:
            Oracle home: /u01/app/oracle/oracle/product/10.2.0/db_1
            Oracle user: oracle
            Spfile: /u01/app/oracle/oracle/product/10.2.0/db_1/dbs/spfileDB10G.ora
            Domain:
            Start options: open
            Stop options: immediate
            Database role: PRIMARY
            Management policy: AUTOMATIC
            Disk Groups:
            Services:


            [oracle@linux1 ~]$ more /u01/app/oracle/oracle/product/10.2.0/db_1/dbs/spfileDB10G.ora


            DB10G.__db_cache_size=423624704
            TEST.__db_cache_size=377487360
            DB10G.__java_pool_size=4194304
            TEST.__java_pool_size=4194304
            DB10G.__large_pool_size=4194304
            TEST.__large_pool_size=4194304
            DB10G.__shared_pool_size=96468992
            TEST.__shared_pool_size=146800640
            DB10G.__streams_pool_size=4194304
            TEST.__streams_pool_size=0
            *.background_dump_dest='/u01/app/oracle/oracle/product/10.2.0/db_1/admin/DB10G'
            *.control_files='/u01/app/oracle/oracle/TEST/controlfile/o1_mf_8m8wo8hp_.ctl'#Oracle managed file
            *.db_block_size=8192
            *.db_create_file_dest='/u01/app/oracle/oracle'
            *.db_create_online_log_dest_1='/u01/app/oracle/oracle'
            *.db_name='TEST'
            *.sga_max_size=512M
            *.sga_target=512M
            *.undo_management='AUTO'
            *.undo_tablespace='UNDO2'



            [oracle@linux1 ~]$
            SQL> alter system set instance_name='DB10G' scope=spfile;

            System altered.

            SQL>

            [oracle@linux1 ~]$ more /u01/app/oracle/oracle/product/10.2.0/db_1/dbs/spfileDB10G.ora


            ...

            *.instance_name='DB10G'
            ...


            [oracle@linux1 ~]$ srvctl stop database -d TEST
            [oracle@linux1 ~]$ srvctl start database -d TEST

            SQL> sho parameter name

            NAME TYPE VALUE
            ------------------------------------ ----------- ------------------------------
            db_file_name_convert string
            db_name string TEST
            db_unique_name string TEST
            global_names boolean FALSE
            instance_name string DB10G
            lock_name_space string
            log_file_name_convert string
            service_names string TEST

            SQL> exit
            Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
            With the Partitioning, OLAP and Data Mining options


            [oracle@linux1 ~]$ env |grep ORACLE
            ORACLE_SID=TEST
            ORACLE_BASE=/u01/app/oracle
            ORACLE_HOME=/u01/app/oracle/oracle/product/10.2.0/db_1


            NOTE: With environment variable ORACLE_SID=DB10G its not getting connection


            [oracle@linux1 ~]$ export ORACLE_SID=DB10G


            [oracle@linux1 ~]$ sqlplus / as sysdba

            SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 19 11:53:37 2013

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

            Connected to an idle instance.

            SQL> exit
            Disconnected
            • 3. Re: Instance Name getting change on configuring Oracle Restart
              moreajays
              Hi ,

              Do this
              ORACLE_SID=TEST
              export ORACLE_SID
              
              sqlplus "/ as sysdba"
              shut immediate;
              exit;
              
              ORACLE_SID=DB10G
              export ORACLE_SID
              sqlplus "/ as sysdba"
              startup
              exit;
              
              ps -ef| grep -i pmon
              Thanks,
              Ajay More
              http://www.moreajays.com
              • 4. Re: Instance Name getting change on configuring Oracle Restart
                Pavan DBA
                you said you created database manually, have you taken the sample init.ora file which comes along with installation or you have copied any existing pfile and did modifications?
                • 5. Re: Instance Name getting change on configuring Oracle Restart
                  User374733
                  [oracle@linux1 ~]$ sqlplus / as sysdba

                  SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 19 13:14:00 2013

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


                  Connected to:
                  Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
                  With the Partitioning, OLAP and Data Mining options

                  SQL> shu immediate
                  Database closed.
                  Database dismounted.
                  ORACLE instance shut down.
                  SQL>

                  [oracle@linux1 ~]$ export ORACLE_SID=DB10G
                  [oracle@linux1 ~]$ sqlplus / as sysdba

                  SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 19 13:14:38 2013

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

                  Connected to an idle instance.

                  SQL> startup
                  ORACLE instance started.

                  Total System Global Area 536870912 bytes
                  Fixed Size 1220460 bytes
                  Variable Size 109052052 bytes
                  Database Buffers 423624704 bytes
                  Redo Buffers 2973696 bytes
                  Database mounted.
                  Database opened.
                  SQL> sho parameter spfile

                  NAME TYPE VALUE
                  ------------------------------------ ----------- ------------------------------
                  spfile string /u01/app/oracle/oracle/product
                  /10.2.0/db_1/dbs/spfileDB10G.o
                  ra
                  SQL>

                  [oracle@linux1 ~]$ ps -ef |grep mmon
                  oracle 4863 1 0 13:14 ? 00:00:00 ora_mmon_DB10G
                  oracle 4899 4879 0 13:15 pts/2 00:00:00 grep mmon
                  oracle 30500 1 0 09:53 ? 00:00:00 asm_mmon_+ASM
                  [oracle@linux1 ~]$ ps -ef |grep pmon
                  oracle 4847 1 0 13:14 ? 00:00:00 ora_pmon_DB10G
                  oracle 4901 4879 0 13:15 pts/2 00:00:00 grep pmon
                  oracle 30472 1 0 09:53 ? 00:00:00 asm_pmon_+ASM
                  [oracle@linux1 ~]$


                  This was the same I was using before Oracle Restart configuration. Now what will be the purpose of Automatic restart configuration?

                  On reboot of the machine:

                  [oracle@linux1 ~]$ srvctl status database -d TEST
                  Database is not running.
                  [oracle@linux1 ~]$
                  • 6. Re: Instance Name getting change on configuring Oracle Restart
                    moreajays
                    Hi,

                    Update service_names to DB10G in spfile/pfile & try

                    Thanks,
                    Ajay More
                    http://www.moreajays.com
                    • 7. Re: Instance Name getting change on configuring Oracle Restart
                      User374733
                      I dont think that it will be fruitful. Logically there must not be any need to change the parameter service_names.

                      SQL> sho parameter name

                      NAME TYPE VALUE
                      ------------------------------------ ----------- ------------------------------
                      db_file_name_convert string
                      db_name string TEST
                      db_unique_name string TEST
                      global_names boolean FALSE
                      instance_name string DB10G
                      lock_name_space string
                      log_file_name_convert string
                      service_names string TEST
                      SQL> !env |grep ORACLE
                      ORACLE_SID=TEST
                      ORACLE_BASE=/u01/app/oracle
                      ORACLE_HOME=/u01/app/oracle/oracle/product/10.2.0/db_1

                      SQL> alter system set service_names='DB10G' scope=spfile;

                      System altered.

                      SQL> exit

                      [oracle@linux1 ~]$ srvctl stop database -d TEST
                      [oracle@linux1 ~]$ srvctl start database -d TEST
                      [oracle@linux1 ~]$

                      [oracle@linux1 ~]$ sqlplus / as sysdba

                      SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 20 10:13:40 2013

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


                      Connected to:
                      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
                      With the Partitioning, OLAP and Data Mining options

                      SQL> sho parameter name

                      NAME TYPE VALUE
                      ------------------------------------ ----------- ------------------------------
                      db_file_name_convert string
                      db_name string TEST
                      db_unique_name string TEST
                      global_names boolean FALSE
                      instance_name string DB10G
                      lock_name_space string
                      log_file_name_convert string
                      service_names string DB10G
                      SQL> !env |grep ORACLE
                      ORACLE_SID=TEST
                      ORACLE_BASE=/u01/app/oracle
                      ORACLE_HOME=/u01/app/oracle/oracle/product/10.2.0/db_1

                      SQL>

                      [oracle@linux1 ~]$ . ./set_dbhome2.sh
                      [oracle@linux1 ~]$ env |grep ORACLE_SID
                      ORACLE_SID=TEST
                      [oracle@linux1 ~]$ export ORACLE_SID=DB10G
                      [oracle@linux1 ~]$ env |grep ORACLE
                      ORACLE_SID=DB10G
                      ORACLE_BASE=/u01/app/oracle
                      ORACLE_HOME=/u01/app/oracle/oracle/product/10.2.0/db_1
                      [oracle@linux1 ~]$ sqlplus / as sysdba

                      SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 20 10:14:46 2013

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

                      Connected to an idle instance.

                      SQL>

                      Same issue.
                      • 8. Re: Instance Name getting change on configuring Oracle Restart
                        User374733
                        I manually created the pfile with most basic parameter settings.