Oracle Database 12cR2-Step by Step Configuration Oracle Dataguard Using Rman 
Description:-
In this is the article we are going to see step-by-step to create a physical standby database using RMAN in Oracle Database 12c Release 2.
Environment Details
Parameters needs to configure both side for Dataguard,
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
PRIMARY SIDE CONFIGURATION:-
Step 1:-Check FORCE LOGGING is enabled,
SQL> select force_logging from v$database
If no means,enable FORCE LOGGING mode,
SQL> ALTER DATABASE FORCE LOGGING
Step 2:- Make sure primary is in archivelog mode
ARCHIVELOG mode, that automatic archiving is enabled.
SQL> archive log list
Step 3:- PFILE creation and copy to standby database:
SQL> create pfile=’/home/oracle/initorcl.ora’ from spfile;
scp /home/oracle/initorcl.ora oracle@clone:/home/oracle
Step 4:-Edit the parameters and directories in created pfile,
#Change the Audit dump location#
*.audit_file_dest=’/oradb/app/oracle/admin/orclstby/adump’
#Change the controlfile location#
*.control_files=’/oradb/app/oracle/oradata/orclstby/control01.ctl’,’/oradb/app/oracle/oradata/orclstby/control02.ctl’
#Change the DISPATCHER service name#
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclstbyXDB)’
#Change the local listener name#
*.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=clone.localdomain.com)(PORT=1521)))’
#Change the LOCAL DEST location#
*.log_archive_dest_1=’LOCATION=/oradb/app/oracle/oradata/orclstby/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
#Add DB_UNIQUE_NAME for standby database#
DB_UNIQUE_NAME=ORCLSTBY’
#Change the service name for standby#
*.service_names=’orclstby.localdomain.com’
#Add FAL entries#
fal_server=ORCL;
fal_client=ORCLSTBY
#Add the below to the created pfile from ORCL database,
*.db_file_name_convert=’/oradb/app/oracle/oradata/orcl/’,’/oradb/app/oracle/oradata/orcl/’
*.log_file_name_convert=’/oradb/app/oracle/oradata/orcl/’,’/oradb/app/oracle/oradata/orcl/’
Step 5:- Parameters for primary database,
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=ORCLSTBY
NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)’ DB_UNIQUE_NAME=’ORCLSTBY’ scope=spfile;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
ALTER SYSTEM SET FAL_SERVER=ORCLSTBY;
ALTER SYSTEM SET FAL_CLIENT=ORCL;
Step 6:- Copy password file from primary to standby server,
scp orapworcl oracle@clone:/oradb/app/oracle/product/12.2.0.1/db_1
STANDBY SIDE CONFIGURATION:-
Step 7:- Make the respective directories for standby database,
mkdir -p /oradb/app/oracle/oradata/orclstby
mkdir -p /oradb/app/oracle/oradata/orclstby/arch
mkdir -p /oradb/app/oracle/admin/orclstby/adump
Step 8:- Keep the database in NOMOUNT stage to create standby database,
export ORACLE_HOME=/oradb/app/oracle/product/12.2.0.1/db_1
export ORACLE_BASE=/oradb/app/oracle
export ORACLE_SID=orcl
sqlplus / as sysdba
SQL> startup nomount pfile=’/home/oracle/initorcl.ora’;
Step 9:- Create spfile for standby database,
SQL> create spfile from pfile=’/home/oracle/initorcl.ora’;
Step 10:- Listener and TNS Configuration :-
Primary listener configuration:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /oradb/app/oracle/product/12.2.0.1/db_1)
)
)
LISTENER_ORCL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =orcl.localdomain.com )(PORT = 1521))
)
)
Primary connection string configuration:
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl.localdomain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.localdomain.com)
)
)
Standby listener configuration:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /oradb/app/oracle/product/12.2.0.1/db_1)
)
)
LISTENER_ORCL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =clone.localdomain.com )(PORT = 1521))
)
)
Standby Connection string configuration:
ORCLSTBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = clone.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl)
(UR = A)
)
)
Step 11:-Start the listener in Standby database and check the status
[oracle@clone:admin orclstby] lsnrctl status
[oracle@clone:~ orcl] lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 – Production on 06-JUN-2018 10:11:29
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 – Production
Start Date 06-JUN-2018 02:51:26
Uptime 0 days 7 hr. 20 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oradb/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Listener Log File /oradb/app/oracle/diag/tnslsnr/clone/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=clone.localdomain.com)(PORT=1521)))
Services Summary…
Service “orcl” has 1 instance(s).
Instance “orcl”, status UNKNOWN, has 1 handler(s) for this service…
Service “orclstby.localdomain.com” has 1 instance(s).
Instance “orcl”, status READY, has 1 handler(s) for this service…
The command completed successfully
Step 12:-In standby side,check RMAN connection.Connect primary database as TARGET and standby database as AUXILIARY(creating new instance)
[oracle@clone:dbs orclstby] rman target sys/oracle@orcl auxiliary sys/oracle@orclstby
Recovery Manager: Release 12.2.0.1.0 – Production on Wed Jun 6 02:52:20 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1504791090)
connected to auxiliary database: ORCL (not mounted)
Step 13:-Active Duplication for creating standby database:
RMAN> duplicate target database for standby from active database nofilenamecheck;
Starting Duplicate Db at 06-JUN-18 04:17:38
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/oradb/app/oracle/product/12.2.0.1/db_1/dbs/orapworcl' auxiliary format
'/oradb/app/oracle/product/12.2.0.1/db_1/dbs/orapworcl' ;
}
executing Memory Script
Starting backup at 06-JUN-18 04:17:39
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=54 device type=DISK
Finished backup at 06-JUN-18 04:17:40
contents of Memory Script:
{
restore clone from service 'orcl' standby controlfile;
}
executing Memory Script
Starting restore at 06-JUN-18 04:17:41
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 orcl
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradb/app/oracle/oradata/orcl/control01.ctl
output file name=/oradb/app/oracle/oradata/orcl/control02.ctl
Finished restore at 06-JUN-18 04:17:42
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
"/oradb/app/oracle/oradata/orcl/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/oradb/app/oracle/oradata/orcl/system01.dbf";
set newname for datafile 3 to
"/oradb/app/oracle/oradata/orcl/sysaux01.dbf";
set newname for datafile 4 to
"/oradb/app/oracle/oradata/orcl/undotbs01.dbf";
set newname for datafile 7 to
"/oradb/app/oracle/oradata/orcl/users01.dbf";
restore
from nonsparse from service
'orcl' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oradb/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 06-JUN-18 04:17:47
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 orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /oradb/app/oracle/oradata/orcl/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:52
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oradb/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /oradb/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 orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /oradb/app/oracle/oradata/orcl/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 06-JUN-18 04:21:01
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=978063665 file name=/oradb/app/oracle/oradata/orcl/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=978063665 file name=/oradb/app/oracle/oradata/orcl/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=978063665 file name=/oradb/app/oracle/oradata/orcl/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=978063665 file name=/oradb/app/oracle/oradata/orcl/users01.dbf
Finished Duplicate Db at 06-JUN-18 04:22:41
Step 14:- Post check status for Standby database,
SQL> select name,database_role,open_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE
——— —————- ——————–
ORCL PHYSICAL STANDBY MOUNTED
Step 15:- Enable the recovery:
SQL> alter database recover managed standby database disconnect from session;
Step 16:- Check the Standby database sync status with primary:
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 25 25 0
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
