Tuesday, April 12, 2011

How to find database related version info

1 How to find the version of the Export and Import utilities.
The version of the Export and Import utilities can be obtained by invoking the utilities with the parameter HELP=Y. The banner of the help page shows the version of the utility. For example:
> exp HELP=Y
Export: Release 8.1.7.4.1 - ... (other output)

> imp HELP=Y
Import: Release 9.2.0.8.0 - ... (other output)

> expdp HELP=Y
Export: Release 10.1.0.5.0 - ... (other output)

> impdp HELP=Y
Import: Release 10.2.0.3.0 - ... (other output)
Alternatively you could invoke the utility in interactive mode (just enter: 'exp' or 'imp' or 'expdp' or 'impdp') in order to obtain the banner, and cancel the program with CTRL-C (on Windows) or CTRL-D (on Unix).


2 How to find the version of the database.

The easiest way to determine the database version (including the main patch-set release which has been installed on the database) is to perform the following query in SQL*Plus:

SELECT * FROM v$version;

BANNER 
---------------------------------------------------------------- 
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi 
PL/SQL Release 10.2.0.3.0 - Production 
CORE    10.2.0.3.0      Production 
TNS for Solaris: Version 10.2.0.3.0 - Production 
NLSRTL Version 10.2.0.3.0 - Production


The output shows the edition (Enterprise), the version of the database (Oracle10g), the release with the installed patch-set (10.2.0.3.0), and the 32-bit/64-bit version (64-bit).

In general, if Oracle is a 64bit version, the word '64bit' will be displayed in the connection information of the opening banners of the Oracle executables such as SQL*Plus. It will also be displayed in the headers of Oracle trace files. Otherwise if the word '64bit' is not displayed, it can be assumed that Oracle is a 32bit version.

Alternatively, when starting SQL*Plus and connecting to the database, the connect message also displays the version of the database:

> sqlplus system/manager

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jul 31 09:27:19 2007
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Subsequent cumulative patch-sets installations, such as the 10.2.0.3.8 patch-set bundle for the Windows platform, will not always be shown in the banner. Starting with Oracle10g Release 2 (10.2.0.x) the patchset and installed Critical Patch Update information can also be examined inside the database, e.g.:
SET lines 100 numwidth 12 pages 100
COL action_time FOR a30
COL action FOR a12
COL version LIKE action
COL comments FOR a30

SELECT action_time, action, version, id, comments
  FROM dba_registry_history
 ORDER BY action_time;

ACTION_TIME                   ACTION   VERSION           ID COMMENTS
----------------------------- -------- ----------- -------- --------------------------
15-MAR-06 03.01.00.987490 AM  UPGRADE  10.2.0.2.0           Upgraded from 10.2.0.1.0
18-JAN-07 02.10.05.942649 PM  UPGRADE  10.2.0.3.0           Upgraded from 10.2.0.2.0
18-MAR-07 04.11.23.119000 PM  CPU      10.2.0.3.0   5846376 Patch2


3 How to Find the Database Characterset
Check the view NLS_DATABASE_PARAMETERS.
You can find out the database characterset with the following query in SQL*Plus:
COL value FOR a20
SELECT * FROM nls_database_parameters
 WHERE parameter LIKE '%SET' 
 ORDER BY 1;  


How to find the version of the Operating System
4.1. Which Unix version is installed.
   --------------------------------
   The Unix command 'uname -a' (generally located in /usr/bin) is available on
   most Unix platforms and is usually sufficient to retrieve basic information
   about the Operating System version. Examples:

     HP-Unix / Sun            : /usr/bin/uname -a
     Linux                    : /bin/uname -a
     Silicon Graphics SGI     : /bin/uname -aR
     IBM-RS6000 AIX           : /usr/bin/oslevel
     Digtial Unix/Compaq Tru64: /usr/sbin/sizer -v

   To determine if your Operating System in 32-bit or 64-bit, check the 
   following:

     HP-Unix (11+)            : /usr/bin/getconf KERNEL_BITS
     Sun (7+)                 : /bin/isainfo -v
     Linux                    : more /proc/cpuinfo   See also:
                                Note:269976.1 "Linux: checking 64-bit Info"
     Silicon Graphics SGI     : only available in 64-bit
     IBM-RS6000 AIX           : /bin/lslpp -l bos.64bit
     Digtial Unix/Compaq Tru64: only available in 64-bit

