ADG单实例搭建系列之（Active Database Du

发表于

「这是我参与11月更文挑战的第17天，活动详情查看：2021最后一次更文挑战」

参考自：Data Guard Physical Standby Setup in Oracle Database 11g Release 2

MOS文档：Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE…FROM ACTIVE DATABASE (Doc ID 1075908.1)

官方文档：Duplicating Databases

一、Active Database Duplicate步骤（Using Image Copies）

1
2
3
4
5
erlang复制代码1.配置主库DG参数，备库根据主库的PFILE,设置参数值,生成备库SPFILE.
2.配置hosts文件，配置TNS，配置静态监听，添加standby log文件.
3.拷贝主库的密码文件至备库，备库创建PFILE中不存在的目录.
4.把备库启动到nomount状态.
5.RMAN同时连接主库与备库，执行duplicate命令.

Description of Figure 25-4 follows

注：由于Active Database Duplicate无需提前备份，而是通过网络在线copy数据库文件，对主库的CPU等负载要求较高，因此最好在空闲时间进行操作，对于TB级别的数据库，使用Active Duplicate进行DG搭建效率较高，节省空间，但是对网络要求较高；源库必须使用SPFILE。

二、环境准备

主机名 ip DB Version db_name db_unique_name
主库 orcl 192.168.56.120 11.2.0.4 orcl orcl
备库 orcl_stby 192.168.56.121 11.2.0.4 orcl orcl_stby

Notes：

1、db_unique_name主备库不能相同。

2、db_name主备库需保持一致。

3、主备库DB版本需保持一致。

三、搭建过程

1、Oracle软件安装

主库一键安装：

1
bash复制代码./AllOracleSilent.sh -i 192.168.56.120 -d 11g -n orcl -o orcl -b /u01/app -s AL32UTF8

备库一键安装：（备库仅安装ORACLE软件，不建库）

1
bash复制代码./AllOracleSilent.sh -i 192.168.56.121 -d 11g -w Y -n orcl_stby -o orcl -b /u01/app -s AL32UTF8

一键安装脚本可参考：ORACLE一键安装单机11G/12C/18C/19C并建库脚本

2、环境配置

a.配置hosts文件

主库：

1
2
3
4
5
bash复制代码cat <<EOF >> /etc/hosts
##FOR DG BEGIN
192.168.56.121 orcl_stby
##FOR DG END
EOF

备库：

1
2
3
4
5
bash复制代码cat <<EOF >> /etc/hosts
##FOR DG BEGIN
192.168.56.120 orcl
##FOR DG END
EOF

b.配置静态监听和TNS

主库+备库：

Notes：注意这里的GLOBAL_DBNAME和service_name保持一致，即备库需要改为 orcl_stby。

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
bash复制代码##listener.ora
su - oracle -c "cat <<EOF >> /u01/app/oracle/product/11.2.0/db/network/admin/listener.ora
##FOR DG BEGIN
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
      (SID_NAME = orcl)
    )
  )
##FOR DG END
EOF"

##重启监听
su - oracle -c "lsnrctl stop"
su - oracle -c "lsnrctl start"

##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 = orcl)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

ORCL_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl_stby)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl_stby)
    )
  )
##FOR DG BEGIN
EOF"

c.主库配置参数

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
sql复制代码select log_mode,force_logging from gv$database;

LOG_MODE     FOR
------------ ---
NOARCHIVELOG NO

--开启归档模式
shutdown immediate
startup mount
alter database archivelog;
alter database open;

--开启强制日志模式
alter database force logging;

--查看数据文件路径是否一致，OMF参数建议关闭
select name from v$datafile;
show parameter db_create_file_dest
alter system reset db_create_file_dest;
--NOTES:如果数据文件路径不一致，duplicate将失败。

--设置DG参数
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCL_STBY)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl_stby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_STBY';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;
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=ORCL_STBY;
ALTER SYSTEM SET FAL_CLIENT=ORCL;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/oradata/orcl','/oradata/orcl' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/oradata/orcl','/oradata/orcl'  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

