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

  • 1
    What version of Oracle Database are you using? The reason for asking is that you may find Oracle Flashback easier to use to achieve the same results? An overview can be found here: download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/… – Ian Carpenter Jul 20 '11 at 11:38
  • I am using 10g. Is flashbacking a good practice? It requires additional space... – kupa Jul 20 '11 at 11:41
  • Also I have not enabled flashbacking... I wonder to know if it is possible to recover database on the SAME host(Consider that you cannot have databas instances with the same name) So you should somehow rename database before recovering,HOW? – kupa Jul 20 '11 at 11:44
  • 1
    @kupa, I'd recommend you ask this question on dba.stackexchange.com – DCookie Jul 20 '11 at 14:49

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.