当前位置： 首页 > news >正文

Oracle adg环境下调整redo日志组以及standby日志组大小

news 2025/5/13 5:37:15

1.在adg环境中，调整redo日志组大小以及standby日志组大小主要思路如下：
a、先备库增加standby redo 删除老standby redo,
b、然后主库增加redo删除老redo,
c、备库增加新redo删除老redo,
d、最后主库增加standby redo。

#主库
[oracle@DB196 ~]$ sql / as sysdba
SQL> SET SQLFORMAT ansiconsole
 
select group#,thread#,sequence#,bytes/1024/1024 mb,members,archived,status from v$log;
    GROUP#    THREAD#  SEQUENCE#         MB    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
        15          1        100        100          1 YES INACTIVE
        16          1        101        100          1 YES INACTIVE
        17          1        102        100          1 NO  CURRENT
        18          1         98        100          1 YES INACTIVE
 
select group#,thread#,sequence#,bytes/1024/1024 mb,archived,status from V$STANDBY_LOG;
    GROUP#    THREAD#  SEQUENCE#         MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------
        20          1          0        100 YES UNASSIGNED
        21          1          0        100 YES UNASSIGNED
        22          1          0        100 YES UNASSIGNED
        23          1          0        100 YES UNASSIGNED
        24          1          0        100 YES UNASSIGNED 
 
select group#,status,type,member from v$logfile;
    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ------------------------------------------------------------
        20         STANDBY /u01/app/oracle/oradata/PROD/redo20.log
        21         STANDBY /u01/app/oracle/oradata/PROD/redo21.log
        22         STANDBY /u01/app/oracle/oradata/PROD/redo22.log
        23         STANDBY /u01/app/oracle/oradata/PROD/redo23.log
        24         STANDBY /u01/app/oracle/oradata/PROD/redo24.log
        15         ONLINE  /u01/app/oracle/oradata/onlinelogredo05.log
        16         ONLINE  /u01/app/oracle/oradata/onlinelogredo06.log
        17         ONLINE  /u01/app/oracle/oradata/onlinelogredo07.log
        18         ONLINE  /u01/app/oracle/oradata/onlinelogredo08.log
 
 
#备库
[oracle@DB197 ~]$ sql / as sysdba
SQL> SET SQLFORMAT ansiconsole
 
select group#,thread#,sequence#,bytes/1024/1024 mb,members,archived,status from v$log;
    GROUP#    THREAD#  SEQUENCE#         MB    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
        15          1        100        100          1 YES CLEARING
        16          1        101        100          1 YES CLEARING
        17          1        102        100          1 YES CURRENT
        18          1          0        100          1 YES UNUSED
 
select group#,thread#,sequence#,bytes/1024/1024 mb,archived,status from V$STANDBY_LOG;
    GROUP#    THREAD#  SEQUENCE#         MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------
        20          1        102        100 YES ACTIVE
        21          1          0        100 YES UNASSIGNED
        22          1          0        100 YES UNASSIGNED
        23          1          0        100 YES UNASSIGNED
        24          1          0        100 YES UNASSIGNED 
 
select group#,status,type,member from v$logfile;
    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- -----------------------------------------------------------------
        20         STANDBY /u01/app/oracle/oradata/PROD/redo20.log
        21         STANDBY /u01/app/oracle/oradata/PROD/redo21.log
        22         STANDBY /u01/app/oracle/oradata/PROD/redo22.log
        23         STANDBY /u01/app/oracle/oradata/PROD/redo23.log
        24         STANDBY /u01/app/oracle/oradata/PROD/redo24.log
        15         ONLINE  /u01/app/oracle/oradata/onlinelogredo05.log
        16         ONLINE  /u01/app/oracle/oradata/onlinelogredo06.log
        17         ONLINE  /u01/app/oracle/oradata/onlinelogredo07.log
        18         ONLINE  /u01/app/oracle/oradata/onlinelogredo08.log
 

2.备库取消同步
alter database recover managed standby database cancel;
 
DGMGRL> show configuration;
Configuration - ADGbroker
  Protection Mode: MaxAvailability
  Members:
  MPCDB196 - Primary database
    MPCDB197 - Physical standby database 
      Error: ORA-16810: multiple errors or warnings detected for the member
 
Fast-Start Failover: DISABLED
 
