Oracle Advanced Compression

Oracle Advanced Compression provides a comprehensive set of compression capabilities to help improve performance and reduce storage costs. It allows organizations to reduce their overall database storage footprint by enabling compression for all types of data: relational (table), unstructured (file), network, Data Guard Redo and backup data.

Let's try it:

- check component status

select * from v$option where parameter='Advanced Compression'; PARAMETER                     VALUE ------------------------------                 ---------- Advanced Compression               TRUE

- prepare test table

create table test_compress as select * from dba_objects; begin for i in 1..5 loop insert into test_compress select * from dba_objects; end loop; end; / SQL> select count(*) from test_compress; COUNT(*) ---------- 522642

- check tabel status

select SEGMENT_NAME, sum(EXTENT_ID) EXTENTS,sum(BLOCKS) BLOCKS, sum(BYTES)/1024/1024 MB from dba_extents where SEGMENT_NAME='TEST_COMPRESS' group by SEGMENT_NAME; SEGMENT_NAME            EXTENTS     BLOCKS      MB --------------------                            ----------              ----------              ---------- TEST_COMPRESS           2775         7680             60 select TABLE_NAME,COMPRESSION,COMPRESS_FOR from dba_tables where TABLE_NAME='TEST_COMPRESS'; TABLE_NAME                     COMPRESS        COMPRESS_FOR ------------------------------                 --------                ------------ TEST_COMPRESS                DISABLED

- use "basic" compress

create table basic_compress compress as select * from test_compress ; commit; select SEGMENT_NAME, sum(EXTENT_ID) EXTENTS,sum(BLOCKS) BLOCKS, sum(BYTES)/1024/1024 MB from dba_extents where SEGMENT_NAME like ('%COMPRESS') group by SEGMENT_NAME; SEGMENT_NAME            EXTENTS     BLOCKS      MB --------------------                            ----------              ----------              ---------- BASIC_COMPRESS              528            2304             18 TEST_COMPRESS              2775       7680         60 select TABLE_NAME,COMPRESSION,COMPRESS_FOR from dba_tables where TABLE_NAME like ('%_COMPRESS'); TABLE_NAME                     COMPRESS        COMPRESS_FOR ------------------------------                 --------                ------------ TEST_COMPRESS                DISABLED BASIC_COMPRESS               ENABLED  BASIC

We can see that after the basic compress the table size only 18 MB.

       

Let’s try compression in RMAN

The compression levels are BASIC, LOW, MEDIUM and HIGH and each affords a tradeoff related to backup through put and the degree of compression afforded.

To use this option, we can run the following RMAN commands

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET; CONFIGURE BACKUP OPTIMIZATION OFF;

set compression algorithm 'MEDIUM'; run { sql 'alter system archive log current'; allocate channel ch1 device type disk format '/tmp/medium/bk_test_%U'; backup database plus archivelog;}

Backup size:     577M   /tmp/medium

set compression algorithm 'HIGH'; run { sql 'alter system archive log current'; allocate channel ch1 device type disk format '/tmp/high/bk_test_%U'; backup database plus archivelog;}

Backup size:     415M   /tmp/high

* Please noted that to use of LOW, MEDIUM and HIGH requires the Advanced Compression license.

Transparent Data Encryption

Transparent Data Encryption

Reference: http://docs.oracle.com/

Oracle Database uses authentication, authorization, and auditing mechanisms to secure data in the database, but not in the operating system data files where data is stored. To protect these data files, Oracle Database provides Transparent Data Encryption (TDE). TDE encrypts sensitive data stored in data files. To prevent unauthorized decryption, TDE stores the encryption keys in a security module external to the database.

Database users and applications do not need to manage key storage or create auxiliary tables, views, and triggers. An application that processes sensitive data can use TDE to provide strong data encryption with little or no change to the application.

Use TDE to protect confidential data, such as credit card and social security numbers, stored in table columns. You can also use TDE to encrypt entire tablespaces.

Transparent Data Encryption (TDE) has the following advantages:

