This content has been marked as final. Show 8 replies
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
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.
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) ) )
above will all connect 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.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.
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
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.)
* 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