-- 10gR2 & SCAN IPs --

There has been some controversy with 10gR2 about the use of SCAN IPs, I have seen a lot of confusion about it which motivated me to write this article. Let's refresh our memory a little bit with the basis.

• 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).  
• 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).
• The real reason behind SCAN is that because the SCAN is associated with the cluster as a whole, rather than to a particular node, the SCAN makes it possible to add or remove nodes from the cluster without needing to reconfigure clients. It also adds location independence for the databases, so that client configuration does not have to depend on which nodes are running a particular database.
• Grid Infrastructure will start local listener LISTENER on all nodes to listen on local VIP, and SCAN listener LISTENER_SCAN1 (up to three cluster wide) to listen on SCAN VIP(s); 11gR2 database by default will set local_listener to local LISTENER, and remote_listener to SCAN listener.
• SCAN listener will be running off GRID_HOME, and by default, in 11gR2 local listener will be running off GRID_HOME as well.

With the SCAN the client sends a request to the SCAN listener, all services in the cluster are registered with the SCAN Listener, the SCAN Listener sends back to the client the address of the local listener on the least loaded node where the service is currently been offered, then the client establishes the connection to the service through the local listener on the node where the service is been offered. 

While 10g/11gR1 SQL*Net does not support certain aspects of this new feature, it is still possible to configure 10g/11gR1 clients and database instances to integrate with 11g R2 SCAN.  In order to make it work you won't will need to have the SCAN VIPs fix, this will not work in the case where the VIP are dynamically allocated via GNS.

 The problem arises because client and database instances with versions < 11g R2 do not support multiple IP addresses associated with the same host name, therefore you can't use the 11gR2 syntax for the remote_listener or the client connect string.  


You still need the SCAN VIPs, SCAN can have between 1 to 3 IPs associated with it, I always recommend 3 because 3 is the maximum number of VIP you will need regardless of the number of nodes in your cluster. It is ok to allocate 2 in the case of you having only 2 nodes as long as you are aware than when you add more nodes you will need an to reconfigure and an additional VIP on the same sub-net as the previous SCAN IPs.   


Please note that as you create the 'local_listener' and remote_listener instance parameters, local_listener should use the node VIP, while the alias for the 'remote_listener' uses the SCAN VIPs same as the setup for 11gR2


LISTENER_10g1 =
         (ADDRESS = (PROTOCOL = TCP) (HOST = node1-vip) (PORT=1521))



LISTENER_10g2 =
         (ADDRESS = (PROTOCOL = TCP) (HOST = node2-vip) (PORT=1521))


LISTENER_GRID =
          (ADDRESS = (PROTOCOL = TCP) (HOST = X.X.X.1) (PORT = 1521))
         (ADDRESS = (PROTOCOL = TCP) (HOST = X.X.X.2) (PORT = 1521))
          (ADDRESS = (PROTOCOL = TCP) (HOST = X.X.X.3) (PORT = 1521))
)


NOTE: Before 11.2 the Oracle Client was configured using ADDRESS_LIST listing all the nodes VIP. When you have a client with this configuration the REMOTE_LISTENER parameter for the pre 11gR2 database needs to list every node VIP plus every SCAN VIP. 


alter system set local_listener='LISTENER_10g1' scope=spfile sid='10G1';
alter system set local_listener='LISTENER_10g2' scope=spfile sid='10G2';
alter system set remote_listener='LISTENER_GRID' scope=spfile sid='*';


Check that you have a CRS service configured in OCR for clients to connect to this database. The client connection string should be similar to.
10G_VIA_SCAN =
  (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.1)(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.2)(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.3)(PORT = 1521))
    )
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = 10G_SCAN)
    )
  )

-- Utilization of Flashback Database as a back out mechanism for Application Deployments (Best Practices) --

Performance observations

 As you have to write to the flashback logs when the database is in flashback mode, you usually have around 5% performance impact especially for OLTP transactions. The impact for large inserts (e.g. batch inserts) or direct loads operations goes from 5% to 40%, this is especially true for 10g, in 11g Oracle introduced “block-new optimization” which significantly relieves that situation.

Best Practices

1. - Set the DB_FLASHBACK_RETENTION_TARGET to a value equal to the farthest time in the past that you wish to be able to recover to.

2.- size the flashback recovery area (FRA), ensure that there is sufficient space allocated to accommodate the flashback database, the flashback logs for the target  retention size and for peak batch rates. A general rule of thumb is that flashback logs get generated approximately at the same rate as redo log.

3. - Configure sufficient I/O bandwidth for FRA, failure to do that will result in high “FLASBACK BUF FREE BY RVWR” wait event and “FLASBACK LOG WRITE” latency of more than 30ms. The easiest way to accomplish this is by placing the flashback logs in the outer portion of the disks or LUNS.

4. - Set the LOG_BUFFER to at least 8MB in order to give the flashback database more buffer space in memory. For large databases consider a range between 32-64 MB.

5. – Set _DB_FLASBACK_LOG_MIN = (11.2.0.3 and above only).

6. - Monitor closely your “Recovery area free space (%).

7. - In Oracle 11.2 onward, you can enable flashback database while the database is open.

8. - It is highly recommended that you use Guarantee Restore Point (GRP) before your deployment.

9. - Enable database flashback when needed and disable it after you are sure the deployment was successful. Please note that every single time you enable or disable flashback database a reset log operation occurs therefor you have a different incarnation of the database, once you get the database out of flashback mode there is no way to move back or forth in time.

-- Big Data - Don't miss this event --

 http://www.oracle.com/us/dm/43327-wwmk11095162mpp003c004-oem-1495099.html