5 Check the version and status of Data Dictionary Components
Enter in SQL*Plus:
SET lines 80 
COL version FOR A10 
COL comp_id FOR A8 
COL schema LIKE comp_id 
COL comp_name FOR A35 
COL status FOR A12 
SELECT comp_id, schema, status, version, comp_name 
  FROM dba_registry 
 ORDER BY 1; 

References:
1 Export-Import iSR - How to Find the Database and Export/Import Version [ID 175627.1]
2 Export-Import iSR - How to Find the Database Characterset [ID 175630.1]
3 Export-Import iSR - How to Find the Operating System Version and if 32 or 64 Bit [ID 175628.1]
4 Export-Import iSR - How to Find the Version of the Data Dictionary Components [ID 175632.1]

Labels: ,

Friday, April 1, 2011

11gR2 SCAN FAQ

In 11gR2, Oracle introduced a new feature called Single Client Access Name (SCAN).  This new feature created a lot of confusions to me, so I decided to do some research to under this new feature. 


I found following Oracle Support notes and other online sources explaining this new feature
1 11gR2 Grid Infrastructure Single Client Access Name (SCAN) Explained [ID 887522.1]
1.1 SCAN Concepts
1.2 Common Questions Regarding SCAN
          1.2.1 How can we configure the SCAN and SCAN listener?
          1.2.2 Do we still need to configure local listeners on each node?
          1.2.3 How does SCAN work ?
          1.2.4 Instead of DNS or GNS, Can we use '/etc/hosts' to resolve SCAN? 
          1.2.5 Can we use the previous method (Using VIP) for client connection?
          1.2.6 Is it mandatory to use SCAN? 
          1.2.7 Is it supported to remove SCAN? 
          1.2.8 Demo 
1.3 References
NOTE:1053147.1 - 11gR2 Clusterware and Grid Home - What You Need to Know
NOTE:887471.1 - PRVF-4664 PRVF-4657: Found inconsistent name resolution entries for SCAN name
NOTE:972500.1 - How to Modify SCAN Setting after Installation
NOTE:975457.1 - How to Troubleshoot Connectivity Issue with 11gR2 SCAN Name
11.2 Scan and Node TNS Listener Setup Examples [ID 1070607.1]
        Real Application Clusters Admin and Deployment Guide
        Clusterware Administration and Deployment Guide
        Oracle Grid Infrastructure Installation Guide
        www.oracle.com/technetwork/database/clustering/overview/scan-129069.pdf


2 How to set up SCAN? How to check SCAN status? 
2.1 First of all, verify SCAN VIP and Listener setup
[oracle@racdbdb01 admin]$ $GRID_HOME/bin/cluvfy comp scan -verbose


Verifying scan


Checking Single Client Access Name (SCAN)...
  SCAN Name         Node          Running?      ListenerName  Port          Running?
  ----------------  ------------  ------------  ------------  ------------  ------------
  racdb-scan         racdbdb02      true          LISTENER_SCAN1  1521          true
  racdb-scan         racdbdb01      true          LISTENER_SCAN2  1521          true
  racdb-scan         racdbdb01      true          LISTENER_SCAN3  1521          true


Checking TCP connectivity to SCAN Listeners...
  Node          ListenerName              TCP connectivity?
  ------------  ------------------------  ------------------------
  localnode     LISTENER_SCAN1            yes
  localnode     LISTENER_SCAN2            yes
  localnode     LISTENER_SCAN3            yes
TCP connectivity to SCAN Listeners exists on all cluster nodes


Checking name resolution setup for "racdb-scan"...
  SCAN Name     IP Address                Status                    Comment
  ------------  ------------------------  ------------------------  ----------
  racdb-scan     192.168.100.174           passed
  racdb-scan     192.168.100.175           passed
  racdb-scan     192.168.100.176           passed


Verification of SCAN VIP and Listener setup passed


Verification of scan was successful.        


Note: in my environment, before the RAC installation, our SA had decided which IPs to be used for SCAN IPs, and set it up in DNS.  During RAC installation, it prompted for the SCAN name. 


2.2 check /etc/hosts file to see if IPs used by SCAN are set. 


2.3 check DNS setup for the scan address

