• Dataguard step-by-step

    DATAGUARD 10-02-2008

    his process is for setting up replication to another databse on the same system.

    SYSTEM

    add the service names in the /etc/hosts file:
    127.0.0.1 localhost.localdomain localhost orcl klon

    PRIMARY DATABASE

    Enable force logging:
    SYS@orcl 11:26:29> select force_logging from v$database;

    FOR
    —
    NO

    SYS@orcl 11:26:54> alter database force logging;

    Database altered.

    SYS@orcl 11:27:12> select force_logging from v$database;

    FOR
    —
    YES

    Create password file (usually not necessary). The SYS password must be the same for the primary and all standby databases.

    Configure a standby redo log
    Get the number of log groups and logfile sizes from EM and then create the standy redo log groups:
    Remember to increment the group numbers, add the ’standby’ keyword, and change the logfile names

    ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ( ‘/u01/app/oracle/oradata/orcl/REDO/sb_redo01.log’) SIZE 51200K;
    ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ( ‘/u01/app/oracle/oradata/orcl/REDO/sb_redo02.log’) SIZE 51200K;
    ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ( ‘/u01/app/oracle/oradata/orcl/REDO/sb_redo03.log’) SIZE 51200K;
    /

    Verify that the standby logs have been added:
    SYS@orcl 12:07:25> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

    GROUP# THREAD# SEQUENCE# ARC STATUS
    ———- ———- ———- — ———-
    4 0 0 YES UNASSIGNED
    5 0 0 YES UNASSIGNED
    6 0 0 YES UNASSIGNED

    Configure the Primary database (both as primary and secondary -- for switchover of roles):
# Primary Role Initialization Parameters:
#
#DB_NAME=orcl
DB_UNIQUE_NAME=orcl
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,klon)'
#
# get from show parameter control_files;
#CONTROL_FILES='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracle/oradata/orcl/control03.ctl'
#
# get from select a.dest_id,name,destination from v$archived_log a, v$archive_dest b where a.dest_id=b.dest_id;
#check the LOCATION directory to see if the db name is in a folder in lowercase or not!
LOG_ARCHIVE_DEST_1=
 'LOCATION=/u01/app/oracle/product/11.1.0/db_1/dbs/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=ORCL'
#
LOG_ARCHIVE_DEST_2=
 'SERVICE=klon LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=klon'
#
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
#REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
#
#get from show parameter log_archive_format
# this is setup automatically when archivelog is enabled
#LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
#
LOG_ARCHIVE_MAX_PROCESSES=4
#
#
#Standby Role Initialization Parameters:
#
FAL_SERVER=klon
FAL_CLIENT=orcl
DB_FILE_NAME_CONVERT='klon','orcl'
LOG_FILE_NAME_CONVERT='/klon/','/orcl/'
STANDBY_FILE_MANAGEMENT=AUTO


Apply these settings to the Primary database (also make sure that the database is in archivelog mode):
create pfile='/home/oracle/DATAGUARD/init_orcl.ora' from spfile;
Then add the above lines to what is there already. Be sure to comment out anything in your additons that are already in the pfile.
shutdown immediate;
create spfile from pfile='/home/oracle/DATAGUARD/init_orcl.ora';
startup

    SECONDARY DATABASE

    Create a backup copy of the primary database datafiles.

    Configure a standby redo log

    ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ( ‘/u01/app/oracle/oradata/klon/REDO/sb_redo01.log’) SIZE 51200K;
    ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ( ‘/u01/app/oracle/oradata/klon/REDO/sb_redo02.log’) SIZE 51200K;
    ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ( ‘/u01/app/oracle/oradata/klon/REDO/sb_redo03.log’) SIZE 51200K;
    /

    Setup the parameters for the standby database:

    create pfile=’/home/oracle/DATAGUARD/init_klon.ora’ from spfile;
    shutdown immediate

    Add the following lines to the parameter file, then apply the changes:

    create spfile from pfile=’/home/oracle/DATAGUARD/init_klon.ora’;
    startup

    Make sure you give a different directory for the log_archive_dest_1 directory so that the logfiles do not clobber each other!

    # db_name must be the same for the primary and all standby databases
    DB_NAME=orcl
    DB_UNIQUE_NAME=klon
    LOG_ARCHIVE_CONFIG=’DG_CONFIG=(orcl,klon)’
    DB_FILE_NAME_CONVERT=(’orcl’,'klon’)
    LOG_FILE_NAME_CONVERT=’orcl’,'klon’
    #LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
    log_archive_trace=7935
    LOG_ARCHIVE_DEST_1=
    ‘LOCATION=/u01/app/oracle/product/11.1.0/db_1/dbs/klon
    VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
    DB_UNIQUE_NAME=klon’
    LOG_ARCHIVE_DEST_2=
    ‘SERVICE=orcl LGWR ASYNC
    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
    DB_UNIQUE_NAME=orcl’
    LOG_ARCHIVE_DEST_STATE_1=ENABLE
    LOG_ARCHIVE_DEST_STATE_2=ENABLE
    REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
    STANDBY_FILE_MANAGEMENT=AUTO
    FAL_SERVER=orcl
    FAL_CLIENT=klon

    HEADS-UP

    I did this on an 11g installation and kept getting error ora-1017. Finally I redid the passwords to remove case-sensitivity and it fixed the problem:

    orapwd file=/u01/app/oracle/product/11.1.0/db_1/dbs/orapw<DBID> password=<passwd> entries=10 force=y ignorecase=y

    I had to enable lgwr logging in order to see the error as well. See post

    set lgwr logging level

    There was also mention that on some versions if the connect identifier had a multiple of 5 characters that the same problem would result.

    see also http://www.colestock.com/blogs/2007/10/creating-physical-standby-using.html

    Posted by admin @ 11:37 am

  • Comments are closed.