-       As a security administrator, you can be sure that sensitive data is safe in case the storage media or data file gets stolen.

-       Implementing TDE helps you address security-related regulatory compliance issues.

-       You do not need to create triggers or views to decrypt data for the authorized user or application. Data from tables is transparently decrypted for the database user and application.

-       Database users and applications need not be aware of the fact that the data they are accessing is stored in encrypted form. Data is transparently decrypted for the database users and applications.

-       Applications need not be modified to handle encrypted data. Data encryption and decryption is managed by the database.

-       Key management operations are automated. The user or application does not need to manage encryption keys.

TDE Column Encryption:
TDE column encryption is used to protect confidential data, such as credit card and social security numbers, stored in table columns.

TDE Tablespace Encryption:
TDE tablespace encryption enables you to encrypt an entire tablespace. All objects created in the encrypted tablespace are automatically encrypted. TDE tablespace encryption is useful if you want to secure sensitive data in tables. You do not need to perform a granular analysis of each table column to determine the columns that need encryption.

Enabling Transparent Data Encryption

TDE column encryption was first introduced in Oracle Database 10g release 2 (10.2). To use this feature, you must be running Oracle Database 10g release 2 (10.2) or higher.

TDE tablespace encryption was introduced in Oracle Database 11g release 1 (11.1). To use this feature, you must be running Oracle Database 11g release 1 (11.1) or higher.

To start using TDE, the security administrator must create a wallet and set a master key. The wallet can be the default database wallet shared with other Oracle Database components, or a separate wallet specifically used by TDE. Oracle strongly recommends that you use a separate wallet to store the master encryption key.

A simple way to use wallet

1.     Setting Wallet directory is simple, modify sqlnet.ora and add strings as below example:

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD=FILE)                (METHOD_DATA=                     (DIRECTORY=/$ORACLE_BASE/admin/labtest2/wallet)))

2.     Setting the Master Encryption Key

Before you can encrypt or decrypt database columns or tablespaces, you must generate a master encryption key. Oracle Database 11g Release 2 (11.2) uses the same master encryption key for both TDE column encryption and TDE tablespace encryption.

To set the master encryption key, use the following command:

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "password";

3.     Opening and Closing the Encrypted Wallet

The database must load the master encryption key into memory before it can encrypt or decrypt columns/tablespaces.

Use the following ALTER SYSTEM command to explicitly open the wallet:

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "password";

Enclose the password string in double quotation marks (" ").

Once the wallet has been opened, it remains open until you shut down the database instance, or close it explicitly by issuing the following command:

SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "password";

Closing the wallet disables all encryption and decryption operations. Any attempt to encrypt/decrypt data or access encrypted data results in the following error:

ORA-28365: wallet is not open

Each time you restart a database instance, you must issue the ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "password" command to reenable encryption and decryption operations.

Note:

Auto login wallets are opened automatically and do not need to be opened explicitly.

In case an auto login wallet needs to be closed, it can be closed with the following command:

SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE

No password is required to close an auto login wallet.

Creating a Table with an Encrypted Column

By default, TDE uses the AES encryption algorithm with a 192-bit key length (AES192). If you encrypt a table column without specifying an algorithm, the column is encrypted using the AES192 algorithm.

TDE adds salt to cleartext before encrypting it. This makes it harder for attackers to steal data through a brute force attack. TDE also adds a Message Authentication Code (MAC) to the data for integrity checking. The SHA-1 integrity algorithm is used by default.

Example: Creating a New Table with an Encrypted Column Using the Default Algorithm (AES192)

CREATE TABLE employee (      first_name VARCHAR2(128),      last_name VARCHAR2(128),      empID NUMBER,      salary NUMBER(6) ENCRYPT);

Creating a Table with an Encrypted Column Using a Nondefault Algorithm and No Salt

By default, TDE adds salt to cleartext before encrypting it. This makes it harder for attackers to steal data through a brute force attack. However, if you plan to index the encrypted column, you must use NO SALT.

Example: Creating a New Table with an Encrypted Column Using 3DES168 and NO SALT

