Oracle 19C Dataguard installation using Active Duplicate Method

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarANKUSH THAVALI
  • 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
 SQL> alter database force logging; 
At least one log file available
 
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.
 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
 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 orapwcdb [email protected]:$ORACLE_HOME/dbs/orapwstand 
 scp /data/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora [email protected]: /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 [email protected]: /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
 SQL> startup nomount pfile='initstand.ora' 
connect with rman with target & auxiliary instance using the following command.
 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
 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