Standby Database Setup Guidelines

Overview

This section describes the setup of a symmetrical physical standby database environment.

Configuration Steps

The following configuration steps will be covered to setup the physical standby database:

Software Installation

Oracle Networking Preparation

Primary Database Preparation

Flashback Recovery Area

Standby Database Creation

Enable Standby Recovery

Software Installation

Oracle Software Directory Setup

Since this environment is being created as a symmetrical installation, all directories should mirror the primary database directories.

The following is the basic directory structure:

Admin directories:

Database Backup

A new level 0 backup should be taken of the primary database with a standby backup control file.

Database Directories

Data file directories:

Control file directories:

Redo log directories:

Standby archive destination:

Oracle Networking Preparation

On the primary and standby database installation there should be network entries that specify how to connect to each database. These are used to transfer archivelog files to the standby database (whichever machine that may be) and to connect to the databases themselves. There should also be explicit entries in the listener.ora file to register the database with the listener and optionally a network entry to specify SQL*Net access to the standby database.

The following examples create a symmetrical environment so that switching back and forth between primary and standby database is transparent to the networking files.

Primary Database

listener.ora File

On the primary database the following entries should be added to the listener.ora file:

# LISTENER.ORA Network Configuration File:
# /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = dg-1.appsdba.com)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DBSID.dg.appsdba.com)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME = DBSID)
    )
  )

tnsnames.ora File

The following example shows the primary and standby database entries in the tnsnames.ora file:

DBSID_DG1 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = dg-1.appsdba.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = DBSID.dg.appsdba.com)

    )

  )

 

DBSID_DG2 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = dg-2.appsdba.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = DBSID.dg.appsdba.com)

    )

  )

 

DBSID =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = DBSID))

      (ADDRESS = (PROTOCOL = TCP)(HOST = dg-1.appsdba.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = DBSID.dg.appsdba.com)

    )

  )

Standby Database

listener.ora File

On the standby database the following entries should be added to the listener.ora file:

# LISTENER.ORA Network Configuration File:

# /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = dg-2.appsdba.com)(PORT = 1521))

      )

    )

  )

 

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = DBSID.dg.appsdba.com)

      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)

      (SID_NAME = DBSID)

    )

  )

tnsnames.ora File

The following example shows the standby database entry in the standby server's tnsnames.ora file:

DBSID_DG1 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = dg-1.appsdba.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = DBSID.dg.appsdba.com)

    )

  )

 

DBSID_DG2 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = dg-2.appsdba.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = DBSID.dg.appsdba.com)

    )

  )

 

DBSID =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = DBSID))

      (ADDRESS = (PROTOCOL = TCP)(HOST = dg-2.appsdba.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = DBSID.dg.appsdba.com)

    )

  )

Primary Database Preparation

Enable Forced Logging

Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement:

SQL> ALTER DATABASE FORCE LOGGING;

This prevents the use of the "nologging" feature, which would not be replicated in the redo logs, and therefore applied on the standby database.

Create A Password File

This is required to enable RMAN to create the standby database. After creating the password file with the orapwd utility, the following init.ora parameter must be set:

remote_login_passwordfile='EXCLUSIVE'

Standby Redo Log Files

Standby redo log files are required for higher protection levels where LGWR is used to write redo records. There is no downside to creating them for both the primary and standby and this allows a faster switchover back to the primary. They must be created as the same size, number of groups and members on both the primary and the standby.

We recommend a naming convention of srl[mm][n].rdo, where "srl" is standby redo log, [mm] is 01,02,… for group, and [n] is a,b,c,… for each member.

SQL> show parameter standby_file_management

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

standby_file_management              string      auto

SQL> alter system set standby_file_management=manual;

      

System altered.

 

SQL> show parameter standby_file_management

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

standby_file_management              string      MANUAL

 

SQL> alter database add standby logfile '/u01/oradata/DBSID/srl01a.rdo' size 100m;

 

Database altered.

 

SQL> alter database add standby logfile '/u01/oradata/DBSID/srl02a.rdo' size 100m;

 

Database altered.

 

SQL> alter database add standby logfile '/u01/oradata/DBSID/srl03a.rdo' size 100m;

 

