Data Guard Setup

The source database tst that will serve as a primary database is configured as specified in Annex 1. In the section a physical standby database will be created and Data Guard broker will be configured.

1. Enable force logging.
   

   SQL> alter database force logging;
   

2. Create Standby Redo Logs (SRL). In Annex 2 is the information related to the online redo log (ORL) files in the database. As there are three redo log groups with two members each we will create four members for each thread. Note that OMF (db_create_file_dest=’+DGDUP’) are used and the SRL are created with the same size as ORL.
   

   alter database add standby logfile thread 1 group 7 size 104857600;

   alter database add standby logfile thread 1 group 8 size 104857600;

   alter database add standby logfile thread 1 group 9 size 104857600;

   alter database add standby logfile thread 1 group 10 size 104857600;

   alter database add standby logfile thread 2 group 11 size 104857600;

   alter database add standby logfile thread 2 group 12 size 104857600;

   alter database add standby logfile thread 2 group 13 size 104857600;

   alter database add standby logfile thread 2 group 14 size 104857600;

3. Configure redo transport on the primary for each instance to the standby database.
   

   SQL> alter system set log_archive_config=’dg_config=(tst,tststby)’ sid=’*’ scope=both;

   System altered.

   SQL>

   SQL> alter system set log_archive_dest_2=’service=tststby SYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=tststby’ sid=’*’ scope=both;

   System altered.

   SQL>
   

4. Configure tnsnames alias and a static registration with the GI listeners for both primary and standby database and compliant with Data Guard Broker. With Oracle 11gR2 since the SCAN Listener was introduced the modification is made to both listeners in GI $OH and to the tnsnames.ora in RDBMS $OH. Annex 3 contains the tnsnames.ora and listener.ora. Reload the listeners.
   

   TSTSTBY

   (DESCRIPTION =

   (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))

   (CONNECT_DATA =

   (SERVER = DEDICATED)

   (SID_NAME = tststby)

   )

   )

   SID_LIST_LISTENER =

   (SID_LIST =

   (SID_DESC =

   (GLOBAL_DBNAME = tst)

   (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)

   (SID_NAME = tst)

   )

   (SID_DESC =

   (GLOBAL_DBNAME = tststby)

   (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)

   (SID_NAME = tststby)

   )

   (SID_DESC =

   (GLOBAL_DBNAME = tst_DGMGRL)

   (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)

   (SID_NAME = tst)

   )

   (SID_DESC =

   (GLOBAL_DBNAME = tststby_DGMGRL)

   (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)

   (SID_NAME = tststby)

   )

   )

5. Create a oracle password file for the standby. To avoid error ORA-16191 specify ignorecase=y. The standby will be created as a single instance database and then will be RAC enabled. That is why three password files are created.
   

   orapwd file=orapwtststby entries=100 password=sys1 ignorecase=y

   orapwd file=orapwtststby1 entries=100 password=sys1 ignorecase=y

   orapwd file=orapwtststby2 entries=100 password=sys1 ignorecase=y

6. Create a pfile for the standby database based on the pfile from the primary database.as in Annex 4 and set *.cluster_database=FALSE and *.db_name=’tststby’.
   
7. Create $ORACLE_BASE/admin/tststby/adump directory on the standby DR site.
   
8. Verify that connection to primary and standby can be made using the tnsnames aliases using the Oracle password files.
   

   sqlplus sys/sys1@tst as sysdba

   sqlplus sys/sys1@tst stbyas sysdba

9. Prepare RMAN scripts to create the standby and set the parameters for the standby database. Start the instance on the standby DR site using the pfile created in step 6. Invoke RMAN connect to target and auxiliary instances. Make sure to connect to auxiliary using the tnsnsmes alias to avoid errors.Run the script. The output from the script is in Annex 5.
   

   run {

   allocate channel tst type disk;

   allocate channel tst1 type disk;

   allocate auxiliary channel tststby type disk;

   duplicate target database for standby from active database DORECOVER

   spfile

   parameter_value_convert “tst”,”tststby”

   set db_unique_name=”tststby”

   set db_file_name_convert=”+DGDUP/tst”,”+DGDUP/tststby”

   set log_file_name_convert=”+DGDUP/tst”,”+DGDUP/tststby”

   set fal_client=”tststby”

   set fal_server=”tst”

   set standby_file_management=”AUTO”

   set log_archive_config=”dg_config=(tst,tststby)”

   set log_archive_dest_2=”service=tst SYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=tst”;}

