Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, Ketan.

Asked: April 07, 2024 - 1:49 am UTC

Last updated: April 09, 2024 - 4:50 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Hello Sir,
I am able to get one scenario to work and that scenario was where I had a VM (server) running Oracle 19c with just 1 table 5 records and I did a backup of the whole VM (disk backup) and now I added a new table in my db with 3 records (ensured db is in Archivelog mode) and then I ran:

rman target /
backup database plus archivelog;

Now I went ahead and added 2 more records and noted the system time lets say **2024-04-06 15:33:55 ** (so I can restore upto this time). So basically a new table with 5 records. Once all this done I ran below command:

backup incremental level 1 database plus archivelog;

Now I deleted my VM and restored the first Old copy of my VM backup (one that had 1 table n 5 records), post this VM restore. I followed the steps below and I was able to get Point in time recovery to work up to 2024-04-06 15:33:55 (here now I should have 2 tables each with 5 records each). The main step which I had missed earlier was RESTORING the control file since I was doing restore on a different (new VM) server:

sql>> shutdown abort;

>> startup nomount;

rman >>RESTORE CONTROLFILE FROM "/mnt/orabkup1/snapcf_ev.f";
rman>> startup mount;
rman >>run
SET UNTIL TIME "TO_DATE('2024-04-06 15:33:55', 'YYYY-MM-DD HH24:MI:SS')";


Everything good here and with this approach I was able to get the Point in time recovery to work. I was missing that restore of the control file.

Now the scenario which I am still not able to work out and I am sure I am making a very basic mistake (may be I dont understand the archivelog and redolog properly).

The scenario I want to make work is : I have VM backup (disk backup) upto a level of 1 Table and 5 records. Then I create 2nd table and add lets say 2 records to it and this time I only take ARCHIVELOG backup and then add 3 more records and then backup incremental archivelog all and I note the time (lets assume '2024-04-06 15:33:55) with following steps:
backup archivelog all;

insert into xxx VALUES(3,'Line 1');
insert into xxx VALUES(4,'Line 1');

backup incremental level 1 archivelog all

Here I have not done backup database plus archivelog (assuming all those new inserts would be in redolog and may be in archivelog?). Now I delete this VM and restore from disk backups a new VM from backup1 (where only 1 table 5 records) exists and now I simply run following:

shutdown abort;

startup nomount;

RESTORE CONTROLFILE FROM "/mnt/orabkup1/snapcf_ev.f";
startup mount;

SET UNTIL TIME "TO_DATE('2024-04-06 15:33:55', 'YYYY-MM-DD HH24:MI:SS')";


But unfortunately it complains about ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'D:\BASE\MYDB\DATA\SYSTEM01.DBF'

Not sure why this, as I was thinking that Archivelog should have all changes post my first VM backup since I ran full archivelog all backup and also incremental archivelog all backup.. I am not sure if this is something to do with online REDOLOG files not yet written to archivelog file and I am just assuming ARchivelog has everything? Apologies if I am wrong here as this is all new to me still trynig to grasp.

Appreciate your guidance. Thank you!

and Connor said...

With *most* recoveries, you would not touch the control files, ie, you might have corrupted the database, or truncated a key table etc etc. In all of those situations, you would *not* restore the control files, because they contain all the important info about archives, datafiles, redo logs etc. (That's the same reason why we always multiplex them).

Of course, in a "I've lost the entire server" scenario, you might have no choice.

But now that you have *restored* the control files, you no longer have any information about the *current* state of the database (because the control files held that).

Thus when you restore them, you now have to provide that lost information, because it does not know where the archives are, where the redo is, and what archives are needed for recovery.

In your case, I suspect your restored control file does not know about the required archive logs to get you up to date. In this case, you probably need to manually record using "recover until cancel" and provide the names of the archive logs as you are prompted.


  (1 rating)


Ketan, April 08, 2024 - 10:23 am UTC

Thank you Connor, appreciate the guidance.
I m trying to restore the whole server as if DR scenario. I ensured that I took current control file bkup when I took the archivelog backup also after I added my new table with 5 records, so I had the backed up copy of control file. Now I restored my VM from snapshot bkup and I first restored on startup nomount and then I did startup mount and even ran catalog from 'path to my archivelog bkup' and then tried to do point in time recovery.
Also post the VM snapshot I have only taken archivelog backup and not database bkup( since I wanted to rely on the vm snapshot and test it).

But still it complains for missing from datafile error.
Can you confirm if my approach is correct here or if I m missing any step?
Connor McDonald
April 09, 2024 - 4:50 am UTC

I think you'll need to give us an exact step by step of what you're doing (including output from commands) so we can see the timing of things going on here.

More to Explore


Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.