DBA Tips Archive for Oracle

  


Creating an Oracle Standby Database - (Oracle 8i)

by Jeff Hunter, Sr. Database Administrator



Contents

  1. Introduction
  2. Overview and Features
  3. Note on Oracle Standby Database Option
  4. New Feature in Oracle8i
  5. Creating the Standby Database


Introduction

This article provides detailed instructions and notes for installing and configuring a standby database in Oracle8i. Keep in mind that none of the new features of Oracle9i with respect to Oracle's standby database option.

NOTE: Oracle changed the name of Standby Database to Data Guard in Oracle9i.



Overview and Features

The Standby Database feature of Oracle (Oracle7 and higher) allows the DBA to maintain a duplicate, or standby, copy of a database at a remote site to provide continued primary database availability in the event of failure. The standby database is created with a special copy of the control file from the primary database. The standby database is kept in close synchronization with the primary database by applying the primary database's archived log files to the standby database. It is therefore necessary to operate the primary database in Archivelog Mode to avail the benefit of a standby database.

A standby database is generally used where high availability of data is required. A standby database is one way of quickly providing access to data if the primary database fails and recovery will take longer than the desired time.

The following section will include many features of the Standby Database feature that are only available in Oracle Version 8i.

Standby Database Features



Note on Oracle Standby Database Option



New Feature in Oracle8i

The notes and examples within this section assume you are working on a Unix platform using Oracle8i. It is assumed that the reader is completely familiar with the Oracle Technical report Oracle8i Standby Database [NOTE:76451.1] which details the planning, design, preparation, deployment and maintenance of a standby database under Oracle7. This paper can be found on Oracle Metalink.

Overview

For many years now there has been additional emphasis placed on redundancy and high availability in information systems. Part of Oracle's response to this has been the standby database solution. Oracle8i offers new functionality to simplify and automate the maintenance of the standby database.

Previously, it was the responsibility of the DBA to copy archived redo logs to the remote host and to apply them so that the primary database and the standby database remained synchronized. These functions are automated in Oracle8i. There are really two features used to provide this functionality; remote archival of the primary database's redo log files to the standby database host, and the subsequent automatic application of these archive files to the standby database.

Additional enhancements are increased flexibility in defining local archive destinations and archive completion requirements, and better I/O failure handling and transmission restart capability.

A new feature of the standby database is the ability to open this database in "READ ONLY" mode. While the standby database cannot be maintained while in read only mode (applying logs from the primary database), this does provide the ability to use the processing capacity of the standby host during off hours batch processing, for example, and return to maintenance mode in the morning and afternoon.

Architectural Overview
The foreground process or ARCH will archive to multiple locations specified by new init<SID>.ora parameters. One of these locations must be a local directory, but one or more can also be a remote location. A remote destination is specified to the primary by a Net8 Service Name which is in turn defined in the appropriate network configuration files (i.e. tnsnames.ora file). It is the job of ARCH to accomplish the transmission of archived redo logs to the remote site.

Once ARCH has something to transmit, it invokes a non-local connection at the standby host called a Remote File Server (RFS). The listener at the remote host accepts the connection requests from ARCH and passes the connection request to RFS.

The RFS process consumes the I/O from ARCH. It is the job of RFS to create and populate the remote archived logs at the remote site, and to update the control file of the standby database. RFS does not update the control file until it has completely written the archive log. Once the control file has been updated, the standby database will be automatically updated if it has been placed in the "Sustained Recovery Mode". Alternatively, the database can be maintained manually as before.

This remote archive process requires uninterrupted connectivity over TCP/IP, and the standby database must be mounted as a standby database, open READ ONLY, or in a sustained or manual maintenance mode. Connectivity to the standby database will have to be reestablished if broken.

Implementation involves configuring the primary database, the network, and the standby database.



Creating the Standby Database

In this section, we look over the steps involved in creating a standby database from a primary database. Commands will be provided where appropriate to show how a step is meant to be performed. For the purpose of this section, I will be creating a standby database using the following machines and database:

Primary Database
Machine Name: linux3
Operating System: Red Hat Linux - (Fedora Core 2)
Oracle SID: ORA817
Instance Service Name: ORA817.IDEVELOPMENT.INFO
TNS Service Name: ORA817_LINUX3.IDEVELOPMENT.INFO
Standby Database
Machine Name: linux4
Operating System: Red Hat Linux - (Fedora Core 2)
Oracle SID: ORA817
Instance Service Name: ORA817.IDEVELOPMENT.INFO
TNS Service Name: ORA817_LINUX4.IDEVELOPMENT.INFO


