Oracle Restore and recover Database on the same host?

Question

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.

And want to return my TEST table to its previous state.

1. 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...

Answer 1

The straightforward answer to this question is: use Flashback.

If you have an Enterprise Edition license I would say enabling Flashback Query is good practice. Bear in mind that we can configure the retention period to suit our needs, so it can be shorter if our system generates a lot of change or if storage is tight. These days, disk is pretty cheap so it is unusual for space to be the overriding concern. And if your server has got sufficient capacity to install an entire copy of your database you have enough space for a large Flashback Area.

However, you say you're not using Flashback, so moving on.

It is perfectly possible to edit a backed-up control file to create a new database providing it was backed-up in the correct fashion:

alter database backup controlfile to trace
/

You would need to change the database name and the paths for all the files. I admit I haven't attempted this procedure to install a second database on the same server, but I don't see why it should be different from cloning a database onto a different server.