February 28, 2014  by admin
0
 

create a dedicated listener for your databases on ODA

OK, you have already noticed that the ODA offers a default LISTENER lying on the SCAN address. This is really useful in most cases but when you run single instances without any HA mechanisms and that you want to be able to get a named listener per db, it’s another story.

Here is therefore a simple procedure to create your own listeners by using the vip address only (I assume that your database has already been created).

 

1. To begin, I strongly recommend dedicating a global home for all your listeners (using the latest engine version)



[root@oda01:/] oakcli create dbhome 
...

[root@oda01:/] oakcli show dbhomes

Oracle Home Name      Oracle Home version                  Home Location
----------------      -------------------                  ------------
OraDb11203_home1      11.2.0.3.8(16902043,17076717)       /u01/app/oracle/product/11.2.0.3/dbhome_1
OraDb11204_home1      11.2.0.4.0                          /u01/app/oracle/product/11.2.0.4/dbhome_1 <------
OraDb11204_home2      11.2.0.4.0                          /u01/app/oracle/product/11.2.0.4/dbhome_2

 

2. We create the listener entries within this dedicated home

 

Just edit the following files by pointing on the vip:

/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/sqlnet.ora
/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/tnsnames.ora
/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora

 

Here is an example for a database which name is TESTDB:

###################################################
# LISTENER.ORA
###################################################

LSN_TESTDB =
  (DESCRIPTION_LIST =
     (DESCRIPTION =
        (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oda01-vip)(PORT = 1530)))
     )
  )

SID_LIST_LSN_TESTDB =
  (SID_LIST=
        (SID_DESC=
          (SID_NAME = TESTDB)
          (GLOBAL_DBNAME = TESTDB_SITE1_DGB)
          (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
        )
  )

TRACE_LEVEL_LSN_TESTDB         = OFF
ADMIN_RESTRICTIONS_LSN_TESTDB  = ON
USE_PLUG_AND_PLAY_TESTDB       = OFF
STARTUP_WAIT_TIME_TESTDB       = 0
CONNECT_TIMEOUT_TESTDB         = 10


###################################################
# SQLNET.ORA
###################################################

DEFAULT_SDU_SIZE   = 32767
AUTOMATIC_IPC      = ON
SQLNET.EXPIRE_TIME = 5

# Name resolution priority
NAMES.DIRECTORY_PATH=(tnsnames, ezconnect)


###################################################
# TNSNAMES.ORA
###################################################

TESTDB =
 (DESCRIPTION =
    (ADDRESS_LIST=
       (ADDRESS = (PROTOCOL = TCP)(HOST = oda01-vip)(PORT = 1531))
    )
   (CONNECT_DATA =
      (SERVICE_NAME = TESTDB)
    )
  )

 

3. We update the local_listener directly into the database

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=oda01-vip)(PORT=1530))' scope=both;
SQL> alter system register;

In the case you would get a second dedicated listener (for admin purpose or anything else), you can set it this way (using 1550 in this example).

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=oda01-vip)(PORT=1531))','(ADDRESS=(PROTOCOL=TCP)(HOST=oda01-vip)(PORT=1550))' scope=both;
SQL> alter system register;

 

4. Now, we just add the listener to grid infra (from the LSN home)

[grid@oda01:/] export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1
[grid@oda01:/] lsnrctl stop LSN_TESTDB 
[grid@oda01:/] srvctl add listener -l LSN_TESTDB -p TCP:1531 -o /u01/app/oracle/product/11.2.0.4/dbhome_1
[grid@oda01:/] srvctl config database -d TESTDB 
[grid@oda01:/] srvctl start listener -l LSN_TESTDB -n oda01
[grid@oda01:/] /u01/app/11.2.0.4/grid/bin/crsctl status res -t

 

Note. In the case you would like to be able to move your database from one node to another (in case of failure of one blade for example), do not forget to also add your listener.ora entries on both nodes to get it immediately available after the failover.

 

5. [optional] Set the listener in oratab to keep a trace

[oracle@oda01:/] vi /etc/oratab
LSN_TESTDB:/u01/app/oracle/product/11.2.0.4/dbhome_1:N

 

Here you are 😀

Leave a comment

Please wait......