前言

经过交流群中朋友的多次要求，这次给大家分享一下 RAC to Single 的 ADG 搭建教程！

一、环境准备

老规矩，测试环境
实战
演示：

主机名 ip DB Version db_name db_unique_name
主库节点一 lucifer01 10.211.55.100 11.2.0.4 orcl
主库节点二 lucifer02 10.211.55.101 11.2.0.4 orcl
备库 luciferdg 10.211.55.110 11.2.0.4 orcl

📢 以下几点需要注意：

  • db_unique_name 主备库不能相同
  • db_name主备库需保持一致
  • 主备库DB版本需保持一致

二、搭建过程

1、Oracle软件安装

使用我写的 Oracle 一键安装脚本，快速安装主库 RAC 和备库单机。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
cd ../racdb
vagrant up
vagrant ssh node1
su - root
cd /soft
sh rac_install.sh

AI写代码bash

* 1
* 2
* 3
* 4
* 5
* 6

Oracle 11G ADG 搭建 RAC to Single 详细教程（RMAN DUPLICATE）

简单等待一段时间，Oracle RAC 就安装成功了！

2、环境配置

搭建 ADG 之前，需要先
配置
一下环境信息，包括主机名解析以及 TNS。

1、配置 hosts 文件

主库：

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
##节点一
#dg
cat<<EOF>>/etc/hosts
10.211.55.110 luciferdg
EOF

##节点二
#dg
cat<<EOF>>/etc/hosts
10.211.55.110 luciferdg
EOF

AI写代码bash

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11

备库：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
##dg
cat<<EOF>>/etc/hosts
10.211.55.100 lucifer01
10.211.55.101 lucifer02
10.211.55.105 lucifer-scan
EOF

AI写代码bash

* 1
* 2
* 3
* 4
* 5
* 6

2、配置 TNS

主库+备库，在 root 用户下执行：

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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
##tnsnames.ora
su - oracle -c "cat <<EOF >> /u01/app/oracle/product/11.2.0/db/network/admin/tnsnames.ora
##FOR DG BEGIN
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lucifer-scan)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

ORCL1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lucifer01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

ORCL2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lucifer02)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
ORCLDG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = luciferdg)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
##FOR DG BEGIN
EOF"

AI写代码bash

* 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

3、主库开启归档和强制日志

开启
归档模式（需要停机）：

1
2
3
4
5
6
7
8
9
10
11
12
13
srvctl stop database -d orcl -o immediate
srvctl start instance -d orcl -i orcl1 -o mount
alter database archivelog;
alter database open;
srvctl start instance -d orcl -i oorcl2

AI写代码bash

* 1
* 2
* 3
* 4
* 5

开启强制日志模式（可在线开启）：

1
2
3
4
5
alter database force logging;

AI写代码sql

* 1

4、复制参数文件和密码文件至备库

复制参数文件至备库（备库执行），要在 oracle 用户下复制：

1
2
3
4
5
6
7
su - oracle
scp oracle@lucifer01:/tmp/initorcldg.ora /tmp

AI写代码bash

* 1
* 2

备库创建目录：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mkdir -p /u01/app/oracle/admin/orcl/adump
su - oracle -c "mkdir -p /oradata/orcl/datafile"
su - oracle -c "mkdir -p /oradata/orcl/onlinelog"
su - oracle -c "mkdir -p /oradata/orcl/tempfile"
mkdir -p /u01/app/oracle/fast_recovery_area/orcl
chown -R oracle:oinstall /u01/app/oracle/admin/orcl/adump
chown -R oracle:oinstall /u01/app/oracle/fast_recovery_area

AI写代码bash

* 1
* 2
* 3
* 4
* 5
* 6
* 7

备库修改参数文件：

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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
*._optimizer_cartesian_enabled=FALSE
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.control_files='/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_create_file_dest='/oradata/orcl'
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=5501878272
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.event='10949 trace name context forever:28401 trace name context forever,level 1:10849 trace name context forever, level 1:19823 trace name context forever, level 90'
*.open_cursors=300
*.pga_aggregate_target=196083712
*.processes=150
*.result_cache_max_size=0
*.sga_target=784334848
*.db_unique_name='orcldg'
*.log_archive_config='dg_config=(ORCLDG,ORCL)'
*.log_archive_dest_1='location=/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=ORCLDG'
*.log_archive_dest_2='service=orcl async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
*.remote_login_passwordfile='exclusive'
*.fal_server='ORCL'
*.fal_client='ORCLDG'
*.db_file_name_convert='+DATA','/oradata'
*.log_file_name_convert='+DATA','/oradata'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

AI写代码bash

* 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

复制密码文件至备库（备库执行），要在 oracle 用户下复制：

1
2
3
4
5
6
7
su - oracle
scp oracle@lucifer01:/u01/app/oracle/product/11.2.0/db/dbs/orapworcl1 /u01/app/oracle/product/11.2.0/db/dbs/orapworcl

AI写代码bash

* 1
* 2

5、主库添加 stanby log 文件

1
2
3
4
5
6
7
8
9
10
11
12
13
set pagesize100
set line222
col member for a60
select * from v$logfile;
select * from v$log;

AI写代码sql

* 1
* 2
* 3
* 4
* 5

📢 注意：

  • stanby log 日志大小与 redo log 日志保持一致
  • stanby log 数量： standby logfile=(1+logfile组数)*thread=(1+3)*1=4 组,需要加 4 组 standby logfile
  • thread 要与 redo log 保持一致，如果是 rac，需要增加多个 thread 对应的 standby log
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
ALTER DATABASE ADD STANDBY LOGFILE thread 1 
group 5 ('+DATA') SIZE 120M,
group 6 ('+DATA') SIZE 120M,
group 7 ('+DATA') SIZE 120M;

ALTER DATABASE ADD STANDBY LOGFILE thread 2
group 8 ('+DATA') SIZE 120M,
group 9 ('+DATA') SIZE 120M,
group 10 ('+DATA') SIZE 120M;

select * from v$standby_log;

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11

6、备库开启到 nomount 状态

1
2
3
4
5
startup nomount pfile='/tmp/initorcldg.ora';

AI写代码sql

* 1

3、 RMAN DUPLICATE

rman 恢复备库：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
rman target sys/oracle@orcl1 AUXILIARY sys/oracle@orcldg
 
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;
}

AI写代码bash

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9

主库设置 ADG 参数：

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
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCLDG)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcldg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDG';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
ALTER SYSTEM SET FAL_SERVER=ORCLDG;
ALTER SYSTEM SET FAL_CLIENT=ORCL;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/oradata','+DATA' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/oradata','+DATA'  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12

4、备库开启日志应用

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
-- 首先开启备库至只读状态
alter database open read only;
-- 开启主备同步进程
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
-- 检查备库角色
select database_role,open_mode from v$database;
   
DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY
-- 检查保护模式，最大性能
SQL> SELECT protection_mode FROM v$database;
 
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

AI写代码sql

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10
* 11
* 12
* 13
* 14
* 15
* 16

5、主库开启 LOG_ARCHIVE_DEST_STATE_2

确认主备之前没有问题，主库开启同步：

1
2
3
4
5
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

AI写代码sql

* 1

写在最后

ADG 的搭建过程比较简单，有很多朋友经常问，单机到单机，RAC 到单机，RAC到RAC的教程。其实，这几种搭建方式差别不大，只需要修改一些参数即可！