一、在一个dg环境中，配置的是实时同步,需要增加主库的redo大小和组数,解决思路是：
1、先备库增加standby redo 删除老standby redo,
2、然后主库增加redo删除老redo,
3、备库增加新redo删除老redo,
4、最后主库增加standby redo。
但是在实施过程中，遇到了一些细节性的问题，主要是学习到了log_file_name_convert如果不配置,将导致备库redo 文件不能被删除
二、注意事项：
1、standby log group 要比 logfile group 至少多一组。
2、standby logfile 和 logfile大小尽量一致。
3、standby logfile 和 logfile 操作逻辑基本上一样，都是要等到状态为inactive或者unused才能drop掉，
4、所以需要结合alter system checkpoint; alter system switch logfile; alter database clear logfile group x;等语句使用。
以下测试脚本基于主备库1,2,3组为logfile，4,5,6,7组为standby logfile，大小均为200m，目标为全部调整到256
三、修改
1、查看现有日志组及重做文件大小及路径
#主库
[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 ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
1 1 946 200 1 YES INACTIVE
2 1 947 200 1 YES INACTIVE
3 1 948 200 1 NO CURRENT
SQL> select group#,thread#,sequence#,bytes/1024/1024 mb,archived,status from V$STANDBY_LOG;
GROUP# THREAD# SEQUENCE# MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------
4 0 0 200 YES UNASSIGNED
5 0 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 /data/u01/app/oracle/oradata/orcl/redo03.log
2 ONLINE /data/u01/app/oracle/oradata/orcl/redo02.log
1 ONLINE /data/u01/app/oracle/oradata/orcl/redo01.log
4 STANDBY /data/u01/app/oracle/oradata/orcl/std_redo04.log
5 STANDBY /data/u01/app/oracle/oradata/orcl/std_redo05.log
6 STANDBY /data/u01/app/oracle/oradata/orcl/std_redo06.log
7 STANDBY /data/u01/app/oracle/oradata/orcl/std_redo07.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 ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
1 1 0 200 1 YES UNUSED
3 1 0 200 1 YES UNUSED
2 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 ARC STATUS
---------- ---------- ---------- ---------- --- ----------
4 1 948 200 YES ACTIVE
5 1 0 200 NO 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 /data/u01/app/oracle/oradata/orcl/redo03.log
2 ONLINE /data/u01/app/oracle/oradata/orcl/redo02.log
1 ONLINE /data/u01/app/oracle/oradata/orcl/redo01.log
4 STANDBY /data/u01/app/oracle/oradata/orcl/std_redo04.log
5 STANDBY /data/u01/app/oracle/oradata/orcl/std_redo05.log
6 STANDBY /data/u01/app/oracle/oradata/orcl/std_redo06.log
7 STANDBY /data/u01/app/oracle/oradata/orcl/std_redo07.log
2.备库取消同步
SQL>alter database recover managed standby database cancel;
3.备库standby添加
alter database add standby logfile group 14 '/data/u01/app/oracle/oradata/orcl/std_redo14.log' size 500m reuse;
alter database add standby logfile group 15 '/data/u01/app/oracle/oradata/orcl/std_redo15.log' size 500m reuse;
alter database add standby logfile group 16 '/data/u01/app/oracle/oradata/orcl/std_redo16.log' size 500m reuse;
alter database add standby logfile group 17 '/data/u01/app/oracle/oradata/orcl/std_redo17.log' size 500m reuse;
4.备库standby删除
（1）先查看备库日志standby_log日志状态，如果4,5,6,7中某一个文件 处于active状态，需要执行alter system swtich logfile进行切换。
SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ----------
4 ACTIVE
5 UNASSIGNED
6 UNASSIGNED
7 UNASSIGNED
14 UNASSIGNED
15 UNASSIGNED
16 UNASSIGNED
17 UNASSIGNED
（2）先删除 5,6,7
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;
此时group 4在使用过程，如果删除会报错
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:
'/data/u01/app/oracle/oradata/orcl/std_redo04.log'
--如在清除standby redo log组时出现上面出错信息执行下面的命令清理
alter database clear logfile group 4;
（3)--在主库多次执行switch logfile,，直至group 4处于UNASSIGNED
SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ----------
4 UNASSIGNED
14 ACTIVE
15 UNASSIGNED
16 UNASSIGNED
17 UNASSIGNED
SQL>alter database drop logfile group 4;
（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 11 '/data/u01/app/oracle/oradata/orcl/redo11.log' size 500m reuse;
alter database add logfile group 12 '/data/u01/app/oracle/oradata/orcl/redo12.log' size 500m reuse;
alter database add logfile group 13 '/data/u01/app/oracle/oradata/orcl/redo13.log' size 500m reuse;
6、主库redo 删除
查看主库redolog状态，status为inactive的可以删除，current的和active的不可以删。进行多次日志切换即可(如果切换不过来，使用手动执行
SQL>alter system checkpoint 命令手动的创建一个检查点，来是active的日志文件组变成inactive的日志文件组，这一点在扩容日志文件组(先删除再添加)的时候会用到！
SQL> select group#,thread#,sequence#,bytes/1024/1024 mb,members,archived,status from v$log;
alter system switch logfile;——（可以多次切换）
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
#到操作系统删除组redo01.log、redo012log、redo03.log
alter database drop logfile group 2;
alter database drop logfile group 3;
#到操作系统删除组redo01.log、redo012log、redo03.log
7、备库redo添加
alter system set standby_file_management='manual';
alter database add logfile group 11 '/data/u01/app/oracle/oradata/orcl/redo11.log' size 500m reuse;
alter database add logfile group 12 '/data/u01/app/oracle/oradata/orcl/redo12.log' size 500m reuse;
alter database add logfile group 13 '/data/u01/app/oracle/oradata/orcl/redo13.log' size 500m reuse;
8、备库redo删除
（1）直接删除redo，报错(因为状态是clearing，无法直接删除）：
SQL> alter database drop logfile group 1; 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'
（2）使用clear 命令删除，再drop
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1; Database altered. SQL> alter database drop logfile group 1; Database altered. SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2; Database altered. SQL> alter database drop logfile group 2; Database altered. SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3; Database altered. SQL> alter database drop logfile group 3; Database altered.
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
#到操作系统删除组redo01.log、redo012log、redo03.log
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;
[]$ ls stdredo0*
stdredo01.log stdredo02.log stdredo03.log stdredo04.log
[oracle@DB196 MPCDB]$ rm stdredo0*
10.主库standby 添加
alter database add standby logfile group 14 '/data/u01/app/oracle/oradata/orcl/std_redo14.log' size 500m reuse;
alter database add standby logfile group 15 '/data/u01/app/oracle/oradata/orcl/std_redo15.log' size 500m reuse;
alter database add standby logfile group 16 '/data/u01/app/oracle/oradata/orcl/std_redo16.log' size 500m reuse;
alter database add standby logfile group 17 '/data/u01/app/oracle/oradata/orcl/std_redo17.log' size 500m reuse;
11.恢复同步和备库文件自动管理（备库执行）
alter system set standby_file_management='AUTO';
alter database recover managed standby database using current logfile disconnect from session;
12.验证同步是否恢复
（1）#主库
SQL> select unique thread#,max(sequence#) over(partition by thread#)last from v$archived_log; THREAD# LAST ---------- ---------- 1 972
（2）#备库：
SQL> select unique thread#,max(sequence#) over(partition by thread#)last from v$archived_log; THREAD# LAST ---------- ---------- 1 972
（3）主库切换日志，并查看主备库（主备库序列号都增加1即可）
SQL> select unique thread#,max(sequence#) over(partition by thread#)last from v$archived_log; THREAD# LAST ---------- ---------- 1 973
文章评论