你的位置:首页 > 数据库

[数据库]oracle11G使用DGbroker创建dg

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.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 11 '/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.0/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.0/db1)   (SID_NAME = atest)  )  (SID_DESC =   (GLOBAL_DBNAME = patest)   (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db1)   (SID_NAME = atest)  )  (SID_DESC =   (GLOBAL_DBNAME = patest_DGMGRL)   (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db1)   (SID_NAME = atest)  ) )LISTENER = (DESCRIPTION_LIST =  (DESCRIPTION =   (ADDRESS = (PROTOCOL = TCP)(HOST = vijay01.database.com)(PORT = 1521))  )  (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 = 1521))  (CONNECT_DATA =   (SERVER = DEDICATED)   (SERVICE_NAME = atest)  ) )PATEST = (DESCRIPTION =  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.51)(PORT = 1521))  (CONNECT_DATA =   (SERVER = DEDICATED)   (SERVICE_NAME = atest)  ) )SATEST = (DESCRIPTION =  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.52)(PORT = 1521))  (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.0/db1)   (SID_NAME = atest)  )  (SID_DESC =   (GLOBAL_DBNAME = satest)   (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db1)   (SID_NAME = atest)  )  (SID_DESC =   (GLOBAL_DBNAME = satest_DGMGRL)   (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db1)   (SID_NAME = atest)  ) )LISTENER = (DESCRIPTION_LIST =  (DESCRIPTION =   (ADDRESS = (PROTOCOL = TCP)(HOST = vijay02.database.com)(PORT = 1521))  )  (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 = 1521))  (CONNECT_DATA =   (SERVER = DEDICATED)   (SERVICE_NAME = atest)  ) )SATEST = (DESCRIPTION =  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.52)(PORT = 1521))  (CONNECT_DATA =   (SERVER = DEDICATED)   (SERVICE_NAME = atest)  ) )PATEST = (DESCRIPTION =  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.51)(PORT = 1521))  (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/123456@patest auxiliary sys/123456@satest

  d.开始克隆:

 duplicate target database for standby nofilenamecheck from active database;

7.设置DGbroker:

  a.登陆dgmgrl:

dgmgrl sys/123456@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;show database 'satest';show database 'patest' 'StatusReport';show database verbose 'satest';

  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不同步检查步骤:

1.检查密码文件2.检查网络3.检查参数文件4.检查防火墙或selinux5.如以上均无问题,只能说明dg环境有问题,需要重新搭建dg(重新传输数据文件到主库,在重新同步)