9. Oracle DataGuard的介绍

2022-05-17 23:00:21

一. Oracle DataGuard简介

　　Oracle DataGuard；简称DG。是由一个Primary Database（主库）和一个或者多个Standby Database（备库）组成。对Oracle来说；本身不能提高性能。通过数据冗余来保护数据。由Primary Database对外提供服务；用户操作在Primary Database上操作；其操作的数据库Redo Log或者Archive log通过网络传输到Standby Database。Standby Database在重做这些日志。从而实现Primary Database和Standby Database数据同步。

架构图如下：

9. Oracle DataGuard的介绍

Oracle DataGuard中的Standby库有两种：物理Standby和逻辑Standby。

  • 逻辑Standby接收后将其转换成SQL语句，在Standby数据库上执行SQL语句实现同步，这种方式叫SQL Apply。
  • 物理Standby接收完Primary数据库生成的REDO数据后，以介质恢复的方式实现同步，这种方式也叫Redo Apply。

二. Oracle DataGuard搭建

　　安装规划：

IP ROLE
192.168.1.235 Primary
192.168.1.221 Standby

2.1. 主库设置归档模式

alter database archivelog;

2.2. 主库设置Force Logging模式

alter database force logging;

2.3. 备库创建相应的dump文件夹

# 在主库查询对应的dump目录

select name, value

  from v$parameter

 where name in ('audit_file_dest',

                'background_dump_dest',

                'control_files',

                'core_dump_dest',

                'user_dump_dest',

                'db_recovery_file_dest'

                               )

 ORDER BY name ASC;

# 备库用oracle用户执行创建目录

mkdir -p $value

2.4. 配置Standby Redologs

　　对于模式中最大保护和最高可用性；采用是 LGWR 模式传送 Redo 日志；需要为 Standby库配置 Standby Redologs。Standby Redologs 和 ONline Redologs 文件大小是相同的。比 ONline Redologs 个数要 +1 。

# 查看redologs日志信息

SQL> select group#,type,member from v$logfile;

GROUP# TYPE   MEMBER

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

     1 ONLINE   /u01/app/oradata/ora235/redo01.log

     2 ONLINE   /u01/app/oradata/ora235/redo02.log

     3 ONLINE   /u01/app/oradata/ora235/redo03.log

SQL> select group#,bytes from v$log;

GROUP#     BYTES

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

     1  52428800

     2  52428800

     3  52428800

# 添加Standby Redologs；添加如下：

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4('/u01/app/oradata/ora235/stdred001.log') SIZE 50 M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5('/u01/app/oradata/ora235/stdred002.log') SIZE 50 M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6('/u01/app/oradata/ora235/stdred003.log') SIZE 50 M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7('/u01/app/oradata/ora235/stdred004.log') SIZE 50 M;

2.5. 创建实例密钥文件

  • 主库生成密钥文件
orapwd FILE=/u01/app/oracle/dbs/orapwora235 password=li0924 entries=5;
  • 将密钥文件传输到备库
scp /u01/app/oracle/dbs/orapwora235 oracle@192.168.1.221:/u01/app/oracle/dbs

2.6. 创建参数文件

　　本文采用 Primary 库和 Standby 库采用相同路径；所以仅设置必要的参数。

  • 主库通过spfile生成pfile文件
create pfile from spfile;
  • 添加Primary端的pfile内容如下
*.DB_UNIQUE_NAME=db_primary

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(db_primary,db_standby)'

*.LOG_ARCHIVE_DEST_2='SERVICE=tns_standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_standby'

*.LOG_ARCHIVE_DEST_STATE_2=DEFER

*.LOG_FILE_NAME_CONVERT ='/u01/app/oradata/ora235/','/u01/app/oradata/ora235/'

*.FAL_SERVER=tns_standby

*.FAL_CLIENT=tns_primary

*.STANDBY_FILE_MANAGEMENT=AUTO
  • Primary重新启动
primary>create spfile from pfile;

File created.

primary>startup

ORACLE instance started.

Total System Global Area  780824576 bytes

Fixed Size      2257312 bytes

Variable Size    511708768 bytes

Database Buffers   264241152 bytes

Redo Buffers      2617344 bytes

Database mounted.

Database opened.
  • 复制参数文件到Standby库
scp /u01/app/oracle/dbs/initora235.ora oracle@192.168.1.221:/u01/app/oracle/dbs
  • 添加Standby端的pfile内容如下
*.DB_UNIQUE_NAME=db_standby

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(db_primary,db_standby)'

*.LOG_ARCHIVE_DEST_2='SERVICE=tns_primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_primary'

