| Archival of Production Database [message #123521] |
Mon, 13 June 2005 17:41  |
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  |
 |
Mahesh Rajendran
Messages: 10708
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 |
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!
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
