ITPub博客

首页 > 数据库 > Oracle > 【Data Guard】Oracle DataGuard 搭建

【Data Guard】Oracle DataGuard 搭建

原创 Oracle 作者:恩强Boy 时间:2018-07-12 09:33:35 0 删除 编辑

DataGuard 搭建


准备工作

 

1. 规划


主库

备库

ip

192.168.131.100

192.168.131.101

instance_name

orcl1

o rcl 1

service_names

db01

db02

db_unique_name

db01

db02

H ostName

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 文件 配置( 主备库

 

R oot 用户(主备库 相同

[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

 

配置 l intener  & tnsnames (主备库)

O racle 用户

 

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 = orcl 1 )

        )

)

2 、修改 tnsname s .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 = orcl 1 )

    )

  )

 

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 )开启 数据库

st artup( 使新 参数生效 )

 

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 initorcl 1 .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 修改成 ma nual

 

* ***************

3 )生成 spfile (shutdown 状态 )

create spfile from pfile =' /u01/app/oracle/product/11.2.0/db_1/dbs/init orcl1 .ora' ;

 

4 )开启 数据库

st artup( 使新 参数生效 )

 

复制 数据库

 

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;


此时 ,已经完成了 D ataGuard 搭建 部分

 

、查询主备库角色

 

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

 

- ------------ end ------------


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31529886/viewspace-2157710/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2018-04-03

  • 博文量
    59
  • 访问量
    23398