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;

rman

>> 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')";
RESTORE DATABASE;
RECOVER DATABASE;

sql ‘ALTER DATABASE OPEN RESETLOGS’;
}

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');

commit;
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;

run
{
SET UNTIL TIME "TO_DATE('2024-04-06 15:33:55', 'YYYY-MM-DD HH24:MI:SS')";
RESTORE ARCHIVELOG all ;
RECOVER DATABASE;
sql ‘ALTER DATABASE OPEN RESETLOGS’;

}

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!