Dear All,
In this post i will show you how to Create Oracle 19C Dataguard Primary and Physical Standby Database configuration step by step Process.
Part 3 : Data guard configuration (Both primary and physical standby configuration) ( part 3 check next post )
Ø Primary side configuration steps
Ø Standby server side configuration steps
Ø DG configuration.
check for Part 1 and 2
Part 3 : Data guard
configuration (Both primary and physical standby configuration)
Ø Primary side configuration steps
Ø Standby server side configuration steps
Ø DG configuration.
Ø Primary side configuration steps
p Pre-requisites to enable the Dataguard is :
archive log mode should be enabled
enable flashback
enable force logging mode .
alter database force logging;
alter database flashback on;
select name,open_mode,FORCE_LOGGING,log_mode,FLASHBACK_ON from v$database;
alter system set db_recovery_file_dest_size=15G scope=both;
alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' scope=both;
Actual Data guard configuration steps : Redo
transport configuration on Primary DB :
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 21
'/u01/app/oracle/oradata/RPROD/redo21.log'
SIZE 200M;
ALTER
DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 22
'/u01/app/oracle/oradata/RPROD/redo22.log' SIZE 200M;
ALTER
DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 23
'/u01/app/oracle/oradata/RPROD/redo23.log' SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 23
'/u01/app/oracle/oradata/RPROD/redo24.log' SIZE 200M;
SELECT
GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
GROUP# THREAD#
SEQUENCE# ARC STATUS
----------
---------- ---------- --- ----------
21 1 0 YES UNASSIGNED
22 1 0 YES UNASSIGNED
23 1 0 YES UNASSIGNED
24 1 0 YES UNASSIGNED
ALTER SYSTEM
SET fal_client='rprod' SCOPE=both;
ALTER SYSTEM
SET log_archive_dest_2='service=stby async
valid_for=(online_logfiles,primary_role) db_unique_name=stby' SCOPE=both;
ALTER SYSTEM
SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM
SET log_archive_config='dg_config=(rprod,stby)' SCOPE=both;
Network configuration
on primary and standby servers
Tnsnames.ora
and listerner.ora
========================================
primary side :
Standby side :
remote_login_passwordfile string EXCLUSIVE
create pfile on primary side and copy to standby server
copy password and pfile to standby server
edit the standby parameter and rename the password file on standby side
create required directories on standby
[root@standby db_home]# mkdir -p /u01/app/oracle/admin/rprod/adump
[root@standby db_home]#
connect to rman to build standby .. i have connected from the primary server
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;
Standby side :
enable the flashback , shutdown the database and startup in mount then start the MRP .
primary :
On Standby Server
=================
alter database flashback on;
shut immediate;
startup mount;
alter database recover managed standby database disconnect;
select process, status, sequence# from v$managed_standby;
select sequence#, applied, first_time, next_time, name filename from v$archived_log order by sequence#;
primary side :
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RPROD READ WRITE PRIMARY
SQL> select process, status, sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 7
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CONNECTED 0
ARCH CLOSING 8
ARCH CLOSING 9
LNS OPENING 7
DGRD ALLOCATED 0
LNS WRITING 10
9 rows selected.
SQL> select sequence#, applied, first_time, next_time, name filename from v$archived_log order by sequence#;
SEQUENCE# APPLIED FIRST_TIM NEXT_TIME
---------- --------- --------- ---------
FILENAME
--------------------------------------------------------------------------------
5 NO 30-SEP-24 30-SEP-24
/u01/Backup/RPROD/archivelog/2024_09_30/o1_mf_1_5_mhoyfhz4_.arc
6 NO 30-SEP-24 30-SEP-24
/u01/Backup/RPROD/archivelog/2024_09_30/o1_mf_1_6_mhp0spjc_.arc
7 NO 30-SEP-24 30-SEP-24
/u01/app/oracle/fast_recovery_area/RPROD/archivelog/2024_09_30/o1_mf_1_7_mhp4r56
n_.arc
SEQUENCE# APPLIED FIRST_TIM NEXT_TIME
---------- --------- --------- ---------
FILENAME
--------------------------------------------------------------------------------
8 NO 30-SEP-24 30-SEP-24
/u01/app/oracle/fast_recovery_area/RPROD/archivelog/2024_09_30/o1_mf_1_8_mhp4s8w
1_.arc
9 NO 30-SEP-24 30-SEP-24
/u01/app/oracle/fast_recovery_area/RPROD/archivelog/2024_09_30/o1_mf_1_9_mhp4s92
1_.arc
SQL> alter system switch log;
alter system switch log
*
ERROR at line 1:
ORA-01900: LOGFILE keyword expected
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> select process, status, sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 7
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CLOSING 10
ARCH CLOSING 11
ARCH CLOSING 12
LNS WRITING 13
DGRD ALLOCATED 0
DGRD ALLOCATED 0
9 rows selected.
SQL> alter system switch logfile;
System altered.
Standby Side :
--------------------
SQL> alter database flashback on;
Database altered.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RPROD MOUNTED PHYSICAL STANDBY
SQL> shut immediate;
startup mount;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
ORACLE instance started.
Total System Global Area 1795159104 bytes
Fixed Size 8897600 bytes
Variable Size 419430400 bytes
Database Buffers 1358954496 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> SQL>
SQL>
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RPROD MOUNTED PHYSICAL STANDBY
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
RFS IDLE 0
RFS IDLE 10
8 rows selected.
SQL> alter database recover managed standby database disconnect;
Database altered.
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
RFS IDLE 0
RFS IDLE 10
MRP0 APPLYING_LOG 10
9 rows selected.
SQL> select process, status, sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 11
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CLOSING 12
ARCH CLOSING 10
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 13
MRP0 APPLYING_LOG 13
9 rows selected.
SQL> /
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 13
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CLOSING 12
ARCH CLOSING 10
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 14
MRP0 APPLYING_LOG 14
9 rows selected.
Thanks,
Srini
