共计 8288 个字符,预计需要花费 21 分钟才能阅读完成。
这篇文章主要讲解了“如何搭建 Oracle DataGuard”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着丸趣 TV 小编的思路慢慢深入,一起来研究和学习“如何搭建 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 这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是丸趣 TV,丸趣 TV 小编将为大家推送更多相关知识点的文章,欢迎关注!