Database altered.

 

SQL> alter database add standby logfile '/u01/oradata/DBSID/srl04a.rdo' size 100m;

 

Database altered.

 

SQL> alter system set standby_file_management=auto;

      

System altered.

 

SQL>

Create A Standby Controlfile (Optional)

A standby controlfile from the primary database is required when the standby option is specified:

SQL> alter database create standby controlfile as '<file>';

where <file> is the name of the standby controlfile.

If using RMAN then this step can be included as part of the backup.

RMAN Backup With Standby Controlfile

The following option was added to the RMAN "backup database" command to insure that a controlfile was available in standby format for RMAN to create the standby database. This is important to also allow for future re-instantiation of the standby database through RMAN.

CONFIGURE CONTROLFILE AUTOBACKUP ON;

Appended to the backup command:

include current controlfile for standby

Configure Database init.ora Parameters

The following init.ora parameters should be set. These should match the standby database parameters to insure symmetric role transitions:

archive_lag_target=0

db_name = DBSID

db_unique_name = DBSID_DG1

instance_name = DBSID

#

# Standby specific parameters

#

#db_file_name_convert =

#('/u01/oradata/DBSID','/oracle/oradata/DBSID',

#'/u01/oradata/DBSID','/oracle/oradata/DBSID',

#'/u01/oradata/DBSID','/oracle/oradata/DBSID')

#

#log_file_name_convert =

#('/u01/oradata/DBSID','/oracle/oradata/DBSID',

#'/u01/oradata/DBSID','/oracle/oradata/DBSID',

#'/u01/oradata/DBSID','/oracle/oradata/DBSID')

#

standby_archive_dest = '/u01/app/oracle/orabkup/DBSID_dg/'

standby_file_management=auto

# Deprecated in 10g

#remote_archive_enable=true

#

# Set to allow log apply services to automatically resolve archive gaps

#

fal_client=DBSID_DG1

fal_server=DBSID_DG2

#

# Set logging Parameters

#

log_archive_config='dg_config=(DBSID_DG1,DBSID_DG2)'

log_archive_dest_1 = 'LOCATION=/u01/app/oracle/admin/DBSID/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DBSID_DG1'

log_archive_dest_2='SERVICE=DBSID_DG2 ARCH SYNC NOAFFIRM delay=0

OPTIONAL max_failure=0 reopen=300 register

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBSID_DG1'

log_archive_dest_state_1='ENABLE'

log_archive_dest_state_2='DEFER'

log_archive_format = 'DBSID_%t_%s_%r.arc'

log_checkpoint_interval=10000

log_checkpoint_timeout=1800

Note: The standby_archive_dest parameter should match the log_archive_dest_n parameter in 9i and in 10g Data Guard it should not.

log_archive_dest_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ONLINE_LOGFILE,ALL_ROLES)'

log_archive_dest_2 = 'service=DBSID_dg2 LGWR NOAFFIRM delay=0

OPTIONAL max_failure=0 reopen=300 async=61440

db_unique_name="DBSID_DG2" register nonet_timeout  valid_for=(online_logfile,primary_role)'

 

#log_archive_dest_3 = 'LOCATION=USE_RECOVERY_FILE_DEST

# valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)'

 

standby_archive_dest = USE_DB_RECOVERY_FILE_DEST

Additional 10g init.ora Parameters

Add the “VALID_FOR” parameter to the current log destination:

LOG_ARCHIVE_DEST_1='location=/u01/app/oracle/admin/DBSID/arch/ VALID_FOR=(ONLINE_LOGFILE,ALL_ROLES)'

Add a second log destination for the standby database:

log_archive_dest_2='service=DBSID_DG2 ARCH SYNC NOAFFIRM delay=0

OPTIONAL max_failure=0 reopen=300 register

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=”DBSID_DG2”'

or

log_archive_dest_2='service=DBSID_DG2 LGWR ASYNC NOAFFIRM delay=0

OPTIONAL max_failure=0 reopen=300 register

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=DBSID_DG2'

Add these additional parameters:

DB_UNIQUE_NAME=DBSID_DG1

log_archive_config = "dg_config=(DBSID_DG1,DBSID_DG2)"

