oracle11g broker,oracle11G使用DGbroker创办dg

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 fromspfile;

scp initatest.ora orapwatest192.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 [verbose];

show database[verbose] 'satest';

show database'patest''StatusReport';

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

SELECT GROUP#,dbid,archived,status fromv$standby_log;select dest_id,valid_type,valid_role,valid_now fromv$archive_dest;select process,status,group#,thread#,sequence# fromv$managed_standby order by process,group#,thread#,sequence#;select name,value,time_computed fromv$dataguard_stats;select timestamp,facility,dest_id,message_num,error_code,message fromv$dataguard_status order by timestamp;select recid,archived,applied from v$archived_log;

8.DG不同步检查步骤：

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