如何搭建Oracle DataGuard
这篇文章主要讲解了"如何搭建Oracle DataGuard"，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"如何搭建Oracle DataGuard"吧！
一、准备工作
1.规划
主库 | 备库 | |
ip | 192.168.131.100 | 192.168.131.101 |
instance_name | orcl1 | orcl1 |
service_names | db01 | db02 |
db_unique_name | db01 | db02 |
HostName | primary | standby |
OS版本 | Oracle linux 6.4 | Oracle linux 6.4 |
DB版本 | 11.2.0.4 | 11.2.0.4 |
2.关闭防火墙
service iptables stop
chkconfig iptables off
3.禁用selinux防火墙
vi /etc/selinux/config
selinux=disabled
二、开启归档模式（主备库）
1、创建归档目录
[oracle@primary ~]# mkdir -p /u01/app/oracle/archivelog
2、开启归档模式
（1）数据库到mount状态开启归档模式
[root@primary ~]# su - oracle
[oracle@primary ~]$ sqlplus / as sysdba
SQL> startup mount;
ORACLE instance started.
Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 507514504 bytes
Database Buffers 264241152 bytes
Redo Buffers 2633728 bytes
Database mounted.
（2）设置主库归档目录
SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/archivelog';
（3）开启归档模式
SQL> alter database archivelog;
Database altered.
（4）查看归档设置
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archivelog
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
（5） 开启数据库
SQL> alter database open;
Database altered.
三、强制主库为force logging模式（主库）
SQL> select force_logging from v$database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FOR
---
YES
四、HOST文件配置（主备库）
Root用户（主备库相同）
[root@primary ~]# vi /etc/hosts
127.0.0.1 localhost localhost.localdomain
#Primary database IP
192.168.131.100 primary
#Standby database IP
192.168.131.101 standby
五、配置lintener & tnsnames（主备库）
Oracle用户
1、主备库监听配置
（1）主库监听配置
[oracle@primary ~]$ vi $ORACLE_HOME/network/admin/listener.ora
（添加以下内容）
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl1)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl1)
)
)
（2）备库监听配置
[oracle@primary ~]$ vi $ORACLE_HOME/network/admin/listener.ora
（添加以下内容）
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl1)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl1)
)
)
2、修改tnsnames.ora文件（主备库）
（1）主库修改
[oracle@primary ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
db01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl1) ##此处service_name应和上面GLOBAL_NAME值相等
)
)
db02 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl1)
)
)
（2）备库修改
[oracle@primary ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
db01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl1)
)
)
db02 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl1)
(UR=A)
)
)
（3）主备库检测(主备库)
[oracle@primary admin]$ tnsping db01
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 11-JUL-2018 08:48:56
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl1)))
OK (10 msec)
[oracle@primary admin]$ tnsping db02
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 11-JUL-2018 08:49:01
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl1) (UR=A)))
OK (10 msec)
六、修改参数文件（主备库）
1、主库修改参数
（1）生成参数文件
SQL> create pfile from spfile;
File created.
（2）修改参数文件
[oracle@primary ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@primary dbs]$ vi initorcl1.ora
添加以下内容：
db_unique_name=db01
log_archive_config='dg_config=(db01,db02)'
log_archive_dest_1='location=/u01/app/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=db01'
log_archive_dest_2='service=db02 lgwr async valid_for=(online_logfiles,primary_roles) db_unique_name=db02'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
db_file_name_convert='/u01/app/oracle/oradata/orcl2','/u01/app/oracle/oradata/orcl1'
log_file_name_convert='/u01/app/oracle/oradata/orcl2','/u01/app/oracle/oradata/orcl1'
fal_server=db02
fal_client=db01
standby_file_management=auto
（3）生成spfile（shutdown状态）
create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl1.ora';
（4）开启数据库
startup(使新参数生效)
2、备库修改参数
（1）生成参数文件
SQL> create pfile from spfile;
File created.
（2）修改参数文件
[oracle@primary ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@primary dbs]$ vi initorcl1.ora
添加以下内容：
db_unique_name=db02
log_archive_config='dg_config=(db01,db02)'
log_archive_dest_1='location=/u01/app/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=db02'
log_archive_dest_2='service=db01 lgwr async valid_for=(online_logfiles,primary_roles) db_unique_name=db01'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
db_file_name_convert='/u01/app/oracle/oradata/orcl1','/u01/app/oracle/oradata/orcl2'
log_file_name_convert='/u01/app/oracle/oradata/orcl1','/u01/app/oracle/oradata/orcl2'
fal_server=db01
fal_client=db02
standby_file_management=auto // 如果要修改备库日志文件的大小，需要将此处 auto修改成manual
****************
（3）生成spfile(shutdown状态)
create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl1.ora';
（4）开启数据库
startup(使新参数生效)
七、复制数据库
1、备库开启到nomount模式
SQL> shutdown immediate;
SQL> startup nomount;
2、RMAN进行复制（主库）
[oracle@primary ~]$ rman target sys/oracle@db01 auxiliary sys/oracle@db02
RMAN>duplicate target database for standby nofilenamecheck from active database;
此时，已经完成了DataGuard搭建部分！
八、查询主备库角色
1、主库查询
SQL> col db_unique_name for a15
SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------------- -------------------- ---------------- --------------------
db01 READ WRITE PRIMARY SESSIONS ACTIVE
2、备库查询
SQL> col db_unique_name for a15
SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------------- -------------------- ---------------- --------------------
db02 READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
3.参数设置检查
SQL> col name for a25
SQL> col value for a30
SQL> select a.name, a.value from v$parameter a where a.name like '%file_name_convert' or a.name like '%fal%' or a.name like 'standby_file%';
NAME VALUE
------------------------- ------------------------------
db_file_name_convert /u01/app/oracle/oradata/orcl2,
/u01/app/oracle/oradata/orcl1
log_file_name_convert /u01/app/oracle/oradata/orcl2,
/u01/app/oracle/oradata/orcl1
fal_client db01
fal_server db02
standby_file_management auto
九、备库添加standby 日志组（备库）
1、查看数据库日志组
查看数据库的日志组个数与大小，因为我们创建 standby 日志组的个数是原日志
组个数+1 再与 thread 的积((1)*3)，size 不能小于原日志文件的大小。
SQL> select group#,thread#,bytes/1024/1024 M,status from v$log;
GROUP# THREAD# M STATUS
---------- ---------- ---------- ----------------
1 1 50 UNUSED
2 1 50 CLEARING
3 1 50 CURRENT
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl2/redo03.log
/u01/app/oracle/oradata/orcl2/redo02.log
/u01/app/oracle/oradata/orcl2/redo01.log
2、新建备库日志组
SQL> alter database add standby logfile thread 1 group 4
2 ('/u01/app/oracle/oradata/orcl2/redo04.log') size 50M;
Database altered.
SQL> alter database add standby logfile thread 1 group 5
2 ('/u01/app/oracle/oradata/orcl2/redo05.log') size 50M;
Database altered.
SQL> alter database add standby logfile thread 1 group 6
2 ('/u01/app/oracle/oradata/orcl2/redo06.log') size 50M;
Database altered.
SQL> alter database add standby logfile thread 1 group 7
2 ('/u01/app/oracle/oradata/orcl2/redo07.log') size 50M;
Database altered.
3、查看日志组状态
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/orcl2/redo03.log
2 ONLINE /u01/app/oracle/oradata/orcl2/redo02.log
1 ONLINE /u01/app/oracle/oradata/orcl2/redo01.log
4 STANDBY /u01/app/oracle/oradata/orcl2/redo04.log
5 STANDBY /u01/app/oracle/oradata/orcl2/redo05.log
6 STANDBY /u01/app/oracle/oradata/orcl2/redo06.log
7 STANDBY /u01/app/oracle/oradata/orcl2/redo07.log
十、备库应用日志
SQL> alter database recover managed standby database using current logfile disconnect from session;
十一、验证数据库操作
1、在主库创建用户
SQL> create user test identified by test;
User created.
SQL> alter system switch logfile;
System altered.
2、在备库查看
SQL> select username from dba_users where username='TEST';
USERNAME
------------------------------
TEST
感谢各位的阅读，以上就是"如何搭建Oracle DataGuard"的内容了，经过本文的学习后，相信大家对如何搭建Oracle DataGuard这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！
