Oracle9i Data Guard Concepts and Administration
Release 1 (9.0.1)

Part Number A88808-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

6
Data Guard Scenarios

This chapter describes the following standby database scenarios:

6.1 Scenario 1: Creating a Standby Database on the Same Site

This scenario describes the creation of a standby database standby1 on the same site as the primary database primary1. The site is a UNIX system 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-site standby database in managed recovery mode, you can keep it continuously up-to-date.

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

6.1.1 Step 1: Plan the Standby Database.

Because the site 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 system as the primary database, you must set the following parameters in the standby database initialization parameter file:

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.

6.1.2 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 is shut 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.dbf  
    /fs1/dbs/tbs_02.dbf  
    /fs1/dbs/tbs_11.dbf  
    /fs1/dbs/tbs_12.dbf  
    /fs1/dbs/tbs_21.dbf  
    /fs1/dbs/tbs_22.dbf  
    /fs1/dbs/tbs_13.dbf  
    /fs1/dbs/tbs_23.dbf  
    /fs1/dbs/tbs_24.dbf  
    /fs1/dbs/tbs_31.dbf  
    /fs1/dbs/tbs_32.dbf  
    /fs1/dbs/tbs_41.dbf  
    /fs1/dbs2/tbs_42.dbf 
    /fs1/dbs2/tbs_51.dbf 
    /fs1/dbs2/tbs_52.dbf 
    /fs1/dbs2/tbs_03.dbf 
    /fs1/dbs3/tbs_14.dbf 
    /fs1/dbs3/tbs_25.dbf 
    /fs1/dbs3/tbs_33.dbf 
    /fs1/dbs3/tbs_43.dbf 
    /fs1/dbs3/tbs_53.dbf 
    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 restart the database as follows:

    SQL> STARTUP PFILE=initPRIMARY1.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.ctl';
    
  4. Copy 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 transfer of datafiles can take a long time, first copy the control file, copy 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.ctl /fs2/dbs/cf1.ctl
    % cp /fs1/temp/tbs* /fs2/dbs
    

6.1.3 Step 3: Configure Oracle Net.

To run a standby database in a Data Guard environment, you must configure an Oracle Net 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 site. Because you are using the local naming method, you must create new entries in the tnsnames.ora file. You must also add corresponding entries in the listener.ora file.


Note:

If, in the future, you choose to manage this standby database using the Data Guard broker, you must use the TCP/IP protocol instead of the IPC protocol. See Section 6.2.4 for an example using the TCP/IP protocol. 


  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 primary1 database:

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

    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=primary1) (ORACLE_HOME=/fs3/oracle))
    )
    

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

    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=/fs3/oracle)))
    

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

    % lsnrctl
    
    LSNRCTL for Solaris: Version 9.0.0.0.0 - Development on 09-MAR-2001 14:13:40
    
    Copyright (c) 1991, 2001, 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 Oracle Net Configuration Assistant graphical user interface to configure the network files.

See Also:

Oracle9i Net Services Administrator's Guide 

6.1.4 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 initialization parameter file looks as follows:

db_name=primary1
control_files=(/fs1/dbs/cf1.ctl,/fs1/dbs/cf2.ctl)
compatible=9.0.1.0.0
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 the Oracle database server 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 the Oracle database server 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 the Oracle database server 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/initPRIMARY1.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.

6.1.5 Step 5: Configure the Standby Database Parameter File.

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

Table 6-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 9.0.1.0.0 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 

You must rename your standby datafile filenames to direct the standby database to correctly access its datafiles. A convenient way to do so is to use this parameter. If you have more than one set of files mapping from your primary datafiles to your standby datafiles, you need to specify multiple mapping pairs as values to this parameter. For this example, you decide to map primary datafiles from /fs1/dbs to /fs2/dbs, from /fs1/dbs2 to /fs2/dbs, and from /fs1/dbs3 to /fs2/dbs

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 primary database parameter file. Consequently, you leave this parameter alone. 

FAL_SERVER 

This parameter specifies the net service name that the standby database should use to connect to the FAL server. You decide to set the name to primary1

FAL_CLIENT 

This parameter specifies the net service name that the FAL server should use to connect to the standby database. You decide to set the name to standby1

LOCK_NAME_SPACE  

This parameter 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 site. 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 

You must rename your online log filenames on the standby database to allow correct access of its online log file. Even though the online log files are not used until you fail over to the standby database, it is good practice to rename your online log files. Much like the DB_FILE_NAME_CONVERT initialization parameter, you can specify more than one mapping pair as values to this parameter. For this example, you decide to map the primary online log file from /fs1/dbs to the standby online log file location /fs2/dbs

STANDBY_ARCHIVE_DEST 

The Oracle database server 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:

db_name = primary1                #The same as PRMYinit.ora
control_files = (/fs2/dbs/cf1.ctl)
compatible = 9.0.1.0.0
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',
                        '/fs1/dbs2','/fs2/dbs',
                        '/fs1/dbs3','/fs2/dbs')
log_file_name_convert = ('/fs1/dbs','/fs2/dbs') 
lock_name_space = standby1                             
fal_server=primary1
fal_client=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

6.1.6 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. 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@primary1 AS sysdba
    SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1
    2> OPTIONAL REOPEN=180'; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE;

6.1.7 Step 7: 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 that log apply services will wait until log transport services 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;

The standby database is now in managed recovery. When you enable managed recovery, log apply services automatically identify and resolve any archive gaps that may exist. As the primary database archives redo logs to the standby site, the standby database automatically applies them.

6.2 Scenario 2: Creating a Standby Database on a Remote Site

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

6.2.1 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. 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
    -----------------------------------------------------------------------
    /oracle/dbs/dbf_1.dbf
    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):

    % cp /oracle/dbs/dbf_1.dbf /backup
    
  4. Reopen the primary database as follows:

    SQL> STARTUP PFILE=PRMYinit.ora;
    

6.2.2 Step 2: Create the Standby Database Control File.

  1. Before you create the standby database control file, 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," perform the following steps:

    1. Shut down the primary database as follows:

      SQL> SHUTDOWN IMMEDIATE;
      
    2. Start and mount the primary database instance without opening it:

      SQL> STARTUP MOUNT PFILE=PRMYinit.ora;
      
    3. Set the ARCHIVELOG mode as follows:

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

      SQL> ALTER DATABASE OPEN;
      
  2. Create the standby database control file by issuing the following statement:

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

    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.

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

Transfer 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

6.2.4 Step 4: Configure Oracle Net.

This scenario assumes that the TCP/IP network protocol is used to connect to the primary and the standby databases. To achieve remote archiving of redo log files, Oracle Net must be set up correctly from the primary database to the standby database. To allow the standby database to fetch archive gaps from the primary database, Oracle Net must again be set up correctly from the standby database to the primary database. This step involves editing the following files:

  1. Configure the tnsnames.ora file on the primary site.

    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 on the standby site.

    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. You can either create a new listener or add a new address to an existing listener.

  3. Configure the tnsnames.ora file on the standby site.

    You need to add a new TNS name entry at the standby site to allow the standby database to connect to the primary database. The following entry is added:

    primary1 =
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1601)(HOST=prmyhost))
                          (CONNECT_DATA=(SID=prmy)))
    
  4. Configure the listener.ora file on the primary site.

    The following lines are added to the default LISTENER entry:

    LISTENER = (
                   ADDRESS_LIST=( 
                   .
                   .
                   .
                   (ADDRESS=(PROTOCOL=tcp) (PORT=1601) (HOST=prmyhost)))
                   .
                   .
                   .
                )
    
    SID_LIST_LISTENER = (
                            SID_LIST= ( 
                            .
                            .
                            .
                            (SID_DESC=(SID_NAME=prmy) (ORACLE_HOME=/oracle)))
                            .
                            .
                            .
                         )
    

    Make sure the SID_NAME in the listener.ora file matches the SID in the tnsnames.ora file in step 3. Also make sure the PORT and HOST values are the same in the two files.

    See Also:

    Oracle9i Net Services Administrator's Guide for detailed directions on using the Oracle Net Manager 

6.2.5 Step 5: Start the Listener on the Primary and Standby Site.

