Source file
In today's data-driven world, ensuring high availability and disaster recovery for your databases is crucial. Oracle Data Guard is a powerful solution that helps you maintain and protect your data by providing standby databases that can take over in case of primary database failure. In this blog, we'll walk you through the steps to set up Oracle Data Guard, ensuring your database environment is robust and resilient.
What is Oracle Data Guard?
Oracle Data Guard is a feature of the Oracle Database that helps you achieve high availability, data protection, and disaster recovery. It allows you to create and maintain standby databases that can automatically or manually take over when the primary database fails. Data Guard supports both physical and logical standby databases, giving you flexibility based on your business needs.
1. Prerequisites
=============
Here I have listed as my system configurations. It may vary compared your system configuration. Before applying production environment I would suggest that 1st test the configuration on test environment.
Environment : Primary
Server name : dg1.bracececoder
server version : Oracle Linux 7.9
IP Address : 192.168.1.108
Database name : orcl
Global name : orcl.bracecoder
database version : 19c (19.3.0.0)
DB Home Location : /u01/app/oracle/product/19.3.0.0/dbhome_1
archive location : /u01/archive
TNS name : PRIMARYTNS
Environment : Physical Standby
Server name : dg2.bracececoder
server version : Oracle Linux 7.9
IP Address : 192.168.1.109
Database name : Software only (Database not created)
Global name : NA
database version : 19c (19.3.0.0)
DB Home Location : /u01/app/oracle/product/19.0.0/db_1
archive location : /u01/archive
TNS name : STANDBYTNS
2. enable archivelog mode on primary
=================================
Data Guard requires archive logging to ensure that all changes are saved and can be applied to the standby database.
[oracle@dg1 ~]$ . oraenv
ORACLE_SID = [orcl] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@dg1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Oct 12 23:35:45 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 1526722880 bytes
Fixed Size 8896832 bytes
Variable Size 889192448 bytes
Database Buffers 620756992 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/arch
Oldest online log sequence 4
Current log sequence 6
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1526722880 bytes
Fixed Size 8896832 bytes
Variable Size 889192448 bytes
Database Buffers 620756992 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/arch
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
SQL>
3. set parameter values on primary
===============================
alter system set log_archive_config='dg_config=(<PRIMARY_DB_TNS_NAME>,<STANDBY_DB_TNS_NAME>)';
alter system set log_archive_dest_1='location=<NEW_ARCHIVELOG_LOCATION> valid_for=(all_logfiles,primary_role)';
alter system set db_unique_name=<DB_UNIQUE_NAME> scope=spfile;
[oracle@dg1 admin]$ mkdir -p /u01/archive
SQL> alter system set log_archive_config='dg_config=(PRIMARYTNS,STANDBYTNS)';
System altered.
SQL> alter system set log_archive_dest_1='location=/u01/archive valid_for=(all_logfiles,primary_role)';
System altered.
SQL> alter system set log_archive_dest_2=' ';
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> alter system set log_archive_dest_state_1=enable;
System altered.
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
System altered.
SQL> alter system set standby_file_management=auto;
System altered.
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
System altered.
SQL> alter system set log_archive_max_processes=10;
System altered.
SQL> alter system set db_unique_name=primaryorcl scope=spfile;
System altered.
SQL> alter system set fal_client=PRIMARYTNS;
System altered.
SQL> alter system set fal_server=STANDBYTNS;
System altered.
SQL> show parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL>
SQL>
SQL> ALTER system set standby_file_management=manual scope=both;
System altered.
SQL> ALTER DATABASE FORCE LOGGING;
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1526722880 bytes
Fixed Size 8896832 bytes
Variable Size 889192448 bytes
Database Buffers 620756992 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> create pfile='/home/oracle/initorcl.ora' from spfile;
File created.
SQL>
4. Copy generated pfile and password file from primary to standby
==============================================================
Copy /home/oracle/initorcl.ora from primary to /u01/app/oracle/product/19.0.0/db_1 on standby
In standby edit pfile using vi editor
*.db_unique_name='STANDBYORCL'
*.log_archive_dest_1='location=/u01/archive valid_for=(all_logfiles,primary_role)'
=============================================
5. Configure netca/netmgr on both node
===================================
Primary server
--------------
[oracle@dg1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.3.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.108)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
(SID_DESC =
(GLOBAL_DBNAME = primary_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0.0/dbhome_1)
(SID_NAME = ORCL)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@dg1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.3.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PRIMARYTNS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.108)(PORT = 1521))
(LOAD_BALANCE = no)
(FAILOVER = yes)
)
(CONNECT_DATA =
(SERVICE_NAME = PRIMARYORCL.bracecoder)
(SERVER = DEDICATED)
(UR = A)
)
)
STANDBYTNS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.109)(PORT = 1521))
(LOAD_BALANCE = no)
(FAILOVER = yes)
)
(CONNECT_DATA =
(SERVICE_NAME = STANDBYORCL.bracecoder)
(SERVER = DEDICATED)
(UR = A)
)
)
[oracle@dg1 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/19.3.0.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
[oracle@dg1 admin]$
Standby server
--------------
[oracle@dg2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.109)(PORT = 1521))
(LOAD_BALANCE = yes)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =(GLOBAL_DBNAME = standby_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1 )
(SID_NAME = orcl)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@dg2 admin]$ cat tnsnames.ora
PRIMARYTNS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.108)(PORT = 1521))
(LOAD_BALANCE = no)
(FAILOVER = yes)
)
(CONNECT_DATA =
(SERVICE_NAME = PRIMARYORCL.bracecoder)
(UR = A)
(SERVER = DEDICATED)
)
)
STANDBYTNS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.109)(PORT = 1521))
(LOAD_BALANCE = no)
(FAILOVER = yes)
)
(CONNECT_DATA =
(SERVICE_NAME = STANDBYORCL.bracecoder)
(SERVER = DEDICATED)
(UR = A)
)
)
[oracle@dg2 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/19.0.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
[oracle@dg2 admin]$
6. Standby configuration
=====================
[oracle@dg2 admin]$ mkdir -p /u01/app/oracle/admin/orcl/adump
[oracle@dg2 admin]$ mkdir -p /u01/app/oracle/oradata/ORCL
[oracle@dg2 admin]$ mkdir -p /u01/app/oracle/fast_recovery_area/ORCL/
[oracle@dg2 admin]$ mkdir -p /u01/archive
[oracle@dg2 admin]$ . oraenv
ORACLE_SID = [orcl] ? orcl
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.0.0/db_1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@dg2 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 13 00:30:37 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1526722880 bytes
Fixed Size 8896832 bytes
Variable Size 889192448 bytes
Database Buffers 620756992 bytes
Redo Buffers 7876608 bytes
SQL> alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.109)(PORT = 1521))';
System altered.
template
--------
create spfile='$ORACLE_HOME/dbs/spfile<INSTANCE_NAME>.ora' from pfile='$ORACLE_HOME/dbs/<INSTANCE_NAME>.ora';
SQL> create spfile='/u01/app/oracle/product/19.0.0/db_1/dbs/spfileorcl.ora' from pfile='/u01/app/oracle/product/19.0.0/db_1/dbs/initorcl.ora';
File created.
SQL> shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1526722880 bytes
Fixed Size 8896832 bytes
Variable Size 889192448 bytes
Database Buffers 620756992 bytes
Redo Buffers 7876608 bytes
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/19.0.0
/db_1/dbs/spfileorcl.ora
SQL> ALTER DATABASE FORCE LOGGING;
Setup listener on both node
---------------------------
SQL> show parameter listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
forward_listener string
listener_networks string
local_listener string
remote_listener string
template
--------
alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = <server_ip>)(PORT = 1521))';
SQL> alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.108)(PORT = 1521))';
System altered.
SQL> alter system register;
System altered.
SQL> alter system disable restricted session;
System altered.
SQL> show parameter listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
forward_listener string
listener_networks string
local_listener string (ADDRESS = (PROTOCOL = TCP)(HO
ST = 192.168.1.109)(PORT = 152
1))
remote_listener string
SQL>
SQL> alter system set REMOTE_OS_AUTHENT = FALSE scope = spfile;
Primary server
--------------
SQL> alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.108)(PORT = 1521))';
System altered.
SQL> alter system register;
System altered.
SQL> alter system disable restricted session;
System altered.
SQL> show parameter listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
forward_listener string
listener_networks string
local_listener string (ADDRESS = (PROTOCOL = TCP)(HO
ST = 192.168.1.108)(PORT = 152
1))
remote_listener string
SQL>
SQL> alter system set REMOTE_OS_AUTHENT = FALSE scope = spfile;
7. Clone database from primary to standby usig RMAN
================================================
[oracle@dg2 admin]$ rman target sys/admin@PRIMARYTNS auxiliary sys/admin@STANDBYTNS
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Oct 13 11:03:52 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1707365888)
connected to auxiliary database: ORCL (not mounted)
RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;
Starting Duplicate Db at 13-OCT-24
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1 device type=DISK
current log archived
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/u01/app/oracle/product/19.0.0/db_1/dbs/orapworcl' ;
}
executing Memory Script
Starting backup at 13-OCT-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=75 device type=DISK
Finished backup at 13-OCT-24
contents of Memory Script:
{
restore clone from service 'PRIMARYTNS' standby controlfile;
}
executing Memory Script
Starting restore at 13-OCT-24
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRIMARYTNS
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/ORCL/control01.ctl
output file name=/u01/app/oracle/oradata/ORCL/control02.ctl
Finished restore at 13-OCT-24
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/system01.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/sysaux01.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/undotbs01.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/users01.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (tempfile) file name /u01/app/oracle/oradata/ORCL/temp01.dbf conflicts with a file used by the target database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/ORCL/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/ORCL/system01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/ORCL/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/ORCL/undotbs01.dbf";
set newname for datafile 7 to
"/u01/app/oracle/oradata/ORCL/users01.dbf";
restore
from nonsparse from service
'PRIMARYTNS' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/ORCL/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 13-OCT-24
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRIMARYTNS
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCL/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRIMARYTNS
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRIMARYTNS
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCL/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRIMARYTNS
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 13-OCT-24
sql statement: alter system archive log current
current log archived
contents of Memory Script:
{
restore clone force from service 'PRIMARYTNS'
archivelog from scn 2140381;
switch clone datafile all;
}
executing Memory Script
Starting restore at 13-OCT-24
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service PRIMARYTNS
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=7
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service PRIMARYTNS
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 13-OCT-24
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1182266567 file name=/u01/app/oracle/oradata/ORCL/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1182266567 file name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1182266567 file name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1182266567 file name=/u01/app/oracle/oradata/ORCL/users01.dbf
contents of Memory Script:
{
set until scn 2140735;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 13-OCT-24
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/product/19.0.0/db_1/dbs/arch1_7_1179585160.arc
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/product/19.0.0/db_1/dbs/arch1_8_1179585160.arc
archived log file name=/u01/app/oracle/product/19.0.0/db_1/dbs/arch1_7_1179585160.arc thread=1 sequence=7
archived log file name=/u01/app/oracle/product/19.0.0/db_1/dbs/arch1_8_1179585160.arc thread=1 sequence=8
media recovery complete, elapsed time: 00:00:01
Finished recover at 13-OCT-24
contents of Memory Script:
{
delete clone force archivelog all;
}
executing Memory Script
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=75 device type=DISK
deleted archived log
archived log file name=/u01/app/oracle/product/19.0.0/db_1/dbs/arch1_7_1179585160.arc RECID=1 STAMP=1182266565
deleted archived log
archived log file name=/u01/app/oracle/product/19.0.0/db_1/dbs/arch1_8_1179585160.arc RECID=2 STAMP=1182266566
Deleted 2 objects
RMAN-05535: warning: All redo log files were not defined properly.
RMAN-05535: warning: All redo log files were not defined properly.
RMAN-05535: warning: All redo log files were not defined properly.
Finished Duplicate Db at 13-OCT-24
RMAN>
8. Crosscheck on standby/ open database
====================================
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 9 9 0
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 9 9 0
SQL> select status,instance_name,database_role from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
MOUNTED orcl PHYSICAL STANDBY
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
9
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
12 rows selected.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORCL MOUNTED
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database cancel;
Database altered.
9. Check log switch file location and add 3 standby logfile on both node
=====================================================================
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
no rows selected
SQL> select bytes from v$log;
BYTES
----------
209715200
209715200
209715200
SQL> select group#, member from v$logfile;
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
3
/u01/app/oracle/oradata/ORCL/redo03.log
2
/u01/app/oracle/oradata/ORCL/redo02.log
1
/u01/app/oracle/oradata/ORCL/redo01.log
SQL> desc v$logfile
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
CON_ID NUMBER
SQL> select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024,
MEMBERS,STATUS from v$log; 2
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 MEMBERS STATUS
---------- ---------- ---------- --------------- ---------- ----------------
1 1 16 200 1 INACTIVE
2 1 17 200 1 INACTIVE
3 1 18 200 1 CURRENT
SQL>
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/ORCL/redo04.log') SIZE 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/ORCL/redo05.log') SIZE 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/ORCL/redo06.log') SIZE 200M;
Database altered.
Standby
=======
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
SQL> select status,instance_name,database_role,open_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN orcl PHYSICAL STANDBY READ ONLY WITH APPLY
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORCL READ ONLY WITH APPLY
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
MRP0 WAIT_FOR_LOG 10
13 rows selected.
10. primary and standby enable dg_broker_start on both node
=======================================================
SQL> show parameter dg_broker_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean FALSE
SQL>
SQL> ALTER SYSTEM SET dg_broker_start=true scope=both;
System altered.
SQL>
standby
-------
SQL> show parameter broker
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)),
((TYPE=EMON)(BROKERS=1))
dg_broker_config_file1 string /u01/app/oracle/product/19.0.0
/db_1/dbs/dr1ORCL.dat
dg_broker_config_file2 string /u01/app/oracle/product/19.0.0
/db_1/dbs/dr2ORCL.dat
dg_broker_start boolean TRUE
use_dedicated_broker boolean FALSE
SQL>
primary
-------
SQL> show parameter broker
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)),
((TYPE=EMON)(BROKERS=1))
dg_broker_config_file1 string /u01/app/oracle/product/19.3.0
.0/dbhome_1/dbs/dr1ORCL.dat
dg_broker_config_file2 string /u01/app/oracle/product/19.3.0
.0/dbhome_1/dbs/dr2ORCL.dat
dg_broker_start boolean TRUE
use_dedicated_broker boolean FALSE
SQL>
[oracle@dg1 admin]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sun Oct 13 11:34:12 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "ORCL"
Connected as SYSDG.
DGMGRL>
Template
--------
create configuration '<CONFIGURE_NAME>' as primary database is '<PRIMARY_DB_UNIQUE_NAME>' connect identifier is <PRIMARY_DB_TNS_NAME>;
add database <STANDBY_DB_UNIQUE_NAME> as connect identifier is <STANDBY_DB_TNS_NAME> maintained as physical;
DGMGRL> create configuration 'dataguard' as primary database is 'primaryorcl' connect identifier is primarytns;
Configuration "dataguard" created with primary database "primaryorcl"
DGMGRL> add database standbyorcl as connect identifier is standbytns maintained as physical;
Database "standbyorcl" added
DGMGRL> show configuration;
Configuration - dataguard
Protection Mode: MaxPerformance
Members:
primaryorcl - Primary database
standbyorcl - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL>
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration
Configuration - dataguard
Protection Mode: MaxPerformance
Members:
primaryorcl - Primary database
standbyorcl - Physical standby database
Warning: ORA-16809: multiple warnings detected for the member
Fast-Start Failover: Disabled
Configuration Status:
WARNING (status updated 6 seconds ago)
DGMGRL>
11. Crosscheck
===========
Wait a few minutes for sync all redolog entries to apply on standby database
standby
-------
DGMGRL> show configuration
Configuration - dataguard
Protection Mode: MaxPerformance
Members:
primaryorcl - Primary database
standbyorcl - Physical standby database
Warning: ORA-16809: multiple warnings detected for the member
Fast-Start Failover: Disabled
Configuration Status:
WARNING (status updated 45 seconds ago)
DGMGRL> show configuration
Configuration - dataguard
Protection Mode: MaxPerformance
Members:
primaryorcl - Primary database
standbyorcl - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 55 seconds ago)
DGMGRL>
after success primary
---------------------
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 10 10 0
1 10 10 0
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
2 APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
3 4 (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
5 (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
6 (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
7 (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
8 WHERE
ARCH.THREAD# = APPL.THREAD#
9 10 ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 10 10 0
1 10 10 0
SQL> select status,instance_name,database_role from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
OPEN orcl PRIMARY
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
10
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 10
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CLOSING 6
ARCH CLOSING 7
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CLOSING 9
ARCH CONNECTED 0
ARCH CONNECTED 0
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
DGRD ALLOCATED 0
LNS CLOSING 10
DGRD ALLOCATED 0
LNS WRITING 11
16 rows selected.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORCL READ WRITE
SQL>
Standby
-------
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 10 10 0
SQL> select status,instance_name,database_role from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
OPEN orcl PHYSICAL STANDBY
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
10
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
MRP0 APPLYING_LOG 11
RFS IDLE 11
RFS IDLE 0
15 rows selected.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORCL READ ONLY WITH APPLY
SQL>
12. Conclusion
==========
Setting up Oracle Data Guard is a vital step in ensuring the high availability and disaster recovery of your databases. By following the steps outlined above, you can establish a robust Data Guard configuration that will safeguard your data against potential failures. Remember to regularly monitor and test your setup to ensure it meets your organization's needs.
If you have any questions or need further assistance with your Oracle Data Guard setup, feel free to reach out in the comments below!
