Commit_logging and Commit_wait in SYNC DataGuard configuration

Hi there,

In this post, I would like to show the effect of commit_logging and commit_wait parameters with physical standby in SYNC mode.

To clearly understand how those two parameters work, you can first read this nice article from my colleague Chris Antognini. here

Keep in mind that you should make aware the customer about the possible violation of the D of ACID in some extreme circumstances.

The configuration is the following:

Production database: PROD5
Physical Standby: PROD52, opened read-only
Protection mode: Max Availability (SYNC)

The test case:


alter system set commit_logging = immediate;
alter system set commit_wait = wait;

drop table sh.testcommit;
drop sequence sh.seqtest;
create table sh.testcommit (code number, name varchar2(50));
create sequence sh.seqtest cache 5000;

Create or replace procedure sh.RunTest as
lVal Number;
F1  UTL_FILE.file_type; -- For Tracing Sequence progression
begin

F1 := UTL_FILE.fopen ('DATA_PUMP_DIR','check.txt','w');

for i in 1..3000000 loop

select sh.seqtest.nextval into lVal from dual;
insert into sh.testcommit values (lVal, to_char (lVal)||' value');
UTL_FILE.put_line (F1,' inserting '||to_char (lVal)||' value', true);
commit;

end loop;
UTL_FILE.fclose (F1);
end;
/ 

DGMGRL: show configuration;

Configuration - prod

Protection Mode: MaxAvailability
Members:
PROD5 - Primary database
PROD52 - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 6 seconds ago)

Test 1:

SQL: exec sh.RunTest;

Then after 20 seconds, we kill smon, pmon and lgwr processes!

ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8359
Session ID: 72 Serial number: 49680

In order to check the effect, we stop listeners on both servers.
The we start the primary and we check number of rows.

On the standby:

select count (*) from sh.testcommit;
 COUNT(*)
----------
 41572

On the primary:

select count (*) from sh.testcommit;
 COUNT(*)
----------
 41573

In the file:

inserting 41573 value

Wouaou, we have a gap on standby!
But wait a minute, let’s failover…after restarting the listeners

DGMGRL: failover to 'PROD52';
Performing failover NOW, please wait...
Failover succeeded, new primary is "PROD52"

SQL: select count (*) from sh.testcommit;

 COUNT(*)
----------
 41573

Ok, transmitted but not yet applied.
So we are fine.

Lesson learned, if you lose you production, do immediately a failover if you standby is opened!

Before continuing with test 2, let’s but back the production to PROD5.
First we reinstate the PROD5 database.

reinstate database 'PROD5';
Reinstating database "PROD5", please wait...
Reinstatement of database "PROD5" succeeded

Then switchover back to PROD5

DMGRL: switchover to 'PROD5';
Performing switchover NOW, please wait...
Operation requires a connection to instance "PROD5" on database "PROD5"
Connecting to instance "PROD5"...
Connected as SYSDBA.
New primary database "PROD5" is opening...
Operation requires start up of instance "PROD5" on database "PROD52"
Starting instance "PROD5"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "PROD5"

Test 2:

alter system set commit_logging = batch;
alter system set commit_wait = nowait; -- This is the parameter that gives most benefits

SQL: exec sh.RunTest;

Then after 20 seconds, we kill smon, pmon and lgwr processes!

ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8359
Session ID: 72 Serial number: 49680

In order to check the effect, we stop listeners on both servers.
The we start the primary and we check number of rows.

On the standby:

select count (*) from sh.testcommit;
 COUNT(*)
----------
 379054

Note: You can see that it’s going much faster.

On the primary:

select count (*) from sh.testcommit;
 COUNT(*)
----------
 410407

Wouaou, huge gap here, 31353 rows!

In the file:

inserting 471680 value

So we lost 61273 sequence numbers so 61273 comited rows are gone!

Let’s try to failover failover…after restarting the listeners

DGMGRL: failover to 'PROD52';
Performing failover NOW, please wait...
Failover succeeded, new primary is "PROD52"

SQL: select count (*) from sh.testcommit;

 COUNT(*)
----------
 410407

We have our rows and hopefully we are sync with production.

Conclusion:

Commit_logging and Commit_wait parameters can considerably speed up your transactions and your standby will of course alway be sync in case of production crash if you are in MaxAvailability mode.

But, remember in case you lose your production, do immediately a failover !

jko

The following two tabs change content below.

Jacques

I am Oracle Certified Master 11g & 12c database architect with significant experience in heterogeneous environments, and strong ability to lead complex and critical projects requiring multiple technical implementations. at Trivadis SA

1 thought on “Commit_logging and Commit_wait in SYNC DataGuard configuration

Leave a Reply

Your email address will not be published. Required fields are marked *