如何搭建Oracle DataGuard

56次阅读
没有评论

共计 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 小编将为大家推送更多相关知识点的文章,欢迎关注!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-20发表,共计8288字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)