RAC到单机的ADG环境搭建

咖啡逗不逗 2020-09-07

2036

作者 | kf_dbd

来源 | 咖啡逗不逗（ID: kf_dbd）

转载请联系授权 | (微信ID：xh870545795)

大家好，我是 kf_dbd,很高兴又和大家见面了，今天分享一个RAC到单机的DG环境搭建(11.2.0.4.0）过程。本文首发于微信公众号【咖啡逗不逗】，欢迎点击上方蓝字关注我吧！本期主要介绍RAC和单机备库的操作设置过程，如果大家有兴趣，需提前准备一套RAC环境，正好公司最近有这个要求，因此就在这里写一下大概的步骤操作，仅供大家参考，如有错误还请大家提出指证！

1.配之前的准备工作

需要准备一台单机备库环境，要求是最好和主库RAC相同版本的操作系统，并且空间大小可以容纳主库所有数据，然后搭建单机环境，注意安装完成数据库软件（数据库版本相同）即可，无需dbca建库。本篇文章不介绍单机及RAC的搭建过程。

2.主库RAC及单机备库配置/etc/hosts

[root@djdb1 ~]# cat etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.5.0.53   djdb1
10.5.0.55   djdb2
10.5.0.54   djdb1-vip
10.5.0.56   djdb2-vip
10.5.0.61   djdb-scan
192.168.1.53  djdb1-priv
192.168.1.55  djdb2-priv
10.5.0.70   xh-duojin-dg

10.5.0.70 xh-duojin-dg 为添加的备库地址

3.主库RAC开启强制日志

SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FORCE_
------
YES

4.拷贝密码文件

[oracle@djdb1 dbs]$ scp orapwDJCRM1 10.5.0.55:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwDJCRM2
[oracle@xh-duojin-dg dbs]$ scp orapwDJCRM1 10.5.0.70:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwDJCRMdg

将RAC第一个节点的密码文件拷贝到第二个节点，将RAC第一个节点的密码文件拷贝到远程备库的节点。

5.主库创建standby log

SQL> select THREAD#, GROUP#, BYTES/1024/1024 M from v$log;--四组
THREAD#     GROUP#         M
---------- ---------- ----------
1      1       200
1      2       200
2      3       200
2      4       200
SQL> col member for a50
set lines 100
set pages 100
SQL>select * from v$logfile order by 1;
GROUP# STATUS  TYPE    MEMBER                IS_
---------- ------- ------- -------------------------------------------------- ---
1     ONLINE  +DATA/DJCRM/onlinelog/group_1.261.1021899417        NO
2     ONLINE  +DATA/DJCRM/onlinelog/group_2.262.1021899417        NO
3     ONLINE  +DATA/DJCRM/onlinelog/group_3.265.1021899501        NO
4     ONLINE  +DATA/DJCRM/onlinelog/group_4.266.1021899503        NO
SQL> alter database add standby logfile thread 1 group 20 size 200m,group 21 size 200m,group 22 size 200m;---主库standby日志一般比数据库日志多两组
SQL> alter database add standby logfile thread 2 group 30 size 200m,group 31 size 200m,group 32 size 200m;
SQL>  select THREAD#, GROUP#, BYTES/1024/1024 M from v$standby_log;--六组
THREAD#     GROUP#         M
---------- ---------- ----------
1     20       200
1     21       200
1     22       200
2     30       200
2     31       200
2     32       200

主库standby日志一般比数据库日志多两组

6.主备库配置监听环境listener.ora

rac节点1--注意grid用户

$GRID_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC = (GLOBAL_DBNAME = djcrm)
(SID_NAME = djcrm1)
(ORACLE_HOME = u01/app/oracle/product/11.2.0/db_1)
)
)
$ lsnrctl reload

rac节点2--注意grid用户

$GRID_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC = (GLOBAL_DBNAME = djcrm)
(SID_NAME = djcrm2)
(ORACLE_HOME = u01/app/oracle/product/11.2.0/db_1)
)
)
$ lsnrctl reload

单机备库

$ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC = (GLOBAL_DBNAME = djcrmdg)
(SID_NAME = djcrmdg)
(ORACLE_HOME =  /u01/app/oracle/product/11.2.0/db_1)
)
)
$ lsnrctl reload

7.主备库配置tns

(tns名字与下面第8、9步参数一致)（oracle用户）

vi $ORACLE_HOME/network/admin/tnsnames.ora
##########scan-ip###########

DJCRM_SYN = --(主)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = djdb-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = djcrm)
)
)

DJCRMDG_SYN =--（备）
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xh-duojin-dg)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = djcrmdg)
)
)

$ tnsping DJCRMDG_SYN

$ tnsping DJCRM_SYN

8.主库修改参数

SQL> alter system set fal_client='DJCRM_SYN' scope=both;
SQL> alter system set fal_server='DJCRMDG_SYN' scope=both;
SQL> alter system set log_archive_config='dg_config=(djcrm,djcrmdg)' scope=both;
SQL> alter system set log_archive_dest_2='service=DJCRMDG_SYN valid_for=(online_logfiles, primary_role) LGWR ASYNC NOAFFIRM delay=0 reopen=10 db_unique_name=djcrmdg' scope=both;
SQL> alter system set standby_file_management=auto scope=both;
SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata','+DATA', '/u01/app/oracle/oradata','+ARCH' scope=spfile;
SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata','+DATA', '/u01/app/oracle/oradata','+ARCH' scope=spfile;
SQL> alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;
SQL> show parameter fal_client
...........

