Configuring Transparent Data Encryption -- 1 : For a Tablespace

 Oracle allows TDE (Transparent Data Encryption) for specific (i.e. selected) columns or a full Tablespace.

(I have also presented the steps for TDE in SQL Server).

Here is a quick demo of TDE for a Tablespace.

First, I setup a target tablespace with some data

SQL> connect hemant/hemant
Connected.
SQL> create tablespace TDE_TARGET_TBS datafile '/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS.dbf' size 100M;

Tablespace created.

SQL>
SQL>  create table TDE_TARGET_TABLE
  2  (id_col number,
  3  data_col varchar2(50))
  4  tablespace TDE_TARGET_TBS
  5  /

Table created.

SQL> insert into TDE_TARGET_TABLE
  2  select rownum,
  3  'MY DATA CONTENT : ' || rownum
  4  from dual
  5  connect by level "less than" 1001   -- the "less than" symbol replaced by string to preserve formatting for HTML
  6  /

1000 rows created.

SQL> commit;

Commit complete.

SQL>


oracle19c>dd if=/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS.dbf ibs=8192 skip=128 > /tmp/dump_of_TDE_TARGET_TBS.TXT
12673+0 records in
202768+0 records out
103817216 bytes (104 MB) copied, 1.10239 s, 94.2 MB/s
oracle19c>
oracle19c>strings -a /tmp/dump_of_TDE_TARGET_TBS.TXT |grep 'CONTENT' |head -5
MY DATA CONTENT : 944,
MY DATA CONTENT : 945,
MY DATA CONTENT : 946,
MY DATA CONTENT : 947,
MY DATA CONTENT : 948,
oracle19c>strings -a /tmp/dump_of_TDE_TARGET_TBS.TXT |grep 'CONTENT' | wc -l
1000
oracle19c>

I inserted 1000 rows with the text "MY DATA CONTENT" and it is visible as plain-text when I dump the datafile.   

Note how not all the inserted rows appear to be in physical order -- the "last" 57 "records" (i.e. rows) seem to appear before the first "record" (row) as I show in this video recording of a viewing of the dump.  Never assume physical ordering of data in a datafile or when retrieving output (for ordering the results of a SELECT statement, *always* use the ORDER BY clause)

So, I now intend to encrypt the tablespace.

Step 1 : Specify the ENCRYPTION WALLET LOCATION

In earlier releases, this is specified in the sqlnet.ora file like this :

ENCRYPTION_WALLET_LOCATION=
  (SOURCE=
   (METHOD=FILE)
    (METHOD_DATA=
     (DIRECTORY=/home/oracle/wallet)))  -- or this could be any other folder, or defaulting to $ORACLE_BASE/admin/db_unique_name/wallet

However, in 19c, Oracle recommends using the KEYSTORE_CONFIGURATION attribute of the TDE_CONFIGURATION initialization parameter after setting the WALLET_ROOT.

SQL> show parameter tde

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
one_step_plugin_for_pdb_with_tde     boolean     FALSE
tde_configuration                    string
SQL> show parameter wallet

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ssl_wallet                           string
wallet_root                          string
SQL>
SQL> alter system set wallet_root='/opt/oracle/product/19c/dbhome_1/TDE_WALLETS' scope=SPFILE;

System altered.

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1207958960 bytes
Fixed Size                  8895920 bytes
Variable Size             318767104 bytes
Database Buffers          872415232 bytes
Redo Buffers                7880704 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter wallet

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ssl_wallet                           string
wallet_root                          string      /opt/oracle/product/19c/dbhome
                                                 _1/TDE_WALLETS
SQL>
SQL>  alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE' scope=SPFILE;

System altered.

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1207958960 bytes
Fixed Size                  8895920 bytes
Variable Size             318767104 bytes
Database Buffers          872415232 bytes
Redo Buffers                7880704 bytes
Database mounted.
Database opened.
SQL> show parameter wallet

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ssl_wallet                           string
wallet_root                          string      /opt/oracle/product/19c/dbhome
                                                 _1/TDE_WALLETS
SQL> show parameter tde

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
one_step_plugin_for_pdb_with_tde     boolean     FALSE
tde_configuration                    string      KEYSTORE_CONFIGURATION=FILE
SQL>

In the case of this database, since I had not earlier configured these parameters, I had to do a manual restart for them to take effect (Note : "WALLET_ROOT" must have been configured before "TDE_CONFIGURATION" can be set, that is why I had to do an additional restart between setting the two parameters).

I have deliberately configured WALLET_ROOT to a non-default/standard location.

Step 2 : Create the KEYSTORE (under WALLET_ROOT)

This is where I actually  create the Wallet.  The syntax specifies KEYSTORE location, but can default to WALLET_ROOT as I have already defined it. I can also create an Auto-Login Keystore

SQL> administer key management create keystore identified by mysecretpassword;

keystore altered.

SQL> !ls -l /opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde
total 4
-rw-------. 1 oracle oinstall 2555 Nov 21 18:38 ewallet.p12

SQL>
SQL> administer key management create LOCAL auto_login keystore
  2  from keystore '/opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde'
  3  identified by mysecretpassword
  4  /

keystore altered.

SQL> !ls -l /opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde
total 8
-rw-------. 1 oracle oinstall 2600 Nov 21 18:49 cwallet.sso
-rw-------. 1 oracle oinstall 2555 Nov 21 18:38 ewallet.p12

SQL>
SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
------------------------------------------------------------------------------------------------------------------------------------
STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
------------------------------ -------------------- --------- -------- --------- ----------
FILE
/opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde/
OPEN_NO_MASTER_KEY             LOCAL_AUTOLOGIN      SINGLE    NONE     UNDEFINED          0


SQL>

In this case, "ewallet.p12" is the Password Protected Keystore and "cwallet.sso" is the Auto-Login Keystore (created LOCALly only, not for remote servers/clients).

Step 3 : OPEN the Keystore  (only if it is NOT already OPEN) 

I can see that the Keystore is already OPEN (from the query on v$encryption_wallet) but I could attempt OPENing it with :

SQL> administer key management set keystore open
  2  identified by mysecretpassword
  3  /
administer key management set keystore open
*
ERROR at line 1:
ORA-28354: Encryption wallet, auto login wallet, or HSM is already open


SQL>
SQL> !oerr ora 28354
28354, 0000, "Encryption wallet, auto login wallet, or HSM is already open"
// *Cause:  Encryption wallet, auto login wallet, or HSM was already opened.
// *Action: None.
//

SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
------------------------------------------------------------------------------------------------------------------------------------
STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
------------------------------ -------------------- --------- -------- --------- ----------
FILE
/opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde/
OPEN_NO_MASTER_KEY             LOCAL_AUTOLOGIN      SINGLE    NONE     UNDEFINED          0


SQL>

Step 4 : Setup the Master Encryption Key

Now, I setup the Master Key (and also backup the existing key file)

SQL> administer key management set key
  2  using tag 'For_Tablespace_TDE'
  3  force keystore  -- because I am using LOCAL_AUTOLOGIN
  4  identified by mysecretpassword
  5  with backup using 'tde_key_backup'
  6  /

keystore altered.

SQL>
SQL> !ls -l /opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde
total 20
-rw-------. 1 oracle oinstall 4232 Nov 21 19:04 cwallet.sso
-rw-------. 1 oracle oinstall 2555 Nov 21 19:04 ewallet_2020112111043216_tde_key_backup.p12
-rw-------. 1 oracle oinstall 4171 Nov 21 19:04 ewallet.p12

SQL>
SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
------------------------------------------------------------------------------------------------------------------------------------
STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
------------------------------ -------------------- --------- -------- --------- ----------
FILE
/opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde/
OPEN                           LOCAL_AUTOLOGIN      SINGLE    NONE     NO                 0


SQL>
SQL> select key_id, creation_time, keystore_type, tag from v$encryption_keys;

KEY_ID
------------------------------------------------------------------------------
CREATION_TIME                                                               KEYSTORE_TYPE
--------------------------------------------------------------------------- -----------------
TAG
------------------------------------------------------------------------------------------------------------------------------------
AaHRyuP8yE+ivzI/hZHcOdoAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
21-NOV-20 07.04.32.347090 PM +08:00                                         SOFTWARE KEYSTORE
For_Tablespace_TDE


SQL>

Now I am ready the encrypt my Tablespace.

Step 5 :  Online Encryption of Tablespace

This is a 19c feature, earlier versions required Offline Encryption.

This method creates a new datafile with encrypted data

SQL> alter tablespace TDE_TARGET_TBS
  2  encryption online
  3  using 'AES192'
  4  encrypt file_name_convert = ('/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS.dbf','/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf')
  5  /

