[20130817]Oracle 12c new feature In-Database Archiving_Information Life Cycle Management.txt

一些大表儲存大量資訊，裡面的許多資訊可能不再需要。而且保持這些資訊，對備份以及恢復都帶來影響。

12C提供新特性-In-Database Archiving,即 Information Life Cycle Management (ILM)，保持新與舊的資訊，僅僅顯示新資訊。

要使用這個新特性，系統會建立一個隱含列ORA_ARCHIVE_STATE,預設是0。

   Very often in our databases we have some very large tables which have a lot of historical and legacy data and the
challenge is deciding what is old data and what is current data and even if we do identify the old data we do not need
and have moved that data to tape storage, what happens if that data is suddenly required. Getting that data back in the
database can be a very expensive and time consuming exercise.

   Keeping large volumes of (unnecessary at most times) historical data in the production OLTP database can not only
increase the database footprint for backup and recovery but can also have an adverse impact on database performance.

   The new 12c Information Life Cycle Management (ILM) feature called In-Database Archiving enables us to overcome the
issues stated above by enabling the database to distinguish from active data and 'older' in-active data while at the
same time storing everything in the same database.

   When we enable row archival for a table, a hidden column called ORA_ARCHIVE_STATE column is added to the table and
this column is automatically assigned a value of 0 to denote current data and we can decide what data in the table is to
be considered as candidates for row archiving and they are assigned the value 1

   Once the older and more current data is distinguished, we can archive and compress the older data to reduce the size
of the database or move that older data to a cheaper storage tier to reduce cost of storing data.

   Let us have a look at an example of using this Oracle 12c new feature called In-Database Archiving

做一個測試看看：

SQL> @ver


BANNER                                                                               CON_ID

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

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0


SQL> create table t as select rownum id ,'test' name from dual connect by level<=1e3;

Table created.


SQL> select count(*) from t;

  COUNT(*)

----------


SQL> select object_name,object_id,data_object_id from dba_objects where wner=user and object_name='T';

OBJECT_NAME           OBJECT_ID DATA_OBJECT_ID

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

T                         92958          92958


SQL> select col#,name,segcol#,intcol# from sys.col$ where obj#=92958;

      COL# NAME                    SEGCOL#    INTCOL#

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

         1 ID                            1          1

         2 NAME                          2          2

2.開啟row archival功能:

SQL> alter table t row archival;

Table altered.


SQL> select object_name,object_id,data_object_id from dba_objects where wner=user and object_name='T';

OBJECT_NAME           OBJECT_ID DATA_OBJECT_ID

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

T                         92958          92958


SQL> select col#,name,segcol#,intcol# from sys.col$ where obj#=92958;

      COL# NAME                    SEGCOL#    INTCOL#

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

         1 ID                            1          1

         2 NAME                          2          2

         0 SYS_NC00003$                  3          3

         0 ORA_ARCHIVE_STATE             4          4

--可以發現開啟row archival後，實際上增加了兩個隱含欄位。儲存在第3,4個欄位，對原來的儲存沒有影響。

SQL> select table_name,column_name,data_type from dba_tab_cols where wner=user and table_name='T';

TABLE_NAME COLUMN_NAME          DATA_TYPE

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

T          ORA_ARCHIVE_STATE    VARCHAR2

T          SYS_NC00003$         RAW

T          NAME                 CHAR

T          ID                   NUMBER


SQL> select column_name,data_default from dba_tab_cols where wner=user and table_name='T' and column_name in ('SYS_NC00003$','ORA_ARCHIVE_STATE');

COLUMN_NAME          DATA_DEFAULT

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

SYS_NC00003$

ORA_ARCHIVE_STATE    0

--可以看出ORA_ARCHIVE_STATE的預設值就是0.

SQL> column ora_archive_state format a10

SQL> select  SYS_NC00003$,ora_archive_state,id,name from t where rownum<=5;

SYS_NC00003$         ORA_ARCHIV         ID NAME

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

                     0                   1 test

                     0                   2 test

                     0                   3 test

                     0                   4 test

                     0                   5 test


SQL> update t set ORA_ARCHIVE_STATE=DBMS_ILM.ARCHIVESTATENAME(1) where id <=5;

5 rows updated.


SQL> commit ;

Commit complete.


SQL> select count(*) from t;

  COUNT(*)

----------

--僅僅看到995行，比原來少了5條記錄。要想看到全部記錄

SQL> alter session set row archival visibility=ALL;

Session altered.

SQL> select count(*) from t;

  COUNT(*)

----------


SQL> select  rowid ,SYS_NC00003$,ora_archive_state,id,name from t where rownum<=6;

ROWID              SYS_NC00003$         ORA_ARCHIV         ID NAME

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

AAAWseAAJAAAACrAAA 01                   1                   1 test

AAAWseAAJAAAACrAAB 01                   1                   2 test

AAAWseAAJAAAACrAAC 01                   1                   3 test

AAAWseAAJAAAACrAAD 01                   1                   4 test

AAAWseAAJAAAACrAAE 01                   1                   5 test

AAAWseAAJAAAACrAAF                      0                   6 test

6 rows selected.

3.關於資訊的儲存：
很明顯，這些資訊儲存在塊中。做一個塊轉儲就很清晰了。

SQL> @lookup_rowid AAAWseAAJAAAACrAAA

    OBJECT       FILE      BLOCK        ROW DBA

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

     92958          9        171          0 9,171


SQL> alter system dump datafile 9 block 171;

System altered.

--太長,僅僅取需要說明的部分：
block_row_dump:
tab 0, row 0, @0x749
tl: 15 fb: --H-FL-- lb: 0x2 cc: 4
col 0: [ 2] c1 02
col 1: [ 4] 74 65 73 74
col 2: [ 1] 01
col 3: [ 1] 31
tab 0, row 1, @0x73a
tl: 15 fb: --H-FL-- lb: 0x2 cc: 4
col 0: [ 2] c1 03
col 1: [ 4] 74 65 73 74
col 2: [ 1] 01
col 3: [ 1] 31
tab 0, row 2, @0x72b
tl: 15 fb: --H-FL-- lb: 0x2 cc: 4
col 0: [ 2] c1 04
col 1: [ 4] 74 65 73 74
col 2: [ 1] 01
col 3: [ 1] 31
tab 0, row 3, @0x71c
tl: 15 fb: --H-FL-- lb: 0x2 cc: 4
col 0: [ 2] c1 05
col 1: [ 4] 74 65 73 74
col 2: [ 1] 01
col 3: [ 1] 31
tab 0, row 4, @0x70d
tl: 15 fb: --H-FL-- lb: 0x2 cc: 4
col 0: [ 2] c1 06
col 1: [ 4] 74 65 73 74
col 2: [ 1] 01
col 3: [ 1] 31
tab 0, row 5, @0x1f3e
tl: 11 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 07
col 1: [ 4] 74 65 73 74
tab 0, row 6, @0x1f33
tl: 11 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 08
col 1: [ 4] 74 65 73 74

SQL> select dump(6,16) from dual ;

DUMP(6,16)

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

Typ=2 Len=2: c1,7

--可以發現id>=6以後的都沒有修改。

4.這個特性給人的感覺馬上聯想到rowdependencies特性。
當然rowdependencies特性僅僅在定義表的時候指定，無法alter。

[20130817]Oracle 12c new feature In-Database Archiving.txt

相關文章