In my previous demos, I've shown 1 Standby (STDBYDB) for the Primary (ORCLCDB).
However, an organisation may choose to have additional Standby Databases.
Note that each Standby Database must be licensed. If the Primary is licensed by Processor count (say 32 processors), each Standby must also be licensed by Processor count, although the Standbys may be running on servers with fewer processors, thus needing smaller licensing than the Primary.
Why would an organisation have additional Standbys ? Multiple reasons
1. Having additional Disaster Recovery Data Centres. Thus, with 2 DRCs in addition to the Production, there may be 2 Standby Databases.
2. Choosing to have a Standby that can be used to run Reporting Queries when it is OPENed READ ONLY for, say, 4hours a day. [If the organisation has the Active Dataguard Licence, the Standby can be running Reporting Queries all 24hours] Without the Active Dataguard Licence, Recovery has to be stopped at this Standby for those 4hours, though it will continue to receive and Archive Redo from the Primary. Once the 4hour window is closed, Recovery can resumed and the Standby will apply all the "pending" Redo [from the ArchiveLogs that it has received] to "catch-up" to the Primary
3. To test D.R. scenarios, whereby a secondary Standby is opened for Read-Write operations, without impacting the actual Production and first Standby instance which continue to be in-sync throughout the D.R. testing. The Standby may be opened as a Snapshot Standby
4. To have an environment that can be opened day-time hours for UAT while it is resynced (i.e. Redo Apply done) with the Primary every night. This will require the Standby to have Flashback Restore Points created and reverted to each day.
5. To have a Standby that is lagging the Primary by, say, 30minutes or 1hour or 4hours deliberately. This allows the organisation a database that can be quickly opened for data recovery in case someone makes a mistake and deletes data or drops tables/objects from the Primary and the delete/drop has already been replicated to the first Standby.
6. To run [additional] Database Backups at a Standby DRC. I prefer that the Primary and each Standby be running local database backups.
7. To migrate the Production Primary to a new Data Centre via a Standby built and refreshed over a week and then a Switchover executed during a weekend downtime.
8. To migrate the Production Primary to new Server Hardware via a Standby built and refreshed over a week and then a Switchover executed during a weekend downtime.
I have built my Second Standby with these parameters :
initSTDB2.ora :
*.audit_file_dest='/opt/oracle/admin/STDB2/adump'
*.audit_sys_operations=false
*.audit_trail='none'
*.commit_logging='batch'
*.commit_wait='nowait'
*.compatible='19.0.0'
*.control_files='/opt/oracle/oradata/STDB2/control01.ctl','/opt/oracle/oradata/STDB2/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/opt/oracle/oradata'
*.db_file_name_convert='/opt/oracle/oradata/ORCLCDB','/opt/oracle/oradata/STDB2'
*.db_name='ORCLCDB'
*.db_recovery_file_dest_size=10G
*.db_recovery_file_dest='/opt/oracle/FRA/STDB2'
*.db_unique_name='STDB2'
*.diagnostic_dest='/opt/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=STDB2XDB)'
*.enable_pluggable_database=true
*.fal_server='ORCLCDB'
*.filesystemio_options='setall'
*.local_listener='LISTENER_STDB2'
*.log_archive_config='DG_CONFIG=(ORCLCDB, STDBYDB, STDB2)'
*.log_archive_dest_1='LOCATION=/opt/oracle/archivelog/STDB2'
###*.log_archive_dest_2='SERVICE=ORCLCDB SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLCDB'
*.log_file_name_convert='/opt/oracle/oradata/ORCLCDB','/opt/oracle/oradata/STDB2'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=384m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1152m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
and listener.ora :
LISTENER_STDB2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1532))
)
)
SID_LIST_LISTENER_STDB2 =
(SID_LIST=
(SID_DESC =
(ORACLE_HOME = /opt/oracle/product/19c/dbhome_1)
(SID_NAME = STDB2)
)
)
and the password file copied from the Primary -- although Oracle will copy the password file in recent versions, I still prefer to do it myself
oracle19c>pwd
/opt/oracle/product/19c/dbhome_1/dbs
oracle19c>ls orapw$ORACLE_SID
orapwSTDB2
oracle19c>
Note how I have disabled log_archive_dest_2. In this case, this Standby will never be part of a Role Reversal done either by Switchover or Failover [where it could become a Primary in the D.R. Data Centre and ship Redo to the old Primary in the Production Data Centre]
oracle19c>lsnrctl start listener_STDB2 LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-JUL-2021 22:07:14 Copyright (c) 1991, 2019, Oracle. All rights reserved. Starting /opt/oracle/product/19c/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 19.0.0.0.0 - Production System parameter file is /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora Log messages written to /opt/oracle/diag/tnslsnr/ora19cs1/listener_stdb2/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1532))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1532))) STATUS of the LISTENER ------------------------ Alias listener_STDB2 Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 15-JUL-2021 22:07:14 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora Listener Log File /opt/oracle/diag/tnslsnr/ora19cs1/listener_stdb2/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1532))) Services Summary... Service "STDB2" has 1 instance(s). Instance "STDB2", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully oracle19c> oracle19c>echo $ORACLE_SID STDB2 oracle19c>sqlplus '/ as sysdba' SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 15 22:21:47 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount; ORACLE instance started. Total System Global Area 1207955552 bytes Fixed Size 9134176 bytes Variable Size 318767104 bytes Database Buffers 872415232 bytes Redo Buffers 7639040 bytes SQL>
Then, at the Primary :
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(ORCLCDB, STDBYDB)
SQL> alter system set log_archive_config='DG_CONFIG=(ORCLCDB, STDBYDB, STDB2)'; -- note that this update is also done at the first Standy STDBYDB2
System altered.
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=STDBYDB SYNC AFFIRM VA
LID_FOR=(ONLINE_LOGFILES,PRIMA
RY_ROLE) DB_UNIQUE_NAME=STDBYD
B
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
SQL> show parameter log_archive_dest_3
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string
SQL> alter system set log_archive_dest_3='SERVICE=STDB2 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDB2';
System altered.
SQL>
Note that log_archive_dest_2 is set to SYNC AFFIRM but log_archive_dest_3 is set to ASYNC and [NOAFFIRM]. Because I can afford a slight lag (latency) for this Standby. Maybe it is at a Data Centre that is much further away, increasing the latency.
Then, I copy the Primary to the new Standby using RMAN (see the previous example)
-- assuming that the tnsnames.ora at the Primary is updated to include "STDB2"
oracle19c>rman target sys/manager auxiliary sys/manager@STDB2
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jul 15 22:24:00 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLCDB (DBID=2778483057)
connected to auxiliary database: ORCLCDB (not mounted)
RMAN> duplicate target database for standby from active database dorecover;
Starting Duplicate Db at 15-JUL-21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=256 device type=DISK
current log archived
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/opt/oracle/product/19c/dbhome_1/dbs/orapwSTDB2' ;
}
executing Memory Script
Starting backup at 15-JUL-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=273 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=274 device type=DISK
....
....
....
....
....
Finished Duplicate Db at 15-JUL-21
RMAN>
After that I shutdown and restart the new Standby and enable Recovery
SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 1207955552 bytes Fixed Size 9134176 bytes Variable Size 318767104 bytes Database Buffers 872415232 bytes Redo Buffers 7639040 bytes Database mounted. SQL> alter database recover managed standby database disconnect from session; Database altered. SQL>
Let me now verify that the Primary is shipping Redo to *2* Standbys
2021-07-15T22:49:55.328276+08:00 ALTER SYSTEM ARCHIVE LOG 2021-07-15T22:49:56.801388+08:00 LGWR (PID:6404): SRL selected to archive T-1.S-272 LGWR (PID:6404): SRL selected for T-1.S-272 for LAD:2 LGWR (PID:6404): SRL selected to archive T-1.S-272. However, all SRLs are currently active 2021-07-15T22:49:57.005260+08:00 Thread 1 advanced to log sequence 272 (LGWR switch) Current log# 2 seq# 272 mem# 0: /opt/oracle/oradata/ORCLCDB/redo02.log 2021-07-15T22:49:57.973514+08:00 NET (PID:28568): Archived Log entry 588 added for T-1.S-271 ID 0xa7521ccd LAD:1 2021-07-15T22:49:59.487773+08:00 TT03 (PID:8344): SRL selected for T-1.S-272 for LAD:3 2021-07-15T22:50:06.667866+08:00 ALTER SYSTEM ARCHIVE LOG 2021-07-15T22:50:07.669103+08:00 LGWR (PID:6404): SRL selected to archive T-1.S-273 LGWR (PID:6404): SRL selected for T-1.S-273 for LAD:2 LGWR (PID:6404): SRL selected to archive T-1.S-273. However, all SRLs are currently active 2021-07-15T22:50:07.862847+08:00 Thread 1 advanced to log sequence 273 (LGWR switch) Current log# 3 seq# 273 mem# 0: /opt/oracle/oradata/ORCLCDB/redo03.log 2021-07-15T22:50:08.191087+08:00 NET (PID:28568): Archived Log entry 592 added for T-1.S-272 ID 0xa7521ccd LAD:1 2021-07-15T22:52:42.492619+08:00 ALTER SYSTEM ARCHIVE LOG 2021-07-15T22:52:42.988590+08:00 LGWR (PID:6404): SRL selected to archive T-1.S-274 LGWR (PID:6404): SRL selected for T-1.S-274 for LAD:2 LGWR (PID:6404): SRL selected to archive T-1.S-274. However, all SRLs are currently active 2021-07-15T22:52:43.059342+08:00 Thread 1 advanced to log sequence 274 (LGWR switch) Current log# 1 seq# 274 mem# 0: /opt/oracle/oradata/ORCLCDB/redo01.log 2021-07-15T22:52:43.142797+08:00 NET (PID:28568): Archived Log entry 594 added for T-1.S-273 ID 0xa7521ccd LAD:1 2021-07-15T22:52:43.917398+08:00 TT03 (PID:8344): SRL selected for T-1.S-274 for LAD:3 and SQL> select protection_mode from v$database; PROTECTION_MODE -------------------- MAXIMUM AVAILABILITY SQL>
Thus, we can see that the Primary is shipping Redo to both "LAD2" and "LAD3"
While the first Standby (STDBYDB) shows :
And the new Standby (STDB2) shows :
So, now the Primary is shipping Redo to 2 Standbys. The first Standby (STDBYDB) is in MAXIMUM AVAILABILITY mode, the second Standby (STDB2) is in MAXIMUM PERFORMANCE mode.
ARC2 (PID:23577): Archived Log entry 26 added for T-1.S-272 ID 0xa7521ccd LAD:1 2021-07-15T22:50:07.448944+08:00 rfs (PID:23634): Archival of T-1.S-272 complete 2021-07-15T22:50:07.533082+08:00 PR00 (PID:23765): Media Recovery Waiting for T-1.S-273 (in transit) 2021-07-15T22:50:07.668714+08:00 rfs (PID:23634): Selected LNO:4 for T-1.S-273 dbid 2778483057 branch 1036108814 2021-07-15T22:50:09.298541+08:00 Recovery of Online Redo Log: Thread 1 Group 4 Seq 273 Reading mem 0 Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log 2021-07-15T22:52:42.921683+08:00 rfs (PID:23634): Standby controlfile consistent with primary rfs (PID:23634): No SRLs available for T-1 rfs (PID:23634): Waiting for T-1.S-273 archival to complete 2021-07-15T22:52:42.929839+08:00 ARC0 (PID:23569): Archived Log entry 27 added for T-1.S-273 ID 0xa7521ccd LAD:1 2021-07-15T22:52:42.935444+08:00 rfs (PID:23634): Archival of T-1.S-273 complete 2021-07-15T22:52:42.988242+08:00 rfs (PID:23634): Selected LNO:4 for T-1.S-274 dbid 2778483057 branch 1036108814 2021-07-15T22:52:43.001151+08:00 PR00 (PID:23765): Media Recovery Waiting for T-1.S-274 (in transit) 2021-07-15T22:52:43.002924+08:00 Recovery of Online Redo Log: Thread 1 Group 4 Seq 274 Reading mem 0 Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log and SQL> select protection_mode from v$database; PROTECTION_MODE -------------------- MAXIMUM AVAILABILITY SQL>
And the new Standby (STDB2) shows :
Recovery of Online Redo Log: Thread 1 Group 4 Seq 272 Reading mem 0 Mem# 0: /opt/oracle/oradata/STDB2/stdbredo01.log 2021-07-15T22:50:08.467719+08:00 rfs (PID:23637): No SRLs available for T-1 2021-07-15T22:50:08.530396+08:00 PR00 (PID:23655): Media Recovery Waiting for T-1.S-273 (in transit) 2021-07-15T22:50:08.532241+08:00 rfs (PID:23637): Opened log for T-1.S-273 dbid 2778483057 branch 1036108814 2021-07-15T22:50:08.532340+08:00 ARC0 (PID:23450): Archived Log entry 13 added for T-1.S-272 ID 0xa7521ccd LAD:1 2021-07-15T22:52:43.299392+08:00 rfs (PID:23637): Archived Log entry 14 added for B-1036108814.T-1.S-273 ID 0xa7521ccd LAD:3 2021-07-15T22:52:43.917098+08:00 rfs (PID:23637): Selected LNO:4 for T-1.S-274 dbid 2778483057 branch 1036108814 2021-07-15T22:52:44.137685+08:00 PR00 (PID:23655): Media Recovery Log /opt/oracle/archivelog/STDB21_273_1036108814.dbf PR00 (PID:23655): Media Recovery Waiting for T-1.S-274 (in transit) 2021-07-15T22:52:44.576855+08:00 Recovery of Online Redo Log: Thread 1 Group 4 Seq 274 Reading mem 0 Mem# 0: /opt/oracle/oradata/STDB2/stdbredo01.log and SQL> select protection_mode from v$database; PROTECTION_MODE -------------------- MAXIMUM PERFORMANCE SQL>
So, now the Primary is shipping Redo to 2 Standbys. The first Standby (STDBYDB) is in MAXIMUM AVAILABILITY mode, the second Standby (STDB2) is in MAXIMUM PERFORMANCE mode.
(For simplicity, I haven't created additional Standby Redo Logs, I have only Standby Redo Log at Primary and each of the Standbys --- it is group#4, filename "stdbredo01.log" in the listing above)
In the next blog post, I will explore how to have STDB2 deliberately lagging the Primary by 30minutes 60minutes (use case 5 in the list at the beginning of this blog post) {updated to 60minutes)
No comments:
Post a Comment