Tablespace altered.

SQL>

oracle19c>pwd
/opt/oracle/oradata/HEMANT
oracle19c>ls -l TDE*
-rw-r-----. 1 oracle oinstall 104865792 Nov 21 19:42 TDE_TARGET_TBS_encrypted.dbf
oracle19c>
oracle19c>dd if=/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf ibs=8192 skip=128 > /tmp/dump_of_TDE_TARGET_TBS_encrypted.TXT
12673+0 records in
202768+0 records out
103817216 bytes (104 MB) copied, 0.414517 s, 250 MB/s
oracle19c>
oracle19c>strings -a /tmp/dump_of_TDE_TARGET_TBS_encrypted.TXT |grep 'CONTENT' |head -5
oracle19c>

SQL> l
  1  select t.name, e.ts#, e.encryptionalg, e.encryptedts, e.key_version, e.status
  2  from v$tablespace t, v$encrypted_tablespaces e
  3* where t.ts#=e.ts#
SQL> /

NAME                                  TS# ENCRYPT ENC KEY_VERSION STATUS
------------------------------ ---------- ------- --- ----------- ----------
TDE_TARGET_TBS                          8 AES192  YES           1 NORMAL

SQL>

Oracle has replaced the TDE_TARGET_TBS.dbf file with TDE_TARGET_TBS_encrypted.dbf file. The new file does NOT have any plain-text values "CONTENT"

For subsequent Tablespaces, Steps 1 to 4 would not be required.

Active Data Guard and DML Redirection

 Active Data Guard (also known as "ADG") allows you to open a Standby Database in Read Only mode and query it while Media Recovery (i.e. Redo Apply from the Primary) is concurrently running.

Caveat : ADG requires purchase of additional licences to use this feature on a Standby Database.

If you issue the commands from sqlplus and not from dgmrl, you must first stop Media Recovery before you OPEN the database and then re-enable it on the Standby :

SQL> select FORCE_LOGGING, FLASHBACK_ON,DATABASE_ROLE,PROTECTION_MODE, PROTECTION_LEVEL, OPEN_MODE
  2  from v$database;

FORCE_LOGGING                           FLASHBACK_ON       DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
--------------------------------------- ------------------ ---------------- -------------------- --------------------
OPEN_MODE
--------------------
YES                                     NO                 PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE
MOUNTED


SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in progress


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select FORCE_LOGGING, FLASHBACK_ON,DATABASE_ROLE,PROTECTION_MODE, PROTECTION_LEVEL, OPEN_MODE
  2  from v$database;

FORCE_LOGGING                           FLASHBACK_ON       DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
--------------------------------------- ------------------ ---------------- -------------------- --------------------
OPEN_MODE
--------------------
YES                                     NO                 PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE
READ ONLY


SQL>
SQL> alter pluggable database orclpdb1 open read only;

Pluggable database altered.

SQL>
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>

Thereafter, you would be able to query the Standby Database.  DML (INSERT, UPDATE, DELETE) and DDL (CREATE, DROP, ALTER etc) are *not* allowed by default.  

19c does have a DML Redirection feature ADG_REDIRECT_DML which I demonstrate below :

SQL> connect hemant/hemant@stdbypdb1
Connected.
SQL> select * from x;

D
-
X

SQL> delete x;
delete x
       *
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access


SQL> ALTER SESSION ENABLE ADG_REDIRECT_DML;

Session altered.

SQL> delete x;

1 row deleted.

SQL> commit;

Commit complete.

SQL>

The actual execution of the DML will take some time as Oracle has to actually push it to the Primary database for execution and then wait for the Redo Apply to replicate back to the Standby.

PLSQL also can be executed with ADG_REDIRECT_PLSQL     which I am not demonstrating here.

Note :  According to Oracle's documentation the DML Redirect feature is for "read-mostly applications, which occasionally execute DMLs, on the standby database."    .  I wouldn't advise trying this for all sessions at the Instance ("alter system") level but only to be used occasionally at session level.

Compressing VARCHAR2 and CHAR column data

 Most applications that I see use VARCHAR2 columns.  However, there are some (including many built by "in house database designers" that use CHAR columns).  CHAR columns are for Fixed-Length character strings.  Any inserted values that are shorter than the defined length are right-padded with blanks.  Comparison semantics are also different in that VARCHAR2 uses non-padded comparison while CHAR uses blank-padding for comparison.

