dgmgrl

Step by Step Oracle Active Data Guard Far Sync RAC

Posted on by debasis maity

     Step by Step Oracle Active Data Guard Far Sync RAC

Active Data Guard Far Sync, a new capability with Oracle Database 12c, eliminates compromise by extending zero data loss protection to a replica database located at any distance from the primary database. Active Data Guard Far Sync accomplishes this with minimal expense or complexity compared to other extended distance data protection and availability solutions.

We need to ensure zero data loss for various mission critical databases with minimum impact on production.Oracle dataguard ensures zero data loss by ‘SYNC’ parameter.

How ‘SYNC’ works for dataguard:-

The ‘SYNC’ ensures your committed data will be written to disk in standby site as soon as it commits.In primary,the redo generated during commit will be first written in redo buffer and it will transmit to standby redo log as well to ensure committed data has been protected by standby.The primary will not commit the data until it is written in standby redo of DR site.

Performance impact of ‘SYNC’

As the distance between primary and standby increases, however, the total round-trip time required to acknowledge the remote log file write can reach a point where it has too great of an impact on database performance to make it practical to support zero data loss protection.

Oracle Active Data Guard Far Sync Feature

 

  • Is a very slim/light weight ODG special instance with no physical data files of its own acts as a middle layer between a primary database and all its standby databases configured far away.

Use instance caging 3 in order to achieve the smallest possible SGA. Reducing the CPU_COUNT during testing had no effect on the performance of the Far sync instance. » MAA tests determined that a 300MB SGA 300 with CPU_COUNT=1 on Linux was sufficient for Far Sync.(CPU_COUNT=1 SGA_TARGET=300M)

  • Owns only server parameter file (spfile), a standby control file and set of Standby redo logs (SRLs).The Far Sync ODG should be in mount mode.
  • The purpose of Far Sync instance is to receive redo from the primary database synchronously and transport them to respective remote destinations, a maximum of 29 destinations, asynchronously in real time, provides zero data loss fail-over capabilities.
  • Supports Maximum availability and maximum performance protection modes.
  • Advised to deploy/configure closer to the primary database location, roughly 30-150 miles, to avoid the network latency while shipping redo.
  • Best recommended option when standby databases are placed thousands of kilometer away from a primary site.
  • The solo purpose is to offloads/minimizes a primary database performance overheads.
  • Part of an active data guard option that requires separate license.
  • Its role can’t be converted either to primary or any other type of standby database.
  • Consumes minimal server resources, CPU, disk, memory etc.

 

 

Primary FarSync Standby
IP 10.84.1.1, 10.84.1.2 10.84.1.3, 10.84.1.4 10.84.1.5, 10.84.1.6
Database Name EBILPD1 ORCLFS EBILDR1
Instance Type RAC RAC RAC

 

  1. Please take pfile from spfile in primay database.The pfile will be as following.