CREATE TABLE employee (      first_name VARCHAR2(128),      last_name VARCHAR2(128),      empID NUMBER ENCRYPT NO SALT,      salary NUMBER(6) ENCRYPT USING '3DES168');

Creating an Encrypted Tablespace

The CREATE TABLESPACE command enables you to create an encrypted tablespace. If no encryption algorithm is specified, the default encryption algorithm is used. The default encryption algorithm is AES128.

Example Creating an Encrypted Tablespace

CREATE TABLESPACE securespace DATAFILE '/home/user/oradata/secure01.dbf' SIZE 150M ENCRYPTION USING '3DES168' DEFAULT STORAGE(ENCRYPT);

Example Creating an Encrypted Tablespace

CREATE TABLESPACE securespace2 DATAFILE '/home/user/oradata/secure01.dbf' SIZE 150M ENCRYPTION DEFAULT STORAGE(ENCRYPT);

The following data dictionary views maintain information about the encryption status of a tablespace. You can query these views to verify that a tablespace has been encrypted:

-       DBA_TABLESPACES: The ENCRYPTED column indicates whether a tablespace is encrypted

-       USER_TABLESPACES: The ENCRYPTED column indicates whether a tablespace is encrypted

Transparent Data Encryption Works with Export and Import

You can use Oracle Data Pump to export and import tables that contain encrypted columns, as well as encrypt entire dump sets. When you use Oracle Data Pump to export and import tables containing encrypted columns, it uses the ENCRYPTION parameter to enable encryption of data in dump file sets. The ENCRYPTION parameter allows the following values:

ENCRYPTED_COLUMNS_ONLY: Writes encrypted columns to the dump file set in encrypted format

DATA_ONLY: Writes all of the data to the dump file set in encrypted format

METADATA_ONLY: Writes all of the metadata to the dump file set in encrypted format

ALL: Writes all of the data and metadata to the dump file set in encrypted format

NONE: Does not use encryption for dump file sets

ENCRYPTION_PASSWORD

The ENCRYPTION_PASSWORD parameter is not new in Oracle Data Pump 11g release 1. It was first introduced in Oracle Data Pump 10g release 2 and was used when exporting TDE encrypted columns. It now can also be used when creating encrypted dump file sets. The password value that is supplied specifies a key for re-encrypting encrypted table columns, metadata segments, or table data segments so that they are not written as clear text in the dump file set. If the export operation involves encrypted table columns, but an encryption password is not supplied, then the encrypted columns are written to the dump file set as clear text and a warning is issued. For export operations, this parameter is required if the ENCRYPTION_MODE parameter is set to either PASSWORD or DUAL. It is also required if the ENCRYPTION parameter is set to ENCRYPTED_COLUMNS_ONLY.

Exporting and Importing Tables with Encrypted Columns

1. Ensure that the keystore is open before you attempt to export tables containing encrypted columns.To find if the keystore is open, query the STATUS column of the V$ENCRYPTION_WALLET view.

2. Run the EXPDP command, using the ENCRYPTION_PASSWORD parameter to specify a password that is used to encrypt column data in the export dump file set.

example:

expdp \'/ as sysdba \' schemas=alan directory=alan dumpfile=alan.dmp logfile=alanexp.log compression=all encryption_password="password"

RMAN with TDE

To use TDE with RMAN, ensure that the wallet is open before you attempt to backup database. To find if the wallet is open, query the STATUS column of the V$ENCRYPTION_WALLET view.

Example:

CONFIGURE ENCRYPTION FOR DATABASE ON; backup as compressed backupset database;

or

set encryption on; backup as compressed backupset database;

OCP 11gR2: Administration II 練習筆記 (二)

Lession 02:
===========================================================
1. Create recovery catalog instance
create "rcat" instance by dbca or slient mode

require
- no archivelog mode
- no sample
- SGA 500MB


silent mode:
1.1 Set and check OS variables
export DISPLAY=IPADDR:0.0
export ORACLE_SID=rcat
echo $ORACLE_SID
echo $ORACLE_BASE
echo $ORACLE_HOME

1.2 Create a response file dbca_createDB.rsp
------------------------------------------
[GENERAL]
RESPONSEFILE_VERSION = "11.2.0"
OPERATION_TYPE = "createDatabase"

[CREATEDATABASE]
GDBNAME = "rcat"
SID = "rcat"
TEMPLATENAME = "General_Purpose.dbc"
SYSPASSWORD = "password"
SYSTEMPASSWORD = "password"
SYSMANPASSWORD = "password"
DBSNMPPASSWORD = "password"
CHARACTERSET = "AL32UTF8"
NATIONALCHARACTERSET= "UTF8"

1.3 Run dbca in slient mode
dbca -silent -responseFile dbca_createDB.rsp
tail the log file "/u01/app/oracle/cfgtoollogs/dbca/rcat/rcat0.log" for details.


2. Create recovery catalog owner
- create tablespace rcat_ts datafile 'location' size 500M;
- create user rcowner identitied by rcpass temporary 
  tablespace temp default tablespace rcat_ts quota unlimited on rcat_ts;
- grant recovery_catalog_owner to rcowner;

also check recovery_catalog_owner privilege



3. create recovery catalog
RMAN> create recovery catalog;
RMAN> register database;
select * from dbinc;



4. Manuel Resync concept
- After the recovery catalog was unavailable for RMAN
- Infrequent backup target database
- Making change structure of target database (like add/modify datafile)


5. Stored script
RMAN> create global script backup_db
2> {
3> crosscheck backup;
4> delete force noprompt backup;
5> backup as compressed backupset database current controlfile plus archivelog;
6> sql 'alter system archive log current';
7> }

- run script
run {execute script backup_db;}

list script names;
print global script backup_db;
delete global script backup_db;

6. backup recovery catalog
you can backup recovery catalog by controlfile base or use data pump

- use data pump
on target :
SQL> create or replace directory tempdir as '/tmp';
SQL> grant read,write on directory tempdir to rcowner;
# expdp rcowner/rcpass@rcat directory=tempdir dumpfile=rcowner.dmp logfile=exp.log schemas=rcowner
on destination:
SQL> create or replace directory tempdir as '/tmp';
SQL> grant read,write on directory tempdir to rcowner;
# impdp rcowner/rcpass@rcat directory=tempdir dumpfile=rcowner.dmp logfile=imp.log schemas=rcowner


7. Convert base recovery catalog to virtual private catalog (VPC based on base catalog)
- unregister database from base catalog
- create user vpcowner and grant recovery catalog role to vpcowner
- connect base catalog with rcowner
- grant register database to vpcowner in RMAN
- connect base catalog with vpcowner
- create virtual catalog
- connect target and catalog by vpcowner & register database
.
VPC information can query by
select * from rcowner.vpc_users;
select * from rcowner.vpc_databases;


8. config backup parameter
enable controlfile autobackup
config controlfile backup to other location
config device type disk|sbt  -- SBT = system backup to tape
config duplex backupset(datafile, archivelog)
config backup optimization


9. abort backup optimization
- RMAN中的備份優化(Backup Optimization)是指在備份過程中，如果滿足特定條件，RMAN將自動跳過某些文件而不將它們包含在備份集中以節省時間和空間。說的直白些就是能不備的它就不備了，不像原來甭管文件有沒有備份過統統再備一遍。

通常必須滿足如下幾個條件的情況下，才能夠啟用備份優化的功能：
CONFIGURE BACKUP OPTIMIZATION參數置為on；
執行的BACKUP DATABASE或BACKUP ARCHIVELOG命令中帶有ALL或LIKE參數。
分配的通道僅使用了一種設備類型，也就是沒有同時分配使用sbt與disk的多個通道。
那麼在進行備份優化時，RMAN是如何判斷要備份的文件是否需要被優化呢，這個算法就相當複雜了，而且可能影響優化算法的因素也非常多，假如某庫在上午9點被執行過一次全庫備份，等下午3點再次執行全庫備份時，備份的文件沒有變動而且也已經被備份過時，才會跳過這部分文件。所以理論上備份優化僅對於只讀表空間或offline表空間起作用。當然對於已經備份過的archivelog文件它也會跳過。

