Disaster recovery

7.4 Disaster recovery

As data warehouses become more operational in nature and need to be available 24 hours a day, even temporary outages have the potential to jeopardize the business. Just as with an OLTP system, it is critical to develop a strategy to minimize downtime of the data warehouse and develop a plan to recover from disasters that result in the loss of data. September 11, 2001, highlighted the criticality of having a business continuity plan in place.

It is important to consider what may go wrong, examine the expected cost of downtime and data loss, and compare that with the cost of the solution. The investment needed to enable business continuity in the face of a disaster is analogous to buying an insurance policy. Hopefully, you never need it, but it is there if and when you do. In many cases the resources invested to improve availability can also be used for other purposes.

When we talk about disaster recovery, we are referring to a catastrophic failure, such as a building catching fire or a bomb destroying the building. Other problems such as a hardware failure caused by a disk crash should be recoverable from your standard backup and recovery procedures. Disaster recovery is different, because it involves setting up your computer systems at an entirely different site, which presents a whole new set of problems.

The disaster recovery plan for your data warehouse is probably different from your transaction processing systems. One of the main issues with the data warehouse is its sheer size. Hopefully, at least one recent set of backup tapes is kept off site and those kept on site are held in a fireproof safe. Once you know that you have a good set of backup tapes to restore the database, your procedures should take into account the size of the database and the hardware required to rebuild it.

The importance of your data warehouse to the business may determine whether it is even recreated at your new temporary location. It may be that you decide that the business can do without the warehouse until your regular site is available again. Great care should be taken if you make this decision, because it may take much longer than you think to return to normal working conditions. Chances are that if you have a major failure, especially something that relates to a building, it could be many months before you return.

Once you have decided upon your disaster recovery plan, Oracle Data Guard can be used to help implement the plan.

7.4.1 Oracle Data Guard

Oracle Data Guard creates and maintains a copy of a database as a standby at another site. If the primary database becomes unavailable, applications can be restarted and run on the standby. The standby is initially created from a backup of the primary database. As changes are made on the primary database, the redo that is generated is transmitted to the standby, where it is applied, keeping the databases synchronized. Data Guard can be configured to ensure that no data is lost in the event of a failure. It can also be used to support planned maintenance operations such as hardware or operating system upgrades. Both the primary and standby systems can use real application clusters. There are two types of standby databases: physical and logical.

Physical standby databases have been available for a number of releases and are currently used to protect many OLTP systems and operational data stores. Because a physical standby database cannot be open at the same time it is being maintained, the database cannot be used for other purposes. Logical standby databases were introduced in Oracle 9i Release 2 to help solve this problem.

In Oracle 9i Release 2 a standby database may either be a physical standby database or a logical standby database, as follows:

• A physical standby database is an identical copy, block for block, of the primary database. It is kept synchronized with the primary database by recovering the redo data from the primary database (called redo apply) using the same process that is used for media recovery. When the standby is not running recovery, it can be open for read-only queries.

• A logical standby database contains the same logical data as the primary database, but its physical structure may be different. It is kept synchronized with the primary database by converting the redo data to SQL and then executing the SQL statements (called SQL apply). Data can be read at the same time SQL apply is applying changes; however, data cannot be updated. On a logical standby database you can also add indexes and materialized views to improve query performance. Additional tables may also be added. These tables can be updated at any time. At the time of writing, there are a number of restrictions on the data types, types of tables, and operations supported on a logical standby.

A Data Guard configuration can be made up of one primary database and up to nine logical or physical standby databases. A typical configuration is shown in Figure 7.41. The primary database transmits redo to both a physical and logical standby database, which may be at the same or different locations. The physical standby is used for disaster recovery, and the logical is used primarily for reporting but can also be used for disaster recovery.

Figure 7.41: Data guard configuration.

7.4.2 The data warehouse and Enterprise disaster recovery plan

The data warehouse could be incorporated into the Enterprise disaster recovery plan in a variety of ways:

• The logical standby database could be used as a source of data for the warehouse. Since it contains a copy of the data from the production system, it could be used as the source for the ETL process.

• The logical standby database could be used as the operational data store. Data could then be moved into a staging area on a different system, where it is transformed into a star schema and is ready for ware-house queries.

• The logical standby database could contain the warehouse tables. The tables maintained by SQL apply must have the same logical structure as the tables on the OLTP system, but additional tables could be created in the logical standby database to perform transformations and create a star schema. Any tables not being maintained by SQL apply can be updated.

The importance of your warehouse to the business will ultimately determine the strategy put in place for disaster recovery. We have now caught a glimpse of how even a data warehouse should be included in our disaster recovery plans and the features provided by Oracle that allow us to implement that plan.