CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Real Application Clusters (RAC)
Version 11.1
Acronyms
RAC Related Acronyms
Acronym Short For Comment
ASM Automated Storage Management Oracle instance for managing raw partitions
CRS Cluster Ready Services  
FCF Fast Connection Failover  
GSD Global Services Daemon  
OCFS Oracle Cluster File System Open-source file system that supports sharable files
TAF Transparent Application Failover  
VIP Virtual Internet Protocol (address) Required 
 
CRS_STAT Commands

Cluster Ready Services Tool
Option

Description

-a resource_name Used with the -g or -r option to verify whether the specified resource is registered or running under Oracle Clusterware. Specify the name of the resource as listed in its application profile.
-c cluster_node Displays information about applications or application resources on the specified cluster node.
-f Displays all information about the resource including extended information (-v) and the in-memory profile (-p).
-g Returns 0 (zero) if the specified application or application resource is registered with Oracle Clusterware; returns 1 if it is not. This option only works if a single resource is specified with the -a option.
-l Displays the status in a list (nontabular) format. This is the default format.
-ls Lists resources, owners, and permissions for all resources.
-ls resource_name Lists resources, owners, and permissions for a specified resource.
-p resource_name [...]] Displays the status of the in-memory profile for the specified resources. If no resources are specified, then the status of the in-memory profile for all registered resources is displayed. If a resource is not registered, its status is not displayed.
-q Runs the command in quiet mode (no messages are displayed on the console).
-r Returns 0 (zero) if the specified application or application resource is running under Oracle Clusterware; returns 1 if it is not. This option can only be successful if a single resource is specified with the -a option.
-t Lists information for all resources in a tabular form.
 
-v
Lists how many times a resource has been restarted or has failed within the resource failure interval, the maximum number of times that a resource can be restarted or can fail, and the target state of the application. Also, this option lists normal status information.

This option displays extended information about the status of resources. Extra attributes shown include RESTART_COUNT and FAILURE_COUNT. The RESTART_COUNT attribute shows the number of restarts of the application that have been attempted since it was started. The FAILURE_COUNT attribute shows how many failures have occurred during the last FAILURE_THRESHOLD in seconds. The attribute FAILOVER_STATUS shows the time at which an application resource started while waiting to relocate due to a cluster node failure if the resource has a FAILOVER_DELAY value greater than 0. It is not displayed otherwise.
 
Dictionary Views
Dependent Objects
CLUSTER_DATABASES CLUSTER_INSTANCES CLUSTER_NODES
GV_$CLUSTER_INTERCONNECTS GV_$CONFIGURED_INTERCONNECTS  
 
Interconnect Tuning Views
gv$cache_transfer Monitor blocks transferred by object
gv$class_cache_transfer Monitor block transfer by class
gv$file_cache_transfer Monitor the blocks transferred per file
gv$temp_cache_transfer Monitor the transfer of temporary tablespace blocks
FAN Notification Related recent_resource_incarnations$
Global Views SELECT view_name
FROM dba_views
WHERE view_name LIKE 'GV%'
ORDER BY 1;

Related Scripts in /rdbms/admin
File Name Location Description
addmrpt.sql $ORACLE_HOME/rdbms/admin ADDM Report creation
addmrpti.sql $ORACLE_HOME/rdbms/admin ADDM Report creation
catawrtb.sql $ORACLE_HOME/rdbms/admin RAC Statistics data dictionary table creation
catclust.sql $ORACLE_HOME/rdbms/admin Create cluster-related views and DBMS_CLUSTDB package
catfusrg.sql $ORACLE_HOME/rdbms/admin Catalog registration of feature usage
clsrus.msb $ORACLE_HOME/racg/mesg RAC error messages
clsrus.msg $ORACLE_HOME/racg/mesg RAC error messages
cmpdbdwg.sql $ORACLE_HOME/rdbms/admin Downgrade RAC database
dbmssrv.sql $ORACLE_HOME/rdbms/admin Builds DBMS_SERVICE package
e0902000sql $ORACLE_HOME/rdbms/admin Drop RAC related views
olse101.sql $ORACLE_HOME/rdbms/admin Downgrade for RAC enabled OLS
spcpkg.sql $ORACLE_HOME/rdbms/admin Builds STATSPACK package
sprepcon.sql $ORACLE_HOME/rdbms/admin StatsPack report configuration
sprepsins.sql $ORACLE_HOME/rdbms/admin Reports on differences between StatsPack snapshots 
sprsqins.sql $ORACLE_HOME/rdbms/admin StatsPack resource usage report
spup92.sql $ORACLE_HOME/rdbms/admin stats$segstat_obj creation for RAC stats
utlclust.sql $ORACLE_HOME/rdbms/admin Dump cluster database related information
utlprp.sql $ORACLE_HOME/rdbms/admin Recompile invalid objects in a cluster environment
utlsyxsz.sql $ORACLE_HOME/rdbms/admin Estimates space requirement for SYSAUX tablespace
 
