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:
- Features and working principles of the logical standby database
- The pre-installation steps for a logical standby database configuration
- Creating a logical standby database from a physical standby database
- Verification of the newly created logical standby database configuration
- Customizing the environment with selective replication, Database Guard settings and creating an independent database object on the logical standby database
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:
- Tables containing LOB columns stored as
SecureFiles(unless the compatibility level is set to 11.2 or higher) - Tables with virtual columns
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- Collections (including
VARRAYSand nested tables) - Multimedia data types (including spatial, image, and Oracle text)
ROWIDandUROWID- User-defined data types
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:
- There is no guarantee that all primary data will be present in the logical standby database. We should be aware of the unsupported objects that will not be replicated. If there are important tables on your primary database, which will not be replicated because of the unsupported data type, you should consider the physical standby database for these purposes.
- Once we failover to a logical standby database, all other standby databases in the configuration must be recreated. This is not the same on physical standby configuration. Physical standby databases are able to send redo to other standby databases in the configuration after a switchover or failover. If you consider using more than one standby, using a physical standby for disaster recovery will be more effective.
- Physical standby offers higher recovery performance than the logical standby because it consumes less memory, CPU, and I/O resource on the apply process. If the primary database has high redo generation rate, you can consider using a physical standby for the purposes in question.
- The management of a logical standby configuration is more complex than that of physical. In a physical standby database we start Redo Apply and it's guaranteed that all the changes on the data will be replicated to standby. Logical standby will require more manual administrator interferences and they need to be consistently synchronized and work with optimum performance.