Oracle DG

发表于 | 分类于
字数统计: 2.3k 字 | 阅读时长 ≈ 10 分钟

Data Guard

DataGuard是甲骨文推出的一种高可用性数据库方案，在Oracle 8i之前被称为Standby Database。从Oracle 9i开始，正式更名为Data Guard。它是在主节点与备用节点间通过日志同步来保证数据的同步，可以实现数据库快速切换与灾难性恢复。Data Guard只是在软件上对数据库进行设置，并不需要额外购买任何组件。用户能够在对主数据库影响很小的情况下，实现主备数据库的同步。而主备机之间的数据差异只限于在线日志部分，因此被不少企业用作数据容灾解决方案。

写在前面

1
2
3
4
# 开库顺序
先启备库，再启主库（启动监听，打开告警日志）
# 关库顺序
先关主库再关备库

配置准备

  • 查看数据库版本
    1
    2
    3
    # 主库ip：192.168.100.2；备库ip：192.168.100.3
    # 查看数据库版本，必须是企业版否则不支持oracle data guard
    SQL> select * from v$version;

mark

  • 备库只安装软件

mark

  • 确保备库安装路径、实例名与主库完全一致，避免同步出错

主库配置

  • 在/app下创建interlib文件夹(自定义文件夹名),在创建文件夹log
1
2
# 日志文件路径：'logUrl=D:\app\interlib\log'
# 控制文件路径：'standbyUrl=D:\app\interlib'
  • 开启归档模式
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    # 在主库上启动数据库到mount模式，开启归档模式与force logging
    sqlplus / as sysdba
    SQL>shutdown immediate; 
    SQL>startup mount; 
    #修改为归档模式
    SQL>alter database archivelog; 
    SQL>alter database open; 
    #设置强制归档模式
    SQL> alter database force logging;
    #查看命令：
    select log_mode,force_logging from v$database;
    #查看是否归档命令：
    Archive log list ;

mark

  • 为备库创建日志文件

    1
    2
    3
    4
    # logUrl=D:\app\interlib\log  根据实际的“日志文件路径”改变
    SQL> alter database add standby logfile group 4 ('D:/app/interlib/log/STAN04.LOG') size 50m;
    SQL> alter database add standby logfile group 5 ('D:/app/interlib/log/STAN05.LOG') size 50m;
    SQL> alter database add standby logfile group 6 ('D:/app/interlib/log/STAN06.LOG') size 50m;

  • 创建standby控制文件

    1
    SQL> alter database create standby controlfile as 'D:\app\interlib\standby.ctl';

  • 导出当前数据库参数并修改

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    SQL> create pfile='D:/app/interlib/initora.ora' from spfile;
    # 修改相关路径，以及增加没有的参数
    orcl.__db_cache_size=654311424
    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=318767104
    orcl.__streams_pool_size=0
    *.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_name='orcl'
    *.db_recovery_file_dest='D:\app\Administrator\flash_recovery_area'
    *.db_recovery_file_dest_size=4102029312
    *.diagnostic_dest='D:\app\Administrator'
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
    *.local_listener='LISTENER_ORCL'
    *.memory_target=1717567488
    *.open_cursors=300
    *.processes=150
    *.remote_login_passwordfile='EXCLUSIVE'
    *.undo_tablespace='UNDOTBS1'
    # 如下是增加的参数
    *.db_unique_name='primary'
    *.archive_lag_target=1800
    *.fal_client='standby'
    *.fal_server='primary'
    *.log_archive_config='DG_CONFIG=(primary,standby)'
    *.log_archive_dest_1='LOCATION=D:\app\interlib\log\ VALID_FOR=(all_logfiles,all_roles) db_unique_name=primary'
    *.log_archive_dest_2='service=standby arch async valid_for=(online_logfiles,primary_role) db_unique_name=standby'
    *.log_archive_dest_state_1='enable'
    *.log_archive_dest_state_2='enable'
    *.log_archive_format='%t_%s_%r.dbf'
    *.DB_FILE_NAME_CONVERT='D:\app\Administrator\oradata\orcl\','D:\app\Administrator\oradata\orcl\'
    *.LOG_FILE_NAME_CONVERT='D:\app\interlib\log','D:\app\interlib\log'
    *.standby_file_management='auto'

  • 重新加载配置启动服务

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    #停止服务
    SQL> shutdown immediate;
    #使用新参数文件启动数据库
    SQL> startup pfile='D:\app\interlib\initora.ora' nomount;
    #创建新的 spfile 文件
    SQL> create spfile from pfile='D:\app\interlib\initora.ora';
    #停止服务
    SQL> shutdown immediate;
    #启动服务
    SQL> startup;