In this demonstration, I'd like to focus on compressibility of columns defined as VARCHAR2 and CHAR.

These scripts are executed in 19c on Linux.

First with VARCHAR2

SQL> create tablespace VC2_TBS datafile '/opt/oracle/oradata/HEMANT/VC2_TBS.dbf' size 2G;

Tablespace created.

SQL>
SQL> create table VC2_TABLE
  2  (id_col number(8), data_col_1 varchar2(50), data_col_2 varchar2(50), data_col_3 varchar2(50))
  3  pctfree 0
  4  tablespace VC2_TBS
  5  /

Table created.

SQL>
SQL> declare
  2  l_c number;
  3  begin
  4  for l_c in 1 .. 100
  5  loop
  6  insert     into VC2_TABLE
  7  select rownum, dbms_random.string('X',25), dbms_random.string('X',15), dbms_random.string('X',10)
  8  from dual
  9  connect by level "less than sign" 100001;  -- "less than sign" replaced by words to preserve HTML formatting
 10  commit;
 11  end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_table_stats('','VC2_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL>
SQL> select compression, compress_for, num_rows, blocks, avg_row_len
  2  from user_tables
  3  where table_name = 'VC2_TABLE'
  4  /

COMPRESS COMPRESS_FOR                       NUM_ROWS       BLOCKS AVG_ROW_LEN
-------- ------------------------------ ------------ ------------ -----------
DISABLED                                  10,000,000       78,437          58

SQL>
SQL> select bytes/1048576
  2  from user_segments
  3  where segment_name = 'VC2_TABLE'
  4  /

BYTES/1048576
-------------
          616

SQL>
SQL> pause For RMAN BACKUP AS COMPRESSED BACKUPSET
For RMAN BACKUP AS COMPRESSED BACKUPSET

RMAN> backup as compressed backupset tablespace VC2_TBS;

Starting backup at 08-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/opt/oracle/oradata/HEMANT/VC2_TBS.dbf
channel ORA_DISK_1: starting piece 1 at 08-NOV-20
channel ORA_DISK_1: finished piece 1 at 08-NOV-20
piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0svf1p8h_1_1 tag=TAG20201108T221753 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 08-NOV-20


oracle19c>ls -l /opt/oracle/product/19c/dbhome_1/dbs/0svf1p8h_1_1
-rw-r-----. 1 oracle oinstall 386490368 Nov  8 22:18 /opt/oracle/product/19c/dbhome_1/dbs/0svf1p8h_1_1
oracle19c>
-- approx 369MB  RMAN Compressed Backup

SQL>
SQL> alter table VC2_TABLE
  2  move compress
  3  /

Table altered.

SQL>
SQL> exec dbms_stats.gather_table_stats('','VC2_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL>
SQL> select compression, compress_for, num_rows, blocks, avg_row_len
  2  from user_tables
  3  where table_name = 'VC2_TABLE'
  4  /

COMPRESS COMPRESS_FOR                       NUM_ROWS       BLOCKS AVG_ROW_LEN
-------- ------------------------------ ------------ ------------ -----------
ENABLED  BASIC                            10,000,000       79,027          58

SQL>
SQL> select bytes/1048576
  2  from user_segments
  3  where segment_name = 'VC2_TABLE'
  4  /

BYTES/1048576
-------------
          624

SQL>

So these are the facts for the table with 3 VARCHAR2(50) columns with random 25, 15 and 10 character-strings. 

Note : I deliberately chose dbms_random.string to ensure that I'd get very few (if any) repeatable values that are compressible themselves.

10million rows of Average Row Length of 58bytes

616MB Segment (78,437 blocks equal 613MB approx).

RMAN Compressed Size of the Datafile 369MB (approx 60% of the Segment Size)

BASIC Compression Size of the Table :  624MB  (i.e. no compression achieved because I used random strings)

Next with CHAR

SQL> create tablespace CHAR_TBS datafile '/opt/oracle/oradata/HEMANT/CHAR_TBS.dbf' size 2G;  -- subsequently resized to 4000M

Tablespace created.

SQL>
SQL> create table CHAR_TABLE
  2  (id_col number(8), data_col_1 char(50), data_col_2 char(50), data_col_3 char(50))
  3  pctfree 0
  4  tablespace CHAR_TBS
  5  /

Table created.

SQL>
SQL> declare
  2  l_c number;
  3  begin
  4  for l_c in 1 .. 100
  5  loop
  6  insert     into CHAR_TABLE
  7  select rownum, dbms_random.string('X',25), dbms_random.string('X',15), dbms_random.string('X',10)
  8  from dual
  9  connect by level "less than" 100001;  -- "less than sign" replaced by words to preserve HTML formatting
 10  commit;
 11  end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_table_stats('','CHAR_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL>
SQL> select compression, compress_for, num_rows, blocks, avg_row_len
  2  from user_tables
  3  where table_name = 'CHAR_TABLE'
  4  /

COMPRESS COMPRESS_FOR                       NUM_ROWS       BLOCKS AVG_ROW_LEN
-------- ------------------------------ ------------ ------------ -----------
DISABLED                                  10,000,000      204,116         158

SQL>
SQL> select bytes/1048576
  2  from user_segments
  3  where segment_name = 'CHAR_TABLE'
  4  /

BYTES/1048576
-------------
         1600

SQL>
SQL> pause For RMAN BACKUP AS COMPRESSED BACKUPSET
For RMAN BACKUP AS COMPRESSED BACKUPSET


RMAN> backup as compressed backupset tablespace CHAR_TBS;

Starting backup at 08-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/opt/oracle/oradata/HEMANT/CHAR_TBS.dbf
channel ORA_DISK_1: starting piece 1 at 08-NOV-20
channel ORA_DISK_1: finished piece 1 at 08-NOV-20
piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0vvf1r7f_1_1 tag=TAG20201108T225127 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 08-NOV-20


oracle19c>ls -l /opt/oracle/product/19c/dbhome_1/dbs/0vvf1r7f_1_1
-rw-r-----. 1 oracle oinstall 441876480 Nov  8 22:51 /opt/oracle/product/19c/dbhome_1/dbs/0vvf1r7f_1_1
oracle19c>
-- appox 421MB  RMAN Compressed Backup

SQL>
SQL> alter table CHAR_TABLE
  2  move compress
  3  /

Table altered.

SQL>
SQL> exec dbms_stats.gather_table_stats('','CHAR_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL>
SQL> select compression, compress_for, num_rows, blocks, avg_row_len
  2  from user_tables
  3  where table_name = 'CHAR_TABLE'
  4  /

COMPRESS COMPRESS_FOR                       NUM_ROWS       BLOCKS AVG_ROW_LEN
-------- ------------------------------ ------------ ------------ -----------
ENABLED  BASIC                            10,000,000      204,766         158

SQL>
SQL> select bytes/1048576
  2  from user_segments
  3  where segment_name = 'CHAR_TABLE'
  4  /

BYTES/1048576
-------------
         1600

SQL>

So these are the facts for the table with 3 CHAR(50) columns with random 25, 15 and 10 character-strings. 

 Note : I deliberately chose dbms_random.string to ensure that I'd get very few (if any) repeatable values that are compressible themselves. 

10million rows of Average Row Length of 158bytes (up from 58 bytes for the VARCHAR2 columns) 

1600MB Segment (204,116 blocks equal 1595MB approx). 

RMAN Compressed Size of the Datafile 421MB  (approx 26% of the Segment Size)

BASIC Compression Size of the Table : 1600MB (i.e. no compression achieved, because I used random strings ? -- shouldn't the right-padded blanks be compressed ?) 

 So, in this test comparing random strings in VARCHAR2 and CHAR, quite obviously the table with CHAR columns took up much more space.

BASIC Compression didn't achieve anything (again : because I used random strings?)

RMAN default compression ("CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ") achieved better compression with CHAR

Of course, since I used random strings and you might have actual data that is compressible and the length of the actual strings inserted into the CHAR columns may be different in your production / test table, you would see different levels of compression achieved.

The point is that compression success depends not just on the type of data but can be different whether you use BASIC compression in the tablespace or the default BASIC in RMAN.

Note :  Given RMAN's default behaviour of "unused block compression" do not expect RMAN to have to backup all the blocks in entire 2GB or 4000MB datafile  -- particularly because I specifically create the tablespace just before the test and it doesn't have any other objects (segments).

An SQL Server Blog

 As I have recently started working with SQL Server, I have created a new SQL Server Blog.

I will be periodically updating both my Oracle and SQL Server blogs.