How to fast recover data lost due to user errors (without RMAN)

To a DBA time is money. In cases of downtime …. more time stoped, less money and less time stoped, more money.

Many times we make mistakes for not knowing all the resources available in certain situations that require quick action.

When someone calls you and says that a someone mistakenly deleted data, usually the first thought that comes is that we need to perform the restore of the data.

Here comes an important issue. How to restore data in a short period to cause less impact and less downtime.

Below is the request of a colleague who was in this situation, read:
Version:10g R2
One of our DBA colleagues accidently updated all the records of a table to a particular values without a WHERE clause.I need to revert it back to its previous state (as of 11 am today morning)
Since this is 3 TB schema, we don’t take logical backup for this schema. Flashback feauture is not enabled either.
I have LEVEL0 backup from Monday morning and LEVEL1 backup from Tuesday (today) morning. How can I restore the table to the previous state?

We have many options, but what option you decide to use will make all difference.

The first feature that I will try to use is the FLASHBACK QUERY.  Although this feature exists since version 9i unfortunately for many people is still a novelty.

Let’s test:

$ sqlplus user_test@db11g

SQL*Plus: Release 11.2.0.2.0 Production on Fri Feb 3 14:32:56 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Enter password:

Connected to:
Oracle Database 11g Release 11.2.0.1.0 - Production
With the Automatic Storage Management option

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

### CREATING TABLE############
SQL>
CREATE TABLE USER_ERROR AS SELECT * FROM DBA_OBJECTS;
Table created.
##############################

### Populating Data ##########
INSERT INTO USER_ERROR SELECT * FROM USER_ERROR;
COMMIT;
76416 rows inserted
commited

INSERT INTO USER_ERROR SELECT * FROM USER_ERROR;
COMMIT;
152832 rows inserted
commited

INSERT INTO USER_ERROR SELECT * FROM USER_ERROR;
COMMIT;
305664 rows inserted
commited

INSERT INTO USER_ERROR SELECT * FROM USER_ERROR;
COMMIT;
611328 rows inserted
commited
##############################

### Checking Data
SELECT COUNT(1) FROM USER_ERROR;

COUNT(1)
----------------------
1222656

SQL> select count(object_type) qtd,object_type
from user_error
group by object_type
order by 1;

       QTD OBJECT_TYPE
---------- -------------------
        16 LOB PARTITION
        16 EDITION
        16 MATERIALIZED VIEW
        16 RULE
        32 DESTINATION
        48 JAVA SOURCE
        48 SCHEDULE
        64 SCHEDULER GROUP
       112 CONTEXT
       144 INDEXTYPE
       144 WINDOW
       144 UNDEFINED
       160 RESOURCE PLAN
       160 CLUSTER
       208 EVALUATION CONTEXT
       208 JOB CLASS
       256 DIRECTORY
       304 RULE SET
       304 PROGRAM
       400 CONSUMER GROUP
       448 JOB
       576 QUEUE
       816 XML SCHEMA
       880 OPERATOR
      2928 LIBRARY
      3552 PROCEDURE
      3824 TYPE BODY
      3984 SEQUENCE
      4448 TABLE PARTITION
      4864 INDEX PARTITION
      4880 JAVA DATA
      5392 FUNCTION
     13344 JAVA RESOURCE
     17168 TRIGGER
     17296 LOB
     23680 PACKAGE BODY
     24752 PACKAGE
     44624 TYPE
     58752 TABLE
     84384 INDEX
     86384 VIEW
    366768 JAVA CLASS
    446112 SYNONYM

43 rows selected.

SQL> SELECT CURRENT_TIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
03-FEB-12 02.38.08.300885 PM -02:00

### UPDATE WITHOUT WHERE CLAUSE

SQL> UPDATE USER_ERROR SET OBJECT_TYPE='UNKNOWN';

1222656 rows updated.

SQL> COMMIT;

Commit complete.

SQL> select count(object_type) qtd,object_type
from user_error
group by object_type
order by 1;

       QTD OBJECT_TYPE
---------- -------------------
   1222656 UNKNOWN

###### RECOVERING DATA QUICKLY #####

SQL>  CREATE TABLE USER_ERROR_RECOVERED
      AS
      SELECT * FROM USER_ERROR
      AS OF TIMESTAMP TO_TIMESTAMP('03-02-2012 14:38:08','DD-MM-YYYY HH24:MI:SS');

Table created.

SQL> SELECT COUNT(1) FROM USER_ERROR_RECOVERED;

  COUNT(1)
----------
   1222656

SQL> select count(object_type) qtd,object_type
from user_error_recovered
group by object_type
order by 1;
       QTD OBJECT_TYPE
---------- -------------------
        16 RULE
        16 LOB PARTITION
        16 MATERIALIZED VIEW
        16 EDITION
        32 DESTINATION
        48 JAVA SOURCE
        48 SCHEDULE
        64 SCHEDULER GROUP
       112 CONTEXT
       144 UNDEFINED
       144 WINDOW
       144 INDEXTYPE
       160 CLUSTER
       160 RESOURCE PLAN
       208 JOB CLASS
       208 EVALUATION CONTEXT
       256 DIRECTORY
       304 PROGRAM
       304 RULE SET
       400 CONSUMER GROUP
       448 JOB
       576 QUEUE
       816 XML SCHEMA
       880 OPERATOR
      2928 LIBRARY
      3552 PROCEDURE
      3824 TYPE BODY
      3984 SEQUENCE
      4448 TABLE PARTITION
      4864 INDEX PARTITION
      4880 JAVA DATA
      5392 FUNCTION
     13344 JAVA RESOURCE
     17168 TRIGGER
     17296 LOB
     23680 PACKAGE BODY
     24752 PACKAGE
     44624 TYPE
     58752 TABLE
     84384 INDEX
     86384 VIEW
    366768 JAVA CLASS
    446112 SYNONYM