log_archive_dest_state_2='ENABLE'

Create An SPFILE For OEM and Data Guard Support

Oracle's Enterprise Manager and Data Guard require an spfile to fully administration of the standby database enviornment. This can be created with the following command:

SQL> create spfile from pfile;

This will create an spfile in the default $ORACLE_HOME/dbs directory and will take affect on the next database startup.

Flashback Recovery Area

Setting init.ora Parameters

When using a flash recovery area with primary/standby databases the following parameters may need to be configured:

#

# Set flash recovery area

#

db_recovery_file_dest='/u01/app/oracle/flashrecovery'

db_recovery_file_dest_size=10G

#

# Set logging parameters

#

log_archive_dest_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST

valid_for=(ONLINE_LOGFILE,ALL_ROLES)'

log_archive_dest_state_1='ENABLE'

#

# Set standby parameters

#

standby_archive_dest = 'USE_DB_RECOVERY_FILE_DEST'

NOTE: There seems to be some inconsistency with the way the Data Guard broker deals with these parameters. The log_archive_dest_1 setting requires no comma before the “valid_for” parameter, but the broker will eventually change the setting and add a comma. The broker will also null the standby_archive_dest parameter on the primary database, but will leave the setting in its configuration file.

Data Guard Broker

The “StandbyArchiveLocation” database property will be set by the Data Guard broker (for both databases if creating a symmetrical configuration) to the following:

StandbyArchiveLocation = 'LOCATION=USE_DB_RECOVERY_FILE_DEST'

Note that although the standby_archive_dest parameter does not use the "LOCATION" keyword the Data Guard broker parameter does.

Flashback Database Options – 10g

To set the database into “Flashback Database” mode the following must be enabled:

  1.   Database must be in archivelog mode

  2.  Must have a flash recovery area defined.

To enable the flashback database feature:

  1.  Mount the database:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

  2.  Set the flashback retention target:

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days

  3.  Enable the Flashback Database feature:

SQL> ALTER DATABASE FLASHBACK ON;

Standby Database Creation

Configure Oracle Server Software

The primary database and the standby database should be running the same version of Oracle software, including patchsets and security patches.

Create Standby Database Environment

Standby Database Password File

A new password file was created for the standby database instance using the orapwd utility.

orapwd file=orapwDBSID password=xxxxxx entries=20

Configure the Standby Database init.ora Parameters

The source database init.ora parameters were copied and the following added or changed:

aq_tm_processes=0

archive_lag_target=0

db_name = DBSID

db_unique_name = DBSID_DG2

instance_name = DBSID

#

control_files = ('/u01/oradata/DBSID/control01.ctl',

                 '/u02/oradata/DBSID/control02.ctl',

                 '/u03/oradata/DBSID/control03.ctl')

#

background_dump_dest = '/u01/app/oracle/admin/DBSID/bdump'

core_dump_dest = '/u01/app/oracle/admin/DBSID/cdump'

user_dump_dest = '/u01/app/oracle/admin/DBSID/udump'

#

job_queue_processes=0          

#

# Standby specific parameters

#

#db_file_name_convert =

#('/u01/oradata/DBSID','/oracle/oradata/DBSID',

#'/u01/oradata/DBSID','/oracle/oradata/DBSID',

#'/u01/oradata/DBSID','/oracle/oradata/DBSID')

#

#log_file_name_convert =

#('/u01/oradata/DBSID','/oracle/oradata/DBSID',

#'/u01/oradata/DBSID','/oracle/oradata/DBSID',

#'/u01/oradata/DBSID','/oracle/oradata/DBSID')

#

standby_archive_dest = '/u01/app/oracle/orabkup/DBSID_dg/'

standby_file_management=auto

# Deprecated in 10g

#remote_archive_enable=true

#

# Set to allow log apply services to automatically resolve archive gaps

#

fal_client=DBSID_DG2

fal_server=DBSID_DG1

#

# Set logging Parameters

#

log_archive_dest_1 = 'LOCATION=/u01/app/oracle/admin/DBSID/arch/ VALID_FOR(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DBSID_DG2'

