Solved

Oracle 11g Dataguard LGWR issue

Posted on 2012-03-29
7
865 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
  • 3
  • 2
7 Comments
 
LVL 47

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 47

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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 47

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 500 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now