Step by step Data Guard implementation

Step by step Data Guard implementation (On Oracle 10gR2)

Assuming that PRIMARY Database is up and running and Oracle s/w is installed on STANDY Server.
Oracle_home=/oracle/ora10g
Oracle_base=/oracle

PRIMARY Server:


Host name : traindb1
Database name (db_name): trg
Database Unique Name (db_unique_name): trg
TNS Service : trg (Through this service, the STANDBY machine will be connected to PRIMARY machine)

STANDBY Server:

Host name : traindb2
Database name (db_name): trg
Database Unique Name (db_unique_name): trg1
TNS Service: trg1 (Through this service, the PRIMARY machine will be connected to Standy machine)

At PRIMARY Server

If password file does not exist than create the password file:

/oracle/ora10g2/bin/orapwd file= /oracle/ora10g2/dbs/orapwtrg password=password


Add following parameters in the inittrg.ora file on the PRIMARY Machine (If exist replace with new value):

*.archive_lag_target=0
*.db_file_name_convert='/oracle/oradata/trg1','/oracle/oradata01/trg','/oracle/oradata01/trg1','/oracle/oradata01/trg' *.db_unique_name='trg'
*.dg_broker_start=TRUE
*.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=traindb1.oneshield.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=trg_XPT)(INSTANCE_NAME=trg)(SERVER=dedicated)))'
*.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=traindb2.oneshield.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=trg1_XPT)(SERVER=dedicated)))'
*.Log_archive_config='DG_CONFIG=(trg,trg1)'
*.log_archive_dest_1='location="/oracle/oradata01/trg/archives"','valid_for=(ALL_LOGFILES,ALL_ROLES)'
*.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=traindb2.oneshield.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=trg1_XPT)(INSTANCE_NAME=trg1)(SERVER=dedicated)))"',' LGWR ASYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="trg1" register net_timeout=180 valid_for=(online_logfile,primary_role)'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
*.log_archive_trace=0
*.log_file_name_convert='/oracle/oradata/trg1','/oracle/oradata/trg'
*.Service_names='trg'
*.standby_archive_dest='/oracle/oradata01/trg/archives'
*.standby_file_management='AUTO'

Take the Database to Archive Mode if it not:

SQL> startup mount;

SQL> Alter database ArchiveLog;

Enable Force Logging:

SQL> Alter database Force Logging;

Check the status:

SQL>select FORCE_LOGGING from V$database;

Create standby redo logs on the PRIMARY Database to support the standby role. The standby redo logs are the same size as the PRIMARY Database online logs. The recommended number of standby redo logs is one more than the number of online redo logs for each thread. For example two online redo logs for each thread, three standby redo logs are required for each thread.

A Standby Redo log is added to enable Data Guard Maximum Availability and Maximum Protection modes. It is important to configure the Standby Redo Logs (SRL) with the same size as the online redo logs.

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
('/oracle/oradata/trg/StbyRedo4.log') SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
('/oracle/oradata/trg/StbyRedo5.log') SIZE 52428800;


ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
('/oracle/oradata/trg/StbyRedo6.log') SIZE 52428800;

Check the STANDBY logfiles:

SQL>SELECT * FROM V$STANDBY_LOG


Listener entry at PRIMARY machine:

(SID_DESC =
(GLOBAL_DBNAME = trg_DGMGRL)
(ORACLE_HOME = /oracle/ora10g2)
(SID_NAME = trg)
(SERVICE_NAME = trg)
)

#lsnrctl stop
#lsnrctl start


TNS Entry in TNSnames.ora file through which the PRIMARY Server will be connected to the STANDBY Server.

trg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = traindb1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = trg)
)
)

trg1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = traindb2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = trg1)
)
)



Check the connectivity from the SQL Prompt:

SQL> connect sys/oracle@trg as sysdba

Setup of SSH and SCP:

# mkdir ~/.ssh
# chmod 700 ~/.ssh
#/usr/bin/ssh-keygen -t rsa
#/usr/bin/ssh-keygen -t dsa

#cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
#cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

#scp authorized_keys traindb1:~/.ssh/authorized_keys
#cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
#cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

Backup the PRIMARY Database to create the STANDBY Database:

Make sure that you can have same folder on source and STANDBY Database
On both Nodes (Primary & Standby) :

#mkdir -p /oracle/oradata01/backup/rmanbackup

On PRIMARY Server, connect to the PRIMARY Database and create a PFILE from the SPFILE

SQL> create pfile='/oracle/oradata01/backup/rmanbackup/inittrg.ora' from spfile;

#rman target /
Rman>crosscheck archivelog all;

Rman> delete force noprompt expired archivelog all;

