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 list
SQL> 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.sql
SQL>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 list
Standby:
SQL> archive log list
From 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 20
Using 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
