ORACLE 12C ADG 之二十五 (调整redo 和 standby redo大小)

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

最后编辑于：2019.11.09 14:42:12

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 216,692评论 6赞 501
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 92,482评论 3赞 392
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 162,995评论 0赞 353
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,223评论 1赞 292
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,245评论 6赞 388
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,208评论 1赞 299
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,091评论 3赞 418
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,929评论 0赞 274
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,346评论 1赞 311
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,570评论 2赞 333
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,739评论 1赞 348
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,437评论 5赞 344
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 41,037评论 3赞 326
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,677评论 0赞 22
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,833评论 1赞 269
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,760评论 2赞 369
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,647评论 2赞 354

ORACLE 12C ADG 之二十五 (调整redo 和 standby redo大小)

推荐阅读更多精彩内容