Hungry DBA .com for DBAs who like food

Configuring the DR Databases

The Oracle DR configurations on both the primary and standby servers are identical.  Thus the alias TEST points to the primary server and STTEST points to the standby server on both environments.  Only the TNSNAMES.ORA file differs on each server defining the aliases TEST and STTEST.  For example on the primary server PRSERVER, TEST points to PRSERVER as the primary server and STSERVER as the standby; where as on the standby server STSERVER the alias TEST points to STSERVER as the primary server and PRSERVER as the standby (opposite to that on PRSERVER). 

TNSNAMES.ORA File on the primary server PRSERVER:

TEST.DBSUPPORTSERVICES.CO.UK =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = TEST)

    )

  )

STTEST.DBSUPPORTSERVICES.CO.UK =

  (DESCRIPTION = 

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = TEST)

    )

  )

TNSNAMES.ORA File on the standby server STSERVER:

TEST.DBSUPPORTSERVICES.CO.UK =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = TEST)

    )

  )

STTEST.DBSUPPORTSERVICES.CO.UK =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = TEST)

    )

  )

Relevant Initialisation Parameters on both Production and DR Servers:

*.log_archive_dest_1='LOCATION=M:\Oracle-Databases\TEST\archive'

*.log_archive_dest_2='SERVICE=STTEST.DBSUPPORTSERVICES.CO.UK REOPEN=60'

*.log_archive_dest_state_2='ENABLE'

*.standby_archive_dest='P:\Oracle-Databases\TEST\archive'

*.standby_file_management='AUTO'

*.fal_client='TEST.DBSUPPORTSERVICES.CO.UK'

*.fal_server='STTEST.DBSUPPORTSERVICES.CO.UK'

*.archive_lag_target=900 

Configuring the environments as above will allow log replication to continue after a clean switchover so that it is easy to switch back and forth between servers.  For example PRSERVER can become a standby database while STSERVER is primary and vice Aversa.

Creating a Standby Database from the Primary via RMAN

On production server: 

1.       Launch RMAN to connect to the TEST database and check the current RMAN configuration:

C:\ >set ORACLE_SID=TEST

C:\ >rman target /

Recovery Manager: Release 9.2.0.6.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: TEST (DBID=1949209861) 

RMAN> show all; 

using target database controlfile instead of recovery catalog

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;

CONFIGURE BACKUP OPTIMIZATION OFF;

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'I:\Oracle_Backups\%d_%F.rman';

CONFIGURE DEVICE TYPE DISK PARALLELISM 1;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   'I:\Oracle_Backups\%d_%s_%p.rman';

CONFIGURE MAXSETSIZE TO UNLIMITED;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\ORACLE\ORA92\DATABASE\SNCFTEST.ORA';

2.       Backup the current production database to create a standby database:

RMAN> backup database include current controlfile for standby plus archivelog;

Manually copy the backup sets from I:\Oracle_Backups on the production server to I:\Oracle_Backups on the DR Server (location of backups must match on both production and DR).

On the DR Server start up the TEST database in nomount mode:

C:\> set ORACLE_SID=TEST

C:\> sqlplus /nolog

SQL> connect / as sysdba

SQL> startup nomount

SQL> exit

Create the standby database using RMAN (This assumes the database file structures will be identical on both servers):

C:\> RMAN target ‘sys/fluffy@STTEST’ auxiliary /

RMAN> duplicate target database for standby nofilenamecheck dorecover; 

6.       Once the DR database is created; you will need to manually add a tempfile:

SQL> alter database open read only;

SQL> alter tablespace temp add tempfile ‘F:\Oracle-Databases\TEST\TEMP01.DBF’ size 500M; 

7.       Put the DR database into managed standby mode:

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect; 

8.       On the production database switch logs to initiate replication:

SQL> alter system switch logfile;

The configuration of Dataguard is now complete.

Invoking a DR Standby Database

Overview

There are different methods you can use to invoke a standby database depending on the typical DR scenario you are facing.

Method

Description

Scenario for usage.

Database Switchover

Using this method you can switch backwards and forwards between the primary and DR servers (e.g. so that the primary can become DR and DR can become primary) without having to rebuild either environment. 

This scenario can be used if both the Primary and Standby Servers are available so that you can connect to both environments simultaneously and perform a clean switchover.

Activating a Standby Database

This method will activate the Standby Database as the primary database recovering up to the point of the last log shipment.  After activating a standby database as the primary the original primary server becomes obsolete and will need to be rebuilt as a standby database (e.g. you can not just switch the servers back to act as a primary or standby database and will need to rebuild the data-guard environments).

This scenario can be used if the primary server is not available and you need to force the standby database to become the primary.

Opening the standby Database in Read-Only Mode

This method involves stopping data-guard replication and opening the standby database in read-only mode for querying.  The database can then be shutdown and reopened in standby mode so that data-guard replication can resume (assuming all required archive logs are still available)

This scenario can be used if you want to open the standby database temporarily for querying data and then wish to put the database back into standby mode.

The following section describes how to perform each of the above methods of invoking a standby database:

Database Switchover

Using this method you can switch backwards and forwards between the primary and DR servers (e.g. so that the primary can become DR and DR can become primary) without having to rebuild either environment: 

On Primary Server:

SQL> alter database commit to switchover to standby; 

This may cause the following error to be generated:

ERROR at line 1:

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

If this does occur then restart the database, as below, before retrying the above command:

SQL> shutdown immediate

SQL> startup

 

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect; 

The primary server is now configured as a DR standby database. 

On DR Server:

SQL> alter database recover managed standby database cancel;

SQL> alter database commit to switchover to primary;

SQL> shutdown immediate

SQL> startup 

The DR server is now configured as the primary database.

To switch back you just need to repeat the above process but the other way around (e.g. convert the DR database back to a standby and the primary database back to primary).

Activating a Standby Database

If the primary database is not available the standby database can be converted into the primary database as follows: 

SQL> alter database recover managed standby database cancel;

SQL> alter database activate standby database;

SQL> shutdown immediate

SQL> startup 

The original primary database is now obsolete and can be rebuilt as a standby database once it is available again.

Opening the Standby Database in Read Only Mode

The standby database can be opened in read only mode for querying and then converted back into a standby database without affecting the primary. 

On standby server:

SQL> alter database recover managed standby database cancel;

SQL> alter database open read only; 

The standby database is now open and available for querying in read only mode.

To put the standby database back into standby mode: 

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect; 

How to check whether the Standby Database is in Sync  

On the primary server:

SQL> SELECT max(sequence#) AS "PRIMARY" FROM v$log_history;  

On the standby server:

SQL> SELECT max(sequence#) AS "STANDBY", applied

 FROM v$archived_log GROUP BY applied;

The standby database is in sync with the primary database if the above PRIMARY value matches the above STANDBY value where applied = 'YES'.