Oracle 11g 增加DGMGRL管理工具

一、开启FLASH BACK功能

1.1 闪回状态查看

SQL> select name,flashback_on from v$database;

NAME      FLASHBACK_ON
--------- ------------------
DGBASE    NO

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /data/oracle/fast_recovery_are
                                                 a
db_recovery_file_dest_size           big integer 4182M
recovery_parallelism                 integer     0
SQL> alter database flashback on;

Database altered.

SQL>

附图

1.2 主库操作

同时启动DGMGRL进程并扩大恢复期，其实这个是逻辑的，并非一开始就占了50GB的系统磁盘空间

# 主库执行
SQL> alter system set db_recovery_file_dest_size=50G scope=both;

System altered.

SQL> alter system set dg_broker_start=true scope=both;

System altered.

SQL>

附图

1.3 备库操作

-- --取消恢复:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> alter system set db_recovery_file_dest_size=100G scope=both;

System altered.

SQL> alter database flashback on;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL> alter system set dg_broker_start=true scope=both;

System altered.

SQL>

附图

二 修改主备的DG参数 使其成为最大可用模式

2.1 dgmgr 配置

[oracle@dg01 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/123456@dgbase01
Connected.
DGMGRL> create configuration dg_broker_master as primary database is dgbase01 connect identifier is dgbase01;
Configuration "dg_broker_master" created with primary database "dgbase01"
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - dg_broker_master

  Protection Mode: MaxPerformance
  Databases:
    dgbase01 - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> add database dgbase02 as connect identifier is dgbase02 maintained as physical;
Database "dgbase02" added
DGMGRL> show configuration;

Configuration - dg_broker_master

  Protection Mode: MaxPerformance
  Databases:
    dgbase01 - Primary database
    dgbase02 - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> enable database dgbase02;

附图

2.2 主和备库所有属性信息

DGMGRL> show database verbose dgbase01;

Database - dgbase01

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

  Properties:
    DGConnectIdentifier             = 'dgbase01'
    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)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    SidName                         = 'dgbase'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dgbase01_DGMGRL)(INSTANCE_NAME=dgbase)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/data/oracle/archive'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arc'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

– 显示备库信息

DGMGRL> show database verbose dgbase02;

Database - dgbase02

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

  Properties:
    DGConnectIdentifier             = 'dgbase02'
    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)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    SidName                         = 'dgbase'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dgbase02_DGMGRL)(INSTANCE_NAME=dgbase)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/data/oracle/archive'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arc'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

说明

添加服务名
需要修改监听listener.ora文件.我们必须添加一个静态注册的service_name为db_unique_name_DGMGRL.db_domain,这个service_name会在DGMGRL重启数据库的时候用到.通过DGMGRL重启数据库时DMON进程会先将数据库关闭,然后DGMGRL在通过静态监听中的service_name连接到数据库,发送启动的命令.如果不这么做的话,在做switch over的时候我们容易遇到TNS-12514错误
搭建过程中需要注意两点：
1）创建DG_Broker配置文件所使用的各库的连接标识为tnsnames.ora文件中各库所对应的SERVICE_NAME。
2）进行转换之前注意查看配置文件show configuration，必须要保证数据库为开启状态

2.3 监听修改

[oracle@dg02 admin]$ cat listener.ora
# listener.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

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

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = dgbase)
     (ORACLE_HOME = /data/oracle/product/11.2.0/db_1)
     (SID_NAME = dgbase)
    )
   (SID_DESC =
     (GLOBAL_DBNAME = dgbase02_DGMGRL)
     (SERVICE_NAME = dgbase02)
     (ORACLE_HOME = /data/oracle/product/11.2.0/db_1)
     (SID_NAME = dgbase)
    )
   )
ADR_BASE_LISTENER = /data/oracle

alter system set service_names='dgbase01,dgbase01_DGMGRL' scope=both;
alter system set service_names='dgbase02,dgbase02_DGMGRL' scope=both;

三. DG broker 切换

DGMGRL> switchover to dgbase02;
Performing switchover NOW, please wait...
Operation requires a connection to instance "dgbase" on database "dgbase02"
Connecting to instance "dgbase"...
Connected.
New primary database "dgbase02" is opening...
Operation requires startup of instance "dgbase" on database "dgbase01"
Starting instance "dgbase"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

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

Please complete the following steps to finish switchover:
        start up instance "dgbase" of database "dgbase01"