Rman> backup device type disk format '/oracle/oradata01/backup/rmanbackup/%u' database plus archivelog;

Rman> backup device type disk format '/oracle/oradata01/backup/rmanbackup/ctr_%u' current controlfile for standby;

#scp * traindb2:/oracle/oradata01/backup/rmanbackup

Now “Mount” the PRIMARY Database ( To avoid RMAN error messages, RMAN-03002, RMAN-05500):

SQL>shutdown immediate;
SQL>startup mount


CONFIGURATION ON STANDBY

mkdir -p /oracle/admin/trg1/bdump
mkdir -p /oracle/admin/trg1/cdump
mkdir -p /oracle/admin/trg1/udump
mkdir -p /oracle/admin/trg1/adump
*make change path as per available mount point
mkdir -p /oracle/oradata01/trg1/stby_archive

mkdir -p /oracle/oradata/trg1

Add STANDBY Database name in /etc/oratab file:
trg1:/oracle/ora10g2:Y


Copy inittrg.ora file from PRIMARY Server to STANDBY Server, rename to inittrg1.ora
Open file inittrg1.ora in vi editor, Rename trg1 to trg3 globally, rename trg to trg1 globally than rename trg3 to trg globally.


Add / Check following parameters in the inittrg1.ora file on the STANDBY Server


*.archive_lag_target=0
*.db_file_name_convert='/oracle/oradata/trg','/oracle/oradata01/trg1','/oracle/oradata01/trg','/oracle/oradata01/trg1' *.db_unique_name='trg1'
*.dg_broker_start=TRUE
*.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=traindb1.oneshield.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=trg1_XPT)(INSTANCE_NAME=trg1)(SERVER=dedicated)))'
*.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=traindb2.oneshield.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=trg_XPT)(SERVER=dedicated)))'
*.Log_archive_config='DG_CONFIG=(trg1,trg)'
*.log_archive_dest_1='location="/oracle/oradata01/trg1/archives"','valid_for=(ALL_LOGFILES,ALL_ROLES)'
*.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=traindb2.oneshield.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=trg_XPT)(INSTANCE_NAME=trg)(SERVER=dedicated)))"',' LGWR ASYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="trg" register net_timeout=180 valid_for=(online_logfile,primary_role)'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
*.log_archive_trace=0
*.log_file_name_convert='/oracle/oradata/trg','/oracle/oradata/trg1'
*.Service_names='trg1'
*.standby_archive_dest='/oracle/oradata01/trg1/archives'
*.standby_file_management='AUTO'

Create password file at STANDBY Server:


# orapwd file=/oracle/ora10g2/dbs/orapwtrg1 password=password entries=5

Listener entry at STANDBY Server:


(SID_DESC =
(GLOBAL_DBNAME = trg1_DGMGRL)
(ORACLE_HOME = /oracle/ora10g2)
(SID_NAME = trg1)
(SERVICE_NAME = trg1)
)


#lsnrctl stop
#lsnrctl start

TNS Entry in TNSnames.ora file through which the STANDBY Server will be connected to the PRIMARY Server.


trg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = traindb1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = trg)
)
)


trg1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = traindb2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = trg1)
)
)


Create a spfile at STANDBY Server:

Sqlplus> startup nomount pfile=’/oracle/ora10g2/dbs/inittrg1.ora’
Sqlplus> Create spfile from pfile;

Now startup the STANDBY Database to nomount stage:

#rman auxiliary / target sys/password@trg

connected to target database: DRGTRG3 (DBID=4054010069, not open)
connected to auxiliary database: DRGTRG3 (not mounted)

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY;

Query the column DATABASE_ROLE from V$DATABASE to view the role of STANDBY Database. It should return ‘PHYSICAL STANDBY’.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;

LOG SHIPPING

On PRIMARY Server:

# sqlplus / as sysdba
SQL> alter database open;
SQL> Alter system switch logfile;

Now on the PRIMARY Database check the status of STANDBY Archiving destination.

SQL> Select Status, Error from v$Archive_dest where dest_id=2;

The STATUS should return – VALID. If it returns Error, then check the connectivity between the PRIMARY and STANDBY machines

LOG APPLY SERVICE

On the STANDBY Database execute the following command to start Managed Recovery Process (MRP). This command is executed on Mount stage.

SQL> Alter Database Recover Managed Standby Database Disconnect;

To check whether the log is applied or not:

SQL> Select Name, Archived, Applied from v$Archived_log;

