Creating a Logical Standby

---

In versions of Oracle prior to Oracle Database 10g, the creation of a logical standby required either a cold backup of the primary database or a period of time in which the primary was quiesce restricted. With the ever-growing demands for constant and consistent availability of databases, this restriction presented some challenges. With Oracle Database 10g, it is now possible to create a logical standby without having to shut down or quiesce the primary database. As you will see while creating the logical standby, we start with a working physical standby (which can be created using a hot backup) and convert it into a logical standby. This new creation procedure greatly reduces the impact that creating a logical standby has on the primary database and allows us to experiment without having to wait for maintenance windows to open up.

Is Logical Standby Right for Your Application?

The first step in creating a logical standby is to examine the applications on the primary and make sure that logical standby has support for all of the datatypes and tables. Determining support for your application database objects on the primary database before you create a logical standby database is very important. Changes made on the primary database to any objects that are not supported will not be propagated to the logical standby. When the log apply services on the logical standby encounters any unsupported objects, it will silently exclude all changes made to those objects- with no warning given to the user.

Determining Unsupported Objects

Fortunately, Oracle makes it very easy to determine exactly which objects are supported and which are not. To obtain a list of unsupported tables, use the following query on the primary database:

select distinct owner,table_name from dba_logstdby_unsupported order by owner,table_name;

Tables that are returned from the above query are unsupported for one of several reasons-unsupported datatypes, use of compression, or belonging to a default Oracle schema that is not maintained. In most cases, the tables are unsupported due to datatype restrictions. To see exactly which column/datatype is unsupported, we can modify the above query to include column information for any tables returned:

select column_name,data_type from dba_logstdby_unsupported where table_name = 'foo';

In Oracle Database 10g, the number of supported datatypes increased greatly-so much so that it's easier to list datatypes that are not supported:

BFILE	ROWID
UROWID	user-defined types
object types REFs	Varrays
nested tables	XMLType

Uniquely Identifying Rows on the Primary

When you update a row on the primary database, a redo record is created that can uniquely identify the exact row that was changed. In the absence of a primary key or unique key index, we will utilize the ROWID to locate the row. While this is a fast and efficient method for uniquely locating rows on the primary, it does not work in the case of a logical standby. A logical standby can have a drastically different physical layout, which makes the use of ROWID impossible. Without getting into a deep discussion of the architecture of SQL Apply, this can cause performance issues. To resolve the issue, Oracle recommends that you add a primary key or 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.

To identify tables on the primary that do not have a primary key or do not have a unique index, run the following query:

select owner, table_name,bad_column from dba_logstdby_not_unique where table_name not in  (select table_name from dba_logstdby_unsupported); 

It's important to note that tables returned in the above query can still be supported by SQL Apply, because supplemental logging will add information into the redo stream that will enable the rows to be uniquely identified. However, performance might be impacted. If your application ensures uniqueness, you should consider adding a RELY constraint onto that table on the primary along with an index of the unique columns on the logical standby.

HA Workshop: Creating a Logical Standby

Workshop Notes

Once you have confirmed that logical standby will support your application, it is time to perform the actual task of the creation process.

Step 1.  Create a physical standby.

Many of you might be scratching your head and thinking 'Is that first step a typo?' It's not a typo. The first step in creating a logical standby is to create a physical standby using the same steps outlined in the preceding section. Once the physical standby has been created, we need to start the apply process and allow recovery to bring the standby up to a consistent state with the primary database.

Step 2.  Enable supplemental logging on the primary.

Supplemental logging must be enabled on the primary and standby databases in order for a logical standby to function and support role transitions. When supplemental logging is enabled, additional information is placed into the redo stream that is then used by SQL Apply to help uniquely identify rows and properly maintain tables in the logical standby. To determine if your primary or standby database currently has supplemental logging enabled, run the following query:

select supplemental_log_data_pk as pk_log, supplemental_log_data_ui as ui_log  from v$database;

If either column reports NO, you must enable supplemental logging by issuing the following statement on both the primary and standby database:

alter database add supplemental log data (primary key, unique index) columns;

Once the above command completes, you should once again query V$DATABASE to assure that both the SUPPLEMENTAL_LOG_DATA_PK and SUPPLEMENTAL_LOG_DATA_UI return the value YES.

Step 3.  Prepare initiation parameters for both primary and logical standby.

As part of our physical standby creation process, we configured several parameters in both the primary and standby initialization. If the logical standby that we are creating in this example is to be in addition to the existing physical standby, we would need to configure several new parameters and modify a few existing ones. In the following example, we are maintaining support for the existing physical standby and adding support for the new logical standby. This example also supports both the primary and standby role so that role transitions will be seamless.

## Primary Role Parameters ## DB_UNIQUE_NAME=Orlando SERVICE_NAMES=Orlando LOG_ARCHIVE_CONFIG='DG_CONFIG=(Orlando,Nashville,Nahsville_Reports)' LOG_ARCHIVE_DEST_1=  'LOCATION=/database/10gDR/Orlando/arch/    VALID_FOR=(ALL_LOGFILES,ALL_ROLES)   DB_UNIQUE_NAME=Orlando' LOG_ARCHIVE_DEST_2=  'SERVICE=Nashville_hasun1    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)    DB_UNIQUE_NAME=Nashville' LOG_ARCHIVE_DEST_3=  'SERVICE=Nashville_Reports_hasun1    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)    DB_UNIQUE_NAME=Nashville_Reports' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE LOG_ARCHIVE_DEST_STATE_3=DEFER ## Standby Role Parameters ## DB_FILE_NAME_CONVERT=  ('/database/10gDR/Nashville/','/database/10gDR/Orlando/') LOG_FILE_NAME_CONVERT=  ('/database/10gDR/Nashville/','/database/10gDR/Orlando/')  STANDBY_FILE_MANAGEMENT=AUTO FAL_SERVER=Nashville_hasun1 FAL_CLIENT=Orlando_hasun1

At this point, we should also create the initialization parameter file for the logical standby. If needed, convert the standby database spfile to a text initialization file by issuing the following command:

create pfile from spfile;

Now that we have the text initialization file, we can modify the file to contain the parameters needed to support a logical standby. What follows is an example initialization parameter file for the logical standby that supports both primary and standby roles:

## Primary Role Parameters ## DB_UNIQUE_NAME=Nashville_Reports SERVICE_NAMES=Nashville_Reports LOG_ARCHIVE_CONFIG='DG_CONFIG=(Orlando,Nashville,Nahsville_Reports)' LOG_ARCHIVE_DEST_1=  'LOCATION=/database/10gDR/Orlando/arch/   VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)   DB_UNIQUE_NAME=Nashville_Reports' LOG_ARCHIVE_DEST_2=  'SERVICE=Orlando    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)    DB_UNIQUE_NAME=Orlando' LOG_ARCHIVE_DEST_3=  'LOCATION=/database/10gDR/Nashville_Reports/stby_arch/    VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)    DB_UNIQUE_NAME=Nashville_Reports' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE LOG_ARCHIVE_DEST_STATE_3=ENABLE ## Standby Role Parameters ## STANDBY_FILE_MANAGEMENT=AUTO FAL_SERVER=Orlando_hasun1 FAL_CLIENT=Nashville_Reports_hasun1

Step 4.  Shut down the standby to be transitioned.

Shut down the physical standby that we are going to transition to a logical standby by issuing the following command:

shutdown immediate;

Step 5.  Create the new logical standby controlfile.

Issue the following statement on the primary database to create the logical standby controlfile:

alter database create logical standby controlfile as '/database/backup/control.ctl';

The above command can take some time to complete, depending on the current work load of your primary database. The above statement will not complete until any transactions that were running prior to the command being issued have completed. This is so that we can assure a consistent starting point for the logical standby.

Once the controlfile has been created, transfer the file to the standby host and place in the directory specified by the CONTROL_FILES initialization parameter.

Step 6.  Activate the standby database.

Now we must finish a bit of media recovery and activate the standby, thereby allowing it to be opened in read/write mode. We start this by bringing the standby to the mount state and starting managed recovery:

startup mount; alter database recover managed standby database disconnect; 

We must wait for media recovery to complete prior to continuing. We know that media recovery is complete when the following messages are printed to the alert log:

MRP0: Media Recovery Complete MRP0: Background Media Recovery process shutdown

Once media recovery is complete, we must then activate the standby database with the following command:

alter database activate standby database;

Step 7.  Reset the database name using nid.

It is a good practice to rename your database name to something other than the value of the primary database. This prevents confusion and inadvertently connecting to the wrong database. To perform this action, we must first shut down the standby and bring it back to the mount state:

shutdown immediate; startup mount;

Next, we use the nid utility to change the database ID and name to a distinct value. In the following example, which is run as an OS command, assume we are on the standby host and the ORACLE_SID environment variable is set to Nashville:

nid TARGET=SYS/Not4U DBNAME=Nashville_Reports

The nid utility will prompt you for verification that you want to proceed. Once the utility has completed the database name and ID conversion, you will need to change the DB_NAME value in your standby initialization parameter file as well as the ORACLE_SID environment variable to the new database name. You will also need to generate a new password file for the new database name. As this should be it for major parameter changes, you can create an spfile from the text initialization parameter file at this point.

Step 8.  Create tempfiles for the temporary tablespace.

If a locally managed temporary tablespace existed in the primary when you created the standby, the tablespace will also exist in the standby. All that is necessary now is to add the tempfiles to that locally managed temporary tablespace on the standby. To do this, use the following SQL to add the files:

alter tablespace temp add tempfile '/database/10gDR/temp.dbf' size 100m reuse; 

Step 9.  Create a database link to primary database.

A database link that points to the primary will be necessary for use of the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure. You will need to reset the GLOBAL_DBNAME for your logical standby so that it is not the same as that of your primary database.

Step 10.  Start the SQL Apply engine.

Finally we are ready to start the SQL Apply engine and begin applying transactions from the primary. To do this, we issue the following statement on the logical standby as sys:

alter database start logical standby apply;

Step 11.  Verify that the SQL Apply engine is working.

When starting the SQL Apply engine for the first time, certain actions are taken to prepare the LogMiner dictionary that will be used for all future work. The time taken for this one-time work to complete will vary depending on the size of the dictionary and the capacity of the standby host. To view the SQL Apply engine, and the status of each process associated with the SQL Apply engine, you can use the V$LOGSTDBY view. The following query will show the name of the process, the SCN that it is currently working on, and its current status:

select type, high_scn, status from v$logstdby;

When all of the processes state that no work is available, they are considered to be idle-waiting on additional information to arrive from the primary.

Another good view to determine the progress of the SQL Apply engine is the DBA_LOGSTDBY_PROGRESS view. The APPLIED_SCN column lists the highest SCN that has been applied to the standby database. The NEWEST_SCN column lists the highest SCN that has been received from the primary. When the two values are equal, we know that all available redo that has arrived from the primary has been applied.

---