EBILPD12.__data_transfer_cache_size=0
EBILPD11.__data_transfer_cache_size=0
EBILPD12.__db_cache_size=1442840576
EBILPD11.__db_cache_size=2013265920
EBILPD12.__java_pool_size=234881024
EBILPD11.__java_pool_size=234881024
EBILPD12.__large_pool_size=5469372416
EBILPD11.__large_pool_size=5469372416
EBILPD11.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
EBILPD12.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
EBILPD12.__pga_aggregate_target=2583691264
EBILPD11.__pga_aggregate_target=2583691264
EBILPD12.__sga_target=12884901888
EBILPD11.__sga_target=12884901888
EBILPD12.__shared_io_pool_size=167772160
EBILPD11.__shared_io_pool_size=167772160
EBILPD12.__shared_pool_size=4429185024
EBILPD11.__shared_pool_size=3858759680
EBILPD12.__streams_pool_size=0
EBILPD11.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/EBILPD1/adump’
*.audit_trail=’db’
*.cluster_database=true
*.compatible=’12.1.0.2.0′
*.control_files=’+REDO_BILLP/EBILPD1/CONTROLFILE/current.256.928072537′,’+FRA/EBILPD1/CONTROLFILE/current.265.928072537′
*.db_block_size=8192
*.db_create_file_dest=’+DATA_BILLP’
*.db_create_online_log_dest_1=’+REDO_BILLP’
*.db_create_online_log_dest_2=’+FRA’
*.db_domain=’world.internal’
*.db_file_multiblock_read_count=8
*.db_files=2048
*.db_name=’EBILPD1′
*.db_recovery_file_dest=’+FRA’
*.db_recovery_file_dest_size=536870912000
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=EBILPD1XDB)’
*.dml_locks=20272
EBILPD11.instance_number=1
EBILPD12.instance_number=2
*.job_queue_processes=8
*.log_archive_dest_1=’LOCATION=+ARCH_BILLP’
*.log_archive_format=’log_%d_%t_%s_%r.arc’
*.log_buffer=2147483648
*.memory_max_target=0
*.memory_target=0
*.open_cursors=500
*.open_links=10
*.open_links_per_instance=100
*.parallel_max_servers=2590
*.parallel_min_servers=576
*.pga_aggregate_limit=9437184000
*.pga_aggregate_target=2583691264
*.processes=3000
*.remote_login_passwordfile=’exclusive’
*.sga_max_size=12884901888
*.sga_target=12884901888
*.shared_pool_reserved_size=50331648
*.standby_file_management=’AUTO’
EBILPD12.thread=2
EBILPD11.thread=1
*.undo_retention=3600
EBILPD12.undo_tablespace=’UNDOTBS2′
EBILPD11.undo_tablespace=’UNDOTBS1′
*.use_large_pages=’ONLY’

  1. The pfile needs to be change and copied to Far Sync node.The following parameters are changed to make Far Sync pfile ready.

cat initORCLFS1.ora

*.audit_file_dest=’/u01/app/oracle/admin/ORCLFS/adump’
*.audit_trail=’db’
*.cluster_database=true
*.compatible=’12.1.0.2.0′
*.control_files=’+DATA_REP/ORCLFS/control.ctl’
*.db_block_size=8192
*.db_create_file_dest=’+DATA_REP‘
*.db_create_online_log_dest_1=’+REDO_REP‘
*.db_create_online_log_dest_2=’+FRA’
*.db_domain=’world.internal’
*.db_file_multiblock_read_count=8
*.db_files=2048
*.db_name=’EBILPD1′
*.db_recovery_file_dest=’+FRA’
*.db_recovery_file_dest_size=536870912000
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLFSXDB)’
*.dml_locks=20272
ORCLFS1.instance_number=1
ORCLFS2.instance_number=2
*.job_queue_processes=8
*.log_archive_dest_1=’LOCATION=+ARCH_REP‘
*.log_archive_format=’log_%d_%t_%s_%r.arc’
*.log_buffer=2147483648
*.memory_max_target=0
*.memory_target=0
*.open_cursors=500
*.open_links=10
*.open_links_per_instance=100
*.parallel_max_servers=2590
*.parallel_min_servers=576
*.pga_aggregate_limit=9437184000
*.pga_aggregate_target=2583691264
*.processes=3000
*.remote_login_passwordfile=’exclusive’
*.sga_max_size=12884901888
*.sga_target=12884901888
*.shared_pool_reserved_size=50331648
*.standby_file_management=’AUTO’
ORCLFS2.thread=2
ORCLFS1.thread=1
*.undo_retention=3600
ORCLFS2.undo_tablespace=’UNDOTBS2′
ORCLFS1.undo_tablespace=’UNDOTBS1′
*.use_large_pages=’ONLY’
*.db_unique_name=’ORCLFS‘

  1. The pfile needs to be change and copied to standby node.The following parameters are changed to make standby pfile ready.

cat initEBILDR11.ora

