Oracle 11G DG Broker搭建Data Guard

最新推荐文章于 2023-05-07 17:51:24 发布

zuozhiji

最新推荐文章于 2023-05-07 17:51:24 发布

阅读量369

点赞数

分类专栏： ORACLE 11G DG

本文链接：https://blog.csdn.net/jolly10/article/details/103479514

版权

ORACLE 11G 同时被 2 个专栏收录

43 篇文章 1 订阅

订阅专栏

17 篇文章 0 订阅

订阅专栏

1.环境如下：

项目列表	primary库	standby库
操作系统：	rhel6.3	rhel6.3
oracle软件版本：	oracle 11.2.0.3	oracle 11.2.0.3
IP地址：	172.17.61.131	172.17.61.132
db_unique_name	db131	db132
oracle_sid,db_name	orcl	orcl

2.提前工作：（主从）

　　a.关闭防火墙：/etc/init.d/iptables stop

　　b.关闭selinux：setenforce 0

查看状态：getenforce（关闭状态：Permissive）

3.主库设置：

　　a.在一个dg环境中，db_name,oracle_sid必须是一样的，db_unique_name用来区别dg环境中的不同库，将主库的db_unique_name设置为db131:

SQL> alter system set db_unique_name='db131' scope=spfile;

System altered.

　　b.设置主库为强制记录日志：

SQL> alter database force logging;

SQL> select name,force_logging from v$database;

NAME      FOR
--------- ---
ORCL      YES

c.确认开启归档

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6

d.设置standy_file_management：

SQL> alter system set standby_file_management ='AUTO';

System altered.

e.设置归档路径：


[oracle@qht131 dbs]$ mkdir -p /u01/app/oracle/orcl/flash_recovery_area
[oracle@qht131 dbs]$ mkdir -p /u01/app/oracle/orcl/archivelog

SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/orcl/archivelog';

System altered.

f.创建standby log:

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  /u01/oradata/orcl/redo03.log                       NO
         2         ONLINE  /u01/oradata/orcl/redo02.log                       NO
         1         ONLINE  /u01/oradata/orcl/redo01.log                       NO

SQL> select group#,status,bytes,blocksize,status from v$log;

    GROUP# STATUS                BYTES  BLOCKSIZE STATUS
---------- ---------------- ---------- ---------- ----------------
         1 INACTIVE           52428800        512 INACTIVE
         2 INACTIVE           52428800        512 INACTIVE
         3 CURRENT            52428800        512 CURRENT

SQL>  alter database add standby logfile group 4 '/u01/oradata/orcl/standbyredo04.log' size 50m;

Database altered.

SQL> alter database add standby logfile group 5  '/u01/oradata/orcl/standbyredo05.log' size 50m;

Database altered.

SQL> alter database add standby logfile group 6 '/u01/oradata/orcl/standbyredo06.log' size 50m;

Database altered.

SQL> alter database add standby logfile group 7 '/u01/oradata/orcl/standbyredo07.log' size 50m;

Database altered.

g.开启DGbroker：

SQL> alter system set DG_BROKER_START=TRUE;

System altered.

h.配置网络监听（备库做相应修改）

[oracle@qht131 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/u01/network/admin/listener.ora
# Generated by Oracle configuration tools.

INBOUND_CONNECT_TIMEOUT_LISTENER=0

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11203)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11203)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11203)
      (SID_NAME = orcl)
    (SID_DESC =
      (GLOBAL_DBNAME = db131)
      (ORACLE_HOME = /u01/app/oracle/product/11203)
      (SID_NAME = orcl)
    )
    )
    (SID_DESC =
      (GLOBAL_DBNAME = db131_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11203)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.61.131)(PORT = 1521))
  )


[oracle@qht131 admin]$ cat tnsnames.ora
ORCL131 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.61.131)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )


ORCL132 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.61.132)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

设置完成后用tnsping测试一下互通。

i.修改local_listener为本地的监听名字

SQL> alter system set local_listener=orcl131 scope=both;

System altered.

4.复制参数文件和密码文件到备库

SQL> create pfile='/u01/app/oracle/product/11203/dbs/initorcl.ora' from spfile;

File created.


[oracle@qht131 dbs]$ scp initorcl.ora orapworcl 172.17.61.132:/u01/app/oracle/product/11203/dbs

修改备库参数：

*.db_unique_name='db132'

*.local_listener='ORCL132'

如果主备使用了不同的文件路径则需要修改DB_FILE_NAME_CONVERT，LOG_FILE_NAME_CONVERT

5.备份主库：

备份之前需要重启一下，让前面修改的参数生效

SQL> shutdown immediate;

SQL> startup

