Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
  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...

share|improve this question

2 Answers 2

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.

share|improve this answer
    
WOW almost complete answer!! You must be a very good DBA! thank you.. But I don't understand something.. During duplication does Oracle refers to the backups or target database? –  kupa Jul 21 '11 at 19:11
    
No one main point when duplicating using the commands I've said it uses the backups as a point of reference. There is an option to copy the live data base but this has failed in my experience. As for a good DBA I'm not so sure, I've done this a lot out of a requirement to update preproduction environments using this method hence the details but as far as oracle I've still got a long way to go. But hey, the journey is fun. –  GrumpyMonkey Jul 22 '11 at 22:00

You'll have to restore what you backed up, so the easy answer is: no, you cannot partially recover the database.

Depending on how you laid out your current database, you may be able to do a user managed tablespace point in time recovery (tspitr) as outlined in the docs here: http://download.oracle.com/docs/cd/B14117_01/server.101/b10734/ostspitr.htm#1006085

The key here is that you'll be rolling back all the objects in the same tablespace as your "TEST" table. To avoid this ill effect, you can do two TSPITRs, one with your TEST table, and take another hot backup of the tablespace before you start the process and export the table after recovery, then TSPITR back to before and import the table.

More complicated option: Another option is to restore the database backup you have:

  1. restore the datafiles, etc. to DIFFERENT location then your current DB
  2. hopefully you have a controlfile to trace to work from, otherwise you'll need to craft your own recreate controlfile sql script (see this link for details)
  3. shutdown the existing DB with the same name and stop the listener (not forever, only until you get your Table back in the next few steps)
  4. Startup and recover your new DB with the new controlfile from a server term session (to avoid bring back up the listener and having users connect)
  5. Export the table (using exp or data pump as per your db version)
  6. Shutdown the restored backup
  7. Restart your DB and listener, rename, CTAS or drop your TEST table, import your TEST table
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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