The objective of this chapter is to show you how to create and manage a logical standby database environment. We've already learned what a logical standby database is and what are its highlights. Now it's time to study the installation and administration of the logical standby database with hands-on examples.
In this chapter we'll discuss the following topics:
It's important to know the logical standby database properties well in order to decide if your business needs the physical or logical option. The different log apply modes make them distinct solutions for data replication, high availability, and disaster recovery. By using SQL Apply (the log apply method of logical standby databases), Data Guard mines the redo data (which was transferred from the primary database), builds the SQL statements (which will result in the same data change as in the primary database).
Finally executes these SQL statements on the logical standby database as shown in the following diagram:
Maintaining this kind of standby database has its own pros and cons. Now let's see what they are.
Depending on your conditions, there may be cases where you don't want all the data in your primary database to be replicated. This is not possible with a physical standby database; however, the logical standby database offers to skip replication of some tables or schemas.
It's possible to use a logical standby database anytime to offload reporting jobs from the primary database because a logical standby database is always open for user connections. This is also available with the Oracle version 11g physical standby feature of Active Data Guard but it requires an additional license.
A logical standby database may contain additional schemas and objects that do not exist on the primary database. This feature also relies on the fact that the logical standby database is a read/write accessible database. We can use this feature particularly for the reporting jobs running on the standby database. It's possible to create indexes and materialized views on the standby database, which can be expensive to maintain on the primary database. Also, many reporting tools require us to create global temporary tables. These reporting tools may run on a logical standby database but not on an Active Data Guard standby, because Active Data Guard allows only read operations on the standby database.
The replicated data on a standby database normally needs to be non-modifiable in order to provide data consistency. Logical standby database is capable of guaranteeing this with the use of Database Guard settings. It's also possible to configure a logical standby database in order to allow users to create new objects and modify the data on these non-replicated objects or not allow any modification on the standby database.
There are specific Oracle database objects and data types that are not supported for replication in a logical standby database configuration. Updates on the following objects will not be replicated to a logical standby:
SecureFiles
(unless the compatibility level is set to 11.2 or higher)We should also keep in mind that changes on the tables or sequences owned by SYS
are not applied by SQL Apply, because SYS
organizes its own structure on the logical standby database. We should be careful so as to not put any user data under SYS
objects or create any object under the SYS
schema in the primary database manually.
Another important point is redo will not be generated for DML on Global Temporary Tables. Hence, they're out of the replication scope.
The following data types are also not supported in a logical standby database configuration. If a table contains a column with one of these data types, the entire table will be skipped by SQL Apply:
BFILE
VARRAYS
and nested tables)ROWID
and UROWID
And last but not least, DDL statements for materialized views and database links are skipped by SQL Apply. Therefore, these objects must be handled manually on the logical standby database, if necessary.
A logical standby database can be used for switchover or failover just like the physical standby database configuration. We can also configure fast-start failover with the logical standby environment. These properties make the logical standby database an appropriate solution for high availability and disaster recovery. However, the following considerations are very important if you use the logical standby database for high availability and disaster recovery: