| Trnasportable Tablespace [message #392572] |
Wed, 18 March 2009 08:12  |
rumman
Messages: 48
Registered: June 2007
Location: Bangladesh
|
Member |
|
|
Hi,
I am facing problem in Transport Tablespace using EXPDP/IMPDP.
I have two databases:
1)NEW
2)TRN
I did the following steps in order to transport a tablespace EX from NEW to TRN.
1) begin
sys.dbms_tts.transport_set_check('EX,EX_IND', TRUE);
end;
2)
select * from sys.transport_set_violations;
> NO ROWS RETURN
3)
alter tablespace ex read only;
alter tablespace ex_ind read only;
4)
expdp system/admin@new dumpfile=EX.dmp directory=exp transport_tablespaces='EX,EX_IND' transport_full_check=Y
I found exported successsfully.
5)
I copied the 2 datafiles from D:\new to E:\TRN, where
D:\new contains the datafiles for new and E:\TRN contains the datafiles for TRN.
6) Then I use the impdp cmd:
impdp system/admin@rmanback directory=exp dumpfile=exp:EX.DMP transport_datafiles='E:\TRN\SIT01.DBF','E:\TRN\EX_IND01.DBF'
Import successfully.
But I did not find any change in V$TABLESPACE, V$DATAFILE.
Note that the characterset is same for both the databases as both the databases are on the same machine with same ORACLE_HOME.
Please tell me if I am making any mistake in the steps.
|
|
|
|
|
|
| Re: Trnasportable Tablespace [message #392720 is a reply to message #392577] |
Thu, 19 March 2009 01:06  |
rumman
Messages: 48
Registered: June 2007
Location: Bangladesh
|
Member |
|
|
Hi,
Thanks for the reply. Next time I will try to follow the posting guideline.
Please give me a suggestion.
Details Export LOG:
;;;
Export: Release 10.1.0.2.0 - Production on Wednesday, 18 March, 2009 18:35
Copyright (c) 2003, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/********@new dumpfile=ex.dmp directory=exp transport_tablespaces='ex,ex_IND' transport_full_check=Y
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
D:\ORACLE\PRODUCT\10.1.0\ORADATA\EXP\EX.DMP
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 18:36
Detail Import Log:
;;;
Import: Release 10.1.0.2.0 - Production on Wednesday, 18 March, 2009 18:41
Copyright (c) 2003, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/********@rmanback directory=exp dumpfile=exp:ex.DMP transport_datafiles='E:\oracle\product\10.1.0\oradata\rmanback\ex01.DBF','E:\oracle\product\10.1.0\oradata\rmanback\ex_IND01.DBF'
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 18:41
V$TABLESPACE in SOURCE DB:
TS# NAME
---------- ------------------------------
0 SYSTEM
1 UNDOTBS1
2 SYSAUX
4 USERS
3 TEMP
6 EXAMPLE
7 SIT
8 SIT_IND
V$DATAFILE in source DB;
D:\ORACLE\PRODUCT\10.1.0\ORADATA\NEW\SYSTEM01.DB READ WRITE
D:\ORACLE\PRODUCT\10.1.0\ORADATA\NEW\UNDOTBS01.DBF READ WRITE
D:\ORACLE\PRODUCT\10.1.0\ORADATA\NEW\SYSAUX01.DBF READ WRITE
D:\ORACLE\PRODUCT\10.1.0\ORADATA\NEW\USERS01.DBF READ ONLY
D:\ORACLE\PRODUCT\10.1.0\ORADATA\NEW\EXAMPLE01.DBF READ ONLY
D:\ORACLE\PRODUCT\10.1.0\ORADATA\NEW\SIT01.DBF READ ONLY
D:\ORACLE\PRODUCT\10.1.0\ORADATA\NEW\SIT_IND01.DBF READ ONLY
v$TABLESPACE in target DB:
TS# NAME
0 SYSTEM
1 UNDOTBS1
2 SYSAUX
4 USERS
3 TEMP
v$DATAFILE in target db:
E:\ORACLE\...\SYSTEM01.DBF READ WRITE
E:\ORACLE\...\UNDOTBS01.DBF READ WRITE
E:\ORACLE\...\SYSAUX01.DBF READ WRITE
E:\ORACLE\...\USERS01.DBF READ WRITE
|
|
|
|
| Re: Trnasportable Tablespace [message #392728 is a reply to message #392720] |
Thu, 19 March 2009 01:48 |
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
|
Senior Member |
 
|
|
From Source:
SQL> select name from v$database;
NAME
---------
KNBDB
SQL> create tablespace source datafile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\KNBDB\source01.dbf' size 10m;
Tablespace created.
SQL> alter tablespace source read only;
Tablespace altered.
SQL> exec dbms_tts.transport_set_check('source',true);
PL/SQL procedure successfully completed.
SQL> select * from sys.transport_set_violations;
no rows selected
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Baskar>exp sys/sys transport_tablespace=y tablespaces=source tts_full_check=y file=d:\source.dmp
Export: Release 10.2.0.1.0 - Production on Thu Mar 19 12:10:12 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
EXP-00056: ORACLE error 28009 encountered
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
Username: sys as sysdba
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace SOURCE ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
C:\Documents and Settings\Baskar>exit
SQL> alter tablespace source read write;
Tablespace altered.
SQL> shutdowb abort
Target:
C:\Documents and Settings\Baskar>set oracle_sid=rdb
C:\Documents and Settings\Baskar>
C:\Documents and Settings\Baskar>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 19 12:12:30 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from v$database;
NAME
---------
RDB
SQL> select * from dba_tablespaces where tablespace_name='SOURCE';
no rows selected
SQL>
SQL>
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Baskar>imp sys/sys transport_tablespace=y file=d:\source.dmp datafiles=('F:\oracle\product\10.2.0\oradata\Rdb\SOURCE01.DBF')
Import: Release 10.2.0.1.0 - Production on Thu Mar 19 12:14:48 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
IMP-00058: ORACLE error 28009 encountered
ORA-28009: connection as SYS should be as SYSDBA or SYSOPERUsername: sys as sysdba
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
Import terminated successfully with warnings.
C:\Documents and Settings\Baskar>
C:\Documents and Settings\Baskar>exit
SQL> select name from v$database;
NAME
---------
RDB
SQL> select * from dba_tablespaces where tablespace_name='SOURCE';
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN
----------- ------------ ---------- --------- --------- --------- --- ----------
ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION BIG
--------- --- ------ -------- ----------- ---
SOURCE 8192 65536 1
2147483645 65536 READ ONLY PERMANENT LOGGING NO LOCAL
SYSTEM YES AUTO DISABLED NOT APPLY NO
SQL>
PS: I have used Original Export and Import Utilities not DataPump
[Updated on: Thu, 19 March 2009 02:15] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