10. Register the standby database with OCR. Create a shared ASM based spfile.. Make sure that *.cluster_database=TRUE and each instance pfile pints to the ASM based spfile. See Annex 6 for more information.
    

    [oracle@raclinux2 dbs]$ srvctl add database -d tststby -o /u01/app/oracle/product/11.2.0/db_10 -s mount -r physical_standby -c RAC

    [oracle@raclinux2 dbs]$ srvctl add instance -d tststby -i tststby2 -n raclinux2

    [oracle@raclinux2 dbs]$ srvctl add instance -d tststby -i tststby1 -n raclinux1

    Create a shared spfile on ASM.

    SQL> create spfile=’+DGDUP/spfiletststby.ora’ from memory;

    File created.

    SQL>

    Make sure

    [oracle@raclinux2 dbs]$ cat inittststby2.ora for instance 1 as well

    SPFILE=’+DGDUP/spfiletstsstby.ora’

    [oracle@raclinux2 dbs]$

    [oracle@raclinux2 trace]$ srvctl add service -d tststby -s tststby_s -r tststby2 -a tststby1

    [oracle@raclinux2 dbs]$ srvctl modify database -d tststby -p ’+DGDUP/spfiletststby.ora’

    [oracle@raclinux2 dbs]$ srvctl config database -d tststby

    Database unique name: tststby

    Database name:

    Oracle home: /u01/app/oracle/product/11.2.0/db_10

    Oracle user: oracle

    Spfile: +DGDUP/spfiletststby.ora

    Domain:

    Start options: mount

    Stop options: immediate

    Database role: PRIMARY

    Management policy: AUTOMATIC

    Server pools: tststby

    Database instances: tststby1,tststby2

    Disk Groups: DGDUP

    Mount point paths:

    Services: tststby_s

    Type: RAC

    Database is administrator managed

    [oracle@raclinux2 dbs]$

11. Make sure that the standby database is operational. Test redo shipping and redo apply. See Annex 7 for details.
    

    SQL> select PROTECTION_MODE, PROTECTION_LEVEL, OPEN_MODE, DATABASE_ROLE from v$database;

    PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE DATABASE_ROLE

    ——————– ——————– ——————– —————-

    MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE MOUNTED PHYSICAL STANDBY

    SQL>

12. Set Data Guard Broker. Execute the following SQL on both primary and standby database.
    

    SQL> alter system set dg_broker_start=true scope=both sid=’*';
    

13. Create Data Guard Configuration. From dgmgrl execute the following commands.
    

DGMGRL> connect /

Connected.

DGMGRL> create configuration “DataGuard” as primary database is “tst” connect identifier is tst;

Configuration “DataGuard” created with primary database “tst”

DGMGRL>

DGMGRL> add database “tststby” as connect identifier is tststby;

Database “tststby” added

DGMGRL>

DGMGRL> enable configuration;

Enabled.

DGMGRL>

DGMGRL> show configuration;

Configuration – DataGuard

Protection Mode: MaxPerformance

Databases:

tst – Primary database

Warning: ORA-16532: Data Guard broker configuration does not exist

tststby – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

WARNING

DGMGRL>

See the resolution of the error in point 1 in Error and Fixes. After locating the files to ASM shared among the RAC instances we have,

DGMGRL> show configuration;

Configuration – DataGuard

Protection Mode: MaxPerformance

Databases:

tst – Primary database

tststby – Physical standby database (disabled)

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>

DGMGRL> show configuration verbose;

Configuration – DataGuard

Protection Mode: MaxPerformance

Databases:

tst – Primary database

tststby – Physical standby database (disabled)

Properties:

FastStartFailoverThreshold = ’30′

OperationTimeout = ’30′

FastStartFailoverLagLimit = ’30′

CommunicationTimeout = ’180′

FastStartFailoverAutoReinstate = ‘TRUE’

FastStartFailoverPmyShutdown = ‘TRUE’

BystandersFollowRoleChange = ‘ALL’

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>

DGMGRL> show database “tst”;

Database – tst

Role: PRIMARY

Intended State: TRANSPORT-ON

Instance(s):

tst1

tst2

Database Status:

SUCCESS

DGMGRL> show database “tststby”;

Database – tststby

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: (unknown)

Apply Lag: (unknown)

Real Time Query: OFF

Instance(s):

tststby2

Database Status:

DISABLED

DGMGRL>

1. Turn Flashback Database feature on both Primary and Standby database.
   

   Issue on the primary
   

   SQL> alter database flashback on;

   Database altered.

   SQL>

   Issue on the Standby

   DGMGRL> edit database “tststby” set state=’apply-off’;

   Succeeded.

   DGMGRL>

   SQL> alter database flashback on;

   Database altered.

   SQL>

   DGMGRL> edit database “tststby” set state=’apply-on’;

   Succeeded.

   DGMGRL>

2. Set Fast Start failover.
   

   DGMGRL> show fast_start failover;

   Fast-Start Failover: DISABLED

   Threshold: 30 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)

   DGMGRL> enable fast_start failover;

   Enabled.

   DGMGRL> show fast_start failover;

   Fast-Start Failover: ENABLED

   Threshold: 30 seconds

   Target: tststby

   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)

   DGMGRL>

