模拟主备库redo日志大小不一样的异常场景，并提供m6米乐安卓版下载的解决方案。
主库：oracle 11.2.0.4 centos 7.6 实例名：wafu
备库：oracle 11.2.0.4 centos 7.6 实例名：fuwa
1.查看redo大小
1.1.主库
--查数据库角色
idle 29-nov-23> set linesize 999
idle 29-nov-23> select name,open_mode,database_role,protection_mode,switchover_status from v$database;
name open_mode database_role protection_mode switchover_status
-------------------------------------------------------------------------------- -------------------- ---------------- -------------------- --------------------
fuwa read write primary maximum performance sessions active
--查redo信息
idle 29-nov-23> set linesize 999
idle 29-nov-23> col member for a60
idle 29-nov-23> select b.thread#,a.group#,a.member,bytes/1024/1024,b.members,b.status from v$logfile a,v$log b where a.group#=b.group#;
thread# group# member bytes/1024/1024 members status
---------- ---------- ------------------------------------------------------------ --
1 3 /data/oradata/wafu/redo03.log 50 1 current
1 2 /data/oradata/wafu/redo02.log 50 1 inactive
1 1 /data/oradata/wafu/redo01.log 50 1 inactive
--查redo详细信息
idle 29-nov-23> select vlf.member,vl.group#,vl.thread#,vl.bytes/1024/1024 as "size(m)",vl.status,vlf.type from v$log vl,v$logfile vlf;
member group# thread# size(m) status type
------------------------------------------------------------ ---------- ---------- ---------- ---------------- -------
/data/oradata/wafu/redo03.log 1 1 50 inactive online
/data/oradata/wafu/redo03.log 2 1 50 inactive online
/data/oradata/wafu/redo03.log 3 1 50 current online
/data/oradata/wafu/redo02.log 1 1 50 inactive online
/data/oradata/wafu/redo02.log 2 1 50 inactive online
/data/oradata/wafu/redo02.log 3 1 50 current online
/data/oradata/wafu/redo01.log 1 1 50 inactive online
/data/oradata/wafu/redo01.log 2 1 50 inactive online
/data/oradata/wafu/redo01.log 3 1 50 current online
/data/oradata/wafu/redo04.log 1 1 50 inactive standby
/data/oradata/wafu/redo04.log 2 1 50 inactive standby
/data/oradata/wafu/redo04.log 3 1 50 current standby
/data/oradata/wafu/redo05.log 1 1 50 inactive standby
/data/oradata/wafu/redo05.log 2 1 50 inactive standby
/data/oradata/wafu/redo05.log 3 1 50 current standby
/data/oradata/wafu/redo06.log 1 1 50 inactive standby
/data/oradata/wafu/redo06.log 2 1 50 inactive standby
/data/oradata/wafu/redo06.log 3 1 50 current standby
/data/oradata/wafu/redo07.log 1 1 50 inactive standby
/data/oradata/wafu/redo07.log 2 1 50 inactive standby
/data/oradata/wafu/redo07.log 3 1 50 current standby
1.2.备库
--查数据库角色
idle 29-nov-23> set linesize 999
idle 29-nov-23> select name,open_mode,database_role,protection_mode,switchover_status from v$database;
name open_mode database_role protection_mode switchover_status
-------------------------------------------------------------------------------- -------------------- ---------------- -------------------- --------------------
fuwa read only with apply physical standby maximum performance not allowed
--查redo信息
idle 29-nov-23> set linesize 999
idle 29-nov-23> col member for a60
idle 29-nov-23> select b.thread#,a.group#,a.member,bytes/1024/1024,b.members,b.status from v$logfile a,v$log b where a.group#=b.group#;
thread# group# member bytes/1024/1024 members status
---------- ---------- ------------------------------------------------------------ --------------- ---------- ----------------
1 3 /data/oradata/fuwa/redo03.log 50 1 current
1 2 /data/oradata/fuwa/redo02.log 50 1 clearing
1 1 /data/oradata/fuwa/redo01.log 50 1 clearing
--查redo详细信息
sys@fuwa 2023-11-30 10:30:57> select vlf.member,vl.group#,vl.thread#,vl.bytes/1024/1024 as "size(m)",vl.status,vlf.type from v$log vl,v$logfile vlf;
member group# thread# size(m) status type
------------------------------------------------------------ ---------- ---------- ---------- ---------------- -------
/data/oradata/fuwa/redo03.log 1 1 100 unused online
/data/oradata/fuwa/redo03.log 2 1 100 unused online
/data/oradata/fuwa/redo03.log 3 1 100 current online
/data/oradata/fuwa/redo03.log 8 1 100 unused online
/data/oradata/fuwa/redo02.log 1 1 100 unused online
/data/oradata/fuwa/redo02.log 2 1 100 unused online
/data/oradata/fuwa/redo02.log 3 1 100 current online
/data/oradata/fuwa/redo02.log 8 1 100 unused online
/data/oradata/fuwa/redo01.log 1 1 100 unused online
/data/oradata/fuwa/redo01.log 2 1 100 unused online
/data/oradata/fuwa/redo01.log 3 1 100 current online
/data/oradata/fuwa/redo01.log 8 1 100 unused online
/data/oradata/fuwa/redo04.log 1 1 100 unused standby
/data/oradata/fuwa/redo04.log 2 1 100 unused standby
/data/oradata/fuwa/redo04.log 3 1 100 current standby
/data/oradata/fuwa/redo04.log 8 1 100 unused standby
/data/oradata/fuwa/redo05.log 1 1 100 unused standby
/data/oradata/fuwa/redo05.log 2 1 100 unused standby
/data/oradata/fuwa/redo05.log 3 1 100 current standby
/data/oradata/fuwa/redo05.log 8 1 100 unused standby
/data/oradata/fuwa/redo06.log 1 1 100 unused standby
/data/oradata/fuwa/redo06.log 2 1 100 unused standby
/data/oradata/fuwa/redo06.log 3 1 100 current standby
/data/oradata/fuwa/redo06.log 8 1 100 unused standby
/data/oradata/fuwa/redo07.log 1 1 100 unused standby
/data/oradata/fuwa/redo07.log 2 1 100 unused standby
/data/oradata/fuwa/redo07.log 3 1 100 current standby
/data/oradata/fuwa/redo07.log 8 1 100 unused standby
/data/oradata/fuwa/redo08.log 1 1 100 unused online
/data/oradata/fuwa/redo08.log 2 1 100 unused online
/data/oradata/fuwa/redo08.log 3 1 100 current online
/data/oradata/fuwa/redo08.log 8 1 100 unused online
2.增加主库redo大小
2.1.取消备库实时应用
--备库上操作
sys@fuwa 2023-11-29 16:52:49> alter database recover managed standby database cancel;
database altered.
2.2.处理主库redo
--主库增加redo
idle 29-nov-23> alter database add logfile group 8 ('/data/oradata/wafu/redo08.log') size 100m;
idle 29-nov-23> alter system switch logfile;
idle 29-nov-23> alter system checkpoint;
idle 29-nov-23> col member for a60
select b.thread#,a.group#,b.sequence#,a.member,bytes/1024/1024,b.members,b.status,b.archived from v$logfile a,v$log b where a.group#=b.group#;
group# thread# sequence# members status arc
---------- ---------- ---------- ---------- ---------------- ---
1 1 71 1 inactive yes
2 1 72 1 inactive yes
3 1 73 1 inactive yes
8 1 74 1 current no
idle 29-nov-23> alter database drop logfile group 1;#drop前group 1的status需为inactive
idle 29-nov-23> alter database drop logfile group 2;#drop前group 2的status需为inactive
idle 29-nov-23> alter database drop logfile group 3;#drop前group 3的status需为inactive
操作系统层面删除/data/oradata/wafu/redo01.log(慎用rm，空间允许的话mv到其他位置)
[oracle@db02:/home/oracle]$ mv /data/oradata/wafu/redo01.log /home/oracle/
[oracle@db02:/home/oracle]$ mv /data/oradata/wafu/redo02.log /home/oracle/
[oracle@db02:/home/oracle]$ mv /data/oradata/wafu/redo03.log /home/oracle/
idle 29-nov-23> alter database add logfile group 1 ('/data/oradata/wafu/redo01.log') size 100m;
idle 29-nov-23> alter database add logfile group 2 ('/data/oradata/wafu/redo02.log') size 100m;
idle 29-nov-23> alter database add logfile group 3 ('/data/oradata/wafu/redo03.log') size 100m;
idle 29-nov-23> alter system switch logfile;
idle 29-nov-23> alter system checkpoint;
--查看主库上的redo
idle 29-nov-23> select vlf.member,vl.group#,vl.thread#,vl.bytes/1024/1024 as "size(m)",vl.status,vlf.type from v$log vl,v$logfile vlf;
member group# thread# size(m) status type
------------------------------------------------------------ ---------- ---------- ---------- ---------------- -------
/data/oradata/wafu/redo02.log 1 1 100 current online
/data/oradata/wafu/redo02.log 2 1 100 unused online
/data/oradata/wafu/redo02.log 3 1 100 unused online
/data/oradata/wafu/redo02.log 8 1 100 inactive online
/data/oradata/wafu/redo03.log 1 1 100 current online
/data/oradata/wafu/redo03.log 2 1 100 unused online
/data/oradata/wafu/redo03.log 3 1 100 unused online
/data/oradata/wafu/redo03.log 8 1 100 inactive online
/data/oradata/wafu/redo01.log 1 1 100 current online
/data/oradata/wafu/redo01.log 2 1 100 unused online
/data/oradata/wafu/redo01.log 3 1 100 unused online
/data/oradata/wafu/redo01.log 8 1 100 inactive online
/data/oradata/wafu/redo04.log 1 1 100 current standby
/data/oradata/wafu/redo04.log 2 1 100 unused standby
/data/oradata/wafu/redo04.log 3 1 100 unused standby
/data/oradata/wafu/redo04.log 8 1 100 inactive standby
/data/oradata/wafu/redo05.log 1 1 100 current standby
/data/oradata/wafu/redo05.log 2 1 100 unused standby
/data/oradata/wafu/redo05.log 3 1 100 unused standby
/data/oradata/wafu/redo05.log 8 1 100 inactive standby
/data/oradata/wafu/redo06.log 1 1 100 current standby
/data/oradata/wafu/redo06.log 2 1 100 unused standby
/data/oradata/wafu/redo06.log 3 1 100 unused standby
/data/oradata/wafu/redo06.log 8 1 100 inactive standby
/data/oradata/wafu/redo07.log 1 1 100 current standby
/data/oradata/wafu/redo07.log 2 1 100 unused standby
/data/oradata/wafu/redo07.log 3 1 100 unused standby
/data/oradata/wafu/redo07.log 8 1 100 inactive standby
/data/oradata/wafu/redo08.log 1 1 100 current online
/data/oradata/wafu/redo08.log 2 1 100 unused online
/data/oradata/wafu/redo08.log 3 1 100 unused online
/data/oradata/wafu/redo08.log 8 1 100 inactive online
idle 29-nov-23> col member for a60
select b.thread#,a.group#,b.sequence#,a.member,bytes/1024/1024,b.members,b.status,b.archived from v$logfile a,v$log b where a.group#=b.group#;
group# thread# sequence# members status arc
---------- ---------- ---------- ---------- ---------------- ---
1 1 75 1 current no
2 1 0 1 unused yes
3 1 0 1 unused yes
8 1 74 1 inactive yes
2.3.处理主库standby redo
配置原则：
①standby logfile大小要和redo logfile大小相同；
②standby logfile要比redo logfile多一组，目前redo logfile是3组，因此创建4组standby logfile；
③为了安全，standby logfile可以包含多个member。
补充：创建适当的日志组
一般而言，standby redo日志文件组数要比primary数据库的online redo日志文件组数至少多一个。推荐standby redo日志组数量基于primary数据库的线程数(这里的线程数可以理解为rac结构中的rac节点数)。
有一个推荐的公式可以做参考：(每线程的日志组数 1)*最大线程数
例如primary数据库有两个线程，每个线程分配两组日志，则standby日志组数建议为6组，使用这个公式可以降低primary数据库实例lgwr进程锁住的可能性。
idle 29-nov-23> select group#,sum(bytes/1024/1024) "size in mb" from v$standby_log group by group#;
group# size in mb
---------- ----------
6 50
4 50
5 50
7 50
idle 29-nov-23> select group#,status from v$standby_log;
group# status
---------- ----------
4 unassigned
5 unassigned
6 unassigned
7 unassigned
idle 29-nov-23> alter database drop standby logfile group 4;
idle 29-nov-23> alter database drop standby logfile group 5;
idle 29-nov-23> alter database drop standby logfile group 6;
idle 29-nov-23> alter database drop standby logfile group 7;
--操纵系统层面上传standby log
操作系统层面删除standby log(慎用rm，空间允许的话mv到其他位置)
[oracle@db02:/home/oracle]$ mv /data/oradata/wafu/redo04.log /home/oracle/
[oracle@db02:/home/oracle]$ mv /data/oradata/wafu/redo05.log /home/oracle/
[oracle@db02:/home/oracle]$ mv /data/oradata/wafu/redo06.log /home/oracle/
[oracle@db02:/home/oracle]$ mv /data/oradata/wafu/redo07.log /home/oracle/
--增加standby log
alter database add standby logfile group 4 '/data/oradata/wafu/redo04.log' size 100m;
alter database add standby logfile group 5 '/data/oradata/wafu/redo05.log' size 100m;
alter database add standby logfile group 6 '/data/oradata/wafu/redo06.log' size 100m;
alter database add standby logfile group 7 '/data/oradata/wafu/redo07.log' size 100m;
alter database add standby logfile group 9 '/data/oradata/wafu/redo09.log' size 100m;
保持standby log数量比redo组数*线程数 1
idle 29-nov-23> select group#,sum(bytes/1024/1024) "size in mb" from v$standby_log group by group#;
group# size in mb
---------- ----------
6 100
4 100
5 100
7 100
9 100
至此，成功模拟出主备库redo日志大小不一样的场景.
3.模拟故障
3.1.主库产生redo日志
create table t1 tablespace users as select * from user_objects;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
commit;
3.2.备库日志
thu nov 30 09:02:41 2023
archived log entry 54 added for thread 1 sequence 78 rlc 1147204804 id 0x8581a630 dest 2:
rfs[3]: no standby redo logfiles available for thread 1
rfs[3]: opened log for thread 1 sequence 79 dbid -2055228158 branch 1147204804
media recovery log /data/oradata/archivelog/1_76_1147204804.dbf
报错"no standby redo logfiles available for thread 1",实际生产过程中会导致主备实时应用停止.
4、m6米乐安卓版下载的解决方案
4.1、修改前准备
--备库停止mrp
alter database recover managed standby database cancel;
--备库上参数需要设置为manual，才能修改日志组
sys@fuwa 2023-11-30 09:13:56> show parameter standby_file_management
name type value
------------------------------------ ----------- ------------------------------
standby_file_management string auto
sys@fuwa 2023-11-30 10:04:35> alter system set standby_file_management=manual;
sys@fuwa 2023-11-30 09:13:56> show parameter standby_file_management
name type value
------------------------------------ ----------- ------------------------------
standby_file_management string manual
说明：备库上此参数需要设置为manual，才能修改日志组.
补充：standby_file_management参数说明
standby_file_management：用于控制应用日志文件的处理，如果设置为auto时，此参数将用于控制应用日志文件是被自动删除、备份或迁移，以满足物理备份恢复要求。
启用自动备用文件管理后，主数据库上的操作系统文件添加和删除操作将复制到备用数据库上。standby_file_management仅适用于物理备用数据库。
- manual禁用自动备用文件管理
- auto启用自动备用文件管理
设置standby_file_management为auto会导致 oracle 自动在备用数据库上创建文件，并在某些情况下覆盖现有文件。设置时必须小心standby_file_management，db_file_name_convert以免现有的备用文件被意外覆盖。
如果备用数据库与主数据库位于同一系统上，则确保主系统和备用系统不指向相同的文件。
设置正确的方法：
- 在oracle实例中，使用alter system 命令将standby_file_management参数的值设置为auto：
alter system set standby_file_management=auto scope=spfile;
- 使用v$parameter视图查看参数设置是否生效：
select name, value from v$parameter where name = ‘standby_file_management’;
- 如果设置生效，则参数值会变为auto；如果没有生效，则重启数据库可以使参数正确设置。
4.2. 处理备库redo
扩展备库日志大小
--备库查看当前redo
idle 29-nov-23> set linesize 999
idle 29-nov-23> col member for a60
idle 29-nov-23> select b.thread#,a.group#,a.member,bytes/1024/1024,b.members,b.status from v$logfile a,v$log b where a.group#=b.group#;
说明：处理clearing 状态的日志组
--备库处理日志组
alter database clear logfile group 1;
alter database drop logfile group 1;
alter database add logfile group 1 '/data/oradata/fuwa/redo01.log' size 100m;
alter database clear logfile group 3;
alter database drop logfile group 3;
alter database add logfile group 3 '/data/oradata/fuwa/redo03.log' size 100m;
--主库切换日志组
alter system switch logfile;
--备库查看redo
sys@fuwa 2023-11-30 10:26:38> sys@fuwa 2023-11-30 10:26:38> select b.thread#,a.group#,a.member,bytes/1024/1024,b.members,b.status from v$logfile a,v$log b where a.group#=b.group#;
thread# group# member bytes/1024/1024 members status
---------- ---------- ------------------------------------------------------------ --------------- ---------- ----------------
1 3 /data/oradata/fuwa/redo03.log 100 1 current
1 2 /data/oradata/fuwa/redo02.log 50 1 clearing
1 1 /data/oradata/fuwa/redo01.log 100 1 unused
--备库删除redo2 再增加redo8
alter database clear logfile group 2;
alter database drop logfile group 2;
alter database add logfile group 2 '/data/oradata/fuwa/redo02.log' size 100m;
alter database add logfile group 8 '/data/oradata/fuwa/redo08.log' size 100m;
和主库保持一致
报错处理
对于一个oracle数据库实例，至少要包含两个联机重做日志组
目的：进行切换，以便归档模式下进行归档。
1）确认当前系统日子组信息
sys@ora10g> select group#,member from v$logfile;
group# member
---------- --------------------------------------------------
1 /oracle/ora10gr2/oradata/ora10g/redo01.log
2 /oracle/ora10gr2/oradata/ora10g/redo02.log
2）尝试再删除一组日志
目前系统中仅剩两组日志组，此时我们尝试再删除一组日志，看看结果如何。
sys@ora10g> alter database drop logfile group 2;
alter database drop logfile group 2
*
error at line 1:
ora-01567: dropping log 2 would leave less than 2 log files for instance ora10g (thread 1)
ora-00312: online log 2 thread 1: '/oracle/ora10gr2/oradata/ora10g/redo02.log'
其中的“ora-01567”错误提示内容已经清晰的说明了一切：dropping log 2 would leave less than 2 log files for instance ora10g (thread 1)
4.3. 处理备库standby redo
配置原则：
①standby logfile大小要和redo logfile大小相同；
②standby logfile要比redo logfile多一组，目前redo logfile是3组，因此创建4组standby logfile；
③为了安全，standby logfile可以包含多个member。
补充：创建适当的日志组
一般而言，standby redo日志文件组数要比primary数据库的online redo日志文件组数至少多一个。推荐standby redo日志组数量基于primary数据库的线程数(这里的线程数可以理解为rac结构中的rac节点数)。
有一个推荐的公式可以做参考：(每线程的日志组数 1)*最大线程数
例如primary数据库有两个线程，每个线程分配两组日志，则standby日志组数建议为6组，使用这个公式可以降低primary数据库实例lgwr进程锁住的可能性。
--备库查看现有standby log
sys@fuwa 2023-11-30 10:29:32> select group#,sum(bytes/1024/1024) "size in mb" from v$standby_log group by group#;
group# size in mb
---------- ----------
6 50
4 50
5 50
7 50
sys@fuwa 2023-11-30 10:30:48> select group#,status from v$standby_log;
group# status
---------- ----------
4 unassigned
5 unassigned
6 unassigned
7 unassigned
--备库中删除standby log
sys@fuwa 2023-11-30 10:30:48> alter database drop standby logfile group 4;
sys@fuwa 2023-11-30 10:30:48> alter database drop standby logfile group 5;
sys@fuwa 2023-11-30 10:30:48> alter database drop standby logfile group 6;
sys@fuwa 2023-11-30 10:30:48> alter database drop standby logfile group 7;
--备库服务器上删除standby log
[root@db01:/data]$ cd /data/oradata/fuwa/
[root@db01:/data/oradata/fuwa]$ mv redo04.log redo05.log redo06.log redo07.log /home/oracle/
--备库中增加standby log
alter database add standby logfile group 4 '/data/oradata/fuwa/redo04.log' size 100m;
alter database add standby logfile group 5 '/data/oradata/fuwa/redo05.log' size 100m;
alter database add standby logfile group 6 '/data/oradata/fuwa/redo06.log' size 100m;
alter database add standby logfile group 7 '/data/oradata/fuwa/redo07.log' size 100m;
alter database add standby logfile group 9 '/data/oradata/fuwa/redo09.log' size 100m;
4.4. 备库恢复日志应用
--备库更改参数
alter system set standby_file_management=auto;
--备库实时应用
alter database recover managed standby database using current logfile disconnect from session;
--监控各进程的状态，以及归档应用情况
select process,status,sequence# from v$managed_standby;
参考网址：
参考网址：
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。