Skip to Main Content
  • Questions
  • Archive Table data and restore when needed

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Vickey.

Asked: August 27, 2018 - 11:28 am UTC

Last updated: November 26, 2018 - 5:03 pm UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

We have a table named DS_AUDIT_ENTRY where logs of audits are recorded. It contains many years data.
Now we want to keep 6 months data only and archive old data. Also if required we have to fetch the old data whenever asked. How can I perform this activity?

What we have tried is below :

1. we created a new empty table with name 'ds_audit_entry_old1' in a new schema.
2. swapped the existing table (ds_audit_entry <> ds_audit_entry_old1) with the above one
3. Now the ds_audit_entry_old1 is populated with the last ~6 months of audit logs
4. Archived ds_audit_entry_old1.
5. Ds_audit_entry table now contains 6 months old data.

Now we want to restore the data before 6 months in the table along with the current data.
How to do that?
-----------------------------------------------------------------------
Table Creation:

DS_AUDIT_ENTRY

CREATE TABLE "UCDUSER"."DS_AUDIT_ENTRY" 
   ( "ID" VARCHAR2(36 BYTE) NOT NULL ENABLE, 
 "VERSION" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE, 
 "USER_ID" VARCHAR2(64 BYTE), 
 "USER_NAME" VARCHAR2(255 BYTE), 
 "EVENT_TYPE" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
 "DESCRIPTION" VARCHAR2(255 BYTE), 
 "OBJ_TYPE" VARCHAR2(255 BYTE), 
 "OBJ_NAME" VARCHAR2(255 BYTE), 
 "OBJ_ID" VARCHAR2(255 BYTE), 
 "CREATED_DATE" NUMBER(*,0) NOT NULL ENABLE, 
 "STATUS" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
 "DELETABLE" VARCHAR2(1 BYTE) DEFAULT 'Y', 
 "IP_ADDRESS" VARCHAR2(40 BYTE), 
  PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "UCD_TESTCASE"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "UCD_TESTCASE" ;

DS_AUDIT_ENTRY_OLD1


 CREATE TABLE "ARCIVER"."DS_AUDIT_ENTRY_OLD1" 
   ( "ID" VARCHAR2(36 BYTE) NOT NULL ENABLE, 
 "VERSION" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE, 
 "USER_ID" VARCHAR2(64 BYTE), 
 "USER_NAME" VARCHAR2(255 BYTE), 
 "EVENT_TYPE" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
 "DESCRIPTION" VARCHAR2(255 BYTE), 
 "OBJ_TYPE" VARCHAR2(255 BYTE), 
 "OBJ_NAME" VARCHAR2(255 BYTE), 
 "OBJ_ID" VARCHAR2(255 BYTE), 
 "CREATED_DATE" NUMBER(*,0) NOT NULL ENABLE, 
 "STATUS" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
 "DELETABLE" VARCHAR2(1 BYTE) DEFAULT 'Y', 
 "IP_ADDRESS" VARCHAR2(40 BYTE), 
  PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TEST_ARCHIVAL"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TEST_ARCHIVAL" ;


----------------------------------------------------
Swapping the data in DS_AUDIT_ENTRY_OLD1:

insert into DS_AUDIT_ENTRY_OLD1
select * from DS_AUDIT_ENTRY;

and Chris said...

Let's start from the beginning:

Why do you want to archive data older than 6 months?

And what precisely does "fetch the old data whenever asked" mean?

Often archiving means the data only exists in a backup. So to get it you have to restore the database, then run the query. If you rarely query the old data and only need it for compliance reasons this is often "good enough".

If you'll often query old archived rows this method is impractical. Transferring rows to an archive table is another way (which is my guess at what you're currently doing).

You can easily get the complete picture by unioning the two together:

select * from DS_AUDIT_ENTRY
union all
select * from DS_AUDIT_ENTRY_OLD1


Partitioning can also help here. This makes it easy to copy data from the current to old table. And remove it from the current with something along the lines of:

insert into DS_AUDIT_ENTRY_OLD1
  select * from DS_AUDIT_ENTRY partition ( pold );

alter table DS_AUDIT_ENTRY drop partition pold;


Of course, with partitioning you may not need to archive the old data at all... But to really help here, we need to understand your goals.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Reply

Vickey Panjiyar, August 28, 2018 - 11:12 am UTC

DS_AUDIT_ENTRY table is used by an application in Production env where the logs are shown in the application console.

We are trying to move the data to archive so as to increase the performance of the application as data in the table is very huge.


By "fetch the old data whenever asked" I mean that if customer in future says that they need their data back in production system then how can we do that?
Chris Saxon
August 28, 2018 - 2:18 pm UTC

The point is people rarely query audit data years old. Usually you only need it in response to a request from auditors. These are one-off demands that you have a few days to answer.

So to save you the baggage of storing all the data in your production database, you delete it. If there is a request for it, you restore from a backup taken before the delete.

This requires your DBAs to recover the data. Which can be time-consuming.

If you want something more on-demand, you may be better off looking at partitioning. This keeps all the data in the original table. But logically splits it into smaller tables, e.g. one for each month.

You can learn more about partitioning in the following guide:

https://asktom.oracle.com/partitioning-for-developers.htm

But in any case, to choose the best archiving strategy, you need to know:

- Who will query this data
- Why they need to view it
- How frequently they need access

Reply

Vickey Panjiyar, August 28, 2018 - 2:35 pm UTC

Thanks for the reply.
Ok so, we can try the below approach:?

1. First DB Team will take a backup of the table DS_AUDIT_ENTRY, say today we have taken a backup of the table which contains 6 months audit logs.
2. After backup we will delete all the data from DS_AUDIT_ENTRY .
3. Now DS_AUDIT_ENTRY is empty, it will store current data.
4. After 2 months (now we have 6+2, 8 months of records) ,if the request comes to retrieve old data(say 5 months back), then we will take again backup of the current table and restore the table to the earlier backup.
5. After the request is fulfilled, we will again restore the table to new backup (which contains 2 months data).

Is this possible?
Chris Saxon
August 28, 2018 - 2:49 pm UTC

I'd modify that slightly to:

1. The DBA team backs up the whole database as normal as part of their daily backups. The tape for this is marked to keep for archiving.
2. You have a regular job to delete data older than 6 months. So DS_AUDIT_ENTRY always has 6 months of data in it + a few days.
3. If a request comes in to view data older than 6 months, the DBA team restores the database to a separate machine. Then someone on the DB team runs the query to get the data. You leave current data in the DS_AUDIT_ENTRY as-is.
4. Once the data request is dealt with, you drop the restored database.

But this is a process you need to validate with your business. Ask them why they need this audit data. If this is for regulatory reasons, the regulations may define how your archiving process works.


Reply

Vickey Panjiyar, August 28, 2018 - 2:54 pm UTC

The thing is data should reflect in application console. So it has to be taken from the same database and table.

Chris Saxon
August 28, 2018 - 4:49 pm UTC

Consider partitioning the table by date. Provided you're searching for audit records in a given timeframe, this will help performance. And make it easier to delete old rows should you decide to do this at some point.

To learn more, read the link I provided above.

Reply

Vickey, November 15, 2018 - 12:28 pm UTC

Hi Thanks for the reply.

We want now to automate the process of copying the data from original table to new table where audit logs will be copied after every 3 months or so and original table will contain recent 1 month data.


How can we achieve this?

What script shud we create?

Can we run a cron job ?


Chris Saxon
November 15, 2018 - 5:36 pm UTC

It depends.

What's the script for your tables? Did you partition it?

Reply

Vickey, November 17, 2018 - 5:22 pm UTC

The same table that I have posted in my Question. We will be creating a new table with same properties and columns and will copy the data from original table to the new table.

This entire process we want to automate so that it should run after every 3 months and copy the data from original table to the new table and remove the data from original table leaving one month of data in it.


It will be helpful for me if you can guide me to do that..
Chris Saxon
November 26, 2018 - 5:03 pm UTC

If you've partitioned the source table, it's easy & fast:

insert into copy_tab
  select * from orig_tab partition ( ... );

alter table orig_tab drop partition ( ... );


If not, replace the drop partition with a delete from the orig_tab. And add a suitable where clause to the insert-select.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.