Oracle Data Guard is the management, monitoring, and automation software infrastructure that creates, maintains, and monitors one or more standby databases to protect enterprise data from failures, disasters, errors, and corruptions.

The process below is applicable if you have separate Windows/Unix  servers for Datguard primary & Standby Install . For single machine Dataguard Install use my other post.

Oracle Data Guard Overview

Windows 2000 with Oracle 9.2.0.8
Primary Server is called “Primary”
Standby Server is called “Standby”
A working DNS is available for both servers.
Oracle Instance is called DATAGUARD on both Servers
TNS Listener is configured as Primary.world and Standby.world

IMP:
– Configure listener.ora /Tnsnames.ora and add both Primary as well as Standby Database Services
– Always Copy the up -to- date password file from Primary to Standby
– Use Spfile for all configuration settings

Sample Listener file listener.ora
Sample Tnsnames file tnsnames.ora

At Primary Database

1. Check Archivelog mode and force database into Archivelog

SQLPLUS>archive log list;
If this returns “Noarchivelog Mode” then
Add following parameters at initPrimary.ora ….
log_archive_dest_1 = ‘location=f:\Oracle\oradata\Primary MANDATORY REOPEN=5’
log_archive_format = “Primary_%S.ARC”
log_archive_start = true
log_buffer = 1000000
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800

SQL>Startup nomount;
SQL>Alter database archivelog;
Do “alter system switch logfile” and see archive files are dunped at right location

SQL> archive log list;
Standbybase log mode Archive Mode
Automatic archival Enabled
Archive destination f:\Oracle\primary\DATAGUARD
Oldest online log sequence 14
Next log sequence to archive 17
Current log sequence 17

SQL> create spfile from pfile;
File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

At this stage
– COLD backup Primary Database files and ship it to Standby Server.
– At primary create standby control file
SQLPLUS> Alter Database Create Standby Controlfile as ‘D:\Oracle\Oradata\Primary\control.ctl’;
– Copy standby.ctl on to Standby Machine

SQL> startup;
SQL> show parameter spfile;
NAME TYPE VALUE
———————————— ———– ——————————
spfile string %ORACLE_HOME%\StandbyBASE\SPFILE%
ORACLE_SID%.ORA

SQL> connect / as sysdba
Connected.

SQL> alter system set dg_broker_start=TRUE scope=both;
System altered.

C:\Documents and Settings\oracle>dgmgrl
DGMGRL for 32-bit Windows: Version 9.2.0.1.0 – Production.
(c) Copyright 2002 Oracle Corporation. All rights reserved.
Welcome to DGMGRL, type “help” for information.

DGMGRL> connect sys/oracle
Connected.

DGMGRL> show configuration verbose;
Error: ORA-16532: Standby Guard configuration does not exist

SQL> connect / as sysdba
Connected.

SQL> show parameter dg_broker_start
NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean TRUE

DGMGRL> show configuration verbose;
Error: ORA-16532: Standby Guard configuration does not exist

Above Error indicates we need configuration at Data Guard

Configure Data Guard

Create a new DRC and define a primary Site

DGMGRL> create configuration ‘Primary Site’
as primary site is ‘Primary_site’
resource is ‘Primary_db’
hostname is ‘Primary’
instance name is ‘dataguard’
service name is ‘primary’
site is maintained as physical;
Configuration “Primary Site” added with primary site “Primary_site”
Standbybase resource “Primary_db” added.
Create Secondary Site

DGMGRL> create site ‘Standby_site’
resource is ‘Standby_db’
hostname is ‘Standby’
instance name is ‘dataguard’
service name is ‘standby’
site is maintained as physical;
Site “Standby_site” added to configuration.
Standbybase resource “Standby_db” added.
View DRC config

DGMGRL> show configuration verbose;
Configuration
Name: ‘Primary Site ‘
Enabled: ‘no’
Default state: ‘ONLINE’
Intended state: ‘OFFLINE’
Protection Mode: ‘MaxPerformance’
Number of sites: 2
Sites:
Primary Site: Primary_site
Standby Site: Standby_site
Current status for “Primary Site”:
DISABLED
Enable DRC
Please review alrtlog as well as log for DMON process($BDUMP/alrtDMON.log).

DGMGRL> enable configuration
Enabled.

DGMGRL> show configuration;
Configuration ‘Primary Site’ is
Primary Site is ‘Primary_site’
Standby Site is ‘Standby_site’
Current status for “Genesys Newport”:
SUCCESS

DGMGRL> show resource Primary_db
Resource ‘Primary_db’ on site ‘Primary_site’
depends on ‘Primary_site’
Current status for “Primary_db”:
SUCCESS

DGMGRL> show resource Standby_db
Resource ‘Standby_db’ on site ‘Standby_site’
depends on ‘Standby_site’
Current status for “Standby_db”:
SUCCESS

DGMGRL> exit

At Physical Standby Database

– Configure listener.ora /Tnsnames.ora and add both Primary as well as Standby Database Services
– Always Copy up -to- date password file from Primary to Standby
– Use Spfile for all Configuration Settings
=========================================================================

Add following 2 parameters at standby init.ora file
standby_file_management=auto
standby_archive_dest=’f:\oracle\oradata\standby’

C:\Documents and Settings\oracle>oradim -new -sid DATAGUARD -startmode m

C:\Documents and Settings\oracle>set oracle_sid=DATAGUARD

C:\Documents and Settings\oracle>sqlplus /nolog
SQL*Plus: Release 9.2.0.8.0 – Production on Wed Feb 14 16:12:33 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 655843824 bytes
Fixed Size 455152 bytes
Variable Size 335544320 bytes
Database Buffers 318767104 bytes
Redo Buffers 1077248 bytes

SQL> alter database mount standby database;
Database altered.

SQL> recover managed standby database disconnect from session;
Media recovery complete.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination f:\Oracle\oradata\primary
Oldest online log sequence 14
Next log sequence to archive 17
Current log sequence 17

SQL> create spfile from pfile;
File created.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 655843824 bytes
Fixed Size 455152 bytes
Variable Size 335544320 bytes
Database Buffers 318767104 bytes
Redo Buffers 1077248 bytes
ORA-01666: controlfile is for a standby database
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 655843824 bytes
Fixed Size 455152 bytes
Variable Size 335544320 bytes
Database Buffers 318767104 bytes
Redo Buffers 1077248 bytes

SQL> alter database mount standby database;
Database altered.

SQL> recover managed standby database disconnect from session;
Media recovery complete.

SQL> alter system set dg_broker_start=TRUE scope=both;
System altered.

SQL> show parameter dg_broker_start;
NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean TRU

Sample Listener file listener.ora
Sample Tnsnames file tnsnames.ora