Skip to content

OCM 12c升级考试-手工搭建Data Guard和配置DG BROKER进行switch over和fail over

前面写过一篇用图形界面来安装Physical Standby的，但是考试的时候如果搭建不起来gc，那就很被动和麻烦了。所以有时候我们需要做两手准备，图形界面用不了，我还能手工搭建。那么这篇文章就是纯手工实现所有data guard操作的。

1.在主库，修改db_unique_name,打开归档和flashback,force logging

SQL> alter system set db_unique_name=PROD5_T1 scope=spfile; 
System altered.

shutdown immediate;
startup mount;
alter database archivelog;
alter database flashback on;
alter database open;
alter database force logging;
alter system set standby_file_management=auto;

2.主库上增加standby log file,在做switch over的时候，与在线重做日志相比，每个线程应该多加一个额外的组。否则会影响switch over

alter database add standby logfile ('/u01/app/oracle/oradata/PROD5/onlinelog/standby_redo01.log') SIZE 50M;
alter database add standby logfile ('/u01/app/oracle/oradata/PROD5/onlinelog/standby_redo02.log') SIZE 50M;
alter database add standby logfile ('/u01/app/oracle/oradata/PROD5/onlinelog/standby_redo03.log') SIZE 50M;
alter database add standby logfile ('/u01/app/oracle/oradata/PROD5/onlinelog/standby_redo04.log') SIZE 50M;

3.在主库和备库配置service

prod5_pri =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host01.us.oracle.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = PROD5)
    )
  )

prod5_stby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orae12.us.oracle.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = PROD5)
    )
  )

3.在主库配置静态监听服务,这里注意名字要命名为PROD5_T1_DGMGRL

SID_LIST_LISTENER =
  ( SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = PROD5_T1_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
      (SID_NAME=PROD5)
   )
 )

4.在备库配置静态监听服务,这里注意名字要命名为PROD5_T2_DGMGRL

SID_LIST_LISTENER =
  ( SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = PROD5_T2_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
      (SID_NAME=PROD5)
   )
 )

5.在主库和备库均reload监听服务，查看监听状态.

lsnrctl reload
lsnrctl status

Service "PROD5_T1_DGMGRL" has 1 instance(s).
  Instance "PROD5", status UNKNOWN, has 1 handler(s) for this service...
Service "PROD5_T2_DGMGRL" has 1 instance(s).
  Instance "PROD5", status UNKNOWN, has 1 handler(s) for this service...

6.备库创建目录，按照主库的目录结构进行创建。

mkdir -p /u01/app/oracle/oradata/PROD5/datafile/
mkdir -p /u01/app/oracle/fast_recovery_area
mkdir -p /u01/app/oracle/admin/PROD5/adump

7.在备库创建密码文件

orapwd file=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwPROD5 password=oracle entries=10

8.在备库创建参数文件，只需要设置db_name

cd /u01/app/oracle/product/12.1.0/dbhome_1/dbs
cat initPROD5.ora
*.db_name='PROD5'

9.在备库启动之前，先要在/etc/oratab加入一行，这样可以使用12c的环境变量。

PROD5:/u01/app/oracle/product/12.1.0/dbhome_1:N

10.在主库使用DUPLICATE创建standby

先要在备库启动实例
. oraenv
sqlplus / as sysdba
startup nomount 
然后主库执行DUPLICATE
rman target sys/oracle@prod5_pri auxiliary sys/oracle@prod5_stby
DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    SET db_unique_name='PROD5_T2' COMMENT 'this is standby'
  NOFILENAMECHECK;

11.主库和备库同时打开dg_broker_start参数

ALTER SYSTEM SET dg_broker_start = true;

12.使用dg broker来配置主库和备库的关联关系，并启用配置

dgmgrl sys/oracle@prod5_pri
DGMGRL> CREATE CONFIGURATION my_dg_config AS PRIMARY DATABASE IS PROD5_T1 CONNECT IDENTIFIER IS prod5_pri;
DGMGRL> ADD DATABASE PROD5_T2 AS CONNECT IDENTIFIER IS prod5_stby MAINTAINED AS PHYSICAL;
DGMGRL> show CONFIGURATION
Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  prod5_t1 - Primary database
    prod5_t2 - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> ENABLE CONFIGURATION;
Enabled.

13.检查配置的情况发现报ORA-16810

DGMGRL> show  Configuration 

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  prod5_t1 - Primary database
    prod5_t2 - Physical standby database 
      Error: ORA-16810: multiple errors or warnings detected for the database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 39 seconds ago)

先分别先检查主库和备库都是一些什么问题.执行show database verbose检查
DGMGRL> show database verbose "prod5_t1"

Database - prod5_t1

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

  Properties:
    DGConnectIdentifier             = 'prod5_pri'
    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           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host01.us.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PROD5_T1_DGMGRL)(INSTANCE_NAME=PROD5)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

DGMGRL> show database verbose "prod5_t2"

Database - prod5_t2

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          (unknown)
  Average Apply Rate: (unknown)
  Active Apply Rate:  (unknown)
  Maximum Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    PROD5
      Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
      Warning: ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the database setting
      Warning: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting
      Warning: ORA-16714: the value of property LogArchiveTrace is inconsistent with the database setting
      Warning: ORA-16675: database instance restart required for property value modification to take effect
      Warning: ORA-16714: the value of property LogArchiveFormat is inconsistent with the database setting

  Database Error(s):
    ORA-16766: Redo Apply is stopped

  Properties:
    DGConnectIdentifier             = 'prod5_stby'
    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           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orae12.us.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PROD5_T2_DGMGRL)(INSTANCE_NAME=PROD5)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

14.处理ORA-16714问题

