Creating a Logical Standby Database Oracle Database Tips by Donald Burleson

Creating a Logical Standby Database

The first step in creating a logical standby database is to get a backup of the primary database. As mentioned in the previous section, "Creating Physical Standby Databases", this backup does not have to be the most recent backup as long as the DBA has all the archived redo logs that can be used to recover it to its current state. Either a cold backup or a hot backup can be used for the purpose of creating the logical standby database.  Both the cold backup and hot backup methods will be included.

To create a logical Oracle instance from a cold backup of the primary site, shutdown the primary database and copy the files to the standby location. This step is similar to the one presented for physical Oracle instance creation.

The following tasks need to be performed on the primary database to create a logical Oracle instance using a hot backup. On the primary site, the following files need to be backed up:

• Backup data files for all tablespaces, except for temporary tablespaces.

• Backup Control file.

• Backup the archived redo logs created during the tablespace backup and log miner dictionary creation phase.

• Backup the initialization parameter file to be modified and used on Standby Site.

One of the requirements for using a hot backup is that the resource manager should be running on the primary database. This is required because the DBA may need to quiesce the database to build the LogMiner dictionary. If the Resource Manager has not run since the instance startup, Alter system quiesce is restricted and the statement will fail.

TIP - Alter System Set Resource_Manager_Plan = < Plan Name> will change the resource plan, but the DBA will not be able to quiesce the database because resource manager was not running since instance startup. Change the parameter in init.ora file or SPFILE if in use, and then restart the instance.

Backup of the Primary Database

Once the DBA has ensured that the Resource Manager is running, a hot backup of the primary database can be created. To perform a hot backup, put the tablespaces in BACKUP mode one at a time and then copy the datafiles to the backup location. After all the datafiles for a tablespace are copied, take that tablespace out of BACKUP mode.

ALTER TABLESPACE <TablespaceName> BEGIN BACKUP

and

ALTER TABLESPACE <TablespaceName> END BACKUP

statements can be used for this purpose.

Creating Logical Standby Databases in 10g and 11g

A logical Oracle instance in Oracle10g is transitioned from a physical standby database. In order to create a logical standby database, a physical Oracle instance must be created.  This is a much simpler task.

Creating Logical Standby Databases

In this section, it is assumed that the primary database and the Oracle instance reside on separate hosts. A quick review of the Oracle instance creation concepts explained in Chapter 3, "Implementing Standby Databases," is good preparation for this section, as most of the concepts presented here are similar to those of Oracle9i that are described in great detail in that chapter.

Following is the step-by-step guide that is used to create a logical standby database:

• Enable the forced logging to capture all database changes in the redo log file. To enable the FORCE LOGGING, execute the following statement on primary database:

ALTER DATABASE FORCE LOGGING;

• Enable the supplemental data logging to capture additional information that assists the SQL apply operation on the logical standby database. Supplemental logging can be enabled by executing the following statement on the primary database:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

• Create a physical Oracle instance and start the managed recovery mode so that the primary database and the Oracle instance are in sync. The method of creating a physical Oracle instance is explained in detailed in Chapter 3 of this book.
   

• Once the physical Oracle instance is recovered to the last available archived redo log file, start the process of transitioning it to a logical standby database. Modify the initialization parameter file to support the logical standby database. A logical Oracle instance has its own online log file, which will be archived to one of the local archived destination. As a result, a log archival destination preferably, log_archive_dest_1 should be configured to handle it. Also, in order to have an efficient log apply service, increase the parallel-max_server parameter from the default value of five to the default value of nine which is the setting recommended by Oracle.  The following is an extract of the init.ora parameter file from a Oracle instance showing the log management parameters:

Creating Logical Standby Databases

• The create control file statement also creates the LogMiner dictionary; hence, it may take few minutes to create, depending on the availability of the computing resources. LogMiner dictionary creation can be verified from the v$archived_log view.
   