Configuration Status:
ERROR   (status updated 7 seconds ago)
3.备库standby添加

alter database add standby logfile group 10 '/u01/app/oracle/oradata/PROD/stdredo10.log' size 50m reuse;
alter database add standby logfile group 11 '/u01/app/oracle/oradata/PROD/stdredo11.log' size 50m reuse;
alter database add standby logfile group 12 '/u01/app/oracle/oradata/PROD/stdredo12.log' size 50m reuse;
alter database add standby logfile group 13 '/u01/app/oracle/oradata/PROD/stdredo13.log' size 50m reuse;
alter database add standby logfile group 14 '/u01/app/oracle/oradata/PROD/stdredo14.log' size 50m reuse;

4.备库standby删除

alter database drop logfile group 20;
alter database drop logfile group 21;
alter database drop logfile group 22;
alter database drop logfile group 23;
alter database drop logfile group 24;
 
SQL>  alter database drop logfile group 4;
ERROR at line 1:
ORA-00261: log 4 of thread 1 is being archived or modified
ORA-00312: online log 4 thread 1: '/u01/app/oradata/orcl/stredo02.log'
--如在清除standby redo log组时出现上面出错信息执行下面的命令清理
alter database clear  logfile group 20;
 
#到操作系统删除组4、5、6、7
[oracle@DB197 MPCDB]$ ls stdredo0*
stdredo01.log  stdredo02.log  stdredo03.log  stdredo04.log
 
[oracle@DB197 MPCDB]$ rm stdredo0*

5.主库redo添加

alter database add logfile group 1 '/u01/app/oracle/oradata/PROD/redo01.log' size 50m reuse;
alter database add logfile group 2 '/u01/app/oracle/oradata/PROD/redo02.log' size 50m reuse;
alter database add logfile group 3 '/u01/app/oracle/oradata/PROD/redo03.log' size 50m reuse;
alter database add logfile group 4 '/u01/app/oracle/oradata/PROD/redo04.log' size 50m reuse;


6.主库redo 删除
select group#,thread#,sequence#,bytes/1024/1024 mb,members,archived,status from v$log;

alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
 
alter database drop logfile group 15;
alter database drop logfile group 16;
alter database drop logfile group 17;
alter database drop logfile group 18;
 
到操作系统删除组1、2、3
[oracle@DB196 MPCDB]$ ls redo0*
redo01.log  redo02.log  redo03.log
[oracle@DB196 MPCDB]$ rm redo0*
7.备库redo添加

alter system set standby_file_management='manual';
 
alter database add logfile group 1 '/u01/app/oracle/oradata/PROD/redo01.log' size 50m reuse;
alter database add logfile group 2 '/u01/app/oracle/oradata/PROD/redo02.log' size 50m reuse;
alter database add logfile group 3 '/u01/app/oracle/oradata/PROD/redo03.log' size 50m reuse;
alter database add logfile group 4 '/u01/app/oracle/oradata/PROD/redo04.log' size 50m reuse;


8.备库redo删除

 
show parameter NAME_CONVERT 
NAME                  TYPE   VALUE                                                          
--------------------- ------ -------------------------------------------------------------- 
db_file_name_convert  string /u01/app/oracle/oradata/MPCDB/, /u01/app/oracle/oradata/MPCDB/ 
log_file_name_convert string /u01/app/oracle/oradata/MPCDB/, /u01/app/oracle/oradata/MPCDB/ 
pdb_file_name_convert string 
 
#A 如 db_file_name_convert  、log_file_name_convert  为空则如下
SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/MPCDB/','/u01/app/oracle/oradata/MPCDB/' scope=spfile;
SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/MPCDB/','/u01/app/oracle/oradata/MPCDB/' scope=spfile;
备库的 log_file_name_convert 参数要设置，否则无法运行 alter database clear logfile group xxx; 语句
SQL> shutdown immediate
SQL> startup 
SQL> select open_mode,database_role from v$database;
OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY            PHYSICAL STANDBY
SQL> select group#,status from v$log;
SQL> show parameter file_name_convert
 
#B 否则如下语句则包错
SQL> alter database drop logfile group 1;
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance wasudb (thread 1)
ORA-00312: online log 1 thread 1: '/data/oradata/wasudb/redo01.log'
 
