• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 955
  • Last Modified:

Oracle 11g Dataguard LGWR issue

Hi all ,

I have following parameters on my primary db

log_archive_dest_2=service=QS81TWSA.world db_unique_name=QS81TWS valid_for=                (all_logfiles, primary_role)

select archiver,process,status,target,register,transmit_mode,affirm,type from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';

ARCHIVER   PROCESS    STATUS    TARGET  REG TRANSMIT_MOD AFF TYPE
---------- ---------- --------- ------- --- ------------ --- -------
LGWR       LGWR       VALID     STANDBY YES ASYNCHRONOUS NO  PUBLIC

I inserted a row in a table and commited . It didn't take any time to commit as transport_mode=aync and affirm=no  as expected

Now , I changed log_archive_dest_2=service=QS81TWSA.world db_unique_name=QS81TWS LGWR valid_for=(all_logfiles, primary_role)

select archiver,process,status,target,register,transmit_mode,affirm,type from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';

ARCHIVER   PROCESS    STATUS    TARGET  REG TRANSMIT_MOD AFF TYPE
---------- ---------- --------- ------- --- ------------ --- -------
LGWR       LGWR       VALID     STANDBY YES PARALLELSYNC YES PUBLIC

In the same table i inserted another row and commited , again commit didnt take any time . I am expecting bit delay to commit as the transmit_mode=sync and affirm=YES are set and LGWR on primary waits for acknowledgement from RFS on standby  .

why am i not able to see  any  delay on commit ?

Standby and primary db are not in same system and standby db is not using standby redologs and is in Max Performance mode

Anyone , Please clarify my doubt

Thanks
0
ajaybelde
Asked:
ajaybelde
  • 3
  • 2
1 Solution
 
schwertnerCommented:
I guess you are speaking about Physical Data Guard.

To transfer data from the Primary to Data Guard you have to switch the online redo log.

Commit is not enough to initiate the transfer of the log file.

So use:

COMMIT;

alter system switch logfile;
0
 
ajaybeldeAuthor Commented:
Thanks for the reply .

Yes , i am using physical dataguard and i am following  the attached oracle document which says when we use LGWR to transport redo data to standby , it sends redo data to standby while writing to online redo log ( Before the online log switched ) . As we have used SYNC transport mode and AFFIRM =YES, LGWR process waits for acknowledgement from standby that it has written redo data and that data is archived before it commits any transaction . So  after inserting a row ,  i am expecting a delay to commit  when we do 'COMMIT'  .

 Please check the attached file  and correct me if i am wrong in my understanding  .

Thanks
DG-exhange.txt
0
 
schwertnerCommented:
In this case you have to measure the speed of the network transmission between the primary and the standby. Because the volume of the transfered data is not high and is done synchonously by a fast connection you should experience no delay.

Use tnsping to observe "naturally, with your eys" the connection speed.
The network engineers can provide you more sophisticated tools to measure the speed and even with sniffers to read the messages. But this is big time investment
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
ajaybeldeAuthor Commented:
Thanks  for the reply

 I stopped the listener on standby db side and log_archive_dest_2 on primary is same as before (i.e  service = standbydb db_unique_name=standby unique name LGWR sync AFFIRM ) , even then i can see the transactions are commited on primary , which should not as log transport to standby  cant take place as listener is down on standby and so commit should not occur right ( as SYNC and Affirm attributes are mentioned in log_archive_dest_2 parameter) .
Please answer

Thanks ,
Sandeep
0
 
schwertnerCommented:
The stopping of the listener doesn't quit the connection between the two databases.
The Listener of the Data Guard only initiates the FIRST connection.
After that it establishesh socket connection between the processes on Primary and Standby.
To quit the connection you should find which is the server process on the Data Guard which works with the Primary and kill it.
I do not know how to do this, but this should be mentioned in the docs and in Google.
But be very careful - you can colapse the data Guard if you miss the processes.
0
 
FaherCommented:
there isnt any dely because your standby database mode is maximum performance, if your database is in maximum availability or maximum protection then you'll face some dely but its minor. Regarding the stoping listener you are not facing any issue while stoping listener because again your database mode is maximum performance mean it dose not care if it has standby database sync or not it will response all users without effecting performance
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now