Step by Step Configuring Dataguard on Different Operating Systems (Heterogeneous)

Step by Step Configuring Dataguard on Different Operating Systems (Heterogeneous)

Description:-

In this article we are going to configure Oracle Heterogeneous Standby with Primary in Linux & Standby in Windows and  see the below configuration details.Primary Server side Configurations:-

Step1:-Change Archivelog mode
SQL> archive log listSQL> shut immediate
SQL> startup mount
SQL> alter system set log_archive_dest_1=’LOCATION=/oradb/app/oracle/archive/orcl_prim’ scope=spfile;
SQL> alter database archivelog;
SQL> alter database open;

Step2:-Change force logging mode
SQL> alter database force logging;
SQL> select force_logging,log_mode from v$database;Step3:-Adding Redologfile for standby database
SQL> alter database add standby logfile group 4 ‘/oradb/app/oracle/oradata/ORCL_PRIM/redo04.log’ size 50m;
SQL> alter database add standby logfile group 5 ‘/oradb/app/oracle/oradata/ORCL_PRIM/redo05.log’ size 50m;
SQL> alter database add standby logfile group 6 ‘/oradb/app/oracle/oradata/ORCL_PRIM/redo06.log’ size 50m;

Now check the all the database files location:-

SQL> @all_db_files.sqlSQL>SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Step4:-Adding the network entry in primary and standby side(Both servers)
Listener Entry(Primary):-
*******************
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_PRIM)
(ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.105)(PORT = 1624))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1624))
)
)

Listener Entry(Standby):-
*******************
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_STBY)
(ORACLE_HOME = D:\app\oracle\product\11.2.0.4\db_1)
(SID_NAME = ORCL)
)
)

Tnsnames entry(Both):-
******************
ORCL_PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.105)(PORT = 1624))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL_PRIM)
)
)

ORCL_STBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.106)(PORT = 1624))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL_STBY)
)
)

Start the listener in both primary & standby and tnsping to primary & standby:-
$ tnsping ORCL_PRIM

TNS Ping Utility for Linux: Version 11.2.0.4.0 – Production on 24-JUN-2018 20:47:15
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.105)(PORT = 1624))) (CONNECT_DATA = (SERVICE_NAME = ORCL_PRIM)))
OK (10 msec)

$ tnsping ORCL_STBY

TNS Ping Utility for Linux: Version 11.2.0.4.0 – Production on 24-JUN-2018 20:48:18
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.106)(PORT = 1624))) (CONNECT_DATA = (SERVICE_NAME = ORCL_STBY)))
OK (10 msec)

Step5:-Changing parameters in primary database
SQL> ALTER SYSTEM SET db_unique_name=’ORCL_PRIM’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(ORCL_PRIM,ORCL_STBY)’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_1=’LOCATION=/oradb/app/oracle/archive/orcl_prim valid_for=(all_logfiles,all_roles) db_unique_name=ORCL_PRIM’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_2=’service=ORCL_STBY async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL_STBY’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET fal_server=’ORCL_STBY’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET fal_client=’ORCL_PRIM’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET db_file_name_convert=’/oradb/app/oracle/oradata/ORCL_PRIM’,’D:\app\oracle\oradata\ORCL_STBY’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_file_name_convert=’/oradb/app/oracle/oradata/ORCL_PRIM’,’D:\app\oracle\oradata\ORCL_STBY’ SCOPE=SPFILE;
SQL> SHUT IMMEDIATE
SQL> STARTUP

Verify All the required parameters are set properly with the below script:-SQL> @stby_parameters.sql

Standby Server side Configurations:-

Step1:-Password file creation
Set ORACLE_BASE,ORACLE_HOME & PATH:-
$ set ORACLE_SID=ORCL
$ set ORACLE_BASE=D:\app\oracle
$ set ORACLE_HOME=%ORACLE_BASE%\product\11.2.0.4\db_1
$ set PATH=%ORACLE_HOME%\bin;%PATH%

Copy the remote login password file (orapwORCL) from the primary database server in $ORACLE_HOME/dbs directory to the
standby database server in $ORACLE_HOME/database:-

$ pscp $ORACLE_HOME/dbs/orapwORCL administrator@192.168.1.106:D:\app\oracle\product\11.2.0.4\db_1\database