*.audit_file_dest=’/u01/app/oracle/admin/EBILDR1/adump’
*.audit_trail=’db’
*.cluster_database=true
*.compatible=’12.1.0.2.0′
*.control_files=’+FRA/EBILDR1/control.ctl’
*.db_block_size=8192
*.db_create_file_dest=’+DATA_CRM‘
*.db_create_online_log_dest_1=’+REDO_CRM‘
*.db_create_online_log_dest_2=’+FRA’
*.db_domain=’world.internal’
*.db_file_multiblock_read_count=8
*.db_files=2048
*.db_name=’EBILPD1′
*.db_recovery_file_dest=’+FRA’
*.db_recovery_file_dest_size=536870912000
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=EBILDR1XDB)’
EBILDR11.instance_number=1
EBILDR12.instance_number=2
*.job_queue_processes=8
*.log_archive_dest_1=’LOCATION=+ARCH_CRM‘
*.log_archive_format=’log_%d_%t_%s_%r.arc’
*.log_buffer=2147483648
*.memory_max_target=0
*.memory_target=0
*.open_cursors=500
*.open_links=10
*.open_links_per_instance=100
*.remote_login_passwordfile=’exclusive’
*.sga_max_size=16884901888
*.sga_target=16884901888
*.standby_file_management=’AUTO’
EBILDR12.thread=2
EBILDR11.thread=1
*.undo_retention=3600
EBILDR12.undo_tablespace=’UNDOTBS2′
EBILDR11.undo_tablespace=’UNDOTBS1′
#*.use_large_pages=’ONLY’
*.db_unique_name=’EBILDR1‘

  1. The tns needs to be changed/added and copied to all node to $ORACLE_HOME/network/admin/tnsnames.ora

 

EBILDR1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DR-scan.world.internal)(PORT = 1591))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = EBILDR1.world.internal)
)
)

EBILPD1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PROD-scan.world.internal)(PORT = 1591))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = EBILPD1.world.internal)
)
)

ORCLFS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =FS-scan.world.internal)(PORT = 1591))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLFS.world.internal)
)

 

  1. Copy password file from primary and copy it to FarSync and Standby ASM.

In Primary

su – grid

ASMCMD> pwget –dbuniquename EBILPD1
+DATA_BILLP/EBILPD1/PASSWORD/pwdebilpd1.256.928072397

ASMCMD> pwcopy +DATA_BILLP/EBILPD1/PASSWORD/pwdebilpd1.256.928072397 /tmp/
copying +DATA/PrimaryDB/PASSWORD/pwdPrimaryDB.276.913723271 -> /tmp/pwdPrimaryDB.276.913723271

In FarSync host:-

su – grid

scp -pr oracle@10.84.44.6:/tmp/pwdebilpd1.256.928072397 /tmp/

ASMCMD>pwcopy /tmp/pwdebilpd1.256.928072397 +DATA_REP/pwdORCLFS.ora

In standby host:-

su – grid

scp -pr oracle@10.84.44.6:/tmp/pwdebilpd1.256.928072397 /tmp/

ASMCMD>pwcopy /tmp/pwdebilpd1.256.928072397 +DATA_CRM/pwdEBILDR1.ora

Create spfile from pfile in both FarSync and standby

In FarSync

SQL> create spfile=’+DATA_REP/ORCLFS/spORCLFS.ora’ from pfile;

In Standby

SQL> create spfile=’+DATA_CRM/EBILDR1/spfileEBILDR1.ora’ from pfile;

  1. Please add SRVCTL service for both standby and FarSync

In FarSync after login using oracle user

srvctl add database -d ORCLFS -o /u01/app/oracle/product/12.1.0/db_1 -r PHYSICAL_STANDBY -s mount -pwfile +DATA_REP/pwdORCLFS.ora -spfile ‘+DATA_REP/ORCLFS/spORCLFS.ora’

srvctl add instance -d ORCLFS -i ORCLFS1 -n prfsdbf01
srvctl add instance -d ORCLFS -i ORCLFS2 -n prfsdbf02

