ORACLE 12C ADG 之二十五 (调整redo 和 st

作者: 轻飘飘D | 来源:发表于2019-11-02 11:29 被阅读0次

ORACLE 12C ADG 之二十五 (调整redo 和 st
ORACLE19.3 升級之路之 01(ADG 升级 [11.
ORACLE19.3 升級之路之 02(ADG 升级 [11.
Oracle 12C Sharding 安装配置
Docker安装Oracle 12c
oracle12c静默安装
Oracle12c IMO 测试
oracle中redo和undo的区别
技术分享 | 企业版监控工具 MEM 初探
技术分享 | MySQL 使用 MariaDB 审计插件

1.在一个dg环境中，配置的是实时同步,需要增加主库的redo大小和组数,本来是一个很简单的问题,解决思路是：
a、先备库增加standby redo 删除老standby redo,
b、然后主库增加redo删除老redo,
c、备库增加新redo删除老redo,
d、最后主库增加standby redo。

但是在实施过程中，遇到了一些细节性的问题，主要是
学习到了log_file_name_convert如果不配置,将导致备库redo 文件不能被删除

注意事项：
standby log group 要比 logfile group 至少多一组。
standby logfile 和 logfile大小尽量一致。
standby logfile 和 logfile 操作逻辑基本上一样，都是要等到状态为inactive或者unused才能drop掉，所以需要结合alter system checkpoint; alter system switch logfile; alter database clear logfile group x;等语句使用。
以下测试脚本基于主备库1,2,3组为logfile，4,5,6,7组为standby logfile，大小均为200m，目标为全部调整到256m

查看现有日志组及重做文件大小及路径

#主库
[oracle@DB196 ~]$ sql / as sysdba
SQL> SET SQLFORMAT ansiconsole

SQL> select group#,thread#,sequence#,bytes/1024/1024 mb,members,archived,status from v$log;
GROUP#  THREAD#  SEQUENCE#  MB   MEMBERS  ARCHIVED  STATUS    
1       1        45         200  1        YES       INACTIVE  
2       1        46         200  1        NO        CURRENT   
3       1        44         200  1        YES       INACTIVE  

SQL> select group#,thread#,sequence#,bytes/1024/1024 mb,archived,status from V$STANDBY_LOG;
GROUP#  THREAD#  SEQUENCE#  MB   ARCHIVED  STATUS      
4       1        0          200  YES       UNASSIGNED  
5       1        0          200  YES       UNASSIGNED  
6       0        0          200  YES       UNASSIGNED  
7       0        0          200  YES       UNASSIGNED  

SQL>  select group#,status,type,member from v$logfile;
GROUP#  STATUS  TYPE     MEMBER                                       
3               ONLINE   /u01/app/oracle/oradata/MPCDB/redo03.log     
2               ONLINE   /u01/app/oracle/oradata/MPCDB/redo02.log     
1               ONLINE   /u01/app/oracle/oradata/MPCDB/redo01.log     
4               STANDBY  /u01/app/oracle/oradata/MPCDB/stdredo01.log  
5               STANDBY  /u01/app/oracle/oradata/MPCDB/stdredo02.log  
6               STANDBY  /u01/app/oracle/oradata/MPCDB/stdredo03.log  
7               STANDBY  /u01/app/oracle/oradata/MPCDB/stdredo04.log  

#备库
[oracle@DB197 ~]$ sql / as sysdba
SQL> SET SQLFORMAT ansiconsole

SQL> select group#,thread#,sequence#,bytes/1024/1024 mb,members,archived,status from v$log;
GROUP#  THREAD#  SEQUENCE#  MB   MEMBERS  ARCHIVED  STATUS  
1       1        0          200  1        YES       UNUSED  
2       1        0          200  1        YES       UNUSED  
3       1        0          200  1        YES       UNUSED  

SQL> select group#,thread#,sequence#,bytes/1024/1024 mb,archived,status from V$STANDBY_LOG;
GROUP#  THREAD#  SEQUENCE#  MB   ARCHIVED  STATUS      
4       1        0          200  NO        UNASSIGNED  
5       1        46         200  YES       ACTIVE      
6       0        0          200  YES       UNASSIGNED  
7       0        0          200  YES       UNASSIGNED  

SQL> select group#,status,type,member from v$logfile;
GROUP#  STATUS  TYPE     MEMBER                                       
3               ONLINE   /u01/app/oracle/oradata/MPCDB/redo03.log     
2               ONLINE   /u01/app/oracle/oradata/MPCDB/redo02.log     
1               ONLINE   /u01/app/oracle/oradata/MPCDB/redo01.log     
4               STANDBY  /u01/app/oracle/oradata/MPCDB/stdredo01.log  
5               STANDBY  /u01/app/oracle/oradata/MPCDB/stdredo02.log  
6               STANDBY  /u01/app/oracle/oradata/MPCDB/stdredo03.log  
7               STANDBY  /u01/app/oracle/oradata/MPCDB/stdredo04.log

2.备库取消同步

SQL> 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 31 '/u01/app/oracle/oradata/MPCDB/stdredo31.log' size 256m reuse;
alter database add standby logfile group 32 '/u01/app/oracle/oradata/MPCDB/stdredo32.log' size 256m reuse;
alter database add standby logfile group 33 '/u01/app/oracle/oradata/MPCDB/stdredo33.log' size 256m reuse;
alter database add standby logfile group 34 '/u01/app/oracle/oradata/MPCDB/stdredo34.log' size 256m reuse;
alter database add standby logfile group 35 '/u01/app/oracle/oradata/MPCDB/stdredo35.log' size 256m reuse;
alter database add standby logfile group 36 '/u01/app/oracle/oradata/MPCDB/stdredo36.log' size 256m reuse;

4.备库standby删除

alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;

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 4;

#到操作系统删除组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 21 '/u01/app/oracle/oradata/MPCDB/redo21.log' size 256m reuse;
alter database add logfile group 22 '/u01/app/oracle/oradata/MPCDB/redo22.log' size 256m reuse;
alter database add logfile group 23 '/u01/app/oracle/oradata/MPCDB/redo23.log' size 256m reuse;
alter database add logfile group 24 '/u01/app/oracle/oradata/MPCDB/redo24.log' size 256m reuse;
alter database add logfile group 25 '/u01/app/oracle/oradata/MPCDB/redo25.log' size 256m reuse;

6.主库redo 删除

alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

到操作系统删除组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 21 '/u01/app/oracle/oradata/MPCDB/redo21.log' size 256m reuse;
alter database add logfile group 22 '/u01/app/oracle/oradata/MPCDB/redo22.log' size 256m reuse;
alter database add logfile group 23 '/u01/app/oracle/oradata/MPCDB/redo23.log' size 256m reuse;
alter database add logfile group 24 '/u01/app/oracle/oradata/MPCDB/redo24.log' size 256m reuse;
alter database add logfile group 25 '/u01/app/oracle/oradata/MPCDB/redo25.log' size 256m reuse;

8.备库redo删除

SQL> 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 1;
alter database clear logfile group 2;
alter database clear logfile group 3;

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

#到操作系统删除组1、2、3
[oracle@DB197 MPCDB]$ ls redo0*
redo01.log  redo02.log  redo03.log
[oracle@DB197 MPCDB]$ rm redo0*

9.主库standby 删除

alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;

#到操作系统删除组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 31 '/u01/app/oracle/oradata/MPCDB/stdredo31.log' size 256m reuse;
alter database add standby logfile group 32 '/u01/app/oracle/oradata/MPCDB/stdredo32.log' size 256m reuse;
alter database add standby logfile group 33 '/u01/app/oracle/oradata/MPCDB/stdredo33.log' size 256m reuse;
alter database add standby logfile group 34 '/u01/app/oracle/oradata/MPCDB/stdredo34.log' size 256m reuse;
alter database add standby logfile group 35 '/u01/app/oracle/oradata/MPCDB/stdredo35.log' size 256m reuse;
alter database add standby logfile group 36 '/u01/app/oracle/oradata/MPCDB/stdredo36.log' size 256m reuse;

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.验证同步是否恢复

#主库
SQL> select open_mode from v$database;
OPEN_MODE   
READ WRITE  

SQL> select group#,thread#,sequence#,bytes/1024/1024 mb,members,archived,status from v$log;
GROUP#  THREAD#  SEQUENCE#  MB   MEMBERS  ARCHIVED  STATUS    
21      1        74         256  1        YES       INACTIVE  
22      1        75         256  1        NO        CURRENT   
23      1        70         256  1        YES       INACTIVE  
24      1        71         256  1        YES       INACTIVE  
25      1        72         256  1        YES       INACTIVE 

SQL> select group#,thread#,sequence#,bytes/1024/1024 mb,archived,status from V$STANDBY_LOG order by 1;
GROUP#  THREAD#  SEQUENCE#  MB   ARCHIVED  STATUS      
31      0        0          256  YES       UNASSIGNED  
32      0        0          256  YES       UNASSIGNED  
33      0        0          256  YES       UNASSIGNED  
34      0        0          256  YES       UNASSIGNED  
35      0        0          256  YES       UNASSIGNED  
36      0        0          256  YES       UNASSIGNED

SQL> select group#,status,type,member from v$logfile order by 1;
GROUP#  STATUS  TYPE     MEMBER                                       
21              ONLINE   /u01/app/oracle/oradata/MPCDB/redo21.log     
22              ONLINE   /u01/app/oracle/oradata/MPCDB/redo22.log     
23              ONLINE   /u01/app/oracle/oradata/MPCDB/redo23.log     
24              ONLINE   /u01/app/oracle/oradata/MPCDB/redo24.log     
25              ONLINE   /u01/app/oracle/oradata/MPCDB/redo25.log     
31              STANDBY  /u01/app/oracle/oradata/MPCDB/stdredo31.log  
32              STANDBY  /u01/app/oracle/oradata/MPCDB/stdredo32.log  
33              STANDBY  /u01/app/oracle/oradata/MPCDB/stdredo33.log  
34              STANDBY  /u01/app/oracle/oradata/MPCDB/stdredo34.log  
35              STANDBY  /u01/app/oracle/oradata/MPCDB/stdredo35.log  
36              STANDBY  /u01/app/oracle/oradata/MPCDB/stdredo36.log 

#备库
SQL> select open_mode from v$database;
OPEN_MODE             
READ ONLY WITH APPLY  

SQL> select group#,thread#,sequence#,bytes/1024/1024 mb,members,archived,status from v$log order by 1;
GROUP#  THREAD#  SEQUENCE#  MB   MEMBERS  ARCHIVED  STATUS  
21      1        0          256  1        YES       UNUSED  
22      1        0          256  1        YES       UNUSED  
23      1        0          256  1        YES       UNUSED  
24      1        0          256  1        YES       UNUSED  
25      1        0          256  1        YES       UNUSED 

SQL> select group#,thread#,sequence#,bytes/1024/1024 mb,archived,status from V$STANDBY_LOG order by 1;
GROUP#  THREAD#  SEQUENCE#  MB   ARCHIVED  STATUS      
31      1        0          256  NO        UNASSIGNED  
32      1        75         256  YES       ACTIVE      
33      0        0          256  YES       UNASSIGNED  
34      0        0          256  YES       UNASSIGNED  
35      0        0          256  YES       UNASSIGNED  
36      0        0          256  YES       UNASSIGNED

SQL> select group#,status,type,member from v$logfile order by 1;
GROUP#  STATUS  TYPE     MEMBER                                       
21              ONLINE   /u01/app/oracle/oradata/MPCDB/redo21.log     
22              ONLINE   /u01/app/oracle/oradata/MPCDB/redo22.log     
23              ONLINE   /u01/app/oracle/oradata/MPCDB/redo23.log     
24              ONLINE   /u01/app/oracle/oradata/MPCDB/redo24.log     
25              ONLINE   /u01/app/oracle/oradata/MPCDB/redo25.log     
31              STANDBY  /u01/app/oracle/oradata/MPCDB/stdredo31.log  
32              STANDBY  /u01/app/oracle/oradata/MPCDB/stdredo32.log  
33              STANDBY  /u01/app/oracle/oradata/MPCDB/stdredo33.log  
34              STANDBY  /u01/app/oracle/oradata/MPCDB/stdredo34.log  
35              STANDBY  /u01/app/oracle/oradata/MPCDB/stdredo35.log  
36              STANDBY  /u01/app/oracle/oradata/MPCDB/stdredo36.log

ORACLE 12C ADG 之二十五 (调整redo 和 st
1.在一个dg环境中，配置的是实时同步,需要增加主库的redo大小和组数,本来是一个很简单的问题,解决思路是：a、...
ORACLE19.3 升級之路之 01(ADG 升级 [11.
ADG 升级 [11.2.0.1 -> 11.2.0.4] 目标 ORACLE ADG 环境下将 11.2.0.1...
ORACLE19.3 升級之路之 02(ADG 升级 [11.
ADG 升级 [11.2.0.4 -> 19.3] 目标 ORACLE ADG 环境下将 11.2.0.4 升级到...
Oracle 12C Sharding 安装配置
參考：Oracle 12C Sharding部署和测试12c新特性-Oracle Sharding简介 1.规划 ...
Docker安装Oracle 12c
Oracle简介 Oracle 12c，全称Oracle Database 12c，是Oracle 11g的升级版...
oracle12c静默安装
Oracle 12c 静默安装Oracle Database，又名Oracle RDBMS，或简称Oracle。是...
Oracle12c IMO 测试
1.概述 2014 年 6 月，在 Oracle 12c 的 12.1.0.2 版本中，Oracle 正式发布和引...
oracle中redo和undo的区别
一. 什么是redo（用于重做数据） redo也就是重做日志文件（redo log file），Oracle维护...
技术分享 | 企业版监控工具 MEM 初探
作者：姚远专注于 Oracle、MySQL 数据库多年，Oracle 10G 和 12C OCM，MySQL 5....
技术分享 | MySQL 使用 MariaDB 审计插件
作者：姚远专注于 Oracle、MySQL 数据库多年，Oracle 10G 和 12C OCM，MySQL 5....