Relocate service in RAC environment(Temporarily/Permanently)

In this document, i have explained how to move the service from one node of the cluster to the other node of the cluster. you can move it through Temporarily & Permanently.  i have tried this example in 11gR2 RAC environment having 2 node primary & 2 node standby database.

ABOUT SERVICE IN RAC

Service name is used by clients to connect to one or more instances. The service name should be unique throughout your system.

If multiple databases in the cluster offer the same service name, then RAC balances connections to that service across all such databases. If you want client connections to a service to be directed to a particular database, then the service name must be unique within the cluster

 

 

TEMPORARILY RELOCATE SERVICE FROM ONE NODE TO ANOTHER NODE IN CLUSTER

$srvctl relocate service -h

Temporarily relocates service from one node of the cluster to another.

Usage: srvctl relocate service -d <db_unique_name> -s <service_name> {-i <old_inst_name> -t <new_inst_name> | -c <current_node> -n <target_node>} [-f]

       Specify instances for an administrator-managed database, or nodes for a policy managed database

    -d <db_unique_name>      Unique name for the database

    -s <service>             Service name

    -i <old_inst>            Old instance name

    -t <new_inst>            New instance name

    -c <current_node>        Node name to relocate service from

    -n <target_node>         Node name to relocate service to

    -f                       Disconnect all sessions during stop or relocate service operations

    -h                       Print usage

BEFORE CHANGING SERVICE STATUS

ora.BHU_a.db        ONLINE on bhuora01, ONLINE on bhuora02

ora.BHU_a.bhurac.com.svc ONLINE on bhuora01

Using Relocate service option to move from one node to another node

% srvctl relocate service -d BHU_A -s bhurac.com -i BHU_2 -t BHU_1 –f

AFTER CHANGING SERVICE STATUS

ora.BHU_a.db            ONLINE on bhuora01, ONLINE on bhuora02

ora.BHU_a.bhurac.com.svc ONLINE on bhuora01

In the above example, we have moved our service from one node to another node of the cluster. this doesnt change in the configuration

$ srvctl config service -d BHU_a

Service name: bhurac.com

Service is enabled

Server pool: BHU_A_RAC.com

Cardinality: 1

Disconnect: false

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Failover type: NONE

Failover method: NONE

TAF failover retries: 0

TAF failover delay: 0

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: NONE

Edition:

Preferred instances: BHU_2

Available instances: BHU_1

PERMANENT RELOCATE SERVICE FROM ONE NODE OF THE CLUSTER TO ANOTHER

To view the service configuration of the environment. i am planning to move my service from BHU_1 to BHU_2 permanently

$ srvctl config service -d BHU_b -s bhurac.com

Service name: bhurac.com

Service is enabled

Server pool: BHU_B_rac.com

Cardinality: 1

Disconnect: true

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Failover type: NONE

Failover method: NONE

TAF failover retries: 0

TAF failover delay: 0

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: NONE

Edition:

Preferred instances: BHU_1

Available instances: BHU_2

$ srvctl modify service -h

Modifies the configuration for the service.

Usage: srvctl modify service -d <db_unique_name> -s <service_name> -n -i "<preferred_list>" [-a "<available_list>"] [-f]

    -d <db_unique_name>      Unique name for the database

    -s <service>             Service name

    -n                       Modify service configuration

    -i "<preferred_list>" Comma separated list of preferred instances

    -a "<available_list>" Comma separated list of available instances

    -f Disconnect all sessions during stop or relocate service operations

To modify service from one to another node

$ srvctl modify service -d BHU_b -s bhurac.com -n -i BHU_2 -a BHU_1 –f

To verify whether the service has moved from one node to another permanently

$srvctl config service -d BHU_b -s bhurac.com

Service name: bhurac.com

Service is enabled

Server pool: BHU_B_rac.com

Cardinality: 1

Disconnect: false

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Failover type: NONE

Failover method: NONE

TAF failover retries: 0

TAF failover delay: 0

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: NONE

Edition:

Preferred instances: BHU_2

Available instances: BHU_1

ORA-16516: current state is invalid for the attempted operation

Problem: when I am trying to do a switch over using DG broker and I am facing the issue. 

DGMGRL> switchover to 'BHU_A';

Performing switchover NOW, please wait...

Error: ORA-16516: current state is invalid for the attempted operation