43 rows selected.

### DROPPING TABLE #######

SQL> DROP TABLE USER_ERROR;

Table dropped.

SQL> SELECT COUNT(1) FROM USER_ERROR;
SELECT COUNT(1) FROM USER_ERROR
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> FLASHBACK TABLE USER_ERROR TO BEFORE DROP;

Flashback complete.

SQL>  SELECT COUNT(1) FROM USER_ERROR;

  COUNT(1)
----------
   1222656

SQL>

In the example above I was able to restore data in a short time, saving work and time.

For this procedure succeeds the data must still be in the UNDO tablespace, then when more quickly you identify the error and try to fix it, Will increase the probability of success.

Oracle used the term “flashback” to cover very different things, I don’t like this because it confuse which feature we can use.

To use Flasback Table or Flasback Query you don’t need the “flashback feature” enabled, because flashback query is based on undo information, unlike flashback database which needs flashback logs + redo logs.

What Oracle Editions I can use feature flasback query?
Flashback Query – Is enabled to use in all Oracle Editions no additional cost.
http://www.oracle.com/us/products/database/product-editions-066501.html

What Oracle Editions I can use feature flasback table?
Flashback Table – Is enabled to use only in Oracle Enterprise Edition. (Works in SE edition)
http://www.oracle.com/us/products/database/product-editions-066501.html

Enjoy…

Google

Advertisements

How configure Multiples Public Network in a Grid Infrastructure 11g R2 (11.2) environment

Introduction

Using earlier versions of RAC Oracle 11.2 is not possible to create more than one public subnet to serve different database clients in different sites/network, to get the routing of connections we should have help from a software/hardware from third parties.

Now it’s possible configure multiples network segment (i.e different subnet) with GI/RAC 11.2.
Oracle GI/RAC 11.2 works like a router managing connections of multiple network, routing connections to the correct network/endpoint.

During the Grid Infrastructure installation, it’s not possible configure more than one public/vip network. So, the other networks should be configured manually after installation.

Purpose

The purpose of this post is to explain how to create  multipe network/listener/services on multiple public network in an 11.2 Grid Infrastructure environment.

No downtime is required to perform this task. All tasks can be done with Clusterware and RAC online.

Concept before Starting

Please Note – Limitations of Grid Infrastructure 11g R2:

SCAN feature will work only in one public network, because we can configure only one SCAN for entire Clusterware Stack and SCAN must be associated whith only one public network. So, only one  public network  we can use all feature  of GI 11.2 the others public networks will be required use the VIP in our Oracle Clients  like in version 11g R1 and 10g.

I believe that Oracle will solve this issue in later versions.

Before start configuration you must learn how things work to know to properly configure our RAC.  You need to know how they work to understand how to configure your Oracle RAC.


Basic Concept:

The listener is a server-side process that listens for incoming client connection requests and manages traffic to the database. When a database instance starts, and at various times during its life, the instance contacts a listener and establishes a communication pathway to this instance.

Service registration enables the listener to determine whether a database service and its service handlers are available. A service handler is a dedicated server process or dispatcher that acts as a connection point to a database. During registration, the PMON process provides the listener with the instance name, database service names, and the type and addresses of service handlers. This information enables the listener to start a service handler when a client request arrives.

A service name is a logical representation of a service used for client connections.

When a client connects to a listener, it requests a connection to a service.Thus, the listener acts as a mediator between the client and instances and routes the connection request to the right place.

To ensure service registration works properly,  the initialization parameter file should contain the following parameters:

  • LOCAL_LISTENER for the local listener
  • REMOTE_LISTENER for the remote listener

LOCAL_LISTENER: To have PMON register with a local listener in RAC env we must configure the LOCAL_LISTENER parameter in the initialization parameter file to locate the local listener. Multiple addresses are supported, but connect-time failover and client load balancing features are not supported.

REMOTE_LISTENER: A remote listener is a listener residing on one computer that redirects connections to a database instance on another computer. In a dedicated server RAC environment, we must enable the PMON background process to register with a remote listener. You do this by configuring the REMOTE_LISTENER parameter.

Each network must have a dedicated LISTENER.  It would be necessary to configure multiple addresses (Listener) in the parameter  LOCAL_LISTENER from all instances  to serve NET1 and NET2. Multiple addresses are supported, but connect-time failover and client load balancing features are not supported. (first problem) 😦

If we use  only parameter LOCAL_LISTENER and REMOTE_LISTENER the connections can be redirected by REMOTE_LISTENER to LOCAL_LISTENER  from others network. (second problem) 😦 😦

To ensure that connections to the remote listener are only redirected to the local listener on the same network Oracle create new Parameter LISTENER_NETWORKS :-), this parameter is avaliable only in version 11g R2.

  • LISTENER_NETWORKS specifies one or more sets of local & remote listeners for cross-registration. All listeners within the same network_name will cross-register.

Using parameter LISTENER_NETWORKS we can configure multipes LOCAL & REMOTE Listener in your own network.  It ensure wich connections incoming by network net1 will be redirected over RAC Database only on network net1.

This tasks will be done in 2 steps:

1. Create New Network and Listener in Oracle Clusterware

2. Configure RAC to support multipe Network and Create Service

Creating  New Network and Listener in Oracle Clusterware

The Clusterware must be configured and online in all nodes.

Overview of Environment

  1. First Public network is called North.
  2. Second Public network is called South.
  3. Third Network is called East.
Infrastructure:

Servers Hostname
1° northora01
2° northora02
3° northora03

North - eth0 - 192.168.217.0
# Public Hostnames/IP North
northora01 - 192.168.217.60
northora02 - 192.168.217.45
northora03 - 192.168.217.68

# Virtual Hostnames/IP North
northora01-vip - 192.168.217.52
northora02-vip - 192.168.217.53
northora02-vip - 192.168.217.69

