oracle11G使用DGbroker创建dg

时间：2022-08-20 11:37:30

1.环境：　　

　　a.主机环境：centos6.5

　　b.软件环境：oracle 11.2.0.4

　　c.主机信息：

DG1：192.168.100.51（数据库sid：atest）

DG2：192.168.100.52

2.前提工作（主从都要设置）：

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

　　b.关闭selinux：setenforce 0

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

3.主库设置：

　　a.设置主库db_unique_name：

alter system set db_unique_name='patest' scope=spfile;

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

alter database force logging;

　　  检查状态(YEs为强制)：

select name,force_logging from v$database;

　　c.设置standy_file_management：

alter system set standby_file_management ='AUTO';

　　d.创建standbylog：

alter database add standby logfile group   '/u01/app/oracle/oradata/ATEST/standbylog/standby11.log' size 50m;
alter database add standby logfile group  12 '/u01/app/oracle/oradata/ATEST/standbylog/standby12.log' size 50m;
alter database add standby logfile group  13 '/u01/app/oracle/oradata/ATEST/standbylog/standby13.log' size 50m;
alter database add standby logfile group  14 '/u01/app/oracle/oradata/ATEST/standbylog/standby14.log' size 50m;

　　e.开启归档(简单不详述)：

alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/ATEST/archivelog';

alter database archivelog;

　　f.开启DGbroker：

alter system set DG_BROKER_START=TRUE;

　　g.传输pfile和密码文件：

 create pfile from spfile;

 scp initatest.ora orapwatest 192.168.100.52:/u01/app/oracle/product/11.2./db1/dbs/

　　h.设置监听：atest,patest,patest_DGMGRL

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = atest)

      (ORACLE_HOME = /u01/app/oracle/product/11.2./db1)

      (SID_NAME = atest)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = patest)

      (ORACLE_HOME = /u01/app/oracle/product/11.2./db1)

      (SID_NAME = atest)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = patest_DGMGRL)

      (ORACLE_HOME = /u01/app/oracle/product/11.2./db1)

      (SID_NAME = atest)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = vijay01.database.com)(PORT = ))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

　　i.设置tnsnames.ora

ATEST =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.51)(PORT = ))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = atest)

    )

  )

PATEST =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.51)(PORT = ))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = atest)

    )

  )

SATEST =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.52)(PORT = ))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = atest)

    )

  )

4.备库设置：

　　a.设置监听：atest,satest,satest_DGMGRL

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = atest)

      (ORACLE_HOME = /u01/app/oracle/product/11.2./db1)

      (SID_NAME = atest)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = satest)

      (ORACLE_HOME = /u01/app/oracle/product/11.2./db1)

      (SID_NAME = atest)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = satest_DGMGRL)

      (ORACLE_HOME = /u01/app/oracle/product/11.2./db1)

      (SID_NAME = atest)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = vijay02.database.com)(PORT = ))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

　　b.设置tnsnames.ora

ATEST =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.52)(PORT = ))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = atest)

    )

  )

SATEST =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.52)(PORT = ))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = atest)

    )

  )

PATEST =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.51)(PORT = ))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = atest)

    )

  )

5.备份主库：

　　a.rman target /

　　b.backup database;

6.在备库上克隆主库：

　　a.修改备库pfile上的db_unique_name=satest

　　b.启动数据库到nomount：

startup nomount

　　c.登陆rman：

rman target sys/@patest auxiliary sys/@satest

　　d.开始克隆：

 duplicate target database for standby nofilenamecheck from active database;

7.设置DGbroker：

　　a.登陆dgmgrl：

dgmgrl sys/@patest

　　b.设置主库：

create configuration dgc as primary database is patest connect identifier is patest;

　　c.添加备库：

add database satest as connect identifier is satest maintained as physical;

　　d.启用配置文件:

enable configuration

　　e.查看DGbroker配置：

show configuration [verbose];

show database [verbose] 'satest'; 

show database 'patest''StatusReport';

　　f.查看数据库的DG状态：

SELECT GROUP#,dbid,archived,status from v$standby_log;

select dest_id,valid_type,valid_role,valid_now from v$archive_dest;

select process,status,group#,thread#,sequence# from v$managed_standby order by process,group#,thread#,sequence#;

select name,value,time_computed from v$dataguard_stats;

select timestamp,facility,dest_id,message_num,error_code,message from v$dataguard_status order by timestamp;

select recid,archived,applied from v$archived_log;

8.DG不同步检查步骤：

.检查密码文件

.检查网络

.检查参数文件

.检查防火墙或selinux

.如以上均无问题，只能说明dg环境有问题，需要重新搭建dg（重新传输数据文件到主库，在重新同步）

　　

相关文章