Installing a logical Standby Database

- Creating a Data Guard Logical Standby database

Installing Physical Standby Database

A physical Oracle instance  can be used to perform a backup operation in either recovery or read-only mode.

Creating a Physical Standby Database

In this section, all the steps involved in creating a physical Oracle instance  will be presented. A physical Oracle instance  can be created in three different configurations as follows:

• On a remote host machine other than the primary database; keeping the same directory structure.
   

• On the local host machine; sharing the machine with the primary database. In this case, the directory structure has to be different, otherwise the primary and the Oracle instance
   files will overwrite each other.
   

• On a remote host machine in a different directory structure.

The first option is most common configuration created and used during the implementation of Data Guard on most sites. This configuration offers ease of administration at the cost of having to buy an additional machine. The second host machine does not need to be of same specification, in terms of hardware. For example, the primary database can reside on an 8 CPU Sun Fire V880 machine and the supporting Oracle instance  on a 2 CPU Sun Fire v220.

Installing Standby Database on Remote Host Machine

The first step in creating a physical standby database is obtaining a copy of the backup of the primary database. The backup copy does not have to be the most recent as long as all the redo logs have been archived and can be used to recover the database to its current state. The backup of the primary database includes all the data files except the temporary data files and the control file. The backup of the control file is created using a special syntax for the Oracle instance which is covered later in this section.

The primary database must be running in archived log mode. This is a requirement of a physical standby database, and if the primary database is not running in archive log mode, it must be put into archive log mode before creating the standby control file. However, the archiving can be enabled later.

The database should be put into FORCE LOGGING mode before the backup is made. This is required to avoid data divergence on the Oracle instance in case any operation performed on the primary database is unrecoverable. A summary of FORCE LOGGING is presented later in this section.

Preparing To Create a Physical Standby Database

The first step is the creation of a standby database.  The primary database is called appsdb and is running on the host named jkpr01. The secondary database will be created on a host called jksp01. The next step is to check to see that the database is running in archive log mode. To do this, start a SQLplus session, connect as SYSDBA, and issue the archive log list command:

Installing Data Guard Physical Standby Databases

sql> sqlplus /nolog
sql> connect sys/<sys passwd> as sysdba;
sql> archive log list;

The output will be similar to the following:

archive log list 

Database log mode              No Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/appsdb/arch
Oldest online log sequence     220
Current log sequence           222

The value for the ?Database log mode? shows the mode in which the database is running.  In this case, it is not running in archive log mode. To change the mode, the database must be shut down, mounted and the following statement must be issued:

ALTER DATABASE ARCHIVELOG;

If the ?automatic archival? is not enabled, it can be enabled using the following statement:

ALTER SYSTEM ARCHIVE LOG START;

Change the log_archive_start=true parameter in the initialization file so that it will remain enabled on next startup of the database. 

Once the database is in archive log mode, the next step is to put it in FORCE LOGGING mode. This will ensure that all the transactions made on the primary database will be registered in the redo logs of the primary database and can be replicated on standby databases.

Installing Data Guard with the FORCE LOGGING Option

Oracle Data guard FORCE LOGGING

Backup of the Primary Database

Once the preliminary tasks have been completed, it is an appropriate time to take the backup of the primary database. This is not a necessary step if a good backup of the database which can be used to create the Oracle instance already exists.

Backup of the Primary Database

Either a cold or hot backup of the primary database can be used for this purpose. Either the operating system utility or the recovery manager can be used for backing up the primary database. For this text, a cold backup, or consistent backup, will be made using the operating system utility.

A list of all the files required to take backup can be found in the v$datafile view.  The following query will help so identify the files:

select
   name
from
   V$DATAFILE;

A sample output from this statement is as follows:

NAME
------------------------------------------------------
/oracle/appsdb/data/system01.dbf
/oracle/appsdb/data/undotbs01.dbf
/oracle/appsdb/data/userdata01.dbf
/oracle/appsdb/data/indxdata01.dbf

