Step by Step to configure Oracle 19c Data Guard Physical Standby

Environment Details

Parameters needs to configure both side for Data guard,

Primary side:
db_name:- Same name for all primary and standby database
db_unique_name:-a unique db name to uniquely identify primary and standby db because db_name is same
Log_archive_dest_1:-local archive destination
Log_archive_dest_2:-destination for standby database
Standby_file_management:-automatically  create file at standby db site

Standby Side:
db_unique_name:-it has to be different from DB_NAME parameter
db_file_name_convert:-specify the path name and datafile location of primary database datafile followed by standby location
log_file_name_convert:-specify the path name and redo logfile location of primary database redo logfile followed by standby location
log_archive_dest_1:-location of archives coming from primary database
fal_server:-to fetch archived log from primary site when log gap is detected at standby site.The parameter value is tns service name
fal_client:- to copies missing archive to standby database

Step 1:-Check FORCE LOGGING is enabled

Step 2:- Make sure primary is in archivelog mode

Step 3:- PFILE creation and copy to standby database

Step 4:-Edit the parameters and directories in created pfile

Step 5:- Parameters for primary database

Step 6:- Copy password file from primary to standby server

Step 7:- Make the respective directories for standby database

Step 8:- Keep the database in NOMOUNT stage to create standby database

Step 9:- Create spfile for standby database

Step 10:- Listener and TNS Configuration

Step 11:-Start the listener in Standby database and check the status

Step 12:-In standby side,check RMAN connection.Connect primary database as TARGET and standby database as AUXILIARY(creating new instance)

Step 13:-Active Duplication for creating standby database

Step 14:- Post check status for Standby database

Step 15:- Enable the recovery(MRP Process)

Step 16:- Check the Standby database sync status with primary

PRIMARY SIDE CONFIGURATION:-

[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 13 18:58:05 2019
Version 19.2.0.0.0

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

Connected to an idle instance.

SQL> startup 
ORACLE instance started.

Total System Global Area 2415917872 bytes
Fixed Size 8899376 bytes
Variable Size 654311424 bytes
Database Buffers 1744830464 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.

SQL> select force_logging from v$database;

FORCE_LOGGING
------------------
NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence 3
SQL> shut immediate 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2415917872 bytes
Fixed Size 8899376 bytes
Variable Size 654311424 bytes
Database Buffers 1744830464 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> create pfile from spfile;

File created.


Check the Datafiles,Log files location in Primary side:

SQL> show parameter db_name;

NAME                     TYPE            VALUE
---------------------- ----------- ------------------
db_name                  string          dbwr

SQL> show parameter db_unique_name;

NAME TYPE VALUE
---------------------- ----------- --------------------
db_unique_name            string         dbwr

SQL> select file_name from dba_data_files;

FILE_NAME
-----------------------------------------------------------------------
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/datafile/o1_mf_system_g78vt7wo_.dbf

/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/datafile/o1_mf_sysaux_g78vx8f6_.dbf

/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/datafile/o1_mf_users_g78vyq0c_.dbf

/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/datafile/o1_mf_undotbs1_g78vyor1_.dbf

Dynamically change parameter and Configure it in Primary side:

SQL> alter system set log_archive_config='dg_config=(dbwr,standby)';

System altered.

SQL> alter system set log_archive_dest_2='service=standby noaffirm async valid_for=(online_logfiles,primary_role) db_unique_name=standby';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

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=30;

System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET FAL_SERVER=STANDBY;

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

Create standby controlfile:

SQL> alter database add standby logfile ('/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/redo1.log') size 50m;

Database altered.

SQL> alter database add standby logfile ('/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/redo2.log') size 50m;

Database altered.

SQL> alter database add standby logfile ('/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/redo3.log') size 50m;

Database altered.

SQL> alter database add standby logfile ('/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/redo4.log') size 50m;

Database altered.

PRIMARY SIDE:

[oracle@ram admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

DBWR =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.doyen.com)(PORT = 1521))
)
)
[oracle@ram admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DBWR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.doyen.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dbwr)
(UR = A)
)
)

STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.doyen.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STANDBY)
(UR = A)
)
)
[oracle@ram admin]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-MAR-2019 00:43:46

Copyright (c) 1991, 2018, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias DBWR
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 15-MAR-2019 22:54:24
Uptime 0 days 1 hr. 49 min. 21 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ram/dbwr/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ram.doyen.com)(PORT=1521)))
Services Summary...
Service "dbwr" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Service "dbwrXDB" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@ram admin]$

STANDBY SIDE:

[oracle@standby ~]$ cd $ORACLE_HOME
[oracle@standby dbhome_1]$ cd network/admin/
[oracle@standby admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_STANDBY =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dbwr)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = dbwr)
)
)

STANDBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.doyen.com)(PORT = 1521))
)
)
[oracle@standby admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DBWR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.doyen.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dbwr)
(UR = A)
)
)

STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.doyen.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STANDBY)
(UR = A)
)
)

[oracle@standby admin]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-MAR-2019 00:57:37

Copyright (c) 1991, 2018, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias STANDBY
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 16-MAR-2019 00:00:08
Uptime 0 days 0 hr. 57 min. 29 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/standby/standby/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby.doyen.com)(PORT=1521)))
Services Summary...
Service "dbwr" has 1 instance(s).
Instance "dbwr", status UNKNOWN, has 1 handler(s) for this service...
Service "standby" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@standby admin]$

In standby side,check RMAN connection.Connect primary database as TARGET and standby database as AUXILIARY(creating new instance)

Active Duplication for creating standby database:

[oracle@standby ~]$ export ORACLE_SID=dbwr
[oracle@standby ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 16 00:34:57 2019
Version 19.2.0.0.0

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2415917872 bytes
Fixed Size 8899376 bytes
Variable Size 654311424 bytes
Database Buffers 1744830464 bytes
Redo Buffers 7876608 bytes
SQL> !
[oracle@standby ~]$ rman target sys/oracle@dbwr auxiliary sys/oracle@standby

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 16 00:35:46 2019
Version 19.2.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: DBWR (DBID=1335353556)
connected to auxiliary database: DBWR (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 16-MAR-19
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=39 device type=DISK

contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwdbwr' ;
}
executing Memory Script

Starting backup at 16-MAR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=121 device type=DISK
Finished backup at 16-MAR-19

contents of Memory Script:
{
restore clone from service 'dbwr' standby controlfile;
}
executing Memory Script

Starting restore at 16-MAR-19
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 dbwr
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/admin/standby/control1.ctl
output file name=/u01/app/oracle/admin/standby/control2.ctl
Finished restore at 16-MAR-19

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/admin/standby/o1_mf_temp_g78w14cc_.tmp";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/admin/standby/o1_mf_system_g78vt7wo_.dbf";
set newname for datafile 3 to
"/u01/app/oracle/admin/standby/o1_mf_sysaux_g78vx8f6_.dbf";
set newname for datafile 4 to
"/u01/app/oracle/admin/standby/o1_mf_undotbs1_g78vyor1_.dbf";
set newname for datafile 7 to
"/u01/app/oracle/admin/standby/o1_mf_users_g78vyq0c_.dbf";
restore
from non sparse from service
'dbwr' clone database
;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/admin/standby/o1_mf_temp_g78w14cc_.tmp in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 16-MAR-19
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 dbwr
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/admin/standby/o1_mf_system_g78vt7wo_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:59
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service dbwr
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/admin/standby/o1_mf_sysaux_g78vx8f6_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:09
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service dbwr
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/admin/standby/o1_mf_undotbs1_g78vyor1_.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 dbwr
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/admin/standby/o1_mf_users_g78vyq0c_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 16-MAR-19

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1003020439 file name=/u01/app/oracle/admin/standby/o1_mf_system_g78vt7wo_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1003020439 file name=/u01/app/oracle/admin/standby/o1_mf_sysaux_g78vx8f6_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1003020439 file name=/u01/app/oracle/admin/standby/o1_mf_undotbs1_g78vyor1_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1003020439 file name=/u01/app/oracle/admin/standby/o1_mf_users_g78vyq0c_.dbf
............
............
............
Finished Duplicate Db at 16-MAR-19

RMAN> exit


Enable the recovery

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

Post check status for Standby database

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

NAME               DATABASE_ROLE                 OPEN_MODE
---------          ----------------          ----------------
DBWR               PHYSICAL STANDBY              MOUNTED

Check the Standby database sync status with primary:

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE#         APPLIED
----------        ---------
    9               YES
Catch Me On:- Hariprasath Rajaram

LinkedIn:   https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:   https://www.facebook.com/HariPrasathdba
FB Group:   https://www.facebook.com/groups/894402327369506/
FB Page:    https://www.facebook.com/dbahariprasath/?
Twitter:    https://twitter.com/hariprasathdba

Leave a Reply

Your email address will not be published. Required fields are marked *