Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    21

    Question Unanswered: using Oracle EXP to export database tables and recover DB using IMP.

    Hello,

    I have a question.

    Currently, we are running Oracle 8.1.7.0.1 on Dell PowerEdge server.

    We run cron jobs to take the database backup every midnight.
    We take cold backups i.e shutdown the database copy the control files and data files.

    But what I want is to take online backup of database/tablespace so that we can recover the database to reflect the recent changes to the database rather than restoring the data file, if needed, which is 24 hrs old.

    Since I have never tried archive relog mode before, I am thinking of taking logical backup of database at regular intervals may be in an interval of 4 hrs during the day.

    This way, we will have the data files and control files backed up in case the disk fails and have EXP dump too with us so that we can recover the data upto the recent changes.

    I am looking for suggestions regarding taking online backups.

    Please let me know if I am thinking in the right direction.

    Later in the future after couple of tests, may be I will move towards to running the database in archive log mode and taking online tablespace backups.

    Please give your suggestions about this.

    Thanks,

    - Bikram.

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    With using the EXP/IMP, you may not get all the data... During
    active hours, the data is constantly changing. Export hits table A...
    5 minutes later it gets to Table B (At which time, changes are being made to Table A). Now suppose you have a foreign key constraint on table B to A.... Upon restoration, you will find that Table A is missing
    Parent records....

    If the database is a production database,
    I would learn to use Archive log mode and try it out ...
    It has saved my clients on numerous occassions.

    Hope this helps
    Gregg

  3. #3
    Join Date
    Jul 2003
    Posts
    21
    I agree with you completely.

    But it is a University (educational) background here.
    So, the structure of database entities does not change that often.
    What I was thinking was to start with logical imports and exports and then later on make transition to running database in archive log mode.

    But I agree with you completely. thanks a lot.

    Somwhere down the line, I will start taking online backups.

    What do u think ???



    - Bikram.


    Originally posted by gbrabham
    With using the EXP/IMP, you may not get all the data... During
    active hours, the data is constantly changing. Export hits table A...
    5 minutes later it gets to Table B (At which time, changes are being made to Table A). Now suppose you have a foreign key constraint on table B to A.... Upon restoration, you will find that Table A is missing
    Parent records....

    If the database is a production database,
    I would learn to use Archive log mode and try it out ...
    It has saved my clients on numerous occassions.

    Hope this helps
    Gregg

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by gbrabham
    With using the EXP/IMP, you may not get all the data... During
    active hours, the data is constantly changing. Export hits table A...
    5 minutes later it gets to Table B (At which time, changes are being made to Table A). Now suppose you have a foreign key constraint on table B to A.... Upon restoration, you will find that Table A is missing
    Parent records....

    Hope this helps
    Gregg
    Consistent=Y solves the problem above, but you can only do an export so many times per hour so you will most likely lose some data.

    It always depends on how valuable you feel the data is. Can you lose 6 hours of possible updates/changes? If so, then maybe Export is fine.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jul 2003
    Posts
    21

    Thumbs up

    Thanks a lot for your help !

    - Bikram.

    Originally posted by The_Duck
    Consistent=Y solves the problem above, but you can only do an export so many times per hour so you will most likely lose some data.

    It always depends on how valuable you feel the data is. Can you lose 6 hours of possible updates/changes? If so, then maybe Export is fine.

Posting Permissions

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