DBA Tips Archive for Oracle

  

Data Guard Archived Redo Log Repository

by Jeff Hunter, Sr. Database Administrator

Contents

Introduction

Introduced in Oracle9i Release 1, a Data Guard archived redo log repository is a type of destination that allows off-site archiving of redo data. This remote location is also sometimes referred to as an archival standby database. An archived redo log repository is created by using a physical standby control file, starting the instance, and mounting the database. This database contains no datafiles and cannot be used for switchover or failover in a Data Guard configuration. The primary database is configured to send its archived redo data to the Oracle instance at the remote location which is only responsible for accepting and storing the archived redo log files.

An archived redo log repository is an efficient mechanism for transporting and storing archive logs at an alternate location; remote or local. This can be accomplished by configuring the repository destination for archiver-based transport (using the ARCH attribute on the LOG_ARCHIVE_DEST_n parameter) or asynchronous based transport (using the ASYNC attribute on the LOG_ARCHIVE_DEST_n parameter) in a Data Guard configuration running in maximum performance mode.

 

A Data Guard archived redo log repository is no substitution for a fully configured standby database. For a no data loss environment, you should still configure a standby database using the LGWR, SYNC, and AFFIRM transport settings in a Data Guard configuration running in either maximum protection mode or maximum availability mode.

The purpose of this guide is to provide the steps necessary to create a Data Guard archived redo log repository.

Archived Redo Log Repository Uses

  1. Remote archive repository for physical database backups.

    An archived redo log repository can be used as a method for backing up the archived redo logs directly to a separate disk location or separate site from the primary.

  2. An additional remote destination for maximum availability or maximum protection Data Guard configurations.

    An archived redo log repository can be used as an added level of protection in addition to the standby(s).

  3. Rolling database upgrade scenarios using Data Guard to meet the Recovery Point Objective (RPO) if the primary site fails during the upgrade of the logical site.

    During a rolling upgrade using SQL Apply or a Transient Logical Standby, there is a point where the current standby database is not receiving redo from the primary site because the upgraded primary cannot send redo to the lower version standby. During those times it is recommended to use an archived redo log repository.

  4. Temporary storage location for archived redo logs at a disaster recovery (DR) site in Oracle Database 10g while a physical standby is open read-write for testing or other purposes.

    In Oracle Database 10g, when a standby database is opened in read-write mode, it cannot receive or apply redo. This is not the cause in Oracle Database 11g with the introduction of Snapshot Standby Database. An Oracle Database 11g snapshot standby database receives and archives, but does not apply redo data from a primary database. Prior to Oracle Database 11g, to reduce the RPO impact during the time the standby is opened read-write, an archived redo log repository can be used.

Prerequisites for the Archived Redo Log Repository

  1. Same Oracle software version as the primary site.

  2. Compatible platform per Metalink note 413484.1.

  3. Disk space for:

        Oracle Software
        standby controlfile
        archived redo logs
        standby redo logs (optional but recommended)

  4. Approximately 100 MB of memory for the SGA.

Example Configuration

This section describes the Oracle database environment and parameters used in this guide to create archived redo log repository.

Primary Database
Oracle Release Oracle 10g Release 2 — (10.2.0.5)
Host Name vmlinux1.idevelopment.info — (192.168.1.160)
Operating System Red Hat Linux 5 — (CentOS 5.5)
Database Configuration Single Instance, non-ASM, Oracle Managed Files (OMF), Flash Recovery Area (FRA), SPFILE
Database Name (db_name) modesto
Database Domain (db_domain) idevelopment.info
Oracle SID modesto
Database Unique Name (db_unique_name) modesto
TNS Alias modesto.idevelopment.info
Service Names modesto.idevelopment.info, modesto
Database Files - (db_create_file_dest) /u02/oradata
Flash Recovery Area - (db_recovery_file_dest) /u03/flash_recovery_area
Local Online Redo Log Files - (log_archive_dest_1) location=use_db_recovery_file_dest — (all_logfiles,all_roles)
Remote Archive Destination - (log_archive_dest_2) service=turlock — (online_logfiles,primary_role)

