Step by Step to configure Oracle 12c Data Guard Physical Standby

Oracle 12c Data Guard with Active DataGuard feature

Oracle 12c Data Guard Configuration (Active Data guard Database using RMAN DUPLICATE FROM ACTIVE command) on Redhat Linux 6 using VMware environment step by step.

Requirements:

1) Two machines with Redhat Linux 6 OS.
2) Oracle 12c Database software.
3) Primary Database name OCPPR & IP ADDRESS IS 192.168.1.10
4) Standby Database name OCPDR & IP ADDRESS IS 192.168.1.20

Note: – we are going to configure Oracle Data Guard on 12c Container Database, On the Primary machine install Oracle software with one database configuration & on the Standby machine install only Oracle 12c software without any Database.

The parameter DB_NAME will same on both nodes, but parameter DB_UNIQUE_NAME must be different.

Primary side configurations

Step-1. Install Oracle 12c software with one Database Click Here

Start Oracle 12c configuration

Step-2. Check Primary Database must be running in archive log mode if not, change it.

SQL> SELECT log_mode FROM v$database; 
LOG_MODE
------------
NOARCHIVELOG 

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

Enable Force Logging

Step-3. Enable force logging using the following command.

SQL> alter database force logging;

At least one log file available

SQL> alter system switch logfile;

Create standby redos

Step-4. Now create standby redo logs for switchovers and should be adding one extra.

SQL> alter database add standby logfile ‘/u01/app/oracle/oradata/ocpr/standby_redo01.log’ size 50m;

SQL> alter database add standby logfile ‘/u01/app/oracle/oradata/ocppr/standby_redo02.log’ size 50m;

SQL> alter database add standby logfile ‘/u01/app/oracle/oradata/ocppr/standby_redo03.log’ size 50m;

SQL> alter database add standby logfile ‘/u01/app/oracle/oradata/ocppr/standby_redo04.log’ size 50m;

Step-5. Check DB_NAME & DB_UNIQUE_NAME

SQL> show parameter db_name NAME      
                              
TYPE         VALUE       NAME
----------- ----------- -----------
db_name       string      ocppr 


SQL> show parameter db_unique_name   
                                 
TYPE              VALUE        NAME
---------------  ------------ -----------
db_unique_name     string      ocppr

Note:- On standby have db_unique_name is OCPDR.

Enable Log_archive_dest_2

Step-6. Now set remote archive log destination for standby & local is in flash_recovery_area.

SQL> alter system set log_archive_dest_2= ‘service=ocpdr async noaffirm reopen=15  valid_for=(all_logfiles,primary_role) db_unique_name=ocpdr’;

Step-7. The STANDBY_FILE_MANAGEMENT parameter must be set AUTO.

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Step-8. Now configure a listener for your Primary Database using the NETMGR command & make TNS service on both sides (Primary & Standby).

Step-9.  Restart the listener on both sides and try typing.

$lsnrctl stop
$lsnrctl start
$tnsping ocpdr   (on primary)
$tnsping ocppr   (on standby)

Step-10.  Set the log_archive_config parameter as below.

SQL> alter system set log_archive_config='dg_config=(ocppr,ocpdr)';

Step-11. Must be set remote_login_passwordfile exclusive.

SQL> alter system set remote_login_passwordfile='EXCLUSIVE';

Step-12. Now update the fal_client & fal_server parameter using the following commands.

SQL> alter system set fal_server='OCPDR';
SQL> alter system set fal_client='OCPPR';

Step-13. Create pfile from spfile for the standby database.

SQL> create pfile=’/u01/initocpdr.ora’ from spfile;

After all, that configuration your Primary pfile lock lick this.

ocppr.__data_transfer_cache_size=0
 ocppr.__db_cache_size=620756992
 ocppr.__java_pool_size=16777216
 ocppr.__large_pool_size=33554432
 ocppr.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
 ocppr.__pga_aggregate_target=637534208
 ocppr.__sga_target=956301312
 ocppr.__shared_io_pool_size=50331648
 ocppr.__shared_pool_size=218103808
 ocppr.__streams_pool_size=0
 *.audit_file_dest='/u01/app/oracle/admin/ocppr/adump'
 *.audit_trail='db'
 *.compatible='12.1.0.0.0'
