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:
[code language=”sql”]
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)
[/code]
Test 1:
[code language=”sql”]
SQL: exec sh.RunTest;
[/code]
Then after 20 seconds, we kill smon, pmon and lgwr processes!
[code language=”sql”]
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8359
Session ID: 72 Serial number: 49680
[/code]
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:
[code language=”sql”]
select count (*) from sh.testcommit;
COUNT(*)
———-
41572
[/code]
On the primary:
[code language=”sql”]
select count (*) from sh.testcommit;
COUNT(*)
———-
41573
[/code]
In the file:
[code language=”sql”]
inserting 41573 value
[/code]
Wouaou, we have a gap on standby!
But wait a minute, let’s failover…after restarting the listeners
[code language=”sql”]
DGMGRL: failover to ‘PROD52’;
Performing failover NOW, please wait…
Failover succeeded, new primary is "PROD52"
SQL: select count (*) from sh.testcommit;
COUNT(*)
———-
41573
[/code]
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.
[code language=”sql”]
reinstate database ‘PROD5’;
Reinstating database "PROD5", please wait…
Reinstatement of database "PROD5" succeeded
[/code]
Then switchover back to PROD5
[code language=”sql”]
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"
[/code]
Test 2:
[code language=”sql”]
alter system set commit_logging = batch;
alter system set commit_wait = nowait; — This is the parameter that gives most benefits
SQL: exec sh.RunTest;
[/code]
Then after 20 seconds, we kill smon, pmon and lgwr processes!
[code language=”sql”]
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8359
Session ID: 72 Serial number: 49680
[/code]
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:
[code language=”sql”]
select count (*) from sh.testcommit;
COUNT(*)
———-
379054
[/code]
Note: You can see that it’s going much faster.
On the primary:
[code language=”sql”]
select count (*) from sh.testcommit;
COUNT(*)
———-
410407
[/code]
Wouaou, huge gap here, 31353 rows!
In the file:
[code language=”sql”]
inserting 471680 value
[/code]
So we lost 61273 sequence numbers so 61273 comited rows are gone!
Let’s try to failover failover…after restarting the listeners
[code language=”sql”]
DGMGRL: failover to ‘PROD52’;
Performing failover NOW, please wait…
Failover succeeded, new primary is "PROD52"
SQL: select count (*) from sh.testcommit;
COUNT(*)
———-
410407
[/code]
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


     

Leave a Reply

Your email address will not be published.