DBA Tips Archive for Oracle
No Title[an error occurred while processing this directive]
by Michael New, MichaelNew@earthlink.net, Gradation LLC
Oracle Data Guard (known as Oracle Standby Database prior to Oracle9i), forms an extension to the Oracle RDBMS and provides organizations with high availability, data protection, and disaster recovery for enterprise databases. Oracle Data Guard provides the DBA with services for creating, maintaining, managing, and monitoring one or more standby databases. The functionality included with Oracle Data Guard enables enterprise data systems to survive both data corruption as well as major disasters.
This article provides instructions for creating and configuring a logical standby database from a primary database using Oracle Database 10g Release 2 (10.2) operating in maximum performance protection mode. It should be noted that several different methods exist to create a logical standby database configuration and that this is just one of those ways. The methods outlined in this guide present a simple approach that should be easy to implement in most situations. In fact, if you break down the essential tasks required to build a logical standby database, you will see that it is essentially nothing more than creating an initial physical standby database, building a dictionary in the redo data for LogMiner, verifying supplemental logging is enabled on both the primary and standby, converting the physical standby database to a logical standby database, putting the logical standby database in managed recovery mode (SQL Apply), and starting remote archiving from the primary database (Redo Transport). Obviously there are a number of smaller steps I am leaving out which will all be discussed in more depth throughout this guide.
All configuration parameters related to the Oracle instance and networking will be discussed as well as how to place the standby database in Managed Recovery Mode.
The standby database feature of Oracle was first introduced with the release of Oracle 7 in the early 1990's. The design was fairly simple. Oracle used media recovery to apply archive logs to a remote standby database, however, none of the automation we now take for granted was present in this release of the product. DBA's were required to write custom scripts that shipped and applied archive logs to the remote standby database. It wasn't until Oracle8i where some form of automation was introduced that relied on Oracle Net Services to transfer and apply archive redo logs. DBA's were still required to supply scripts that handled gap resolution and resynchronize the primary and standby database when they lost connectivity with one another. Also included in Oracle8i was a set of pre-written scripts that simplified the switchover and failover process.
With the introduction of Oracle9i, the standby database feature was renamed to Oracle Data Guard. In addition to the re-branding of the product, Oracle delivered a comprehensive automated solution for disaster recovery that was fully integrated with the database kernel. Finally, a fully integrated disaster recovery solution without the need to maintain custom written scripts! Oracle9i also provided a vast array of new features which included automatic gap resolution, enhanced redo transport methods (synchronous and asynchronous redo transport), the ability to configure zero data loss, and the concept of protection modes.
Until Oracle9i Release 2, the only standby database type available was the physical standby database. A physical standby database is an identical, block-for-block copy of the primary database and is kept in sync with the primary using media recovery (also referred to as Redo Apply). Oracle introduced a new type of standby database with Oracle9i Release 2 named Logical Standby Database. This new type of standby database keeps in sync with the primary database using SQL Apply (versus Redo Apply used with a physical standby database). A logical standby database remains open for user access while logical records are being received and applied from the primary database which makes this a great candidate for a reporting database.
When the standby database site is hosted in a different geographical location than the primary site, it provides for an excellent High Availability (HA) solution. When creating a standby database configuration, the DBA should always attempt to keep the primary and standby database sites identical as well as keeping the physical location of the production database transparent to the end user. This allows for an easy role transition scenario for both planned and unplanned outages. When the secondary (standby) site is identical to the primary site, it allows predictable performance and response time after failing over (or switching over) from the primary site.
Oracle Data Guard is only available as a bundled feature included within its Enterprise Edition release of the Oracle Database software. It is not available with Oracle Database Standard Edition. With the exception of performing a rolling database upgrade using logical standby database, it is mandatory that the same release of Oracle Database Enterprise Edition be installed on the primary database and all standby databases!
While it remains possible to simulate a standby database environment running Oracle Database Standard Edition, it requires the DBA to develop custom scripts that manually transfer archived redo log files and then manually applying them to the standby database. This is similar to the methods used to maintain a standby database with Oracle 7. The consequence with this type of configuration is that it does not provide the ease-of-use, manageability, performance, and disaster-recovery capabilities available with Data Guard.
There are two types of standby databases that can be created with Oracle Data Guard physical or logical. Deciding which of the two types of standby databases to create is critical and depends on the nature of the business needs the organization is trying to satisfy.
A physical standby database is an identical, block-for-block copy of the primary database and is kept in sync with the primary using media recovery. As redo gets generated on the primary database, it gets transferred to the standby database where an RFS process receives the primary redo and applies the change vectors directly to the standby database. A physical standby database is an excellent choice for disaster recovery.
A logical standby database works in a different manner which keeps in sync with the primary by transforming redo data received from the primary database into logical SQL statements and then executes those SQL statements against the standby database. With a logical standby database, the standby remains open for user access in read/write mode while still receiving and applying logical records from the primary. While a physical standby database is an exact physical replica of the primary, a logical standby database is not. Because Oracle is applying SQL statements to the standby database and not performing media recovery (as is done with a physical standby database), it is possible for the logical standby database to contain the same logical data, but at the same time have a different physical structure. A logical standby database is an excellent solution for a reporting database while at the same time retaining the attributes of a disaster recovery solution. Not only does a logical standby database contain the same logical information as the primary, it can also support the creation of additional objects to support improved reporting requirements.
After deciding between a physical or logical standby database, the next major decision is which data protection mode should be used to operate the Data Guard configuration. At the heart of this decision lies the answer to one important question how much data loss is your organization willing to endure in the event of a failover? The obvious answer to expect from management is none. Configuring Data Guard with guaranteed no data loss, however, requires a significant investment in equipment and other resources necessary to provide support for this type of environment.
An Oracle Database 10g Data Guard configuration will always run in one of three data protection modes:
Each of the three modes provide a high degree of data protection; however they differ with regards to data availability and performance of the primary database.
When selecting a protection mode, always consider the one that best meets the needs of your business. Carefully take into account the need to protect the data against any loss vs. availability and performance expectations of the primary database. An in-depth discussion on the three available data protection modes and how redo transport works to support them is beyond the scope of this guide. To keep the article simple, I will be using the default protection mode of Maximum Performance.
Logical standby database, first released with Oracle Database in Release 9.2, has been enhanced in Oracle Database 10g Release 1 to allow rolling upgrades, improve the overall ease-of-use and manageability, expand the disaster recovery capabilities, and simplify the steps to create a logical standby database. The following list summarizes the new features for logical standby databases in Oracle Database 10g (10.1):
Zero Downtime Instantiation
In versions prior to Oracle Database 10g, creating a logical standby database required either a cold backup of the primary database or a period of time in which the primary was quiesce restricted. With today's critical application environments and their demand for constant and consistent uptime of the database, this restriction presented a challenge. Starting with Oracle Database 10g Release 1, it is now possible to create a logical standby database without having to shut down or quiesce the primary database. This is achieved by using an online backup of the primary database and creating a logical standby control file.
Rolling Database Upgrades with SQL Apply
In a future patchset release of Oracle Database 10g, it will be possible to do a rolling upgrade using logical standby databases. The foundation for rolling upgrades is now implemented into the SQL Apply technology so that the primary database incurs minimal downtime when you upgrade Oracle Database software on each database in the Data Guard configuration. For example, using SQL Apply and logical standby databases, you will be able to upgrade the Oracle Database software from patchset release 10.1.0.n to the next database 10.1.0.(n+1) patch set release, or migrate Oracle Database 10g Release 1 (10.1) to Oracle Database 11g Release 2 (11.2).
Support for Maximum Protection Mode
With the introduction of support for standby redo log files, it is now possible to have a logical standby database be part of a Data Guard configuration running in maximum protection mode.
Support for Additional Data Types
Logical standby databases now include support for LONG, LONG RAW, and NCLOB data types. Also, support for index organized tables was added provided the index organized table does not contain either an overflow segment or any LOB column.
Let's take a look at the Oracle configuration and a summary of the parameters that will be used to create the logical standby database configuration described in this guide.
|Oracle Release||Oracle 10g Release 2 (10.2.0.5)|
|Host Name||vmlinux1.idevelopment.info (192.168.1.160)|
|Operating System||Red Hat Linux 5 (CentOS 5.5)|
|Database Name - (db_name)||modesto|
|Database Domain - (db_domain)||idevelopment.info|
|Database Unique Name - (db_unique_name)||modesto|
|Service Names||modesto.idevelopment.info, modesto|
|Database Files - (db_create_file_dest)||/u02/oradata|
|Flash Recovery Area - (db_recovery_file_dest)||/u03/flash_recovery_area|
|Local Online Redo Log Files - (log_archive_dest_1)||location=use_db_recovery_file_dest (online_logfiles,all_roles)|
|Remote Archive Destination - (log_archive_dest_2)||service=turlock (online_logfiles,primary_role)|
|Redo Log Files Received From Primary - (log_archive_dest_3)||location=/u04/oracle/oraarch/MODESTO (standby_logfiles,standby_role)|
|Oracle Release||Oracle 10g Release 2 (10.2.0.5)|
|Host Name||vmlinux2.idevelopment.info (192.168.1.162)|
|Operating System||Red Hat Linux 5 (CentOS 5.5)|
|Database Name - (db_name)||turlock|
|Database Domain - (db_domain)||idevelopment.info|
|Database Unique Name - (db_unique_name)||turlock|
|Service Names||turlock.idevelopment.info, turlock|
|Database Files - (db_create_file_dest)||/u02/oradata|
|Flash Recovery Area - (db_recovery_file_dest)||/u03/flash_recovery_area|
|Local Online Redo Log Files - (log_archive_dest_1)||location=use_db_recovery_file_dest (online_logfiles,all_roles)|
|Remote Archive Destination - (log_archive_dest_2)||service=modesto (online_logfiles,primary_role)|
|Redo Log Files Received From Primary - (log_archive_dest_3)||location=/u04/oracle/oraarch/TURLOCK (standby_logfiles,standby_role)|
It is assumed that Oracle Database 10g Release 2 and all patchsets have been installed on both nodes in the Oracle Data Guard configuration. Click here for a guide on installing Oracle Database 10g R2 on the Red Hat Linux 5 platform.
Before implementing a logical standby database, you should first examine your application to determine if logical standby has support to maintain all of the data types and tables. When a change is performed on the primary database to any objects that are not supported, those changes will not be propagated to the logical standby. When Log Apply services encounters unsupported data types, tables, sequences, or views sent from the the primary database, they will be automatically skipped by SQL Apply on the logical standby database. Moreover, no error message will be returned to the user. This is why it is important to identify unsupported database objects in your application on the primary database before deciding to implement logical standby database.
Some schemas that ship with the Oracle database are automatically skipped by SQL Apply. To determine exactly which schemas will be skipped, query the DBA_LOGSTDBY_SKIP view.
When setting up a logical standby database, you must ensure the logical standby database can maintain the data types and tables in your primary database. Oracle Database 10g provides a simple method to determine exactly which database objects are supported and which are not supported. Run the following query on the primary database to list all tables that contain unsupported data types:
Any tables returned from the above query are considered unsupported by logical standby database for one of several reasons which include unsupported data types, use of compression, or belonging to a default Oracle schema that is not maintained. To determine which column/data type is unsupported, the above query can be modified to include the column information for a specified table along with why it is unsupported. The ATTRIBUTES column of DBA_LOGSTDBY_UNSUPPORTED displays the reason why the table is not supported by SQL Apply. If the structure of the table is unsupported (not just the data type of some columns), this view displays all columns in the table, with one of the following values displayed for each column in the table:
IOT with Overflow IOT with LOB Mapping table for physical rowid of IOT IOT with row movement Table Compression Object Table Table with encrypted column
If the structure of the table is supported but certain columns in the table have unsupported data types, the ATTRIBUTE column will be NULL.
The following lists the various database objects, storage types, and PL/SQL supplied packages that are supported and unsupported by logical standby databases with Oracle Database 10g Release 2.
Supported Data Types
BINARY_DOUBLE BINARY_FLOAT BLOB CHAR CLOB and NCLOB DATE INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND LONG LONG RAW NCHAR NUMBER NVARCHAR2 RAW TIMESTAMP TIMESTAMP WITH LOCAL TIMEZONE TIMESTAMP WITH TIMEZONE VARCHAR2 and VARCHAR
Unsupported Data Types
BFILE Collections (including VARRAYS and nested tables) Encrypted columns Multimedia data types (including Spatial, Image, and Context) ROWID, UROWID User-defined types XMLType
Supported Storage Types
Cluster tables (including index clusters and heap clusters)
Index-organized tables (partitioned and non-partitioned, including overflow segments)
Heap-organized tables (partitioned and non-partitioned
Unsupported Storage Type
Logical standby databases do not support the segment compression storage type.
Supported PL/SQL Supplied Packages
Oracle PL/SQL supplied packages that do not modify system metadata or user data leave no footprint in the archived redo log files, and hence are safe to use on the primary database. Examples of such packages are DBMS_OUTPUT, DBMS_RANDOM, DBMS_PIPE, DBMS_DESCRIBE, DBMS_OBFUSCATION_TOOLKIT, DBMS_TRACE, and DBMS_METADATA.
Oracle PL/SQL supplied packages that do not modify system metadata but may modify user data are supported by SQL Apply, as long as the modified data belongs to the list supported data types. Examples of such packages are DBMS_LOB, DBMS_SQL, and DBMS_TRANSACTION.
Unsupported PL/SQL Supplied Packages
Oracle PL/SQL supplied packages that modify system metadata typically are not supported by SQL Apply, and therefore their effects are not visible on the logical standby database. Examples of such packages are DBMS_JAVA, DBMS_REGISTRY, DBMS_ALERT, DBMS_SPACE_ADMIN, DBMS_REFRESH, DBMS_REDEFINITION, DBMS_SCHEDULER, and DBMS_AQ.
Specific support for DBMS_JOB has been provided. Job execution is suspended on a logical standby database and jobs cannot be scheduled directly on the standby database. However, jobs submitted on the primary database are replicated in the standby database. In the event of a switchover or failover, jobs scheduled on the original primary database will automatically begin running on the new primary database.
By default, the following SQL statements are automatically skipped by SQL Apply:
All other SQL statements executed on the primary database are applied to the logical standby database.
When a row gets updated on the primary database, a redo record is created that can uniquely identify the exact row that was changed. If a table does not contain a primary key or non-null unique key index, the ROWID can be used to locate the row. Using a ROWID is an extremely fast and efficient way to uniquely locate a row on the primary database, however, it does not work in the case of a logical standby database. Even though the logical standby database is created from a backup copy of the primary database, the physical organization in a logical standby database is different from that of the primary database. Thus, ROWIDs contained in the redo records generated by the primary database cannot be used to identify the corresponding row in the logical standby database. To resolve this issue and to improve overall performance, add a primary key or non-null unique key index to as many of your application tables as possible. Doing so will allow SQL Apply to efficiently apply updates to those tables on the logical standby database.
Oracle uses primary-key or unique-constraint/index supplemental logging to logically identify a modified row in the logical standby database. When database-wide primary-key and unique-constraint/index supplemental logging is enabled, each UPDATE statement also writes the column values necessary in the redo log to uniquely identify the modified row in the logical standby database.
If a table has a primary key defined, then the primary key is logged along with the modified columns as part of the UPDATE statement to identify the modified row.
In the absence of a primary key, the shortest non-null unique-constraint/index is logged along with the modified columns as part of the UPDATE statement to identify the modified row.
In the absence of both a primary key and a non-null unique constraint/index, all columns of bounded size are logged as part of the UPDATE statement to identify the modified row. In other words, all columns except those with the following types are logged: LONG, LOB, LONG RAW, object type, and collections.
Oracle recommends that you add a primary key or a non-null unique index to tables in the primary database, whenever possible, to ensure that SQL Apply can efficiently apply redo data updates to the logical standby database.
Perform the following steps to ensure SQL Apply can uniquely identify rows of each table being replicated in the logical standby database.
Query the DBA_LOGSTDBY_NOT_UNIQUE view to display a list of tables that SQL Apply may not be able to uniquely identify. For example:
Note that tables returned in the above query can still be supported by SQL Apply because supplemental logging (when enabled) will place additional information in the redo stream that will be used by SQL Apply to assist in uniquely identifying which rows to modify on the logical standby. The supplemental logging information ensures each update contains enough information to logically identify each row that is modified by the statement.
If your application ensures the rows in a table are unique, you can create a disabled primary key RELY constraint on the table. This avoids the overhead of maintaining a primary key on the primary database.
To create a disabled RELY constraint on a primary database table, use the ALTER TABLE statement with a RELY DISABLE clause. The following example creates a disabled RELY constraint on a table named MYTAB, for which rows can be uniquely identified using the ID and NAME columns:
When you specify the RELY constraint, the system will assume that rows are unique. Because you are telling the system to rely on the information, but are not validating it on every modification done to the table, you must be careful to select columns for the disabled RELY constraint that will uniquely identify each row in the table. If such uniqueness is not present, then SQL Apply will not correctly maintain the table.
To improve the performance of SQL Apply, add a unique-constraint/index to the columns to identify the row on the logical standby database. Failure to do so results in full table scans during UPDATE or DELETE statements carried out on the table by SQL Apply.
After verifying that the logical standby database can be used to support your given application, use the steps outlined in this section to create the logical standby.
The first step in creating a logical standby using Oracle Database 10g or higher, is to create an initial physical standby database. This physical database will be converted to a logical standby using the steps outlined in this section. Once the physical standby is created, start the Redo Apply process and allow managed recovery (Redo Apply) to bring the standby database up to a consistent state with the primary database.
The following article can be used to create the initial physical standby database and enable managed recovery mode:
The remaining steps in this section will document how to convert the new physical standby database to a logical standby database.
It is possible to have Redo Apply running on the new physical standby database for any length of time before converting it to a logical standby database. However, before converting to a logical standby database, stop Redo Apply on the physical standby database. Stopping Redo Apply is necessary to avoid applying changes past the redo that contains the LogMiner dictionary (described in the next section).
To stop Redo Apply, issue the following statement on the physical standby database. If the database is a RAC database comprised of multiple instances, then you must first stop all RAC instances except one before issuing this statement:
A LogMiner dictionary must be built into the redo data so that the LogMiner component of SQL Apply can properly interpret changes it sees in the redo.
To build the LogMiner dictionary, issue the following statement from the primary database:
The DBMS_LOGSTDBY.BUILD procedure waits for all existing transactions to complete. Long-running transactions executed on the primary database will affect the timeliness of this command.
The DBMS_LOGSTDBY.BUILD procedure uses Flashback Query to obtain a consistent snapshot of the data dictionary that is then logged in the redo stream. Oracle recommends setting the UNDO_RETENTION initialization parameter to 3600 on both the primary and logical standby databases.
As part of building LogMiner Multiversioned Data Dictionary, supplemental logging is automatically set up to log primary key and unique-constraint/index columns (see next section).
Supplemental logging must be enabled on both the primary and standby databases in order for a logical standby to function and support role transitions. When building the LogMiner Multiversioned Data Dictionary in the previous section, supplemental logging was automatically set up to log primary key and unique-constraint/index columns on the primary database. In order to support role transition, supplemental logging should be enabled on both the primary and new physical standby database.
When supplemental logging is enabled, additional information is is placed in the redo stream that will be used by SQL Apply to assist in uniquely identifying which rows to modify on the logical standby. The supplemental logging information ensures each update contains enough information to logically identify each row that is modified by the statement.
Run the following query on both the primary and standby database to determine if supplemental logging is enabled:
Supplemental logging should already be enabled on the primary database as a result of building the LogMiner Multiversioned Data Dictionary in the previous section.
In order to support role transition, supplemental logging should be enabled on the new physical standby database.
If either column in the above query report NO, supplemental logging must be enabled by issuing the following statement on both the primary and standby database:
After executing the statement above, re-query V$DATABASE to ensure that SUPPLEMENTAL_LOG_DATA_PK and SUPPLEMENTAL_LOG_DATA_UI report a value of YES.
The following sections describe how to prepare the physical standby database to transition to a logical standby database.
At this point, the redo logs contain all of the information necessary to convert the physical standby database to a logical standby database. To continue applying redo data to the physical standby database until it is ready to convert to a logical standby database, issue the following SQL statement on the standby:
For db_name, specify a database name to uniquely identify the new logical standby database. For the purpose of this guide, I will use the obvious choice of naming the database the same as the instance name (turlock). For example:
The SQL statement above runs the nid command-line utility underneath the scenes to change the name of the logical standby database to a unique name (DB_NAME). If you are using a server parameter file (spfile) at the time you issue this statement, then the database will update the file with appropriate information about the new logical standby database. If you are not using an spfile, then the database issues a message reminding you to set the name of the DB_NAME parameter after shutting down the database. The following is an excerpt from the alert.log file of the new logical standby database after running the ALTER DATABASE that displays tracing information from the nid command:
The ALTER DATABASE statement above waits, applying redo data to the standby until the LogMiner dictionary is found in the log files. This may take several minutes, depending on how long it takes redo generated in the previous section to be transmitted to the standby database, and how much redo data needs to be applied. I have always been uncertain why the Oracle documentation has us cancel managed recovery before building the LogMiner dictionary, but I've never had the ALTER DATABASE statement hang while waiting for the redo log containing the LogMiner dictionary to arrive at the standby so I don't give it much thought anymore. If the ALTER DATABASE statement does hang while waiting for the archived redo log containing the LogMiner dictionary, you may have to manually register the missing logs to the standby using:
Continue registering any missing logs to the standby until you receive the error "ORA-01507: database not mounted". This indicates that the standby has received and applied the LogMiner dictionary and the ALTER DATABASE statement that was waiting, should now be complete.
If a dictionary build was not successfully performed on the primary database, this command will never complete. You can cancel the SQL statement by issuing the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL statement from another SQL session.
Because the conversion process changes the database name for the logical standby database (that was originally set with the DB_NAME initialization parameter), you must re-create the password file.
During the creation of the physical standby database, several initialization parameters were configured on both the primary and standby database for their role in the Data Guard configuration. The Data Guard configuration described in this guide will not be maintaining support for an existing physical standby database. Only the new logical standby database will be maintained and therefore will only require minimal changes to the initialization parameters on the primary and standby. In fact, the only initialization parameters that you need to be concerned about are the log archive destination and DB_FILE_NAME_CONVERT parameters.
Log Archive Destination Parameters
You may need to modify the LOG_ARCHIVE_DEST_n parameters because, unlike physical standby databases, logical standby databases are open databases that generate redo data and have multiple log files (online redo log files, archived redo log files, and standby redo log files). It is good practice to specify separate local destinations for:
Archived redo log files that store redo data generated by the logical standby database. In this example, this is configured as the LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' destination (Flash Recovery Area).
Archived redo log files that store redo data received from the primary database. In this example, this is configured as the LOG_ARCHIVE_DEST_3='LOCATION=/u04/oracle/oraarch/TURLOCK' destination.
Modify the log archive destination initialization parameters for the logical standby database as follows:
The following table describes the archival processing defined by the initialization parameters shown in this example for the new turlock logical standby database.
|When the Turlock Database Is Running in the
|When the Turlock Database Is Running in the
Logical Standby Role
|LOG_ARCHIVE_DEST_1||Directs archival of redo data generated by the primary database from the local online redo log files to the local archived redo log files in the Flash Recovery Area (USE_DB_RECOVERY_FILE_DEST).||Directs archival of redo data generated by the logical standby database from the local online redo log files to the local archived redo log files in the Flash Recovery Area (USE_DB_RECOVERY_FILE_DEST).|
|LOG_ARCHIVE_DEST_2||Directs transmission of redo data to the remote logical standby database modesto.||Is ignored; LOG_ARCHIVE_DEST_2 is valid only when turlock is running in the primary role.|
|LOG_ARCHIVE_DEST_3||Is ignored; LOG_ARCHIVE_DEST_3 is valid only when turlock is running in the standby role.||Directs archival of redo data received from the primary database to the local archived redo log files in /u04/oracle/oraarch/TURLOCK.|
To support role transition, modify the log archive destination initialization parameters for the primary database as well:
The DB_FILE_NAME_CONVERT Initialization Parameter
The DB_FILE_NAME_CONVERT initialization parameter is not honored once a physical standby database is converted to a logical standby database. This can be a problem, for example, when adding a non-OMF datafile to the primary database and the datafile paths are different between the primary and standby.
If necessary, you should register a skip handler and provide SQL Apply with a replacement DDL string to execute by converting the path names of the primary database datafiles to the standby datafile path names.
See the Managing a Logical Standby Database section for more information on how to set up a skip handler for DDL statements.
On the logical standby database, shutdown the instance and issue the STARTUP MOUNT statement to start and mount the database. Do not open the database; it should remain closed to user access until later in the creation process.
The new standby database is logically the same as the primary database, but it is transactionally inconsistent with the primary database, and thus incompatible for recovery operations.
To open the new logical standby database, you must open it with the RESETLOGS option by issuing the following statement:
Because this is the first time the database is being opened, the database's global name is adjusted automatically to match the new DB_NAME initialization parameter.
Issue the following statement to start real-time apply on the logical standby database to immediately apply redo data from the standby redo log files to the logical standby database:
After starting SQL Apply for the first time, certain actions are performed to prepare the LogMiner dictionary that will be used for all future operations. The time it takes for this one-time operation to complete will vary depending on the size of the data dictionary and the capacity of the standby machine.
With the SQL Apply engine started on the logical standby database, the next and final phase is to start shipping redo data to the standby from the primary.
If the remote archive destination on the primary database, (the destination defined that will transfer redo data to the standby database), is set to DEFER, enable SQL Transport Services by issuing the following statement
With the protection mode used in this guide (maximum performance), archiving of redo data to the remote standby does not occur until after a log switch. By default, a log switch occurs when an online redo log becomes full which means the standby database does not get updated until then.
To force the current redo logs to be archived immediately, use the following statement on the primary database:
At this point, the standby database will continue to apply changes from archive redo logs being transferred from the primary.
With the standby and primary databases now in operation, the next step is to verify the Data Guard configuration. This will ensure that Redo Transport on the primary and SQL Apply on the logical standby are working correctly.
Given this Data Guard configuration is running in maximum performance mode, the validation tasks will involve switching redo log files from the primary and verifying those log files are being shipped and applied to the physical standby database.
From the primary database, perform a log switch and then verify the transmissions of the archive redo log file was successful:
If the transmission was successful, the status of the destination will be VALID as shown above. If for any reason the transmission was unsuccessful, the status will be INVALID and the full text of the error message will be populated in the ERROR column which can be used to investigate and correct the issue.
To verify SQL Apply, identify the current registered log for the logical standby database, archive a log or two from the primary, and then check the standby database again. This test will ensure that redo data was shipped from the primary and then successfully received, archived, and applied to the standby.
First, identify the current registered log for the logical standby database:
From the primary database, archive the current log using the following SQL statement:
Go back to the standby database and re-query the DBA_LOGSTDBY_LOG view to verify redo data was shipped, received, and applied:
Another good method used to determine the progress of SQL Apply is to query the DBA_LOGSTDBY_PROGRESS view.
The APPLIED_SCN column lists the highest SCN that has been applied to the logical standby database while the NEWEST_SCN column lists the highest SCN that has been received from the primary. When these two SCN values are equal, we know that all available redo that has arrived from the primary has been applied to the standby. If the APPLIED_SCN is below the NEWEST_SCN and is increasing, SQL Apply is currently applying changes. The READ_SCN column (not shown above), reports the SCN that the SQL Apply engine will start at, should it be restarted.
Querying the DBA_LOGSTDBY_LOG view from the standby database is a good way to ensure Redo Transport and SQL Apply is doing their job correctly. In addition, I also like to tail the alert.log file of the standby database as a double check.
From the standby database, perform a tail -f against the alert.log while issuing the "alter system archive log current" statement from the primary:
To view the SQL Apply engine on the logical standby and the status of each process associated with SQL Apply, use the V$LOGSTDBY view. For example, the following query will show the name of the SQL Apply process, the SCN it is currently working on, and its current status:
It is useful to track the value of the HIGH_SCN column of V$LOGSTDBY view. As long as this value is increasing on subsequent queries of the view, it is an indicator that progress is being made and that changes are being applied by the SQL Apply engine.
When any of the SQL Apply processes (above) show a status of no work available, it is considered to be idle and waiting on information from the primary database.
Another useful resource for gathering information about the SQL Apply engine is the V$LOGSTDBY_STATS view, which provides state and status information. Oracle Data Guard provides the DBMS_LOGSTDBY.APPLY_SET procedure which allows the DBA to set the values of various parameters that configure and maintain SQL Apply. All of these options and their values can be gathered from the V$LOGSTDBY_STATS view (both default and set values).
You can also use the V$LOGSTDBY_STATS view to count the number of transactions applied or transactions read by SQL Apply to determine if transactions are being applied as fast as they are being read.
At this point, the logical standby database is running and can provide the maximum performance level of data protection (the default). The following list describes additional preparations you can take on the logical standby database:
Upgrade the data protection mode
The Data Guard configuration is initially set up in the maximum performance mode (the default). See "Setting the Data Protection Mode of a Data Guard Configuration" for information about the data protection modes and how to upgrade or downgrade the current protection mode.
Enable Flashback Database
Flashback Database removes the need to re-create the primary database after a failover. Flashback Database enables you to return a database to its state at a time in the recent past much faster than traditional point-in-time recovery, because it does not require restoring datafiles from backup nor the extensive application of redo data. You can enable Flashback Database on the primary database, the standby database, or both. See "Using Flashback Database After a Failover" and "Using Flashback Database After Issuing an Open Resetlogs Statement" for scenarios showing how to use Flashback Database in a Data Guard environment. Also, see Oracle Database Backup and Recovery Advanced User's Guide for more information about Flashback Database.
Remove Fetch Archive Log (FAL) Parameters
As of Oracle Database 9i Release 2 (9.2.0), Fetch Archive Log (FAL) Gap Resolution only works with physical standby databases as the process is tied to the MRP process. Gap resolution on a logical standby database is handled exclusively through the heartbeat mechanism found in Oracle's Automatic Gap Resolution. As a result, the FAL_SERVER and FAL_CLIENT initialization parameters are not honored once a physical standby database is converted to a logical standby database and can be safely removed.
If the Oracle Data Guard configuration will only contain the one primary database and the one logical standby database, it is safe to remove the FAL initializations parameters from both the primary and the logical. If the primary database will be supporting a physical standby configured using the manual FAL gap resolution method, only remove the FAL initialization parameters from the logical standby.
As you have seen in this article, the Log Transport service or the method of delivering change data to a physical and logical standby database are pretty much the same. The same, however, cannot be said about the method used to apply changes to the standby database as they are very different from one another. A logical standby applies changes by reading redo from either archived redo logs or standby redo logs and converting this redo into SQL statements. These SQL statements are then applied to the database tables on the standby database while it is open in read/write mode. This section examines the six key processes that make up the SQL Apply engine.
SQL Apply uses a collection of parallel execution servers and background processes to apply changes from the primary database to the logical standby database. The purpose of these background processes are to redo the redo, transform the redo, construct transactions, and apply transactions to the database tables.
Figure 1: Overview of the SQL Apply Architecture
The different processes involved and their functions during log mining and apply processing are as follows.
During Log Mining:
The READER process reads redo records from the archived redo log files or standby redo log files.
The PREPARER process converts block changes contained in redo records into logical change records (LCRs). Multiple PREPARER processes can be active for a given archived redo log file or standby redo log file. The LCRs are staged in the shared pool of the system global area (SGA), known as the LCR cache.
The BUILDER process groups LCRs into transactions, and performs other tasks, such as memory management in the LCR cache, check-pointing related to SQL Apply restart and filtering out of uninteresting changes.
During Apply Processing:
The ANALYZER process examines the transaction chunks containing a group of LCRs, possibly filtering out uninteresting transactions, and identifying dependencies between different transactions.
The COORDINATOR process (LSP):
Monitors dependencies between transactions and coordinates scheduling
Authorizes the commitment of changes to the logical standby database
The APPLIER processes:
Applies the LCRs to the database
Asks the COORDINATOR process to approve transactions with unresolved dependencies
Commits the transactions
You can query the V$LOGSTDBY_PROCESS view to examine the activity of the SQL Apply processes. Another view that provides information about current activity is the V$LOGSTDBY_STATS view that displays statistics, current state, and status information for the logical standby database during SQL Apply activities.
The first step in starting SQL Apply is to bring the logical standby database to an open state. After the standby database is open, issue the following statement as SYSDBA to begin applying redo data to the logical standby database:
After running the above statement, Oracle will spawn all six SQL Apply processes and will read redo data from the archived redo logs as they are registered with the logical standby database.
By default, Data Guard waits for the full archived redo log file to arrive on the standby database before applying it to the standby database. However, if you have configured standby redo log files on the standby database, you can optionally enable real-time apply. With real-time apply enabled, SQL Apply applies redo data from standby redo log files at the same time the log files are being written to, as opposed to applying from an archived redo log file after a log switch occurs. Immediately applying standby redo log files in this manner keeps the logical standby database closely caught up with the primary database, without requiring the standby redo log files to be archived at the standby site. This can result in quicker switchovers and failovers.
To start real-time apply on the logical standby database, include the IMMEDIATE keyword as shown in the following statement:
Before shutting down the logical standby database or before attempting to change attributes of the SQL Apply engine, you will need to first stop logical apply. Use the following statement to stop SQL Apply on the logical standby database:
By default, SQL Apply automatically deletes archived redo log files from the file system and the data dictionary of the logical standby when they are no longer needed.
This behavior can be overridden by executing the following PL/SQL procedure on the logical standby:
Although SQL Apply automatically deletes archived redo log files when they are no longer needed on the logical standby database, there may be times when you want to manually remove them (for example, to reclaim disk space).
If you are overriding the default automatic log deletion capability, perform the following steps to identify and delete archived redo log files that are no longer needed by SQL Apply:
To purge the logical standby session of metadata that is no longer needed, enter the following PL/SQL statement:
This statement also updates the DBA_LOGMNR_PURGED_LOG view that displays the archived redo log files that are no longer needed.
Query the DBA_LOGMNR_PURGED_LOG view to list the archived redo log files that can be removed:
Use an operating system-specific command to delete the archived redo log files listed by the query above.
A common question after building a logical standby database is, "Now that the logical standby database is open for read / write user access and tables are being replicated from the primary, what is preventing users from modifying those replicated tables?" When working with a physical standby database, we never had to worry about this type of scenario. For example, if a user mistakenly issues a DML or DDL statement, it will fail because the physical standby database is either mounted or in read-only mode. A logical standby database, however, is open for read / write access. To address the concerns of protecting replicated tables in a logical standby database, Oracle Data Guard provides the GUARD feature.
A database GUARD can be configured with one of the following three values:
This the most restrictive GUARD value for a database and the default value for a logical standby database. It prevents all non-sys users from modifying tables being maintained by SQL Apply and prevents non-sys users from creating new objects on the logical standby.
This GUARD value is only valid for a logical standby database. It prevents all non-sys users from modifying tables being maintained by SQL Apply but allows users to create and modify new objects.
The database GUARD is disabled and all modifications are allowed. This is the default for a primary database.
Use the ALTER DATABASE statement to set the GUARD for a database.
For example, to set the GUARD to STANDBY on a logical standby database, use:
By default, the GUARD is set to NONE for a primary database which means users and applications are free to modify any tables for which they have the privileges necessary to perform modifications.
By default, the GUARD is set to ALL for a logical standby database which is the most stringent setting as it prevents user modifications to all tables in a database, replicated by SQL Apply or not.
Note that the NONE and ALL settings are available to all databases (primary or standby) while the STANDBY setting is only meaningful on a logical standby database.
When configuring the local archive log destination for a primary or physical standby database, I use the Flash Recovery Area (FRA). Doing the same for a logical standby database proved to be a little more challenging.
When attempting to configure the logical standby database in this guide to use the FRA for its local archive log destination, I explicitly set the destination for the FRA by using the USE_DB_RECOVERY_FILE_DEST parameter as follows:
The logical standby database, however, didn't like that:
To configure the local archive destination to use the FRA, I found that you must explicitly define the destination parameter to include the VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) option.
The DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT initialization parameters are not honored once a physical standby database is converted to a logical standby database. This can be a problem, for example, when adding a datafile to the primary database and the datafile paths are different between the primary and standby. This section describes the steps necessary to register a skip handler and provide SQL Apply with a replacement DDL string to execute by converting the path names of the primary database datafiles to the standby datafile path names.
This may or may not be a problem for everyone. For example, if you are using Oracle Managed Files (OMF), SQL Apply will successfully execute DDL statements generated from the primary to CREATE and ALTER tablespaces and their associated system generated path name on the logical standby.
If on the other hand, you attempt to specify a physical path name in the CREATE/ALTER tablespace statement that does not exist on the logical standby, SQL Apply will not succeed in processing the statement and will fail. Whenever SQL Apply encounters an error while applying a SQL statement, it will stop and provide the DBA with an opportunity to correct the statement and restart SQL Apply.
As stated at the beginning of this section, it is possible to avoid errors of this nature on the logical standby database by registering a skip handler and provide SQL Apply with a replacement DDL string to execute by converting the path names of the primary database datafiles to the standby datafile path names. The steps to perform this are presented below. The actions below should be run on the logical standby database.
First, create the PL/SQL 'skip procedure' to handle tablespace DDL transactions.
Stop SQL Apply.
Register the skip procedure with SQL Apply.
Start SQL Apply.
Perform a test.
The previous section provided an example of how to recover from a failed DDL transaction.
When SQL Apply receives an error during a DDL transactions, it is often safe for the user to manually issue a compensating DDL transaction, skip the failed transaction, and restart SQL Apply. A failed DML transaction, however, can become problematic and should be carefully reviewed before simply trying to skip it. For example, suppose you have a transaction that consists of 100 inserts and 2 updates. If one of the inserts fail and the user decides to simply skip that single insert, then the entire transaction will be considered logically corrupt, creating data integrity issues on the standby database.
In most cases, a failed DML transaction is the result of a table associated with that transaction being out of synch with its corresponding table on the primary. The following is a list of common reasons why a table on the logical standby may become out of synch with the primary:
Having the SQL Apply engine skip transactions without properly issuing a compensating transaction.
Providing users improper access to objects being maintained by SQL Apply.
Performing unsupported operations on the primary.
Modifying user data as the SYS user.
The best way for resolving this type of error is to instantiate the table so that it is once again in synch with the primary. Fortunately, Oracle provides the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure as an easy and efficient method to bring a table on a logical standby back into sync with the version on the primary database.
The first step is to create a database link on the logical standby that points to the primary.
Next, let's modify the structure of the SCOTT.EMP table on the logical standby which will result in a failed DML transaction on the logical standby when updates for this table are sent from the primary.
From the primary database, modify a record or two in the SCOTT.EMP table. After modifying the table records, perform a log switch on the primary to send its redo to the logical standby.
Query the DBA_LOGSTDBY_EVENTS view on the logical standby to look for the failed DML transaction.
To start the recovery process for the failed DML transaction, we first need to stop logical standby apply.
Next, use the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure on the logical standby to put the SCOTT.EMP table back in synch with the primary.
Restart logical standby apply.
Verify the integrity of the logical standby and the new SCOTT.EMP table.
Set the GUARD on the logical standby back to its original setting.
A database can operate in one of two mutually exclusive modes in an Oracle Data Guard configuration: primary or standby. Whenever the role is changed between the primary and standby, this is referred to as a role transition. Role transition plays an important part in Data Guard by providing an interface that allows DBA's to activate a standby database to take over as the primary production database or vice versa. There are two types of role transitions supported in Oracle 10g Data Guard: switchover and failover. Knowing the difference between the two role transition methods is crucial.
Click here for a guide on how to perform role transitions and activating a logical standby database.
Additional information on Oracle 10g Release 2 Data Guard can be found in the Data Guard Concepts and Administration. This guide is available from the Oracle Documentation Library website located at the following address http://download.oracle.com/docs/cd/B19306_01/server.102/b14239.pdf.
Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.
Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at email@example.com.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.
Last modified on
Thursday, 17-Apr-2014 02:51:09 EDT
Page Count: 56099