Installation and Configuration
Before you start to build Data Guard or any database for that matter setup there are two areas that need to be discussed
The above two are then incorporated into your SLA agreement as you need to know what can be achieved before you commit to it.
An RPO is basically "How much data are you willing to lose when a failover occurs?", generally we speak of seconds, minutes or even hours, however also speaking in transactions will also give you a understanding of how much data may have to be entered manually to get the system back how you want it. When deciding on the RPO think about the following
If the first answer to the first question is yes, then how much, just remember you may need an army of people to re-key all the lost transactions and if this fails a few times in a row it can get very expensive. Also remember that your standby could go down, while this is down the risk on the primary become high as you are not protecting your transactions.
The biggest part of a Data Guard solution is the network, you must have a network capable of being able to keep up with delivering the redo from the primary to the standby database, so distance may play an important part on where you place your standby database. Although you don't need a dedicated network for Data Guard it is well advised to have one, you need to determine the primary's database redo generation rate at peak and steady states to determine the network latency and bandwidth you can sustain and how it will affect your production throughput.
RTO is different to RPO, RTO is about how fast you can get the service back up and running again, the following all play a part in the RTO
All of the above adds time when trying to recover a database environment, obviously this is the area where your design is most impacted, if you have the cash you can create a very fault tolerance solution but if you are on a budget then the solution could be vastly different. However having up to date documentation and knowing the procedures costs very little. Make sure that the business is fully aware of the time scales that you need to get a failed primary database backup and running and any future impacts you may have for example will you need to fail back which may result in a small outage. Even if the outage took a little longer, as long as management can see that you have everything under control and procedures are being followed you come out of this with great respect by your fellow piers.
Once you are armed with your RPO and RTO you can start making some decisions about the following
We use the Oracle parameter log_archive_dest_n where n is number between 1 and 9, which means that you can have up to 9 standby databases. This parameter defines where and how the redo is sent either a local archive log file or a standby database.
As we mentioned on the previous section (architecture) there are 3 protection modes, this is the RPO part and there are some important points to remember about each mode
This is the default mode, remember you get the highest performance but the lowest protection. How much data you lose depends on the redo rate and how well you network can handle the amount of redo also known as transport lag. Even if you have a zero lag time you still will lose some data at failover time because the primary database is a RAC the final apply of he remaining redo must find a common point in the redo streams from the primary which will result in some data loss (potentially 3 to 6 seconds), regardless of the transport mode.
You can have up to 9 standby database and you will use the Asynchronous transport (ASYNC) with no affirmation of the standby I/O (NOAFFIRM). You can use this anywhere in the world but bear in mind the network latency and making sure it can support your redo rate.
While it is not mandatory to have standby redo logs (SRL) in this mode, it is advise to do so. The SRL files need to be the same size as the online redo log files (ORL) and you need to have the same amount plus one (if you have a RAC you need plus one per RAC instance).
If the primary database is in a RAC environment a node that lost connection to the standby will stop sending redo, but the other instances that still have a connection will continue to send redo, the RAC instance ARCH process will continually ping the standby database waiting for its return. When it does return the ARCH process will resolve any gaps but the LGWR process will not restart the LNS process for the current redo stream until the next normal log switch at the primary database.
This is the middle middle of the range, it offers maximum protection but not at the expense of causing problems with the primary database. However you must remember that it is possible to lose data, if your network was out for a period of time and the standby has not had a chance to resynchronize and the primary went down then there will be data loss.
Again you can have up to 9 standby databases and you will use Synchronous transport (SYNC) with affirmation of the standby I/O (AFFIRM) and SRL files.
The network has a greater impact on this solution, make sure that you have low latency and adequate bandwidth and that you adjust the net_timeout value to an appropriate value.
Most documents on the web are stating network latencies of 1ms to 20ms translate from 0 miles to 200 miles (320km) distance between your primary and standby database. I will be going into greater detail on network performance tuning later in this section.
When a standby database is deemed to have failed the primary database forces a log switch to "fix" the zero data loss point and then begins generating redo that is not sent to the standby database. In an Oracle RAC this log switch causes all primary instances to stop sending redo even if they can communicate with the standby database. If his was your last SYNC standby, the protection mode drops to unprotected, otherwise it stays at maximum availability. Once again the ARCH continually pings the standby database and when it returns any gaps in the redo will be sent and a forced log switch will be sent to all instances to start the LNS process, once everything has caught up the standby database is marked as synchronized.
This offers the maximum protection even at the expense of the primary database, there is no data loss.
In order to open the primary database you must have at least one connectable standby database, if not then the database will crash with the error message "a minimum of one standby database destination is required". When that last standby database is not connectable the LGWR process will attempt to reconnect before abandoning the last standby database, it will try about 20 times sleeping for 15 seconds between each attempt, during this time the primary database is not allowed to generate any redo, basically stalling the database (this also includes the whole RAC, if RAC is being used). If the standby does come back the LGWR will reconnect and send the last bit of redo and processing will resume. If it does not come back then the primary instance will crash and another instance in a oracle RAC (again if RAC is being used) will perform the crash recovery sending all the final bits of redo to its synchronized standby database, the primary will not open if at least one standby database is available.
Oracle advises you to have at least two standby databases for maximum protection.
The order to setup Data Guard is the following
alter database set standby to maximum performance; (default)
alter database set standby to maximum availability;
alter database set standby to maximum protection;
When defining the redo transport mode you have to change the log_archive_dest_2 parameter, you will also need to set the parameters db_unique_name and valid_for attributes and tune the net_timeout and reopen attributes.
|LOG_ARCHIVE_DEST_2='SERVICE=oradg_DR0 ASYNC NOAFFIRM'
|LOG_ARCHIVE_DEST_2='SERVICE=oradg_DR0 SYNC AFFIRM'
Tuning the Network
Knowing how much redo your primary database will be generating at peak and steady times is important because you will know how much traffic the redo will generate and if your network can support that amount of traffic.
You require the following information and configuration
Most companies now have adequate networks between sites, however understanding how things are working underneath helps when you have to fault find, obviously you are going to be using fast network interfaces and switches (preferably 1GB minimum).
There are a number of of different leased lines that you may have at your company which have different speeds and costs
Networks basically have two things to consider bandwidth and latency, most networks if they have the same length will have the same latency, bandwidth is the how much data you can push down the line at the same time, the bigger the pipe the more data you can push across the wire in a shorter time.
You can see how much redo you are generating either looking at the AWR reports or by examining the alert log and noting the time the log switches and then using some basic maths to calculate the average. If you have a RAC environment you must add all the instances redo and then calculate as a total to obtain the bandwidth requirements.
One point to make here is get the required bandwidth for your peak time, it may be tempting to say that you can catch on the redo during the non-peak times but that exposes you to a risk where the redo is not synchronized with the standby and remember that maximum performance and maximum availability can result in loss of data, so rule of thumb here is get the most you can.
So lets say you have a 3 node RAC that are generating the below
|Node 1 and Node 2 (OLTP)
|steady state: 2 MB/sec
peak state: 5 MB/sec
|Node 3 (Batch)
|peak state: 12 MB/sec
It looks like you need between 12MB/sec to 17MB/sec, but due to network errors, overheads, etc you probably want at least 20-50% more which brings the total up to 15 MB/sec to 21MB/sec. Because earlier the leased lines are measured in megabits we need to multiple it by 8 which gives us a figure of 120Mbits/sec to 168Mbits/sec, which means that a OC-3 connection should able to handle the data, remember most the of the time we will not be running at a peak state for all instances at the same time.
Latency does affect maximum availability and maximum protection, in a perfect world 1ms RTT is about 33 miles (53KM), to keep the production impact down to 4% range you will need to keep the latency down to 10ms or 300 miles, when you ping your remotes sites try different packets size and different times during the day and see what values are returned.
Oracle buffers data into session data units (SDU) which have a default size of 8192 bytes, redo data is usually larger than this, so we would need to change the size of the SDU buffer which does increase performance, you could just set it in the sqlnet.ora file the parameter is default_sud_size. To make this available to a specific connection we want to set it at the TNS level in our connection descriptor for our standby database. We also need to set in the Listener on the primary database so that incoming connections from the standby database also get the maximum SDU size
### TNSNAMES.ORA (primary)
### LISTENER.ORA (primary)
Note: don't forget to set it up on the standby database as well.
This will now cause Data Guard to request 32,767 bytes for the session data unit whenever it makes a connection to the standby called DGPhysical1
There are a number of TCP network parameters that you may need to adjust, if you find that you are reaching the limits of your network, the most of the time you will not need to adjust these.
There are also a number of network queues that can be adjusted as well, here are some guide lines
To tune the queues use the below, remember that they must be set in both directions
|Tune Queue lengths
|# ifconfig eth0 txqueuelen 10000
sysctl -w net.core.netdev_max_backlog=20000
SRL File I/O
The SRL files are where the RFS process writes the incoming redo so that it is persistent on disk for recovery. You should configure them for all protection modes, having them saves the RFS process from having to create the archive log files, if they weren't there the RFS on the standby database has to create a archive log file the same size as the primary every time the primary performs a log switch, this means a delay while the standby creates these files, not good especially if you are using maximum protection as the database on the primary will pause while these SRL files are being created on the standby, hence why it is mandatory to create SRL files when using maximum availability and maximum protection.
In maximum availability and maximum protection modes the disk write to the SRL file must occur prior to sending an acknowledgment back to the primary database that the redo has been received, this is called AFFIRM processing. The following best practices for SRL files
The Oracle Maximum Availability Architecture team (MAA) have performed many tests regarding the SRL files, network tuning, just do a search on Goggle and view there best practices documents, they have managed to increase performances by 8000% plus in some cases.
As you are aware Oracle 11 Data Guard will read from the log buffer first, so you will want to size the buffer so that LNS is always able to find the redo that it needs to send within the log buffer. You can monitoring the I/O to the ORL files for an increase above normal will tell you whether the ASYNC LNS processes are falling into the ORL file. Increasing the log_buffer parameter can help keep the LNS process reading from memory. You can track this via the view x$logbuf_readhist. A low hit ratio indicates that the LNS frequently reading from the ORL instead of the buffer log. It is also possible for the LNS process to fall all the way down and read from the archive logs to the network is not up to scratch. You can use Oracle compression feature to reduce network bandwidth, a good test to see if this is worth doing is to take one of your archive logs and run them through WinZIP to see how much space you save, if its not greater than 35% then you should not bother with compression.
You can configure compression for gap resolution only or for gap resolution and asynchronous standby destinations, by default Data Guard does not compress the redo
|## All standby databases should use compression, set below parameter to TRUE
## OR state specific standby databases that should be compressed
LOG_ARCHIVE_DEST_2='SERVICE=PRIMARYDG01 ASYNC NOAFFIRM COMPRESSION=ENABLE'
You can also use the Data Guard broker to set this parameter which we will discuss in a later section.
Data Guard Installation
There are a number of things we need to setup and configure, so I am going to split off these sections otherwise this section will become to large, select what section you need but I have put them in order. Some sections I will go into more depth than just the setup procedures, for example in the broker section I discuss what it is, how it works, etc.
Due to the fact I have a limited knowledge of Data Guard theses sections will be updated from time to time as I become more experienced with the data Guard product.