Hi, I am looking for some assistance in diagnosing a procedural issue I am having with a point-in-time database recovery.
Background:
I am trying to simulate a scenario whereby I manifest a logical issue at the database level. Then use RMAN to Point in time restore to just before it. As per directly below - I want to get back to 04/07/2016 03:12.
SQL> insert into mystuff (select sysdate || ' good data ' from dual) ;
1 row created.
SQL> commit;
Commit complete.
SQL> insert into mystuff (select sysdate || ' really bad data!' from dual) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from mystuff;
NOTES
----------------------------------------------------------------------------------------------------
04/07/2016 03:12 good data
04/07/2016 03:13 really bad data!
I also removed three OMF datafiles from the running database (*Just in order to simulate a physical corruption occurring also).
[oracle@oralinux7 datafile]$ rm o1_mf_catalog__cmnytmvx_.dbf o1_mf_system_cqmj64kw_.dbf o1_mf_sysaux_cgvp296r_.dbf
[oracle@oralinux7 datafile]$
SQL> SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME FROM V$RECOVER_FILE;
FILE# ERROR ONLINE_ CHANGE# TIME
---------- ----------------------------------------------------------------- ------- ---------- ---------
1 FILE NOT FOUND ONLINE 0
3 FILE NOT FOUND ONLINE 0
7 FILE NOT FOUND ONLINE 0
Just to show that the DB is in fact in "broken" at this point...
[oracle@oralinux7 datafile]$ sqlplus app/app
SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 4 03:23:16 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 1
ORA-01110: data file 1:
'/u01/app/oracle/oradata/SANDPRD/datafile/o1_mf_system_cqmj64kw_.dbf'
etc.etc...
RMAN> validate database ;
Starting validate at 04-JUL-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
RMAN-06169: could not read file header for datafile 1 error reason 4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of validate command at 07/04/2016 03:26:46
RMAN-06056: could not access datafile 1
I would like to restore and recover the database to the PIT of 04/07/2016 03:12:59, as noted earlier above with "good data".
Prior to performing the above - I initiated an RMAN backup; Please see attached for full output and 'list' command output.
However, my problem is the below. It appears to RMAN that my backupsets are not visible. They certainly appear to be there to me but not to the restore operation?
Note: I have tried multiple variations of the restore operation via SCN, alternate "until time" syntax.
Note: I am using flash recovery area and I am aware that a resetlogs command can cause different incarnations of files to get muddled when using the FRA (I have therefore attempted to start the instance and restore with the flash recovery area turned off - no success.
I have been testing with RMAN in the last few hours, but I didn't initiate a resetlogs however, so I don't think its an incarnation issue.
run {
set until time 'Jul 04 2016 03:12:59';
restore database ;
recover database ;
}
executing command: SET until clause
Starting restore at 04-JUL-16
using channel ORA_DISK_1
datafile 7 will be created automatically during restore operation
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/04/2016 03:58:19
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 6 found to restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
What I am asking is: Can someone please take a look at the 'list backup' command output attached and examine as to whether there is sufficient backup in order to restore to this PIT (or am i missing something). Any help / ideas would be appreciated?
Regards
Ruan