SQL>  ALTER DATABASE CLEAR LOGFILE GROUP 1;
ERROR at line 1:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/data/oradata/wasudb/redo01.log'
 
alter database clear logfile group 15;
alter database clear logfile group 16;
alter database clear logfile group 17;
alter database clear logfile group 18;

alter database drop logfile group 15;
alter database drop logfile group 16;
alter database drop logfile group 17;
alter database drop logfile group 18;

select group#,status,type,member from v$logfile;
 
#到操作系统删除组1、2、3
[oracle@DB197 MPCDB]$ ls redo0*
redo01.log  redo02.log  redo03.log
[oracle@DB197 MPCDB]$ rm redo0*

9.主库standby 删除
select group#,thread#,sequence#,bytes/1024/1024 mb,archived,status from V$STANDBY_LOG;

alter database drop logfile group 20;
alter database drop logfile group 21;
alter database drop logfile group 22;
alter database drop logfile group 23;
alter database drop logfile group 24;

 
#到操作系统删除组4、5、6、7
[oracle@DB196 MPCDB]$ ls stdredo0*
stdredo01.log  stdredo02.log  stdredo03.log  stdredo04.log
 
[oracle@DB196 MPCDB]$ rm stdredo0*
10.主库standby 添加


alter database add standby logfile group 10 '/u01/app/oracle/oradata/PROD/stdredo10.log' size 50m reuse;
alter database add standby logfile group 11 '/u01/app/oracle/oradata/PROD/stdredo11.log' size 50m reuse;
alter database add standby logfile group 12 '/u01/app/oracle/oradata/PROD/stdredo12.log' size 50m reuse;
alter database add standby logfile group 13 '/u01/app/oracle/oradata/PROD/stdredo13.log' size 50m reuse;
alter database add standby logfile group 14 '/u01/app/oracle/oradata/PROD/stdredo14.log' size 50m reuse;

select group#,thread#,sequence#,bytes/1024/1024 mb,archived,status from V$STANDBY_LOG;


11.恢复同步和备库文件自动管理（备库执行）

alter system set standby_file_management='AUTO';
alter database recover managed standby database using current logfile disconnect;
 
DGMGRL> show configuration;
Configuration - ADGbroker
  Protection Mode: MaxAvailability
  Members:
  MPCDB196 - Primary database
    MPCDB197 - Physical standby database 
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS   (status updated 6 seconds ago)
12.验证同步是否恢复

#主库
select open_mode from v$database;
OPEN_MODE   
READ WRITE  
 
select group#,thread#,sequence#,bytes/1024/1024 mb,members,archived,status from v$log;

    GROUP#    THREAD#  SEQUENCE#         MB    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
         1          1        108         50          1 YES INACTIVE
         2          1        109         50          1 YES INACTIVE
         3          1        110         50          1 YES INACTIVE
         4          1        111         50          1 NO  CURRENT
 
select group#,thread#,sequence#,bytes/1024/1024 mb,archived,status from V$STANDBY_LOG order by 1;

    GROUP#    THREAD#  SEQUENCE#         MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------
        10          0          0         50 YES UNASSIGNED
        11          0          0         50 YES UNASSIGNED
        12          0          0         50 YES UNASSIGNED
        13          0          0         50 YES UNASSIGNED
        14          0          0         50 YES UNASSIGNED
 
select group#,status,type,member from v$logfile order by 1;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ------------------------------------------------------------
         1         ONLINE  /u01/app/oracle/oradata/PROD/redo01.log
         2         ONLINE  /u01/app/oracle/oradata/PROD/redo02.log
         3         ONLINE  /u01/app/oracle/oradata/PROD/redo03.log
         4         ONLINE  /u01/app/oracle/oradata/PROD/redo04.log
        10         STANDBY /u01/app/oracle/oradata/PROD/stdredo10.log
        11         STANDBY /u01/app/oracle/oradata/PROD/stdredo11.log
        12         STANDBY /u01/app/oracle/oradata/PROD/stdredo12.log
        13         STANDBY /u01/app/oracle/oradata/PROD/stdredo13.log
        14         STANDBY /u01/app/oracle/oradata/PROD/stdredo14.log
 
#备库
select open_mode from v$database;
OPEN_MODE             
READ ONLY WITH APPLY  
 
