Oracle 18c DG搭建手册完整版(DBCA方式) -- cnDBA.cn

1.环境说明

本文档仅用于测试搭建Oracle 18c DG，没有任何性能参数方面的调整。而且故意将主备库的数据文件存放目录设置不同，为了之后一些问题的测试。

	主库	备库
IP地址	192.168.1.166	192.168.1.167
数据库SID	leidb	leidb
DB_UNIQUE_NAME	leidb_p http://www.cndba.cn/Expect-le/article/3072	leidb_s
数据文件路径	/u01/app/oracle/oradata	/u01/app/oracle/oradata_s
归档路径	/u01/archivelog	/u01/archivelog_s

2.主备库的数据库安装

主库安装数据库软件和创建数据库

备库只需要安装数据库软件和创建相关目录即可

2.1. 操作系统环境配置

如果没有特殊说明，下面每一步都需要在主备库上执行。

目前Oracle官方网站上放出了18.3版本的下载，今天测试一下18.3的RAC安装。

https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle18c-linux-180000-5022980.html

2.1.1. 配置host

cat /etc/hosts

127.0.0.1 localhost

192.168.1.166 lei-p

192.168.1.167 lei-s

2.1.2. 添加用户和组

/usr/sbin/groupadd -g 54321 oinstall

/usr/sbin/groupadd -g 54322 dba

/usr/sbin/groupadd -g 54323 oper

/usr/sbin/useradd -u 54321 -g oinstall -G dba ,oper oracle

设置密码

passwd oracle

Changing password for user oracle.

New password:

BAD PASSWORD: The password is shorter than 8 characters

Retype new password:

passwd: all authentication tokens updated successfully.

2.1.3. 关闭防火墙和selinux

防火墙：

systemctl stop firewalld.service

systemctl disable firewalld.service

rm '/etc/systemd/system/basic.target.wants/firewalld.service'

rm '/etc/systemd/system/dbus-org.Fedoraproject.FirewallD1.service'

SELINUX：

cat /etc/selinux/config

# This file controls the state of SELinux on the system.

# SELINUX= can take one of these three values:

# enforcing - SELinux security policy is enforced.

# permissive - SELinux prints warnings instead of enforcing.

# disabled - No SELinux policy is loaded.

SELINUX=disabled

# SELINUXTYPE= can take one of these two values:

# targeted - Targeted processes are protected,

# mls - Multi Level Security protection.

SELINUXTYPE=targeted

2.1.4. 创建目录

mkdir -p /u01/app/oracle/product/18.3.0/db_1

chown -R oracle:oinstall /u01/

chmod -R 775 /u01/

2.1.5. 配置用户环境变量·

2.1.5.1. ORACLE用户

在/home/oracle/.bash_profile中添加以下内容

ORACLE_SID=leidb;export ORACLE_SID

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/18.3.0/db_1; export ORACLE_HOME

PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin:$ORA_CRS_HOME/bin

PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin

export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib

LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib

LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib

export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE

CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib

CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib

CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib

export CLASSPATH

export TEMP=/tmp

export TMPDIR=/tmp

umask 022

2.1.6. 修改资源限制

2.1.6.1. 修改/etc/security/limits.conf

cat >> /etc/security/limits.conf <

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

oracle soft stack 10240

oracle hard stack 32768

oracle soft memlock 3145728

oracle hard memlock 3145728

EOF

2.1.7. 修改内核参数

vi /etc/sysctl.conf

fs.file-max = 6815744

kernel.sem = 250 32000 100 128

kernel.shmmni = 4096

kernel.shmall = 1073741824

kernel.shmmax = 4398046511104

kernel.panic_on_oops = 1

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

net.ipv4.conf.all.rp_filter = 2

net.ipv4.conf.default.rp_filter = 2

fs.aio-max-nr = 1048576

net.ipv4.ip_local_port_range = 9000 65500

生效

sysctl -p

2.1.8. 安装必要的包

在Redhat 7.*镜像文件中没有了compat-libstdc++-33包，需要单独下载。

yum install binutils compat-libstdc++-33 gcc gcc-c++ glibc glibc.i686 glibc-devel ksh libgcc.i686 libstdc++-devel libaio libaio.i686 libaio-devel libaio-devel.i686 libXext libXext.i686 libXtst libXtst.i686 libX11 libX11.i686 libXau libXau.i686 libxcb libxcb.i686 libXi libXi.i686 make sysstat unixODBC unixODBC-devel zlib-devel zlib-devel.i686 compat-libcap1 -y

2.1.9. 安装数据库软件

由于从12.2开始，Oracle将采用ZIP格式直接将Oracle_HOME下的内容直接压缩到一个ZIP格式文件中，所以直接解压到ORACLE_HOME下即可。例如：

