Data Guard with Broker

Stanisa
Stanisa Member Posts: 1 Green Ribbon
edited March 30 in General

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/[email protected] 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:

[[email protected] ~]$ 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/[email protected]

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?