Wednesday, June 30, 2010

-- Caveats you need to be aware of when using VLANs for interconnect --

By Bryant Bisnar and Rafael Orta

A VLAN is a group of ports designated by the switch as belonging to the same broadcast domain. (That is, all ports carrying traffic for a particular subnet address would normally belong to the same VLAN.)

When using a VLAN you can group users by logical function and this helps to control bandwidth usage by allowing you to group high bandwidth users on low-traffic segments and to organize users from different LAN segments according to their need for common resources.

VLANs are typically used to reduce broadcast traffic and to increase security.
A group of network users assigned to a VLAN forms a broadcast domain that is separate from other VLANs that may be configured on a switch.

On a given switch, packets are forwarded only between ports that belong to the same VLAN.
Thus, all ports carrying traffic for a particular subnet address should be configured to the same VLAN. Cross-domain broadcast traffic in the switch is eliminated and bandwidth is saved by not allowing packets to flood out all ports.



Here are some guidelines and caveats you need to be aware of when using VLANs for interconnect.

If deploying the interconnect on a VLAN, there should be a 1:1 mapping of VLAN to non-routable subnet and the VLAN should not span multiple VLANs (tagged) or multiple switches. Deployment concerns in this environment include Spanning Tree loops when the larger IP network topology changes, Asymmetric routing that may cause packet flooding, and lack of fine grained monitoring of the VLAN/port.

Caveats you need to be aware of when deploying the interconnect on a shared switch: 
-          The interconnect has to be on a non-routable dedicated subnet mapped to a single dedicated VLAN.
-          If VLANs are 'trunked' between switches for redundancy, the interconnect VLAN can be in that VLAN trunk. However, this trunk should have enough capacity to guarantee adequate bandwidth for all trunked VLANs. Example, avoid a 1GbE link between the switches when you have 8 1GbE VLANs expected to go across that 1GbE inter-switch link.
-          If the interconnect participates in a VLAN trunk between redundant switches, the interconnect VLAN should be 'pruned' so that the interconnect VLAN traffic does not propagate into the corporate network. Interconnect traffic should remain on the switches to which the links are attached. 
-          The network engineer needs to protect the interconnect VLAN from Spanning Tree re-convergence events. Also if the switch supports it, turn off STP for the interconnect VLAN. Or, mitigate the impact of STP re-convergence events by defining a root bridge the minimizes the number of network device in a loop. Any STP re-convergence event that affects the interconnect switch must complete within 60 seconds.

-          The interconnect VLAN should not be negatively impacted by Quality of Service (QoS) definitions for any other network traffic on that shared switch. If QoS is defined at the port level (OSI layer 2) and modifies the network packet. This may introduce latency in interconnect packet processing. Any QoS advantages for the interconnect need to be well understood and tested. 

-          If you are configuring the interconnect in a dedicated VLAN on a shared switch, that switch should never experience congestion or 'over subscription' that might negatively impact the interconnect traffic.
-          The network engineer should label and document the use of the VLAN and label the ports on the switch accordingly.
-          The network engineer should monitor load on our VLAN to ensure that there is no packet loss, buffer overflow, inexplicable latency and asymmetrical traffic patterns.

Unlike other non-Oracle RAC clustered environments, our use of a cluster interconnect is non-traditional. We use the cluster interconnect, not only for network heartbeats (c.f. traditional active/standby clustering) but also for block sharing between a global cache. It is, for all intents and purposes, a latency sensitive i/o channel that requires high bandwidth, high availability and it should be secure (non-shared).

Wednesday, June 23, 2010

-- How to manage AUDIT trails automatically in 11g before it grows out-of-control overtime --

By Ken Patel


11.2 has AUDIT trail turned on for lot of SYSTEM privileges. But there is no automatic purge job defined out-of-box, not only that SYS.AUD$ is located in SYSTEM tablespace which can cause space management problem unnecessarily.

11.2 has new package DBMS_AUDIT_MGMT to manage this. one of good thing of this package, is that it allows DBA to setup automatic purge job (not to say, Oracle should have done this out-of-box with 7 days or some good interval just like AWR snapshots). this package also helps moving SYS.AUD$ to SYSAUX or any other tablespace DBA wants.


I think, DBA should manage this as soon as 11.2 Database is setup. Purging takes time if done after few weeks. I would do atleast this much after deploying 11.2 database for controlling audit trails ( Documentation has more details on various other package calls as well as different argument and their significance).


1.

