Solved

Archiving tables

Posted on 2013-01-15
3
286 Views
Last Modified: 2013-02-15
I have been simply asked to archive two tables. This has been asked to me due to the change in roles and I haven't done archiving before.. There is not much information given as to archiving policy, retention peroid, whether the data needs to be available online or not etc

What are some of questions you would ask if you are assigned with this task..please provide me what you think is the good set of questions to ask..

Thanks
0
Comment
Question by:gs79
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 167 total points
ID: 38780678
>>what you think is the good set of questions to ask..

I think you covered them:  archiving policy, retention peroid, whether the data needs to be available online or not etc

I would also ask what is meant by 'archive'.  This could mean pull specific records from the tables or it could mean remove the tables all together.  I suspect it is archiving specific data from the tables.

The answer to that question depends on what specific steps you will need to take.

Assuming archiving data, I suggest you get familiar with the tables and any associated constraints like foreign keys and if these keys cascade deletes.

This will tell you what additional data you need to preserve when you delete from the main tables.

For example: an employee table might have foreign keys to a job_history table.  If you delete an employee, it might automatically delete job_history records.  If you only save off employee records, then delete the employees, when the time comes to recover an employee, you will be missing all the history records if you did not also preserve the history records.
0
 
LVL 4

Assisted Solution

by:tvedtem
tvedtem earned 167 total points
ID: 38781133
The above answer is 100% correct & acceptable.

I'd get something heavy and clonk whoever asked you on the head.
Being left to do that if you're not a DBA (and, more importantly, if you're not familiar with the schema) is not really fair or wise.

I would suggest that you get familiar with the schema before you dare even touch it.
Find the person who used to do this before you and ask them to explain the DB design to you, then ask them whether it's safe to do what's been requested + how they would do it.

If that person is not available, have a stuff drink.  Then make sure the database has been backed up recently.  If it hasn't, do not touch it, and resolve that first.

Cascading deletes, in particular, have the potential to ruin your day...

Finally, if for whatever reason you have no luck with any of the above, I would archive just one row of each type first, then test what happens.  Only when you are very sure should you go removing large volumes of data.
0
 
LVL 15

Assisted Solution

by:Devinder Singh Virdi
Devinder Singh Virdi earned 166 total points
ID: 38794955
DBAs maintains Data but not Business Rules.
Archive policies should come from management and before doing this you should have clear picture what is needed. Sometime archiving means = Purging.

If this is the first time on going process, I would suggest you to create stored/some scripts. i.e. lets say you have two tables as you mentioned. Retention period of 1 is one year and another table hold data of first table for another year. Which means you have 1 year in first table and 2 year in another table.
In this case, I would preferred to make monthly partition of each tables. Convert all Global indexes to local IF NEEDED and do purging/archiving as per your policy.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question