Oracle Data Guard 12c Testing Scenarios

Objective

The objective of this post is to show the detailed steps for:

  • Testing Read-Only access to the Standby Database
  • Testing Active Data Guard Access to the Standby Database
  • Testing Switching over/back to/from Standby Database
  • Testing Failover to the Standby

Current Data Guard Environment

1

Testing Read-Only access to the Standby Database

On the current primary database create a test user and test table as follows:

2

To open the standby database “proddr” in read-only mode:
Login to the DG Manager (from any node) and stop the log apply service:

3

Open the standby in read-only mode:
Login to any node in the standby RAC:

4
5

Now login to the standby database as user “ahmed” and select data from the emp table:

6

==> This confirms the read-only access to the standby database

Testing Active Data Guard Access to the Standby Database

Now enable the Active Data Guard, by enabling redo application:

7

Now insert some data on the primary and ensure it is visible from standby also:
Login to the primary database as user ahmed:

8

Now login to the standby database as user ahmed and ensure that the new data is visible there:

9

Now stop the Active Data Guard mode:

srvctl stop database -d proddr
srvctl start database -d proddr -o mount
oradr@standby02:~$ srvctl status database -d proddr
Instance proddr1 is running on node proddr1
Instance proddr2 is running on node proddr2

10
11

Switchover/Switch Back Testing

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.

On Dr Server set the Environment to the DR database PRODDR and login via sys:

12
13
14
15

Now Switchover to the DR Database proddr:

16
17
18
19
20
21

Let us test switching back to prod:

22
23

HA Service Status After Switchover

Assuming that we created the following HA sevrice “PRODFO” that is active only on the database with role=’PRIMARY’:

begin
dbms_service.create_service(‘PRODFO ‘, ‘PRODFO ‘);
end;
/
begin
dbms_service.start_service(‘PRODFO’);
end;
/

create or replace trigger DR_Service after startup on database
declare
v_role varchar(30);
begin
select database_role into v_role from v$database;
if v_role =’PRIMARY’ then
DBMS_service.start_service(‘PRODFO’,DBMS_SERVICE.ALL_INSTANCES);
else
DBMS_service.stop_service(‘PRODFO’,DBMS_SERVICE.ALL_INSTANCES);
end if;
end;
/

PRODFO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <primary-cluster-scan>)(PORT = 1526))
(ADDRESS = (PROTOCOL = TCP)(HOST = <standby-scan>)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRODFO)
)
)

Now from any client , we will connect to our database using any application user:

24

So, the application user can use this service to connect to the database irrespective of its current site (Primary or Standby).

Failover Testing

This option should be your last choice and you must first try to recover your primary database. If this cannot be done or not done within a correct time frame you can perform a (complete) failover. Please note that your initial primary will be lost (you will need to reinstate or recreate it from backup).

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

Assuming that the current DG configuration is as follows:

25

If possible and you have access to the primary server, then you need to remove your primary database from Oracle cluster configuration to avoid an automatic startup. To check current status use:
srvctl config database -d prod –a
srvctl disable database -d prod
If applicable, you may need to stop the primary database and/or primary server

Now failover to the standby database as follows:
Login to the standby server and set the oracle database environment

26

Donate to support our blogging work

$1.00

Thanks
Ahmed

One thought on “Oracle Data Guard 12c Testing Scenarios

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s