Database Import

Printable View

08-09-2002, 05:28 AM
Upeshp

Hi friends,

In my windowsNT box there was one database, now due to disk crash the database is not there. I have installed windowsNT and Oracle. Now i want to restore whole DB with export backup(latest one), but i don't know the structure of earlier DB (datafiles path and all) so How do i proceed with import ??

I don't have anyother backup except EXPORT.

Thnaks,
08-09-2002, 06:05 AM
ales

Hi,
datafiles paths are not important here.
You have to create all tablespaces that were in the old database.
In order to find out their names you may directly look in the dumpfile with an editor or use

Code:

imp user/pwd full=y indexfile=schema.sql

In the schema.sql you'll see the create statements for all objects with tablespace names.
HTH
08-09-2002, 06:40 AM
helpme

mebbe replacing IMP with EXP would work!!
08-09-2002, 06:55 AM
aarroyob

Quote:

Originally posted by helpme
mebbe replacing IMP with EXP would work!!

Sleep a bit and try again helpme :D

Cheers

Angel
08-09-2002, 07:31 AM
Upeshp

Hi Ales

*************************************************
imp user/pwd full=y indexfile=schema.sql

In the schema.sql you'll see the create statements for all objects with tablespace names
*************************************************
where to give this command, I think it will start importing data. How can i know the tablespace names and size from export.dmp file ??

Any other way.....? any other suggestions ??
08-09-2002, 08:01 AM
helpme

lol....aarroyob

yeah..i deff need those..or mebbe need to read more books!!!*rolling eyes*

using the indexfile option doesnt import data,,..just creates a file with the deff!!!
08-09-2002, 08:02 AM
ramaral

Ales is correct:

http://metalink.oracle.com/metalink/...1&p_showHelp=1

This may make it a little clearer.
08-09-2002, 08:13 AM
sandycrab

Upeshp
-----------------------------------------
where to give this command, I think it will start importing data. How can i know the tablespace names and size from export.dmp file ??
-----------------------------------------

You can not find the tablespace related info from the export dump. Simply write IMP command on the C:\ .

Sandy
08-09-2002, 08:46 AM
srlhome00

Upeshp:

If you open your export dump file, you will find at the very beginning of the tablespace structure you are looking for.

Susan
08-09-2002, 11:33 AM
Upeshp

Dear Susan,

Please tell me How to open export dump file, I really don't know.....

Thanks
08-09-2002, 12:47 PM
srlhome00

If you use windows OS, you can open the file from notepad. On the unix OS, you can use "more" or "vi". The tablespace info is just right at the beginning of file.

Susan
08-09-2002, 12:56 PM
Upeshp

Hello,

U mean to say that i have to open export.dmp file in notepad in winOS. Are u sure ?
08-09-2002, 01:13 PM
srlhome00

I use textpad on Win OS.
In your case, you can use 'head' command on the Unix.

Susan
08-09-2002, 01:17 PM
srlhome00

I mix your post with another post. Sorry.

Yes! The answer is using the notepad or any text editor. If you peformed a full export of the database, you will see the talespace structure right at the top.

Susan
08-09-2002, 01:20 PM
Upeshp

Hello,

Still my question is not answered ?

I want to know the file name .......I thought export.dmp file is been read only by import utility. So please tell me which file i have to open it NOTEPAD. I am going to do it on WindowsNT.
08-09-2002, 01:25 PM
ramaral

Yes dmp file is a binary file that is read by Oracle utilities. You can try and edit it with notepad but most of it will be garbage and very likely the dmp file will no longer be valid even if you do not save the file through notepad (I learned this the hard way).

Try this:

You are at the dos prompt:

c:>
c:>\export ORACLE_SID=
c:>imp system/ file=export.dmp full=y indexfile=c:\fullschema.sql

once the import has been completed edit the c:\fullschema.sql file with notepad for whatever changes you want to do or to find out the tablespaces info.

Done.

Just try that and see what happens. No data will get imported into the database when you use the indexfile parameter during import.

This is what oracle writes in their manual:
INDEXFILE
Default: none

Specifies a file to receive index-creation statements.

When this parameter is specified, index-creation statements for the requested mode are extracted and written to the specified file, rather than used to create indexes in the database. No database objects are imported.

If the Import parameter CONSTRAINTS is set to y, Import also writes table constraints to the index file.

The file can then be edited (for example, to change storage parameters) and used as a SQL script to create the indexes.

To make it easier to identify the indexes defined in the file, the export file's CREATE TABLE statements and CREATE CLUSTER statements are included as comments.

Perform the following steps to use this feature:

Import using the INDEXFILE parameter to create a file of index-creation statements.

Edit the file, making certain to add a valid password to the connect strings.

Rerun Import, specifying INDEXES=n.

(This step imports the database objects while preventing Import from using the index definitions stored in the export file.)

Execute the file of index-creation statements as a SQL script to create the index.

The INDEXFILE parameter can be used only with the FULL=y, FROMUSER, TOUSER, or TABLES parameters.

http://doc.oracle.com/cd_a91034/DOC/...02.htm#1005473

[Edited by ramaral on 08-09-2002 at 01:30 PM]
08-09-2002, 01:28 PM
srlhome00

export.dmp is the default dump filename. I don't know your export filename. If you didn't specify any dump filename, then export.dmp will be the file you want to read.

Go ahead to open this file and see if any information is useful for you.

Susan
08-09-2002, 01:35 PM
Upeshp

Hi,

I just went through the link of Oracle documentation, still there are two doubts,
1. Will i get all tablespaces name from the file ?

2. What happened after creating a tablespace i will rerun the full import with INDEX=Y ?

Thanks,
08-09-2002, 01:43 PM
ramaral

Yes you will get the tablespace name from the exp file.

Once you get the schema information you can edit the fullschema.sql file for the tablespaces info making sure the datafile locations are fine for you server. Then you can run the fullschema.sql that you just finished editing in sqlplus and let it create the schemas and objects. Once this is done then import the dmp file with ignore=y

ie.

imp system/ file=export.dmp full=y ignore=y log=schemaimp.log

Once completed,review the schemaimp.log to make sure the import was successful. You will see a number of imp-0015 errors. Most of them, if not all of them, are fine because those will be system objects that you have created from the database's initial creation and from the fullschema.sql file - those are nothing to worry about.
08-09-2002, 02:02 PM
ramaral

alternatively you can also do this

c:\imp system\ file=export.dmp full=y show=y log=export.log

This will also spool out to the export.log your tablespace create statements without actually importing any data including datafile locations. You can actually run this one if you want because you will not have to edit it to remove the rem statements generated through the other method.

Now that I look at the 2 methods this one might be better for you in the long run.
08-09-2002, 07:18 PM
vndba

Hi Upeshp:

You will need to create a new database and its associated tablespaces before you can import the dump file. Once the database is created, do an import with the option 'SHOW=Y' to get the name and size of the tablespaces. Once the tablespaces are created, the import should be straight forward. Good luck.

John
08-10-2002, 12:10 AM
Upeshp

Thank to all of you People.

Great help.