[oracle@qht131 RMAN0]$ cat backup_full0.rcv
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/RMAN0/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
run {
 crosscheck archivelog all;
 delete expired archivelog all;
 crosscheck backup;
 delete expired backup;
 allocate channel c1 type disk;
 backup incremental level 0 database format '/u01/RMAN0/db0%u_%s_%p.bak' filesperset 3 include current controlfile;
 backup spfile tag='spfile' format='/u01/RMAN0/ORCL_spfile_%U_%T';
 sql 'alter system archive log current';
 backup filesperset 1 format '/u01/RMAN0/arch%u_%s_%p.bak'
 archivelog all delete input;
 release channel c1;
}
exit;

[oracle@qht131 RMAN0]$ cat rman0.sh
#!/bin/bash

. /home/oracle/.bash_profile
date=`/bin/date +%Y%m%d`
rman target / cmdfile=/u01/RMAN0/backup_full0.rcv log=/u01/RMAN0/rman0_${date}.log

[oracle@qht131 RMAN0]$ ./rman0.sh

6.在备库上克隆主库：

a.开启数据库到nomount

SQL> startup nomount;

b.通过rman克隆数据库：

[oracle@qht132 dbs]$ rman target sys/sys@orcl131 auxiliary sys/sys@orcl132 nocatalog


RMAN> duplicate target database for standby nofilenamecheck from active database;

7.开始配置DGbroker：

主库上开启dgmgrl，创建基础配置，添加主备库的配置信息。

[oracle@qht131 RMAN0]$ dgmgrl /

DGMGRL> create configuration dg_test11 as primary database is db131 connect identifier is orcl131;
Configuration "dg_test11" created with primary database "db131"

--语句含义：第一个dg_test11是配置的名称,这里可以随便填.PRIMARY DATABASE IS 'db131 ' ,这儿的db131 是指database的db_unique_name,而connect identifier is ‘orcl131′这里的orcl131是指tnsname.ora连接到主库的net service name.

DGMGRL> add database db132 as connect identifier is orcl132 maintained as physical;
Database "db132" added
--add database 'db132 ' ,这里的db132 是指database的db_unique_name,而as connect identifier is orcl132 这里的orcl132 是指tnsname.ora连接到standby database的net service name.

这时配置还没有生效：

DGMGRL> show configuration;

Configuration - dg_test11

  Protection Mode: MaxPerformance
  Databases:
    db131 - Primary database
    db132 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

启用配置：

DGMGRL> enable configuration;
Enabled.


DGMGRL> show configuration;

Configuration - dg_test11

  Protection Mode: MaxPerformance
  Databases:
    db131 - Primary database
    db132 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

8.备库使用Active Data Gurad特性

SQL> alter database open;
Database altered.

--查看一下备库当前的log file，都自动建立到参数db_recovery_file_dest指定的位置了。
SQL> select  * from v$logfile;

GROUP# STATUS  TYPE    MEMBER                                                                           IS_
------ ------- ------- -------------------------------------------------------------------------------- ---
     3         ONLINE  /u01/app/oracle/orcl/flash_recovery_area/DB132/onlinelog/o1_mf_3_gz145m22_.log   YES
     2         ONLINE  /u01/app/oracle/orcl/flash_recovery_area/DB132/onlinelog/o1_mf_2_gz145l2o_.log   YES
     1         ONLINE  /u01/app/oracle/orcl/flash_recovery_area/DB132/onlinelog/o1_mf_1_gz145kfy_.log   YES
     4         STANDBY /u01/app/oracle/orcl/flash_recovery_area/DB132/onlinelog/o1_mf_4_gz145mrx_.log   YES
     5         STANDBY /u01/app/oracle/orcl/flash_recovery_area/DB132/onlinelog/o1_mf_5_gz145ngw_.log   YES
     6         STANDBY /u01/app/oracle/orcl/flash_recovery_area/DB132/onlinelog/o1_mf_6_gz145p0r_.log   YES
     7         STANDBY /u01/app/oracle/orcl/flash_recovery_area/DB132/onlinelog/o1_mf_7_gz145pmt_.log   YES

7 rows selected.

SQL>  recover managed standby database using current logfile disconnect from session;
ORA-01153: an incompatible media recovery is active


SQL> alter database recover managed standby database cancel;

Database altered.

SQL>  recover managed standby database using current logfile disconnect from session;
Media recovery complete.

9.测试一下主备切换：

DGMGRL> switchover to 'db132'
Performing switchover NOW, please wait...
New primary database "db132" is opening...
Operation requires shutdown of instance "orcl" on database "db131"
Shutting down instance "orcl"...
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
        shut down instance "orcl" of database "db131"
        start up instance "orcl" of database "db131"

切换正常，但是需要手工重启一下新的备库。

这个问题还不知道怎么解决，每次switchover需要手动重启一下新的备库。

安装过程出现的错：

DGMGRL> enable configuration;
Enabled.
DGMGRL>  show configuration;

Configuration - dg_test11

  Protection Mode: MaxPerformance
  Databases:
    db131 - Primary database
    db132 - Physical standby database
      Error: ORA-16797: database is not using a server parameter file

Fast-Start Failover: DISABLED

Configuration Status:
ERROR


备库没有用spfile，生成spfile重启一下就可以了。