0racle 11g release 1后提供了redo 日志传输压缩的功能,可以快速的解决归档日志gap的问题,可以在ASYNC/MaxPerformance redo transport模式下启用压缩,11.2.0.1版本以后也可以扩展到MaxProtection, MaxAvailability模式;本文介绍oracle 11g active data guard环境下(MAXIMUM AVAILABILITY)对redo传输的压缩; redo 日志传输压缩适用于redo切换快,带宽小且CPU强劲的data guard环境下!
一:查看日志传输的延时情况
- [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