oracle 11g dataguard configuration
TRANSCRIPT
-
7/28/2019 Oracle 11G Dataguard Configuration
1/18
Version 1.0
Oracle DataGuard Broker
ConfigurationWith Oracle 11gR2
-
7/28/2019 Oracle 11G Dataguard Configuration
2/18
News International
News International
Sandeep kumar Singh
-
7/28/2019 Oracle 11G Dataguard Configuration
3/18
Hardware and Software Enviroment
2 Linux servers, SUSE Linux Enterprise Server 10 (x86_64), the Primary and
Standby databases are located on these servers. 1 SUSE Linux Enterprise Server 10 (x86_64) ,The Data Guard Broker
Observer is located on this server
Oracle Database 11g R2 ssh is configured for user oracle on both nodes Oracle Home is on identical path on both nodes Primary database DB1_WA Standby database DB1_PB
An Over view
The walkthrough begins with a single database that will become the primary of a DataGuard configuration. For this build, we will use a single physical standby database.
FSFO can also be used with logical standbys and an FSFO-enabled configuration may
have multiple standbys with a mix of physical and logical, but only one standby can
be the failover target at any given time.
Conventions used
Database hosts are referred to as unixpgtest1" and unixpgtest3" hosts and the
databases themselves are referred to as the "DB1_WA" and "DB1_PB" databases.
Names used in the examples:
Database name DB1
Database unique names
DB1_WA
DB1_WB
Domain name mydomain.com
Hostnames
unixpgtest3(primary node)
unixpgtest1(standby node)
uniwaoradbr01 (observer)
TNS aliases
DB1PRIMARY
DB1STANDBY
-
7/28/2019 Oracle 11G Dataguard Configuration
4/18
ORACLE DATA GUARD BROKER CONFIGURATIO PLAN
Task Name: Oracle Data guard Broker Configuration
Short Description This task related to Oracle 11g Dataguard Broker Configuration with
observer in Unix/Linux Environment
Step by Step Implementation of Data Guard Broker
1.
Log on to the Production server using the administrative ID.
Login ID Used sys/***** as sysdba
2. Take a full backup of Database (RMAN/Cold Backup)
3. Configure Oracle Net
Data Guard uses Oracle Net (SQL*Net) for communication between the primary and standby
databases and the FSFO observer. Getting the Oracle Net configuration right is one of the key
factors in a successful FSFO deployment. Improper Oracle Net configuration is a leading cause of
reported FSFO issues
Configure listeners
4. listener.ora configuration for host " unixpgtest3 (primary node)":
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DB1)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome)
(SID_NAME = DB1)
)
(SID_DESC =
(SID_NAME = DB1)
(GLOBAL_DBNAME = DB1_WA_DGMGRL.mydomain.com)
-
7/28/2019 Oracle 11G Dataguard Configuration
5/18
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = unixpgtest3.ds.newsint)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
5. listener.ora configuration for host " unixpgtest1(standby node)":
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DB1)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = DB1)
)
(SID_DESC =
(SID_NAME=DB1)
(GLOBAL_DBNAME = DB1_PB_DGMGRL.mydomain.com)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)
)
)
LISTENER =
(DESCRIPTION =
-
7/28/2019 Oracle 11G Dataguard Configuration
6/18
-
7/28/2019 Oracle 11G Dataguard Configuration
7/18
)
(CONNECT_DATA =
(SERVICE_NAME = DB1_PB_DGMGRL.mydomain.com)
)
7. TNSNAMES configuration for host " unixpgtest1(standby node)":# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DB1 =(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = unixpgtest1.ds.newsint)(PORT = 1521))
)
(CONNECT_DATA =(SERVICE_NAME = DB1)
)
)
DB1_WA =(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = unixpgtest3.ds.newsint)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DB1)
)
)
DB1_PB =
(DESCRIPTION =
(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = unixpgtest1.ds.newsint)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DB1)
)
)
DB1PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = unixpgtest3.ds.newsint)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DB1_WA_DGMGRL.mydomain.com)
)
)
DB1STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = unixpgtest1.ds.newsint)(PORT = 1521))
)
(CONNECT_DATA =(SERVICE_NAME = DB1_PB_DGMGRL.mydomain.com)
)
)
-
7/28/2019 Oracle 11G Dataguard Configuration
8/18
8.
Start the listener
Start the listener on both " unixpgtest1" and " unixpgtest3" hosts.
lsnrctl start LISTENER
9. Test the Oracle Net connections
Verify the configuration from both hosts.
-
7/28/2019 Oracle 11G Dataguard Configuration
9/18
10. Setup the Local_Listener parameter on both the Primary and Standby databases
SQL> alter system set
local_listener='(address=(host=unixpgtest1.ds.newsint)(port=1521)(protocol=tcp))';
System altered.
SQL> alter system set
local_listener='(address=(host=unixpgtest3.ds.newsint)(port=1521)(protocol=tcp))';
System altered.
11.Setup the Broker configuration :The broker configuration files are automatically created when the broker is started using
ALTER SYSTEM SET DG_BROKER_START=TRUE.
The default destination can be modified using the parametersDG_BROKER_CONFIG_FILE1 and DG_BROKER_CONFIG_FILE2
On Primary:
On Standby:
-
7/28/2019 Oracle 11G Dataguard Configuration
10/18
12.
Creating DGMGRL Configuration
Add the standby to the configuration and check it
DGMGRL>ADD DATABASE DB1_PB ASCONNECT IDENTIFIER IS DB1STANDBYMAINTAINED AS PHYSICAL;
13.
Show the configuration information.
-
7/28/2019 Oracle 11G Dataguard Configuration
11/18
Show Database Configuration for both nodes :
14. Enabling the configuration and databases
DGMGRL> enable configuration;Enabled.DGMGRL> show configuration;
Then Show the details :
-
7/28/2019 Oracle 11G Dataguard Configuration
12/18
15. Adding standby redo logs on all databases
On Primary :
SQL >ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 /u01/DB1/standby01.log SIZE50M;
SQL >ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 /u01/DB1/standby02.log SIZE50M;
SQL >ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 /u01/DB1/standby03.log SIZE
50M;
Verify
16. Enable Flashback Database on the Primary and Standby databases.
On Primary:
-
7/28/2019 Oracle 11G Dataguard Configuration
13/18
On Standby :
Sql> shu immediate:
Sql>startup mount;
-
7/28/2019 Oracle 11G Dataguard Configuration
14/18
17. Edit Database Properties
LogXptMode
Broker will set the primary to use asynchronous log transport by default. For Maximum
Availability environments, change this to synchronous.
These commands will succeed only if database is configured with standby redo logs.
DGMGRL> EDIT DATABASE DB1_WA SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated
DGMGRL> EDIT DATABASE DB1_PB SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated
18. Enable Fast-Start Failover
This is a good time to enable FSFO to make sure that all of the prerequisites have been met.
Broker will verify that the configuration meets all prerequisites before enabling FSFO and will
report any problems it finds. The most common problems are mismatched Data Guard protection
modes and LogXptMode properties and forgetting to enable Flashback Database on the primary or
standby.
Specify the FastStartFailoverTarget property
Enable Fast-Start Failover
-
7/28/2019 Oracle 11G Dataguard Configuration
15/18
Now Check :
19. Configure the Observer
To maximize the benefits of FSFO, the observer should run on a different host than the primary
and standby databases. Ideally the primary, standby, and observer will be in geographically
separate areas. The observer is very lightweight, requiring few system resources. Unlike the
primary / standby interconnect, where bandwidth and latency are determining performance
factors, the observer requires very little network bandwidth and is not overly latency sensitive,
allowing the it to be placed practically anywhere a reliable connection is available.
Since the observer is a specialized instance of a dgmgrl session, the observer host should be
installed with either the Oracle Client Administrator software or the full Oracle Database software
stack.
Verify connectivity to the primary and standby from Third Node
(UNIWAORADBR01-observer )
-
7/28/2019 Oracle 11G Dataguard Configuration
16/18
20.Start the observer
Start the observer from a third server
Now Verify The Complete Configuration
-
7/28/2019 Oracle 11G Dataguard Configuration
17/18
Testing the Configuration
The real test of the configuration is a successful role transition in both directions with both
switchover and FSFO failover. We'll start with switchovers.
Test switchover in both directions
In order to fully automate switchover, Broker needs SYSDBA credentials in order to restart one or
both databases. Without the credentials, Broker will complete the role transition, but will leave
the databases in need of a manual restart.
1.
Perform a Switchover
Check the configuration First before switchover.
Connect to DGMGRL on the observer server:
-
7/28/2019 Oracle 11G Dataguard Configuration
18/18
Now Check Database Role :
8.
Click Next.
9.Click Finish.
10. Verify the Installation by Using the Command : Select Server property(product version)