Oracle Restore and recover Database on the same host

oraclerestore

  1. I have DB1 database on Server1 .
  2. I have taken backup.
  3. After that I have created table TEST.
  4. Inserted some values in the TEST table.
  5. Truncated table.
  6. And want to return my TEST table to its previous state.

For this I took backup(datafiles, archive logs, controlfile, spfile)

I don't want to recover whole database, just this table is needed.
It can be done by restoring and recovering database to another host, export table from the new DB and import to the existing DB(DB1).

Let's assume I have just one server… Can I restore and recover database on the same machine with the new database name?

Waiting your suggestions, I will also try to search something…

Best Answer

N.B. This has been editied since its original posting.

You may want to look at duplicating the database to a new name provided you have all the required backups and archivelogs to restore to the required point in time.

I am assuming that ASM is involved here, if not you might need to look at the duplicate command to see how to rename the files as it's done automatically by ASM.

If your using standard os files rather than ASM then you might need to include this in your duplicate statement once you've connected to the atarget and auxiliary databases (see below);

run 
{
  SET NEWNAME FOR DATAFILE 1 TO '/oradata/instance/file1.dbf';
  SET NEWNAME FOR DATAFILE 2 TO '/oradata/instance/file1.dbf';
  <repeat for all the files in your Database>
  DUPLICATE TARGET DATABASE to newdb;
}

Now the theory should be;

Create a new pfile (from the existing database and rename the various references to the database), making sure you change any log destinations and the directories exist

Start the new instance in a no mount state

Then start 

rman
Connect target sys@instance
Connect auxiliary /

Once the two connections are up.

The duplicate command to restore to a point in time should be as follows

DUPLICATE TARGET DATABASE TO newdb UNTIL TIME TO_DATE('20/07/2011 19:45:00','DD/MM/YYYY HH24:MI:SS')

-- This should be prior to when you created the Test table so the database will be restored upto that point in time. It theory at least.

another option i do find useful is to resize the logfile and groups

DUPLICATE TARGET DATABASE TO newdb
LOGFILE
      GROUP 1 ('/oradata/instance/redo01a.log', 
               '/oradata/instance/redo01b.log') SIZE 200K REUSE, 
      GROUP 2 ('/oradata/instance/redo02a.log', 
               '/oradata/instance/redo02b.log') SIZE 200K REUSE;

or with ASM something like

DUPLICATE TARGET DATABASE TO newdb
LOGFILE
      GROUP 1 ('+DATA', 
               '+FRA') SIZE 200K REUSE, 
      GROUP 2 ('+DATA', 
               '+FRA') SIZE 200K REUSE;

There are several options you can also specify, I believe one is to an scn, sequence or until a date time. You might need to do a to_date convertion for the last option. putting these together should help you get what you're after.

Related Question