Home » RDBMS Server » Server Administration » Transporting Datafiles from one server to another (10.2.0.3, Win2k3)
Transporting Datafiles from one server to another [message #377472] Tue, 23 December 2008 03:27 Go to next message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member
Hello,

Is it possible to transfert a datafile from one database, to another database? If yes, could you explain to me how to do that and the conditions that have to be satisfied?

Thanks in advance.
Re: Transporting Datafiles from one server to another [message #377475 is a reply to message #377472] Tue, 23 December 2008 03:32 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Tablespaces can be transported between databases, but not individual data files.
Re: Transporting Datafiles from one server to another [message #377480 is a reply to message #377475] Tue, 23 December 2008 03:49 Go to previous messageGo to next message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member
Ok. Once transporting a tablespace, I guess only the logical content is transported,isn't it? If yes, how do we handle the datafiles contained in the transported tablespace?
Re: Transporting Datafiles from one server to another [message #377483 is a reply to message #377480] Tue, 23 December 2008 03:53 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
The physical datafiles are copied or FTP'ed to the remote system together with some metadata that you export with expdp.
Re: Transporting Datafiles from one server to another [message #377505 is a reply to message #377483] Tue, 23 December 2008 05:01 Go to previous messageGo to next message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member
Is datapump export the only way to export the metadata?-I prefer the old good imp/exp utilities- Sad
Re: Transporting Datafiles from one server to another [message #377535 is a reply to message #377505] Tue, 23 December 2008 06:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes you can use exp/imp.

Regards
Michel
Re: Transporting Datafiles from one server to another [message #377551 is a reply to message #377535] Tue, 23 December 2008 07:56 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
I've started a transportable tablespaces article just for you. Please feel free to expand on it.
Re: Transporting Datafiles from one server to another [message #378447 is a reply to message #377551] Tue, 30 December 2008 08:13 Go to previous messageGo to next message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member
Hello,

Happy new year to all. First of all, sorry for replying late. I know the less to do when they give u a tip is tell whether it helped.

I went through the article you wrote and It helped me. After I "prepared" the
tablespace I was going to transport, I entered the export command (I used
the old exp syntax) suggested in the document and below is what I got:

Microsoft Windows XP [version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrateur>set oracle_sid=m4prod

C:\Documents and Settings\Administrateur>exp system/m4prod transport_tablespace=
yes tablespaces=meta4 constraints=no triggers=no file='c:\m4prodtts.dmp'

Export: Release 10.2.0.3.0 - Production on Mar. DÚc. 30 10:05:55 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


ConnectÚ Ó : Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Product
ion
With the Partitioning, OLAP and Data Mining options
EXP-00044: seule une connexion SYSDBA peut rÚaliser un import Point-in-time Reco
very ou Transportable Tablespace
EXP-00000: ProcÚdure d'export terminÚe avec erreur

C:\Documents and Settings\Administrateur>
The error says that only a sysdba connection can realise an import point-in-time recovery
or transportable tablespace.
Then I did something you might find stupid: I granted the sysdba privilege to system hoping something
dandy would come out of that. But I got the same result. Then I tried the following:

C:\Documents and Settings\Administrateur>exp sys/m4prod transport_tablespace=yes
 tablespaces=meta4 constraints=no triggers=no file='c:\m4prodtts.dmp'

Export: Release 10.2.0.3.0 - Production on Mar. DÚc. 30 10:18:17 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


EXP-00056: Erreur ORACLE 28009 rencontrÚe
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
Nom utilisateur : / as sysdba
and the export started.

Using the OS copy utility, I copied the .dmp that was generated as a result
of the export along with the datafile that belongs to the transported tablespace
to my target database.
As the tablespace wasn't existing in my target database, I didn't need to drop it.
So I ran the following command:
imp system/prod transport_tablespace=y file='d:\m4prodtts.dmp' datafile='e:\oradata\meta4\m4prod.dbf'

As the user did not exist on the target database, the import command
first returned an error. I then created the user meta4 but I could assign
it a default tablespace so I let his default tablespace be the system table.
I ran the imp command a second time and it worked. After that, I altered the user m4prod
on the target database and set its default tablespace to meta4.


I have 4 questions:

1- why should the connection be as sysdba in order
to perform this type of export?

2- How to configure such a connection?

3- why did the user contained in the tablespace being transported
was not ..."transported" along with the other objects?

4- Is there a workaround to the error related the user, as the one I got when trying to import the transportable
tablespace?

Thanks in advance.
Re: Transporting Datafiles from one server to another [message #378468 is a reply to message #378447] Tue, 30 December 2008 09:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1- Because Oracle says so
2- exp userid='/ as sysdba' ...
3- a user is NOT contain in a tablespace, only objects are contained in tablespace
4- precreate the user

Regards
Michel
Re: Transporting Datafiles from one server to another [message #378481 is a reply to message #378468] Tue, 30 December 2008 10:34 Go to previous messageGo to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
One thing that always through me off when cycling between UNIX and Windows servers is the command syntax for conn as sysdba. Michel provided the Unix connection string, but this will not work in Windows Server.

For Windows server, start the RDBMS listener and Database services (or ensure they are running). Then open a MKS environment shell (located on DBtier - %ORACLE_HOME%\envshell.cmd)

From command prompt type sqlplus "sys as sysdba" and provide the sys password when prompted. I'm not sure why this difference exists, but sqlplus '/ as sysdba' does not work for standard Windows configurations.
Re: Transporting Datafiles from one server to another [message #378486 is a reply to message #378481] Tue, 30 December 2008 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Michel provided the Unix connection string, but this will not work in Windows Server.

Are you sure?
C:\>exp userid='/ as sysdba'

Export: Release 10.2.0.4.0 - Production on Mar. DÚc. 30 16:49:40 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SYS
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SYS ^C

C:\>sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Mar. DÚc. 30 18:00:30 2008

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

***SYS***>

Regards
Michel
Re: Transporting Datafiles from one server to another [message #378488 is a reply to message #378486] Tue, 30 December 2008 12:20 Go to previous messageGo to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Sorry about that, I should have clarified. It will not work on 9i. I have yet to upgrade our DB's to 10g.

Sorry for the confusion,
Steve
Re: Transporting Datafiles from one server to another [message #378691 is a reply to message #378488] Wed, 31 December 2008 06:07 Go to previous message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member
Thank you for all of your assistance. I put all the suggestions arose in this thread on practice - even those concerning the connection as sysdba- and they all worked fine.

Thanks again and happy new year.
Previous Topic: Account Lock
Next Topic: Oracle shutdown immediate hang
Goto Forum:
  


Current Time: Sun Dec 11 04:10:38 CST 2016

Total time taken to generate the page: 0.12372 seconds