Tuesday, July 31, 2012

Configure TAF in 11gR2

srvctl add service -d asmdb -s asmdbs -r “asmdb1,asmdb2″ -P BASIC
srvctl start service -d asmdb -s asmdbs
srvctl config service -d asmdb

SQL> select name,service_id from dba_services where name = ‘asmdbs’;

SQL> select name, failover_method, failover_type, failover_retries,goal,
clb_goal,aq_ha_notifications  from dba_services where service_id = 4;

SQL> execute dbms_service.modify_service (service_name => ‘asmdb’ -
, 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);

Thursday, February 9, 2012

Hangcheck timer module till 11gR1 (Linux)

Hangcheck-timer should be loaded at boot time, and monitors the Linux kernel for long operating system hangs that could affect the reliability of a RAC node. It runs in kernel mode and uses the Time Stamp Counter (TSC) to catch scheduling delays or node hangs. This is done by setting a timer, then checking when the timer fires as to whether it was delayed by more than the allowed margin of error. If the duration exceeds the allowed time of (hangcheck_tick + hangcheck_margin seconds), the machine is restarted. Hangcheck-timer will not cause reboots to occur due to CPU starvation.

Hangcheck-timer requires three configuration parameters:

  • hangcheck_tick - defines how often, in seconds, the hangcheck-timer checks the node for hangs. The default value is 60 seconds.
  • hangcheck_margin - defines how much margin is allowed, in seconds, between expected scheduling and real scheduling time. The default value is 180 seconds.
  • hangcheck_reboot - determines if the hangcheck-timer restarts the node if the kernel fails to respond within the sum of the hangcheck_tick and hangcheck_margin parameter values. If the value of hangcheck_reboot is equal to or greater than 1, then the hangcheck-timer module restarts the system. If the hangcheck_reboot parameter is set to zero, then the hangcheck-timer module will not reboot the node, even if a hang is detected. The default value varies by kernel version. In the 2.4 kernel, the default is 1. In 2.6 kernels, the default is 0.
All hangcheck-timer default values should be explicitly overridden when loading the kernel module, based on the Oracle release as follows:
  • 9i: Assuming the default setting of "oracm misscount" is set to 220 seconds:
    hangcheck_tick=30 hangcheck_margin=180 hangcheck_reboot=1
  • 10g/11gR1: Assuming the default setting of "CSS misscount" is set to either 30 or 60 seconds:
    hangcheck_tick=1 hangcheck_margin=10 hangcheck_reboot=1
You must always ensure that the Cluster misscount setting is greater than the sum of the setting for hangcheck_tick + hangcheck_margin.

Note : Hangheck timer is not required starting with Oracle Clusterware 11gR2

11.1.0.6 vs 11.2.0.2


Existing 10gR2 RAC setup (Before Upgrade)

Target 11gR2 RAC Setup (After Upgrade)

Clusterware

Oracle 11g R1 Clusterware 11.1.0.6

Oracle 11gR2 Grid Infrastructure 11.2.0.1

ASM Binaries

11g R1 RAC 11.1.0.6

Oracle 11gR2 Grid Infrastructure 11.2.0.1

Cluster Name

Lab

lab

Cluster Nodes

node1, node2, node3

node1, node2, node3

Clusterware Home

/u01/app/oracle/crs (CRS_HOME)

/u01/app/grid11201 (GRID_HOME)

Clusterware Owner

oracle:(oinstall, dba)

oracle:(oinstall, dba)

VIPs

node1-vip, node2-vip, node3-vip

node1-vip, node2-vip, node3-vip

SCAN

N/A

lab-scan.hingu.net

SCAN_LISTENER Host/port

N/A

Scan VIPs Endpoint: (TCP:1525)

OCR and Voting Disks Storage Type

Raw Devices

Raw Devices OR ASM diskgroup

OCR Disks

/dev/raw/raw1, /dev/raw/raw2

/dev/raw/raw1, /dev/raw/raw2 OR +OCRVDBKP

Voting Disks

/dev/raw/raw3, /dev/raw/raw4, /dev/raw/raw5

/dev/raw/raw3, /dev/raw/raw4, /dev/raw/raw5 OR +OCRVDBKP

