12c: IMPROVE BACKUP PERFORMANCE USING IN-DATABASE ARCHIVING

Database 12c, Uncategorized,

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:

Home

Database 12c Index

12c : In-Database Archiving
12c : Performance Issue with In-Database Archiving
12c: Solution To Performance Issue With In-database Archiving

 

———————————————————————————————

Your comments and suggestions are welcome!