lfree的个人空间

暂无签名

  • 博客访问: 5244346
  • 博文数量: 1741
  • 用 户 组: 普通用户
  • 注册时间: 2008-01-03 08:21
个人简介

暂无介绍

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(1741)

文章存档

2017年(154)

2016年(306)

2015年(294)

2014年(203)

2013年(182)

2012年(173)

2011年(36)

2010年(11)

2009年(13)

2008年(27)

2007年(148)

2006年(152)

2005年(40)

2000年(2)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题

分类: Oracle

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

阅读(551) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册