Step by Step Configure Oracle 12c Data Guard Far Sync Standby Database

Description:-

  • In this article we are going to see Step by Step Configure Oracle 12c Data Guard Far Sync Standby Database
  • An Oracle Data Guard far sync instance is a remote Oracle Data Guard destination that accepts redo from the primary database and then ships that redo to other members of the Oracle Data Guard configuration.
  • A far sync instance manages a control file, receives redo into standby redo logs (SRLs), and archives those SRLs to local archived redo logs, but that is where the similarity with standbys ends. A far sync instance does not have user data files, cannot be opened for access, cannot run redo apply, and can never function in the primary role or be converted to any type of standby database.
  • Far sync instances are part of the Oracle Active Data Guard Far Sync feature, which requires an Oracle Active Data Guard license.
  • Creating a far sync instance close to the primary has the benefit of minimizing impact on commit response times to an acceptable threshold (due to the smaller network latency between primary and far sync instance) while allowing for higher data protection guarantees — if the primary were to fail, and assuming the far sync instance was synchronized at the time of the failure, the far sync instance and the terminal standby would coordinate a final redo shipment from the far sync instance to the standby to ship any redo not yet available to the standby and then perform a zero-data-loss failover.
  • Summary:-
    • A light-weight/remote standby instance
    • It contains instance, standby controlfile and set of redo logs only Not datafile
    • Far sync standby database consumes minimal resources over the server (CPU, Memory, I/O etc)
    • Overcome network latency issues while shipping redo synchronously
      Guarantees zero data loss capabilities
    • Keep it close to the primary site, 40-150 miles
    • Far sync instance doesn’t have any physical structure
    • Multiple Far sync instance can be configured for active/passive purpose
    • Supports a maximum of 29 remote standby databases

Far Sync Standby Architecture:-

High Level Steps:-

  1. Adding TNS Entry and listener
  2. Creating the Standby Database
  3. create Far Sync Instance
  4. Adding Standby Logfiles in all the Databases
  5. Creating our Data Guard configuration with far sync instance

Let’s start the demo:-

Environment Details:-

Adding TNS entry (check all the nodes)

[oracle@prod1 admin]$ tnsping oradbwrp

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 20:53:25
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.24)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradbwr)))
OK (10 msec)

[oracle@prod1 admin]$ tnsping oradbwrs

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 20:53:28
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.26)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradbwrs)))
OK (10 msec)

[oracle@prod1 admin]$ tnsping oradbwrf

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 20:53:31
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.25)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradbwrf)))
OK (10 msec)

Adding  Listener (check all the nodes) Mark Red colour (DG Broker) 

[oracle@prod1 dbs]$ lsnrctl start

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 19-JUL-2018 04:18:57
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.2.0.1/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/prod1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.24)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.24)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 19-JUL-2018 04:18:58
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/prod1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.24)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "oradbwr" has 1 instance(s).
Instance "oradbwr", status UNKNOWN, has 1 handler(s) for this service...
Service "oradbwr_DGMGRL" has 1 instance(s).
Instance "oradbwr", status UNKNOWN, has 1 handler(s) for this service...
Service "oradbwrf" has 1 instance(s).
Instance "oradbwrf", status UNKNOWN, has 1 handler(s) for this service...
Service "oradbwrf_DGMGRL" has 1 instance(s).
Instance "oradbwrf", status UNKNOWN, has 1 handler(s) for this service...
Service "oradbwrs" has 1 instance(s).
Instance "oradbwrs", status UNKNOWN, has 1 handler(s) for this service...
Service "oradbwrs_DGMGRL" has 1 instance(s).
Instance "oradbwrs", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Enabling archivelog & flashback

