I am performing time based recovery.
The retention policy is set to 14 days .
I am running a restore going to back to 3 days .
But , for some reason , RMAN is looking for archived redo log from today which does not exists.
Please advise.
Here is the parital ouput
===========================
RMAN> run{
set until time "TO_DATE('20160313 01:23:53' , 'YYYYMMDD HH24:MI:SS')";
restore tablespace "SALDATA";
recover tablespace "SALDATA";
alter tablespace SALdata online;
}
output
... ...
executing command: SET until clause
Starting restore at 16-MAR-16
......
archived log file name=W:\BAK\DBDEV\ARCHIVELOG\2016_03_16\O1_MF_1_47521_CGMMO3YS_.ARC RECID=47480 STAMP=906657766
unable to find archived log
archived log thread=1 sequence=47522
...
...
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/16/2016 17:22:50
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 47522 and starting SCN of 7428768871758
....
RMAN> list archivelog all;
List of Archived Log Copies for database with db_unique_name SCPDEV
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
47468 1 47550 A 16-MAR-16
Name: W:\BAK\DBDEV\ARCHIVELOG\2016_03_16\O1_MF_1_47550_CGMGTTQP_.ARC
47486 1 47551 A 16-MAR-16
Name: W:\BAK\DBDEV\ARCHIVELOG\2016_03_16\O1_MF_1_47551_CGMRX52D_.ARC
That's not the syntax for tablespace point in time recovery, so in effect you are doing a full recovery, eg:
SQL> select sysdate from dual;
SYSDATE
-------------------
17/03/2016 10:55:00
SQL> delete from t;
93930 rows deleted.
SQL> commit;
Commit complete.
so I recovered to 10:55 I should get my rows back in T, but
RMAN> run{
2> set until time "TO_DATE('17/03/2016 10:55:00' , 'DD/MM/YYYY HH24:MI:SS')";
3> restore tablespace "USERS";
4> recover tablespace "USERS";
5> }
executing command: SET until clause
Starting restore at 20160317-110139
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to C:\ORACLE\ORADATA\NP12\USERS01.DBF
channel ORA_DISK_1: reading from backup piece D:\ORACLE\BKP\NP12_0ER0K3DN_1_1
channel ORA_DISK_1: piece handle=D:\ORACLE\BKP\NP12_0ER0K3DN_1_1 tag=TAG20160316T091535
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:35
Finished restore at 20160317-110614
Starting recover at 20160317-110614
using channel ORA_DISK_1
starting media recovery
[snip]
archived log file name=C:\ORACLE\ARCH\NP12_0000001479_0895666867.0001 thread=1 sequence=1479
archived log file name=C:\ORACLE\ARCH\NP12_0000001480_0895666867.0001 thread=1 sequence=1480
archived log file name=C:\ORACLE\ARCH\NP12_0000001481_0895666867.0001 thread=1 sequence=1481
archived log file name=C:\ORACLE\ARCH\NP12_0000001482_0895666867.0001 thread=1 sequence=1482
media recovery complete, elapsed time: 00:00:02
Finished recover at 20160317-110624
It used *all* available archives (NP12_0000001482 was the latest on my laptop).
The syntax you want is:
run{
recover tablespace "USERS" until time "TO_DATE('17/03/2016 10:55:00' , 'DD/MM/YYYY HH24:MI:SS')";
}