- The retention policy has an effect on which files backup optimization skips.

- BACKUP DEVICE TYPE sbt BACKUPSET ALL FORCE;
  can override backup optimization and back up all files whether or not they have changed

10. encrypt backup
about encrypt backup, OCP material not mention too much, refer to below URL for details.
Transparent Data Encryption


11. Oracle advance compression
about the OAC, OCP material also not mention too much, refer to below URL for details.
Oracle Advanced Compression



OCP 11gR2: Administration II 練習筆記 (三)
OCP 11gR2: Administration II 練習筆記 (一)

學習初級Oracle PL/SQL (四)

如何在PL/SQL程式跟踪變量(variables)

PL/SQL Data Types

VARCHAR2 - 資料型態儲存變動的字串長度,也就是說,假設宣告一個欄位資料型態為VARCHAR2,長度為5個字，若插入值'a',則欄位中的值為'a',長度只佔1個字.不同於CHAR,資料庫在比較VARCHAR2的值時是採取nonpadded的方式,也就是不會在字串後面補上空白使長度一致後才比較.VARCHAR2最大可裝4000bytes的字PL/SQL VARCHAR2 可裝32,767bytes的字.

Number - 大部分的時間你會在PL/SQL使用number數據類型.而且數據庫,PL/SQL有其他可用的數字數據類型. 例如,你可以使用PLS_INTEGER的類型

,它能存儲-2147483647到+2147483647之間的整數.PLS_INTEGER可以直接進行算術運算(NUMBER數據類型不能直接進行算術運算,如果要計算,NUMBER必須先被轉換成二進制).所以在進行算術的時候PLS_INTEGER比NUMBER和BINARY_INTEGER快一些.但是, 除非你已非常熟習用PL/SQL撰寫procedure, 否則不必太深究它.

DATE - 大部分的時間你會在PL/SQL使用日期和時間,數據類型為DATE. 像NUMBER, PL/SQL也有額外的時間相關的數據類型.

Variables

PL/SQL程式中variables(變量)是一個臨時命名用來支持特定的數據類型.你必須先在PL/SQL宣告區delcare它們.

Variable 命名:

像SQL或數據庫的數據類型,PL/SQL變量必須遵循命名規則:

1. 長度少於31字符.

2. 變量名稱必須以英文名開始,不分大小寫.

3. 變數可以是字母,數字,_ ,$,#

4. PL/SQL保留的關鍵字符不可以定義為變數

5. 每行只能夠定義一個標識符

變數名稱定義習慣:

Prefix	Data Type
c_	CURSOR
d_	DATE
n_	NUMBER
r_	ROW
t_	TABLE
v_	VARCHAR2

例如 table authors

SQL> desc authors

 Name                       Null?  Type

 ----------------------------------------- -------- ----------------------------

 ID                          NOT NULL NUMBER(38)

 NAME                    NOT NULL VARCHAR2(100)

 BIRTH_DATE         DATE

 GENDER                                 VARCHAR2(30)

變數可以命名為:

-       n_id

-       v_name

-       d_birth_date

-       v_gender

使用這些prefix的好處是, 你永遠知道該變量的數據類型和它的範圍. 此外,因為您所命名的名字明顯,你也知道它從哪裡來或到哪裡去. 所謂"明顯"的意思是你不應該命名已經存在的標識符. 例如,不從birth_date列的值創建一個名為d_born變量或d_date_of_birth或d_bday. 為什麼? 因為所有這些名稱變化只會讓指示不清楚.

現在你知道如何命名你的變量,讓我們來看看以下例子如何delcare.

語法: <variable_name> <data_type>;

declare

n_id                      number;

v_name                 varchar2(100);

d_birth_date          date;

v_gender               varchar2(30);

begin

...

end;

或使用Variable Anchors, anchor指利用關鍵字%TYPE用來對應table column的數據類型, 例子如下:

語法: <variable_name> <table_name>.<column_name>%TYPE;

