Re: Break out instances to different ports

Comments embedded.
On Apr 4, 4:28 pm, "ken.h..._at_holmcc.com" <ken.h..._at_holmcc.com> wrote:
> We have three instances of Oracle on one machine under the same port
> (1521). I would like to break the TEST and DEV instances out to their
> own ports. What needs to be done?

Set up separate listeners for each instance.

> It is possible to take PROD down
> for a short period.

Why? This is a listener configuration issue, not an instance problem.

> However, PROD needs to stay just the way it is
> with no changes.
>

So don't change the tnsnames.ora entry.

> Here's our listener.ora config:
>
> # listener.ora Network Configuration File: /app/oracle/product/oradata/
> network/admin/listener.ora
> # Generated by Oracle configuration tools.
>
> SID_LIST_LISTENER =
> (SID_LIST =
> (SID_DESC =
> (SID_NAME = PLSExtProc)
> (ORACLE_HOME = /app/oracle/product/oradata)
> (PROGRAM = extproc)
> )
> )
>
> LISTENER =
> (DESCRIPTION_LIST =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
> (ADDRESS = (PROTOCOL = TCP)(HOST = unix18.umsmed.edu)(PORT =
> 1521))
> )
> )
>

You need two named listeners in addition to your default listener to accomplish this, such as listener_test and listener_dev, on separate ports. Then change your tnsnames.ora to reflect the new port assignments. Then, start your additional listeners, connect to TEST and DEV and issue an 'alter system register;' to have your new listeners recognize them.

> Here's our tnsnames.ora config:
>
> # tnsnames.ora Network Configuration File: /app/oracle/product/oradata/
> network/admin/tnsnames.ora
> # Generated by Oracle configuration tools.
>
> TEST =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = TCP)(HOST = unix18.umsmed.edu)(PORT =
> 1521))
> (CONNECT_DATA =
> (SERVER = DEDICATED)
> (SERVICE_NAME = TEST)
> )
> )
>
> DEV =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = TCP)(HOST = unix18.umsmed.edu)(PORT =
> 1521))
> (CONNECT_DATA =
> (SERVER = DEDICATED)
> (SERVICE_NAME = DEV)
> )
> )
>
> PROD =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = TCP)(HOST = unix18.umsmed.edu)(PORT =
> 1521))
> (CONNECT_DATA =
> (SERVER = DEDICATED)
> (SERVICE_NAME = PROD)
> )
> )
>
> EXTPROC_CONNECTION_DATA =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
> )
> (CONNECT_DATA =
> (SID = PLSExtProc)
> (PRESENTATION = RO)
> )
> )

This is not rocket science, it's a simple listener configuration exercise. No down time should be necessary for any of the instances.

David Fitzjarrell Received on Wed Apr 04 2007 - 17:37:13 CDT