Following my previous post, here is some setup information :
Relevant database instance parameter(s) for the Primary database :
Relevant database instance parameter(s) for the Standby database :
The listener.ora and tnsnames.ora entries on the Primary server :
The listener.ora and tnsnames.ora entries on the Standby server :
Database listener and instance startup commands on the Standby :
Once the Standby database instance is started I can see entries in the *Standby* database instance alert log file which show that backlog of archivelogs (43 to 46) that were generated in the Primary database instance but hadn't been applied yet to the Standby (the Standby was shutdown while the Primary was still active):
Subsequently as redo generation continues on the Primary, the Standby starts showing that it waits for archive logs, applies redo and even does datafile resizes:
Log Group#4 is actually the Standby Redo Log :
I can monitor the Standby with this query :
At my first execution of this query (at 23:32:25), Sequence#50 is the CURRENT Redo Log file in the Primary database. V$MANAGED_STANDBY on the Standby shows two entries but the active one is the one where it shows that the CLIENT_PROGRAM is the LGWR (Log Writer) on the Primary that is shipping Redo to the Standby.
As transactions occur on the Primary, you can see that the current BLOCK# has also changed for Sequence#50.
When the Primary forces an Archive and Log switch to #51, V$MANAGED_STANDBY now reflects #51 as the redo sequence that is being applied. Subsequently, the current BLOCK# changes as transactions occur on the Primary.
Thus, this monitoring does show that the Standby is receiving and applying Redo without waiting for actual Archival of the Redo Log file from the Primary.
Relevant database instance parameter(s) for the Primary database :
*.local_listener='LISTENER_ORCLCDB' *.log_archive_dest_1='LOCATION=/opt/oracle/archivelog/ORCLCDB' *.log_archive_dest_2='SERVICE=STDBYDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBYDB' *.remote_login_passwordfile='EXCLUSIVE'
Relevant database instance parameter(s) for the Standby database :
*.audit_file_dest='/opt/oracle/admin/STDBYDB/adump' *.control_files='/opt/oracle/oradata/STDBYDB/control01.ctl','/opt/oracle/oradata/STDBYDB/control02.ctl' *.db_file_name_convert='/opt/oracle/oradata/ORCLCDB','/opt/oracle/oradata/STDBYDB' *.db_name='ORCLCDB' *.db_unique_name='STDBYDB' *.dispatchers='(PROTOCOL=TCP) (SERVICE=STDBYDBXDB)' *.fal_server='ORCLCDB' *.local_listener='LISTENER_STDBYDB' *.log_archive_dest_1='LOCATION=/opt/oracle/archivelog/STDBYDB' *.log_archive_dest_2='SERVICE=ORCLCDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLCDB' *.log_file_name_convert='/opt/oracle/oradata/ORCLCDB','/opt/oracle/oradata/STDBYDB' *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO'
The listener.ora and tnsnames.ora entries on the Primary server :
{listener}
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))
)
)
{tnsnames}
LISTENER_ORCLCDB =
(ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))
STDBYDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STDBYDB)
)
)
The listener.ora and tnsnames.ora entries on the Standby server :
{static listener entry}
LISTENER_STDBYDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1522))
)
)
SID_LIST_LISTENER_STDBYDB =
(SID_LIST=
(SID_DESC =
(ORACLE_HOME = /opt/oracle/product/19c/dbhome_1)
(SID_NAME = STDBYDB)
)
)
{tnsnames}
LISTENER_STDBYDB =
(ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1522))
ORCLCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLCDB)
)
)
Database listener and instance startup commands on the Standby :
STDBYDB_server>lsnrctl start listener_stdbydb LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-MAR-2020 23:05:43 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/oracle-19c-vagrant/listener_stdbydb/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1522))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1522))) STATUS of the LISTENER ------------------------ Alias listener_stdbydb Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 15-MAR-2020 23:05:43 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/oracle-19c-vagrant/listener_stdbydb/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=x.x.x.x)(PORT=1522))) Services Summary... Service "STDBYDB" has 1 instance(s). Instance "STDBYDB", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully STDBYDB_server> STDBYDB_server>sqlplus '/ as sysdba' SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 15 23:06:07 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 1207955552 bytes Fixed Size 9134176 bytes Variable Size 436207616 bytes Database Buffers 754974720 bytes Redo Buffers 7639040 bytes Database mounted. SQL> alter database recover managed standby database disconnect from session; Database altered. SQL>
Once the Standby database instance is started I can see entries in the *Standby* database instance alert log file which show that backlog of archivelogs (43 to 46) that were generated in the Primary database instance but hadn't been applied yet to the Standby (the Standby was shutdown while the Primary was still active):
Completed: ALTER DATABASE MOUNT 2020-03-15T23:06:22.664060+08:00 rfs (PID:6164): Primary database is in MAXIMUM PERFORMANCE mode 2020-03-15T23:06:22.756031+08:00 rfs (PID:6164): Selected LNO:4 for T-1.S-47 dbid 2778483057 branch 1007421686 2020-03-15T23:06:23.159102+08:00 rfs (PID:6168): Opened log for T-1.S-45 dbid 2778483057 branch 1007421686 2020-03-15T23:06:23.176308+08:00 rfs (PID:6166): Opened log for T-1.S-43 dbid 2778483057 branch 1007421686 2020-03-15T23:06:23.201644+08:00 rfs (PID:6170): Opened log for T-1.S-44 dbid 2778483057 branch 1007421686 2020-03-15T23:06:23.266812+08:00 rfs (PID:6168): Archived Log entry 3 added for B-1007421686.T-1.S-45 ID 0xa59c8470 LAD:2 2020-03-15T23:06:23.342737+08:00 rfs (PID:6166): Archived Log entry 4 added for B-1007421686.T-1.S-43 ID 0xa59c8470 LAD:2 2020-03-15T23:06:23.353286+08:00 rfs (PID:6170): Archived Log entry 5 added for B-1007421686.T-1.S-44 ID 0xa59c8470 LAD:2 2020-03-15T23:06:23.402195+08:00 rfs (PID:6168): Opened log for T-1.S-46 dbid 2778483057 branch 1007421686 2020-03-15T23:06:23.451732+08:00 rfs (PID:6168): Archived Log entry 6 added for B-1007421686.T-1.S-46 ID 0xa59c8470 LAD:2 2020-03-15T23:06:30.118056+08:00 alter database recover managed standby database disconnect from session 2020-03-15T23:06:30.124297+08:00 Attempt to start background Managed Standby Recovery process (STDBYDB) Starting background process MRP0 2020-03-15T23:06:30.138764+08:00 MRP0 started with pid=49, OS id=6178 2020-03-15T23:06:30.139465+08:00 Background Managed Standby Recovery process started (STDBYDB) 2020-03-15T23:06:35.172532+08:00 Started logmerger process 2020-03-15T23:06:35.184395+08:00 PR00 (PID:6184): Managed Standby Recovery starting Real Time Apply max_pdb is 3 2020-03-15T23:06:35.518115+08:00 Parallel Media Recovery started with 2 slaves 2020-03-15T23:06:35.563095+08:00 stopping change tracking 2020-03-15T23:06:35.733514+08:00 PR00 (PID:6184): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_43_1007421686.dbf 2020-03-15T23:06:36.129942+08:00 PR00 (PID:6184): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_44_1007421686.dbf 2020-03-15T23:06:36.142908+08:00 Completed: alter database recover managed standby database disconnect from session 2020-03-15T23:06:39.365000+08:00 PR00 (PID:6184): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_45_1007421686.dbf 2020-03-15T23:06:40.241700+08:00 PR00 (PID:6184): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_46_1007421686.dbf 2020-03-15T23:06:40.981414+08:00
Subsequently as redo generation continues on the Primary, the Standby starts showing that it waits for archive logs, applies redo and even does datafile resizes:
PR00 (PID:6184): Media Recovery Waiting for T-1.S-47 (in transit) 2020-03-15T23:06:40.997356+08:00 Recovery of Online Redo Log: Thread 1 Group 4 Seq 47 Reading mem 0 Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log 2020-03-15T23:12:52.195417+08:00 Resize operation completed for file# 1, old size 931840K, new size 942080K 2020-03-15T23:13:08.231444+08:00 rfs (PID:6572): Primary database is in MAXIMUM PERFORMANCE mode rfs (PID:6572): Re-archiving LNO:4 T-1.S-47 2020-03-15T23:13:08.489447+08:00 PR00 (PID:6184): Media Recovery Waiting for T-1.S-48 2020-03-15T23:13:08.495944+08:00 rfs (PID:6572): No SRLs available for T-1 2020-03-15T23:13:08.515405+08:00 rfs (PID:6572): Opened log for T-1.S-48 dbid 2778483057 branch 1007421686 2020-03-15T23:13:08.516367+08:00 ARC2 (PID:6141): Archived Log entry 7 added for T-1.S-47 ID 0xa59c8470 LAD:1 2020-03-15T23:19:13.700490+08:00 rfs (PID:6572): Archived Log entry 8 added for B-1007421686.T-1.S-48 ID 0xa59c8470 LAD:2 2020-03-15T23:19:13.769405+08:00 rfs (PID:6572): Selected LNO:4 for T-1.S-49 dbid 2778483057 branch 1007421686 2020-03-15T23:19:14.445032+08:00 PR00 (PID:6184): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_48_1007421686.dbf PR00 (PID:6184): Media Recovery Waiting for T-1.S-49 (in transit) 2020-03-15T23:19:14.947878+08:00 Recovery of Online Redo Log: Thread 1 Group 4 Seq 49 Reading mem 0 Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
Log Group#4 is actually the Standby Redo Log :
{at the Primary}
SQL> select l.group#, f.member
2 from v$standby_log l, v$logfile f
3 where l.group#=f.group#
4 /
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
4
/opt/oracle/oradata/ORCLCDB/stdbredo01.log
SQL>
{at the Standby}
SQL> select l.group#, f.member
2 from v$standby_log l, v$logfile f
3 where l.group#=f.group#
4 /
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
4
/opt/oracle/oradata/STDBYDB/stdbredo01.log
SQL>
I can monitor the Standby with this query :
23:32:25 SQL> l
1 select thread#, sequence#, group#, client_process, block#, blocks, delay_mins
2 from v$managed_standby
3 where thread#=1
4 and sequence# is not null
5 and sequence# != 0
6* order by 1,2
23:32:25 SQL> /
THREAD# SEQUENCE# GROUP# CLIENT_P BLOCK# BLOCKS DELAY_MINS
---------- ---------- ------ -------- ---------- ---------- ----------
1 47 4 ARCH 26624 945 0
1 49 4 ARCH 139264 656 0
1 50 N/A N/A 0 0 0
1 50 2 LGWR 86 1 0
23:32:26 SQL>
23:32:55 SQL> /
THREAD# SEQUENCE# GROUP# CLIENT_P BLOCK# BLOCKS DELAY_MINS
---------- ---------- ------ -------- ---------- ---------- ----------
1 47 4 ARCH 26624 945 0
1 49 4 ARCH 139264 656 0
1 50 N/A N/A 0 0 0
1 50 2 LGWR 65490 3 0
23:32:56 SQL>
23:33:19 SQL> /
THREAD# SEQUENCE# GROUP# CLIENT_P BLOCK# BLOCKS DELAY_MINS
---------- ---------- ------ -------- ---------- ---------- ----------
1 47 4 ARCH 26624 945 0
1 49 4 ARCH 139264 656 0
1 50 N/A N/A 0 0 0
1 50 2 LGWR 133538 1 0
23:33:19 SQL>
23:34:00 SQL> /
THREAD# SEQUENCE# GROUP# CLIENT_P BLOCK# BLOCKS DELAY_MINS
---------- ---------- ------ -------- ---------- ---------- ----------
1 47 4 ARCH 26624 945 0
1 49 4 ARCH 139264 656 0
1 51 N/A N/A 9 409600 0
1 51 3 LGWR 9 1 0
23:34:01 SQL>
23:38:03 SQL> /
THREAD# SEQUENCE# GROUP# CLIENT_P BLOCK# BLOCKS DELAY_MINS
---------- ---------- ------ -------- ---------- ---------- ----------
1 47 4 ARCH 26624 945 0
1 49 4 ARCH 139264 656 0
1 51 N/A N/A 66201 409600 0
1 51 3 LGWR 66201 1 0
23:38:04 SQL>
At my first execution of this query (at 23:32:25), Sequence#50 is the CURRENT Redo Log file in the Primary database. V$MANAGED_STANDBY on the Standby shows two entries but the active one is the one where it shows that the CLIENT_PROGRAM is the LGWR (Log Writer) on the Primary that is shipping Redo to the Standby.
As transactions occur on the Primary, you can see that the current BLOCK# has also changed for Sequence#50.
When the Primary forces an Archive and Log switch to #51, V$MANAGED_STANDBY now reflects #51 as the redo sequence that is being applied. Subsequently, the current BLOCK# changes as transactions occur on the Primary.
Thus, this monitoring does show that the Standby is receiving and applying Redo without waiting for actual Archival of the Redo Log file from the Primary.
No comments:
Post a Comment