Solution Verification

In order to make sure that DR Data Guard implementation works I will implement a switchover, failover, reinstate a former primary as a standby after failover and some troubleshooting..

1. Switchover
   

   DGMGRL> switchover to “tststby”;

   Performing switchover NOW, please wait…

   New primary database “tststby” is opening…

   Operation requires shutdown of instance “tst1″ on database “tst”

   Shutting down instance “tst1″…

   ORA-01031: insufficient privileges

   Warning: You are no longer connected to ORACLE.

   Please complete the following steps to finish switchover:

   shut down instance “tst1″ of database “tst”

   start up and mount instance “tst1″ of database “tst”

   DGMGRL>

   From sqlplus perform the requested activities.

   DGMGRL> show configuration;

   Configuration – DataGuard

   Protection Mode: MaxPerformance

   Databases:

   tststby – Primary database

   tst – (*) Physical standby database

   Fast-Start Failover: ENABLED

   Configuration Status:

   SUCCESS

   DGMGRL>

2. Changing a protection Mode. Depending on how Redo transport is set the protection mode can be easily changed as shown below.
   

   DGMGRL> edit configuration set PROTECTION MODE AS MaxAvailability;

   Succeeded.

   DGMGRL> show configuration;

   Configuration – DataGuard

   Protection Mode: MaxAvailability

   Databases:

   tst – Primary database

   tststby – Physical standby database

   Fast-Start Failover: DISABLED

   Configuration Status:

   SUCCESS

   DGMGRL> edit configuration set PROTECTION MODE AS MaxProtection;

   Succeeded.

   DGMGRL> show configuration;

   Configuration – DataGuard

   Protection Mode: MaxProtection

   Databases:

   tst – Primary database

   tststby – Physical standby database

   Fast-Start Failover: DISABLED

   Configuration Status:

   SUCCESS

   DGMGRL>

   SQL> select NAME, DATABASE_ROLE, DATAGUARD_BROKER, PROTECTION_MODE,PROTECTION_LEVEL from v$database;

   NAME DATABASE_ROLE DATAGUAR PROTECTION_MODE PROTECTION_LEVEL

   ——— —————- ——– ——————– ——————–

   TST PRIMARY ENABLED MAXIMUM PROTECTION MAXIMUM PROTECTION

   SQL>

3. Starting with Oracle 11.2.0.2 a SQL can be executed from dgmgrl utility. See an example of executing SQL.
   

   [oracle@raclinux1 ~]$ dgmgrl

   DGMGRL for Linux: Version 11.2.0.2.0 – 64bit Production

   Copyright (c) 2000, 2009, Oracle. All rights reserved.

   Welcome to DGMGRL, type “help” for information.

   DGMGRL> connect /

   Connected.

   DGMGRL> sql “alter system archive log current”;

   Succeeded.

   DGMGRL> sql “alter system archive log current”;

   Succeeded.

   DGMGRL> sql “alter system archive log current”;

   Succeeded.

   DGMGRL>

4. Failover. Connect to the standby database to perform the failover.
   

   DGMGRL> connect sys/sys1@tststby

   Connected.

   DGMGRL> failover to “tststby”;

   Performing failover NOW, please wait…

   Failover succeeded, new primary is “tststby”

   DGMGRL>

1. Reinstate. the former primary database.
   

   DGMGRL> connect sys/sys1@tststby

   Connected.

   DGMGRL> failover to “tststby”;

   Performing failover NOW, please wait…

   Failover succeeded, new primary is “tststby”

   DGMGRL> reinstate database “tst”;

   Reinstating database “tst”, please wait…

   Operation requires shutdown of instance “tst1″ on database “tst”

   Shutting down instance “tst1″…

   ORA-03113: end-of-file on communication channel

   Please complete the following steps and reissue the REINSTATE command:

   shut down instance “tst1″ of database “tst”

   start up and mount instance “tst1″ of database “tst”

   DGMGRL>

   DGMGRL> connect /

   Connected.

   DGMGRL> show configuration;

   Configuration – DataGuard

   Protection Mode: MaxPerformance

   Databases:

   tststby – Primary database

   tst – Physical standby database (disabled)

   ORA-16661: the standby database needs to be reinstated

   Fast-Start Failover: DISABLED

   Configuration Status:

   SUCCESS

   DGMGRL> reinstate database “tst”;

   Reinstating database “tst”, please wait…

   Reinstatement of database “tst” succeeded

   DGMGRL>

   DGMGRL> show configuration;

   Configuration – DataGuard

   Protection Mode: MaxPerformance

   Databases:

   tst – Primary database

   tststby – Physical standby database

   Fast-Start Failover: DISABLED

   Configuration Status:

   SUCCESS

   DGMGRL>