| transportable tablespace [message #513038] |
Thu, 23 June 2011 06:57  |
 |
x-oracle
Messages: 380
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  |
Frank Naude
Messages: 4579
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 #513953 is a reply to message #513701] |
Thu, 30 June 2011 07:07 |
|
x-oracle
Messages: 380
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 #514176 is a reply to message #514065] |
Sat, 02 July 2011 04:50 |
|
x-oracle
Messages: 380
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>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
