Traditionally, the historical data needed for query purposes only was removed from the database and stored on tape, because
- Tape can store vast quantities of information for a very low cost.
- Huge amounts of old data will not be backed up again along with database backups.
This solution has major disadvantages such as:
- The data is not instantly available when needed.
- Reloading the data into the database from the tape is expensive and time-consuming.
In-Database Archiving stores operational and archival data in the same database so that the data is still online and available and the cost is only slightly higher than storing this information on tape.
– Data is partitioned to distinguish active data from those in archived state.
– The partitions containing historical data
. are compressed and located on low cost storage tiers.
. are identified as read-only so that subsequent backups are not required after the initial database backup.
Demonstration:
– Create two tablespaces
- active_tbs – to store active rows
- archived_tbs – to store archived rows
SQL> create tablespace active_tbs; create tablespace archived_tbs;
– Create table to store employees’ details
- Partition key is column ORA_ARCHIVE_STATE
- partition p_active – stores active rows (ora_archive_state = 0) in active_tbs tablespace in uncompressed format
- partition p_archived – stores archived rows (ora_archive_state <> 0 ) in archived_tbs tablespace in compressed format
SQL>create table emp (id number, name varchar2(30), retired char(1)) row archival partition by list(ora_archive_state) (partition p_active values(0) tablespace active_tbs, partition p_archived values (default) tablespace archived_tbs compress);
– Populate table –
SQL>begin
for i in 1..5 loop
insert into emp values (i, 'EMP'||i, 'N');
end loop;
for i in 6..10 loop
insert into emp values (i, 'EMP'||i, 'Y');
end loop;
end;
/
– Check that all the records are currently located in active_tbs as by default ora_archive_state=0
SQL> col name for a10
col retired for a7
col ora_archive_state for a15
select id, name, retired, ora_archive_state
from emp partition (p_active);
ID NAME RETIRED ORA_ARCHIVE_STA
———- ———- ——- —————
1 EMP1 N 0
2 EMP2 N 0
3 EMP3 N 0
4 EMP4 N 0
5 EMP5 N 0
6 EMP6 Y 0
7 EMP7 Y 0
8 EMP8 Y 0
9 EMP9 Y 0
10 EMP10 Y 0
10 rows selected.
– Try to archive the records of retired employees (retired = Y)
— We get error message as ora_archive_state is the partitining key and changing its value would cause rows to move from p_active to p_archived
SQL> update emp set ora_archive_state = 1 where retired='Y';
update emp set ora_archive_state = 1 where retired='Y'
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change
– Enable row movement and then archive the records of retired employees (retired = Y)
SQL> alter table emp enable row movement;
update emp set ora_archive_state = 1
where retired='Y';
5 rows updated.
– Check that partition p_active contains records of employees which have not retired (retired = ‘N’)
SQL> select id, name, retired, ora_archive_state from emp partition (p_active); ID NAME RETIRED ORA_ARCHIVE_STA ---------- ---------- ------- --------------- 1 EMP1 N 0 2 EMP2 N 0 3 EMP3 N 0 4 EMP4 N 0 5 EMP5 N 0
– Check if records of employees which have retired (retired = ‘Y’)have moved to partition p_archived
— No records are displayed because by default only active rows are displayed
SQL> select id, name, retired, ora_archive_state from emp partition (p_archived);
no rows selected
– Enable visibility of archived rows also
SQL> alter session set row archival visibility = all;
– Check that records of employees which have retired (retired = ‘Y’)have moved to partition p_archived
SQL> select id, name, retired, ora_archive_state from emp partition (p_archived); ID NAME RETIRED ORA_ARCHIVE_STA ---------- ---------- ------- --------------- 6 EMP6 Y 1 7 EMP7 Y 1 8 EMP8 Y 1 9 EMP9 Y 1 10 EMP10 Y 1
– Since archived records will be accessed for read only, change the tablespace archived_tbs to read only
SQL> alter tablespace archived_tbs read only;
-- Try to modify data in archived records – disallows as the tablespace is read only
SQL> update emp set name = 'emp6' where id = 6;
update emp set name = 'emp6' where id = 6
*
ERROR at line 1:
ORA-00372: file 12 cannot be modified at this time
ORA-01110: data file 12:
'+DATA/CDB1/E7CFA41503C950F1E0438602A8C06FD7/DATAFILE/archived_tbs.279.827852189'
– Backup tablespace archived_tbs after it has been made read only
RMAN> connect target sys/oracle@pdb1
backup tablespace archived_tbs;
-- Exclude archived_tbs from whole database backups
RMAN> connect target sys/oracle@cdb1
configure exclude for tablespace pdb1:archived_tbs;
Tablespace PDB1:ARCHIVED_TBS will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored
I hope information in this post was useful.
Your comments and suggestions are always welcome!
—————————————————————————————–
Related Links:
12c : In-Database Archiving
12c : Performance Issue with In-Database Archiving
12c: Solution To Performance Issue With In-database Archiving
———————————————————————————————