Re: move database to new server/directory

Helmut Schneider wrote:
> Charles Hooper (hooperc2000_at_yahoo.com) wrote:
> > Helmut Schneider wrote:
> [...]
> >> 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
> >
> > 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
>
> I'm deeply impressed and thank you very much. Worked like a charm.
>
> Helmut
>
> --
> Please do not feed my mailbox, Swen still does his job well

Next time use RMAN, rename datafile, or backup controlfile method.

Never, never, never export.....It's now not the same db any more... You've altered your prod data and now own the problem if there are any issues.

I'm happy that it worked for you though. Cheers.... Received on Thu Nov 16 2006 - 16:25:01 CST