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
网友评论