dataguard修改redo大小、添加redo等操作

背景：

某项目dataguard正常同步，但pr redo设置为2G，查看

select OPTIMAL_LOGFILE_SIZE from v$instance_recovery;

需要将redo从2G加到4G，并添加redo组到10个，standby redo添加到11个，由于dataguard已搭好，需要同步redo配置到容灾

步骤：

1、关闭备库MRP进程并将容灾的standby_file_management设为manual

alter database recover managed standby database cancel;

alter system set standby_file_management='manual';

2、生产容灾删除standby redo

select group# 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 ;

alter database drop logfile group 25 ;

alter database drop logfile group 26 ;

alter database drop logfile group 27 ;

alter database drop logfile group 28 ;

alter database drop logfile group 29 ;

alter database drop logfile group 30 ;

3、生产容灾添加11组standby log

容灾：

alter database add standby logfile thread 1 group 20('+RBSYS/CCDG/redo_020_1.dbf','+RBDATA04/CCDG/redo_020_2.dbf') size 4G reuse;

alter database add standby logfile thread 1 group 21('+RBSYS/CCDG/redo_021_1.dbf','+RBDATA04/CCDG/redo_021_2.dbf') size 4G reuse;

alter database add standby logfile thread 1 group 22('+RBSYS/CCDG/redo_022_1.dbf','+RBDATA04/CCDG/redo_022_2.dbf') size 4G reuse;

alter database add standby logfile thread 1 group 23('+RBSYS/CCDG/redo_023_1.dbf','+RBDATA04/CCDG/redo_023_2.dbf') size 4G reuse;

alter database add standby logfile thread 1 group 24('+RBSYS/CCDG/redo_024_1.dbf','+RBDATA04/CCDG/redo_024_2.dbf') size 4G reuse;

alter database add standby logfile thread 1 group 25('+RBSYS/CCDG/redo_025_1.dbf','+RBDATA04/CCDG/redo_025_2.dbf') size 4G reuse;

alter database add standby logfile thread 1 group 26('+RBSYS/CCDG/redo_026_1.dbf','+RBDATA04/CCDG/redo_026_2.dbf') size 4G reuse;

alter database add standby logfile thread 1 group 27('+RBSYS/CCDG/redo_027_1.dbf','+RBDATA04/CCDG/redo_027_2.dbf') size 4G reuse;

alter database add standby logfile thread 1 group 28('+RBSYS/CCDG/redo_028_1.dbf','+RBDATA04/CCDG/redo_028_2.dbf') size 4G reuse;

alter database add standby logfile thread 1 group 29('+RBSYS/CCDG/redo_029_1.dbf','+RBDATA04/CCDG/redo_029_2.dbf') size 4G reuse;

alter database add standby logfile thread 1 group 30('+RBSYS/CCDG/redo_030_1.dbf','+RBDATA04/CCDG/redo_030_2.dbf') size 4G reuse;

生产：

alter database add standby logfile thread 1 group 20('+CCSYS/CC/redo_020_1.dbf','+CCDATA/CC/redo_020_2.dbf') size 4G reuse;

alter database add standby logfile thread 1 group 21('+CCSYS/CC/redo_021_1.dbf','+CCDATA/CC/redo_021_2.dbf') size 4G reuse;

alter database add standby logfile thread 1 group 22('+CCSYS/CC/redo_022_1.dbf','+CCDATA/CC/redo_022_2.dbf') size 4G reuse;

alter database add standby logfile thread 1 group 23('+CCSYS/CC/redo_023_1.dbf','+CCDATA/CC/redo_023_2.dbf') size 4G reuse;

alter database add standby logfile thread 1 group 24('+CCSYS/CC/redo_024_1.dbf','+CCDATA/CC/redo_024_2.dbf') size 4G reuse;

alter database add standby logfile thread 1 group 25('+CCSYS/CC/redo_025_1.dbf','+CCDATA/CC/redo_025_2.dbf') size 4G reuse;

alter database add standby logfile thread 1 group 26('+CCSYS/CC/redo_026_1.dbf','+CCDATA/CC/redo_026_2.dbf') size 4G reuse;

alter database add standby logfile thread 1 group 27('+CCSYS/CC/redo_027_1.dbf','+CCDATA/CC/redo_027_2.dbf') size 4G reuse;

alter database add standby logfile thread 1 group 28('+CCSYS/CC/redo_028_1.dbf','+CCDATA/CC/redo_028_2.dbf') size 4G reuse;

alter database add standby logfile thread 1 group 29('+CCSYS/CC/redo_029_1.dbf','+CCDATA/CC/redo_029_2.dbf') size 4G reuse;

