?
Solved

Oracle 11g Dataguard LGWR issue

Posted on 2012-03-29
7
Medium Priority
?
897 Views
Last Modified: 2012-08-14
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
Comment
Question by:ajaybelde
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 37783337
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
 

Author Comment

by:ajaybelde
ID: 37783554
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
 
LVL 48

Expert Comment

by:schwertner
ID: 37797560
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:ajaybelde
ID: 37799390
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
 
LVL 48

Expert Comment

by:schwertner
ID: 37800134
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
 
LVL 1

Accepted Solution

by:
Faher earned 2000 total points
ID: 37800503
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this article, we’ll look at how to deploy ProxySQL.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question