South - eth2 - 140.120.120.0
# Public Hostnames/IP South
southora01 - 140.120.120.200
southora02 - 140.120.120.201
southora03 - 140.120.120.202
# Virtual Hostnames/IP South
southora01-vip - 140.120.120.100
southora02-vip - 140.120.120.101
southora03-vip - 140.120.120.102

East - eth3 - 154.120.120.0
# Public Hostnames/IP East
eastora01 - 154.120.120.200
eastora02 - 154.120.120.201
eastora03 - 154.120.120.202
# Virtual Hostnames/IP East
eastora01-vip - 154.120.120.100
eastora02-vip - 154.120.120.101
eastora03-vip - 154.120.120.102

Before start the New Interfaces (eth2 and eth3) must be configured with IP,Mask and Gateway.

After setting up the new interfaces you must set VIP hostnames from new networks in hosts files (e.g /etc/hosts) from all nodes and add VIP-hostnames in your DNS.

# /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
# Public North
192.168.217.60          northora01.north.com		northora01
192.168.217.45          northora02.north.com		northora02
192.168.217.68          northora03.north.com		northora03

# VIP North
192.168.217.52          northora01-vip.north.com		northora01-vip
192.168.217.53          northora02-vip.north.com		northora02-vip
192.168.217.69          northora03-vip.north.com		northora03-vip

# Public South
140.120.120.200         southora01.south.com            southora01
140.120.120.201         southora02.south.com            southora02
140.120.120.202         southora03.south.com            southora03

# VIP South
140.120.120.100         southora01-vip.south.com	southora01-vip
140.120.120.101         southora02-vip.south.com	southora02-vip
140.120.120.102        	southora03-vip.south.com	southora03-vip

# Public East
154.120.120.200		eastora01.east.com		eastora01
154.120.120.201		eastora02.east.com		eastora02
154.120.120.202		eastora03.east.com		eastora03

# Vip East
154.120.120.100		eastora01-vip.east.com		eastora01-vip
154.120.120.101		eastora02-vip.east.com		eastora02-vip
154.120.120.102		eastora03-vip.east.com		eastora03-vip


Ensure Public and VIP address is defined for the 2nd and 3nd public network

Creating the CRS resource (network/vip) using srvctl

A new network and new vip resources for the new network are created by using the ‘-k’ switch.
The-k option indicates to which network it is. The public network has automatically installed the first number.

Using Oracle Grid Infrastucture 11.2.0.1 you can’t create network resource explicitly, when we create a new vip resource the network resource will be created implicitly and a dependency between resources (vip and network) will be configured.

Retrieving information about the environment

# Actual Network
# Network Info
crsctl status resource -f |grep NAME=ora.net -A 3
NAME=ora.net1.network
TYPE=ora.network.type
STATE=ONLINE
TARGET=ONLINE

# VIP Info
crsctl status resource -f |grep NAME=ora.northora -A 3 |grep .vip -A 3
NAME=ora.northora01.vip
TYPE=ora.cluster_vip_net1.type
STATE=ONLINE
TARGET=ONLINE
--
NAME=ora.northora02.vip
TYPE=ora.cluster_vip_net1.type
STATE=ONLINE
TARGET=ONLINE
--
NAME=ora.northora03.vip
TYPE=ora.cluster_vip_net1.type
STATE=ONLINE
TARGET=ONLINE

# Getting Nodes Information
olsnodes -n -s
northora01	1       Active
northora02  	2       Active
northora03   	3       Active

Adding Network/VIP Resources using Clusterware 11.2.0.1

as root user:

# srvctl add vip -n  -k  -A //[if1[|if2...]] [-v]

srvctl add vip -n northora01 -A southora01-vip/255.255.255.0/eth2 -k 2
srvctl add vip -n northora02 -A southora02-vip/255.255.255.0/eth2 -k 2
srvctl add vip -n northora03 -A southora03-vip/255.255.255.0/eth2 -k 2

srvctl start vip -i southora01-vip
srvctl start vip -i southora02-vip
srvctl start vip -i southora03-vip

# Checking Status Network 2
crsctl status resource -f |grep NAME=ora.net2 -A 3
NAME=ora.net2.network
TYPE=ora.network.type
STATE=ONLINE
TARGET=ONLINE

crsctl status resource -f |grep NAME=ora.south -A 3 |grep .vip -A 3
NAME=ora.southora01-vip.vip
TYPE=ora.cluster_vip_net2.type
STATE=ONLINE
TARGET=ONLINE
--
NAME=ora.ora.southora02-vip.vip.vip
TYPE=ora.cluster_vip_net2.type
STATE=ONLINE
TARGET=ONLINE
--
NAME=ora.ora.southora03-vip.vip.vip
TYPE=ora.cluster_vip_net2.type
STATE=ONLINE
TARGET=ONLINE

From 11.2.0.2+, network resource can be created explicitly:

as root user:
# srvctl add network [-k ] -S //[if1[|if2...]] [-w ] [-v]

srvctl add network -k 3 -S 154.120.120.0/255.255.255.0/eth3

crsctl status resource -f |grep NAME=ora.net3 -A 3
NAME=ora.net3.network
TYPE=ora.network.type
STATE=OFFLINE
TARGET=OFFLINE

Then add vip resource for the 3nd network:
srvctl add vip -n northora01 -A eastora01-vip/255.255.255.0/eth3 -k 3
srvctl add vip -n northora02 -A eastora02-vip/255.255.255.0/eth3 -k 3
srvctl add vip -n northora03 -A eastora03-vip/255.255.255.0/eth3 -k 3

And starting vip resource
srvctl start vip -i eastora01-vip
srvctl start vip -i eastora02-vip
srvctl start vip -i eastora03-vip

 crsctl status resource -f |grep NAME=ora.east -A 3 |grep .vip -A 3
