If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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.

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.

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 ...