DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Friday, June 23, 2017

DGMGRL Switchover StaticConnectIdentifier issue

Error:

DGMGRL Switchover not happened automatically because of db_unique_name STD database of StaticConnectIdentifier port is 1521.


DGMGRL> switchover to PROD;
Performing switchover NOW, please wait...
Operation requires a connection to instance "PROD" on database "prod"
Connecting to instance "PROD"...
Connected as SYSDBA.
New primary database "prod" is opening...
Operation requires start up of instance "STD" on database "std"
Starting instance "STD"...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod.doy.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STD_DGMGRL)(INSTANCE_NAME=STD)(SERVER=DEDICATED)))

ORA-12541: TNS:no listener



Solution:

Tns entries of PROD and STD database.

[oracle@prod admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_STD =
  (ADDRESS = (PROTOCOL = TCP)(HOST = prod.doy.com)(PORT = 1522))


PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prod.doy.com)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD)
    )
  )

STD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prod.doy.com)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STD)
    )
  )

LISTENER_PROD =
  (ADDRESS = (PROTOCOL = TCP)(HOST = prod.doy.com)(PORT = 1522))


We need to change the StaticConnectIdentifier parameter to 1522 in DGMGRL.Because,its wrongly point through the different port 1521.

DGMGRL> show database verbose STD;

Database - std

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 5.00 KByte/s
  Active Apply Rate:  0 Byte/s
  Maximum Apply Rate: 0 Byte/s
  Real Time Query:    OFF
  Instance(s):
    STD

  Properties:
    DGConnectIdentifier             = 'std'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    RedoRoutes                      = ''
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'MANUAL'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u01/app/oracle/oradata/PROD, /u01/app/oracle/oradata/STD'
    LogFileNameConvert              = '/u01/app/oracle/oradata/PROD, /u01/app/oracle/oradata/STD'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod.doy.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STD_DGMGRL)(INSTANCE_NAME=STD)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/app/oracle/fast_recovery_area/prod'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS



[oracle@prod ~]$ echo $ORACLE_SID
STD
[oracle@prod ~]$ dgmgrl sys/oracle@STD;
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.

DGMGRL> edit database 'std' set property 'StaticConnectIdentifier'='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod.doy.com)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=STD_DGMGRL)(INSTANCE_NAME=STD)(SERVER=DEDICATED)))';
Property "StaticConnectIdentifier" updated


STD:

DGMGRL> show database verbose STD;

Database - std

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 3.00 KByte/s
  Active Apply Rate:  0 Byte/s
  Maximum Apply Rate: 0 Byte/s
  Real Time Query:    OFF
  Instance(s):
    STD

  Properties:
    DGConnectIdentifier             = 'std'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    RedoRoutes                      = ''
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'MANUAL'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u01/app/oracle/oradata/PROD, /u01/app/oracle/oradata/STD'
    LogFileNameConvert              = '/u01/app/oracle/oradata/PROD, /u01/app/oracle/oradata/STD'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod.doy.com)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=STD_DGMGRL)(INSTANCE_NAME=STD)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/app/oracle/fast_recovery_area/prod'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS


Switchover:

[oracle@prod ~]$ dgmgrl sys/oracle@PROD;
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> switchover to STD;
Performing switchover NOW, please wait...
Operation requires a connection to instance "STD" on database "std"
Connecting to instance "STD"...
Connected as SYSDBA.
New primary database "std" is opening...
Operation requires start up of instance "PROD" on database "prod"
Starting instance "PROD"...
ORACLE instance started.
Database mounted.

Switchover succeeded, new primary is "std"
DGMGRL> DGMGRL> 
DGMGRL> switchover to PROD;
Performing switchover NOW, please wait...
Operation requires a connection to instance "PROD" on database "prod"
Connecting to instance "PROD"...
Connected as SYSDBA.
New primary database "prod" is opening...
Operation requires start up of instance "STD" on database "std"
Starting instance "STD"...
ORACLE instance started.
Database mounted.

Switchover succeeded, new primary is "prod"





No comments: