Oracle 11g DataGuard搭建

【摘要】

Oracle DataGuard是Oracle自带的数据同步功能，基本原理是将日志文件从原数据库传输到目标数据库，然后在目标数据库上应用这些日志文件，从而使目标数据库与源数据库保持同步，是一种数据库级别的高可用性方案。

DataGuard可以提供Oracle数据库的冗灾、数据保护、故障恢复等，实现数据库快速切换与灾难性恢复。在生产数据库的保证"事务一致性"时，使用生产库的物理全备份创建备库，备库会通过生产库传输过来的归档日志或重做条目自动维护备用数据库。

【正文】

Oracle DataGuard由一个primary数据库(生产数据库)及一个或多个standby数据库(最多9个)组成。组成Data Guard的数据库通过Oracle Net连接，并且有可以分布于不同地域。只要各库之间可以相互通信，它们的物理位置并没有什么限制，不受操作系统的限制。

本次搭建采用一台primary和一台standby来实现，配置如下：

类型

primary

standby

主机IP

192.168.150.5

192.168.150.6

instance_name

ORADB

ORADB

service_name

ORADB

ORADB_dg

主机名

ora

ora_dg

操作系统版本

Oracle Linux Server release 6.8

Oracle版本

11.2.0.4.0

一 主库配置及相关操作
确认主库处于归档模式并开启强制日志模式；

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 10

Next log sequence to archive 12

Current log sequence 12

如果不是归档模式，必须先调整；开启强制日志模式；

SQL> alter database force logging;

Database altered.

1.1 添加备用日志文件standby redo log
备用日志组建议比redo日志多一组；

SQL> alter database add standby logfile group 4 ‘/u01/app/oracle/oradata/ORADB/standby04.log’ size 30m;

Database altered.

SQL> alter database add standby logfile group 5 ‘/u01/app/oracle/oradata/ORADB/standby05.log’ size 30m;

Database altered.

SQL> alter database add standby logfile group 6 ‘/u01/app/oracle/oradata/ORADB/standby06.log’ size 30m;

Database altered.

SQL> alter database add standby logfile group 7 ‘/u01/app/oracle/oradata/ORADB/standby07.log’ size 30m;

Database altered.

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

GROUP# STATUS  TYPE

---

MEMBER

---

     3         ONLINE

/u01/app/oracle/oradata/ORADB/redo03.log

     2         ONLINE

/u01/app/oracle/oradata/ORADB/redo02.log

     1         ONLINE

/u01/app/oracle/oradata/ORADB/redo01.log

GROUP# STATUS  TYPE

---

MEMBER

---

     4         STANDBY

/u01/app/oracle/oradata/ORADB/standby04.log

     5         STANDBY

/u01/app/oracle/oradata/ORADB/standby05.log

     6         STANDBY

/u01/app/oracle/oradata/ORADB/standby06.log

GROUP# STATUS  TYPE

---

MEMBER

---

     7         STANDBY

/u01/app/oracle/oradata/ORADB/standby07.log

7 rows selected.

1.2 创建standby controlfile
创建指定目录及名称；

SQL> alter database create standby controlfile as ‘/home/oracle/control01.ctl’;

Database altered.

复制此参数文件到备库；

[oracle@ora ~]$ scp control01.ctl 192.168.150.6:/u01/app/oracle/

1.3 创建、修改主库初始化参数文件
修改spfile参数，通过以下语句：

SQL> alter system set log_archive_config=‘DG_CONFIG=(ORADB,ORADB_dg)’ scope=both;

System altered.

SQL> alter system set log_archive_dest_2=‘SERVICE=ORADB_dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORADB_dg’ scope=both;

System altered.

SQL> alter system set standby_file_management=AUTO scope=both;

System altered.

SQL> alter system set fal_server=‘ORADB_dg’ scope=both;

System altered.

拷贝pfile参数文件到备库；

[oracle@ora dbs]$ scp initORADB.ora oracle@192.168.150.6:/u01/app/oracle/product/11.2.0/db_1/dbs

