转载

20170315测试在线日志与备用日志大小不一样

[20170315]测试在线日志与备用日志大小不一样.txt

--//今天测试一下,主库的在线日志与备库的备用日志大小不一样的情况,因为备库日志主要目的是接收主库传输过来的日志并应用日志.
--//仅仅测试备用日志小于主库在线日志的情况.

--//一般运维要求日志大小在线与备用大小一致,备用日志的数量比在线日志数量+1,如果rac环境也就是每个实例比在线日志多一组.
--//满足业务高峰20-30分钟切换一次就ok了.

--//出现不一致的情况,主要是主库修改日志大小,而备库忘记修改.测试这种情况下会出现什么情况:

1.环境:
SYS@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

--//备库
SYS@bookdg> @ &r/logfile
GROUP# STATUS     TYPE       MEMBER                           IS_ GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- -------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
     1            ONLINE     /mnt/ramdisk/book/redo01.log     NO       1       1       805    52428800       512       1 YES CURRENT      13277509688 2017-03-15 09:21:13  13277509678 2017-03-15 09:21:11
     2            ONLINE     /mnt/ramdisk/book/redo02.log     NO       2       1       803    52428800       512       1 YES CLEARING     13277509678 2017-03-15 09:21:11  13277509683 2017-03-15 09:21:12
     3            ONLINE     /mnt/ramdisk/book/redo03.log     NO       3       1       804    52428800       512       1 YES CLEARING     13277509683 2017-03-15 09:21:12  13277509688 2017-03-15 09:21:13
     4            STANDBY    /mnt/ramdisk/book/redostb01.log  NO
     5            STANDBY    /mnt/ramdisk/book/redostb02.log  NO
     6            STANDBY    /mnt/ramdisk/book/redostb03.log  NO
     7            STANDBY    /mnt/ramdisk/book/redostb04.log  NO
7 rows selected.

SYS@bookdg> select * from v$standby_log;
GROUP# DBID        THREAD# SEQUENCE#       BYTES BLOCKSIZE         USED ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME           LAST_CHANGE# LAST_TIME
------ ----------- ------- --------- ----------- --------- ------------ --- ---------- ------------- ------------------- ------------ ------------------- ------------ -------------------
     4 UNASSIGNED        1         0    52428800       512            0 NO  UNASSIGNED
     5 1337401710        1       805    52428800       512        76288 YES ACTIVE       13277509688 2017-03-15 09:21:13                                   13277509837 2017-03-15 09:23:42
     6 UNASSIGNED        1         0    52428800       512            0 NO  UNASSIGNED
     7 UNASSIGNED        0         0    52428800       512            0 YES UNASSIGNED

--//当前是group#=5在接收日志.

2.删除备库的备用日志:
SYS@bookdg> alter database drop standby logfile group 4;
alter database drop standby logfile group 4
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

$ oerr ora 01156
01156, 00000, "recovery or flashback in progress may need access to files"
// *Cause: Either media recovery, instance recovery, or flashback was
//         in progress. The recovery or flashback in progress may need
//         the files to which this operation is being applied.
// *Action: Wait for recovery or flashback to complete.


SYS@bookdg> alter database recover managed standby database cancel ;
Database altered.

SYS@bookdg> alter database drop standby logfile group 4;
Database altered.

SYS@bookdg> alter database drop standby logfile group 6;
Database altered.

SYS@bookdg> alter database drop standby logfile group 7;
Database altered.

SYS@bookdg> alter database drop standby logfile group 5;
alter database drop standby logfile group 5
*
ERROR at line 1:
ORA-00261: log 5 of thread 1 is being archived or modified
ORA-00312: online log 5 thread 1: '/mnt/ramdisk/book/redostb02.log'
--//在用无法drop.

--//主库执行:
SYS@book> alter system archive log current;
System altered.

--//备库执行:
SYS@bookdg> alter database drop standby logfile group 5;
Database altered.

--//ok现在删除备库全部备用日志.现在建立新的备用日志,大小20M
--//alter database add standby logfile  ('/mnt/ramdisk/book/redostb01.log') size 50m ;
SYS@bookdg> alter database add standby logfile  group 4 ('/mnt/ramdisk/book/redostb01.log') size 20m reuse;
Database altered.

alter database add standby logfile  group 5 ('/mnt/ramdisk/book/redostb02.log') size 20m reuse;
alter database add standby logfile  group 6 ('/mnt/ramdisk/book/redostb03.log') size 20m reuse;
alter database add standby logfile  group 7 ('/mnt/ramdisk/book/redostb04.log') size 20m reuse;

