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 prim
192.168.0.61 stand.oracle.com stand 

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  prim
 192.168.0.61   stand.oracle.com  stand

Disable the firewall of both primary and standby machine.

[root@prim ~]# systemctl stop firewalld
[root@prim ~]# systemctl disable firewalld

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;
SELECT group#, type, member FROM v$logfile WHERE type = 'STANDBY' order by group#;

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' scope=spfile;

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 for CDB and PDB datafile also.

mkdir -p /data/oracle/app/admin/stand/adump
mkdir -p /data/oracle/app/oradata/stand/
mkdir -p /data/oracle/app/fast_recovery_area/stand/
mkdir -p /data/oracle/app/oradata/stand/pdbseed/
mkdir -p /data/oracle/app/oradata/stand/pdbprim/

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=285212672
stand.__java_pool_size=4194304
stand.__large_pool_size=8388608
stand.__oracle_base='/data/oracle/app/oracle'#ORACLE_BASE set from environment
stand.__pga_aggregate_target=268435456
stand.__sga_target=499122176
stand.__shared_io_pool_size=16777216
stand.__shared_pool_size=176160768
stand.__streams_pool_size=0
*.audit_file_dest='/data/oracle/app/oracle/admin/stand/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/data/oracle/app/oracle/oradata/stand/control01.ctl','/data/oracle/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/oracle/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4800m
*.fal_client='stand'
*.fal_server='prim'
*.diagnostic_dest='/data/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)'
*.db_file_name_convert='/data/oracle/app/oracle/oradata/prim','/data/oracle/app/oracle/oradata/stand'
*.log_file_name_convert='/data/oracle/app/oracle/oradata/prim/','/data/oracle/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'
enable_pluggable_database=true

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

Recommended For You

ankushthavali

About the Author: ankushthavali

Leave a Reply

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