转载

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教程 java-demo Maven教程 springcloud-demo hibernate教程 Docker 教程 memcached教程 Quartz指南 Hive教程 ANTLR教程 SpringCloud Ant教程 java实例教程 Hazelcast教程 XStream教程 Elastic-Job-Lite 深入浅出MyBatis ibaties教程 SVN教程 rabittmq教程 Hadoop教程 solr教程 WebService CXF学习 JPA教程 ActiveMQ中文指南 Java内存模型 dubbo教程 python3-demo Linux入门视频教程
近期评论
  1. 1 使用 Docker 打包和运行 Vue 应用
  2. 2 Dependency Check命令行方式扫描jar包的安全漏洞
  3. 3 maven使用Dependency-Check来扫描安全漏洞
  4. 4 使用sonarqube分析代码质量
  5. 5 使用screw来对比数据库表和字段差异
  6. 6 Spring Boot 实现防盗链功能
  7. 7 在Spring Boot中的实现国际化（i18n）
  8. 8 一个用于 SMB/CIFS 网络的 Java 库-JCIFS
  9. 9 Spring Boot 3.x嵌入MongoDB 进行测试
  10. 10 java如何解析和生成yaml文件？
网站信息
  • 文章总数：82,737 篇
  • 文件总数：284,312 个
  • 标签总数：2,416 个
  • 分类总数：85 个
  • 留言数量：2,559 条
  • 在线人数：630 人
  • 运行天数：4,439天
  • 最后更新：2024年12月22日19点
Loading...

其他链接

关于本站

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

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

问题交流

[HBLOG]公众号 HBLOG HBLOG