Kiran Dalvi
30 Dec, 2019
0 Comments
7 Mins Read

Oracle 19C 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
Hostname :	node1.learnomate.org	node2.learnomate.org
IP	192.168.56.50	192.168.56.60
DB Unique Name	node1	node1

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

Change the IP address of standby machine using neat command.

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@node1 ~]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.56.50 node1.learnomate.org node1
192.168.56.60 node2.learnomate.org node2

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 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.56.50 node1.learnomate.org node1
192.168.56.60 node2.learnomate.org node2

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

1	`SQL> alter database force logging;`

At least one log file available

1	`SQL> alter system` `switch` `logfile;`

Check the size of online logfile and create same size standby logfile

select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024,MEMBERS,STATUS from v$log; 
 
   GROUP#    THREAD#  SEQUENCE# BYTES/1024/1024    MEMBERS STATUS
---------- ---------- ---------- --------------- ---------- ----------------
         1          1          4             200          1 INACTIVE
         2          1          5             200          1 ACTIVE
         3          1          6             200          1 CURRENT
 
SQL> select member from v$logfile;
 
MEMBER
--------------------------------------------------------------------------------
/data/app/oracle/oradata/PRIM/redo03.log
/data/app/oracle/oradata/PRIM/redo02.log
/data/app/oracle/oradata/PRIM/redo01.log

Check the size of Online Redo log file

SQL> select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024, MEMBERS,STATUS from v$log;
 
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 MEMBERS STATUS
---------- ---------- ---------- --------------- ---------- ----------------
1 1 10 200 2 INACTIVE
2 1 11 200 2 INACTIVE
3 1 12 200 2 CURRENT

create standby redo logs for switchovers and should be adding one extra.
 
alter database add standby logfile '/data/app/oracle/oradata/PRIM/redo04.log' size 200m;
alter database add standby logfile '/data/app/oracle/oradata/PRIM/redo05.log' size 200m;
alter database add standby logfile '/data/app/oracle/oradata/PRIM/redo06.log' size 200m;
alter database add standby logfile '/data/app/oracle/oradata/PRIM/redo07.log' size 200m;

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.

1	`SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;`

Listener configuration on primary and standby

       
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
        (GLOBAL_DBNAME = prim)
        (ORACLE_HOME = /data/app/oracle/product/19C/dbhome_3)
        (SID_NAME = prim)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = stand)
    (ORACLE_HOME = /data/app/oracle/product/19C/dbhome_3)
    (SID_NAME = stand)
    )
)
 
LISTENER =
    (DESCRIPTION_LIST =
        (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = node1.learnomate.org)(PORT = 1521))
        )
    )

TNS FIle on both primary and standby

PRIM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node1.learnomate.org)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prim)
    )
  )
STAND =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node2.learnomate.org)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stand)
    )
  )

Stop and Start the listener

lsnrctl stop 
 
lsnrctl start

Crosscheck with tnsping for both stand and primary database.

[oracle@test admin]$ tnsping stand
 
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 05-SEP-2023 09:55:23
 
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
 
Used parameter files:
/data/app/oracle/product/19C/dbhome_3/network/admin/sqlnet.ora
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node2.learnomate.org)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stand)))
TNS-12541: TNS:no listener
[oracle@test admin]$
[oracle@test admin]$
[oracle@test admin]$
[oracle@test admin]$ tnsping prim
 
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 05-SEP-2023 09:55:27
 
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
 
Used parameter files:
/data/app/oracle/product/19C/dbhome_3/network/admin/sqlnet.ora
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node1.learnomate.org)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prim)))
OK (0 msec)

Set the log_archive_config parameter

1	`SQL> alter system set log_archive_config='dg_config=(prim,stand)';`

set remote_login_passwordfile exclusive.

1	`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

1	`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.

1	`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/app/oracle/oradata/STAND/pdbprim
mkdir -p /data/app/oracle/oradata/STAND/PDBPRIM2
mkdir -p /data/app/oracle/oradata/STAND/pdbseed
mkdir -p /data/app/oracle/admin/stand/adump
mkdir -p /data/app/oracle/fast_recovery_area/stand/

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

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/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/app/oracle/admin/stand/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/data/app/oracle/oradata/STAND/control01.ctl','/data/app/oracle/oradata/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)'
*.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=/data/archive 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

1	`SQL> startup nomount pfile='initstand.ora'`

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

1	`rman target sys/oracle@prim auxiliary sys/oracle@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

1	`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

1	`SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;`

#dataguard #oracle #oracledataguard #ankushthavali

Oracle 19C Dataguard installation using Active Duplicate Method

Listener configuration on primary and standby

TNS FIle on both primary and standby

Crosscheck with tnsping for both stand and primary database.

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

Join Our Free Webinar Today!

Explore Insights, Trends, and Expert Advice

About Us

Explore

Useful Links

Contact Info