Pre-check For SWITCHOVER using DG Broker

PRE-CHECK FOR SWITCHOVER USING DG BROKER

1. Verify the primary database instance is open (READ/WRITE Mode) and the standby database instance is mounted.

SELECT NAME, OPEN_MODE FROM GV$DATABASE;

2. Verify there are no active users connected to the databases.

SET LINES 10000 pages 10000

SELECT SID, SCHEMANAME, OSUSER, MACHINE, STATUS FROM GV$SESSION WHERE USERNAME IS NOT NULL;

3) Check for any active jobs running

SELECT * FROM DBA_JOBS_RUNNING;

SELECT OWNER, JOB_NAME, START_DATE, END_DATE, ENABLED
FROM DBA_SCHEDULER_JOBS WHERE ENABLED='TRUE' AND OWNER <> 'SYS';

4) Check the primary db has standby redolog files

SELECT GROUP#, BYTES/1024, STATUS FROM GV$STANDBY_LOG;

5) Check standby database has tempfile and it should match the size of the temp file from primary db.

SELECT NAME, BYTES FROM V$TEMPFILE;

6) Switch the backup process to the new primary database, if the backup policy is from the primary db.

7) Check whether the following settings are available for redo transport services such as LogXptMode, NetTimeout, LogShipping, StandbyFileManagement, FastStartFailoverTarget, StandbyArchiveLocation & AlternateLocation., DelayMins in the standby & primary database.

DGMGRL> SHOW DATABASE VERBOSE 'PRIMARY_DB_UNIQUE_NAME';

DGMGRL> SHOW DATABASE VERBOSE 'STANDBY_DB_UNIQUE_NAME';

Check whether StaticConnectIdentifier is configured on ALL NODES, so you have run this command 4 times if you have 2 node RAC primary & standby database.

SHOW INSTANCE VERBOSE 'INSTANCE_NAME' ON DATABASE 'DB_NAME'

Check start options for the primary & standby database

 srvctl config database -d ‘PRIMARY_DB_NAME’ –a

 srvctl config database -d ‘STANDBY_DB_NAME’ –a

Check the DELAYMINS, if it has set for value. Reduce the DELAYMINS to ZERO and apply all the logs before starting switchover.

DGMGRL> SHOW DATABASE 'PRIMARY_DB_UNIQUE_NAME' DELAYMINS;
DGMGRL> EDIT DATABASE 'PRIMARY_DB_UNIQUE_NAME' SET PROPERTY 'DELAYMINS'='0';

8)  Check the datafile status in the standby database.

SELECT DISTINCT STATUS FROM V$DATAFILE;

Note: The status should be in “ONLINE” & “SYSTEM” mode. If any file/files are in RECOVER status, then identify the reason.

Check the offline datafiles in the primary & standby database

SELECT DISTINCT STATUS FROM V$DATAFILE_HEADER WHERE STATUS <> 'ONLINE';

9) Perform a log switch from the primary DB and verify logs are applied on the standby database.

IF RAC

ALTER SYSTEM ARCHIVE LOG CURRENT;

NON – RAC

ALTER SYSTEM SWITCH LOGFILE;

10) Make sure things are fine with the database on both primary (All instances) and Standby database (All instances). Check alert log, trace files from all nodes to make sure the database running without any issues.

11) check the status of the DG Broker LISTENER status, it should be up and running and check the status of the REMOTE_LISTENER, LISTENER_NETWORKS, LOCAL_LISTENER

lsnrctl status <LSNR_NAME>

12) Check FAST START FAILOVER is enabled and check the preferred STANDBY database.

13) Once the switch over process completes, Oracle DG Broker will have the same DG protection mode & Network Transmission role for the NEW PRIMARY DB.

14) Check the following

The primary database is enabled and is in the TRANSPORT-ON state.

DGMGRL> show database 'PRIMARY_DB_UNIQUE_NAME';

Note: Check for “Intended State” and it should be “TRANSPORT-ON”

 The target standby database is enabled and is in the APPLY-ON state.

DGMGRL> show database 'STANDBY_DB_UNIQUE_NAME';

Note: Check for “Intended State” and it should be “APPLY-ON”

15) Check the flashback database is enabled in the primary/standby database. If it is not enabled then enable the FLASHBACK database

SELECT FLASHBACK_ON FROM GV$DATABASE;

TIPS

“ORA-12514 during the switchover” - Check whether the StaticConnectIdentifier is set correctly

TO CHECK (NEED TO CHECK ON ALL THE INSTANCES)

SHOW INSTANCE VERBOSE 'INSTANCE_NAME' ON DATABASE 'DB_NAME'

TO CHANGE

DGMGRL> edit instance dg112i1 on database dg112i_prm set PROPERTY StaticConnectIdentifier='';

