Originally posted by Axr2 I logged a TAR with Oracle about this. Here is the excerpt from the horse's mouth. I guess the new tablespace is not recoverable in the scenario I was trying to simulate.
Explained that unfortunately, we cannot restore something that was not backed up. So even though there are archivelogs, we do not have a datafile to restore. As we need a baseline from which to restore, if it was never backed up, we do not have that baseline."
I don't mean to mix issues here..but what beats me is how does Data guard (physical standby) then create a a new tablespace on the standby when you create one on the primary? Obviously the standby is intelligent enough to extract DDL from the archive logs (if you use a ARCH log transport) and create the tablespace on the standby..so why is RMAN not able to do the same! Am I missing something obvious?
that depends, if you have standby_management set to auto then yeah it will create the ts's and df's on the fly 4 u
Thanks for the note. Yup, I've performed that (adding a d/f and 'fooling' it to perform recovery on the new file) with old traditional hot backups in the past. The process is somewhat akin to standby database maintenance in 7.3.4 days..
Anyway, that's exactly what I told the guy in Oracle support as well. But he claims that one can't do this directly through RMAN. (It is another matter that support person didn't know that such a recovery was even possible! hehe..).
RMAN still has some ways to go before it can be the 'plug n play' tool that Oracle claims it is.
FYI. RMAN addresses tablespace recovery without a baseline backup.
Here's how. Eventually, asked Tom about it. Embarassingly for me, he pointed me to the user guide. :(
Recovery is identical to how it was performed with traditional Oracle hot backups.
Recovering a Lost Datafile Without a Backup: Example
In this scenario, the following sequence of events occurs:
You make a whole database backup of your ARCHIVELOG mode database.
You create a tablespace containing a single datafile called ?/oradata/trgt/history01.dbf.
You populate the newly created datafile with data.
You archive all the active online redo logs.
Someone accidentally deletes ?/oradata/trgt/history01.dbf from the operating system before you have a chance to back it up.
Are you prevented from recovering the data in the lost datafile because you have no backup of the file? No. You can recover the data by creating a new datafile with the same filename as the lost datafile, then run the RECOVER command to apply the redo for this file.
For example, start RMAN, connect to the target database, and then run the following statements at the RMAN prompt:
# take the missing datafile offline
# note that SQL statement is bounded by double quotes, but the datafile name has two
# individual single quotes both before and after it
SQL "ALTER DATABASE DATAFILE
'' ?/oradata/trgt/history01.dbf '' OFFLINE";
# create a new datafile with the same name as the missing datafile
SQL "ALTER DATABASE CREATE DATAFILE
'' ?/oradata/trgt/history01.dbf '' ";
# recover the newly created datafile
RECOVER DATAFILE '?/oradata/trgt/history01.dbf';
# bring the recovered datafile back online
SQL "ALTER DATABASE DATAFILE
'' ?/oradata/trgt/history01.dbf '' ONLINE";