Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Help: How to restore a database scheme from a *.ora file?

2701424Jul 30 2014 — edited Jul 31 2014

Hello Guru's,

I am currently taking a crash course, literally, in database recovery. I have a schema called fair and it has crashed. How can I restore the entire thing? I got a fair.ora backed up, but I don't know where to even start. I am currently "RT*M", but this is a time critical issue, so I thought I would throw out an SOS here.

Thanks,

Geeky

Comments

unknown-951199

2701424 wrote:

Hello Guru's,

I am currently taking a crash course, literally, in database recovery. I have a schema called fair and it has crashed. How can I restore the entire thing? I got a fair.ora backed up, but I don't know where to even start. I am currently "RT*M", but this is a time critical issue, so I thought I would throw out an SOS here.

Thanks,

Geeky

schema can not & does not crash.

> I got a fair.ora backed up,

what does above mean?

post actual command which completed the reported "backup"

How do I ask a question on the forums?

https://forums.oracle.com/forums/thread.jspa?threadID=2174552#9360002

FreddieEssex

You probably mean database and not schema.

You want to restore the entire thing?  Do you have any backups?

fair.ora?  Do you mean initfair.ora?  This is the database initialisation file and not a backup of the database.

2701424

Gurus,

Let me be more precise. The database for my fair schema has died, specifically, one of the tables in the schema just decided to drop a lot of it's information. I have a "backup" of this database called fair.ora. This database is hosted on an oracle machine, let's call it db2. I have sys access to db2 where fair database lives. I do not have initfair.ora. I was told that fair.ora is a backup which was made a day ago.

Thanks,

Geeky

FreddieEssex

A table in a database have the built in intelligence to decide to drop it's data....but anyway.

This backup called fair.ora....what kind of backup is it?

Is it an RMAN backup?  Does your database run in archivelog mode?  If so you may potentially be able to restore it to a point in time or just restore the entire database.

Is it a datapump export backup?  If so do you have the logs?  Do you see the table backed up in the logs?  If so then you should be able to restore the table from this backup.

fair.ora doesn't really mean anything to me I'm afraid.  Ask whoever told you it was a backup to clarify what kind of backup.

unknown-951199

2701424 wrote:

Gurus,

Let me be more precise. The database for my fair schema has died, specifically, one of the tables in the schema just decided to drop a lot of it's information. I have a "backup" of this database called fair.ora. This database is hosted on an oracle machine, let's call it db2. I have sys access to db2 where fair database lives. I do not have initfair.ora. I was told that fair.ora is a backup which was made a day ago.

Thanks,

Geeky

any specific solution depends upon exactly  how the "backup" was made.

what is OS name & version for the system hosting DB2?

2701424

Gurus,

I am trying to find out how this backup was made and what kind of backup it is. I was just given a file and told "here". The individual who gave this to me isn't around for days, hence why I am asking on here. As for the OS this is running on, it is Windows Server 2008 R2. I am running Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64 bit.

As soon as I get an answer how the .ora file that was given to me was obtained, I will let you know.

Geeky

JohnWatson

2701424 wrote:

Gurus,

Let me be more precise. The database for my fair schema has died, specifically, one of the tables in the schema just decided to drop a lot of it's information. I have a "backup" of this database called fair.ora. This database is hosted on an oracle machine, let's call it db2. I have sys access to db2 where fair database lives. I do not have initfair.ora. I was told that fair.ora is a backup which was made a day ago.

Thanks,

Geeky

If the only problem is that you have deleted a few rows, just recover them with a flash back query:

orclz>

orclz> delete from emp;

14 rows deleted.

orclz> commit;

Commit complete.

orclz> flashback table emp to timestamp sysdate - 10/1440;

Flashback complete.

orclz> select * from emp;

    EMPNO ENAME      JOB              MGR HIREDATE