On the primary database

  1. Ensure that the primary database is in ARCHIVELOG mode:
    SQL> archive log list
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            /u06/app/oradata/ORA817/archive
    Oldest online log sequence     15
    Next log sequence to archive   17
    Current log sequence           17

  2. Create a backup (hot or cold) of the primary database datafiles.
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    Take a cold backup of the database and then start the database back up:
    SQL> startup open
    ORACLE instance started.
    
    Total System Global Area  252777660 bytes
    Fixed Size                   451772 bytes
    Variable Size             218103808 bytes
    Database Buffers           33554432 bytes
    Redo Buffers                 667648 bytes
    Database mounted.
    Database opened.

  3. Create a standby control file on the primary database.
    SQL> alter database create standby controlfile as '/u01/app/oracle/control01.ctl';
    
    Database altered.

  4. Archive the current online logs of the primary database. Insure consistency in the backup datafiles, standby control file and logfiles. From the primary database:
    SQL> alter system archive log current;
    
    System altered.

  5. Transfer all the duplicate files (standby control file, datafiles and all archived redo logs) to the standy site.

  6. Prepare the init<SID>.ora file for the primary database. In this step, I provide an overview of the parameters that are important for a successful standby database configuration for the primary database:
    ...
    log_archive_dest_1        = 'location=/u06/app/oradata/ORA817/archive MANDATORY'
    log_archive_dest_2        = 'service=ORA817_LINUX4 OPTIONAL REOPEN=30'
    log_archive_dest_state_1  = 'enable'
    log_archive_dest_state_2  = 'enable'
    log_archive_format        = 'arch_t%t_s%s.dbf'
    log_archive_start         = true
    service_names             = 'ORA817.IDEVELOPMENT.INFO'
    ...

  7. When setting up the standby databse, use the same naming convention for files and directories.

    NOTE: If this is not possible, you can setup the following parameters in the init<SID>.ora for the standby database:
    db_file_standby_name_convert (for V7)
    log_file_standby_name_convert (for V7)
    
    - or -
    
    db_file_name_convert (for V8)
    log_file_name_convert (for V8)
    This is required if the standby database is on the same machine as the primary. If the above init<SID>.ora parameters are used, you MUST use ALTER DATABASE RENAME FILE <oldname> TO <newname> when the standby database is activated.

  8. Configure the Oracle Networking Components that allow the primary database to communicate to the standby database. On the primary host create a net service name that the primary database can use to connect to the standby database. On the primary host create a net service name that standby, when running on the primary host, can use to connect to the primary, when it is running on the standby host.

    You should first put the following two entries in the tnsnames.ora file for the primary host. Keep in mind that the entries below will appear in both the primary and standby hosts:

    TNS Names Entries

    ORA817_LINUX3.IDEVELOPMENT.INFO = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = linux3) (PORT = 1521) ) ) (CONNECT_DATA = (SERVICE_NAME = ORA817.IDEVELOPMENT.INFO) ) ) ORA817_LINUX4.IDEVELOPMENT.INFO = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = linux4) (PORT = 1521) ) ) (CONNECT_DATA = (SERVICE_NAME = ORA817.IDEVELOPMENT.INFO) ) )

    Now, you will need to setup the listener.ora file on the primary host:

    Listener.ora File on the Primary Host

    LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = linux3.ads.com)(PORT = 1521)) ) (PROTOCOL_STACK = (PRESENTATION = TTC) (SESSION = NS) ) ) (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) ) (PROTOCOL_STACK = (PRESENTATION = TTC) (SESSION = NS) ) ) ) CONNECT_TIMEOUT_LISTENER = 10 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/9.2.0) (PROGRAM = extproc) (ENVS="EXTPROC_DLLS=ANY") ) (SID_DESC = (GLOBAL_DBNAME = ORA817_LINUX3.IDEVELOPMENT.INFO) (SID_NAME = ORA817) (ORACLE_HOME = /u01/app/oracle/product/9.2.0) ) )


