行业资讯

Oracle 19c Data Guard物理备库搭建

发布时间：2020-08-09 14:32:44 作者：s相濡以沫y
来源：ITPUB博客 阅读：175

  1. 部署目标


  1. 部署环境介绍



  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)

    )

  )


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


推荐阅读：
  1. [读书笔记]ORACLE11GR2 DataGuard架构
  2. Oracle 19C Data Guard基础运维-05Failovers (GAP)

免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。

19c data guard

上一篇：i3 窗口管理器使 Linux 更美好

下一篇：反转单链表——16

相关阅读

您好，登录后才能下订单哦！

密码登录
登录注册
获取短信验证码
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》
产品服务
地区划分
帮助支持
关于我们
行业资讯-文章归档 问答-问答归档
广州亿速云计算有限公司

7*24小时在线电话：400-100-2938

7*24小时在线QQ：800811969

Copyright © Yisu Cloud Ltd. All Rights Reserved. 2018 版权所有
粤ICP备17096448号-1 粤公网安备 44010402001142号