tnsnames.ora setup

system@DEMO>
system@DEMO> show parameter name

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string                           DEMO
db_unique_name                       string                           DEMO
global_names                         boolean                          FALSE
instance_name                        string                           DEMO1
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string                           DEMO
system@DEMO>
system@DEMO> show parameter domain

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
db_domain                            string


system@DEMO>  select * from global_name;

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
DEMO


sun001@DEMO1:/ora01/oracle/admin/network $ cat sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, LDAP, EZCONNECT)
ADR_BASE = /oramisc01/oracle
DIAG_ADR_ENABLED=true
SQLNET.SEND_TIMEOUT=10

=============================================================================

Db_name=DEMO

Service name : DEMO and DEMO_REPORT

step 1

# In database

alter system set service_name= DEMO,  DEMO_REPORT scope=both sid='*';

step 2
check sqlnet.ora and comment parameter names.default_domain 

step 3
lsnrctl reload
lsnrctl status 


step 4

# in tnsnames

DEMO_REPORT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST <SCANNAME>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DEMO)
    )
  )


DEMO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST <SCANNAME>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DEMO)
    )
  )

# the end

listener file on 2 nodes cluster 11gR2

cluster with 2 nodes sun001 and sun002

sun001

VIP         sun001-VIP

HOST IP          HOST=x.x.x.xx 

sun001

VIP         sun002-VIP

HOST IP          HOST=x.x.x.yy

PORT = 1521

Directory for trace and logs /oramisc01/oracle

NOTE : location of listener file is <grid_home> not <db_home>

Starting with Oracle Grid Infrastructure 11g Release 2 the node listener run out of the Oracle Grid Infrastructure home and listens on the node-VIP using the specified port (default port is 1521). 

Ever since Oracle Database 11g Release 2, SCAN is an essential part of the Oracle RAC database configuration and therefore the REMOTE_LISTENER parameter is set to the SCAN per default, assuming that the database is created using standard Oracle tools (e.g. the formerly mentioned DBCA). This allows the instances to register with the SCAN Listeners as remote listeners to provide information on what services are being provided by the instance, the current load and a recommendation on how many incoming connections should be directed to the instance.

Single Client Access Name (SCAN) is a feature used in Oracle Real Application Clusters environments that provides a single name for clients to access any Oracle Database running in a cluster.

SCAN resolves to three IP addresses using a round-robin algorithm. 

You can check the SCAN configuration in DNS using “nslookup”

READ MORE http://www.oracle.com/technetwork/products/clustering/overview/scan-129069.pdf

listener.ora and endpoints_listener.ora are in <grid_home>/network/admin directory

$ cat listener.ora on both nodes

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent

LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))))                # line added by Agent

LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))))                # line added by Agent

LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON                # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON                # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

# custom parameters

ADR_BASE_LISTENER = /oramisc01/oracle

ADR_BASE_LISTENER_SCAN1 = /oramisc01/oracle

ADR_BASE_LISTENER_SCAN2 = /oramisc01/oracle

ADR_BASE_LISTENER_SCAN3 = /oramisc01/oracle

$ cat endpoints_listener.ora on node 1 sun001 (notice sun001–vip, ping sun001-vip to verify)

LISTENER_sun001=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)

(HOST=sun001-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.xx)(PORT=1521)(IP=FIRST))))                # line added by Agent

$ cat endpoints_listener.ora on node 2 lnx70177 (notice sun002–vip, ping sun002-vip to verify)

LISTENER_sun001=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)

(HOST=sun002-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.yy)(PORT=1521)(IP=FIRST))))              # line added by Agent

# start/stop sequence

srvctl start listener

srvctl start scan

srvctl start scan_listener

stop in the same order as above

# How to check config

srvctl config  listener

srvctl config  scan

srvctl config  scan_listener

# How to check status

srvctl status listener

srvctl status scan

srvctl status scan_listener

# sample 

set environment to grid home

sun001@grid $  srvctl config listener

Name: LISTENER

Network: 1, Owner: oracle

Home: <CRS home>

End points: TCP:1521

sun002@grid $ srvctl config listener

Name: LISTENER

Network: 1, Owner: oracle

Home: <CRS home>

End points: TCP:1521

sun002@grid $ srvctl status listener

Listener LISTENER is enabled

Listener LISTENER is running on node(s): sun001, sun002

# Database parameter settings :  local_listener / remote_listener

The LOCAL_LISTENER parameter should be set to the node-VIP.  

The  REMOTE_LISTENER parameter should be set to the SCAN:port
   

IP-SCAN1, IP-SCAN2, IP-SCAN3 are SCAN  IP addresses of SCAN NAME
IP_VIP   are VIP IP address of NODE 1 and NODE 2

Node 1 : 
in local_listener HOST=VIP IP address of node 1

