Auto Maintain Archive, Purge & Purge on Source Table in Oracle

We are developing many application where we are auditing, archiving and purging data also for many tables. Most of the application will use Water Flow SDLC for development. While modifying main table, we also need to maintain archival and purge objects.
There many places where we need to archive child table also if we are archiving parent table based on foreign key relationship with tables.
We are also using configuration table to maintain archival and purge period for different tables and also entering different key if we want to maintain archival or purge with different period.
Here, we tried to automate all above scenario. We will go through all above scenario one by one and their configuration. Before that we need to configure some objects. We need to create below objects to configure it. (Oracle Configure Archival Process.sql)
Tables

1. Error_Log (Check blog http://www.sqlscientist.com/2013/07/error-logging-and-alerts-in-oracle.html)
   
2. Config (Check blog http://www.sqlscientist.com/2013/07/error-logging-and-alerts-in-oracle.html)
   
3. Archive_Purge_Config
   
   1. Columns
      1. is_archive
         1. 1 = Archive is enable from ERROR_LOG to ERROR_LOG_ARCHIVE.
         2. 0 = Archive is disable.
      2. archive_column_name
         1. a. Archive is happening based on this datetime column.
      3. archive_day
         1. Archive is happening based on configured days in this column.
      4. archive_rule_sql
         1. Archive and Purge will happened on filtered records configured in this rule.
         2. NULL – It will happened to whole table.
      5. is_purge
         1. 1 = Purge is enable from ERROR_LOG_ARCHIVE.
         2. 0 = Purge is disable.
      6. purge_days
         1. Purge is happening based on configured days in this column.
      7. is_purge_on_source
         1. 1 = Purge is enable from ERROR_LOG. In this case, It will ignore values in is_archive and is_purge columns.
         2. 0 = Purge is disable.
      8. is_active
         1. 1 = Record is active.
         2. 0 = Record is inactive.

Procedures

1. cp_archive_purge
2. cp_error_log_alert (Check blog http://www.sqlscientist.com/2013/07/error-logging-and-alerts-in-oracle.html)

Jobs

1. JOB_ARCHIVE_PURGE
   1. Execute procedure cp_archive_purge daily once at 1:00 AM.
2. JOB_ERROR_LOG_ALERT (Check blog http://www.sqlscientist.com/2013/07/error-logging-and-alerts-in-oracle.html)

Examples

To understand this scenario, I have taken Employee and Department example. Below is E-R Diagram for that. Below are the details of tables. (Oracle Configure Archival Process Employee & Department tables.sql)

1. Department
   
   1. There may be many departments in database.
2. Employee
   
   1. There are many employees for each departments
3. Contact
   
   1. For each employee, there may be many contact details.
4. Address
   
   1. For each employee, there may be many address details.
5. AddressDetails
   
   1. For each address, there may or may not be additional address details.

Now, our requirement is to archive and purge on Department & Error_Log tables which also should archive it’s child tables if exist based on relationship created between them. Below is configuration required in dbo.Archive_Purge_Config table to archive that. We also need to add config_key “Archive_Purge_Max_Queue_Size” in dbo.Config table. It will used to setup batch size while deleting records from table inside procedure. (Configure Archival Process Data.sql)

1. Department – It will archive 30 days in Department_Archive table and purge(permanently) on 30 days from Department_Archive table.
   
2. Error_Log - It will archive 30 days in Error_Log_Archive table and purge(permanently) on 30 days from Error_Log_Archive table.

Once job dbo.job_archive_purge will execute, it will create new _Archive tables for each tables configured in dbo.archive_purge_config if it’s not exists with their child relationship tables. Below are the list of archive tables will be created by dbo.cp_archive_purge procedure.

1. Department_archive
2. Contact_Archive
3. Employee_Archive
4. Address_Archive
5. AddressDetail_Archive
6. Error_Log_Archive

Now, we will talk about our daily changes on tables. We can have 3 types of changes in our tables. We will talk about each one by one.

1. New Column

I want to add one more column “DepartmentType varchar(5)” in dbo.Department table. Once job dbo.job_archive_purge will execute, it will also add column in _Archive table. Below is screenshot of Department_Archive table after job execution.

Query:
ALTER TABLE DEPARTMENT ADD DEPARTMENTTYPE VARCHAR2(5)

2. Rename Column

I want to rename column “AddressDetail” to “Address1” in dbo.AddressDetail table. Once job dbo.job_archive_purge will execute, it will drop old column and create new column in _Archive table. Below is screenshot of AddressDetail_Archive table after job execution.

Query:
ALTER TABLE ADDRESSDETAIL RENAME COLUMN ADDRESSDETAIL TO ADDRESS1;

3. Drop Column

If we extra column in archive table, it will not break archive process so we are not dropping extra columns for each run. Procedure will drop extra columns from _Archive table with any other fixes for that table like we did Rename Column as above.

4. Data Type Change of Column

After adding column “DepartmentType” in dbo.Department table, I realize that it should be varchar(10). While doing this conversion, we should make sure that _Archive column should be convertible with new data type. If it’s require data fixes then do that first before execution of job. To test this scenario we should have some data to archive. (Oracle Configure Archival Process Employee & Department tables Data.sql)

Query:
ALTER TABLE DEPARTMENT MODIFY DEPARTMENTTYPE VARCHAR2(10);

Summary

1. Archive Process can support below features.
   
   1. Auto create archive table.
   2. Auto create and delete additional columns on archive table.
   3. Auto modify data type of column.
2. Archive Process can create new column without any constraint.
   
3. Archive table will be created with one more column “archive_created_date” with default getdate().
   
4. Archive feature with their child tables.
   
5. Purge on archive table based on days setup in dbo.Archive_Purge_Config table.
   
6. Purge from Source directly based on days setup in dbo.Archive_Purge_Config table.

Below are the scripts to download.

1. Oracle Configure Archival Process.sql
   
2. Oracle Configure Archival Process Employee & Department tables.sql
   
3. Oracle Configure Archival Process Data.sql
   
4. Oracle Configure Archival Process Employee & Department tables Data.sql

Error Logging and Alerts in Oracle

Error Logging is main part of our database application. I am trying to share some sample code which I am using in my daily life.
We need to follow below steps.
1) Create Error Log Table.
Table: Error_Log
Columns:

1. Log_ID
   1. Identity column.
2. HOST_NAME
   1. Host Name.
3. SERVICE_NAME
   1. Service Name.
4. USER_NAME
   1. User Name.
5. PROC_NAME
   1. Procedure Name.
6. SQL_CODE
   1. Error Code from Procedure.
7. SQL_ERRM
   1. Error Message from Procedure.
8. ERROR_BACKTRACE
   1. Error Line Number from Procedure.
9. Created_Date
   1. Default current date from database.

2) Create Sequence for Error_Log Table.
3) Create trigger on Error Log table to set sequence to Log_ID on insert.
4) Create Error Log Procedure
5) Create Procedure which used Error Log Procedure in 3rd Step.
6) Execute Parent Procedure. Below is error output from Parent Procedure.

