Oracle DataGuard:单节点到RAC集群的主备环境搭建

  1. 云栖社区>
  2. 袋鼠云技术团队>
  3. 博客>
  4. 正文

Oracle DataGuard:单节点到RAC集群的主备环境搭建

笱局长 2017-07-24 11:37:50 浏览2514
展开阅读全文

需求
随着业务增长,数据量业务复杂度越来越高,数据量越来越大,对数据库和服务器的性能、高可用、容灾等要求也越来越高。以当前的数据库环境为例,Windows 2008 r2 服务器+NAS存储+Oracle11.2.0.1+12T+300GARCH/天的规模已经变得非常臃肿,不再适合快速发展的业务场景。
因此,根据业务场景、数据规模和客户需求,提出数据库迁移至Linux+OracleRAC环境,同步完成之后再做主备切换;满足数据库高可用、容灾需求。

实施步骤
DBCA创建集群测试数据库(orcl);
主备数据库参数设置;
删除集群数据库控制文件和数据文件、日志文件;
只启动RAC节点1的实例做数据库同步;
归档追完之后,启动节点2;
监控数据库同步状态;
监控集群状态。

主备数据库环境
image

网络规划
image
主备环境在同一个机房内,并且为了不影响生产环境,专门拉了一条直连的网线,配置局域网内环境。主机配置192.168.1.1,备机RAC节点1配置192.168.1.2。
切记DNS不要自动生成,以免在两个机器内部产生环路,影响与核心交换机的网路路由。

参考文档
How to Create a RAC Standby Database
http://www.oracle.com/technetwork/cn/database/dataguardoverview-091578-zhs.html
http://www.oracle.com/technetwork/database/features/availability/twp-dataguard-11gr2-1-131981.pdf

主库环境检查和参数设置

# 是否开启 force logging
SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES
#   如果NO,则强制开启
SQL> alter database force logging;
Database altered.

# 是否开启归档
SQL> archive log list

Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           G:\arch
Oldest online log sequence     100
Next log sequence to archive   102
Current log sequence           102
#    如果未开启,则重启数据库至mount状态,开启归档
SQL> startup mount
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1392511096 bytes
Database Buffers 738197504 bytes
Redo Buffers 13848576 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.

# (oracle)tnsnames.ora  服务配置
# 主备数据库的都需要配置,配置完成后将文件拷贝至RAC双节点
ORCL_PRI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1523))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

test_rac =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (UR = A)
    )
  )

# (oracle)密码文件拷贝至备库各节点(本次采取直接在备库生成密码文件)
cd $ORACLE_HOME/dbs
orapwd file=orapwdorcl1 entries=5 ignorecase=y password=xxxxxx

# (oracle)注册静态监听 listener.ora
LISTENER3 = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT= 1523))
  )

SID_LIST_LISTENER3 =
  (SID_LIST = 
    (SID_DESC = 
      (GLOBAL_NAME = orcl)
      (ORACLE_HOME =D:\app\Administrator\product\11.2.0\dbhome_1)
      (SID_NAME = orcl)
        (ENVS = “EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oracle11.dll”)
    )
  )

lsnrctl start LISTENER3

lsnrctl status LISTENER3
…………
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOW, has 1 handler(s) for this service…

# (oracle)备库上测试监听是否可以正常连接
tnsping  orcl_pri
sqlplus xxx/yyy@orcl_pri

# (oracle)数据文件路径
SQL> select name from v$datafile;
………………

# (oracle)日志文件路径

SQL> select member from v$logfile;
…………

# (oracle)初始化参数配置
# 因为主库已有一个单节点的备库,所以部分参数不做修改
# 在线修改
alter system set fal_server = 'orcl_dr,testrac';    
alter system set log_archive_dest_3='SERVICE=test_rac LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testrac';

备库环境准备和参数设置(节点1)

# (oracle)是否开启 force logging
SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES
#   如果NO,则强制开启
SQL> alter database force logging;
Database altered.

# (oracle)是否开启归档
SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           +ORAARCH/orcl/archivelog 
Oldest online log sequence     100
Next log sequence to archive   102
Current log sequence           102
#     如果未开启,则重启数据库至mount状态,开启归档
SQL> startup mount
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1392511096 bytes
Database Buffers 738197504 bytes
Redo Buffers 13848576 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.

# (oracle)tnsnames.ora  服务配置
# 主备数据库的都需要配置,配置完成后将文件拷贝至RAC双节点
ORCL_PRI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1523))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

test_rac =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (UR = A)
    )
  )

# (oracle)注册静态监听 listener.ora
# 主备数据库的都需要配置,配置完成后将文件拷贝至RAC双节点,修改SID_NAME和HOST信息
LISTENER = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = testrac1)(PORT= 1521))
  )

SID_LIST_LISTENER =
  (SID_LIST = 
    (SID_DESC = 
      (GLOBAL_NAME = orcl)
      (ORACLE_HOME = /opt/oracle/database/product/11.2.0)
      (SID_NAME = orcl1)
    )
  )

ADR_BASE_LISTENER = /opt/oracle/database

lsnrctl start LISTENER

lsnrctl status
…………
Service "LISTENER" has 1 instance(s).
  Instance "orcl1", status UNKNOW, has 1 handler(s) for this service…

# (oracle)主库上测试监听是否可以正常连接
tnsping  test_rac
sqlplus xxx/yyy@test_rac

# (oracle)数据文件路径
SQL> select name from v$datafile;
………………

# (oracle)日志文件路径
SQL> select member from v$logfile;
……………