select group#,thread#,sequence#,bytes/1024/1024 mb,members,archived,status from v$log order by 1;

    GROUP#    THREAD#  SEQUENCE#         MB    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
         1          1          0         50          1 YES UNUSED
         2          1          0         50          1 YES UNUSED
         3          1        110         50          1 YES CLEARING
         4          1        111         50          1 YES CURRENT
 
select group#,thread#,sequence#,bytes/1024/1024 mb,archived,status from V$STANDBY_LOG order by 1;

    GROUP#    THREAD#  SEQUENCE#         MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------
        10          1        111         50 YES ACTIVE
        11          1          0         50 NO  UNASSIGNED
        12          0          0         50 YES UNASSIGNED
        13          0          0         50 YES UNASSIGNED
        14          0          0         50 YES UNASSIGNED
 
select group#,status,type,member from v$logfile order by 1;


    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- -----------------------------------------------------------------
         1         ONLINE  /u01/app/oracle/oradata/PROD/redo01.log
         2         ONLINE  /u01/app/oracle/oradata/PROD/redo02.log
         3         ONLINE  /u01/app/oracle/oradata/PROD/redo03.log
         4         ONLINE  /u01/app/oracle/oradata/PROD/redo04.log
        10         STANDBY /u01/app/oracle/oradata/PROD/stdredo10.log
        11         STANDBY /u01/app/oracle/oradata/PROD/stdredo11.log
        12         STANDBY /u01/app/oracle/oradata/PROD/stdredo12.log
        13         STANDBY /u01/app/oracle/oradata/PROD/stdredo13.log
        14         STANDBY /u01/app/oracle/oradata/PROD/stdredo14.log


-- 检查备库角色
select database_role,open_mode from v$database;
select db_unique_name,open_mode,switchover_status,database_role from v$database;

查看进程

主库主要看 LNS 进程，此进程负责将主数据库的重做日志条目传输到备用数据库。备库主要看 MRP0 进程，此进程负责将接收到的归档日志应用到备用数据库上，以维持与主数据库的同步。MRP进程是ADG中的关键组件，它确保备用数据库的数据与主数据库保持一致。

select process ,status , sequence# from v$managed_standby;

http://www.xdnf.cn/news/400735.html

相关文章：

  • CSDN博客粘贴图片失败如何解决
  • 佰力博科技与您探讨阻抗谱测量的基本原理和测量方法
  • 【言语】刷题2
  • Qt5.14.2 链接 MySQL 8.4 遇到的问题
  • 第三方软件测评中心分享：软件功能测试类型和测试工具
  • 动手学深度学习12.4.硬件-笔记练习（PyTorch）
  • # 实时英文 OCR 文字识别：从摄像头到 PyQt5 界面的实现
  • python 的 ​uv、pip​ 和 ​conda​ 对比和技术选型
  • 安卓玩机工具-----安卓机型一款很好用的数据备份恢复软件 支持云端备份
  • C#中SetProperty方法使用
  • Qt进阶开发：QTcpServer的的详解
  • 对抗进行性核上性麻痹，健康护理筑牢生活防线
  • Golang 应用的 CI/CD 与 K8S 自动化部署全流程指南
  • 深度拆解！MES如何重构生产计划与排产调度全流程？
  • 【软件工程】软件缺陷 基于组合的优化方法
  • 获取高德地图JS API的安全密钥和Key的方法
  • HBase进阶之路：从原理到实战的深度探索
  • 1.6 偏导数
  • Ollama+OpenWebUI+docker附带软件下载链接，配置流程，适合内网部署，可以多人内网使用
  • CSS3(BFC)
  • 智能家居“心脏“升级战：GD25Q127CSIG国产芯片如何重构家庭物联生态
  • Lodash isEqual 方法源码实现分析
  • 企业级商城系统容器化部署技术方案
  • 【Linux】socket网络编程之TCP
  • mac M2下虚拟机CentOS 8 安装上安装 Berkeley DB
  • tar -zxvf jdk-8u212-linux-x64.tar.gz -C /opt/module/这个代码的解释
  • 2.3 定积分
  • [Spring]-组件注入
  • 【C++重载操作符与转换】构造函数和复制控制
  • 嵌入式培训之数据结构学习（一）数据结构的基础概念、线性表