Active Data Guard
In this section I will discuss the following
The read-only physical standby database can be used to offload query from the primary database, you can use select statements and complex queries against this this database freeing up resources on the primary database, also remember that the standby database could be localized thus reducing network latency. While the standby is open read-only, the following operations are disallowed
The difference between a read-only and active Data Guard, is that active Data Guard permits both read-only services and disaster recovery while the standby database is open for operation. Which allows for immediate switchover or failover without having to shutdown the standby and put it back in managed recovery mode so it can catch up with the recent redo generated by the primary, also with active Data Guard you will have real-time reporting database.
The main reason for a read-only standby is to reduce the resources on the primary, especially if you have a large database for example medical facilities, police departments, manufacturing, these areas generally use volumes of documents. You can also use a read-only database for bug fixing, testing new improved SQL code, temporary change the mode to read-only, perform your testing and then put it back in to recovery mode. If you are using flashback recovery then Data Guard will automatically synchronized the standby database once its been flashed back.
The downside is that once the standby database has been open in read-only mode it is no longer applying redo from the primary and thus becomes out of sync, here are the steps to open in read-only and to put it back in to recovery mode
open in read-only mode | alter database recover managed standby database cancel; alter database open; |
return to recovery mode | shutdown immediate; startup mount; alter database recover managed standby database disconnect from session; |
enable real-time apply of redo | alter database recover managed standby database using current logfile disconnect; |
The difference between a read-only standby and a snapshot standby is that the snapshot standby is fully up dateable. It was possible in Oracle 10g to open a standby database as read-write but as from version 11g you now have the snapshot feature. This new feature makes it simpler to make the standby read-write and to revert to back again with the use of the Broker, also it is advised to use the flashback database feature as it makes life a whole lot simpler.
snapshot standby | DGMGRL> convert database 'prod1dr' to snapshot standby; DGMGRL> show configuration; ## Check that redo is still being sent to the standby select status, sequence#, block# from v$managed_standby; ## you can double check some tables make sure that you are protected |
revert back | DGMGRL> convert database 'prod1dr' to physical standby; DGMGRL> show configuration; ## shutdown the physical database and restart it mount mode |
As you can see the broker makes light work of this.
There are two new features with Oracle 11g
Database Replay would allow us to capture production workload and test it against a snapshot database before and after changes to that database, the changes could be the following
SQL performance analyzer would allow us to test
Firstly lets talk about the database relay feature, once you have converted the physical standby database into a snapshot standby database, you can begin the steps to capture the production workload with database replay.
There are four main components that make up database replay
Workload capture | Workload capture uses binary files (called capture files) to capture any changes received by the Oracle database are tracked and written to these capture files, you can specify where these files are located. |
Workload processing | This process is typically run o the snapshot database, the capture files are converted to replay files and and metadata for replaying the workload in the snapshot database. There is no limit to the number of times the converted files can be replayed. |
Workload replay | The workload replay uses a replay client program that processes the replay files and submits calls to the database with the same timing and concurrency as in the workload capture system. A calibration tool is provided to assist you in determining how many replay clients you will need to emulate the workload from the original capture system. |
Analysis and reporting | Database replay provides extensive reports from simple errors listings to differences in rows returned by DML statements. You can also access AWR reports for detailed comparison between the capture and replay system. |
Here are the steps to capture a workload from the primary and replay it several times on the snapshot standby
capture workload and replay it on the snapshot standby | ## First we must configure the Database control on the primary
## At this point you are done with the primary database, copy the capture files to the snapshot standby ## You now need to process the captured files, this can be done on either the primary or the snapshot database but i have
##
First we must create a restore point, on the snapshot database ## Now we are ready to apply the workload to the snapshot database, at least apply it once without any changes to get a
## Now rewind the snapshot database to the restore point we made earlier ## When you have completely finished its time to revert the snapshot database back to a physical database ## Now you should be back as you were. |
SQL Performance Analyzer is tightly integrated with SQL tuning sets, SQL tuning advisor and SQL lan management functionality. When database replay is running in its client replay mode the SQL Performance Analyzer is capturing detailed statistics and plan information for every DML statement and query executed against the test environment sequentially before and after the changes occur. SQL Performance Analyzer uses these statistics and produces reports that outlines the workload improvements after the changes were made, it also highlights any degrade of performance as a result of the changes made. Use of the SQL Performance Analyzer encompasses five steps
SQL Performance Analyzer |
|
Oracle have a number of whitepapers on Real Application Testing and they have a dedicate web section as well.
We have covered physical standby using read-only and read-write and snapshot standby, all of these variants have one thing in common there is an accepted impact to the recovery time objective (RTO) and potentially to the recovery point objective (RPO).
Active Data Guard allows you to open the physical standby database in read-only mode but what is different is that it will continue to apply redo keeping the standby up to date, this allows you to use this standby as a real-time reporting database or even to backup the primary data, also as a result it does not have any impact on RTO or RPO.
Active Data Guard also supports RAC, which means that you could have a single primary and a Active Data Guard Reader Farm
To enable Active Data Guard, you need to open the physical standby in read-only mode and start redo apply. The Data Guard should be in one of two states prior to enabling Active Data Guard
To enable Active data guard you can either use SQL Plus or the Broker
Enable Active Data Guard using SQL Plus | ## First stop the redo and open the database as read-only sql> recover managed standby database cancel; sql> alter database open read only; ## Restart the redo sql> recover managed standby database disconnect using current logfile; |
Enable Active Data Guard using the Broker | ## First stop the redo DGMGRL> edit database prod1dr set state='apply=off'; ## Open the database as read-only sql> alter database open read only; ## Restart the redo DGMGRL> edit database prod1dr set state='apply=on'; |
I know what you are think what no option to set to enable Active Data Guard, remember all Active Data Guard is, is a physical standby database that is open read-only with redo being applied, so no special command to turn it on.
That's it for this section, create yourself a test environment and have a play around especially with Real Application Testing, again with everything you test make sue that you have the logfiles open and see what Oracle is doing in the background, before you know it you will have a very good understanding how Data Guards internally.