Dear Readers,
In this article, we will see the following Database Archiving in Oracle Database 12c.
In this article, We will see how we can implement In-Database Archiving.
As an oracle DBA we have biggest challenge is how to deal with historical vast quantities of data?
As an Oracle DBA we always come across a point during the life-cycle of the data that data no longer being needed/accessed and we considered that it should be archived.
Generally, the data would have been removed from the database and stored in tape because tape is low cost storage and it stored large quantities data for a long period of time also.
As you already aware in this solution we have some disadvantage also like data is not available in the database, if in case application team ask that they need again that data then we need to reload data into database from the tape and this whole process or task is time consuming and a lot of work for DBA. If we keep years and years data (historical data) in database that will directly impact on database performance like CPU utilization will increase and it will take too much time for database backup and restore. Database size will grow every day like whale because of database size, there will be performance cost associated with queries, DDLs and DMLs like index creation, rebuild index, removing unused space from tablespace and expdp/impdp.
To overcome above all situation from 12c onward oracle has introduced new smart feature called In Database Archiving. From 12c onward, it is no longer necessary to archive data into tape but we can keep in database only. This is called In-Database Archiving.
Advantage of this feature is
- We can access historical data faster
- Backup and restore performance increased
- Identify between active vs inactive data at row level
- Access active data only
- The goal to keep data online as much as possible
In-Database archiving is enhanced in oracle Database 12c with row-archival management, which indicates as a row property the activeness of row data. To distinguish active rows from those in a non-active state within the same table and then compress the latter ones, you would use the new In-Database archiving capability.
How to implement In-Database archiving?
Let’s look at one of the new features of Oracle 12c In-Database Archiving, which really allows you to go out and take columns, rows that are not being used and set them as inactive, so they really can’t be same.
Note :
We can enable In-database archiving at table level or else you can enable later altering the table by adding clause row archival.
Let’s see first how to enable In-database archiving at table level.
|
1 2 3 4 5 6 |
SQL> create table acc_cust (sno number, sname varchar2(10), address varchar2(30)) row archival; Table created. |
We have added row archival in the last line while creating table to enable In-database archiving.
Let’s see that it is enabled or not on table, to check let’s execute below query
|
1 2 3 4 5 6 7 8 9 10 |
SQL> select table_name, column_name, column_id, HIDDEN_COLUMN from dba_tab_cols where table_name = 'ACC_CUST'; TABLE_NAME COLUMN_NAME COLUMN_ID HID -------------------- -------------------- ---------- --- ACC_CUST ORA_ARCHIVE_STATE YES ACC_CUST SNO 1 NO ACC_CUST SNAME 2 NO ACC_CUST ADDRESS 3 NO SQL> drop table acc_cust; Table dropped. |
Now, we will see second method that after creating table how to enable in-database archiving by using alter table command.
Let’s create first table and insert few rows.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> create table acc_cust (sno number,sname varchar2(10),address varchar2(30)); Table created. SQL> insert into acc_cust(sno,sname,address) values(101,'VINOD','19/45.....'); 1 row created. SQL> insert into acc_cust(sno,sname,address) values(102,'AJAY','14/85...'); 1 row created. SQL> insert into acc_cust(sno,sname,address) values(103,'SAI','16/58.....'); 1 row created. SQL> COMMIT; Commit complete. SQL> SELECT * FROM acc_cust; SNO SNAME ADDRESS ---------- ---------- ------------------------------ 101 VINOD 19/45..... 102 AJAY 14/85... 103 SAI 16/58..... |
Now, We wanted to convert above table into In-Database Archive mode, execute the below command as shown.
|
1 2 |
SQL> alter table acc_cust row archival; Table altered. |
As you can see above command we have added ROW ARCHIVAL clause. This clause will automatically add an extra column name called ORA_ARCHIVE_STATE to the table. But remember this column is hidden column.
If you will we can describe the table, you will see in output that you won’t able to see this column.
|
1 2 3 4 5 6 |
SQL> desc acc_cust Name Null? Type ------------------------------------------- -------- ------------------------------------------------ SNO NUMBER SNAME VARCHAR2(10) ADDRESS VARCHAR2(30) |
DBA_TAB_COLUMNS :- with the help of dba_tab_columns view you can check that column ora_archive_state is added or not
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> select table_name, column_name, column_id, HIDDEN_COLUMN from dba_tab_cols where table_name = 'ACC_CUST'; TABLE_NAME COLUMN_NAME COLUMN_ID HID -------------------- -------------------- ---------- --- ACC_CUST ORA_ARCHIVE_STATE YES ACC_CUST SYS_NC00004$ YES ACC_CUST ADDRESS 3 NO ACC_CUST SNAME 2 NO ACC_CUST SNO 1 NO SQL> column ORA_ARCHIVE_STATE format a15 SQL> select ora_archive_state,sno,sname,address from acc_cust; ORA_ARCHIVE_STA SNO SNAME ADDRESS --------------- ---------- ---------- ------------------------------ 0 101 VINOD 19/45..... 0 102 AJAY 14/85... 0 103 SAI 16/58..... |
Above query fetched all row with ORA_ARCHIVE_STATE pseudo column. As we can see above command all values is 0, means all are current data or in other words active rows. With the help of this command you can distinguish between active data vs inactive data. The column ORA_ARCHIVE_STATE can have two value – either 0 or 1. When we insert any new rows or update any existing row in any table so row will be in active state. The column will be 0 as the row is in active state. When you stop accessing that rows or no one accessing/ updating it then you can convert that row values into non-active state means you can set row values 1.
Remember column values does not set automatically 1. We need to set it means either a DBA or data owner can manually set values into 1.
–>Let’s consider in table acc_cust SNO=101 data is inactive and don’t need any more so let’s make it inactive. To make this column inactive we need to execute below DML command.
|
1 2 |
SQL> update acc_cust set ORA_ARCHIVE_STATE=DBMS_ILM.ARCHIVESTATENAME(1) where sno =101; 1 row updated. |
The DBMS_ILM.ARCHIVESTATENAME function is use for update value of column ORA_ARCHIVE_STATE.
As we have successfully marked above row as inactive so let’s fetch data again from table to see that row is visible or not.
|
1 2 3 4 5 |
SQL> select ora_archive_state,sno,sname,address from acc_cust; ORA_ARCHIVE_STA SNO SNAME ADDRESS --------------- ---------- ---------- ------------------------------ 0 102 AJAY 14/85... 0 103 SAI 16/58..... |
Looking at output of query, as you can see above we are not able to see row which one we have marked as inactive.
Let’s think like if our business suddenly need all old data that we have marked as an inactive, to do that we need to execute below command.
|
1 2 |
SQL> alter session set row archival visibility=all; Session altered. |
Now, we can check again that row is visible again or all data is visible.
|
1 2 3 4 5 6 |
SQL> select ora_archive_state,sno,sname,address from acc_cust; ORA_ARCHIVE_STA SNO SNAME ADDRESS --------------- ---------- ---------- ------------------------------ 1 101 VINOD 19/45..... 0 102 AJAY 14/85... 0 103 SAI 16/58..... |
We can actually see above output that the ARCHIVE_STATE is set to 1 in first row and also it is visible to us means we can see all archival data also . To make column again in active state we need to execute below DML command
|
1 2 3 4 |
SQL> update acc_cust set ORA_ARCHIVE_STATE= DBMS_ILM.ARCHIVESTATENAME(0) where sno =101; 1 row updated. SQL> commit; Commit complete. |
To check row values again updated in active state or not. Fetch data from the table.
|
1 2 3 4 5 6 |
SQL> select ora_archive_state,sno,sname,address from acc_cust; ORA_ARCHIVE_STA SNO SNAME ADDRESS --------------- ---------- ---------- ------------------------------ 0 101 VINOD 19/45..... 0 102 AJAY 14/85... 0 103 SAI 16/58..... |
Remember if you will create a new table by using Create table as select (CTAS) then new table will contain both data archived as well as non-archived data means active and inactive data.
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Loading...
