Categories
Data Guard with Broker
The above is my DG configuration.
I setup the DG according the step-by-step instruction from Oracle Base
After changing role of cdb1_stby with command:
CONVERT DATABASE cdb1_stby TO SNAPSHOT STANDBY;
I could not log into pdb1 database using alias:
conn hr/hr@pdb1 but I could connect using alter session set container=pdb1;
How to connect using alias from tnsnames?
More information. This is lsnrctl status on primary:
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 30-MAR-2021 03:34:27
Uptime 0 days 0 hr. 58 min. 37 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/oracle/product/19.3/dbhome_1/network/admin/listener.ora
Listener Log File /opt/app/oracle/diag/tnslsnr/primary/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primary.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=primary.localdomain)(PORT=5500))(Security=(my_wallet_directory=/opt/app/oracle/admin/cdb1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "86b637b62fdf7a65e053f706e80a27ca.localdomain" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "bd1eb82e330f2bb3e0536001a8c04352.localdomain" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1" has 1 instance(s).
Instance "cdb1", status UNKNOWN, has 1 handler(s) for this service...
Service "cdb1.localdomain" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1XDB.localdomain" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1_CFG.localdomain" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1_DGMGRL" has 1 instance(s).
Instance "cdb1", status UNKNOWN, has 1 handler(s) for this service...
Service "cdb1_stby" has 1 instance(s).
Instance "cdb1", status UNKNOWN, has 1 handler(s) for this service...
Service "pdb1.localdomain" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
The command completed successfully
-----------------------------------------------------
This is content of tnsnames.ora file:
# tnsnames.ora Network Configuration File: /opt/app/oracle/product/19.3/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
cdb1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SID = cdb1)
)
)
cdb1_stby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SID = cdb1)
)
)
PDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb1.localdomain)
)
)
-----------------------------------------
After issuing command:
DGMGRL> CONVERT DATABASE cdb1_stby TO SNAPSHOT STANDBY;
Converting database "cdb1_stby" to a Snapshot Standby database, please wait...
Database "cdb1_stby" converted successfully
DGMGRL> show configuration
Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
cdb1 - Primary database
cdb1_stby - Snapshot standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 29 seconds ago)
-------------------------------------------------------
On standby database:
[oracle@standby ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 30 10:40:45 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL>
-----------------------------------------
Conecting via alias:
SQL> conn hr/hr@pdb1
ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect
descriptor
Warning: You are no longer connected to ORACLE.
------------------------
lsnrctl status:
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 30-MAR-2021 10:34:00
Uptime 0 days 0 hr. 8 min. 32 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/oracle/product/19.3/dbhome_1/network/admin/listener.ora
Listener Log File /opt/app/oracle/diag/tnslsnr/standby/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "cdb1" has 1 instance(s).
Instance "cdb1", status UNKNOWN, has 1 handler(s) for this service...
Service "cdb1_stby" has 1 instance(s).
Instance "cdb1", status UNKNOWN, has 1 handler(s) for this service...
Service "cdb1_stby_DGMGRL" has 1 instance(s).
Instance "cdb1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
-----------------------
Content of tnsnames on standby:
# tnsnames.ora Network Configuration File: /opt/app/oracle/product/19.3/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
CDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = CDB1)
)
)
PDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SID = pdb1)
)
)
CDB1_STBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SID = cdb1)
)
)
-------------------------------------
How to connect to pdb1 via alias?