GROUP# DBID       THREAD# SEQUENCE#       BYTES BLOCKSIZE         USED ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME           LAST_CHANGE# LAST_TIME
------ ---------- ------- --------- ----------- --------- ------------ --- ---------- ------------- ------------------- ------------ ------------------- ------------ -------------------
     4 UNASSIGNED       0         0    20971520       512            0 YES UNASSIGNED
     5 UNASSIGNED       0         0    20971520       512            0 YES UNASSIGNED
     6 UNASSIGNED       0         0    20971520       512            0 YES UNASSIGNED
     7 UNASSIGNED       0         0    20971520       512            0 YES UNASSIGNED

--//现在全部是20M.

3.开启日志传输与应用看看:

SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
Database altered.

SYS@bookdg> select * from v$standby_log;
GROUP# DBID       THREAD#  SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME           LAST_CHANGE# LAST_TIME
------ ---------- ------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------- ------------ ------------------- ------------ -------------------
     4 UNASSIGNED       0          0   20971520        512          0 YES UNASSIGNED
     5 UNASSIGNED       0          0   20971520        512          0 YES UNASSIGNED
     6 UNASSIGNED       0          0   20971520        512          0 YES UNASSIGNED
     7 UNASSIGNED       0          0   20971520        512          0 YES UNASSIGNED
--//可以发现备用日志并不接受日志.
SYS@bookdg> @ &r/dg/dg
PROCESS       PID STATUS       CLIENT_P GROUP#  THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------- ------- ---------- ---------- ---------- ----------
RFS         10079 IDLE         ARCH     N/A           0          0          0          0          0
RFS         10077 IDLE         LGWR     2             1        806        940          1          0
ARCH        10072 CLOSING      ARCH     5             1        805          1        462          0
MRP0        10163 WAIT_FOR_LOG N/A      N/A           1        806          0          0          0

--//这样并不能使用实时应用.MRP0仅仅停止在block#=0的情况.RFS PID=10077 group#=2在接收.

--//主库执行:
SYS@book> alter system archive log current;
System altered.

--//备库执行:
SYS@bookdg> @ &r/dg/dg
PROCESS       PID STATUS       CLIENT_P GROUP#  THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ------- ------------ -------- --------------- ---------- ---------- ---------- ----------
RFS         10079 IDLE         ARCH     N/A           0          0          0          0          0
RFS         10077 IDLE         LGWR     3             1        807         16          1          0
ARCH        10072 CLOSING      ARCH     5             1        805          1        462          0
MRP0        10163 WAIT_FOR_LOG N/A      N/A           1        807          0          0          0

--//可以发现在归档后才应用,也就是延迟应用.
--//实际上RFS PID=10077 group#=3在接收.真的码?

SYS@bookdg> alter system dump logfile '/mnt/ramdisk/book/redo03.log' validate ;
alter system dump logfile '/mnt/ramdisk/book/redo03.log' validate
*
ERROR at line 1:
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: '/mnt/ramdisk/book/redo03.log'

--//很明显接收的不是在线日志,而是

$ ls -l /proc/10077/fd
total 14
lr-x------  1 oracle oinstall 64 2017-03-15 09:51:18 0 -> /dev/null
l-wx------  1 oracle oinstall 64 2017-03-15 09:51:18 1 -> /dev/null
lrwx------  1 oracle oinstall 64 2017-03-15 09:51:18 14 -> socket:[429975916]
l-wx------  1 oracle oinstall 64 2017-03-15 09:51:18 2 -> /dev/null
lrwx------  1 oracle oinstall 64 2017-03-15 09:51:18 256 -> /data/ramdisk/book/control01.ctl
lrwx------  1 oracle oinstall 64 2017-03-15 09:51:18 257 -> /data/ramdisk/book/control02.ctl
lrwx------  1 oracle oinstall 64 2017-03-15 09:51:18 258 -> /u01/app/oracle/archivelog/book/1_807_896605872.dbf
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
lr-x------  1 oracle oinstall 64 2017-03-15 09:51:18 3 -> /dev/null
lr-x------  1 oracle oinstall 64 2017-03-15 09:51:18 4 -> /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/mesg/oraus.msb
lr-x------  1 oracle oinstall 64 2017-03-15 09:51:18 5 -> /proc/10077/fd
lr-x------  1 oracle oinstall 64 2017-03-15 09:51:18 6 -> /dev/zero
lrwx------  1 oracle oinstall 64 2017-03-15 09:51:18 7 -> /u01/app/oracle/admin/book/adump/bookdg_ora_10077_20170315091852338409143795.aud
l-wx------  1 oracle oinstall 64 2017-03-15 09:51:18 8 -> /u01/app/oracle/diag/rdbms/bookdg/bookdg/trace/bookdg_rfs_10077.trc
l-wx------  1 oracle oinstall 64 2017-03-15 09:51:18 9 -> /u01/app/oracle/diag/rdbms/bookdg/bookdg/trace/bookdg_rfs_10077.trm