alter database add standby logfile thread 1 group 30('+CCSYS/CC/redo_030_1.dbf','+CCDATA/CC/redo_030_2.dbf') size 4G reuse;

4、容灾删除redo log

select group# from v$logfile;

alter database drop logfile group 1 ;

alter database drop logfile group 2 ;

alter database drop logfile group 3 ;

alter database drop logfile group 4 ;

alter database drop logfile group 5 ;

alter database drop logfile group 6 ;

alter database drop logfile group 7 ;

alter database drop logfile group 8 ;

5、容灾添加redo log

alter database add logfile thread 1 group 1('+RBDATA04/CCDG/redo_001_2.dbf','+RBSYS/CCDG/redo_001_1.dbf') size 4G reuse;

alter database add logfile thread 1 group 2('+RBDATA04/CCDG/redo_002_2.dbf','+RBSYS/CCDG/redo_002_1.dbf') size 4G reuse;

alter database add logfile thread 1 group 3('+RBDATA04/CCDG/redo_003_2.dbf','+RBSYS/CCDG/redo_003_1.dbf') size 4G reuse;

alter database add logfile thread 1 group 4('+RBDATA04/CCDG/redo_004_2.dbf','+RBSYS/CCDG/redo_004_1.dbf') size 4G reuse;

alter database add logfile thread 1 group 5('+RBDATA04/CCDG/redo_005_2.dbf','+RBSYS/CCDG/redo_005_1.dbf') size 4G reuse;

alter database add logfile thread 1 group 6('+RBDATA04/CCDG/redo_006_2.dbf','+RBSYS/CCDG/redo_006_1.dbf') size 4G reuse;

alter database add logfile thread 1 group 7('+RBDATA04/CCDG/redo_007_2.dbf','+RBSYS/CCDG/redo_007_1.dbf') size 4G reuse;

alter database add logfile thread 1 group 8('+RBDATA04/CCDG/redo_008_2.dbf','+RBSYS/CCDG/redo_008_1.dbf') size 4G reuse;

alter database add logfile thread 1 group 9('+RBDATA04/CCDG/redo_009_2.dbf','+RBSYS/CCDG/redo_009_1.dbf') size 4G;

alter database add logfile thread 1 group 10('+RBDATA04/CCDG/redo_010_2.dbf','+RBSYS/CCDG/redo_010_1.dbf') size 4G;

6、生产删除inactive redo log并添加4G的redo

alter database drop logfile group 3 ;

alter database drop logfile group 4 ;

alter database drop logfile group 5 ;

alter database drop logfile group 6 ;

alter database drop logfile group 7 ;

alter database drop logfile group 8 ;

alter database add logfile thread 1 group 3('+CCDATA/CC/redo_003_2.dbf','+CCSYS/CC/redo_003_1.dbf') size 4G reuse;

alter database add logfile thread 1 group 4('+CCDATA/CC/redo_004_2.dbf','+CCSYS/CC/redo_004_1.dbf') size 4G reuse;

alter database add logfile thread 1 group 5('+CCDATA/CC/redo_005_2.dbf','+CCSYS/CC/redo_005_1.dbf') size 4G reuse;

alter database add logfile thread 1 group 6('+CCDATA/CC/redo_006_2.dbf','+CCSYS/CC/redo_006_1.dbf') size 4G reuse;

alter database add logfile thread 1 group 7('+CCDATA/CC/redo_007_2.dbf','+CCSYS/CC/redo_007_1.dbf') size 4G reuse;

alter database add logfile thread 1 group 8('+CCDATA/CC/redo_008_2.dbf','+CCSYS/CC/redo_008_1.dbf') size 4G reuse;

alter database add logfile thread 1 group 9('+CCDATA/CC/redo_009_2.dbf','+CCSYS/CC/redo_009_1.dbf') size 4G;

alter database add logfile thread 1 group 10('+CCDATA/CC/redo_010_2.dbf','+CCSYS/CC/redo_010_1.dbf') size 4G;

7、生产删除active和current redo,并重新添加

alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

alter system checkpoint;(记得此命令要在业务低峰)

alter database drop logfile group 1 ;

alter database drop logfile group 2 ;

alter database add logfile thread 1 group 1('+CCDATA/CC/redo_001_2.dbf','+CCSYS/CC/redo_001_1.dbf') size 4G reuse;

alter database add logfile thread 1 group 2('+CCDATA/CC/redo_002_2.dbf','+CCSYS/CC/redo_002_1.dbf') size 4G reuse;

8、容灾改回standby_file_management并开启MRP进程

alter system set standby_file_management='auto';

alter database recover managed standby database disconnect from session;

9、检查dataguard同步是否恢复正常

select status,sequence# from v$managed_standby;