WindowsServer配置Oracle11g DG

Oracle 11g DG

服务器环境

操作系统 Server 2012 R2

数据库版本 Oracle 11G 11.2.0.4.0

配置准备

主库安装数据库，备库只需要安装数据库软件

安装路径主备库的路径要一致

DG配置

主库开启强制归档和闪回恢复

sqlplus / as sysdba
shutdown immediate
startup mount
alter database archivelog;
alter database force logging;
alter database flashback on;

主库为备库创建日志文件

set wrap off;
select * from v$logfile order by group#; #查询日志组
GROUP# STATUS    TYPE        MEMBER  
---------- -------------- -------------------- ---------------
   1  ONLINE      D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
   2  ONLINE      D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG
   3  ONLINE      D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
# 当前在线日志共有3组，为备库创建的日志建议比在线日志多一组，即4组
alter database add standby logfile group 4 ('D:\APP\ADMINISTRATOR\ORADATA\ORCL\stdredo004.log') size 50m;
alter database add standby logfile group 5 ('D:\APP\ADMINISTRATOR\ORADATA\ORCL\stdredo005.log') size 50m;
alter database add standby logfile group 6 ('D:\APP\ADMINISTRATOR\ORADATA\ORCL\stdredo006.log') size 50m;
alter database add standby logfile group 7 ('D:\APP\ADMINISTRATOR\ORADATA\ORCL\stdredo007.log') size 50m;
select * from v$logfile order by group#; #查询日志组
GROUP# STATUS    TYPE        MEMBER  
---------- -------------- -------------------- ---------------------------------------------------- 
  1  ONLINE      D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
  2  ONLINE      D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG
  3  ONLINE      D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
  4  STANDBY     D:\APP\ADMINISTRATOR\ORADATA\ORCL\STDREDO004.LOG
  5  STANDBY     D:\APP\ADMINISTRATOR\ORADATA\ORCL\STDREDO005.LOG
  6  STANDBY     D:\APP\ADMINISTRATOR\ORADATA\ORCL\STDREDO006.LOG
  7  STANDBY     D:\APP\ADMINISTRATOR\ORADATA\ORCL\STDREDO007.LOG

主库修改系统参数

#设置db_unique_name
alter system set db_unique_name='primary' scope=spfile;
#设置日志归档配置，用于限制日志传输的目标，只能在这两个指定的数据库进行，该配置中的顺序可以调换
alter system set log_archive_config='DG_CONFIG=(primary,standby)' scope=spfile;
#设置日志归档路径1
alter system set log_archive_dest_1='location=D:\app\Administrator\archivelog\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary' scope=spfile;
#设置日志归档路径2
alter system set log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=standby' scope=spfile;
#启用归档路径1
alter system set log_archive_dest_state_1='enable' scope=spfile;
#启用归档路径2
alter system set log_archive_dest_state_2='enable' scope=spfile;
#设置当前库获取归档日志的服务器listerner名称（与8成对出现，用于switchover角色切换）
alter system set fal_server='standby' scope=spfile;
#设置当前库获取归档日志的客户端名称listerner名称（与7成对出现，用于switchover角色切换）
alter system set fal_client='primary' scope=spfile;
#设置归档日志强制切换时间（s）1800表示30分钟
alter system set archive_lag_target=1800 scope=spfile;
#设置归档日志文件名命名方式
alter system set log_archive_format='%t_%s_%r.arc'scope=spfile;
#设置备库文件管理的方式为auto,结合后续的convert参数，当主库增删文件时会自动在备库也生成相应文件。
alter system set standby_file_management=auto scope=spfile;
#设置数据文件名称的映射关系（注意顺序不能变）
alter system set db_file_name_convert='standby','primary' scope=spfile;
#设置日志文件名称的映射关系（注意顺序不能变）
alter system set log_file_name_convert='standby','primary' scope=spfile;

主库重新启动数据库

alter database open;
shutdown immediate;
startup;

主库创建pfile文件

create pfile from spfile;
文件位置：D:\app\Administrator\product\11.2.0\dbhome_1\database\INITorcl.ORA

主库创建密码文件

默认情况下已经生成了密码文件，位置在：D:\app\Administrator\product\11.2.0\dbhome_1\database\PWDorcl.ORA

如果没有生成该文件，可以手工进行创建

C:\Users\Administrator> orapwd file=D:\app\Administrator\product\11.2.0\dbhome_1\database\PWDorcl.ora password=Flexsystem123 entries=5

主库配置网络监听