$ ls -l /u01/app/oracle/archivelog/book/1_807_896605872.dbf
-rw-r-----  1 oracle oinstall 52429312 2017-03-15 09:54:41 /u01/app/oracle/archivelog/book/1_807_896605872.dbf

--//很明显接收日志的是归档目的文件/u01/app/oracle/archivelog/book/1_807_896605872.dbf.注意大小52429312/1024/1024= 50M+512字节.

--//主库执行:
SYS@book> alter system archive log current;
System altered.

--//备库执行:
$ ls -l /u01/app/oracle/archivelog/book/1_807_896605872.dbf
-rw-r-----  1 oracle oinstall 409600 2017-03-15 09:56:40 /u01/app/oracle/archivelog/book/1_807_896605872.dbf

--//现在才还归档的文件大小.

4.采用备用日志文件大小大于在线日志.

SYS@bookdg> alter database recover managed standby database cancel ;
Database altered.

alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;
alter database drop standby logfile group 7;

alter database add standby logfile  group 4 ('/mnt/ramdisk/book/redostb01.log') size 60m reuse;
alter database add standby logfile  group 5 ('/mnt/ramdisk/book/redostb02.log') size 60m reuse;
alter database add standby logfile  group 6 ('/mnt/ramdisk/book/redostb03.log') size 60m reuse;
alter database add standby logfile  group 7 ('/mnt/ramdisk/book/redostb04.log') size 60m reuse;

--//再次启动日志传输与应用.
SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
Database altered.

SYS@bookdg> select * from v$standby_log;
GROUP# DBID       THREAD#  SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME           LAST_CHANGE# LAST_TIME
------ ---------- ------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------- ------------ ------------------- ------------ -------------------
     4 1337401710       1        809   62914560        512       1536 YES ACTIVE        1.3278E+10 2017-03-15 10:01:04                                    1.3278E+10 2017-03-15 10:01:05
     5 UNASSIGNED       0          0   62914560        512          0 YES UNASSIGNED
     6 UNASSIGNED       0          0   62914560        512          0 YES UNASSIGNED
     7 UNASSIGNED       0          0   62914560        512          0 YES UNASSIGNED

SYS@bookdg> @ &r/dg/dg
PROCESS       PID STATUS       CLIENT_P GROUP#  THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------- ------- ---------- ---------- ---------- ----------
RFS         10079 IDLE         ARCH     N/A           0          0          0          0          0
RFS         10077 IDLE         LGWR     2             1        809         42          1          0
ARCH        10072 CLOSING      ARCH     5             1        805          1        462          0
MRP0        10232 APPLYING_LOG N/A      N/A           1        809         42     122880          0
    
--//现在备用日志接收并应用日志.
--//再看看相关进程打开文件句柄的情况.

$ ls -l /proc/10232/fd
total 14
lr-x------  1 oracle oinstall 64 2017-03-15 10:02:21 0 -> /dev/null
l-wx------  1 oracle oinstall 64 2017-03-15 10:02:21 1 -> /dev/null
l-wx------  1 oracle oinstall 64 2017-03-15 10:02:21 10 -> /u01/app/oracle/diag/rdbms/bookdg/bookdg/trace/bookdg_mrp0_10232.trc
l-wx------  1 oracle oinstall 64 2017-03-15 10:02:21 11 -> /u01/app/oracle/diag/rdbms/bookdg/bookdg/trace/bookdg_mrp0_10232.trm
l-wx------  1 oracle oinstall 64 2017-03-15 10:02:21 2 -> /dev/null
lrwx------  1 oracle oinstall 64 2017-03-15 10:02:21 256 -> /data/ramdisk/book/control01.ctl
lrwx------  1 oracle oinstall 64 2017-03-15 10:02:21 257 -> /data/ramdisk/book/control02.ctl
lr-x------  1 oracle oinstall 64 2017-03-15 10:02:21 3 -> /dev/null
lr-x------  1 oracle oinstall 64 2017-03-15 10:02:21 4 -> /dev/null
lr-x------  1 oracle oinstall 64 2017-03-15 10:02:21 5 -> /dev/null
lr-x------  1 oracle oinstall 64 2017-03-15 10:02:21 6 -> /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/mesg/oraus.msb
lr-x------  1 oracle oinstall 64 2017-03-15 10:02:21 7 -> /proc/10232/fd
lr-x------  1 oracle oinstall 64 2017-03-15 10:02:21 8 -> /dev/zero
lrwx------  1 oracle oinstall 64 2017-03-15 10:02:21 9 -> /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/hc_bookdg.dat
--//可以发现MPR0进程10232并没有打开相关备用日志句柄.

