Oracle 12c - In-Database Archiving

 

In-Database Archiving, one of the many exciting 12c features, allows you to selectively archive the data that is no longer needed for the application, yet you can keep it online along with other active data within the same database for the audit compliance.  
 
In order to access the archived data, all you need to do is just change the visibility setting for the session. This will greatly save you from all the tedious process of the tape backup restore and loading the data back to the database.
 
Even though the archived data is not displayed by default, it is still residing within the same table and will still be validated for all the constraint rules similar to the active data. Also, it is recommended to compress the archived data to prevent any application query and backup performance issues.
 
 
Here is a small demo for this feature.

In this example, I have chosen a test table (acct_details), which stores all the open and closed account information of a bank. Table description and the sample data are listed below. With the in-database archiving feature, we will be archiving data of all the closed accounts.

 
SQL> desc acct_details
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ACCT_ID					    NUMBER(10)
 ACCT_HLDR_FNAME				    VARCHAR2(50)
 ACCT_HLDR_LNAME				    VARCHAR2(50)
 ACCT_TYPE					    VARCHAR2(30)
 ACCT_OPEN_DATE 				    DATE
 ACCT_CLOSE_DATE				    DATE
 ACCT_BRANCH_CODE				    NUMBER(5)


SQL> select * from acct_details;

   ACCT_ID ACCT_HLDR_FNAME	ACCT_HLDR_LNAME      ACCT_TYPE	ACCT_OPEN ACCT_CLOS ACCT_BRANCH_CODE
---------- -------------------- -------------------- ---------- --------- --------- ----------------
      1234 RAJKIRAN		GHANTA		     CURR	15-APR-12 20-APR-13	       34567
      2354 RAJKIRAN		GHANTA		     CURR	20-APR-13 24-FEB-14	       34689
      3891 RAJKIRAN		GHANTA		     CURR	24-FEB-14		       89898
      2658 RADHI		R		     CURR	09-JUN-13 26-JAN-14	       23484
      3568 RADHI		R		     CURR	26-JAN-14		       84372


SQL> select table_name,column_name,data_type from user_tab_cols
  2  where table_name='ACCT_DETAILS';

TABLE_NAME		       COLUMN_NAME		      DATA_TYPE
------------------------------ ------------------------------ ------------------------------
ACCT_DETAILS		       ACCT_BRANCH_CODE 	      NUMBER
ACCT_DETAILS		       ACCT_CLOSE_DATE		      DATE
ACCT_DETAILS		       ACCT_OPEN_DATE		      DATE
ACCT_DETAILS		       ACCT_TYPE		      VARCHAR2
ACCT_DETAILS		       ACCT_HLDR_LNAME		      VARCHAR2
ACCT_DETAILS		       ACCT_HLDR_FNAME		      VARCHAR2
ACCT_DETAILS		       ACCT_ID			      NUMBER

7 rows selected.

 

 

In-database Archiving can be enabled at the table level using the row archival clause.

 
SQL> alter table acct_details row archival;

Table altered.

 

You may notice that two additional hidden columns are added to the table, as seen from the user_tab_cols view.

SQL> select table_name,column_name,data_type from user_tab_cols
  2  where table_name='ACCT_DETAILS';

TABLE_NAME		       COLUMN_NAME		      DATA_TYPE
------------------------------ ------------------------------ ------------------------------
ACCT_DETAILS		       ORA_ARCHIVE_STATE	      VARCHAR2
ACCT_DETAILS		       SYS_NC00008$		      RAW
ACCT_DETAILS		       ACCT_BRANCH_CODE 	      NUMBER
ACCT_DETAILS		       ACCT_CLOSE_DATE		      DATE
ACCT_DETAILS		       ACCT_OPEN_DATE		      DATE
ACCT_DETAILS		       ACCT_TYPE		      VARCHAR2
ACCT_DETAILS		       ACCT_HLDR_LNAME		      VARCHAR2
ACCT_DETAILS		       ACCT_HLDR_FNAME		      VARCHAR2
ACCT_DETAILS		       ACCT_ID			      NUMBER

9 rows selected.

 

The value of '0' in the ORA_ARCHIVE_STATE column denotes that the row data is active.

SQL> select acct_id,acct_hldr_fname,acct_hldr_lname,acct_type,acct_open_date,acct_close_date,acct_branch_code,ora_archive_state
     from acct_details
