Dear Experts ,
I just simulated a scenario on my local machine.
Already INCOMPLETE RECOVERY done.
I took RMAN full back after INCOMPLETE RECOVERY.
Now i am trying to recover my database based on UNTIL TIME .. but sticking in middle ..
I googled but still i am struggling to recover my database .. based on UNTIL TIME CLAUSE
>>
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 HRMS 1036634958 PARENT 1 07-JUL-14
2 2 HRMS 1036634958 PARENT 1594143 01-OCT-18
3 3 HRMS 1036634958 CURRENT 1736264 01-OCT-18
>>
RMAN> backup incremental level 0 database plus archivelog TAG="INCR-0-BKP";
Starting backup at 02-OCT-18
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=24 RECID=36 STAMP=988421967
input archived log thread=1 sequence=25 RECID=37 STAMP=988421977
input archived log thread=1 sequence=26 RECID=38 STAMP=988421994
input archived log thread=1 sequence=27 RECID=39 STAMP=988422015
channel ORA_DISK_1: starting piece 1 at 02-OCT-18
channel ORA_DISK_1: finished piece 1 at 02-OCT-18
piece handle=/u02/app/oracle/fast_recovery_area/HRMS/backupset/2018_10_02/o1_mf_annnn_INCR_0_BKP_fv4hz0lz_.bkp tag=INCR-0-BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-OCT-18
Starting backup at 02-OCT-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u02/app/oracle/oradata/hrms/example01.dbf
input datafile file number=00001 name=/u02/app/oracle/oradata/hrms/system01.dbf
input datafile file number=00003 name=/u02/app/oracle/oradata/hrms/sysaux01.dbf
input datafile file number=00004 name=/u02/app/oracle/oradata/hrms/undotbs01.dbf
input datafile file number=00006 name=/u02/app/oracle/oradata/hrms/users01.dbf
channel ORA_DISK_1: starting piece 1 at 02-OCT-18
channel ORA_DISK_1: finished piece 1 at 02-OCT-18
piece handle=/u02/app/oracle/fast_recovery_area/HRMS/backupset/2018_10_02/o1_mf_nnnd0_TAG20181002T014017_fv4hz2z2_.bkp tag=TAG20181002T014017 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:46
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 02-OCT-18
channel ORA_DISK_1: finished piece 1 at 02-OCT-18
piece handle=/u02/app/oracle/fast_recovery_area/HRMS/backupset/2018_10_02/o1_mf_ncsn0_TAG20181002T014017_fv4j4bfh_.bkp tag=TAG20181002T014017 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 02-OCT-18
Starting backup at 02-OCT-18
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=28 RECID=40 STAMP=988422188
channel ORA_DISK_1: starting piece 1 at 02-OCT-18
channel ORA_DISK_1: finished piece 1 at 02-OCT-18
piece handle=/u02/app/oracle/fast_recovery_area/HRMS/backupset/2018_10_02/o1_mf_annnn_INCR_0_BKP_fv4j4gcx_.bkp tag=INCR-0-BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-OCT-18
>>
SYS>create user jhil identified by jhil default tablespace TBS1 quota unlimited on TBS1;
User created.
SYS>grant create session, resource to jhil;
Grant succeeded.
SYS>create table jhil.t1(id number);
Table created.
SYS>create table jhil.t2(id number);
Table created.
SYS>insert into jhil.t1 select level from dual connect by level<6001;
6000 rows created.
SYS>insert into jhil.t2 select level from dual connect by level<6001;
6000 rows created.
SYS>commit;
Commit complete.
SYS>select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') TIME from dual;
TIME
-------------------
02/10/2018 01:51:50
>>
SYS>create table jhil.new_deployment_t1 (id number);
Table created.
SYS>create table jhil.new_deployment_t2 (id number);
Table created.
SYS>insert into jhil.new_deployment_t1 select * from jhil.t1;
6000 rows created.
SYS>insert into jhil.new_deployment_t2 select * from jhil.t2;
6000 rows created.
SYS>alter system switch logfile;
System altered.
SYS>alter system switch logfile;
System altered.
SYS>truncate table jhil.t1;
Table truncated.
SYS>truncate table jhil.t2;
Table truncated.
SYS>commit;
Commit complete.
SYS>alter system switch logfile;
System altered.
>>
SYS>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
>>
$ rlrman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Tue Oct 2 02:01:42 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 838860800 bytes
Fixed Size 2929936 bytes
Variable Size 562039536 bytes
Database Buffers 268435456 bytes
Redo Buffers 5455872 bytes
RMAN> run {
2> set until time "to_date('2018:10:01 01:51:48','YYYY:MM:DD HH24:MI:SS')";
3> restore database;
4> recover database;
5> }
executing command: SET until clause
Starting restore at 02-OCT-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/02/2018 02:06:25
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time
>>