Hi Dears,
In this article, we will see Configure Active DataGuard and DG BROKER.
Oracle Active Data Guard allows DBAs and users to run real-time queries on a physical standby system for reporting.
Queries and reports can be offloaded from the production system to a synchronized physical standby database – all queries at the standby database return up-to-date results.
1. Make sure archive log enabled in primary database.
|
1 |
ARCHIVE LOG LIST |
2. Enabled forced logging and try to switch logfile
|
1 2 |
ALTER DATABASE FORCE LOGGING; ALTER SYSTEM SWITCH LOGFILE; |
The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter. For this example, the standby database will have the value “TESTDB_DG”.
|
1 |
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(TESTDB,TESTDB_DG)'; |
3. Set suitable remote archive log destinations for both the database
|
1 2 |
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=TESTDB_DG NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TESTDB_DG'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; |
4. The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters must be set to appropriate values and the REMOTE_LOGIN_PASSWORDFILE must be set to exclusive
|
1 2 3 |
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4; ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE; |
5. Set the parameter in primary database
|
1 2 |
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; ALTER SYSTEM SET FAL_SERVER=TESTDB_DG |
6. Entries for the primary and standby databases in “$ORACLE_HOME/network/admin/tnsnames.ora” files on both servers
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
TESTDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdb) ) ) TESTDB_DG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = lnx02.oracle.com)(PORT = 1521)) (CONNECT_DATA = (UR=A) (SERVER = DEDICATED) (SERVICE_NAME = testdb_dg) ) ) |
7. Create standby Control file and PFILE
|
1 2 |
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/testdb_dg.ctl'; CREATE PFILE =’/tmp/inittestdb_dg.ora’ from SPFILE; |
8. Change the necessary parameter according to Standby Database
|
1 2 3 |
*.db_unique_name='TESTDB_DG' *.fal_server='TESTDB' *.log_archive_dest_2='SERVICE=TESTDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TESTDB' |
Step for Standby Database preparation
1. Create the necessary directories on the standby server
|
1 2 3 |
mkdir -p /u01/app/oracle/product/product/18.0.0/dbhome_1/ mkdir -p /u01/app/oracle/diag mkdir -p /u01/app/oracle/admin/testdb/adump |
2. Copy password file from primary to standby database
|
1 2 3 4 5 6 7 |
Login to primary database and copy the password file to local file system ASMCMD> cp pwdtestdb.288.1042913219 /tmp copying +DATA/TESTDB/PASSWORD/pwdtestdb.288.1042913219 -> /tmp/pwdtestdb.288.1042913219 From standby server do sftp to primary database server sftp> get pwdtestdb.288.1042913219 /tmp Fetching /tmp/pwdtestdb.288.1042913219 to /tmp/pwdtestdb.288.1042913219 /tmp/pwdtestdb.288.1042913219 |
3. Create standby database using DUPLICATE command
|
1 2 3 4 |
$ export ORACLE_SID=TESTDB_DG $ export ORACLE_HOME= /u01/app/oracle/product/18.0.0.0/db_1/ $ sqlplus / as sysdba STARTUP NOMOUNT PFILE='/tmp/inittestdb_dg.ora'; |
4. Connect to RMAN, specifying a full connect string for both the TARGET and AUXILIARY instances
|
1 2 3 4 5 |
rman TARGET sys/****@testdb AUXILIARY sys/*****@testdb_dg DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE; |
5. Switch standby database to Read only mode
|
1 2 3 |
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE OPEN READ ONLY; |
6. Start MRP process in standby database
|
1 |
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; |
Enable DATAGUARD BROKER
1. Connect to both databases (primary and standby) and issue the following command
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
ALTER SYSTEM SET dg_broker_start=true; dgmgrl sys/*****@testdb Connected as SYSDBA. Add Primary database: DGMGRL> CREATE CONFIGURATION my_dg_config AS PRIMARY DATABASE IS testdb CONNECT IDENTIFIER IS testdb; Add Standby database: DGMGRL> ADD DATABASE testdb_dg AS CONNECT IDENTIFIER IS testdb_dg MAINTAINED AS PHYSICAL; DGMGRL> ENABLE CONFIGURATION; |
2. Check configuration and status of the databases from the broker
|
1 2 3 |
DGMGRL> SHOW CONFIGURATION; DGMGRL> SHOW DATABASE TESTDB; DGMGRL> SHOW DATABASE TESTDB_DG |
3. Switch over and Fail over database using DATAGUARD broker
|
1 2 3 4 5 6 7 8 9 10 |
$ dgmgrl sys/*****@testdg DGMGRL for Linux: Release 18.0.0.0.0 - Production on Sat Sep 1 09:39:33 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected as SYSDBA. DGMGRL> SWITCHOVER TO testdb_dg; DGMGRL> FAILOVER to testdb_dg |
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Note: Please test scripts in Non Prod before trying in Production.
Loading...
