After data guard configuration in oracle database version 19.22, I realized that redo logs were not being shipped to the standby database and the RFS process was also not running on the standby. The primary database alert logs had the following error:
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
2024-12-10T12:39:45.832794+00:00
TT00 (PID:4116841): Error 12514 received logging on to the standby
2024-12-10T12:40:46.231956+00:00SOLUTION
On the Primary, I checked the maximum sequence.
SQL> select MAX(SEQUENCE#),THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
668 1On the Standby, I checked the maximum sequence.
SQL> select MAX(SEQUENCE#),THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;
no rows selected
On the Standby, I checked the processes running. As seen in the output, the RFS process was not running.
SQL> select process, status, sequence#, block# from v$managed_standby order by 1;
PROCESS STATUS SEQUENCE# BLOCK#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
DGRD ALLOCATED 0 0
DGRD ALLOCATED 0 0
MRP0 WAIT_FOR_LOG 647 0
7 rows selected.A check of the listener on the Standby database indicated that the listener was started but did not support any services.
$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 10-DEC-2024 13:42:39
Copyright (c) 1991, 2023, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db-dr.tudis.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 10-DEC-2024 13:41:19
Uptime 0 days 1 hr. 1 min. 20 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/db-dr/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-dr.tudis.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfullyOn the Standby, I checked the ‘local_listener’ parameter.
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string LISTENER_DBDROn the Standby, I checked what local_listener value, ‘LISTENER_DBDR’ points to in the tnsnames.ora file.
$ cd $ORACLE_HOME/network/admin
[oracle@ofsaa-db-dr admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DBDC=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db-dc.tudis.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DBDC)
)
)
DBDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db-dr.tudis.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DBDR)
)
)
LISTENER_DBDC =
(ADDRESS = (PROTOCOL = TCP)(HOST = db-dc.tudis.com)(PORT = 1521))The mistake was that, the listener entry in the tnsnames.ora file still pointed to the Primary database as seen below.
LISTENER_DBDC =
(ADDRESS = (PROTOCOL = TCP)(HOST = db-dc.tudis.com)(PORT = 1521))This was corrected to read:
LISTENER_DBDR =
(ADDRESS = (PROTOCOL = TCP)(HOST = db-dr.tudis.com)(PORT = 1521))The entry now points to the Standby database.
I did a restart of the Standby database.
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.
Total System Global Area 7.5162E+10 bytes
Fixed Size 32614032 bytes
Variable Size 8858370048 bytes
Database Buffers 6.6035E+10 bytes
Redo Buffers 235819008 bytes
Database mounted.I checked the status of the listener on the Standby.
$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 10-DEC-2024 13:52:40
Copyright (c) 1991, 2023, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db-dr.tudis.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 10-DEC-2024 13:41:19
Uptime 0 days 1 hr. 11 min. 20 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/db-dr/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-dr.tudis.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "DBDR" has 1 instance(s).
Instance "DBDR", status READY, has 1 handler(s) for this service...
The command completed successfullyNow ,I confirmed the RFS process was running on the Standby database.
SQL> select process, status, sequence#, block# from v$managed_standby order by 1;
PROCESS STATUS SEQUENCE# BLOCK#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CLOSING 670 583680
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
DGRD ALLOCATED 0 0
DGRD ALLOCATED 0 0
RFS RECEIVING 649 770049
RFS RECEIVING 648 790529
RFS IDLE 671 6075
RFS IDLE 0 0
RFS RECEIVING 650 675841
11 rows selected.
SQL> select MAX(SEQUENCE#),THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
670 1Logs are now being shipped to the Standby and error 12514 has stopped appearing in the alert logs of the Primary database.