Solved

Oracle 11g Dataguard LGWR issue

Posted on 2012-03-29
7
859 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Read about achieving the basic levels of HRIS security in the workplace.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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…

705 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

12 Experts available now in Live!

Get 1:1 Help Now