/

   ACCT_ID ACCT_HLDR_FNAME	ACCT_HLDR_LNAME      ACCT_TYPE	ACCT_OPEN ACCT_CLOS ACCT_BRANCH_CODE ORA_ARCHIV
---------- -------------------- -------------------- ---------- --------- --------- ---------------- ----------
      1234 RAJKIRAN		GHANTA		     CURR	15-APR-12 20-APR-13	       34567 0
      2354 RAJKIRAN		GHANTA		     CURR	20-APR-13 24-FEB-14	       34689 0
      3891 RAJKIRAN		GHANTA		     CURR	24-FEB-14		       89898 0
      2658 RADHI		R		     CURR	09-JUN-13 26-JAN-14	       23484 0
      3568 RADHI		R		     CURR	26-JAN-14		       84372 0

SQL> 

 

 

All the closed accounts are archived by updating ora_archive_state column to non-zero value as shown below.

 
SQL> update acct_details
  2  set ora_archive_state=dbms_ilm.archivestatename(1)
  3  where acct_close_date is not null;

3 rows updated.

SQL> commit;

Commit complete.

 

After the update, only the active accounts (with acct_close_date as null) are displayed.

SQL> select acct_id,acct_hldr_fname,acct_hldr_lname,acct_type,acct_open_date,acct_close_date,acct_branch_code,ora_archive_state
  2  from acct_details
  3  ;

   ACCT_ID ACCT_HLDR_FNAME	ACCT_HLDR_LNAME      ACCT_TYPE	ACCT_OPEN ACCT_CLOS ACCT_BRANCH_CODE ORA_ARCHIV
---------- -------------------- -------------------- ---------- --------- --------- ---------------- ----------
      3891 RAJKIRAN		GHANTA		     CURR	24-FEB-14		       89898 0
      3568 RADHI		R		     CURR	26-JAN-14		       84372 0



SQL> SQL> select * from acct_details;

   ACCT_ID ACCT_HLDR_FNAME	ACCT_HLDR_LNAME      ACCT_TYPE	ACCT_OPEN ACCT_CLOS ACCT_BRANCH_CODE
---------- -------------------- -------------------- ---------- --------- --------- ----------------
      3891 RAJKIRAN		GHANTA		     CURR	24-FEB-14		       89898
      3568 RADHI		R		     CURR	26-JAN-14		       84372

 

 

If needed, you can view the archived data by changing the visibility setting for the session. The value setting for the session visibility is either ALL or ACTIVE.

SQL> alter session set row archival visibility=all;

Session altered.

SQL>  select * from acct_details;

   ACCT_ID ACCT_HLDR_FNAME	ACCT_HLDR_LNAME      ACCT_TYPE	ACCT_OPEN ACCT_CLOS ACCT_BRANCH_CODE
---------- -------------------- -------------------- ---------- --------- --------- ----------------
      1234 RAJKIRAN		GHANTA		     CURR	15-APR-12 20-APR-13	       34567
      2354 RAJKIRAN		GHANTA		     CURR	20-APR-13 24-FEB-14	       34689
      3891 RAJKIRAN		GHANTA		     CURR	24-FEB-14		       89898
      2658 RADHI		R		     CURR	09-JUN-13 26-JAN-14	       23484
      3568 RADHI		R		     CURR	26-JAN-14		       84372

SQL> select acct_id,acct_hldr_fname,acct_hldr_lname,acct_type,acct_open_date,acct_close_date,acct_branch_code,ora_archive_state
  2  from acct_details;

   ACCT_ID ACCT_HLDR_FNAME	ACCT_HLDR_LNAME      ACCT_TYPE	ACCT_OPEN ACCT_CLOS ACCT_BRANCH_CODE ORA_ARCHIV
---------- -------------------- -------------------- ---------- --------- --------- ---------------- ----------
      1234 RAJKIRAN		GHANTA		     CURR	15-APR-12 20-APR-13	       34567 1
      2354 RAJKIRAN		GHANTA		     CURR	20-APR-13 24-FEB-14	       34689 1
      3891 RAJKIRAN		GHANTA		     CURR	24-FEB-14		       89898 0
      2658 RADHI		R		     CURR	09-JUN-13 26-JAN-14	       23484 1
      3568 RADHI		R		     CURR	26-JAN-14		       84372 0

