Creating FAR Sync Instance or FAR Sync Standby in Oracle

Configuration Details:

Operating System: Windows 10 Pro 64 Bit

Database: Oracle DB 19.0.0.0

Primary:

DB_NAME=PR

DB_UNIQUE_NAME=PR

log_archive_dest_3=service=MIN LGWR SYNC

log_archive_config=dg_config=(PR,DR,MIN)

SRLs configured ? : NO (SRLs are not required on Primary database).

Standby:

DB_NAME=PR

DB_UNIQUE_NAME=DR

log_archive_config=dg_config=(PR,DR,MIN)

SRLs configured ? : YES

FAR Sync:

DB_NAME=PR

DB_UNIQUE_NAME=MIN

log_archive_config=dg_config=(PR,DR,MIN)

log_archive_dest_2=service=DR LGWR ASYNC

SRLs configured ? : YES

Note: Here, I assume that we have already configured Primary and actual Standby databases, only the FAR Sync Instance needs to be created.

Step 1: Capture below details from all the three environments i.e. from PR, DR, and MIN.

On Primary:

SQL> set lines 300 pages 3000

SQL> col DB_UNIQUE_NAME for a16

SQL> col OPEN_MODE for a14

SQL> col name for a8

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

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL

---- -------------- ----------- ------------- -------------------- -------

PR PR READ WRITE PRIMARY MAXIMUM PERFORMANCE CURRENT

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

SQL> col member for a60

SQL> select a.GROUP#, a.BYTES/1024/1024, b.MEMBER,a.status,a.sequence# from v$log a,v$logfile b where a.group#=b.group#;

GROUP# BYTES/1024/1024 MEMBER STATUS SEQUENCE#

------ ----------------- ------------------------------------------ ------- ---------

3 200 D:\RUPESH\SETUPS\PRIMARY\DB\PR\REDO03.LOG CURRENT 30

2 200 D:\RUPESH\SETUPS\PRIMARY\DB\PR\REDO02.LOG ACTIVE 29

1 200 D:\RUPESH\SETUPS\PRIMARY\DB\PR\REDO01.LOG ACTIVE 28

SQL> col member for a56

SQL> set lines 300 pages 3000

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

------ ------- ---------- ---------- ---------- ------- --- -------- ------------- --------- ------------ ---------

1 1 28 209715200 512 1 YES INACTIVE 2300986 18-APR-23 2300999 18-APR-23

2 1 29 209715200 512 1 YES INACTIVE 2300999 18-APR-23 2301009 18-APR-23

3 1 30 209715200 512 1 NO CURRENT 2301009 18-APR-23 9.2954E+18

Step 2: Set the protection mode to maximum availability mode. Execute the below command in Primary database.

SQL> ALTER DATABASE SET STANDBY TO MAXIMIZE AVAILABILITY;

Database altered.

SQL> set lines 300 pages 3000

SQL> col DB_UNIQUE_NAME for a16

SQL> col OPEN_MODE for a14

SQL> col name for a8

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

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL

----- -------------- ---------- ------------- -------------------- -------

PR PR READ WRITE PRIMARY MAXIMUM AVAILABILITY CURRENT

SQL> alter system set log_archive_config='dg_config=(PR,DR,MIN)';

SQL> alter system set log_archive_dest_3='service=MIN LGWR SYNC valid_for=(all_logfiles,all_roles) db_unique_name=MIN';

SQL> alter system set log_archive_dest_state_3=enable;

SQL> show parameter log_archive_dest_2

NAME TYPE VALUE

------------------- ------ ---------------------------------------------------------------------------

log_archive_dest_2 string service=MIN LGWR SYNC valid_for=(all_logfiles,all_roles) db_unique_name=MIN

SQL> show parameter log_archive_dest_state_2

NAME TYPE VALUE

------------------------ ------ ------

log_archive_dest_state_2 string enable

Step 3: Create FAR Sync Instance control file. Execute the below command in Primary database the same way you create it for standby database. You have to mention FAR SYNC keyword in the command.

SQL> ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS 'D:\RUPESH\Setups\WINDOWS.X64_193000_db_home\database\far_sync.ctl';

Database altered.

Step 4: Create service for FAR Sync Instance on Windows Server and start the instance in nomount stage.

oradim -NEW -SID MIN -syspwd sys123 -STARTMODE auto -PFILE D:\RUPESH\Setups\WINDOWS.X64_193000_db_home\database\INITMIN.ORA

Pfile for FAR Sync Instance:

*._readable_standby_sync_timeout=60

*.audit_file_dest='D:\RUPESH\Setups\MIN\adump'

*.audit_trail='db'

*.compatible='19.0.0'

*.control_files='D:\RUPESH\Setups\Minimal\DB\MIN\control01.ctl','D:\RUPESH\Setups\Minimal\DB\MIN\control02.ctl'

*.db_block_size=8192

*.db_keep_cache_size=218103808

*.db_name='PR'

*.db_unique_name='MIN'

*.diagnostic_dest='D:\RUPESH\app\rghubade'

*.log_archive_dest_1='LOCATION=D:\RUPESH\Setups\Minimal\ARCH'

*.log_archive_format='%t_%s_%r.arc'

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=300

*.pga_aggregate_limit=3221225472

*.pga_aggregate_target=1073741824

*.processes=1000

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=2048m