log_archive_dest_2='SERVICE=DBSID_DG1 ARCH SYNC NOAFFIRM delay=0

OPTIONAL max_failure=0 reopen=300 register

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBSID_DG2'

log_archive_dest_state_1='ENABLE'

log_archive_dest_state_2='DEFER'

log_archive_format = 'DBSID_%t_%s_%r.arc'

log_archive_max_processes=2

log_archive_min_succeed_dest=1

log_archive_start=true

log_archive_trace=0

Note: The standby_archive_dest parameter should match the log_archive_dest_n parameter in 9i and in 10g Data Guard it should not.

10g Parameters

Oracle 10g has introduced some new parameters. Specifically:

db_unique_name

Spfile parameters

*.archive_lag_target=0
*.audit_file_dest='/u01/app/oracle/admin/DBSID/adump'
*.background_dump_dest='/u01/app/oracle/admin/DBSID/bdump'
*.compatible='10.1.0.5.0'
*.control_files='/u01/oradata/DBSID/control01.ctl',
'/u01/oradata/DBSID/control02.ctl',
'/u01/oradata/DBSID/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/DBSID/cdump'
*.db_block_size=8192
*.db_domain='dg.appsdba.com'
*.db_file_multiblock_read_count=16
*.db_name='DBSID'
*.db_unique_name='DBSID'
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DBSIDXDB)'
*.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)

(HOST=dg-1.appsdba.com)(PORT=1521)))

(CONNECT_DATA=(SERVICE_NAME=DBSID_dg1_XPT.dg.appsdba.com)

(INSTANCE_NAME=DBSID)(SERVER=dedicated)))'
*.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)

(HOST=dg-2.appsdba.com)(PORT=1521)))

(CONNECT_DATA=(SERVICE_NAME=DBSID_dg2_XPT.dg.appsdba.com)

(SERVER=dedicated)))'
*.job_queue_processes=0
*.log_archive_config='dg_config=(DBSID_dg1,DBSID_dg2)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/admin/DBSID/arch/

VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DBSID'
DBSID.log_archive_dest_1='location="/u01/app/oracle/admin/DBSID/arch/"',

'valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
*.log_archive_dest_2='service="(DESCRIPTION=

(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)

(HOST=dg-2.appsdba.com)(PORT=1521)))

(CONNECT_DATA=(SERVICE_NAME=DBSID_DG_XPT.dg.appsdba.com)

(INSTANCE_NAME=DBSID)(SERVER=dedicated)))"',

'LGWR NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=300 async=61440

db_unique_name="DBSID_DG" register nonet_timeout

valid_for=(online_logfile,primary_role)'
DBSID.log_archive_dest_3=''
*.log_archive_dest_state_2='RESET'
*.log_archive_format='DBSID_%t_%s_%r.arc'
DBSID.log_archive_format='DBSID_%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
DBSID.log_archive_trace=0
*.open_cursors=300
*.pga_aggregate_target=1048576000
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='DBSID.dg.appsdba.com','DBSID.dg.appsdba.com',

'DBSID_XPT','DBSID_dg1_XPT'
*.sga_target=1572864000
*.standby_archive_dest='/u01/app/oracle/orabkup/DBSID_stby/'
DBSID.standby_archive_dest=''
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/DBSID/udump'

 Primary Parameters

spfile=/u01/app/oracle/admin/DBSID/pfile/spfileDBSID.ora

db_unique_name=DBSID_dg1

*.fal_client='DBSID_DG1'

*.fal_server='DBSID_DG2'

Standby Parameters

spfile=/u01/app/oracle/admin/DBSID/pfile/spfileDBSID.ora

db_unique_name=DBSID_dg2

*.fal_client='DBSID_DG2'

*.fal_server='DBSID_DG1'

Create spfile

An spfile was created from the init.ora which was used to initially start the standby database. The following will create an spfile from an init.ora file and put it in the default location (i.e. $ORACLE_HOME/dbs):

SQL> create spfile from pfile;

Create the Standby Database with RMAN

The standby database is created using RMAN and the latest online backup and archivelog files from the primary database. The standby creation requires the use of a "standby controlfile" so the primary database backup is made with the additional clause "include current controlfile for standby".  

