【摘要】
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