d.生成备库pfile文件并修改，复制参数文件和密码文件至备库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
bash复制代码create pfile='/tmp/initorcl_stby.ora' from spfile;

--修改的部分：
*.db_unique_name=orcl_stby
*.fal_client='ORCL_STBY'
*.fal_server='ORCL'
*.log_archive_config='DG_CONFIG=(ORCL_STBY,ORCL)'
*.log_archive_dest_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL_STBY'
*.log_archive_dest_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'

--复制参数文件至备库（备库执行）
scp oracle@orcl:/tmp/initorcl_stby.ora /tmp

--复制密码文件至备库（备库执行）,要在oracle用户下复制
su - oracle
scp oracle@orcl:/u01/app/oracle/product/11.2.0/db/dbs/orapworcl /u01/app/oracle/product/11.2.0/db/dbs

e.主库添加stanby log文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
sql复制代码set line222
col member for a60
select t2.thread#,t1.group#,t1.member,t2.bytes/1024/1024 from gv$logfile t1,gv$log t2 where t1.group#=t2.group#;

   THREAD#     GROUP# MEMBER                                                       T2.BYTES/1024/1024
---------- ---------- ------------------------------------------------------------ ------------------
         1          3 /oradata/orcl/redo03.log                                                    120
         1          2 /oradata/orcl/redo02.log                                                    120
         1          1 /oradata/orcl/redo01.log                                                    120

--需要注意：
--1.stanby log日志大小与redo log日志保持一致
--2.stanby log数量： 
standby logfile=(1+logfile组数)*thread=(1+3)*1=4组,需要加4组standby logfile.
--3.thread要与redo log保持一致，如果是rac，需要增加多个thread对应的standby log

ALTER DATABASE ADD STANDBY LOGFILE thread 1 
group 4 ('/oradata/orcl/standby_redo04.log') SIZE 120M,
group 5 ('/oradata/orcl/standby_redo05.log') SIZE 120M,
group 6 ('/oradata/orcl/standby_redo06.log') SIZE 120M,
group 7 ('/oradata/orcl/standby_redo07.log') SIZE 120M;

f.备库创建db目录，开启到nomount状态

1
2
3
4
5
6
7
bash复制代码su - oracle -c "mkdir -p /oradata/orcl"
su - oracle -c "mkdir -p /u01/app/oracle/fast_recovery_area/orcl"
su - oracle -c "mkdir -p /u01/app/oracle/admin/orcl/adump"

sqlplus / as sysdba
create spfile from pfile='/tmp/initorcl_stby.ora';
startup nomount

3、 RMAN DUPLICATE

1
2
3
4
5
6
7
8
9
bash复制代码rman target sys/oracle@orcl AUXILIARY sys/oracle@orcl_stby

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;
}

4、备库开启日志应用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
sql复制代码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

5、主库开启LOG_ARCHIVE_DEST_STATE_2

1
sql复制代码ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

6、测试同步情况

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
sql复制代码set line222
col member for a60

--查看是否存在RFS和MRP进程
select process,group#,thread#,sequence# from gv$managed_standby;

--查看standby日志status是否存在active
select t1.group#,t1.thread#,t1.bytes/1024/1024,t1.status,t2.member from gv$standby_log t1,gv$logfile t2 where t1.group#=t2.group#;

--主库建表空间，建用户，建表，增删改测试
create tablespace TEST datafile '/oradata/orcl/test01.dbf' size 100M autoextend off;
create user test identified by test;
grant dba to test;
conn test/test
create table test(id number);
insert into test values (1);
insert into test values (2);
commit;

--备库查看是否同步

SQL> select tablespace_name from dba_tablespaces where tablespace_name='TEST';

TABLESPACE_NAME
------------------------------
TEST

SQL> select username,account_status,created from dba_users where username='TEST';

USERNAME                       ACCOUNT_STATUS                   CREATED
------------------------------ -------------------------------- ------------------
TEST                           OPEN                             17-APR-21

SQL> select * from test.test;

        ID
----------
         1
         2

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%