RMAN Primary Database Backup Script

$ORACLE_HOME/bin/rman target / nocatalog <<-EON

set snapshot controlfile name to '$ORACLE_BASE/admin/$ORACLE_SID/pfile/snap_$ORACLE_SID.ctl';

run {

 CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' FORMAT = '${CFG_BKUP_FILE}.%U';

 set command id to 'rman_incr0_tape';

#

 $BKUP_CMD

   incremental level = 0

   filesperset ${CFG_FILESPERSET}

   tag incr0_tape_$ORACLE_SID

   database

   include current controlfile for standby

   plus archivelog format '${CFG_ARCH_FILE}.%U'

   delete all input;

RMAN Standby Database Creation Script

In order to create the standby database with RMAN the standby instance must be started in nomount mode. RMAN will then create a "duplicate" database for "standby".

#

$ORACLE_HOME/bin/rman nocatalog target sys/xxxxxx@DBNAME log=dupdb.log <<-EOF

connect auxiliary /

#

run {

#

# Note: Set this in the init.ora as well!

#

sql 'ALTER SESSION SET NLS_DATE_FORMAT="DD-MON-YY HH24:MI"';

#set until time = '08-SEP-03 01:00';

#

duplicate target database for standby

nofilenamecheck

dorecover;

}

exit;

EOF

Backup File Availability

Depending on how backups are performed and what the media type is, additional tasks may be involved in making the backup media available for restore on a different server. RMAN will want to access backup files from the same location they were backed up to. In the case of disk this may mean copying the files to a directory that has the same name as the source, or creating a symbolic link. In the case of tape, it may mean insuring that the restore server has permisisons to access the backup tape library and has appropriate environment variables set.

Legato NSR Requirements

For Legato environments, the NSR_CLIENT parameter will need to be set to the primary server’s host name and the standby server will have to be given explicit access permission to the primary server’s backup media in the Networker Administration utility.

Standby Redo Log Files

Create the standby redo log files with the same size and at least the same number of groups (Oracle recommends adding one additional group) and members as the primary online redo log database.

SQL> alter database recover managed standby database cancel;

 

Database altered.

 

SQL> show parameter standby_file_management

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- -----------

standby_file_management              string      auto

SQL> alter system set standby_file_management=manual;

      

System altered.

 

SQL> show parameter standby_file_management

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- -----------

standby_file_management              string      MANUAL

 

SQL> alter database add standby logfile '/u01/oradata/DBSID/srl01a.rdo' size 100m;

 

Database altered.

 

SQL> alter database add standby logfile '/u01/oradata/DBSID/srl02a.rdo' size 100m;

 

Database altered.

 

SQL> alter database add standby logfile '/u01/oradata/DBSID/srl03a.rdo' size 100m;

 

Database altered.

 

SQL> alter database add standby logfile '/u01/oradata/DBSID/srl04a.rdo' size 100m;

 

Database altered.

 

SQL>

Note: If standby log files are already defined (e.g. from the primary database), then they must be dropped and recreated.

SQL> alter database drop standby logfile '/oracle/oradata/DBSID/srl01a.rdo';

 

Database altered.

 

SQL> alter database add standby logfile '/oracle/oradata/DBSID/srl01a.rdo' size 500m;

 

Database altered.

 

SQL>

Oracle recommends creating one more standby redo log file than there is on the primary database.

SQL> alter system set standby_file_management=auto;

 

System altered.

 

SQL> show parameter standby_file_management

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- -----------

standby_file_management              string      AUTO

SQL>

 

 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

 

Database altered.

 

SQL>

To review the newly create standby log files:

SQL> select * from v$standby_log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES       USED ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------

FIRST_CHANGE# FIRST_TIM LAST_CHANGE# LAST_TIME

------------- --------- ------------ ---------

         4          0          0   52428800       1024 YES UNASSIGNED

            0                      0

 

         5          0          0   52428800       1024 YES UNASSIGNED

            0                      0

 

         6          0          0   52428800       1024 YES UNASSIGNED

            0                      0

 

         7          0          0   52428800       1024 YES UNASSIGNED

            0                      0

 

 

SQL>

To enable LGWR sending of redo records on the primary database change the definition of log_archive_dest_3:

SQL> l                            

  1* select * from v$archive_dest where dest_id < 4;

 

   DEST_ID

----------

DEST_NAME

---------------------------

STATUS    BINDING   NAME_SP TARGET  ARCHIVER   SCHEDULE

--------- --------- ------- ------- ---------- --------

DESTINATION

---------------------------

LOG_SEQUENCE REOPEN_SECS DELAY_MINS NET_TIMEOUT PROCESS    REG FAIL_DATE

------------ ----------- ---------- ----------- ---------- --- ---------

FAIL_SEQUENCE FAIL_BLOCK FAILURE_COUNT MAX_FAILURE

------------- ---------- ------------- -----------

ERROR

---------------------------

ALTERNATE

---------------------------

DEPENDENCY

---------------------------

REMOTE_TEMPLATE

---------------------------

QUOTA_SIZE QUOTA_USED    MOUNTID TRANSMIT_MOD ASYNC_BLOCKS AFF TYPE

---------- ---------- ---------- ------------ ------------ --- -------

         1

LOG_ARCHIVE_DEST_1

VALID     OPTIONAL  SYSTEM  PRIMARY ARCH       ACTIVE

/u01/app/oracle/admin/DBSID/arch

        4071         300          0           0 ARCH       YES

            0          0             0           0

 

NONE

NONE

NONE

         0          0          0 SYNCHRONOUS             0 NO  PUBLIC

 

         2

LOG_ARCHIVE_DEST_2

VALID     OPTIONAL  SYSTEM  STANDBY ARCH       ACTIVE

STBY_PASDB

        4071         300          0           0 ARCH       YES

            0          0             0           0

 

NONE

NONE

NONE

         0          0          0 SYNCHRONOUS             0 NO  PUBLIC

 

         3

LOG_ARCHIVE_DEST_3

INACTIVE  OPTIONAL  SYSTEM  PRIMARY ARCH       INACTIVE

 

           0           0          0           0 ARCH       NO

            0          0             0           0

 

NONE

NONE

NONE

         0          0          0 SYNCHRONOUS             0 NO  PUBLIC

 

SQL> alter system set log_archive_dest_3='service=DBSID_DG2

lgwr async noaffirm delay=0 optional max_failure=0 reopen=300

register' scope=both;

 

System altered.

 

SQL> alter system set log_archive_dest_state_3='ENABLE' scope=both;

 

System altered.

 

SQL> select * from v$archive_dest

  2  where dest_id = 3;

 

   DEST_ID

----------

DEST_NAME

---------------------------

STATUS    BINDING   NAME_SP TARGET  ARCHIVER   SCHEDULE

--------- --------- ------- ------- ---------- --------

DESTINATION

---------------------------

LOG_SEQUENCE REOPEN_SECS DELAY_MINS NET_TIMEOUT PROCESS    REG FAIL_DATE

------------ ----------- ---------- ----------- ---------- --- ---------

FAIL_SEQUENCE FAIL_BLOCK FAILURE_COUNT MAX_FAILURE

------------- ---------- ------------- -----------

ERROR

---------------------------

ALTERNATE

---------------------------

DEPENDENCY

---------------------------

REMOTE_TEMPLATE

---------------------------

QUOTA_SIZE QUOTA_USED    MOUNTID TRANSMIT_MOD ASYNC_BLOCKS AFF TYPE

---------- ---------- ---------- ------------ ------------ --- -------

         3

LOG_ARCHIVE_DEST_3

VALID     OPTIONAL  SYSTEM  STANDBY LGWR       PENDING

DBSID_DG2

           0         300          0           0 LGWR       YES

            0          0             0           0

 

NONE

NONE

NONE

         0          0          0 ASYNCHRONOUS         2048 NO  PUBLIC

 

 

SQL>

 

alter system set log_archive_dest_3='service=DBSID_DG2

LGWR ASYNC NOAFFIRM DELAY=0 OPTIONAL max_failure=0

reopen=300 register' scope=both;

 

alter system set log_archive_dest_state_3='ENABLE' scope=both;

Note that now two definitions have been created. The "log_archive_dest_2" definition defines an archiver path to transfer archived redo log files and the "log_archive_dest_3" definition defines a log writer path. Once the log writer definition is enabled it will become effective at the next log switch. If there is a failure Oracle will fall back to the archiver definition if possible, and Oracle will attempt to use the log writer definition at each log switch.

Tempfile Creation (Optional)

Prior to Oracle 10g Release 2 RMAN will not recreate tempfiles, and any temporary tablespaces defined in the primary database will not have corresponding tempfiles associated with those tablespaces in the standby database. It is possible to “pre-create” the tempfiles since these are needed for sorting when using the “read-only” feature of physical standby databases. The following will detail the procedures to pre-create tempfiles for the standby database. This is then one less thing that has to be done when actually failing over to a standby database.

  1.  Cancel log apply services and open the physical standby database for read-only access.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> ALTER DATABASE OPEN;

  2.  Create the appropriate temporary files for the temporary tablespace(s) defined in the database. The easiest way to get these definitions is to use a text based backup controlfile from the primary database. dbcontrol has the ability to create this text based backup controlfile and this is done automatically when using the bkctrl utility on the primary database.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE –

'/u01/oradata/DBSID/temp01.dbf' SIZE 2000M;

SQL> ALTER TABLESPACE TEMP2 ADD TEMPFILE –

'/u01/oradata/DBSID/temp02.dbf' size 2000M;

  3.  Shutdown the database and restart in managed recovery mode.

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP NOMOUNT;

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Enable Standby Recovery

Enable Redo Log Shipping

SQL> alter system set LOG_ARCHIVE_DEST_state_2=enable scope=both;

 

System altered.

 

SQL> alter system switch logfile;

 

System altered.

Verify Archive Destination

SQL> select * from v$archive_dest where dest_id=2;

 

   DEST_ID

----------

DEST_NAME

---------------------------

STATUS    BINDING   NAME_SP TARGET  ARCHIVER   SCHEDULE

--------- --------- ------- ------- ---------- --------

DESTINATION

---------------------------

LOG_SEQUENCE REOPEN_SECS DELAY_MINS MAX_CONNECTIONS NET_TIMEOUT PROCESS   

REG FAIL_DATE       FAIL_SEQUENCE FAIL_BLOCK FAILURE_COUNT MAX_FAILURE

------------ ----------- ---------- --------------- ----------- ----------

--- --------------- ------------- ---------- ------------- -----------

ERROR

---------------------------

ALTERNATE

---------------------------

DEPENDENCY

---------------------------

REMOTE_TEMPLATE

---------------------------

QUOTA_SIZE QUOTA_USED    MOUNTID TRANSMIT_MOD ASYNC_BLOCKS AFF TYPE   

VALID_NOW        VALID_TYPE      VALID_ROLE   DB_UNIQUE_NAME

---------- ---------- ---------- ------------ ------------ --- -------

---------------- --------------- ------------ ------------------------------

VER

---

         2

LOG_ARCHIVE_DEST_2

VALID     OPTIONAL  SYSTEM  STANDBY LGWR       ACTIVE

DBSID_DG2

           7         300          0               1         180 LGWR    

YES                             0          0             0           0

 

NONE

NONE

NONE

         0          0          0 ASYNCHRONOUS        61440 NO  PUBLIC 

YES              ONLINE_LOGFILE  PRIMARY_ROLE DBSID_DG2

NO

 

 

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 

 SEQUENCE# FIRST_TIME      NEXT_TIME

---------- --------------- ---------------

         1 19-APR-07       20-APR-07

         2 20-APR-07       20-APR-07

         3 20-APR-07       20-APR-07

         4 20-APR-07       20-APR-07

Verifying Apply on Standby

SQL> l

  1* SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#

SQL> /

 

 SEQUENCE# FIRST_TIME      NEXT_TIME       APP

---------- --------------- --------------- ---

         3 20-APR-07 11:07 20-APR-07 11:10 YES

         4 20-APR-07 11:10 20-APR-07 11:47 YES

         5 20-APR-07 11:47 20-APR-07 11:49 YES

         6 20-APR-07 11:49 20-APR-07 11:50 YES

 

SQL>