Re: move database to new server/directory

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 16 Nov 2006 04:06:24 -0800
Message-ID: <1163678784.106848.117890@h48g2000cwc.googlegroups.com>

Helmut Schneider wrote:
> What's in a namespace (xml_at_ns.com) wrote:
>
> > OK! I feared this was a production situation.
>
> No, it's a playground.
>
> > Export/Import should not depend on ORACLE_HOME settings, so that can't
> > be te reason for the failure.
> > Which exact steps did you take to do the export/import?
>
> exp file=d:\export.dat full=yes log=d:\export.log
> No errors, no warnings.
>
> > What do you mean by 'All the config?' (specially when the directories are
> > different, the original uses separate directories for installation and
> > data, the copy doesn't (yet))
>
> I played around and made some settings like defining a mailrelay or a backup
> plan. Nothing important but I'd like to know if that data could be exported,
> too.
>
> > How many database schemas are involved? How much data? Could a default
> > installation and one user export/import do the job?
>
> I'm not a hundred percent sure how a "schema" is defined, but I have two
> custom table spaces and one database user. The dumpfile has a size of 160MB.
>
> Then I move the export file to the new server and:
> imp file=d:\export.dat full=yes log=d:\import.log
>
> The error log says:
>
> IMP-00003: ORACLE error 1119 encountered
> ORA-01119: error in creating database file 'D:\ORADATA\ORCL\MYDB.DBF'
> ORA-27040: file create error, unable to create file
> OSD-04002: unable to open file
>
> On the new server $ORADATA is D:\oracle\product\10.2.0\oradata\orcl and not
> D:\oradata\orcl
>
> Helmut

Precreate the tablespaces before performing the import. First, find out the tablespace names on the old server. Use SQLPlus, Excel, Access, or some other query tool to execute: SELECT

  T.NAME TABLESPACE_NAME,
  D.NAME FILENAME,
  D.BYTES

FROM
  V$DATAFILE D,
  V$TABLESPACE T
WHERE
  D.TS#=T.TS#
ORDER BY
  T.NAME; And then the same query on the new server: SELECT

  T.NAME TABLESPACE_NAME,
  D.NAME FILENAME,
  D.BYTES

FROM
  V$DATAFILE D,
  V$TABLESPACE T
WHERE
  D.TS#=T.TS#
ORDER BY
  T.NAME; Then, use SQLPlus to create the missing tablespaces, likely each item listed except SYSTEM. For example, if you have a tablespace named USER_DATA that needs to be created to contain 9GB of data (create larger than identified by the above to allow room for additional data), you could execute a statement like this: CREATE SMALLFILE TABLESPACE "USER_DATA" LOGGING DATAFILE 'D:\oracle\product\10.2.0\oradata\orcl\usrORCL01.dbf' SIZE 9000M AUTOEXTEND ON NEXT 40M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; The above will create a locally managed (rather than dictionary managed) tablespace that starts out as a roughly 9GB file, grows in 40MB increments to the full size of the hard drive array, and uses ASSM to automatically determine the extent size.

You will need to repeat the process for the temp tablespace(s): SELECT

  T.NAME TABLESPACE_NAME,
  D.NAME FILENAME,
  D.BYTES

FROM
  V$TEMPFILE D,
  V$TABLESPACE T
WHERE
  D.TS#=T.TS#;

If you need a temporary tablespace named TEMPORARY_DATA that is 600MB in size, you could create the tablespace using the following command: CREATE SMALLFILE TEMPORARY TABLESPACE "TEMPORARY_DATA" TEMPFILE 'D:\oracle\product\10.2.0\oradata\orcl\tmpORCL' SIZE 600M AUTOEXTEND ON NEXT 40M MAXSIZE 8000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; The above will create a locally managed temporary tablespace that starts out at 600MB, grows in 40MB increments, with a maximum size of roughly 8GB, and using 1MB extent sizes.

I suggest that you make changes to the export command: exp system/my_old_system_pass_at_orcl file=d:\export.dat log=d:\export.log FULL=y GRANTS=y INDEXES=y ROWS=y CONSTRAINTS=y STATISTICS=none DIRECT=y CONSISTENT=y COMPRESS=n

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Nov 16 2006 - 06:06:24 CST