Archiving - A generic solution - Part 6 - Deploy and Test

Part 1 is to create the Archive Schema and its objects. I have created a sample HR schema and its objects in that post. 

Also the github link for the same has been provided in the post below. 

Archiving: A generic solution - Part 1

Alternatively you can download the HR schema and the deploy scripts from the link below

Download link for database archive scripts
deploy_archive_schema_objects.zip 

HR_schema.zip

Once all the scripts from Part 2 to Part 6 are saved in local folder on your machine you are ready to deploy and test the same.

installation_script.sql

--1_, 2_ and 3_ to be run as DBA user.
--\1_ddl_cre_archive_tablespace.sql -- this script is optional. You can use an existing tablespace.
--\2_ddl_cre_indx_tablespace.sql -- this script is optional. You can use an existing tablespace.
@\3_ddl_cre_archive_user.sql

--hr_archive_grants.sql for grants to be run in schema from which data is to be archived.
@\hr_archive_grants.sql

--Below scrtipts to be run in HR_ARCHIVE schema.
@\4_ddl_cre_archive_type.sql
@\5_ddl_cre_archive_sequences.sql
@\6_ddl_cre_archive_tables.sql
@\7_ddl_cre_archive_indexes.sql
@\8_ddl_cre_archive_constraints.sql
@\9_ddl_cre_archive_triggers.sql
@\10_ddl_cre_archive_functions.sql
@\11_ddl_cre_archive_packages.sql
@\12_ddl_cre_archive_procedures.sql

After all components are deployed you should have all objects created. Output is as follows.

--Create the archive schema user HR_ARCHIVE with the requuired grants
--user HR_ARCHIVE dropped.
user HR_ARCHIVE created.
GRANT succeeded.
GRANT succeeded.
GRANT succeeded.
GRANT succeeded.
GRANT succeeded.
GRANT succeeded.
GRANT succeeded.
user HR_ARCHIVE altered.
user HR_ARCHIVE altered.
GRANT succeeded.
GRANT succeeded.

--Grants of HR schema objects to HR_ARCHIVE
GRANT succeeded.
GRANT succeeded.

--Compile the type objects
TYPE TAB_COL_DTLS compiled
TYPE TYPE_REF_STR compiled
TYPE TYP_REC_TABLE_KEY_COLS compiled
TYPE TYP_REF_DATE compiled
TYPE TYP_REF_NUMBER compiled
TYPE TYP_REF_STR compiled
TYPE TYP_TBL_TABLE_KEY_COLS compiled

--Create the required sequences
sequence ARCHIVE_LOG_RULE_ID created.
sequence SEQ_ARCHIVE_BATCH_ID created.
sequence SEQ_ARCHIVE_LOG_DETAILS_ID created.
sequence SEQ_ARCHIVE_LOG_EXCEPTIONS_ID created.
sequence SEQ_ARCHIVE_LOG_HEADER_ID created.
sequence SEQ_ARCHIVE_LOG_ID created.
sequence SEQ_TEMP_ARCHIVE_ERROR_ID created.

--Create the required tables
table ARCHIVE_CONFIG created.
table ARCHIVE_ERROR_LOG created.
table ARCHIVE_LOG_EXCEPTIONS created.
table ARCHIVE_LOG_HEADER created.
table ARCHIVE_LOG_MAIN created.
table ARCHIVE_PK_DTLS created.
table ARCHIVE_RULES created.
table EMPLOYEES created.
table JOB_HISTORY created.

--Create the table indexes
index IDX_ARCHIVE_ERROR_LOG created.
index IDX_ARCHIVE_ERROR_LOG_1 created.
unique index PK_ARCHIVE_BATCH_ID created.
unique index PK_ARCHIVE_LOG_HEADER_ID created.
unique index PK_ARCHIVE_LOG_ID created.
unique index PK_EXCEPTION_DETAILS_ID created.
unique index PK_RULE_CD created.
unique index EMP_EMP_ID_PK created.
unique index JHIST_EMP_ID_ST_DATE_PK created.

--Create the table constraints
table ARCHIVE_ERROR_LOG altered.
table ARCHIVE_LOG_EXCEPTIONS altered.
table ARCHIVE_LOG_HEADER altered.
table ARCHIVE_LOG_MAIN altered.
table EMPLOYEES altered.
table EMPLOYEES altered.
table EMPLOYEES altered.
table EMPLOYEES altered.
table EMPLOYEES altered.
table JOB_HISTORY altered.
table JOB_HISTORY altered.
table JOB_HISTORY altered.
table JOB_HISTORY altered.
table JOB_HISTORY altered.
table ARCHIVE_RULES altered.

