联系：手机/微信(+86 17813235971) QQ(107644445)
标题：WINDOWS 下用dg broker搭建ADG（单机to单机）
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
环境
#主备库 C:\Windows\System32\drivers\etc\hosts 文件 192.168.11.10 dg1 192.168.11.11 dg2 #环境 主库主机名：dg1 现有实例orcl 备库主机名：dg2 只安装软件
一，主库配置
–主库设置强制日志，保证所有的操作都记录到日志文件
–查看当前force\_logging的设置
#主库如果已开启归档，不需要停机
sqlplus / as sysdba
select force_logging from v$database;
select flashback_on from v$database;
alter database force logging; -- 开启强制日志模式
#######################################################
#如果没开归档
sqlplus / as sysdba
shudown immediate;
startup mount;
alter database archivelog; -- 开启归档模式
alter database force logging; -- 开启强制日志模式
#alter database flashback on; -- 开启闪回，不是必须，推荐开启
#######################################################
#主库添加standby日志组
#查看日志文件大小 select bytes/1024/1024 from v$log;这里是50M
alter database add standby logfile group 10 ('D:\app\Administrator\oradata\orcl\standby_redo01.log') size 50m;
alter database add standby logfile group 11 ('D:\app\Administrator\oradata\orcl\standby_redo02.log') size 50m;
alter database add standby logfile group 12 ('D:\app\Administrator\oradata\orcl\standby_redo03.log') size 50m;
alter database add standby logfile group 13 ('D:\app\Administrator\oradata\orcl\standby_redo04.log') size 50m;
########################################################
#设置文件管理自动
alter system set standby_file_management=auto;
二、主备库网络设置
#主库listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl_DGMGRL) #用于dg broker的静态监听
(ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
#主库tnsnames.ora
ORCL_STBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
#备库listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl_stby_DGMGRL) #用于dg broker的静态监听
(ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
#备库tnsnames.ora
ORCL_STBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
#主库监听reload
lsnrctl reload
#备库启动监听
lsnrctl start
三、备库配置
#创建一个临时参数文件如d:\pfile.txt内容如下 *.db_name='orcl' #创建密码文件，或者从主库拷贝一个 orapwd file=D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\database\PWDorcl.ora password=oracle entries=10 #创建备库所需目录 mkdir D:\app\Administrator\oradata\orcl\ mkdir D:\app\Administrator\admin\orcl\adump\ mkdir D:\app\Administrator\fast_recovery_area\orcl\ #用ORADIM创建实例 oradim -new -sid orcl #用临时参数文件启动 sqlplus / as sysdba starup nomount pfile='d:\pfile.txt';
四、RMAN duplicate创建备库
#tnsping测试互通性
tnsping orcl
tnsping orcl_stby
#主库连接备库
sqlplus sys/oracle@stddb as sysdba
#备库连接主库
sqlplus sys/oracle@orcl as sysdba
#########################################################
#备库执行，连接主备库
rman target sys/oracle@orcl auxilary sys/oracle@orcl_stby
#创建dg备库，这里假设主备库路径相同
duplicate target database
for standby
from active database
dorecover
spfile
set db_unique_name='orcl_stby'
nofilenamecheck;
#########################################################
#如果主备库路径不同
duplicate target database
for standby
from active database
dorecover
spfile
set db_unique_name='orcl_stby'
set db_file_name_convert='orcl','orcl_stby'
set log_file_name_convert='orcl','orcl_stby'
set job_queue_processes='0'
nofilenamecheck;
#开启ADG
sqlplus / as sysdba
alter database open read only;
alter database recover managed standby database disconnect from session;
五、配置DG BROKER
#主备库两边执行 alter system set dg_broker_start=true; #主库连接dgmgrl dgmgrl sys/oracle@orcl #创建dg broker配置 create configuration dg_config as primary database is orcl connect identifier is orcl; #添加备库到配置文件 add database orcl_stby as connect identifier is orcl_stby; #启用配置 enable configuration; ############################################################################ #显示DG配置信息 show configuration show configuration verbose #显示主备库信息 show database orcl show database orcl_stby show database verbose orcl show database verbose orcl_stby
六、一些测试
#测试Database Switchover dgmgrl sys/oracle@orcl switchover to orcl_stby; show configuration #切换回来 switchover to orcl; show configuration ########################################################################### #测试Database Failover，此时dg关系已经打破 dgmgrl sys/oracle@orcl failover to orcl_stby; #如果主库开启了flashback，执行以下语句自动重建主库 reinstate database orcl; #如果没有开启flashback，删除重建主库，重新建立dg关系 ############################################################################ #测试快照备库 dgmgrl sys/oracle@orcl convert database orcl_stby to snapshot standby; show configuration; #快照转成正常备库 convert database orcl_stby to physical standby; show configuration;
七、总结
优点在于除监听设置外主备库都不需要做过多的设置，备库临时参数文件只需要一个dbname，其余dg有关的参数dg broker会自动设置。
八、参考资料
ORACLE-BASE – Data Guard Physical Standby Setup Using the Data Guard Broker in Oracle Database 11g Release 2