Failover

Failover comparison
FCF TAF
Works for Both Client-side drivers Works only for JDBC-OCI
Rapid Node/Service Failure Detection Rapid Node/Service Failure Detection
Connection Retry by Application or Container Transparent Connection Retry and Query Retry
In-Flight Transactions Automatically Rolled Back Application must Roll Back and notify TAF
Supports Dynamic Work Load Balancing No Support for Work Load Balancing
 
Initialization Parameters
RAC Modifiable Parameters set linesize 121
col name format a40
col value format a50

SELECT name, value
FROM gv$parameter
WHERE isinstance_modifiable = 'TRUE';
ACTIVE_INSTANCE_COUNT Range of values 1 or >= the number of instances in the cluster. (Values other than 1 have no effect on the active or standby status of any instances.)
ACTIVE_INSTANCE_COUNT = 8
CLUSTER_DATABASE For all instances, the value must be set to TRUE.
CLUSTER_DATABASE = TRUE
CLUSTER_DATABASE_ INSTANCES is a Real Application Clusters parameter that specifies the number of instances that are configured as part of your cluster database. You must set this parameter for every instance. Normally you should set this parameter to the number of instances in your Real Application Clusters environment. A proper setting for this parameter can improve memory use.
CLUSTER_DATABASE_INSTANCES = 8

CLUSTER_INTERCONNECTS
One or more IP addresses, separated by colons in the form CLUSTER_INTERCONNECTS = ifn [: ifn ] ....

This parameter can be used to override the default interconnect with a preferred cluster traffic network. This parameter is useful in Data Warehouse systems that have reduced availability requirements and high interconnect bandwidth demands. You can also use CLUSTER_INTERCONNECTS to override the default interconnect chosen by Oracle.

For example, if you are running two instances of Oracle for two databases on the same machine, then you can load balance the interconnect traffic to different physical interconnects. This does not reduce Oracle availability.
TBD

GC_FILES_TO_LOCK
GC_FILES_TO_LOCKS = '{file_list=lock_count[!blocks][EACH][:...]}'

You must set this parameter for every instance, and multiple instances must have identical values. To change the value, you must shut down all instances in the cluster, change the value for each instance, and then start up each instance.

GC_FILES_TO_LOCKS is a Real Application Clusters parameter that has no effect on an instance running in exclusive mode. It controls the mapping of pre-release 9.0.1 parallel cache management (PCM) locks to datafiles.
TBD

INSTANCE_GROUPS
INSTANCE_GROUPS = group_name [, group_name ] ...

INSTANCE_GROUPS is a Real Application Clusters parameter that you can specify only in parallel mode. Used in conjunction with the PARALLEL_INSTANCE_GROUP parameter, it lets you restrict parallel query operations to a limited number of instances.

This parameter specifies one or more instance groups and assigns the current instance to those groups. If one of the specified groups is also specified in the PARALLEL_INSTANCE_GROUP parameter, then Oracle allocates query processes for a parallel operation from this instance.
TBD

INSTANCE_NUMBER
Lowest available number; derived from instance start up order and INSTANCE_NUMBER value of other instances. If not configured for Real Application Clusters, then 0.

INSTANCE_NUMBER is a Real Application Clusters parameter that can be specified in parallel mode or exclusive mode. It specifies a unique number that maps the instance to one free list group for each database object created with storage parameter FREELIST GROUPS.

The INSTANCE parameter of the ALTER TABLE ... ALLOCATE EXTENT statement assigns an extent to a particular free list group. If you set INSTANCE_NUMBER to the value specified for the INSTANCE parameter, the instance uses that extent for inserts and for updates that expand rows.

The practical maximum value of this parameter is the maximum number of instances specified in the CREATE DATABASE statement. The absolute maximum is operating system-dependent.
TBD
MAX_COMMIT_
PROPAGATION_DELAY
Range of values 0 to 90000

