[20181112]11g 日志传输压缩模式.txt
--//由于主备库不在一个局域网内,一样采用级联模式,先传输日志到一台主机,然后在由这台备库传输日志到远端的dataguard备库.
--//这样一定程度减轻主库负担,另外加快日志应用,这样影响小一点,另外一种方式日志传输模式采用压缩模式.以前自己也测试过.
--//链接:http://blog.itpub.net/267265/viewspace-1143480/
--//工作需要,重复验证看看.
1.环境:
SCOTT@book> @ 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
SCOTT@book> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------ ------ -----------------------------------------------------------------------------------------------
log_archive_dest_2 string SERVICE=bookdg LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bookdg
SCOTT@book> alter system set log_archive_dest_2='SERVICE=bookdg LGWR ASYNC AFFIRM compression=enable VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bookdg';
System altered.
--//这样就ok了.
2.如何验证日志传输是压缩的呢?
--//首先在主备库打开归档跟踪:
--//主库:
SCOTT@book> alter system set log_archive_trace=16;
ystem altered.
--//备库:
SCOTT@bookdg> alter system set log_archive_trace=16;
System altered.
SYS@book> alter system archive log current;
System altered.
$ ps -ef | egrep "ns[a]|UI[D]"
UID PID PPID C STIME TTY TIME CMD
oracle 44236 1 0 09:05 ? 00:00:24 ora_nsa2_book
--//nsa进程号44236
$ ls -l *44236*.trc
-rw-r----- 1 oracle oinstall 46407479 2018-11-12 15:10:24 book_nsa2_44236.trc
--//发现log_archive_trace=16;记录信息有点多.
SYS@book> alter system set log_archive_trace=1;
System altered.
$ tail -f book_nsa2_44236.trc
*** 2018-11-12 15:11:36.093
*** 2018-11-12 15:11:36.093 1274 krsu.c
RFS message number 23059 (RFSWRITE)
RTC: Compression ratio for this I/O is 81%
*** 2018-11-12 15:11:37.094
*** 2018-11-12 15:11:37.094 1274 krsu.c
RFS message number 23060 (RFSWRITE)
RTC: Compression ratio for this I/O is 82%
*** 2018-11-12 15:11:38.095
*** 2018-11-12 15:11:38.095 1274 krsu.c
RFS message number 23061 (RFSWRITE)
RTC: Compression ratio for this I/O is 80%
*** 2018-11-12 15:11:39.096
*** 2018-11-12 15:11:39.096 1274 krsu.c
RFS message number 23062 (RFSWRITE)
RTC: Compression ratio for this I/O is 81%
$ grep "RTC:" book_nsa2_44236.trc | sort|uniq -c
1 RTC: Compression ratio for this I/O is 77%
5 RTC: Compression ratio for this I/O is 79%
16 RTC: Compression ratio for this I/O is 80%
50 RTC: Compression ratio for this I/O is 81%
26 RTC: Compression ratio for this I/O is 82%
3 RTC: Compression ratio for this I/O is 83%
1 RTC: Compression ratio for this I/O is 85%
--//可以发现压缩率非常高.产生一些事务看看:
SCOTT@book> create table t as select * from dba_objects ;
Table created.
SCOTT@book> update t set OBJECT_NAME=lower(OBJECT_NAME);
86993 rows updated.
SCOTT@book> commit ;
Commit complete.
$ grep "RTC:" book_nsa2_44236.trc | sort|uniq -c
1 RTC: Compression ratio for this I/O is 46%
1 RTC: Compression ratio for this I/O is 48%
1 RTC: Compression ratio for this I/O is 50%
4 RTC: Compression ratio for this I/O is 55%
1 RTC: Compression ratio for this I/O is 56%
1 RTC: Compression ratio for this I/O is 57%
3 RTC: Compression ratio for this I/O is 58%
1 RTC: Compression ratio for this I/O is 59%
1 RTC: Compression ratio for this I/O is 63%
1 RTC: Compression ratio for this I/O is 64%
2 RTC: Compression ratio for this I/O is 68%
1 RTC: Compression ratio for this I/O is 69%
4 RTC: Compression ratio for this I/O is 72%
3 RTC: Compression ratio for this I/O is 74%
1 RTC: Compression ratio for this I/O is 75%
5 RTC: Compression ratio for this I/O is 76%
5 RTC: Compression ratio for this I/O is 77%
6 RTC: Compression ratio for this I/O is 78%
20 RTC: Compression ratio for this I/O is 79%
66 RTC: Compression ratio for this I/O is 80%
99 RTC: Compression ratio for this I/O is 81%
50 RTC: Compression ratio for this I/O is 82%
19 RTC: Compression ratio for this I/O is 83%
21 RTC: Compression ratio for this I/O is 84%
4 RTC: Compression ratio for this I/O is 85%
--//压缩率也能达到50%以上.
3.在备库观察:
SYS@bookdg> @ &r/dg/dg
PROCESS PID STATUS CLIENT_P GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------- ------- ---------- ---------- ---------- ----------
RFS 28207 IDLE ARCH N/A 0 0 0 0 0
ARCH 28364 CONNECTED ARCH N/A 0 0 0 0 0
RFS 28205 IDLE LGWR 1 1 731 251 1 0
ARCH 28362 CLOSING ARCH 4 1 727 1 513 0
ARCH 28360 CLOSING ARCH 5 1 728 73728 1902 0
ARCH 28366 CLOSING ARCH 5 1 730 88064 1750 0
MRP0 28162 APPLYING_LOG N/A N/A 1 731 251 102400 0
7 rows selected.
--//可以确定接收的进程当前是28205. 备库:
$ grep RTC: bookdg_rfs_28205.trc | less
...
RTC: decompressed 232440 bytes to 1048576 bytes
RTC: decompressed 785 bytes to 3072 bytes
RTC: decompressed 222314 bytes to 1048576 bytes
RTC: decompressed 1396 bytes to 5120 bytes
RTC: decompressed 217892 bytes to 1048576 bytes
RTC: decompressed 1564 bytes to 5632 bytes
RTC: decompressed 223295 bytes to 1048576 bytes
RTC: decompressed 115 bytes to 512 bytes
RTC: decompressed 189832 bytes to 1048576 bytes
RTC: decompressed 2941 bytes to 14848 bytes
RTC: decompressed 192267 bytes to 1048576 bytes
RTC: decompressed 610 bytes to 2560 bytes
RTC: decompressed 192315 bytes to 1048576 bytes
RTC: decompressed 531 bytes to 2048 bytes
RTC: decompressed 191932 bytes to 1048576 bytes
RTC: decompressed 597 bytes to 2560 bytes
RTC: decompressed 193008 bytes to 1048576 bytes
RTC: decompressed 655 bytes to 2560 bytes
RTC: decompressed 5732 bytes to 28160 bytes
RTC: decompressed 194474 bytes to 1048576 bytes
RTC: decompressed 591 bytes to 2560 bytes
RTC: decompressed 174682 bytes to 1048576 bytes
RTC: decompressed 505 bytes to 2560 bytes
RTC: decompressed 4911 bytes to 27648 bytes
RTC: decompressed 159418 bytes to 1048576 bytes
RTC: decompressed 249 bytes to 1024 bytes
RTC: decompressed 4773 bytes to 27648 bytes
RTC: decompressed 162834 bytes to 1048576 bytes
RTC: decompressed 532 bytes to 2560 bytes
RTC: decompressed 162426 bytes to 1048576 bytes
RTC: decompressed 566 bytes to 2560 bytes
RTC: decompressed 4941 bytes to 28160 bytes
RTC: decompressed 160794 bytes to 1048576 bytes
RTC: decompressed 279 bytes to 1024 bytes
RTC: decompressed 5578 bytes to 32256 bytes
RTC: decompressed 163626 bytes to 1048576 bytes
RTC: decompressed 561 bytes to 2560 bytes
RTC: decompressed 4761 bytes to 27136 bytes
RTC: decompressed 159520 bytes to 1048576 bytes
RTC: decompressed 559 bytes to 2560 bytes
RTC: decompressed 4918 bytes to 28672 bytes
RTC: decompressed 160996 bytes to 1048576 bytes
RTC: decompressed 281 bytes to 1024 bytes
RTC: decompressed 159494 bytes to 1048576 bytes
RTC: decompressed 160961 bytes to 1048576 bytes
RTC: decompressed 13209 bytes to 84992 bytes
RTC: decompressed 159411 bytes to 1048576 bytes
RTC: decompressed 154267 bytes to 836608 bytes
RTC: decompressed 205968 bytes to 1048576 bytes
RTC: decompressed 192858 bytes to 1048576 bytes
RTC: decompressed 201346 bytes to 1048576 bytes
RTC: decompressed 15110 bytes to 78336 bytes
RTC: decompressed 202438 bytes to 1048576 bytes
RTC: decompressed 204217 bytes to 1048576 bytes
--//从解压看,压缩比率还是蛮大的
4.RTC表示什么,猜测一下:
--//RTC应该表示redo transport compression
5.收尾:
SCOTT@book> alter system reset log_archive_trace;
System altered.
SYS@bookdg> alter system reset log_archive_trace;
System altered.
SYS@book> alter system set log_archive_dest_2='SERVICE=bookdg LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bookdg';
System altered.
--//重启主备库.略.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2219634/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2219634/
204
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