Node 2: 
in local_listener HOST=VIP IP address of node2

remote_listener remans same on both

sys@DEMO> show parameter list

NAME                            VALUE
------------------------------- ------------------------------
listener_networks            
local_listener                   (ADDRESS=(PROTOCOL=TCP)(HOST=<IP-VIP)(PORT=1521))
                                
remote_listener        (ADDRESS_LIST=
                                (ADDRESS=(PROTOCOL=TCPS)(HOST=<IP-SCAN1>)(PORT=1521))
                                (ADDRESS=(PROTOCOL=TCPS)(HOST=<IP-SCAN2>)(PORT=1521))
                                (ADDRESS=(PROTOCOL=TCPS)(HOST=<IP-SCAN3>)(PORT=1521)))
                             
 # the end                      

ADR_BASE_LISTENER for listener log adn trace files

ADR_BASE_LISTENER directory

To Change the adr directory for listener from default

Make Changes in listener.ora file

 e.g. ADR_BASE_LISTENER = /oralog/oracle

example:

/oralog/oracle/oradiag_oracle/diag/clients/user_oracle/

Hope this helps. Regards Rupam

Install Oracle InstantClient basic and instantclient sqlplus on win32

1. Download Oracle Instant Client  from Oracle site

http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

e.g

   instantclient-basic-win32-11.1.0.7.0.zip
   instantclient-sqlplus-win32-11.1.0.7.0.zip

Oracle JDBC Connections

Example of connect string using jdbc

Recommend

the method we use for WebSphere:

jdbc:oracle:thin:@ldap://oid-uat:389/demo,cn=OracleContext,dc=csxt,dc=csx,dc=com  

A single instance:

   jdbc:oracle:thin:@lnx101-vip:1521:demo

Using TNSName: 

   jdbc:oracle:thin:@demo.world

Full URL:

   jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = lnx101-vip.csxt.csx.com)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = lnx102-vip.csxt.csx.com)(PORT = 1521))(LOAD_BALANCE = yes)(FAILOVER = on)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = demo)(FAILOVER_MODE =(TYPE = session)(METHOD = basic)(RETRIES = 10)(DELAY = 1))))

another option :

jdbc:oracle:thin:@ldap:// oid-dev:389/ demo,cn=OracleContext,dc=csxt,dc=csx,dc=com ldap:// oid-dev:389/ demo,cn=OracleContext,dc=csxt,dc=csx,dc=com

Hope this help. Regards Rupam

Install Oracle Instant Client Installation on UNIX / Linux Server

1. Confirm you can login as the oracle account (primary group dba) on the target server:

[oracle@lnx544 ~]$ id

uid=5005(oracle) gid=56(dba) groups=56(dba),57(oinstall),58(dbasudo),102(operator),2003(prpftp),2004(tcsgrp),5084(paiseasftp),5107(da)

Oracle Instant Client

Instant Client allows you to run your applications without installing the standard Oracle client or having an ORACLE_HOME. OCI, OCCI, Pro*C, ODBC, and JDBC applications work without modification, while using significantly less disk space than before. Even SQL*Plus can be used with Instant Client. No recompile, no hassle.

Hope this help. Regards Rupam

SCAN Listener

SCAN Listener

New Feature in Oracle 11g

Single Client Access Name (SCAN) allows clients to use one name in connection string to connect to the cluster as a whole.  

Why SCAN listener?

1. Removes the requirements to change the client connect if cluster changes were made

2. Load Balance across the instance providing a service

3. Failover between moved instance

Note: REMOTE_LISTERNER register the instance with all SCAN listeners.

 

How to Configure SCAN listener in RAC?

# Prerequite

the SCAN name with three ip addresses assigned via DNS in round robin fashion

# Configure

In the step 4 of 16 of Oracle Grid Instrastructure/Install and Configure Grid Infrastructure of a grid

# listener.ora

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))

   

LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) 

LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) 

LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) 

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON      
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON      
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON          

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON                     

# endpoints_listener.ora
   LISTENER_LNX200=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=lnx200-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1521)(IP=FIRST))))           # line added by Agent

# tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ORACL01.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

# start/stop sequence

srvctl start listener

srvctl start scan

srvctl start scan_listener

stop in the same order as above

# How to check config

srvctl config  listener

srvctl config  scan

srvctl config  scan_listener

# How to check status

srvctl status listener

srvctl status scan

srvctl status scan_listener

listerner log file - switch to new log file

#!/bin/ksh

. ~/.profile
ORACLE_SID=demo;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES

listener_log_extension=`date '+%m%d%y'`

echo "set current_listener LISTENER" >/tmp/listener
echo "set log_file listener.$listener_log_extension">>/tmp/listener
echo "quit">>/tmp/listener


lsnrctl < /tmp/listener