ASM_HOME

/u01/app/oracle/asm11gr1

/u01/app/grid11201

ASM_HOME Owner

oracle:(oinstall, dba)

oracle:(oinstall, dba)

ASMLib user:group

oracle:oinstall

oracle:oinstall

ASM LISTENER

LISTENER (TCP:1521)

LISTENER (TCP:1521)




DB Binaries

Oracle 11gR1 RAC (11.1.0.6)

Oracle 11gR2 RAC (11.2.0.1)

DB_HOME

/u01/app/oracle/db11gr1

/u01/app/oracle/db11201

DB_HOME Owner

oracle:(oinstall, dba)

oracle:(oinstall, dba)

DB LISTENER

LAB_LISTENER

LAB_LISTENER

DB Listener Host/port

node1-vip, node2-vip, node3-vip (port 1530)

node1-vip, node2-vip, node3-vip (port 1530)

DB Storage Type, File Management

ASM with OMFs

ASM with OMFs

ASM diskgroups for DB and FRA

DATA, FRA

DATA, FRA

OS Platform

Oracle Enterprise Linux 5.5 (32 bit)

Oracle Enterprise Linux 5.5 (32 bit)


Oracle Clusterware 11g Release 2 backs up the OCR automatically every four hours on a schedule that is dependent on when the node started (not clock time). One node is dedicated to these backups, but the node dedicated to the backups might change should the node that was doing the backups go down. OCR backups are made to the GRID_HOME/cdata/ directory on the node performing the backups. These backups are named as follows:

  • 4-hour backups (3 max) –backup00.ocr, backup01.ocr, and backup02.ocr.
  • Daily backups (2 max) – day.ocr and day_.ocr
  • Weekly backups (2 max) – week.ocr and week_.ocr

Therefore no additional clean-up tasks are required of the DBA. Oracle Clusterware will also take a backup at the end of the day. The last two of these backups are retained. Finally, at the end of each week Oracle will perform another backup, and again the last two of these backups are retained. You should make sure that your routine file system backups backup the OCR location. Note that RMAN does not backup the OCR.

You can use the ocrconfig command to view the current OCR backups as seen in this example:

Ocrconfig –showbackup auto

One thing to be aware of is that if your cluster is shutdown, then the automatic backups will not occur (nor will the purging). The timer restarts from the beginning when the cluster is restarted. When you start the cluster backup, a backup will not be taken immediately. Be aware then, if you are stopping and starting your cluster that you could impact the OCR backups and the backup period could go long beyond 4 hours.

If you feel that you need to backup the OCR immediately (for example, you have made a number of cluster related changes) then you can use the ocrconfig command to perform a manual backup:

Ocrconfig –manualbackup

You can list the manual backups with the ocrconfig command too:

Ocrconfig –showbackup manual

Ocrconfig also supports the creation of a logical backup of the OCR as seen here:

Ocrconfig –export /tmp/ocr.exp

It is recommended that the OCR backup location be on a shared file system and that the cluster be configured to write the backups to that file system. To change the location of the OCR backups, you can use the ocrconfig command as seen in this example:

Ocrconfig –backuploc /u01/app/oracle/ocrloc

The Voting Disk Files are used by Oracle Clusterware to determine which nodes are currently members of the cluster. The voting disk files are also used in concert with other Cluster components such as CRS to maintain the clusters integrity.

As with most everything else Clusterware/RAC related, you need to consider redundancy in your architecture. Oracle expects that you will configure at least 3 voting disks for redundancy purposes. You should always configure an odd number of voting disks >= 3. This is because loss of more than half your voting disks will cause the entire cluster to fail.

You should plan on allocating 280MB for each voting disk file. For example, if you are using ASM and external redundancy then you will need to allocate 280MB of disk for the voting disk. If you are using ASM and normal redundancy you will need 560MB.

Oracle Database 11g Release 2 provides the ability to store the voting disks in ASM along with the OCR. Oracle Clusterware can access the OCR and the voting disks present in ASM even if the ASM instance is down. As a result CSS can continue to maintain the Oracle cluster even if the ASM instance has failed. By default Oracle will create three voting disk files in ASM. This requires that the ASM diskgroup that will store the voting disk file be configured with three failure groups. If you are upgrading to Oracle Database 11g you can migrate your voting disks to ASM easily with the crsctl replace command.