Failed.

Unable to switchover, primary database is still "BHU_B"

DGMGRL>

Reason

After analyzing I found the apply has been stopped in the standby database and noted that we should have a zero lag before proceeding with the switchover

DGMGRL> show database verbose 'BHU_A';

Database - BHU_A

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-OFF

  Transport Lag:   0 seconds

  Apply Lag:       24 minutes 26 seconds

  Real Time Query: OFF

  Instance(s):

    BHU_1 (apply instance)

    BHU_2

<Removed some lines of the output>

Enabling the Apply On process using the DG Broker

DGMGRL> edit database 'BHU_A' set state='APPLY-ON';

Succeeded.

DGMGRL> show database verbose 'BHU_A';

Database - BHU_A

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds

  Apply Lag:       0 seconds

  Real Time Query: ON

  Instance(s):

    BHU_1 (apply instance)

    BHU_2

<Removed some lines of the output>

Starting the Switchover process again after checking the LAG.

Note: since I have write note for the above error message only. If you are performing the switchover process, a lot of check needs to be performed.

DGMGRL> switchover to 'BHU_A'

Performing switchover NOW, please wait...

New primary database "BHU_A" is opening...

Operation requires shutdown of instance "BHU_1" on database "BHU_B"

Shutting down instance "BHU_1"...

ORACLE instance shut down.

Operation requires startup of instance "BHU_1" on database "BHU_B"

Starting instance "BHU_1"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "BHU_A"

DGMGRL>

I hope this document helps you to solve the issue.

ORA-16653: failed to reinstate database

When I try to issue the reinstate command after issuing the failover command on the standby database and try to rebuild the standby database using the reinstate command.

Problem

DGMGRL> reinstate database 'BHU_B';

Reinstating database "BHU_B", please wait...

Error: ORA-16653: failed to reinstate database

Failed.

Reinstatement of database "BHU_B" failed

Reason:

Reinstate fails since Flashback Database is disabled in the standby database.

Probably we can use the active standby database build or traditional standby database build to proceed further.

RESTORING OCR DISK & VOTING DISK ON ASM DISK GROUP

RECOVERING OCR DISK & VOTING DISK ON ASM DISK GROUP FROM CORRUPTION OR LOSS

When we have lost or having a corruption issue on the OCR & VOTING Disk, we have follow the below procedure to bring it back.

When using an ASM disk group for CRS there are typically 3 different types of files located in the disk group that potentially need to be restored/recreated for function of the cluster.

• Oracle Cluster Registry file (OCR)
• Voting files
•  Shared SPFILE for the ASM instances

In this scenario, we are trying to restore the corrupted OCR Disk & Voting Disk from the backup.

Step #1 Stop cluster on each node(Root user).

# crsctl stop crs -f

Step #2 we are starting the cluster in the excusive mode(Root user)

As root start GI in exclusive mode on one node only:

In 11201 RAC, we have to use below option to start the cluster in the exclusive mode.

# crsctl start crs -excl

In 11202 RAC, we have to use below option to start the cluster in the exclusive mode.

# crsctl start crs -excl -nocrs

Note: A new option '-nocrs' has been introduced with  11.2.0.2, which prevents the start of the ora.crsd resource. It is vital that this option is specified; otherwise the failure to start the ora.crsd resource will tear down ora.cluster_interconnect.haip, which in turn will cause ASM to crash.

If you don’t have the OCR DISK GROUP, then create it else move to restoring OCR DISK

Step #3 OCR RESTORE

To Know the OCR Location on the cluster environment

$ cat /etc/oracle/ocr.loc  -- In Linux

To Check whether ocrcheck is corrupted or not

# ocrcheck

Check whether ocrcheck is able to complete it successfully

OCR CHECK Ex

# ocrcheck

Status of Oracle Cluster Registry is as follows :

         Version                  :          3

         Total space (kbytes)     :     262120

         Used space (kbytes)      :       4404

         Available space (kbytes) :     257716

         ID                       : 1306201859

         Device/File Name         :  +OCR_VOTE

                                    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 succeeded

        

Note: 1) Check whether cluster registry integrity check is successful.

          2) When you run as oracle user, logical corruption check will be bypassed. You can see this line end of the “ocrcheck” output.

“Logical corruption check bypassed due to non-privileged user”