MAX_COMMIT_PROPAGATION_DELAY is a Real Application Clusters parameter. This initialization parameter should not be changed except under a limited set of circumstances specific to the cluster database.
TBD
PARALLEL_INSTANCE_ GROUP PARALLEL_INSTANCE_GROUP = group_name

PARALLEL_INSTANCE_GROUP is a Real Application Clusters parameter that you can specify in parallel mode only. Used in conjunction with the INSTANCE_GROUPS parameter, it lets you restrict parallel query operations to a limited number of instances.

This parameter identifies the parallel instance group Oracle will use for spawning parallel execution processes. Parallel operations will spawn parallel execution processes only on instances that specify a matching group in their INSTANCE_GROUPS parameter.

If the value assigned to PARALLEL_INSTANCE_GROUP is the name of a group that does not exist, then the operation runs serially. No parallelism is used.
TBD

THREAD
Range of values 0 to the maximum number of enabled threads

THREAD is a Real Application Clusters parameter that specifies the number of the redo thread to be used by an instance.

When you create a database, Oracle creates and enables thread 1 as a public thread (one that can be used by any instance). You must create and enable subsequent threads using the ADD LOGFILE THREAD clause and ENABLE THREAD clause of the ALTER DATABASE statement. The number of threads you create is limited by the MAXINSTANCES parameter specified in the CREATE DATABASE statement.

In exclusive mode, thread 1 is the default thread. However, you can specify THREAD for an instance running in exclusive mode if you want to use the redo log files in a thread other than thread 1.

In parallel mode, you can specify any available redo thread number, as long as that thread number is enabled and is not in use by another instance.

A value of zero specifies that this instance can use any available, enabled public thread.
TBD
 
Links
Related Links
Topic Description Link
NIC Bonding on RHEL 4 Hewlett/Packard Resource Center Forums

Click Here

 
Net Services Parameters

Maximum Availability Parameters
Option Valid Values
FAILOVER on, off, yes, no, true, false
LOAD_BALANCE on, off, yes, no, true, false

SOURCE_ROUTE
Use the parameter SOURCE_ROUTE to enable routing through multiple protocol addresses.
When set to on or yes, Net Services tries each address in order until the destination is reached. The parameter can be embedded under either DESCRIPTION_LIST DESCRIPTION or the ADDRESS_LIST.

DEMO_RAC =
  (DESCRIPTION=
  (SOURCE_ROUTE=YES)
  (ADDRESS=                                        # hop 1
    (PROTOCOL=TCP)
    (HOST=vipalpha2.psoug.org)
    (PORT=1521))
  (ADDRESS_LIST=
    (FAILOVER=ON)
    (LOAD_BALANCE=off)
    (ADDRESS=                                      # hop 2
      (PROTOCOL=tcp)
      (HOST=vipalpha3.psoug.org)
      (PORT=1521))
    (ADDRESS=
      (PROTOCOL=tcp)
      (HOST=vipalpha1.psoug.org)(PORT=1630)))
  (ADDRESS=                                     # hop 3
    (PROTOCOL=tcp)
    (HOST=vipalpha4.psoug.org)
    (PORT=1521))
  (CONNECT_DATA=(SERVICE_NAME=tfta_alpha.psoug.org)))

Failover-Mode Parameters
Option Valid Values
BACKUP Specify the failover node by its net service name. A separate net service name must be created for the failover node.
DELAY Specify the number of times to attempt to connect after a failover. If DELAY is specified, RETRIES defaults to five retry attempts.
 
METHOD
Specify how fast failover is to occur from the primary node to the backup node:
  • BASIC: Establishes connections at failover time. This option requires almost no work on the backup database server until failover time.
  • PRECONNECT: Pre-establishes connections. This provides faster failover but requires that the backup instance be able to support all connections from every supported instance.
RETRIES <number_of_retries>
SESSION Fails over the session; that is, if a user's connection is lost, a new session is automatically created for the user on the backup. This type of failover does not attempt to recover selects.
TYPE Specify the type of failover. Three types of Oracle Net failover functionality are available by default to Oracle Call Interface (OCI) applications:
 
Oracle Cluster Registry Configuration Tool
ocrconfig
Option Description
   
 
Server Configuration Tool
Downgrade configuration srvconfig -downgrade -dbname <database_name> -orahome <oracle_home>
srvconfig -downgrade -dbname TSTA -orahome $ORACLE_HOME
 
