（一）DataGuard概要
DataGuard中文称为”数据卫士“，提供了数据库高可用性、数据保护和灾难恢复的功能。DataGuard通过建立primary数据库和standby数据库来确立参照关系，DataGuard将主库（primary）的redo日志传递给备库（standby），然后在备库中应用redo进行同步。
备库又分为2种类型：物理备库和逻辑备库
- 物理standby是通过块拷贝方式同步，通过接受并应用primary数据库的redo log，以介质恢复的方式同步。在物理备库中，数据是完全相同的，包括schema、表、索引都是一样的。
- 逻辑standby是通过应用sql语句进行同步，通过接收primary数据库的redo log并转换成sql语句。 @H_404_13@
- standby日志组个数：配置为redo日志组个数+1
- 在主库与备库都添加standby日志组。主库可以不添加，但是如果后期发生主备切换，还是要添加，所以最好一次性添加。
- 只查询standby日志组： select * from v$standby_log ; @H_404_13@
物理备库在实际生产中使用较多，这里记录物理备库的搭建过程。
（二）DataGuard环境规划
| 主库 | 备库 | |
| 操作系统 | redhat 6.7 x86-64(64位) | redhat 6.7 x86-64(64位) |
| 服务器名称 | primarynode | standbynode |
| IP地址规划 | 192.168.10.51 | 192.168.10.52 |
| --------------- | ----------------------- | ---------------- |
| 数据库版本 | 11.2.0.4 | 11.2.0.4 |
| db_name | adgdb | adgdb |
| db_unique_name | adgdb | adgdbstandby |
| instance_name | adgdb | adgdbstandby |
| service_name | adgdb | adgdb |
| 数据库安装 | 安装数据库软件+创建监听+安装数据库 | 安装数据库软件+创建监听 |
常规配置：
( 1 )主库和备库hosts文件配置
[root@primarynode ~]# vim /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4
::1 localhost localhost.localdomain localhost6
192.168.10.51 primarynode
192.168.10.52 standbynode
（2）主库oracle用户环境配置
[oracle@primarynode ~]$ more .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/bin ORACLE_SID=adgdb; export ORACLE_SID ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin; export PATH export TEMP=/tmp export TMPDIR=/tmp umask 022 export PATH
（3）备库环境变量配置
[oracle@standbynode ~]$ adgdbstandby; export ORACLE_SID ORACLE_BASE=/u01/app/ export PATH
(三)DataGuard主库配置
( 1 )主库启用强制记录日志功能
查询是否启用强制记录日志
select force_logging from v$database;
如果未启用，则使用下面语句来开启强制记录日志
alter database force logging; --强制记录日志
( 2 )启用归档
查询是否启用归档
archive log list 或者 select log_mode database;
如果未启用归档，可以按照如下步骤开启归档
--Oracle 11g数据库归档模式开启
STEP1：以sysdba角色登陆数据库
sqlplus / as sysdba
STEP2：干净的关闭数据库
shutdown immediate
STEP3：将数据库启动到mount状态
startup mount
STEP4：开启归档
database archivelog;
STEP5：打开数据库
database open
( 3 )主库参数配置
(3.1)db_unique_name
sql> alter system set db_unique_name = 'adgdb' scope=spfile;
(3.2)log_archive_config
sql> set log_archive_config=DG_CONFIG=(adgdb,adgdbstandby)=spfile;
(3.3)log_archive_dest_1
set log_archive_dest_1LOCATION=/u01/app/oracle/product/11.2.0/db_1/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=adgdb=both;
(3.4)log_archive_dest_2
set log_archive_dest_2SERVICE=tnsadgdbstandby LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=adgdbstandby=spfile;
(3.5)log_archive_dest_state_1
set log_archive_dest_state_1 = ENABLE;
(3.6)log_archive_dest_state_2
set log_archive_dest_state_2 = ENABLE;
(3.7)db_file_name_convert
查看数据文件的位置：
select name from v$datafile;
NAME
------------------------------------------------
/u01/app/oracle/oradata/adgdb/system01.dbf
sysaux01.dbf
undotbs01.dbf
users01.dbf
/example01.dbf
如果主库与备库数据文件位置不相同，则需要使用db_file_name_convert来转换。
set db_file_name_convert/u01/app/oracle/oradata/adgdbstandby',/u01/app/oracle/oradata/adgdb=spfile;
(3.8)log_file_name_convert
查看在线日志文件的位置：
select member v$logfile;
MEMBER
/redo03.log
/redo02./redo01.log
如果主库与备库在线日志文件位置不相同，则需要使用log_file_name_convert来转换。
set log_file_name_convert=spfile;
(3.9)standby_file_management
> alter system set standby_file_management=auto scope=spfile;
(3.10)fal_client
set fal_clienttnsadgdb=both;
(3.11)fal_server
set fal_servertnsadgdbstandby=both;
( 4 )主库静态监听配置
[oracle@primarynode admin]$ vim listener.ora
# listener.ora Network Configuration File: /u01/oracle/product/0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = primarynode)(PORT = 1521)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME=adgdb) (SID_NAME = adgdb) (ORACLE_HOME = /u01/app/oracle/product/db_1) ) ) ADR_BASE_LISTENER = /u01/oracle
查看监听：
[oracle@primarynode admin]$ lsnrctl status
LSNRCTL for Linux: Version 0.4.0 - Production on 06-APR-2019 13:48:15
Copyright (c) 1991,2013,Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=primarynode)(PORT=)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR 0 - Production
Start Date 42:48
Uptime 0 days 0 hr. 5 min. 27 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/listener.ora
Listener Log File /u01/app/oracle/product/0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primarynode)(PORT=)))
Services Summary...
Service "adgdb" has 2 instance(s).
Instance ",status UNKNOWN,has 1 handler(s) for this service...
Instance this service...
Service adgdbXDB1 this service...
The command completed successfully
( 5 )主库tnsnames.ora文件配置
[oracle@primarynode admin]$ vim tnsnames.ora
tnsadgdb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.51)(PORT = )) ) (CONNECT_DATA = (SERVICE_NAME = adgdb) ) ) tnsadgdbstandby =10.52)(PORT = adgdb) ) )
(四)DataGuard备库配置
（ 1 ）密码文件
在dataguard中，主库与备库sys密码需一致。可以将主库的密码文件拷贝到备库中
--登陆到主库，将密码文件复制到备库 [oracle@primarynode ~]$ cd $ORACLE_HOME/dbs [oracle@primarynode dbs]$ scp orapwadgdb 10.52:/u01/app/oracle/product/0/db_1/dbs oracle@10.52's password: orapwadgdb 100% 1536 1.5KB/s 00:00 --登陆到备库，修改密码文件的名称 [oracle@standbynode dbs]$ mv orapwadgdb orapwadgdbstandby
或者不拷贝密码文件，直接使用orapwd生成一个新的密码文件，密码需与主库一致：
orapwd file=orapwadgdbstandby password=oracle';
（ 2 ）初始化参数文件
create pfile spfile;
File created.
在主库上执行复制操作
[oracle@primarynode dbs]$ scp initadgdb.ora s password:
initadgdb.ora 1524 00
--在备库上执行重命名操作
[oracle@standbynode dbs]$ mv initadgdb.ora initadgdbstandby.ora
将初始化参数中的这些参数做修改，其他参数不用动
db_name='
db_unique_name=adgdbstandby
audit_file_dest=/u01/app/oracle/admin/adgdbstandby/adump
compatible=11.2.0.4.0
control_files=/u01/app/oracle/oradata/adgdbstandby/control01.ctl/u01/app/oracle/oradata/adbdbstandby/control02.ctl
log_archive_config=
log_archive_dest_1=
log_archive_dest_2=SERVICE=tnsadgdb LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=adgdb
db_file_name_convert=/u01/app/oracle/oradata/adgdb/u01/app/oracle/oradata/adgdbstandby
log_file_name_convert=
fal_client=tnsadgdbstandby
fal_server=tnsadgdb
standby_file_management=AUTO'
注意：里面涉及到路径的需要手动创建
mkdir -p /u01/app/oracle/admin/adgdbstandby/adump
登陆到idle数据库
sqlplus 创建spfile，可在$ORACLE_HOME/dbs下查看
create spfile pfile
启动数据库到nomount状态
startup nomount
( 3 )配置静态监听
[oracle@standbynode trace]$ cd /product/11.2.0/db_1/network/admin/ oracle@standbynode admin]$ touch listener.ora $ vim listener.ora # listener.ora Network Configuration File: listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = standbynode)(PORT = )) ) ) ) SID_LIST_LISTENER (SID_LIST (SID_DESC (GLOBAL_DBNAMEadgdb) (SID_NAME adgdbstandby) (ORACLE_HOME = db_1) ) ) ADR_BASE_LISTENER /oracle
备注：备库为什么一定需要使用静态监听
nomount状态下必须使用静态监听才能连接到实例。
（ 4 ）配置tnsnames.ora文件，直接把主库的拷贝过来即可
/
$ vim tnsnames.ora
tnsadgdb
(DESCRIPTION
(ADDRESS_LIST
(ADDRESS 192.168.10.51)(PORT ))
)
(CONNECT_DATA
(SERVICE_NAME adgdb)
)
)
tnsadgdbstandby 10.52)(PORT adgdb)
)
)
（ 5 ）测试网络连通性
在主库与备库上均执行，确保可以正常访问：
tnsping tnsadgdb
tnsping tnsadgdbstandby
在主库：
（ 6 ）参数检查
由于参数配置的较多，需要认真检查参数配置是否正确，主要检查这些参数
1.db_unique_name ：2个节点需要不一样
2.compatible ：主库与备库兼容性需一致
3.log_archive_config : 配置主库与备库的db_unique_name
4.log_archive_dest_1,2 ：归档日志的路径
5.log_archive_dest_state_2 :
enable -- 启用log_archive_dest_2
defer --禁用log_archive_dest_2
6.db_file_name_convert ：数据文件转换路径
7.log_file_name_convert ：日志文件转换路径
8.standby_file_management ：设置为auto
9.log_archive_format ：日志文件格式，两边需一致
（ 7 ）使用duplicate创建物理standby
（7.1）连接到主库和备库
## 一定要加nocatalog，否则在执行duplicate时会报错
[oracle@primarynode ~]$ rman target sys/oracle@tnsadgdb auxiliary sys/oracle@tnsadgdbstandby nocatalog
（7.2）使用duplicate复制数据库
RMAN> duplicate target for standby from active database nofilenamecheck;
执行过程见：
RMAN> duplicate target database standby from active database nofilenamecheck; Starting Duplicate Db at 19 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=248 device type=DISK contents of Memory Script: { backup as copy reuse targetfile /u01/app/oracle/product/11.2.0/db_1/dbs/orapwadgdb auxiliary format /u01/app/oracle/product/11.2.0/db_1/dbs/orapwadgdbstandby ; } executing Memory Script Starting backup at allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=23 device type=DISK Finished backup at contents of Memory Script: { ctl; restore clone controlfile to /u01/app/oracle/oradata/adgdbstandby/control02.ctl from ; } executing Memory Script Starting backup at using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy copying standby control file 183 channel ORA_DISK_1: datafile copy complete,elapsed time: 01 Finished backup at Starting restore at using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: copied control file copy Finished restore at 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 to /u01/app/oracle/oradata/adgdbstandby/temp01.dbf"; switch clone tempfile all; set newname for datafile /u01/app/oracle/oradata/adgdbstandby/system01.dbf; set newname /u01/app/oracle/oradata/adgdbstandby/sysaux01.dbf3/u01/app/oracle/oradata/adgdbstandby/undotbs01.dbf4/u01/app/oracle/oradata/adgdbstandby/users01.dbf5/u01/app/oracle/oradata/adgdbstandby/example01.dbf; backup as copy reuse datafile datafile ; sql alter system archive log current; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/oradata/adgdbstandby/temp01.dbf in control executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/adgdb/system01.dbf output file name=/u01/app/oracle/oradata/adgdbstandby/system01.dbf tag=TAG20190406T160954 channel ORA_DISK_1: datafile copy complete,1)">45 channel ORA_DISK_1: starting datafile copy input datafile 00002 name=/u01/app/oracle/oradata/adgdb/sysaux01.dbf output file name=/u01/app/oracle/oradata/adgdbstandby/sysaux01.dbf tag=3600005 name=/u01/app/oracle/oradata/adgdb/example01.dbf output file name=/u01/app/oracle/oradata/adgdbstandby/example01.dbf tag=2500003 name=/u01/app/oracle/oradata/adgdb/undotbs01.dbf output file name=/u01/app/oracle/oradata/adgdbstandby/undotbs01.dbf tag=0700004 name=/u01/app/oracle/oradata/adgdb/users01.dbf output file name=/u01/app/oracle/oradata/adgdbstandby/users01.dbf tag= sql statement: alter system archive log current contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile switched to datafile copy input datafile copy RECID=9 STAMP=1004890308 file name=/u01/app/oracle/oradata/adgdbstandby/system01.dbf datafile 10 STAMP=sysaux01.dbf datafile 11 STAMP=undotbs01.dbf datafile 12 STAMP=users01.dbf datafile 13 STAMP=example01.dbf Finished Duplicate Db at 19
（ 8 ）添加主库和备库的standby日志组
（8.1）添加standby日志组需要注意的事项
（8.2）添加主库的standby日志组
add standby logfile group 4 (/u01/app/oracle/oradata/adgdb/stredo04.log) size 50M; 5 (/u01/app/oracle/oradata/adgdb/stredo05.log6 (/u01/app/oracle/oradata/adgdb/stredo06.log7 (/u01/app/oracle/oradata/adgdb/stredo07.log') size 50M;
（8.3）添加备库的standby日志组
/u01/app/oracle/oradata/adgdbstandby/stredo04.log/u01/app/oracle/oradata/adgdbstandby/stredo05.log/u01/app/oracle/oradata/adgdbstandby/stredo06.log/u01/app/oracle/oradata/adgdbstandby/stredo07.log') size 50M;
（ 9 ）开始同步数据库，在备库上执行
open; 方式一：开启实时同步 database recover managed standby database using current logfile disconnect session; 或简写为： current logfile disconnect; 方式二：开启同步（日志切换时才同步） database disconnect from session;
至此配置完成。
（五）DataGuard状态查看
（ 1 ）主库状态查看
sqlselect open_mode,数据库打开模式，如果实时同步，则为：read only with apply，取消同步则为：read only database_role,1)">数据库角色，是主库还是备库 protection_mode,1)">保护模式 protection_level 保护级别 from v$; OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL ------------------ ---------------- -------------------- -------------------- READ WRITE PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
（ 2 ）备库状态查看
sqlselect open_mode,database_role,protection_mode,protection_level database;
OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
（六）DataGuard测试
在主库上创建表lijiaman.test01,并插入数据
sqlcreate table test01(id number,name varchar2(50)); Table created. sqlinsert into test01 values(1,1)">lijiaman); 1 row created. sql2,1)">gegemancommit;
在standby数据库上查询lijiaman.test01表，数据一致
sqlselect db_unique_name ; DB_UNIQUE_NAME ---------------------------- adgdbstandby sql lijiaman.test01; ID NAME -------- -------------------- lijiaman 2 gegeman
【完】