n_id                 AUTHORS.id%TYPE;

v_name            AUTHORS.name%TYPE;

d_birth_date     AUTHORS.birth_date%TYPE;

v_gender          AUTHORS.gender%TYPE;

你現在知道了,通過使用anchor,變量按照來源使用相同的數據類型和大小.

Variable Assignments(變量分派)

變量分派要使用運算符,這是一個冒號後面跟一個等號( := ), 例子如下:

declare

...

begin

n_id                         :=     400;

v_name            :=     'STEVEN FEUERSTEIN';

d_birth_date     :=     to_date('19800101', 'YYYYMMDD');

v_gender          :=     'M';

end;

第二種分配一個值給一個變量方法是在SQL SELECT語句中使用INTO子句, 例子如下:

select id,name,birth_date,gender

into n_id,v_name,d_birth_date,v_gender

from AUTHORS where ........

默認情況下,變量是未初始化的,因此為NULL.你可以設定初始化值,通過declare簡單地分配他們的值聲明.例如,您可以初始化authors變量如下:

語法: <variable_name> <table_name>.<column_name>%TYPE := <value>;

declare

  n_id              AUTHORS.id%TYPE                 :=     400;

  v_name         AUTHORS.name%TYPE            :=     'STEVEN FEUERSTEIN';

  d_birth_date   AUTHORS.birth_date%TYPE     :=     to_date('19800101', 'YYYYMMDD');

  v_gender       AUTHORS.gender%TYPE         :=     NULL;

begin

...

end;

It’s Your Turn to Things Out

尋找下列錯誤原因:

begin

pl(scope1.gv_scope);

pl(scope1.my_scope_is_global());

pl(scope1.iv_scope);

pl(scope1.my_scope_is_instance());

end;

/

pl(scope1.iv_scope);

          *

ERROR at line 4:

ORA-06550: line 4, column 11:

PLS-00302: component 'IV_SCOPE' must be declared

ORA-06550: line 4, column 1:

PL/SQL: Statement ignored

ORA-06550: line 5, column 11:

PLS-00302: component 'MY_SCOPE_IS_INSTANCE' must be declared

ORA-06550: line 5, column 1:

PL/SQL: Statement ignored

create or replace package SCOPE1 as gv_scope varchar2(80) := 'global variable'; function my_scope_is_global return varchar2; procedure my_scope_is_global; end scope1; /

create or replace package body scope1 as iv_scope varchar2(80) := 'instance variable'; function my_scope_is_instance return varchar2 is v_answer_1 varchar2(3) := 'Yes';  begin         pl('can function see gv_scope?');         pl(gv_scope);         return v_answer_1; end my_scope_is_instance; function my_scope_is_global return varchar2 is v_answer_2 varchar2(3) := 'Yes';  begin         pl('Can function see iv_scope?');         pl(iv_scope);         return v_answer_2; end my_scope_is_global; procedure my_scope_is_instance is v_answer_3 varchar2(3) := 'Yes';  begin         pl('Can procedure see gv_scope?');         pl(gv_scope); end my_scope_is_instance; procedure my_scope_is_global is v_answer_4 varchar2(3) := 'Yes';  begin         pl('Can procedure see iv_scope?');         pl(iv_scope); end my_scope_is_global; end scope1; /

Answer:

create or replace package SCOPE1 as gv_scope varchar2(80) := 'global variable'; iv_scope   varchar2(80) := 'instance variable';             -- missing function my_scope_is_global return varchar2; function my_scope_is_instance return varchar2;         -- missing procedure my_scope_is_global; procedure my_scope_is_instance;                            -- missing end scope1; /

create or replace package body scope1 as iv_scope varchar2(80) := 'instance variable';               -- delete function my_scope_is_instance return varchar2 is v_answer_1 varchar2(3) := 'Yes';  begin         pl('can function see gv_scope?');         pl(gv_scope);         return v_answer_1; end my_scope_is_instance;

學習初級Oracle PL/SQL (三)
學習初級Oracle PL/SQL (五)