Server Control Commands
Add instance srvctl add instance -d <database_name> instance -n <instance_name>
srvctl add instance -d TSTA instance -n tsta3
Add TAF service srvctl add service -d <database_name> -s <service_name> -r <instance_names> -p <service_type>
srvctl add service -d TSTA -s TSTA_TAF -r TSTA1,TSTA2 -p BASIC
Remove Clusterware node applications from a node srvctl remove nodeapps -n <node_name>
srvctl remove nodeapps -n omega1
Remove database srvctl remove database -d <database_name>
srvctl remove database -d TSTA
Remove instance srvctl remove instance -d <database_name> -i <instance_name>
srvctl remove instance -d TSTA -i tsta2
Start nodeapps running on a node and check status srvctl start nodeapps -n <node_name>
srvctl start nodeapps -n TSTA1
crs_stat -t
Start service running on a node srvctl start service -d <database_name> -s <service_name>
srvctl start service -d TSTA -s TSTA_TAF
Show all services running on a node srvctl status nodeapps -n <node_name>
srvctl status nodeapps -n omega1
Stop Clusterware node applications on a node srvctl stop nodeapps -n <node_name>
srvctl stop nodeapps -n omega1
 
RAC Class Files

BASH.ORACLE
PS1='[\u@\h \W]# '
export PS1

TEMP=/tmp
TMPDIR=/tmp
export TEMP TMPDIR

ORACLE_BASE=/oracle/app
export ORACLE_BASE

CRS_HOME=$ORACLE_BASE/product/10.2.0.1/crs_1
export CRS_HOME

ORACLE_HOME=$ORACLE_BASE/product/10.2.0.1/db_1
export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$PATH
export PATH

ORACLE_SID=TSTxx
export ORACLE_SID

LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/jdbc/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH

alias ob='cd $ORACLE_BASE'
alias oh='cd $ORACLE_HOME'
alias ch='cd $CRS_HOME'
alias crs='$CRS_HOME/bin/crs_stat'
alias sql='sqlplus "/ as sysdba"'

umask 022

BASH.ROOT
PS1='[\u@\h \W]# '
export PS1

TEMP=/tmp
TMPDIR=/tmp
export TEMP TMPDIR

ORACLE_BASE=/oracle/app
export ORACLE_BASE

CRS_HOME=$ORACLE_BASE/product/10.2.0.1/crs_1
export CRS_HOME

ORACLE_HOME=$ORACLE_BASE/product/10.2.0.1/db_1
export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$PATH
export PATH

ORACLE_SID=TSTxx
export ORACLE_SID

LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/jdbc/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH

alias ob='cd $ORACLE_BASE'
alias oh='cd $ORACLE_HOME'
alias ch='cd $CRS_HOME'
alias crs='$CRS_HOME/bin/crs_stat'
alias sql='sqlplus "/ as sysdba"'

umask 022
CMCFG.ORA TBD
CVUQDISK CVUQDISK_GRP=oinstall; export CVUQDISK_GRP
echo $CVUQDISK_GRP
cd /stage/clusterware/rpm
rpm -iv cvuqdisk-1.0.1-1.rpm

FSTAB
# Uncomment the line only for YOUR team!

fstab
-- note for Solaris replace nolock with llock.

/* NEW 270C setup alphas
ntap270a:/vol/alpha /u01 nfs rw,bg,intr,hard,rsize=32768,wsize=32768,noac,nolock,tcp,vers=3 0 0
*/

/* NEW 270C setup betas
ntap270a:/vol/beta /u01 nfs rw,bg,intr,hard,rsize=32768,wsize=32768,noac,nolock,tcp,vers=3 0 0
*/

/*
ntap270b:/vol/oraapps /u01 nfs rw,bg,intr,hard,rsize=32768,wsize=32768,noac,nolock,tcp,vers=3 0 0
*/

# ALPHA - Uncomment the following
#netapp1:/vol/vol2/alpha /u01 nfs rw,bg,intr,hard,rsize=32768,wsize=32768,noac,nolock,tcp,vers=3 0 0

# BETA - Uncomment the following
#netapp1:/vol/vol2/beta /u01 nfs rw,bg,intr,hard,rsize=32768,wsize=32768,noac,nolock,tcp,vers=3 0 0