NAME=ora.eastora01-vip.vip
TYPE=ora.cluster_vip_net3.type
STATE=ONLINE
TARGET=ONLINE
--
NAME=ora.eastora02-vip.vip
TYPE=ora.cluster_vip_net3.type
STATE=ONLINE
TARGET=ONLINE
--
NAME=ora.eastora03-vip.vip
TYPE=ora.cluster_vip_net3.type
STATE=ONLINE
TARGET=ONLINE

Creating New Listener  for each network.

You can create new listener using srvctl, but I recommend create Listener using NETCA.
As the grid user invoke “netca” from the 11.2 GRID_HOME, the select “Cluster configuration” -> “Listener configuration” -> “Add”, enter Listener name as required.

Repeat the procedure above to create the Listener on the network South.

Validating Clusterware Configuration

Checking Listeners created

srvctl config listener -a
Name: LISTENER
Network: 1, Owner: oracle
Home:
  /u01/app/11.2.0/grid on node(s) northora01,northora02,northora03
End points: TCP:1521
Name: LISTENER_SOUTH
Network: 2, Owner: oracle
Home:
  /u01/app/11.2.0/grid on node(s) northora01,northora02,northora03
End points: TCP:1522
Name: LISTENER_EAST
Network: 3, Owner: oracle
Home:
  /u01/app/11.2.0/grid on node(s) northora01,northora02,northora03
End points: TCP:1523

srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): northora01,northora02,northora03
Listener LISTENER_EAST is enabled
Listener LISTENER_EAST is running on node(s): northora01,northora02,northora03
Listener LISTENER_SOUTH is enabled
Listener LISTENER_SOUTH is running on node(s): northora01,northora02,northora03

srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node northora01
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node northora02
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node northora03

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

The Configuration of  network and listener on Cluster-Wide is finished.

Configuring RAC Database “db11g”  to support multipe Network and Creating Service

Now we need configure Oracle RAC Database (11.2) to provide service on all networks (network 1,2 and 3).

 Prepare the database instance for the new listener

To ensure that connections to the remote listener are only redirected to the local listener on the same network, LISTENER_NETWORKS parameter needs to be set in the pfile or spfile for the database instance.

Use tnsnames.ora from Oracle Home (RAC)  to resolve listener name alias for LISTENER_NETWORKS.  All alias must be in tnsnames.ora from all nodes of RAC Database.

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_NORTH1=
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = northora01-vip.oracle.com)(PORT = 1521))
  )

LISTENER_NORTH2=
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = northora02-vip.oracle.com)(PORT = 1521))
  )

LISTENER_NORTH3=
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = northora03-vip.oracle.com)(PORT = 1521))
  )

LISTENER_SOUTH1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = southora01-vip.oracle.com)(PORT = 1522))
  )

LISTENER_SOUTH2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = southora02-vip.oracle.com)(PORT = 1522))
  )

LISTENER_SOUTH3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = southora03-vip.oracle.com)(PORT = 1522))
  )

REMOTE_SOUTH =
   (DESCRIPTION_LIST =
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = southora01-vip.oracle.com)(PORT = 1522)))
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = southora02-vip.oracle.com)(PORT = 1522)))
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = southora03-vip.oracle.com)(PORT = 1522)))
   )

LISTENER_EAST1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = eastora01-vip.east.com)(PORT = 1523))
  )

LISTENER_EAST2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = eastora02-vip.east.com)(PORT = 1523))
  )

LISTENER_EAST3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = eastora03-vip.east.com)(PORT = 1523))
  )

REMOTE_EAST =
   (DESCRIPTION_LIST =
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = eastora01-vip.east.com)(PORT = 1523)))
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = eastora02-vip.east.com)(PORT = 1523)))
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = eastora03-vip.east.com)(PORT = 1523)))
   )

Don’t forget configure sqlnet.ora.

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /u01/app/oracle

Testing connectivity of all entries in TNSNAMES.ora using utility tnsping

This step is very important, all of listener alias name must be validated in all nodes.

e.g

tnsping LISTENER_SOUTH1

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 18-OCT-2011 17:31:12

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

Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = southora1-vip.oracle.com)(PORT = 1522)))
OK (0 msec)

# Testing REMOTE

tnsping REMOTE_EAST

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 18-OCT-2011 17:31:55

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

Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = eastora01-vip.east.com)(PORT = 1523))) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = eastora02-vip.east.com)(PORT = 1523))) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = eastora03-vip.east.com)(PORT = 1523))))
OK (0 msec)

Configuring Oracle RAC to support multiples network

Please, don’t change  current parameter of LOCAL_LISTENER and REMOTE_LISTENER of your spfile/pfile.

Setting  LISTENER_NETWORKS is enough to RAC to work properly.

Recommendation : Set parameter using scope=both, it’s will validade if the all Listener alias is working. If you use scope=spfile, it will not validaded and if not was configured properly the error will be raised at moment of startup of database instance.

####### Instance 1 #############
ALTER SYSTEM
 SET LISTENER_NETWORKS = '((NAME=NORTH_NET)(LOCAL_LISTENER=LISTENER_NORTH1)(REMOTE_LISTENER=global-scan.north.com:1521))',
                         '((NAME=SOUTH_NET)(LOCAL_LISTENER=LISTENER_SOUTH1)(REMOTE_LISTENER=REMOTE_SOUTH))',
                         '((NAME=EAST_NET)(LOCAL_LISTENER=LISTENER_EAST1)(REMOTE_LISTENER=REMOTE_EAST))'
 SCOPE=BOTH SID='db11g1';
####### Instance 2 #############
System altered.