-- this block initializes the clean up as well as moves SYS.AUD$ to SYSAUX tablespace


BEGIN

DBMS_AUDIT_MGMT.INIT_CLEANUP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,

DEFAULT_CLEANUP_INTERVAL => 12 );

END;

/


2.

-- manual clean up

BEGIN

DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(

AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,

USE_LAST_ARCH_TIMESTAMP => TRUE );

END;

/


2.

-- creating automatic purge job


BEGIN

DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (

AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,

AUDIT_TRAIL_PURGE_INTERVAL => 12,

AUDIT_TRAIL_PURGE_NAME => 'Sys_Audit_Purge_JOB',

USE_LAST_ARCH_TIMESTAMP => TRUE );

END;


Ken is a Senior Database Engineer at NETO DBE&O Solutions Engineering Team.

Thursday, June 17, 2010

-- SCAN --

By Brian Focht, Krishna Bhavaraju and Rafael Orta

What is the SCAN benefit? The clients using SCAN do not need to change if you add or remove nodes in the cluster. Having a single name to access the cluster allows clients to use the EZConnect client and the simple JDBC thin URL to access any database running in the clusters independently of which server(s) in the cluster the database is active. SCAN provides load balancing and failover of client connections to the database.


SCAN and Load Balance:

SCAN does not force load balancing at the database level. The service does not need to be running running on a node in order to failover. To simplify, essentially, the "Preferred" instance will register with the SCAN listener. So if I have a service called service1 and I want it to run on node1 and failover to node2, the service will be configured as node1 being preferred and node2 being available. The service will register with the local listeners and the SCAN listeners as such. So when you look at the scan listener, you will see service "Service1" has 1 instance on node1. So all traffic will be directed to that node. If that node fails, RAC will transfer the service to the next "available" node.


How it works?

e.g.
Consider a four (4) node cluster with a database XYZ and instances INST1, INST2,INST3 and INST4. You can define a service SERV1 with preferred (INST 1/2) and available (INST 3 / 4). Another SERV2 , preferred (INST 3/4) and available (INST 1 / 2). In this case the SERVICES – SERV1, SERV2 is registered with listener, not instances.

Case 1 - All node are up - TNS connections with SERVICE_NAME= SERV1 always go to INST1/2 and SERV2 go to INST3/4 even though all the instances are up.

Case 2 - In case INST1/2 are down, (CRS will handle the failover of the service and register the service on the new instance. It works the same way in 10g RAC) then SERV1 handlers points to INST3/4 (SERV1 - available).

Case 3- In case INST3/4 are down, (CRS will handle the failover of the service and register the service on the new instance. It works the same way in 10g RAC) then SERV2 handlers points to INST1/1 (SERV2 - available).

Services SERV1, SERV2 are always available as long as at least one node survives.

Services are defined to resource optimization like CPU, Memory etc, and restricting to specific node(s). Also serves the failover between primary and replication environments like DG, Streams etc. too.


Here is a live example: service br01 and it currently only runs on pwbrpr2 (Node2) of a 2 node 11gr2 RAC. It will failover to pwbrpr1.


Output from srvctl for the service br01:

[oracle] :/opt/home/oracle +ASM2> srvctl config service -d pwbrpr -s br01
Service name: br01
Service is enabled
Server pool: pwbrpr_br01
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SELECT
Failover method: NONE
TAF failover retries: 10
TAF failover delay: 1
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Preferred instances: pwbrpr2
Available instances: pwbrpr1

Now if you look at the SCAN listener, you will see Service br01 has 1 instance servicing it. If the service failsover to pwbrpr1, the listeners will know that the service has failed over to pwbrpr1.

Output from lsnrctl status LISTENER_SCAN2 which is one of the SCAN listeners: The outout from LISTENER_SCAN1 would be the same.

STATUS of the LISTENER
------------------------
Alias LISTENER_SCAN2
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 24-MAY-2010 04:53:30
Uptime 24 days 8 hr. 17 min. 9 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/pwbrpr-wc-2p/listener_scan2/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN2)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=24.40.54.236)(PORT=1525)))
Services Summary...
Service "bh" has 1 instance(s).
Instance "pwbrpr2", status READY, has 1 handler(s) for this service...
Service "br01" has 1 instance(s).
Instance "pwbrpr2", status READY, has 1 handler(s) for this service...
Service "csddsrv" has 1 instance(s).
Instance "pwbrpr2", status READY, has 1 handler(s) for this service...
Service "pwbrpr" has 2 instance(s).
Instance "pwbrpr1", status READY, has 1 handler(s) for this service...
Instance "pwbrpr2", status READY, has 1 handler(s) for this service...
Service "pwbrprXDB" has 2 instance(s).
Instance "pwbrpr1", status READY, has 1 handler(s) for this service...
Instance "pwbrpr2", status READY, has 1 handler(s) for this service...
Service "wb01" has 2 instance(s).
Instance "pwbrpr1", status READY, has 1 handler(s) for this service...
Instance "pwbrpr2", status READY, has 1 handler(s) for this service...