7) Check logs in Error Log table.

8) Create Config Table.
Table: Error_Log
Columns:

1. CONFIG_ID
   1. Identity column
2. CONFIG_KEY
   1. Config Key
3. CONFIG_VALUE
   1. Config Value
4. IS_ACTIVE
   1. 1 = Config Key is active
   2. 0 = Config Key is inactive
5. CREATED_DATE
   1. Default current date from database.
6. MODIFIED_DATE

9) Create Sequence for Config Table.
10) Create trigger on Config table to set sequence to Config_ID on insert.
11) Insert mail configuration data into Config Table.

1. Mail_Smtp_Host
2. Mail_From
3. Mail_To
4. Mail_Subject_Error_Log_Alert

12) Create string array type.
13) Create function split
14) Create Procedure to send mails.
15) Create Procedure to send alert mails.
16) Create job to send error log alert daily once at 7:00 A.M.
17) Execute job to send alert mail. Below is alert mail.
Below are the scripts to download.

1. Oracle Error Logging and Alerts.sql

Note

1. Before executing attached script replace your email id.
2. You should have ACL created on your schema. (Check http://www.sqlscientist.com/2013/07/how-to-create-aclaccess-control-list-in.html)

Auto Maintain Archive, Purge & Purge on Source Table in SQL Server

We are developing many application where we are auditing, archiving and purging data also for many tables. Most of the application will use Water Flow SDLC for development. While modifying main table, we also need to maintain archival and purge objects.

There many places where we need to archive child table also if we are archiving parent table based on foreign key relationship with tables.

We are also using configuration table to maintain archival and purge period for different tables and also entering different key if we want to maintain archival or purge with different period.

Here, we tried to automate all above scenario. We will go through all above scenario one by one and their configuration. Before that we need to configure some objects. We need to create below objects to configure it. (Configure Archival Process.sql)

Tables

1. Error_Log (Check blog http://www.sqlscientist.com/2013/07/error-logging-and-alerts.html)
2. Config (Check blog http://www.sqlscientist.com/2013/07/error-logging-and-alerts.html)
   
3. Archive_Purge_Config
   
   1. Columns
      1. is_archive
         1. 1 = Archive is enable from ERROR_LOG to ERROR_LOG_ARCHIVE.
         2. 0 = Archive is disable.
      2. archive_column_name
         1. a. Archive is happening based on this datetime column.
      3. archive_day
         1. Archive is happening based on configured days in this column.
      4. archive_rule_sql
         1. Archive and Purge will happened on filtered records configured in this rule.
         2. NULL – It will happened to whole table.
      5. is_purge
         1. 1 = Purge is enable from ERROR_LOG_ARCHIVE.
         2. 0 = Purge is disable.
      6. purge_days
         1. Purge is happening based on configured days in this column.
      7. is_purge_on_source
         1. 1 = Purge is enable from ERROR_LOG. In this case, It will ignore values in is_archive and is_purge columns.
         2. 0 = Purge is disable.
      8. is_active
         1. 1 = Record is active.
         2. 0 = Record is inactive.

Procedures

1. cp_archive_purge
2. cp_error_log_alert (Check blog http://www.sqlscientist.com/2013/07/error-logging-and-alerts.html)

Jobs

1. DatabaseName.dbo.job_archive_purge
   1. Name : DatabaseName.dbo.job_archive_purge
   2. Description: This job is used to archive, purge and purge on source table as per configuration in dbo.ARCHIVE_PURGE_CONFIG table.
   3. Steps
      1. Name: Step 1
      2. Type: Transact-SQL script(T-SQL)
      3. Command: exec dbo.cp_archive_purge
   4. Schedules
      1. Name: Occurs every day at 1:00:00 AM.
      2. Schedule type: Recurring
      3. Occurs: Daily
      4. Occurs once at: 1:00:00 AM
      5. Start Date: Today’s Date
2. DatabaseName.dbo.job_error_log_alert (Check blog http://www.sqlscientist.com/2013/07/error-logging-and-alerts.html)

Examples

To understand this scenario, I have taken Employee and Department example. Below is E-R Diagram for that. Below are the details of tables. (Configure Archival Process Employee & Department tables.sql)

1. Department
   1. There may be many departments in database.
2. Employee
   1. There are many employees for each departments
3. Contact
   1. For each employee, there may be many contact details.
4. Address
   1. For each employee, there may be many address details.
5. AddressDetails
   1. For each address, there may or may not be additional address details.

Now, our requirement is to archive and purge on Department & Error_Log tables which also should archive it’s child tables if exist based on relationship created between them. Below is configuration required in dbo.Archive_Purge_Config table to archive that. We also need to add config_key “Archive_Purge_Max_Queue_Size” in dbo.Config table. It will used to setup batch size while deleting records from table inside procedure. (Configure Archival Process Data.sql)

1. Department – It will archive 30 days in Department_Archive table and purge(permanently) on 30 days from Department_Archive table.
2. Error_Log - It will archive 30 days in Error_Log_Archive table and purge(permanently) on 30 days from Error_Log_Archive table.

Once job dbo.job_archive_purge will execute, it will create new _Archive tables for each tables configured in dbo.archive_purge_config if it’s not exists with their child relationship tables. Below are the list of archive tables will be created by dbo.cp_archive_purge procedure.

1. Department_archive
2. Contact_Archive
3. Employee_Archive
4. Address_Archive
5. AddressDetail_Archive
6. Error_Log_Archive

Now, we will talk about our daily changes on tables. We can have 3 types of changes in our tables. We will talk about each one by one.

1. New Column

I want to add one more column “DepartmentType varchar(5)” in dbo.Department table. Once job dbo.job_archive_purge will execute, it will also add column in _Archive table. Below is screenshot of Department_Archive table after job execution.

Query:

alter table dbo.department add DepartmentType varchar(5)

2. Rename Column

I want to rename column “AddressDetail” to “Address1” in dbo.AddressDetail table. Once job dbo.job_archive_purge will execute, it will drop old column and create new column in _Archive table. Below is screenshot of AddressDetail_Archive table after job execution.

Query:

sp_rename 'AddressDetail.AddressDetail' , 'Address1', 'column'

3. Drop Column

If we extra column in archive table, it will not break archive process so we are not dropping extra columns for each run. Procedure will drop extra columns from _Archive table with any other fixes for that table like we did Rename Column as above.

4. Data Type Change of Column

After adding column “DepartmentType” in dbo.Department table, I realize that it should be varchar(10). While doing this conversion, we should make sure that _Archive column should be convertible with new data type. If it’s require data fixes then do that first before execution of job. To test this scenario we should have some data to archive. (

Query:

alter table dbo.department alter column DepartmentType varchar(10)

Summary

1. Archive Process can support below features.
   1. Auto create archive table.
   2. Auto create and delete additional columns on archive table.
   3. Auto modify data type of column.
2. Archive Process can create new column without any constraint.
   
3. Archive table will be created with one more column “archive_created_date” with default getdate().
   
4. Archive feature with their child tables.
   
5. Purge on archive table based on days setup in dbo.Archive_Purge_Config table.
   
6. Purge from Source directly based on days setup in dbo.Archive_Purge_Config table.

Below are the scripts to download.

1. Configure Archival Process.sql
2. Configure Archival Process Employee & Department tables.sql
3. Configure Archival Process Data.sql
4. Configure Archival Process Employee & Department tables Data.sql

How to create ACL(Access Control List) in Oracle

Below are some steps to create ACL in Oracle. Please change UserName in script with your own UserName for database.

Script:

begin

        dbms_network_acl_admin.create_acl (

                acl             => 'utlpkg3.xml',

                description     => 'Normal Access',

                principal       => 'UserName',

                is_grant        => TRUE,

                privilege       => 'connect',

                start_date      => null,

                end_date        => null

        );

end;

/

begin

  dbms_network_acl_admin.add_privilege (

  acl                         => 'utlpkg3.xml',

  principal             => 'UserName',

  is_grant              => TRUE,

  privilege             => 'connect',

  start_date         => null,

  end_date          => null);

end;

/

begin

  dbms_network_acl_admin.assign_acl (

  acl => 'utlpkg3.xml',

  host => 'localhost',

  lower_port => 22,

  upper_port => 55);

end;

/

commit;

/

Sample Script:

Add your email address before executing script.

declare

  l_mail_conn   UTL_SMTP.connection;

begin

  l_mail_conn := UTL_SMTP.open_connection('localhost', 25);

  UTL_SMTP.helo(l_mail_conn, 'localhost');

  UTL_SMTP.mail(l_mail_conn, ' Your email address');

  UTL_SMTP.rcpt(l_mail_conn, ' Your email address ');

  UTL_SMTP.data(l_mail_conn, 'This is a test message.' || UTL_TCP.crlf || UTL_TCP.crlf);

  UTL_SMTP.quit(l_mail_conn);

end;

/

Below is error if ACL is missing.

ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.UTL_TCP”, line 17
ORA-06512: at “SYS.UTL_TCP”, line 246
ORA-06512: at “SYS.UTL_SMTP”, line 115
ORA-06512: at “SYS.UTL_SMTP”, line 138
ORA-06512: at “XXX”, line 36
ORA-06512: at line 1

Compare collation from SQL Server 2000(source) table to 2008(target) view and create script to cast data type in View

We are always upgrading from lower version to highest version and also changing approaches for new implementation. There may be chances that data type mismatched from our old version(2000) to new version(2008).
Here, we had requirement to compare collation from SQL Server 2000(source) table to 2008(target) view and create script to cast data type in View. I have tried to create script which will use of OPENROWSET command to read system tables from SOURCE database and compare it with TARGET database. This script will return difference from SOURCE to TARGET tables which are exists in TARGET database. We are doing that because it’s possible that we don’t want some table from older version. This script also provides cast prefix and suffix for TARGET database to sync views with SOURCE database.
Below is script to download.
Compare Collation from Source To Target.sql

Error Logging and Alerts in SQL Server

Error Logging is main part of our database application. I am trying to share some sample code which I am using in my daily life.
We need to follow below steps.
1) Create database.
2) Create Error Log Table.
Table: Error_Log
Columns:

1. Log_ID
   1. Identity column.
2. Database_Name
   1. Database Name.
3. Proc_Name
   1. Procedure Name.
4. Err_Number
   1. Error Number from Procedure.
5. Err_State
   1. Error Stage from Procedure.
6. Err_Message
   1. Error Message from Procedure.
7. Err_Line
   1. Error Line from Procedure.
8. Created_Date
   1. Default current date from database.

3) Create Error Log Procedure.
4) Create Procedure with error which used Error Log Procedure in 3rd Step.
5) Execute Parent Procedure. Below is error output from Parent Procedure.
66) Check logs in Error Log table.

7) Create Config Table. Add below entry for Mail Configuration.

1. Mail_From
2. Mail_Subject_Error_Log_Alert
3. Mail_To

8) Create Procedure to send alert mails.
9) Create Job to execute Procedure to send alert mails. Below is alert mail.

Below are the scripts to download.
Error Logging and Alerts.sql