How to perform step by step manual failover | Oracle Data Guard

okay so what does the failover failover is when your primary database is completely lost when there is a failover standby is converted into primary but primary is not converted into standby as it is already lost when your primary server is crashed how would you convert it into standby and you just cannot connect to the server itself if you do not have flashback enabled on primary you must recreate primary from scratch using armed and duplicate method now this is the very important point which I was telling you guys earlier that I will discuss about the importance of flashback in failover let's take you dinner and you do not enable the flashback right so what happens is when you rebuild the primary because you lost the primary transactions will continue on standby the new transactions which are happening on the standby you need to get all those transactions onto the primary right so if flashback is enabled you can flashback your primary database back to the crash time and then start applying the logs from the new primary so that's why flashback is very important if you do not enable slash back then you will have to build the primary from scratch now the next lesson that we have is rebuilding primary after failover so in our case we have enabled the flashback and we will be looking at how to rebuild the primary database after failover has happened right so we'll be using the same technique will use the flashback database to rebuild the primary if flashback is not enabled then you need to follow the entire documents again as to how we create the physical standby like delete all the control file data files from the original primary clone the and I mean your activated primary that is standby then you will have to clone from that server the new primary build the primary server and then performance switch over pretty confusing but this is how it works in our case we have already enabled flashback own on both primary and standby as I already told you flashback needs to be enabled on both but the only problem in real time is you will have flashback logs generated when the flashback logs are generated you need to have another management area or another area like archive log destination you will have to manage the logs and the space that is one little overhead over there but that's okay and to be very frank no production environment will have flashback enabled because you do not see failover so so frequently like I myself have never seen a failover you don't see servers crashing just like in normal situations so your servers definitely continue normally but it does not mean that you are not prepared for the worst case it's your job to be prepared for crashes note in our earlier activity we had performed switch over current state of your service should be in in this switch over earlier we activated the proudly be under square ste as primary and prod DB was our physical standby but later on we have reverted back the roles so let us connect over server and check the database rules so I am connected to the primary okay prod DB is up and running we'll take another session SSH - ex root at the rate 192 168 0 or triple - perfect this is the dr server so on dr will check the database instance that is running we have brought DB undress Westie already running over there great so we'll switch to oracle user sqlplus / else's DBA and on the primary switch user - Oracle clear scale + / Elsa stevia select name comma open underscore mode comma theta based on the school from B dollar database this is the primary database and this should be the physical standby now by looking at this one we cannot guarantee whether the MRP is running or not so let us check whether MRP is already up and running or not so select process status sequence number from V dollar managed standby great MRP is up and running it is applying log number 146 archive log list and the current log sequence number is 146 perfect our data guard is in sync and this is the current status of our servers now our goal is to crash the primary prodding me in order to simulate failure vini we will shut down the primary server fraud DB as root user shut down the server without shutting down the DB so what we are doing is we are crashing the primary database so how are we crashing with root user we'll just shut down the server directly ok and after shutting down we'll execute the query on the client so that we will see from where we are getting the output right so let me open a third window which will act as our client so this green text is our client okay so so choose your Oracle scale plus says slash this at the rate prod DB else's DBA now on the client will execute the same query and we'll see where exactly we are connected to so we need to have the DB unique name as well DB unique name from big dollar database right okay so we have both client and our data got set up over there right now or the client we are getting the output from original primary that is prod DB perfect so let us crash the primary that is broad DB so with I mean with root user I will directly shut down the server our primary is already up and running and I am just shutting down this over because we cannot simulate a crash or something so let us bluntly shut down the server so this server is down and on primary I mean on the client I will run the query so it is hanging now now to minimize the data loss if you can mount the primary database then flush the logs to standby but in our case our primary is not at all accessible so we can't even flush the redo entries to the standby sometimes what happens is let's take somebody shut down the server and then what you can do is you can bring up the server mount the database and then flush the logs but generally you know crash is a crash you cannot mount it but still I have included at this point in our case we are going to ignore this one because we cannot mount the database our primary is completely down so next if you are not able to mount the database then check if primary server is up again primary is not at all upright in that case manually copy archive logs from primary to standby and register those locks on standby database so this is an exceptional case if you can really mount the primary server or if you cannot mount you can only open the primary server then copy the logs manually next check for redo gaps we have bluntly already shut down the primary so we'll okay now this will have a problem because we connect it to this one using that put accession so what I'll do is I mean if you remember the dr we first connected to primary then we did ssh to the dr server so what we will do is we'll close this one take another pretty session i'll rather take a completely new one our main catalog this should be up okay and then dr server okay this is not working no problem we'll take this one so with the client I am doing an SSH to the dr server so don't get confused SSH if an ex root tag the rate of 192 168 0.1 triple to route at the rate one two three so we are onto the DEA's over PA - EF creppy Mon this is Rodney be under scarcity so what we need to do next is we need to check for gaps so we'll switch to Oracle user skill plus slash assist EBA so it says there are no gaps and definitely our data guard was in sync perfect start the failover we need to activate standby so that client can continue to access even after the failover so on the standby we need to execute this command alter database recover manage standby database cancel first we are cancelling the MRB process because our MRP is running in the background right so we have to cancel the MRP process then alter database recover manage standby database finish we are saying that okay boss you have done lot of log apply so now finish it just complete so execute this one and next select switchover status from we dollar database we can check these switchover status and you must see that it is to primary now you must see two primary or sessions active switch standby to primary this command alter database commit to switch over to primary with session shutdown will switch your standby to primary role database is altered and then we have to openly database so our original primary is crashed it is shutdown on the standby we have activated it to primary and now we are opening the database is open now we'll move on to the client okay already a timeout is done I'll reconnect panel we execute the same command select name open more database or DB unique name so from where you should get the output on this standby we can see that DB unique name is prod DB underscore st that means we have successfully activated this stand by when our primary got crashed this is how we activate the standby but now the problem is let's take the client continues to execute some transactions okay so create table test sno number comma so then name where care 210 I am just creating a dummy table okay so insert into test values 1 comma then cash and I mean reinserting the same records and I am committing the transaction at this stage we are in a separate situation now what is this situation let me just talk about it primary is completely gone primary scratched we have activated the standby so the earliest standby is now primary correct the client is right now connected to the new primary that is also fine so let us assume the archive log number that was running before the crash happened was 15 and on the standby we had activated the standby when the log sequence number was 50 also we have executed some transactions so definitely multiple locks we would have been generated right something like that so if I power on this server in our case we only did a shutdown okay let us assume we even try to power on then how can I apply these logs over here manually you can't do it we can't do it manually like you cannot perform a normal recovery those all things will take lot of time I mean you will have to register log so you will have to apply them using arm and it will take lot of time so what is the best method see the simplest method logically if you think the current database is in primary right this is the primary so if I start this database I can make it standby correct so what I'll do is when I start this database I will flash back the database till the point when it was crashed so the crash point I'll slash back then I'll change the database role to standby and enable MRP when I do so what happens is the logs will flow in the reverse direction and automatically the log which is required like 51 52 53 they will be applied reversely back to the original primary which is currently the standby so that is the logic we are using rather than manually copying the logs and doing all those things not needed we are automating the process so in a data guard environment we can have only one primary so when you activate the original standby is primary the crashed primary can be opened as a standby so once you open it as a standby enable the MRP MRP will automatically take care of all the logs it will fetch the logs from the new primary and apply all those logs 51 52 and automatically your new standby will become in sync with the new primary so once the sync is done the I mean the server's will look like this this will be your standby and this will be your primary right what we can do is once both the servers are in sync we can perform a switchover so standby becomes primary and primary becomes standby that way we'll get back the original configuration and logs will flow from left to right so this is fraud dB and this is fraud DB under square steam so this is how we are going to recover the original crashed primary okay so we have ran some transactions and these transactions are recorded on to the current primary which was earlier our standby okay so next is rebuilding primary after failover we have seen a failover so how do we rebuild the primary after that after the failover now there are two methods method one is rebuild from scratch we need to use the same method like arm and duplicate method how we have used initially to create the physical standby right then method two is flashback the database only flashback was enabled if you did not enable the flashback you cannot use this method and this is the reason I was stressing earlier like it is important for you guys to enable flashback note in our earlier activity we have performed failover current state of your servers should be prod DBS crashed we shut down the server and prod DB undersquare ste is right now current primary this is our setup right next what we need to do is we need to get the Sen at which standby became primary now your base tables like inside your database we have a column called as standby became primary Sen under we dollar database we need to get this Sen number because we will flash back the database till this Sen right when the standby Hank was activated as primary till that Sen will flash back the primary database so what I'll do is when I say primary it is the crashed primary ok so this is the crashed primary where we shut it down let me bring up the server first it is showing that server is still up and running okay so we'll have to shut down there is no proper shutdown yet so now I'll start it up okay so system is up our crash primary server has been started now we'll connect to the server and we'll switch to Oracle user first what we need to do is we need to get the SN number till where we need to flash back the primary so you need to run this query on to the current activated primary so this is the database which is right now open and this is the Sen number when this standby was converted to primary so we need to make a note of this sen number right now flashback crashed primary that is prod dB start the party B server we have already done that mount the database and flashback prod db2 Sen from the last step so let us check the databases and repeat mon-sol we'll wait for ASM to come up it will take some time for ASM to come up so save me some time if you see that ASM is not up after like 10 15 minutes then you will have to investigate why it did not come up so right now we have the ASM up and running perfect so sqlplus / assist the be a startup mount ok the Oracle restart utility is trying to start the database as I said earlier when you are using the grid infrastructure like ASM the ASA more SRV CTL utility will automatically start your database so what you need to do is in this case shut down the database because it makes no sense if this database is up and running already your current primary is ahead of this database yeah tell me both oh ok sorry have you little bit faster today like than other time where you find the middle I cannot feel that way but it is possible bit of my brain slow today I don't know ok no harm at the same pace yeah so there seems to be an issue with the database ok a skill plus slashes DBA startup Mountain this is one small issue with the Oracle I'm in the grid infrastructure a service ideal utility you know it conflicts with you trying to start and stop the database but any which was once you have mounted the primary database say you need to shut down the database startup mount and then flash back the database to SC n number what is the asean number we have already copied this is the SN number we have to flash back the database flash back completed now convert the crash primary to physical standby nothing but the prod the best way is to convert it to a physical standby start the MRP automatically it will get the logs from the new primary and apply it over here so alter database convert to physical standby this is the command so these all commands we are running on the crashed primary the original primary okay prod dB now we are shutting down again we'll start in the mount state and enable the MRP so meanwhile what I'll do is I'll perform some lock switches on to the current primary alter system switch log file perfect so start up mount and then alter database Nick or manage standby database disconnect this will ma enable the MRP process so once MRP is enabled it will automatically fetch all the locks from the new primary select process status sequence number from V dollar managed standby okay now we can see that RFS process is not coming up over here which means we need to see the log archive destination - on the standby so show parameter test to show parameter state underscore - ok state - is enabled so which means we need to wait for RFS to start yes now RFS is started and you can see the log number it is applying is seven so can anyone tell me how come the log number is seven I mean it is lower than our earlier log numbers which were 141 142 146 log numbers why are we seeing log numbers like 710 anyone any idea this is very important interview question when you perform a failover your database goes into a new incarnation this is very very very important interview question whenever there is a failover database gets into a new incarnation log sequences we set so that's why the lock sequence again started with 1 and the current lock sequence is going on is 7 so how can we see it how exit Harmon target slash show list incarnation we can see the current incarnation and the date and time is current date and time as per my system so just now we performed the failover right so whenever you perform the failover your database gets into a new incarnation I mean whenever you try to recover the database out of the failover it gets into a new incarnation lock sequence is reset and it is always mandatory that you take a backup whenever there is a new incarnation so the current state of your databases should be the Prada be is converted to physical standby and prod DB under scarcity continues to be primary now what we can do is revert to the original configuration at this stage if you would like to revert the current state of databases to original you can perform a manual switch over like our earlier activity that we have seen so what I'll do is I'll go ahead and perform a manual switch over so that will we'll get back the original state of our databases so where is the switchover document this is the switchover so I'll try to perform it pretty quickly connect to prod neaby database ok I'm not connecting to client I'm just performing this switch over so I'll even leave the gaps and all I'll just convert the primary to standby this is the our second is that could you can we get the documents yeah yeah after the session I'll send it yeah I have I have written it yeah thanks for reminding okay yeah no problem yeah so on the current primary sessions active we can still go ahead alter database commit to switchover physical standby converting the current primary to standby and then mount the database now we'll convert the standby to primary so these things will confuse you a little bit but once you practice them it will be easy so the original primary is ready to be converted I am converting it into the primary database and I'll just open the database and on the original standby I will activate the MRP process okay so this is the current status what I have done I have reverted back i have executed all these steps on the correct primary and standby so database is open on the client what I'll do is I'll check where are we connected right now see we are connected to the Prada be if you look at this session right now we have performed failover and switch over both in one activity still this session is active earlier we got the output from prod DB under scarcity right now we got the output from prod DB so this is how your clients will still continue to you know be connected to the data guard environment so guys if you have any doubts let me know we have just now seen the failover process and also we have seen this switch over once again after failover okay so if nothing will proceed further the next topic that we have is Arman in data guard configuration how should you configure your Arman when you are working with data guard environment so it's very simple now when I have few queries and few suggestions over here so first we will look at these suggestions and later on I have an assignment for you guys and this assignment needs to be executed by you on your setup the data guard setup so I'll talk about this assignment later first let us understand about the Arman configuration Arman in data guard configuration works very normal like single standalone database but there are few important things you should know in order to define your backup and recovery strategy when using data guard first of all you need not register the standby with the catalog Armin will identify it as standby we will only register the primary database with the catalog automatically standby will also be get registered again this is an interview question where interviewer can ask you like how do you register standby database to catalogue you will have to say that we do not register the standby database to catalogue when we register the primary Armen automatically picks up the standby database next catalog is a must we already know why catalog is very important so I won't talk about it backup control file on primary you must still backup the control file on the primary let us assume you can you know in a data card environment you can offload the backups on to the standby you can do that because your databases are block by block same in a physical standby configuration but even though you offload the backups on to the standby still you must take the backup of control file on the primary because control file is very important and as I mentioned earlier in a data guard environment your primary and standby control files are different so you need to have that primary control file backup on a standby system you cannot backup the primary control file right so it is always good to still backup control file on primary if you plan to offload backups on to the standby on standby the control file is a standby control file in case of primary crash you cannot use standby control file so at least the backup control file and SP file on the primary so what we'll do is we'll configure the control file auto backup on basically you do it on both the servers not really on primary or standby so our main target / configure control file auto backup on and the same thing I will execute on to the standby our main target / perfect next set proper retention policy this will not delete backups for the specified recovery window depending on your environment and I mean you'll have to configure the retention policy so in this case we are going with seven days you might have or differ retention balls he based on your okay we cannot execute this command in the standby server because this is a standby control file and the standby control file will not take this parameter so just leave it you need to execute only on to the primary next you can backup archive locks on both primary and standby this will provide you archive locks redundancy but not recommended now what you can do is you can have archive block backups on primary also you can have archived locks backups on standby also but you know you don't need so many copies of arcade locks so better you try to backup archive locks only on the primary don't put the overhead of backing up archives again on standby so this is good but not recommended now how do you register a primary database with catalog this is very important step over here you will learn some new things so let us register primary database with the catalog now what I need to do is I need to bring up the our cat database let me start the arkad database I have a central catalog server I'll just enable it okay Harmon catalog let me start the server okay so the sick half catalogue is up and running okay it's up so let me just get the catalog details because we need to configure the DNS entries for the catalogs all right so if config - a the IP address is 200 catalog details and let me switch to Oracle user env grab para scale plus slash assist ABA let us start up the arc and database don't worry I'm just starting up the recovery catalog and then we will register our data guard environment into this recovery catalog perfect so recovery catalog is up and running let me do TNS ping our cat teen in spring our cat okay so I'll copy directly the DNS entry network admin DNS names Dora this is the our cat DNS entry which we can use directly so we'll have to place this entry on both the server's not only on the primary place it on both the primary and standby so go to the Oracle underscore home slash network admin open the TNS name's Dora go to the end of the file copy the our cat TNS entry and put it over there the same I'll do on the standby legs it C D dollar Oracle and Rosco home slash network admin Tina's name Dora and let us place the Arquette okay now DNS ping our cat it's working fine okay no listener we did not start the listener a lesson our CTL start listener perfect okay I'll get the NS ping is working DNS ping our cat it's working fine now once you have the recovery catalog on primary we will register the database with the catalog so we can use this I mean the Armand connection string that will connect to my our cat database in your case you will have to check the user ID password for the arc and database you can see it is connected to prod DB and it is also connected to the catalog recovery catalog database now let me issue this command register database one minute guys let me run this command first okay I will tell you why I run this command because the same s IDs were earlier registered with the Harmon catalog so that's why what we need to do is we need to delete these entries and register of fresh for deviant products questi so let us remove the remove database from recovery catalog okay now we can do this one delete backup now under USERRA catalog only yeah this is important in DBQ from this one let us connect to catalogue or man who are see our man on Reservoir C we need to connect with the recovery catalog owner user and then I'll just have to remove this one will basically have to unregister this one to give the DB key we'll have to give this number undone so this is something which I ran on to the catalog so we'll check again now there are no DB unique names in the catalog so nothing is registered on the catalog so we'll

Channel: DBA Genesis

Views: 8,153

Keywords: oracle, oracle data guard, activate standby database, database failover, Oracle 12c Data Guard, Failover, Switchover, data guard failover, dg broker, data guard switchover, What is a Failover, failover cluster manager

Id: G-kdhjnpEXU

Length: 42min 8sec (2528 seconds)

Published: Sat Jul 28 2018