Oracle12c Windows 多实例ADG
Oracle DG英文全称Oracle Data Guard,DG提供全面的服务包括:创建、维护、管理、以及监控standby数据库,确保数据安全。DG用途是构建企业数据高可用应用环境。ADG实际是实现自动切换主备的意思,这里关于A暂时不做配置。
[toc]
1. 环境介绍
1.1 安装环境
在主机1上安装数据库软件,并建监听和实例,在主机2上安装数据库软件,并建监听,可以不建实例。
项目
主机1(主库)
主机2(备库)
操作系统
Windows server 2016
Windows server 2016
主机名称
db31
db32
IP地址
172.16.103.31
172.16.103.32
Oracle版本
Oracle 12.2.0.1
Oracle 12.2.0.1
BASE
D:\app\Administrator\virtual
D:\app\Administrator\virtual
SID
orcl1,orcl2
orcl1,orcl2
db_unique_name
db31_orcl1,db31_orcl2
db32_orcl1,db32_orcl2
1.2 多实例启动问题
1.2.1 现象
先部署orcl1实例,在部署orcl2实例。部署后orcl2能正常shutdown/startup,而orcl1在shutdown之后startup不能正常启动,提示没有pfile配置文件。官方解决办法需要指定pfile(Oracle多实例启动)。但是如果只是为了启动实例,后面的ADG同样还是配置不下去的,后面还有很多报错依旧。
1.2.2 分析
在分析日志报错以及日志文件,最多的显示是“指定文件不存在、或无法识别、或无权限访问”。请牢记这个错误提示就是windows下文件权限导致的。
如当前的环境,orcl1不能直接启动需指定pfile,但明明指定路径下是有spfile文件,它为什么非要去读pfile文件呢?问题就在于我们orcl1的spfile的权限不正常。打开spfile的属性安全设置会发现,只有orcl2实例在权限,没有orcl1的权限。
1.2.3 解决办法
通过上面的分析,我们只有把属于orcl1实例的spfile文件的权限修改为everyone修改权限即可。当然其他属于orcl1实例的文件也需要修改。这里还需要把PWDorcl1.ORA文件权限修改正确。
如何修改window文件权限,这里不阐述。
1.3 如何切换实例
切换实例在linux下是修改环境配置文件。在window下直接使用实例名即可。如:
// 登入orc1
sqlplus sys/123456@orc1 as sysdba
// 登入orc2
sqlplus sys/123456@orc2 as sysdba
2. 监听配置
多实例也好,单实例也罢,监听文件只需要一份。在配置时需要注意区分开来。用做ADG的lis和tns,有多少个实例就配置多组,但文件只要一份。
2.1 listener.ora(主端)
# listener.ora Network Configuration File: D:\app\Administrator\virtual\product\12.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\app\Administrator\virtual\product\12.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\virtual\product\12.2.0\dbhome_1\bin\oraclr12.dll")
)
(SID_DESC =
(GLOBAL_DBNAME = orcl1)
(ORACLE_HOME = D:\app\Administrator\virtual\product\12.2.0\dbhome_1)
(SID_NAME = orcl1)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\virtual\product\12.2.0\dbhome_1\bin\oraclr12.dll")
)
(SID_DESC =
(GLOBAL_DBNAME = orcl2)
(ORACLE_HOME = D:\app\Administrator\virtual\product\12.2.0\dbhome_1)
(SID_NAME = orcl2)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\virtual\product\12.2.0\dbhome_1\bin\oraclr12.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.103.31)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
2.2 listener.ora(备端)
# listener.ora Network Configuration File: D:\app\Administrator\virtual\product\12.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\app\Administrator\virtual\product\12.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\virtual\product\12.2.0\dbhome_1\bin\oraclr12.dll")
)
(SID_DESC =
(GLOBAL_DBNAME = orcl1)
(ORACLE_HOME = D:\app\Administrator\virtual\product\12.2.0\dbhome_1)
(SID_NAME = orcl1)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\virtual\product\12.2.0\dbhome_1\bin\oraclr12.dll")
)
(SID_DESC =
(GLOBAL_DBNAME = orcl2)
(ORACLE_HOME = D:\app\Administrator\virtual\product\12.2.0\dbhome_1)
(SID_NAME = orcl2)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\virtual\product\12.2.0\dbhome_1\bin\oraclr12.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.1