# GAMMA - Uncomment the following
#netapp1:/vol/vol2/gamma /u01 nfs rw,bg,intr,hard,rsize=32768,wsize=32768,noac,nolock,tcp,vers=3 0 0

# DELTA - Uncomment the following
#netapp1:/vol/vol2/delta /u01 nfs rw,bg,intr,hard,rsize=32768,wsize=32768,noac,nolock,tcp,vers=3 0 0

# Oracle documentation - do not edit
netapp1:/vol/vol0/stage.10gR2.extras/docs /oradoc nfs ro,bg,intr,hard,rsize=32768,noac,nolock,tcp,vers=3 0 0


hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost

#Public Physical Port Addresses
192.168.1.211 alpha1.psoug.org alpha1
192.168.1.212 alpha2.psoug.org alpha2
192.168.1.221 beta1.psoug.org  beta1
192.168.1.222 beta2.psoug.org  beta2
192.168.1.231 gamma1.psoug.org gamma1
192.168.1.232 gamma2.psoug.org gamma2
192.168.1.241 delta1.psoug.org delta1
192.168.1.242 delta2.psoug.org delta2

192.168.1.213 alpha3.psoug.org alpha3
192.168.1.214 alpha4.psoug.org alpha4
192.168.1.223 beta3.psoug.org  beta3
192.168.1.224 beta4.psoug.org  beta4
192.168.1.233 gamma3.psoug.org gamma3
192.168.1.234 gamma4.psoug.org gamma4
192.168.1.243 delta3.psoug.org delta3
192.168.1.244 delta4.psoug.org delta4

# Private Interconnect Addresses
10.0.1.1 node1_alpha
10.0.1.2 node2_alpha
10.0.2.1 node1_beta
10.0.2.2 node2_beta
10.0.3.1 node1_gamma 
10.0.3.2 node2_gamma
10.0.4.1 node1_delta
10.0.4.2 node2_delta

10.0.1.3 node3_alpha
10.0.1.4 node4_alpha
10.0.2.3 node3_beta
10.0.2.4 node4_beta
10.0.3.3 node3_gamma
10.0.3.4 node4_gamma
10.0.4.3 node4_delta
10.0.4.4 node4_delta

# Shared Storage NetApp NAS Server
192.168.2.200 netapp1
192.168.1.200 netappweb
192.168.2.201 ntap270a
192.168.2.202 ntap270b


# Oracle VIP Addresses
192.168.1.11 vipalpha1.psoug.org vipalpha1
192.168.1.12 vipalpha2.psoug.org vipalpha2
192.168.1.21 vipbeta1.psoug.org vipbeta1
192.168.1.22 vipbeta2.psoug.org vipbeta2
192.168.1.31 vipgamma1.psoug.org vipgamma1
192.168.1.32 vipgamma2.psoug.org vipgamma2
192.168.1.41 vipdelta1.psoug.org vipdelta1
192.168.1.42 vipdelta2.psoug.org vipdelta2

192.168.1.13 vipalpha3.psoug.org vipalpha3
192.168.1.14 vipalpha4.psoug.org vipalpha4
192.168.1.23 vipbeta3.psoug.org vipbeta3
192.168.1.24 vipbeta4.psoug.org vipbeta4
192.168.1.33 vipgamma3.psoug.org vipgamma3
192.168.1.34 vipgamma4.psoug.org vipgamma4
192.168.1.43 vipdelta3.psoug.org vipdelta3
192.168.1.44 vipdelta4.psoug.org vipdelta4

# PSOUG Class Support Addresses
10.0.1.1 node1
10.0.1.2 node2
192.168.1.119 bigdog.psoug.org bigdog

hosts.equiv
# Comment/Uncomment those entries for your cluster below.
#alpha1       oracle
#alpha2       oracle
#beta1        oracle
#beta2        oracle
#gamma1       oracle
#gamma2       oracle
delta1        oracle
delta2        oracle

#alpha-node1  oracle
#alpha-node2  oracle
#beta-node1   oracle
#beta-node2   oracle
#gamma-node1  oracle
#gamma-node2  oracle
delta-node1   oracle
delta-node2   oracle

# DO NOT Comment out this entry!
bigdog       oracle

limits.conf
# Oracle Entries

* soft nproc 2047
* hard nproc 16384
* soft nofile 1024
* hard nofile 65536
* soft memlock 3145728
* hard memlock 3145728
listener.ora TBD
login session required /lib/security/pam_limits.so