Ex

DGMGRL> edit instance dg112i1 on database dg112i_prm set PROPERTY StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.11.225)(PORT=1555))(CONNECT_DATA=(SERVICE_NAME=DG112I_PRM_DGMGRL.au.oracle.com)(INSTANCE_NAME=dg112i1)(SERVER=DEDICATED)))';

MOVING SPFILE FOR RAC DB IN ASM

$ sqlplus / as sysdba

SQL> create pfile='/home/oracle/pfile_Bhuvan1.ora' from spfile;

File created.

SQL> create spfile='+BHUVAN1' from pfile='/home/oracle/pfile_zp1.ora';

File created.

you have to find the file name details in the asmcmd
After finding the name, you have to change to the database

$ srvctl modify database -d BHU -p +BHUVAN/BHU/PARAMETERFILE/spfile.272.762954069

STOP AND START THE RAC DATASBASE

CHECK THE SPFILE LOCATION IN ALL NODES

SQL> show parameter spfile

NAME                                 TYPE        VALUE

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

spfile                               string      +BHUVAN/BHU/parameterfile

                                                 /spfile.272.762954069

Note: Remove your old spfile using rm command or ALTER DISKGROUP DISK_GROUP_NAME DROP FILE ‘OLD_LOCATION’;

MOVING CONTROLFILES IN ASM

MOVING CONTROLFILES TO DIFFERENT LOCATION IN ASM

1) BACKUP YOUR SPFILE

2) IDENTIFY THE CONTROLFILE LOCATION

SQL> show parameter control_files

3) SHUTDOWN YOUR DATABASE AND BRING IT IN NOMOUNT MODE (If you are running on the RAC, start only ONE INSTANCE)

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 7466151936 bytes

Fixed Size                  2240872 bytes

Variable Size            3774877336 bytes

Database Buffers         3674210304 bytes

Redo Buffers               14823424 bytes

4) Change the Mount point location for the controlfile

SQL> alter system set control_files='+Bhuvan','+Bhuvan2','+Bhuvan3' scope=spfile sid='*';

System altered.

5) shutdown and bring the database to nomount stage

SQL> shutdown immediate;

SQL> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 26 15:49:38 2011

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

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1219334144 bytes

Fixed Size                  2225952 bytes

Variable Size             620759264 bytes

Database Buffers          587202560 bytes

Redo Buffers                9146368 bytes

Note: you need to specify your original copy of the control file for restoring

$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Sep 26 15:50:53 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: Bhuvan (not mounted)

RMAN> restore controlfile from '+Bhuvan/bhu/controlfile/backup.256.762532857';

Starting restore at 26-SEP-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=80 instance=BHU_1 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=+BHUVAN1/bhu1/controlfile/current.268.762882663

output file name=+BHUVAN2/bhu1/controlfile/current.386.762882663

output file name=+BHUVAN3/bhu1/controlfile/current.269.762882665

Finished restore at 26-SEP-11

RMAN>

SQL> alter database mount;

Database altered.

SQL> show parameter control

NAME                                 TYPE        VALUE

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

control_file_record_keep_time        integer     30

control_files                        string      +bhuvan1/bhu1/controlfile/current.268.762882663,

                                                        +bhuvan2/bhu1/controlfile/current.386.762882663,

                                                        +bhuvan3/bhu1/controlfile/current.269.762882665

SQL> alter database open;

Database altered.

SQL>

FAST-START FAILOVER (FSF)

The following conditions must be met before you can use the broker:

■ Primary and standby DB’s must be on same version

■ You must use a SPFILE to ensure the broker can persistently reconcile values between broker properties.

■ DG_BROKER_START parameter must be set to TRUE.

■ DG_BROKER_CONFIG_FILE file should be place in the shared area for RAC.

■ Oracle Net Services network files must be set up on the primary database and on the standby database.

■ To enable DGMGRL to restart instances during the course of broker operations, a service with a specific name must be statically registered with the local listener of each instance.

■ the primary database must be opened in ARCHIVELOG mode.

■ Ensure the COMPATIBLE initialization parameter is set to the same value on all systems.

■ Flashback database should be enable for fast start failover.

Enabling Fast-Start Failover

If you have more than one standby database, then we have to select the one which will be used by the FAST START FAILOVER operation

FAST START FAILOVER CONFIGURATION

DGMGRL> show fast_start failover

Fast-Start Failover: DISABLED

  Threshold:        180 seconds

  Target:           (none)

  Observer:         (none)

  Lag Limit:        30 seconds

  Shutdown Primary: TRUE

  Auto-reinstate:   TRUE

Configurable Failover Conditions

  Health Conditions:

    Corrupted Controlfile          YES

    Corrupted Dictionary           YES

    Inaccessible Logfile            NO

    Stuck Archiver                  NO

    Datafile Offline               YES

  Oracle Error Conditions:

    (none)

ASSIGNING FAILOVER TARGET

DGMGRL> EDIT DATABASE 'BHUVAN_A' SET PROPERTY FastStartFailoverTarget = 'BHUVAN_B';

Property "faststartfailovertarget" updated

DGMGRL> EDIT DATABASE 'BHUVAN_B' SET PROPERTY FastStartFailoverTarget = 'BHUVAN_A';

Property "faststartfailovertarget" updated

DGMGRL> show fast_start failover

Note: When you have only one target on the standby database, then there is no need to specify the target.

SETTING PROTECTION MODE

DGMGRL> SHOW DATABASE 'BHUVAN_A' 'LogXptStatus';

LOG TRANSPORT STATUS

PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME               STATUS

               FE1_1                BHUVAN_B

               FE1_2                BHUVAN_B

To Display the protection mode

DGMGRL> show configuration

Configuration - DG_BHUVAN

  Protection Mode: MaxAvailability

  Databases:

    BHUVAN_A - Primary database

    BHUVAN_B - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

Enable maximum availability mode or maximum performance mode.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;

Note: 1) If you cannot tolerate any loss of data, then ensure that the configuration protection mode is set to maximum availability. To do this, the LogXptMode database property for both the primary and target standby database must be set to SYNC.

DGMGRL> EDIT DATABASE ’BHUVAN_A’ SET PROPERTY LogXptMode=SYNC;

DGMGRL> EDIT DATABASE ’BHUVAN_B’ SET PROPERTY LogXptMode=SYNC;

     2) If you can tolerate data loss, then we can go for maximum performance mode and set FastStartFailoverLagLimit. This property specifies the amount of data, in seconds, that the target standby database can lag behind the primary database in terms of redo applied.

DGMGRL> EDIT DATABASE ’BHUVAN_A’ SET PROPERTY LogXptMode=ASYNC;

DGMGRL> EDIT DATABASE ’BHUVAN_B’ SET PROPERTY LogXptMode=ASYNC;

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxPerformance;

DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverLagLimit=45;

FAST START FAILOVER CONFIGURATION PROPERTY.

Fast-start failover will occur if both the observer and the target standby database lose connection to the primary database for the period of time specified by the FastStartFailoverThreshold configuration property.

DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = '60';

Property "faststartfailoverthreshold" updated

Note: 1) Setting the Threshold value for RAC System[ID 1319917.1]

Check the css value from the cluster environment

$ crsctl get css misscount

CRS-4678: Successful get misscount 30 for Cluster Synchronization Services.

Add 30 to 30 sec extra. I have 30 sec for my miss count. So I am setting this value as 60

2) Setting FastStartFailoverPmyShutdown

If the FastStartFailoverPmyShutdown configuration property is set to TRUE, the primary database will shut down after FastStartFailoverThreshold seconds has elapsed if redo generation has been stalled and the primary database is unable to re-establish connectivity with either the observer or target standby database.

DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverPmyShutdown = 'TRUE';

Property "faststartfailoverpmyshutdown" updated

DGMGRL> show fast_start failover;

Fast-Start Failover: DISABLED

  Threshold:        60 seconds

  Target:           (none)

  Observer:         (none)

  Lag Limit:        30 seconds

  Shutdown Primary: TRUE

  Auto-reinstate:   TRUE

Configurable Failover Conditions

  Health Conditions:

    Corrupted Controlfile          YES

    Corrupted Dictionary           YES

    Inaccessible Logfile            NO

    Stuck Archiver                  NO

    Datafile Offline               YES

  Oracle Error Conditions:

    (none)

3) Setting FastStartFailoverAutoReinstate

This configuration property causes the former primary database to be automatically reinstated if a fast-start failover was initiated because the primary database was either isolated or had crashed.

DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverAutoReinstate = 'TRUE';

Property "faststartfailoverautoreinstate" updated

4) ObserverConnectIdentifier, This database property is used to specify how the observer should connect to and monitor the primary and standby database. Set this property for the

Primary and target standby database if you want the observer to use a different connect identifier than that used to ship redo data (that is, the connect identifier specified by the DGConnectIdentifierproperty).

DGMGRL> EDIT DATABASE ‘DB_NAME’ SET PROPERTY ObserverConnectIdentifier = ' ';

5) Enable additional fast-start failover conditions

Fast-start failover is done when both the observer and the standby cannot reach the primary after the configured time threshold (FastStartFailoverThreshold) has passed.

You can optionally indicate the database health conditions that should cause fast-start failover to occur.

Below parameters are enable by default.

1) A datafile is offline because of a write error

