Oracle Data Guard - Recover Standby Database Using Incremental Backup - Resolve Log Gap

hello viewers welcome to the series you the Oracle expert and today we are going to talk about how to resolve log Gap in a standby database using an incremental backup from primary when there is no option left to recover any missing archive [Music] logs so viewers in this series we cover different Oracle database related topics and today we are going to talk about how to recover from a situation when the archived logs are deleted before they were applied on the standby and there is no backup of the archived logs available this situation will develop a log Gap in the standby causing it to be out of sync with the primary I will demonstrate using an incremental backup from the primary how we can resolve the log Gap and bring the standby database back into sync with the primary I assume you are already an oracle DBA or familiar with at least the basics of Oracle database architecture I will keep everything simple so there won't be any difficulty in understanding the concepts even if you are a beginner please note that whatever commands I am using in these tutorials they are all available in a shared document in my Google Drive you can access that using a link provided in the description of this video below before I start the demonstration let's first get familiar with the environment I am doing all these demonstrations in my virtual environment created using Oracle virtual Bo virtual machines for the Data Guard primary and standby databases I am using Oracle database version 19.3 and for monitoring and management I am using OEM version 13.5 all the virtual servers have the 64-bit Oracle Linux 7.9 installed if you want to create your own virtual learning environment using Oracle virtual box virtual machines please watch my tutorials using the link given in the description of this video below here's a recap before we proceed so I have my my two Linux servers named Linux 1 which is my primary server and Linux 2 which is our standby server and the primary server has the primary database with the name test DZ for which I have already set up the environment and we will simply call the Data Guard manager command prompt from here I logged in s CIS and I'll use the command so configuration and we see that the test dz1 is currently the primary database and test dz2 is the physical standby database and our Data Guard broker configuration is working fine now let's take our log Gap scenario caused by an accidental deletion of archive logs before they were applied in the standby and how using an incremental backup from the primary we can recover the standby and bring it back into sync with the primary it does not matter from where the archive logs were deleted it maybe from your primary database before they have been shipped or from the standby after they were shipped but if the archive loocks are deleted before they have been applied in the standby it's a real serious situation but it is recoverable to recover from this situation you need to understand a few things which I am explaining in the form of a diagram once you understand this you can bring back your standby to life now in this diagram you see the logs are getting generated in the primary database in Chicago data set Center and shipped to the standby database in Denver data center but due to some issues log sequence number four and five were deleted before they were applied in the standby causing a log Gap even if the subsequent log sequences starting from 6 7 and 8 were available in the standby side they won't be applied because MRP cannot just jump skipping the sequence number four and five unfortunately you don't have the logs available in the primary or in the standby database and also you don't have any backups in this situation the quickest solution is to see until what scn or system Sange number the standby was synced up with the primary and take an incremental backup from that scn in the primary and then take the backup to the standby and recover the standby using that backup here we see that the log sequence number three was applied in the standby that corresponds to the SC number 3 ,000 so we'll have to take an incremental backup from SC number 30,000 in the primary once we take the backup guess who knows about it it is none other than the control file in the primary it knows about the backup that we just have taken so when we will transfer the backup to the standby we must also take the control file along with it but how can the standby use the same control file as the primary well there is a way by converting a copy of the primary control file to a standby control file and then transfer and then we start the standby database using the standby control file and recover it using the incremental backup that we took from the primary once we recover the standby database using the backup it will be synced up with the primary until the latest scn which is in this example 80,000 and from that point onwards Data Guard will start working as normal as the Gap is no more present now let's see this in action first I will stop the MRP in the standby then I'll stop the transport of logs from the primary and do a few lock switches to create a few archive logs then I will delete two of the newly created archive logs in the primary then I will start MRP in the standby and also start the transport from the primary we will see that the MRP is now waiting for those two logs which were deleted now before stopping the MRP process let's see what is the status of the MRP currently it is applying the log sequence 102 now we will stop the MRP process let's check the status again and it is stopped now we will also stop the log shipping from the primary we will go to the SQL prompt in the primary and using the command alar system set log archive desk State 2 equals to the scope equal to both we will stop the loog shpping from the primary to the standby and then we will do a few loog switches to create some logs I mean archive logs let's check the latest log sequence in the primary which is 107 now because the MRP is also stopped and log shpping is also stopped these recent log sequences will not be available in the standby side so let's validate that earlier it was available up to 101 yes up to 102 it is available but all the recent ones that is the 3 4 5 6 7 these archive logs are no more available say out of these all archive logs we will delete two the archive blocks which were recently generated say this is 104 and 105 which we are going to delete in the operating system level using RM command 104 and 105 now we will resume the log sipping from the primary and also the MRP process first let's start the MRP using alter database recover managed standby database using current lock file it is started and it is stuck in 103 now because four and five we deleted and six is available 7 is available but it cannot jump to those without resolving this gap of 104 and 105 now also we will enable the log shipping using alter system set log archive desk state two to enable and scope equals to vot and in the standby databases archive location we have the 1 2 and 103 and then 106 but 104 and 105 are not there and now because we don't have the backups of these missing archive blocks available we are in a real serious situation so we will proceed with the steps that I just explained in the slides before first we will connect to the standby database and take its scn number which is the latest using select current scn from V dollar database and this is the scn number and Beyond this there is no changes happening in this database because it is still stuck in the loog sequence number 100 three now the next step is we will take an rmen incremental backup in the primary database from this SC number now that we know the scn number on which the standby is stuck we will use that scn number to take an incremental backup in the primary so we are in the primary server and now we are going to first create One Directory to keep the backup pieces under the temp file system and the name of the directory is BKP so let's create it first then I will connect to the primary database using Arman prompt and then using an rmen run block I'm going to take a backup let me copy and paste it and then I'll explain I'm using this run block starting with this Carly BR and I am allocating one channel to the disk and the backup piece location is this temp VK that we just created and that is the format I'm using and the actual backup command is backup in incremental from scn this is the SC number that we copied from the standby then database and I'm ending the command here press enter this incremental backup should not take a very long time based on how many transactions have been carried out after that particular scn in the primary because ours is a test database and it should not take more than a few seconds and the backup has been completed next we have to take a backup of the current control file in the primary database for the standby database using the command backup current control file for standby and the format I am using for the same location where I have created the backup pieces and the name of the backup copy of the control file will be standby _ control. CTL press enter now let's go back to the command prompt and see the backup pieces in the BKP directory and we can see these three backups are available which we are going to transfer to the standby location using an SCP command because I have the user equivalence setup for the Oracle user between the Linux one and Linux to servers I don't have to specify a password to transfer these backup pieces just press enter and that will transfer all the three backup pieces to the standby server now let's go to the standby server and I have created the same backup directory in the standby server also you can have a different location or directory in the standby server in which case you have to catalog the backup pieces to let the control file know about the new location just to avoid this extra step I am creating the same directory structure in the standby server also for the backup pieces now let's do an LS LRT yes all the backup pieces are available next we will shut down the standby database which is currently in the mount State using shutdown immediate next we will start up the standby database in no Mount mode and the database has has been started in no Mount mode now we'll exit from here and connect to the database using rmen next we are going to restore the standby control file that we copied from the primary in the standby server using the command restore standby control file from this is the location where the copy of the backup is available and we will simply press enter and the standby control file has been restored to its designated location now we can mount the database using alter database Mount now at this point there's a very important thing to consider that is we have start up the standby database using the control file which we backed up in the primary so this control file does not know the locations of the data files in the standby usually the standby data file locations are different than the primary for example if you see the control file location has a particular string as test dz2 so we have to let the control file know about the location of the data files in your standby database if you are using the same directory structure or file locations in the primary and in the standby then you don't have to do this step as in my case it is different so I have to catalog the data file locations using this command catalog start with u001 ap Oracle AA data test dz2 data file this is the data file location in my case press enter and it will ask for a confirmation type in yes and all the data files have been cataloged in the new control file now the next command to use is the switch database to copy so that the sanges are permanent now we are almost done the next step is to recover the database using recover database no redo this no redo is required because there is no redo lock files required for this recovery it will be solely based on the incremental backup that we have taken press enter based on the volume of sanges taken place in the primary database since the scn that we took in the standby this recovery may take some time and you will see more lines in this output in our case it just finished momentarily the reason is between the scn where the standby was stuck and when we took the backup in the primary there was no change because this is just a test environment we have not made a lot of changes in the primary database so the incremental backup literally has nothing to be recovered but it doesn't mean that you don't have to recover you must use this command recover database no redo now we will go back to our SQL prompt and we will try to start the MRP process using all our database recover managed standby database using current log file disconnect and it has been altered now let's check the status of the recovery processes using our mostly used query here and we see that the MRP process is currently up and the log apply has been resumed from sequence number 107 now let's do a couple of loog switches in the primary and see how it catches up using alter system switch log file 1 2 3 that is enough and it should take the sequence number Advanced by three which is now 110 and let's go back to our standby database and execute this quy again yes we can see that the MRP is currently app Ling the 110 log sequence which is the latest so we have brought back our standby database into sync with the primary and currently there is no log Gap now let's check the current scn of the primary and the standby one more time using the select current SC from V dollar database and in the standby we can see it is 1726 4894 we will execute the same in the primary also which is 1726 6170 and both these numbers are very close to each other it means our primary and standby are in sync now you know how important it is to keep your archive loocks intact but is there a way we can protect the archive blocks until they are backed up or at least applied in the standby well yes there is an Arman backup policy that can protect the archive logs let's see that let's exit from the SQL prompt and connect to the primary database using rmen and then we'll use the so all command to display all the policies in the Armen and here the second last line if you see something here configure archive log delion policy to none that means right now the archive logs are not protected there are couple of values that can be set to protect the archive blocks which are say you want to delete the archive blocks only when they are applied in all the standby available in that case you can use this command I'm just pasting it here configure archive block deletion policy to apply it on all standby and if you press enter this this policy will take effect let's do a so and now we can say that our archive logs will be deleted only when they'll be applied on all the standbys another setting is say you want to delete only when it has been sent to the standby although it has not been applied then you can use this particular policy which I'm pasting here configure archive log deletion policy to shift to all standby it means it will be eligible for deletion only when the primary database knows that the archive log has been at least Shi to all the standby locations the third option is say you want to delete only when it has been backed up and also it has been applied in that case you can use this particular policy which I'm pasting here that is configure archive log deletion policy to back up one times to disk or whatever backup media you are using then apply it on all standby if you use this then it is going to give us a different level of prote so these are all different types of archive log deletion policies that you can set and protect your archive logs from getting deleted so I'll set it back to the first one which is my favorite that is configure archive block delion policy to apply it on all standby this is all for today so viewers please provide your feedback in the comment section of the video and hit the like button if you found it useful that will mean a lot to me and help grow this channel

Channel: YouVolve

Views: 1,093

Keywords: oracle database, oracle dataguard, standby database, physical standby, oracle data guard 19c, oracle physical standby database, data guard using oem, standby log gap, log gap resolution, how to resolve log gap, physical standby log gap, standby database log gap, rman incremental backup, recover standby using incremental backup

Id: ZA5czDcbyxY

Length: 21min 2sec (1262 seconds)

Published: Mon Dec 25 2023