[oracle@prod1 ~]$ export ORACLE_SID=oradbwr
[oracle@prod1 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 17 21:27:26 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> startup mount
ORACLE instance started.

Total System Global Area 935329792 bytes
Fixed Size 8627344 bytes
Variable Size 343935856 bytes
Database Buffers 578813952 bytes
Redo Buffers 3952640 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.

SQL> alter database flashback on;
Database altered.

SQL> alter database force logging;
Database altered.

SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 935329792 bytes
Fixed Size 8627344 bytes
Variable Size 343935856 bytes
Database Buffers 578813952 bytes
Redo Buffers 3952640 bytes
Database mounted.
Database opened.

SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/oradbwr/redo04.log' size 50m;
Database altered.

SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/oradbwr/redo05.log' size 50m;
Database altered.

SQL>alter database add standby logfile group 6 '/u01/app/oracle/oradata/oradbwr/redo06.log' size 50m; 
Database altered.

Changing parameters in primary database

SQL> ALTER SYSTEM SET db_unique_name='oradbwr' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_config='dg_config=(oradbwr,oradbwrs)' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=oradbwr' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_dest_2='service=stand async valid_for=(online_logfiles,primary_role) db_unique_name=oradbwrs' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_dest_state_1='ENABLE' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET remote_login_passwordfile='EXCLUSIVE' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET fal_server='oradbwrs' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET fal_client='oradbwr' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_format='oradbwr_%t_%s_%r.arc' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET db_file_name_convert='/u01/app/oracle/oradata/oradbwrs','/u01/app/oracle/oradata/oradbwr' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_file_name_convert='/u01/app/oracle/oradata/oradbwrs','/u01/app/oracle/oradata/oradbwr' SCOPE=SPFILE;
System altered.

Standby database side

Step:1 Password file creation

copy the remote login password file (orapworadbwr) from the primary database server to the $ORACLE_HOME/dbs directory on the
standby database server, renaming it to orapworadbwrs.

Step:2 Changing parameters in standby database

In the $ORACLE_HOME/dbs directory of the standby system, create an initialization parameter file named initstand.ora
Containing a single parameter: DB_NAME=oradbwrs

Step:3 Create directory Structure in Standby database

cd $ORACLE_BASE/admin/
mkdir oradbwrs
cd oradbwrs
mkdir adump pfile dpdump
mkdir -p /u01/app/oracle/oradata/oradbwrs

Step:4 start the standby database using pfile

startup nomount pfile=$ORACLE_HOME/dbs/initoradbwrs.ora

Step:5 connect to the rman

[oracle@prod3 ~]$ rman target sys/oracle@oradbwrp auxiliary sys/oracle@oradbwrs

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jul 18 23:34:41 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADBWR (DBID=1408230468)
connected to auxiliary database: ORADBWR (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
spfile
parameter_value_convert 'oradbwr','oradbwrs'
set db_name='oradbwr'
set db_unique_name='oradbwrs' 
set db_file_name_convert='/u01/app/oracle/oradata/oradbwr','/u01/app/oracle/oradata/oradbwrs'
set log_file_name_convert='/u01/app/oracle/oradata/oradbwr','/u01/app/oracle/oradata/oradbwrs'
set control_files='/u01/app/oracle/oradata/oradbwrs/standby1.ctl'
set log_archive_max_processes='5'
set fal_client='oradbwrs'
set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/oradbwrs'
set db_recovery_file_dest_size='8000m'
set fal_server='oradbwr'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(oradbwrs,oradbwr)'
set compatible='12.2.0.1.0'
set sga_target='1500m'
nofilenamecheck;
}
2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=75 device type=DISK
allocated channel: p2
channel p2: SID=76 device type=DISK
allocated channel: p3
channel p3: SID=71 device type=DISK
allocated channel: p4
channel p4: SID=63 device type=DISK
allocated channel: s1
channel s1: SID=36 device type=DISK
Starting Duplicate Db at 18-JUL-18
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/12.2.0.1/db_1/dbs/orapworadbwr' auxiliary format 
'/u01/app/oracle/product/12.2.0.1/db_1/dbs/orapworadbwrs' targetfile 
'/u01/app/oracle/product/12.2.0.1/db_1/dbs/spfileoradbwr.ora' auxiliary format 
'/u01/app/oracle/product/12.2.0.1/db_1/dbs/spfileoradbwrs.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/12.2.0.1/db_1/dbs/spfileoradbwrs.ora''";
}
executing Memory Script
Starting backup at 18-JUL-18
Finished backup at 18-JUL-18
sql statement: alter system set spfile= ''/u01/app/oracle/product/12.2.0.1/db_1/dbs/spfileoradbwrs.ora''
contents of Memory Script:
{
sql clone "alter system set audit_file_dest = 
''/u01/app/oracle/admin/oradbwrs/adump'' comment=
'''' scope=spfile";
sql clone "alter system set dispatchers = 
''(PROTOCOL=TCP) (SERVICE=oradbwrsXDB)'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 = 
''location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 = 
''service=oradbwrs async valid_for=(online_logfiles,primary_role) db_unique_name=oradbwrss'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_format = 
''oradbwrs_%t_%s_%r.arc'' comment=
'''' scope=spfile";
sql clone "alter system set db_name = 
''oradbwr'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name = 
''oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert = 
''/u01/app/oracle/oradata/oradbwr'', ''/u01/app/oracle/oradata/oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert = 
''/u01/app/oracle/oradata/oradbwr'', ''/u01/app/oracle/oradata/oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set control_files = 
''/u01/app/oracle/oradata/oradbwrs/standby1.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_max_processes = 
5 comment=
'''' scope=spfile";
sql clone "alter system set fal_client = 
''oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set db_recovery_file_dest = 
''/u01/app/oracle/fast_recovery_area/oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set db_recovery_file_dest_size = 
8000m comment=
'''' scope=spfile";
sql clone "alter system set fal_server = 
''oradbwr'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management = 
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_config = 
''dg_config=(oradbwrs,oradbwr)'' comment=
'''' scope=spfile";
sql clone "alter system set compatible = 
''12.2.0.1.0'' comment=
'''' scope=spfile";
sql clone "alter system set sga_target = 
1500m comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/oradbwrs/adump'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=oradbwrsXDB)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=stand async valid_for=(online_logfiles,primary_role) db_unique_name=oradbwrss'' comment= '''' scope=spfile
sql statement: alter system set log_archive_format = ''oradbwrs_%t_%s_%r.arc'' comment= '''' scope=spfile
sql statement: alter system set db_name = ''oradbwr'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''/u01/app/oracle/oradata/oradbwr'', ''/u01/app/oracle/oradata/oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/u01/app/oracle/oradata/oradbwr'', ''/u01/app/oracle/oradata/oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/oradbwrs/standby1.ctl'' comment= '''' scope=spfile
sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile
sql statement: alter system set fal_client = ''oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest = ''/u01/app/oracle/fast_recovery_area/oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest_size = 8000m comment= '''' scope=spfile
sql statement: alter system set fal_server = ''oradbwr'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set log_archive_config = ''dg_config=(oradbwrs,oradbwr)'' comment= '''' scope=spfile
sql statement: alter system set compatible = ''12.2.0.1.0'' comment= '''' scope=spfile
sql statement: alter system set sga_target = 1500m comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1577058304 bytes
Fixed Size 8621136 bytes
Variable Size 503317424 bytes
Database Buffers 1056964608 bytes
Redo Buffers 8155136 bytes
allocated channel: s1
channel s1: SID=34 device type=DISK
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/oradbwrs/standby1.ctl';
}
executing Memory Script
Starting backup at 18-JUL-18
channel p1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/12.2.0.1/db_1/dbs/snapcf_oradbwr.f tag=TAG20180718T233622
channel p1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 18-JUL-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 
"/u01/app/oracle/oradata/oradbwrs/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to 
"/u01/app/oracle/oradata/oradbwrs/system01.dbf";
set newname for datafile 3 to 
"/u01/app/oracle/oradata/oradbwrs/sysaux01.dbf";
set newname for datafile 4 to 
"/u01/app/oracle/oradata/oradbwrs/undotbs01.dbf";
set newname for datafile 7 to 
"/u01/app/oracle/oradata/oradbwrs/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format 
"/u01/app/oracle/oradata/oradbwrs/system01.dbf" datafile 
3 auxiliary format 
"/u01/app/oracle/oradata/oradbwrs/sysaux01.dbf" datafile 
4 auxiliary format 
"/u01/app/oracle/oradata/oradbwrs/undotbs01.dbf" datafile 
7 auxiliary format 
"/u01/app/oracle/oradata/oradbwrs/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/oradbwrs/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 18-JUL-18
channel p1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/oradbwr/system01.dbf
channel p2: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/oradbwr/sysaux01.dbf
channel p3: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/oradbwr/undotbs01.dbf
channel p4: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/oradata/oradbwr/users01.dbf
output file name=/u01/app/oracle/oradata/oradbwrs/users01.dbf tag=TAG20180718T233628
channel p4: datafile copy complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/oradbwrs/undotbs01.dbf tag=TAG20180718T233628
channel p3: datafile copy complete, elapsed time: 00:00:07
output file name=/u01/app/oracle/oradata/oradbwrs/sysaux01.dbf tag=TAG20180718T233628
channel p2: datafile copy complete, elapsed time: 00:01:15
output file name=/u01/app/oracle/oradata/oradbwrs/system01.dbf tag=TAG20180718T233628
channel p1: datafile copy complete, elapsed time: 00:01:25
Finished backup at 18-JUL-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=1 STAMP=981848275 file name=/u01/app/oracle/oradata/oradbwrs/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=981848275 file name=/u01/app/oracle/oradata/oradbwrs/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=981848275 file name=/u01/app/oracle/oradata/oradbwrs/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=981848275 file name=/u01/app/oracle/oradata/oradbwrs/users01.dbf
Finished Duplicate Db at 18-JUL-18
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: s1

