too many TNS-01182 errors reported after creating a Standby DB

 Problem:

The following error started to come frequently in the listener's log after the creation of a standby DB for the same DB:

 TNS-01182: Listener rejected registration of service ""
 

Analysis:

I've found that the local listener parameter was already configured on the primary DB and hence after the duplication of the standby the value of LOCAL_LISTENER parameter in the Standby was pointing to the Primary server IP, the thing which keep triggering TNS-01182 errors on the Primary server side.

Solution:

Set the LOCAL_LISTENER parameter to point to the local IP of the Standby server, e.g. if the standby server IP is 10.10.10.1 then the LOCAL_LISTENER value should be set to:

SQL> alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.1)(PORT = 1521))';

Duplicate Target database for standby from active database failing with ORA-27101

Problem:

Duplicate Target database for standby from active database failing with ORA-27101

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of allocate command at 03/26/2023 14:47:54
RMAN-06403: could not obtain a fully authorized session
RMAN-04006: error from auxiliary database: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory

Solution:

Inside the listener.ora file in the Standby DB side under SID_LIST_LISTENER the GLOBAL_DBNAME & SID_NAME parameters were set to the db_unique_name value of the standby DB which is DWHSBY while it should be set to the instance_name which is DWH

Wrong:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC = (GLOBAL_DBNAME = DWHSBY ) (ORACLE_HOME = /u01/oracle/11.2.0.4) (SID_NAME = DWHSBY ))
  )

Corrected to:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC = (GLOBAL_DBNAME = DWH ) (ORACLE_HOME = /u01/oracle/11.2.0.4) (SID_NAME = DWH ))
  )

Also make sure the tns entry inside the tnsnames.ora file at the Primary DB side is configured to connect to the same SID_NAME configured in the Standby's listener.ora

DWHDB01S =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = STANDBYSERVER)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DWH)
    )
  )

UNDO Tablespace disappear from dba_tablespace_usage_metrics

 Problem:

 I had a problem on 19c DBs where I cannot monitor the size of UNDO tablespace because it's now shown in dba_tablespace_usage_metrics view.

SQL> select * from dba_tablespace_usage_metrics;

Solution:

It's Bug 28821847 - DBA_TABLESPACE_USAGE_METRICS Does Not Include Undo Tablespace Info ( Doc ID 28821847.8 ) impacting my current DB version 19.5 and it's fixed on 19.6 

Workaround:

I've modified my monitoring script to get the size of the UNDO tablespaces adding below green color script:

select tablespace_name,
       round((tablespace_size*8192)/(1024*1024)) Total_MB,
       round((used_space*8192)/(1024*1024)) Used_MB,
       round(((tablespace_size-used_space)*8192)/(1024*1024)) Free_MB,
       round(used_percent) "%Used"
from dba_tablespace_usage_metrics
 where tablespace_name not like '%UNDO%' union all SELECT a.tablespace_name,round(Total_MB) Total_MB,round(Used_MB) Used_MB,round ((Total_MB - Used_MB)) Free_MB,round ((Used_MB*100)/Total_MB,2) "%Used"
FROM
    ( SELECT SUM (maxbytes) / 1024 / 1024 Total_MB, b.tablespace_name
      FROM dba_data_files a, dba_tablespaces b
      WHERE a.tablespace_name = b.tablespace_name AND b.contents like 'UNDO'
      GROUP BY b.tablespace_name) a,
    ( SELECT c.tablespace_name, SUM (bytes) / 1024 / 1024 Used_MB
      FROM DBA_UNDO_EXTENTS c
      WHERE status <> 'EXPIRED' GROUP BY c.tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
/

How To Check if Oracle Gateway for MSSQL is Installed

How To Check if Oracle Gateway for MSSQL is Installed:

If the following command returns a list of files, then Oracle Gateway for MSSQL is installed:

# ls -l $ORACLE_HOME/dg4msql

If Oracle Gateway for MSSQL is installed, the following command will show you the Oracle Gateway version details:

# $ORACLE_HOME/bin/dg4msql 

To check if Oracle Gateway is configured on the system, search for "dg4msql" in the listener status output: [Provide the listener name if you are using a non-default listener name]

# lsnrctl status

 

Reference:

https://docs.oracle.com/database/121/OTGIS/configsql.htm#OTGIS900