profile
# For oracle install
if [ $USER = "oracle" ]; then
  if [ $SHELL = "/bin/ksh" ]; then
    ulimit -p 16384
    ulimit -n 65536
  else
    ulimit -u 16384 -n 65536
  fi
fi

rc.local
# Oracle RAC Entries
insmod hangcheck-time hangcheck_tick=30 hangcheck_margin=180
/sbin/service nscd start
srvConfig.loc TBD

sysctl.conf
# Oracle 10g Parameters
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144

TNSNAMES.ORA
10.2.0.1
RedHat Linux AS 4 U2
TSTAB_TAF =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipalpha1.psoug.org)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipalpha2.psoug.org)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipalpha3.psoug.org)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipalpha4.psoug.org)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTA_TAF)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

TSTA_TAF =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipalpha1.psoug.org)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipalpha2.psoug.org)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTA_TAF)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

TSTAB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipalpha1.psoug.org)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipalpha2.psoug.org)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipalpha3.psoug.org)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipalpha4.psoug.org)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTA)
    )
  )

TSTA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipalpha1.psoug.org)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipalpha2.psoug.org)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTA)
    )
  )

TSTA4 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipalpha4.psoug.org)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTA)
      (INSTANCE_NAME = TSTA4)
    )
  )

TSTA3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipalpha3.psoug.org)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTA)
      (INSTANCE_NAME = TSTA3)
    )
  )

TSTA2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipalpha2.psoug.org)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTA)
      (INSTANCE_NAME = TSTA2)
    )
  )

TSTA1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipalpha1.psoug.org)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTA)
      (INSTANCE_NAME = TSTA1)
    )
  )

TSTB_TAF =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipbeta1.psoug.org)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipbeta2.psoug.org)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTB_TAF)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

TSTB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipbeta1.psoug.org)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipbeta2.psoug.org)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTB)
    )
  )

TSTB4 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipbeta4.psoug.org)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTB)
      (INSTANCE_NAME = TSTB4)
    )
  )

TSTB3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipbeta3.psoug.org)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTB)
      (INSTANCE_NAME = TSTB3)
    )
)

TSTB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipbeta2.psoug.org)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTB)
      (INSTANCE_NAME = TSTB2)
    )
)

TSTB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipbeta1.psoug.org)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTB)
      (INSTANCE_NAME = TSTB1)
    )
  )

TSTCD_TAF =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipgamma1.psoug.org)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipgamma2.psoug.org)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipgamma3.psoug.org)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipgamma4.psoug.org)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTC_TAF)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

TSTC_TAF =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipgamma1.psoug.org)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipgamma2.psoug.org)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTC_TAF)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

TSTCD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipgamma1.psoug.org)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipgamma2.psoug.org)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipgamma3.psoug.org)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipgamma4.psoug.org)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTC)
    )
  )

TSTC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipgamma1.psoug.org)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipgamma2.psoug.org)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTC)
    )
 )

TSTC4 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipgamma4.psoug.org)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTC)
      (INSTANCE_NAME = TSTC4)
    )
  )

TSTC3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipgamma3.psoug.org)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTC)
      (INSTANCE_NAME = TSTC3)
    )
  )

TSTC2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipgamma2.psoug.org)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTC)
      (INSTANCE_NAME = TSTC2)
    )
  )

TSTC1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipgamma1.psoug.org)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTC)
      (INSTANCE_NAME = TSTC1)
    )
  )

TSTD_TAF =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipdelta1.psoug.org)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipdelta2.psoug.org)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTD_TAF)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

TSTD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipdelta1.psoug.org)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipdelta2.psoug.org)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTD)
    )
  )

TSTD4 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipdelta4.psoug.org)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTD)
      (INSTANCE_NAME = TSTD4)
    )
  )

TSTD3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipdelta3.psoug.org)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTD)
      (INSTANCE_NAME = TSTD3)
    )
  )

TSTD2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipdelta2.psoug.org)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTD)
      (INSTANCE_NAME = TSTD2)
    )
  )

TSTD1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vipdelta1.psoug.org)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TSTD)
      (INSTANCE_NAME = TSTD1)
    )
  )

TNSNAMES.ORA
10.1.0.4
Mac OS X
ORCL4 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-rac4-vip)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (INSTANCE_NAME = orcl4)
    )
  )