2) Dictionary corruption of a critical database object

3) Control file damaged because of a disk error

4) LGWR is unable to write to any member of the log group because of an I/O error

5) Archive is unable to archive a redo log because the device is full or unavailable

6) Primary to observer and primary to standby network failure

7) An instance crash occurs (single instance)

8) All instances of a rac crash

9) Shutdown abort of primary

10) You can specify a error message, if you want to start the Fast start failover process. When I get ORA-xxxxx error is detected on the primary database with the following command:

DGMGRL> ENABLE FAST_START FAILOVER CONDITION xxxxx;

DGMGRL> enable fast_start failover condition "Inaccessible Logfile";

Succeeded.

DGMGRL> enable fast_start failover condition "Stuck Archiver";

Succeeded.

ENABLE FAST-START FAILOVER

DGMGRL> enable fast_start failover;

Enabled.

DGMGRL> show fast_start failover;

Fast-Start Failover: ENABLED

  Threshold:        60 seconds

  Target:           BHUVAN_B

  Observer:         (none)

  Lag Limit:        30 seconds (not in use)

  Shutdown Primary: TRUE

  Auto-reinstate:   TRUE

Configurable Failover Conditions

  Health Conditions:

    Corrupted Controlfile          YES

    Corrupted Dictionary           YES

    Inaccessible Logfile            NO

    Stuck Archiver                  NO

    Datafile Offline               YES

  Oracle Error Conditions:

    (none)

Start the Observer

Must install DGMGRL on an observer computer (Not on the same DB server)

1) Install complete Oracle Client Administrator

2) Install a full db installation

1. PRE-REQS Must be in max availability or max performance

2. LogXptMode

LogXptMode must be in SYNC in max availability for 11g

LogXptMode must be in ASYNC in max performance for 11g

3. FLASHBACK DB must be enabled on primary and standby

4. tnsnames.ora must be configured on the observer

5. A static service name must exist so the observer can automatically restart databases.

You can start the observer before or after you enable fast-start failover. If fast-start failover is already enabled, the observer immediately begins monitoring the status and connections to the primary and target standby databases. If fast-start failover is not already enabled, the observer waits until fast-start failover gets enabled and then begins monitoring.

#!/bin/ksh

# startobserver

dgmgrl -logfile 11g_observer.log << eof

connect sys/oracle@bhuvan

START OBSERVER;

Eof

You can check the process in the unix side, whether the process is running

Ps –ef|grep filename

Tips

1) Error “ORA-16820”

Error message: Fast-Start Failover observer is no longer observing this db

Solution è Check the reason why the observer cannot contact this database. If the problem cannot be corrected, stop the current observer by connecting to the Data Guard configuration and issue the DGMGRL "STOP OBSERVER" command. Then restart the observer

2) To get more information about the configuration

DGMGRL> show configuration verbose;

DGMGRL> show database verbose ‘DB_NAME’;

3) OBSERVER CONFIGURATION

DGMGRL> START OBSERVER FILE=/oracle/observer/obs.dat;

If file is not set the current working directory is searched for a file name FSFO.dat.

4) To view FAST START FAIL OVER INFORMATION (PRIMARY & STANDBY)

SQL> SELECT FS_FAILOVER_STATUS,FS_FAILOVER_CURRENT_TARGET, db_unique_name, FS_FAILOVER_THRESHOLD, FS_FAILOVER_OBSERVER_PRESENT,FS_FAILOVER_OBSERVER_HOST

FROM V$DATABASE;

5) To view the reason for the FAST START FAILOVER

SELECT LAST_FAILOVER_TIME, LAST_FAILOVER_REASON FROM

V$FS_FAILOVER_STATUS;

FAST-START FAILOVER OBSERVER (FSFO)

Must install DGMGRL on an observer computer (Not on the same DB server)

1) Install complete Oracle Client Administrator

2) Install a full db installation

1. PRE-REQS Must be in max availability or max performance

2. LogXptMode

LogXptMode must be in SYNC in max availability for 11g

LogXptMode must be in ASYNC in max performance for 11g

3. FLASHBACK DB must be enabled on primary and standby

4. tnsnames.ora must be configured on the observer

5. A static service name must exist so the observer can automatically restart databases.

You can start the observer before or after you enable fast-start failover. If fast-start failover is already enabled, the observer immediately begins monitoring the status and connections to the primary and target standby databases. If fast-start failover is not already enabled, the observer waits until fast-start failover gets enabled and then begins monitoring.

#!/bin/ksh

# startobserver

dgmgrl -logfile 11g_observer.log << eof

connect sys/oracle@bhuvan

START OBSERVER;

Eof

You can check the process in the unix side, whether the process is running

Ps –ef|grep filename