Data Guard is Oracle’s brand name for their technology involving standby databases. Oracle is a US-based database vendor.
This page is a collection of my Data Guard related postings:
Use RMAN to create your standby database without a previous backup
Starting the Observer in the background
Resizing datafiles with Data Guard in place
Adding Tablespaces/Datafiles with Logical Standby DB present
Logical Standby in NOARCHIVELOG mode
Record DDL Statements in DBA_LOGSTDBY_EVENTS & alert.log
Connect Time Failover & Transparent Application Failover for Data Guard
Performance Impact of Logical Standby on the Primary
Using Flashback in a Data Guard Environment
Data Guard & Oracle Restart in 11gR2
Snapshot Standby Database in Action
Real-Time Query for Read-Mostly Applications
Real-Time Query and Automatic Block Media Recovery in 11gR2
Clone Database from Snapshot Standby Database
Flashback Database puts Physical Standby into MOUNT from READ-ONLY OPEN
Rolling Upgrade with Transient Logical Standby
After Test-Failover, make NEW Primary Standby again
Auditing for Physical Standby Databases with Real-Time Query
Modify segment attributes on Logical Standby during Migration
The Data Guard Broker: Why it is recommended
How to add a Logical Standby to an existing #Oracle Data Guard Configuration
Parameter to prevent license violation with Active Data Guard
Let the Data Guard Broker control LOG_ARCHIVE_* parameters!
Foreign Archived Log in #Oracle – what does it mean?
Data Guard Logical Standby – what does it mean?
FASTSYNC Redo Transport for Data Guard in #Oracle 12c
Another reason why you should use the Data Guard Broker for your #Oracle Standby
How to reinstate the old Primary as a Standby after Failover in #Oracle
#1 von candba am Juni 17, 2011 - 21:13
This is my first time on your blog and looks like I will be visiting it more often now:)
I have got a question regarding dataguard(I have posted it at OTN as well but why not ask the expert as well:) :
We have a 10gr2 database(P1) on solaris 8. We want to move to a new server running on solaris 10.
This is what we are planning to do:
-Setup a standby(S1) on new solaris 10 server in advance
-Let the logs move/apply on S1 for a few days
-Do a manual switchover on the move day so S1 is the new Production(P2) now
-Keep the old primary(P1) as new standby(S2)
-If any major issues after switchover then revert back to old Primary using another switchover from P2 to S2
I tries looking around everywhere but did not find anything which could provide me steps for last 2 action items. We can use rolling upgrade methodology but for that we have to use a logicla standby database(thereby limiting some data types).
Is their a way to use physical standby configuration to do a switchover and then(if needed) another swichover back to the original primary.
We are not using flashback/DG broker.
#2 von Uwe Hesse am Juni 24, 2011 - 09:00
Your question translates to me as: „Can we have a Primary on Solaris 8 and a Standby on Solaris 10?“
My guess is that this should be possible, but I have not done or seen that personally yet.
If yes, then you may of course switchover to the Standby for a while and switchover back again if you like; that is standard functionality.
The rolling upgrade that you mention is not for OS upgrade, but for Database upgrade.
#3 von azardba am September 15, 2011 - 14:37
Hello Mr.Uwe , I hope you’re fine. I have a question about dataguard standby database. My question is below,
One of my colleague (He is Sr Network Consultant) asked me, Can we able to make standby database from standby database not from primary database like below procedure….
Our production db in saudi arabia, I want to make standby db in dubai. , here First I want to sync data in dubai standby db and then I want to make another one standby database in Uk which is sync data from dubai standby db…so the uk standby database should be depends on dubai standby database…So Can We make standby from standby database?
#4 von Uwe Hesse am September 15, 2011 - 20:49
yes, that is a technique called Cascaded Standby Databases:
#5 von azardba am September 15, 2011 - 21:26
Thank you for your response. Yes I got a idea now…:-)
#6 von Uwe Hesse am September 16, 2011 - 14:37
You’re welcome 🙂
#7 von JC Dauchy am November 2, 2011 - 16:17
Hello Mr. Uwe,
After reading the documentation and several blogs entries concerning cascading Standby, i still have 2 questions concerning Failover scenarii with Logical Standby for the 2 following cases.
Let’s say i have :
case 1 : Primary sending redo to Standby and Logical Standby
Logical Standby Physical Standby
When I failover to Physical standby, do i have to recreate the logical standby since the Logical might have processed too much SQL apply ?
case 2 : Primary sending redo to Standby then cascading to Logical Standby
Primary => Physical Standby => Logical Standby
After the failover to Physical standby, when I want to switchover back to the initial configuration, will the initial Primary resynch automatically with the Logical Standby ?
Thanks in advance for any clarifying answer.
#8 von Uwe Hesse am November 3, 2011 - 10:25
I cannot say much about cascaded Standby, because I have never actually used them, and they are only very little documented.
My guesses would be: case 1 probably needs a new creation (or flashback to a time before the switchover) of the Logical Standby, while case 2 should work without further intervention.
Also, I consider cascaded Standby as obsolete after the introduction of 31 archive destinations that now enable customers to overcome the old restriction to „only“ up to 9 Standby without cascading.
#9 von JC Dauchy am November 3, 2011 - 10:51
Thanks for the reply, by looking again closer, i found a document which explains each case :
Klicke, um auf maa10gr2multiplestandbybp-1-131937.pdf zuzugreifen
So you are right about both case, so what i can conclude is that it is better to have a delay to the apply on the „non Failover“ destination so that in case of failingover, the remaining standby is late in applying the redo => no recreation.
Have you used flashback in production ? I never used it except for testing purpose, i would be afraid of the overhead it can induce !
#10 von Uwe Hesse am November 3, 2011 - 11:35
You should not be afraid of putting your production databases into flashback mode. It is recommended at least for the databases that are part of a Data Guard configuration. See this post for further explanation why it is better than delay: https://uhesse.wordpress.com/2010/08/06/using-flashback-in-a-data-guard-environment/
The performance overhead of writing flashback logs is neglectable; the only concern is the space they need inside of the Recovery Area.
Since 11g, you can even turn it on/off without going into MOUNT state (have posted about that also: https://uhesse.wordpress.com/2010/06/25/turning-flashback-database-on-off-with-instance-in-status-open/) – so you can very easy test it (with a low DB_FLASHBACK_RETENTION_TARGET at first, maybe) to find out the space requirements for your case.
#11 von JC Dauchy am November 3, 2011 - 13:09
Thanks Uwe, i will try it out on my bench RAC platform.
The same question concerning the broker, have you used it in production ?
I always prefer to make my own failover-switchover script so I can check all the information necessary before launching the final command (especially in 10.2 RAC).
Julian Dyke seems to agree with this approach :
#12 von Uwe Hesse am November 3, 2011 - 17:46
As an Instructor, I don’t work with production databases, although I encounter many people who do so. You will always find people who refuse to do things with (relatively) new tools because they don’t trust them resp. have crafted their own methods before the tools were usable.
All I can say is: It is officially recommended to use the Data Guard Broker. Some features even require it. Many customers (the majority meanwhile as I suppose) use it to their satisfaction.
Besides: The people who refuse to use Data Guard Broker will probably not talk much about the mistakes they made that would have been avoided by the Data Guard Broker. Instead, they focus on some old bugs of the 9i version. My advice to them: Join the 21st century 🙂
#13 von JC Dauchy am November 22, 2011 - 23:43
I completely agree with the fact that new features should be used, my main concern is usually to be able to fully understand them (pros and cons and catches) before implementing them.
I setup at home a cascaded architecture with my primary sending to physical standby, then sending redo to a logical standby.
Here is what I have noticed for the moment :
– when the Standby is cascaded to a logical, the Mining seems to be applied only at a log switch, can’t figure why but this is the point where I arrived..
– but when the Logical received redo directly from the primary (no cascade), the Mining happens in „Real Time“.
May be it is the expected behaviour, or maybe I missed something in the configuration..One thing is sure, you were completely right when you said there almost no documentation on cascaded standby.
#14 von JC Dauchy am November 22, 2011 - 23:55
I forgot to mentionned I am on Solaris with RDBMS in 22.214.171.124.0.
#15 von Uwe Hesse am November 23, 2011 - 00:03
That is indeed normal with the cascaded Standby. It must be so, if you think about it, because there is no LGWR writing Redo Protocol on the Physical Standby that can be transmitted to the Logical Standby. Instead, it must send Archivelogs to the Logical Standby.
#16 von JC Dauchy am November 23, 2011 - 00:47
Thanks for this quick answer, so I understand correctly, on the standby, since there is no LGWR, the redo buffer is not populated either, so the LNS process can only get the full redolog file while the redolog file is archived. This clarify a lot waht i noticed.
Thanks a lot.
Next tests will be only Role transition behaviour between the different architecture.
#17 von Uwe Hesse am November 23, 2011 - 18:27
You’re welcome – and thank you for sharing your findings about cascaded Standby Databases here! Just to clarify: There is a LGWR present on the Physical Standby, but it is not writing Redo Protocol. The LNS process will not ship Archivelogs to the cascaded Standby but ARCH processes on the Physical Standby will do that.
#18 von Ahmed am Dezember 14, 2011 - 12:55
I read a couple of your articls, and sure got addicted to them. so while reading I came up with a decision of creating a logical standby database. would you be kind enough to recomend a document that helps to do that. my primary database is 10.2.0.3 runing in Solaris 64.
thanks and great job.
#19 von Uwe Hesse am Dezember 14, 2011 - 14:38
a brief description is here:
Klicke, um auf dgcli.pdf zuzugreifen
a more detailed description is here:
#20 von amak am Juni 29, 2012 - 11:57
Enjoy very much on your blog.
Suppose I have
P1 – primary
S1 – Physical standby
B1 – level 0 backup of the db from S1
P2 – New primary
S2 – New physical standby
Say I have a media failure on P1 and failover to S1
Now S1 become P2 (new primary)
Restore B1 to P1
Now how do I convert the newly restored P1 using B1 to become S2 (new standby) ?
Can I change the db name and just follow the create physical standby db procedure to make it as the S2?
After converting P1 to S2 and let the S2 catchup with P2, Can I use DGMGRL switchover to swap back P2 and S2 ?
#21 von Patrick am September 21, 2012 - 20:59
I just discovered your articles, what a great resource. I tried to create a standby with your method (without backup) and got a problem when tried to connect to auxiliary from RMAN. I created the init file and password file like mentioned in the document but when I tried to connect to the instance it’s like the password is not good. I must have missed something there.
Thank you very much
#22 von Uwe Hesse am September 25, 2012 - 18:02
Pat, you’re welcome 🙂 Regarding your problem, I suggest that you post a question in the OTN Data Guard forum http://forums.oracle.com/forums/forum.jspa?forumID=849&start=0 where you should include as many details as possible: Especially version, command you did run and error message.
#23 von Ed am Juli 22, 2013 - 13:01
I am a big fan of your blog and I must say they are a lot better than Oracle Support !!!
I have a question about Dataguard+Flashback+Oracle Managed Files that I hope you can shed some light on. We are on Oracle 10g/11g, once in a while we do SWITCHOVER and whenever that happens, RMAN does not recognize archivelogs and imagecopy that each database created when they were on their respective roles, i.e. for example when the Physical Standby is switched to become a Primary, it does not recognize having the image backup copies that has been done on the Primary and when the original Primary becomes the Primary again, it does not recognize the image copies created for when it was a Physical Standby?
After the switchover, the incremental backups on the Primary will get RMAN-03002: failure of recover command and ORA-19870: error reading backup piece which is expected I believe because obviously it does have the copy of the datafile from when the roles are reversed?
In most cases, I have to do a level 0 backup again. Is there any way around this so that this problem never happens or I am doing what really has to be done after the SWITCHOVER, do a level 0 backup?
Any advise much appreciated. Thanks in advance.
#24 von Uwe Hesse am Juli 24, 2013 - 08:30
Hi Ed, thanks for the nice words 🙂 I suppose you don’t use a recovery catalog DB. Problem is, that RMAN info is stored in the controlfile in that case, which does not get replicated to the Standby. Workaround would be to create a new Standby controlfile (containing that info) before the switchover.
#25 von SB am Oktober 12, 2016 - 14:57
Hi Uwe Hesse,
I am getting confused when we upgrade database from 126.96.36.199 to 188.8.131.52 in Data Guard environment.
Please clarify it. Database has been upgraded to 184.108.40.206
We have managed Primary database though Enterprise manager (Database control)
If i wanna upgrade EM (Database control) , in which path i need to mention while upgrade ?
220.127.116.11 => /u01/app/…/dbhome_1
18.104.22.168 => /u01/app/…/dbhome_2
$ echo $ORACLE_HOME
$ emca -upgrade db
STARTED EMCA at Oct 12, 2016 3:50:19 PM
EM Configuration Assistant, Version 22.214.171.124.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.
Enter the following information:
ORACLE_HOME for the database to be upgraded:
#26 von Bernd Vierschilling am März 15, 2017 - 10:06
for me the one fo the most important new feature in 12.2 is the possibility to build a physical standby on a different endian format.
That could be a great help to minimal downtime for cross platform migrations.
Did you already test this new feature?
#27 von Uwe Hesse am März 21, 2017 - 09:54
Bernd, yes, sounds like a great feature! I haven’t tested it yet, though.
#28 von Sarfaraz Alam am Mai 23, 2017 - 18:44
Hi UWE, I am planing to convert 10g Logical standby to physical standby, will you suggest any steps or Doc Id Please. ?
#29 von Remya am Mai 5, 2018 - 12:59