创建DG物理备库

简介

个人认为的DG搭建的难点

  1. 参数文件。编辑主库和备库的参数文件,指导DG进程工作。
  2. OracleNet(监听)配置
  3. 用Rman auxiliary的恢复备用数据库

具体步骤

主库准备:

  1. 启用强制日志记录模式
alter database force logging;
10:27:43 SYS AS SYSDBA@CSP> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES
  1. 添加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
posted @   老牛的田  阅读(1)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示