注册登录
提 问
English中文版
hbhe0316
作者hbhe0316·2021-11-27 08:58
数据库管理员·IBM
关注75

Oracle 19c配置ob server

字数 15453阅读 1069评论 0赞 1

1.环境准备,observer安装在192.168.56.100

[oracle@observer admin]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.56.100 observer
192.168.56.101 dg01
192.168.56.102 dg02

2.observer的tnsnames.ora文件查看

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

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl_p)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL_P_DGMGRLl)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = hbhe)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl_s_dgmgrl)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl_s)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = observer)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
~
[oracle@observer admin]$ cat tnsnames.ora 

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = observer)(PORT = 1521))


ORCL_P =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl_p)
    )
  )

ORCL_S =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg02)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl_s)
    )
  )

ORCL_P_DGMGRL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL_P_DGMGRL)
    )
  )


ORCL_S_DGMGRL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg02)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL_S_DGMGRL)
    )
  )

3.dg01的tnsnamer.ora文件和listener.ora文件

[oracle@dg01 admin]$ pwd
/u01/app/oracle/product/19.3.0/dbhome_1/network/admin
[oracle@dg01 admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl_p)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = orcl)
    )
   (SID_DESC =
      (GLOBAL_DBNAME = orcl_p_dgmgrl)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = hbhe)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg01)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

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

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = dg01)(PORT = 1521))


ORCL_P =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl_p)
    )
  )

ORCL_S =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg02)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl_s)
    )
  )

4.dg02的tnsnamer.ora文件和listener.ora文件

[oracle@dg02 admin]$ pwd
/u01/app/oracle/product/19.3.0/dbhome_1/network/admin
[oracle@dg02 admin]$        cat listener.ora 
SID_LIST_LISTENER =
  (SID_LIST =
   (SID_DESC =
      (GLOBAL_DBNAME = orcl_s_dgmgrl)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl_s)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = hbhe)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg02)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

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

ORCL_P =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl_p)
    )
  )

ORCL_S =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg02)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl_s)
    )
  )

5.在dg01上执行命令解锁sysdg用户

SQL> alter user sysdg identified by wwwwww account unlock;

User altered.

SQL> grant sysdg to sysdg;

Grant succeeded.

SQL> set linesize 200
SQL> set pagesize 200
SQL> col username for a20
SQL> col sysbackup for a10
SQL> col sysdg for a20
SQL> select username,SYSBACKUP, SYSDG from V$PWFILE_USERS;

USERNAME             SYSBACKUP  SYSDG
-------------------- ---------- --------------------
SYS                  FALSE      FALSE
SYSDG                FALSE      TRUE

6.在dg01和dg02上执行命令，注意：两边都需要执行。

SQL>  alter system set dg_broker_start=true;

System altered.

在dg02上执行命令

SQL> alter system set LOG_ARCHIVE_DEST_2='';

System altered.

7.在observer上测试连接

[oracle@observer admin]$ dgmgrl sysdg@orcl_p
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Nov 13 21:00:44 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Password:
Connected to "orcl_p"
Connected as SYSDG.

8.在observer上配置主库和备库

DGMGRL> CREATE CONFIGURATION dg_config AS PRIMARY DATABASE IS ORCL_P CONNECT IDENTIFIER IS ORCL_P;
Configuration "dg_config" created with primary database "orcl_p"
DGMGRL> Add database 'ORCL_S' as connect identifier is ORCL_S maintained as physical;
Database "ORCL_S" added

DGMGRL> show configuration;

Configuration - dg_config

  Protection Mode: MaxPerformance
  Members:
  orcl_p - Primary database
    ORCL_S - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED

DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL>  show configuration;

Configuration - dg_config

  Protection Mode: MaxPerformance
  Members:
  orcl_p - Primary database
    ORCL_S - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 4 seconds ago)

DGMGRL> show database orcl_p;

Database - orcl_p

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

Database Status:
SUCCESS

DGMGRL> show database orcl_s;

Database - ORCL_S

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 17.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    orcl

Database Status:
SUCCESS

9.主备切换

DGMGRL> VALIDATE DATABASE orcl_p; 

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    orcl_p:  Off

  Managed by Clusterware:
    orcl_p:  NO             
    Validating static connect identifier for the primary database orcl_p...
    The static connect identifier allows for a connection to database "orcl_p".

DGMGRL> VALIDATE DATABASE orcl_s; 

  Database Role:     Physical standby database
  Primary Database:  orcl_p

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    orcl_p:  Off
    ORCL_S:  Off

  Managed by Clusterware:
    orcl_p:  NO             
    ORCL_S:  NO             
    Validating static connect identifier for the primary database orcl_p...
    The static connect identifier allows for a connection to database "orcl_p".

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status       
              (ORCL_S)                (orcl_p)                             
    1         3                       0                       Insufficient SRLs
    Warning: standby redo logs not configured for thread 1 on orcl_p

  Transport-Related Property Settings:
    Property                        orcl_p Value             ORCL_S Value
    LogXptMode                      ASYNC                    SYNC
DGMGRL> show database VERBOSE orcl_p;  