Step6 :- Post check status for Standby database,

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

NAME               DATABASE_ROLE                           OPEN_MODE
——— —————- ——————–
ORADBWR             PHYSICAL STANDBY                        MOUNTED

Step 7:- Enable the recovery:

SQL> alter database recover managed standby database disconnect from session;

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

SQL> 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                 24                   24                 0

Deploying Far Sync instance

Step1:-We need to create a controlfile in primary database that will be used to create far sync instance

SQL> ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS '/home/oracle/stbyfarsync.ctl';
Database altered.

Step2:-Copy the far sync controlfile & passwordfile

Copy the far sync controlfile & passwordfile
[oracle@prod1 ~]$ scp stbyfarsync.ctl oracle@192.168.1.25:/u01/app/oracle/oradata/oradbwrf
oracle@192.168.1.25's password: 
stbyfarsync.ctl 100% 10MB 10.1MB/s 00:00 
[oracle@prod1 ~]$ cd $ORACLE_HOME/dbs
[oracle@prod1 dbs]$ scp orapworadbwr oracle@192.168.1.25:$ORACLE_HOME/dbs
oracle@192.168.1.25's password: 
orapworadbwr

step3:-Farsync instance Pfile

audit_file_dest='/u01/app/oracle/oradbwrf/adump'
audit_trail='db'
compatible='12.2.0.1.0'
control_files='/u01/app/oracle/oradata/oradbwrf/stbyfarsync.ctl'
db_block_size=8192
db_domain=''
db_name='oradbwr'
db_unique_name='oradbwrf'
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/oradbwrf'
db_recovery_file_dest_size=4800m
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=oradbwrfXDB)'
log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=oradbwrf'
log_archive_dest_2='service=oradbwrs ASYNC valid_for=(standby_logfiles,standby_role) db_unique_name=oradbwrs'
log_archive_config='DG_CONFIG=(oradbwr,oradbwrf,oradbwrs)'
open_cursors=300
pga_aggregate_target=300m
processes=300
remote_login_passwordfile='EXCLUSIVE'
sga_target=1300m
undo_tablespace='UNDOTBS1'
fal_server='oradbwr'
db_file_name_convert='/u01/app/oracle/oradata/oradbwr/','/u01/app/oracle/oradata/oradbwrf/'
log_file_name_convert='/u01/app/oracle/oradata/oradbwr/','/u01/app/oracle/oradata/oradbwrf/'

Step4:- startup the Far sync instance and check

[oracle@prod2 ~]$ sqlplus ‘/as sysdba’;

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 19 03:54:43 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.
Total System Global Area 1375731712 bytes
Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes

SQL> alter database mount;
Database altered.

SQL> select database_role from v$database;

DATABASE_ROLE
—————-
FAR SYNC

SQL> select DB_UNIQUE_NAME,DEST_ROLE from V$DATAGUARD_CONFIG;

DB_UNIQUE_NAME                    DEST_ROLE
------------------------------ -----------------
oradbwrf                        FAR SYNC INSTANCE
oradbwrs                        PHYSICAL STANDBY
oradbwr                         PRIMARY DATABASE

Data Guard configuration with far sync instance

Parameters change 

Primary:-
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(oradbwr,oradbwrf,oradbwrs)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradbwr' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=oradbwrf SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradbwrf' scope=both;

Far sync Standby:-
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(oradbwr,oradbwrf,oradbwrs)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ordbwrf' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=ordbwrs ASYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=ordbwrs' scope=both;

Standby:-
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(oradbwr,oradbwrf,oradbwrs)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradbwrs' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=oradbwr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradbwr' scope=both;
[oracle@prod1 admin]$ dgmgrl
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Thu Jul 19 06:26:35 2018

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@oradbwrp
Connected to "oradbwr"
Connected as SYSDBA.
DGMGRL> CREATE CONFIGURATION 'chennai' AS PRIMARY DATABASE IS 'oradbwr' CONNECT IDENTIFIER IS 'oradbwr';
Configuration "chennai" created with primary database "oradbwr"
DGMGRL> ADD DATABASE 'oradbwrs' AS CONNECT IDENTIFIER IS 'oradbwrs' maintained as physical;
Database "oradbwrs" added
DGMGRL> ADD FAR_SYNC 'oradbwrf' AS CONNECT IDENTIFIER IS 'oradbwrf';
far sync instance "oradbwrf" added
DGMGRL> show configuration;

Configuration - chennai

Protection Mode: MaxAvailability
Members:
oradbwr - Primary database
oradbwrs - Physical standby database 
oradbwrf - Far sync instance 
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED

Check the Standby database sync status with primary:

SQL> 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                 37                     37                0

Successfully configured Far sync standby database.

Reference:-

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sbydb/creating-oracle-data-guard-far-sync-instance.html#GUID-8AD7FBA2-42B0-46CF-852B-1AF0CB4A36E8

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

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