8 Replies Latest reply: Dec 26, 2012 7:01 PM by Srini Ramaswamy RSS

    Multiple Net Service alias for single instance

    Srini Ramaswamy
      We are in the process of consolidating multiple DB into different schema in one DB. Different web application has been using these DB and now all application will point to the same DB.

      I would like to know if its possible to has multiple alias for the same DB Servcice so the websites still connect using the old DB SID - in this way we can avoid making new build and couple of other custom data load process also does not have to be updated.

      I looked at configuring multiple service name for the same DB using Net Manager with no success - I might be doing it wrong or might be totally doing it at the wrong place

      Please get me pointer on how to achieve this and other suggestion also welcome

      Thanks for your time and help in advance

      -Srini
        • 1. Re: Multiple Net Service alias for single instance
          orafad
          It would probably be easier if you just show us how your applications are connecting or have connection strings configured. What you mean by "mutiple alias", "DB service", "DB SID", "service name" may not be the same as everyone elses definitions.

          What you could take a look at is dynamic registration and instance parameter service_names (note the ending s/plural).
          From 11.2 docs, http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams228.htm
          • 2. Re: Multiple Net Service alias for single instance
            Karan Kukreja
            Hi,

            I am not sure if this will help.

            websites will make connection to the Databases using the tnsnames.ora.

            The developers pass the tnsnames.ora or the connection string in the website code and that is listened by the Database Listener.

            Suppose you move all the imp schemas from various databases into one database , then you have to change the connection string for all the databases to the common database. Without those changes i dont think you will be able to achive any connectivity between the websites and the databases.



            Would suggest lets wait for experts also to suggest.

            regards
            kk
            • 3. Re: Multiple Net Service alias for single instance
              sb92075
              tnsnames.ora is valid as shown below
              ORCL, ALIAS1, ORACLE_OLD, FUBAR, ANOTHER_NAME =
                (DESCRIPTION =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = db_server)(PORT = 1521))
                  (CONNECT_DATA =
                    (SERVER = DEDICATED)
                    (SERVICE_NAME = orcl)
                  )
                )
              sqlplus username/password@ORCL
              sqlplus username/password@ALIAS1
              sqlplus username/password@ORCL
              sqlplus username/password@ORACLE_OLD
              sqlplus username/password@FUBAR

              above will all connect to the same DB
              • 4. Re: Multiple Net Service alias for single instance
                Karan Kukreja
                Hi sb92075 ,


                But in this case , the DB name will remain the same. And incase the connection strings passed in the application include the DB name as well , then they also need to be changed at the application level as they would all be now connecting to one DB.

                Regards
                kk
                • 5. Re: Multiple Net Service alias for single instance
                  Aman....
                  You said,
                  I would like to know if its possible to has multiple alias for the same DB Servcice so the websites still connect using the old DB SID - in this way we can avoid making new build and couple of other custom data load process also does not have to be updated.
                  If I am getting you correctly, you are trying to "club" multiple databases into one and then trying to connect to those databases using different aliases in the TNSNAMES, correct? If yes, there is no such facility available yet in the database. There can be only one physical database with multiple instances possible but not one physical database containing multiple physical databases. So you have to consolidate your data in one database and then use it for connectivity IMO.

                  Aman....
                  • 6. Re: Multiple Net Service alias for single instance
                    Srini Ramaswamy
                    Little bit of more details - our web application use connect string from within WAR file and most of the application use Hibernate to connect to DB.

                    So all DB connect string are configured with the application like url="jdbc:oracle:thin:@HostName:1521:DB_SID".

                    After migration different DB_SID will point to the same SID - can I make an alias for different DB_SID to point to the same SID - so we dont have to make any changes at the application level.

                    We have some legacy application - with not much knowledge of how they are configured/build and dont have source code to re-build if required

                    Thanks again for your time and answers
                    • 7. Re: Multiple Net Service alias for single instance
                      orafad
                      >
                      So all DB connect string are configured with the application like url="jdbc:oracle:thin:@HostName:1521:DB_SID".
                      How would you manage hostname change in connect strings? Are dns "aliases" used so you could point to a different host name? What about port no, is the default 1521 always used?

                      To verify if connections are actually made via "SID", could you check listener.log files if connect data includes SID= or SERVICE_NAME= in log entries for jdbc connections.

                      After migration different DB_SID will point to the same SID - can I make an alias for different DB_SID to point to the same SID - so we dont have to make any changes at the application level.
                      How? If legacy/deprecated connection style is used, i.e. thin url with sid, then setting up different service names won't help. (If client uses service_name in connection, an instance could be setup with requested db service in service_names parameter, and instance would enlist for that service with listener via dynamic registration. Still some kind of hostname "redirection" may be required.)
                      • 8. Re: Multiple Net Service alias for single instance
                        Srini Ramaswamy
                        * Yes we will use put in DNS alias and we use the default port for DB.
                        * I checked the listener.log and it appears we are using the thin URL with SID and below is what I see in the logs

                        26-DEC-2012 16:15:02 * (CONNECT_DATA=(SID=DB_SID)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.x.xxx)(PORT=4918)) * establish * DB_SID * 0

                        -Srini