ALTER SYSTEM
 SET LISTENER_NETWORKS='((NAME=NORTH_NET)(LOCAL_LISTENER=LISTENER_NORTH2)(REMOTE_LISTENER=global-scan.north.com:1521))',
                       '((NAME=SOUTH_NET)(LOCAL_LISTENER=LISTENER_SOUTH2)(REMOTE_LISTENER=REMOTE_SOUTH))',
                       '((NAME=EAST_NET)(LOCAL_LISTENER=LISTENER_EAST2)(REMOTE_LISTENER=REMOTE_EAST))'
SCOPE=BOTH SID='db11g2';

System altered.

####### Instance 3 #############
ALTER SYSTEM
 SET LISTENER_NETWORKS='((NAME=NORTH_NET)(LOCAL_LISTENER=LISTENER_NORTH3)(REMOTE_LISTENER=global-scan.north.com:1521))',
                       '((NAME=SOUTH_NET)(LOCAL_LISTENER=LISTENER_SOUTH3)(REMOTE_LISTENER=REMOTE_SOUTH))',
                       '((NAME=EAST_NET)(LOCAL_LISTENER=LISTENER_EAST3)(REMOTE_LISTENER=REMOTE_EAST))'
SCOPE=BOTH SID='db11g3';

System altered.

Checking if default database service db11g was registered proprely

#################### Network 1 #####################

lsnrctl status LISTENER
..
.
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.217.52)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.217.60)(PORT=1521)))
Services Summary...
..
.
Service "db11g" has 1 instance(s).
  Instance "db11g2", status READY, has 1 handler(s) for this service...
..
.
The command completed successfully

# In network 1 the remote_listener is registered in Listener SCAN
lsnrctl status LISTENER_SCAN3
..
.
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN3)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.217.62)(PORT=1521)))
Services Summary...
..
.
Service "db11g" has 3 instance(s).
  Instance "db11g1", status READY, has 2 handler(s) for this service...
  Instance "db11g2", status READY, has 2 handler(s) for this service...
  Instance "db11g3", status READY, has 2 handler(s) for this service...
..
.
The command completed successfully

#################### Network 2 #####################

lsnrctl status LISTENER_SOUTH
..
.
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SOUTH)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=140.120.120.101)(PORT=1522)))
Services Summary...
..
.
Service "db11g" has 3 instance(s).
  Instance "db11g1", status READY, has 1 handler(s) for this service...
  Instance "db11g2", status READY, has 2 handler(s) for this service...
  Instance "db11g3", status READY, has 1 handler(s) for this service...
The command completed successfully

#################### Network 2 #####################

lsnrctl status LISTENER_EAST
..
.
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_EAST)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=154.120.120.101)(PORT=1523)))
Services Summary...
..
.
Service "db11g" has 3 instance(s).
  Instance "db11g1", status READY, has 1 handler(s) for this service...
  Instance "db11g2", status READY, has 2 handler(s) for this service...
  Instance "db11g3", status READY, has 1 handler(s) for this service...
The command completed successfully

Creating Service “db11g_inbound”

Using “srvctl add service” with option -k it’s possible create a dependency specifying the number of network. However, this has no effect on listeners which is registered with the service, the service will be registered in all listener configured in RAC (LOCAL,REMOTE and NETWORK Listener). It rather serves only to ensure that the service is started, when the corresponding subnet, or the corresponding ora.netX.network resource is present and started.
Bad news, each service can be configure with  dependency  to only one network. We can not specify multiple networks numbers.

I created a workaround to fix it. (It’s not supported by Oracle)

Creating new Service DB11G_INBOUND

In example below a dependency will be created with ora.net2.network

srvctl add service -s db11g_inbound -m BASIC -e SELECT -d db11g -k 2 -r "db11g1,db11g2,db11g3"

Solution not supported by Oracle. Creating dependency of resource manually.

# Create service without specifying flag "-k" srvctl will use default network 1
srvctl add service -s db11g_inbound -m BASIC -e SELECT -d db11g  -r "db11g1,db11g2,db11g3"

# Getting current START and STOP Dependencies.

crsctl status resource ora.db11g.db11g_inbound.svc -f |grep -E 'START_DEPENDENCIES|STOP_DEPENDENCIES'
START_DEPENDENCIES=
hard(ora.db11g.db,type:ora.cluster_vip_net1.type)
weak(type:ora.listener.type)
pullup(type:ora.cluster_vip_net1.type)
pullup:always(ora.db11g.db)

STOP_DEPENDENCIES=hard(intermediate:ora.db11g.db,intermediate:type:ora.cluster_vip_net1.type)

# Modifying START Dependencies adding type cluster_vip_net (cluster_vip_net2 and cluster_vip_net3)
crsctl modify resource ora.db11g.db11g_inbound.svc -attr\
 "START_DEPENDENCIES='hard(\
ora.db11g.db,\
type:ora.cluster_vip_net1.type,\
type:ora.cluster_vip_net2.type,\
type:ora.cluster_vip_net3.type)\
weak(type:ora.listener.type) \
pullup(type:ora.cluster_vip_net1.type) \
pullup(type:ora.cluster_vip_net2.type) \
pullup(type:ora.cluster_vip_net3.type) \
pullup:always(ora.db11g.db)'"

# Modifying STOP Dependencies adding type cluster_vip_net (cluster_vip_net2 and cluster_vip_net3)
crsctl modify resource ora.db11g.db11g_inbound.svc -attr\
"STOP_DEPENDENCIES='hard(\
intermediate:ora.db11g.db,\
intermediate:type:ora.cluster_vip_net1.type,\
intermediate:type:ora.cluster_vip_net2.type,\
intermediate:type:ora.cluster_vip_net3.type)'"

# P.S The service db11g_inbound will not start if network (VIP IP) 1,2 and 3 is not avaliable, and you cannot stop network 1,2 and 3 if sevice db11g_inbound is online.
# Starting Service Created
srvctl start service -d db11g -s db11g_inbound