Oracle服务器端，配置listener配置文件，添加一个orcl的静态注册
# listener.ora Network Configuration File: D:\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    #(SID_DESC =
    # (GLOBAL_DBNAME = orcl)
    # (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
    # (SID_NAME = orcl)
    #)
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl1)(PORT = 1521)) //这里的主机名orcl1不要修改，windows的怪脾气，修改后监听会有问题
    )
  )
ADR_BASE_LISTENER = D:\app\Administrator
Oracle客户端，配置tnsnames.ora
# tnsnames.ora Network Configuration File: D:\tnsnames.ora
# Generated by Oracle configuration tools.
#PRIMARY =
# (DESCRIPTION =
#   (ADDRESS_LIST =
#     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.68.21)(PORT = 1521))
#   )
#   (CONNECT_DATA =
#     (SERVICE_NAME = orcl)
#   )
# )
ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )
#STANDBY =
# (DESCRIPTION =
#   (ADDRESS_LIST =
#     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.68.22)(PORT = 1521))
#   )
#   (CONNECT_DATA =
#     (SERVICE_NAME = orcl)
#   )
# )
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

主库测试监听

在服务管理器里面重启listener服务和oracle数据库

C:\Users\Administrator>tnsping primary

C:\Users\Administrator>tnsping orcl

在主库上tnsping测试都能ping通

C:\>sqlplus scott/Flexsystem123@192.168.68.21/primary

C:\>sqlplus scott/Flexsystem123@192.168.68.21/orcl

在备库上sqlplus连接数据库都能连接

主库复制配置及参数文件

#关闭主数据库
SQL> shutdown immediate
#将主库D:\app\Administrator文件夹下面的admin、cfgtoollogs、diag、flash_recover_area文件夹及PWDorcl.ora、 listener.ora和tnsnames.ora文件拷贝到备库相应目录下。
修改listener.ora中的监听地址（主机名HOST）
# listener.ora Network Configuration File: D:\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
      (SID_NAME = orcl)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
#      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl2)(PORT = 1521))
    )
  )
ADR_BASE_LISTENER = D:\app\Administrator

备库新建实例

C:\>oradim -new -sid orcl
oradim -new -sid spectra -startmode auto  
oradim -new -sid spectra1 -startmode auto 
实例已创建。

备库启动监听

C:\> lsnrctl start
#初次启动会在services.msc服务列表中注册Listener服务
#备库参数文件调整
#将主库的pfile文件拷贝到备库D:\app\Administrator\product\11.2.0\dbhome_1\database相应目录下并修改红色部分如下
orcl.__db_cache_size=704643072
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=704643072
orcl.__sga_target=1023410176
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=268435456
orcl.__streams_pool_size=0
*.archive_lag_target=1800
*.audit_file_dest='D:\app\Administrator\admin\orcl\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='D:\app\Administrator\oradata\orcl\control01.ctl','D:\app\Administrator\flash_recovery_area\orcl\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='primary','standby'
*.db_name='orcl'
*.db_recovery_file_dest='D:\app\Administrator\flash_recovery_area'
*.db_recovery_file_dest_size=4102029312
*.db_unique_name='standby'
*.diagnostic_dest='D:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='standby'
*.fal_server='primary'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=D:\app\Administrator\archivelog\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.log_archive_dest_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=primary'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
*.log_file_name_convert='primary','standby'
*.memory_target=1717567488
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
#在备库新建文件夹D:\app\Administrator\archivelog
#在备库上用拷贝过来修改好的参数文件启动实例
SQL> startup nomount pfile='D:\app\Administrator\product\11.2.0\dbhome_1\database\INITorcl.ORA'

复制主库数据

建立一个完整备份到临时文件夹

C:\Users\Administrator>rman target /
恢复管理器: Release 11.2.0.1.0 - Production on 星期日 7月 21 22:56:47 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
连接到目标数据库: ORCL (DBID=1541935557)
RMAN> backup full database format='D:\tmp\forstandby_%u%p%s.RMN' include current controlfile for standby;
#归档当前二进制日志
RMAN> sql 'alter system archive log current';

将主库的完整rman备份拷贝到备库相同的路径下

在备库上创建与主库相同的数据目录

然后在主库的rman终端连接备库进行数据恢复

C:\Users\Administrator>rman target /
RMAN> connect auxiliary sys/Flexsystem123@standby
RMAN> duplicate target database for standby nofilenamecheck;

备库启动数据同步

C:\Users\Administrator>sqlplus / as sysdba
SQL> startup nomount pfile='D:\app\Administrator\product\11.2.0\dbhome_1\database\INITorcl.ORA'
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
SQL> create spfile from pfile;
#开启数据库flashback特性
SQL> alter database flashback on;
#关闭数据同步
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
#注：如果想让备库比主库延迟几分钟可以启动为如下方式
alter database recover managed standby database delay 5 disconnect from session; 