-- Fundamental Oracle flaw revealed --

Very interesting InfoWorld article worth to share, please be sure you apply the latest January 2012 CPU patch. You can access the article by clicking here.

- Oracle 11g Automatic Memory Management (AMM) --

Let's start with a brief introduction to basic concepts.

PROGRAM GLOBAL AREA (PGA): is a region that contains data and control information for a server process, this is non shared memory created by the database when a server process is started and access is exclusive to the particular server process.SH
SHARED_GLOBAL_AREA (SGA) :  Is a group of memory structures that contain data and control information for the Oracle instance, it is shared by all background processes.


Oracle has made great progress to simplify memory management through the last versions of the RDBMS. In 9i they introduced PGA management, in 10g they automated the SGA management and in 11g you can manage both (PGA and SGA) with a single configuration using "Automatic Memory Management (AMM)". AMM allows you to allocate a chunk of memory which Oracle uses to manage both SGA and PGA, AMM uses 2 parameters:

MEMORY_TARGET: Amount of memory available to Oracle to use dynamically to control SGA and PGA, this is the amount of memory Oracle will allocate when you start the database to manage PGA and SGA.

MEMORY_MAX_TARGET: Maximum amount of memory that Oracle can use, when no specified the max is equal to MEMORY_TARGET.

When  AMM is in use, SGA_TARGET (Introduced in 10g) and PGA_AGGREGATE_TARGET (Introduced in 9i)  act as the minimum size setting for their respective areas. It is recommend to set this to 0 when using AMM so Oracle can take full control of it.

-- How to map disk devices to physical devices in Solaris --

You can map between the two device naming conventions using a script like the one below.

Instance Names: Instance names refer to the nth device in the system (for example, sd20).

Instance names are occasionally reported in driver error messages. You can determine the binding of an instance name to a physical name by looking at dmesg(1M) output, as in the following example.

sd9 at esp2: target 1 lun 1 sd9 is /sbus@1,f8000000/esp@0,800000/sd@1,0

Once the instance name has been assigned to a device, it remains bound to that device.

Instance numbers are encoded in a device's minor number. To keep instance numbers consistent across reboots, the system records them in the /etc/path_to_inst file.

!/usr/bin/env perl

use strict;

my @path_to_inst = qx#cat /etc/path_to_inst#;
map {s/"//g} @path_to_inst;
my ($device, $path, @instances);

for my $line (qx#ls -l /dev/dsk/*s2#) {
    ($device, $path) = (split(/\s+/, $line))[-3, -1];
    $path =~ s#.*/devices(.*):c#$1#;

    @instances =
        map {join("", (split /\s+/)[-1, -2])}
            grep {/$path/} @path_to_inst;
*emphasized text*
    for my $instance (@instances) {
        print "$device $instance\n";
    }
}

-- Oracle TAF server side not properly configured using srvctl due to bug 6886239 --

Oracle alerted us recently of a non public bug #6886239 "DBMS_SERVICE parameters are not added using srvctl add service. It affects 10g and 11gR1 databases, this is fixed in release 11.2 onwards. In Oracle RAC after setting up TAF although it does not fails and everything looks good, it does not configure correctly.

When you check the service configuration you find no values for failover method, type and retries, those values are needed for TAF to happen.

Creating a service

srvctl add service -d rac -s server_taf -r "rac1,rac2" -P BASIC

Start the service

srvctl start service -d rac -s server_taf

Checking that the services are running.

srvctl config service -d rac

ractest PREF: rac1 rac2 AVAIL:
server_taf PREF: rac1 rac2 AVAIL:

Getting the service ID value

sqlplus /nolog
Connect / as sysdba
SQL> select name,service_id from dba_services where name = 'server_taf';
NAME SERVICE_ID
---------------------------------------------------------------- ----------
server_taf 6

Checking the setup

SQL>col name format a15
col failover_method format a11 heading 'METHOD'
col failover_type format a10 heading 'TYPE'
col failover_retries format 9999999 heading 'RETRIES'
col goal format a10
col clb_goal format a8
col AQ_HA_NOTIFICATIONS format a5 heading 'AQNOT'
SQL>select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications
from dba_services where service_id = 6
NAME METHOD TYPE RETRIES GOAL CLB_GOAL AQNOT
--------------- ----------- ---------- -------- ---------- -------- -----
server_taf LONG NO

Adding the server side failover parameter to the service

Server side TAF method is BASIC. BASIC is the only value currently supported. This means that a new connection is established at failure time. It is not possible to pre-establish a backup connection. (which is to say, PRECONNECT is not supported)

SQL> execute dbms_service.modify_service (service_name => 'server_taf' -
, aq_ha_notifications => true -
, failover_method => dbms_service.failover_method_basic -
, failover_type => dbms_service.failover_type_select -
, failover_retries => 180 -
, failover_delay => 5 -
, clb_goal => dbms_service.clb_goal_long);
PL/SQL procedure successfully completed.

Check that the service can now see the values for methods, types and retries

SQL>select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications
from dba_services where service_id = 6
NAME METHOD TYPE RETRIES GOAL CLB_GOAL AQNOT
--------------- ----------- ---------- -------- ---------- -------- -----
server_taf BASIC SELECT 180 NONE LONG YES

Check that the listener has the service registered

lsnrctl services

Create a net service name. Here we have client load balancing between the two nodes.

SERVERTAF =
(DESCRIPTION =
(LOAD_BALANCE = yes)
(ADDRESS = (PROTOCOL = TCP)(HOST = dell01)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dell02)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = server_taf.za.oracle.com)

You are all set now :-