| Copy database [message #56982] |
Mon, 12 May 2003 07:35  |
Dik
Messages: 27
Registered: June 2002
|
Junior Member |
|
|
Hi,
I have a database on Unix and I would like to have the same database on windows NT.(with the same tablespaces, datafiles,users....,the same schema). How to do that? Is Export of full database enough?
|
|
|
|
|
|
| Re: Copy database [message #56988 is a reply to message #56982] |
Mon, 12 May 2003 10:03  |
psmyth
Messages: 81
Registered: October 2002
|
Member |
|
|
yup full export will be fine, but I'd create the tablespaces and datafiles first - and keep a copy of the sql scripts you used to do it, so you can do it again easily if you need to ;-)
I usually do a 'show=y' import first, and then strip out the 'create user' commands (search the log from the import for each username and then you can make a sql script to create each user)... often you'll find odd things that need to be fixed. Don't bother creating each of the tables in each schema, they'll get created when you import the schema. Its a bit painful to get the formatting sorted out (removing all the quotes etc), but its worth the effort.
Once you have the db built with all the tablespaces etc, and you've run the 'create user' scripts, you can run an import for each schema... eg:
imp system/manager file=full_export_of_unix_db.dmp fromuser={schemaname} touser={schemaname} ignore=y log=imp_schemaname.log
Do that for each user, and think about what order you do them in... if one schema has 'grants' from another user, it might be best to take that into account ;-)
Doing it all this way means you have 'create' scripts for future use, you have logs of the creation of each schema, and its a lot easier to spot anything that didn't work... and you only want to import your application schemas, not the Oracle 'internal' schemas (sys, dbsnmp etc).
Also, when you are running the 'full' export from the unix db, specify 'consistent=y'.
|
|
|
|
| Re: Copy database [message #56992 is a reply to message #56982] |
Mon, 12 May 2003 13:02 |
Trifon Anguelov
Messages: 514
Registered: June 2002
|
Senior Member |
|
|
Yes and no.
You have to first take a export with ROWS=N, then get the tablespace DDL from the dmp file, edit the paths (tehy will be different from the UNIX ones in Windows), then run the DDL to pre-create the tablespaces.
Then take another export with ROWS=Y, adn when importing use IGNORE=Y, to ignore tablespace existence.
Hope that helps,
clio_usa
OCP - DBA
Visit our Web site
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
