DBA Hub

📋Steps in this guide1/1

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

oracle configurationintermediate
by OracleDba
16 views
1

Overview

Oracle 19C Dataguard installation using Active Duplicate Method 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. Disable the firewall of both primary and standby machine. Primary Side Configuration Put the database in archive log mode. Crosscheck with tnsping for both stand and primary database.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
[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

[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

[root@prim ~]# systemctl stop firewalld

[root@prim ~]# systemctl disable firewalld

SQL> SELECT log_mode FROM v$database;  
LOG_MODE 
------------ 
NOARCHIVELOG  
SQL> SHUTDOWN IMMEDIATE; 
SQL> STARTUP MOUNT; 
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

SQL> alter database force logging;

SQL> alter system switch 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

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

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

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

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))
		)
	)

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)
    )
  )

lsnrctl stop 

lsnrctl start

[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)

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

SQL> alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;

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

SQL> create pfile from spfile;

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

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/

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

SQL> startup nomount pfile='initstand.ora'

rman target sys/oracle@prim auxiliary sys/oracle@stand

Duplicate target database for standby from active database dorecover nofilenamecheck;

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

alter database recover managed standby database disconnect from session;

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

SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!