[oracle@racdb01 admin]$ nslookup rac-scan
Server:         192.168.100.10
Address:        192.168.100.10#53


Name:   rac-scan.hostdb.com
Address: 192.168.100.174
Name:   rac-scan.hostdb.com
Address: 192.168.100.176
Name:   rac-scan.hostdb.com
Address: 192.168.100.175


2.4 Differences between SCAN Listener and Node Listener
-- Oracle recommends using 3 SCAN listeners for RAC.  If the RAC has 3 nodes, each node will have its own SCAN listener. If there is only 1 SCAN listener configured, then only 1 node would have the SCAN Listener running. 
-- Each node has its own listener, just like the regular listener in the single instance database.
-- Warning: make sure that both SCAN Listener and Node (Local) Listener are running from the $GRID_HOME, not the one from the $ORACLE_HOME



[oracle@racdb01 admin]$ ps -ef |grep tnslsnr
oracle   11778     1  0 Feb16 ?        00:00:55 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER_SCAN3 -inherit
oracle   11780     1  0 Feb16 ?        00:00:54 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER_SCAN2 -inherit
oracle   11783     1  0 Feb16 ?        00:01:04 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER -inherit
=======================================================
[oracle@node1 ~]$ ps -ef |grep tnslsnr
oracle   15702     1  0 Jan18 ?        00:14:55 /u01/11.2.0.2/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
oracle   16015     1  0 Jan18 ?        00:15:43 /u01/11.2.0.2/grid/bin/tnslsnr LISTENER -inherit
oracle   21481  1293  0 15:10 pts/0    00:00:00 grep tnslsnr



Note: 
1 racdb01 is part of 2 nodes cluster with 3 SCAN Listener configured, so 2 SCAN Listener are running from the same node.  racdb02 has only 1 SCAN listener running. 
2 node1 is part of 2 nodes cluster with only 1 SCAN Listener configured. 

2.5 LOCAL_LISTENER vs REMOTE_LISTENER
-- Database parameter LOCAL_LISTENER points to Node VIP address

SQL> show parameter local_listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=racdb01-vip)(PORT=1521))))

--Database Parameter REMOTE_LISTENER points to SCAN name

SQL> show parameter remote_listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_listener string rac-scan.hostdb.com:1521

Note: please pay extra attention to these two listener settings.  In 10g, if these 2 are not set correctly, TAF would not work properly. 

2.6 listener.ora file
Warning: make sure it's the $GRID_HOME/network/admin/listener.ora being used by the database.
[oracle@racdb01 admin]$ cat $GRID_HOME/network/admin/listener.ora
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
ADMIN_RESTRICTION_LISTENER=ON
SQLNET.EXPIRE_TIME=10

=======================================================
[oracle@node1 ~]$ cat $GRID_HOME/grid/network/admin/listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # 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=ON              # line added by Agent




Note: The ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ parameter is set to allow the listener to accept connections for pre-11.2 databases which did not register the dynamic endpoint.


2.7 Listener status checking: 
Before check node listener and SCAN listener status, make sure set TNS_ADMIN=$GRID_HOME/network/admin
-- lsnrctl status listener
The above command would show 1 instance registered, which is the local instance running on the node where the above command is executed. 





[oracle@racdb01 admin]$ lsnrctl status listener


LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 01-APR-2011 15:25:06


Copyright (c) 1991, 2010, Oracle.  All rights reserved.


Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                16-FEB-2011 16:09:37
Uptime                    43 days 22 hr. 15 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/racdb01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.172)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.250.10)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "rac" has 1 instance(s).
  Instance "rac1", status READY, has 1 handler(s) for this service...
Service "rac_taf" has 1 instance(s).
  Instance "rac1", status READY, has 1 handler(s) for this service...
The command completed successfully


=======================================================
[oracle@node1 ~]$ lsnrctl status listener


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 01-APR-2011 15:25:00


Copyright (c) 1991, 2009, Oracle.  All rights reserved.


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1.hostdb.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                18-JAN-2011 16:54:26
Uptime                    72 days 21 hr. 30 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/11.2.0.2/grid/network/admin/listener.ora
Listener Log File         /u02/oracle/diag/tnslsnr/node1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.130)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.211)(PORT=1521)))
Services Summary...
Service "netapp_taf.hostdb.com" has 1 instance(s).
  Instance "racflo1", status READY, has 1 handler(s) for this service...
