« Por fin el 1z0-536 es mío..SQL Developer y problemas al ejecutarlo (JRE o JDK) »

ORA-12541: TNS:no listener en Switchover con DGMGRL



A continuación les mostraré un error que sucede con demasiada frecuencia al momento de hacer un Switchover en un Dataguard construído en Oracle 11g y que cuesta un poco encontrarlo

Y que aparece como un terrible ORA-12541: TNS:no listener




Para hacer un seguimiento al error, llevamos a cabo los siguientes


Nos conectamos desde cualquier nodo (Standby o primario) al DGMGRL

[oracle@nodo1-dg] $ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/oracle1
Connected.



Mostramos la configuración generada y verificamos cual es nuestra Standby y nuestra base primaria

DGMGRL> show configuration

Configuration - conf_dgprod

Protection Mode: MaxPerformance
Databases:
prod2 - Primary database
prod - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS



Procedemos a realizar el Switchover

DGMGRL> switchover to prod;
Performing switchover NOW, please wait...
New primary database "prod" is opening...
Operation requires shutdown of instance "prod" on database "prod2"
Shutting down instance "prod"...
ORACLE instance shut down.
Operation requires startup of instance "prod" on database "prod2"
Starting instance "prod"...
Unable to connect to database
ORA-12541: TNS:no listener

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
start up and mount instance "prod" of database "prod2"

DGMGRL> quit



Y dentro del Switchover aparece el mensaje

ORA-12541: TNS:no listener

Acá vienen los cuestionamientos y preguntas, ya que si hacemos la conexión mediante TCP/IP con los string de conexión, no existen inconvenientes...entonces debemos buscar el error por otro lado



Por lo pronto, subimos la base de datos Standby que no se pudo hacer a través del Switchover

[oracle@nodo1-dg] $ sqlplus sys/oracle1@PROD2 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 26 16:50:45 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup force nomount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size 1349732 bytes
Variable Size 226494364 bytes
Database Buffers 838860800 bytes
Redo Buffers 4628480 bytes
SQL> alter database mount standby database;

Database altered.

SQL> quit



Chequeamos la configuración para ver si ambas bases están disponibles

DGMGRL> show configuration

Configuration - conf_dgprod

Protection Mode: MaxPerformance
Databases:
prod - Primary database
prod2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS



Ahora vemos un detalle más acabado de la configuración , quizás podemos ver si por aquí está nuestro error

DGMGRL> show database verbose 'prod';

Database - prod

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
prod

Properties:
DGConnectIdentifier = 'prod'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'prod'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nodo1-dg.oracleyyo.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prod_DGMGRL)(INSTANCE_NAME=prod)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'arc_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'

Database Status:
SUCCESS



De hecho, hay algo que nos debería llamar la atención, que es la configuración de este parámetro StaticConnectIdentifier

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nodo1-dg.oracleyyo.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prod_DGMGRL)(INSTANCE_NAME=prod)(SERVER=DEDICATED)))'



En este parámetro aparece un puerto que no corresponde al puerto que tenemos realmente nuestro listener, ya que estamos escuchando por el puerto 1530


Por ende , procedemos a cambiar ese parámetro dentro de la configuración del DGMGRL

DGMGRL> edit database 'prod' set property 'StaticConnectIdentifier'='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nodo1-dg.oracleyyo.com)(PORT=1530))(CONNECT_DATA=(SERVICE_NAME=prod_DGMGRL)(INSTANCE_NAME=prod)(SERVER=DEDICATED)))'
> ;
Property "StaticConnectIdentifier" updated
DGMGRL>



Mostramos nuevamente la la configuración en detalle de nuestras bases de datos configuradas en el DGMGRL

DGMGRL> show database verbose 'prod';

Database - prod

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
prod

Properties:
DGConnectIdentifier = 'prod'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'prod'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nodo1-dg.oracleyyo.com)(PORT=1530))(CONNECT_DATA=(SERVICE_NAME=prod_DGMGRL)(INSTANCE_NAME=prod)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'arc_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'

Database Status:
SUCCESS

DGMGRL>



Chequemos lo mismo en el nodo2

DGMGRL> connect sys/oracle1
Connected.
DGMGRL>
DGMGRL> show database verbose 'prod2';

Database - prod2

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
prod

Properties:
DGConnectIdentifier = 'prod2'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/u02, /u03'
LogFileNameConvert = '/u02, /u03'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'prod'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nodo2-dg.oracleyyo.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prod2_DGMGRL)(INSTANCE_NAME=prod)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'arc_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'

Database Status:
SUCCESS



Y vemos que también tiene problemas en el puerto del listener que está configurado


Editamos la propiedad StaticConnectIdentifier y le cambiamos el puerto

DGMGRL> edit database 'prod2' set property 'StaticConnectIdentifier'='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nodo2-dg.oracleyyo.com)(PORT=1530))(CONNECT_DATA=(SERVICE_NAME=prod2_DGMGRL)(INSTANCE_NAME=prod)(SERVER=DEDICATED)))';
Property "StaticConnectIdentifier" updated
DGMGRL>



Mostramos la configuración en detalle

DGMGRL> show database verbose 'prod2'

Database - prod2

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
prod

Properties:
DGConnectIdentifier = 'prod2'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/u02, /u03'
LogFileNameConvert = '/u02, /u03'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'prod'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nodo2-dg.oracleyyo.com)(PORT=1530))(CONNECT_DATA=(SERVICE_NAME=prod2_DGMGRL)(INSTANCE_NAME=prod)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'arc_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'

Database Status:
SUCCESS

DGMGRL>



Y vemos que ya han sido cambiado los datos, después de esto realizamos nuevamente el Switchover de ida y vuelta, sin ningún problema

DGMGRL> switchover to prod2;
Performing switchover NOW, please wait...
New primary database "prod2" is opening...
Operation requires shutdown of instance "prod" on database "prod"
Shutting down instance "prod"...
ORACLE instance shut down.
Operation requires startup of instance "prod" on database "prod"
Starting instance "prod"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "prod2"


DGMGRL> switchover to prod;
Performing switchover NOW, please wait...
New primary database "prod" is opening...
Operation requires shutdown of instance "prod" on database "prod2"
Shutting down instance "prod"...
ORACLE instance shut down.
Operation requires startup of instance "prod" on database "prod2"
Starting instance "prod"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "prod"
DGMGRL>


Asunto arreglado :>>

by Ligarius
17.10.12. 10:01:31. 1344 words, 5370 views. Categories: Base de datos, StandBy - Data Guard ,