Now, Connect to the STANDBY Database, and create the standby redo logs to support the standby role.
The standby redo logs must be the same size as the PRIMARY Database online logs. The recommended number of standby redo logs is:
(maximum # of logfiles +1) * maximum # of threads
We have two online log files for each thread. Thus, the number of standby redo logs
should be (2 + 1) * 1 = 3.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 8
('/oracle/oradata/trg1/StbyRedo8.log') SIZE 52428800;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 9
('/oracle/oradata/trg1/StbyRedo9.log') SIZE 52428800;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10
('/oracle/oradata/trg1/StbyRedo10.log') SIZE 52428800;

Switchover:

At PRIMARY Server:

SQL> Select Database_role from v$Database;
DATABASE_ROLE
----------------
PRIMARY

At Standby Server:

SQL> Select Database_role from v$Database;
PHYSICAL STANDBY

SQL> Select switchover_status from v$Database;
SESSIONS ACTIVE

To perform a switchover (manually)

At PRIMARY Server:

SQL> Alter Database Commit to Switchover to Physical Standby with session Shutdown;

SQL> shutdown immediate;

SQL> startup mount;

Now your PRIMARY Database has become Physical STANDBY:

SQL> Select Database_role from v$database;
PHYSICAL STANDBY

On the STANDBY Database execute the following command to start Managed Recovery Process (MRP). This command is executed on Mount stage.

SQL> Alter Database Recover Managed Standby Database Disconnect;

At STANDBY Server, MRP which is running in the background of the STANDBY Database:

SQL> Alter database Recover Managed Standby Database Cancel;

To perform a manual switchover from Physical STANDBY to PRIMARY:

SQL> Alter Database Commit to Switchover to PRIMARY with session Shutdown;

SQL> Select Database_role from v$Database;
PRIMARY

Now “PHYSICAL STANDBY” Database has become “PRIMARY”:

SQL> shutdown immediate;

SQL> startup;

Now on the PRIMARY Database check the status of Standby Archiving destination:

SQL> Select Status, Error from v$Archive_dest where dest_id=2;


DG broker configuration

show parameter broker

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /oracle/ora10g2/dbs/dr1trg1.dat
dg_broker_config_file2 string /oracle/ora10g2/dbs/dr2trg1.dat
dg_broker_start boolean FALSE

To enable the Data guard broker:

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

$ dgmgrl
DGMGRL for Linux: Version 10.2.0.2.0 - 64bit Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.

Connect to PRIMARY Database

DGMGRL> connect sys/oracle@trg
Connected.Connected.

Create broker configuration:

DGMGRL>create configuration 'primary_hst' as
primary database is 'trg'
connect identifier is 'trg';

Add the STANDBY Database ‘TRG1’

DGMGRL> add database 'trg1' as connect identifier is 'trg1' maintained as physical;

Check the configuration:

DGMGRL> show configuration

Configuration
Name: primary_hst
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
trg - Primary database
trg1 - Physical standby database

Current status for "primary_hst":
DISABLED

To enable the DG Broker Configuration:

DGMGRL> enable configuration;
Enabled


DGMGRL> show configuration;
Configuration
Name: primary_hst
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
trg - Primary database
trg1 - Physical standby database

Current status for "primary_hst":
SUCCESS

To show DG broker configuration for PRIMARY Database ‘TRG’:


DGMGRL> show database verbose trg
Database
Name: trg
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
trg

Properties:
InitialConnectIdentifier = 'trg'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/oracle/oradata/trg1, /oracle/oradata01/trg, /oracle/oradata01/trg1, /oracle/oradata01/trg'
LogFileNameConvert = '/oracle/oradata/trg1, /oracle/oradata/trg'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'traindb1.oneshield.com'
SidName = 'trg'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=traindb1.oneshield.com)(PORT=1521))'
StandbyArchiveLocation = '/oracle/oradata01/trg/archives'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'trg_%t_%s_%r.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'

Current status for "trg":
SUCCESS


To edit DG broker configuration:

DGMGRL>edit database 'trg' set property 'DbFileNameConvert'='/oracle/oradata/trg1, /oracle/oradata01/trg, /oracle/oradata01/trg1, /oracle/oradata01/trg';

DGMGRL>edit database 'trg' set property 'LogFileNameConvert'='/oracle/oradata/trg1, /oracle/oradata/trg';


Switchover to STANDBY Database ‘TRG1’:

DGMGRL> switchover to 'trg1';
Performing switchover NOW, please wait...
Operation requires shutdown of instance "trg" on database "trg"
Shutting down instance "trg"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "trg1" on database "trg1"
Shutting down instance "trg1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "trg" on database "trg"
Starting instance "trg"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "trg1" on database "trg1"
Starting instance "trg1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "trg1"

PRIMARY Database Successfully converted to Physical STANDBY.

Failover to STANDBY database ‘TRG1’:

DGMGRL> failover to ‘trg1’;