加载中…
个人资料
oracleworld
oracleworld
  • 博客等级:
  • 博客积分:0
  • 博客访问:106,301
  • 关注人气:17
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
相关博文
谁看过这篇博文
加载中…
正文 字体大小:

解决Oracle DataGuard Broker出现的TNS-12514错误

(2012-12-07 23:21:28)
标签:

杂谈

分类: Oracle技术

环境:主备库都在一个机器上面,

listener文件配置如下:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocm-db11g)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
      (GLOBAL_DBNAME=prod_DGMGRL)
      (ORACLE_HOME=/oracle/db11g)
      (SID_NAME=prod)
   )
  (SID_DESC =
      (GLOBAL_DBNAME=prod)
      (ORACLE_HOME=/oracle/db11g)
      (SID_NAME=prod)
  )
 )


LISTENER2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocm-db11g)(PORT = 1522))
    )
  )

SID_LIST_LISTENER2 =
 (SID_LIST =
   (SID_DESC =
      (GLOBAL_DBNAME=sbdb_DGMGRL)
      (ORACLE_HOME=/oracle/db11g)
      (SID_NAME=sbdb)
   )
  (SID_DESC =
      (GLOBAL_DBNAME=sbdb)
      (ORACLE_HOME=/oracle/db11g)
      (SID_NAME=sbdb)
  )
 )

 

主库prod使用监听端口1521

备库sbdb使用监听端口1522

在dgmgrl中执行转换时候

DGMGRL> show configuration verbose;

Configuration
  Name:                mydg
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Databases:
    prod - Primary database
    sbdb - Physical standby database

Fast-Start Failover: DISABLED

Current status for "mydg":
SUCCESS

DGMGRL> switchover to sbdb;
Performing switchover NOW, please wait...
New primary database "sbdb" is opening...
Operation requires shutdown of instance "prod" on database "prod"
Shutting down instance "prod"...
ORA-01109: database not open

Database dismounted.
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 "sbdb"
DGMGRL> show configuration verbose;

Configuration
  Name:                mydg
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Databases:
    sbdb - Primary database
    prod - Physical standby database

Fast-Start Failover: DISABLED

Current status for "mydg":

SUCCESS

这里可以看到prod到sbdb角色切换没有问题。

 

如果再把prod库切回到主库遇到问题了:

DGMGRL> switchover to prod;
Performing switchover NOW, please wait...
New primary database "prod" is opening...
Operation requires shutdown of instance "sbdb" on database "sbdb"
Shutting down instance "sbdb"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "sbdb" on database "sbdb"
Starting instance "sbdb"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
You are no longer connected to ORACLE
Please connect again.
Unable to start instance "sbdb"
You must start instance "sbdb" manually
Switchover succeeded, new primary is "prod"

我想这个和sbdb监听端口使用1522有关系,通过检查sbdb的配置发现:

DGMGRL> show database verbose sbdb;

Database
  Name:            sbdb
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  APPLY-ON
  Instance(s):
    sbdb

  Properties:
    DGConnectIdentifier             = 'sbdb'
    ObserverConnectIdentifie      = ''
    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               = 'prod, sbdb'
    LogFileNameConvert              = 'prod, sbdb'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'ocm-db11g'
    SidName                         = 'sbdb'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm-db11g)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sbdb_DGMGRL)(INSTANCE_NAME=sbdb)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/oracle/sbdb_arch'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

Current status for "sbdb":
SUCCESS

可以看到DG Broker使用了默认的1521端口,修改下:

DGMGRL> edit database sbdb set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm-db11g)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=sbdb_DGMGRL)(INSTANCE_NAME=sbdb)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated
DGMGRL> show database verbose sbdb;

Database
  Name:            sbdb
  Role:            PRIMARY
  Enabled:         YES
  Intended State:  TRANSPORT-ON
  Instance(s):
    sbdb

  Properties:
    DGConnectIdentifier             = 'sbdb'
    ObserverConnectIdentifie      = ''
    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               = 'prod, sbdb'
    LogFileNameConvert              = 'prod, sbdb'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'ocm-db11g'
    SidName                         = 'sbdb'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm-db11g)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=sbdb_DGMGRL)(INSTANCE_NAME=sbdb)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/oracle/sbdb_arch'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

Current status for "sbdb":
SUCCESS
然后再执行一次切换测试:

DGMGRL> show configuration verbose;

Configuration
  Name:                mydg
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Databases:
    sbdb - Primary database
    prod - Physical standby database

Fast-Start Failover: DISABLED

Current status for "mydg":
SUCCESS

DGMGRL> switchover to prod;
Performing switchover NOW, please wait...
New primary database "prod" is opening...
Operation requires shutdown of instance "sbdb" on database "sbdb"
Shutting down instance "sbdb"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "sbdb" on database "sbdb"
Starting instance "sbdb"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "prod"

DGMGRL> show configuration verbose;

Configuration
  Name:                mydg
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Databases:
    prod - Primary database
    sbdb - Physical standby database

Fast-Start Failover: DISABLED

Current status for "mydg":
SUCCESS

DGMGRL>

这次无论是那种方式切换都成功。

 

 

0

阅读 评论 收藏 转载 喜欢 打印举报
已投稿到:
  • 评论加载中,请稍候...
发评论

       

    发评论

    以上网友发言只代表其个人观点,不代表新浪网的观点或立场。

      

    新浪BLOG意见反馈留言板 不良信息反馈 电话:4006900000 提示音后按1键(按当地市话标准计费) 欢迎批评指正

    新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 会员注册 | 产品答疑

    新浪公司 版权所有