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.主库设置:
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 [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不同步检查步骤:
1.检查密码文件 2.检查网络 3.检查参数文件 4.检查防火墙或selinux 5.如以上均无问题,只能说明dg环境有问题,需要重新搭建dg(重新传输数据文件到主库,在重新同步)