In standby after login using oracle user

srvctl add database -d EBILDR1 -o /u01/app/oracle/product/12.1.0/db_1 -r PHYSICAL_STANDBY -s mount -pwfile +DATA_CRM/pwdEBILDR1.ora -spfile ‘+DATA_CRM/EBILDR1/spfileEBILDR1.ora’

srvctl add instance -d EBILDR1 -i EBILDR11 -n prdrdbf01
srvctl add instance -d EBILDR1 -i EBILDR12 -n prdrdbf02

  1. Please take backup of controlfile for FarSync and copy it to FarSync node

SQL>ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS ‘/tmp/orclfs.ctl’;

scp -pr *.ctl  oracle@10.84.44.6:/tmp/

The following steps needs to be done in FarSync to restore FarSync controlfile

rman target /

restore controlfile to ‘+DATA_REP/ORCLFS/control.ctl’ from ‘/tmp/orclfs.ctl’;

  1. Please add standby redolog file in FarSync and check status

—add standby log—-

set serveroutput on;
declare
log_num number;
log_size number;
log_num_standby number;
begin
for j in (select inst_id i from gv$instance) loop
select count(*) into log_num from v$log where thread#=j.i;
select max(BYTES) into log_size from gv$log where inst_id=j.i ;
for i in 1..(log_num+1) loop
dbms_output.put_line(‘ALTER DATABASE ADD STANDBY LOGFILE THREAD ‘||j.i ||’ SIZE ‘||log_size||’;’);
end loop;
end loop;
end;
/

SQL> select database_role from v$database;

DATABASE_ROLE
—————-
FAR SYNC

SQL> select DB_UNIQUE_NAME,DEST_ROLE from V$DATAGUARD_CONFIG;

no rows selected

SQL>alter system set log_archive_config=’DG_CONFIG=(EBILPD1,ORCLFS,EBILDR1)’;
SQL>alter system set log_archive_dest_2=’service=EBILDR1 ASYNC valid_for=(standby_logfiles,standby_role) db_unique_name=EBILDR1′;

SQL> ALTER DATABASE SET STANDBY TO MAXIMIZE AVAILABILITY;

  1. Enable forced logging in primary

In order to implement Standby Database I enable ‘Forced Logging’. This option ensures that even in the event that a ‘nologging’ operation is done, force logging takes precedence and all operations are logged into the redo logs.

SQL> alter database force logging;

 

  1. In Primary add following dataguard related parameters.

SQL>alter system set log_archive_dest_2=’service=ORCLFS SYNC AFFIRM alternate=log_archive_dest_3 valid_for=(online_logfiles,primary_role) db_unique_name=ORCLFS’;
SQL>alter system set log_archive_dest_3=’service=EBILDR1 SYNC max_failure=1 alternate=log_archive_dest_2 valid_for=(online_logfiles,primary_role) db_unique_name=EBILDR1′;
SQL>alter system set log_archive_dest_state_3=alternate;
SQL>alter system set log_archive_config=’DG_CONFIG=(EBILPD1,ORCLFS,EBILDR1)’;
SQL>alter system set fal_server=’EBILDR1′;
SQL>alter system set standby_file_management=’AUTO’;

SQL> ALTER DATABASE SET STANDBY TO MAXIMIZE AVAILABILITY;

  1. Take RMAN backup in primary to be restored in standby

export ORACLE_SID=EBILPD1

run
{
allocate channel ch1 type disk format ‘/oraclecd/rman/Primary_bkp_for_stndby_%U’;
allocate channel ch2 type disk format ‘/oraclecd/rman/Primary_bkp_for_stndby_%U’;
BACKUP AS COMPRESSED BACKUPSET DATABASE;
backup current controlfile for standby;
}

 

  1. In Standby,We need to copy the backup and restore

export ORACLE_SID=EBILDR1

