Enabling Redo Log Transport Compression with active dataguard
0 racle 11 g release 1後にredoログ転送圧縮の機能を提供し、アーカイブログgapの問題を迅速に解決することができ、ASYNC/MaxPerformance redo transportモードで圧縮を有効にすることができ、11.2.0.1バージョン以降もMaxProtection、MaxAvailabilityモードに拡張することができる.本稿ではoracle 11 g active data guard環境におけるredo伝送の圧縮について説明する.redoログ転送圧縮はredo切替が速く、帯域幅が小さく、CPUが強いdata guard環境に適しています.
一:ログ転送の遅延状況を表示する
二:隠しパラメータの変更_REDO_TRANSPORT_COMPRESS_ALL、インスタンスの再起動
三:log_を修正するarchive_dest_2パラメータ
四:圧縮の検証
参考記事:http://oraclehandson.wordpress.com/2011/01/07/enabling-redo-log-transport-compression/(どうぞ×××アクセス!http://docs.oracle.com/cd/B28359_01/server.111/b28294/log_transport.htm#BABEBHHB https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=325669420448759&id=729551.1&_afrWindowMode=0&_adf.ctrl-state=7uubfwzms_4
一:ログ転送の遅延状況を表示する
- [root@db1 ~]# su - oracle
- [oracle@db1 ~]$ sqlplus /nolog
- SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 28 09:42:48 2012
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
-
- SQL> conn /as sysdba
- Connected.
-
- SQL> select protection_mode,protection_level,database_role from v$database;
-
- PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE
- -------------------- -------------------- ----------------
- MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PRIMARY
-
- SQL> SELECT max(DURATION) FROM V$REDO_DEST_RESP_HISTOGRAM WHERE DEST_ID=2 AND FREQUENCY>1;
-
- MAX(DURATION)
- -------------
- 7
-
- SQL> SELECT min(DURATION) FROM V$REDO_DEST_RESP_HISTOGRAM WHERE DEST_ID=2 AND FREQUENCY>1;
-
- MIN(DURATION)
- -------------
- 1
-
- SQL> SELECT FREQUENCY, DURATION FROM V$REDO_DEST_RESP_HISTOGRAM WHERE DEST_ID=2 AND FREQUENCY>1;
- FREQUENCY DURATION
- ---------- ----------
- 4018 1
- 13 2
- 11 3
- 11 4
- 3 5
- 2 6
- 2 7
- 7 rows selected.
二:隠しパラメータの変更_REDO_TRANSPORT_COMPRESS_ALL、インスタンスの再起動
- SQL> col parameter for a20
- SQL> col value for a20
-
- SQL> select * from v$option where parameter ='Advanced Compression';
-
- PARAMETER VALUE
- -------------------- --------------------
- Advanced Compression TRUE
-
- SQL> alter system set "_REDO_TRANSPORT_COMPRESS_ALL"=TRUE SCOPE=SPFILE;
- System altered.
-
- SQL> show parameter COMPRESS;
-
- SQL> shutdown immediate
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SQL> startup
- ORACLE instance started.
-
- Total System Global Area 1536602112 bytes
- Fixed Size 2228624 bytes
- Variable Size 1174408816 bytes
- Database Buffers 352321536 bytes
- Redo Buffers 7643136 bytes
- Database mounted.
- Database opened.
-
- SQL> show parameter COMPRESS;
-
- NAME TYPE VALUE
- ------------------------------------ ----------- ------
- _redo_transport_compress_all boolean TRUE
三:log_を修正するarchive_dest_2パラメータ
- SQL> show parameter log_archive_dest_2;
-
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- log_archive_dest_2 string SERVICE=db2 lgwr sync valid_fo
- r=(online_logfiles,primary_role
- ) db_unique_name=db2
-
- SQL> alter system set log_archive_dest_2='SERVICE=db2 lgwr sync valid_for=(online_logfiles,primary_role) compression=enable db_unique_name=db2';
- System altered.
-
- SQL> show parameter log_archive_dest_2
-
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- log_archive_dest_2 string SERVICE=db2 lgwr sync valid_fo
- r=(online_logfiles,primary_rol
- e) compression=enable db_uniqu
- e_name=db2
四:圧縮の検証
- SQL> select DEST_ID,COMPRESSION from v$archive_dest where dest_id=2;
-
- DEST_ID COMPRES
- ---------- -------
- 2 ENABLE
-
-
- SQL> archive log list;
- Database log mode Archive Mode
- Automatic archival Enabled
- Archive destination USE_DB_RECOVERY_FILE_DEST
- Oldest online log sequence 196
- Next log sequence to archive 198
- Current log sequence 198
-
- gzip 1 ， oracle ， 79%：
- [oracle@db1 2012_11_28]$ gzip -1 o1_mf_1_198_8cbvxpm1_.arc
- [oracle@db1 2012_11_28]$ gzip --list o1_mf_1_198_8cbvxpm1_.arc.gz
- compressed uncompressed ratio uncompressed_name
- 139090 671744 79.3% o1_mf_1_198_8cbvxpm1_.arc
参考記事:http://oraclehandson.wordpress.com/2011/01/07/enabling-redo-log-transport-compression/(どうぞ×××アクセス!http://docs.oracle.com/cd/B28359_01/server.111/b28294/log_transport.htm#BABEBHHB https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=325669420448759&id=729551.1&_afrWindowMode=0&_adf.ctrl-state=7uubfwzms_4