1.4 修改主库监听器
编辑目录/u01/app/oracle/product/11.2.0/db_1/network/admin/下面的listener.ora文件，增加以下内容，修改完需要reload listener；

[oracle@ora admin]$ vi listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

  (SID_NAME = ORADB)

  (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

  (PRESPAWN_MAX = 20)

  (PRESPAWN_LIST =

    (PRESPAWN_DESC =

      (PROTOCOL = tcp)

      (POOL_SIZE = 2)

      (TIMEOUT = 1)

    )

  )

)

)

修改完重启监听；

[oracle@ora admin]$ lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 07-JUN-2017 19:50:08

Copyright © 1991, 2013, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora)(PORT=1521)))

The command completed successfully

1.5 修改主库本地NET服务名
修改/u01/app/oracle/product/11.2.0/db_1/network/admin下面的tnsnames.ora文件；

[oracle@ora admin]$ vi tnsnames.ora

ORADB =

(DESCRIPTION =

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

(CONNECT_DATA =

  (SERVER = DEDICATED)

  (SERVICE_NAME = ORADB)

)

)

ORADB_dg =

(DESCRIPTION =

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

(CONNECT_DATA =

  (SERVER = DEDICATED)

  (SERVICE_NAME = ORADB_dg)

)

)

复制此TNS本地服务名文件到备库；

[oracle@ora admin]$ scp tnsnames.ora oracle@192.168.150.6:/u01/app/oracle/product/11.2.0/db_1/network/admin

1.6 复制主库密码文件到备库
[oracle@ora dbs]$ scp orapwORADB oracle@192.168.150.6:/u01/app/oracle/product/11.2.0/db_1/dbs/

二 备库配置及相关操作
2.1 修改初始化参数
修改/u01/app/oracle/product/11.2.0/db_1/dbs/下的初始化参数文件initORADB.ora

修改完的参数文件如下（主要修改路径，内存参数和DG参数）；

[oracle@ora_dg dbs]$ cat initORADB.ora

db_name=‘ORADB’

processes = 150

audit_file_dest=’/u01/app/oracle/admin/ORADB/adump’

audit_trail =‘NONE’

sga_target=680M

pga_aggregate_target=100M

db_block_size=8192

db_domain=’’

db_recovery_file_dest=’/u01/app/oracle/fast_recovery_area’

db_recovery_file_dest_size=2G

db_unique_name=‘ORADB_dg’

diagnostic_dest=’$ORACLE_BASE’

dispatchers=’(PROTOCOL=TCP) (SERVICE=ORADBXDB)’

fal_server=‘ORADB’

log_archive_config=‘DG_CONFIG=(ORADB_dg,ORADB)’

log_archive_dest_1=‘LOCATION=/u01/app/oracle/fast_recovery_area/ORADB/archivelog’

log_archive_dest_2='SERVICE=ORADB LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORADB’

log_archive_format=’%t_%s_%r.dbf’

nls_language=‘ENGLISH’

nls_territory=‘CHINA’

open_cursors=300

processes=1000

remote_login_passwordfile=‘EXCLUSIVE’

sessions=1105

standby_file_management=‘AUTO’

undo_tablespace=‘UNDOTBS1’

You may want to ensure that control files are created on separate physical

devices

control_files = ‘/u01/app/oracle/control01.ctl’

compatible =‘11.2.0.4.0’

2.2 创建目录结构
备库创建跟主库一致的archivelog的目录，并创建参数文件所对应的目录；

[oracle@ora_dg oracle]$ mkdir -p /u01/app/oracle/fast_recovery_area/ORADB/archivelog

[oracle@ora_dg ORADB]$ mkdir -p /u01/app/oracle/admin/ORADB/adump

2.3 修改备库监听器
编辑目录/u01/app/oracle/product/11.2.0/db_1/network/admin/下面的listener.ora文件，增加以下内容，修改完重启listener；

[oracle@ora_dg admin]$ vi listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

  (SID_NAME = ORADB)

  (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

  (PRESPAWN_MAX = 20)

  (PRESPAWN_LIST =

    (PRESPAWN_DESC =

      (PROTOCOL = tcp)

      (POOL_SIZE = 2)

      (TIMEOUT = 1)

    )

  )

)

)

