Tag Archives: Automatic Data optimization

12C: IN DATABASE ARCHIVING

In this post, I will demonstrate a new feature introduced in 12c : In database archiving.  Instead of deleting old records, it enables you to archive  rows within a table by marking them as archived . The archived rows are invisible to running applications but remain in the original table in case they need to be restored at a later date.  In other words,  individual rows are  “soft-deleted”  so they are excluded from normal DML statements, but they’re still there for regulatory, compliance or archiving reasons.  Moreover, Archived data can be compressed to help improve backup performance.

This feature  is accomplshed  by means of a hidden column ORA_ARCHIVE_STATE. These invisible rows  can be viewed , if needed, by setting a session parameter ROW ARCHIVAL VISIBILITY.    Now, the DB’s need not  restore old backups to view archived data.

Overview:

— Create test user uilm, tablespace ilmtbs
— Connect as user uilm
— create and populate test table (5 rows) ilmtab with row archival clause
— Note that the table has an additional column ORA_ARCHIVE_STATE automatically created   and has the default value of 0 (indicates that row is active)
— Note that this column is not visible when we describe the table or simply issue select * from …
— We need to access data dictionary to view the column
— Make two  rows in the table inactive by setting ORA_ARCHIVE_STATE column to a non zero value.
— Check that inactive rows are not visible to query
— Set the parameter ROW ARCHIVAL VISIBILITY  = all to see inactive rows also
— Set the parameter ROW ARCHIVAL VISIBILITY  = active to hide inactive rows
— Add primary key constraint to thetable and try to add invisible id again : fails as constraints are still applicable to hidden rows
— Create another table from the table using CTAS and note that all the rows (visible/invisible are inserted but column ORA_ARCHIVE_STATE is not copied to the target table
— Drop primary key constraint
— Issue an insert into … select * and check that only 3 visible rows are inserted
— Set the parameter ROW ARCHIVAL VISIBILITY  = all to see inactive rows also
— Issue an insert into … select * and check that all the rows are inserted but ORA_ARCHIVE_STATE    is not propagated in inserted rows
— Disable row archiving in the table and check that column ORA_ARCHIVE_STATE is automatically dropped
— drop tablespace ilmtbs and user uilm

Implementation :

– Create test user, tablespace and test table

SQL> conn sys/oracle@em12c:1523/pdb1 as sysdba
sho con_name

CON_NAME
------------------------------
PDB1

SQL> set sqlprompt PDB1>

PDB1>create tablespace ilmtbs datafile '/u02/app/oracle/oradata/cdb1/pdb1/ilmtbs01.dbf' size 1m;
grant connect, resource, dba  to uilm identified by oracle;
alter user uilm default tablespace ilmtbs;

conn uilm/oracle@em12c:1523/pdb1
sho con_name

CON_NAME
------------------------------
PDB1

– create table with “row archival clause”

PDB1>drop table ilmtab purge;
create table ilmtab (id number, txt char(15)) row archival;
insert into ilmtab values (1, 'one');
insert into ilmtab values (2, 'two');
insert into ilmtab values (3, 'three');
insert into ilmtab values (4, 'four');
insert into ilmtab values (5, 'five');
commit;

– Note that the table has an additional column ORA_ARCHIVE_STATE automatically created    and has the default value of 0 (indicates that row is active)

PDB1>col ora_archive_state for a20
select id, txt, ora_archive_state from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one             0
2 two             0
3 three           0
4 four            0
5 five            0

– Note that this column is not visible when we describe the table or simply issue select * from …

PDB1>desc ilmtab
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
TXT                                                CHAR(15)

PDB1>select * from ilmtab;

ID TXT
---------- ---------------
1 one
2 two
3 three
4 four
5 five

— Since the column is invisible, let me try and make it visible
– Note that Since the column is maintained by oracle itself, user can’t modify its attributes

PDB1>alter table ilmtab modify (ora_archive_state visible);
alter table ilmtab modify (ora_archive_state visible)
*
ERROR at line 1:
ORA-38398: DDL not allowed on the system ILM column

— We need to access data dictionary to view the column
– Note that this column is shown as hidden and has not been generated by user

PDB1>col hidden for a7
col USER_GENERATED for 20
col USER_GENERATED for a20

select TABLE_NAME, COLUMN_NAME, HIDDEN_COLUMN, USER_GENERATED
from user_tab_cols where table_name='ILMTAB';

TABLE_NAME  COLUMN_NAME          HID USER_GENERATED
----------- -------------------- --- --------------------
ILMTAB      ORA_ARCHIVE_STATE    YES NO
ILMTAB      ID                   NO  YES
ILMTAB      TXT                  NO  YES

– We can make selected rows in the table inactive by setting ORA_ARCHIVE_STATE column to a non zero value.
This can be accomplished using update table… set ORA_ACRHIVE_STATE =
. <non-zero value>
. dbms_ilm.archivestatename(1)

– Let’s update row with id =1 with ORA_ARCHIVE_STATE=2
     and update row with id =2 with dbms_ilm.archivestatename(2)

PDB1>update ilmtab set ora_archive_state=2 where id=1;

update ilmtab set ora_archive_state= dbms_ilm.archivestatename(2) where id=2;

– Let’s check whether updates have been successful and hidden rows are not visible

PDB1>select id, txt, ORA_ARCHIVE_STATE from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
3 three           0
4 four            0
5 five            0

— The updated rows are not visible!!
— Quite logical since we have made the rows active and by default only active rows are visible

– To see inactive rows also, we need to set the parameter ROW ARCHIVAL VISIBILITY  = all at session level
— Note that the column ORA_ARCHIVE_STATE has been set to 1 for id =2 although we had set it to 2 using
dbms_ilm.archivestatename(2)

PDB1>alter session set ROW ARCHIVAL VISIBILITY  = all;
select id, txt, ORA_ARCHIVE_STATE from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one             2
2 two             1
3 three           0
4 four            0
5 five            0

– Note that the column ORA_ARCHIVE_STATE has been set to 1 for id =2 although we had set it to 2 using    dbms_ilm.archivestatename(2)

— Let’s find out why
– Note that The function dbms_ilm.archivestatename(n) returns only two values    0 for n=0 and 1 for  n <> 0

PDB1>col state0 for a8
col state1 for a8
col state2 for a8
col state3 for a8

select dbms_ilm.archivestatename(0) state0 ,dbms_ilm.archivestatename(1) state1,
dbms_ilm.archivestatename(2) state2,dbms_ilm.archivestatename(3) state3  from dual;

STATE0   STATE1   STATE2   STATE3
-------- -------- -------- --------
0        1        1        1

– In order to make the inactive rows (id=1,2) hidden again, we need to set the parameter ROW ARCHIVAL VISIBILITY  = Active

PDB1>alter session set row archival visibility = active;
select id, txt, ORA_ARCHIVE_STATE from ilmtab;

ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
3 three           0
4 four            0
5 five            0

– Let’s add primary key constraint to the table

SQL> alter table ilmtab add constraint ilmtab_pk primary key(id);

– Let’s try to insert record for an id which is already there but is not visible – say id=1

SQL> insert into ilmtab values (1, 'duplicate');
\insert into ilmtab values (1, 'duplicate')
*
ERROR at line 1:
ORA-00001: unique constraint (UILM.ILMTAB_PK) violated

-- It is clear that although we have made rows invisible, constraints defined are still adhered to

– Let’s try to create another table from ilmtab using CTAS
– Note that all the rows are inserted and column ORA_ARCHIVE_STATE is not copied to the target table

PDB1>create table ilmtab_copy as  select * from ilmtab;

     select id, txt, ora_archive_state from ilmtab_copy;

ERROR at line 1:
ORA-00904: "ORA_ARCHIVE_STATE": invalid identifier

SQL>  select id, txt from ilmtab_copy
  2  ;

        ID TXT
---------- ---------------
         1 one
         2 two
         3 three
         4 four
         5 five

– Let’s drop the primary key constraint and  issue an insert into … select *
– Note that only 3 new rows are visible

PDB1>alter table ilmtab drop primary key;
     insert into ilmtab select * from ilmtab;

3 rows created.

PDB1> select id, txt, ora_archive_state from ilmtab order by id;

        ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
         3 three           0
         3 three           0
         4 four            0
         4 four            0
         5 five            0
         5 five            0

6 rows selected.

— I want to check if hidden rows were also inserted
— Let’s check by making  hidden rows visible again
– Note that only visible rows(id=3,4,5) were inserted

PDB1>alter session set row archival visibility=all;
     select id, txt, ora_archive_state from ilmtab order by id;

        ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
         1 one             2
         2 two             1
         3 three           0
         3 three           0
         4 four            0
         4 four            0
         5 five            0
         5 five            0

8 rows selected.

– Let’s set row archival visibility = all and then again insert rows from ilmtab
– Note that all the 8 rows are inserted but ORA_ARCHIVE_STATE ha not been copied    ORA_ARCHIVE_STATE <> 0 in only 2 records (id = 1,2) even now.

PDB1>alter session set row archival visibility=all;
     insert into ilmtab select * from ilmtab;
     select id, txt, ora_archive_state from ilmtab order by id;
8 rows created.

SQL> 
        ID TXT             ORA_ARCHIVE_STATE
---------- --------------- --------------------
         1 one             0
         1 one             2
         2 two             0
         2 two             1
         3 three           0
         3 three           0
         3 three           0
         3 three           0
         4 four            0
         4 four            0
         4 four            0
         4 four            0
         5 five            0
         5 five            0
         5 five            0
         5 five            0

16 rows selected.

– Disable row level archiving for the table
— Note that as soon as row archiving is disabled, pseudo column ora_archive_state is dropped automatically

PDB1>alter table ilmtab no row archival;
     select id, txt, ORA_ARCHIVE_STATE from ilmtab;

ERROR at line 1:
ORA-00904: "ORA_ARCHIVE_STATE": invalid identifier

PDB1>col hidden for a7
col USER_GENERATED for 20
col USER_GENERATED for a20

select TABLE_NAME, COLUMN_NAME, HIDDEN_COLUMN, USER_GENERATED
from user_tab_cols where table_name='ILMTAB';

TABLE_NAME  COLUMN_NAME          HID USER_GENERATED
----------- -------------------- --- --------------------
ILMTAB      ID                   NO  YES
ILMTAB      TXT                  NO  YES

Note : Had we created this table using sys, we could not have disabled row archiving .

– cleanup –

PDB1>conn sys/oracle@em12c:1523/pdb1 as sysdba
drop tablespace ilmtbs including contents and datafiles;
drop user uilm cascade;

Summary:

– In-database archiving enables archiving of rows in tables created with row archival clause.

– Such tables have an oracle generated column ORA_ARCHIVE_STATE  which can contain values :

. 0 : Rows are visible  to queries

. Non zero : Rows are hidden from queries

– Invisible rows can be seen if parameter row archival visibility is set to all at session level.

– Default value of parameter row archival visibility is active : allows only those rows to be visible which have ora_archive_state=0.

– If another table is created using CTAS from a table with archived rows, column ORA_ARCHIVE_STATE is not copied to the target table

– If rows from this table are inserted into another table

. if row archival visibility = active

only visible rows are inserted with ORA_ARCHIVE_STATE=0 in target table

. if row archival visibility = all

all the rows are inserted with ORA_ARCHIVE_STATE=0 in target table

 

References:

http://docs.oracle.com/cd/E16655_01/server.121/e17613/part_lifecycle.htm#VLDBG14154

—————————————————————————————————-
Related Links:

Home

Database 12c Index

12c: Improve Backup Performance Using In-database Archiving
12c: Performance Issue With In-database Archiving
12c: Solution To Performance Issue With In-database Archiving

———————————————————————————————-