Home » RDBMS Server » Performance Tuning » Archival of Production Database
Archival of Production Database [message #123521] Mon, 13 June 2005 17:41 Go to next message
ppriya_r
Messages: 4
Registered: June 2005
Junior Member
Hi ,
My production DB( Oracle 8i) is growing enormously, n slows down so much, for I need to start with Archival now.

Req:
1)Data older thn 1 yr (from few tables alone)has to be backed up.
2)Backed up data from those tables have to be brought back to transaction upon request.

I suggest:
1) Create a new schema( IN THE SAME DB) with same table structures( for those tables to be backed up).
2) Create a new procedure(n script)tht runs automatically to load data from Prod. Schema to Archival schema ( in a regular interval). This script will also purge data from Prod. DB
3) Create a new procedure tht will take care of loading data back into Prod from Archival.

I go in for a new schema, as I donot want to load the prod DB more.

Is there a better way to do this? Has anyone performed a similar archival earlier? Is there a better way to archive( like partitioning a table, n taking it offline/online) if I use Oracle 9i?

AM not sure if Offline/Online will work in this case, as I have to load data into Archival at regular intervals.

Can anyone throw light on "Transparent Online Archival of Data?"



Re: Archival of Production Database [message #123525 is a reply to message #123521] Mon, 13 June 2005 18:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>My production DB( Oracle 8i) is growing enormously, n slows down so much
Is there any metrics involved here?
what is the rate_of_growth ? how did you measure?
rate_of_slowdown ? what exactly is this? Are your queries running slow or the insert is slow?. irrelevant of the size of database, we need to fix this first. Size of database matters mostly with administration( for example backup/regular maintainence) and in a few cases has nothing to do with performance.
Assuming there is a lot of activity in the database, are the tables/indexes analyzed and staticstics gathered regularly?

>>1)Data older thn 1 yr (from few tables alone)has to be backed up.
>>2)Backed up data from those tables have to be brought back to transaction upon request.

Is there a timestamp available in those tables?
Else we have to pick one fine day in future and stop the database operations , do the archiving and continue. How easy is bring back the data? to put them to work ?(performance, again your tables/indexes may be tripled or quadrapuled in size).

>>1) Create a new schema( IN THE SAME DB) with same table structures( for those tables to be backed up).
>>2) Create a new procedure(n script)tht runs automatically to load data from Prod. Schema to Archival schema ( in a regular interval). This script will also purge data from Prod. DB
>>3) Create a new procedure tht will take care of loading data back into Prod from Archiva

the archived data again resides in the same database.
So what makes the difference?

Please look into partitioning and transportable tablespaces.
Transportable tablespaces are cool and are like plug and play devices. You can attach and detach them within one or many (compatible, restrictions apply) databases.
Regarding the partitioning options, we can talk about it for another few days. There is SO much of it.
Read about, try it, analyze it and you will love it.
More information available in documentation. Please go through it.

Re: Archival of Production Database [message #123527 is a reply to message #123525] Mon, 13 June 2005 18:55 Go to previous messageGo to next message
ppriya_r
Messages: 4
Registered: June 2005
Junior Member
Sorry for I did not mention tht. Ther eis a time stamp in ALL tables.

Slowing down - Yes few queries run FOREVER.

Indexes/Statistics- Yes they are analyzed regularly , n maintained by the DBA in maintenenace window( every sunday)

Rate of growth- NO measure as such.. Just that the table grows tremendously.

Putting the data back- Not a big issue, as they are gonna request few specific records to be placed, not the whole bulk.

reason for new Schema- well evn I do not get it exactly( I understand tht processes and buffers are the same for both) . But, It was suggested by my DBA, not to add archival tables to current schema, as he felt, it is gonna hurt it more.

I cannot make my front end application get connected to diff DB's.( Production)I read somewhere, having diff. schema's in Oracle is more like having DB's in SQL. And at the same time, I cannot bring the tablesspace offline/online too.

Somehow, I felt my design was a round about way, and there has to be smarter way of doing things. Or DO u c anything tht might go wrong in this way of work?

Thanks for letting me know about transportable tablespaces.
I shall read in detail about them, and shall come up with further queries!



Re: Archival of Production Database [message #123529 is a reply to message #123525] Mon, 13 June 2005 19:05 Go to previous messageGo to next message
ppriya_r
Messages: 4
Registered: June 2005
Junior Member
n yes, jus now I went through "Transportable Tablespaces " . I presume, it is not possible to use them, if we need to perform a archival based on the data. ( Ex: Archive data older thn 1 yr)
Re: Archival of Production Database [message #123532 is a reply to message #123529] Mon, 13 June 2005 20:12 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
How about partitioning?
Seems that anyhow you are trying to simulate something like the same.
You can partition by month/year/week whatever you want.
Optionally, older partitions that are NOT active can be stored in a seperate tablespace and be compressed.

Re: Archival of Production Database [message #123533 is a reply to message #123532] Mon, 13 June 2005 20:16 Go to previous messageGo to next message
ppriya_r
Messages: 4
Registered: June 2005
Junior Member
Thank you.. I read about Partitioning after receiving your reply.
I presume that would be a better approach.

I checked this link.. tht gave more of insight: http://www.rittman.net/archives/000707.html

Again, thanks a lot. I shall get back in case I face any problems.
Re: Archival of Production Database [message #125028 is a reply to message #123521] Wed, 22 June 2005 17:06 Go to previous message
skempins
Messages: 16
Registered: June 2005
Location: Florida
Junior Member

Quote:

Can anyone throw light on "Transparent Online Archival of Data?"


Some food for thought, this paper from Oracle is pretty interesting:
http://www.oracle.com/technology/deploy/availability/pdf/ILM_on_Oracle_10g_TWP.pdf
Previous Topic: How to optimize this sql
Next Topic: Index for temporary table
Goto Forum:
  


Current Time: Sat Sep 19 00:13:39 CDT 2020