You must understand your environment and see if  is better to create a service for each network or create a service to all networks.
Remember that the service created always will be available in all networks (listeners), but in network maintenance may affect services if you manually created dependency.
Checking if Service was registered in all Listeners


############### Network 1 #########################
 lsnrctl status listener_scan1
..
.
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.217.63)(PORT=1521)))
..
.
Service "db11g_inbound" has 3 instance(s).
  Instance "db11g1", status READY, has 1 handler(s) for this service...
  Instance "db11g2", status READY, has 1 handler(s) for this service...
  Instance "db11g3", status READY, has 1 handler(s) for this service...
..
.

 lsnrctl status listener
..
.
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.217.60)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.217.52)(PORT=1521)))
..
.
Service "db11g_inbound" has 1 instance(s).
  Instance "db11g2", status READY, has 2 handler(s) for this service...
..
.
############### Network 2 #########################
lsnrctl status listener_south
..
.
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SOUTH)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=140.120.120.101)(PORT=1522)))
..
.
Service "db11g_inbound" has 3 instance(s).
  Instance "db11g1", status READY, has 1 handler(s) for this service...
  Instance "db11g2", status READY, has 2 handler(s) for this service...
  Instance "db11g3", status READY, has 1 handler(s) for this service...
..
.
############### Network 3 #########################
lsnrctl status listener_east
..
.
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_EAST)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=154.120.120.101)(PORT=1523)))
..
.
Service "db11g_inbound" has 3 instance(s).
  Instance "db11g1", status READY, has 1 handler(s) for this service...
  Instance "db11g2", status READY, has 2 handler(s) for this service...
  Instance "db11g3", status READY, has 1 handler(s) for this service...
..
.

Use the connect string above to connect  your clients on RAC Database 11.2


########### Connection String to Network 1 #####################
### Using SCAN ###########
db11g_inbound =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = global-scan.north.com)(PORT = 1521))
  (LOAD_BALANCE = YES)
  (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = db11g_inbound)
   (FAILOVER_MODE =
   (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5)
   )
  )

### Or Using VIP ###########
db11g_inbound =
 (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = northora01-vip.north.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = northora02-vip.north.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = northora03-vip.north.com)(PORT = 1521))
    )
  (LOAD_BALANCE = YES)
  (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = db11g_inbound)
   (FAILOVER_MODE =
   (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5)
   )
  )

########### Connection String to Network 2 #####################
db11g_inbound =
 (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = southora01-vip.north.com)(PORT = 1522))
      (ADDRESS = (PROTOCOL = TCP)(HOST = southora02-vip.north.com)(PORT = 1522))
      (ADDRESS = (PROTOCOL = TCP)(HOST = southora03-vip.north.com)(PORT = 1522))
    )
  (LOAD_BALANCE = YES)
  (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = db11g_inbound)
   (FAILOVER_MODE =
   (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5)
   )
  )

########### Connection String to Network 3 #####################
db11g_inbound =
 (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = eastora01-vip.north.com)(PORT = 1523))
      (ADDRESS = (PROTOCOL = TCP)(HOST = eastora02-vip.north.com)(PORT = 1523))
      (ADDRESS = (PROTOCOL = TCP)(HOST = eastora03-vip.north.com)(PORT = 1523))
    )
  (LOAD_BALANCE = YES)
  (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = db11g_inbound)
   (FAILOVER_MODE =
   (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5)
   )
  )

Useful docs about how configure multiple network an RAC env.

Data Guard: Redo Transport Services – How to use a separate network in a RAC environment. [ID 1210153.1] (11.1 or above)

How to Configure A Second Listener on a Separate Network in 11.2 Grid Infrastructure [ID 1063571.1] (11.2)

Data Guard Physical Standby 11.2 RAC Primary to RAC Standby using a second network [ID 1349977.1] (11.2)

Hope this helps.

Enjoy


Configuring Client to use SCAN 11.2.0

Server Side

Check the settings of Clusterware/Network/Database on Server Side .

Nodes information:

$ olsnodes -i  -s -n
node1        1       node1-vip    Active
node2        2       node2-vip    Active

Checking IP configured to VIP:

$ srvctl config vip -n node1
VIP exists.:node1
VIP exists.: /node1-vip/192.168.217.52/255.255.255.0/eth0

$ srvctl config vip -n node2
VIP exists.:node2
VIP exists.: /node2-vip/192.168.217.53/255.255.255.0/eth0

Checking SCAN configuration:

$ srvctl config scan
SCAN name: node-scan.oracle.com, Network: 1/192.168.217.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /node-scan.oracle.com/192.168.217.61
SCAN VIP name: scan2, IP: /node-scan.oracle.com/192.168.217.62

Checking configuration on Database:

SQL> select INST_ID, NAME, VALUE
       from gv$parameter
       where name like '%_listener%';

INST_ID NAME                 VALUE
------- -------------------- ----------------------------------------------------------------------
      1 local_listener       (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip)(PORT=1521))))
      1 remote_listener      node-scan.oracle.com:1521

      2 local_listener       (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip)(PORT=1521))))
      2 remote_listener      node-scan.oracle.com:1521

Checking on all nodes if Service (ORCL) is registered on Both LISTENERS (SCAN/LOCAL)

Node1

Checking SCAN ( Observer that the connection will be redirected to the VIP hostname registered in the Listener SCAN (i.e node1-vip or node2-vip)


$ lsnrctl service listener_scan1

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 03-MAY-2011 17:43:08

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
Services Summary...
Service "orcl" has 2 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip)(PORT=1521)))
  Instance "orcl2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip)(PORT=1521)))
The command completed successfully

Checking Service on LOCAL Listener

$ lsnrctl service

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 03-MAY-2011 17:50:25

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
Services Summary...
...
Service "orcl" has 1 instance(s).
  Instance "orcl2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
...
The command completed successfully

Node2