rman target sys/***@EBILPD1 auxiliary /

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;

After that please open the standby database for active data guard and start managed recovery.

RMAN>alter database open;

RMAN>alter database recover managed standby database disconnect;

  1. Now please check alert logs and sync.

For Oracle RDBMS Version 12.1 set the parameter ”_redo_transport_stall_time”=60 in all instances within the configuration for best return to synchronization after a node outage with an ALTERNATE Far Sync configuration

DGMGRL configuration

Please enable DG_BROKER using below command

alter system set dg_broker_start=false sid=’*’;

–Please place DG related configuration file in ASM in case of RAC–
alter system set dg_broker_config_file1=’+FRA/dr1.dat’ sid=’*’ scope=both;
alter system set dg_broker_config_file2=’+FRA/dr2.dat’ sid=’*’ scope=both;

alter system set dg_broker_start=true sid=’*’;

Please reset those parameters if you did not use DGMGRL (For example as in above case)

alter system set log_archive_dest_2=”;
alter system set log_archive_dest_3=”;
alter system set log_archive_config=”;
alter system set fal_server=”;

Now login to primary and start DGMGRL configuration

export ORACLE_SID=EBILPD1

dgmgrl /
create configuration ‘DGconfig’ as primary database is EBILPD1 connect identifier is EBILPD1;
ADD FAR_SYNC ORCLFS as connect identifier is ORCLFS;
add database EBILDR1 as connect identifier is EBILDR1;
edit FAR_SYNC ORCLFS set property RedoRoutes='(EBILPD1 :EBILDR1 ASYNC)’;
EDIT DATABASE EBILDR1 SET PROPERTY LogXptMode = ‘SYNC’;
EDIT DATABASE EBILPD1 SET PROPERTY MaxFailure = 0;
EDIT FAR_SYNC ORCLFS SET PROPERTY MaxFailure = 1;
EDIT DATABASE EBILDR1 SET PROPERTY MaxFailure = 0;
EDIT DATABASE EBILDR1 SET PROPERTY RedoRoutes = ‘(LOCAL : EBILPD1 ASYNC)’;

EDIT DATABASE EBILPD1 SET PROPERTY RedoRoutes = ‘(LOCAL : ORCLFS SYNC ALT =(EBILDR1 ASYNC FALLBACK))’;

DISABLE CONFIGURATION;
EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

ENABLE CONFIGURATION;

Check status

[oracle@EBILPD1 olg1prbscsdbf01 ~]$ dgmgrl /

DGMGRL for Linux: Version 12.1.0.2.0 – 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.

Connected as SYSDG.

DGMGRL> show configuration;

Configuration – DGconfig

Protection Mode: MaxAvailability

Members:

ebilpd1 – Primary database

orclfs  – Far sync instance

ebildr1 – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS   (status updated 57 seconds ago)

Now all Dataguard related configurations will be automatically setup.

In Primary,

log_archive_dest_2 string service=”orclfs”, SYNC AFFIRM delay=0 optional compression=disable max_failure=1 max_connections=1 reopen=300 db_unique_name=”orclfs” net_timeout=30,alternate=LOG_ARCHIVE_DEST_3 v
alid_for=(online_logfile,all_roles)

log_archive_dest_3 string service=”ebildr1″, ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_c
onnections=1 reopen=300 db_unique_name=”ebildr1″ net_timeout=30, alternate=LOG_ARCHIVE_DEST_2 valid_for=(online_logfile,all_roles)

log_archive_dest_state_3 string ALTERNATE

  In FarSync

log_archive_dest_2 string service=”ebildr1″, ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name=”ebildr1″ net_timeout=30, valid_for=(standby_logfile,all_roles)

SQL> show parameter fal;

NAME TYPE VALUE
———————————— ———– ——————————
fal_client string
fal_server string ebilpd1, ebildr1

In standby

SQL> show parameter fal;

NAME TYPE VALUE
———————————— ———– ——————————
fal_client string
fal_server string ebilpd1, orclfs

 

Reference

Click to access farsync-2267608.pdf