To Know the OCR Location on the cluster environment

$ cat /etc/oracle/ocr.loc  -- In Linux

If the OCR DISK corrupted, then perform the below steps

Locate OCR LOG file location

$GRID_HOME /log/<hostname>/client/ocrcheck_<pid>.log

Locate the latest automatic OCR backup

$GRID_HOME\bin\ocrconfig –showbackup

Restore the latest OCR backup(root user)

# ocrconfig -restore $GRID_HOME/cdata/racsapie1/backup00.ocr

racsapie1 è SCAN NAME for the cluster

Step #4 VOTING DISK RECREATE

           

Recreate the Voting file (root user)

The Voting file needs to be initialized in the CRS disk group

# crsctl replace votedisk +OCR_DISK

Note: 1) Above command will Re-create/move your voting disk in the specified ASM Disk Group, if you query the voting disk it will display your voting disk location in the DISK Group which has been specified above.

2)  Voting File is that it is no longer supported to take a manual backup of it with dd.  Instead, the Voting File gets backed up automatically into the OCR.

Query Voting Disk location

# $GRID_HOME/bin/crsctl query css votedisk

Note: You cannot create more than 1 voting disk in the same or on another/different Disk group disk when using External Redundancy in 11.2. The rules are as follows:

External = 1 voting disk

Normal= 3 voting disk

High= 5 voting disk

Step #5 Stop & start the cluster

Shutdown CRS è CRS is running in exclusive mode, it needs to be shutdown (Root User).

# crsctl stop crs -f

Start CRS è Start the CRS in one node, if everything is ok then start the CRS in other nodes (root user).

# crsctl start crs

CRS Status è Once it is start, you can check the status of the CRS(Root / Oracle user)

# crsctl stat res –t –init      à if you are checking for one node

# crsctl check cluster –all  à if you are checking for entire cluster.

Important Tips

Oracle Clusterware 11g Release 2 backs up the OCR automatically every four hours on a schedule that is dependent on when the node started

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

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

Ocrconfig –showbackup auto

Note: automatic backups will not occur, when the cluster is down

Verifying OCR integrity of all of the cluster nodes by running the following CVU command:

$ cluvfy comp ocr -n all -verbose

Please provide your valuable comments. Happy Learning!!!!!
 

Snapshot Standby Database in 11gR2

Snapshot Standby Database help physical standby to open in READ-WRITE mode easily

A snapshot standby database is a fully update-able standby database that is created by converting a physical standby database into a snapshot standby database.

A snapshot Standby is open in the read-write mode and hence it is possible to process transactions independently of the primary database. At the same time, it maintains protection by continuing to receive data from the production database, archiving it for later use.
Using a single command change made while the database is in read-write mode can throw away the changes made to the standby database only and re-synchronize the standby database with the production database.
1. Snapshot standby database receives and archives, but does not apply the redo data.

2. Redo data received from the primary database is applied automatically once it is converted back into a physical standby database.

3. Snapshot standby database cannot be the target of a switchover or failover. A snapshot standby database must first be converted back into a physical standby database before performing a role transition to it.

 Steps to convert Physical Standby Database to the Snapshot Standby Database
I have 2 node primary cluster database and 2 node standby cluster database with the ASM. It is running on 11gR2

Note: In the snapshot standby database, there is no step to be performed in the primary database. all the steps are performed only in the physical standby database

STATUS OF THE STANDBY DATABASE

SQL> select name,open_mode from gv$database;

NAME      OPEN_MODE

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

BHU       MOUNTED

BHU       MOUNTED

CHECKING RECOVERY AREA AND ALLOCATE SIZE

SQL> show parameter db_recov

NAME                                 TYPE        VALUE

------------------------------------ ----------- -------------db_recovery_file_dest                string      +BHU_RECO

db_recovery_file_dest_size           big integer 4G

CHECKING WHETHER FLASHBACK IS ENABLED OR NOT. TO OPEN A STANDBY DATABASE IN THE READ/WRITE MODE, WE NEED TO HAVE THE FLASHBACK WITH ENOUGH SIZE.

SQL> select flashback_on from gV$database;

FLASHBACK_ON

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

YES

YES

CHECKING THE STATUS OF RECOVERY PROCESS, IF IT IS ENABLED THEN WE HAVE TO STOP THE RECOVERY.

SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR';

PROCESS   CLIENT_P    THREAD#  SEQUENCE#     BLOCK#

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

RFS       LGWR              2         74       6080

MRP0      N/A               2         74       6078

RFS       LGWR              1         78       7145

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

AFTER CANCELING THE RECOVERY PROCESS, CHECK THE STATUS

SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or           client_process='LGWR';

PROCESS   CLIENT_P    THREAD#  SEQUENCE#     BLOCK#

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

RFS       LGWR              2         74       6150

RFS       LGWR              1         78       7216

CONVERTING THE STANDBY DATABASE AS A SNAPSHOT STANDBY DATABASE

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.

CHECKING THE STATUS OF THE DATABASE

SQL> select open_mode,database_role from gv$database;

OPEN_MODE            DATABASE_ROLE

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

MOUNTED              SNAPSHOT STANDBY

MOUNTED              SNAPSHOT STANDBY

NOW WE ARE STOPPING & STARTING THE DATABASE TO OPEN MODE

$ srvctl stop database -d BHU_b

$ srvctl start database -d BHU_b -o open

CHECKING THE STATUS OF THE DATABASE

SQL> select open_mode,database_role from gv$database;

OPEN_MODE            DATABASE_ROLE

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

READ WRITE           SNAPSHOT STANDBY

READ WRITE           SNAPSHOT STANDBY

CHECKING THE SYSTEM CREATED THE RESTORE POINT

SQL> select NAME,SCN,TIME from v$restore_point;

NAME                                                     SCN

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

TIME

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

SNAPSHOT_STANDBY_REQUIRED_11/21/2011 10:48:00         20539509

21-NOV-11 10.48.00.000000000 AM

I AM CREATING SOME TABLES & PERFORMING SOME DML OPERATIONS IN THE SNAPSHOT DATABASE

SQL> create table bhuvan as select * from dba_objects;

Table created.

SQL> select count(1) from bhuvan;

  COUNT(1)

----------

     17065

SQL> delete bhuvan where owner='BHUVAN';

9699 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(1) from bhuvan;

  COUNT(1)

----------

      7366

NOW WE ARE STOPPING & CONVERTING SNAPSHOT DATABASE IN TO PHYSICAL STANDBY DATABASE

$ srvctl stop database -d BHU_b

# I am using single instance to perform the conversion from the snapshot standby database to the physical standby database

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Nov 21 10:53:16 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 1.4431E+10 bytes

Fixed Size                  2240272 bytes

Variable Size            3892314352 bytes

Database Buffers         1.0503E+10 bytes

Redo Buffers               34148352 bytes

Database mounted.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

Once we convert from the snapshot standby database to the physical standby database, database will go to the no mount stage.

SQL> select open_mode,database_role from v$database;

select open_mode,database_role from v$database

                 *

ERROR at line 1:

ORA-01507: database not mounted

NOW WE ARE STOPPING & STARTING THE DATABASE TO MOUNT STAGE AND CHECKING THE RECOVERY PROCESS

$ srvctl stop database -d BHU_b

$ srvctl start database -d BHU_b

CHECKING THE RESTORE POINT, SYSTEM WILL REMOVE IT AUTOMATICALLY ONCE WE ARE CONVERTED TO PHYSICAL STANDBY DATABASE

SQL> select NAME,SCN,TIME from v$restore_point;

no rows selected

CHECKING THE RECOVEY PROCESS

SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR';

PROCESS   CLIENT_P    THREAD#  SEQUENCE#     BLOCK#

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

RFS       LGWR              2         83       3495

RFS       LGWR              1         87       4407

MRP0      N/A               2         83       3495

TAF WITH SCAN IN 11GR2

TRANSPARENT APPLICATION FAILOVER WITH SCAN

Transparent Application Failover is process by which the sessions are automatically failover to the other available instance in the cluster database. New connection would be point to the available instance on the cluster database and if you have any active transaction and it will be rollback to the last committed transaction.

TRANSPARENT APPLICATION FAILOVER (TAF) FAILOVER METHODS

(METHOD = BASIC/PRECONNECT)

BASIC FAILOVER 

The second connection to the available instance is made only if and when the primary instance is down or disconnected for some reason.

PRECONNECT FAILOVER 