[oracle@lei-p software]$ unzip -d /u01/app/oracle/product/18.3.0/db_1/ LINUX.X64_180000_db_home.zip

[oracle@lei-p software]$ cd /u01/app/oracle/product/18.3.0/db_1/

[oracle@lei-p db_1]$ ./runInstaller

如果是单实例则选择第一个

选择企业版

修改为新的ORACLE_BASE目录

默认

安装前检查，内存不符合要求。18c建议的内存为8G

正在安装

执行root.sh脚本

2.1.10. DBCA创建数据库

仅在主库执行即可。

这里直接启用归档

3. 主库启动FORCE LOGGING

在CDB root中执行以下命令

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING

--------------------------------------------------------------------------------

YES

4. 主库启动归档模式

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

Database altered.

修改归档目录（建议修改），备库为：/u01/archive_s

SQL> alter system set log_archive_dest_1='location=/u01/archivelog' scope=both;--直接生效

System altered.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/archivelog

Oldest online log sequence 2

Next log sequence to archive 4

Current log sequence 4

SQL> alter database open;

Database altered.

5. 在主库添加 standby redo log

在CDB root中添加standby redo log，实际上在主库上是用不到standby redo log的。但是为了方便以后的switchover等操作就直接添加了，而且后面搭建备库时备库也会被创建。

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> select group#, members, bytes from v$log;

GROUP# MEMBERS BYTES

---------- ---------- ----------

1 1 209715200 --可以看到默认大小以及是200M了，一起是50M。

2 1 209715200

3 1 209715200

SQL>

SQL> select member from v$logfile;

MEMBER

-------------------------------------------------------------------------------------------------------

/u01/app/oracle/oradata/LEIDB/redo01.log

/u01/app/oracle/oradata/LEIDB/redo02.log

/u01/app/oracle/oradata/LEIDB/redo03.log

--要比online redo log多一组

alter database add standby logfile '/u01/app/oracle/oradata/LEIDB/stdredo01.log' size 200M;

alter database add standby logfile '/u01/app/oracle/oradata/LEIDB/stdredo02.log' size 200M;

alter database add standby logfile '/u01/app/oracle/oradata/LEIDB/stdredo03.log' size 200M;

alter database add standby logfile '/u01/app/oracle/oradata/LEIDB/stdredo04.log' size 200M;

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/LEIDB/redo03.log

/u01/app/oracle/oradata/LEIDB/redo02.log

/u01/app/oracle/oradata/LEIDB/redo01.log

/u01/app/oracle/oradata/LEIDB/stdredo01.log

/u01/app/oracle/oradata/LEIDB/stdredo02.log

/u01/app/oracle/oradata/LEIDB/stdredo03.log

/u01/app/oracle/oradata/LEIDB/stdredo04.log

7 rows selected

6. 在主库创建pfile 文件并修改pfile 内容

SQL> create pfile='/tmp/initleidb.ora' from spfile;

File created.

在pfile中添加如下内容：

[oracle@host1 dbs]$ vi /u01/app/oracle/product/18.1.0/dbhome_1/dbs/initcndba.ora

*.db_unique_name='leidb_p'

*.log_archive_config='dg_config=(leidb_p,leidb_s)'

*.log_archive_dest_1='location=/u01/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=leidb_p'

*.log_archive_dest_2='service=leidb_s affirm sync valid_for=(online_logfiles,primary_role) db_unique_name=leidb_s'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.standby_file_management='auto'

*.fal_server='leidb_s'

*.log_file_name_convert='/u01/app/oracle/oradata/LEIDB','/u01/app/oracle/oradata_s/LEIDB' --如果你的环境主备库数据文件目录相同，可以不需要这两个参数。

*.db_file_name_convert='/u01/app/oracle/oradata/LEIDB','/u01/app/oracle/oradata_s/LEIDB'

用新参数重启数据库：

SQL> shutdown immediate

SQL> create spfile from pfile='/tmp/initleidb.ora';

SQL> startup

7. 主备库配置静态监听并启动

这里最好通过netmgr来配置，因为监听文件对格式要求比较高。

[oracle@lei-p admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/18.3.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = leidb)

(ORACLE_HOME = /u01/app/oracle/product/18.3.0/db_1)

(SID_NAME = leidb)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = lei-p)(PORT = 1521))

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

ADR_BASE_LISTENER = /u01/app/oracle

重启监听或直接reload

[oracle@host1 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 17-JUL-2018 11:41:07

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521)))

The command completed successfully

8. 主备库配置tnsnames.ora

[oracle@host1 admin]$ cat /u01/app/oracle/product/18.1.0/dbhome_1/network/admin/tnsnames.ora

主备库添加以下内容：

LEIDB_S =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = lei-s)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = leidb)

)

