Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using exp/delete/imp to recreate database

Status
Not open for further replies.

HeathRamos

IS-IT--Management
Apr 28, 2003
112
US
I am having trouble restoring data from a production database to a test database.

I restored thr datafiles from a cold backup, created the control files and tried to do the alter database resetlogs command but got an error (ora-0600). Oracle instructed me to change the init.ora file to allow resetlog corruption in order to get the database up. After doing this, they said I need to do a full database export, delete the database, created it and do a full database import.

Well...

While doing the full database export, I got an error:

. about to export SPACEMGR's tables via Conventional Path ...
. . exporting table QUEST_COM_PRODUCTS
EXP-00056: ORACLE error 376 encountered
ORA-00376: file 49 cannot be read at this time
ORA-01111: name for data file 49 is unknown - rename to correct file
ORA-01110: data file 49: '/usr/oracle/product/8.1.7/dbs/MISSING00049'

Looks like there is an error for every table in that schema (as well as one other schema).

Also...when I do a select name from v$datafile, I get:
/usr/oracle/product/8.1.7/dbs/MISSING00048
/usr/oracle/product/8.1.7/dbs/MISSING00049

Seems to be a problem with one particular tablespace and a couple of schemas.

Any idea what could cause this and how to fix it?

One thing that is different is that our production server had an application installed on it that the test server does not (Spotlight on Oracle as well as Spacemanager). I created the tablespace in question and installed the server side objects into it on the production server.
 
Heath,

How does this sound for an alternative:

1) Create a fresh, empty test database.
2) Do either a full database export (or a schema-by-schema export) from your production database.
3) Import your dumpfile(s) into the test database.

You should not experience and difficulties under this scenario.

Let us know the outcome.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.
 
That is possible (assuming I have the needed hard drive space for the dump files on the production server) but I kind of want to have the data from this particular backup.
 
also...once I delete the database (removing files using root) and log on as a user (oracle8i) that points to that sid in it's profile, won't it give me an error?
 
Heath,

I'm not quite sure of the context from which you are asking your question. Are you talking about re-creating the database following your removal of the files for the former database?

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.
 
I am exporting data from a database, deleting the database I just exported from, recreating the database with the same sid, and importing that data back into it.
 
Heath,

Just to clarify, when you say "database" are you using that term in the Oracle sense (i.e., the entire Oracle database instance, made up of many database users/schemas), or do you mean "database" in the non-Oracle sense (i.e., a single schema of tables)?

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.
 
1. ORACLE_SID=TEST
exp system/<password> file=export.dmp full=y log=export.log

2. remove the TEST database from server by deleting datafiles, logfiles, control files and all file references to that oracle sid.

3. create empty database with just the system tablespace and same sid name as previously used

4. import the dump file
 
Heath,

A couple of thoughts:

1) If you had trouble importing into TEST, then it seems to me that any export from that instance could/would be spurious.

2) You do not want to have SYSTEM be the only tablespace in the resurrected version of the TEST database/instance. You should never import anything into that tablespace since it should be just for objects in the data dictionary. You will want to create some application tablespace into which the import can place all of the non-data-dictionary, application objects.

Let us know your thoughts.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.
 
I was under the impression that when you did a full database export and import, the tablespaces would be created automatically as long as you didn't have the ignore=y option.

(That was from the top of my head, since the notes are at work).
 
Heath,

You may very will be right on the "full database export/import" topic with regard to tablespace recreation. I just always do my exports/imports by schema, which does not do the tablespace creates. Sorry for the miscue.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top