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:
12c: Improve Backup Performance Using In-database Archiving
12c: Performance Issue With In-database Archiving
12c: Solution To Performance Issue With In-database Archiving
———————————————————————————————-