Checking if Service (ORCL) is registered SCAN ( Observer that the connection will be redirected to the VIP hostname registered in the Listener SCAN (i.e node1-vip or node2-vip)


$ lsnrctl service listener_scan2

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 03-MAY-2011 17:54:13

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))
Services Summary...
Service "orcl" has 2 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2 refused:0 state:ready
         REMOTE SERVER
         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip)(PORT=1521)))
  Instance "orcl2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2 refused:0 state:ready
         REMOTE SERVER
         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip)(PORT=1521)))
The command completed successfully

Checking Service on LOCAL Listener

$ lsnrctl service

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 03-MAY-2011 17:56:23

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
Services Summary...
...
Service "orcl" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
...
The command completed successfully

Everthing is ok.

Client Side

Check the settings of Network/Oracle on Client Side:

Check if SCAN is resolved by DNS.

C:\oracle>nslookup node-scan
Server:  node-dns.oracle.com
Address:  192.168.217.6

Name:    node-scan.oracle.com
Addresses:  192.168.217.62
                 192.168.217.61

Testing the reachability of hosts (SCAN, VIP):
Do not test reachability of hosts using IP (e.g ping ) , the hostname MUST be resolved. Oracle will use hostname configured on LOCAL_LISTENER parameter to establish connection.

If you do not have a DNS configured you MUST set in HOST file from all client: All hostname of vip and hostname of scan.

C:\oracle>ping node-scan

Pinging node-scan.oracle.com [192.168.217.62] with 32 bytes of data:

Reply from 192.168.217.62: bytes=32 time=1ms TTL=64

Ping statistics for 192.168.217.62:
    Packets: Sent = 1, Received = 1, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 1ms, Average = 0ms

C:\oracle>ping node1-vip

Pinging node1-vip.oracle.com [192.168.217.52] with 32 bytes of data:

Reply from 192.168.217.52: bytes=32 time=1ms TTL=64

Ping statistics for 192.168.217.52:
    Packets: Sent = 1, Received = 1, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 1ms, Average = 0ms

C:\oracle>ping node2-vip

Pinging node2-vip.oracle.com [192.168.217.53] with 32 bytes of data:

Reply from 192.168.217.53: bytes=32 time=1ms TTL=64

Ping statistics for 192.168.217.53:
    Packets: Sent = 1, Received = 1, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 1ms, Average = 0ms

Checking Ports is opened:

C:\oracle> telnet node-scan 1521
C:\oracle> telnet node1-vip  1521
C:\oracle> telnet node2-vip  1521

# To exit the Telnet after connection press [enter] multiple times.

e.g Port Closed
C:\oracle>telnet node-scan 1521
Connecting To node...Could not open connection to the host, on port 1521: Connect failed
Contact your sysadmin to check network problem (firewall,etc...) .

Check configuration on SQLNET.ora.
Configure TNSNAMES and EZCONNECT on DIRECTORY_PATH.

%ORACLE_HOME%/network/admin/sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Now test connect on DATABASE using Easy Connect, starting with VIP:

C:\oracle>sqlplus system/oracle@node1-vip:1521/ORCL

SQL*Plus: Release 11.2.0.1.0 Production on Ter Mai 3 16:40:50 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL>exit

C:\oracle>sqlplus system/oracle@node2-vip:1521/ORCL

SQL*Plus: Release 11.2.0.1.0 Production on Ter Mai 3 16:40:50 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL>exit

C:\oracle>sqlplus system/oracle@node-scan:1521/ORCL

SQL*Plus: Release 11.2.0.1.0 Production on Ter Mai 3 16:40:50 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL>exit

Configure the Service Name on TNSNAMES.ora:

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node-scan.oracle.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )
C:\oracle>sqlplus system/oracle@ORCL

SQL*Plus: Release 11.2.0.1.0 Production on Ter Mai 3 16:46:51 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL >

SCAN-11.2.0 – What You Need to Know

I saw that many problems with feature Oracle SCAN occur due to improper configuration of the environment because of to lack of knowledge on the issue.

Here I’ll post what you should be aware that things work well.

If I ask you to read the concept, maybe you’re not willing to read … then I’ll post the main questions that give us an overview.

What’s SCAN?
Single client access name (SCAN) is the virtual hostname to provide for all clients connecting to the cluster (as opposed to the vip hostnames in 10g and 11gR1).
The feature SCAN is a new “layer” network (oracle) with high availability that allows you to change the characteristics of your cluster (i.e add/remove nodes) without the need to make configuration changes in their clients “Grid concept”.
The IP SCAN and VIP have different goals, therefore working together.
The VIP IP provides high availability at the “tier” of the Cluster.
The SCAN IP provides high availability at the “tier” of the Clients.
No matter if we are adding or removing nodes in a cluster or if the nodes are active or inactive, SCAN IP is always available for clients (Managing Connections-Failover) since one node of cluster is active.
How does SCAN work?
“When a client submits a request, the SCAN listener listening on a SCAN IP address and the SCAN port is contracted on a client’s behalf. Because all services on the cluster are registered with the SCAN listener, the SCAN listener replies with the address of the local listener on the least-loaded node (Each scan listener keeps updated cluster load statistics) where the service is currently being offered. Finally, the client establishes connection to the service through the listener on the node where service is offered.All of these actions take place transparently to the client without any explicit configuration required in the client.”
Requirements to set up SCAN?

SCAN is a domain name registered to at least one and up to three IP addresses, either in the domain name service (DNS) or the Grid Naming Service (GNS), only DNS can resolve more than one IP addresses on the same hostname. That’s possible because only DNS have feature Round-Robim.  

SCAN VIP addresses must be on the same subnet as virtual IP addresses and public IP addresses.

By default, the name used as the SCAN is also the name of the cluster and must be globally unique throughout your enterprise. SCAN name must be at least one character long and no more than 15 characters in length, must be alphanumeric – cannot begin with a numeral and may contain hyphens (-).

