Oracle 12C dataguard installation using Active Duplicate Method

In this blog , we will learn about the oracle 12c dataguard installation using active duplicate method.

We are using oracle enterprise linux as database server

Primary Standby
Hostnameprim.oracle.comstand.oracle.com
IP192.168.0.60192.168.0.61
DB Unique NamePRIMSTAND

We are assuming that primary database is ready with database installation and we have copied the vmware files to create standby database.

  1. Change the IP address of standby machine using neat command.
  2. Edit the hosts file for both primary and standby server. Add the ip ,hostname entry in hosts file.

Primary Machine hosts file will be like this

 [oracle@prim ~]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 prim.oracle.com prim localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
192.168.0.60 prim.oracle.com oracle
192.168.0.61 stand.oracle.com oracle 

Standby Machine hosts file will be like this.

[oracle@stand ~]$ cat /etc/hosts
 Do not remove the following line, or various programs
 that require network functionality will fail.
 127.0.0.1               stand.oracle.com prim localhost.localdomain localhost
 ::1             localhost6.localdomain6 localhost6
 192.168.0.60   prim.oracle.com  oracle
 192.168.0.61   stand.oracle.com  oracle

Primary Side Configuration

Put the database in archive log mode.

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

SQL> alter database force logging;

At least one log file available

SQL> alter system switch logfile;

 create standby redo logs for switchovers and should be adding one extra.

alter database add standby logfile ‘/data/app/oracle/oradata/cdb/redo04.log’ size 50m;
alter database add standby logfile ‘/data/app/oracle/oradata/cdb/redo05.log’ size 50m;
alter database add standby logfile ‘/data/app/oracle/oradata/cdb/redo06.log’ size 50m;
alter database add standby logfile ‘/data/app/oracle/oradata/cdb/redo07.log’ size 50m;

Check DB_NAME & DB_UNIQUE_NAME

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


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

set remote archivelog destination for standby & local is in flash_recovery_area.

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

The STANDBY_FILE_MANAGEMENT parameter must be set AUTO.

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Listener configuration on primary and standby

Add the entry for both the unique id.

[oracle@prim ~]$ cat /data/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
 listener.ora Network Configuration File: /data/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
 Generated by Oracle configuration tools.
 SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (GLOBAL_DBNAME = cdb)
       (ORACLE_HOME = /data/app/oracle/product/12.1.0.2/db_1)
       (SID_NAME = cdb)
     )
     (SID_DESC =
       (GLOBAL_DBNAME = stand)
       (ORACLE_HOME = /data/app/oracle/product/12.1.0.2/db_1)
       (SID_NAME = stand)
     )
   )
 LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1521))
     )
   )

TNSNAMES.ORA file configuration for both the server.

[oracle@prim ~]$ cat /data/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora
 tnsnames.ora Network Configuration File: /data/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora
 Generated by Oracle configuration tools.
 LISTENER_CDB =
   (ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1521))
 PDBCDB =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = pdbcdb)
     )
   )
 STAND =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = stand.oracle.com)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = stand)
     )
   )
 PRIM =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = prim)
     )
   )
 CDB =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = cdb)
     )
   )

Stop and Start the listener

lsnrctl stop

lsnrctl start

Crosscheck with tnsping for both stand and primary database.

[oracle@prim ~]$ tnsping stand
 TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 29-DEC-2019 23:48:54
 Copyright (c) 1997, 2014, Oracle.  All rights reserved.
 Used parameter files:
 /data/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
 Used TNSNAMES adapter to resolve the alias
 Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = stand.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stand)))
 OK (0 msec)
 [oracle@prim ~]$
 [oracle@prim ~]$
 [oracle@prim ~]$ tnsping prim
 TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 29-DEC-2019 23:49:05
 Copyright (c) 1997, 2014, Oracle.  All rights reserved.
 Used parameter files:
 /data/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
 Used TNSNAMES adapter to resolve the alias
 Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prim)))
 OK (100 msec)

Set the log_archive_config parameter