Start the standby listener on the standby site. For example:

% lsnrctl start stdby1_listener

Normally, your default listener is started on your primary site. Restart the default listener on the primary database to pick up the new definitions. For example:

% lsnrctl stop
% lsnrctl start

6.2.6 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 /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.ctl 

    STANDBY_ARCHIVE_DEST 

    /fs2/oracle/stdby/ 

    LOG_ARCHIVE_DEST_1 

    /fs2/oracle/stdby/ 

    LOG_ARCHIVE_FORMAT 

    stdby_%t_%s 

    DB_FILE_NAME_CONVERT 

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

    LOG_FILE_NAME_CONVERT 

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

    LOG_ARCHIVE_START 

    true 

    FAL_SERVER 

    standby1 

    FAL_CLIENT 

    primary1 

The STBYinit.ora file looks as follows:

#
#parameter file STBYinit.ora
#

db_name=primary1                   # The same as PRMYinit.ora

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

# 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=/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=('/oracle/dbs','/fs2/oracle/stdby')
log_file_name_convert=('/oracle/dbs','/fs2/oracle/stdby')
log_archive_start=true
log_archive_trace=127 
fal_server=standby1
fal_client=primary1

6.2.7 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 Section 6.2.4 for information on how to configure the tnsnames.ora file.

6.2.8 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;
    

6.2.9 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 
      2> MANDATORY REOPEN=60';
    SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
    

6.2.10 Step 10: 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;

6.3 Scenario 3: Accommodating Physical Changes in the Primary Database

This scenario describes the procedures you should follow when a physical change is made in the primary database. The following topics are covered:

6.3.1 Adding a Datafile to the Primary Database

To maintain consistency when you add a datafile to the primary database, you must also add a corresponding datafile to the standby database. Otherwise, changes in the online redo logs that relate to the new datafile in the primary database will not be applied to the standby database. The steps you perform depend on how the datafile was created on the primary database and how the initialization parameter STANDBY_FILE_MANAGEMENT is defined on the standby database.

If  Then 

You create a new datafile on the primary database with the STANDBY_FILE_MANAGEMENT initialization parameter set to auto on the standby database 

The new datafile is automatically created on the standby database. 

If you create a new datafile on the primary database with the STANDBY_FILE_MANAGEMENT initialization parameter undefined or set to manual on the standby database 

You must manually copy the new datafile to the standby database and re-create the standby control file. 

You copy an existing datafile from another database to primary database 

You must also copy the new datafile to the standby database and re-create the standby control file. 

Section 6.3.1.1 illustrates the steps to enable the automatic creation of datafiles on the standby database. Section 6.3.1.2 illustrates the manual steps necessary if standby file management is not automatic.

6.3.1.1 Enabling the Automatic Creation of Datafiles on the Standby Database

When you create a new datafile on the primary database, the datafile is automatically created on the standby database if the initialization parameter STANDBY_FILE_MANAGEMENT is set to auto in the standby database initialization file.

The following steps describe how to set up your standby database to automate the creation of datafiles.

  1. Add the following line to your standby database initialization file.

    STANDBY_FILE_MANAGEMENT=auto
    
  2. Shut down the standby database and restart it to pick up the changes in the initialization file and then restart managed standby.

    SQL> SHUTDOWN;
    SQL> STARTUP NOMOUNT pfile=initSTANDBY.ora
    SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
    

    Alternatively, you can dynamically set the STANDBY_FILE_MANAGEMENT initialization parameter with an ALTER SYSTEM statement to avoid shutting down and restarting the instance. For example:

    SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=auto;
    
  3. Add a new tablespace to the primary database.

    SQL> CREATE TABLESPACE new_ts datafile 't_db2.dbf'
      2> SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;
    
  4. Archive the current redo log so it will get copied to the standby database.

    SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
    
  5. Verify that the new datafile was added to the primary database.

    SQL> SELECT name FROM v$datafile;
    NAME
    ----------------------------------------------------------------------
    /oracle/dbs/t_db1.dbf
    /oracle/dbs/t_db2.dbf
    
  6. Verify that the new datafile was added to the standby database.

    SQL> SELECT name FROM v$datafile;
    NAME
    ----------------------------------------------------------------------
    /oracle/dbs/s2t_db1.dbf
    /oracle/dbs/s2t_db2.dbf
    

6.3.1.2 Manually Creating New Datafiles on the Standby Database

The following steps illustrate how to manually create datafiles on the standby database when standby file management is manual.

  1. Make sure managed recovery is caught up by comparing the log sequence numbers in the V$LOG fixed view on the primary database with the V$MANAGED_STANDBY fixed view on the standby database.

    SQL> -- on the primary
    SQL> SELECT SEQUENCE#, ARCHIVED, STATUS
      2> FROM v$log
      3> WHERE STATUS = 'CURRENT';
    
    SEQUENCE# ARC STATUS
    ---------- --- ----------------
           123 NO  CURRENT
    
    SQL> -- on the standby
    SQL> SELECT PROCESS, STATUS, SEQUENCE#
      2> FROM v$managed_standby;
    
    PROCESS STATUS        SEQUENCE#
    ------- ------------ ----------
    MRP0    WAIT_FOR_LOG        123
    
  2. Shut down the standby database.

    SQL> SHUTDOWN;
    
  3. Add a new tablespace to the primary database.

    SQL> CREATE TABLESPACE new_ts DATAFILE 't_db2.dbf'
      2> SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;
    
  4. Verify that the new datafile has been added to the primary database.

    SQL> SELECT name FROM v$datafile;
    NAME
    
    ----------------------------------------------------------------------
    /oracle/dbs/t_db1.dbf
    /oracle/dbs/t_db2.dbf
    
  5. Set the new tablespace offline, copy the new datafile to the standby database with an operating system command, and then set the new tablespace back online.

    SQL> ALTER TABLESPACE new_ts OFFLINE;
    
    % cp t_db2.dbf s2t_db2.dbf
    
    SQL> ALTER TABLESPACE new_ts ONLINE;
    
  6. Re-create the standby control file and copy it to the standby database.

    SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'scf2.ctl' REUSE;
    
    % rcp scf2.ctl <standby location>
    
  7. Start the standby database and start managed recovery.

    SQL> STARTUP NOMOUNT pfile=initSTANDBY.ora
    SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
    
  8. Archive the current redo log on the primary database so it will get copied to the standby database.

    SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
    
  9. Verify that the datafile has been added on the standby database.

    SQL> SELECT name FROM v$datafile;
    NAME
    
    ----------------------------------------------------------------------
    /oracle/dbs/s2t_db1.dbf
    /oracle/dbs/s2t_db2.dbf
    

6.3.2 Renaming a Datafile in the Primary Database

When you rename one or more datafiles in the primary database, you also need to rename the corresponding datafiles in the standby database.


Note:

Unlike adding or deleting datafiles, the STANDBY_FILE_MANAGEMENT initialization parameter has no effect when renaming datafiles. 


  1. To rename the datafile at the primary site, you must take the tablespace offline:

    SQL> ALTER TABLESPACE tbs_4 OFFLINE;
    
  2. Exit SQL and execute the following command to rename the datafile on the system:

    % mv tbs_4.dbf tbs_x.dbf
    
  3. Go back into SQL and execute the following statements to rename the datafile in the database and to bring the tablespace back online:

    SQL> ALTER TABLESPACE tbs_4 RENAME DATAFILE 'tbs_4.dbf' TO 'tbs_x.dbf';
    SQL> ALTER TABLESPACE tbs_4 ONLINE;
    
  4. At the primary site, create the standby database control file by issuing the following statement:

    SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'stbycf.ctl';
    
  5. Ensure that the standby database has applied all of the online redo logs by issuing the following statement:

    SQL> RECOVER AUTOMATIC STANDBY DATABASE;
    
  6. Shut down the standby database with the IMMEDIATE option:

    SQL> SHUTDOWN IMMEDIATE;
    
  7. Copy the standby database control file from the primary site to the standby site, overwriting the control file that exists on the standby site:

    % rcp stbycf.ctl stbyhost:/fs2/oracle/stdby/
    
  8. Rename the datafile at the standby site:

    % mv tbs_4.dbf tbs_x.dbf
    
  9. Start the standby database instance without mounting the database:

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

    SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
    
  11. On the standby database, enable managed recovery by issuing the following statement:

    SQL> RECOVER MANAGED STANDBY DATABASE;
    

