Oracle8i Documentation Addendum
Release 3 (8.1.7)

Part Number A85455-01

Library

Product

Contents

Index

Go to previous page Go to next page

12
Standby Database

This chapter describes changes to the 8.1.6 Oracle8i Standby Database Concepts and Administration manual. This chapter contains the following sections:

Revised Script to Identify the Logs in a Gap Sequence

The 8.1.6 Oracle8i Standby Database Concepts and Administration manual documents a script for identifying the logs in a gap sequence. This script, which is referenced throughout the manual, has been updated for release 8.1.7. The updated script, shown in Figure 12-1, supersedes the previous version of the script. The updated script handles the case where one or more of the datafiles are offline. The previous version of the script gave incorrect results when one or more of the datafiles were offline.

Example 12-1 Updated Script to Identify the Logs in a Gap Sequence

      SELECT   high.thread#, "LowGap#", "HighGap#"
      FROM
       (SELECT   thread#, MIN(sequence#)-1 "HighGap#"
        FROM
              (SELECT  a.thread#, a.sequence#
               FROM
                      (SELECT * FROM V$ARCHIVED_LOG) a,
                      (SELECT thread#, MAX(next_change#) gap1
                       FROM  V$LOG_HISTORY
                       GROUP BY thread# )  b
               WHERE
                      a.thread# = b.thread#
                      AND
                      a.next_change# > gap1
              ) GROUP BY thread#
       ) high,
       (SELECT thread#, MIN(gap2) "LowGap#"
        FROM
              (SELECT thread#, sequence#+1 gap2
               FROM   V$LOG_HISTORY, V$DATAFILE
               WHERE
                      checkpoint_change# <= next_change#
                        AND
                      checkpoint_change# >= first_change#
                        AND
                      enabled = 'READ WRITE'
              ) GROUP BY thread#
        ) low
      WHERE
         low.thread# = high.thread#
           AND
         "LowGap#" < "HighGap#";

Additions to Compatibility and Operational Requirements

Chapter 1 in the 8.1.6 Oracle8i Standby Database Concepts and Administration manual has a section titled "Compatibility and Operational Requirements." The following section supersedes the section in the 8.1.6 documentation.

Compatibility and Operational Requirements

Note the following requirements for maintaining a standby database:

Changes to Enabling Online Changes to the Initialization Parameter Settings

Chapter 2 in the 8.1.6 Oracle8i Standby Database Concepts and Administration manual has a section titled, "Enabling Changes to the Initialization Parameter Settings." The following section supersedes the section in the 8.1.6 documentation.

Enabling Online Changes to the Initialization Parameter Settings

If you configured the primary initialization parameter file to archive to the standby site, you should enable these new parameter settings after starting the standby instance and the listener on the standby site.

You can make changes to the LOG_ARCHIVE_DEST parameters in the primary database initialization parameter file while the database is open, but the changes only take effect when the instance is restarted. If the database is open and you want to avoid restarting it, enable the parameter changes dynamically using ALTER SYSTEM statements.

For example, assume that you made the following changes to the initialization parameter file while the database was open:

LOG_ARCHIVE_DEST_1="LOCATION=/arc_dest/ MANDATORY REOPEN=2";
LOG_ARCHIVE_DEST_2="SERVICE=stby1 MANDATORY REOPEN=2";
LOG_ARCHIVE_DEST_STATE_1=ENABLE;
LOG_ARCHIVE_DEST_STATE_2=ENABLE;
LOG_ARCHIVE_MIN_SUCCEED_DEST=2;

You can then connect to the primary database using SQL*Plus and issue ALTER SYSTEM statements as follows to enable these settings:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1="LOCATION=/arc_dest/ MANDATORY REOPEN=2";
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2="SERVICE=stby1 MANDATORY REOPEN=2";
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_MIN_SUCCEED_DEST=2;

Once you have enabled these changes, the primary database can start attempting to archive redo logs to a standby site. Once an archiving attempt has succeeded, archived redo logs continue to transfer from the primary site to the standby site unless the standby instance is shut down, the data communication link goes down, or a destination is manually disabled.

Changes to Receiving Archived Redo Logs While in Read-Only Mode

Chapter 3 in the 8.1.6 Oracle8i Standby Database Concepts and Administration manual has a section titled, "Receiving Archived Redo Logs While in Read-Only Mode." The following section supersedes the section in the 8.1.6 documentation.

Receiving Archived Redo Logs While in Read-Only Mode

While the standby database is in read-only mode, the site can still receive archived redo logs from the primary site. Nevertheless, Oracle does not apply these logs automatically, as in managed recovery. Consequently, a read-only standby database is not synchronized with the primary database at the archive level. You should not activate the standby database in a failover situation unless all archived redo logs have been applied.

See Also:

Enabling Changes to the Initialization Parameter Settings of the 8.1.6 Oracle8i Standby Database Concepts and Administration manual for examples of initialization parameter settings you need to define to automatically archive from the primary site to the standby site 

Changes to Creating the Standby Database Files

Chapter 2 in the 8.1.6 Oracle8i Standby Database Concepts and Administration manual has a section titled, "Creating the Standby Database Files." The following section supersedes the section in the 8.1.6 documentation.

Creating the Standby Database Files

You can create a standby database on the same host as your primary database or on a separate host. If you create your standby database on the same host, follow the creation procedure carefully when creating the standby database files so that you do not overwrite files on the primary database.

The creation of the standby database files occurs in three stages:

  1. Creating the Standby Datafiles

  2. Creating the Standby Control File

  3. Transferring Files to the Standby Site

Creating the Standby Datafiles

First, make backups of your primary database datafiles. You create the standby datafiles from these backups.

You can use any backup of the primary database so long as you have archived redo logs to completely recover the database. The backup can be old or new, consistent or inconsistent. Hot backups have the advantage of allowing you to keep the database open while performing the backup. Nevertheless, you may prefer to make a new closed, consistent backup to prevent the application of a large number of archived redo logs.

To make a consistent, whole database backup to serve as the basis for the standby database:

  1. Start a SQL*Plus session on your primary database and query the V$DATAFILE view to obtain a list of the primary datafiles. For example, enter:

    SQL> SELECT name FROM v$datafile;
    NAME 
    ----------------------------------------------------------------------------
    /oracle/dbs/tbs_01.f
    /oracle/dbs/tbs_02.f
    /oracle/dbs/tbs_03.f
    /oracle/dbs2/tbs_11.f 
    /oracle/dbs2/tbs_12.f
    /oracle/dbs3/tbs_21.f
    /oracle/dbs3/tbs_22.f 
    7 rows selected.
    
    
    
  2. Shut down the primary database cleanly:

    SQL> SHUTDOWN IMMEDIATE;
    
    
    
  3. Make a consistent backup of the datafiles from your primary database using an operating system utility. For example, to copy all of the datafiles into the /backup temporary directory, enter:

    % cp /oracle/dbs/*.f /backup
    % cp /oracle/dbs2/*.f /backup
    % cp /oracle/dbs3/*.f /backup
    
    
  4. Start and mount the primary database without opening it. For example, enter:

    SQL> STARTUP MOUNT pfile=initPROD1.ora;
    

    See Also:

    The Oracle8i Backup and Recovery Guide, to learn how to make operating system backups 

Creating the Standby Control File

After you have created the backups that will be used as the standby datafiles, you can create the standby database control file. The control file must have been created at a time later than the latest timestamp for the backup datafiles.


Note:

You cannot use a single control file for both the primary and standby databases. The standby instance is independent from the primary instance and so requires exclusive possession of its database files. 


To create the standby database control file:

  1. Connect to the primary database and create the control file for your standby database. For example, if you want to create the standby control file as /oracle/dbs/stbycf.f on the primary site, enter the following:

    SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/oracle/dbs/stbycf.f';
    
    

    Note that the filename for the created standby control file must be different from the filename of the current control file of the primary database.

    See Also:

    For ALTER DATABASE syntax, see the Oracle8i SQL Reference

  2. Ensure that the primary database is in ARCHIVELOG mode and that archiving is enabled. Either issue the ARCHIVE LOG LIST statement or query the V$DATABASE view:

    SQL> CONNECT sys/change_on_install@prod1 AS SYSDBA
    SQL> ARCHIVE LOG LIST
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            /vobs/oracle/work/arc_dest/arc
    Oldest online log sequence     821
    Next log sequence to archive   822
    Current log sequence           822
    
    

    If the output from the ARCHIVE LOG LIST statement displays "No Archive Mode," set the log archive mode as follows:

    SQL> ALTER DATABASE ARCHIVELOG;
    
    
  3. Open the primary database as follows:

    SQL> ALTER DATABASE OPEN;
    

Transferring Files to the Standby Site

After you have successfully created the standby datafiles and control file, transfer the files to the standby site using an operating system utility. For example, if the standby site and primary site are on the same host, you can use the UNIX cp command to transfer files; if they are on separate hosts, you can use ftp.

If the standby database is on  Then you 

A separate host with the same directory structure as the primary database 

Can use the same path names for the standby files as the primary files. In this way, you do not have to rename the primary datafiles in the standby control file. 

The same host as the primary database, or the standby database is on a separate host with a different directory structure 

Must rename the primary datafiles in the standby control file after copying them to the standby site. You can:

 

To transfer datafiles and the control file to the standby site:

Transfer the created control file and datafile backups to the standby site using operating system commands or utilities. Use an appropriate method for transferring binary files.

  1. Transfer the control file first, because this transfer takes the least time. For example, enter the following:

    % cp /backup/db.cf /standby/oracle/dbs/db.cf
    
    
  2. Transfer the backup datafiles. For example, enter:

    % cp /backup/*.df /standby/oracle/dbs
    
    
  3. Transfer all available archived redo logs to the standby site. For example, enter:

    % cp /arc_dest/*.arc /standby/arc_dest
    

Revised Scenario 1: Creating a Standby Database on the Same Host

Chapter 5 in the 8.1.6 Oracle8i Standby Database Concepts and Administration manual has a section titled, "Scenario 1: Creating a Standby Database on the Same Host." The following section supersedes the section in the 8.1.6 documentation.

Scenario 1: Creating a Standby Database on the Same Host

This scenario describes the creation of a standby database STANDBY1 on the same host as the primary database PROD1. The host is a UNIX machine with three file systems, each mounted on a separate disk configuration on a different controller. By placing the standby database on a different file system from the primary database, you protect the primary database from a hard disk failure. By running the same-host standby database in managed recovery mode, you can keep it continuously up-to-date.

After you set up the standby database on the local host, you plan to create a standby database on a remote host for total disaster protection. In this way, even if all disks of the primary database crash or are destroyed in a disaster, you can fail over to the remote standby database and keep the database open.

Step 1: Plan the Standby Database.

Because the host uses three file systems, each on its own set of disks with its own controller, you decide to maintain the primary database files on the first file system, the standby database files on the second file system, and the ORACLE_HOME binaries on the third file system. If the primary database disks fail, you can switch to the standby database; if the ORACLE_HOME disks fail, you can switch to the remote standby database.

To host the standby database on the same machine as the primary database, you must set the following parameters in the standby database initialization parameter file:

Fortunately, most (but not all) of the primary database datafiles and redo log files are in the same directory and are named consistently. You will have to rename some of the files manually using ALTER DATABASE statements.

Because the primary database is shut down every Sunday for an hour for maintenance, you decide to use that time to make a cold, consistent backup. You can then restart the database while you make the necessary configurations for the standby database.

Step 2: Create the Standby Database.

The next step in the procedure is to create the backup that will form the basis for the standby database. You know that you can use either an inconsistent or consistent backup, but because the database must go down every Sunday for maintenance, you decide to make a consistent backup then and use it for the standby database.

  1. Determine the database files.

    On Sunday, before shutting down the primary database, you query the database to determine which datafiles it contains:

    SQL> SELECT name FROM v$datafile;
    NAME                                                                            
    --------------------------------------------------------------------------------
    /fs1/dbs/tbs_01.f                                                       
    /fs1/dbs/tbs_02.f                                                       
    /fs1/dbs/tbs_11.f                                                       
    /fs1/dbs/tbs_12.f                                                       
    /fs1/dbs/tbs_21.f                                                       
    /fs1/dbs/tbs_22.f                                                       
    /fs1/dbs/tbs_13.f                                                       
    /fs1/dbs/tbs_23.f                                                       
    /fs1/dbs/tbs_24.f                                                       
    /fs1/dbs/tbs_31.f                                                       
    /fs1/dbs/tbs_32.f                                                       
    /fs1/dbs/tbs_41.f                                                       
    /fs1/dbs2/tbs_42.f                                                       
    /fs1/dbs2/tbs_51.f                                                       
    /fs1/dbs2/tbs_52.f                                                       
    /fs1/dbs2/tbs_03.f                                                       
    /fs1/dbs3/tbs_14.f                                                       
    /fs1/dbs3/tbs_25.f                                                       
    /fs1/dbs3/tbs_33.f                                                       
    /fs1/dbs3/tbs_43.f                                                       
    /fs1/dbs3/tbs_53.f                                                       
    21 rows selected.
    
    
  2. Back up the datafiles.

    After determining which datafiles are in the database, you shut down the database with the IMMEDIATE option:

    SQL> SHUTDOWN IMMEDIATE;
    
    

    At this point, you decide to back up all of the primary datafiles to a temporary directory as follows:

    % cp /fs1/dbs/* /fs1/temp
    % cp /fs1/dbs2/* /fs1/temp
    % cp /fs1/dbs3/* /fs1/temp
    
    

    You perform some other routine maintenance operations and then start and mount the database without opening it as follows:

    SQL> STARTUP MOUNT PFILE=initPROD1.ora;
    
    
  3. Create the standby database control file.

    After a few minutes, you create the standby database control file in the same directory in which you stored the consistent backup:

    SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/fs1/temp/stbycf.f';
    
    
  4. Ensure that the primary database is in ARCHIVELOG mode and that automatic archival is enabled. Issue the ARCHIVE LOG LIST statement:

    SQL> ARCHIVE LOG LIST
    
    

    If the output from the ARCHIVE LOG LIST statement displays "No Archive Mode," set the log archive mode as follows:

    SQL> ALTER DATABASE ARCHIVELOG;
    
    
  5. Open the primary database as follows:

    SQL> ALTER DATABASE OPEN;
    
    
  6. Transfer files to the standby file system.

    After you have successfully created the standby database control file, you can copy the datafiles and the standby database control file from the primary file system to the standby file system.

    Because the transferring of datafiles can take a long time, you first copy the control file, begin copying the datafiles, and then proceed to other tasks (such as network configuration). For example, enter the following at the UNIX command shell:

    % cp /fs1/temp/stbycf.f /fs2/dbs/cf1.f
    % cp /fs1/temp/tbs* /fs2/dbs
    

Step 3: Configure the Network Files.

In order to run a standby database in a managed standby environment, you must configure a Net8 connection between the primary and standby databases so that you can archive the redo logs to the standby service.

You use the IPC protocol to connect the primary database to the standby database because both databases are on the same host. Because you do not maintain an Oracle Names server, you must create both a tnsnames.ora entry for the primary database and a listener.ora entry for the standby database.

  1. Configure the tnsnames.ora file.

    Your next step is to open the tnsnames.ora file in a text editor:

    % vi /fs3/oracle/network/admin/tnsnames.ora
    
    
    

    Currently, only one service name entry exists in the file, a TCP/IP connection to the PROD1 database:

    prod1 = (DESCRIPTION=
            (ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=dlsun183))
            (CONNECT_DATA=(SID=prod1))
    )
    
    
    

    To define an IPC connection between the primary and the standby database, you add an entry with the following format:

    standby_service_name = (DESCRIPTION=
                               (ADDRESS=(PROTOCOL=ipc) (KEY=keyhandle))
                               (CONNECT_DATA=(SID=standby_sid)))
    
    

    Substitute appropriate values for standby_service_name, keyhandle, and standby_sid, as the following example shows:

    standby1 = (DESCRIPTION=
                  (ADDRESS=(PROTOCOL=ipc) (KEY=kstdby1))
                  (CONNECT_DATA=(SID=stdby1)))
    
    
  2. Configure the listener.ora file.

    Your next step is to open the listener.ora file, which is located on file system /fs3:

    % vi /fs3/oracle/network/admin/listener.ora
    
    

    You discover the following list of addresses (where on the host the listener is listening) and SIDs (which connections the listener is listening for):

    LISTENER = (ADDRESS_LIST=
     (ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=dlsun183))
    )
    SID_LIST_LISTENER = (SID_LIST=
     (SID_DESC=(SID_NAME=PROD1)(ORACLE_HOME=/fs3/oracle))
    )
    
    

    Currently, the listener is listening on port 1521 of the host dlsun183 for database PROD1.

    You need to edit the listener.ora file and add two entries with the following format:

    STANDBY_LISTENER = (ADDRESS_LIST=(ADDRESS=(PROTOCOL=ipc)
                            (KEY=keyhandle)))
    
    SID_LIST_STANDBY_LISTENER = (SID_LIST=
                      (SID_DESC=(SID_NAME=standby_sid)(ORACLE_HOME=/oracle_home)))
    

    The listener.ora file is typically located in the $ORACLE_HOME/network/admin directory on the standby site. Substitute appropriate values for keyhandle, standby_sid, and oracle_home as the following example shows:

    STBY1_LISTENER = (ADDRESS_LIST=(ADDRESS=(PROTOCOL=ipc)   # same node as primary
                       (KEY=kstdby1)))   # ORACLE_SID standby instance is started with
    
    SID_LIST_STBY1_LISTENER = (SID_LIST=
                      (SID_DESC=(SID_NAME=stdby1)(ORACLE_HOME=/vobs/fs3/oracle)))
    
    

    Now that you have edited the listener.ora file, you must start the listener:

    % lsnrctl
    LSNRCTL for Solaris: Version 8.1.5.0.0 - Production on 05-APR-99 11:39:41
    
    (c) Copyright 1998 Oracle Corporation.  All rights reserved.
    
    Welcome to LSNRCTL, type "help" for information.
    
    LSNRCTL> start stby1_listener
    
    

As an alternative to the steps outlined in this section, you can use the Net8 Assistant graphical user interface to configure the network files. For additional information, see the Net8 Administrator's Guide.

Step 4: Configure the Primary Database Parameter File.

Now that you have configured the network files, you can edit the primary database initialization parameter file. The primary database is now up and running, so these changes will only be enabled if you restart the instance or issue ALTER SESSION or ALTER SYSTEM statements.

The only changes you need to make to the file involve archiving to the standby service. Currently, the primary database parameter file appears as follows:

db_name=prod1
control_files=(/fs1/dbs/cf1.f,/fs1/dbs/cf2.f)
compatible=8.1.6
log_archive_start = TRUE
log_archive_dest_1 = 'LOCATION=/fs1/arc_dest/ MANDATORY REOPEN=60'
log_archive_dest_state_1 = ENABLE
log_archive_format = log_%t_%s.arc
audit_trail=FALSE
o7_dictionary_accessibility=FALSE
global_names=FALSE
db_domain=regress.rdbms.dev.us.oracle.com
remote_login_passwordfile = exclusive

# default parameters for instance 1
processes=30
sessions=30
transactions=21
transactions_per_rollback_segment=21
distributed_transactions=10
db_block_buffers=1000
db_files=200
shared_pool_size=10000000

  1. Specify standby archive destinations.

    Currently, you archive to only one location: a local directory. Because you want to maintain the local standby database in managed recovery mode, you must specify a new archiving location using a service name.

    Open the primary database initialization parameter file with a text editor and examine the current archiving location and format:

    log_archive_dest_1 = 'LOCATION=/fs1/arc_dest/ MANDATORY REOPEN=60'
    log_archive_dest_state_1 = ENABLE
    log_archive_format = log_%t_%s.arc
    
    

    Parameter/Option  Meaning 

    LOG_ARCHIVE_DEST_1 

    Indicates an archiving destination. 

    LOCATION 

    Indicates a local directory. 

    LOG_ARCHIVE_DEST_STATE_1 

    Indicates the state of the LOG_ARCHIVE_DEST_1 archiving destination. 

    ENABLE 

    Indicates that Oracle can archive to the destination. 

    LOG_ARCHIVE_FORMAT 

    Indicates the format for filenames of log files. 

  2. Because you want to archive to the standby database with service STANDBY1, you edit the file, adding the following entries:

    log_archive_dest_2 = 'SERVICE=standby1 OPTIONAL REOPEN=180'
    log_archive_dest_state_2 = ENABLE
    

    Parameter/Option  Meaning 

    LOG_ARCHIVE_DEST_2 

    Indicates a new archiving destination. LOG_ARCHIVE_DEST_1 is already reserved for local archiving to /fs1/arc_dest/

    SERVICE 

    Indicates the service name of the standby database. 

    OPTIONAL 

    Indicates that Oracle can reuse online redo logs even if this destination fails. 

    REOPEN 

    Indicates how many seconds the archiving process waits before reattempting to archive to a previously failed destination. 

    LOG_ARCHIVE_DEST_STATE_2 

    Indicates the state of the LOG_ARCHIVE_DEST_2 archiving destination. 

    ENABLE 

    Indicates that Oracle can archive to the destination. 

    After editing the primary database initialization parameter file, create a copy for use by the standby database:

    % cp /fs1/temp/initPROD1.ora /fs3/oracle/dbs/initSTANDBY1.ora
    
    

    If the primary database initialization parameter file contains the IFILE parameter, you also need to copy the file referred to by the IFILE parameter to the standby site and, if necessary, make appropriate changes to it.

Step 5: Configure the Standby Database Parameter File.

You know that the initialization parameters shown in Table 11-1 play a key role in the standby database recovery process, and decide to edit them.

Table 12-1  Configuring Standby Database Initialization Parameters
Parameter  Setting 

COMPATIBLE 

This parameter must be the same at the primary and standby databases. Because it is already set to 8.1.6 in the primary database parameter file, you can leave the standby setting as it is. 

CONTROL_FILES 

This parameter must be different between the primary and standby databases. You decide to locate the control files in the /fs2/dbs directory. 

DB_FILE_NAME_CONVERT 

Set when you want to make your standby datafile filenames distinguishable from your primary database filenames. Most (but not all) of the datafiles are in the /fs1/dbs directory. You set this parameter to /fs2/dbs to convert the files in the /dbs subdirectory automatically; the others you will convert using ALTER DATABASE RENAME FILE statements. 

DB_FILES 

DB_FILES must be the same at both databases so that you allow the same number of files at the standby database as you allow at the primary database. Consequently, you leave this parameter alone. An instance cannot mount a database unless DB_FILES is equal to or greater than MAXDATAFILES. 

DB_NAME 

This value should be the same as the DB_NAME value in the production database parameter file. Consequently, you leave this parameter alone. 

LOCK_NAME_SPACE  

Specifies the name space that the distributed lock manager (DLM) uses to generate lock names. Set this value if the standby and primary databases share the same host. You decide to set the name to STANDBY1. 

LOG_ARCHIVE_DEST_1 

This parameter specifies the location of the archived redo logs. You must use this directory when performing manual recovery. You decide to set the value to /fs2/arc_dest/. 

LOG_FILE_NAME_CONVERT 

Set when you want to make your standby redo log filenames distinguishable from your primary database redo log filenames. Because your primary redo logs are located in /fs1/dbs, you decide to locate the standby logs in /fs2/dbs

STANDBY_ARCHIVE_DEST 

Oracle uses this value to create the name of the logs received from the primary site. You decide to set it to /fs2/arc_dest/

Edit the standby database parameter file as follows (with edited values in bold):

db_name = prod1                #The same as PRMYinit.ora
control_files = (/fs2/dbs/cf1.f)
compatible = 8.1.6
log_archive_start = TRUE
log_archive_dest_1='LOCATION=/fs2/arc_dest/'
log_archive_dest_state_1 = ENABLE
log_archive_format = log_%t_%s.arc
standby_archive_dest = /fs2/arc_dest/
db_file_name_convert = ('/fs1/dbs','/fs2/dbs')  
log_file_name_convert = ('/fs1/dbs','/fs2/dbs') 
lock_name_space = standby1                             
audit_trail=FALSE
o7_dictionary_accessibility=FALSE
global_names=FALSE
db_domain=regress.rdbms.dev.us.oracle.com
remote_login_passwordfile = exclusive

# default parameters for instance 1
processes=30
sessions=30
transactions=21
transactions_per_rollback_segment=21
distributed_transactions=10
db_block_buffers=1000
db_files=200
shared_pool_size=10000000

Step 6: Start the Standby Database in Preparation for Managed Recovery.

Now that you have configured all network and parameter files, you can enable archiving to the standby database.

  1. Set the ORACLE_SID environment variable to the same value as the SID parameter in the tnsnames.ora file on the primary site and the listener.ora file on the standby site as follows:

    % setenv ORACLE_SID stdby1
    
    
  2. Start the instance.

    First, you start the standby database instance without mounting the standby database control file, as the following example shows:

    SQL> CONNECT sys/change_on_install@standby1 AS SYSDBA
    SQL> STARTUP NOMOUNT PFILE=/fs3/oracle/dbs/initSTANDBY1.ora;
    SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
    
    
  3. Manually rename datafiles.

    Next, write a SQL script to rename the datafiles not captured by the DB_FILE_NAME_CONVERT parameter:

    ALTER DATABASE RENAME FILE /fs1/dbs2/tbs_42.f,
                               /fs1/dbs2/tbs_51.f,
                               /fs1/dbs2/tbs_52.f,
                               /fs1/dbs2/tbs_03.f,
                               /fs1/dbs3/tbs_14.f,  
                               /fs1/dbs3/tbs_25.f, 
                               /fs1/dbs3/tbs_33.f, 
                               /fs1/dbs3/tbs_43.f, 
                               /fs1/dbs3/tbs_53.f,  
    TO
                               /fs2/dbs/tbs_42.f,
                               /fs2/dbs/tbs_51.f,
                               /fs2/dbs/tbs_52.f,
                               /fs2/dbs/tbs_03.f,
                               /fs2/dbs/tbs_14.f,
                               /fs2/dbs/tbs_25.f,
                               /fs2/dbs/tbs_33.f,
                               /fs2/dbs/tbs_43.f,
                               /fs2/dbs/tbs_53.f
    /
    
    
  4. Enable changes to the primary database parameter file.

    Finally, you enable the changes you made to the primary database parameter file so that the standby database can begin receiving archived redo logs:

    SQL> CONNECT sys/change_on_install@prod1 as sysdba
    SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1 OPTIONAL REOPEN=180';
    SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE;
    

Step 7: Identify the Logs in the Gap Sequence.

Because you have enabled the changes to the primary database parameter file, the primary database is now able to archive to the standby service name. Before you can perform managed recovery, however, you must synchronize the standby database by applying those logs containing changes made after the primary database backup, but before the first log received by the standby database.

Write the following SQL script and run it on the standby database:

 SELECT   high.thread#, "LowGap#", "HighGap#"
 FROM
  (SELECT   thread#, MIN(sequence#)-1 "HighGap#"
   FROM
         (SELECT  a.thread#, a.sequence#
          FROM
                 (SELECT * FROM V$ARCHIVED_LOG) a,
                 (SELECT thread#, MAX(next_change#) gap1
                  FROM  V$LOG_HISTORY
                  GROUP BY thread#       )  b
          WHERE
                 a.thread# = b.thread#
                 AND
                 a.next_change# > gap1
         ) GROUP BY thread#
  ) high,
  (SELECT thread#, MIN(gap2) "LowGap#"
   FROM
         (SELECT thread#, sequence#+1 gap2
          FROM   V$LOG_HISTORY, V$DATAFILE
          WHERE
                 checkpoint_change# <= next_change#
                   AND
                 checkpoint_change# >= first_change#
                   AND
                 enabled = 'READ WRITE'
         ) GROUP BY thread#
   ) low
 WHERE
    low.thread# = high.thread#
      AND
    "LowGap#" < "HighGap#";

The output of the query is as follows:

SQL> @gap
THREAD#    LowGap#    HighGap#  
---------- ---------- ----------
       1   250        252
  

Hence, you must apply log sequences 250, 251, and 252 to synchronize the standby database before initiating managed recovery.

Step 8: Copy the Logs in the Gap Sequence to the Standby File System.

The archived log filenames generated by gap sequence queries on the standby database are generated by the LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_FORMAT parameters in the initialization parameter file. Before transmitting the logs from the primary site to the standby site, you must determine the correct filenames.

First, you determine the filenames of the logs in the gap that were archived by the primary database. After connecting to the primary database using SQL*Plus, issue the following SQL query to obtain the names:

SQL> CONNECT sys/change_on_install@prod1 AS SYSDBA
SQL> SELECT name FROM v$archived_log WHERE sequence# IN (250,251,252);

NAME                                                                            
--------------------------------------------------------------------------------
/fs1/arc_dest/log_1_250.arc
/fs1/arc_dest/log_1_251.arc 
/fs1/arc_dest/log_1_252.arc


The gap sequence in this case consists of log_1_250.arc, log_1_251.arc, and log_1_252.arc. The settings for LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_FORMAT in the standby database parameter file are as follows:

LOG_ARCHIVE_DEST_1 = 'LOCATION=/fs2/arc_dest/'
LOG_ARCHIVE_FORMAT = log_%t_%s.arc


You move the gap sequence logs from the primary file system to the standby file system, renaming them according to values for the LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_FORMAT initialization parameters at the standby site:

% cp /fs1/arc_dest/log_1_250.arc /fs2/arc_dest/log_1_250.arc
% cp /fs1/arc_dest/log_1_251.arc /fs2/arc_dest/log_1_251.arc
% cp /fs1/arc_dest/log_1_252.arc /fs2/arc_dest/log_1_252.arc

Step 9: Apply the Logs in the Gap Sequence to the Standby Database.

Now you can apply the gap sequence logs using the RECOVER AUTOMATIC STANDBY DATABASE statement. This statement uses the values of the LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_FORMAT parameters to construct the target filename. Once the gap sequence logs have been applied, the standby database is synchronized with the primary database and can be placed in managed recovery mode.

While connected to the standby database in SQL*Plus, recover the database using the AUTOMATIC option:

SQL> RECOVER AUTOMATIC STANDBY DATABASE;
ORA-00279: change 35083 generated at 08/16/1999 14:08:37 needed for thread 1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL>

After recovering the gap sequence logs, Oracle prompts you for the name of a log that does not exist. The reason is that the recovery process does not know about the logs archived to the STANDBY service by the primary database. Cancel recovery at this point by entering CANCEL at the prompt.

Step 10: Place the Standby Database in Managed Recovery Mode.

You can now enable managed recovery using the RECOVER MANAGED STANDBY DATABASE statement. You decide to use the TIMEOUT option of the RECOVER statement to specify a time interval of 20 minutes so that Oracle waits the specified number of minutes to write the requested archived log entry to the directory of the standby database control file. If the requested archived log entry is not written to the standby database control file directory within the specified time interval, the recovery operation is canceled.

While connected to the standby database using SQL*Plus, place the standby database in managed recovery mode:

SQL> RECOVER MANAGED STANDBY DATABASE TIMEOUT 20;

Oracle now begins managed recovery. As the primary database archives redo logs to the standby site, the standby database automatically applies them.

Revised Scenario 2: Creating a Standby Database on a Remote Host

Chapter 5 in the 8.1.6 Oracle8i Standby Database Concepts and Administration manual has a section titled, "Scenario 2: Creating a Standby Database on a Remote Host." The following section supersedes the section in the 8.1.6 documentation.

Scenario 2: Creating a Standby Database on a Remote Host

This scenario describes the creation of a standby database STANDBY1 on a remote host. The following assumptions are being made:

Step 1: Back Up the Primary Database Datafiles.

Create the backup that will form the basis for the standby database.

  1. Query the primary database to determine the datafiles. Invoke SQL*Plus and query the V$DATAFILE view to obtain a list of the primary database datafiles, as the following example shows:

    SQL> SELECT name FROM v$datafile;
    NAME
    -----------------------------------------------------------------------
    /vobs/oracle/dbs/dbf_1.f
    1 row selected.
    
    
  2. Shut down the primary database to make a consistent backup of the datafiles:

    SQL> SHUTDOWN IMMEDIATE;
    
    
  3. Copy the primary database datafiles to a temporary location (/backup), as the following example shows:

    % cp /vobs/oracle/dbs/dbf_1.f /backup
    
    
  4. Start and mount the primary database without opening it:

    SQL> STARTUP MOUNT PFILE=PRMYinit.ora;
    

Step 2: Create the Standby Database Control File.

  1. Create the standby database control file by issuing the following statement:

    SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/backup/stbycf.f'
    
    

    The standby database control file and the primary database datafiles are in the same temporary location at the primary site to make copying to the standby site easier.

  2. Ensure that the primary database is in ARCHIVELOG mode and that automatic archival is enabled. Issue the ARCHIVE LOG LIST statement:

    SQL> ARCHIVE LOG LIST
    
    

    If the output from the ARCHIVE LOG LIST statement displays "No Archive Mode," set the log archive mode as follows:

    SQL> ALTER DATABASE ARCHIVELOG;
    
    
  3. Open the primary database:

    SQL> ALTER DATABASE OPEN;
    

Step 3: Transfer the Datafiles and Control File to the Standby Site.

Copy the primary database datafiles and the standby control file from the temporary location at the primary site to the standby site, as the following example shows:

% rcp /backup/* STBYHOST:/fs2/oracle/stdby

Step 4: Configure the Network Files.

This scenario assumes that the TCP/IP network protocol is used to connect to the primary and the standby databases. This step involves editing the following parameter files:

  1. Configure the tnsnames.ora file.

    You need to edit the tnsnames.ora file and add an entry with the following format:

    standby_service_name = (DESCRIPTION=
       (ADDRESS=(PROTOCOL=tcp) (PORT=port_number)(HOST=host_name))
       (CONNECT_DATA=(SID=standby_sid)))
    
    

    The tnsnames.ora file is typically located in the $ORACLE_HOME/network/admin directory on the primary site. Substitute appropriate values for standby_service_name, port_number, host_name, and standby_sid, as the following example shows:

    standby1 = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
              (PORT=1521)(HOST=STBYHOST))
              (CONNECT_DATA=(SID=stdby1)))
    
    
  2. Configure the listener.ora file.

    You need to edit the listener.ora file and add two entries with the following format:

    STANDBY_LISTENER = (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)
                                    (PORT=port_number)(HOST=host_name)))
    
    SID_LIST_STANDBY_LISTENER = (SID_LIST=
                      (SID_DESC=(SID_NAME=standby_sid)(ORACLE_HOME=/oracle_home)))
    
    

    The listener.ora file is typically located in the $ORACLE_HOME/network/admin directory on the standby site. Substitute appropriate values for port_number, host_name, standby_sid, and oracle_home, as the following example shows:

    STDBY1_LISTENER = (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)
                             (PORT=1521)(HOST=STBYHOST)))
    
    SID_LIST_STDBY1_LISTENER = (SID_LIST=
              (SID_DESC=(SID_NAME=stdby1)(ORACLE_HOME=/oracle)))
    
    

    Make sure the SID_NAME in the listener.ora file matches the SID in the tnsnames.ora file. Also, make sure the PORT and HOST values are the same in the two files. Note that you have the option of creating a new listener or adding a new address to an existing listener.

Step 5: Start the Listener on the Standby Site.

The two entries that you added in step 4 define a listener to listen for connections to standby database stdby1 on port 1521. In this step, you must start the stdby1_listener listener. For example:

% lsnrctl start stdby1_listener;

Step 6: Configure the Standby Initialization Parameter File.

  1. Copy the primary database initialization parameter file from the primary site to the standby site. From the primary site, issue a command similar to the following:

    % rcp /vobs/oracle/dbs/PRMYinit.ora STBYHOST:/fs2/oracle/stdby/STBYinit.ora
    
    
  2. Edit the standby initialization parameter file (STBYinit.ora). Edit the following parameters:

    Parameter  Value 

    CONTROL_FILES 

    stbycf.f 

    STANDBY_ARCHIVE_DEST 

    /fs2/oracle/stdby/ 

    LOG_ARCHIVE_DEST_1 

    /fs2/oracle/stdby/ 

    LOG_ARCHIVE_FORMAT 

    stdby_%t_%s 

    DB_FILE_NAME_CONVERT 

    ('/vobs/oracle/dbs','/fs2/oracle/stdby') 

    LOG_FILE_NAME_CONVERT 

    ('/vobs/oracle/dbs','/fs2/oracle/stdby') 

    LOG_ARCHIVE_START 

    FALSE 

The STBYinit.ora file appears as follows:

#
#parameter file STBYinit.ora
#

db_name=prod1                   # The same as PRMYinit.ora

# The following parameter has changed from PRMYinit.ora
control_files=/fs2/oracle/stdby/stbycf.f

# The following parameters are the same as PRMYinit.ora
audit_trail=FALSE
o7_dictionary_accessibility=FALSE
global_names=FALSE
db_domain=regress.rdbms.dev.us.oracle.com
commit_point_strength=1

processes=30
sessions=30
transactions=21
transactions_per_rollback_segment=21
distributed_transactions=10
db_block_buffers=100
shared_pool_size=4000000
ifile=/vobs/oracle/work/tkinit.ora # Verify that file exists on the standby site
                                   # and that the file specification is valid

# specific parameters for standby database
log_archive_format = stdby_%t_%s.arc
standby_archive_dest=/fs2/oracle/stdby/
log_archive_dest_1='LOCATION=/fs2/oracle/stdby/'
db_file_name_convert=('/vobs/oracle/dbs','/fs2/oracle/stdby')
log_file_name_convert=('/vobs/oracle/dbs','/fs2/oracle/stdby')
log_archive_start=FALSE
log_archive_trace=127 

Step 7: Copy the Standby Initialization Parameter File.

  1. Make a copy of the STBYinit.ora file by issuing the following command:

    % cp STBYinit.ora Failover.ora
    
    
    

    Edit Failover.ora so if you fail over to the stdby1 standby database, you can use the Failover.ora file as the initialization parameter file for the new primary database. Make sure you use appropriate values for the LOG_ARCHIVE_DEST_n parameters.

  2. Edit the tnsnames.ora file on the standby site in case failover to the standby database occurs. See step 4 for information on how to configure the tnsnames.ora file.

Step 8: Start the Standby Database.

Start the standby database to enable archiving.

  1. Set the ORACLE_SID environment variable to the same value as the SID parameter in the tnsnames.ora file on the primary site and the listener.ora file on the standby site as follows:

    % setenv ORACLE_SID stdby1
    
    
  2. Start SQL*Plus:

    SQL> CONNECT sys/sys_password as sysdba
    
    
  3. Start the standby database instance without mounting the database:

    SQL> STARTUP NOMOUNT PFILE=STBYinit.ora;
    
    
  4. Mount the standby database:

    SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
    

Step 9: Configure the Primary Initialization Parameter File.

  1. Specify the archive destination by adding the following entry to the PRMYinit.ora file:

    LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1 MANDATORY REOPEN=60'
    
    
  2. Enable the archive destination state by adding the following entry to the PRMYinit.ora file:

    LOG_ARCHIVE_DEST_STATE_2 = ENABLE
    
    
  3. Issue the following statements to ensure that the initialization parameters you have set in this step take effect:

    SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby1 MANDATORY REOPEN=60';
    SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
    

Step 10: Apply the Logs in the Gap Sequence.

  1. On the primary database, archive the current redo log as follows:

    SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
    
    
  2. On the standby database, run the following SQL script to identify the archived redo logs in the gap sequence:

     SELECT   high.thread#, "LowGap#", "HighGap#"
     FROM
      (SELECT   thread#, MIN(sequence#)-1 "HighGap#"
       FROM
             (SELECT  a.thread#, a.sequence#
              FROM
                     (SELECT * FROM V$ARCHIVED_LOG) a,
                     (SELECT thread#, MAX(next_change#) gap1
                      FROM  V$LOG_HISTORY
                      GROUP BY thread#       )  b
              WHERE
                     a.thread# = b.thread#
                     AND
                     a.next_change# > gap1
             ) GROUP BY thread#
      ) high,
      (SELECT thread#, MIN(gap2) "LowGap#"
       FROM
             (SELECT thread#, sequence#+1 gap2
              FROM   V$LOG_HISTORY, V$DATAFILE
              WHERE
                     checkpoint_change# <= next_change#
                       AND
                     checkpoint_change# >= first_change#
                       AND
                     enabled = 'READ WRITE'
             ) GROUP BY thread#
       ) low
     WHERE
        low.thread# = high.thread#
          AND
        "LowGap#" < "HighGap#";
    
    
         THREAD#    LowGap#    HighGap# 
        ---------- ---------- ----------
                 1         90         92
    
    
  3. On the primary database, obtain the filenames of the logs in the gap sequence by performing a query on the V$ARCHIVED_LOG view as follows:

    SELECT name FROM v$archived_log 
    WHERE thread#=1 AND sequence#<=92 AND sequence#>=90;
    
    NAME
    -----------------------------------------
    /vobs/oracle/dbs/r_1_90.arc
    /vobs/oracle/dbs/r_1_91.arc
    /vobs/oracle/dbs/r_1_92.arc
    

  4. Transfer the logs in the gap sequence from the primary database to the standby database as follows:

    % rcp /vobs/oracle/dbs/r_1_90.arc STBYHOST:/fs2/oracle/stdby/stdby_1_90.arc
    % rcp /vobs/oracle/dbs/r_1_91.arc STBYHOST:/fs2/oracle/stdby/stdby_1_91.arc
    % rcp /vobs/oracle/dbs/r_1_92.arc STBYHOST:/fs2/oracle/stdby/stdby_1_92.arc
    
    
  5. On the standby database, issue the following SQL statement to manually apply the logs in the gap sequence:

    SQL> RECOVER AUTOMATIC STANDBY DATABASE;
    

Step 11: Place the Standby Database in Managed Recovery Mode.

On the standby database, enable managed recovery by issuing the following SQL statement:

SQL> RECOVER MANAGED STANDBY DATABASE;


Go to previous page Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index