Step2:-Changing parameters in standby database:-

In the $ORACLE_HOME/database directory of the standby system, create an initialization parameter file named initORCL.ora with the below parameter.

ORCL.__oracle_base=’D:\app\oracle’#ORACLE_BASE set from environment
*.audit_file_dest=’D:\app\oracle\admin\ORCL_STBY\adump’
*.audit_trail=’db’
*.compatible=’11.2.0.4′
*.control_files=’D:\app\oracle\oradata\ORCL_STBY\control01.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_file_name_convert=’/oradb/app/oracle/oradata/ORCL_PRIM/’,’D:\app\oracle\oradata\ORCL_STBY\’
*.db_name=’ORCL’
*.db_recovery_file_dest=’D:\app\oracle\fast_recovery_area’
*.db_recovery_file_dest_size=4385144832
*.db_unique_name=’ORCL_STBY’
*.diagnostic_dest=’D:\app\oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)’
*.fal_client=’ORCL_STBY’
*.fal_server=’ORCL_PRIM’
*.log_archive_config=’dg_config=(ORCL_PRIM,ORCL_STBY)’
*.log_archive_dest_1=’LOCATION=D:\app\oracle\archive\orcl_stby valid_for=(all_logfiles,all_roles) db_unique_name=ORCL_STBY’
*.log_archive_dest_2=’service=ORCL_PRIM async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL_PRIM’
*.log_archive_max_processes=5
*.log_file_name_convert=’/oradb/app/oracle/oradata/ORCL_PRIM/’,’D:\app\oracle\oradata\ORCL_STBY\’
*.memory_target=1509949440
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.standby_file_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′

Step3:-Create directory Structure in Standby database

$ cd %ORACLE_BASE%
$ mkdir admin\ORCL_STBY
$ cd admin\ORCL_STBY
$ mkdir adump pfile dpdump
$ mkdir -p D:\app\oracle\oradata\ORCL_STBY
$ mkdir -p D:\app\oracle\fast_recovery_area\ORCL_STBY

Step4:-Setup the windows service for oracle and start the standby database using pfile

$ oradim -new -sid ORCL -SYSPWD manager -pfile D:\app\oracle\product\11.2.0.4\db_1\database\initORCL.ora

$ sqlplus ‘/ as sysdba’
SQL> create spfile from pfile=’D:\app\oracle\product\11.2.0.4\db_1\database\INITorcl.ora’;
SQL> startup nomount

Step5:-Connect to the rman(Make sure the tnsnames.ora file has the primary & standby database entry in %ORACLE_HOME%\network\admin location)