If you do not rename the corresponding file at the standby site, and then attempt to refresh the standby database control file, the standby database will attempt to use the renamed datafile, but it will not find the renamed datafile. Consequently, you will see error messages similar to the following:

ORA-00283: recovery session canceled due to errors
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/oracle/dbs/stdby/tbs_x.dbf'

6.3.3 Deleting a Datafile or Dropping a Tablespace in the Primary Database

When you delete one or more datafiles or drop one or more tablespaces in the primary database, you also need to delete the corresponding datafiles or drop the corresponding tablespaces in the standby database. You also need to refresh the standby database control file.

  1. Drop the tablespace at the primary site:

    SQL> DROP TABLESPACE tbs_4;
    % rm tbs_4.dbf
    
  2. Refresh the standby database control file. See Section 6.3.6 for the steps.

  3. Delete the corresponding datafile at the standby site:

    % rm tbs_4.dbf
    

If the STANDBY_FILE_MANAGEMENT initialization parameter is set to auto, dropped tablespaces and deleted datafiles from the primary database are automatically dropped or deleted from the standby database. However, if the STANDBY_FILE_MANAGEMENT initialization parameter is set to manual, or if the parameter is not defined, you must manually drop tablespaces or delete datafiles from the standby database and re-create the standby control file.

6.3.4 Adding or Dropping Online Redo Logs

One method of tuning available to the database administrator (DBA) is changing the size and number of online redo logs. Consequently, when you add or drop an online redo log at the primary site, it is important that you refresh the standby database control file.

  1. Add or drop an online redo log as follows:

    SQL> ALTER DATABASE ADD LOGFILE 'prmy3.log' SIZE 100K;
    

    or

    SQL> ALTER DATABASE DROP LOGFILE 'prmy3.log';
    
  2. Refresh the standby database control file. See Section 6.3.6 for the steps.

6.3.5 Altering Control Files

If you use the CREATE CONTROLFILE statement in the primary database to change one or more database parameters, you need to refresh the standby database control file. Some of the parameters you can change with this statement are the maximum number of redo log file groups, redo log file members, archived redo log files, datafiles, or instances that can concurrently have the database mounted and open.

  1. On the primary database, alter the control file as follows:

    SQL> CREATE CONTROLFILE REUSE DATABASE primary1 NORESETLOGS
      2>    LOGFILE  'prmy1.log' SIZE 100K, 'prmy2.log' SIZE 100K
      3>    DATAFILE 'dbf_1.dbf' SIZE 10M MAXLOGFILES 52 ARCHIVELOG;
    
  2. Refresh the standby database control file. See Section 6.3.6 for the steps.


    Note:

    If you use the RESETLOGS clause of the CREATE CONTROLFILE statement, you will invalidate the standby database. Once the standby database is invalidated, your only option is to re-create the standby database. 


6.3.6 Refreshing the Standby Database Control File

In some cases, when you make a physical change to the primary database, in addition to making the corresponding change to the standby database, you also need to refresh the standby database control file. The online redo logs do not record changes made to the primary database control file. This section describes the cases where you need to refresh the standby database control file and the steps to follow.

You need to refresh the standby database control file whenever you:

Whether you must refresh the control file after dropping a tablespace depends on the setting of the STANDBY_FILE_MANAGEMENT initialization parameter. If the parameter is not set, or it is set to manual, you must refresh the control file after dropping a tablespace. If the parameter is set to auto, you do not have to refresh the control file after dropping a tablespace, as this is done automatically.

Perform the following steps to keep the standby database control file synchronized with the primary database control file:

  1. At the primary site, create the standby database control file by issuing the following statement:

    SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'stbycf.ctl';
    
  2. If the standby database is in managed recovery mode, you need to cancel recovery by issuing the following statement:

    SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
    
  3. Shut down the standby database with the IMMEDIATE option:

    SQL> SHUTDOWN IMMEDIATE;
    
  4. Copy the standby database control file from the primary site to the standby site, overwriting the control file that exists on the standby site:

    % rcp stbycf.ctl stbyhost:/fs2/oracle/stdby/
    
  5. Start the standby database instance without mounting the database:

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

    SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
    
  7. On the standby database, enable managed recovery by issuing the following statement:

    SQL> RECOVER MANAGED STANDBY DATABASE;
    

    You may get the following error messages when you try to enable managed recovery:

    ORA-00308: cannot open archived log 'standby1'
    ORA-27037: unable to obtain file status
    SVR4 Error: 2: No such file or directory
    Additional information: 3
    ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error 
    below
    ORA-01152: file 1 was not restored from a sufficiently old backup
    ORA-01110: data file 1: '/oracle/dbs/stdby/tbs_1.dbf'
    

    If you get the ORA-00308 error, cancel recovery by issuing the following statement:

    SQL> CANCEL
    

    These error messages are issued when one or more logs in the archive gap have not been successfully applied. If you receive these errors, manually resolve the gaps and repeat step 7.

    See Also:

    Section B.3 for information on resolving an archive gap 

6.3.7 Physical Changes That Require You to Rebuild the Standby Database

Some physical changes you make to the primary database can invalidate the standby database. Once a standby database is invalidated, your only option is to rebuild it.

The following clauses of the ALTER DATABASE statement invalidate the standby database:

6.4 Scenario 4: Recovering After the NOLOGGING Clause Is Specified

In some SQL statements, the user has the option of specifying the NOLOGGING clause, which indicates that the database operation is not logged in the redo log file. Even though the user specifies the NOLOGGING clause, a redo log record is still written to the redo log. However, when the redo log file is copied to the standby site and applied to the standby database, a portion of the datafile is unusable and marked as being unrecoverable. When you either activate the standby database, or open the standby database with the read-only option, and attempt to read the range of blocks that are marked as UNRECOVERABLE, you will see error messages similar to the following:

ORA-01578: ORACLE data block corrupted (file # 1, block # 2521)
ORA-01110: data file 1: '/oracle/dbs/stdby/tbs_1.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

To recover after the NOLOGGING clause is specified, you need to copy the datafile that contains the unjournaled data from the primary site to the standby site. Perform the following steps:

  1. Determine which datafiles should be copied.

    1. Issue the following query in the primary database:

      SQL> SELECT name, unrecoverable_change# FROM v$datafile;
      NAME                                                  UNRECOVERABLE
      ----------------------------------------------------- -------------
      /oracle/dbs/tbs_1.dbf                                       5216
      /oracle/dbs/tbs_2.dbf                                          0
      /oracle/dbs/tbs_3.dbf                                          0
      /oracle/dbs/tbs_4.dbf                                          0
      4 rows selected.
      
    2. Issue the following query in the standby database:

      SQL> SELECT name, unrecoverable_change# FROM v$datafile;
      NAME                                                  UNRECOVERABLE
      ----------------------------------------------------- -------------
      /oracle/dbs/stdby/tbs_1.dbf                                 5186
      /oracle/dbs/stdby/tbs_2.dbf                                    0
      /oracle/dbs/stdby/tbs_3.dbf                                    0
      /oracle/dbs/stdby/tbs_4.dbf                                    0
      4 rows selected.
      
    3. Compare the query results from the primary and the standby databases.

      Compare the value of the UNRECOVERABLE_CHANGE# column in both query results. If the value of the UNRECOVERABLE_CHANGE# column in the primary database is greater than the same column in the standby database, then the datafile needs to be copied from the primary site to the standby site.

      In this example, the value of the UNRECOVERABLE_CHANGE# in the primary database for the tbs_1.dbf datafile is greater, so you need to copy the tbs_1.dbf datafile to the standby site.

  2. On the primary site, back up the datafile that you need to copy to the standby site as follows:

    SQL> ALTER TABLESPACE system BEGIN BACKUP;
    SQL> EXIT;
    % cp tbs_1.dbf /backup
    SQL> ALTER TABLESPACE system END BACKUP;
    
  3. Create a new standby database control file.

    In the primary database, issue the following statement:

    SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/backup/stbycf.ctl;
    
  4. Shut down the standby database.

    In the standby database, issue the following statement:

    SQL> SHUTDOWN IMMEDIATE;
    
  5. Copy the datafile and the standby database control file from the primary site to the standby site as follows:

    % rcp /backup/* stbyhost:/fs2/oracle/stdby/
    
  6. Start the standby database instance without mounting the database:

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

    SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
    
  8. On the standby database, enable managed recovery by issuing the following statement:

    SQL> RECOVER MANAGED STANDBY DATABASE;
    

    You may get the following error messages when you try to enable managed recovery:

    ORA-00308: cannot open archived log 'standby1'
    ORA-27037: unable to obtain file status
    SVR4 Error: 2: No such file or directory
    Additional information: 3
    ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error 
    below
    ORA-01152: file 1 was not restored from a sufficiently old backup
    ORA-01110: data file 1: '/oracle/dbs/stdby/tbs_1.dbf'
    

    If you get the ORA-00308 error, cancel recovery by issuing the following statement:

    SQL> CANCEL
    

    These error messages are issued when one or more logs in the archive gap have not been successfully applied. If you receive these errors, manually resolve the gaps and repeat step 8.

    See Also:

    Section B.3 for information on resolving an archive gap 

6.5 Scenario 5: Deciding Which Standby Database to Fail Over to in a Multiple Standby Database Configuration

Every standby database is associated with one and only one primary database. A single primary database can, however, support multiple standby databases. This scenario identifies the kind of information you need in order to decide which of the multiple standby databases to activate.

One of the important things to consider in a multiple standby database configuration is whether the archive destination is mandatory or optional. The following table lists an advantage and disadvantage for each destination:

Destination  Advantage  Disadvantage 

MANDATORY 

All archived redo logs are archived to the mandatory archive destination. After you apply the archived redo logs at the standby site, you can ensure that the standby database is up-to-date. Furthermore, you can activate the standby database as the new primary database with minimum loss of data. 

In some cases, (such as network failure), the archived redo logs cannot reach the mandatory archive destination, causing the archiving of the redo log to stop. In the worst case, if all online redo logs are full and cannot be archived, the primary database instance will stop working.

You can issue the following SQL query to determine whether the primary database stopped because it was not able to switch to an online redo log:

SELECT decode (count(*), 0, 'NO', 'YES') "switch_possible"

FROM v$log

WHERE archived='YES';

If the output from the query displays "Yes," a log switch is possible; if the output displays "No," a log switch is not possible.

See Section 6.8

OPTIONAL 

The primary database continues to operate normally when archival of the redo logs to the optional archive destination at the standby site is interrupted. 

An archive gap may cause data loss because archive logs that are required to be applied to the standby database are unavailable. This results in the managed recovery operation terminating before all primary data has been applied to the standby database. 

Consider the following recommendations in a multiple standby database configuration:

Suppose the primary database is located in San Francisco and supports five standby databases as follows:

Standby  Location  Type  Description 

Local directory 

Mandatory 

Local copy of the archived redo logs. 

San Francisco 

Mandatory 

Fail over to this standby database when there is physical damage at the primary site. This standby site is connected to the primary site by a local area network. 

Boston 

Optional 

Fail over to this standby database when a disaster occurs that affects San Francisco. 

Los Angeles 

Optional 

This standby site receives archived redo logs, but does not apply them. See Section 6.10 for a description of this type of configuration. 

San Francisco 

Optional 

This standby site receives archived redo logs, and applies them after an 8-hour time lag. See Section 6.11 for a description of this type of configuration. 

Assume that a disaster occurs in San Francisco where the primary site is located, and the primary site is damaged. One of the standby databases must be activated. You cannot assume that the database administrator (DBA) who set up the multiple standby database configuration is available to decide which standby database to fail over to. Therefore, it is imperative to have a disaster recovery plan at each standby site, as well as at the primary site. Each member of the disaster recovery team needs to know about the disaster recovery plan and be aware of the procedures to follow. This scenario identifies the kind of information that the person who is making the decision would need when deciding which standby database to activate.

One method of conveying information to the disaster recovery team is to include a ReadMe file at each standby site. This ReadMe file is created and maintained by the DBA and should describe how to:

The following example shows the contents of a sample ReadMe file:

----------------Standby Database Disaster Recovery ReadMe File----------------

Warning:
********************************************************************************
Perform the steps in this procedure only if you are responsible for failing over 
to a standby database after the primary database fails.

If you perform the steps outlined in this file unnecessarily, you may corrupt 
the entire database system.
********************************************************************************

Multiple Standby Database Configuration:

No.     Location       Type     IP Address
--- --------------- --------- --------------
 1   San Francisco   Primary   128.1.124.25 
 2   San Francisco   Standby   128.1.124.157
 3   Boston          Standby   136.132.1.55
 4   Los Angeles     Standby   145.23.82.16
 5   San Francisco   Standby   128.1.135.24

You are in system No. 3, which is located in Boston.

Perform the following steps to fail over to the most up-to-date and available 
standby database:

1.  Log on to the local standby database as a DBA.

    a)  Log on with the following user name and password:

               username: Standby3
               password: zkc722Khn

    b)  Invoke SQL*Plus as follows:

        % sqlplus
     
    c)  Connect as the DBA as follows:

        CONNECT sys/s23LsdIc AS SYSDBA

2.  Connect to as many remote systems as possible. You can connect to a maximum
    of four systems. System 4 does not have a firewall, so you can connect to it
    directly. Systems 1, 2, and 5 share the same firewall host.  You need to go
    to the firewall host first and then connect to each system.  The IP address
    for the firewall host is 128.1.1.100.  Use the following user name and
    password:
               username: Disaster
               password: 82lhsIW32

3.  Log on to as many remote systems as possible with the following user names
    and passwords:

    Login information:

    No.     Location     IP Address    username   password
    --- --------------- ------------- ---------- ----------
    1   San Francisco   128.1.124.25   Oracle9i   sdd290Ec
    2   San Francisco   128.1.124.157  Standby2   ei23nJHb
    3                    (L o c a l)
    4   Los Angeles     145.23.82.16   Standby4   23HHoe2a
    5   San Francisco   128.1.135.24   Standby5   snc#$dnc

4.  Invoke SQL*Plus on each remote system you are able to log on to as follows:

    % sqlplus

5.  Connect to each remote database as follows:

    CONNECT sys/password AS SYSDBA

    The DBA passwords for each location are:

    No.     Location      Password
    --- --------------- -----------
    1   San Francisco     x2dwlsd91
    2   San Francisco     a239s1DAq
    3           (L o c a l)
    4   Los Angeles       owKL(@as23
    5   San Francisco     sad_KS13x

6.  If you are able to log on to System 1, invoke SQL*Plus and issue the
    following statements:

    SQL> SHUTDOWN IMMEDIATE;
    SQL> STARTUP PFILE=PRMYinit.ora;

    Note: If you are able to execute the STARTUP statement successfully, the
          primary database has not been damaged.  Do not continue with this
          procedure.

7.  Issue the following SQL statements on each standby database (including the
    one on this system) that you were able to connect to:

    SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
    SQL> SHUTDOWN IMMEDIATE;
    SQL> STARTUP NOMOUNT PFILE=STBYinit.ora;
    SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
    SQL> RECOVER AUTOMATIC STANDBY DATABASE;
           *** press the Return key for each of the prompts
    SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$LOG_HISTORY GROUP BY THREAD#;

    Compare the query results of each standby database. Activate the
    standby database with the largest sequence number.

8.  Fail over to the standby database with the largest sequence number.

    On the standby database with the largest sequence number, invoke SQL*Plus
    and issue the following SQL statements:

    SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
    SQL> SHUTDOWN IMMEDIATE;
    SQL> STARTUP PFILE=Failover.ora;

------------End of Standby Database Disaster Recovery ReadMe File-------------

6.6 Scenario 6: Switching Over a Primary Database to a Standby Database

Reversing the roles of a primary and standby database is sometimes referred to as a switchover or a switchback operation. The following steps outline what commands must be issued to perform a switchover operation.

For this discussion, boston is initially the primary database and la is initially the standby database. During the following switchover scenario, la will become the primary database and boston will become the standby database.

This scenario assumes that the primary and standby databases have been previously created and initialized.

See Also:

Section 5.3.3, Section 6.1, and Section 6.2 

6.6.1 Step 1: End Read or Update Activity on the Primary and Standby Databases.

Exclusive database access is required by the DBA before beginning a switchover operation. Ask users to log off the primary and standby databases or query the V$SESSION view to identify users that are connected to the databases and close all open sessions except the SQL*Plus session from which you are going to issue the switchover command.

See Also:

Oracle9i Database Administrator's Guide for more information on managing users 

6.6.2 Step 2: Prepare the Primary Database for Switchover.

On the primary database, boston, execute the following statement:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

This statement does the following:

6.6.3 Step 3: Shut Down and Start Up the Former Primary Instance Without Mounting the Database.

Execute the following statement on boston:

SQL> SHUTDOWN NORMAL;
SQL> STARTUP NOMOUNT;

6.6.4 Step 4: Mount the Former Primary Database in the Standby Database Role.

Execute the following statement on boston:

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

6.6.5 Step 5: Prepare the Former Standby Database to Switch to the Primary Database Role.

Execute the following statement on la:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

This statement does the following:

6.6.6 Step 6: Shut Down the Database.

Execute the following statement on la:

SQL> SHUTDOWN;

6.6.7 Step 7: Start Up the Database in the Primary Role.

Execute the following statement on la:

SQL> STARTUP;         

6.6.8 Step 8: Put the Standby Database in Managed Recovery Mode.

Execute the following statement on the standby database, boston, to place it in managed recovery mode:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

6.6.9 Step 9: Start Archiving Logs from the Primary Database to the Standby Database.

Execute the following statement on the primary database, la:

SQL> ALTER SYSTEM ARCHIVE LOG START;
SQL> ALTER SYSTEM SWITCH LOGFILE;

6.7 Scenario 7: Configuring Client Application Failover

When a standby database is activated, it becomes a primary database, and is no longer capable of serving as a standby database. Client applications must redirect their connections from the original primary database to the newly activated primary database. This scenario describes the following ways to set up client failover:

6.7.1 Local TNS Configuration

The tnsnames.ora file can be configured for multiple addresses. In a local TNS configuration, at least one of the addresses should be the address of a standby site. Modify the tnsnames.ora file at each client site to ensure that an address for a standby site has been supplied. The tnsnames.ora file is typically located in the $ORACLE_HOME/network/admin directory. You can assign multiple addresses to one TNS name and use the FAILOVER option.

See Also:

Oracle9i Net Services Administrator's Guide for details about how to set multiple addresses and how to use the FAILOVER option 

The following example shows an entry that has an address for a standby site in addition to the address for the primary site:

ProductDB = (   DESCRIPTION=
                (FAILOVER=ON)
                (LOAD_BALANCE=OFF)
                (ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=prmyhost.foo.com))
                (ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=stbyhost.foo.com))
                (CONNECT_DATA=(SID=db1))
            )

In this example, the primary database is located at prmyhost.foo.com, and the standby database is located at stbyhost.foo.com. When the client application connects to ProductDB, it tries to send a connection request to prmyhost.foo.com first. If there is no response, the client application tries to send another connection request to stbyhost.foo.com. When the primary database is down and the standby database is activated, the client application can connect to the new primary database automatically.

If the primary database fails after the connection has been established, the client application will not automatically direct the remaining request to the newly activated primary database. You must establish the connection to the ProductDB database again.

6.7.2 Oracle Names Server Configuration

If you are using an Oracle Names Server, you can change the TNS name settings on the server. You can assign multiple addresses to one TNS name and use the FAILOVER option.

See Also:

Oracle9i Net Services Administrator's Guide for details about how to set multiple addresses and how to use the FAILOVER option on an Oracle Names server 

The format for setting up the TNS name on the Oracle Names server is the same as the format for the local tnsnames.ora file. Therefore, the example in Section 6.7.1 also applies to the Oracle Names server configuration.

6.7.3 Transparent Application Failover (TAF) Configuration

The following configurations require that the client reconnect to the database server when the primary database fails over to the standby database:

However, if you are using an Oracle Call Interface (OCI) client, you can use transparent application failover (TAF). TAF is the ability of applications to automatically reconnect to the database if the connection fails. If the client application is not involved in a database transaction, then users may not notice the failure of the primary database server.

See Also:

Oracle9i Net Services Administrator's Guide for details on how to configure TAF 

The following example shows address information for the ProductDB database and the STANDBY1 database:

ProductDB=( DESCRIPTION=
            (ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=prmyhost.foo.com))
            (CONNECT_DATA=(SID=db1)(FAILOVER_MODE=(BACKUP=Standby1)
                                                  (TYPE=session)
                                                  (METHOD=basic)))
          )
Standby1 =( DESCRIPTION=
            (ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=stbyhost.foo.com))
            (CONNECT_DATA=(SID=db1))
          )

Sequence of events:

  1. Client application is connected to the ProductDB database.

  2. The primary database in prmyhost.foo.com fails.

  3. The standby database is activated as the new primary database.

  4. When the client application fails to connect to prmyhost.foo.com, it uses the database specified with the BACKUP parameter in the FAILOVER_MODE clause, and automatically connects to stbyhost.foo.com.

6.7.4 Manual Network Configuration

Instead of setting the configurations so that the client can automatically fail over to the new primary database, you can always choose to manually modify the network settings after the standby database is activated.

You can modify the local tnsnames.ora file. Redirect the TNS name pointing to the original primary database to the newly activated primary database. For example, assume the original TNS setting is as follows:

ProductDB=( DESCRIPTION=
               (ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=prmyhost.foo.com))
                (CONNECT_DATA=(SID=db1))
          )

When the primary database on prmyhost.foo.com fails, and the standby database in stbyhost.foo.com is activated, causing it to become the new primary database, you need to edit the tnsnames.ora file and change the entry to the following:

ProductDB=( DESCRIPTION=
               (ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=stbyhost.foo.com))
                (CONNECT_DATA=(SID=db1))
          )

You do not need to change your client application; subsequent connections to the primary database will be sent to the new primary database on stbyhost.foo.com.

If you are using an Oracle Names server, make a similar change for the corresponding entry. All clients using this Oracle Names server will send their subsequent connections to the new primary database on stbyhost.foo.com.

You can also change the settings on the DNS server. Change the settings for the domain name, which is used by the clients to locate the primary database.

For example, assume the following:

Item  Value 

Domain name 

ProductDB.foo.com 

tnsnames.ora entry 

ProductDB=(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp) (PORT=1521) (HOST=ProductDB.foo.com))
(CONNECT_DATA=(SID=db1)
)
 

DNS server entry for domain name 

ProductDB.foo.com IN A 136.1.23.15 

IP address of stbyhost.foo.com 

128.3.151.63 

Change the DNS server entry for the domain name in the DNS server to the following:

ProductDB.foo.com   IN  A   128.3.151.63

After you change the DNS settings, not all clients know about the change immediately. The old DNS settings may be cached somewhere, causing some clients to continue to use the old settings. The old settings must be replaced with the new settings.

6.8 Scenario 8: Recovering After a Network Failure

When a standby database is in managed recovery mode, the standby database automatically applies archived redo logs as it receives them from the primary database. When the network goes down, automatic archival from the primary database to the standby database stops.

If the standby database is specified as an optional archive destination, then the primary database continues to operate normally.

When the network is up and running again, automatic archival of the archived redo logs from the primary database to the standby database resumes. However, if the standby database is specified as an optional archive destination, and a log switch occurred at the primary site, the standby database has an archive gap for the time when the network was down. The archive gap is automatically detected and resolved when managed recovery mode is enabled (when FAL_SERVER and FAL_CLIENT are defined in the initialization parameter file).

See Also:

Section 4.5 for information on archive gaps 

If the standby database is specified as a mandatory archive destination, then the primary database will not archive any redo logs until the network failure is resolved and the primary database is able to archive to the standby site.

The primary database may eventually stall if the network problem is not fixed in a timely manner, because the primary database will not be able to switch to an online redo log that has not been archived. You can issue the following SQL query to determine whether the primary database stalled because it was not able to switch to an online redo log:

SELECT decode(COUNT(*),0,'NO','YES') "switch_possible"
FROM V$LOG
WHERE ARCHIVED='YES';

If the output from the query displays "Yes," a log switch is possible; if the output displays "No," a log switch is not possible.

It is important to specify a local directory at the primary site as a mandatory archive destination, so that all of the archived redo logs reside on the same system as the primary database. When the primary system is unreachable, and the primary database is part of a multiple standby database configuration, you can try to identify the archived redo logs at the other standby sites.

This scenario describes how to recover after a network failure.

6.8.1 Step 1: Identify the Network Failure.

The V$ARCHIVE_DEST view contains the network error and identifies which standby database cannot be reached. On the primary database, issue the following SQL statement for the archived log destination that experienced the network failure. For example:

SQL> SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID = 2;

DEST_ID    STATUS
---------- ---------
ERROR
-----------------------------------------------------------------
        2  ERROR
ORA-12224: TNS:no listener

The query results show there are errors archiving to the standby database, and the cause of the error as TNS:no listener. You should check whether the listener on the standby site is started. If the listener is stopped, then start it.

6.8.2 Step 2: Prevent the Primary Database from Stalling.

If you cannot solve the network problem quickly, and if the standby database is specified as a mandatory destination, try to prevent the database from stalling by doing one of the following:

6.8.3 Step 3: Archive the Current Redo Log.

On the primary database, archive the current redo log:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

When the network is back up again, log apply services can detect and resolve the archive gaps automatically if you place the standby database in managed recovery mode.

6.9 Scenario 9: Re-Creating a Standby Database

This scenario describes the case where you have failed over to a standby database and have begun using it as a normal primary database. After a period of time, you decide you want to fail back to the original primary system and make the primary database the standby database again.

6.9.1 Step 1: Create a Standby Database at the Original Primary Site.

  1. Copy the original standby initialization parameter file from the original standby site to the original primary site as follows:

    % rcp STBYinit.ora PRMYHOST:fallback.ora
    

    The fallback.ora file will become the standby initialization parameter file for the standby database at the original primary site.

  2. On the original primary site, configure the fallback.ora file. You need to modify the following parameters:

    Parameter  Value 

    CONTROL_FILES 

    /fs2/oracle/stdby/cf1.ctl 

    LOCK_NAME_SPACE 

    fallback 

    LOG_ARCHIVE_FORMAT 

    r_%t_%s.arc 

    STANDBY_ARCHIVE_DEST 

    /oracle/dbs/ 

    LOG_ARCHIVE_DEST_1 

    'LOCATION=/oracle/dbs/' 

    DB_FILE_NAME_CONVERT 

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

    LOG_FILE_NAME_CONVERT 

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

    The fallback.ora file looks as follows:

    #
    #parameter file fallback.ora
    #
    
    db_name=primary1               #The same as PRMYinit.ora
    
    control_files=/fs2/oracle/stdby/cf1.ctl
    lock_name_space=fallback;
    
    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=/oracle/work/tkinit.ora
    
    # specific parameters for standby database
    log_archive_format = r_%t_%s.arc
    standby_archive_dest=/oracle/dbs/
    log_archive_dest_1='LOCATION=/oracle/dbs/'
    log_archive_dest_state_1 = ENABLE
    db_file_name_convert=('/fs2/oracle/stdby','/oracle/dbs')
    log_file_name_convert=('/fs2/oracle/stdby','/oracle/dbs')
    log_archive_start=true
    log_archive_trace=127
    
  3. On the original primary site, create or modify the primary initialization parameter file.

    You need to supply appropriate values for the LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_STATE_1 initialization parameters.


    Note:

    Section 6.2.7 suggested that you make a copy of the standby database initialization parameter file. If you made a copy, then you can modify the copy in this step. 


  4. On the original standby site, back up the primary database datafiles.

  5. On the original standby site, create the standby database control file.

  6. Copy the primary database datafiles and the standby database control file from the original standby site to the original primary site.

  7. Archive the current online redo log and shut down the primary database to prevent further modifications as follows:

    SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
    SQL> SHUTDOWN IMMEDIATE;
    
  8. Copy the archived redo logs that were generated after the primary database datafiles were backed up from the original standby site to the original primary site, as follows:

    % rcp /fs2/oracle/stdby/stdby_1_102.arc prmyhost:/oracle/dbs/r_1_102.arc
    % rcp /fs2/oracle/stdby/stdby_1_103.arc prmyhost:/oracle/dbs/r_1_103.arc
    

6.9.2 Step 2: Fail Over to the Standby Database at the Original Primary Site.

  1. On the original primary site, start and mount the standby database:

    SQL> CONNECT sys/sys_password AS SYSDBA
    SQL> STARTUP NOMOUNT PFILE=fallback.ora;
    SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
    
  2. Fail over to the standby database:

    SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
    SQL> SHUTDOWN IMMEDIATE;
    
  3. Start the primary database at the original primary site:

    SQL> STARTUP PFILE=PRMYinit.ora;
    
  4. Configure the network settings to enable client applications to access the primary database.

6.9.3 Step 3: Create a New Standby Database at the Original Standby Site.

Create a new standby database at the original standby site.

See Also:

Section 6.2 for the steps to create a standby database 

6.10 Scenario 10: Standby Database with No Ongoing Recovery

This scenario describes what the DBA needs to do when archived online redo logs are automatically copied to the standby site, but they are not automatically applied to the standby database.

For example, suppose a standby database is set up on a site running a Web server. The workload for the site is very heavy, and you do not want to add workload to the site by automatically applying redo logs as they arrive from the primary site. You decide to let the site receive archived redo logs without applying them. Thus, the system resources for applying the redo logs will be saved. Later, if you decide to open the standby database as a read-only database to perform some queries, or switch over the standby database as a primary database, you can stop the Web server and apply all of the redo logs at one time.

This scenario assumes that you already have a standby database set up. Section 6.2 describes how to set up a standby database.

The listener for the standby database should be started. The standby database should be started and mounted, but not in recovery mode. (Thus, the time and resources for applying redo logs will be saved.)

This scenario is similar to Section 6.2. However, in Section 6.2, the standby database is in managed recovery mode. When a standby database is in managed recovery mode, it automatically applies archived redo logs received from the primary site.

6.10.1 Managing a Standby Database with No Ongoing Recovery

When the archived redo logs are not immediately applied to the standby database, there is no ongoing recovery at the standby site. This section describes the tasks a DBA needs to consider when managing a standby database with no ongoing recovery. This section covers the following topics:

6.10.1.1 Copying a Datafile to the Primary Database

When a datafile is copied to the primary site, you need to decide what action to take on the standby site. You have the following options:

6.10.1.2 Responding to a Change in the Primary Database Control File

When the control file on the primary site has been altered, if you want to account for it on the standby site, you should apply the redo logs at the standby site by issuing the following statement:

SQL> RECOVER AUTOMATIC STANDBY DATABASE;

After you apply the redo logs at the standby site, refresh the standby database control file.

See Also:

Section 6.3.6 

6.10.1.3 Responding When the NOLOGGING Clause Is Specified

To recover after the NOLOGGING clause is specified at the primary site, you must do the following:

  1. Apply the redo logs at the standby site by issuing the following statement:

    SQL> RECOVER AUTOMATIC STANDBY DATABASE;
    
  2. Copy the affected datafiles and standby database control file to the standby site.

    See Also:

    Section 6.4 

If you can afford to lose the changes incurred by specifying NOLOGGING at the primary site, you may choose to do nothing.

6.10.2 Activating a Standby Database with No Ongoing Recovery

Before you activate the standby database, you should apply all applicable redo logs. You must resolve any gaps in the redo log sequence before you activate the standby database, as outlined in the following steps:

  1. Query the V$ARCHIVE_GAP view on the standby database as follows:

    SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE#
    FROM V$ARCHIVE_GAP;
    
    THREAD#    LOW_SEQUENCE#   HIGH_SEQUENCE#
    ---------- -------------   --------------
             1            90               92
    

    The archive gap is the LOW_SEQUENCE# to the HIGH_SEQUENCE#. In this example, the gap is 90, 91, and 92 for thread 1. If no gap is selected in this step, go to step 5.

  2. Identify the archived redo logs that need to be copied from the primary site to the standby site.

    Obtain the filenames of the logs in the archive gap by performing a query on the V$ARCHIVED_LOG view on the primary database as follows:

    SELECT name FROM V$ARCHIVED_LOG 
    WHERE thread#=1 AND sequence#<=92 AND sequence#>=90;
    
    NAME
    -----------------------------------------
    /oracle/dbs/r_1_90.arc
    /oracle/dbs/r_1_91.arc
    /oracle/dbs/r_1_92.arc
    
  3. Copy the logs in the archive gap from the primary database to the standby database as follows:

    % rcp /oracle/dbs/r_1_90.arc stbyhost:/fs2/oracle/stdby/stdby_1_90.arc
    % rcp /oracle/dbs/r_1_91.arc stbyhost:/fs2/oracle/stdby/stdby_1_91.arc
    % rcp /oracle/dbs/r_1_92.arc stbyhost:/fs2/oracle/stdby/stdby_1_92.arc
    

    It is important to specify a local directory at the primary site as a mandatory archive destination so that all of the archived redo logs reside on the same system as the primary database. When the primary system is unreachable, and the primary database is part of a multiple standby database configuration, you can try to identify the archived redo logs at the other standby sites.

  4. On the standby database, issue the following statement to manually apply the logs in the archive gap:

    SQL> RECOVER AUTOMATIC STANDBY DATABASE;
    

    Repeat steps 1 through 4 until there are no more gaps.

  5. Activate the standby database by issuing the following statement:

    SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
    
  6. Shut down the standby database instance as follows:

    SQL> SHUTDOWN IMMEDIATE;
    
  7. Start the new primary instance.

    If necessary, build the parameter file for the new primary instance. You can build it from the parameter file for the standby database. Then, you can issue the following statement at the standby database:

    SQL> STARTUP PFILE=FailOver.ora;
    

6.11 Scenario 11: Standby Database with a Time Lag

By default, in managed recovery mode, the standby database automatically applies redo logs when they arrive from the primary database. But in some cases, you may not want the logs to be applied immediately, because you want to create a time lag between the archiving of a redo log at the primary site and the application of the log at the standby site. A time lag can protect against the transfer of corrupted or erroneous data from the primary site to the standby site.

For example, suppose you run a batch job every night on the primary database. Unfortunately, you accidently ran the batch job twice and you did not realize the mistake until the batch job completed for the second time. Ideally, you need to roll back the database to the point in time before the batch job began. A primary database that has a standby database with a time lag (for example, 8 hours) could help you to recover. You could activate the standby database with the time lag and use it as the new primary database.

To create a standby database with a time lag, use the DELAY attribute of the LOG_ARCHIVE_DEST_n initialization parameters in the primary database initialization parameter file. The archived redo log files are still automatically copied from the primary site to the standby site, but the log files are not immediately applied to the standby database. The log files are applied when the specified time interval has expired.

This scenario use a 4-hour time lag and covers the following topics:

Readers of this scenario are assumed to be familiar with the procedures for creating a typical standby database. The details have been omitted from the steps outlined in this scenario.

See Also:

Section 6.2 for details of normal standby database setup 

6.11.1 Creating a Standby Database with a Time Lag

Perform the following steps to create a standby database with a time lag:

  1. Back up the datafiles and create the standby database control file at the primary site.

  2. Copy the datafiles and the standby database control file to the standby site.

  3. Configure the tnsnames.ora and listener.ora network files.

  4. Start the listener on the standby site.

  5. Configure the standby initialization parameter file.

    Edit the STANDBY_ARCHIVE_DEST parameter.

    You must use the directory specified by the STANDBY_ARCHIVE_DEST parameter when performing managed recovery.

    For example, assume you have set the parameter to the following value:

    STANDBY_ARCHIVE_DEST=/fs2/oracle/stdby_log/
    
  6. Mount the standby database.

  7. Configure the primary initialization parameter file.

    Edit the LOG_ARCHIVE_DEST_n initialization parameter to include the DELAY keyword.

    For example, to specify a 4-hour delay, set the parameter as follows:

    LOG_ARCHIVE_DEST_2 DELAY=240
    

    The DELAY attribute indicates that the archived redo logs at the standby site are not available for recovery until the 4-hour time interval has expired. The time interval (expressed in minutes) starts when the archived redo logs are successfully transmitted to the standby site.

6.11.2 Managing a Standby Database with a Time Lag

The runtime scenario of a standby database with a time lag is slightly different from a standby database with no time lag, because the application of the online redo logs lags behind.

As the DBA, you need to keep the following tasks in mind when managing a standby database with a time lag:

6.11.3 Rolling Back the Database to a Specified Time

In the case stated at the beginning of this scenario, you may want to take advantage of the time lag, and get a primary database whose status is a specified time (for example, 4 hours) before the current primary database. You can activate the appropriate time-lagged standby database as follows:

  1. Activate the standby database by issuing the following statement:

    SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
    
  2. Shut down the standby database instance:

    SQL> SHUTDOWN IMMEDIATE;
    
  3. Start the new primary instance:

    SQL> STARTUP PFILE=FailOver.ora;
    

6.11.4 Bypassing the Time Lag and Activating the Standby Database

If you do not want to take advantage of the time lag, you can activate the standby database as a normal standby database with no time lag as follows:

  1. Apply all of the archived redo logs:

    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;
    
  2. Activate the standby database by issuing the following statement:

    SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
    
  3. Shut down the standby database instance:

    SQL> SHUTDOWN IMMEDIATE;
    
  4. Start the new primary instance:

    SQL> STARTUP PFILE=FailOver.ora;
    

6.12 Scenario 12: Using a Standby Database to Back Up the Primary Database

This scenario describes using a physical standby database to back up the primary database. This allows the standby site to offload the task of backup at the primary site. The backup at the standby site can be done while the standby database is in the managed recovery mode. When the primary database needs to be restored, you can use the backup created at the standby site.

6.12.1 Step 1: Back Up the Standby Database.

The standby database can be used to back up the datafiles and the archived redo logs. The primary control file must be backed up at the primary site because the standby database uses a different standby control file. Because the control file is usually much smaller than the datafiles, backing up the control file at the primary site does not significantly affect the performance of the primary database.

To back up a database at the standby site:

  1. At the standby site, start the Recovery Manager utility (RMAN) with the NOCATALOG option to back up the standby datafiles and log files. Assume that stby is the connect string of the standby database. For example:

    % rman target sys/change_on_install@stby nocatalog
    connected to target database: ADE3 (DBID=1417165739)
    using target database controlfile instead of recovery catalog
    
  2. Back up the standby datafiles. You can perform the backup while the standby database is still in managed recovery mode.

    RMAN> run {
    2> allocate channel c1 type disk;
    3> backup database;
    4> }
    
    allocated channel: c1
    channel c1: sid=13 devtype=DISK
    
    Starting backup at 07-NOV-00
    channel c1: starting full datafile backupset
    channel c1: specifying datafile(s) in backupset
    input datafile fno=00001 name=/oracle/dbs/s3t_db1.dbf
    channel c1: starting piece 1 at 07-NOV-00
    channel c1: finished piece 1 at 07-NOV-00
    piece handle=/oracle/dbs/04c9t2ki_1_1 comment=NONE
    channel c1: backup set complete, elapsed time: 00:00:35
    Finished backup at 07-NOV-00
    
    Starting Control File Autobackup at 07-NOV-00
    warning - controlfile is not current, controlfile autobackup skipped
    Finished Control File Autobackup at 07-NOV-00
    released channel: c1
    
  3. Back up the archived redo logs.

    After you back up a database, Oracle Corporation recommends that you record the current sequence number and thread number applied at the standby site. This is important because the archived logs, after this backup, may be needed to restore the database at the primary site if the primary database loses its local archived logs.

    For example, suppose we have only one thread and the sequence number at the time of database backup is 15. After several new logs are archived to the standby site, you can back up these archived logs using the following commands:

    RMAN> run {
    2> allocate channel c1 type disk;
    3> backup archivelog from logseq 15;
    4> }
    
    allocated channel: c1
    channel c1: sid=9 devtype=DISK
    
    Starting backup at 07-NOV-00
    channel c1: starting archive log backupset
    channel c1: specifying archive log(s) in backup set
    input archive log thread=1 sequence=15 recid=15 stamp=413043150
    input archive log thread=1 sequence=16 recid=16 stamp=413043168
    input archive log thread=1 sequence=17 recid=17 stamp=413043433
    input archive log thread=1 sequence=18 recid=18 stamp=413043442
    input archive log thread=1 sequence=19 recid=19 stamp=413043450
    input archive log thread=1 sequence=20 recid=20 stamp=413043454
    channel c1: starting piece 1 at 07-NOV-00
    channel c1: finished piece 1 at 07-NOV-00
    piece handle=/oracle/dbs/05c9t3c9_1_1 comment=NONE
    channel c1: backup set complete, elapsed time: 00:00:03
    Finished backup at 07-NOV-00
    
    Starting Control File Autobackup at 07-NOV-00
    warning - controlfile is not current, controlfile autobackup skipped
    Finished Control File Autobackup at 07-NOV-00
    released channel: c1
    
  4. View the backup set using the list command:

    RMAN> list backup;
    
    List of Backup Sets
    ===================
    
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    4       Full    104M       DISK        00:00:29     07-NOV-00
            BP Key: 4   Status: AVAILABLE   Tag:
            Piece Name: /oracle/dbs/04c9t2ki_1_1
      List of Datafiles in backup set 4
      File LV Type Ckp SCN    Ckp Time  Name
      ---- -- ---- ---------- --------- ----
      1       Full 73158      07-NOV-00
    /oracle/dbs/s3t_db1.dbf
    
    BS Key  Device Type Elapsed Time Completion Time
    ------- ----------- ------------ ---------------
    5       DISK        00:00:02     07-NOV-00
            BP Key: 5   Status: AVAILABLE   Tag:
            Piece Name: /oracle/dbs/05c9t3c9_1_1
    
      List of Archived Logs in backup set 5
      Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
      ---- ------- ---------- --------- ---------- ---------
      1    15      73139      07-NOV-00 73156      07-NOV-00
      1    16      73156      07-NOV-00 73158      07-NOV-00
      1    17      73158      07-NOV-00 73163      07-NOV-00
      1    18      73163      07-NOV-00 73165      07-NOV-00
      1    19      73165      07-NOV-00 73166      07-NOV-00
      1    20      73166      07-NOV-00 73167      07-NOV-00
    
  5. You can also use the RMAN BACKUP TABLESPACE and BACKUP DATAFILE commands to back up individual tablespaces and datafiles.

6.12.2 Step 2: Restore the Backup at the Primary Site.

To restore the backup at the primary site, take the following steps:

  1. Set up a recovery catalog database on the primary site.

    See Also:

    Oracle9i Recovery Manager User's Guide for instructions on setting up a catalog database 

  2. Ensure that the primary database is in the mounted state.

  3. Move the standby control file and the backup pieces to the primary site.


    Note:

    If the backup is on disk, the backup pieces must reside under the same directory at the primary site as they do at the standby site. This is a current restriction of RMAN. This is not an issue if the backup is on tape, which is the preferred method. 


  4. Resynchronize the catalog database with the standby control file.

    Assume that prmy is the connect string of the primary database, and rcat is the connect string of the recovery catalog database.

    % rman target sys/change_on_install@prmy catalog rman/rman@rcat
    
    connected to target database: ADE3 (DBID=1417165739)
    connected to recovery catalog database
    
    RMAN> resync catalog from controlfilecopy 'scf3.ctl';
    
  5. List the backup set obtained by the catalog database after the resynchronization:

    RMAN> list backup;
    
    starting full resync of recovery catalog
    full resync complete
    
    List of Backup Sets
    ===================
    
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    182     Full    104M       DISK        00:00:29     07-NOV-00
            BP Key: 184   Status: AVAILABLE   Tag:
            Piece Name: /oracle/dbs/04c9t2ki_1_1
      List of Datafiles in backup set 182
      File LV Type Ckp SCN    Ckp Time  Name
      ---- -- ---- ---------- --------- ----
      1       Full 73158      07-NOV-00 /oracle/dbs/t_db1.dbf
    
    BS Key  Device Type Elapsed Time Completion Time
    ------- ----------- ------------ ---------------
    183     DISK        00:00:02     07-NOV-00
            BP Key: 185   Status: AVAILABLE   Tag:
            Piece Name: /oracle/dbs/05c9t3c9_1_1
    
      List of Archived Logs in backup set 183
      Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
      ---- ------- ---------- --------- ---------- ---------
      1    15      73139      07-NOV-00 73156      07-NOV-00
      1    16      73156      07-NOV-00 73158      07-NOV-00
      1    17      73158      07-NOV-00 73163      07-NOV-00
      1    18      73163      07-NOV-00 73165      07-NOV-00
      1    19      73165      07-NOV-00 73166      07-NOV-00
      1    20      73166      07-NOV-00 73167      07-NOV-00
    
  6. Restore the primary database:

    RMAN> run {
    2> allocate channel c1 type disk;
    3> restore database;
    4> }
    
    allocated channel: c1
    channel c1: sid=11 devtype=DISK
    
    Starting restore at 07-NOV-00
    
    channel c1: starting datafile backupset restore
    channel c1: specifying datafile(s) to restore from backup set
    restoring datafile 00001 to /oracle/dbs/t_db1.dbf
    channel c1: restored backup piece 1
    piece handle=/oracle/dbs/04c9t2ki_1_1 tag=null
    params=NULL
    channel c1: restore complete
    Finished restore at 07-NOV-00
    released channel: c1
    
  7. Restore the archived log.

    After restoring the database, you can try to open the database:

    SQL> ALTER DATABASE OPEN;
    alter database open
    *
    ERROR at line 1:
    ORA-01113: file 1 needs media recovery
    ORA-01110: data file 1: '/oracle/dbs/t_db1.dbf'
    

    The ORA-01113 error indicates that media recovery is required on the database before you can open it. Issue the manual recovery statement, RECOVER DATABASE, as follows:

    SQL> RECOVER DATABASE;
    ORA-00279: change 73158 generated at 11/07/2000 14:12:47 needed for
    thread 1
    ORA-00289: suggestion : /oracle/dbs/db11_17.dbf
    ORA-00280: change 73158 for thread 1 is in sequence #17
    
    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    

    The resulting error messages indicate that the database needs log sequence #17 for recovery. You can provide logs in several ways:

    • If the primary database did not lose its local archived logs, then you can use these log files.

    • You can move the standby archived logs back to the primary database. In this case, some renaming may be necessary.

    • If you already backed up the archived log at the standby site, you can move the backup piece to the primary site, and restore these archived logs, as shown in the following example:

      RMAN> run {
      2> allocate channel c1 type disk;
      3> restore archivelog from logseq 15;
      4> }
      
      allocated channel: c1
      channel c1: sid=11 devtype=DISK
      
      Starting restore at 07-NOV-00
      
      channel c1: starting archive log restore to default destination
      channel c1: restoring archive log
      archive log thread=1 sequence=15
      channel c1: restoring archive log
      archive log thread=1 sequence=16
      channel c1: restoring archive log
      archive log thread=1 sequence=17
      channel c1: restoring archive log
      archive log thread=1 sequence=18
      channel c1: restoring archive log
      archive log thread=1 sequence=19
      channel c1: restoring archive log
      archive log thread=1 sequence=20
      channel c1: restored backup piece 1
      piece handle=/oracle/dbs/05c9t3c9_1_1 tag=null
      params=NULL
      channel c1: restore complete
      Finished restore at 07-NOV-00
      released channel: c1
      

    After restoring the archived logs, you can recover the database and then open it.


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

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback