Question about Incrementa/cumulative exports
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Question about Incrementa/cumulative exports

  1. #1
    Join Date
    Feb 2001
    Posts
    99

    Question

    Okay, its time to change my logical backup scheme. To date, I have been doing Full exports every 6hours. Was not my choice of the way to do business, but was given the direction it be done that way. Now, it takes just over an hour to export and file size is currently at 1.6gig and climbing about a 8megs a day.

    I am looking at the examples of the complete/cumlative/incremental process and it looks like my answer, but have some questions.

    Table A has 300,000 rows. I do a complete backup. 6 hours later, I do an incremental backup. Table now has 310,000 rows. according to my Oracle DBA handbook, if I do an incremental or cumlative export, EVERY row of Table A will be exported, not just the new rows. So, from a time factor, I have not saved any time.

    Also, if every row is exported, what happens when I recover? If I import the complete backup, that gets me the inital 300,000. Then, when I import the cumlative, does it then only import the new 10,000 rows?

    At an estimate, i would say that less than 25% of the tables in my database rarely change. I have lots of tables, with some large tables, but mostly very small tables. The largest table in my system has 7.5 mil rows and is probaby 3/4 of my export. That table will shortly stableize at about 8mil. Legal requirements force me to maintain 5years of active data online in our system. We are at year 2. That one table can be trimmed to keep one year active, and the rest archived.

    So, in essence, if a incremental and cumlative change export every row of every modified table, then, as I see it, I cannot save any time here? Have I interpreted these modes correctly?

    Also, I have read where cumlative and incremental modes were going away. Did they go away in 9i? We will be moving from 7.3.4 to 9i in the next few months.

    Any thoughts as to how I can reduce the logical export time would be greatly appreciated.

    Thanks
    Bob

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Your understanding is totaly correct. As soon as one row in a table is changed, the whole table will be exported in the next cumulative/incremental export. That is why the concept of cumulative/incremental exports is totaly useless in a real life. Just forget about it!

    And yes, it is true, the concept of cumulative/incremental exports is not available in 9i any more. I think the base reason was exactly its practical uselesnes. So if you are realy in the process of migration to 9i in next few months - don't waste your time in studying and implementing cumulative/incremental exports. Just forget about it.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Feb 2001
    Posts
    99
    thanks,

    Had pretty much reached that conclusion as well from my reading, just wanted some confirmation.

    So, how do I approach my issue. I am affraid that I am going to have to convince my mgmt that a daily or weekly full export is all we need. We do hot backups everynight.

    I run in archive log mode. I checkpoint every hour. My logs switch between 2 and 3 hours.

    I don't think I need to do the exports every 6. But, I need some ammo to help in my fight. I know that with archive logs, and the online redo logs, I can roll forward almost to the point that a system failed.

    Any other suggestions?

    Thks

  4. #4
    Join Date
    Jul 2000
    Posts
    521
    Look at RMAN.
    svk

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    I agree with you, I think your mgmt don't exactly understand what logical backup realy can be used for, or more imporatantly, what you can't do with it. I would like to know their real argument as to "Why do we need full export every 6 hours for?".

    But if you can't convince them then better concentrate on how to exclude those archive data from your full export. One scenario would be:

    1. Decide how much historical data you realy need to have online all the time. Lets say you only need last three months worth in your table TABLE1.
    2. To sattisfy the legal requirements you'll still maintain the rest of your 5years of active data in the database, but it will be stored in another table, let's say TABLE1_ARCH (with the same structure as TABLE1, of course). But you would create that table in a separate tablespace, let's call it TBLSPC_ARCH.
    3. At the end of each month you would move your oldest one month worth of data from TABLE1 to TABLE1_ARCH.
    4. Just before you start your regular full export you would take your TBLSPC_ARCH offline. That way your TABLE1_ARCH will not be included in your full export. You would leave the archive tablespace online for your full export only once a month, when you add new rows to it. That way your archival data will be exported only once a month instead of 4x31 times a month.

    Of course thera are various details that you will have to or be able to implement in this scenario. For example, when you migrate to 9i you could use the partitioning to totaly automate the maintaining of the rolling window for the current data. If you won't use partitioning and if your application needs the access to your archive data ocasionly, you will have to create a view which will union-all your TABLE1 and TABLE1_ARCH. Etc etc...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330
    Hi If your database in Archive log mode...why to worry.
    Do full export once in a week...and retain atleast 2 weeks of archive logs...If some thing goes wrong restore from backup and apply logs....your db is up to date.
    2 weeks archive logs for the safety reason.If your last week backup no good you have to go one more week down...so archive logs help from that point.
    I think this solves your problem.
    Thanks.
    Thanigaivasan

  7. #7
    Join Date
    Feb 2001
    Posts
    99
    good ideas, will have to warrant further instruction.

    I have attempted the archive log argument, but that was a while ago. May have to revist it again. Also, might have to show it being done. restore to an old system, copy over archive logs, and roll it forward. Do a log switch right before I copy the logs so I get the most current data. Its the WHOLE reason why you have archive log modes.

    Upon our migration to 9i, I will be setting up replication, so that should take care of any arguments. Will cut full exports down to once a week.

    Unfortunately, I have little control over the application/database structure. We are under a maint contract with vendor. So, massive coord headache if I/we want to alter structure.

    24x7 shop so I don't have the luxory of piddling with the system too much. Do my dev work on my dev server, then have to coordinate any fixes through the vendor, so the base code is changed. They still charge us for that, so I normally let them do it anyway.

    App is "sealed" so i cannot make ANY changes to app. Strictly vendor property to maintain. Plus, I dont know the programming tool and the level of sophistication is over me.

    We are working with the vendor to create a seperate archive instance that will hold the data over 5 years old. Also, the complexity of the system makes me a little tentative to go in and try to setup a copy into an archive table. maybe when I get a little better at this stuff!

    thanks for the thoughts. Keep them coming!

  8. #8
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by thanigai
    Hi If your database in Archive log mode...why to worry.
    Do full export once in a week...and retain atleast 2 weeks of archive logs...If some thing goes wrong restore from backup and apply logs....your db is up to date.
    2 weeks archive logs for the safety reason.If your last week backup no good you have to go one more week down...so archive logs help from that point.
    I think this solves your problem.
    Thanks.
    Thanigaivasan

    I did not know that one could apply archive logs to a database restored from an export.

    Am I missing something here or is it my ignorance on the Topic. Please Confirm.

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by ronnie
    [BAm I missing something here or is it my ignorance on the Topic. Please Confirm.
    [/B]
    You rae not missing anything, your understanding is perfrctly correct. Export and archive logs can not be used together in any way at all, for any kind of recovery. Two totaly different concepts....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by jmodic
    Originally posted by ronnie
    [BAm I missing something here or is it my ignorance on the Topic. Please Confirm.
    You rae not missing anything, your understanding is perfrctly correct. Export and archive logs can not be used together in any way at all, for any kind of recovery. Two totaly different concepts.... [/B]

    But it would be good to have a functionality like that :-)
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width