mark

  • 创建密码文件

    1
    2
    3
    4
    5
    6
    # 如果有此步，存在密码文件可不操作
    # 在DOS窗口执行，不需要登录sqlplus，路径不能加引号，否则会报opw-00001错误    
    orapwd file=passwordUrl\PWDorcl.ora password=123 entries=10
    # 密码文件存放路径：
    密码文件存放路径：
    passwordUrl=D:\app\Administrator\product\11.2.0\dbhome_1\database\PWDorcl.ora

  • 配置监听和访问服务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# 修改 listener.ora
# listener.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\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
	(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))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.2)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = D:\app\Administrator
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
# 修改tnsname.ora文件
# tnsnames.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 92.168.100.2)(PORT = 1521))

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 1922.168.100.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

# 增加主库配置
PRIMARY = 
    (DESCRIPTION = 
    (ADDRESS_LIST = 
        (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.100.2) (PORT = 1521))
    )
    (CONNECT_DATA = 
        (SERVICE_NAME = orcl)
    )
) 
# 增加从库配置
STANDBY= 
    (DESCRIPTION = 
    (ADDRESS_LIST = 
        (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.100.3) (PORT = 1521))
    )
    (CONNECT_DATA = 
        (SERVICE_NAME = orcl)
    )
)

从库配置

  • 拷贝数据到备库

    • 主库和备库创建 D:\app\interlib\tmp 文件夹，并把interlib其余目录也拷贝过去
    • 将主库oracle目录下的oradata文件夹下内容复制到从库相同目录
    • 将D:\app\Administrator 目录下的admin,cfgtollogs,diag,flash_recover_area 目录以及密码文件(‘D:\app\Administrator\product\11.2.0\dbhome_1\database\PWDorcl.ora’)拷贝到备用库的相同路径。可直接覆盖
    • 将主库的listener.ora和tnsname.ora拷贝到备库相同路径，并修改linstener.ora的ip为备库ip (‘D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN’)
      1
      2
      3
      4
      5
      6
      7
      LISTENER =
        (DESCRIPTION_LIST =
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.3)(PORT = 1521))
          )
      )

  • 备库新建实例

    1
    2
    3
    4
    # 备库新建实例，如果备库也安装了数据库，实例也是orcl这步可跳过
    # 在备库上注册oracle实例到服务中，cmd下执行
    oradim -new -sid orcl
    lsnrctl start

mark

  • 修改备库参数并创建实例
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    # 将从主库拷贝的 D:\app\interlib\initora.ora修改
    #下面是要修改的地方
    *.db_unique_name='standby'
    *.archive_lag_target=1800
    *.fal_client='primary'
    *.fal_server='standby'
    *.log_archive_config='DG_CONFIG=(primary,standby)'
    *.log_archive_dest_1='LOCATION=D:\app\interlib\log\ VALID_FOR=(all_logfiles,all_roles) db_unique_name=standby'
    *.log_archive_dest_2='service=primary arch async valid_for=(online_logfiles,primary_role) db_unique_name=primary'
    *.log_archive_dest_state_1='enable'
    *.log_archive_dest_state_2='enable'
    *.log_archive_format='%t_%s_%r.dbf'
    *.DB_FILE_NAME_CONVERT='D:\app\Administrator\oradata\orcl\','D:\app\Administrator\oradat
    a\orcl\'
    *.LOG_FILE_NAME_CONVERT='D:\app\interlib\log\','D:\app\interlib\log\'
    *.standby_file_management='auto'
