Redo log corruption

Recovering After the Loss of Online Redo Log Files: Scenarios

If a media failure has affected the online redo logs of a database, then the appropriate recovery procedure depends on the following:

• The configuration of the online redo log: mirrored or non-mirrored
• The type of media failure: temporary or permanent
• The types of online redo log files affected by the media failure: current, active, unarchived, or inactive

Table 19-1 displays V$LOG status information that can be crucial in a recovery situation involving online redo logs.

Table 19-1 STATUS Column of V$LOG

Status	Description
UNUSED	The online redo log has never been written to.
CURRENT	The online redo log is active, that is, needed for instance recovery, and it is the log to which the database is currently writing. The redo log can be open or closed.
ACTIVE	The online redo log is active, that is, needed for instance recovery, but is not the log to which the database is currently writing.It may be in use for block recovery, and may or may not be archived.
CLEARING	The log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, then the status changes to UNUSED.
CLEARING_CURRENT	The current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
INACTIVE	The log is no longer needed for instance recovery. It may be in use for media recovery, and may or may not be archived.

Recovering After Losing a Member of a Multiplexed Online Redo Log Group

If the online redo log of a database is multiplexed, and if at least one member of each online redo log group is not affected by the media failure, then the database continues functioning as normal, but error messages are written to the log writer trace file and the alert_SID.log of the database.

Solve the problem by taking one of the following actions:

• If the hardware problem is temporary, then correct it. The log writer process accesses the previously unavailable online redo log files as if the problem never existed.
• If the hardware problem is permanent, then drop the damaged member and add a new member by using the following procedure.

Note:

The newly added member provides no redundancy until the log group is reused.

1. Locate the filename of the damaged member in V$LOGFILE. The status is INVALID if the file is inaccessible:

2.  SELECT GROUP#, STATUS, MEMBER

3.  FROM V$LOGFILE

4.  WHERE STATUS='INVALID';

5.   

6.  GROUP#    STATUS       MEMBER

7.  -------   -----------  ---------------------

8.  0002      INVALID      /oracle/oradata/trgt/redo02.log

9.   

10. Drop the damaged member. For example, to drop member redo01.log from group 2, issue:

11. ALTER DATABASE DROP LOGFILE MEMBER '/oracle/oradata/trgt/redo02.log';

12.  

13. Add a new member to the group. For example, to add redo02.log to group 2, issue:

14. ALTER DATABASE ADD LOGFILE MEMBER '/oracle/oradata/trgt/redo02b.log'

15.   TO GROUP 2;

16.  

If the file you want to add already exists, then it must be the same size as the other group members, and you must specify REUSE. For example:

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/oradata/trgt/redo02b.log'

  REUSE TO GROUP 2;

Recovering After the Loss of All Members of an Online Redo Log Group

If a media failure damages all members of an online redo log group, then different scenarios can occur depending on the type of online redo log group affected by the failure and the archiving mode of the database.

If the damaged log group is active, then it is needed for crash recovery; otherwise, it is not.

If the group is . . .	Then . . .	And you should . . .
Inactive	It is not needed for crash recovery	Clear the archived or unarchived group.
Active	It is needed for crash recovery	Attempt to issue a checkpoint and clear the log; if impossible, then you must restore a backup and perform incomplete recovery up to the most recent available redo log.
Current	It is the log that the database is currently writing to	Attempt to clear the log; if impossible, then you must restore a backup and perform incomplete recovery up to the most recent available redo log.

Your first task is to determine whether the damaged group is active or inactive.

1. Locate the filename of the lost redo log in V$LOGFILE and then look for the group number corresponding to it. For example, enter:

2.  SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;

3.   

4.  GROUP#    STATUS       MEMBER

5.  -------   -----------  ---------------------

6.  0001                    /oracle/dbs/log1a.f

7.  0001                    /oracle/dbs/log1b.f

8.  0002      INVALID       /oracle/dbs/log2a.f

9.  0002      INVALID       /oracle/dbs/log2b.f

10. 0003                    /oracle/dbs/log3a.f

11. 0003                    /oracle/dbs/log3b.f

12.  

13. Determine which groups are active. For example, enter:

14. SELECT GROUP#, MEMBERS, STATUS, ARCHIVED

15. FROM V$LOG;

16.  

17. GROUP#  MEMBERS           STATUS     ARCHIVED

18. ------  -------           ---------  -----------

19.  0001   2                 INACTIVE   YES

20.  0002   2                 ACTIVE     NO

21.  0003   2                 CURRENT    NO

22.  

23. If the affected group is inactive, follow the procedure in Losing an Inactive Online Redo Log Group. If the affected group is active (as in the preceding example), then follow the procedure in "Losing an Active Online Redo Log Group".

Losing an Inactive Online Redo Log Group

If all members of an online redo log group with INACTIVE status are damaged, then the procedure depends on whether you can fix the media problem that damaged the inactive redo log group.

If the failure is . . .	Then . . .
Temporary	Fix the problem. LGWR can reuse the redo log group when required.
Permanent	The damaged inactive online redo log group eventually halts normal database operation. Reinitialize the damaged group manually by issuing the ALTER DATABASE CLEAR LOGFILE statement as described in this section.

Clearing Inactive, Archived Redo

You can clear an inactive redo log group when the database is open or closed. The procedure depends on whether the damaged group has been archived.

To clear an inactive, online redo log group that has been archived, use the following procedure:

1. If the database is shut down, then start a new instance and mount the database:

2.  STARTUP MOUNT

3.   

4. Reinitialize the damaged log group. For example, to clear redo log group 2, issue the following statement:

5.  ALTER DATABASE CLEAR LOGFILE GROUP 2;

Clearing Inactive, Not-Yet-Archived Redo

Clearing a not-yet-archived redo log allows it to be reused without archiving it. This action makes backups unusable if they were started before the last change in the log, unless the file was taken offline prior to the first change in the log. Hence, if you need the cleared log file for recovery of a backup, then you cannot recover that backup. Also, it prevents complete recovery from backups due to the missing log.

To clear an inactive, online redo log group that has not been archived, use the following procedure:

1. If the database is shut down, then start a new instance and mount the database:

2.  STARTUP MOUNT

3.   

4. Clear the log using the UNARCHIVED keyword. For example, to clear log group 2, issue:

5.  ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 2;

6.   

If there is an offline datafile that requires the cleared log to bring it online, then the keywords UNRECOVERABLE DATAFILE are required. The datafile and its entire tablespace have to be dropped because the redo necessary to bring it online is being cleared, and there is no copy of it. For example, enter:

ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 2 UNRECOVERABLE DATAFILE;

7. Immediately back up the whole database with an operating system utility, so that you have a backup you can use for complete recovery without relying on the cleared log group. For example, enter:

8.  % cp /disk1/oracle/dbs/*.f /disk2/backup

9.   

10. Back up the database's control file with the ALTER DATABASE statement. For example, enter:

11. ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/dbs/cf_backup.f';

Failure of CLEAR LOGFILE Operation

The ALTER DATABASE CLEAR LOGFILE statement can fail with an I/O error due to media failure when it is not possible to:

• Relocate the redo log file onto alternative media by re-creating it under the currently configured redo log filename
• Reuse the currently configured log filename to re-create the redo log file because the name itself is invalid or unusable (for example, due to media failure)

In these cases, the ALTER DATABASE CLEAR LOGFILE statement (before receiving the I/O error) would have successfully informed the control file that the log was being cleared and did not require archiving. The I/O error occurred at the step in which the CLEAR LOGFILE statement attempts to create the new redo log file and write zeros to it. This fact is reflected in V$LOG.CLEARING_CURRENT.

Losing an Active Online Redo Log Group

If the database is still running and the lost active redo log is not the current log, then issue the ALTER SYSTEM CHECKPOINT statement. If successful, then the active redo log becomes inactive, and you can follow the procedure in"Losing an Inactive Online Redo Log Group". If unsuccessful, or if your database has halted, then perform one of procedures in this section, depending on the archiving mode.

The current log is the one LGWR is currently writing to. If a LGWR I/O fails, then LGWR terminates and the instance crashes. In this case, you must restore a backup, perform incomplete recovery, and open the database with theRESETLOGS option.

To recover from loss of an active online log group in NOARCHIVELOG mode:

1. If the media failure is temporary, then correct the problem so that the database can reuse the group when required.
2. Restore the database from a consistent, whole database backup (datafiles and control files) as described in "Restoring Datafiles Before Performing Incomplete Recovery". For example, enter:

3.  % cp /disk2/backup/*.dbf $ORACLE_HOME/oradata/trgt/

4.   

5. Mount the database:

6.  STARTUP MOUNT

7.   

8. Because online redo logs are not backed up, you cannot restore them with the datafiles and control files. In order to allow the database to reset the online redo logs, you must first mimic incomplete recovery:

9.  RECOVER DATABASE UNTIL CANCEL

10. CANCEL

11.  

12. Open the database using the RESETLOGS option:

13. ALTER DATABASE OPEN RESETLOGS;

14.  

15. Shut down the database consistently. For example, enter:

16. SHUTDOWN IMMEDIATE

17.  

18. Make a whole database backup.

To recover from loss of an active online redo log group in ARCHIVELOG mode:

If the media failure is temporary, then correct the problem so that the database can reuse the group when required. If the media failure is not temporary, then use the following procedure.

1. Begin incomplete media recovery, recovering up through the log before the damaged log.
2. Ensure that the current name of the lost redo log can be used for a newly created file. If not, then rename the members of the damaged online redo log group to a new location. For example, enter:

3.  ALTER DATABASE RENAME FILE "?/oradata/trgt/redo01.log" TO "/tmp/redo01.log";

4.  ALTER DATABASE RENAME FILE "?/oradata/trgt/redo01.log" TO "/tmp/redo02.log";

5.   

6. Open the database using the RESETLOGS option:

7.  ALTER DATABASE OPEN RESETLOGS;

Note:

All updates executed from the endpoint of the incomplete recovery to the present must be re-executed.

Loss of Multiple Redo Log Groups

If you have lost multiple groups of the online redo log, then use the recovery method for the most difficult log to recover. The order of difficulty, from most difficult to least difficult, follows:

1. The current online redo log
2. An active online redo log
3. An unarchived online redo log
4. An inactive online redo log

Oracle RAC interview questions and answers

RAC :

1-Difference between 11gr1 and 11gr2

Well, there is not much difference between 10g and 11gR (1) RAC.

But there is a significant difference in 11gR2.

Prior to 11gR1(10g) RAC, the following were managed by Oracle CRS

• Databases
• Instances
• Applications
• Node Monitoring
• Event Services
• High Availability

From 11gR2(onwards) its completed HA stack managing and providing the following resources as like the other cluster software like VCS etc.

• Databases
• Instances
• Applications
• Cluster Management
• Node Management
• Event Services
• High Availability
• Network Management (provides DNS/GNS/MDNSD services on behalf of other traditional services) and SCAN – Single Access Client Naming method, HAIP
• Storage Management (with help of ASM and other new ACFS filesystem)
• Time synchronization (rather depending upon traditional NTP)
• Removed OS dependent hang checker etc, manages with own additional monitor process

racle 9i RAC

• OPS (Oracle Parallel Server) was renamed as RAC
• CFS (Cluster File System) was supported
• OCFS (Oracle Cluster File System) for Linux and Windows
• watchdog timer replaced by hangcheck timer

Oracle 10g R1 RAC

• Cluster Manager replaced by CRS
• ASM introduced
• Concept of Services expanded
• ocrcheck introduced
• ocrdump introduced
• AWR was instance specific

Oracle 10g R2 RAC

• CRS was renamed as Clusterware
• asmcmd introduced
• CLUVFY introduced
• OCR and Voting disks can be mirrored
• Can use FAN/FCF with TAF for OCI and ODP.NET

Oracle 11g R1 RAC

• Oracle 11g RAC parallel upgrades - Oracle 11g have rolling upgrade features whereby RAC database can be upgraded without any downtime.
• Hot patching - Zero downtime patch application.
• Oracle RAC load balancing advisor - Starting from 10g R2 we have RAC load balancing advisor utility. 11g RAC load balancing advisor is only available with clients who use .NET, ODBC, or the Oracle Call Interface (OCI).
• ADDM for RAC - Oracle has incorporated RAC into the automatic database diagnostic monitor, for cross-node advisories. The script addmrpt.sql run give report for single instance, will not report all instances in RAC, this is known as instance ADDM. But using the new package DBMS_ADDM, we can generate report for all instances of RAC, this known as database ADDM.
• Optimized RAC cache fusion protocols - moves on from the general cache fusion protocols in 10g to deal with specific scenarios where the protocols could be further optimized.
• Oracle 11g RAC Grid provisioning - The Oracle grid control provisioning pack allows us to "blow-out" a RAC node without the time-consuming install, using a pre-installed "footprint".

Oracle 11g R2 RAC

• We can store everything on the ASM. We can store OCR & voting files also on the ASM.
• ASMCA
• Single Client Access Name (SCAN) - eliminates the need to change tns entry when nodes are added to or removed from the Cluster. RAC instances register to SCAN listeners as remote listeners. SCAN is fully qualified name. Oracle recommends assigning 3 addresses to SCAN, which create three SCAN listeners.
• Clusterware components: crfmond, crflogd, GIPCD.
• AWR is consolidated for the database.
• 11g Release 2 Real Application Cluster (RAC) has server pooling technologies so it’s easier to provision and manage database grids. This update is geared toward dynamically adjusting servers as corporations manage the ebb and flow between data requirements for datawarehousing and applications.
• By default, LOAD_BALANCE is ON.
• GSD (Global Service Deamon), gsdctl introduced.
• GPnP profile.
• Cluster information in an XML profile.
• Oracle RAC OneNode is a new option that makes it easier to consolidate databases that aren’t mission critical, but need redundancy.
• raconeinit - to convert database to RacOneNode.
• raconefix - to fix RacOneNode database in case of failure.
• racone2rac - to convert RacOneNode back to RAC.
• Oracle Restart - the feature of Oracle Grid Infrastructure's High Availability Services (HAS) to manage associated listeners, ASM instances and Oracle instances.
• Oracle Omotion - Oracle 11g release2 RAC introduces new feature called Oracle Omotion, an online migration utility. This Omotion utility will relocate the instance from one node to another, whenever instance failure happens.
• Omotion utility uses Database Area Network (DAN) to move Oracle instances. Database Area Network (DAN) technology helps seamless database relocation without losing transactions.
• Cluster Time Synchronization Service (CTSS) is a new feature in Oracle 11g R2 RAC, which is used to synchronize time across the nodes of the cluster. CTSS will be replacement of NTP protocol.
• Grid Naming Service (GNS) is a new service introduced in Oracle RAC 11g R2. With GNS, Oracle Clusterware (CRS) can manage Dynamic Host Configuration Protocol (DHCP) and DNS services for the dynamic node registration and configuration.
• Cluster interconnect: Used for data blocks, locks, messages, and SCN numbers.
• Oracle Local Registry (OLR) - From Oracle 11gR2 "Oracle Local Registry (OLR)" something new as part of Oracle Clusterware. OLR is node’s local repository, similar to OCR (but local) and is managed by OHASD. It pertains data of local node only and is not shared among other nodes.
• Multicasting is introduced in 11gR2 for private interconnect traffic.
• I/O fencing prevents updates by failed instances, and detecting failure and preventing split brain in cluster. When a cluster node fails, the failed node needs to be fenced off from all the shared disk devices or diskgroups. This methodology is called I/O Fencing, sometimes called Disk Fencing or failure fencing.
• Re-bootless node fencing (restart)‏ - instead of fast re-booting the node, a graceful shutdown of the stack is attempted.
• Clusterware log directories: acfs*
• HAIP (IC VIP).
• Redundant interconnects: NIC bonding, HAIP.
• RAC background processes: DBRM – Database Resource Manager, PING – Response time agent.
• Virtual Oracle 11g RAC cluster - Oracle 11g RAC supports virtualization.
• Additionally, in Version 11g R2 with ASMCMD, you can manage an ASM instance and disk group operations such as:
• • Instance startup/shutdown
• • Set the disk discovery paths
• • Create a disk group
• • Drop a disk group

2-Cache fusion

-We know that every instance of the RAC database has its own local buffer cache which performs the usual cache functionality for that instance. Now there could be occasions when a transaction/user on instance A needs to access a data block which is being owned/locked by the other instance B. In such cases, the instance A will request instance B for that data block and hence accesses the block through the interconnect mechanism. This concept is known as CACHE FUSION where one instance can work on or access a data block in other instance?s cache via the high speed interconnect.  

-Global Cache Service (GCS) is the heart of Cache Fusion concept. It is through GCS that data integrity in RAC is maintained when more than one instance need a particular data block.

-CACHE FUSION helps resolve all the possible contentions that could happen between instances in a RAC setup.   There are 3 possible contentions in a RAC setup which we are going to discuss in detail here with a mention of cache fusion where ever applicable.

1.      Read/Read contention:  Read-Read contention might not be a problem at all because the table/row will be in a shared lock mode for both transactions and none of them is trying an exclusive lock anyways.

2.      Read/Write contention:   This one is interesting. 

Here is more about this contention and how the concept of cache fusion helps resolve this contention

1.     A data block is in the buffer cache of instance A and is being updated. An exclusive lock has been acquired on it.

2.     After some time instance B is interested in reading that same data block and hence sends a request to GCS. So far so good ? Read/Write contention has been induced

3.     GCS checks the availability of that data block and finds that instance A has acquired an exclusive lock. Hence, GCS asks instance A to release the block for instance B.

4.     Now there are two options ? either instance A releases the lock on that block (if it no longer needs it) and lets instance B read the block from the disk OR instance A creates a CR image of the block in its own buffer cache and ships it to the requesting instance via interconnect

5.     The holding instance notifies the GCS accordingly (if the lock has been released or the CR image has been shipped)

6.     Creation of CR image, shipping it to the requesting instance and involvement of GCS is where CACHE FUSION comes into play

3.     Write/Write contention:  

This is the case where both instance A as well as B are trying to acquire an exclusive lock on the data block. A data block is in the buffer cache of instance A and is being updated. An exclusive lock has been acquired on it

1.     Instance B send the data block request to the GCS

2.     GCS checks the availability of that data block and finds that instance A has acquired an exclusive lock. Hence, GCS asks instance A to release the block for instance B

3.     There are 2 options - either instance A releases the lock on that block (if it no longer needs it) and lets instance B read the block from the disk OR instance A creates a PI image of the block in its own buffer cache, makes the redo entries and ships the block to the requesting instance via interconnect

4.     Holding instance also notifies the GCS that lock has been released and a PI has been preserved

5.     Instance B now acquires the exclusive lock on that block and continues with its normal processing. At this point GCS records that data block is now with instance B

6.     The whole mechanism of resolving this contention with the due involvement of GCS is attributed to the CACHE FUSION.

 -Global Enqueue Services (GES) is responsible for managing locks across the cluster. As a side note, GES was previously called the Distributed Lock Manager (DLM). When Oracle introduced their first cluster on DEC VAX systems, the clustered database used VAX?s cluster lock manager but it was not designed for the demands of a transactional database and did not scale well. Oracle designed the DLM to have a scalable global lock manager. The DLM still persists in many publications.

-for Oracle RAC, we need the Buffer Caches on all instances to appear to be global across the cluster. 

The processes running to support an Oracle RAC instance include:

?         LMS:  This process is GCS. This process used to be called the Lock Manager Server.

?         LMON:  The Lock Monitor. This process is the GES master process. 

?         LMD:  The Lock Manager Daemon. This process manages incoming lock requests.  

?         LCK0: The instance enqueue process. This process manages lock requests for library cache objects.

3-Background process-

ACMS    — Atomic Control file to Memory Service (ACMS)
GTX0-j  — Global Transaction Process
LMON    — Global Enqueue Service Monitor
LMD     — Global Enqueue Service Daemon
LMS     — Global Cache Service Process
LCK0    — Instance Enqueue Process
DIAG    — Diagnosability Daemon
RMSn    — Oracle RAC Management Processes (RMSn)
RSMN    — Remote Slave Monitor
DBRM    — Database Resource Manager (from 11g R2)
PING    — Response Time Agent (from 11g R2)

ACMS:  Atomic Controlfile to Memory Service (ACMS)

In RAC ACMS per-instance process ensure a distributed SGA memory update is either globally committed if success or globally aborted if a failure occurs.

GTX0-j: Global Transaction Process

The GTX0-j process provides transparent support for XA global transactions.

The database autotunes the number of these processes based on the workload of XA global transactions.

LMON: Global Enqueue Service Monitor

The LMON process monitors global enqueues and resources across the cluster and performs global enqueue recovery operations.

LMD: Global Enqueue Service Daemon

The LMD process manages incoming remote resource requests within each instance.

LMS: Global Cache Service Process

The LMS process maintains records of the data file statuses and each cached block by recording in a Global Resource Directory (GRD). Also controls the flow of messages to remote instances and manages global data block access. Transmits block images between the buffer caches of different  instances. This processing is part of the Cache Fusion feature.

LCK0: Instance Enqueue Process

The LCK0 process manages non-Cache Fusion resource requests such as library and row cache requests.

RMSn: Oracle RAC Management Processes (RMSn)

The RMSn processes perform manageability tasks for RAC. Tasks accomplished by an RMSn process include creation of resources related to Oracle RAC when new instances are added to the clusters.

RSMN: Remote Slave Monitor manages background slave process creation and communication on remote instances. These background slave processes perform tasks on behalf of a coordinating process running in another instance.

11gr2 background process—

Process Name	Functionality
crsd	•The CRS daemon (crsd) manages cluster resources based on configuration information that is stored in Oracle Cluster Registry (OCR) for each resource. This includes start, stop, monitor, and failover operations. The crsd process generates events when the status of a resource changes.
cssd	•Cluster Synchronization Service (CSS): Manages the cluster configuration by controlling which nodes are members of the cluster and by notifying members when a node joins or leaves the cluster. If you are using certified third-party clusterware, then CSS processes interfaces with your clusterware to manage node membership information. CSS has three separate processes: the CSS daemon (ocssd), the CSS Agent (cssdagent), and the CSS Monitor (cssdmonitor). The cssdagent process monitors the cluster and provides input/output fencing. This service formerly was provided by Oracle Process Monitor daemon (oprocd), also known as OraFenceService on Windows. A cssdagent failure results in Oracle Clusterware restarting the node.
diskmon	•Disk Monitor daemon (diskmon): Monitors and performs input/output fencing for Oracle Exadata Storage Server. As Exadata storage can be added to any Oracle RAC node at any point in time, the diskmon daemon is always started when ocssd is started.
evmd	•Event Manager (EVM): Is a background process that publishes Oracle Clusterware events
mdnsd	•Multicast domain name service (mDNS): Allows DNS requests. The mDNS process is a background process on Linux and UNIX, and a service on Windows.
gnsd	•Oracle Grid Naming Service (GNS): Is a gateway between the cluster mDNS and external DNS servers. The GNS process performs name resolution within the cluster.
ons	•Oracle Notification Service (ONS): Is a publish-and-subscribe service for communicating Fast Application Notification (FAN) events
oraagent	•oraagent: Extends clusterware to support Oracle-specific requirements and complex resources. It runs server callout scripts when FAN events occur. This process was known as RACG in Oracle Clusterware 11g Release 1 (11.1).
orarootagent	•Oracle root agent (orarootagent): Is a specialized oraagent process that helps CRSD manage resources owned by root, such as the network, and the Grid virtual IP address
oclskd	•Cluster kill daemon (oclskd): Handles instance/node evictions requests that have been escalated to CSS
gipcd	•Grid IPC daemon (gipcd): Is a helper daemon for the communications infrastructure
ctssd	•Cluster time synchronisation daemon(ctssd) to manage the time syncrhonization between nodes, rather depending on NTP

4-Different parameters in init.ora files.

There are three types of initialization parameters in the Real Application Clusters environment:

* Parameters that must be Identical across all Instances
* Parameters that must be Unique across all Instances 
* Multi-Valued Parameters

Parameters that must be Identical across all Instances

Specify these parameter values in the SPFILE, or within the individual PFILEs for each instance. The following list contains the parameters that must be identical on every instance:

·         ACTIVE_INSTANCE_COUNT

·         ARCHIVE_LAG_TARGET

·         CLUSTER_DATABASE

·         CLUSTER_DATABASE_INSTANCES

·         COMPATIBLE

·         CONTROL_FILES

·         DB_BLOCK_SIZE

·         DB_DOMAIN

·         DB_FILES

·         DB_NAME

·         DB_RECOVERY_FILE_DEST

·         DB_RECOVERY_FILE_DEST_SIZE

·         DB_UNIQUE_NAME

·         INSTANCE_TYPE (RDBMS or ASM)

·         PARALLEL_MAX_SERVERS

·         REMOTE_LOGIN_PASSWORD_FILE

·         UNDO_MANAGEMENT

The setting for DML_LOCKS must be identical on every instance only if set to zero.

Unique Parameters (across instances)

In this category of parameters are uniquely identified for a particular instance. They specify the identifiers of the independent instance, and give independent characteristics to an instance

·         instance_name :- Unique name of the instance.

·         instance_number:- unique number that maps the instance

·         rollback_segments:- one or more rollback segments by name to this instance.

·         thread :- number of the redo thread to be used by an instance.

·         undo_tablespace:- undo tablespace to be used when an instance starts up.

·         Note :- If your SPFILE contains instance-specific settings, then these settings take precedence over settings made with ALTER SYSTEMcommands.

·         SPFILE parameter values and RAC

·         You can change parameter settings using the 
ALTER SYSTEM SET command from any instance

·         ALTER SYSTEM SET scope=memory sid=’’;

·         To remove an entry from your spfile
ALTER SYSTEM RESET scope=spfile sid=’’;

5-OCR and voting disk and their backup recovery

OCR: It created at the time of Grid Installation. It’s store information to manage Oracle cluster-ware and it’s component such as RAC database, listener, VIP,Scan IP & Services.
Minimum 1 and maximum 5 copy of OCR is possible.
Voting Disk: It manage information about node membership. Each voting disk must be accessible by all nodes in the cluster.If any node is not passing heat-beat across other note or voting disk, then that node will be evicted by Voting disk.
Minimum 1 and maximum 15 copy of voting disk is possible.

New Facts:

• We can store OCR And Voting disk on ASM or certified cluster file system.
• We can dynamically add or replace voting disk & OCR.
• Backup of Voting disk using “dd” command not supported.
• Voting disk and OCR can be keep in same disk-group or different disk-group
• Voting disk and OCR automatic backup kept together in a single file.
• Automatic backup of Voting disk and OCR happen after every four hours, end of the day, end of the week
• You must have root or sudo privilege account to manage it.

To find current location of Voting disk:
[oracle@rsingle ~]$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
—  —–    —————–                ——— ———
1. ONLINE   6a60a2c3510c4fbfbff62dcdc279b247 (ORCL:DATA1) [DATA]

Relocate or multiplexing Voting disk to another disk-group (With normal redundancy)

[root@rsingle ~]# /u01/app/11.2.0/grid/bin/crsctl replace votedisk +CRS
Successful addition of voting disk afb77b2693a24f1ebfe876784103e82a.
Successful addition of voting disk 3e2542c5b1154ffdbfc8b6dea7dce390.
Successful addition of voting disk 8e0f3c5921cc4f93bf223de1465d83cc.
Successful deletion of voting disk 6a60a2c3510c4fbfbff62dcdc279b247.
Successfully replaced voting disk group with +CRS.
CRS-4266: Voting file(s) successfully replaced

New location of Voting disk:
[root@rsingle ~]# /u01/app/11.2.0/grid/bin/crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
—  —–    —————–                ——— ———
1. ONLINE   afb77b2693a24f1ebfe876784103e82a (ORCL:DATA2) [CRS]
2. ONLINE   3e2542c5b1154ffdbfc8b6dea7dce390 (ORCL:DATA3) [CRS]
3. ONLINE   8e0f3c5921cc4f93bf223de1465d83cc (ORCL:DATA4) [CRS]
Located 3 voting disk(s).

To find location of Corrent OCR:

[oracle@rsingle ~]$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version                  :          3
Total space (kbytes)     :     262120
Used space (kbytes)      :       2964
Available space (kbytes) :     259156
ID                       : 1390115973
Device/File Name         :      +DATA
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check bypassed due to non-privileged user
Create mirror copy of OCR online
[oracle@rsingle ~]$ sudo ocrconfig -add +CRS
Password:

Check location of OCR after mirror copy creation:
[root@rsingle ~]# /u01/app/11.2.0/grid/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version                  :          3
Total space (kbytes)     :     262120
Used space (kbytes)      :       2964
Available space (kbytes) :     259156
ID                       : 1390115973
Device/File Name         :      +DATA
Device/File integrity check succeeded
Device/File Name         :       +CRS
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

Another file to find location of OCR:

[root@rsingle ~]# cat /etc/oracle/ocr.loc
#Device/file  getting replaced by device +CRS
ocrconfig_loc=+DATA
ocrmirrorconfig_loc=+CRS

6-Node eviction/Split brain/Fencing/STONITH(Shoot the node in the head):

-Split-brain scenarios in a RAC cluster can be defined as functional overlapping of separate instances, each in its own world without any guaranteed consistency or coordination between them.

-Basically, communication is lost among the nodes of a cluster, with them being evicted/kicked out of the cluster,resulting in node/instance reboots. This can happen due to a variety of reasons ranging from hardware failure on the private cluster interconnect to nodes becoming unresponsive because of CPU/memory starvation issues.

Ø Establish redundancy at the networking tier : redundant switches/Network Interface Cards

(NICs) trunked/bonded/teamed together.

Ø Allocate enough CPU for the application workloads and establish limits for CPU consumption.

Basically, CPU exhaustion can lead to a point where the node becomes unresponsive to the

other nodes of the cluster, resulting in a split-brain scenario leading in turn to node evictions.

Ø Allocate enough memory for the various applications and establish limits for memory

consumption. Automatic Memory Management comes in handy, as it puts limits on both

SGA and Program Global Area (PGA) areas.

Ø Employ/deploy DBRM along with IORM (if you are operating RAC clusters on Exadata).

Ø Set up and configure instance caging (CPU_COUNT parameter) for multi-tenant database

RAC nodes; monitor and watch out for RESMGR:CPU quantum waits related to instance

caging, especially when instances have been  overconsolidated.

Ø Ensure that any kind of antivirus software is not active/present on any of the RAC nodes of

the cluster.

Ø Patch to the latest versions of the Oracle Database software. Many bugs have been associated

with various versions that are known to cause split-brain scenarios to occur. Staying current

with the latest CPU/PSUs is known to mitigate stability/performance issues.

Ø Avoid allocating/configuring an excessive no. of LMS_PROCESSES. LMS is a CPU-intensive

process, and if not configured properly, can cause CPU starvation to occur very rapidly,

ultimately resulting in node evictions.

Ø Partition large objects to reduce I/O and improve overall performance.

Ø Parallelization and AUTO DOP: Set up/configure/tune carefully. Turning on Automatic

Degree of Parallelism (AUTO DOP—PARALLEL_DEGREE_POLICY= AUTO) can have negative

consequences on RAC performance.

NODE EVICTION : -

-A node eviction is the mechanism/process (piece of code) designed within the Oracle Clusterware to ensure cluster consistency and maintain overall cluster health by removing the node(s) that either suffers critical issues or doesn’t respond to other nodes’ requests in the cluster in a timely manner.

-For example, when a node in the cluster is hung or suffering critical problems, such as network latency or disk latency to maintain the heartbeat rate within the internaltimeout value, or if the cluster stack or clusterware is unhealthy, the node will leave the cluster and do a fast selfrebootto ensure overall cluster health.

-When a node doesn’t respond to another node’s request in a timely manner,the node will receive a position packet through disk/network with the instruction to leave the cluster by killing itself.When the problematic node reads the position (kill) pockets, it will evict and leave the cluster.

Node Evictions—Top/Common Causes and Factors

The following are only a few of the most common symptoms/factors that lead to node evictions, cluster stack sudden death, reboots, and status going unhealthy:

·        Network disruption, latency, or missing network heartbeats

• Delayed or missing disk heartbeats

• Corrupted network packets on the network may also cause CSS reboots on certain platforms

• Slow interconnect or failures

• Known Oracle Clusterware bugs

• Unable to read/write or access the majority of the voting disks (files)

• Lack of sufficient resource (CPU/memory starvation) availability on the node for OS

scheduling by key CRS daemon processes

• Manual termination of the critical cluster stack daemon background processes

(css, cssdagent, cssdmonitor)

• No space left on the device for the GI or /var file system

• Sudden death or hang of CSSD process

• ORAAGENT/ORAROOTAGENT excessive resource (CPU, MEMORY, SWAP) consumption resulting in

node eviction on specific OS platforms

7-TAF ,FAN ,ONS

1) TAF with tnsnames
a feature of Oracle Net Services for OCI8 clients. TAF is transparent application failover which will move a session to a backup connection if the session fails. With Oracle 10g Release 2, you can define the TAF policy on the service using dbms_service package. It will only work with OCI clients. It will only move the session and if the parameter is set, it will failover the select statement. For insert, update or delete transactions, the application must be TAF aware and roll back the transaction. YES, you should enable FCF on your OCI client when you use TAF, it will make the failover faster.
Note: TAF will not work with JDBC thin.
2) FAN with tnsnames with aq notifications true

FAN is a feature of Oracle RAC which stands for Fast Application Notification. This allows the database to notify the client of any change (Node up/down, instance up/down, database up/down). For integrated clients, inflight transactions are interrupted and an error message is returned. Inactive connections are terminated. 
FCF is the client feature for Oracle Clients that have integrated with FAN to provide fast failover for connections. Oracle JDBC Implicit Connection Cache, Oracle Data Provider for .NET (ODP.NET) and Oracle Call Interface are all integrated clients which provide the Fast Connection Failover feature.
3) FCF, along with FAN when using connection pools
FCF is a feature of Oracle clients that are integrated to receive FAN events and abort inflight transactions, clean up connections when a down event is received as well as create new connections when a up event is received. Tomcat or JBOSS can take advantage of FCF if the Oracle connection pool is used underneath. This can be either UCP (Universal Connection Pool for JAVA) or ICC (JDBC Implicit Connection Cache). UCP is recommended as ICC will be deprecated in a future release.

4) ONS, with clusterware either FAN/FCF

ONS is part of the clusterware and is used to propagate messages both between nodes and to application-tiers
ONS is the foundation for FAN upon which is built FCF.
RAC uses FAN to publish configuration changes and LBA events. Applications can react as those published events in two way :
- by using ONS api (you need to program it)
- by using FCF (automatic by using JDBC implicit connection cache on the application server)
you can also respond to FAN event by using server-side callout but this on the server side (as their name suggests it)

Relationship between FAN/FCF/ONS

ONS –> FAN –> FCF
ONS -> send/receive messages on local and remote nodes.
FAN -> uses ONS to notify other processes about changes in configuration of service level
FCF -> uses FAN information working with conection pools JAVA and others.

8-Oracle RAC startup sequence

 

Whilst reading the book “http://www.amazon.com/Pro-Oracle-Database-11g-Linux/dp/1430229586” found excellent diagram representing the same but very understandable manner. (at least for me)

Without much hassle or discussion with bottom to top approach, I understand now,

OHASD Phase:-

• OHASD (Oracle High Availability Server Daemon) starts Firsts and it will start

OHASD Agent Phase:-

• OHASD Agent starts and in turn this will start

gipcd	Grid interprocess communication daemon, used for monitoring cluster interconnect
mdnsd	Multicast DNS service It resolves DNS requests on behalf of GNS
gns	The Grid Naming Service (GNS), a gateway between DNS and mdnsd, resolves DNS requests
gpnpd	Grid Plug and Play Daemon, Basically a profile similar like OCR contents stored in XML format in $GI_HOME/gpnp/profiles/<peer> etc., this is where used by OCSSD also to read the ASM disk locations to start up with out having ASM to be up, moreover this also provides the plug and play profile where this can be distributed across nodes to cluster
evmd/ evmlogger	Evm service will be provided by evmd daemon, which is a information about events happening in cluster, stop node,start node, start instance etc.

• cssdagent (cluster synchronization service agent), in turn starts

ocssd

Cluster synchronization service daemon which manages node membership in the cluster

If cssd found that ocssd is down, it will reboot the node to protect the data integrity.

• cssdmonitor (cluster synchronization service monitor), replaces oprocd and provides I/O fencing

• OHASD orarootagent starts and in turn starts

crsd.bin	Cluster ready services, which manages high availability of cluster resources , like stopping , starting, failing over etc.
diskmon.bin	disk monitor (diskdaemon monitor) provides I/O fencing for exadata storage
octssd.bin	Cluster synchronization time services , provides Network time protocol services but manages its own rather depending on OS

CRSD Agent Phase:- crsd.bin starts two more agents

crsd orarootagent(Oracle root agent) starts and in turn this will start

gns	Grid interprocess communication daemon, used for monitoring cluster interconnect
gns vip	Multicast DNS service It resolves DNS requests on behalf of GNS
Network	Monitor the additional networks to provide HAIP to cluster interconnects
Scan vip	Monitor the scan vip, if found fail or unreachable failed to other node
Node vip	Monitor the node vip, if found fail or unreachable failed to other node

crsd oraagent(Oracle Agent) starts and in turn it will start (the same functionality in 11gr1 and 10g managed by racgmain and racgimon background process) which is now managed by crs Oracle agent itself.

·

ASM & disk groups	Start & monitor local asm instance
ONS	FAN feature, provides notification to interested client
eONS	FAN feature, provides notification to interested client
SCAN Listener	Start & Monitor scan listener
Node Listener	Start & monitor the node listener (rdbms?)

Hope this helps, comments & suggestions are most welcome.

9-Installation of RAC database

10-Preinstallation check using runcluvfy statement

11-RAC patching steps

Applying PSU Patch 11.2.0.1.2 To A Two Node RAC

Posted by Srikrishna Murthy Annam on May 5, 2011

NOTE : This article is aimed at showing the issues and resolution while applying the PSU patch 11.2.0.1.2 (9655006) to a specific environment and not generalized. Please refer the readme of the PSU patch for detailed procedure to apply PSU patch.

Video Demos are also uploaded to show you the errors and successfull installation of the PSU to 2 node RAC.

1. Record Pre Patch Information.

2. OPatch Utility Information.

3. OCM Configuration.

4. Validation of Oracle Inventory.

5. One-off Patch Conflict Detection and Resolution.

6. Download and Unzip the PSU Patch 9655006

7. Patching GI Home

8. Patching RAC Database Homes

9. Loading Modified SQL Files into the Database.

10. Patch Successful Verification Steps.

11. Issues & Resolutions.

1. Record Pre Patch Information.

1.     Login to each node in RAC as grid user and execute the following command.

$GRID_ORACLE_HOME/OPatch/opatch lsinventory

$GRID_ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i ‘GI PSU’

2.     Login to each node in RAC as oracle user and execute the following command.

$ORACLE_HOME/OPatch/opatch lsinventory

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i ‘DATABASE PSU’

3.     Connect to each instance and record registry information.

SQL> select comp_name,version,status from dba_registry;

2. OPatch Utility Information.

$ORACLE_HOME/OPatch/opatch version -h /u01/home/oracle/product/11.2.0/db_1

$GRID_ORACLE_HOME/OPatch/opatch version -h /u01/home/11.2.0/grid

3. OCM Configuration.

Create ocm response file using the following command and provide appropriate values for the prompts.

$GRID_ORACLE_HOME/OPatch/ocm/bin/emocmrsp

Verify the created file using,

$GRID_ORACLE_HOME/OPatch/ocm/bin/emocmrsp –verbose ocm.rsp

NOTE: The Opatch utility will prompt for your OCM (Oracle Configuration Manager) response file when it is run. Without which we cant proceed further.

4. Validation of Oracle Inventory.

$GRID_ORACLE_HOME/OPatch/opatch lsinventory -detail -oh /u01/home/11.2.0/grid

$ORACLE_HOME/OPatch/opatch lsinventory -detail –oh /u01/home/oracle/product/11.2.0/db_1

5. One-off Patch Conflict Detection and Resolution.

NA

6. Download and Unzip the PSU Patch 9655006

$cd /u01/home/oracle/product/11.2.0/db_1/patches/psupatch

$unzip p9655006_11201_Linux.zip

$chmow -R 777 *

7. Patching GI Home

NOTE: If the GI home is shared, then make sure to shut down the GI stack on all remote nodes. Keep the GI stack up and running on the local node.

NOTE: If the GI home is not shared, then make sure the GI stack is running on all nodes in the cluster.

Our Grid Home is not shared, So don’t shutdown any services.

$su – ( Login to root user )

#/u01/home/11.2.0/grid/OPatch/opatch auto /u01/home/oracle/product/11.2.0/db_1/patches/psupatch -oh /u01/home/11.2.0/grid

Monitor the logfile created in $GRID_ORACLE_HOME/cfgtoollogs/

Execute the above opatch command on each RAC node as root user.

** Please refer the Issue & Resolutions secion in the same document for any issues.

8. Patching RAC Database Homes

All Oracle processes and applications (such as emconsole and emagent) that are running from the database home and that are not managed by clusterware should be stopped manually before you apply the patch using the opatch auto command.

$su – ( Login to root user )

# /u01/home/oracle/product/11.2.0/db_1/OPatch/opatch auto /u01/home/oracle/product/11.2.0/db_1/patches/psupatch -oh /u01/home/oracle/product/11.2.0/db_1

Monitor the logfile created in $ORACLE_HOME/cfgtoollogs/

Execute the above opatch command on each RAC node as root user.

** Please refer the Issue & Resolutions secion in the same document for any issues.

9. Loading Modified SQL Files into the Database.

For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus. Connect as SYSDBA and run the catbundle.sql script as follows:

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> @catbundle.sql psu apply

SQL> QUIT

Check the log files in $ORACLE_HOME/cfgtoollogs/catbundle for any errors

10. Patch Successful Verification Steps.

1.     Login to each node in RAC as grid user and execute the following command.

$GRID_ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i ‘GI PSU’

2.     Login to each node in RAC as oracle user and execute the following command.

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i ‘DATABASE PSU’

3.     Connect to each instance and record registry information.

SQL> select comp_name,version,status from dba_registry;

SQL> select * from dba_registry_history;

11. Issues & Resolutions.

Issue 1:

When applying the patch, you may get the following error

The opatch minimum version check for patch /u01/home/oracle/product/11.2.0/db_1/patches/9655006/custom failed for /u01/home/11.2.0/grid

The opatch minimum version check for patch /u01/home/oracle/product/11.2.0/db_1/patches/9655006/etc failed for /u01/home/11.2.0/grid

The opatch minimum version check for patch /u01/home/oracle/product/11.2.0/db_1/patches/9655006/files failed for /u01/home/11.2.0/grid

Opatch version check failed for oracle home /u01/home/11.2.0/grid

Opatch version check failed

update the opatch version for the failed homes and retry

Solution:

Ref Note : 1308858.1

We need to provide the Patch unzipped base directory, not the directory including patch number

Ex : /u01/home/oracle/product/11.2.0/db_1/patches/psupatch/9655006 ( Wrong )

/u01/home/oracle/product/11.2.0/db_1/patches/psupatch ( correct )

Issue 2

Patch may exit with the following error messages

Unable to determine if /u01/home/11.2.0/grid is shared oracle home

Enter ‘yes’ if this is not a shared home or if the prerequiste actions are performed to patch this shared home (yes/no):yes

You must kill crs processes or reboot the system to properly

cleanup the processes started by Oracle clusterware

The Oracle Clusterware stack failed to stop.

You should stop the stack with ‘crsctl stop crs’ and rerun the command

The opatch Applicable check failed for /u01/home/11.2.0/grid. The patch is not applicable for /u01/home/11.2.0/grid

patch ././9655006 apply failed for home /u01/home/11.2.0/grid

Solution :

This error may be specific to this environment though want to specify it here. This error is due to the reason that there are some cluster resources available referring the 10g database installed earlier. Delete those resources from clusterware.

# crsctl delete resource <resource_name> -f

Issue 3 :

PSU patch for GRID home on node 2 failed with the following error

Unable to determine if /u01/home/11.2.0/grid is shared oracle home

Enter ‘yes’ if this is not a shared home or if the prerequiste actions are performed to patch this shared home (yes/no):yes

Successfully unlock /u01/home/11.2.0/grid

patch ././9655006 apply failed for home /u01/home/11.2.0/grid

Verified the detailed log file locate in /u01/home/11.2.0/grid/cfgtoollogs/ and found the permission issue on some files.

The following actions have failed:

Copy failed from ‘/u01/home/oracle/product/11.2.0/db_1/patches/psupatch/9655006/files/bin/crsctl.bin’ to ‘/u01/home/11.2.0/grid/bin/crsctl.bin’…

Copy failed from ‘/u01/home/oracle/product/11.2.0/db_1/patches/psupatch/9655006/files/bin/oifcfg.bin’ to ‘/u01/home/11.2.0/grid/bin/oifcfg.bin’…

Solution:

Give 777 permission to these files crsctl.bin and oifcfg.bin

Issue 4 :

PSU Patch for RDBMS Home on node 1 failed with the following error

Unable to determine if /u01/home/oracle/product/11.2.0/db_1 is shared oracle home

Enter ‘yes’ if this is not a shared home or if the prerequiste actions are performed to patch this shared home (yes/no):yes

patch ././9655006/custom/server/9655006 apply failed for home /u01/home/oracle/product/11.2.0/db_1

Verified the detailed log file locate in /u01/home/oracle/product/11.2.0/db_1/cfgtoollogs and found the platform issue.

Running prerequisite checks…

Prerequisite check “CheckPatchApplicableOnCurrentPlatform” failed.

The details are:

Patch ( 9655006 ) is not applicable on current platform.

Platform ID needed is : 46

Platform IDs supported by patch are: 226

UtilSession failed: Prerequisite check “CheckPatchApplicableOnCurrentPlatform” failed.

OPatch failed with error code 73

Solution:

export OPATCH_PLATFORM_ID=226 and execute the psu patch command again.

Issue 5:

While applying Grid Infrastructure PSU (patch 9343627) to $GRID_HOME, opatch prerequisite check CheckSystemSpace failed:

Running prerequisite checks…

 Prerequisite check “CheckSystemSpace” failed.

 The details are:

 Required amount of space(4373569440) is not available.

 UtilSession failed: Prerequisite check “CheckSystemSpace” failed.

 OPatch failed with error code 73

Solution:

Follow the note ID 1088455.1 and make sure you have enough space on disk in GRID HOME mount point.

12-RAC upgrade—

E:\DBA\DBA documents\Database upgrade

13-Server kernel parameter shared memory

E:\DBA\RAC\Semaphore_sharedmemory

14-SCAN IP,VIP,PUBLIC IP and private IP

VIPs

-A VIP is an IP address assigned to a cluster node, monitored by Oracle Clusterware, which can be failed over to a surviving node in case of node failure.

-In a RAC cluster, a VIP6 is assigned to a specific node and monitored by the Clusterware, and each node can be configured with one or more VIPs. Suppose that rac1.example.com has VIP 10.21.8.100. If the node rac1.example.com fails, then the Clusterware running on other surviving cluster nodes will immediately relocate VIP 10.21.8.100 to a surviving node (say rac2.example.com).

-Since an IP address is available in the network, connection requests to the IP address 10.21.8.100 will immediately receive a CONN_RESET message (think of it as a No listener error message),and the connection will continue to try the next address in the address list. There is no wait for TCP timeout, as the IP address responds to the connection request immediately.

-VIP eliminates unnecessary waits for TCP timeout. VIPs are also monitored by Clusterware,providing additional high availability for incoming connections. Clusterware uses ioctl functions (on Unix and

Linux) to check the availability of network interfaces assigned for VIPs.

-As a secondary method, the gateway server is also pinged to verify network interface availability. Any errors in the ioctl command will immediately relocate the IP address to another available interface or relocate the VIP resource to another node.

-Listeners are not started after a VIP failover to another node in RAC. The network layer level CONN_REST error message is the key to eliminate TCP timeouts.

15-Describe SCAN working behavior-

Single Client Access Name (SCAN) is a new Oracle Real Application Clusters (RAC) 11g Release 2 feature that provides a single name for clients to access Oracle Databases running in a cluster. Thebenefit is that the client’s connect information does 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 cluster, independently of which server(s) in the cluster the database is active. SCAN provides load balancing and failover for client connections to the database. The SCAN works as a cluster alias for databases in the cluster.

SCAN Concepts

• 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).
• By default, the name used as the SCAN is also the name of the cluster and must be globally unique throughout your enterprise. The default value for the SCAN is based on the local node name. 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 (-). If you require a SCAN that is longer than 15 characters, then select an Advanced installation.
• For installation to succeed, the SCAN must resolve to at least one address.
• SCAN VIP addresses must be on the same subnet as virtual IP addresses and public IP addresses.
• 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.
• If hosts file is used to resolve SCAN hostname, you will receive Cluster Verification Utility failure at end of installation (see Note: 887471.1 for more details)
• For high availability and scalability, Oracle recommends that you configure the SCAN to use DNS Round Robin resolution to three addresses.
• 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.
• Clients can continue to access the cluster in the same way as with previous releases, but Oracle recommends that clients accessing the cluster use the SCAN. Clients using the SCAN can also access the cluster using EZCONNECT.
• 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.

·         There are 2 options for defining the SCAN:

·         1.      Define the SCAN in your corporate DNS (Domain Name Service)

·         2.      Use the Grid Naming Service (GNS)

·          

·         Define the SCAN in your corporate DNS (Domain Name Service)

·          

·         If you choose Option 1, you must ask your network administrator to create a single name that resolves to 3 IP addresses using a round-robin algorithm. Three IP addresses are recommended considering load balancing and high availability requirements regardless of the number of servers in the cluster. The IP addresses must be on the same subnet as your public network in the cluster. The name must be 15 characters or less in length, not including the domain, and must be resolvable without the domain suffix (for example: “krac-scan’ must be resolvable as opposed to “krac-scan.india.com”). The IPs must not be assigned to a network interface (on the cluster), since Oracle Clusterware will take care of it.

·          

·         kracnode-scan     192.168.1.72

·                           192.168.1.70

·                           192.168.1.71

·          

·         You can check the SCAN configuration in DNS using “nslookup”. If your DNS is set up to provide round-robin access to the IPs resolved by the SCAN entry, then run the “nslookup” command at least twice to see the round-robin algorithm work. The result should be that each time, the “nslookup” would return a set of 3 IPs in a different order.

·         First nslookup

·          

·         [root@kracnode2 ~]# nslookup kracnode-scan

·         Server:         192.168.1.100

·         Address:        192.168.1.100#53

·          

·         Name:   kracnode-scan.india.com

·         Address: 192.168.1.72

·         Name:   kracnode-scan.india.com

·         Address: 192.168.1.70

·         Name:   kracnode-scan.india.com

·         Address: 192.168.1.71

·          

·         Second nslookup

·          

·         [root@kracnode2 ~]# nslookup kracnode-scan

·         Server:         192.168.1.100

·         Address:        192.168.1.100#53

·          

·         Name:   kracnode-scan.india.com

·         Address: 192.168.1.70

·         Name:   kracnode-scan.india.com

·         Address: 192.168.1.71

·         Name:   kracnode-scan.india.com

·         Address: 192.168.1.72

Note: If your DNS server does not return a set of 3 IPs as shown in figure 3 or does not round-robin, ask your network administrator to enable such a setup. DNS using a round-robin algorithm on its own does not ensure failover of connections. However, the Oracle Client typically handles this. It is therefore recommended that the minimum version of the client used is the Oracle Database 11g Release 2 client. Refer about DNS configuration ClickHere.

How can we configure the SCAN and SCAN listener?

During Typical installation, you are prompted to confirm the default Single Client Access Name (SCAN), which is used to connect to databases within the cluster irrespective of which nodes they are running on.  If you change the SCAN from the default, then the name that you use must be globally unique throughout your enterprise.

If the SCAN name resolves to one IP address, root script (root.sh or rootupgrade.sh) will create the number of SCAN VIP resources(ora.scan1.vip) and corresponding SCAN listener resource(ora.LISTENER_SCAN1.lsnr) depend on how many IP address the SCAN name resolves to, i.e.if the SCAN name resolves to two IP addresses, it will create two SCAN VIP resources and two corresponding SCAN listener resource.

SCAN VIP and the corresponding SCAN listener works like a pair, when SCAN VIP fails over to other node, the corresponding SCAN listener will also be failed over to the same node.

When SCAN VIP fails over happens, it will always select a node with least running SCAN VIP, i.e., if SCAN VIP runs on node1, node2 and node3 of a 4-node cluster, if node3 goes down, the SCAN VIP and corresponding SCAN listener will be failed over to node4 as the other two nodes already have one SCAN VIP running on each node.

Also we can use 'srvctl' to add/modify the scan resource and the listeners.  Please refer to "Real Application Clusters Admin and Deployment Guide" or Note 1053147.1 for more information.

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.

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."

[oracle@kracnode1]$ srvctl STATUS SCAN_LISTENER

SCAN Listener LISTENER_SCAN1 is enabled

SCAN listener LISTENER_SCAN1 is running on node kracnode1

SCAN Listener LISTENER_SCAN2 is enabled

SCAN listener LISTENER_SCAN2 is running on node kracnode2

SCAN Listener LISTENER_SCAN3 is enabled

SCAN listener LISTENER_SCAN3 is running on node kracnode3

[oracle@kracnode1]$

Instead of DNS or GNS, Can we use '/etc/hosts' to resolve SCAN?

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.
failure at end of installation (See NOTE 887471.1 for more details) 

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.

11gR2 RAC: Why SCAN & Node Listeners in 11gRAC

Hello,

A long notes (confusing) on listeners in 11gR2 RAC.

You all aware of we have two listeners running in database servers in Grid Infrastructure (Aka RAC) namely scan listeners and node listeners.

How does they work?

The SCAN works by being able to resolve to multiple IP addresses reflecting multiple listeners in the cluster handling public client connections. 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 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.

A picture can save 100 words of explanation

We have 3 scan listeners , registering the IPC(TCP) end points as the RAC instances (remote_listener) registered with PMON of rac instances and the PMON also registers to node listeners (local_listener) which actually points to your database.

• Indeed they both run from the same home (Grid Home), but for different functionality. From 11gR2 onwards these listeners are part of clusterware and managed by oraagent. This oraagent take care of listeners. This oraagent spawned by crsd takes care of our listeners in terms of configuration and monitoring.
• Especially when the listener is started by oraagent you can see the lines in the listener.ora file #line added by agent.
• Another important aspect of the listener.ora files are these files are managed by oraagent, whenever there is a clusterware command is used to manage this listeners. for example srvctl etc.
• Further, the listeners will be monitored by oraagent process every 60 seconds, which you can find in the  $GRID_HOME/log/nodename}/agent/crsd/oraagent_oracle/oraagent_oracle.log
• You must set the local_listener and remote_listener parameter to scan listener, description or address list of the same. Since these is the only way now to register your database/instance to scan listener. Even if you dont specify the oraagent will automatically add local_listener values , but you must exclusively set remote_listener parameter to cluster scan name which will provide you the TAF. 

For example,

If you delete the listener.ora and restart the listener with srvctl start listener, a listener.ora will reappear. The original configuration will reappear in listener.ora and the manually modified listener.ora will be renamed (with a timestamp suffix) 

• The agent also creates and maintains the file: endpoints_listener.ora this file is there for backward compatibility,
• Oraagent comes into action also at instance startup, when the instance is started with srvctl (as opposed to ‘manually’ started instance from sqlplus) and sets LOCAL_LISTENER  parameter, dynamically (this is done with an alter system command and only if the parameter has not been set on spfile).

You may also has observed in listener.ora file there were no tcp/ip settings, Where are the TCP/IP settings of my listeners in listener.ora? and Only IPC endpoints are listed in listener.ora,  As you see below the listener.ora contains only scan listener which contain IPC protocol 

Notes:- Before proceed further

What is dynamic listening end point?

dynamic listening endpoint=the address or connection point to the listener. The most known endpoint in the oracle DB world being TCP, port 1521.

Oraagent connects to the listener via IPC and activates the TCP (TCPS, etc) endpoints as specified in the clusterware configuration

Example Listener.ora settings:-

[oracle@rac1]$ cat /u01/app/11.2.0.2/grid/network/admin/listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/11.2.0.2/grid/network/admin/cost)
)
) 

So here no SID, no Port, no host address configured, this is due to the settings whole managed in the clusterware.

SCAN Listeners has an endpoints to the scan listeners in endpoint_listener.ora via IPC or TCP looks like.

endpoints_listener.ora

LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=strac201a-vip)(PORT=1522))(ADDRESS=(PROTOCOL=TCP)(HOST=172.24.21.89)(PORT=1522)(IP=FIRST)))) # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=strac201a-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=172.24.21.89)(PORT=1521)(IP=FIRST)))) # line added by Agent

tnsnames.ora

MYDB1522 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = strac201-scan.frxntnyc.frx2.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MYDB1522)
)
)

As you can see the listener.ora files points to IPC scan listeners end points of vip/hostnames in endpoint_listener.ora and once these listeners started , the database pmon registers the sid/services to this listeners (see picture above) as per tnsentries or the local_listener parameter, the pmon register to both node and scan listeners.

For example,

When you just start a listener ,

$GRID_HOME/bin/lsnrctl stop listener; $GRID_HOME/bin/lsnrctl start listener;

This command starts only IPC endpoint

However oraagent is posted at listener startup and makes active the rest of the endpoints (notably listening on the TCP port), this can be seen for example by running the following a few seconds after listener restart: $GRID_HOME/bin/lsnrctl status listener (which will list all the active endpoints), the active endpoints are infact the database instances. This is possible due to the listener.ora parameter you usually see ENABLE_GLOBAL_DYNAMIC_ENDPOINT_{LISTENER_NAME}=ON 

Another check,

when you disable the ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=OFF in listener.ora

and manually stop the listener (manually means not using srvctl)

$GRID_HOME/bin/lsnrctl stop listener 

$GRID_HOME/bin/lsnrctl start listener

When you check listener.ora and check that the parameter edited above has not changed, that is  in this case the TCP endpoint will not be started, that is the listener will be listening only on IPC.

Check with: $GRID_HOME/bin/lsnrctl status listener

If we try do the same exercise by stopping and starting the listener with srvctl, as it would be the typical way to do it, we will see that the parameter ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER in listener.ora will be set again to ON. 

Now, morale of the post,

Always, use srvctl to add listeners,stop,start

And Always, Use srvctl to start instances/db as it set the local_listeners parameter automatically to endpoints.

So that the listener.ora file manages promptly/neatly by oraagent process.

16- As you said Voting & OCR Disk resides in ASM Diskgroups, but as per startup sequence OCSSD starts first before than ASM, how is it possible?

How does OCSSD starts if voting disk & OCR resides in ASM Diskgroups?

You might wonder how CSSD, which is required to start the clustered ASM instance, can be started if voting disks are stored in ASM? This sounds like a chicken-and-egg problem: without access to the voting disks there is no CSS, hence the node cannot join the cluster. But without being part of the cluster, CSSD cannot start the ASM instance. To solve this problem the ASM disk headers have new metadata in 11.2: you can use kfed to read the header of an ASM disk containing a voting disk. The kfdhdb.vfstart and kfdhdb.vfend fields tell CSS where to find the voting file. This does not require the ASM instance to be up. Once the voting disks are located, CSS can access them and joins the cluster.

17-All srvctl and crsctl commands.

18-Server pool

-A new feature introduced with 11gR2, server pools, is a dynamic grouping of Oracle RAC instances into abstracted RAC pools, by virtue of which a RAC cluster can be efficiently and effectively partitioned, managed, and optimized.

-Simply put, they are a logical grouping of server resources in a RAC cluster and enable the automatic on-demand startup and shutdown of RAC instances on various nodes of a RAC cluster.

19-Policy managed database vs administrator managed database.

20-Instance Caging

-When multiple instances are consolidated in a RAC DB node, resource distribution, consumption, and management become a challenge for a DBA/DMA. Each instance typically will have different requirements and behavior during peak and non-peak business timeframes. There is a high probability that a CPU thirsty instance could impact the performance of other instances configured on the same node.

-As a powerful new feature with Oracle 11gR2, with instance caging, DBAs can significantly simplify and control an instance CPUs/cores overall usage by restricting each instance to a certain number of CPUs. Large-scale RAC environments can greatly benefit with this option.

SQL> ALTER SYSTEM SET CPU_COUNT=2 SCOPE=BOTH SID='INSTANCE_NAME';

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = default_plan|myplan SCOPE=BOTH SID='INSTANCE_NAME';

SQL> show parameter cpu_count

SQL> SELECT instance_caging FROM v$rsrc_plan WHERE is_top_plan = 'TRUE';

After enabling the feature, the CPU usage can be monitored by querying the gv$rsrc_consumer_group and gv$rsrcmgrmetric_history dynamic views. The views will provide useful inputs about the instance caging usage.It provides in minute detail the CPU consumption and throttling feedback for the past hour.