---------- ---------- --------- ---------- ------------------- -----

      7369 SMITH      CLERK          7902 1980-12-17 00:00:00

      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00

      7521 WARD      SALESMAN        7698 1981-02-22 00:00:00

      7566 JONES      MANAGER        7839 1981-04-02 00:00:00

      7654 MARTIN    SALESMAN        7698 1981-09-28 00:00:00

      7698 BLAKE      MANAGER        7839 1981-05-01 00:00:00

      7782 CLARK      MANAGER        7839 1981-06-09 00:00:00

      7788 SCOTT      ANALYST        7566 1987-04-19 00:00:00

      7839 KING      PRESIDENT            1981-11-17 00:00:00

      7844 TURNER    SALESMAN        7698 1981-09-08 00:00:00

      7876 ADAMS      CLERK          7788 1987-05-23 00:00:00

      7900 JAMES      CLERK          7698 1981-12-03 00:00:00

      7902 FORD      ANALYST        7566 1981-12-03 00:00:00

      7934 MILLER    CLERK          7782 1982-01-23 00:00:00

14 rows selected.

orclz>

Don't hang about, though - there are limits to how far back you can go.

unknown-951199

>The database for my fair schema has died,

what exactly does above mean?

use COPY & PASTE so we can see what you do & exactly how Oracle responds.

2701424

I am still trying to get in touch with the person who set this database up, but they haven't responded. What I can tell you is that all Oracle databases are supposed to make an export (I don't know what it is) and it is getting backed up daily by something called Comvault.

So this leads me to one question"

1. How can I figure out what my database is exporting and to where and how? Can I log into db2 as sysdba and run a query to figure this out? If so, could you help me do this?

Please help guru's.

Geeky

unknown-951199

you claim some problem exists, but have posted NOTHING to actually show us any evidence what is specifically wrong.

How do I ask a question on the forums?

https://forums.oracle.com/forums/thread.jspa?threadID=2174552#9360002

use COPY & PASTE so we can see what you do & exactly how Oracle responds that provides us evidence concerning the problem you are trying to solve.

Mark D Powell

Geeky, people cannot help you do not clearly define the problem and provide all related information usually in the form of copy and past of the actual commands ran, response received, etc....

- -

Is the file fair.ora a text file, that is, can you read the contents using a text editor like vi or notepad?  If so, is it in a format something like xxxx,1234,yyyyy,n,zzz where the comma might be '|' or a tab character.  If yes, then that is a delimited file and you can probably use sqlldr to insert the data into the target table.  See the Utilities manual for your version of Oracle.

- -

If fair.ora is not a text file then you need to know how it was created.  True backups are point in time copies of Oracle database data files made either using rman or manually usually with the database placed into backup mode.  In either case forward recovery of the database which means reapplying the archived redo logs is usually required after restoring the file.  Also a database file normally includes more than one table so you would likely also be overlaying the contents of numerous other tables if this is in fact a backup of a database file.  Wiping out other tables to restore the contents of just one file may not be acceptable to the owners of the other tables involved.

- -

It might be a good idea to perform a head on fair.ora as the result may identify what kind of file you are dealing with (character data, exp file, expdp, etc ...)

- -

HTH -- Mark D Powell --


Hemant K Chitale

>What I can tell you is that all Oracle databases are supposed to make an export (I don't know what it is) and it is getting backed up daily by something called Comvault

Which would mean that you'd need (1) restore the export from Comvault if it is no longer on disk (2) to import the data.

We don't know if the export is a full database export or a schema export (if you've come from a non-Oracle -- e.g. MS SQLServer -- backround : note that a database in oracle consists of multiple schemas).

The restore command requires knowledge of the Comvault utilities.

The import command required to import the table depends on how the export was done.

Hemant K Chitale

andrewmy

If this is a real emergency get an Oracle consultant in and sit beside him and (hopefully) learn. Honestly. If time is critical there is too much you do not know about Oracle to effect a recovery with the incomplete information that you possess. With incomplete information, you need somebody who is experienced enough to investigate to find out the actual situation, formulate a recovery strategy and execute it. There are too many unknowns for generic advice.

Or we could keep asking you to check this or that for next few days to find out the situation before we can suggest any plan of action.

VinodN76

If the 'fair.ora' is a binary file then you can use the 'list backup' command of RMAN and then search for 'fair.ora' in the backuppiece names to verify whether it's a RMAN backup file...

1 - 14
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 28 2014
Added on Jul 30 2014
14 comments
3,399 views