Archived Redo Log Repository
Oracle Release Oracle 10g Release 2 — (10.2.0.5)
Host Name vmlinux4.idevelopment.info — (192.168.1.166)
Operating System Red Hat Linux 5 — (CentOS 5.5)
Database Configuration Single Instance, non-ASM, Oracle Managed Files (OMF), Flash Recovery Area (FRA), SPFILE
Database Name (db_name) modesto
Database Domain (db_domain) idevelopment.info
Oracle SID alrepos
Database Unique Name (db_unique_name) alrepos
TNS Alias alrepos.idevelopment.info
Service Names alrepos.idevelopment.info, alrepos
Standby Control File - (control_files) /u03/flash_recovery_area/ALREPOS/controlfile/control01.ctl
Archived Redo Log Repository Location - (Flash Recovery Area) /u03/flash_recovery_area/ALREPOS/archivelog

Special Notes

Implementing an Archived Redo Log Repository

Create Standby Controlfile from Primary Database

The first step is to create a standby controlfile from the primary database. 


[oracle@vmlinux1 ~]$ sqlplus / as sysdba

SQL> alter database create standby controlfile as '/u04/oracle/dg_staging/stby_control_alrepos.ctl';

Database altered.

Copy Standby Controlfile to Remote Database Server

Next, transfer the controlfile from the primary database server to the remote server. 


[oracle@vmlinux1 ~]$ scp /u04/oracle/dg_staging/stby_control_alrepos.ctl oracle@vmlinux4:/u04/oracle/dg_staging/stby_control_alrepos.ctl
stby_control_alrepos.ctl         100% 7184KB   7.0MB/s   00:01

Create an Oracle Instance on Remote Server for the Archive Log Repository Site

Now, create an Oracle instance on the remote server that will be used as the archived redo log repository.

  1. Create the Standby Password File

    As part of the new redo transport security and authentication features, it is now mandatory that each database in an Oracle Data Guard configuration utilize a password file. In addition, the SYS password must be identical on every database in order for redo transport to function. Create the password file on the standby database using the following steps: 


    [oracle@vmlinux4 ~]$ echo $ORACLE_SID
alrepos

[oracle@vmlinux4 ~]$ cd $ORACLE_HOME/dbs
[oracle@vmlinux4 dbs]$ orapwd file=orapwalrepos password=MySysPassword

  2. Create an spfile for the Standby Instance

    Note that an archived redo log repository requires a minimally sized SGA and therefore any SGA related database parameters can be significantly reduced. The specific parameters used in this guide make use of Automatic Shared Memory Management (sga_target). An SGA of 100MB should be a sufficient size.

          $ORACLE_HOME/dbs/initalrepos.ora 


    audit_file_dest             = '/u01/app/oracle/admin/alrepos/adump'
background_dump_dest        = '/u01/app/oracle/admin/alrepos/bdump'
core_dump_dest              = '/u01/app/oracle/admin/alrepos/cdump'
user_dump_dest              = '/u01/app/oracle/admin/alrepos/udump'
compatible                  = '10.2.0.5.0'
control_files               = '/u03/flash_recovery_area/ALREPOS/controlfile/control01.ctl'
db_block_size               = 8192
db_name                     = modesto
db_recovery_file_dest       = '/u03/flash_recovery_area'
db_recovery_file_dest_size  = 32G
db_unique_name              = alrepos
log_archive_config          = 'dg_config=(modesto,alrepos)'
log_archive_dest_1          = 'location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles)'
log_archive_dest_state_1    = 'enable'
log_archive_max_processes   = 4
log_file_name_convert       = '/MODESTO/','/ALREPOS/'
remote_login_passwordfile   = exclusive
service_names               = alrepos.idevelopment.info
sga_target                  = 120M

     

    Note that db_block_size and db_name must match to that of the primary database which you created the controlfile from. The archived redo log files will be placed in the directory specified by the Flash Recovery Area (log_archive_dest_1).

    Create an SPFILE and backup the PFILE. 


    SQL> create spfile from pfile;

