Data Gard Setup RAC

add primary and dr ip in /etc/hosts on all nodes (primary and dr)

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

################################ PRIMARY #########################################
# Public
10.34.140.11 hq-HostName-v01   hq-HostName-v01.pbdac.local
10.34.140.12 hq-HostName-v02   hq-HostName-v02.pbdac.local


# Virtual
10.34.140.13     hq-HostName-v01-vip   hq-HostName-v01-vip.pbdac.local
10.34.140.14     hq-HostName-v02-vip   hq-HostName-v02-vip.pbdac.local

# Private
10.34.40.15      hq-HostName-v01-priv  hq-HostName-v01-priv.pbdac.local
10.34.40.16      hq-HostName-v02-priv  hq-HostName-v02-priv.pbdac.local


# SCAN
10.34.140.17     hq-HostName-scan   hq-HostName-scan.pbdac.local
10.34.140.18     hq-HostName-scan   hq-HostName-scan.pbdac.local
10.34.140.19     hq-HostName-scan   hq-HostName-scan.pbdac.local


############################## DR #############################################

# Public
10.48.140.11     dr-HostName-db-v01        dr-HostName-db-v01.pbdac.local
10.48.140.12     dr-HostName-db-v02        dr-HostName-db-v02.pbdac.local

# Private
172.20.140.13    dr-HostName-db-v01-priv    dr-HostName-db-v01-priv.pbdac.local
172.20.140.14    dr-HostName-db-v02-priv    dr-HostName-db-v02-priv.pbdac.local


# Virtual
10.48.140.15     dr-HostName-db-v01-vip     dr-HostName-db-v01-vip.pbdac.local
10.48.140.16     dr-HostName-db-v02-vip     dr-HostName-db-v02-vip.pbdac.local

# SCAN
10.48.140.17     dr-HostName-db-scan       dr-HostName-db-scan.pbdac.local
10.48.140.18     dr-HostName-db-scan       dr-HostName-db-scan.pbdac.local
10.48.140.19     dr-HostName-db-scan       dr-HostName-db-scan.pbdac.local


============================== on primary =================================

select log_mode,database_role,open_mode from v$database;

---------------------If it is noarchivelog mode----------------------

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

select force_logging from v$database; >> object write in redolog , when doing switchover there is be corruption 

ALTER DATABASE FORCE LOGGING;

alter system set log_archive_config='DG_CONFIG=(<DB_Name_PRD>,<DB_Name_DR>)' scope=both sid='*';


alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST_AS_DEFAULT VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=<DB_Name_PRD>' scope= both sid='*';

alter system set log_archive_dest_2='SERVICE=<DB_Name_DR> VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=<DB_Name_DR>' scope=both sid='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='=SERVICE=CBECONDR AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CBECONDR' scope=both sid='*' ;

alter system set LOG_ARCHIVE_MAX_PROCESSES=8 scope=both sid='*';

-------------- check datafile locations 

rman target/

report schema


alter system set DB_FILE_NAME_CONVERT='+DATA/<DB_Name_DR>','+DATA/<DB_Name_PRD>' scope=spfile sid='*';

--------------- check redo log files location

sqlplus / as sysdba

select member from v$logfile;

alter system set LOG_FILE_NAME_CONVERT= '+DATA/<DB_Name_DR>/ONLINELOG','+DATA/<DB_Name_PRD>/ONLINELOG','+FRA/<DB_Name_DR>/ONLINELOG','+FRA/<DB_Name_PRD>/ONLINELOG' scope=spfile sid='*';

alter system set FAL_SERVER='<DB_Name_DR>1,<DB_Name_DR>2' scope=both sid='*'; >>fetch archivelog 

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*'; ==> 3shan a5ly el files kolha tro7 ll Dr auto msh manually

SQL> create pfile='/u01/app/oracle/init<DB_Name_DR>.ora' from spfile;

scp /u01/app/oracle/init<DB_Name_DR>.ora 10.48.193.11:/u01/app/oracle

------------------ check redolog group 

sqlplus / as sysdba

select THREAD#,GROUP#,MEMBERS,BYTES,status from v$log; >> group = group+1 , size = size , member=member

 THREAD#     GROUP#    MEMBERS      BYTES
---------- ---------- ---------- ----------
         1         11          2 1073741824
         1         12          2 1073741824
         2         13          2 1073741824
         2         14          2 1073741824
		 
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('+FRA','+DATA') SIZE 2147483648;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('+FRA','+DATA') SIZE 2147483648;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('+FRA','+DATA') SIZE 2147483648;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 ('+FRA','+DATA') SIZE 2147483648;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 ('+FRA','+DATA') SIZE 2147483648;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 ('+FRA','+DATA') SIZE 2147483648;


