Oracle 19c Data Guard物理备库搭建

• 搭建

  • 部署目标

• • 部署环境介绍

  操作步骤

• (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)

      )

    )

  
  

  SS19S =

    (DESCRIPTION =

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

      (CONNECT_DATA =

        (SERVER = DEDICATED)

        (SERVICE_NAME = SS19S.sandata.com.cn)

      )

    )

  
  

  (8).  将主库中的网络文件和口令文件传输到备库；

  [oracle@sdedu admin]$ scp listener.ora tnsnames.ora sdrep:`pwd`

  oracle@sdrep's password: 

  listener.ora                                                  100%  558   451.3KB/s   00:00    

  tnsnames.ora                                                  100%  652   580.3KB/s   00:00

  
  

  [oracle@sdedu admin]$ cd ../../dbs

  [oracle@sdedu dbs]$ scp orapwSS19P sdrep:`pwd`/orapwSS19S

  oracle@sdrep's password: 

  orapwSS19P

  
  

  (9).  备库中创建对应路径：

  [oracle@sdrep ~]$ cd /u01/app/oracle/

  [oracle@sdrep oracle]$ mkdir -p admin/SS19S/adump

  [oracle@sdrep oracle]$ mkdir -p oradata/SS19S

  [oracle@sdrep oracle]$ mkdir -p fast_recovery_area/SS19S

  
  

  (10).  创建备库的参数文件：

  [oracle@sdrep oracle]$ cd /u01/app/oracle/product/19.2.0/dbhome_1/dbs/

  [oracle@sdrep dbs]$ vi initSS19S.ora 

  
  

  DB_NAME=SS19S

  
  

  (11).  修改备库的 listener.ora 配置文件：

  [oracle@sdrep dbs]$ vi ../network/admin/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 =  SS19S.example.com )(PORT = 1521))

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

      )

    )

  
  

  SID_LIST_LISTENER =

    (SID_LIST =

      (SID_DESC =

        (GLOBAL_DBNAME =  SS19S.sandata.com.cn )

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

        (SID_NAME =  SS19S )

       )

     )

  
  

  (12).  修改备库的 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 =  sdrep.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)

      )

    )

  
  

  SS19S =

    (DESCRIPTION =

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

      (CONNECT_DATA =

        (SERVER = DEDICATED)

        (SERVICE_NAME = SS19S.sandata.com.cn)

      )

    )

  
  

  (13).  启动辅助实例；

  [oracle@sdrep dbs]$ export ORACLE_SID=SS19S

  [oracle@sdrep dbs]$ sqlplus / as sysdba

  
  

  SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 7 16:41:03 2019 

  Version 19.2.0.0.0

  
  

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

  
  

  Connected to an idle instance.

  
  

  SQL> startup nomount

  ORACLE instance started.

  
  

  Total System Global Area 1543500824 bytes

  Fixed Size                  9135128 bytes

  Variable Size            1006632960 bytes

  Database Buffers          520093696 bytes

  Redo Buffers                7639040 bytes

  Database mounted.

  SQL> quit

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

  Version 19.2.0.0.0

  
  

  (14).  使用复制数据库技术搭建备库，由于备库的文件路径包含SS19S，因此需要写明audit_file_dest,control_files参数（使用单引号也可以）；

  [oracle@sdrep dbs]$  rman target sys/oracle@SS19P auxiliary sys/oracle@SS19S

  
  

  Recovery Manager: Release 19.0.0.0.0 - Production on Wed Mar 6 20:51:58 2019

  Version 19.2.0.0.0

  
  

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

  
  

  connected to target database: SS19P (DBID=592912636)

  connected to auxiliary database: SS19P (not mounted)

  
  

  RMAN>  DUPLICATE TARGET DATABASE

  2>     FOR STANDBY

  3>     FROM ACTIVE DATABASE

  4>     DORECOVER

  5>     SPFILE

  6>     SET DB_UNIQUE_NAME="SS19S" COMMENT "Is a dbstyle duplicate"

  7>     SET AUDIT_FILE_DEST="/u01/app/oracle/admin/SS19S/adump"

  8>     SET CONTROL_FILES='/u01/app/oracle/oradata/SS19S/control01.ctl','/u01/app/oracle/fast_recovery_area/SS19S/control02.ctl'

  9>     SET LOG_ARCHIVE_CONFIG="DG_CONFIG=(SS19S,SS19P)"

  10>    SET LOG_ARCHIVE_DEST_1="LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SS19S"

  11>     SET LOG_ARCHIVE_DEST_2="SERVICE=SS19P ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SS19P"

  12>     SET DB_FILE_NAME_CONVERT="SS19P","SS19S" 

  13>     SET LOG_FILE_NAME_CONVERT="SS19P","SS19S"  

  14>     SET FAL_SERVER="SS19P" COMMENT "Is primary"

  15>     SET STANDBY_FILE_MANAGEMENT="AUTO"

  16>     NOFILENAMECHECK;

  
  

  Starting Duplicate Db at 06-MAR-19

  using target database control file instead of recovery catalog

  allocated channel: ORA_AUX_DISK_1

  channel ORA_AUX_DISK_1: SID=21 device type=DISK

  current log archived

  
  

  省略部分 ………

  
  

  released channel: ORA_DISK_1

  released channel: ORA_AUX_DISK_1

  allocated channel: ORA_DISK_1

  channel ORA_DISK_1: SID=53 device type=DISK

  deleted archived log

  archived log file name=/u01/app/oracle/fast_recovery_area/SS19S/archivelog/2019_03_06/o1_mf_1_10_g7zjp7gs_.arc RECID=1 STAMP=1002228807

  deleted archived log

  archived log file name=/u01/app/oracle/fast_recovery_area/SS19S/archivelog/2019_03_06/o1_mf_1_11_g7zjp8po_.arc RECID=2 STAMP=1002228808

  Deleted 2 objects

  
  

  Finished Duplicate Db at 06-MAR-19

  
  

  RMAN> quit

  
  

  
  

  Recovery Manager complete.

  
  

  (15).  进入备库验证角色信息；

  [oracle@sdrep dbs]$ sqlplus / as sysdba

  
  

  SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 6 20:54:39 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 database_role from v$database;

  
  

  DATABASE_ROLE

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

  PHYSICAL STANDBY

  
  

  SQL> select instance_name from v$instance;

  
  

  INSTANCE_NAME

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

  SS19S

  
  

  (16).  在备库启动recover 过程，应用主库传过来的日志（默认已经是real-time apply模式，因此省略using current logfile）；

  SQL>  recover managed standby database disconnect from session;

  Media recovery complete.

  
  

  (17).  查询v$dataguard_process 视图，验证来自主库传输过来的日志过程和备库应用日志的情况（v$dataguard_process视图在12.2版本出现，取代了v$managed_standby)；

  
  

  SQL> select role,thread#,sequence#,action from v$dataguard_process;

  
  

  ROLE                               THREAD#      SEQUENCE#     ACTION

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

  recovery apply slave                            0                          0   IDLE

  archive local                                           0                          0  IDLE

  redo transport timer                                0                          0  IDLE

  gap manager                                           0                          0  IDLE

  recovery logmerger                                1                        12  WAIT_FOR_LOG

  recovery apply slave                                                         0  IDLE

  managed recovery                                                            0  IDLE

  archive redo                                           0                            0  IDLE

  archive redo                                           0                            0  IDLE

  archive redo                                           0                            0  IDLE

  redo transport monitor                         0                            0  IDLE

  log writer                                             0                            0  IDLE

  
  

  12 rows selected.

  
  

  (18).  查询 v$archived_log 视图，验证来自主库传输过来的日志变化情况，下面输出可以看到主库传输过来的日志在增加；

  SQL> select sequence#,applied from v$archived_log;

  
  

   SEQUENCE#   APPLIED

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

                       10  YES

                       11  YES

  
  

  SQL>  select sequence#,applied from v$archived_log;

  
  

   SEQUENCE#   APPLIED

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

                        10  YES

                        11  YES

                        12  YES

  
  

  SQL>  select sequence#,applied from v$archived_log;

  
  

   SEQUENCE#   APPLIED

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

                        10  YES

                        11  YES

                        12  YES

                        13  YES

  两节点 Oracle 19c Dataguard 环境

• 搭建两节点 Oracle 19c Dataguard 环境

• 主库不关闭

• 使用物理备库

• 不使用Broker

• 操作系统版本：OEL 7.6

• 数据库版本：Oracle Database 19c

• 两台主机名为：sdedu， sdrep

• 主库名称：SS19P

• 备库名称：SS19S

• 主库不关闭

• 使用物理备库

• 不使用Broker

• 操作系统版本：OEL 7.6

• 数据库版本：Oracle Database 19c

• 两台主机名为：sdedu， sdrep

• 主库名称：SS19P

• 备库名称：SS19S