ORCL, ALIAS1, ORACLE_OLD, FUBAR, ANOTHER_NAME = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db_server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )
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.
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?
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.)