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