# (oracle)停库,oracle用户下
srvctl stop database -d orcl
srvctl status database -d orcl

# (oracle)去掉在OCR中的注册信息
srvctl remove instance -d orcl -i orcl1
srvctl remove instance -d orcl -i orcl2
srvctl remove database -d orcl 

# (grid)删除数据、日志文件、控制文件
asmcmd

# (oracle)初始化参数配置,启动数据库至nomount状态
# 在线修改
alter system set db_files=2000 scope=spfile;

alter system set db_unique_name=testrac scope=spfile;
alter system set service_names=orcl;

alter system set control_files=‘+ORADATA/orcl/controlfile/controlfile01.ora’,’+ORADATA/orcl/controlfile/controlfile02.ora','+ORAFRA/orcl/controlfile/controlfile03.ora' scope=spfile;

alter system set log_archive_config=‘dg_config=(orcl_pri,test_rac)’; 

alter system set log_archive_dest_1='LOCATION=+ORAARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testrac';

alter system set log_archive_dest_2='SERVICE=orcl_pri LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl';

alter system set fal_client=test_rac;

alter system set fal_server=orcl_pri;

alter system set db_file_name_convert='D:\APP\ADMINISTRATOR\ORADATA\ORCL','+ORADATA/orcl/datafile','G:\DATA','+ORADATA/orcl/datafile','F:\DATA','+ORADATA/orcl/datafile','E:\DATA','+ORADATA/orcl/datafile','C:\ORADATA','+ORADATA/orcl/datafile' scope=spfile;

alter system set log_file_name_convert='G:\arch\ORCL','+ORADATA/orcl/onlinelog' scope=spfile;

# (oracle)注册testrac 信息至grid集群
srvctl add database -d testrac -o /opt/oracle/database/product/11.2.0 -p +ORADATA/orcl/spfileorcl.ora -r PRIMARY -s open  -a “ORADATA,ORAARCH,ORAFRA"

srvctl add instance -d testrac -i orcl1 -n testrac1
srvctl add instance -d testrac -i orcl2 -n testrac2

# (oracle)重启,并手动生成 pfile 拷贝至备库
srvctl start database -d testrac -o nomount
srvctl status database -d testrac
create file=$ORACLE_HOME/pfile from spfile=‘+ORADATA/orcl/spfileorcl.ora’;

# (oracle)关闭集群
srvctl stop database -d testrac

ADG同步

# (oracle)启动节点1的数据库实例至 nomount 状态
srvctl start database -d testrac -n testrac1 -o no mount

# (oracle)开始同步
参考附录1

standby 和redo 日志文件处理

# (oracle)创建standby log file,比主库在线日志组多一个
alter database add standby logfile group 11 '+ORADATA/orcl/standbylog/standby11a.dbf' size 1G;
………………
alter database add standby logfile group 17 '+ORADATA/orcl/standbylog/standby17a.dbf' size 1G;

# (oracle)开启实时日志恢复
alter database recover managed standby database disconnect from session using current logfile;

# (oracle)主库切换系统日志(多次)
alter system switch logfile;

# (oracle)打开备库
alter database recover managed standby database cancel;
alter database open;

# (oracle)重建备库redo log group
# 查看当前日志状态
select GROUP#,STATUS from v$log;

# 关闭standby 文件自动管理
alter system set standby_file_management = ‘MANUAL';

# 清理并删除CLEARING 状态的日志组和文件,并重建
alter database clear logfile group 1;
alter database drop logfile group 1;
alter database add logfile group 1 '+ORADATA/orcl/ONLINELOG/online01.ora' size 1G;
………………
# 清理并删除CLEARING_CURRENT状态的日志组和文件,并重建
# 主库切换日志
alter system switch logfile;

# 备库重建日志
alter database clear logfile group 4;
alter database drop logfile group 4;
alter database add logfile group 4 '+ORADATA/orcl/ONLINELOG/online04.ora' size 1G;

# (oracle)开启数据库实时同步进程(MRP)
alter system set standby_file_management = ‘AUTO';
alter database recover managed standby database disconnect from session using current logfile;

同步校验

# (oracle)连接备库校验
SQL> select sequence#,archived,applied from v$archived_log;
SEQUENCE# ARC APPLIED

99 YES YES
   100 YES YES
   101 YES IN-MEMORY
   
SQL> select to_number(substr(t.value,5,2)) h, to_number(substr(value,8,2)) s from v$dataguard_stats t where name = 'apply lag';
h s

0 0

# (oracle)主库

SQL> create table sync_test(id number, name varchar2(20));
SQL> insert into sync_test values(1,’haha’);
SQL> commit;

(oracle)备库

SQL> select * from sync_test;

    ID         NAME
    -- ----------
     1         haha

集群处理

# 拉起RAC集群
# (oracle)当前集群状态
srvctl status database -d testrac
Instance orcl1 is running on node testrac1
Instance orcl2 is not running on node testrac2

# (oracle)拉起节点2实例
srvctl start database -d testrac  -n testrac2

# (oracle)查看集群状态
srvctl status database -d testrac
    Instance orcl1 is running on node testrac1
    Instance orcl2 is running on node testrac2

SQL> select inst_id,instance_name,status from gv$instance;
INST_ID INSTANCE_NAME STATUS

     1 orcl1            OPEN
     2 orcl2            OPEN
更多集群状态查询,参考附录2
附件下载: 附录.zip[笱局长].1500867285.zip

网友评论

登录后评论
0/500
评论
笱局长
+ 关注
所属团队号: 袋鼠云技术团队