SQL> alter system set log_archive_config=’dg_config=(prim,stand)’;

set remote_login_passwordfile exclusive.

SQL> alter system set remote_login_passwordfile=’EXCLUSIVE’;

Update the fal_server and fal_client

SQL> alter system set fal_server=’stand’;
SQL> alter system set fal_client=’prim’;

Create pfile from spfile for the standby database

SQL> create pfile from spfile

Move the PFILE,Password file,listener.ora,tnsnames.ora file to standby. Change the name of files as per standby database.

scp initcdb.ora oracle@192.168.0.61:$ORACLE_HOME/dbs/initstand.ora

scp orapwcdb oracle@192.168.0.61:$ORACLE_HOME/dbs/orapwstand

scp /data/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora oracle@192.168.0.61: /data/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora

scp /data/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora oracle@192.168.0.61: /data/app/oracle/product/12.1.0.2/db_1/network/admin/ listener.ora

Standby Configuration

Create directory on standby

mkdir /data/app/oracle/admin/stand/adump
mkdir /data/app/oracle/oradata/prim/
mkdir /data/app/oracle/fast_recovery_area/prim/

Do changes in standby pfile and add following two parameter as log_file_name_convert and db_file_name_convert

PFILE on standby should be look like this.

stand__data_transfer_cache_size=0
  stand__db_cache_size=620756992
  stand__java_pool_size=16777216
  stand__large_pool_size=33554432
  stand__oracle_base='/data/app/oracle'#ORACLE_BASE set from environment
  stand__pga_aggregate_target=637534208
  stand__sga_target=956301312
  stand__shared_io_pool_size=50331648
  stand__shared_pool_size=218103808
  stand__streams_pool_size=0
  *.audit_file_dest='/data/app/oracle/admin/stand/adump'
  *.audit_trail='db'
  *.compatible='12.1.0.0.0'
 *.control_files='/data/app/oracle/oradata/stand/control01.ctl','/data/app/oracle/fast_recovery_area/stand/control02.ctl'
  *.db_block_size=8192
  *.db_domain=''
  *.db_name='prim'
  *.db_unique_name='stand'
  *.db_recovery_file_dest='/data/app/oracle/fast_recovery_area'
  *.db_recovery_file_dest_size=4800m
  *.fal_client='stand'
  *.fal_server='prim'
  *.diagnostic_dest='/data/app/oracle'
  *.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)'
  *.memory_target=1520m
  *.db_file_name_convert='/data/app/oracle/oradata/prim','/data/app/oracle/oradata/stand'
  *.log_file_name_convert='/data/app/oracle/oradata/prim/','/data/app/oracle/oradata/stand/'
  *.log_archive_config='dg_config=(prim,stand)'
  *.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=stand'
  *.log_archive_dest_2='service=prim valid_for=(all_logfiles,primary_role) db_unique_name=prim'
  *.open_cursors=300
  *.processes=300
  *.remote_login_passwordfile='EXCLUSIVE'
  *.standby_file_management='AUTO'
  *.undo_tablespace='UNDOTBS1'

Start the database in nomount stage using pfile

SQL > startup nomount pfile=’initSTAND.ORA’

connect with rman with target & auxiliary instance using the following command.

rman target sys/Oracle123@cdb auxiliary sys/Oracle123@stand

Run the following duplicate command, that command will start copying all database on the standby server.

Duplicate target database for standby from active database dorecover nofilenamecheck;

Dataguard important Queries

Check the database status on primary and standby

select status,instance_name,database_role,protection_mode from v$database,v$instance;

Start the MRP process on standby database

alter database recover managed standby database disconnect from session;

Status of the MRP process

select process,status,client_process,thread#,sequence#,block#,blocks,delay_mins from v$managed_standby;

Check if both the primary and standby is in sync

SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

#dataguard #oracle #oracledataguard #ankushthavali

Share and Enjoy !

0Shares
0 0 0

Leave a Comment

Your email address will not be published. Required fields are marked *