Setting the parameter on primary database for physical standby database.

In the previous article, we have seen basics about the Architecture of Oracle Dataguard.

We can configure DataGuard with main three stages.

1. Preparing Primary Database.
2. Setting up net services between primary and standby database.
3. Creating Standby Database.

In this article, we will set the parameters on the primary database. We will see parameter setting of Primary database necessary for standby database configuration.

Step 1: Your Database must be in archive log mode.

[oracle@test1 ~]$ export ORACLE_SID=testdb 
[oracle@test1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 19 23:47:21 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name,open_mode ,log_mode from v$database;

NAME	  OPEN_MODE	       LOG_MODE
--------- -------------------- ------------
TESTDB	  READ WRITE	       ARCHIVELOG

SQL>

Step 2: Check if a database is enabled for force logging or not. If not enabled, enable it.

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

FOR
---
NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL>

Step 3: Set Initialization parameters.

SQL> SHOW PARAMETER DB_NAME

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_name 			     string	 testdb
SQL> SHOW PARAMETER DB_UNIQUE_NAME

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_unique_name			     string	 testdb
SQL>

For this configuration, I have set db_name and db_unique_name same, we can change it according to requirement.

Set Initialization parameters remote_login_passwordfile must be set to EXCLUSIVE.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

System altered.

SQL> SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile	     string	 EXCLUSIVE

Step 4: Set following parameters on Production to configure data guard :

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(TESTDB,STD_TESTDB)';
System altered.

SQL> alter system set log_archive_dest_1='LOCATION=/u01/arc/testdb VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)';
System altered.

SQL> alter system set log_archive_dest_2='service=std_testdb lgwr sync AFFIRM valid_FOR=(ONLINE_LOGFILES,
PRIMARY_ROLE) db_unique_name=std_testdb';
System altered.

SQL> alter system set log_archive_dest_4='LOCATION=/u01/arc/testdb/stdby/ valid_for=(STANDBY_LOGFILES,
STANDBY_ROLES) db_unique_name=testdb';
System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
System altered.

SQL> ALTER SYSTEM SET FAL_SERVER=STD_TESTDB; 
System altered. 

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; 
System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30; 
System altered.

The primary database is now configured for DataGuard creation.

Here we can see I have set log_archive_dest_4 for Standby Role. That will help us in switchover scenario.

In next article, we will see Oracle Net Configuration for Data Guard.

Stay tuned for More articles on Oracle DataGuard

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp