Data Guard : A Practical Approach to Quickly Building Physical Standby Database

Creating physical standby database on the same machine where primary database is running does not serve any reasonable purpose. But, we cannot rule out any possible usage of this layout. Here, we see how we can configure primary and standby databases running on same machine.

We would like to create the Data Guard with following details

Parameter Primary DB Standby DB
Instance Name dosa dosa
DB Unique Name dosa dosa1
DB Name dosa dosa
Datafile Location /u01/dosa/data /u01/dosa1/data
Logfile Location /u01/dosa/log /u01/dosa1/log
Database Configuration Requirements
Database must run in ARCHIVELOG mode
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u2/dosa/archived_log
Oldest online log sequence     49
Next log sequence to archive   51
Current log sequence           51
FORCE LOGGING must be enabled
SQL> select name, force_logging from v$database;

NAME      FOR
--------- ---
DOSA      NO

If you see FORCE_LOGGING is NO, then you need to enable FORCE LOGGING by executing below ALTER DATBABASE command

SQL> alter database force logging;
Standby Redo Logs

Though not madatory if the database is not running on standby mode, it is safe to create standby redolog files on primary database

SQL> select group#, sequence#, bytes/1024/1024 size_mb, status from v$standby_log;

no rows selected

There are no standby redo logs present initially. Hence, this has to be created. But the standby redo logfile must have the same size as online redo logs and, the count of standby redo logs is one more than online redo logfiles

SQL> select group#, sequence#, bytes/1024/1024 size_mb, status from v$log;

    GROUP#  SEQUENCE#    SIZE_MB STATUS
---------- ---------- ---------- ----------------
         1         49         50 INACTIVE
         2         50         50 INACTIVE
         3         51         50 CURRENT

Here, there are 3 groups for online logfiles and size of each is 50 MB. Use below command to create 4 standby logfiles on primary database. Let the group number begin from 11

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('') SIZE 50m;
Recovery file destination

To keep recovery related files, create DB_RECOVERY_FILE_DEST. First set db_recovery_file_dest_size and then db_recovery_file_dest.

SQL> ALTER SYSTEM SET db_recovery_file_dest_size=4g;

SQL> ALTER SYSTEM SET db_recovery_file_dest='/u2/dosa/fra';

SQL> SHO PARAMETER db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u2/dosa/fra
db_recovery_file_dest_size           big integer 4977M
Database Parameter Requirements
COMPATIBLE

Ensure the COMPATIBLE parameter between primary and standby should be the same or the redo apply will not be successful

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.0.0
LOG_ARCHIVE_CONFIG

General syntax for setting this parameter is below

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(db_unique_name1,db_unique_name2,...,db_unique_namen)' SCOPE=both;

where db_unique_name1, db_unique_name2 ... are the unique names of all the databases in the Data Guard

Example
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(dosa,dosa1)' SCOPE=both;

SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string
LOG_ARCHIVE_DEST_1
SQL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=<path to archived log files on the local system>';
Example
SQL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u2/dosa/archived_log';

SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=/u2/dosa/archived_log
LOG_ARCHIVE_MAX_PROCESSES

This parameter sets the total number of archiver processes running on the system.

SQL> ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=both;

SQL> show parameter log_archive_max_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes            integer     4
For using db_recovery_file_dest for storing archived redo
LOCATION
SQL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';
SERVICE
SQL> ALTER SYSTEM SET log_archive_dest_2='SERVICE=dosa DB_UNIQUE_NAME=dosa1 VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)';
AFFIRM
SQL> select affirm from v$archive_dest where dest_id = 2;

AFF
---
NO
MAX_CONNECTIONS
SQL> select max_connections from v$archive_dest where dest_id = 2;
MAX_FAILURES, REOPEN
SQL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST REOPEN=8 MAX_FAILURES=4';

SQL> SELECT max_failure, failure_count, reopen_secs FROM v$archive_dest
  2  WHERE dest_id = 1;

MAX_FAILURE FAILURE_COUNT REOPEN_SECS
----------- ------------- -----------
          0             0         300
NET_TIMEOUT
SQL> select net_timeout from v$archive_dest where dest_id = 2;
DELAY
SQL> col delay_mins for 9999
SQL> col destination  for a44
SQL> select delay_mins, destination from v$archive_dest where dest_id = 2;

To ignore DELAY in apply, use

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;
STANDBY SITE
STANDBY_FILE_MANAGEMENT
SQL> ALTER SYSTEM SET standby_file_management=auto scope=both;
DB_FILE_NAME_CONVERT
SQL> ALTER SYSTEM SET db_file_name_convert="'a/b','c/d'" scope=spfile;
LOG_FILE_NAME_CONVERT
SQL> ALTER SYSTEM SET log_file_name_convert="'a/b','c/d'" scope=spfile;
listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/11.2.0/db_4/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER_DOSA =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = DOSA))
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl)(PORT = 1525))
    )
  )

ADR_BASE_LISTENER_DOSA = /opt/oracle
tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/product/11.2.0/db_4/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_DOSA =
  (ADDRESS = (PROTOCOL = TCP)(HOST = orcl)(PORT = 1525))


DOSA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcl)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dosa)
    )
  )
run {
  allocate channel c1 type disk;
  allocate channel c2 type disk;
  allocate auxiliary channel c3 type disk;
  allocate auxiliary channel c4 type disk;
  duplicate target database
    for standby
    from active database
    dorecover
    spfile 
      set db_unique_name='dosa1'
      set db_name='dosa'
      set log_archive_max_processes='4'
      set fal_server='dosa'
      set log_archive_config='DG_CONFIG=(dosa,dosa1)'
      set log_archive_dest_1='LOCATION=/u01/dosa1/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NANME=dosa1'
      set log_archive_dest_2='SERVICE=dosa VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dosa1'
      set log_archive_dest_state_1='ENABLE'
      set db_file_name_convert="'dosa','dosa1'"
      set log_file_name_convert="'dosa','dosa1'"
      set audit_file_dest='/u01/dosa1/audit'
      set db_recovery_file_dest_size='400m'
      set db_recovery_file_dest='/u01/dosa1/fra'
      set diagnostic_dest='/u01/dosa1/diag'
      set standby_file_management='auto'
}
Comments