On the standby database

  1. Prepare the init<SID>.ora file for the standby database. In this step, I provide an overview of the parameters that are important for a successful standby database configuration for the standby database:
    ...
    log_archive_dest_1        = 'location=/u06/app/oradata/ORA817/archive MANDATORY'
    log_archive_dest_state_1  = 'enable'
    log_archive_format        = 'arch_t%t_s%s.dbf'
    log_archive_start         = true
    service_names             = 'ORA817.IDEVELOPMENT.INFO'
    standby_archive_dest      = '/u06/app/oradata/ORA817/archive'
    ...

  2. Configure the Oracle Networking Components for the standby database.

    You should first put the following two entries in the tnsnames.ora file for the standby host. Keep in mind that the entries below will appear in both the primary and standby hosts:

    TNS Names Entries

    ORA817_LINUX3.IDEVELOPMENT.INFO = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = linux3) (PORT = 1521) ) ) (CONNECT_DATA = (SERVICE_NAME = ORA817.IDEVELOPMENT.INFO) ) ) ORA817_LINUX4.IDEVELOPMENT.INFO = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = linux4) (PORT = 1521) ) ) (CONNECT_DATA = (SERVICE_NAME = ORA817.IDEVELOPMENT.INFO) ) )

    Now, you will need to setup the listener.ora file for the standby host:

    Listener.ora File on the Standby Host

    LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = linux4.ads.com)(PORT = 1521)) ) (PROTOCOL_STACK = (PRESENTATION = TTC) (SESSION = NS) ) ) (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) ) (PROTOCOL_STACK = (PRESENTATION = TTC) (SESSION = NS) ) ) ) CONNECT_TIMEOUT_LISTENER = 10 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/9.2.0) (PROGRAM = extproc) (ENVS="EXTPROC_DLLS=ANY") ) (SID_DESC = (GLOBAL_DBNAME = ORA817_LINUX4.IDEVELOPMENT.INFO) (SID_NAME = ORA817) (ORACLE_HOME = /u01/app/oracle/product/9.2.0) ) )

  3. Configure the standby control file on the standby server. When we were working on the primary database server, we created a standby control file. At this point, you will need to copy that standby control to every place indentified in your init<SID>.ora file for the standby database. For my example, I have the control_files defined as follows:
    control_files = '/u03/app/oradata/ORA817/control01.ctl',
                    '/u04/app/oradata/ORA817/control02.ctl',
                    '/u05/app/oradata/ORA817/control03.ctl'
    % cp control01.ctl /u03/app/oradata/ORA817/control01.ctl
    % cp control01.ctl /u04/app/oradata/ORA817/control02.ctl
    % cp control01.ctl /u05/app/oradata/ORA817/control03.ctl

  4. If necessary, create an Oracle Password File for the standby database if you defined it in your init<SID>.ora file:
    % cd $ORACLE_HOME/dbs
    % orapwd file=orapwORA817 password=change_on_install

  5. Put the standby database in managed (automatic) recovery mode using the following:
    SQL> connect / as sysdba
    
    SQL> startup nomount
    ORACLE instance started.
    
    Total System Global Area  252777660 bytes
    Fixed Size                   451772 bytes
    Variable Size             218103808 bytes
    Database Buffers           33554432 bytes
    Redo Buffers                 667648 bytes
    
    
    SQL> alter database mount standby database;
    
    Database altered.
    
    
    SQL> recover standby database until cancel;
    
    ORA-00279: change 1453225 generated at 09/05/2004 12:42:26 needed for thread 1
    ORA-00289: suggestion : /u06/app/oradata/ORA817/archive/arch_t1_s18.dbf
    ORA-00280: change 1453225 for thread 1 is in sequence #18
    
    
    Specify log: {=suggested | filename | AUTO | CANCEL}
    auto
    ORA-00279: change 1474276 generated at 09/05/2004 14:56:58 needed for thread 1
    ORA-00289: suggestion : /u06/app/oradata/ORA817/archive/arch_t1_s19.dbf
    ORA-00280: change 1474276 for thread 1 is in sequence #19
    ORA-00278: log file '/u06/app/oradata/ORA817/archive/arch_t1_s18.dbf' no longer needed for this recovery
    
    
    ORA-00308: cannot open archived log
    '/u06/app/oradata/ORA817/archive/arch_t1_s19.dbf'
    ORA-27037: unable to obtain file status
    Linux Error: 2: No such file or directory
    Additional information: 3
    
    
    SQL> recover managed standby database;
    At this point, the session you executed the above command from, will NOT give your prompt back. It will sit there while waiting for archived redo logs to be transferred over and applied.

After performing the above steps, your standby database should now be in managed recovery mode - waiting for archived logs to be copied over (from the primary database) so they can automatically be applied to the standby database. The "recover managed standby database;" command put the standby database in managed recovery mode, and as you notice, you do not get your prompt back. If you want to see the progress of the standby database, you can view the messages being written to the standby database's alert.log file. From the primary database, I can issue serveral "alter system switch logfile;" commands and then view the progress on the standby database.

Open another terminal window on the standby database and perform the following while you are issueing the "alter system switch logfile;" statement on the primary database":

% cd ~oracle/admin/ORA817/bdump

% tail -f alert_ORA817.log
Datafile 9: '/u06/app/oradata/ORA817/xdb01.dbf'
Starting datafile 10 recovery in thread 1 sequence 19
Datafile 10: '/u06/app/oradata/ORA817/example01.dbf'
Starting datafile 11 recovery in thread 1 sequence 19
Datafile 11: '/u06/app/oradata/ORA817/perfstat01.dbf'
Media Recovery Log
Media Recovery Waiting for thread 1 seq# 19
Sun Sep  5 15:13:14 2004
Restarting dead background process QMN0
QMN0 started with pid=9
Sun Sep  5 15:15:40 2004
Media Recovery Log /u06/app/oradata/ORA817/archive/arch_t1_s19.dbf
Media Recovery Waiting for thread 1 seq# 20
Media Recovery Log /u06/app/oradata/ORA817/archive/arch_t1_s20.dbf
Media Recovery Waiting for thread 1 seq# 21


Copyright (c) 1998-2017 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 jhunter@idevelopment.info.

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, 21-Jun-2007 16:30:07 EDT
Page Count: 22057