Home » RDBMS Server » Backup & Recovery » transportable tablespace (windows 7,oracle 10.2)
transportable tablespace [message #513038] Thu, 23 June 2011 06:57 Go to next message
x-oracle
Messages: 345
Registered: April 2011
Location: gujarat
Senior Member
hello

i have one question regarding transportable tablespace. i want to move my t1 tablespace from red database that reside on windows 7 64bit to orcl database its reside on windows xp 32 bit

i perform this following steps on red database

Conn / as sysdba


SQL> select * from nls_database_parameters where parameter like '%SET%';

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET WE8MSWIN1252
NLS_NCHAR_CHARACTERSET AL16UTF16



SQL> select platform_name from v$database;

PLATFORM_NAME
--------------------------------------------------------------------------------

Microsoft Windows IA (32-bit)


SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('t1',TRUE);

PL/SQL procedure successfully completed.


SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

SQL> select tablespace_name,file_name from dba_data_files
2 where tablespace_name='T1';

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------

T1
F:\ORACLE\PRODUCT\10.2.0\ORADATA\RED\T0.DBF


SQL> CREATE OR REPLACE DIRECTORY PRACTICE AS 'F:\practice';


SQL> GRANT READ,WRITE ON DIRECTORY PRACTICE TO PUBLIC;

Grant succeeded.


SQL> EXIT

F:\oracle\product\10.2.0\db_1\bin>EXPDP SYSTEM/ORACLE@RED DUMPFILE=T1.DMP DIRECTORY=PRACTICE TRANSPORT_TABLESPACES=T1



and the step i perform on orcl that reside on windows xp 32 bit


SQL> select platform_name from v$database;

PLATFORM_NAME
--------------------------------------------------------------------------------

Microsoft Windows IA (32-bit)


SQL> select * from nls_database_parameters where parameter like '%SET%';

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET WE8MSWIN1252
NLS_NCHAR_CHARACTERSET AL16UTF16

my orcl database home is D:/oracle/product/10.2.0
and orcl database datafile reside on D:/oracle/product/10.2.0/oradata/orcl\

so can u suggest me what can i do on this orcl database to import this database

and when i also perform this step on orcl database also

D:\oracle\product\10.2.0\db_1\BIN>set oracle_sid=orc

D:\oracle\product\10.2.0\db_1\BIN>set oracle_sid=orcl

D:\oracle\product\10.2.0\db_1\BIN>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jun 23 15:18:14 2011

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

connected to target database: ORCL (DBID=1277532456)

RMAN> convert tablespace t1
2> DB_FILE_NAME_CONVERT
3> ('F:\ORACLE\PRODUCT\10.2.0\ORADATA\RED\T0.DBF','D:\ORACLE\PRODUCT\10.2.0\ORAD
ATA\ORCL\T0.DBF')
4> ;

Starting backup at 23-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 06/23/2011 15:21:49
RMAN-20202: tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "T1"
s
so can you suggest me what can i do

Re: transportable tablespace [message #513094 is a reply to message #513038] Thu, 23 June 2011 11:11 Go to previous messageGo to next message
Frank Naude
Messages: 4420
Registered: April 1998
Senior Member
You can only use CONVERT TABLESPACE when you are connected to the source database.

In your case, use CONVERT DATAFILE (you are connected to the destination database!)

Best of luck.

Frank
Re: transportable tablespace [message #513141 is a reply to message #513094] Fri, 24 June 2011 01:01 Go to previous messageGo to next message
x-oracle
Messages: 345
Registered: April 2011
Location: gujarat
Senior Member
Frank i also did this on my orcl database target database

D:\oracle\product\10.2.0\db_1\BIN>set oracle_sid=orcl

D:\oracle\product\10.2.0\db_1\BIN>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jun 23 15:18:14 2011

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

connected to target database: ORCL (DBID=1277532456)

RMAN> convert tablespace t1
2> DB_FILE_NAME_CONVERT
3> ('F:\ORACLE\PRODUCT\10.2.0\ORADATA\RED\T0.DBF','D:\ORACLE\PRODUCT\10.2.0\ORAD
ATA\ORCL\T0.DBF')
4> ;

and i got this error msg


Starting backup at 23-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 06/23/2011 15:21:49
RMAN-20202: tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "T1"

=====

so can you suggest me which systax i have to run and on which database
Re: transportable tablespace [message #513150 is a reply to message #513141] Fri, 24 June 2011 01:33 Go to previous messageGo to next message
Frank Naude
Messages: 4420
Registered: April 1998
Senior Member
And you are still using "convert tablespace"? Please look-up "convert datafile"!
Re: transportable tablespace [message #513160 is a reply to message #513150] Fri, 24 June 2011 02:39 Go to previous messageGo to next message
x-oracle
Messages: 345
Registered: April 2011
Location: gujarat
Senior Member
ok frank thanks for giving me reply
i am sorry for that ok now i have did this on my this red database


F:\oracle\product\10.2.0\db_1\bin>set oracle_sid=red

F:\oracle\product\10.2.0\db_1\bin>set db_name=red

F:\oracle\product\10.2.0\db_1\bin>rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Fri Jun 24 12:31:36 2011

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

connected to target database: RED (DBID=2268875332)

RMAN> convert datafile 'F:\ORACLE\PRODUCT\10.2.0\oradata\red\t0.dbf'
2> db_file_name_convert
3> 'F:\oracle\product\10.2.0\oradata\red\t0.dbf','D:\red';

Starting backup at 24-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=F:\ORACLE\PRODUCT\10.2.0\ORADATA\RED\T0.DBF
converted datafile=D:\RED
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:17
Finished backup at 24-JUN-11

RMAN>

====

now can u suggest me what can i do now on my orcl database can u suggest tme this imp syntax
Re: transportable tablespace [message #513518 is a reply to message #513150] Tue, 28 June 2011 00:47 Go to previous messageGo to next message
x-oracle
Messages: 345
Registered: April 2011
Location: gujarat
Senior Member
frank what happnd still you not givwe me any reply..

plzz help me

if you have any solution regarding this my question
Re: transportable tablespace [message #513528 is a reply to message #513518] Tue, 28 June 2011 01:24 Go to previous messageGo to next message
Frank Naude
Messages: 4420
Registered: April 1998
Senior Member
The examples in the manual are quote good, have you looked at them?
There are also tons of tutorial all over the Internet that you can study.
Re: transportable tablespace [message #513569 is a reply to message #513528] Tue, 28 June 2011 05:10 Go to previous messageGo to next message
x-oracle
Messages: 345
Registered: April 2011
Location: gujarat
Senior Member
when i use this systax
IMP TRANSPORT_TABLESPACE=Y DATAFILES=D:\POM\T0.DBF FILE=D:\POM\T1.DMP

i got this error

IMP-00010: not a valid export file, header failed verification
IMP-00000: Import terminated unsuccessfully

so can you help me
Re: transportable tablespace [message #513570 is a reply to message #513569] Tue, 28 June 2011 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 59775
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Dump file must be transfered in BINARY mode.
Did you do it in this mode?

Regards
Michel
Re: transportable tablespace [message #513584 is a reply to message #513570] Tue, 28 June 2011 05:48 Go to previous messageGo to next message
x-oracle
Messages: 345
Registered: April 2011
Location: gujarat
Senior Member
michel i am using dmpfile which i have created by this systam

F:\oracle\product\10.2.0\db_1\bin>EXPDP SYSTEM/ORACLE@RED DUMPFILE=T1.DMP DIRECTORY=PRACTICE TRANSPORT_TABLESPACES=T1
Re: transportable tablespace [message #513701 is a reply to message #513570] Wed, 29 June 2011 01:07 Go to previous messageGo to next message
x-oracle
Messages: 345
Registered: April 2011
Location: gujarat
Senior Member
please help me michel in my this senario.

actualy i want to transport tablespace from my windows 7 pc to my windows xp pc

i have oracle on my windows 7 pc on f drive and on my windows xp pc my oracle on d drive

i have post all step i have did .

but still i cannot get successful result can you help me
Re: transportable tablespace [message #513953 is a reply to message #513701] Thu, 30 June 2011 07:07 Go to previous messageGo to next message
x-oracle
Messages: 345
Registered: April 2011
Location: gujarat
Senior Member
i did also this step i have two database on my this same machine windows 7

orcl and red

source database = red

traget database = orcl


i have create one tablespace name orange on red database by using this syntax

SQL > create tablespace orange
datafile 'F:\oracle\product\10.2.0\oradata\red\orange.dbf' size 50m;

than after i have create table and insert some row into this table by using this

SQL > scott/tiger

SQL > create table dp (no number(20)) tablespace orange;

than after i have insert some raw into this dp table

SQL >insert into dp values(20);

SQL >insert into dp values(30);

SQL >insert into dp values(40);

sql > commit;

than i have put this tablespace orange on read only mode by using this syntax

SQL > alter tablespace orange read only ;

than i have export this tablespace by using this


F:\oracle\product\10.2.0\db_1\bin>EXPDP SYSTEM/ORACLE@RED DUMPFILE=ORANGE.DMP DIRECTORY=PRACTICE TRANSPORT_TABLESPACES=ORANGE

than after i have convert datafile by using this


F:\oracle\product\10.2.0\db_1\bin>set oracle_sid=red

F:\oracle\product\10.2.0\db_1\bin>set db_name=red


F:\oracle\product\10.2.0\db_1\bin>rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Thu Jun 30 16:35:05 2011

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

connected to target database: RED (DBID=2268875332)


RMAN> convert datafile 'F:\ORACLE\PRODUCT\10.2.0\oradata\red\orange.dbf'
2> db_file_name_convert
3> 'F:\oracle\product\10.2.0\oradata\red\orange.dbf','F:\oracle\product\10.2.0\oradata\orcl\orange.dbf';
Starting backup at 30-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=134 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=F:\ORACLE\PRODUCT\10.2.0\ORADATA\RED\ORANGE.DBF
converted datafile=F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ORANGE.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:08
Finished backup at 30-JUN-11

RMAN> exit

than after i have move this dump file to orcl at

F:\oracle\product\10.2.0\admin\orcl\dpdump

and than after i use this syntax to import on orcl by using this syntax

F:\oracle\product\10.2.0\db_1\bin>impdp system/oracle@orcl dumpfile=orange.dmp d
irectory=data_pump_dir transport_datafiles='F:\oracle\product\10.2.0\oradata\orc
l\orange.dbf'

Import: Release 10.2.0.5.0 - Production on Thursday, 30 June, 2011 17:05:31

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/********@orcl dumpfile=
orange.dmp directory=data_pump_dir transport_datafiles='F:\oracle\product\10.2.0
\oradata\orcl\orange.dbf'
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-00721: changes by release 10.2.0.5.0 cannot be used by release 10.2.0.3.0

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 17:05:34


i got this error so can you suggest me what can i do to successfuly transfter this tablespace from red database to orcl database
Re: transportable tablespace [message #513967 is a reply to message #513953] Thu, 30 June 2011 07:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59775
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot transport a higher version into a lower one as the older one cannot know how to read the new information.

Regards
Michel

[Updated on: Thu, 30 June 2011 07:45]

Report message to a moderator

Re: transportable tablespace [message #514064 is a reply to message #513967] Fri, 01 July 2011 03:26 Go to previous messageGo to next message
x-oracle
Messages: 345
Registered: April 2011
Location: gujarat
Senior Member
michel i do this on same oracle i have install and on same machine

i have create two database here name red and orcl

and i have create one tablespace in red database and than i have to transfer this tablespace into my orcl database

Re: transportable tablespace [message #514065 is a reply to message #514064] Fri, 01 July 2011 03:38 Go to previous messageGo to next message
Michel Cadot
Messages: 59775
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Upgrade your orcl database.

Regards
Michel
Re: transportable tablespace [message #514176 is a reply to message #514065] Sat, 02 July 2011 04:50 Go to previous message
x-oracle
Messages: 345
Registered: April 2011
Location: gujarat
Senior Member
TRANSPORTABLE TABLESPACE


NOTE:-
SOME OF STEPS USEFUL TO YOU

Conn / as sysdba
SQL> desc gv$transportable_platform
SQL> SELECT * FROM v$transportable_platform ORDER BY 1;
SQL> select platform_name from v$database;
SQL> select * from nls_database_parameters where parameter like '%SET%';

HERE WE HAVE PERFORM TABLESAPCE NAME MANGO FROM ORCL DATABASE TO RED DATABASE THIS ORCL DATABASE AND RED DATABASE ARE ONE THE SAME SYSTEM.

Source database = orcl
Target database = red


 I have create one tablespace name mango on orcl (Source) database by using this syntax

F:\>cd oracle/product/10.2.0/db_1/bin
F:\oracle\product\10.2.0\db_1\bin>set oracle_sid=orcl
F:\oracle\product\10.2.0\db_1\bin>set db_name=orcl
F:\oracle\product\10.2.0\db_1\bin>sqlplus
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Jul 2 11:02:40 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Enter user-name: sys as sysdba
Enter password:

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

SQL > create tablespace mango
datafile 'F:\oracle\product\10.2.0\oradata\orcl\mango.dbf' size 50m;


 Than after i have create table name RJ1 in Scott Schema and insert some row into this table by using this

SQL> conn scott/tiger
Connected.
SQL>
SQL> CREATE TABLE RJ1 (NO NUMBER(10)) TABLESPACE MANGO;

Table created.

SQL> INSERT INTO RJ1 VALUES(100);

1 row created.

SQL> INSERT INTO RJ1 VALUES(200);

1 row created.

SQL> INSERT INTO RJ1 VALUES(300);

1 row created.

SQL> INSERT INTO RJ1 VALUES(400);

1 row created.

SQL> COMMIT;

Commit complete.

 Verify Tablespace Users is self-constrained and check violation

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('MANGO',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected


 Than i have put this tablespace Mango on read only mode by using this syntax

SQL> CONN SYS AS SYSDBA
Enter password:
Connected.
SQL>
SQL> alter tablespace mango read only;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
And Check mango datafile location by using this


 Then Check DataFile location oF Mango tablespace

SQL> select tablespace_name,file_name from dba_data_files
2 where tablespace_name='MANGO';

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------

MANGO
F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\MANGO.DBF

 After That I have convert the datafile location on orcl (Source) database by RMAN Because my red database on F:\oracle\product\10.2.0\oradata\red

F:\oracle\product\10.2.0\db_1\bin>set oracle_sid=orcl

F:\oracle\product\10.2.0\db_1\bin>set db_name=orcl

F:\oracle\product\10.2.0\db_1\bin>rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Fri Jul 1 16:46:58 2011

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

connected to target database: ORCL (DBID=1274406310)

RMAN> convert datafile 'F:\ORACLE\PRODUCT\10.2.0\oradata\orcl\mango.dbf'
2> db_file_name_convert
3> 'F:\oracle\product\10.2.0\oradata\orcl\mango.dbf','F:\oracle\product\10.2.0\o
radata\red\mango.dbf';

Starting backup at 01-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=138 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\MANGO.DBF
converted datafile=F:\ORACLE\PRODUCT\10.2.0\ORADATA\RED\MANGO.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:08
Finished backup at 01-JUL-11

RMAN> exit


Recovery Manager complete.



 Exporting Meta Data of Mango Tablespace in Practice directory

F:\oracle\product\10.2.0\db_1\bin>expdp system/oracle@orcL dumpfile=mango.dmp directory=practice transport_tablespaces=mango

Export: Release 10.2.0.5.0 - Production on Friday, 01 July, 2011 16:45:15

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/********@orcL dumpfile=
mango.dmp directory=practice transport_tablespaces=mango
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded

******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
F:\PRACTICE\MANGO.DMP
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 16:46:07

 Importing metadata of mango tablespace into RED (Target )Database

F:\oracle\product\10.2.0\db_1\bin>impdp system/oracle@red dumpfile=mango.dmp dir
ectory=practice transport_datafiles='F:\oracle\product\10.2.0\oradata\red\mango.dbf'


Import: Release 10.2.0.5.0 - Production on Friday, 01 July, 2011 16:55:14

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/********@red dumpfile=m
ango.dmp directory=practice transport_datafiles='F:\oracle\product\10.2.0\oradat
a\red\mango.dbf'
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 16:55:32


 Now Check it on your RED (target) database

Perform this steps on your Red(target) database

F:\oracle\product\10.2.0\db_1\bin>set oracle_sid=red

F:\oracle\product\10.2.0\db_1\bin>set db_name=red

F:\oracle\product\10.2.0\db_1\bin>sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Jul 1 16:57:05 2011

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

Enter user-name: sys as sysdba
Enter password:

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

SQL> select name from v$database;

NAME
---------
RED
SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
EXAMPLE
ORANGE
SHAAN
MANGO

9 rows selected.

SQL> conn scott/tiger
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
RJ1 TABLE
TEST TABLE
SH1 TABLE
DP TABLE
FGA_TEST TABLE

9 rows selected.

SQL> select * from rj1;

NO
----------
100
200
300
400

SQL> show user
USER is "SCOTT"
SQL> conn sys as sysdba
Enter password:
Connected.
SQL>

SQL> select owner,table_name,tablespace_name from dba_tables where tablespace_na
me='MANGO';

OWNER TABLE_NAME
------------------------------ ------------------------------
TABLESPACE_NAME
------------------------------
SCOTT RJ1
MANGO


SQL>












Previous Topic: Script to save users/privs before cloning
Next Topic: Restore datafiles in another database
Goto Forum:
  


Current Time: Tue Nov 25 15:23:28 CST 2014

Total time taken to generate the page: 0.09273 seconds