select group#,thread#,bytes from v$standby_log;

---------------- check password file location and transfer to standby

srvctl config database -d <DB_Name_PRD> -a

. grid_env
asmcmd
cd +DATA/<DB_Name_PRD>/PASSWORD
ls
cp +DATA/<DB_Name_PRD>/PASSWORD/pwd<DB_Name_PRD>.256.1093106997 /u01/app/oracle/pw<DB_Name_PRD>
exit


scp /u01/app/oracle/pw<DB_Name_PRD> 192.168.0.127:/u01/app/oracle/product/19.3/db_1/dbs/orapw<DB_Name_DR>1




================================ on standby ======================


asmcmd

ASMCMD> cd DATA
ASMCMD> mkdir <DB_Name_DR>
ASMCMD> cd <DB_Name_DR>
ASMCMD> mkdir DATAFILE
ASMCMD> mkdir ONLINELOG
ASMCMD> mkdir PASSWORD
ASMCMD> mkdir CONTROLFILE
ASMCMD> mkdir PARAMETERFILE
ASMCMD> mkdir TEMPFILE/
ASMCMD> cp /u01/app/oracle/product/19.3/db_1/dbs/orapw<DB_Name_DR>1 +DATA/<DB_Name_DR>/PASSWORD/pw<DB_Name_DR>
ASMCMD> cd FRA
ASMCMD> mkdir <DB_Name_DR>
ASMCMD> cd <DB_Name_DR>
ASMCMD> mkdir DATAFILE
ASMCMD> mkdir ONLINELOG
ASMCMD> mkdir ARCHIVELOG
ASMCMD> mkdir CONTROLFILE

====================== on two standby nodes =================================

mkdir -p /u01/app/oracle/admin/<DB_Name_DR>/adump


================================== on primary ==============================

vi /u01/app/oracle/product/19.3/db_1/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.3/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

<DB_Name_PRD> =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <DB_Name_PRD>)
    )
  )

<DB_Name_DR> =
  (DESCRIPTION =
  (LOAD_BALANCE=on)
  (FAILOVER=on)
    (ADDRESS = (PROTOCOL = TCP)(HOST =racnode1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST =racnode2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <DB_Name_DR>)
    )
  )

<DB_Name_PRD>1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <DB_Name_PRD>) (UR=A)
      (INSTANCE_NAME = <DB_Name_PRD>1)
    )
  )

<DB_Name_PRD>2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <DB_Name_PRD>) (UR=A)
      (INSTANCE_NAME = <DB_Name_PRD>2)
    )
  )


<DB_Name_DR>1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <DB_Name_DR>) (UR=A)
      (INSTANCE_NAME = <DB_Name_DR>1)
    )
  )


<DB_Name_DR>2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <DB_Name_DR>) (UR=A)
      (INSTANCE_NAME = <DB_Name_DR>2)
    )
  )

scp /u01/app/oracle/product/19.3/db_1/network/admin/tnsnames.ora 192.168.1.128:/u01/app/oracle/product/19.3/db_1/network/admin/tnsnames.ora

scp /u01/app/oracle/product/19.3/db_1/network/admin/tnsnames.ora 192.168.0.127:/u01/app/oracle/product/19.3/db_1/network/admin/tnsnames.ora

scp /u01/app/oracle/product/19.3/db_1/network/admin/tnsnames.ora 192.168.0.128:/u01/app/oracle/product/19.3/db_1/network/admin/tnsnames.ora

----------------------------on secondary 
add database in lisnter (. grid_env) and reload listner 

. grid_env or su - grid

vi /u01/app/19.0.0.0/grid/network/admin/listner.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = <DB_Name_DR>)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0.0/dbhome_1)
      (SID_NAME = <DB_Name_DR>1)
    )
  )

srvctl	stop listener
=================================== on standby =======================================
on pfile 


vi /u01/app/oracle/product/19.3/db_1/dbs/init<DB_Name_DR>.ora

*.audit_file_dest='/u01/app/oracle/admin/<DB_Name_DR>/adump'
*.compatible='19.0.0'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='<DB_Name_PRD>'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=501760M
*.db_unique_name='<DB_Name_DR>'
*.db_file_name_convert='+DATA/<DB_Name_PRD>/DATAFILE','+DATA/<DB_Name_DR>/DATAFILE'
*.diagnostic_dest='/u01/app/oracle'
*.fal_server='<DB_Name_PRD>1,<DB_Name_PRD>2'
*.fal_client='<DB_Name_DR>1,<DB_Name_DR>2'
*.log_archive_config='dg_config=(<DB_Name_DR>,<DB_Name_PRD>)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST_AS_DEFAULT VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=<DB_Name_DR>'
*.log_archive_dest_2='SERVICE=<DB_Name_PRD> VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=<DB_Name_PRD>'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='+DATA/<DB_Name_PRD>/ONLINELOG','+DATA/<DB_Name_DR>/ONLINELOG','+FRA/<DB_Name_PRD>/ONLINELOG','+FRA/<DB_Name_DR>/ONLINELOG'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1603m
*.processes=1280
*.remote_login_passwordfile='exclusive'
*.sga_target=4808m
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'


SQL> startup nomount pfile='/u01/app/oracle/product/19.3/db_1/dbs/init<DB_Name_DR>.ora'
SQL > create spfile from pfile='/u01/app/oracle/product/19.3/db_1/dbs/init<DB_Name_DR>.ora';
SQL> shut abort
SQL> startup nomount
or 
SQL> startup nomount force


================================= on primary ==================================


rman target sys/Ora2022_2022@<DB_Name_PRD>1  auxiliary sys/Ora2022_2022@<DB_Name_DR>1

vi duplicate.cmd

run{
allocate channel ch01 device type disk;
allocate channel ch02 device type disk;
allocate channel ch03 device type disk;
allocate channel ch04 device type disk;
allocate channel ch05 device type disk;
allocate channel ch06 device type disk;
allocate channel ch07 device type disk;
allocate channel ch08 device type disk;
allocate auxiliary channel ch09 device type disk;
allocate auxiliary channel ch10 device type disk;
allocate auxiliary channel ch11 device type disk;
allocate auxiliary channel ch12 device type disk;
allocate auxiliary channel ch13 device type disk;
allocate auxiliary channel ch14 device type disk;
allocate auxiliary channel ch15 device type disk;
allocate auxiliary channel ch16 device type disk;
duplicate target database for standby from active database nofilenamecheck section size 8G;
}


chmod 775 duplicate.cmd

nohup rman target sys/Ora2022_2022@<DB_Name_PRD>1  auxiliary sys/Ora2022_2022@<DB_Name_DR>1 cmdfile=/home/oracle/duplicate.cmd log=/home/oracle/duplicate.log &



========================================== on standby node 1 ====================================
 ---------- create pfile to add cluster parameters
 
SQL> create pfile='/u01/app/oracle/init<DB_Name_DR>1.ora' from spfile;

vi /u01/app/oracle/init<DB_Name_DR>1.ora

*.audit_file_dest='/u01/app/oracle/admin/<DB_Name_DR>/adump'
*.compatible='19.0.0'
*.control_files='+DATA/<DB_Name_DR>/CONTROLFILE/current.268.1106056993','+FRA/<DB_Name_DR>/CONTROLFILE/current.257.1106056993'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_file_name_convert='+DATA/<DB_Name_PRD>/DATAFILE','+DATA/<DB_Name_DR>/DATAFILE'
*.db_name='<DB_Name_PRD>'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=501760M
*.db_unique_name='<DB_Name_DR>'
*.diagnostic_dest='/u01/app/oracle'
*.fal_client='<DB_Name_DR>1,<DB_Name_DR>2'
*.fal_server='<DB_Name_PRD>1,<DB_Name_PRD>2'
*.log_archive_config='dg_config=(<DB_Name_DR>,<DB_Name_PRD>)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=<DB_Name_DR>'
*.log_archive_dest_2='SERVICE=<DB_Name_PRD> VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=<DB_Name_PRD>'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='+DATA/<DB_Name_PRD>/ONLINELOG','+DATA/<DB_Name_DR>/ONLINELOG','+FRA/<DB_Name_PRD>/ONLINELOG','+FRA/<DB_Name_DR>/ONLINELOG'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1603m
*.processes=1280
*.remote_login_passwordfile='exclusive'
*.sga_target=4808m
*.standby_file_management='auto'
*.cluster_database=true
<DB_Name_DR>1.instance_name='<DB_Name_DR>1'
<DB_Name_DR>2.instance_name='<DB_Name_DR>2'
<DB_Name_DR>1.instance_number =1
<DB_Name_DR>2.instance_number =2
<DB_Name_DR>1.thread=1
<DB_Name_DR>2.thread=2
<DB_Name_DR>1.undo_tablespace='UNDOTBS1'
<DB_Name_DR>2.undo_tablespace='UNDOTBS2'


 - Start Instance 1 with this pfile : 
   
   sqlplus / as sysdba
   shut immediate
   startup nomount pfile='/u01/app/oracle/init<DB_Name_DR>1.ora';
   create spfile='+DATA' from pfile='/u01/app/oracle/init<DB_Name_DR>1.ora';
   shut immediate;
   
  -------------------- check spfile name from asmcmd
  
  . grid_env or su - grid
  
  cd +DATA/<DB_Name_DR>/PARAMETERFILE
  
  ls 
  
  +DATA/<DB_Name_DR>/PARAMETERFILE/spfile.270.1189353049

   
  - ADD DATABASE TO CLUSTER from first node by ORACLE_HOME (su - oracle)
srvctl add database -d <DB_Name_DR> -o /u01/app/oracle/product/19.3.0.0/dbhome_1 -pwfile +DATA/<DB_Name_DR>/PASSWORD/pwd<DB_Name_DR> -spfile +DATA/<DB_Name_DR>/PARAMETERFILE/spfile.270.1189353049 -role physical_standby -startoption mount -dbname <DB_Name_PRD>
   srvctl config database -d <DB_Name_DR> -a
   srvctl add instance -d <DB_Name_DR> -i <DB_Name_DR>1 -n dr-misdb-v01
   srvctl add instance -d <DB_Name_DR> -i <DB_Name_DR>2 -n dr-misdb-v02
   srvctl config database -d <DB_Name_DR> -a
   srvctl start database -d <DB_Name_DR>
   
   cd /u01/app/19.3/grid/bin/
   ./crsctl stat res -t
   
   edit local listener on node2 (add vip2)
   
   sqlplus / as sysdba
   
   alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.48.193.14)(PORT=1521)))' sid='<DB_Name_DR>2';


====================================confirm aplly log on standby server==========================================
 
 
 ALTER DATABASE RECOVER automatic MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect; >>manage manually from sql 
 instead of broker
 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; >> stop apply
 
 select PROCESS,STATUS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS from V$managed_standby; >> check mrp and rfs proccess
 --------------------------on primary --------------
 
 alter system switch all logfile; >> to switch logfile on all threads
 
 alter system checkpoint global; >> to force checkpoint on all RAC nodes
 
 -------------------------------------------------------DG check on both DB--------------------------------

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;

==============================================================
===================================Configure Data Guard Broker=====================================

Enable DG Broker Parameters:-

Primary:-

cd 
. grid_env
asmcmd
mkdir +DATA/<DB_Name_PRD>/DATAGUARDCONFIG
mkdir +FRA/<DB_Name_PRD>/DATAGUARDCONFIG

. db_env
sqlplus / as sysdba
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DATA/<DB_Name_PRD>/DATAGUARDCONFIG/dgb_config01.ora' SCOPE=BOTH sid='*';
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+FRA/<DB_Name_PRD>/DATAGUARDCONFIG/dgb_config02.ora' SCOPE=BOTH sid='*';
ALTER SYSTEM SET dg_broker_start=true scope=both sid='*';

Standby:-
cd 
. grid_env
asmcmd
mkdir +DATA/<DB_Name_DR>/DATAGUARDCONFIG
mkdir +FRA/<DB_Name_DR>/DATAGUARDCONFIG

. db_env
sqlplus / as sysdba
alter system reset log_archive_dest_2 scope=both sid='*';
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DATA/<DB_Name_DR>/DATAGUARDCONFIG/dgb_config01.ora' SCOPE=BOTH sid='*';
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+FRA/<DB_Name_DR>/DATAGUARDCONFIG/dgb_config02.ora' SCOPE=BOTH sid='*';
ALTER SYSTEM SET dg_broker_start=true scope=both sid='*';

					
========================================Creating the Broker Configuration on primary server=====================
 . db_env
dgmgrl sys/Ora2022_2022

create configuration 'DGBROK' as primary database  is '<DB_Name_PRD>' connect identifier is <DB_Name_PRD>;

show configuration

ADD DATABASE <DB_Name_DR> AS CONNECT IDENTIFIER IS <DB_Name_DR> MAINTAINED AS PHYSICAL;

show configuration



================

enable configuration on broker :


enable configuration

---------------- to check switchover status from broker

dgmgrl sys/***

validate database <DB_Name_DR>;

validate network configuration for  all;

validate database <DB_Name_DR> spfile; >> validate spfile parameters between two nodes

audit_file_dest:
<DB_Name_PRD> (PRIMARY) : /u01/app/oracle/admin/<DB_Name_PRD>/adump
<DB_Name_DR>          : /u01/app/oracle/admin/<DB_Name_DR>/adump

cluster_database_instances:
<DB_Name_PRD> (PRIMARY) : NOT SPECIFIED
<DB_Name_DR>          : 2

dispatchers:
<DB_Name_PRD> (PRIMARY) : (PROTOCOL=TCP) (SERVICE=<DB_Name_PRD>XDB)
<DB_Name_DR>          : (PROTOCOL=TCP) (SERVICE=<DB_Name_DR>XDB)