实验目标
模拟主备库redo日志大小不一样的异常场景,并提供解决方案。
环境介绍
主库: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、解决方案
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;
参考网址:https://blog.51cto.com/u_12991611/5432506
参考网址:https://wenku.baidu.com/view/87aab6f11a5f312b3169a45177232f60ddcce78f.html