The Second connection to another available instance is made at the same time as the connection to the primary instance. Having the backup connection already in place can reduce the time needed for a failover in the event that one needs to take place. We have considered the Over Head while the sessions are pre-connected.

TRANSPARENT APPLICATION FAILOVER (TAF) FAILOVER TYPE 

(TYPE=SESSION/SELECT/NONE)

SESSION

When the TYPE= Session specified, it will failover to the available instance in the cluster database and it doesn’t continue with selected, if it is in the process. OCI will start the select process again.

SELECT

When the type=select specified, it will failover to the available instance in the cluster database and the failover process also replays the queries that is in the progress.

NONE

No failover function is used. When you specify it as none, no failover function will be used.

TAF can be configured at the Oracle Client side in tnsnames.ora or at the Database Server side using srvctl

SRVCTL SYNTAX FOR CREATING SERVICE

srvctl add service -d <db_unique_name> -s <service_name>
-r "<preferred_list>" [-a "<available_list>"] [-P {BASIC | NONE | PRECONNECT}]
-g <server_pool> [-c {UNIFORM | SINGLETON}]
[-k <net_num>]
[-l [PRIMARY][,PHYSICAL_STANDBY][,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]]
[-y {AUTOMATIC | MANUAL}]
[-q {TRUE|FALSE}]
[-x {TRUE|FALSE}]
[-j {SHORT|LONG}]
[-B {NONE|SERVICE_TIME|THROUGHPUT}]
[-e {NONE|SESSION|SELECT}]
       -- This defines the type of TAF whether SESSION or SELECT.
[-m {NONE|BASIC}]
       --This defines the method of TAF.
[-z <failover_retries>]
       -- the number of times to attempt to connect after a failover.
[-w <failover_delay>]
       -- the amount of time in seconds to wait between connect attempts.

SYNTAX

          srvctl add service -d <db_unique_name> -s <service_name>
          -r "<preferred_list>" [-a "<available_list>"] [-P {BASIC | NONE | PRECONNECT}]
         [-e {NONE|SESSION|SELECT}]
         [-m {NONE|BASIC}]
         [-z <failover_retries>]
         [-w <failover_delay>]

PRECONNECT USING SCAN NAMES

When you are connecting using the scan names and having the failover method as PRE-CONNECT then you use should configured with the (preferred instance & available instance).

Ex:

Bhuvan =

  (description =

   (address = (protocol=tcp)(host=bhuvanrac-scan)(port=1521))

   (connect_data=

     (service_name=bhuvanrac)

     (failover_mode= (backup=bhuvanrac_preconnect)

       (type=select) (method=preconnect)

     )

   ) )

bhuvanrac_preconnect =

  (description =

   (address = (protocol=tcp)(host=bhuvanrac-scan)(port=1521))

   (connect_data=(service_name=bhuvanrac_preconnect))

  )

BASIC USING SCAN NAMES

SCAN provides load balancing and failover for client connections to the database. The SCAN works as a cluster alias for databases in the cluster.

bhuvanrac =

  (description =

   (address = (protocol=tcp)(host=bhuvanrac-scan)(port=1521))

   (connect_data=(service_name=bhuvanrac))

  )

TRADITIONAL METHOD

JDBC connection

BHUVAN=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=bhuora01)(Port=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=bhuora02)(PORT = 1521))

(CONNECT_DATA=(SERVICE_NAME=BHUVANRAC)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC))))

TNS ENTRY

BHUVAN=

(DESCRIPTION=

(ADDRESS=(PROTOCOL=TCP)(Host=bhuora01)(Port=1521))

(ADDRESS=(PROTOCOL=TCP)(HOST=bhuora02)(PORT=1521))

(CONNECT_DATA=(SERVICE_NAME=BHUVANRAC)

(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC))))

Start the service
$ srvctl start service -d <DB_NAME> -s <SERVICE_NAME>

Stop the service
$ srvctl stop service -d <DB_NAME> -s <SERVICE_NAME>

To Modify the service

$ srvctl modify service -d <DB_NAME> -s <SERVICE_NAME>

To see the configuration of the service

$ srvctl config service -d <DB_NAME> -s <SERVICE_NAME>

To see the Connection of the pre-connect session
select inst_id,username,service_name,failover_type, failover_method,failed_over from gv$session where username='BHUVAN';