File created.

SQL> exit

[oracle@vmlinux4 ~]$ mv $ORACLE_HOME/dbs/initalrepos.ora $ORACLE_HOME/dbs/initalrepos.pfile

  3. Configure Oracle Net Components

    Oracle Data Guard depends on Oracle Net as the transport mechanism to propagate changes made on the primary database to the archive log repository site. The Oracle Net aliases must be configured on both the primary and standby host in order to facilitate Data Guard's communication requirements. From a high level, the following Oracle Net components need to be configured:

    • An Oracle Net listener running on the primary host

    • An Oracle Net listener running on the standby host

    • An Oracle Net alias on the primary site that points to the archive log repository site

    • An Oracle Net alias on the archive log repository site that points to the primary site

    The primary and standby host should contain an Oracle Net alias in the tnsnames.ora file for the primary and archive log repository service names: 


    MODESTO.IDEVELOPMENT.INFO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vmlinux1.idevelopment.info)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = modesto.idevelopment.info)
    )
  )

ALREPOS.IDEVELOPMENT.INFO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vmlinux4.idevelopment.info)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = alrepos.idevelopment.info)
    )
  )

    Note that each of the Oracle Net alias entries above contain the domain IDEVELOPMENT.INFO. In my Oracle network configuration, I defined the default domain in the sqlnet.ora file on every host which provides name resolution for Oracle Net clients. When this parameter is set, the default domain name is automatically appended to any unqualified net service name or alias: 


    NAMES.DEFAULT_DOMAIN = IDEVELOPMENT.INFO

  4. Create and Start the Standby Instance on the Archive Log Repository Site

    Start by creating the "dump directories" on the archive log repository site. 


    [oracle@vmlinux4 ~]$ mkdir -p /u01/app/oracle/admin/alrepos/adump
[oracle@vmlinux4 ~]$ mkdir -p /u01/app/oracle/admin/alrepos/bdump
[oracle@vmlinux4 ~]$ mkdir -p /u01/app/oracle/admin/alrepos/cdump
[oracle@vmlinux4 ~]$ mkdir -p /u01/app/oracle/admin/alrepos/dpdump
[oracle@vmlinux4 ~]$ mkdir -p /u01/app/oracle/admin/alrepos/pfile
[oracle@vmlinux4 ~]$ mkdir -p /u01/app/oracle/admin/alrepos/scripts
[oracle@vmlinux4 ~]$ mkdir -p /u01/app/oracle/admin/alrepos/udump

    Next, create and verify all directories on the archive log repository site that will be used for the Flash Recovery Area: 


    [oracle@vmlinux4 ~]$ mkdir -p /u03/flash_recovery_area/ALREPOS/archivelog