Thursday, June 3, 2010

-- ASM Call's notes with Oracle Expert --


By Kathy Paterson

ASMlib is a best practice and should be used, it is basically a device management tool that has some overlap with udev

·         Most beneficial for RAC env with more than 4 nodes because it does an automatic scan
·         Purpose is  not to enhance performance, it will aggregate multiple file I/O descriptors so for example if you have multiple databases being managed by the same ASM instance, it will aggregate the file descriptors and consume less CPU
·         It is not part of the database stack
·         In general you don’t want to use LVMs with ASM
·         You can see device mapping to ASM using querydisk –p

ASM new features




·         ASM is now part of the grid infrastructure stack, it used to be part of the database stack, now in 11gr2 it is part of the clusterware stack along with ACFS and it is now bundled with CRS as grid infrastructure stack, a conceptual description is that  CSS comes up -> calls a portion of ASM functionality to  scan disks finds pattern for vote disk -> ASM comes up in short stack mounts the disks for CSS -> then ASM comes up completely afterwards.
·         2 major changes in ASM we should pay attention to 
o   ACFS now is integrated with ASM – should be used for oracle home, the next patchset you can use anything on it since it will be posix compliant.  There is a tight integration with hard dependencies built in so that ASM knows where things like Oracle home are and knows the order in which the diskgroups and filesystems need to come up.
o   ASM command has been extended to do any command in SQL you can do in sql *plus
·         Veritas vs ASM differences
o   Oracle has done a lot of different testing scenarios with VERITAS vs ASM, generally what they see at customers is that when they test they tend to use single stream tests ie dd and VERITAS can perform better however it does not scale when using mixed workloads
o   VERITAS has direct I/O, Pre-Fetch scans, Write Coalescing etc most of which is handled as part of standard oracle functionality for example DBWriter does write coalescing
o   So overall Oracle does not see any loss in capability in migrating to ASM
·         ASM Disks
o   How distinguish between tiered storage ?   You can’t from ASM because  ASM is not under the I/O cloud ie HBAs, what you can do is create different disk groups for different tiers…there is no ILM at the ASM level that you can do, in other words ASM doesn’t prevent SA/DBAs from making an incorrect design decisions (tier 1 – 15K, tier 2 – RAID5 SATA, etc)
·         Linux Multipath and Stacker issues, unless you hard assign disks they  have the potential to change names/ownership which has caused issues in the past.    ASM should be able to handle pathname changes, as long as disk ring is set up correctly and as long as it can find the disk, asmlib will mark that disk and it won’t move it and will not do any LUN walking.  You can setup udev rules to create aliases with a string and then have ASM point to that string, benefit you don’t accidently overwrite it for some other purpose.
·         Comcast is trying automate the process as much as possible, looking to get away from aliasing because it is a manual process.  Linux automatically assigns names, the benefit of aliases is that you are less prone to use the disk for other purposes, so helpful in preventing end user mistakes
·         Tuning for OLTP vs DSS – 4mg is a good starting point for just about everything for AUsize, a really large disk group a lot of databases with a lot of files 16MB is a good starting point for DSS repositories 5TB and higher
·         OCR/Voting disk you can’t select a custom AU size…if add 200GB disk we can define different AU size…the issue is that you are creating a data disk group stuck with 1mg and there is no workaround.  Oracle is looking to get that fixed, it is not an ASM specific issue it is an OUI issue…can you do it in a silent install ?  don’t know oracle will get to us….  What other folks have done is that they create a separate disk group for OCR/VOTE … so you create x number of luns to host you partiion them 200 mg each and then fdisk partition the rest of the disk for your data disk group. This way you don’t have to go back and provision ….
·         It is not a best practice to use LVM for both linux and solaris, you can do it as long as you are doing any
RAID in the LVM and presenting the disk to ASM, if you do it use only RAW logical volume … we should avoid it if we can … it is a semi no-no