ORCL3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-rac3-vip)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (INSTANCE_NAME = orcl3)
    )
  )

ORCL2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-rac2-vip)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (INSTANCE_NAME = orcl2)
    )
  )

ORCL1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-rac1-vip)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (INSTANCE_NAME = orcl1)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-rac1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-rac2-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-rac3-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-rac4-vip)(PORT = 1521))
      (LOAD_BALANCE = yes)
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

BAKBONE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bakbone-oracle)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bakbone)
    )
  )
Verify TAF Status SELECT machine, failover_type, failover_method, failed_over, COUNT(*)
FROM gv$session
GROUP BY machine, failover_type, failover_method, failed_over;
 
Creating a Time Server

Time Server Setup

RedHat Linux
$ vi ntp.conf

# Prohibit general access to the ntpd service
restrict default ignore

# Permit loopback access
restrict 127.0.0.1

# --- CLIENT NETWORK ---
# Permit systems on the 192.168.1 network to use the service
# Do not use those systems as peers for synchronization
restrict 192.168.1.0 mask 255.255.255.0 notrust nomodify notrap

# --- NTP MULTICASTCLIENT ---
multicastclient
restrict 244.0.1.1 mask 255.255.255.255 notrust nomodify notrap
restrict 192.168.1.0 mask 255.255.255.0 notrust nomodify notrap

# --- GENERAL CONFIGURATION ---
server 127.127.1.0             #local clock
fudge  127.127.1.0 stratum 10

driftfile /var/lib/ntp/drift
broadcstdelay 0.008
authenticate yes
keys /etc/ntp/keys

Time Server Management

RedHat Linux
# chkconfig ntpd on

-- To start, stop, and restart NTP
# service ntpd start
# service ntpd stop
# service ntpd restart

-- To verify NTP is running
# pgrep ntpd
 
Miscellaneous

Log sync events
In a RAC environment, cache fusion transfers sometimes causes the log file sync event. This is due to the write ahead logging mechanism. When a foreground process requests a block from another instance, all the redo entries associated with the block must be flushed to disk prior to transferring the block. In this case, there is no commit involved, but buffer cahe synchronization and waits on the log file sync event.

Max commit propagation delay
Max Commit Propagation Delay (MCPD) tells which algorithm to choose to propagate commits in a RAC environment. When you set it to default (10.1 and earlier) 700 cs, it uses lamport algorithm for SCN propagation. Setting this to lower values enables the Broadcast on Commit (BOC) which is little bit more expensive on messages, but commits are visible immediately to the other nodes.

In current versions (10.2 and above) the BOC algorithms are optimized and is the default SCN propagation mechanism.

TAF Restart

Comments from Kuassi Mensah
TAF will not always successfully restart a query in progress. It reopens the cursors and attempts to discard rows already returned. In order to achieve that, it performs a chekcsum of to-be-discarded rows and compares that checksum against a checksum for the rows already returned. If the checksums are different, TAF knows the  discarded rows are not the same as the rows already returned. In such a case, it will not resume returning rows and returns an error. Checksum discrepancies are more likely to happen with replica databases which are not block-for-block identical.

There are some queries that will not survive failover. OCI implicitly replays the query on the surviving instance using the original SCN and discards the rows that were previously returned. If, after failover, the same set of rows is not returned, then we throw an error indicating that the query must be re-executed. With use of the original SCN *and* the checksum verification on the re-fetched rows, it is unlikely that OCI will erroneously re-return rows that had been
previously seen.

PreparedStatement and their cursor are re-openned/executed. However If the application uses any stored
procedures (i.e., CallableStatement), then the state of those procedures is lost after failover.

The surviving PreparedStatement assumes that the Statement handle's bind values have not changed but this unlikey as different bind may return a different result set. At any rate, OCI transparently replays the execute-SQL/fetch during TAF assuming same set of rows is returned from server.

If there is a failure you will most likely see an ORA-25402 error. Applications should be prepared to handle errors in the 25400-25425 range and rollback appropriately.
Down converts query SELECT inst_id, cr_requests, light_works, data_requests, fairness_down_converts
FROM gv$cr_block_server;
 
Related Topics
ASM
DBMS_CLUSTDB
DBMS_HA_ALERTS
DBMS_SCHEMA_COPY
DBMS_SERVICE
DBMS_UTILITY
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [137 users online]    © 2010 psoug.org