Can I use HOSTS files to resolve SCAN?
Technically works because Oracle requires that this virtual hostname be resolved and using HOSTS this is possible.
It’s very important…
Use host file ONLY if you are creating a test environment or if will not use the SCAN feature. My recommendation is: NEVER use the HOSTS file to resolve SCAN in a production environment. This will cause a lot issues.
Oracle strongly recommends that you do not configure SCAN VIP addresses in the hosts file. But if you use the hosts file to resolve SCAN name, you can have only one SCAN IP address.
The HOSTS file does not resolve more than one entry with the same hostname, only the first entry is resolved.
 
See this example:
http://forums.oracle.com/forums/message.jspa?messageID=8392421#8392421
Requirements to set up clients to use SCAN?
It is highly recommended you have a DNS.
You should always use the hostname (e.g node1-vip) never use IP (e.g 192.168.0.5).
This is necessary because Oracle try connect using hostname (not IP) configured on LOCAL/REMOTE Listener on Database.
The client MUST resolve and access the following  hostname and ports:
 
  • The Virtual hostname of SCAN  (e.g  host-scan)
  • Port of Listener SCAN  must be open.
  • ALL hostname from Virtual IP of clusterware (e.g node1-vip,node2-vip and so on). This will be necessary because the conection is estabilished by VIP not by SCANIP.
  • Port of Local Listener must be open.
 
e.g  Check connection on Client 
nslookup host-scan
ping -c node1-vip
ping -c node2-vip
ping -c host-scan
tnsping node1-vip
tnsping node2-vip
tnsping host-scan
#  First test througth VIP
sqlplus scott/tiger@node1-vip:1521/testsvc
sqlplus scott/tiger@node2-vip:1521/testsvc
 # So test througth SCAN
sqlplus scott/tiger@host-scan:1521/testsvc
 
Why not setup only one SCANIP to SCAN?

Sebastian Wrote:

if you only use one, then you only have 1 SCAN VIP and 1 SCAN listener. One scan vip and listener can only be active on one node at a time, and will failover to another.
So there are some things to know
1.) What happens, if you only have one listener instead of 3, regarding connections:
Connections will not be distributed on multiple SCAN listener, but always end up on the same. So be carefull, that this is not a bottleneck.
Connection storms on 1 listener is simply more likely to happen, than if you have 3. But this can be avoided, in setting up the clients in a manner they don’t open 100rds of connections at the same time – and with some listener.ora parameters.
2.) What happens, if the listener is not available (because it’s in the process of failing over):
With 3 scans, the client will simply switch to the next one and get a connection. With only 1 scan, the client will get an error (No listener).
So for the time of the failover with only 1 scan, there will be unsuccessful connection attempts. However relocation of the SCAN VIP and listener is normally quite fast…
However the listener registration by the database (from PMON) is only done every 60 seconds. So if listener is up (after failover), but no service registered (yet), the clients mayl recieve an error (No service).
3.) What happens, if for any reason this one scan listener fails and cannot be restarted.
While very unlikely to happen, because the clusterware tries to avoid this, this would result in a complete “inavailability” of the cluster. With 3 scans, while not good that one listener isn’t running, it does not matter.
 
http://forums.oracle.com/forums/message.jspa?messageID=9554024#9554024
 
 Why not use VIP or Public ?
Because for us to have the concept of GRID we must configure the feature only one time and it will self manage and make cluster transparent to yours clients.
If you add or remove an NODE it’s require to modify the connections of yours all clients (VIP/PUBLIC IP) you broke the concept of GRID. (This makes a big difference when you have many clients i.e.: Over 20 clients.)
If we remove or add node we must configure the clients to use/or not this node. Using SCAN he do this automatically.
Do we still need to configure local listeners on each node?
Yes, you would need to configure independent local listeners for each node.  SCAN listeners are not replacements for the node listeners.
A new set of cluster processes called scan listeners will run on three nodes in a cluster (or all nodes if there are less than 3).  If you have more than three nodes, regardless of the number of nodes you have, there will be at most three scan listeners.  The database registers with the SCAN listener through the remote listener parameter in the init.ora/spfile.  If any of these clustered processes fail, they are automatically restarted on a new node.
 
Can we use the previous method (Using VIP) for client connection?
Clients can continue to access the cluster in the same way as with previous releases. Vips are still used internally, and can still be used for connections. But Oracle strongly recommends that clients accessing the cluster use the SCAN. Clients using the SCAN can also access the cluster using EZCONNECT.
 
Is it mandatory to use SCAN?
It’s highly recommended to use SCAN unless there’s strong business reason preventing it from being used.
 
Is it supported to remove SCAN?
SCAN is an elementary part of 11gR2 Grid Infrastructure, it’s not supported to remove SCAN.
 
 


Use a Single Client Access Name (SCAN) to connect to a RAC database

This Demostratation shows how to use a Single Client Access Name (SCAN) to connect to a RAC database.

See link below

http://st-curriculum.oracle.com/demos/db/11g/r2/grid_rac/11_rac_scan/rac_scan.swf

Enjoy


SINGLE CLIENT ACCESS NAME (SCAN) by Barb Lundhild, Markus Michalewicz

The best paper about Oracle SCAN writing until today.

SINGLE CLIENT ACCESS NAME (SCAN) by Barb Lundhild, Markus Michalewicz

Enjoy


DUPLICATE Without Connection to Target Database – 11g R2

DUPLICATE can be performed without connecting to a target database. This requires connecting to a catalog and auxiliary database.

The benefit is improved availability of a DUPLICATE operation by not requiring connection to a target database. This is particularly useful for DUPLICATE to a destination database where connection to the target database may not be available at all times.

http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642/rcmdupdb.htm#BRADV416