sfae_comp_adm - Compression Advisor for Oracle database files.

sfae_comp_adm -S ORACLE_SID -H ORACLE_HOME

1. Compress archived logs

-o compress_archive_logs [ -n N ] [ -d DIRECTORY ]

2. Compress, uncompress datafiles

-o { compress_datafiles | uncompress_datafiles }
DATAFILES [ DATAFILE2 ... ]
-f FILELIST
-t TIER

3. Display datafiles candidate for compression

-o display [ -n N ] [ -t TABLESPACE ]

4. Display datafiles have been compressed

-o display -c [ -t TABLESPACE ]

Veritas Storage Foundation.

To determine whether this product is installed on the system, enter:

 rpm -qa | grep VRTSdbed

SFAE Compression Advisor is the command line utility used to evaluate Oracle files and recommend files which can be compressed for storage savings. It will also provide a easy-to-use command to compress datafiles and archivelogs.

VxFS has introduced support for compression in the Pinnacle release. Individual files can be compressed using the vxcompress CLI transparently to applications. An application reading a compressed file will automatically get the uncompressed data without affecting the on-disk compression. If an application writes to a compressed file, parts of the file will be uncompressed on disk as necessary.

1. Compress archived logs

-o compress_archive_logs [ -n N ] [ -d DIRECTORY ]

The -o compress_archive_logs option will be used to compress archived logs. With no other options, this command will compress all archivelogs generated before the current date. With a -n N option, it will compress all archivelogs generated N days ago or older. If N is specified as 0, all archivelogs will be compressed. Not specifying the -n option is the same as specifying -n 1.

By default, sfae_comp_adm will attempt to compress archivelogs on all directories, if the database is configured to archive to multiple destinations. If only a specific directory must be compressed, the archivelog directory can be specified using the -d option.

2. Handling datafiles

2.1 Compress datafiles

-o compress_datafiles
DATAFILES [ DATAFILE2 ... ]
-f FILELIST
-t TIER

The -o compress_datafiles option will be used to compress datafiles, The datafiles to compress can be specified in one of two ways:

o	Compressing a single datafile: A single datafile will be specified by giving the filename as an argument to the sfae_comp_adm command.
o	Compressing several datafiles: Multiple datafiles will be specified by giving a filename with the -f option. The specified file will contain datafile names, with one filename on a line.

However the datafile is specified, the following datafiles will not be compressed:

o	Datafiles belonging to the SYSTEM tablespace will not be compressed.
o	Datafiles belonging to the SYSAUX tablespace will not be compressed.
o	Tempfiles will not be compressed.
o	Datafiles comprising undo tablespaces will not be compressed.
o	Files that are not datafiles for the given Oracle instance will not be compressed.
o	Files that are not on VxFS filesystems will not be compressed.

An attempt to compress files of any of these types will result in a warning.

If multiple filenames have been given to be compressed, and one of them is invalid, or the compression command fails, a warning is generated, and the process continues with the next filename.

If the -t TIER option is given to -o compress_datafiles, then the datafiles in the specified tier will be compressed.

2.2 Uncompress datafiles

The -o uncompress_datafiles option will be used to uncompress datafiles. The datafiles can be specified in the same way as used with -o compress_datafiles. All options in -o compress_datafiles are also applied for -o uncompress_datafiles.

3. Display datafiles that are candidate for compression

The -o display option will be used to display the top 10 datafiles of minimum I/O activites that might be good candidates for being compressed. The output of this command will be in a tabular format with the following columns:

o	Tablespace
o	Filename
o	Amount of data read (in bytes)
o	Amount of data written (in bytes)

The table will be sorted in increasing order of the sum of the values in the columns Amount of data read and Amount of data written, so files with minimum I/O activity are at the head of the table.

Files that we do not allow compression on from the 2.1 Compress Datafiles section will not be included in this report.

The -t TABLESPACE option can be used to restrict the output to t he specified tablespace. The default is to include all tablespaces, except the SYSTEM, SYSAUX and temporaries tablespaces.

The -n N option can be used to restrict the output to the given number of files. The default is to display a maximum of 10 files.

4. Display datafiles that have been compressed

The -o display option will also be used to display datafiles that have been compressed.

If the -c option is given, datafiles that are compressed will be displayed in a tabular form with the following columns:

o	Tablespace
o	Filename
o	File size
o	Compression Ratio

A final summary line will display the amount of space saving.

-S ORACLE_SID	ORACLE_SID is the SID of the Oracle database to be used.
-H ORACLE_HOME	ORACLE_HOME is the HOME of the Oracle instance to be used.
-n N	If used with -o compress_archive_logs option, the number of days that logs have been archived, considering value 0 as logs that are archived on current date. The default value is 1 ( logs were archived one day before the current date ). If used with -o display option, the limited number of files to be displayed in the report.
-f FILELIST	Use with -o compress_archive_logs option, the name of file containings archived logs name to be compressed. The archived logs name must be separated by newline ( only one archived log name on each line ).
-t TABLESPACE	Use with -o display, display datafiles belongs to specified tablespace that might be good candidates for compression, this excludes SYSTEM, SYSAUX and temporaries tablespace. The command gets information on datafiles based on AWR snapshots that Oracle provides.
-t TIER	Use with -o compress_datafiles or -o uncompress_datafiles, compress or uncompress only datafiles belongs to specified tier.
-c	Use with -o display option, display all the datafiles that have been compressed, If used with -t TABLESPACE, only datafiles belongs to specified tablespace will be displayed, this excluded SYSTEM, SYSAUX and temporaries tablespace.

1. To compress all archived logs older than current date

 $ sfae_comp_adm -S prod -H /orahome -o compress_archive_logs

2. To compress archived logs older than 7 days

 $ sfae_comp_adm -S prod -H /orahome -o compress_archive_logs -n 7

3. To compress archived logs in directory /arch/prod, on current date

 $ sfae_comp_adm -S prod -H /orahome -o compress_archive_logs -d /arch/prod -n 0

4. To compress datafiles ’/data/sales1.dbf’

 $ sfae_comp_adm -S prod -H /orahome -o compress_datafiles /data/sales1.dbf

5. To compress datafiles ’/data/sales2.dbf’, ’/data/sales3.dbf’

 $ sfae_comp_adm -S prod -H /orahome -o compress_datafiles /data/sales2.dbf /data/sales3.dbf

or
$ sfae_comp_adm -S prod -H /orahome -o compress_datafiles -f salesdatafiles

 $ cat salesdatafiles
 /data/sales2.dbf
 /data/sales3.dbf

6. To compress datafiles belong to tier ’FINANCE’

 $ sfae_comp_adm -S prod -H /orahome -o compress_datafiles -t FINANCE

7. To uncompress datafiles belong to tier ’FINANCE’

 $ sfae_comp_adm -S prod -H /orahome -o uncompress_datafiles -t FINANCE

8. To display the first 10 datafiles that might be good candidate for compression

 $ sfae_comp_adm -S prod -H /orahome -o display

9. To display the first 5 datafiles that might be good candidate for compression
but belongs to ’SALES’ tablespace only.

 $ sfae_comp_adm -S prod -H /orahome -o display -t sales -n 5

10. To display the report on storage saving and information on datafiles that
have been compressed

 $ sfae_comp_adm -S prod -H /orahome -o display -c

11. To display the report on storage saving and information on datafiles that
have been compressed but belongs to ’SALES’ tablespace only.

 $ sfae_comp_adm -S prod -H /orahome -o display -c -t sales

vxcompress(1M)