TimArcher.com A blog about technology...and more.

On the Oracle systems that I manage I typically like to add a simple layer of security by changing the port that the listener listens on. Given that I work for an institution that allows the public to connect to its internal network, and since that network has an Oracle Database server on it, I wanted to make it just a little bit harder to find our databases. I recognize that a good hacker will find our Oracle Database servers regardless of the port that they run on. However, by changing the listening port I hope to eliminate the potential for a member of the public bringing in an infected computer that tries to do something malicious to Oracle databases.

To do this you first have to setup your listener to listen on a different port, and then you have to change your tnsnames.ora file to reference the new port. It really is rather simple and I will demonstrate through the steps below. The steps below have been tested on Oracle 10gR2 running on RedHat AS4.

1. First modify your listener.ora file.
   

   vi $ORACLE_HOME/network/admin/listener.ora
   

2. Where the port 1521 is specified, change it to the new port you want to listen on. I use port 17969. My listener.ora file now looks like:

   SID_LIST_LISTENER =
     (SID_LIST =
       (SID_DESC =
         (SID_NAME = PLSExtProc)
         (ORACLE_HOME = /opt/app/oracle/product/10.1.2_infra)
         (PROGRAM = extproc)
       )
       (SID_DESC =
         (GLOBAL_DBNAME = ASDB.as1.filmoregroup.com)
         (ORACLE_HOME = /opt/app/oracle/product/10.1.2_infra)
         (SID_NAME = ASDB)
         (ENVS = "EXTPROC_DLLS=ANY")
       )
     )
   
   LISTENER =
     (DESCRIPTION_LIST =
       (DESCRIPTION =
         (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = as1.mydomain.com)(PORT = 17969))
         )
         (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
         )
       )
     )
   

3. Now we will restart the listener and ensure it is listening on the new port. As the oracle user run the following commands:

   lsnrctl stop
   lsnrctl start
   

   You should now see from the output of the lsnrctl start command that it is listening on your new port. The relevant section looks like:

   Listening Endpoints Summary...
     (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=as1.mydomain.com)(PORT=17969)))
     (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
   Services Summary...
   Service "ASDB.as1.mydomain.com" has 1 instance(s).
     Instance "ASDB", status UNKNOWN, has 1 handler(s) for this service...
   Service "PLSExtProc" has 1 instance(s).
     Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
   The command completed successfully 
   

4. Now that the listener is started and listening on the new port we need to setup tnsnames.ora to specify the new port to connect to. Open up your tnsnames.ora file in an editor:

   vi $ORACLE_HOME/network/admin/tnsnames.ora
   

5. Change anywhere it references port 1521 to the new port you specified in the listener.ora file. My tnsnames.ora file now looks like:

   ASDB =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = as1.mydomain.com)(PORT = 17969))
       (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = asdb.as1.mydomain.com)
       )
     )
   
   EXTPROC_CONNECTION_DATA =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = as1.mydomain.com)(PORT = 17969))
       )
       (CONNECT_DATA =
         (SERVICE_NAME = PLSExtProc)
       )
     )
   

6. Now you should verify that you can connect to your database. First I do a tnsping to ensure I can ping the listener.

   tnsping asdb
   

   If the ping is successful your results should look like:

   Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = as1.filmoregroup.com)(PORT = 17969)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = asdb.as1.somedomain.com)))
   OK (10 msec)
   

7. And then the last step is to try to connect using SQLPlus. If it works then you have successfully configured your Oracle Database to operate off the new port.