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

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

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

主备数据库环境

网络规划

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

参考文档
How to Create a RAC Standby Database

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