$ ls -l /proc/10077/fd
total 14
lr-x------  1 oracle oinstall 64 2017-03-15 09:51:18 0 -> /dev/null
l-wx------  1 oracle oinstall 64 2017-03-15 09:51:18 1 -> /dev/null
lrwx------  1 oracle oinstall 64 2017-03-15 09:51:18 14 -> socket:[429975916]
l-wx------  1 oracle oinstall 64 2017-03-15 09:51:18 2 -> /dev/null
lrwx------  1 oracle oinstall 64 2017-03-15 09:51:18 256 -> /data/ramdisk/book/control01.ctl
lrwx------  1 oracle oinstall 64 2017-03-15 09:51:18 257 -> /data/ramdisk/book/control02.ctl
lrwx------  1 oracle oinstall 64 2017-03-15 09:51:18 258 -> /data/ramdisk/book/redostb01.log
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
lr-x------  1 oracle oinstall 64 2017-03-15 09:51:18 3 -> /dev/null
lr-x------  1 oracle oinstall 64 2017-03-15 09:51:18 4 -> /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/mesg/oraus.msb
lr-x------  1 oracle oinstall 64 2017-03-15 09:51:18 5 -> /proc/10077/fd
lr-x------  1 oracle oinstall 64 2017-03-15 09:51:18 6 -> /dev/zero
lrwx------  1 oracle oinstall 64 2017-03-15 09:51:18 7 -> /u01/app/oracle/admin/book/adump/bookdg_ora_10077_20170315091852338409143795.aud
l-wx------  1 oracle oinstall 64 2017-03-15 09:51:18 8 -> /u01/app/oracle/diag/rdbms/bookdg/bookdg/trace/bookdg_rfs_10077.trc
l-wx------  1 oracle oinstall 64 2017-03-15 09:51:18 9 -> /u01/app/oracle/diag/rdbms/bookdg/bookdg/trace/bookdg_rfs_10077.trm
--//可以发现RFS PID=10077,打开备用日志/data/ramdisk/book/redostb01.log的句柄.

5.总结:
--维护在线日志更改大小时注意同时备库备用日志的大小,一般最简单的方式生成新的备用控制文件.启用新的备用控制文件.
--最佳的方式保持在线与备用日志大小一致.

正文到此结束
所属分类： 编程技术

热门推荐

相关文章

阿里云首购8折
说给你听

本文目录
随机标签
书籍教程
springboot-demo Java入门教程 bootstrap3 CSS Apache基础教程 php ionic 教程 Python mysql教程 eclipse Ubuntu VPS系统配置 AngularJS 教程 MongoDB教程 Struts2教程 Redis教程 Spring教程 Git教程 Jenkins进阶系列 openfire参考指南 Java设计模式 HBase教程 Maven教程 hibernate教程 Docker 教程 memcached教程 Quartz指南 Hive教程 Ant教程 ANTLR教程 SpringCloud java实例教程 springcloud-demo XStream教程 Hazelcast教程 Elastic-Job-Lite 深入浅出MyBatis SVN教程 ibaties教程 rabittmq教程 solr教程 Hadoop教程 WebService CXF学习 JPA教程 ActiveMQ中文指南 java-demo Java内存模型 dubbo教程 python3-demo Linux入门视频教程
近期评论
  1. 1 Spring Cloud Consul实现选举机制
  2. 2 Spring Boot集成ShedLock实现分布式定时任务
  3. 3 利用oss进行数据库和网站图片备份
  4. 4 Spring Cloud Stream实现数据流处理
  5. 5 Python3访问MySQL数据库快速入门Demo
  6. 6 Github开源项目作者可以免费申请 JetBrains 全家桶
  7. 7 Spring Cloud Vault快速入门Demo
  8. 8 Spring Cloud Gateway快速入门Demo
  9. 9 Spring Cloud Contract快速入门Demo
  10. 10 Spring Cloud Consul快速入门Demo
网站信息
  • 文章总数：82,714 篇
  • 文件总数：284,287 个
  • 标签总数：2,393 个
  • 分类总数：85 个
  • 留言数量：2,560 条
  • 在线人数：660 人
  • 运行天数：4,409天
  • 最后更新：2024年11月22日09点
Loading...

其他链接

关于本站

本站定位：个人技术类博客

本站作用：写博客、记日志、闲聊扯淡鼓捣技术。

问题交流

[HBLOG]公众号 HBLOG HBLOG