Oracle 19c - Data Guard - Mult SBs (Vars)
- Prepare Primary
- Prepare SB
- Status
- Configure Data Guard
- Enable Standby READ-ONLY Mode
- Post Creation Startup
- Add Standby to DG
Vars Version
Overview
Oracle Data Guard consists of services to create and maintain standby databases. What follows are example specs to create a multiple SB Data Guard environment. Each SB was create one at a time.
Session Specifications
Initial Role System DB_NAME DB_UNIQUE_NAME DB File Paths /u01-2/oradata/<name> ------------ ------ ------- -------------- ---------------------------------------- Primary(PRI) aaa dnaprod dnaprod DNAPROD Standby(SB) bbb dnaprod dnaprod_bbb DNAPROD_BBB Standby(SB) ccc dnaprod dnaprod_ccc DNAPROD_CCC Standby(SB) ddd dnaprod dnaprod_ddd DNAPROD_DDD
ORACLE_HOME
/u01/app/oracle/product/19.3.0.0.0/dbhome_1
/etc/hosts entries
192.168.56.71 aaa.local aaa 192.168.56.72 bbb.local bbb 192.168.56.73 ccc.local ccc 192.168.56.74 ddd.local ddd
SB Logs (once created)
Alert log: /u01/app/oracle/diag/rdbms/dnaprod_sb/dnaprod/trace/alert_dnaprod.log Data Guard Broker log: /u01/app/oracle/diag/rdbms/dnaprod_sb/dnaprod/trace/drcdnaprod.log
Prerequisite
- On the Primary and Standby, the Linux versions (OEL 8), Oracle versions (19c), and Oracle patch versons must be the same.
- Primary already has database created, SBs just Oracle database product installed.
- Latest Opatch and Oracle patches applied.
- Confirm Connectivity
- ping aaa
- ping bbb
- ping ccc
- ping ddd
Fun Facts
- Log_Archive_dest_n is only needed on the original PRI during creation.
- SB databases are receivers of archive redo logs, not senders. They only apply the archive redo logs shipped from the Primary to keep their data synchronized.
- Configuring Log_Archive_dest_n on a SB would be redundant because it doesn't generate its own archive redo logs.
- Exception: If you have an extended disaster or need to have the SB permanently become the PRI, then you will need archive redo logs shipped to the other SB locations. These docs reflect that scenario.
Prepare Primary
Enable archivelog
sqlplus / as sysdba
select log_mode from v$database;
ARCHIVELOG
If LOG_MODE = NOARCHIVELOG then:
alter system set log_archive_dest_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both; shutdown immediate startup mount alter database archivelog; alter database open; alter system switch logfile; select name from v$archived_log;
Enable Force Logging
select force_logging from v$database; alter database force logging;
Get Redo Log File Info
select group#, thread#, bytes/1024/1024 mb from v$log; GROUP# THREAD# MB ---------- ---------- ---------- 1 1 200 2 1 200 3 1 200 4 1 200 select thread#, instance from v$thread ; THREAD# INSTANCE ---------- ---------- 1 dnaprod
Create SB Redo Logs
- Make sure to keep the thread# and logfile size exactly same.
- Also, create one additional standby redo log.
alter database add standby logfile thread 1 size 200m; alter database add standby logfile thread 1 size 200m; alter database add standby logfile thread 1 size 200m; alter database add standby logfile thread 1 size 200m; alter database add standby logfile thread 1 size 200m; select group#, thread#, sequence#, bytes/1024/1024 mb, archived, status from v$standby_log; select member from v$logfile where type = 'STANDBY';
In this session 4 redo logs found above and their size is 200m, so create 5 standby logfiles of 200m each.
Show db_unique_name
show parameter db_unique_name
dnaprod
Set log_archive_config
Uses the DB_UNIQUE_NAME.
alter system set log_archive_config='DG_CONFIG= (dnaprod,dnaprod_bbb,dnaprod_ ccc,dnaprod_ddd)' scope=both;
Put this on one line.
Set log_archive_dest_n
alter system set LOG_ARCHIVE_DEST_n='SERVICE="<DB_UNIQUE_NAME>" ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=<DB_UNIQUE_NAME>'; alter system set LOG_ARCHIVE_DEST_2='SERVICE="dnaprod_bbb" ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dnaprod_bbb';
Just configure current SB. When DG configured it will update these.
Set fal_server
show parameter fal_server; alter system set fal_server ='dnaprod,dnaprod_bbb,dnaprod_ccc,dnaprod_ddd' scope=both;
- fal_client is not needed anymore in Oracle 19c.
- After DG configured: PRI will have no value & SBs will show all but currrent.
Set standby_file_management
show parameter standby_file_management alter system set standby_file_management='AUTO' scope=both;
Enable flashback
alter database flashback on;
Confirm Password File Exists
ls $ORACLE_HOME/dbs/orapw*
/u01/app/oracle/product/19.3.0.0.0/dbhome_1/dbs/orapwdnaprod
Create\Edit tnsnames.ora (PRI and SB)
These values are for both PRI and SB. cd $ORACLE_HOME/network/admin
DNAPROD,dnaprod =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = aaa)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dnaprod)
)
)
DNAPROD_BBB,dnaprod_bbb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bbb)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dnaprod_bbb)
)
)
DNAPROD_CCC,dnaprod_ccc =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ccc)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dnaprod_ccc)
)
)
DNAPROD_DDD,dnaprod_ddd =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ddd)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dnaprod_ddd)
)
)
Create\Edit listener.ora
cd $ORACLE_HOME/network/admin
PRI
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = aaa)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=dnaprod)
(GLOBAL_DBNAME = dnaprod)
(ORACLE_HOME=/u01/app/oracle/product/19.3.0.0.0/dbhome_1)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Reload the listener
lsnrctl reload
SB
bbb
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bbb)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=dnaprod)
(GLOBAL_DBNAME = dnaprod_bbb)
(ORACLE_HOME=/u01/app/oracle/product/19.3.0.0.0/dbhome_1)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
ccc
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ccc)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=dnaprod)
(GLOBAL_DBNAME = dnaprod_ ccc)
(ORACLE_HOME=/u01/app/oracle/product/19.3.0.0.0/dbhome_1)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
DDD
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ddd)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=dnaprod)
(GLOBAL_DBNAME = dnaprod_ddd)
(ORACLE_HOME=/u01/app/oracle/product/19.3.0.0.0/dbhome_1)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Bounce Primary Database and Listener
-- Stop sqlplus / as sysdba sqlplus> shutdown immediate lsnrctl stop -- Start lsnrctl start sqlplus / as sysdba sqlplus> startup
Prepare Standby
Ensure Net Files Configured
Create Dirs on All Systems
Login as root to make dir changes.
aaa Dirs (already created on Primary\aaa during DBCA)
mkdir -p /u01/app/oracle/admin/dnaprod/adump chmod -R 775 /u01/app/oracle/admin/dnaprod/adump mkdir -p /u01/oradata/DNAPROD/controlfile mkdir -p /u01/oradata/DNAPROD/datafile mkdir -p /u01/oradata/DNAPROD/onlinelog mkdir -p /u02/oradata/DNAPROD/archivelog mkdir -p /u02/oradata/DNAPROD/autobackup mkdir -p /u02/oradata/DNAPROD/controlfile mkdir -p /u02/oradata/DNAPROD/onlinelog
BBB Dirs
mkdir -p /u01/app/oracle/admin/dnaprod_bbb/adump chmod -R 775 /u01/app/oracle/admin/dnaprod_bbb/adump mkdir -p /u01/oradata/DNAPROD_BBB/controlfile mkdir -p /u01/oradata/DNAPROD_BBB/datafile mkdir -p /u01/oradata/DNAPROD_BBB/onlinelog mkdir -p /u02/oradata/DNAPROD_BBB/archivelog mkdir -p /u02/oradata/DNAPROD_BBB/autobackup mkdir -p /u02/oradata/DNAPROD_BBB/controlfile mkdir -p /u02/oradata/DNAPROD_BBB/onlinelog
CCC Dirs
mkdir -p /u01/app/oracle/admin/dnaprod_ccc/adump chmod -R 775 /u01/app/oracle/admin/dnaprod_ccc/adump mkdir -p /u01/oradata/DNAPROD_CCC/controlfile mkdir -p /u01/oradata/DNAPROD_CCC/datafile mkdir -p /u01/oradata/DNAPROD_CCC/onlinelog mkdir -p /u02/oradata/DNAPROD_CCC/archivelog mkdir -p /u02/oradata/DNAPROD_CCC/autobackup mkdir -p /u02/oradata/DNAPROD_CCC/controlfile mkdir -p /u02/oradata/DNAPROD_CCC/onlinelog
DDD Dirs
mkdir -p /u01/app/oracle/admin/dnaprod_ddd/adump chmod -R 775 /u01/app/oracle/admin/dnaprod_ddd/adump mkdir -p /u01/oradata/DNAPROD_DDD/controlfile mkdir -p /u01/oradata/DNAPROD_DDD/datafile mkdir -p /u01/oradata/DNAPROD_DDD/onlinelog mkdir -p /u02/oradata/DNAPROD_DDD/archivelog mkdir -p /u02/oradata/DNAPROD_DDD/autobackup mkdir -p /u02/oradata/DNAPROD_DDD/controlfile mkdir -p /u02/oradata/DNAPROD_DDD/onlinelog
Admin & Oradata Privs (set this on all SBs)
chown -R oracle:oinstall /u01/app/oracle/admin chown -R oracle:oinstall /u01/oradata chown -R oracle:oinstall /u02/oradata chmod -R 775 /u01/oradata chmod -R 775 /u02/oradata
Create Init Files
lnx02> echo 'DB_NAME=dnaprod' > $ORACLE_HOME/dbs/initdnaprod.ora
Copy the password file from the primary server to the standby server.
lnx01> scp /u01/app/oracle/product/19.3.0.0.0/dbhome_1/dbs/orapwdnaprod
oracle@lnx02:/u01/app/oracle/product/19.3.0.0.0/dbhome_1/dbs/orapwdnaprod
Place on one line to execute.
Confirm On SB
lnx02> ls -al /u01/app/oracle/product/19.3.0.0.0/dbhome_1/dbs/orapwdnaprod
Start listener on SB
lsnrctl start
Confirm TNS
Run from Both PRI and SB.
tnsping DNAPROD tnsping DNAPROD_BBB tnsping DNAPROD_CCC tnsping DNAPROD_DDD
Just tnsping SBs cfg'd thus far.
Set ORACLE_SID on SB
export ORACLE_SID=dnaprod
Ensure set in .bashrc also, ex: export ORACLE_SID=dnaprod
Start the SB in NOMOUNT
sqlplus / as sysdba STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/initdnaprod.ora'; exit
From SB, Duplicate Database
Connect to RMAN
- Set dnaprod_sb to current SB (bbb, ccc, ddd)
Connect to the primary database and to the standby at the same time.
rman TARGET sys/*********@dnaprod AUXILIARY sys/*********@dnaprod_sb
Confirm RMAN Connected OK
Ensure shows connected OK to both as so:
connected to target database: DNAPROD (DBID=nnnnnnnnnn) connected to auxiliary database: DNAPROD (not mounted)
Run RMAN Cmds to DUPLICATE
Set Per Each SB (bbb, ccc, ddd):
- DB_UNIQUE_NAME 'dnaprod_sb'
- SET AUDIT_FILE_DEST '/u01/app/oracle/admin/dnaprod_sb/adump'
- SET CONTROL_FILES '/u01/oradata/DNAPROD_SB/controlfile/control01.ctl'
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET DB_UNIQUE_NAME 'dnaprod_sb' COMMENT 'Is standby'
SET LOG_ARCHIVE_DEST_2 'SERVICE=DNAPROD ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dnaprod'
SET AUDIT_FILE_DEST '/u01/app/oracle/admin/dnaprod_sb/adump'
SET CONTROL_FILES '/u01/oradata/DNAPROD_SB/controlfile/control01.ctl'
SET FAL_SERVER 'dnaprod'
NOFILENAMECHECK;
Example Output Upon Successful Completion
Finished Duplicate Db at 23-APR-24
Once the DUPLICATE command is done, the standby database has been created.
If DG Not Enabled Yet or SB Status Shows Not Transferring
alter database recover managed standby database disconnect from session;
Take Note (If DG cfg'd on PRI and other SBs)
- This new SB will not auto update until DG configured.
- You can get this SB to update only if you bounce SB and manually issue recover.
alter database recover managed standby database disconnect from session;
Status
-- DB Status: Both
COL database_role FORMAT a20
COL db_unique_name FORMAT a15
COL instance FORMAT a15
COL open_mode FORMAT a15
COL protection_level FORMAT a25
COL switchover_status FORMAT a15
SELECT database_role, db_unique_name Instance,
open_mode, protection_level, switchover_status
FROM V$DATABASE;
-- archive log: Primary
archive log list
-- SB Check (after DG configured)
SELECT process,thread#,sequence#,status FROM v$managed_standby WHERE process='MRP0';
-- Transfer Check
-- On Both SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG) GROUP BY THREAD#; -- On Primary ALTER SYSTEM SWITCH LOGFILE; -- On Both SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG) GROUP BY THREAD#;
Configure Data Guard
Add DGMGRL Entry to listener.ora SID_LIST_LISTENER
PRI
(SID_DESC=
(GLOBAL_DBNAME=dnaprod_DGMGRL)
(SID_NAME=dnaprod)
(ORACLE_HOME=/u01/app/oracle/product/19.3.0.0.0/dbhome_1)
)
SB
bbb
(SID_DESC=
(GLOBAL_DBNAME=dnaprod_bbb_DGMGRL)
(SID_NAME=dnaprod)
(ORACLE_HOME=/u01/app/oracle/product/19.3.0.0.0/dbhome_1)
)
ccc
(SID_DESC=
(GLOBAL_DBNAME=dnaprod_ccc_DGMGRL)
(SID_NAME=dnaprod)
(ORACLE_HOME=/u01/app/oracle/product/19.3.0.0.0/dbhome_1)
)
ddd
(SID_DESC=
(GLOBAL_DBNAME=dnaprod_ddd_DGMGRL)
(SID_NAME=dnaprod)
(ORACLE_HOME=/u01/app/oracle/product/19.3.0.0.0/dbhome_1)
)
Reload Listner
lsnrctl reload
Broker Configuration
-- On Both SQLPlus> ALTER SYSTEM SET dg_broker_start=true scope=both; -- Legacy Cmd? -- show parameter log_archive_dest_2 -- SQLPlus> ALTER SYSTEM SET log_archive_dest_2='' scope=both; -- LOG_ARCHIVE_DEST_2 is valid only for the primary role. -- On Primary dgmgrl sys/*********@dnaprod DGMGRL> CREATE CONFIGURATION dg_cfg1 AS PRIMARY DATABASE IS dnaprod CONNECT IDENTIFIER IS dnaprod; DGMGRL> CREATE CONFIGURATION dg_cfg1 AS PRIMARY DATABASE IS dnaprod CONNECT IDENTIFIER IS dnaprod; Configuration "dg_cfg1" created with primary database "dnaprod" DGMGRL> ADD DATABASE dnaprod_bbb AS CONNECT IDENTIFIER IS dnaprod_bbb MAINTAINED AS PHYSICAL; Database "dnaprod_bbb" added DGMGRL> ADD DATABASE dnaprod_ccc AS CONNECT IDENTIFIER IS dnaprod_ccc MAINTAINED AS PHYSICAL; Database "dnaprod_ccc" added DGMGRL> ADD DATABASE dnaprod_ddd AS CONNECT IDENTIFIER IS dnaprod_ddd MAINTAINED AS PHYSICAL; Database "dnaprod_ddd" added DGMGRL> ENABLE CONFIGURATION; Enabled.
Above is by-the-book. In reality, you probably will need to do this after first SB added.
Enable Flashback Database
-- On Primary
sqlplus / as sysdba SELECT flashback_on FROM v$database; -- If Needed on PRI ALTER DATABASE FLASHBACK ON;
-- For Each SB
Set _sb to current SB (beta, ccc, ddd) dgmgrl sys/*********@oradb_sb DGMGRL> edit database dnaprod_sb set state=apply-off; DGMGRL> sql "alter database flashback on"; DGMGRL> edit database dnaprod_sb set state=apply-on; -- On Both (QC check) SQLPlus> SELECT flashback_on FROM v$database;
Enable Standby READ-ONLY Mode
-- On SB
sqlplus / as sysdba --STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/initdnaprod_sb.ora'; alter database open read only;
Below Not Needed after DG Enabled
alter database recover managed standby database disconnect from session;
Post Creation Startup
Ensure ORACLE_SID set in .bashrc
export ORACLE_SID=dnaprod
After creation, if you ever need to restart the SB
sqlplus / as sysdba
startup mount
alter database open read only; -- If Licensed
Optimally configure the dbora script and change startup command to startup mount in the $ORACLE_HOME/bin/dbstart.
Add Standby to DG
- This cleanly purges and recreates cfg.
- Commonly required to add 3rd, 4th...SB.
-- Remove DG Cfg
dgmgrl sys/go@dnaprod remove configuration;
-- Set DG_BROKER_START=FALSE On All (Pri and SBs)
ALTER SYSTEM SET DG_BROKER_START=FALSE scope=both sid='*'; show parameter dg_broker_config_file
-- Purge OS Files on PRI
cd $ORACLE_HOME/dbs ls dr* rm dr?dnaprod.dat
-- Clear LOG_ARCHIVE_DEST_n
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='' scope=both sid='*'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='' scope=both sid='*'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_4='' scope=both sid='*'; show parameter log_archive_des
-- Bounce PRI
sqlplus / as sysdba shutdown immediately startup show parameter log_archive_dest
-- Set DG_BROKER_START=TRUE On All (Pri and SBs)
ALTER SYSTEM SET DG_BROKER_START=TRUE scope=both sid='*'; show parameter DG_BROKER
If Primary, bounce database.
-- On PRI, Configure DG dgmgrl sys/go@dnaprod
CREATE CONFIGURATION dg_cfg1 AS PRIMARY DATABASE IS dnaprod CONNECT IDENTIFIER IS dnaprod; ADD DATABASE dnaprod_bbb AS CONNECT IDENTIFIER IS dnaprod_bbb MAINTAINED AS PHYSICAL; ADD DATABASE dnaprod_ccc AS CONNECT IDENTIFIER IS dnaprod_ccc MAINTAINED AS PHYSICAL; ADD DATABASE dnaprod_ddd AS CONNECT IDENTIFIER IS dnaprod_ddd MAINTAINED AS PHYSICAL; ENABLE CONFIGURATION;
Cfg just all SBs active thus far.
References
DBA