本帖最后由 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认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2022-7-1 20:59:06
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对