• The primary database generates redo data during the LogMiner dictionary build. The redo data needs to be applied on the physical Oracle instance before activating it as the logical standby database. In order to apply these log files, mount the physical Oracle instance and start the recovery. The control file used to mount the database is a logical control file, so it is not essentially a physical standby database; however, this database is still in the PHYSICAL Oracle instance role as evident from the v$database view. The statement shown below will start the recovery of standby database:

STARTUP MOUNT;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

• Once all the archived redo logs have been applied onto the standby database, control will return to the user. At this point, the Oracle instance can be activated as the logical Oracle instance using the following statement:

ALTER DATABASE ACTIVATE STANDBY DATABASE;

• The next step is to change the name of database using nid utility and reflect the new name in the initialization parameters file. After changing the database name, mount and open it using RESETLOGS. Create temp files for the temp tablespace, and start the log apply service. These steps are identical to the steps used for Oracle9i, and are explained in Chapter 3 of this text.
   

• The verification techniques remain same as explained for Oracle9i.

Create the LogMiner Dictionary

See here how to create a LogMiner Dictionary 

Oracle Net Configuration

The next step in this process is to create Oracle Net service for the Oracle instance on the primary site. As noted previously, the archived redo logs are transferred using Oracle Net between the primary and the standby site. Read "Creating a Physical Standby Database" section for a detailed analysis of the Oracle Net configuration, as this step is identical to the physical Oracle instance creation process.

Modifying the Initialization Parameter File

standby_file_management=auto will ensure that all the modification of datafiles on the primary database will be reflected on the standby site.

Changes on the Logical Standby Site

This completes the tasks required on the primary database. The next stage in the process is to instantiate the logical standby database.

Connect to the standby site and copy the following files to the standby site from the primary site:

• Backup Data files
   

• Backup Control file
   

• Modified Init.ora file
   

• Latest Archive redo log generated during LogMiner dictionary build.

Modifying the Initialization Parameter File on the Standby Site

Before the logical Oracle instance is started and mounted, the initialization parameter file will have to be modified and the Oracle Net connection setup. Modify the init.ora file copied from the primary site to add the parameters required to support a logical standby database.

The following is a sample init.ora file for a logical standby database. This is not a complete init.ora file, and only the logical Oracle instance related parameters are shown here.

TIP - All the DUMP_DEST parameters should be updated if the directory structure is different.

Modifying the Initialization Parameter File on the Standby Site

BACKGROUND_DUMP_DEST=/oracle/appsdb/admin/bdump
CORE_DUMP_DEST=/oracle/appsdb/admin/cdump
USER_DUMP_DEST=/oracle/appsdb/admin/udump

# Control File Configuration

CONTROL_FILES=("/oracle/appsdb/control/logicalstdby.ctl")

# Archive paramaters

STANDBY_ARCHIVE_DEST='LOCATION=/oracle/appsdb/arch'
LOG_ARCHIVE_FORMAT=appsdb_%t_%s.dbf
LOG_ARCHIVE_START=TRUE
LOG_ARCHIVE_DEST_1='LOCATION=/oracle/appsdb/archlocal'
REMOTE_ARCHIVE_ENABLE=TRUE
COMPATIBLE=9.2.0
DB_NAME=appsdb
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=appsstdby
FAL_CLIENT=appsprim

# Network Registration

INSTANCE_NAME=appsdb

The remote_archive_enable parameter should be TRUE.  Otherwise, the log transfer process will not start writing the archived redo logs to the standby site. If the Oracle instance is being created on the local host, set the lock_name_space parameter to a unique value such that the distributed lock manager can generate unique lock names. Also, change the instance_name parameter so that the instance_name of the primary and the standby database are different.

Configure Oracle Net on the Standby Site

Configure the Oracle Net on the standby site to add a listener for this logical Oracle instance and services for the logical Oracle instance and the primary standby database. Either the tnsnames.ora and listener.ora files can be edited or Oracle Net Assistant can be used for this purpose. For the details about Oracle Net configuration, read "Creating a Physical Standby Database" section.

Renaming the Filename on the Standby Site

On the standby site, set the oracle_sid environment variable and then mount the standby database:

STARTUP MOUNT;

If the directory structure on the standby site is different from the one at the primary site, the data files and log files will have to be renamed to reflect the new data files and log files name in the control file of the standby database. This can be achieved using the following statement:

