Time based backup & recovery

[pavan_kashyap]

hello,
i have taken a backup at 3pm ,
some data was entered in an xx table at 5pm,
which was lost due to some reasons at 530pm,
now if i want to get back the data what i should do..

as we have a concept of time based recovery in oracle
what is the method used here to get my data back
i have taken my second backup at 7pm
its an imm. requirement for me
just help me out...
thnx & regards
pavan

[Jim]

To start of with, you will need to ensure you have transaction log backups. Without these, you will have lost all of your data since your full backup at 3pm.

This means that your database must be in Full Recovery Mode, so that your transaction logs are keeping the transactions. If your database is in Simple recovery mode, then you will only be able to apply the transaction log with trasactions since the last checkpoint (and most likely, not be able to recover the data you are looking for).


Basically, the procedure goes like this.

Apply the Full backup to the database.
Then, apply the trasaction log backup(s)
(if there is more than 1 transaction log backup, then you will have to apply them in the order that they were taken - for example if you had transaction log backups at 3pm, 3:30pm, 4pm, etc, you will have to apply these in this order.)

Check BOL for the exaxt syntax and procedures for point in time recovery.

Hope this helps,

Jim