Tuesday, July 18, 2006

OraFAQ Forum Reading Notes - Data Guard

OraFAQ Forum Reading Notes - Data Guard
( http://www.orafaq.com/forum/f/12/0/ )

Re: How to disable transfers of LOGS to standby database

Try:
SQL> ALTER SYSTEM SET log_archive_dest_state_2 = DEFER;
System altered.
DEFER = Specifies that valid destination information and attributes are preserved, but the destination is excluded from archiving operations until re-enabled

SUBJECT: log_sequence column in v$archive_dest

In my dataguard setup, in which 2 primary RAC instances are sending archive logs to a Physical standby database, the log_Sequence value in V$ARCHIVE_DEST view is not getting updated.

1 select ads.dest_id,max(sequence#) "Current Sequence",
2 max(log_sequence) "Last Archived"
3 from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads
4 where ad.dest_id=al.dest_id
5 and al.dest_id=ads.dest_id
6* group by ads.dest_id

DEST_ID Current Sequence Last Archived
---------- ---------------- -------------
1 38743 38743 2 38743 0 3 38726 0
There is no problem with the dataguard, and it applies the archive logs correctly and is in sync with PROD.
Our dataguard monitoring script checks the archive log gap based on the values in this column,and METALINK also says the same thing.
From Metalink: (Note: 241374.1)Quote:
-- The following query will determine the current sequence number
-- and the last sequence archived.

If remotely archiving using the-- ARCH process then the archived sequence should be equal to the-- current sequence. The applied sequence information is updated at-- log switch time.

select ads.dest_id,max(sequence#) "Current Sequence",max(log_sequence) "Last Archived"from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads where ad.dest_id=al.dest_id and al.dest_id=ads.dest_id group by ads.dest_id;
Would you please throw some light, as why this is not getting updated.

// -----------------------------------------------------------------------
Q: we are planning a switchover making the standby as the primary.We will not be making the old primary db to standby. Application testers want old primary back as primary and test the application with both old primary and new primary.In this case, How can I make the old primary back to a normal PROD database?? After running, "Alter database commit to switchover to physical standby", it will create the standby control file, right?? So is the control file overwritten in this case?? Do I need to recreate the control file ??

A:Try to do a fail-over instead of a switch-over. After a fail-over you should have 2 primary databases with no data guard.
( messeage read till 16-Jun-06)

--------------------------
SUBJECT: 2x standby db - 1 using redo apply & 1 using sql apply

HiWe are wanting to setup 2 standby databases - 1 for DR/failover using redo apply and 1 for reporting using SQL apply.The first standby db is strictly for failover as the Primary db is a 24x7 system. The second db will allow us to add Summary tables & Materialized views around the core data and remove all reports processing away from the Primary.Oracle documentation says you can have up to 9 standby databases, but doesn't say they can be populated by the 2 different methods (redo & SQL apply). I'm assuming we can, but would like to know if anyone else has successfully setup a similar scenario before investing large amounts of time.thanks

Ans:
As a short answer - yes.We have successfully tested setting up both a physical and logical standby on the same primary - basically for the same reasons you are looking to do it. We are working toward rolling this out in our production environment soon.Our environment is a 10GR1 4 node RAC cluster with physical replication to a single node DR site and soon logical standby to a single node reporting environment.

No comments: