Data Gard Setup Single

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

192.168.149.11 hq-HostName-v01
196.161.149.11 dr-HostName-v01


============================== 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 ;


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 ;

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 ;

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

alter system set LOG_ARCHIVE_MAX_PROCESSES=8 scope=both ;

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

rman target/

report schema;


alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata','/u01/app/oracle/oradata','/u01/app/oracle/oradata','/u01/app/oracle/product/19.3.0.0/dbhome_1/dbs' scope=spfile ;

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

sqlplus / as sysdba

select member from v$logfile;

alter system set LOG_FILE_NAME_CONVERT= '/u01/app/oracle/oradata/<DB_Name_DR>/onlinelog','/u01/app/oracle/oradata/<DB_Name_PRD>/onlinelog','/u01/app/oracle/fast_recovery_area/<DB_Name_DR>/onlinelog','/u01/app/oracle/fast_recovery_area/<DB_Name_PRD>/onlinelog' scope=spfile ;

alter system set FAL_SERVER='<DB_Name_DR>' scope=both ; >>fetch archivelog 

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both ; ==> 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 196.161.149.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 STATUS
---------- ---------- ---------- ---------- ----------------
         1          4          2 2147483648 INACTIVE
         1          5          2 2147483648 CURRENT
         1          6          2 2147483648 UNUSED

ALTER DATABASE ADD  STANDBY LOGFILE THREAD 1  group 7('/u01/app/oracle/oradata/<DB_Name_PRD>/onlinelog/standby_redo7.log','/u01/app/oracle/fast_recovery_area/<DB_Name_PRD>/onlinelog/standby_redo7.log') SIZE 2G;
ALTER DATABASE ADD  STANDBY LOGFILE THREAD 1  group 8('/u01/app/oracle/oradata/<DB_Name_PRD>/onlinelog/standby_redo8.log','/u01/app/oracle/fast_recovery_area/<DB_Name_PRD>/onlinelog/standby_redo8.log') SIZE 2G;
ALTER DATABASE ADD  STANDBY LOGFILE THREAD 1  group 9('/u01/app/oracle/oradata/<DB_Name_PRD>/onlinelog/standby_redo9.log','/u01/app/oracle/fast_recovery_area/<DB_Name_PRD>/onlinelog/standby_redo9.log') SIZE 2G;
ALTER DATABASE ADD  STANDBY LOGFILE THREAD 1  group 10('/u01/app/oracle/oradata/<DB_Name_PRD>/onlinelog/standby_redo10.log','/u01/app/oracle/fast_recovery_area/<DB_Name_PRD>/onlinelog/standby_redo10.log') SIZE 2G;



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

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



scp /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/orapw<DB_Name_PRD> 196.161.149.11:/u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/orapw<DB_Name_DR>




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

mkdir -p /u01/app/oracle/oradata/<DB_Name_DR>/onlinelog

mkdir -p /u01/app/oracle/fast_recovery_area/<DB_Name_PRD>/onlinelog

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


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

vi /u01/app/oracle/product/19.3.0.0/dbhome_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 = hq-HostName-v01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <DB_Name_PRD>)
    )
  )

<DB_Name_DR> =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dr-HostName-v01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <DB_Name_DR>)
    )
  )



scp /u01/app/oracle/product/19.3.0.0/dbhome_1/network/admin/tnsnames.ora 196.161.149.11:/u01/app/oracle/product/19.3.0.0/dbhome_1/network/admin/tnsnames.ora



----------------------------on standby


vi /u01/app/oracle/product/19.3.0.0/dbhome_1/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>)
    )
  )

=================================== on standby =======================================
on pfile 


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

*.audit_file_dest='/u01/app/oracle/admin/<DB_Name_DR>/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/<DB_Name_DR>/controlfile/control1.ctl','/u01/app/oracle/fast_recovery_area/<DB_Name_DR>/controlfile/control2.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_file_name_convert='/u01/app/oracle/oradata','/u01/app/oracle/oradata','/u01/app/oracle/product/19.3.0.0/dbhome_1/dbs','/u01/app/oracle/oradata'
*.db_name='<DB_Name_PRD>'
*.db_unique_name='<DB_Name_DR>'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=85899345920
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=<DB_Name_DR>XDB)'
*.enable_pluggable_database=true
*.fal_server='<DB_Name_PRD>'
*.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,PRIMARY_ROLE) DB_UNIQUE_NAME=<DB_Name_PRD>'
*.log_archive_max_processes=8
*.log_file_name_convert='/u01/app/oracle/oradata/<DB_Name_PRD>/onlinelog','/u01/app/oracle/oradata/<DB_Name_DR>/onlinelog','/u01/app/oracle/fast_recovery_area/<DB_Name_PRD>/onlinelog','/u01/app/oracle/fast_recovery_area/<DB_Name_DR>/onlinelog'
*.open_cursors=300
*.pga_aggregate_target=1571m
*.processes=640
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=4713m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'



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


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


rman target sys/<password>@<DB_Name_PRD>  auxiliary sys/<password>@<DB_Name_DR>

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/<password>@<DB_Name_PRD>  auxiliary sys/<password>@<DB_Name_DR> cmdfile=/home/oracle/duplicate.cmd log=/home/oracle/duplicate.log &



====================================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=====================================

                           ------------------Setting the DG_BROKER_START Initialization Parameter on both---------------------
alter system set  dg_broker_start = true scope=both;

                          --------------------edit GLOBAL_DBNAME in listener.ora on both-------------------
						  
vi /u01/app/oracle/product/19.3.0.0/dbhome_1/network/admin/listener.ora

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



lsnrctl reload
===============================================================================
vi /u01/app/oracle/product/19.3/db_1/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = <DB_Name_DR>_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/19.3/db_1)
      (SID_NAME = <DB_Name_DR>)
    )
  )


lsnrctl reload

========================================Creating the Broker Configuration on primary server=====================

dgmgrl sys/ssss

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

show configuration

================== on standby ======================
alter system reset log_archive_dest_2 scope=both;
===================================================

============== return to primary ============

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


show configuration

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


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