*.undo_tablespace='UNDOTBS1'

*.standby_file_management='AUTO'

*.log_archive_config='dg_config=(PR,DR,MIN)'

SQL> startup nomount pfile='D:\RUPESH\Setups\WINDOWS.X64_193000_db_home\database\INITMIN.ORA';

Step 5: Now restore the FAR Sync control file from the backup and mount the instance. Execute below commands in FAR Sync Instance

C:\windows\system32>set ORACLE_SID=MIN

C:\windows\system32>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Apr 18 13:02:14 2023

Version 19.16.0.0.0

connected to target database: PR (not mounted)

RMAN>

RMAN> restore farsync controlfile from 'D:\RUPESH\Setups\WINDOWS.X64_193000_db_home\database\far_sync.ctl';

Starting restore at 18-APR-23

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=388 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=D:\RUPESH\SETUPS\MINIMAL\DB\MIN\CONTROL01.CTL

output file name=D:\RUPESH\SETUPS\MINIMAL\DB\MIN\CONTROL02.CTL

Finished restore at 18-APR-23

RMAN>

RMAN> alter database mount;

using target database control file instead of recovery catalog

Statement processed

SQL> set lines 300 pages 3000

SQL> col DB_UNIQUE_NAME for a16

SQL> col OPEN_MODE for a14

SQL> col name for a8

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

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL

---- -------------- --------- ------------- -------------------- -------

PR MIN MOUNTED FAR SYNC MAXIMUM AVAILABILITY FARSYNC

Step 6: Now add standby redo log files for FAR Sync instance. Execute below commands in FAR Sync instance.

SQL> alter database add standby logfile group 4 'D:\RUPESH\SETUPS\MINIMAL\SRLS\MINIMUM01.LOG' size 200m;

Database altered.

SQL> alter database add standby logfile group 5 'D:\RUPESH\SETUPS\MINIMAL\SRLS\MINIMUM02.LOG' size 200m;

Database altered.

SQL> alter database add standby logfile group 6 'D:\RUPESH\SETUPS\MINIMAL\SRLS\MINIMUM03.LOG' size 200m;

Database altered.

SQL> alter database add standby logfile group 7 'D:\RUPESH\SETUPS\MINIMAL\SRLS\MINIMUM04.LOG' size 200m;

Database altered.

SQL> select a.GROUP#,a.status,a.sequence# from v$standby_log a;

GROUP# STATUS SEQUENCE#

---------- ---------- ----------

4 ACTIVE 30

5 UNASSIGNED 0

6 UNASSIGNED 0

7 UNASSIGNED 0

SQL> set lines 300 pages 3000

SQL> col member for a56

SQL> select GROUP#,STATUS,TYPE,MEMBER from v$logfile;

GROUP# STATUS TYPE MEMBER

------ ------- ------- -------------------------------------------

4 STANDBY D:\RUPESH\SETUPS\MINIMAL\SRLS\MINIMUM01.LOG

5 STANDBY D:\RUPESH\SETUPS\MINIMAL\SRLS\MINIMUM02.LOG

6 STANDBY D:\RUPESH\SETUPS\MINIMAL\SRLS\MINIMUM03.LOG

7 STANDBY D:\RUPESH\SETUPS\MINIMAL\SRLS\MINIMUM04.LOG

SQL> select * from v$standby_log;

GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME

------ ---------- ------- ---------- ---------- ---------- -------- --- ---------- ------------- --------- ------------ --------- ------------ ---------

4 2829010735 1 30 209715200 512 15659008 YES ACTIVE 2301009 18-APR-23 2302150 18-APR-23

5 UNASSIGNED 1 0 209715200 512 0 NO UNASSIGNED 0

6 UNASSIGNED 1 0 209715200 512 0 NO UNASSIGNED 0

7 UNASSIGNED 0 0 209715200 512 0 NO UNASSIGNED 0

SQL> alter system set log_archive_dest_2='service=DR LGWR ASYNC valid_for=(all_logfiles,all_roles) db_unique_name=DR';

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

On Primary:

You can switch 2-3 logs in Primary and verify whether logs are getting shipped to FAR Sync and then DR. Refer below commands for the same.

alter system switch logfile;

select dest_id,error from v$archive_dest;

select max(sequence#) from v$archived_log;

col member for a60

select a.GROUP#, A.BYTES/1024/1024, b.MEMBER,a.status,a.sequence# from v$log a,v$logfile b where a.group#=b.group#;

On Standby:

select max(sequence#) from v$archived_log where applied='YES';

select process,status,sequence# from v$managed_standby;

select a.GROUP#,a.status,a.sequence# from v$standby_log a;

select * from v$standby_log;

On FAR Sync:

select process,status,sequence# from v$managed_standby;

select a.GROUP#,a.status,a.sequence# from v$standby_log a;

col member for a60

select * from v$log;

select * from v$logfile;

select * from v$standby_log;

Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get next blog updates !

Creating FAR Sync Instance or FAR Sync Standby in Oracle

Post a Comment

Step-by-Step Installation of Oracle 21c RAC on Linux 8

Oracle Database 23ai - Latest Long Term Release

Oracle DBA ++

Followers

Search This Blog

About Me

Contact form

Creating FAR Sync Instance or FAR Sync Standby in Oracle

You may like these posts

Post a Comment

Contact form