You can also use the crsctl query command to locate the voting disks as seen in this example:

Crsctl query css votedisk

You can also migrate voting disks between NAS and ASM or ASM to NAS using the crsctl replace command.

Starting with Oracle Clusterware 11g Release 2 you no longer need to backup the voting disks. The voting disks are automatically backed up as a part of the OCR. In fact, Oracle explicitly indicates that you should not use a backup tool like dd to backup or restore voting disks. Doing so can lead to the loss of the voting disk.

Time Synchronization on Oracle RAC

Oracle Clusterware 11g release 2 (11.2) requires time synchronization across all nodes within a cluster when Oracle RAC is deployed.

You can use one of the following options for time synchronizaion:

  • Your operating system-configured network time protocol (NTP)

  • Oracle Cluster Time Synchronization Service (new in 11gR2)

Hangcheck_timer module is required to run a supported configuration in Oracle Real Application Clusters environments on Linux, with Oracle releases 9i, 10g, or 11gR1 RAC.

Note : Hangheck timer is not required starting with Oracle Clusterware 11gR2

Reference:
http://www.oracledba.org/upgrade/Upgrade_11gR1_RAC_to_11gR2_RAC.htm
http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/RACR2ARC6/Default.aspx

Hard parsing vs Soft parsing in Oracle

Oracle SQL is parsed before execution, and a hard parse includes these steps:

  • Loading into shared pool - The SQL source code is loaded into RAM for parsing. (the "hard" parse step)
  • Syntax parse - Oracle parses the syntax to check for misspelled SQL keywords.
  • Semantic parse - Oracle verifies all table & column names from the dictionary and checks to see if you are authorized to see the data.
  • Query Transformation - If enabled (query_rewrite=true), Oracle will transform complex SQL into simpler, equivalent forms and replace aggregations with materialized views, as appropriate.
  • Optimization - Oracle then creates an execution plan, based on your schema statistics (or maybe with statistics from dynamic sampling in 10g).
  • Create executable - Oracle builds an executable file with native file calls to service the SQL query.
Reference: Oracle Tips by Burleson Consulting

Wednesday, February 1, 2012

Abbreviations commonly used for an Oracle DB

Oracle System Identifier (SID)

Transparent Network Substrate (TNS)

Transparent Application Failover (TAF): A run-time failover for high-availability environments, such as Oracle Real Application Clusters and Oracle Fail Safe, that refers to the failover and re-establishment of application-to-service connections. It enables client applications to automatically reconnect to the database if the connection fails, and, optionally, resume a SELECT statement that was in progress. This reconnect happens automatically from within the Oracle Call Interface (OCI) library.

VPD (Virtual Private Database) is also known as Fine Grained Access Control (FGAC) or Row-level Security (RLS). It provides added security capabilities to the Oracle database by masking data so that users only see their private information.

TKPROF (Transient Kernel Profiler): used to convert trace files into more read-able format



Reference: Oracle documentation

Service Vs SID in tnsnames.ora file

A service name is more flexible than a SID would be.

A database can dynamically register with a listener using one or more service names. In fact, more than one database can register with a listener using the same service name (think about a clustered environment where you have multiple instances that all are the same database under the covers).

A database on the other hand has a single SID. And a single SID goes to a single database. It is a pure 1:1 relationship.

A service is a many to many relationship.

Service names are used with dynamic registration - the data registers with the listener after it starts up. Once it does that, you can connect.


With the SID - that is more like telling the listener "I want you to connect to this specific database, I know the 'address', here you go"

With the SERVICE - you are asking the listener to put you in touch with a database that can service your request, a database that registers using that service.

That you cannot connect using the service name MYDB means your database is not registering with that listener (see the Net Admin guide for dynamic registration) or if it is registered with that listener (lsnrctl services is a command you can use to see the registered services) then it is registering with a different name.

Reference: Tom Kytes

RAC Software Architecture

