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

[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。

http://gavinsoorma.com/2013/08/oracle-12c-new-feature-in-database-archiving/

    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

Table created.

SQL> select count(*) from t;

  COUNT(*)

----------

      1000

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

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 rows updated.

SQL> commit ;

Commit complete.

SQL> select count(*) from t;

  COUNT(*)

----------

       995

--仅仅看到995行，比原来少了5条记录。要想看到全部记录


SQL> alter session set row archival visibility=ALL;

Session altered.

SQL> select count(*) from t;

  COUNT(*)

----------

      1000

SQL> select  rowid ,SYS_NC00003$,ora_archive_state,id,name from t where rownum

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。