Home » RDBMS Server » Backup & Recovery » can anyone help me to restore tables from full offline backup. (oracle 11g)
can anyone help me to restore tables from full offline backup. [message #406446] Thu, 04 June 2009 01:14 Go to next message
neha_sanap
Messages: 4
Registered: June 2009
Location: pune
Junior Member

I have taken full_offline backup in noarchive mode of my database to local system Sad
then i dropped one of the table in database.
Now i m trying to restore it.
jst using,
restore database.
recover database commands.

I am very new to the oracle ,I read a lot of blogs on it,
what i found is we need to create some tablespace for database .we cannot restore database from default SYSTEM tablespace.

When i created database i ddnt specified any Tablespace for it so i think it must be default tablespace.
and even during restoration i ddnt used any until time clause..

Sad please help me..

Can anyone please tell me steps for it..i jst want to restore my tables..
Re: can anyone help me to restore tables from full offline backup. [message #406447 is a reply to message #406446] Thu, 04 June 2009 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many things you don't know.
I recommend you to FIRST read the followings:
Database Concepts
Backup and Recovery Basics
Administrator's Guide

Regards
Michel
Re: can anyone help me to restore tables from full offline backup. [message #406451 is a reply to message #406446] Thu, 04 June 2009 01:51 Go to previous messageGo to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
Hi,U have to perform incomplete recovery.Steps to do this are:-

1.SHUTDOWN IMMEDIATE
2.Restore the most recent whole database backup with operating system commands
3.if you did not back up the online redo logs, you cannot restore them with the datafiles and control files.
SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
SQL> CANCEL
4.Then open the database with the RESETLOGS option to reset the current redo log sequence to 1 as follows:
SQL> ALTER DATABASE OPEN RESETLOGS;

or u can use RECOVER DATABASE until time 'yyyy-mm-dd:hh:mi:ss'

2nd method for restoring table is use flashback query isf the respective table u want is present in recyclebin in or not

3rd method is using export/import utility

Hope this may help u
Re: can anyone help me to restore tables from full offline backup. [message #406457 is a reply to message #406451] Thu, 04 June 2009 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Who is "u"?

Regards
Michel
Re: can anyone help me to restore tables from full offline backup. [message #406462 is a reply to message #406451] Thu, 04 June 2009 02:42 Go to previous messageGo to next message
neha_sanap
Messages: 4
Registered: June 2009
Location: pune
Junior Member
Thanx a lot vinniora..

Actually i have asked to explore RMAN backup and recovery for this.
Can u tell me what steps i should follow to backup the redo files so after restoration,i would get all the data i.e all dbf ,contrl files, my tables,etc..

I m not preferring to online backup becoz i want it in consistent state to do..

Smile

[Updated on: Thu, 04 June 2009 02:44]

Report message to a moderator

Re: can anyone help me to restore tables from full offline backup. [message #406468 is a reply to message #406447] Thu, 04 June 2009 02:59 Go to previous messageGo to next message
neha_sanap
Messages: 4
Registered: June 2009
Location: pune
Junior Member
Thanks michel.. Smile
Re: can anyone help me to restore tables from full offline backup. [message #406469 is a reply to message #406462] Thu, 04 June 2009 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 04 June 2009 09:20
Who is "u"?

Regards
Michel


Please read OraFAQ Forum Guide and don't use IM speak.

All is explained in the links I provided you.

Regards
Michel

[Updated on: Thu, 04 June 2009 03:00]

Report message to a moderator

Re: can anyone help me to restore tables from full offline backup. [message #406479 is a reply to message #406462] Thu, 04 June 2009 03:32 Go to previous messageGo to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
Hi Neha.. you have to take a backup of all the datafiles,control file,redo-log file.
For a database in NOARCHIVELOG mode, you need only restore the affected Oracle datafiles if no redo log file has been overwritten since the last backup.
the procedure which i wrote earlier
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
it will ask you 2-3 parameter
file_name,cancel,redolog
then apply the redolog which is needed.remember provide whole path of redolog file which is needed for example
c:\redo\redolog01.log
Re: can anyone help me to restore tables from full offline backup. [message #406499 is a reply to message #406451] Thu, 04 June 2009 04:59 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
vinniora you are making HUGE assumptions here.

vinniora wrote on Thu, 04 June 2009 02:51
Hi,U have to perform incomplete recovery.

How do you want the OP to perform a PITR recovery with a NOARCHIVELOG database if the changes aren't in the redo logs ?

vinniora wrote on Thu, 04 June 2009 02:51
2nd method for restoring table is use flashback query isf the respective table u want is present in recyclebin in or not


Flashback query will not get back a dropped table !
vinniora wrote on Thu, 04 June 2009 02:51
3rd method is using export/import utility

What will the OP export if the table is dropped ?
Re: can anyone help me to restore tables from full offline backup. [message #406690 is a reply to message #406479] Fri, 05 June 2009 06:55 Go to previous messageGo to next message
neha_sanap
Messages: 4
Registered: June 2009
Location: pune
Junior Member

Hey i got the solution...
Actually was using jst
restore database
recover database command so i was not getting my droped tables after restoration.
now i am using the following command to restore,
restore database until scn 950;
recover database until scn 950;
alter database open resetlogs;

anyway thank you vinniora for helping so far.....
i got the clue to search in this direction from your quote only.

I have another query now,right now i know the scn ,what if i dont know it or i have created any checkpoint or restore point.
means if i m having new machine with complete clean installation and there if i want to restore the bacup i have already taken,
what should i do??




Re: can anyone help me to restore tables from full offline backup. [message #406698 is a reply to message #406690] Fri, 05 June 2009 07:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think it must be useless to repeat that it is explained in the links I posted but I'm pretty sure you prefer someone will give a complete answer to prevent you from making any effort and learning anything and then to your next question and then to your next question and then to your next question and then to your next question and then to your next question and then to your next question and then to your next question and then to your next question and then to your next question and then to your next question and then to your next question and then to your next question and then to your next question and then to your next question and then to your next question...

Regards
Michel
Re: can anyone help me to restore tables from full offline backup. [message #406729 is a reply to message #406698] Fri, 05 June 2009 09:28 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Somehow this comes to mind:

./fa/3400/0/

./fa/3943/0/

./fa/1681/0/
Previous Topic: Recover from missing files(redo log file/control file)
Next Topic: Recover DB from RMAN backup
Goto Forum:
  


Current Time: Thu Dec 08 14:28:51 CST 2016

Total time taken to generate the page: 0.08965 seconds