Description:
In this post I will describe the step by step configuration of Oracle 12c Dataguard physical standby setup with dataguard broker(DGMGRL) on Oracle Linux 7
| Environment | DB_Name | DB_Unique_name |
| Primary | cdb12c | cdb12c |
| Standby | cdb12c | cdb12cdr |
We will be covering the following steps:
Primary Server setup
- Enable archive log mode
- Enable Force logging
- Create standby redologs SRLs
- Check db_name and db_unique_name
- Set standby_file_management
- TNS and listener Setup
- Copy pfile and password to standby
Standby Server setup
- Modify the pfile
- Create necessary directories
- Create standby database using RMAN Duplicate
Configure Dataguard broker
- Enable dataguard broker
- Register the database with dataguard broker
- Add standby database to dataguard broker
- Enable configuration
Primary Server setup
1. Enable archive log mode
Ensure the primary database is in archivelog mode. If not enable it.
alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/arch';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;2. Enable force logging:
Make sure Force logging is enabled on the database.
ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SWITCH LOGFILE;3. Create standby redo logs on the primary database
Create the standby redo logs to support standby role. The recommended number of SRLs is (Number of redologs + 1). The SRLs size must be same as online redo logs size.
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 ('/u01/oradata/dev12c/std_redo12.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 ('/u01/oradata/dev12c/std_redo13.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 14 ('/u01/oradata/dev12c/std_redo14.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 15 ('/u01/oradata/dev12c/std_redo15.log') SIZE 200M;4. Check pfile for db_name and db_unique_name
Make sure primary database has DB_UNIQUE_NAME set. If not set it using ALTER SYSTEM SET command.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string dev12c
SQL> show parameter unique
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string dev12c
SQL>5. Set STANDBY_FILE_MANAGEMENT to AUTO
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;6. TNS and LISTENER setup(for both primary & standby)
Tnsnames configuration:
DEV12C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol7-db.localdomain)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dev12c)
)
)
DEV12CDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol7-db-dr.localdomain)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dev12c)
)
)
LISTENER_DEV12C =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol7-db.localdomain)(PORT = 1522))Listener.ora configuration
LISTENER12C =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol7-db.localdomain)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
SID_LIST_LISTENER12C =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dev12c_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME = dev12c)
)
)Restart the listener(On both primary & standby)
lsnrctl stop listener12c
lsnrctl start listener12c7. Copy pfile and password file to standby server
create pfile='/tmp/initdev12c.ora' from spfile;
scp initdev12c.ora oracle@ol7-db-dr:/tmp
scp $ORACLE_HOME/dbs/orapwdev12c oracle@ol7-db-dr:/u01/app/oracle/product/12.2.0/dbhome_1/dbsStandby Server setup
1. modify the pfile copied from primary and make change for the below parameters
*.db_name=dev12c
*.db_unique_name=dev12cdr
enable_pluggable_database=true
log_file_name_convert='dummy','dummy'2. Create the necessary directories
mkdir -p /u01/app/oracle/admin/dev12c/adump
mkdir -p /u01/oradata/dev12c/pdbseed
mkdir -p /u01/oradata/dev12c/pdb12c
mkdir -p /u01/app/oracle/fra
mkdir -p /u01/app/oracle/arch3. Create standby using RMAN DUPLICATE
Start the auxiliary database instance using the pfile
export ORACLE_SID=dev12c
sqlplus "/as sysdba"
startup nomount pfile='/u01/initdev12c.ora';Connect to RMAN and issue the duplicate command,
rman target sys/idgroup@dev12c auxiliary sys/idgroup@dev12cdr
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;Configure Datagurar Broker
1. Enable dataguard broker on both primary & standby
ALTER SYSTEM SET dg_broker_start=true;2. On primary side: register the database with dataguard broker
dgmgrl sys/idgroup@dev12c
create configuration dgconfig as primary database is dev12c connect identifier is dev12c;
o/p:
[oracle@ol7-db admin]$ dgmgrl sys/idgroup@dev12c
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Sat Jul 17 12:25:24 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "dev12c"
Connected as SYSDBA.
DGMGRL>
DGMGRL> create configuration dgconfig as primary database is dev12c connect identifier is dev12c;
Configuration "dgconfig" created with primary database "dev12c"
DGMGRL>3. On primary side: add the standby database
add database dev12cdr as connect identifier is dev12cdr maintained as physical;
o/p:
DGMGRL> add database dev12cdr as connect identifier is dev12cdr maintained as physical;
Database "dev12cdr" added
DGMGRL>4. On Primary side: enable the configuration
enable configuration;
o/p:
DGMGRL> enable configuration;
Enabled.
DGMGRL>Check the configuration
DGMGRL> show configuration
Configuration - dgconfig
Protection Mode: MaxPerformance
Members:
dev12c - Primary database
dev12cdr - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 41 seconds ago)
DGMGRL>
DGMGRL> show database dev12c
Database - dev12c
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
dev12c
Database Status:
SUCCESS
DGMGRL>
DGMGRL> show database dev12cdr
Database - dev12cdr
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 1.00 KByte/s
Real Time Query: OFF
Instance(s):
dev12c
Database Status:
SUCCESS
DGMGRL>
Hope this helps…
Visit Today : 271
Total Visit : 423816
Total Hits : 1019392
Who's Online : 7