Service "racflo.hostdb.com" has 1 instance(s).
  Instance "racflo1", status READY, has 1 handler(s) for this service...
Service "racfloXDB.hostdb.com" has 1 instance(s).
  Instance "racflo1", status READY, has 1 handler(s) for this service...
The command completed successfully


2.8 endpoints_listener.ora 
"New file for 11.2 called endpoints_listener.ora, showing the Node IP address and Node VIP address."


[grid@netrac1 admin]$ more endpoints_listener.ora
LISTENER_NETRAC1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=netrac1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=12.345.678.888)(PORT=1521)(IP=FIRST)))) # line added by Agent




"Endpoints_listener.ora file is there for backward compatibility with pre-11.2 databases.
DBCA needs to know the endpoints location to configure database parameters and tnsnames.ora file.
It used to use the listener.ora file, 11.2 RAC listener.ora by default only has IPC entries."

2.9 Check SCAN Listener status
Warning: before check node listener and SCAN listener status, make sure set TNS_ADMIN=$GRID_HOME/network/admin


-- In my 1st set of cluster, only one SCAN listener configured, so in node1:

[oracle@node1 ~]$ lsnrctl status listener_scan1


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 01-APR-2011 15:32:35


Copyright (c) 1991, 2009, Oracle.  All rights reserved.


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                18-JAN-2011 16:53:06
Uptime                    72 days 21 hr. 39 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/11.2.0.2/grid/network/admin/listener.ora
Listener Log File         /u01/11.2.0.2/grid/log/diag/tnslsnr/node1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.210)(PORT=1521)))
Services Summary...
Service "netapp_taf.hostdb.com" has 2 instance(s).
  Instance "racflo1", status READY, has 1 handler(s) for this service...
  Instance "racflo2", status READY, has 1 handler(s) for this service...
Service "racflo.hostdb.com" has 2 instance(s).
  Instance "racflo1", status READY, has 1 handler(s) for this service...
  Instance "racflo2", status READY, has 1 handler(s) for this service...
Service "racfloXDB.hostdb.com" has 2 instance(s).
  Instance "racflo1", status READY, has 1 handler(s) for this service...
  Instance "racflo2", status READY, has 1 handler(s) for this service...
The command completed successfully


======================
In node2, there is no SCAN listener running:

[oracle@node2 ~]$ ps -ef |grep tnslsnr
oracle   14517     1  0 Jan18 ?        00:14:05 /u01/11.2.0.2/grid/bin/tnslsnr LISTENER -inherit
oracle   15673 15301  0 15:36 pts/0    00:00:00 grep tnslsnr


============================================
In my 2nd set of 2 nodes cluster, 3 SCAN listener were configured, so in the 1st node:


[oracle@racdb01 ~]$ ps -ef |grep tnslsnr
oracle   11778     1  0 Feb16 ?        00:00:55 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER_SCAN3 -inherit
oracle   11780     1  0 Feb16 ?        00:00:54 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER_SCAN2 -inherit
oracle   11783     1  0 Feb16 ?        00:01:05 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle   18197 30756  0 15:44 pts/0    00:00:00 grep tnslsnr
[oracle@racdb01 ~]$ export TNS_ADMIN=$GRID_HOME/network/admin
[oracle@racdb01 ~]$ lsnrctl status listener_scan1


LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 01-APR-2011 15:44:51


Copyright (c) 1991, 2010, Oracle.  All rights reserved.


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 2: No such file or directory
[oracle@racdb01 ~]$ lsnrctl status listener_scan2


LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 01-APR-2011 15:44:53


Copyright (c) 1991, 2010, Oracle.  All rights reserved.


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN2
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                16-FEB-2011 16:09:37
Uptime                    43 days 22 hr. 35 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/11.2.0/grid/log/diag/tnslsnr/racdb01/listener_scan2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN2)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.174)(PORT=1521)))
Services Summary...
Service "racdb" has 2 instance(s).
  Instance "racdb1", status READY, has 1 handler(s) for this service...
  Instance "racdb2", status READY, has 1 handler(s) for this service...
Service "racdb_taf" has 2 instance(s).
  Instance "racdb1", status READY, has 1 handler(s) for this service...
  Instance "racdb2", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@racdb01 ~]$ lsnrctl status listener_scan3


LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 01-APR-2011 15:44:55


Copyright (c) 1991, 2010, Oracle.  All rights reserved.


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN3
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                16-FEB-2011 16:09:37
Uptime                    43 days 22 hr. 35 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/11.2.0/grid/log/diag/tnslsnr/racdb01/listener_scan3/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN3)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.175)(PORT=1521)))
Services Summary...
Service "racdb" has 2 instance(s).
  Instance "racdb1", status READY, has 1 handler(s) for this service...
  Instance "racdb2", status READY, has 1 handler(s) for this service...
Service "racdb_taf" has 2 instance(s).
  Instance "racdb1", status READY, has 1 handler(s) for this service...
  Instance "racdb2", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@racdb01 ~]$




In the 2nd node:

[oracle@racdb02 ~]$ ps -ef |grep tnslsnr
oracle   12312 12101  0 15:47 pts/0    00:00:00 grep tnslsnr
oracle   13398     1  0 Feb16 ?        00:00:35 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle   13558     1  0 Feb16 ?        00:00:48 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
[oracle@racdb02 ~]$ lsnrctl status listener_scan1


LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 01-APR-2011 15:48:10


Copyright (c) 1991, 2010, Oracle.  All rights reserved.


TNS-01101: Could not find service name listener_scan1
[oracle@racdb02 ~]$ lsnrctl status listener_scan2


LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 01-APR-2011 15:48:20


Copyright (c) 1991, 2010, Oracle.  All rights reserved.


TNS-01101: Could not find service name listener_scan2
[oracle@racdb02 ~]$ export TNS_ADMIN=$GRID_HOME/network/admin
[oracle@racdb02 ~]$ lsnrctl status listener_scan1


LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 01-APR-2011 15:48:26


Copyright (c) 1991, 2010, Oracle.  All rights reserved.


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                16-FEB-2011 16:10:01
Uptime                    43 days 22 hr. 38 min. 25 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/11.2.0/grid/log/diag/tnslsnr/racdb02/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.176)(PORT=1521)))
Services Summary...
Service "rac" has 2 instance(s).
  Instance "rac1", status READY, has 1 handler(s) for this service...
  Instance "rac2", status READY, has 1 handler(s) for this service...
Service "rac_taf" has 2 instance(s).
  Instance "rac1", status READY, has 1 handler(s) for this service...
  Instance "rac2", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@racdb02 ~]$ lsnrctl status listener_scan2


LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 01-APR-2011 15:48:31


Copyright (c) 1991, 2010, Oracle.  All rights reserved.


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 2: No such file or directory


2.10 Further information on the listeners can be found via svrctl.
Configuration changes to the Listener are made via svrctl.



[oracle@racdb02 ~]$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521
[oracle@racdb02 ~]$ srvctl config scan
SCAN name: rac-scan, Network: 1/192.168.100.0/255.255.255.0/bondeth0
SCAN VIP name: scan1, IP: /rac-scan.hostdb.com/192.168.100.176
SCAN VIP name: scan2, IP: /rac-scan.hostdb.com/192.168.100.174
SCAN VIP name: scan3, IP: /rac-scan.hostdb.com/192.168.100.175




[oracle@node1 ~]$  srvctl config scan
SCAN name: rac-cluster.hostdb.com, Network: 1/192.168.100.0/255.255.255.0/bond0
SCAN VIP name: scan1, IP: /rac-cluster.hostdb.com/192.168.100.210
[oracle@node1 ~]$  srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521 


3 References

  1. How To Configure Server Side Transparent Application Failover [ID 460982.1]
  2. Note 952903.1 How to update the IP address of the SCAN VIP resources (ora.scan.vip)
  3. Note 975457.1 How to Troubleshoot Connectivity Issue with 11gR2 SCAN Name
  4. Note 972500.1 How to Modify SCAN Setting after Installation
  5. NOTE:948456.1 - Pre 11.2 Database Issues in 11gR2 Grid Infrastructure Environment
  6. How to Access a RAC Database configured with SCAN via Java Thin Driver [ID 1081179.1]
  7. How to Setup SCAN Listener and Client for TAF and Load Balancing [Video] [ID 1188736.1]
  8. ORA-12545 or ORA-12537 Reported while Connecting to RAC through SCAN name [ID 970619.1]

Labels: , ,