创建DG物理备库
简介
个人认为的DG搭建的难点
- 参数文件。编辑主库和备库的参数文件,指导DG进程工作。
- OracleNet(监听)配置
- 用Rman auxiliary的恢复备用数据库
具体步骤
主库准备:
- 启用强制日志记录模式
alter database force logging;
10:27:43 SYS AS SYSDBA@CSP> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
- 添加standby logfile
select GROUP#,BYTES/1024/1024 as "MB" ,status from v$log;
GROUP# MB STATUS
---------- ---------- ----------------
1 200 INACTIVE
2 200 INACTIVE
3 200 CURRENT
4 200 INACTIVE
5 200 INACTIVE
6 200 INACTIVE
11-FEB-25 SYS AS SYSDBA@CSP> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ---------------------------------------- --- ----------
6 ONLINE /oracle/CSP/origlogB/log_g6m1.dbf NO 0
6 ONLINE /oracle/CSP/mirrlogB/log_g6m2.dbf NO 0
5 ONLINE /oracle/CSP/origlogA/log_g5m1.dbf NO 0
5 ONLINE /oracle/CSP/mirrlogA/log_g5m2.dbf NO 0
4 ONLINE /oracle/CSP/origlogB/log_g4m1.dbf NO 0
4 ONLINE /oracle/CSP/mirrlogB/log_g4m2.dbf NO 0
3 ONLINE /oracle/CSP/origlogA/log_g3m1.dbf NO 0
3 ONLINE /oracle/CSP/mirrlogA/log_g3m2.dbf NO 0
2 ONLINE /oracle/CSP/origlogB/log_g2m1.dbf NO 0
2 ONLINE /oracle/CSP/mirrlogB/log_g2m2.dbf NO 0
1 ONLINE /oracle/CSP/origlogA/log_g1m1.dbf NO 0
1 ONLINE /oracle/CSP/mirrlogA/log_g1m2.dbf NO 0
alter database add standby logfile '/oracle/CSP/origlogB/standby01.dbf' size 500m;
alter database add standby logfile '/oracle/CSP/origlogA/standby01.dbf' size 500m;
alter database add standby logfile '/oracle/CSP/mirrlogA/standby01.dbf' size 500m;
alter database add standby logfile '/oracle/CSP/mirrlogB/standby01.dbf' size 500m;
16:36:13 SYS AS SYSDBA@CSP> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ---------------------------------------- --- ----------
6 ONLINE /oracle/CSP/origlogB/log_g6m1.dbf NO 0
6 ONLINE /oracle/CSP/mirrlogB/log_g6m2.dbf NO 0
5 ONLINE /oracle/CSP/origlogA/log_g5m1.dbf NO 0
5 ONLINE /oracle/CSP/mirrlogA/log_g5m2.dbf NO 0
4 ONLINE /oracle/CSP/origlogB/log_g4m1.dbf NO 0
4 ONLINE /oracle/CSP/mirrlogB/log_g4m2.dbf NO 0
3 ONLINE /oracle/CSP/origlogA/log_g3m1.dbf NO 0
3 ONLINE /oracle/CSP/mirrlogA/log_g3m2.dbf NO 0
2 ONLINE /oracle/CSP/origlogB/log_g2m1.dbf NO 0
2 ONLINE /oracle/CSP/mirrlogB/log_g2m2.dbf NO 0
1 ONLINE /oracle/CSP/origlogA/log_g1m1.dbf NO 0
1 ONLINE /oracle/CSP/mirrlogA/log_g1m2.dbf NO 0
7 STANDBY /oracle/CSP/origlogB/standby01.dbf NO 0
8 STANDBY /oracle/CSP/origlogA/standby01.dbf NO 0
9 STANDBY /oracle/CSP/mirrlogA/standby01.dbf NO 0
10 STANDBY /oracle/CSP/mirrlogB/standby01.dbf NO 0
为主库添加standby redo log,简要描述一下standby redo log的作用
实际上就是与主库接收到的重做日志相对应,也就是说备库调用RFS进程将从主库接收到的重做日志按顺序写入到standby logfile
在主库创建standby logfile是便于发生角色转换后备用
sandby redo log创建原则:
a)、确保standby redo log的大小与主库online redo log的大小一致
b)、如主库为单实例数据库:standby redo log组数=主库日志组总数+1
c)、如果主库是RAC数据库:standby redo log组数=(每线程的日志组数+1)*最大线程数
d)、不建议复用standby redo log,避免增加额外的I/O以及延缓重做传输
所以,我这次创建的standby logfile并不对。
要求归档模式
设置主库初始化参数
参考官方文档:create a physical standby...
create pfile from spfile;
vi initCSP.ora ----配置参数文件
shutdown immediate
create spfile from pfile;
File created.
startup
配置备库参数文件,并创建参数文件中的目录
参考官方文档
复制主库文件
oracsp@bjscpldbtest2:/oracle/CSP/19.0.0/dbs> cp orapwCSP /oracle/CSPSH/19.0.0/dbs
配置监听
需要为数据库配置静态注册,下面是编辑listener文件
SID_LIST_LSNR_CSP =
(SID_LIST =
(SID_DESC = (GLOBAL_DBNAME = CSP)
(ORACLE_HOME = /oracle/CSP/19.0.0)
(SID_NAME = CSP)
)
(SID_DESC = (GLOBAL_DBNAME = CSPSH)
(ORACLE_HOME = /oracle/CSP/19.0.0)
(SID_NAME = CSPSH)
)
)
LSNR_CSP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_CSP))
(ADDRESS = (PROTOCOL = TCP)(HOST = 109.6.52.14)(PORT = 1526)(IP = FIRST))
)
ADR_BASE_LSNR_CSP = /oracle/CSP
说明一下:能看到静态监听的名称是LSNR_CSP,然后SID_LIST_LSNR_CSP是静态监听的列表,我只需要将CSPSH注册在这里就行了
编辑tnsname文件,添加oracleNet 服务名称
主库和备库的都有
CSP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XXX.XXX)(PORT = 1526))
(ADDRESS = (PROTOCOL = TCP)(HOST = my_hostname)(PORT = 1526))
)
(CONNECT_DATA =
(SERVICE_NAME = CSP)
)
)
CSPSH =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XXX.XXX)(PORT = 1526))
(ADDRESS = (PROTOCOL = TCP)(HOST = my_hostname)(PORT = 1526))
)
(CONNECT_DATA =
(SERVICE_NAME = CSPSH)
)
)
----测试tns
oracsp@bjscpldbtest2:/oracle/CSP/19.0.0/network/admin> tnsping CSP
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 12-FEB-2025 15:18:16
Copyright (c) 1997, 2024, Oracle. All rights reserved.
Used parameter files:
/oracle/CSP/19.0.0/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 109.6.52.14)(PORT = 1526)) (ADDRESS = (PROTOCOL = TCP)(HOST = bjscpldbtest2)(PORT = 1526))) (CONNECT
_DATA = (SERVICE_NAME = CSP)))
OK (0 msec)
oracsp@bjscpldbtest2:/oracle/CSP/19.0.0/network/admin> tnsping CSPSH
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 13-FEB-2025 09:39:38
Copyright (c) 1997, 2024, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 109.6.52.14)(PORT = 1526)) (ADDRESS = (PROTOCOL = TCP)(HOST =
bjscpldbtest2)(PORT = 1526))) (CONNECT_DATA = (SERVICE_NAME = CSPSH)))
OK (0 msec)
用Rman复制备库
主库处于open状态,备库用参数文件启动到nomount状态。然后用rman连接两个数据库:
rman target / auxiliary sys/Alfred99#@CSPSH
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 13 10:15:47 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CSP (DBID=4144310150)
connected to auxiliary database: CSP (not mounted)
duplicate target database for standby from active database;
验证
----主库
select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
----备库
select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库