Real Application Clusters Software Architecture




Some of these components are supplied with the Oracle database software while others are vendor-specific.

The Operating System-Dependent Clusterware
Real Application Clusters Shared Disk Components
Real Application Clusters-Specific Daemon and Instance Processes
The Global Cache and Global Enqueue Service

The Operating System-Dependent Clusterware

Real Application Clusters processing uses operating system-dependent (OSD) clusterware to access the operating system and for cluster-related service processing such as communicating information about instance startup and shutdown. Vendors provide the OSD clusterware for UNIX operating systems, and Oracle provides the OSD clusterware for Windows NT and Windows 2000 operating systems. The OSD has the following subcomponents:

The Cluster Manager
The Node Monitor
The Interconnect

The Cluster Manager

The cluster manager (CM) oversees internode messaging that travels over the interconnect to coordinate internode operations. The cluster manager also provides a global view of the cluster and the nodes and instances that are members of it. The cluster manager also controls cluster membership.
The Node Monitor

The cluster manager includes a subset of functionality known as a node monitor. The node monitor polls the status of each resource in the cluster including the nodes, the interconnect hardware and software, and the shared disks. In the Oracle-supplied Cluster Manager for Windows, the node monitor also polls the Oracle instances.

The cluster manager informs clients and the Oracle server when the status of cluster resources change. This is because Real Application Clusters manages cluster membership by reconfiguring the cluster database when a joining instance registers with the cluster manager or when an existing instance disconnects from it.

The node monitor also serves the cluster manager by:

Providing node management interface modules
Discovering and tracking the membership states of the nodes by providing a common view of node membership across the cluster
Detecting and diagnosing changes in the states of active nodes and communicating information about those change events

The IPC or interconnect controls messaging among the nodes. Real Application Clusters also uses the IPC to transfer data blocks between instances.

The Global Services Daemon

The Global Services Daemon (GSD) runs on each node with one GSD process per node. The GSD coordinates with the cluster manager to receive requests from clients such as the DBCA, EM, and the SRVCTL utility to execute administrative job tasks such as instance startup or shutdown. The GSD is not an Oracle instance background process and is therefore not started with the Oracle instance.
Instance Processes Specific to Real Application Clusters

A Real Application Clusters database has the same processes as single-instance Oracle databases such as process monitor (PMON), database writer (DBWRn), log writer (LGWR), and so on. There are also additional Real Application Clusters-specific processes. The exact names of these processes and the trace files that they create are platform-dependent.

Global Cache Service Processes (LMSn), where n ranges from 0 to 9 depending on the amount of messaging traffic, control the flow of messages to remote instances and manage global data block access. LMSn processes also transmit block images between the buffer caches of different instances. This processing is part of the Cache Fusion feature.
The Global Enqueue Service Monitor (LMON) monitors global enqueues and resources across the cluster and performs global enqueue recovery operations. Enqueues are shared memory structures that serialize row updates.
The Global Enqueue Service Daemon (LMD) manages global enqueue and global resource access. Within each instance, the LMD process manages incoming remote resource requests.
The Lock Process (LCK) manages non-Cache Fusion resource requests such as library and row cache requests.
The Diagnosability Daemon (DIAG) captures diagnostic data about process failures within instances. The operation of this daemon is automated and it updates an alert log file to record the activity that it performs.

The Global Cache and Global Enqueue Service

The Global Cache Service (GCS) and Global Enqueue Service (GES) are integrated components of Real Application Clusters that coordinate simultaneous access to the shared database and to shared resources within the database. These services maintain consistency and data integrity. The GCS and GES on each instance, as well as the Cluster Manager, use the IPC to communicate between instances and within the cluster. This section describes the following features of the GCS and GES:

Application Transparency
Global Resource Directory with Distributed Architecture
Resource Mastering and Affinity
GCS and GES Interaction with the Cluster Manager

Application Transparency

The coordination of access to resources that is performed by the GCS and GES is transparent to applications. Applications in Real Application Clusters use the same concurrency mechanisms as in single-instance Oracle databases.
Global Resource Directory with Distributed Architecture