1
2
3
4
5
# 使用新参数文件建立从库实例
SQL> startup nomount pfile='D:\app\interlib\initora.ora';
SQL> create spfile from pfile='D:\app\interlib\initora.ora';
SQL> shutdown immediate; （此步骤可能会报错 01507,暂时忽略）
SQL> startup nomount;

mark

主库执行相关语句

  • 建立主库备份
    1
    2
    3
    4
    5
    # 复制主库，使用RMAN建立备份，cmd下执行
    rman target /
    RMAN> backup full database format='D:\app\interlib\tmp\FOR_STANDBY_%u%p%s,RMN' include current controlfile for standby;
    # 将当前archivelog归档，执行sql语句
    RMAN> sql 'alter system archive log current';

mark

  • 复制数据库
1
2
3
4
5
# 复制数据库；将主库D:\app\interlib\tmp\下产生的的备份集拷贝到备库的相同路径下
# 拷贝完成后在主库刚才的RMAN中执行
RMAN> connect auxiliary sys/123.com@standby #  123.com为备库sys的密码, 可能会提示实例未装载
# 如果提示无法连接，请检查防火墙等是否有限制
RMAN> duplicate target database for standby nofilenamecheck;

mark
mark

  • 备库启动standby
    1
    2
    3
    4
    # 在备库执行
    sqlplus / as sysdba
    alter database mount standby database; # 可能会报错 01100不管
    alter database recover managed standby database disconnect from session;

mark

检查测试

  • 状态查看测试
    1
    2
    # 主库从库分别执行如果 APPLIED 列的值为 yes,表示重做应用成功
    SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

mark

1
2
3
4
# 或者查看切换归档，归档日志记录会+1
select max(sequence#) from v$archived_log;
alter system switch logfile;
select max(sequence#) from v$archived_log;

mark

  • 日志查看测试
1
# 主库上执行alter system switch logfile;，通过select name from v$archived_log; 可以看到主库和备库都增加了一个log文件(.DBF)

mark
mark

  • 查看主备库状态
1
2
# 执行sql语句
select open_mode,protection_mode,database_role,switchover_status from v$database;

DG切换

1
2
3
4
5
6
7
8
9
10
11
12
# 主库执行
# 先将主库切换成备库，然后将原主库启动到物理库的状态
alter database commit to switchover to physical standby with session shutdown;
# 关闭主库
shutdown immediate;
# 开数据库nomount
startup nomount;
# 更改主库为备库
alter database mount standby database;
alter database recover managed standby database disconnect from session;
# 如果配置了 standby redo log 并需要启用实时同步则执行以下代码
alter database recover managed standby database using current logfile disconnect from session;
1
2
3
4
5
6
7
# 备库执行,switchover到primary
# 更改备库为主库
alter database commit to switchover to primary with session shutdown;
# 如果备库还有未应用的日志则执行
alter database recover managed standby database disconnect from session;
shutdown immediate;
startup

DG切换后再恢复最初

即原主库切换为备库，再从备库切换为主库

1
2
3
4
5
6
# 开库顺序
先启备库，再启主库（启动监听，打开告警日志）
# 关库顺序
先关主库再关备库
lsnrctl stop
lsnrctl start

  • 主库操作

    1
    2
    3
    4
    # 登录原主库
    rman target /
    RMAN> connect auxiliary sys/123.com@standby
    RMAN> duplicate target database for standby nofilenamecheck;

  • 备库stnadby

    1
    2
    3
    4
    # 在备库执行
    sqlplus / as sysdba
    alter database mount standby database; # 可能会报错 01100不管
    alter database recover managed standby database disconnect from session;

  • 状态监测

    1
    2
    3
    4
    # 或者查看切换归档，归档日志记录会+1
    select max(sequence#) from v$archived_log;
    alter system switch logfile;
    select max(sequence#) from v$archived_log;
-------------本文结束感谢您的阅读-------------
原创技术分享，感谢您的支持。