重启监听；

[oracle@ora_dg admin]$ lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 07-JUN-2017 23:48:51

Copyright © 1991, 2013, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora_dg)(PORT=1521)))

The command completed successfully

2.4 主备库上测试tns联通性
[oracle@ora_dg admin]$ tnsping ORADB

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 07-JUN-2017 23:50:41

Copyright © 1997, 2013, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.150.5)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORADB)))

OK (10 msec)

[oracle@ora_dg admin]$ tnsping ORADB_dg

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 07-JUN-2017 23:50:51

Copyright © 1997, 2013, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.150.6)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORADB_dg)))

OK (0 msec)

三 恢复操作
3.1 挂载数据库（备库）
SQL> startup nomount;

ORACLE instance started.

Total System Global Area 709836800 bytes

Fixed Size 2256632 bytes

Variable Size 603980040 bytes

Database Buffers 100663296 bytes

Redo Buffers 2936832 bytes

SQL> alter database mount;

Database altered.

SQL> create spfile from pfile;

File created.

3.2 还原数据库
备库上操作，事先将主库的数据文件和控制文件备份并复制过来；

[oracle@ora_dg ss]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jun 8 03:47:53 2017

Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORADB (DBID=2699311109, not open)

RMAN> catalog start with ‘/ss/aa’;

using target database control file instead of recovery catalog

searching for all files that match the pattern /ss/aa

List of Files Unknown to the Database

=====================================

File Name: /ss/aa/db_03s68dph_1_1

File Name: /ss/aa/db_04s68dqa_1_1

File Name: /ss/aa/db_05s68dqd_1_1

File Name: /ss/aa/db_02s68dpg_1_1

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files…

cataloging done

List of Cataloged Files

=======================

File Name: /ss/aa/db_03s68dph_1_1

File Name: /ss/aa/db_04s68dqa_1_1

File Name: /ss/aa/db_05s68dqd_1_1

File Name: /ss/aa/db_02s68dpg_1_1

RMAN> restore database;

Starting restore at 08-JUN-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=25 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORADB/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ORADB/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORADB/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORADB/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORADB/example01.dbf

channel ORA_DISK_1: reading from backup piece /ss/aa/db_03s68dph_1_1

channel ORA_DISK_1: piece handle=/ss/aa/db_03s68dph_1_1 tag=TAG20170608T031705

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:25

Finished restore at 08-JUN-17

到这里，数据已经还原到备库上，这里不需要recover，直接启动同步。

四 开启重做日志应用
4.1 备库开启应用重做日志
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

4.2 检查同步情况
SQL> select * from v$archive_gap;

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#

---

     1             7              9

SQL> select process, client_process, sequence#, status from v$managed_standby;

PROCESS CLIENT_P SEQUENCE# STATUS

---

ARCH ARCH 0 CONNECTED

ARCH ARCH 0 CONNECTED

ARCH ARCH 0 CONNECTED

ARCH ARCH 0 CONNECTED

MRP0 N/A 7 WAIT_FOR_LOG

RFS ARCH 0 IDLE

RFS LGWR 16 IDLE

RFS UNKNOWN 0 IDLE

RFS UNKNOWN 0 IDLE

9 rows selected.

4.3 对比主备两边日志序列号
SQL> select sequence#, first_time, next_time, applied from v$archived_log;

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

---

    10 07-JUN-17 07-JUN-17 NO

    11 07-JUN-17 07-JUN-17 NO

    12 07-JUN-17 08-JUN-17 NO

    13 08-JUN-17 08-JUN-17 NO

    14 08-JUN-17 08-JUN-17 NO

    15 08-JUN-17 08-JUN-17 NO

    15 08-JUN-17 08-JUN-17 NO

    10 07-JUN-17 07-JUN-17 NO

8 rows selected.

确认主备一致，dataguard搭建完成，主库与备库实现数据同步。转自 http://www.learnfuture.com/article/1711