9.主库编辑pfile文件

SQL> create pfile='/tmp/initIMSI.ora' from spfile;
$ vi tmp/initIMSI.ora
*.audit_file_dest='/u01/app/oracle/admin/djcrmdg/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_file_name_convert='+DATA','/u01/app/oracle/oradata','+ARCH','/u01/app/oracle/oradata'
*.db_name='djcrm'
*.db_unique_name='djcrmdg'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=djcrmXDB)'
*.fal_client='DJCRMDG_SYN'
*.fal_server='DJCRM_SYN'
djcrmdg.instance_number=1
*.log_archive_config='dg_config=(djcrmdg,djcrm)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch'
*.log_archive_dest_2='service=DJCRM_SYN valid_for=(online_logfiles, primary_role) LGWR ASYNC NOAFFIRM delay=0 reopen=10 db_unique_name=djcrm'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='+DATA','/u01/app/oracle/oradata','+ARCH','/u01/app/oracle/oradata'
*.memory_target=85899345920
*.open_cursors=30000
*.processes=5000
#*.remote_listener='db-scan:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=5505
*.standby_file_management='AUTO'
djcrmdg.thread=1
*.undo_management='AUTO'
djcrmdg.undo_tablespace='UNDOTBS3'

将主库修改过的pfile文件传到备库(注意权限)

$ scp /tmp/initIMSI.ora xh-duojin-dg:/tmp/initIMSI.ora

10.备库创建目录赋予权限

mkdir -p u01/app/oracle/admin/djcrmdg/adump
mkdir -p u01/app/oracle/{arch,oradata}
mkdir -p u01/app/oracle/oradata/controlfile
mkdir -p u01/app/oracle/oradata/djcrm/onlinelog/
mkdir -p u01/app/oracle/oradata/djcrm/datafile/
mkdir -p u01/app/oracle/oradata/djcrm/tempfile/
chown -R oracle:oinstall u01/app/oracle/arch
chown -R oracle:oinstall u01/app/oracle/oradata
chown -R oracle:oinstall u01/app/oracle/admin

11.创建恢复备库

备库用传输的pfile启动到nomount状态

SQL> startup nomount pfile=/tmp/initIMSI.ora

主库远程恢复备库

$ rman target  sys/oracle123@DJCRM_SYN auxiliary sys/oracle123@DJCRMDG_SYN
run { allocate channel prmy1 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby from active database nofilenamecheck dorecover;
}

12.备库应用日志

SQL> alter database open;
SQL> recover managed standby database using current logfile disconnect;

SQL> alter database recover managed standby database cancel;--取消应用

13.查看MRP、FRS进程状态

SQL> SELECT PROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS FROM V$MANAGED_STANDBY order by 1,3,4;
PROCESS   STATUS    THREAD#  SEQUENCE#   BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH    CONNECTED    0     0        0    0
ARCH    CONNECTED    0     0        0    0
ARCH    CONNECTED    0     0        0    0
ARCH    CLOSING    2   611     4096        420
MRP0    APPLYING_LOG    2   612      117    4194304
RFS    IDLE      0     0        0    0
RFS    IDLE      0     0        0    0
RFS    IDLE      0     0        0    0
RFS    IDLE      0     0        0    0
RFS    IDLE      1  1099      666    5
RFS    IDLE      2   612      119    1

MRP进程在应用归档的时候处于applying_log状态，应用完成后处于wait_for_log状态。RFS进程在应用归档的时候处于waiting状态，应用完成后处于idle状态。

14.查看归档应用情况

SQL> select thread#,sequence#,applied from v$archived_log order by 1,2;
SEQUENCE# APPLIED
---------- ---------
609 NO
610 YES
1097 YES
1098 YES
611 IN-MEMORY

15.主备库配置删除归档策略

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

16.测试数据同步

主库创建测试表
创建表空间
SQL> create tablespace test_tbs datafile '+DATA/djcrm/datafile/test01.dbf' size 50m autoextend off;
创建用户
SQL> create user test identified by test default tablespace test_tbs account unlock;
给用户赋权
SQL> grant dba,resource,connect to test;
连接用户
SQL> conn test/test
创建表
SQL> create table test_tb (id number(9),name varchar2(20));
test_tb表插入数据
SQL> insert into test_tb values(1,'test');
提交
SQL> commit;
主库查询测试表
SQL> select * from test_tb;
ID NAME
---------- ----------------------------------------
1 test
备库查询测试表
SQL> select * from test.test_tb;
ID NAME
---------- ----------------------------------------
1 test

至此RAC-单节点的DG环境算是搭建完成了，以上步骤为小编在搭建公司DG环境中具体的操作步骤，搭建过程也不是一帆风顺，完成后整理此文档供大家参考，如有错误请联系小编，大家一起学习，写作不易，希望大家点个关注。

数据库

文章转载自咖啡逗不逗，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

RAC到单机的ADG环境搭建

评论