Wednesday, June 26, 2013

Oracle 12c New Features - In Database Row Archiving

Oracle 12c has a new feature called In Database Row Archiving.  Instead of deleting old records they can now be marked as archived, be invisible to running applications, but remain in the original table in case they need to be restored at a later date.  This should reduce the need for DBAs to restore old backups, and should keep auditors happy.

First create a test table.

SQL> create table employee (emp_id integer primary key, emp_name varchar2(10), archive_date date);

Table created.

SQL> insert into employee (emp_id, emp_name) values (1,'John');

1 row created.

SQL> insert into employee (emp_id, emp_name) values (2,'Ben');

1 row created.

SQL> commit;

Commit complete.

Now enable archival for the table.

SQL> alter table employee row archival;

Table altered.

We can see from hidden attribute ORA_ARCHIVE_STATE=0 that the records are not archived.

SQL> select * from employee;

EMP_ID EMP_NAME   ARCHIVE_D
------ ---------- ---------
     1 John
     2 Ben

SQL> select emp_id, emp_name, ora_archive_state from employee;

EMP_ID EMP_NAME   ORA_ARCHIVE_STATE
------ ---------- -----------------------------------------------
     1 John       0
     2 Ben        0

Now we want to archive the record for employee Ben (he resigned).

SQL> update employee
set ora_archive_state=dbms_ilm.archivestatename(1), archive_date=sysdate
where emp_id=2; 

1 row updated.

SQL> commit;

Commit complete.

The record record for Ben is no longer visible to normal select operations.

SQL> select * from employee;

EMP_ID EMP_NAME   ARCHIVE_D
------ ---------- ---------
     1 John

But if we set row archive visibility=all then we can see that the record still exists in the table.  

SQL> alter session set row archival visibility = all;

Session altered.

SQL> select emp_id, emp_name, ora_archive_state from employee;

EMP_ID EMP_NAME   ORA_ARCHIVE_STATE
------ ---------- -----------------------------------------------
     1 John       0
     2 Ben        1


SQL> alter session set row archival visibility = active;

Session altered.

And while the record is invisible to the application, it is still considered by constraints.

SQL> select * from employee;

EMP_ID EMP_NAME   ARCHIVE_D
------ ---------- ---------
     1 John

SQL> insert into employee (emp_id, emp_name) values (2,'Ben');
insert into employee (emp_id, emp_name) values (2,'Ben')
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.SYS_C009859) violated

And if we re-hire Ben, the record can be brought back from archive.

SQL> alter session set row archival visibility = all;

Session altered.

SQL> update employee
set ora_archive_state=dbms_ilm.archivestatename(0), archive_date=null
where emp_id=2; 

1 row updated.

SQL> commit;

Commit complete.

SQL> alter session set row archival visibility = active;

Session altered.

SQL> select * from employee;

EMP_ID EMP_NAME   ARCHIVE_D
------ ---------- ---------
     1 John
     2 Ben