--Create the table triggers
TRIGGER TRG_UPDT_ARCHIVE_ERROR_LOG compiled
trigger TRG_UPDT_ARCHIVE_ERROR_LOG altered.
TRIGGER TRG_UPDT_ARCHIVE_LOG_EXCEPTIONS compiled
trigger TRG_UPDT_ARCHIVE_LOG_EXCEPTIONS altered.
TRIGGER TRG_UPDT_ARCHIVE_LOG_MAIN compiled
trigger TRG_UPDT_ARCHIVE_LOG_MAIN altered.
TRIGGER TRG_UPDT_ARCHIVE_PK_DTLS compiled
trigger TRG_UPDT_ARCHIVE_PK_DTLS altered.

--Create the required functions
FUNCTION FXN_STR_TO_COLUMNS compiled
No Errors.

--Create the package
PACKAGE PKG_ARCHIVE compiled

--Create the required archive procedures
PROCEDURE PR_ARCHIVE_ERROR_LOG compiled
PROCEDURE PR_ARCHIVE_LOG_EXCEPTIONS compiled
PROCEDURE PR_UPD_ARCHIVE_BATCH_STATUS compiled
PROCEDURE PR_UPD_ARCHIVE_COUNT_N_STATUS compiled
PROCEDURE PR_INS_PK_DTLS compiled
PROCEDURE PR_INSERT_ARCHIVE_PK_DATA compiled
PROCEDURE PR_ARCHIVE_RULE_PROCESS compiled
PROCEDURE PR_ARCHIVE_TABLE_INSRT compiled
PROCEDURE PR_ARCHIVE_TABLE_DATA compiled
PROCEDURE PR_ARCHIVE_PROCESS compiled
PROCEDURE PR_ARCHIVE_PENDING compiled
PROCEDURE PR_GENERATE_ALH_QUERY_STR compiled
PROCEDURE PR_INSERT_ARCHIVE_LOG_HEADER compiled
PROCEDURE PR_INSERT_ARCHIVE_LOG_MAIN compiled
PROCEDURE PR_ARCHIVE_MACHINE compiled

Sample scripts to insert data in ARCHIVE_CONFIG and ARCHIVE_RULES for testing purpose dml_insert_archive_config.sql

--------------------------------------------------------
--  File created - Friday-May-24-2024   
--------------------------------------------------------
REM INSERTING into ARCHIVE_CONFIG
SET DEFINE OFF;
DELETE FROM ARCHIVE_CONFIG;
Insert into ARCHIVE_CONFIG (TABLE_NAME,RECORD_FILTER_QUERY,ACTIVE_FLAG) values ('EMPLOYEES',null,'Y');
Insert into ARCHIVE_CONFIG (TABLE_NAME,RECORD_FILTER_QUERY,ACTIVE_FLAG) values ('JOB_HISTORY',null,'Y');
REM INSERTING into ARCHIVE_RULES
SET DEFINE OFF;
DELETE FROM ARCHIVE_RULES;
Insert into ARCHIVE_RULES (TXT_RULE_CD,NUM_RULE_PRIORITY,TXT_RULE_DESC,TXT_RULE_QUERY,TXT_ACTIVE_FLG,TXT_PRIMARY_TABLE,TXT_ARCHIVE_TABLE_LIST) values ('100002',1,'Archive employes job history upto 2005','SELECT rowid FROM HR.JOB_HISTORY WHERE START_DATE <= TO_DATE(''31-DEC-05'',''DD-MON-YY'')','Y','JOB_HISTORY','JOB_HISTORY');
Insert into ARCHIVE_RULES (TXT_RULE_CD,NUM_RULE_PRIORITY,TXT_RULE_DESC,TXT_RULE_QUERY,TXT_ACTIVE_FLG,TXT_PRIMARY_TABLE,TXT_ARCHIVE_TABLE_LIST) values ('100001',1,'Archive employes hired upto 2005','SELECT rowid FROM HR.EMPLOYEES WHERE HIRE_DATE <= TO_DATE(''31-DEC-05'',''DD-MON-YY'')','Y','EMPLOYEES','EMPLOYEES');

--dml_insert_archive_config.sql to be run in schema from which data is to be archived.

@\dml_insert_archive_config.sql

Before running the main archiving procedure following configuration points are to be noted.

README.txt

Entry in ARCHIVE_RULES and ARCHIVE_CONFIG tables as shown in sample example above
All main schema tables to be archived should have a primary key.
All constraints on the tables to be archived must be disabled. This is because for a delete operation on a table (after archive copy) having a foreign key constraint to another table, the delete operation will fail because child records will exist in the other table and you will get a constraint violation error. Once the archiving process is over the constraints can be enabled again.
Similar structure table should be created in the archive schema with 2 additional columns
ARCHIVE_LOG_HEADER_ID	NUMBER(20,0)
ARCHIVE_DATE	DATE
Grants to be given to main schema tables on ARCHIVE schema
 GRANT SELECT, DELETE ON HR.EMPLOYEES TO HR_ARCHIVE.EMPLOYEES;
Main schema and Archive schema to be mentioned in PKG_ARCHIVE
MAIN_SCHEMA     CONSTANT     VARCHAR2(100) := 'HR'; -- Change this as per you database main schema
ARCHIVE_SCHEMA  CONSTANT     VARCHAR2(100) := 'HR_ARCHIVE_TEST'; -- Change this as per you database archive schema

Once all deployemnt is completed you can start the archiving machine

BEGIN
  PR_ARCHIVE_MACHINE();
--COMMIT; 
END;

You can verify the status of the archiving as follows

SELECT COUNT(1) FROM HR_ARCHIVE.EMPLOYEES; --53 rows inserted.

SELECT COUNT(1) FROM HR_ARCHIVE.JOB_HISTORY; --6 rows inserted.

Corresponding number of rows would have been deleted from the main HR schema. 

You can verify this by taking a pre and post count of these tables before and after running PR_ARCHIVE_MACHINE procedure.

The status of the archiving batch run can be checked from the following the ARCHIVE_LOG_HEADER and ARCHIVE_LOG_MAIN tables as follows.

SELECT * FROM ARCHIVE_LOG_HEADER;

Output is as below. 

The output will give the status of archiving for each table, the number of records to be archived based on the given criteria, the number of records archived, the number of records failed archiving and the number of records pending for archiving.

NUM_ARCHIVE_LOG_HEADER_ID	NUM_ARCHIVE_BATCH_ID	TXT_RULE_CD	TXT_TABLE_NAME	TXT_TBL_ARCHIVAL_STATUS	NUM_RECORDS_TO_ARCHIVE	NUM_RECORDS_ARCHIVED	NUM_RECORDS_FAILED_ARCHIVAL	NUM_RECORDS_PENDING_ARCHIVAL	CRTD_BY	CRTD_DT	UPDTD_BY	UPDTD_DT
108006	107806	100001	EMPLOYEES	COMPLETE	53	53	0	0	SYSTEM	25-MAY-24	SYSTEM	25-MAY-24
108005	107805	100002	JOB_HISTORY	COMPLETE	6	6	0	0	SYSTEM	25-MAY-24	SYSTEM	25-MAY-24

SELECT * FROM ARCHIVE_LOG_MAIN;

The output will give the status of the archive batch and the archive date as below.

  

NUM_ARCHIVE_BATCH_ID	DAT_ARCHIVE_DATE	TXT_ARCHIVAL_STATUS	CRTD_BY	CRTD_DT	UPDTD_BY	UPDTD_DT
107805	25-MAY-24	COMPLETE	SYSTEM	25-MAY-24	SYSTEM	25-MAY-24
107806	25-MAY-24	COMPLETE	SYSTEM	25-MAY-24	SYSTEM	25-MAY-24

A few limitations of this archiving process are

Performance - Since this approach does row by row insert into archive table and delete from main table the process is very slow.

Primary Key - The approach requires every table to be archived to have a primary key. If possible and if time permits will check on removing this dependency.

Advantages

It is a light weight process and if your system is such that it is not an all or none type of requirement you can set a COMMIT_CNT variable in the package PKG_ARCHIVE.

i.e. - If for a given archive criteria there are 25000 records that will be archived. You can set a COMMIT_CNT of say 5000. So for every 5000 records the main table data will be deleted and moved into archive. If the process fails in between after 15000 records are archived it will not leave the database inconsistent.

Restartability - In the above scenario if the archive process fails after 15000 records are archived, you can restart the process and it will start archiving the remaining records

Thought for the day

Thank you Lord.