| Transporting Datafiles from one server to another [message #377472] |
Tue, 23 December 2008 03:27  |
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 #378447 is a reply to message #377551] |
Tue, 30 December 2008 08:13  |
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>
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
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 #378481 is a reply to message #378468] |
Tue, 30 December 2008 10:34 |
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 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