Database - orcl_p

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

  Properties:
    DGConnectIdentifier             = 'orcl_p'
    ObserverConnectIdentifier       = ''
    FastStartFailoverTarget         = ''
    PreferredObserverHosts          = ''
    LogShipping                     = 'ON'
    RedoRoutes                      = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '30'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    ApplyInstances                  = '0'
    StandbyFileManagement           = ''
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '0'
    LogArchiveMinSucceedDest        = '0'
    DataGuardSyncLatency            = '0'
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = ''
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    ArchiveLocation                 = ''
    AlternateLocation               = ''
    StandbyArchiveLocation          = ''
    StandbyAlternateLocation        = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    LogXptStatus                    = '(monitor)'
    SendQEntries                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'dg01'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_p_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
    TopWaitEvents                   = '(monitor)'
    SidName                         = '(monitor)'

  Log file locations:
    Alert log               : /u01/app/oracle/diag/rdbms/orcl_p/orcl/trace/alert_orcl.log
    Data Guard Broker log   : /u01/app/oracle/diag/rdbms/orcl_p/orcl/trace/drcorcl.log

Database Status:
SUCCESS

DGMGRL> show database VERBOSE orcl_s;  

Database - ORCL_S

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 5.00 KByte/s
  Active Apply Rate:  412.00 KByte/s
  Maximum Apply Rate: 435.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    orcl

  Properties:
    DGConnectIdentifier             = 'orcl_s'
    ObserverConnectIdentifier       = ''
    FastStartFailoverTarget         = ''
    PreferredObserverHosts          = ''
    LogShipping                     = 'ON'
    RedoRoutes                      = ''
    LogXptMode                      = 'SYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '30'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    ApplyInstances                  = '0'
    StandbyFileManagement           = ''
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '0'
    LogArchiveMinSucceedDest        = '0'
    DataGuardSyncLatency            = '0'
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = ''
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    ArchiveLocation                 = ''
    AlternateLocation               = ''
    StandbyArchiveLocation          = ''
    StandbyAlternateLocation        = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    LogXptStatus                    = '(monitor)'
    SendQEntries                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'dg02'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_s_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
    TopWaitEvents                   = '(monitor)'
    SidName                         = '(monitor)'

  Log file locations:
    Alert log               : /u01/app/oracle/diag/rdbms/orcl_s/orcl/trace/alert_orcl.log
    Data Guard Broker log   : /u01/app/oracle/diag/rdbms/orcl_s/orcl/trace/drcorcl.log

Database Status:
SUCCESS

在dg01上检查

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
------------------------------------------------------------
TO STANDBY

10.切换

DGMGRL> connect sys@orcl_p                        
Password:
Connected to "orcl_p"
Connected as SYSDBA.
DGMGRL> SWITCHOVER TO 'orcl_s';
Performing switchover NOW, please wait...
Operation requires a connection to database "ORCL_S"
Connecting ...
Connected to "orcl_s"
Connected as SYSDBA.
New primary database "ORCL_S" is opening...
Operation requires start up of instance "orcl" on database "orcl_p"
Starting instance "orcl"...
Connected to an idle instance.
ORACLE instance started.
Connected to "orcl_p"
Database mounted.
 Database opened.
Connected to "orcl_p"
Switchover succeeded, new primary is "orcl_s"


SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
------------------------------------------------------------
TO STANDBY

11.重新切回来

DGMGRL>      SWITCHOVER TO 'orcl_p';
Performing switchover NOW, please wait...
Operation requires a connection to database "orcl_p"
Connecting ...
Connected to "orcl_p"
Connected as SYSDBA.
New primary database "orcl_p" is opening...
Operation requires start up of instance "orcl" on database "ORCL_S"
Starting instance "orcl"...
Connected to an idle instance.
ORACLE instance started.
Connected to "orcl_s"
Database mounted.
Database opened.
Connected to "orcl_s"
Switchover succeeded, new primary is "orcl_p"
Oracle
著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

赞1

添加新评论0 条评论

Ctrl+Enter 发表

作者其他文章

  • RedHat 8.5 下oracle 19.3升级至19.13
    评论 1 · 赞 4
  • Redhat 7.9安装Oracle 19.3 RAC
    评论 1 · 赞 1
  • Security Bulletin_Vulnerability in Apache Log4j affects WAS_CVE-2021-44228
    评论 0 · 赞 0
  • redhat 8.5安装Oracle 19.3
    评论 0 · 赞 0
  • Oracle 19c RAC 19.3 升级到19.12
    评论 0 · 赞 0

    • 相关文章

  • 数据库租户能力大调研
  • 某大型银行手机银行等重要交易系统不做大幅改造整体去IOE最佳实践-技术路线选择篇
  • Oracle 兼容性面面观
  • Oracle数据库日常巡检指令
  • 一次Oracle bug的故障排查过程思考

    • 相关问题

  • 现在入行数据库，选择Oracle数据库为切入点，是对是错呢？
  • 国产分布式数据库真的能全面替换核心业务吗？Oracle为何没有向分布式发力？
  • 金融行业里，从性能、容量等角度来看，PostgreSQL适合替换什么规模的Oracle数据库？
  • 国产数据库同Oracle数据库的能力差别在哪里？国产数据全面开始替代Oracle等数据库我们还需要再那些方面努力？
  • Oracle迁移至PG，如何保障数据库的高可用和可持续性？

    • 相关资料

  • Redhat7.6 Oracle 19C RAC部署手册
  • Redhat7.0 Oracle 12C RAC部署手册
  • SQL必知必会（第4版）
  • Oracle数据库日常维护手册v1.8
  • 国产K1 Power10 K8880G3上AIX由7.2升级7.3+Oracle_19c升级实践

    • 关于TWT使用指南社区专家合作厂商入驻社区企业招聘投诉建议版权与免责声明联系我们
    © 2024talkwithtrend — talk with trend,talk with technologist京ICP备09031017号-30

    X社区推广