ITPub博客

首页 > 数据库 > Oracle > [20181112]11g 日志传输压缩模式.txt

[20181112]11g 日志传输压缩模式.txt

原创 Oracle 作者:lfree 时间:2018-11-12 15:37:18 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2673
  • 访问量
    6432176