[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.

--//重啟主備庫.略.

[20181112]11g 日誌傳輸壓縮模式.txt

相關文章