[oracle@vmlinux4 ~]$ mkdir -p /u03/flash_recovery_area/ALREPOS/controlfile
[oracle@vmlinux4 ~]$ mkdir -p /u03/flash_recovery_area/ALREPOS/onlinelog

    Copy the standby controlfile that was created on the primary site to the directory that will be used by the archived redo log repository Oracle instance (control_files). 


    [oracle@vmlinux4 ~]$ cp /u04/oracle/dg_staging/stby_control_alrepos.ctl /u03/flash_recovery_area/ALREPOS/controlfile/control01.ctl

    With the standby control file in place start up the instance and mount it. 


    [oracle@vmlinux4 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Jan 26 15:10:58 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  125829120 bytes
Fixed Size                  1272336 bytes
Variable Size              58721776 bytes
Database Buffers           58720256 bytes
Redo Buffers                7114752 bytes
Database mounted.

  5. Verify the new instance is registered with the local listener. 


    [oracle@vmlinux4 ~]$ lsnrctl services | grep '^Service'
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Service "alrepos" has 1 instance(s).
Service "alrepos.idevelopment.info" has 1 instance(s).
Service "alrepos_XPT" has 1 instance(s).

  6. Create Standby Redo Logs (optional)

    Certain data protection modes within Oracle Data Guard, such as maximum availability and maximum protection, mandate the use of standby redo logs. Regardless of the protection mode (even when using maximum performance), it is highly recommended to utilize standby redo logs as generally more data can be recovered during a failover than without them.

    From the archive log repository site, connect as SYS and run the following to create four standby redo log file groups: 


    SQL> alter database add standby logfile thread 1 group 4 size 50m;

Database altered.

SQL> alter database add standby logfile thread 1 group 5 size 50m;

Database altered.

SQL> alter database add standby logfile thread 1 group 6 size 50m;

Database altered.

SQL> alter database add standby logfile thread 1 group 7 size 50m;

Database altered.

    To verify the new standby redo log files: 


    SQL> select group#, type, member from v$logfile order by group#, member;

    GROUP# TYPE    MEMBER
---------- ------- -----------------------------------------------------------------
         1 ONLINE  /u02/oradata/ALREPOS/onlinelog/o1_mf_1_7l23jft5_.log
         1 ONLINE  /u03/flash_recovery_area/ALREPOS/onlinelog/o1_mf_1_7l23jgyw_.log
         2 ONLINE  /u02/oradata/ALREPOS/onlinelog/o1_mf_2_7l23jj0t_.log
         2 ONLINE  /u03/flash_recovery_area/ALREPOS/onlinelog/o1_mf_2_7l23jllv_.log
         3 ONLINE  /u02/oradata/ALREPOS/onlinelog/o1_mf_3_7l23jo51_.log
         3 ONLINE  /u03/flash_recovery_area/ALREPOS/onlinelog/o1_mf_3_7l23jqq0_.log
         4 STANDBY /u03/flash_recovery_area/ALREPOS/onlinelog/o1_mf_4_7l3lhohl_.log
         5 STANDBY /u03/flash_recovery_area/ALREPOS/onlinelog/o1_mf_5_7l3lhwt4_.log
         6 STANDBY /u03/flash_recovery_area/ALREPOS/onlinelog/o1_mf_6_7l3lj1ss_.log
         7 STANDBY /u03/flash_recovery_area/ALREPOS/onlinelog/o1_mf_7_7l3lj7c7_.log

Configure Primary Database to Start Archiving to the Archived Redo Log Repository

The last step is to configure the initialization parameters on the primary database to starting archiving its redo data to the new archived redo log repository.

  1. Set the following initialization parameters on the primary database. 


    SQL> alter system set log_archive_config = 'dg_config=(modesto,turlock,alrepos)';

System altered.
 
SQL> alter system set log_archive_dest_4 = 'service=alrepos.idevelopment.info lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=alrepos';

System altered.

SQL> alter system set log_archive_dest_state_4 = 'enable';

System altered.

  2. Perform a log switch on the primary database. 


    SQL> alter system archive log current;

     

    The archived redo log files will be placed in the directory specified by the Flash Recovery Area (log_archive_dest_1).

Verify the Archive Log Repository is Receiving Logs

Check the alert.log file on both sites.

Managing the Archived Redo Log Repository

Gap Detection and Resolution

Note that the archived redo log repository cannot use FAL; only Automatic Gap Detection will work. So if there are any gaps that cannot be resolved automatically you have to resolve those manually.

Avoid Redo Retransmission

Avoid redo retransmission and register the archived redo log repository logs to be used by the standby.

If the archived redo log repository is used temporarily while a standby on the same host (or with access to the same shared storage) is down or unable to receive redo as in the SQL Apply Rolling upgrade case, then register the archive log repository logs with the standby when it is ready to come back up and apply the logs.

  1. On the standby find the starting SCN for recovery. 


    SQL> recover standby database
ORA-00279: change 585427 generated at 01/26/2012 17:36:58 needed for thread 1
ORA-00289: suggestion :
/u03/flash_recovery_area/TURLOCK/archivelog/2012_01_26/o1_mf_1_68_%u_.arc
ORA-00280: change 585427 for thread 1 is in sequence #68


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

  2. On the archive log repository, use a script to generate the register log commands for the standby. 


    register_logs.sql

set head off feedback off lines 133 pages 0 verify off echo off
spool register_logs_for_standby.sql
select 'alter database register logfile ' || chr(39) || name || chr(39) || ';'
from v$archived_log
where first_change# >= &1
/
spool off

SQL> @register_logs 585427
alter database register logfile '/u03/flash_recovery_area/ALREPOS/archivelog/2012_01_26/o1_mf_1_68_7l3mnhl7_.arc';
alter database register logfile '/u03/flash_recovery_area/ALREPOS/archivelog/2012_01_26/o1_mf_1_69_7l3mnhw5_.arc';
alter database register logfile '/u03/flash_recovery_area/ALREPOS/archivelog/2012_01_26/o1_mf_1_70_7l3mnhow_.arc';
alter database register logfile '/u03/flash_recovery_area/ALREPOS/archivelog/2012_01_26/o1_mf_1_71_7l3mnj4m_.arc';
alter database register logfile '/u03/flash_recovery_area/ALREPOS/archivelog/2012_01_26/o1_mf_1_72_7l3mnj49_.arc';
alter database register logfile '/u03/flash_recovery_area/ALREPOS/archivelog/2012_01_26/o1_mf_1_73_7l3mnjc9_.arc';
alter database register logfile '/u03/flash_recovery_area/ALREPOS/archivelog/2012_01_26/o1_mf_1_74_7l3mnjnt_.arc';
alter database register logfile '/u03/flash_recovery_area/ALREPOS/archivelog/2012_01_26/o1_mf_1_75_7l3mnjvs_.arc';

  3. Register the archived redo logs on the standby now using the generated script. 


    SQL> @register_logs_for_standby

Database altered.

Database altered.

Database altered.

Database altered.

Database altered.

Database altered.

Database altered.

Database altered.

  4. Recover the standby with the registered logs. 


    SQL> recover managed standby database;

  5. Stop the archived redo log repository transfers now on the primary. 


    SQL> alter system set log_archive_dest_state_4=defer;

System altered.

  6. Resume transmission to the standby. 


    SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system archive log current;

System altered.

Redo Log File Retention

Ensure you have a script setup in cron to purge out obsolete archived redo log files after a certain number of days from the archived redo log repository according to your retention policy.

      purge_archived_redo_logs.ksh 


#!/bin/ksh
# ----------------------------
# purge_archived_redo_logs.ksh
# ----------------------------

TARGET_DBA_USERNAME=sys
TARGET_DBA_PASSWORD=MySysPassword
ORACLE_SID=alrepos
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=${ORACLE_BASE}/product/10.2.0/db_1
PATH=${ORACLE_HOME}/bin:${PATH}
LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
NUM_DAYS_TO_KEEP=7

echo Purging archived redo logs older than ${NUM_DAYS_TO_KEEP} days...

$ORACLE_HOME/bin/rman target "${TARGET_DBA_USERNAME}/${TARGET_DBA_PASSWORD}@${ORACLE_SID}" nocatalog <<EOF
    list archivelog all;
    delete noprompt force archivelog until time 'sysdate - ${NUM_DAYS_TO_KEEP}';
    exit;
EOF

exit

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX, Linux, and Windows server environment. Jeff's other interests include mathematical encryption theory, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 18 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science.


Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Friday, 18-May-2012 09:17:18 EDT
Page Count: 2260