LEIDB_P =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = lei-p)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = leidb)

)

配置完成后，使用tnsping 命令检查是否能ping通

[oracle@host1 admin]$ tnsping pdbcndba_p

TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 17-JUL-2018 11:45:32

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.88)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cndba)))

OK (30 msec)

[oracle@lei-p admin]$ tnsping leidb_s

TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 17-SEP-2018 15:23:33

Used parameter files:

/u01/app/oracle/product/18.3.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = lei-s)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = leidb)))

OK (10 msec)

9. 通过DBCA来创建物理备库

相比于RMAN来说感觉更为简单，不需要拷贝密码文件，创建目录，语法如下：

红色部分是相当于参数文件中修改的参数。

-initParams instance_name指定以逗号分隔的初始化参数，如果参数值中有逗号(,)那么需要用转义字符(/),也可以通过-initParamsEscapeChar来指定转义字符。

[oracle@lei-s LEIDB]$ dbca -silent -createDuplicateDB -primaryDBConnectionString lei-p:1521/leidb -gdbName leidb -sid leidb /

-initParams instance_name=leidb, /

log_archive_dest_1='location=/u01/archive valid_for=(all_logfiles,all_roles) db_unique_name=leidb_s', /

log_archive_dest_2='service=leidb_p affirm sync valid_for=(online_logfiles,primary_role) db_unique_name=leidb_p', /

log_file_name_convert='/u01/app/oracle/oradata_s/LEIDB'/,'/u01/app/oracle/oradata/LEIDB', /

db_file_name_convert='/u01/app/oracle/oradata_s/LEIDB'/,'/u01/app/oracle/oradata/LEIDB', /

fal_server='leidb_s', /

log_archive_config='dg_config=(leidb_p,leidb_s)', /

standby_file_management='auto' /

-datafileDestination '/u01/app/oracle/oradata_s/LEIDB' / --备库数据文件目录

-createAsStandby -dbUniqueName leidb_s --备库db_unique_name

Enter SYS user password:

Prepare for db operation

22% complete

Listener config step

44% complete

Auxiliary instance creation

67% complete

RMAN duplicate

89% complete

Post duplicate database operations

100% complete

Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/leidb_s/leidb_s0.log" for further details.

注：这里很奇怪在创建备库时指定了几个初始化参数，但是创建完成后查看相应的几个参数值也没变化。

10. 修改备库初始化参数

虽然在DBCA时不需要修改初始化参数文件，但是在创建完成之后需要修改一些必要的初始化参数以用来以后的switchover等操作。

SQL> create pfile from spfile;

--备库修改参数文件需要修改的内容：

*.control_files='/u01/app/oracle/oradata_s/LEIDB/control01.ctl','/u01/app/oracle/fast_recovery_area/LEIDB/control02.ctl'

*.db_unique_name='leidb_s'

*.log_archive_config='dg_config=(leidb_p,leidb_s)'

*.log_archive_dest_1='location=/u01/archivelog_s valid_for=(all_logfiles,all_roles) db_unique_name=leidb_s'

*.log_archive_dest_2='service=leidb_p affirm sync valid_for=(online_logfiles,primary_role) db_unique_name=leidb_p'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.standby_file_management='auto'

*.fal_server='leidb_p'

*.log_file_name_convert='/u01/app/oracle/oradata_s/LEIDB','/u01/app/oracle/oradata/LEIDB'

*.db_file_name_convert='/u01/app/oracle/oradata_s/LEIDB','/u01/app/oracle/oradata/LEIDB'

用新的spfile文件重启数据

shutdown immediate

create pfile from spfile;

startup

11. 启用MRP

--启动日志应用

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

------------------------------------------------------------

READ ONLY WITH APPLY

--18c新视图，用于查看DG的进程详细信息，取代了v$managed_standby视图。

SQL> select name,type,role,action,group# from V$DATAGUARD_PROCESS;

NAME TYP ROLE ACTION GROUP#

----- --- ------------------------ ------------ ----------

LGWR KSB log writer IDLE 0

TMON KSB redo transport monitor IDLE 0

TT00 KSV gap manager IDLE 0

TT01 KSV redo transport timer IDLE 0

ARC0 KSB archive local IDLE 0

ARC1 KSB archive redo IDLE 0

ARC2 KSB archive redo IDLE 0

ARC3 KSB archive redo IDLE 0

rfs NET RFS ping IDLE 0

rfs NET RFS sync IDLE 4

rfs NET RFS archive IDLE 0

NAME TYP ROLE ACTION GROUP#

----- --- ------------------------ ------------ ----------

MRP0 KSB managed recovery APPLYING_LOG 0

12 rows selected.

注：v$managed_standby视图已被V$DATAGUARD_PROCESS取代。

Oracle 18c DG DBCA

签到成功

CNDBA社区