$ rman target sys/manager@ORCL_PRIM auxiliary sys/manager@ORCL_STBY
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jun 27 00:57:14 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1507437661)
connected to auxiliary database: ORCL (not mounted)
RMAN> run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database nofilenamecheck;
}
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=44 device type=DISK
allocated channel: p2
channel p2: SID=28 device type=DISK
allocated channel: p3
channel p3: SID=48 device type=DISK
allocated channel: p4
channel p4: SID=47 device type=DISK
allocated channel: s1
channel s1: SID=134 device type=DISK
Starting Duplicate Db at 27-JUN-18
contents of Memory Script:
{
backup as copy reuse
targetfile '/oradb/app/oracle/product/11.2.0.4/db_1/dbs/orapwORCL' auxiliary format
'D:\app\oracle\product\11.2.0.4\db_1\DATABASE\PWDorcl.ORA' ;
}
executing Memory Script
Starting backup at 27-JUN-18
Finished backup at 27-JUN-18
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format 'D:\APP\ORACLE\ORADATA\ORCL_STBY\CONTROL01.CTL';
}
executing Memory Script
Starting backup at 27-JUN-18
channel p1: starting datafile copy
copying standby control file
output file name=/oradb/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_ORCL.f tag=TAG20180627T063140 RECID=3 STAMP=979885900
channel p1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27-JUN-18
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
"D:\APP\ORACLE\ORADATA\ORCL_STBY\TEMP01.DBF";
switch clone tempfile all;
set newname for datafile 1 to
"D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSTEM01.DBF";
set newname for datafile 2 to
"D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSAUX01.DBF";
set newname for datafile 3 to
"D:\APP\ORACLE\ORADATA\ORCL_STBY\UNDOTBS01.DBF";
set newname for datafile 4 to
"D:\APP\ORACLE\ORADATA\ORCL_STBY\USERS01.DBF";
set newname for datafile 5 to
"D:\APP\ORACLE\ORADATA\ORCL_STBY\EXAMPLE01.DBF";
backup as copy reuse
datafile 1 auxiliary format
"D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSTEM01.DBF" datafile
2 auxiliary format
"D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSAUX01.DBF" datafile
3 auxiliary format
"D:\APP\ORACLE\ORADATA\ORCL_STBY\UNDOTBS01.DBF" datafile
4 auxiliary format
"D:\APP\ORACLE\ORADATA\ORCL_STBY\USERS01.DBF" datafile
5 auxiliary format
"D:\APP\ORACLE\ORADATA\ORCL_STBY\EXAMPLE01.DBF" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to D:\APP\ORACLE\ORADATA\ORCL_STBY\TEMP01.DBF in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 27-JUN-18
channel p1: starting datafile copy
input datafile file number=00003 name=/oradb/app/oracle/oradata/ORCL_PRIM/undotbs01.dbf
channel p2: starting datafile copy
input datafile file number=00001 name=/oradb/app/oracle/oradata/ORCL_PRIM/system01.dbf
channel p3: starting datafile copy
input datafile file number=00002 name=/oradb/app/oracle/oradata/ORCL_PRIM/sysaux01.dbf
channel p4: starting datafile copy
input datafile file number=00004 name=/oradb/app/oracle/oradata/ORCL_PRIM/users01.dbf
output file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\USERS01.DBF tag=TAG20180627T063150
channel p4: datafile copy complete, elapsed time: 00:02:22
channel p4: starting datafile copy
input datafile file number=00005 name=/oradb/app/oracle/oradata/ORCL_PRIM/example01.dbf
output file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSAUX01.DBF tag=TAG20180627T063150
channel p3: datafile copy complete, elapsed time: 00:03:03
output file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\EXAMPLE01.DBF tag=TAG20180627T063150
channel p4: datafile copy complete, elapsed time: 00:01:32
output file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSTEM01.DBF tag=TAG20180627T063150
channel p2: datafile copy complete, elapsed time: 00:04:27
output file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\UNDOTBS01.DBF tag=TAG20180627T063150
channel p1: datafile copy complete, elapsed time: 00:04:48
Finished backup at 27-JUN-18
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=3 STAMP=979866122 file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=979866122 file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=979866122 file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=979866122 file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=979866122 file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\EXAMPLE01.DBF
Finished Duplicate Db at 27-JUN-18
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: s1
RMAN> exit
Recovery Manager complete.

Step6:-Copy the control file to FRA

SQL> shut immediate
$ copy D:\app\oracle\oradata\ORCL_STBY\CONTROL01.CTL D:\app\oracle\fast_recovery_area\ORCL_STBY\CONTROL02.CTL
SQL> create pfile from spfile;

Change the control_files parameter value using any text editor in the pfile:

$ notepad D:\app\oracle\product\11.2.0.4\db_1\database\INITorcl.ora

From:-
*.control_files=’D:\app\oracle\oradata\ORCL_STBY\control01.ctl’

To:-
*.control_files=’D:\app\oracle\oradata\ORCL_STBY\control01.ctl’,’D:\app\oracle\fast_recovery_area\ORCL_STBY\CONTROL02.CTL’

Step7:-Startup the standby database and start the media recovery process

SQL> create spfile from pfile;
SQL> startup
SQL> alter database recover managed standby database using current logfile disconnect;

Now the Primary & Standby database status and roles:-

Primary:Standby:Now, check the current sequence of archive log in both primary & standby.

Primary:

SQL> archive log listStandby:

SQL> archive log listFrom the above two snaps from primary & standby has the same archive log sequence applied. Now, switch a redo log in primary and check the alert log in standby.

Primary:

SQL> alter system switch logfile;Standby:

SQL> @taln.sql 20Using the above script, we can see the alert log from sqlplus and from the above snap, the sequence 125 has been shipped from primary to standby.

 

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

2 thoughts on “Step by Step Configuring Dataguard on Different Operating Systems (Heterogeneous)”

Leave a Reply

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