*.LOG_FILE_NAME_CONVERT ='/u01/app/oradata/ora235/','/u01/app/oradata/ora235/'

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.FAL_SERVER=tns_primary

*.FAL_CLIENT=tns_standby

*.STANDBY_FILE_MANAGEMENT=AUTO
  • 将Standby端启动到nomount状态
[oracle@oracle221 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 9 22:56:49 2018

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

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  780824576 bytes

Fixed Size      2257312 bytes

Variable Size    511708768 bytes

Database Buffers   264241152 bytes

Redo Buffers      2617344 bytes

2.7 配置主备库的监听文件和网络文件

  • 设置Primary库和Standby库的service_name
SQL> show parameter service_name;

NAME         TYPE  VALUE

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

service_names        string  lottu

SQL> alter system set service_names='db_primary' scope=both;

System altered.

SQL> show parameter service_name;

NAME         TYPE  VALUE

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

service_names        string  db_primary
  • 备库的service_name设置为db_standby；需要到参数文件修改。
*.service_names='db_standby'
  • Primary库配置listener.ora
LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

       (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.235)(PORT = 1521)(IP = FIRST)))

       (ADDRESS_LIST =

         (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

       )

     )

  )

SID_LIST_LISTENER =

  (SID_LIST =

   (SID_DESC =

     (SID_NAME = ora235)

     (GLOBAL_DBNAME = db_primary)

     (ORACLE_HOME =/u01/app/oracle)

   )

  )
  • Standby库配置listener.ora
LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

       (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521)(IP = FIRST)))

       (ADDRESS_LIST =

         (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

       )

     )

  )

SID_LIST_LISTENER =

  (SID_LIST =

   (SID_DESC =

     (SID_NAME = ora235)

     (GLOBAL_DBNAME = db_standby)

     (ORACLE_HOME =/u01/app/oracle)

   )

  )
  • Primary库和Standby库设置tnsname.ora
tns_primary=

  (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.235)(PORT = 1521))

      (CONNECT_DATA =

  (SERVICE_NAME = db_primary)

      )

     (HS = OK)

  )

tns_standby=

  (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521))

    (CONNECT_DATA =

    (SERVICE_NAME = db_standby)

    )

 (HS = OK)

  )

　　重启监听lsnrctl restart

2.8. 克隆Standby库

在Primary库执行

[oracle@oracle235 admin]$ rman target sys/li0924@tns_primary auxiliary sys/li0924@tns_standby

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Aug 9 23:56:46 2018

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

connected to target database: ORA235 (DBID=2047494122)

connected to auxiliary database: ORA235 (not mounted)

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;

Starting Duplicate Db at 09-AUG-18

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:

{

   backup as copy reuse

   targetfile  '/u01/app/oracle/dbs/orapwora235' auxiliary format

 '/u01/app/oracle/dbs/orapwora235'   ;

}

executing Memory Script

Starting backup at 09-AUG-18

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=26 device type=DISK

Finished backup at 09-AUG-18

contents of Memory Script:

{

   backup as copy current controlfile for standby auxiliary format  '/u01/app/oradata/ora235/control01.ctl';

   restore clone controlfile to  '/u01/app/oradata/ora235/control02.ctl' from

 '/u01/app/oradata/ora235/control01.ctl';

}

executing Memory Script

Starting backup at 09-AUG-18

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/u01/app/oracle/dbs/snapcf_ora235.f tag=TAG20180809T235658 RECID=4 STAMP=983750219

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 09-AUG-18

Starting restore at 09-AUG-18

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 09-AUG-18

contents of Memory Script:

{

   sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

   set newname for tempfile  1 to

 "/u01/app/oradata/ora235/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to

 "/u01/app/oradata/ora235/system01.dbf";

   set newname for datafile  2 to

 "/u01/app/oradata/ora235/sysaux01.dbf";

   set newname for datafile  3 to

 "/u01/app/oradata/ora235/undotbs01.dbf";

   set newname for datafile  4 to

 "/u01/app/oradata/ora235/users01.dbf";

   set newname for datafile  5 to

 "/data/oracle/data/lottu01.dbf";

   backup as copy reuse

   datafile  1 auxiliary format

 "/u01/app/oradata/ora235/system01.dbf"   datafile

 2 auxiliary format

 "/u01/app/oradata/ora235/sysaux01.dbf"   datafile

 3 auxiliary format

 "/u01/app/oradata/ora235/undotbs01.dbf"   datafile

 4 auxiliary format

 "/u01/app/oradata/ora235/users01.dbf"   datafile

 5 auxiliary format

 "/data/oracle/data/lottu01.dbf"   ;

   sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oradata/ora235/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 09-AUG-18

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/data/oracle/data/lottu01.dbf

output file name=/data/oracle/data/lottu01.dbf tag=TAG20180809T235706

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u01/app/oradata/ora235/system01.dbf

output file name=/u01/app/oradata/ora235/system01.dbf tag=TAG20180809T235706

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u01/app/oradata/ora235/sysaux01.dbf

output file name=/u01/app/oradata/ora235/sysaux01.dbf tag=TAG20180809T235706

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u01/app/oradata/ora235/undotbs01.dbf

output file name=/u01/app/oradata/ora235/undotbs01.dbf tag=TAG20180809T235706

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/app/oradata/ora235/users01.dbf

output file name=/u01/app/oradata/ora235/users01.dbf tag=TAG20180809T235706

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 10-AUG-18

sql statement: alter system archive log current

contents of Memory Script:

{

   backup as copy reuse

   archivelog like  "/data/arch/1_3_983660602.dbf" auxiliary format

 "/data/arch/1_3_983660602.dbf"   ;

   catalog clone archivelog  "/data/arch/1_3_983660602.dbf";

   switch clone datafile all;

}

executing Memory Script

Starting backup at 10-AUG-18

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=3 RECID=19 STAMP=983750428

output file name=/data/arch/1_3_983660602.dbf RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01

Finished backup at 10-AUG-18

cataloged archived log

archived log file name=/data/arch/1_3_983660602.dbf RECID=1 STAMP=983750429

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=983750429 file name=/u01/app/oradata/ora235/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=5 STAMP=983750429 file name=/u01/app/oradata/ora235/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=6 STAMP=983750429 file name=/u01/app/oradata/ora235/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=7 STAMP=983750429 file name=/u01/app/oradata/ora235/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=8 STAMP=983750429 file name=/data/oracle/data/lottu01.dbf

contents of Memory Script:

{

   set until scn  955597;

   recover

   standby

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 10-AUG-18

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file /data/arch/1_3_983660602.dbf

archived log file name=/data/arch/1_3_983660602.dbf thread=1 sequence=3

media recovery complete, elapsed time: 00:00:00

Finished recover at 10-AUG-18

Finished Duplicate Db at 10-AUG-18

2.9. 启用日志传送

在 Primary 端启动日志传送

alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;

2.10. 启用实时应用redo

　　在 Primary Database 产生的 Redo 日志；传送到 Standby Database；是通过 LGWR 或者 ARCH 进程完成。这个很好理解。默认情况下是由 ARCH 进程。有参数 *.LOG_ARCHIVE_DEST_2 控制的。使用 ARCH 进程； Primary Database 和 Standby Database 存在数据延迟。若 Primary Database 出现异常；容易造成部分数据丢失。为了避免数据丢失，必须要使用 LGWR 进程，需要用到 Standby Redolog。而LGWR 又分SYNC（同步）和ASYNC（异步）两种方式。

  • SYNC方式：对网络要求比较高；必须等待写入本地日志文件操作和通过LNSn进程的网络传送都成功，Primary Database 上的事务才能提交成功。使用LGWR SYNC方式时，可以同时使用NET_TIMEOUT参数，这个参数单位是秒，代表如果多长时间内网络发送没有响应，LGWR 进程会抛出错误。因此对 Primary Database 性能有影响。
*.LOG_ARCHIVE_DEST_2='SERVICE=tns_standby LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_standby NET_TIMEOUT = 30'
  • ASYNC方式：采用异步方式；Primary Database 上的事务提交跟日志是否已经传送没有影响。
*.LOG_ARCHIVE_DEST_2='SERVICE=tns_standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_standby'

1. 在 Primary Database上执行；

alter system set log_archive_dest_2='SERVICE=tns_standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_standby' scope=both;

2. 在Standby Database上执行；

alter system set log_archive_dest_2='SERVICE=tns_primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_primary' scope=both;

3. Standby Database启动实时应用redo

alter database recover managed standby database using current logfile ;

4. 验证结果

# 在Primary Database 上创建表t_lottu02

SQL> set SQLPROMPT Primary>

Primary>conn lottu/li0924

Connected.

Primary>create table t_lottu02 as select level id,'lottu' name from dual connect by level <= 3;

Table created.

# 在Standby Database上查看

SQL> set SQLPROMPT StandBy>

StandBy>conn lottu/li0924

Connected.

StandBy>select * from t_lottu02;

 ID NAME

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

  1 lottu

  2 lottu

  3 lottu
上一篇：字符串中带有emoji表情处理


下一篇：windows下配置php + mysql环境