Oracle 19c Data Guard物理备库搭建

1. 部署目标

• • 搭建

    • 部署目标

  • • 部署环境介绍

    操作步骤

  • (1).   查看主库归档和附加日志配置，如下的输出显示，开启了归档模式但没有开启附加日志；

    [oracle@sdedu ~]$ sqlplus / as sysdba

    
    

    SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 7 20:04:29 2019

    Version 19.2.0.0.0

    
    

    Copyright (c) 1982, 2018, Oracle.  All rights reserved.

    
    

    
    

    Connected to:

    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

    Version 19.2.0.0.0

    
    

    SQL>  select log_mode,force_logging from v$database;

    
    

    LOG_MODE         FORCE_LOGGING

    -------- ---------- ---- ---------------------------------------

    ARCHIVELOG      NO

    
    

    (2).   查看主库归档目的地，从下面结果中可以看到，归档目的地使用了快速恢复区；

    SQL>  archive log list;

    Database log mode                   Archive Mode

    Automatic archival                   Enabled

    Archive destination                  USE_DB_RECOVERY_FILE_DEST

    Oldest online log sequence      1

    Next log sequence to archive   3

    Current log sequence                3

    
    

    (3).   开启主库附加日志，并验证开启的结果；

    SQL>  alter database force logging;

    
    

    Database altered.

    
    

    SQL> select log_mode,force_logging from v$database;

    
    

    LOG_MODE          FORCE_LOGGING

    ---------- ----- ----- -- ---------------------------------------

    ARCHIVELOG      YES

    
    

    从Oracle Database 18c开始，引入了以下两个新的nologging子句，它们可以执行非日志记录操作，同时可以使Active Data Guard备用数据库接收到所有数据，从而防止FORCE 方式生成大量重做日志导致性能下降。

    STANDBY NOLOGGING FOR DATA AVAILABILITY模式使批量加载操作通过其自身与备用数据库的连接将加载的数据发送到每个备用数据库。提交会延迟，直到所有Active Data Guard备用数据库通过 recover 方式将数据应用完成。

    SQL>  alter database set standby nologging for data availability;

    
    

    Database altered.

    
    

    SQL> select log_mode,force_logging from v$database;

    
    

    LOG_MODE                FORCE_LOGGING

    ---------- ----- ----- ----- -- --------------------------------------- ----- ----- ----- ----- ----- -----

    NOARCHIVELOG     STANDBY NOLOGGING FOR DATA AVAILABILITY

    STANDBY NOLOGGING FOR LOAD PERFORMANCE模式与先前的模式类似，不同之处在于，如果网络无法跟上数据加载到主数据库的速度，则加载过程可以停止将数据发送到备用数据库。在此模式下，备用数据库可能缺少数据，但每个Active Data Guard备用数据库都会在recover过程中自动从主数据库中提取数据。

    SQL>  alter database set standby nologging for load performance;

    
    

    Database altered.

    
    

    SQL> select log_mode,force_logging from v$database;

    
    

    LOG_MODE               FORCE_LOGGING

    ------- ------- ------- ----- --------------------------------------- ----- ----- ----- ----- ----- ----- -----

    NOARCHIVELOG    STANDBY NOLOGGING FOR LOAD PERFORMANCE

    
    

    (4).  在主库中添加附加日志；

    SQL>  alter database add standby logfile group 4 ('/u01/app/oracle/oradata/SS19P/stredo04.log') size 100m;

    
    

    Database altered.

    
    

    SQL>  alter database add standby logfile group 5 ('/u01/app/oracle/oradata/SS19P/stredo05.log') size 100m;

    
    

    Database altered.

    
    

    SQL>  alter database add standby logfile group 6 ('/u01/app/oracle/oradata/SS19P/stredo06.log') size 100m; 

    
    

    Database altered.

    
    

    SQL>  alter database add standby logfile group 7 ('/u01/app/oracle/oradata/SS19P/stredo07.log') size 100m;

    
    

    Database altered.

    
    

    (5).  修改主库参数；

    SQL>  alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(SS19P,SS19S)';

    
    

    System altered.

    
    

    SQL>  alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SS19P';

    
    

    System altered.

    
    

    SQL>  alter system set LOG_ARCHIVE_DEST_2='SERVICE=SS19S ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SS19S';

    
    

    System altered.

    
    

    SQL>  alter system set REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE' scope=spfile;

    
    

    System altered.

    
    

    SQL>  alter system set FAL_SERVER=SS19S;

    
    

    System altered.

    
    

    SQL>  alter system set DB_FILE_NAME_CONVERT='SS19S','SS19P' scope=spfile;

    
    

    System altered.

    
    

    SQL>  alter system set LOG_FILE_NAME_CONVERT='SS19S','SS19P' scope=spfile;

    
    

    System altered.

    
    

    SQL>  alter system set STANDBY_FILE_MANAGEMENT=AUTO;

    
    

    System altered.

    
    

    SQL> quit

    Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

    Version 19.2.0.0.0

    
    

    (6).  编辑主库 listener.ora 网络配置文件，添加连接主库点静态注册；

    [oracle@sdedu ~]$ cd /u01/app/oracle/product/19.2.0/dbhome_1/network/admin/

    [oracle@sdedu admin]$ vi listener.ora 

    # listener.ora Network Configuration File: /u01/app/oracle/product/19.2.0/dbhome_1/network/admin/listener.ora

    # Generated by Oracle configuration tools.

    
    

    LISTENER =

      (DESCRIPTION_LIST =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = sdedu.sandata.com.cn)(PORT = 1521))

          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

        )

      )

    
    

    SID_LIST_LISTENER =

      (SID_LIST =

        (SID_DESC =

           (GLOBAL_DBNAME = SS19P.sandata.com.cn)

           (SID_NAME = SS19P)

           (ORACLE_HOME = /u01/app/oracle/product/19.2.0/dbhome_1)

         )

       )

    
    

    (7).  编辑主库 tnsnames.ora 网络配置文件，添加连接备库的本地服务名；

    [oracle@sdedu admin]$ vi tnsnames.ora 

    # tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.2.0/dbhome_1/network/admin/tnsnames.ora

    # Generated by Oracle configuration tools.

    
    

    LISTENER_SS19P =

      (ADDRESS = (PROTOCOL = TCP)(HOST = sdedu.sandata.com.cn)(PORT = 1521))

    
    

    SS19P =

      (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = sdedu.sandata.com.cn)(PORT = 1521))

        (CONNECT_DATA =

          (SERVER = DEDICATED)

          (SERVICE_NAME = SS19P.sandata.com.cn)

        )

      )