Define, Install and Test Real Application Clusters (RAC)
ASM Instance and ASM Disk Groups
How to manage an ASM instance with ASMCMD and ASMLIB
Implementing Oracle 11g data guard and data guard protection modes
Creating an Oracle physical standby server
CRITICAL SKILL 8.9
Understand Data Guard
Providing highly available, recoverable, and secure stable environments is definitely a goal that needs several components for handling different areas. We have already discussed RAC and ASM; the Oracle Data Guard environment offers an additional piece to this architecture. Cluster servers normally reside in the same data center because of the requirements for the interconnect and clusters share disk. Because of this, there is potential for failure of this environment. Also, what about restores and disaster recovery sites? Data Guard can provide solutions in this area, as well as help offload some of the intense resource consumers such as hot backups, exports, and reporting. With Data Guard 11g, the standby server can be an active server that can be used to offload reporting and backups, as well as continue to operate in its standard role for failover and provide a disaster recovery server. Using Data Guard, one or more standby servers could be at different locations to allow for failover to a different site. So, for high availability, Data Guard provides solutions on both fronts of failover and recovery.
Data Guard has been available in some form since Oracle 8i; however, with 11g the secondary database can recover while it is open for reading. This means that ad hoc queries (ones that probably haven't been tuned and are resource hogs),reports, backups, and exports can be off-loaded to this system. Being able to use the failover hardware for useful business purposes makes it a cost-effective part of a disaster recovery plan.
The Data Guard manager provides a practical way to manage the primary and secondary servers. It can allow for manual failover, set up the automatic failover, and place the secondary in snapshot mode. The snapshot mode actually puts the database in read and write mode so that testing can be done against a current set of production data. More on this snapshot mode in a little bit, but first let's walk through some of the architecture and setup for Data Guard.
CRITICAL SKILL 8.10
Explain Data Guard Protection Modes
The different modes of setup for Data Guard allow for different configurations that are adaptive and that are dependent on available hardware, processes, and ultimately business needs.
The modes for the Data Guard configuration include maximum protection, maximum availability, and maximum performance. The Data Guard configuration can also have more than one standby database. Having multiple standby databases is even recommended in modes like maximum protection, in order to make sure at least one standby is available.
Maximum protection is designed for zero data loss, while the redo transport is synchronous. Synchronous transport means that you're applying the database transactions at the same time on the primary and secondary database servers. The primary waits for a response telling it that the transaction has been applied to the standby database before it commits the transaction. Having two standbys or even a RAC setup on the secondary site would be recommended in this situation, because if the standby fails, the primary will be halted in this mode.
Maximum availability also has the goal for zero data loss (again with the redo transport being synchronous). The difference is that if the standby fails or if there is a connectivity issue, it will allow the primary to continue and the standby to fall slightly behind. It is not as critical to have more than one standby for this mode because of the fault tolerance.
Maximum performance has the possibility of minimal data loss, but the performance is the concern on the primary. This is because the redo transport is done asynchronously and it doesn't have to check back with the primary before the primary does a commit. So, if transport is a concern for slowing down the primary database and the performance risk is higher than any data loss, the maximum performance mode will allow for that.
As discussed earlier, each mode has different services that take care of the transport and application:
- Transport Services are the pieces that handle the synchronous and asynchronous transport. These services move the log or transactions to available standby servers and verify that they are being applied to these servers. Synchronous transports validate that transactions have been applied on both primary and standby servers before committing the transactions. The asynchronous transport will validate that transactions have been sent, but transactions will be committed on the primary even if not completed on the standby.
- Apply Services take care of the SQL Apply or the Redo Apply. Apply Services take the SQL statements or redo logs and control applying them to the standby databases. SQL Apply takes the redo and transforms it into SQL statements. After running the SQL statements on primary and standby databases, the standby matches the primary database and can be used as a logical standby database. Redo logs are used for keeping the physical standby database consistent with the primary database. Redo information is applied to the standby databases by Redo Apply and controlled by the Apply Services.
- Role Management Service is for the switching of the standby to primary. This is used either for a planned switchover or for the failover due to an outage of one of the servers.
Figure 8-9 shows a configuration of the Data Guard environment and how the primary and standby servers do not even need to be in the same location. The servers can be in the same data center, down the street, or cities apart from each other, depending on the purpose and need. Having the servers in a different city provides high availability even in an event of a disaster in the location of the primary database. Also in Figure 8-9, notice the options that are available for uses of the standby server, such as reporting, system testing, or even running backups to take this type of load off of the primary server.
FIGURE 8-9. Data Guard configuration
With Redo Apply, the standby database can be opened for read-only queries. The recovery on the standby is canceled and the database is then opened. If you're using the Active Data Guard option, the Redo Apply can be started again to allow the standby to have real-time data while having the database open. This can be done in sqlplus on the standby database:
SQLPLUS> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SQLPLUS> ALTER DATABASE OPEN READ ONLY; And then to start applying the redo again: SQLPLUS> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; /* The database is still open for read-only queries and reporting as the logs are being applied. */
Another option for using the standby database is as a snapshot database that can be updated and used for testing. During this time, no logs are applied. After testing, the snapshot database is converted back to standby database by using a restore point with flashback database:
Create Snapshot: SQLPLUS> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; Convert back to Standby: SQLPLUS> ALTER DATABASE CONVERT TO PHYSICAL STANDBY DATABASE;
When using a snapshot database with SQL Apply, stopping the apply of the SQL statements is not necessary. This is because the standby is kept synchronized by having the same SQL being applied on the primary applied on the standby as well. So, while the standby is still being synchronized, it is also available for queries, running reports, or backups. The commands for setting up the standby for read access shown here are not necessary, but are done during the initial setup to show that SQL Apply is being used:
SQLPLUS> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;