Solved

Oracle 11g Dataguard LGWR issue

Posted on 2012-03-29
7
870 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 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
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 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 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

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

Suggested Solutions

Title # Comments Views Activity
pl/sql - query very slow 26 74
ER Diagram 3 21
SQL Workhours Count beetween Workhours 3 26
Oracle 12c Default Isolation Level 17 42
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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…
Via a live example, show how to take different types of Oracle backups using RMAN.

821 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