Sunday, November 26, 2006

Archivelog compression?

Apparently there is a command which could generate compressed archivelogs which goes something like this.

SQL> ALTER DATABASE ARCHIVELOG COMPRESS enable;

Database altered.

I have skimmed through the documentation but can't seem to find a reference to the same but the view v$archived_log seems to have a column called compressed which from the docs is

COMPRESSED VARCHAR2(3) This column is reserved for internal use only

I am assuming this is related to the underscore parameter _log_archive_compress_enable the description of which is below.
But changing the value of this parameter has not prevented the command from
working.

SQL> REM Formatting columns
SQL> set lines 9999
SQL> col indx format 9999
SQL> col inst_id heading "INST" format 9999
SQL> col ksppinm heading "NAME" format a30
SQL> col ksppdesc heading "DESC" format a60
SQL> col ksppstvl heading "CURR VAL" format a15
SQL> col ksppstdf heading "DEFAULT VAL" format a15

SQL> select v.indx,v.inst_id,ksppinm,ksppstvl,ksppstdf,ksppdesc from x$ksppi i ,
x$ksppcv v where i.indx=v.indx and ksppinm like '_log_archive_compress_enable';

INDX INST NAME CURR VAL DEFAULT VAL DESC
----- ----- ------------------------------ --------------- --------------- -------------------------
473 1 _log_archive_compress_enable FALSE TRUE Bypass database rules for enabling archiv

1 row selected.

So by default archivelogs are not compressed.
SQL> select distinct compressed from v$archived_log;

COM
---
NO

So I used this command, generated a few archivelogs and got the following message in one of the trace files.

Archivelog compression complete.
Input: 3139072 bytes Output: 810830 bytes
Compression Performance: 74.17 percent or 2.07 bits per byte

Lets verify the same.
SQL> alter database archivelog compress disable;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> create table scott.obj$ as select * from dba_objects;

Table created.

SQL> alter system switch logfile;

System altered.


SQL> alter database archivelog compress enable;

Database altered.


SQL> create table frego.obj$ as select * from dba_objects;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> select sequence#,blocks,compressed from v$archived_log where sequence#>225;

SEQUENCE# BLOCKS COM
---------- ---------- ---
226 3820 NO
227 1 NO
228 11579 NO
229 83 YES
230 8 YES
231 1911 YES

6 rows selected.

If you compare the 2 logs of interest (228 and 231 since there were other commands in the interim) you find a compression performance similar to the one indicated above.

It would be wise to check if I can recover using compressed archivelogs though.

P.S This is an undocumented feature (AFAIK) and hence I do not advise anyone to use the same in a Production environment.

4 comments:

Kevin Closson said...

Have you tried it with ASM?

Also, if it can be set with the alter database command (as opposed to _ init.ora), wouldn't that be implicitly supported?

Good post, BTW!

Fairlie Rego said...

Thanks Kevin.

Tested the command with ASM today. It works just as in the case of other filesystems.

What I meant to say was that the command is not documented in the SQL Reference or anywhere else.

Anurag said...

BTW: Metalink Bug: 3464260 mentions this also.

ALTER DATABASE ARCHIVELOG COMPRESS ENABLE/DISABLE IS MISSING IN 10G DOCS.

Supposedly it was documented in the beta doco .. but taken out in the final documentation.

Anonymous said...

I was foolish enough to enable this on a production system. It ran fine for about four days, and then started dumping trace files, about 3 a minute. It dumped 54 GB of trace files in two days. Here is the important error:

ORA-00600: internal error code, arguments: [1880], [0xDD044E0], [0xDD04440], [1], [0], [], [], []

The Oracle engineer stated that Compressed Archive Logging is only partially implemented in 10, and wasn't expected until 11 or even 12. Although he specializes in ORA-00600 errors, he had to go back to source to see what the 1880 parameter meant.