The GCS and GES maintain a Global Resource Directory to record information about resources. The Global Resource Directory resides in memory, is distributed throughout the cluster, and is available to all active instances. In this distributed architecture, each node participates in the management of information in the directory. This distributed scheme provides fault tolerance and enhanced runtime performance.

The GCS and GES ensure the integrity of the Global Resource Directory even if multiple nodes fail. The shared database is always accessible if at least one instance is active after recovery is completed. The fault tolerance of the resource directory also enables Real Application Clusters instances to start and stop at any time, in any order.
Resource Mastering and Affinity

The Global Cache and Global Enqueue Services maintain information about each resource within a cluster. The GCS and GES nominate one instance to manage all information about a particular resource. This instance is called the resource master. The GCS evaluates resource mastering periodically and changes the resource master based on data access patterns. This reduces network traffic as well as resource acquisition time.

GCS and GES Interaction with the Cluster Manager

The GCS and GES operate independently of the cluster manager. However, these services rely on the cluster manager for timely and correct information about the statuses of the instances in the cluster. If these services cannot obtain the information they need from a particular instance, then Oracle shuts down the unresponsive instance. This ensures the integrity of Real Application Clusters databases because each instance must be aware of all other active instances to coordinate shared disk access.

Reference: Oracle Documentation

Thursday, January 5, 2012

All services on node 2 down (10gRAC 2-node)

all service on node 2 were down:

---------------------------------

/oracle_crs/product/10.2.0/crs_1/bin

-> ./crs_stat -t

Name Type Target State Host

------------------------------------------------------------

ora....U1.inst application ONLINE ONLINE nc2s...db01

ora....U2.inst application ONLINE OFFLINE

ora....4U1.srv application ONLINE ONLINE nc2s...db01

ora....4U2.srv application ONLINE OFFLINE

ora....RV01.cs application ONLINE ONLINE nc2s...db01

ora.RMS4U.db application ONLINE ONLINE nc2s...db01

ora....SM1.asm application ONLINE ONLINE nc2s...db01

ora....01.lsnr application ONLINE ONLINE nc2s...db01

ora....b01.gsd application ONLINE ONLINE nc2s...db01

ora....b01.ons application ONLINE ONLINE nc2s...db01

ora....b01.vip application ONLINE ONLINE nc2s...db01

ora....SM2.asm application ONLINE OFFLINE

ora....02.lsnr application ONLINE OFFLINE

ora....b02.gsd application ONLINE OFFLINE

ora....b02.ons application ONLINE OFFLINE

ora....b02.vip application ONLINE ONLINE nc2s...db01

login as root and restart crs service:

-----------------------------------------

# cd /oracle_crs/product/10.2.0/crs_1/bin

# ./crsctl check crs

Failure 1 contacting CSS daemon

Cannot communicate with CRS

Cannot communicate with EVM

# ./crsctl start crs

Attempting to start CRS stack

The CRS stack will be started shortly

/oracle_crs/product/10.2.0/crs_1/bin

-> .//crs_stat -t

Name Type Target State Host

------------------------------------------------------------

ora....U1.inst application ONLINE ONLINE nc2s...db01

ora....U2.inst application ONLINE ONLINE nc2s...db02

ora....4U1.srv application ONLINE ONLINE nc2s...db01

ora....4U2.srv application ONLINE ONLINE nc2s...db02

ora....RV01.cs application ONLINE ONLINE nc2s...db01

ora.RMS4U.db application ONLINE ONLINE nc2s...db01

ora....SM1.asm application ONLINE ONLINE nc2s...db01

ora....01.lsnr application ONLINE ONLINE nc2s...db01

ora....b01.gsd application ONLINE ONLINE nc2s...db01

ora....b01.ons application ONLINE ONLINE nc2s...db01

ora....b01.vip application ONLINE ONLINE nc2s...db01

ora....SM2.asm application ONLINE ONLINE nc2s...db02

ora....02.lsnr application ONLINE ONLINE nc2s...db02

ora....b02.gsd application ONLINE ONLINE nc2s...db02

ora....b02.ons application ONLINE ONLINE nc2s...db02

ora....b02.vip application ONLINE ONLINE nc2s...db02