SQL> 

 

 
 

If you prefer, archived data can also be marked with different archival states (other than the value of '1' )

SQL> update acct_details
  2  set ora_archive_state='GHANTA'
  3  where acct_close_date is not null and acct_hldr_lname='GHANTA';

2 rows updated.

SQL> update acct_details
  2  set ora_archive_state='RADHI'
  3  where acct_close_date is not null and acct_hldr_fname='RADHI';

1 row updated.

SQL> commit;

Commit complete.

SQL> select acct_id,acct_hldr_fname,acct_hldr_lname,acct_type,acct_open_date,acct_close_date,acct_branch_code,ora_archive_state
  2  from acct_details;

   ACCT_ID ACCT_HLDR_FNAME	ACCT_HLDR_LNAME      ACCT_TYPE	ACCT_OPEN ACCT_CLOS ACCT_BRANCH_CODE ORA_ARCHIV
---------- -------------------- -------------------- ---------- --------- --------- ---------------- ----------
      1234 RAJKIRAN		GHANTA		     CURR	15-APR-12 20-APR-13	       34567 GHANTA
      2354 RAJKIRAN		GHANTA		     CURR	20-APR-13 24-FEB-14	       34689 GHANTA
      3891 RAJKIRAN		GHANTA		     CURR	24-FEB-14		       89898 0
      2658 RADHI		R		     CURR	09-JUN-13 26-JAN-14	       23484 RADHI
      3568 RADHI		R		     CURR	26-JAN-14		       84372 0


SQL> alter session set row archival visibility=active;

Session altered.

SQL> select acct_id,acct_hldr_fname,acct_hldr_lname,acct_type,acct_open_date,acct_close_date,acct_branch_code,ora_archive_state
  2  from acct_details;

   ACCT_ID ACCT_HLDR_FNAME	ACCT_HLDR_LNAME      ACCT_TYPE	ACCT_OPEN ACCT_CLOS ACCT_BRANCH_CODE ORA_ARCHIV
---------- -------------------- -------------------- ---------- --------- --------- ---------------- ----------
      3891 RAJKIRAN		GHANTA		     CURR	24-FEB-14		       89898 0
      3568 RADHI		R		     CURR	26-JAN-14		       84372 0

SQL>  select * from acct_details;

   ACCT_ID ACCT_HLDR_FNAME	ACCT_HLDR_LNAME      ACCT_TYPE	ACCT_OPEN ACCT_CLOS ACCT_BRANCH_CODE
---------- -------------------- -------------------- ---------- --------- --------- ----------------
      3891 RAJKIRAN		GHANTA		     CURR	24-FEB-14		       89898
      3568 RADHI		R		     CURR	26-JAN-14		       84372

SQL> 

 

 

In-database archiving for the table can be disabled by using the no row archival clause

SQL> alter table acct_details no row archival;

Table altered.

SQL> select * from acct_details;

   ACCT_ID ACCT_HLDR_FNAME	ACCT_HLDR_LNAME      ACCT_TYPE	ACCT_OPEN ACCT_CLOS ACCT_BRANCH_CODE
---------- -------------------- -------------------- ---------- --------- --------- ----------------
      1234 RAJKIRAN		GHANTA		     CURR	15-APR-12 20-APR-13	       34567
      2354 RAJKIRAN		GHANTA		     CURR	20-APR-13 24-FEB-14	       34689
      3891 RAJKIRAN		GHANTA		     CURR	24-FEB-14		       89898
      2658 RADHI		R		     CURR	09-JUN-13 26-JAN-14	       23484
      3568 RADHI		R		     CURR	26-JAN-14		       84372

 

After disabling the feature, the hidden column no longer exists for the table

SQL>  select acct_id,acct_hldr_fname,acct_hldr_lname,acct_type,acct_open_date,acct_close_date,acct_branch_code,ora_archive_state
  2  from acct_details;
 select acct_id,acct_hldr_fname,acct_hldr_lname,acct_type,acct_open_date,acct_close_date,acct_branch_code,ora_archive_state
                                                                                                          *
ERROR at line 1:
ORA-00904: "ORA_ARCHIVE_STATE": invalid identifier

 

 

References :
 

 

Cheers!

rajkiran

Copyright ©2018 Rajkiran Ghanta (deegeplanet.com)