Re: Tablespaces and Export/Import

Steve Sochacki wrote in message
<01bd356e$94689b00$b42fe4c7_at_SAMHC180.sys.eds.com>...
>I have a database on one physical server that has two drives, C: and D:. I
>have different tablespaces distributed across these drives. I did a full
>database export and went to import it on another server (which is RAID)
>with 5 drives and the same tablespaces spread across all 5.

But are you sure this is what you want? You might be better pulling your redo (and other write-intensive parts of your database off RAID (level 5 I assume from what you've written above?), have a think first and do some planning.

>When the
>import runs, I get error mesages stating it can't create some directories,
>loads some of the tablepsaces correctly but most of the tables end up in
>the USER tablespace.

Are you sure the new server's tablespaces have the correct names and are of sufficient size to hold the data being imported? A full import should work, even though you've created the tablespaces first.

Did you 'log' the output of the import to a file. It's might be an idea to post a few lines of the errors in it here.

>The whole database is imported and works correctly
>but I lost the efficiency of having the indexes and data on separate
>drives.

Er, you've lost me here. If all you have on the new server is one big RAID array and nothing else you can't split the I/O of indexes and data (or the other parts of your database which might not be suitable for RAID 5 - see above, as I'm assuming you have RAID 5). Look at adding additional drives outside of the RAID array, and/or looking at an alternative RAID set-up (i.e., RAID 0+1). This really is part of your physical database design, and it will have an impact for a long time to come - in terms of maintenance, performance, expansion and backup & recovery. Take you time and plan it (and implement it) properly; Get some outside help if you need it, don't be afraid to admit you need it.

>Is there a better way to
>move all the database objects (stored procedures, tables, synonyms.....)
>than an export/import? Any help would be appreciated.

IMP/EXP should work just fine. Yes, you can do physical file copies and then adjust the control files via 'ALTER DATABASE' etc. commands to map to the new directories, but it may be easier for you just to use IMP/EXP. See above.

Let me know if you need any more help.

Steve Phelan. Received on Mon Feb 09 1998 - 00:00:00 CST