This output does not include the name of temporary tablespace files. Backups of the files of the temporary tablespace are not necessary because the transactions in the temporary tablespace or the temporary segments do not generate any redo and are therefore not used in any recovery operation.

Shut the primary database down in normal or immediate mode.  Next, take the backup of the files listed by the above query. The shutdown abort command can be used, but in this case, the instance will have to be recovered when the database is put in managed recovery or read-only mode for the first time. This approach might unnecessarily complicate the creation process.

If the DBA suspects that the shutdown immediate will take significant time, the following command can be issued issuing the shutdown immediate command:

ALTER SYSTEM SWITCH LOGFILE;

This command will clear all the uncommitted transactions and will reduce the time required by the shutdown immediate command.

Once the files have been copied into the same directory structure on the standby database, the next step is to create the control file for the standby database.

If the database has not been put in archive logging mode, the DBA must do so before creating the standby control file.

Install Managed Recovery Process

1. See installing the managed recovery process. 

2. Verify MRP install v$dataguard_status script

3. Verify with v$managed_standby view

4. Verify the Data Guard initialization parameters

 

Verify the MRP is running

On UNIX operating systems, the process list of the O/S can be used to find the background recovery process.

$ ps -ef|grep mrp shows an ora_mrp0_<dbsid>, which indicates that the background recovery process is running.

Now, the Oracle instance configuration is complete and is ready to receive and apply log files from the primary site. In managed recovery mode, the Oracle instance will remain current and can be used for failover and switchover operations.

Details of failover and switchover operations will be presented later in this book.  One final check should be verification that the Oracle instance can be put in read-only mode. To do this, cancel the managed recovery mode and set it in read-only mode. Remember that a physical Oracle instance can be either in recovery or read-only mode but not both at the same time.

Use the following statement to cancel the managed recovery:

ALTER DATABASE RECOVER MANAGED Oracle instance CANCEL;

Use the following statement to cancel the managed recovery and to open the database in read-only mode:

ALTER DATABASE OPEN READ ONLY;

Verify redo logs

Verify archived redo logs with  v$archived_log

 

Installing Data Guard Standby Databases

Verify the Physical Standby Database

Ideally, to open the database in read only mode, a temporary tablespace should be created before the standby database is used for query purposes; however, it can be used without a temporary tablespace as long as all the sort operations are performed within memory specified by the sort_area_size parameter. As a note, the managed recovery must be cancelled when the standby database is shutdown.

On a Local Host Machine

This section contains details on the process of creating a physical Oracle instance on the same host machine as the primary database. The initial few steps are the same as those for the creation of a physical Oracle instance on a remote host.

The steps for creating a backup of data files and control files will not be repeated in this section. The primary database should be in archive log mode, and the FORCE LOGGING should be enabled before taking the backup.

Once a backup of the primary database data files and the control file have been created, the initialization parameter files and the Oracle Net files will have to be configured. The changes required in initialization parameters will follow in the next section.

Modifying the Initialization Parameters

The initialization parameters on the primary database will remain the same except for the archive log parameters. As explained in the previous section, the archive log destination for the Oracle instance must be specified.

Add the following two lines in the init.ora file for the primary database:

Create your tnsnames.ora and listsner.ora

Oracle tnsnames.ora Configuration

PRIMARY1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = appsdb)
    )
  )
 
STDBY2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = stdbydb)
    )
  ) 

The following is a sample of a listener.ora file:

SQL*Net listsner.ora Configuration for a Data Guard database

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /sw/oracle/product/9.2.0)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = appsdb)
      (ORACLE_HOME = /sw/oracle/product/9.2.0)
    )
    (SID_DESC =
      (SID_NAME = stdbydb)
      (ORACLE_HOME = /sw/oracle/product/9.2.0)
    )
  )

 

 

Starting the Standby Database

After making these changes, restart listener to start an instance of these two services. Next, prepare the standby database to be placed in the managed recovery mode. The process of putting the Oracle instance in the managed recovery mode is the same one described in the earlier section called ?On a Remote Host Machine?.

 

 

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.