重庆思庄Oracle、Redhat认证学习论坛
标题: 主备库redo日志大小不一样的异常处理 [打印本页]
作者: denglj 时间: 2022-7-1 20:59
标题: 主备库redo日志大小不一样的异常处理
本帖最后由 denglj 于 2022-7-1 21:01 编辑
实验目标：模拟主备库redo日志大小不一样的异常场景，并提供解决方案.
环境介绍：
主库：oracle11.2.0.4 + CentOS 7.9 实例名：orcl150
备库：oracle11.2.0.4 + CentOS 7.9 实例名：orcl151
1、redo大小
1.1、主库情况SQL>select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
SQL>col member for a60
SQL> r
1* selectb.thread#,a.group#,a.member,bytes/1024/1024,b.members from v$logfile a,v$log bwhere a.group#=b.group#
THREAD# GROUP# MEMBER BYTES/1024/1024 MEMBERS
-------------------- --------------------------------------------------------------------------- ----------
1 3/u01/app/oracle/oradata/orcl150/redo03.log 50 1
1 2/u01/app/oracle/oradata/orcl150/redo02.log 50 1
1 1/u01/app/oracle/oradata/orcl150/redo01.log 50 1
SQL>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
---------------------------------------------------------------------- ---------- ---------- ---------------- -------
/u01/app/oracle/oradata/orcl150/redo03.log 1 1 50 INACTIVE ONLINE
/u01/app/oracle/oradata/orcl150/redo03.log 2 1 50 INACTIVE ONLINE
/u01/app/oracle/oradata/orcl150/redo03.log 3 1 50 CURRENT ONLINE
/u01/app/oracle/oradata/orcl150/redo02.log 1 1 50 INACTIVE ONLINE
/u01/app/oracle/oradata/orcl150/redo02.log 2 1 50 INACTIVE ONLINE
/u01/app/oracle/oradata/orcl150/redo02.log 3 1 50 CURRENT ONLINE
/u01/app/oracle/oradata/orcl150/redo01.log 1 1 50 INACTIVE ONLINE
/u01/app/oracle/oradata/orcl150/redo01.log 2 1 50 INACTIVE ONLINE
/u01/app/oracle/oradata/orcl150/redo01.log 3 1 50 CURRENT ONLINE
/u01/app/oracle/oradata/orcl150/redo11_standby.log 1 1 50 INACTIVE STANDBY
/u01/app/oracle/oradata/orcl150/redo11_standby.log 2 1 50 INACTIVE STANDBY
MEMBER GROUP# THREAD# size(M) STATUS TYPE
---------------------------------------------------------------------- ---------- ---------- ---------------- -------
/u01/app/oracle/oradata/orcl150/redo11_standby.log 3 1 50 CURRENT STANDBY
/u01/app/oracle/oradata/orcl150/redo12_standby.log 1 1 50 INACTIVE STANDBY
/u01/app/oracle/oradata/orcl150/redo12_standby.log 2 1 50 INACTIVE STANDBY
/u01/app/oracle/oradata/orcl150/redo12_standby.log 3 1 50 CURRENT STANDBY
/u01/app/oracle/oradata/orcl150/redo13_standby.log 1 1 50 INACTIVE STANDBY
/u01/app/oracle/oradata/orcl150/redo13_standby.log 2 1 50 INACTIVE STANDBY
/u01/app/oracle/oradata/orcl150/redo13_standby.log 3 1 50 CURRENT STANDBY
/u01/app/oracle/oradata/orcl150/redo14_standby.log 1 1 50 INACTIVE STANDBY
/u01/app/oracle/oradata/orcl150/redo14_standby.log 2 1 50 INACTIVE STANDBY
/u01/app/oracle/oradata/orcl150/redo14_standby.log 3 1 50 CURRENT STANDBY
21 rowsselected.
1.2、备库情况SQL>select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICALSTANDBY
SQL>select b.thread#,a.group#,a.member,bytes/1024/1024,b.members from v$logfilea,v$log b where a.group#=b.group#;
THREAD# GROUP# MEMBER BYTES/1024/1024 MEMBERS
-------------------- --------------------------------------------------------------------------- ----------
1 3/u01/app/oracle/oradata/orcl151/redo03.log 50 1
1 2 /u01/app/oracle/oradata/orcl151/redo02.log 50 1
1 1/u01/app/oracle/oradata/orcl151/redo01.log 50 1
2、增加主库redo大小
2.1、取消实时应用首先将备库实时应用取消
SQL>alter database recover managed standby database cancel;
Databasealtered.
2.2、处理主库redo接下来在主库操作.
SQL>alter database add logfile group 4('/u01/app/oracle/oradata/orcl150/redo04.log') size 100m;
Databasealtered.
SQL>alter system switch logfile;
Systemaltered.
SQL>alter system checkpoint;
Systemaltered.
SQL>select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
-------------------- ---------- ---------- ---------------- ---
1 1 22 1 INACTIVE YES
2 1 23 1 INACTIVE YES
3 1 24 1 INACTIVE YES
4 1 25 1 CURRENT NO
SQL>alter database drop logfile group 1; 说明：drop前group1的status需为inactive，下同.
Databasealtered.
SQL>select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
-------------------- ---------- ---------- ---------------- ---
2 1 23 1 INACTIVE YES
3 1 24 1 INACTIVE YES
4 1 25 1 CURRENT NO
SQL>alter database add logfile group 1('/u01/app/oracle/oradata/orcl150/redo01.log') size 100m;
alterdatabase add logfile group 1 ('/u01/app/oracle/oradata/orcl150/redo01.log')size 100m
*
ERROR atline 1:
ORA-00301:error in adding log file '/u01/app/oracle/oradata/orcl150/redo01.log' - filecannot be created
ORA-27038:created file already exists
Additionalinformation: 1
操作系统层面删除redo01.log
[root@oel~]# cd /u01/app/oracle/oradata/orcl150/
[root@oelorcl150]# ll
total1777672
-rw-r-----1 oracle oinstall 9748480 Jun 30 17:16control01.ctl
-rw-r-----1 oracle oinstall 52429312 Jun 30 17:10redo01.log
-rw-r-----1 oracle oinstall 52429312 Jun 30 17:14redo02.log
-rw-r-----1 oracle oinstall 52429312 Jun 30 17:09redo03.log
-rw-r-----1 oracle oinstall 104858112 Jun 30 17:16 redo04.log
-rw-r-----1 oracle oinstall 52429312 Apr 14 21:52redo11_standby.log
-rw-r-----1 oracle oinstall 52429312 Apr 14 21:52redo12_standby.log
-rw-r-----1 oracle oinstall 52429312 Apr 14 21:52redo13_standby.log
-rw-r-----1 oracle oinstall 52429312 Apr 14 21:52redo14_standby.log
-rw-r-----1 oracle oinstall 524296192 Jun 30 17:15 sysaux01.dbf
-rw-r-----1 oracle oinstall 775954432 Jun 30 17:14 system01.dbf
-rw-r-----1 oracle oinstall 20979712 Jun 30 17:15temp01.dbf
-rw-r-----1 oracle oinstall 31465472 Jun 30 17:14undotbs01.dbf
-rw-r-----1 oracle oinstall 5251072 Jun 30 17:14users01.dbf
[root@oelorcl150]# rm -rf redo01.log
SQL>alter database add logfile group 1 ('/u01/app/oracle/oradata/orcl150/redo01.log')size 100m;
Databasealtered.
SQL>alter database drop logfile group 2;
Databasealtered.
SQL>alter database drop logfile group 3;
Databasealtered.
SQL>select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
-------------------- ---------- ---------- ---------------- ---
1 1 0 1 UNUSED YES
4 1 25 1 CURRENT NO
[root@oelorcl150]# rm -rf redo02.log
[root@oelorcl150]# rm -rf redo03.log
SQL>alter database add logfile group 2('/u01/app/oracle/oradata/orcl150/redo02.log') size 100m;
Databasealtered.
SQL>alter database add logfile group 3('/u01/app/oracle/oradata/orcl150/redo03.log') size 100m;
Databasealtered.
SQL>select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
-------------------- ---------- ---------- ---------------- ---
1 1 0 1 UNUSED YES
2 1 0 1 UNUSED YES
3 1 0 1 UNUSED YES
4 1 18 1 CURRENT NO
SQL>alter system switch logfile;
Systemaltered.
SQL>alter system checkpoint;
Systemaltered.
SQL>select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
-------------------- ---------- ---------- ---------------- ---
1 1 19 1 CURRENT NO
2 1 0 1 UNUSED YES
3 1 0 1 UNUSED YES
4 1 18 1 INACTIVE YES
SQL>alter database drop logfile group 4;
Databasealtered.
[root@oelorcl150]# rm -rf redo04.log
SQL>select b.thread#,a.group#,a.member,bytes/1024/1024,b.members from v$logfilea,v$log b where a.group#=b.group#;
THREAD# GROUP# MEMBER BYTES/1024/1024 MEMBERS
-------------------- --------------------------------------------------------------------------- ----------
1 2/u01/app/oracle/oradata/orcl150/redo02.log 100 1
1 3/u01/app/oracle/oradata/orcl150/redo03.log 100 1
1 1/u01/app/oracle/oradata/orcl150/redo01.log 100 1
SQL> 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
---------------------------------------------------------------------- ---------- ---------- ---------------- -------
/u01/app/oracle/oradata/orcl150/redo02.log 1 1 100 CURRENT ONLINE
/u01/app/oracle/oradata/orcl150/redo02.log 2 1 100 UNUSED ONLINE
/u01/app/oracle/oradata/orcl150/redo02.log 3 1 100 UNUSED ONLINE
/u01/app/oracle/oradata/orcl150/redo03.log 1 1 100 CURRENT ONLINE
/u01/app/oracle/oradata/orcl150/redo03.log 2 1 100 UNUSED ONLINE
/u01/app/oracle/oradata/orcl150/redo03.log 3 1 100 UNUSED ONLINE
/u01/app/oracle/oradata/orcl150/redo01.log 1 1 100 CURRENT ONLINE
/u01/app/oracle/oradata/orcl150/redo01.log 2 1 100 UNUSED ONLINE
/u01/app/oracle/oradata/orcl150/redo01.log 3 1 100 UNUSED ONLINE
/u01/app/oracle/oradata/orcl150/redo11_standby.log 1 1 100 CURRENT STANDBY
/u01/app/oracle/oradata/orcl150/redo11_standby.log 2 1 100 UNUSED STANDBY
MEMBER GROUP# THREAD# size(M) STATUS TYPE
---------------------------------------------------------------------- ---------- ---------- ---------------- -------
/u01/app/oracle/oradata/orcl150/redo11_standby.log 3 1 100 UNUSED STANDBY
/u01/app/oracle/oradata/orcl150/redo12_standby.log 1 1 100 CURRENT STANDBY
/u01/app/oracle/oradata/orcl150/redo12_standby.log 2 1 100 UNUSED STANDBY
/u01/app/oracle/oradata/orcl150/redo12_standby.log 3 1 100 UNUSED STANDBY
/u01/app/oracle/oradata/orcl150/redo13_standby.log 1 1 100 CURRENT STANDBY
/u01/app/oracle/oradata/orcl150/redo13_standby.log 2 1 100 UNUSED STANDBY
/u01/app/oracle/oradata/orcl150/redo13_standby.log 3 1 100 UNUSED STANDBY
/u01/app/oracle/oradata/orcl150/redo14_standby.log 1 1 100 CURRENT STANDBY
/u01/app/oracle/oradata/orcl150/redo14_standby.log 2 1 100 UNUSED STANDBY
/u01/app/oracle/oradata/orcl150/redo14_standby.log 3 1 100 UNUSED STANDBY
21 rows selected.
SQL>select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
-------------------- ---------- ---------- ---------------- ---
1 1 19 1 CURRENT NO
2 1 0 1 UNUSED YES
3 1 0 1 UNUSED YES
2.3、处理主库standby redo现在处理主库上的standbyredo log
SQL>select group#,sum(bytes/1024/1024) "Size in MB" from v$standby_loggroup by group#;
GROUP# Size in MB
--------------------
11 50
13 50
14 50
12 50
SQL>select group#,status from v$standby_log;
GROUP# STATUS
--------------------
11 UNASSIGNED
12 UNASSIGNED
13 UNASSIGNED
14 UNASSIGNED
SQL>alter database drop standby logfile group 11;
Databasealtered.
SQL>alter database drop standby logfile group 12;
Databasealtered.
SQL>alter database drop standby logfile group 13;
Databasealtered.
SQL>alter database drop standby logfile group 14;
Databasealtered.
系统层面操作
[root@oelorcl150]# rm -rf redo11_standby.log
[root@oelorcl150]# rm -rf redo12_standby.log
[root@oelorcl150]# rm -rf redo13_standby.log
[root@oelorcl150]# rm -rf redo14_standby.log
SQL>alter database add standby logfile group 11'/u01/app/oracle/oradata/orcl150/redo11_standby.log' size 100m;
SQL>alter database add standby logfile group 12'/u01/app/oracle/oradata/orcl150/redo12_standby.log' size 100m;
SQL>alter database add standby logfile group 13 '/u01/app/oracle/oradata/orcl150/redo13_standby.log'size 100m;
SQL>alter database add standby logfile group 14'/u01/app/oracle/oradata/orcl150/redo14_standby.log' size 100m;
SQL>select group#,sum(bytes/1024/1024) "Size in MB" from v$standby_loggroup by group#;
GROUP# Size in MB
--------------------
11 100
13 100
14 100
12 100
说明：如上所示，成功模拟出主备库redo日志大小不一样的场景.
3、模拟故障
3.1、主库产生redo日志在主库添加数据，redo日志.
SQL>create table t1 tablespace users as select * from user_objects;
Tablecreated.
SQL>insert into t1 select * from t1;
37697rows created.
SQL> r
1* insert into t1 select * from t1
75394rows created.
SQL> r
1* insert into t1 select * from t1
150788rows created.
SQL> r
1* insert into t1 select * from t1
301576rows created.
SQL> r
1* insert into t1 select * from t1
603152rows created.
SQL> r
1* insert into t1 select * from t1
r
1206304rows created.
SQL> 1* insert into t1 select * from t1
2412608rows created.
SQL>commit;
Commitcomplete.
SQL>alter system switch logfile;
Systemaltered.
SQL>select count(*) from t1;
COUNT(*)
----------
4825216
3.2、备库日志2022-06-3016:00:58.987000 +08:00
ArchivedLog entry 33 added for thread 1 sequence 26 rlc 1101942938 ID 0xf1d7f01e dest2:
RFS[3]:No standby redo logfiles available for thread 1
RFS[3]:Opened log for thread 1 sequence 27 dbid -244204776 branch 1101942938
MediaRecovery Log /u01/app/oracle/fast_recovery_area/archivelog/1_26_1101942938.dbf
报错"Nostandby redo logfiles available for thread 1",实际生产过程中会导致主备实时应用停止.
4、解决方案
4.1、修改前准备备库停止MRP
SQL>alter database recover managed standby database cancel;
Databasealtered.
SQL>show parameter standby_file_management
NAME TYPE VALUE
----------------------------------------------- ------------------------------
standby_file_management string AUTO
说明：备库上此参数需要设置为manual，才能修改日志组.
SQL>alter system set standby_file_management=manual;
Systemaltered.
SQL>show parameter standby_file_management;
NAME TYPE VALUE
----------------------------------------------- ------------------------------
standby_file_management string MANUAL
4.2、处理备库redo扩展备库日志大小
SQL> selectgroup#,status from v$log;
GROUP# STATUS
--------------------------
1 CURRENT
2 CLEARING
3 UNUSED
说明：处理clearing 状态的日志组
SQL>alter database clear logfile group 2;
Databasealtered.
SQL>alter database drop logfile group 2;
Databasealtered.
[root@oeldgorcl151]# rm -rf redo02.log
SQL>alter database add logfile group 2 '/u01/app/oracle/oradata/orcl151/redo02.log'size 100m;
Databasealtered.
SQL>select group#,status from v$log;
GROUP# STATUS
--------------------------
1 CURRENT
2 UNUSED
3 UNUSED
处理处于current状态的日志组1.
回到主库，切换日志组 altersystem switch logfile;
备库检查日志组1的状态，直到状态为clearing后再按上面方式处理日志组1.
SQL>select group#,status from v$log;
GROUP# STATUS
--------------------------
1 CLEARING
2 CURRENT
3 UNUSED
SQL>alter database clear logfile group 1;
Databasealtered.
SQL>alter database drop logfile group 1;
Databasealtered.
[root@oeldgorcl151]# rm -rf redo01.log
SQL>alter database add logfile group 1 '/u01/app/oracle/oradata/orcl151/redo01.log'size 100m;
Databasealtered.
主库切换日志组 altersystem switch logfile;
SQL>select group#,status from v$log
GROUP# STATUS
--------------------------
1 CURRENT
2 CLEARING
3 CLEARING
SQL>alter database clear logfile group 3;
Databasealtered.
SQL>alter database drop logfile group 3;
Databasealtered.
[root@oeldgorcl151]# rm -rf redo03.log
SQL>alter database add logfile group 3 '/u01/app/oracle/oradata/orcl151/redo03.log'size 100m;
Databasealtered.
SQL> selectvlf.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
---------------------------------------------------------------------------------------------------- ---------- ---------- ---------------- -------
/u01/app/oracle/oradata/orcl151/redo03.log 1 1 100 CURRENT ONLINE
/u01/app/oracle/oradata/orcl151/redo03.log 2 1 100 CLEARING ONLINE
/u01/app/oracle/oradata/orcl151/redo03.log 3 1 100 UNUSED ONLINE
/u01/app/oracle/oradata/orcl151/redo02.log 1 1 100 CURRENT ONLINE
/u01/app/oracle/oradata/orcl151/redo02.log 2 1 100 CLEARING ONLINE
/u01/app/oracle/oradata/orcl151/redo02.log 3 1 100 UNUSED ONLINE
/u01/app/oracle/oradata/orcl151/redo01.log 1 1 100 CURRENT ONLINE
/u01/app/oracle/oradata/orcl151/redo01.log 2 1 100 CLEARING ONLINE
/u01/app/oracle/oradata/orcl151/redo01.log 3 1 100 UNUSED ONLINE
/u01/app/oracle/oradata/orcl151/redo11_standby.log 1 1 100 CURRENT STANDBY
/u01/app/oracle/oradata/orcl151/redo11_standby.log 2 1 100 CLEARING STANDBY
MEMBER GROUP# THREAD# size(M) STATUS TYPE
---------------------------------------------------------------------------------------------------- ---------- ---------- ---------------- -------
/u01/app/oracle/oradata/orcl151/redo11_standby.log 3 1 100 UNUSED STANDBY
/u01/app/oracle/oradata/orcl151/redo12_standby.log 1 1 100 CURRENT STANDBY
/u01/app/oracle/oradata/orcl151/redo12_standby.log 2 1 100 CLEARING STANDBY
/u01/app/oracle/oradata/orcl151/redo12_standby.log 3 1 100 UNUSED STANDBY
/u01/app/oracle/oradata/orcl151/redo13_standby.log 1 1 100 CURRENT STANDBY
/u01/app/oracle/oradata/orcl151/redo13_standby.log 2 1 100 CLEARING STANDBY
/u01/app/oracle/oradata/orcl151/redo13_standby.log 3 1 100 UNUSED STANDBY
/u01/app/oracle/oradata/orcl151/redo14_standby.log 1 1 100 CURRENT STANDBY
/u01/app/oracle/oradata/orcl151/redo14_standby.log 2 1 100 CLEARING STANDBY
/u01/app/oracle/oradata/orcl151/redo14_standby.log 3 1 100 UNUSED STANDBY
21 rows selected.
SQL>show parameter standby_file_management;
NAME TYPE VALUE
----------------------------------------------- ------------------------------
standby_file_management string MANUAL
4.3、处理备库standby redo
SQL>select group#,sum(bytes/1024/1024) "Size in MB" from v$standby_loggroup by group#;
GROUP# Size in MB
--------------------
11 50
13 50
14 50
12 50
SQL>select group#,status from v$standby_log;
GROUP# STATUS
--------------------
11 UNASSIGNED
12 UNASSIGNED
13 UNASSIGNED
14 UNASSIGNED
SQL>alter database drop standby logfile group 11;
Databasealtered.
SQL>alter database drop standby logfile group 12;
Databasealtered.
SQL>alter database drop standby logfile group 13;
Databasealtered.
SQL>alter database drop standby logfile group 14;
Databasealtered.
[root@oeldgorcl151]# rm -rf redo11_standby.log
[root@oeldgorcl151]# rm -rf redo12_standby.log
[root@oeldgorcl151]# rm -rf redo13_standby.log
[root@oeldgorcl151]# rm -rf redo14_standby.log
[root@oeldgorcl151]# pwd
/u01/app/oracle/oradata/orcl151
SQL>alter database add standby logfile group 11'/u01/app/oracle/oradata/orcl151/redo11_standby.log' size 100m;
Databasealtered.
SQL>alter database add standby logfile group 12'/u01/app/oracle/oradata/orcl151/redo12_standby.log' size 100m;
Databasealtered.
SQL>alter database add standby logfile group 13'/u01/app/oracle/oradata/orcl151/redo13_standby.log' size 100m;
Databasealtered.
SQL>alter database add standby logfile group 14'/u01/app/oracle/oradata/orcl151/redo14_standby.log' size 100m;
Databasealtered.
4.4、备库恢复日志应用SQL>alter system set standby_file_management=auto;
Systemaltered.
SQL>alter database recover managed standby database using current logfiledisconnect from session;
Databasealtered.
SQL>select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------------------- ----------
ARCH CLOSING 17
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 25
MRP0 WAIT_FOR_LOG 25
9 rowsselected.
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) |
Powered by Discuz! X3.2 |