验证数据同步

#在主库和备库上都执行，对比日志文件如果备库和主库一样表示同步完成
SQL> select name,sequence#,applied,completion_time from v$archived_log order by completion_time desc,sequence# desc;
SQL> select name,sequence#,applied, To_Char(a.COMPLETION_TIME, 'YYYY-MM-DD HH24:MI:SS') AS COMPLETETIME from v$archived_log a  where a.COMPLETION_TIME>sysdate -1/24 order by sequence#;
#主库上切换日志文件再观察有没有正确同步到备库
SQL> alter system switch logfile;

主备库切换

#在主库上查询切换状态，只有为TO STANDBY的状态才可以进行无损切换，其他状态则需要修复。
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
----------------------------------------
TO STANDBY
#执行切换命令，并将主库转换为备库角色
#主库执行切换：
SQL> alter database commit to switchover to physical standby with session shutdown;
#主库角色转换为备库：
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
#备库执行切换：
SQL> alter database commit to switchover to primary;
SQL> shutdown immediate;
SQL> startup
#切换完成后可以alter system switch logfile来验证一下切换后的主备库数据同步是否正常。
#注意：
#如果当前主库上活动的SESSION正在查询数据，则select switchover_status from v$database 命令会显示SESSIONS ACTIVE，可以通过如下命令进行切换。
alter database commit to switchover to physical standby with session shutdown;
#在备库上查询select switchover_status from v$database会显示 NOT ALLOWED,只有当主库上执行了alter database commit to switchover to physical standby后，备库上才会显示TO PRIMARY

故障切换

#当主库发生无法修复的故障时，需要紧急将备库提升为主库来接管服务。这种情况下，需要预先开启主库的flashback，当主库修复后还可以通过flashback闪回到一个时间点，并切换为备库，否则就只能重建备库。
SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database finish force;
SQL> alter database commit to switchover to primary;
SQL> alter database open;
SQL> select database_role from v$database;
DATABASE_ROLE
--------------------------------
PRIMARY

通过flashback重建备库

#当原来的主库经过维修后还可以启动数据库，则可以通过闪回到指定的SCN来进行standby重建
#A). 首先在当前主库，也就是failover过后的新的主库上查询到自己变成主库的那一刻的scn号：
SQL> select to_char(standby_became_primary_scn) from v$database;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
-----------------------------------------------------------------------
1400381
#B). 对到原来的主库（已修好的机器）上闪回数据，并进行切换
SQL> shutdown immediate;
SQL> startup mount;
SQL> flashback database to scn 1400381;
闪回完成。
SQL> alter database convert to physical standby;
SQL> shutdown immediate;
SQL> startup mount;
#C). 在当前主库重新启用log_archive_dest_state_2并切换归档日志（或者直接重启数据库）
SQL> alter system set log_archive_dest_state_2=enable;
SQL> alter system switch logfile;
#D). 回到原主库（当前备库）开启日志应用进程
SQL> alter database recover managed standby database using current logfile disconnect;
#此时数据库已经完成切换，重新回到DG组中，并以备库的方式加入。可以通过3.13.5中的方法验证数据同步状态

备注

#备注1：
#Oracle Dtataguard有三种保护模式：
Maximum Availability Maximum Performance Maximum Protection
AFFIRM NOAFFIRM AFFIRM
SYNC ASYNC SYNC
#Oracle Dataguard配置过程中指定的log_archive_dest_2参数的值为SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=standby 其中ASYNC参数表示异步传输日志，这种日志传输方式只能配置为最佳性能模式，如果要修改为最高可用或者最大保护的话需要将该参数修改为AFFIRM。
#通过select protection_mode from v$database查询保护模式
SQL> select protection_mode from v$database;
PROTECTION_MODE
----------------------------------------
MAXIMUM PERFORMANCE
#备注2：
#当主备同步有问题时候可以通过查询错误输出来初步诊断
SQL> select error from v$archive_dest where target='STANDBY';
#备注：
#当备库由于错误的操作（错误的提升角色或者错误的打开到OPEN状态并且被写入了数据，导致了主备不一致，这时只能抛弃被错误写入的数据）可以将备库闪回到一个正常状态下再重新进行数据同步。比如今天下午2点发现主库有问题，无法启动。这时将备库提升为了主库并开启为OPEN状态，客户端写入了一小部分数据时才发现，原来备库的数据没有即时同步到最新状态。这种情况下只有等主库修复完成正常启动，然后再从主库重新同步才能保证数据丢失更少。
#查询某一个时间点的scn号：
timestamp_to_scn('23-10月-19 11.00.00.000000000 上午') from dual;