分类目录归档：数据库

环境

#主备库 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