Leveraging Logical Standby Databases in Oracle 11g Data Guard
January 27, 2010
Synopsis. Oracle Data Guard still offers support for the venerable logical standby database in Oracle Database 11g. This article the eighth in this ongoing series on Oracle Data Guard investigates how data warehouse and data mart environments can effectively leverage logical standby database features, but simultaneously provide a final destination when failover from a primary database is mandated during disaster recovery.
The prior article in this series explored how to implement Fast-Start Failover between an Oracle 11gR1 primary and physical standby database, including how to:
This article will illustrate how to:
Before I tackle all these tasks, however, Ill review some of the benefits and disadvantages of logical standby databases as well as delve into the architecture of how redo data is applied to a logical standby database via SQL Apply.
Logical Standby Databases: Benefits
Even with all the new features that Oracle 11g adds for physical standby databases (e.g. Real Time Apply), a logical standby database still offers some interesting alternatives for offloading a reporting workload, protection against physical block corruption, patching, and even disaster recovery purposes:
Effective System Resource Use. A logical standby database can function as an independent production database that still accepts updated data and transactions from the primary database. However, unlike a physical standby database, a logical standbys physical structure may be different (e.g. different tablespaces or datafiles). In addition, change data may be selectively applied to particular tables and indexes. Its therefore possible for a logical standby database to offer data from a combination of sources: tables that are perfectly synchronized with the primary database, plus tables whose changed data has been excluded from application to the logical standby database, and even tables which exist only in the logical standby database.
Primary Database Workload Reduction. Since a logical standby database will capture change data for all supported datatypes more on this later! this also means that its possible to build materialized views, materialized view logs, and even additional indexes on existing production tables that would be prohibitively expensive to maintain against the same tables on the primary database. Logical standby databases are therefore excellent targets for data warehouse, data mart, or decision support applications that only need to read production data, but that could also utilize additional constructs (e.g. bitmap indexes) for speedier query processing. Logical standby databases are also excellent alternatives to databases created using basic replication because theyre much easier to maintain.
Increased Data Protection. Because a logical standby databases datafiles arent an exact physical copy of the primary databases datafiles, it offers protection against physical block corruption on the primary database because redo is applied using SQL Apply methods. And just as with a physical standby database, its also possible to delay DML thats been generated on the primary database from being applied against the logical standby database by implementing delay features.
Patching. Because it can assume the role of the primary database during a switchover operation, a logical standby database can facilitate extremely quick Oracle Database patching and upgrade operations. The patches are first applied to the original logical standby database, which then assumes the primary database role after switchover is complete. Once the original primary database is converted to a logical standby database, it can be upgraded as well.
Caveat DBA: Limitations of Logical Standby Databases
While logical standby databases have some excellent benefits, they do have some definite drawbacks as well, including restrictions on unsupported objects and datatypes, limited support for DDL and DCL commands, and what I believe are some glaring implications for failover situations:
Unsupported Objects. A logical standby database cannot accept updates for the following objects:
Unsupported Data Types. In addition, any tables that are comprised of or whose columns contain the following unsupported data types cannot be updated in a logical standby:
Data dictionary view DBA_LOGSTDBY_UNSUPPORTED_TABLE shows all tables that arent supported for update, and data dictionary view DBA_LOGSTDBY_UNSUPPORTED shows all tables that are not supported for update because of their datatypes (tables that use compression or are owned by SYS are already suppressed from this view).
Unsupported DDL and DCL Statements. Oracle 11gR1 doesnt pass on several DDL and DCL statements from the primary database to a counterpart logical standby database, and most of these restrictions make perfect sense. For example, the ALTER DATABASE, ALTER SESSION, ALTER SYSTEM, and CREATE MATERIALIZED VIEW commands will not be passed from the primary to any of its logical standby database(s). Appendix C of the Oracle Database 11gR1 Data Guard Concepts and Administration Guide contains a complete list of these unsupported DDL commands.
Unique Row Identification Required. Its possible that rows added to a target table in a logical standby database will not have the identical value for ROWID as their counterpart table in the primary database. Therefore, all logical standby target tables must enforce uniqueness via either a PRIMARY KEY constraint or a UNIQUE constraint. If row uniqueness is maintained within an application instead of using database referential integrity constraints, then the PRIMARY KEY constraint can be switched to a state of RELY DISABLE. This essentially places the constraint into NOVALIDATE mode and eliminates the overhead of primary key maintenance on the primary database. Data dictionary view DBA_LOGSTDBY_NOT_UNIQUE shows all tables that dont contain sufficiently-unique rows.
To demonstrate some of these these restrictions, Ill construct some new database objects in the primary database:
Listing 8.1 shows the DDL and DML I used to create and populate the tables in the primary database, and Listing 8.2 shows the results of queries against views DBA_LOGSTDBY_UNSUPPORTED_TABLE and DBA_LOGSTDBY_UNSUPPORTED after these objects have been created.
Disaster Recovery. A logical standby database can still be a target of a switchover or failover from the primary database. However, since there are no guarantees that all data will be present in the logical standby database, it cannot be used for the complete recovery of the primary database. Further, since the logical standby databases physical structure wont be identical to the primary databases physical structure, a logical standby database cannot be used as an alternate target for RMAN backup processing. Finally, perhaps the most glaring issue with failing over to a logical standby database is that once failover is complete, all other standby databases must either be reinstated or recreated.
Applying Redo Via SQL Apply
The main difference between physical and logical standby databases is the manner in which changes to data are applied. For a logical standby database, the change vectors stored in incoming redo logs are transformed back into DML statements using LogMiner technology, and those transformed DML statements are then applied to the appropriate objects. This concept is collectively termed SQL Apply. Its quite elegant, and it uses several parallel execution servers and background processes that handle the application of changed data. Please see Figure 8.1 for a corresponding flowchart of the steps listed below:
Step 1: Acceptance. The READER process reads change vectors from the incoming archived redo logs.
Step 2: Conversion. PREPARER processes then convert each change vector into either table changes or Logical Change Records (LCRs). One redo log file may be handled by one or more PREPARERs, which then stage the LCRs in the LCR Cache in the shared pool of the logical standby databases SGA. Note that these LCRs are not really representative of any specific transactions ... yet.
Step 3: Assembly. The BUILDER process then assembles transactions from groups of individual LCRs.
Step 4: Analysis. Next, the ANALYZER process reviews all LCRs, eliminates any transactions that are unnecessary, and determines which transactions (if any) have dependencies (e.g. parent-child relationships). It also sorts them so that theyll be applied in an appropriate order to avoid violation of referential integrity constraints.
Step 5: Coordination. The COORDINATOR process (LSP):
Step 6: Application. Finally, multiple APPLIER processes:
Creating a Logical Standby Database
Creating a logical standby database is a relatively straightforward process. Note that while I could do this using Enterprise Manager Grid Controls Add Standby Database wizard, Ive used manual methods here to better illustrate all thats involved in its creation:
Step 1: Create a Candidate Physical Standby Database. First, Ill create a new physical standby database named LSBY on my standby database server that Ill use as the eventual target for my logical standby database. Ill utilize the same methodology to create the new database as I did in the first article in this series.
Step 2: Halt Redo Apply on Candidate Physical Standby Database. Once the candidate physical standby database has been created, Ill need to halt all redo apply processes. This insures that any change data wont be applied to the candidate physical standby until I complete the creation of the LogMiner dictionary in an upcoming step:
# Execute from SQL*Plus connected to LSBY database: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Step 3: Prepare the Primary Database to Support Logical Standby Database Requirements. Next, Ill modify the initialization parameters of the primary database to prepare it for the eventual transformation of the candidate physical standby database to a logical standby database:
# Execute from SQL*Plus connected to ORCL_PRIMARY database: # Set up for later role transition to logical standby SQL> ALTER SYSTEM SET log_archive_dest_3 = LOCATION=/u01/app/oracle/oradata/archivelog/ 2> VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME=lsby; # Halt possibility of ORA-01555 (snapshot too old) during LogMiner dictionary creation SQL> ALTER SYSTEM SET undo_retention=3600;
Step 4: Build a LogMiner Dictionary. A LogMiner dictionary is required so that the logical standby databases Log Apply Services can make sense of incoming change vectors and translate them to appropriate DML, DDL and DCL statements. Executing procedure DBMS_LOGSTBY.BUILD creates the LogMiner dictionary, enables supplemental logging, and then waits for any existing transactions on the primary database to complete:
# Execute from SQL*Plus connected to ORCL_PRIMARY database: SQL> EXEC DBMS_LOGSTBY.BUILD;
Step 5: Convert the Physical Standby Database to a Logical Standby Database. At this point, the candidate physical standby database is ready to be converted to a logical standby database. Ill complete its transformation with a few simple SQL commands issued against the candidate physical standby database:
# Execute from SQL*Plus connected to LSBY database: SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY lsby; # Shut down and remount the LSBY database: SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT;
Step 6: Open the Logical Standby Database. All thats left to do is to open the new logical standby database and initiate SQL Apply processing against it:
# Reset the LSBY logical standby database's incarnation ... SQL> ALTER DATABASE OPEN RESETLOGS; # ... and activate it as an logical standby by starting SQL Apply processes ... SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Step 7: Verify Logical Standby Database Configuration and Performance. At this point, its crucial to verify that archived redo logs are getting transmitted to and applied at the new logical standby database. Ive captured the appropriate queries to do this in Listing 8.3; alternatively, I could simply monitor the logical standby databases alert log to insure that redo is indeed being transmitted to and applied against that database. Ive highlighted several sections of database LSBYs alert log that show this to be the case.
Step 8: Enroll the Logical Standby Database With Data Guard Broker. Finally, Ill register the LSBY database with Data Guard Broker with a few simple commands from a DGMGRL session:
DGMGRL> ADD DATABASE lsby AS CONNECT IDENTIFIER IS 'lsby'; DGMGRL> ENABLE DATABASE lsby;
In Listing 8.4, Ive laid out the alert logs from databases ORCL_PRIMARY and LSBY, concentrating on the eventual transformation of the LSBY database from a physical standby to a logical standby database. Listing 8.5 shows the resulting Data Guard Broker configuration and corresponding settings for the primary (ORCL_PRIMARY), physical standby (ORCL_STDBY1), and logical standby (LSBY) databases just after the transformation of LSBY into a logical standby database was complete.
SQL Apply Filtering
As I previously described in the list of logical standby database features, a logical standby database doesnt have to accept and apply all incoming DML; in fact, these DML can be filtered (and thus ignored) based on either:
To illustrate this feature, Ill use the DBMS_LOGSTDBY.SKIP procedure to construct and apply a simple SQL Apply filter that prohibits the application of any DML against the OE.SUBCUSTOMERS table I built earlier:
SET SERVEROUTPUT ON BEGIN DBMS_LOGSTDBY.SKIP( stmt => 'DML' ,schema_name => 'OE' ,table_name => 'SUBCUSTOMER', ,proc_name => NULL ); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Failure during setup of DML restrictions on OE.SUBCUSTOMER'); END; /
If I now issued DML against the OE.SUBCUSTOMERS table on the primary database - for example:
UPDATE oe.subcustomers SET last_name = 'Smith-Patel' WHERE MOD(customer_id, 5) = 0; COMMIT;
then even though its been successfully committed on the primary database, Oracle 11gR1 Data Guard would prohibit the transaction from being propagated to the logical standby database.
Managing SQL Apply Filters Through DGMGRL. Alternatively, I could have also implemented this SQL Apply anti-DML filter against table OE.SUBCUSTOMERS through Data Guard Broker Manager by setting appropriate database properties for the LSBY logical standby database. Table 8.1 describes the available SQL Apply filtering that DGMGRL can activate or deactivate:
Managing SQL Apply Filters Through Enterprise Manager Grid Control. SQL Apply settings can also be viewed and manipulated from the Edit Standby Database Properties panel in EM Grid Control.
ALTER DATABASE GUARD: Guarding Against Undesired Changes
By default, a logical standby databases data is locked from any unwarranted or accidental modification (except by the SYS user). Oracle provides the ALTER DATABASE GUARD <directive>; command to modify the permitted limits of modification to logical standby data:
Managing Remote Archived Log File Retention
If the logical standby has been set up using Oracle 11g recommended best practices, then a Flash Recovery Area (FRA) was defined during its creation, and any archived redo logs that have been applied to the logical standby will be automatically deleted whenever sufficient space pressure is detected on the FRA. As an alternative to the FRA, however, Oracle 11gs DBMS_LOGSTBY package offers two parameters to control archived redo log cleanup for a logical standby. Procedures APPLY_SET and APPLY_UNSET use these parameters to determine when cleanup should occur:
Scheduling Jobs Via DBMS_SCHEDULER
Interestingly, its also possible to create a scheduled job with DBMS_SCHEDULER on a logical standby database with a few (obvious) caveats. First, a scheduled job can exist on either a primary database or a logical standby database, but a scheduled job cannot exist on a physical standby database because it would then be possible for a scheduled task to run on both the primary and its physical standby counterpart a most undesirable outcome!
However, whenever a primary database switches roles with its counterpart physical standby database, the new primary database will automatically resume execution of scheduled tasks previously scheduled on the original primary database. DBMS_SCHEDULER uses the DATABASE_ROLE attribute of a job (modified with the SET_ATTRIBUTE procedure) to determine whether it should run on either (a) the primary database, or (b) a specified logical standby database. Since there can only be one primary database in an Oracle 11g Data Guard configuration, theres no possibility conflict between the scheduled tasks.
Logical Standby Database Metadata
Finally, Oracle 11g includes several dynamic views (see Table 8.3) and data dictionary views (see Table 8.4) that provide status information and performance statistics for logical standby databases. Ive provided several useful queries against these views formatted for SQL*Plus in script MonitorLSBY.sql.
In the final article in this series, Ill put everything Ive discussed about Oracle 11g Data Guard into practice by demonstrating how to configure a primary database and a physical standby database within an Oracle 11g Real Application Clusters environment.
References and Additional Reading
While Im hopeful that Ive given you a thorough grounding in the technical aspects of the features Ive discussed in this article, Im also sure that there may be better documentation available since its been published. I therefore strongly suggest that you take a close look at the corresponding Oracle documentation on these features to obtain crystal-clear understanding before attempting to implement them in a production environment. Please note that Ive drawn upon the following Oracle Database 11g documentation for the deeper technical details of this article:
B28279-02 Oracle Database 11g New Features Guide
B28294-03 Oracle Database 11g Data Guard Concepts and Administration
B28295-03 Oracle Database 11g Data Guard Broker
B28320-01 Oracle Database 11g Reference Guide
B28419-02 Oracle Database 11g PL/SQL Packages and Types Reference
Also, the following MetaLink documentation helps clarify this feature set:
728053.1 Configure RMAN to Purge Archivelogs After Applied On Standby