备库出现一些ORA-16714错误。执行检查
DGMGRL>  show database "prod5_t2" 'InconsistentProperties'
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE 
           PROD5     ArchiveLagTarget                    0                                         0 
           PROD5 LogArchiveMaxProcesses                  4                                         4 
           PROD5 LogArchiveMinSucceedDest                1                                         1 
           PROD5      LogArchiveTrace                    0            (missing)                    0 
           PROD5     LogArchiveFormat         %t_%s_%r.dbf            (missing)         %t_%s_%r.dbf     

这里面其实很多值是一样，SPFILE_VALUE为missing需要重启一下,重启备库之后在检查。
sqlplus / as sysdba
shutdown immediate
startup mount

DGMGRL>  show database "prod5_t2" 'InconsistentProperties'
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE 
           PROD5     ArchiveLagTarget                    0                                         0 
           PROD5 LogArchiveMaxProcesses                  4                                         4 
           PROD5 LogArchiveMinSucceedDest                1                                         1            

修改上述三个值。
edit database 'prod5_t2' set property 'ArchiveLagTarget'=0;
edit database 'prod5_t2' set property 'LogArchiveMaxProcesses'=4;
edit database 'prod5_t2' set property 'LogArchiveMinSucceedDest'=1;

全部改完之后再一次检查。就没有任何问题了。
DGMGRL> show  Configuration 

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  prod5_t1 - Primary database
    prod5_t2 - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 19 seconds ago)


DGMGRL> SHOW DATABASE prod5_t2;

Database - prod5_t2

  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: 123.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    PROD5

Database Status:
SUCCESS

15.执行switchover

[oracle@host01 dbs]$ dgmgrl sys/oracle@prod5_pri
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> 
DGMGRL> SWITCHOVER TO prod5_t2;
Performing switchover NOW, please wait...
Operation requires a connection to instance "PROD5" on database "prod5_t2"
Connecting to instance "PROD5"...
Connected as SYSDBA.
New primary database "prod5_t2" is opening...
Operation requires start up of instance "PROD5" on database "prod5_t1"
Starting instance "PROD5"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "prod5_t2"

在另外一个节点在反切回来
[oracle@orae12 trace]$ dgmgrl sys/oracle@prod5_stby
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 prod5_t1; 
Performing switchover NOW, please wait...
Operation requires a connection to instance "PROD5" on database "prod5_t1"
Connecting to instance "PROD5"...
Connected as SYSDBA.
New primary database "prod5_t1" is opening...
Operation requires start up of instance "PROD5" on database "prod5_t2"
Starting instance "PROD5"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "prod5_t1"

16.转换备库为snapshot

[oracle@host01 dbs]$ dgmgrl sys/oracle@prod5_pri
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> CONVERT DATABASE "prod5_t2" TO SNAPSHOT STANDBY;
Converting database "prod5_t2" to a Snapshot Standby database, please wait...
Database "prod5_t2" converted successfully
DGMGRL> CONVERT DATABASE "prod5_t2" TO PHYSICAL STANDBY;
Converting database "prod5_t2" to a Physical Standby database, please wait...
Operation requires shut down of instance "PROD5" on database "prod5_t2"
Shutting down instance "PROD5"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires start up of instance "PROD5" on database "prod5_t2"
Starting instance "PROD5"...
ORACLE instance started.
Database mounted.
Continuing to convert database "prod5_t2" ...
Database "prod5_t2" converted successfully

17.配置fast fail over,这里可以参考传送门

##########主库运行##########
DGMGRL> show configuration

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  prod5_t1 - Primary database
    prod5_t2 - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 27 seconds ago)


edit database "prod5_t1" set property LogXptMode='SYNC';
edit database "prod5_t2" set property LogXptMode='SYNC';
edit configuration set protection mode as  maxavailability;

edit database "prod5_t1" set property FastStartFailoverTarget='prod5_t2';
edit database "prod5_t2" set property FastStartFailoverTarget='prod5_t1';
edit database "prod5_t2" set state='apply-off';

##########备库运行##########
sqlplus / as sysdba
alter database flashback on; 
##########主库运行##########
DGMGRL> edit database "prod5_t2" set state='apply-on';

edit database 'prod5_t1' set property TransportLagThreshold=30; 
edit database 'prod5_t2' set property TransportLagThreshold=30;
edit database 'prod5_t1' set property ApplyLagThreshold=45; 
edit database 'prod5_t2' set property ApplyLagThreshold=45; 

DGMGRL> ENABLE FAST_START FAILOVER
Enabled.
DGMGRL> start observer
Observer started

#############做点破坏性动作，shutdown abort主库
SQL> shutdown abort; 
ORACLE instance shut down


##########然后观察者可以看到下列输出
DGMGRL> start observer
Observer started

19:45:15.24  Sunday, May 27, 2018
Initiating Fast-Start Failover to database "prod5_t2"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "prod5_t2"
19:45:19.26  Sunday, May 27, 2018

至此整个data guard全手工命令行搭建，及switch over,fail over，还有转换成snapshot都完成了。

分享到： 更多

Trackbacks & Pingbacks 1

  1. From 12C OCM 升级考试心得体会 | Buddy Yuan的个人技术博客 on 24 6月 2018 at 15:18

    […] 第三章较为简单，这一场考试时间也是90分钟，如果用图形做的话时间会很充裕，前提是你第二章把Cloud Control搭起来，整个过程我都是用图形做的，除了最后的Fast failover以外(用DGMGRL配置)。但是这一章最好还是要防一手，假设你的GC没搭建起来，全程用命令行+DGMGRL配置起来也得会弄才行。具体这个可以参考我的文章：OCM 12C升级考试-手工搭建DATA GUARD和配置DG BROKER进行SWITCH OVER和FAIL OVER […]

Post a Comment

Your email is never published nor shared. Required fields are marked *