ALTER DATABASE RENAME FILE 'filespec1' TO 'filespec2';

filespec1 for datafiles and log files can be obtained from the v$datafile and v$logfiles views, respectively, from the primary database. Following two queries will assist in getting that information.

Data files:

Select
   Name
From
   V$DATAFILE;
 

Log files:

Select
   Member
From
   V$LOGFILE;

The db_file_name_convert and the log_file_name_convert parameters will not change the datafile and logfile name in the control file. Using these two parameters in the init.ora file will not have any effect.

Create Online Redo Log Files

In order for a logical Oracle instance to work, the online redo logs must be created. The redo logs can be created using the clear logfile group statement for each log group. Use the following statement on the primary database to find the log group #.

Select
   GROUP#
From
   V$LOGFILE;

The output will be similar to the following:

    GROUP#
----------
         1
         2
         3

Create Online Redo Log Files

In this case there are three log groups.

ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 2;
ALTER DATABASE CLEAR LOGFILE GROUP 3;

These statements will create three redo logs of the same size as those defined on the primary database site.

To put all the bits and pieces explained in this chapter together and to give a complete idea about a working standby database, a sample Data Guard configuration will be created.  This sample configuration involves two host machines and two primary databases. The hostnames are jrpr01 and jrpr02, and the databases are "appsdb" and "meddb". 

The name "appsdb" is the production or the primary database on jrpr01, and "meddb" is the primary database on jrpr02. The Oracle instance for "appsdb" is created on jrpr02, and the Oracle instance for "meddb" is created on jrpr01.  Figure 3.1 illustrates this configuration.

Figure 3.1 - Sample Data Guard configuration
 

This two-node cross-host configuration will provide a high degree of fault tolerance for both databases. In case of failure of one of the host machines or one primary database, the corresponding Oracle instance can be activated to perform as the primary node. For example, if the host jrpr01 is not available for planned maintenance or due to unplanned outage, the Oracle instance for "appsdb" on jrpr02 host can be activated.

Oracle Data Guard

Creating Logical Standby Databases

# Archive

STANDBY_ARCHIVE_DEST=/oracle/stdby10g/arch/
LOG_ARCHIVE_DEST_1='LOCATION=/oracle/stdby10g/archlocal'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_FORMAT=apps10g_%t_%s_%r.dbf
LOG_ARCHIVE_DEST_2='SERVICE=apps10g'
LOG_ARCHIVE_DEST_STATE_2=DEFER
LOG_ARCHIVE_TRACE=255
REMOTE_ARCHIVE_ENABLE=TRUE
PARALLEL_MAX_SERVERS=9
           

• Stop the managed recovery process and shutdown the physical standby database.
   

• Create a logical standby control file using the following statement on the primary database. Copy this control file to the location specified by the control_files parameter:

ALTER DATABASE CREATE LOGICAL STANDBY CONTROLFILE AS 'FILESPEC';

For Example

ALTER DATABASE CREATE LOGICAL STANDBY CONTROLFILE AS '/oracle/stdby10g/control/control01.ctl';

Creating Logical Standby Databases

In the next section the enhancement in administration and management of standby databases will be presented.

Improvements in Data Guard Management

There are a few enhancements to further ease the administration and management of Data Guard configuration. These improvements are summarized in this section.

Administration of Standby Database

Startup statements for the physical standby database have been modified in Oracle10g. A summary of these changes is listed below:

• To start a physical standby database and put it in managed recovery mode, execute the following statements:

• Connect using SYS or any other account having SYSDBA privilege.

STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED Oracle instance DISCONNECT FROM SESSION;

• To start a physical standby in READ ONLY mode, issue the STARTUP statement after connecting using SYS or any other account having SYSDBA privilege.
   

• To put a physical Oracle instance in READ ONLY mode from Managed Recovery mode, issue the following statements:

• Connect using sys or any other account having SYSDBA privilege.

ALTER DATABASE RECOVER MANAGED Oracle instance CANCEL;
ALTER DATABASE OPEN;