*.control_files='/u01/app/oracle/oradata/ocppr/control01.ctl','/u01/app/oracle/fast_recovery_area/ocppr/control02.ctl'
 *.db_block_size=8192
 *.db_domain=''
 *.db_name='ocppr'
 *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
 *.db_recovery_file_dest_size=4800m
 *.fal_client='OCPPR'
 *.fal_server='OCPDR'
 *.diagnostic_dest='/u01/app/oracle'
 *.dispatchers='(PROTOCOL=TCP) (SERVICE=ocpprXDB)'
 *.local_listener='LISTENER_OCPPR'
 *.log_archive_config='dg_config=(ocppr,ocpdr)'
 *.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=ocppr'
 *.log_archive_dest_2='service=ocpdr valid_for=(all_logfiles,primary_role) db_unique_name=ocpdr'
 *.memory_target=1520m
 *.open_cursors=300
 *.processes=300
 *.remote_login_passwordfile='EXCLUSIVE'
 *.standby_file_management='AUTO'
 *.undo_tablespace='UNDOTBS1'

Now we have done primary server tasks then move the pfile & password file from the PRIMARY server to the STANDBY server.

STANDBY configurations

Step-14. Make required directories on Standby like.

mkdir /u01/app/oracle/admin/ocpdr/adump
mkdir /u01/app/oracle/oradata/ocdpr/
mkdir /u01/app/oracle/fast_recovery_area/ocdpr/

Step-15. Make some changes in pfile (Add two parameters “log_file_name_convert” & “db_file_name_convert“, after changing your standby pfile lock like this.

ocpdr.__data_transfer_cache_size=0
 ocpdr.__db_cache_size=620756992
 ocpdr.__java_pool_size=16777216
 ocpdr.__large_pool_size=33554432
 ocpdr.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
 ocpdr.__pga_aggregate_target=637534208
 ocpdr.__sga_target=956301312
 ocpdr.__shared_io_pool_size=50331648
 ocpdr.__shared_pool_size=218103808
 ocpdr.__streams_pool_size=0
 *.audit_file_dest='/u01/app/oracle/admin/ocpdr/adump'
 *.audit_trail='db'
 *.compatible='12.1.0.0.0'
*.control_files='/u01/app/oracle/oradata/ocpdr/control01.ctl','/u01/app/oracle/fast_recovery_area/ocpdr/control02.ctl'
 *.db_block_size=8192
 *.db_domain=''
 *.db_name='ocppr'
 *.db_unique_name='ocpdr'
 *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
 *.db_recovery_file_dest_size=4800m
 *.fal_client='OCPDR'
 *.fal_server='OCPPR'
 *.diagnostic_dest='/u01/app/oracle'
 *.dispatchers='(PROTOCOL=TCP) (SERVICE=ocpprXDB)'
 *.memory_target=1520m
 *.db_file_name_convert='/u01/app/oracle/oradata/ocppr','/u01/app/oracle/oradata/ocpdr'
 *.log_file_name_convert='/u01/app/oracle/oradata/ocppr/','/u01/app/oracle/oradata/ocpdr/'
 *.log_archive_config='dg_config=(ocppr,ocpdr)'
 *.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=ocpdr'
 *.log_archive_dest_2='service=ocppr valid_for=(all_logfiles,primary_role) db_unique_name=ocppr'
 *.open_cursors=300
 *.processes=300
 *.remote_login_passwordfile='EXCLUSIVE'
 *.standby_file_management='AUTO'
 *.undo_tablespace='UNDOTBS1'

Step-16. Now start standby instance using pfile at nomount stage.

$export ORACLE_SID=ocpdr

$sqlplus sys/Oracle12c@ocpdr as sysdba

SQL> startup nomount pfile=/u01/initocpdr.ora;

Start Duplicating Database using RMAN

Step-17. Now connect with RMAN with target & auxiliary instance using the following command.

You can do this on Primary or Standby it’s your choice.

$ rman target sys/Oracle12c@ocppr auxiliary sys/Oracle12c@ocpdr

Now run the following duplicate command, that command will start copying all databases on the standby server.

Duplicate target database for standby from active database dorecover nofilenamecheck;

A brief explanation of the command which I used in the above statement.

• FOR STANDBY: confirm the DUPLICATE command is used for standby, so it will not force to change DBID.
• FROM ACTIVE DATABASE: The DUPLICATE will be copied directly from the primary datafiles, without any external backup.
• DORECOVER: The DUPLICATE is to recover the standby up to the current point in time.
• NOFILENAMECHECK: On standby, file locations are not checked.

For more details watch below videos

Part -1 Oracle 12c DataGuard Configuration

Part -2 Configure DataGuard

Part -3 Last steps to configure Dataguard

Read - How to restore archive logs from RMAN backup

Connect with me on:

Instagram: https://www.instagram.com/digitalshripal
Linkedin: https://www.linkedin.com/in/shripal-singh
Twitter: https://twitter.com/ocptechnology
Facebook: https://www.facebook.com/ocptechnology
YouTube: https://www.youtube.com/ocptechnology