Objective : to observe REMAP_SCHMA used during transportation.
1. On top previous test(5), preparations for the import.
orarac2poc:RCAT:/home/oracle> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 22 11:16:41 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@RCAT> drop tablespace tts1 including contents and datafiles;
Tablespace dropped.
SYS@RCAT> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
orarac2poc:RCAT:/home/oracle> su - grid
Password:
[grid@orarac2poc ~]$ asmcmd
ASMCMD> cp /u02/oradata/TTS/tts1_01.dbf +DGRCAT/rcat/datafile/
copying /u02/oradata/TTS/tts1_01.dbf -> +DGRCAT/rcat/datafile/tts1_01.dbf
ASMCMD> exit
[grid@orarac2poc ~]$ exit
logout
2. Import it
orarac2poc:RCAT:/home/oracle/ops/exp> cat impdp_trans_df_remap.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
#TRANSPORT_TABLESPACES=tts1
TRANSPORT_DATAFILES='+DGRCAT/rcat/datafile/tts1_01.dbf'
REMAP_SCHEMA=TRANP:LIQY
orarac2poc:RCAT:/home/oracle/ops/exp> impdp system parfile=impdp_trans_df_remap.par
Import: Release 11.2.0.2.0 - Production on Fri Mar 22 11:20:07 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, 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/******** parfile=impdp_trans_df_remap.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 11:20:18
3. Verification
orarac2poc:RCAT:/home/oracle/ops/exp> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 22 11:20:28 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@RCAT> select status from dba_tablespaces where tablespace_name='TTS1';
STATUS
---------
READ ONLY
SYS@RCAT> select owner from dba_tables where table_name='T01';
OWNER
------------------------------
LIQY
SYS@RCAT> SELECT COUNT(*) FROM LIQY.T01;
COUNT(*)
----------
65949
SYS@RCAT> conn liqy/liqyliqy
Connected.
LIQY@RCAT> SELECT COUNT(*) FROM T01;
COUNT(*)
----------
65949
Conclusion:
The schema owner is changed if remap_schema is used when transporting tablespace.
Showing posts with label data management. Show all posts
Showing posts with label data management. Show all posts
Friday, March 22, 2013
Test transportable tablespace (5) -- Observe object's grant and public synonym
Objective:
a). To observe object's grant if it is retained after transportation.
b). Guess public synonym won't be brought over as imp/impdp.
1. On top of earlier experiment, now create a public synonym and grant to a id called liqy.
SYS@TTS> create public synonym t01 for tranp.t01;
Synonym created.
SYS@TTS> select status from dba_tablespaces where tablespace_name='TTS1';
STATUS
---------
READ ONLY
SYS@TTS> create user liqy identified by liqyliqy;
User created.
SYS@TTS> grant select on tranp.t01 to liqy;
Grant succeeded.
SYS@TTS> grant create session to liqy;
Grant succeeded.
SYS@TTS> conn liqy
Connected.
LIQY@TTS> select count(*) from t01;
COUNT(*)
----------
65949
LIQY@TTS> select count(*) from tranp.t01;
COUNT(*)
----------
65949
2. export the tablespace.
#backup earlier dump files which doesn't contain grant .
orarac2poc:TTS:/home/oracle/ops/exp> mv tts.dmp tts_nogrant.dmp
orarac2poc:TTS:/home/oracle/ops/exp> cat expdp_trans_df.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
TRANSPORT_TABLESPACES=tts1
orarac2poc:TTS:/home/oracle/ops/exp> expdp system parfile=expdp_trans_df.par
Export: Release 11.2.0.2.0 - Production on Fri Mar 22 10:16:41 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** parfile=expdp_trans_df.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
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:
/home/oracle/ops/exp/tts.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS1:
/u02/oradata/TTS/tts1_01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:19:13
orarac2poc:TTS:/home/oracle/ops/exp> ls -l tts.dmp tts_nogrant.dmp
-rw-r----- 1 oracle asmadmin 94208 Mar 22 10:19 tts.dmp
-rw-r----- 1 oracle asmadmin 90112 Mar 21 10:27 tts_nogrant.dmp
Note that, the dump file size is different.
3. In target database, preparations for the import.
SYS@RCAT> drop tablespace tts1 including contents and datafiles;
Tablespace dropped.
SYS@RCAT> select file_name from dba_data_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
+DGRCAT/rcat/datafile/mgmt_ecm_depot_ts.273.804790907
+DGRCAT/rcat/datafile/mgmt_tablespace.272.804790907
+DGRCAT/rcat/datafile/mgmt_ad4j_ts.271.804790909
+DGRCAT/rcat/datafile/system.261.803153779
+DGRCAT/rcat/datafile/sysaux.262.803153789
+DGRCAT/rcat/datafile/undotbs1.263.803153795
+DGRCAT/rcat/datafile/users.265.803153807
+DGRCAT/rcat/datafile/rman_tbs01.dbf
8 rows selected.
SYS@RCAT> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
orarac2poc:RCAT:/home/oracle/ops/exp> su - grid
Password:
[grid@orarac2poc ~]$ asmcmd
ASMCMD> cp /u02/oradata/TTS/tts1_01.dbf +DGRCAT/rcat/datafile/
copying /u02/oradata/TTS/tts1_01.dbf -> +DGRCAT/rcat/datafile/tts1_01.dbf
ASMCMD> ls -l +DGRCAT/rcat/datafile/tts1_01.dbf
Type Redund Striped Time Sys Name
N tts1_01.dbf => +DGRCAT/ASM/DATAFILE/tts1_01.dbf.274.810728659
ASMCMD> exit
[grid@orarac2poc ~]$ exit
logout
SYS@RCAT> create user liqy identified by liqyliqy;
User created.
SYS@RCAT> grant create session to liqy;
Grant succeeded.
4. Import the tablespace.
orarac2poc:RCAT:/home/oracle/ops/exp> cat impdp_trans_df.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
#TRANSPORT_TABLESPACES=tts1
TRANSPORT_DATAFILES='+DGRCAT/ASM/DATAFILE/tts1_01.dbf'
orarac2poc:RCAT:/home/oracle/ops/exp> impdp system parfile=impdp_trans_df.par
Import: Release 11.2.0.2.0 - Production on Fri Mar 22 10:37:25 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, 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/******** parfile=impdp_trans_df.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 10:37:39
5. Verification.
SYS@RCAT> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
RMAN_TBS ONLINE
MGMT_ECM_DEPOT_TS ONLINE
MGMT_TABLESPACE ONLINE
MGMT_AD4J_TS ONLINE
TTS1 READ ONLY
10 rows selected.
SYS@RCAT> select count(*) from tranp.t01;
COUNT(*)
----------
65949
SYS@RCAT> select * from dba_tab_privs where table_name='T01';
GRANTEE OWNER TABLE_NAME GRANTOR
------------------------------ ------------------------------ ------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---
LIQY TRANP T01 TRANP
SELECT NO NO
SYS@RCAT> select * from dba_synonyms where synonym_name='T01';
no rows selected
SYS@RCAT> conn liqy/liqyliqy
Connected.
LIQY@RCAT> select count(*) from tranp.t01;
COUNT(*)
----------
65949
Conclusion
a. No worry about object's grant.
b. Need additional effort public synonym for transported tablespace..
a). To observe object's grant if it is retained after transportation.
b). Guess public synonym won't be brought over as imp/impdp.
1. On top of earlier experiment, now create a public synonym and grant to a id called liqy.
SYS@TTS> create public synonym t01 for tranp.t01;
Synonym created.
SYS@TTS> select status from dba_tablespaces where tablespace_name='TTS1';
STATUS
---------
READ ONLY
SYS@TTS> create user liqy identified by liqyliqy;
User created.
SYS@TTS> grant select on tranp.t01 to liqy;
Grant succeeded.
SYS@TTS> grant create session to liqy;
Grant succeeded.
SYS@TTS> conn liqy
Connected.
LIQY@TTS> select count(*) from t01;
COUNT(*)
----------
65949
LIQY@TTS> select count(*) from tranp.t01;
COUNT(*)
----------
65949
2. export the tablespace.
#backup earlier dump files which doesn't contain grant .
orarac2poc:TTS:/home/oracle/ops/exp> mv tts.dmp tts_nogrant.dmp
orarac2poc:TTS:/home/oracle/ops/exp> cat expdp_trans_df.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
TRANSPORT_TABLESPACES=tts1
orarac2poc:TTS:/home/oracle/ops/exp> expdp system parfile=expdp_trans_df.par
Export: Release 11.2.0.2.0 - Production on Fri Mar 22 10:16:41 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** parfile=expdp_trans_df.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
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:
/home/oracle/ops/exp/tts.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS1:
/u02/oradata/TTS/tts1_01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:19:13
orarac2poc:TTS:/home/oracle/ops/exp> ls -l tts.dmp tts_nogrant.dmp
-rw-r----- 1 oracle asmadmin 94208 Mar 22 10:19 tts.dmp
-rw-r----- 1 oracle asmadmin 90112 Mar 21 10:27 tts_nogrant.dmp
Note that, the dump file size is different.
3. In target database, preparations for the import.
SYS@RCAT> drop tablespace tts1 including contents and datafiles;
Tablespace dropped.
SYS@RCAT> select file_name from dba_data_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
+DGRCAT/rcat/datafile/mgmt_ecm_depot_ts.273.804790907
+DGRCAT/rcat/datafile/mgmt_tablespace.272.804790907
+DGRCAT/rcat/datafile/mgmt_ad4j_ts.271.804790909
+DGRCAT/rcat/datafile/system.261.803153779
+DGRCAT/rcat/datafile/sysaux.262.803153789
+DGRCAT/rcat/datafile/undotbs1.263.803153795
+DGRCAT/rcat/datafile/users.265.803153807
+DGRCAT/rcat/datafile/rman_tbs01.dbf
8 rows selected.
SYS@RCAT> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
orarac2poc:RCAT:/home/oracle/ops/exp> su - grid
Password:
[grid@orarac2poc ~]$ asmcmd
ASMCMD> cp /u02/oradata/TTS/tts1_01.dbf +DGRCAT/rcat/datafile/
copying /u02/oradata/TTS/tts1_01.dbf -> +DGRCAT/rcat/datafile/tts1_01.dbf
ASMCMD> ls -l +DGRCAT/rcat/datafile/tts1_01.dbf
Type Redund Striped Time Sys Name
N tts1_01.dbf => +DGRCAT/ASM/DATAFILE/tts1_01.dbf.274.810728659
ASMCMD> exit
[grid@orarac2poc ~]$ exit
logout
SYS@RCAT> create user liqy identified by liqyliqy;
User created.
SYS@RCAT> grant create session to liqy;
Grant succeeded.
4. Import the tablespace.
orarac2poc:RCAT:/home/oracle/ops/exp> cat impdp_trans_df.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
#TRANSPORT_TABLESPACES=tts1
TRANSPORT_DATAFILES='+DGRCAT/ASM/DATAFILE/tts1_01.dbf'
orarac2poc:RCAT:/home/oracle/ops/exp> impdp system parfile=impdp_trans_df.par
Import: Release 11.2.0.2.0 - Production on Fri Mar 22 10:37:25 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, 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/******** parfile=impdp_trans_df.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 10:37:39
5. Verification.
SYS@RCAT> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
RMAN_TBS ONLINE
MGMT_ECM_DEPOT_TS ONLINE
MGMT_TABLESPACE ONLINE
MGMT_AD4J_TS ONLINE
TTS1 READ ONLY
10 rows selected.
SYS@RCAT> select count(*) from tranp.t01;
COUNT(*)
----------
65949
SYS@RCAT> select * from dba_tab_privs where table_name='T01';
GRANTEE OWNER TABLE_NAME GRANTOR
------------------------------ ------------------------------ ------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---
LIQY TRANP T01 TRANP
SELECT NO NO
SYS@RCAT> select * from dba_synonyms where synonym_name='T01';
no rows selected
SYS@RCAT> conn liqy/liqyliqy
Connected.
LIQY@RCAT> select count(*) from tranp.t01;
COUNT(*)
----------
65949
Conclusion
a. No worry about object's grant.
b. Need additional effort public synonym for transported tablespace..
Thursday, March 21, 2013
Test transportable tablespace (4) -- Transport between different endian platform
Main purpose is to test transport in different Endian format from (Little to Big), in this test the character and default block size are not the same.
The Source DB:
The Target DB:
1. setup in source db
SYS@TTS> set pages 1000
SYS@TTS> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET US7ASCII
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.2.0
20 rows selected.
column platform_name format a30
SELECT d.PLATFORM_NAME, tp.ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
------------------------------ --------------
Linux x86 64-bit Little
SYS@TTS> select file_name from dba_data_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
/u02/oradata/TTS/system01.dbf
/u02/oradata/TTS/sysaux01.dbf
/u02/oradata/TTS/undotbs01.dbf
/u02/oradata/TTS/users01.dbf
SYS@TTS> create tablespace tts1 datafile '/u02/oradata/TTS/tts1_01.dbf' size 20m;
Tablespace created.
SYS@TTS> select file_name from dba_data_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
/u02/oradata/TTS/system01.dbf
/u02/oradata/TTS/sysaux01.dbf
/u02/oradata/TTS/undotbs01.dbf
/u02/oradata/TTS/users01.dbf
/u02/oradata/TTS/tts1_01.dbf
SYS@TTS> create user tranp identified by oracle123 default tablespace tts1;
User created.
SYS@TTS> grant create session, resource to tranp;
Grant succeeded.
SYS@TTS> create table tranp.t01 as select * from sys.dba_objects;
Table created.
SYS@TTS> conn tranp/oracle123
Connected.
TRANP@TTS> select count(*) from t01;
COUNT(*)
----------
65949
SYS@TTS> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TTS1',TRUE);
PL/SQL procedure successfully completed.
SYS@TTS> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
SYS@TTS> create directory tranp_dir as '/home/oracle/ops/exp';
Directory created.
SYS@TTS> grant read, write on directory tranp_dir to system;
Grant succeeded.
SYS@TTS> alter tablespace tts1 read only;
Tablespace altered.
2.1 Create the problem if tablespace is not put into read-only mode.
SYS@TTS> alter tablespace tts1 read write;
Tablespace altered.
orarac2poc:TTS:/home/oracle/ops/exp> cat expdp_trans_df.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
TRANSPORT_TABLESPACES=tts1
orarac2poc:TTS:/home/oracle/ops/exp> expdp parfile=expdp_trans_df.par
Export: Release 11.2.0.2.0 - Production on Thu Mar 21 10:24:17 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": /******** AS SYSDBA parfile=expdp_trans_df.par
ORA-39123: Data Pump transportable tablespace job aborted
ORA-39185: The transportable tablespace failure list is
ORA-29335: tablespace 'TTS1' is not read only
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 10:24:36
2.2 Fix the problem in 2.1
SYS@TTS> alter tablespace tts1 read only;
Tablespace altered.
orarac2poc:TTS:/home/oracle/ops/exp> expdp system parfile=expdp_trans_df.par
Export: Release 11.2.0.2.0 - Production on Thu Mar 21 10:26:34 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** parfile=expdp_trans_df.par
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:
/home/oracle/ops/exp/tts.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS1:
/u02/oradata/TTS/tts1_01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:27:53
3. Copy the dump file and data file to destination server.
odsdev01:ODST:/software/oraods/ops/exp> scp oracle@10.139.90.42:/u02/oradata/TTS/tts1_01.dbf ./
oracle@10.139.90.42's password:
tts1_01.dbf 100% 20MB 10.0MB/s 11.2MB/s 00:02
odsdev01:ODST:/software/oraods/ops/exp> scp oracle@10.139.90.42:~/ops/exp/tts*.dmp ./
oracle@10.139.90.42's password:
tts.dmp 100% 88KB 88.0KB/s 88.0KB/s 00:00
odsdev01:ODST:/software/oraods/ops/exp> ls -lrt
total 41232
-rw-r--r-- 1 oraods dba 93 Mar 21 10:58 impdp_trans_df.par
-rw-r----- 1 oraods dba 20979712 Mar 21 11:01 tts1_01.dbf
-rw-r----- 1 oraods dba 90112 Mar 21 11:02 tts.dmp
4. Preparation in destination database.
uname -a
HP-UX odsdev01 B.11.31 U ia64 2376076186 unlimited-user license
SQL> create directory tranp_dir as '/software/oraods/ops/exp';
Directory created.
SQL> grant read, write on directory tranp_dir to system;
Grant succeeded.
SQL> create user tranp identified by oracle123;
User created.
SQL> grant create session, resource to tranp;
Grant succeeded.
SQL> alter user system identified by oracle123;
User altered.
column platform_name format a30
SELECT d.PLATFORM_NAME, tp.ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
------------------------------ --------------
HP-UX IA (64-bit) Big
select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from
(select VALUE$ aa from sys.props$ where name='NLS_LANGUAGE')tab1,
(select VALUE$ bb from sys.props$ where name='NLS_ISO_CURRENCY')tab2,
(select VALUE$ cc from sys.props$ where name='NLS_CHARACTERSET')tab3;
TAB1.AA||'_'||TAB2.BB||'.'||TAB3.CC
--------------------------------------------------------------------------------
AMERICAN_AMERICA.UTF8
5. Conver the data file.
Datafile conversion necessitates that you choose a technique for naming the output files. You must use the
If the source and destination platforms differ, then you must specify the
CONVERT DATAFILE
'/software/oraods/ops/exp/tts1_01.dbf'
DB_FILE_NAME_CONVERT
'/software/oraods/ops/exp','/ods010/oradata/ODST'
FROM PLATFORM 'Linux x86 64-bit';
Starting conversion at target at 21-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=247 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/software/oraods/ops/exp/tts1_01.dbf
converted datafile=/ods010/oradata/ODST/tts1_01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 21-MAR-13
RMAN> exit
Recovery Manager complete.
odsdev01:ODST:/software/oraods/ops/exp> ls -l /ods010/oradata/ODST/tts1_01.dbf
-rw-r----- 1 oraods dba 20979712 Mar 21 11:08 /ods010/oradata/ODST/tts1_01.dbf
odsdev01:ODST:/software/oraods/ops/exp> ls -l /software/oraods/ops/exp/tts1_01.dbf
-rw-r----- 1 oraods dba 20979712 Mar 21 11:01 /software/oraods/ops/exp/tts1_01.dbf
6.1 import but tablespace block size 8192 does not match
odsdev01:ODST:/software/oraods/ops/exp> cat impdp_trans_df.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
TRANSPORT_DATAFILES='/ods010/oradata/ODST/tts1_01.dbf'
odsdev01:ODST:/software/oraods/ops/exp> impdp system parfile=impdp_trans_df.par
Import: Release 11.2.0.3.0 - Production on Thu Mar 21 11:09:23 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** parfile=impdp_trans_df.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29339: tablespace block size 8192 does not match configured block sizes
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 11:09:34
6.2 Fix the problem of 6.1
odsdev01:ODST:/software/oraods/ops/exp> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 21 11:09:50 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
SQL> show parameter db_block
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TYPICAL
db_block_size integer 32768
SQL> exit
odsdev01:ODST:/software/oraods/ops/exp> oerr ora 29339
29339, 00000, "tablespace block size %s does not match configured block sizes"
// *Cause: The block size of the tablespace to be plugged in or
// created does not match the block sizes configured in the
// database.
// *Action:Configure the appropriate cache for the block size of this
// tablespace using one of the various (db_2k_cache_size,
// db_4k_cache_size, db_8k_cache_size, db_16k_cache_size,
// db_32K_cache_size) parameters.
while the block size in source database is 8k shown below.
SYS@TTS> show parameter db_block
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TYPICAL
db_block_size integer 8192
So in destination DB,
SQL> show parameter cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size big integer 0
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
db_flash_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SQL> alter system set db_8k_cache_size=6m;
System altered.
SQL> show parameter cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size big integer 0
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 16M
db_cache_size big integer 0
db_flash_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SQL> exit
odsdev01:ODST:/software/oraods/ops/exp> impdp system parfile=impdp_trans_df.par
Import: Release 11.2.0.3.0 - Production on Thu Mar 21 11:16:28 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** parfile=impdp_trans_df.par
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 11:16:36
7. verification.
--verification
odsdev01:ODST:/software/oraods/ops/exp> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 21 11:18:52 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
SQL> select tablespace_name, file_name from dba_data_files where tablespace_name='TTS1';
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TTS1
/ods010/oradata/ODST/tts1_01.dbf
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name='TTS1';
TABLESPACE_NAME STATUS
------------------------------ ---------
TTS1 READ ONLY
SQL> conn tranp/oracle123
Connected.
SQL> select count(*) from t01;
COUNT(*)
----------
65949
SQL> conn / as sysdba
Connected.
SQL> alter tablespace tts1 read write;
Tablespace altered.
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name='TTS1';
TABLESPACE_NAME STATUS
------------------------------ ---------
TTS1 ONLINE
The Source DB:
- DB Name: TTS
- Platform: Linux x86_64
- Endian Format: Little
- Character Set: ASC7II
- Default block size: 8k
The Target DB:
- DB Name: ODST
- Platform: HP-UX ia64 v3
- Endian Format: Big
- Character Set: UTF8
- Default block size: 32k
1. setup in source db
SYS@TTS> set pages 1000
SYS@TTS> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET US7ASCII
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.2.0
20 rows selected.
column platform_name format a30
SELECT d.PLATFORM_NAME, tp.ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
------------------------------ --------------
Linux x86 64-bit Little
SYS@TTS> select file_name from dba_data_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
/u02/oradata/TTS/system01.dbf
/u02/oradata/TTS/sysaux01.dbf
/u02/oradata/TTS/undotbs01.dbf
/u02/oradata/TTS/users01.dbf
SYS@TTS> create tablespace tts1 datafile '/u02/oradata/TTS/tts1_01.dbf' size 20m;
Tablespace created.
SYS@TTS> select file_name from dba_data_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
/u02/oradata/TTS/system01.dbf
/u02/oradata/TTS/sysaux01.dbf
/u02/oradata/TTS/undotbs01.dbf
/u02/oradata/TTS/users01.dbf
/u02/oradata/TTS/tts1_01.dbf
SYS@TTS> create user tranp identified by oracle123 default tablespace tts1;
User created.
SYS@TTS> grant create session, resource to tranp;
Grant succeeded.
SYS@TTS> create table tranp.t01 as select * from sys.dba_objects;
Table created.
SYS@TTS> conn tranp/oracle123
Connected.
TRANP@TTS> select count(*) from t01;
COUNT(*)
----------
65949
SYS@TTS> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TTS1',TRUE);
PL/SQL procedure successfully completed.
SYS@TTS> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
SYS@TTS> create directory tranp_dir as '/home/oracle/ops/exp';
Directory created.
SYS@TTS> grant read, write on directory tranp_dir to system;
Grant succeeded.
SYS@TTS> alter tablespace tts1 read only;
Tablespace altered.
2.1 Create the problem if tablespace is not put into read-only mode.
SYS@TTS> alter tablespace tts1 read write;
Tablespace altered.
orarac2poc:TTS:/home/oracle/ops/exp> cat expdp_trans_df.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
TRANSPORT_TABLESPACES=tts1
orarac2poc:TTS:/home/oracle/ops/exp> expdp parfile=expdp_trans_df.par
Export: Release 11.2.0.2.0 - Production on Thu Mar 21 10:24:17 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": /******** AS SYSDBA parfile=expdp_trans_df.par
ORA-39123: Data Pump transportable tablespace job aborted
ORA-39185: The transportable tablespace failure list is
ORA-29335: tablespace 'TTS1' is not read only
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 10:24:36
2.2 Fix the problem in 2.1
SYS@TTS> alter tablespace tts1 read only;
Tablespace altered.
orarac2poc:TTS:/home/oracle/ops/exp> expdp system parfile=expdp_trans_df.par
Export: Release 11.2.0.2.0 - Production on Thu Mar 21 10:26:34 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** parfile=expdp_trans_df.par
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:
/home/oracle/ops/exp/tts.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS1:
/u02/oradata/TTS/tts1_01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:27:53
3. Copy the dump file and data file to destination server.
odsdev01:ODST:/software/oraods/ops/exp> scp oracle@10.139.90.42:/u02/oradata/TTS/tts1_01.dbf ./
oracle@10.139.90.42's password:
tts1_01.dbf 100% 20MB 10.0MB/s 11.2MB/s 00:02
odsdev01:ODST:/software/oraods/ops/exp> scp oracle@10.139.90.42:~/ops/exp/tts*.dmp ./
oracle@10.139.90.42's password:
tts.dmp 100% 88KB 88.0KB/s 88.0KB/s 00:00
odsdev01:ODST:/software/oraods/ops/exp> ls -lrt
total 41232
-rw-r--r-- 1 oraods dba 93 Mar 21 10:58 impdp_trans_df.par
-rw-r----- 1 oraods dba 20979712 Mar 21 11:01 tts1_01.dbf
-rw-r----- 1 oraods dba 90112 Mar 21 11:02 tts.dmp
4. Preparation in destination database.
uname -a
HP-UX odsdev01 B.11.31 U ia64 2376076186 unlimited-user license
SQL> create directory tranp_dir as '/software/oraods/ops/exp';
Directory created.
SQL> grant read, write on directory tranp_dir to system;
Grant succeeded.
SQL> create user tranp identified by oracle123;
User created.
SQL> grant create session, resource to tranp;
Grant succeeded.
SQL> alter user system identified by oracle123;
User altered.
column platform_name format a30
SELECT d.PLATFORM_NAME, tp.ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
------------------------------ --------------
HP-UX IA (64-bit) Big
select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from
(select VALUE$ aa from sys.props$ where name='NLS_LANGUAGE')tab1,
(select VALUE$ bb from sys.props$ where name='NLS_ISO_CURRENCY')tab2,
(select VALUE$ cc from sys.props$ where name='NLS_CHARACTERSET')tab3;
TAB1.AA||'_'||TAB2.BB||'.'||TAB3.CC
--------------------------------------------------------------------------------
AMERICAN_AMERICA.UTF8
5. Conver the data file.
Datafile conversion necessitates that you choose a technique for naming the output files. You must use the
FORMAT or DB_FILE_NAME_CONVERT arguments to CONVERT to control the naming of output files.If the source and destination platforms differ, then you must specify the
FROM PLATFORM parameter. View platform names by querying V$TRANSPORTABLE_PLATFORM. The FROM PLATFORM value must match the format of the datafiles to be converted to avoid an error.CONVERT DATAFILE
'/software/oraods/ops/exp/tts1_01.dbf'
DB_FILE_NAME_CONVERT
'/software/oraods/ops/exp','/ods010/oradata/ODST'
FROM PLATFORM 'Linux x86 64-bit';
Starting conversion at target at 21-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=247 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/software/oraods/ops/exp/tts1_01.dbf
converted datafile=/ods010/oradata/ODST/tts1_01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 21-MAR-13
RMAN> exit
Recovery Manager complete.
odsdev01:ODST:/software/oraods/ops/exp> ls -l /ods010/oradata/ODST/tts1_01.dbf
-rw-r----- 1 oraods dba 20979712 Mar 21 11:08 /ods010/oradata/ODST/tts1_01.dbf
odsdev01:ODST:/software/oraods/ops/exp> ls -l /software/oraods/ops/exp/tts1_01.dbf
-rw-r----- 1 oraods dba 20979712 Mar 21 11:01 /software/oraods/ops/exp/tts1_01.dbf
6.1 import but tablespace block size 8192 does not match
odsdev01:ODST:/software/oraods/ops/exp> cat impdp_trans_df.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
TRANSPORT_DATAFILES='/ods010/oradata/ODST/tts1_01.dbf'
odsdev01:ODST:/software/oraods/ops/exp> impdp system parfile=impdp_trans_df.par
Import: Release 11.2.0.3.0 - Production on Thu Mar 21 11:09:23 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** parfile=impdp_trans_df.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29339: tablespace block size 8192 does not match configured block sizes
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 11:09:34
6.2 Fix the problem of 6.1
odsdev01:ODST:/software/oraods/ops/exp> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 21 11:09:50 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
SQL> show parameter db_block
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TYPICAL
db_block_size integer 32768
SQL> exit
odsdev01:ODST:/software/oraods/ops/exp> oerr ora 29339
29339, 00000, "tablespace block size %s does not match configured block sizes"
// *Cause: The block size of the tablespace to be plugged in or
// created does not match the block sizes configured in the
// database.
// *Action:Configure the appropriate cache for the block size of this
// tablespace using one of the various (db_2k_cache_size,
// db_4k_cache_size, db_8k_cache_size, db_16k_cache_size,
// db_32K_cache_size) parameters.
while the block size in source database is 8k shown below.
SYS@TTS> show parameter db_block
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TYPICAL
db_block_size integer 8192
So in destination DB,
SQL> show parameter cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size big integer 0
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
db_flash_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SQL> alter system set db_8k_cache_size=6m;
System altered.
SQL> show parameter cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size big integer 0
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 16M
db_cache_size big integer 0
db_flash_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SQL> exit
odsdev01:ODST:/software/oraods/ops/exp> impdp system parfile=impdp_trans_df.par
Import: Release 11.2.0.3.0 - Production on Thu Mar 21 11:16:28 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** parfile=impdp_trans_df.par
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 11:16:36
7. verification.
--verification
odsdev01:ODST:/software/oraods/ops/exp> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 21 11:18:52 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
SQL> select tablespace_name, file_name from dba_data_files where tablespace_name='TTS1';
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TTS1
/ods010/oradata/ODST/tts1_01.dbf
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name='TTS1';
TABLESPACE_NAME STATUS
------------------------------ ---------
TTS1 READ ONLY
SQL> conn tranp/oracle123
Connected.
SQL> select count(*) from t01;
COUNT(*)
----------
65949
SQL> conn / as sysdba
Connected.
SQL> alter tablespace tts1 read write;
Tablespace altered.
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name='TTS1';
TABLESPACE_NAME STATUS
------------------------------ ---------
TTS1 ONLINE
Test transportable tablespace (3) - failed due to incompatible character set
The source database use WE8MSWIN1252 (default when choose "general purpose" template from dbca), and the target database use UTF8.
1.1 use RMAN to convert the endian format from Little to Big platform.
CONVERT DATAFILE
'/software/oraods/ops/exp/tts1_01.dbf',
'/software/oraods/ops/exp/tts1_02.dbf'
DB_FILE_NAME_CONVERT
'/software/oraods/ops/exp','/ods001/oradata/ODST'
FROM PLATFORM 'Linux x86 64-bit';
Starting conversion at target at 20-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/software/oraods/ops/exp/tts1_01.dbf
converted datafile=/ods001/oradata/ODST/tts1_01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input file name=/software/oraods/ops/exp/tts1_02.dbf
converted datafile=/ods001/oradata/ODST/tts1_02.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 20-MAR-13
RMAN> exit
Recovery Manager complete.
odsdev01:ODST:/software/oraods/ops/exp> ls -l /ods001/oradata/ODST/tts*
-rw-r----- 1 oraods dba 20979712 Mar 20 13:46 /ods001/oradata/ODST/tts1_01.dbf
-rw-r----- 1 oraods dba 20979712 Mar 20 13:46 /ods001/oradata/ODST/tts1_02.dbf
odsdev01:ODST:/software/oraods/ops/exp> ls -l /software/oraods/ops/exp/tts*.dbf
-rw-r----- 1 oraods dba 20979712 Mar 20 13:36 /software/oraods/ops/exp/tts1_01.dbf
-rw-r----- 1 oraods dba 20979712 Mar 20 13:36 /software/oraods/ops/exp/tts1_02.dbf
odsdev01:ODST:/software/oraods/ops/exp> date
Wed Mar 20 13:47:23 SST 2013
1.2 The impdp error
odsdev01:ODST:/software/oraods/ops/exp> impdp system parfile=impdp_trans_df.par
Import: Release 11.2.0.3.0 - Production on Wed Mar 20 13:48:21 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** parfile=impdp_trans_df.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29345: cannot plug a tablespace into a database using an incompatible character set
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 13:48:34
2. When I tried to change character set in source database with CS scanner. The report shows me there are unconvertible dictionary tables listed below.
NOTHANDLEDDATADICTCOLUMNS
---------------------------------------------------------------------------------------------------------------------------
SYS.HISTGRM$(EPVALUE) - VARCHAR2 - CONVERTIBLE
SYS.REG$(SESSION_KEY) - VARCHAR2 - CONVERTIBLE
SYS.WRI$_OPTSTAT_HISTGRM_HISTORY(EPVALUE) - VARCHAR2 - CONVERTIBLE
SYSMAN.EM_IPW_INFO(IPW) - VARCHAR2 - CONVERTIBLE
SYSMAN.EM_NC_CRED_COLUMNS(CRED_ATTR_VALUE) - VARCHAR2 - CONVERTIBLE
SYSMAN.EM_TARGET_CRED_PROPERTIES(PROPERTY_VALUE) - VARCHAR2 - CONVERTIBLE
SYSMAN.MGMT_AGENT_SEC_INFO(AGENT_KEY) - VARCHAR2 - CONVERTIBLE
SYSMAN.MGMT_CCR_TARGET_SOURCES(DESCRIPTION) - VARCHAR2 - CONVERTIBLE
SYSMAN.MGMT_CNTR_CREDS(CREDS) - VARCHAR2 - CONVERTIBLE
SYSMAN.MGMT_PARAMETERS(PARAMETER_VALUE) - VARCHAR2 - CONVERTIBLE
SYSMAN.MGMT_SEC_INFO(CA_PWD) - VARCHAR2 - CONVERTIBLE
SYSMAN.MGMT_VIEW_USER_CREDENTIALS(VIEW_PASSWORD) - VARCHAR2 - CONVERTIBLE
12 rows selected.
[Data Dictionary Conversion Summary]
Data Dictionary Tables:
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 33,862,781 106 0 0
CHAR 51,168 0 0 0
LONG 301,547 0 0 0
CLOB 1,363,901 63,402 0 0
VARRAY 56,889 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 35,636,286 63,508 0 0
3. If I ignore the warning, there is failure when running csalter.plb.
orarac2poc:RCAT:/home/oracle/ops/exp> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 20 16:00:52 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@RCAT> @?/rdbms/admin/csalter.plb
0 rows created.
Function created.
Function created.
Procedure created.
This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?y
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('y') <> 'Y') then
Checking data validity...
Sorry only one session is allowed to run this script
PL/SQL procedure successfully completed.
Checking or Converting phase did not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.
PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.
SYS@RCAT> exit
To save my time, I assume I am not able to convert WE8MSWIN1252 to UTF8. Will play csscan later.
References:
Running Csalter.Plb Gives Unrecognized Convertible Data Found In Scanner Result [ID 335147.1]
Changing the NLS_CHARACTERSET to AL32UTF8 _ UTF8 (Unicode) [ID 260192.1]
1.1 use RMAN to convert the endian format from Little to Big platform.
CONVERT DATAFILE
'/software/oraods/ops/exp/tts1_01.dbf',
'/software/oraods/ops/exp/tts1_02.dbf'
DB_FILE_NAME_CONVERT
'/software/oraods/ops/exp','/ods001/oradata/ODST'
FROM PLATFORM 'Linux x86 64-bit';
Starting conversion at target at 20-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/software/oraods/ops/exp/tts1_01.dbf
converted datafile=/ods001/oradata/ODST/tts1_01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input file name=/software/oraods/ops/exp/tts1_02.dbf
converted datafile=/ods001/oradata/ODST/tts1_02.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 20-MAR-13
RMAN> exit
Recovery Manager complete.
odsdev01:ODST:/software/oraods/ops/exp> ls -l /ods001/oradata/ODST/tts*
-rw-r----- 1 oraods dba 20979712 Mar 20 13:46 /ods001/oradata/ODST/tts1_01.dbf
-rw-r----- 1 oraods dba 20979712 Mar 20 13:46 /ods001/oradata/ODST/tts1_02.dbf
odsdev01:ODST:/software/oraods/ops/exp> ls -l /software/oraods/ops/exp/tts*.dbf
-rw-r----- 1 oraods dba 20979712 Mar 20 13:36 /software/oraods/ops/exp/tts1_01.dbf
-rw-r----- 1 oraods dba 20979712 Mar 20 13:36 /software/oraods/ops/exp/tts1_02.dbf
odsdev01:ODST:/software/oraods/ops/exp> date
Wed Mar 20 13:47:23 SST 2013
1.2 The impdp error
odsdev01:ODST:/software/oraods/ops/exp> impdp system parfile=impdp_trans_df.par
Import: Release 11.2.0.3.0 - Production on Wed Mar 20 13:48:21 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** parfile=impdp_trans_df.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29345: cannot plug a tablespace into a database using an incompatible character set
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 13:48:34
2. When I tried to change character set in source database with CS scanner. The report shows me there are unconvertible dictionary tables listed below.
NOTHANDLEDDATADICTCOLUMNS
---------------------------------------------------------------------------------------------------------------------------
SYS.HISTGRM$(EPVALUE) - VARCHAR2 - CONVERTIBLE
SYS.REG$(SESSION_KEY) - VARCHAR2 - CONVERTIBLE
SYS.WRI$_OPTSTAT_HISTGRM_HISTORY(EPVALUE) - VARCHAR2 - CONVERTIBLE
SYSMAN.EM_IPW_INFO(IPW) - VARCHAR2 - CONVERTIBLE
SYSMAN.EM_NC_CRED_COLUMNS(CRED_ATTR_VALUE) - VARCHAR2 - CONVERTIBLE
SYSMAN.EM_TARGET_CRED_PROPERTIES(PROPERTY_VALUE) - VARCHAR2 - CONVERTIBLE
SYSMAN.MGMT_AGENT_SEC_INFO(AGENT_KEY) - VARCHAR2 - CONVERTIBLE
SYSMAN.MGMT_CCR_TARGET_SOURCES(DESCRIPTION) - VARCHAR2 - CONVERTIBLE
SYSMAN.MGMT_CNTR_CREDS(CREDS) - VARCHAR2 - CONVERTIBLE
SYSMAN.MGMT_PARAMETERS(PARAMETER_VALUE) - VARCHAR2 - CONVERTIBLE
SYSMAN.MGMT_SEC_INFO(CA_PWD) - VARCHAR2 - CONVERTIBLE
SYSMAN.MGMT_VIEW_USER_CREDENTIALS(VIEW_PASSWORD) - VARCHAR2 - CONVERTIBLE
12 rows selected.
[Data Dictionary Conversion Summary]
Data Dictionary Tables:
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 33,862,781 106 0 0
CHAR 51,168 0 0 0
LONG 301,547 0 0 0
CLOB 1,363,901 63,402 0 0
VARRAY 56,889 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 35,636,286 63,508 0 0
3. If I ignore the warning, there is failure when running csalter.plb.
orarac2poc:RCAT:/home/oracle/ops/exp> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 20 16:00:52 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@RCAT> @?/rdbms/admin/csalter.plb
0 rows created.
Function created.
Function created.
Procedure created.
This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?y
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('y') <> 'Y') then
Checking data validity...
Sorry only one session is allowed to run this script
PL/SQL procedure successfully completed.
Checking or Converting phase did not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.
PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.
SYS@RCAT> exit
To save my time, I assume I am not able to convert WE8MSWIN1252 to UTF8. Will play csscan later.
References:
Running Csalter.Plb Gives Unrecognized Convertible Data Found In Scanner Result [ID 335147.1]
Changing the NLS_CHARACTERSET to AL32UTF8 _ UTF8 (Unicode) [ID 260192.1]
Test transportable tablespace (2) - file renamed while importing
This example illustrates the renaming of data files as part of a transportable tablespace export and import operation.
1. Clean up the tablespace TTS1 in target database RAC node1 ORCL1
TRANP@ORCL1> conn / as sysdba
Connected.
SYS@ORCL1> drop tablespace tts1 including contents and datafiles;
Tablespace dropped.
SYS@ORCL1> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
7 rows selected.
SYS@ORCL1> exit
2. logon as grid who is ASM owner. Rename the file name while copying (remove 0 in filename)
orarac1poc:ORCL1:/home/oracle/ops/exp> su - grid
Password:
[grid@orarac1poc ~]$ asmcmd
ASMCMD> cp /u01/oradata/DG/tts1_01.dbf +DGDATA/orcl//tts1_1.dbf
copying /u01/oradata/DG/tts1_01.dbf -> +DGDATA/orcl//tts1_1.dbf
ASMCMD> cp /u01/oradata/DG/tts1_02.dbf +DGDATA/orcl//tts1_2.dbf
copying /u01/oradata/DG/tts1_02.dbf -> +DGDATA/orcl//tts1_2.dbf
ASMCMD> ls -l +DGDATA/orcl/
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N control01.ctl => +DGDATA/ORCL/CONTROLFILE/Current.260.803128289
N control02.ctl => +DGDATA/ORCL/CONTROLFILE/Current.261.803128289
N example01.dbf => +DGDATA/ORCL/DATAFILE/UNKNOWN.265.803128321
N redo01.log => +DGDATA/ORCL/ONLINELOG/group_1.262.803128313
N redo02.log => +DGDATA/ORCL/ONLINELOG/group_2.263.803128313
N redo03.log => +DGDATA/ORCL/ONLINELOG/group_3.267.803128495
N redo04.log => +DGDATA/ORCL/ONLINELOG/group_4.268.803128495
N spfileORCL.ora => +DGDATA/ORCL/PARAMETERFILE/spfile.269.803128497
N sysaux01.dbf => +DGDATA/ORCL/DATAFILE/SYSAUX.257.803128215
N system01.dbf => +DGDATA/ORCL/DATAFILE/SYSTEM.256.803128215
N temp01.dbf => +DGDATA/ORCL/TEMPFILE/TEMP.264.803128317
N tts1_1.dbf => +DGDATA/ASM/DATAFILE/tts1_1.dbf.270.810560049
N tts1_2.dbf => +DGDATA/ASM/DATAFILE/tts1_2.dbf.271.810560059
N undotbs01.dbf => +DGDATA/ORCL/DATAFILE/UNDOTBS1.258.803128215
N undotbs02.dbf => +DGDATA/ORCL/DATAFILE/UNDOTBS2.266.803128451
N users01.dbf => +DGDATA/ORCL/DATAFILE/USERS.259.803128215
ASMCMD> exit
[grid@orarac1poc ~]$ exit
logout
3. Import the tablespace's metadata
orarac1poc:ORCL1:/home/oracle/ops/exp> cat impdp_trans_df_rename.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
TRANSPORT_DATAFILES='+DGDATA/orcl/tts1_1.dbf' ,'+DGDATA/orcl/tts1_2.dbf'
orarac1poc:ORCL1:/home/oracle/ops/exp> impdp system parfile=impdp_trans_df_rename.par
Import: Release 11.2.0.2.0 - Production on Wed Mar 20 11:35:59 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, 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/******** parfile=impdp_trans_df_rename.par
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 11:36:26
4.1 verification from RAC node1
orarac1poc:ORCL1:/home/oracle/ops/exp> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 20 11:37:40 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@ORCL1> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
TTS1 READ ONLY
8 rows selected.
SYS@ORCL1> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
USERS
+DGDATA/orcl/users01.dbf
UNDOTBS1
+DGDATA/orcl/undotbs01.dbf
SYSAUX
+DGDATA/orcl/sysaux01.dbf
SYSTEM
+DGDATA/orcl/system01.dbf
EXAMPLE
+DGDATA/orcl/example01.dbf
UNDOTBS2
+DGDATA/orcl/undotbs02.dbf
TTS1
+DGDATA/orcl/tts1_2.dbf
TTS1
+DGDATA/orcl/tts1_1.dbf
8 rows selected.
SYS@ORCL1> conn tranp/oracle123
Connected.
TRANP@ORCL1> select count(*) from t01;
COUNT(*)
----------
60708
TRANP@ORCL1> conn / as sysdba
Connected.
SYS@ORCL1> alter tablespace tts1 read write;
Tablespace altered.
SYS@ORCL1> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
TTS1 ONLINE
8 rows selected.
4.2 verify from RAC node2 too
orarac2poc:ORCL2:/home/oracle> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 20 11:47:35 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@ORCL2> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
TTS1 ONLINE
8 rows selected.
SYS@ORCL2> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
USERS
+DGDATA/orcl/users01.dbf
UNDOTBS1
+DGDATA/orcl/undotbs01.dbf
SYSAUX
+DGDATA/orcl/sysaux01.dbf
SYSTEM
+DGDATA/orcl/system01.dbf
EXAMPLE
+DGDATA/orcl/example01.dbf
UNDOTBS2
+DGDATA/orcl/undotbs02.dbf
TTS1
+DGDATA/orcl/tts1_2.dbf
TTS1
+DGDATA/orcl/tts1_1.dbf
8 rows selected.
SYS@ORCL2> select count(*) from tranp.t01;
COUNT(*)
----------
60708
1. Clean up the tablespace TTS1 in target database RAC node1 ORCL1
TRANP@ORCL1> conn / as sysdba
Connected.
SYS@ORCL1> drop tablespace tts1 including contents and datafiles;
Tablespace dropped.
SYS@ORCL1> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
7 rows selected.
SYS@ORCL1> exit
2. logon as grid who is ASM owner. Rename the file name while copying (remove 0 in filename)
orarac1poc:ORCL1:/home/oracle/ops/exp> su - grid
Password:
[grid@orarac1poc ~]$ asmcmd
ASMCMD> cp /u01/oradata/DG/tts1_01.dbf +DGDATA/orcl//tts1_1.dbf
copying /u01/oradata/DG/tts1_01.dbf -> +DGDATA/orcl//tts1_1.dbf
ASMCMD> cp /u01/oradata/DG/tts1_02.dbf +DGDATA/orcl//tts1_2.dbf
copying /u01/oradata/DG/tts1_02.dbf -> +DGDATA/orcl//tts1_2.dbf
ASMCMD> ls -l +DGDATA/orcl/
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N control01.ctl => +DGDATA/ORCL/CONTROLFILE/Current.260.803128289
N control02.ctl => +DGDATA/ORCL/CONTROLFILE/Current.261.803128289
N example01.dbf => +DGDATA/ORCL/DATAFILE/UNKNOWN.265.803128321
N redo01.log => +DGDATA/ORCL/ONLINELOG/group_1.262.803128313
N redo02.log => +DGDATA/ORCL/ONLINELOG/group_2.263.803128313
N redo03.log => +DGDATA/ORCL/ONLINELOG/group_3.267.803128495
N redo04.log => +DGDATA/ORCL/ONLINELOG/group_4.268.803128495
N spfileORCL.ora => +DGDATA/ORCL/PARAMETERFILE/spfile.269.803128497
N sysaux01.dbf => +DGDATA/ORCL/DATAFILE/SYSAUX.257.803128215
N system01.dbf => +DGDATA/ORCL/DATAFILE/SYSTEM.256.803128215
N temp01.dbf => +DGDATA/ORCL/TEMPFILE/TEMP.264.803128317
N tts1_1.dbf => +DGDATA/ASM/DATAFILE/tts1_1.dbf.270.810560049
N tts1_2.dbf => +DGDATA/ASM/DATAFILE/tts1_2.dbf.271.810560059
N undotbs01.dbf => +DGDATA/ORCL/DATAFILE/UNDOTBS1.258.803128215
N undotbs02.dbf => +DGDATA/ORCL/DATAFILE/UNDOTBS2.266.803128451
N users01.dbf => +DGDATA/ORCL/DATAFILE/USERS.259.803128215
ASMCMD> exit
[grid@orarac1poc ~]$ exit
logout
3. Import the tablespace's metadata
orarac1poc:ORCL1:/home/oracle/ops/exp> cat impdp_trans_df_rename.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
TRANSPORT_DATAFILES='+DGDATA/orcl/tts1_1.dbf' ,'+DGDATA/orcl/tts1_2.dbf'
orarac1poc:ORCL1:/home/oracle/ops/exp> impdp system parfile=impdp_trans_df_rename.par
Import: Release 11.2.0.2.0 - Production on Wed Mar 20 11:35:59 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, 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/******** parfile=impdp_trans_df_rename.par
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 11:36:26
4.1 verification from RAC node1
orarac1poc:ORCL1:/home/oracle/ops/exp> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 20 11:37:40 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@ORCL1> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
TTS1 READ ONLY
8 rows selected.
SYS@ORCL1> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
USERS
+DGDATA/orcl/users01.dbf
UNDOTBS1
+DGDATA/orcl/undotbs01.dbf
SYSAUX
+DGDATA/orcl/sysaux01.dbf
SYSTEM
+DGDATA/orcl/system01.dbf
EXAMPLE
+DGDATA/orcl/example01.dbf
UNDOTBS2
+DGDATA/orcl/undotbs02.dbf
TTS1
+DGDATA/orcl/tts1_2.dbf
TTS1
+DGDATA/orcl/tts1_1.dbf
8 rows selected.
SYS@ORCL1> conn tranp/oracle123
Connected.
TRANP@ORCL1> select count(*) from t01;
COUNT(*)
----------
60708
TRANP@ORCL1> conn / as sysdba
Connected.
SYS@ORCL1> alter tablespace tts1 read write;
Tablespace altered.
SYS@ORCL1> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
TTS1 ONLINE
8 rows selected.
4.2 verify from RAC node2 too
orarac2poc:ORCL2:/home/oracle> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 20 11:47:35 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@ORCL2> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
TTS1 ONLINE
8 rows selected.
SYS@ORCL2> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
USERS
+DGDATA/orcl/users01.dbf
UNDOTBS1
+DGDATA/orcl/undotbs01.dbf
SYSAUX
+DGDATA/orcl/sysaux01.dbf
SYSTEM
+DGDATA/orcl/system01.dbf
EXAMPLE
+DGDATA/orcl/example01.dbf
UNDOTBS2
+DGDATA/orcl/undotbs02.dbf
TTS1
+DGDATA/orcl/tts1_2.dbf
TTS1
+DGDATA/orcl/tts1_1.dbf
8 rows selected.
SYS@ORCL2> select count(*) from tranp.t01;
COUNT(*)
----------
60708
Wednesday, March 20, 2013
Test transportable tablespace (1)
In this environment, I will test transportable tablespace TTS1 from one primary data guard database to an RAC database. As there are running on same server , the endian format is the same, i.e little. There were also created by dbca using default character set , i.e WE8MSWIN1252, hence no character set conversion is required.
1. check endian format and character set.
SYS@DG> desc V$TRANSPORTABLE_PLATFORM
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
PLATFORM_ID NUMBER
PLATFORM_NAME VARCHAR2(101)
ENDIAN_FORMAT VARCHAR2(14)
SYS@DG> column PLATFORM_NAME format a30
SYS@DG> select * from V$TRANSPORTABLE_PLATFORM ;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------ --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
13 Linux x86 64-bit Little
16 Apple Mac OS Big
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
19 HP IA Open VMS Little
20 Solaris Operating System (x86- Little
64)
21 Apple Mac OS (x86-64) Little
20 rows selected.
SYS@DG> select PLATFORM_NAME from v$database;
PLATFORM_NAME
------------------------------
Linux x86 64-bit
select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from
(select VALUE$ aa from sys.props$ where name='NLS_LANGUAGE')tab1,
(select VALUE$ bb from sys.props$ where name='NLS_ISO_CURRENCY')tab2,
4 (select VALUE$ cc from sys.props$ where name='NLS_CHARACTERSET')tab3;
TAB1.AA||'_'||TAB2.BB||'.'||TAB3.CC
-------------------------------------------------------------------------------------------------------------------
AMERICAN_AMERICA.WE8MSWIN1252
2. Create test tablespace TTS1 having two data files, id, for transport.
SYS@DG> select file_name from dba_data_files;
FILE_NAME
-------------------------------------------------------------------------------------------------------------------
/u01/oradata/DG/system01.dbf
/u01/oradata/DG/sysaux01.dbf
/u01/oradata/DG/undotbs01.dbf
/u01/oradata/DG/users01.dbf
SYS@DG> create tablespace tts1 datafile '/u01/oradata/DG/tts1_01.dbf' size 20m;
Tablespace created.
SYS@DG> alter tablespace tts1 add datafile '/u01/oradata/DG/tts1_02.dbf' size 20m;
Tablespace altered.
SYS@DG> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
SYSTEM
/u01/oradata/DG/system01.dbf
SYSAUX
/u01/oradata/DG/sysaux01.dbf
UNDOTBS1
/u01/oradata/DG/undotbs01.dbf
USERS
/u01/oradata/DG/users01.dbf
TTS1
/u01/oradata/DG/tts1_01.dbf
TTS1
/u01/oradata/DG/tts1_02.dbf
6 rows selected.
SYS@DG> create user tranp identified by oracle123 default tablespace tts1;
User created.
SYS@DG> grant create session, resource to tranp;
Grant succeeded.
SYS@DG> create table tranp.t01 as select * from sys.dba_objects;
Table created.
3.TRANSPORT_SET_CHECK Procedure
This procedure checks if a set of tablespaces (to be transported) is self-contained. After calling this procedure, the user may select from a view to see a list of violations, if there are any.
If the view does not return any rows, then the set of tablespaces is self-contained. For example,
SYS@DG> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TTS1',TRUE);
PL/SQL procedure successfully completed.
SYS@DG> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
@> conn system
Connected.
4. export the tablespace metadata.
SYS@DG> create directory tranp_dir as '/home/oracle/ops/exp';
Directory created.
SYS@DG> grant read, write on directory tranp_dir to system;
Grant succeeded.
SYS@DG> alter tablespace tts1 read only;
Tablespace altered.
orarac1poc:DG:/home/oracle/ops/exp> cat expdp_trans_df.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
TRANSPORT_TABLESPACES=tts1
orarac1poc:DG:/home/oracle/ops/exp> expdp system parfile=expdp_trans_df.par
Export: Release 11.2.0.2.0 - Production on Wed Mar 20 10:34:14 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** parfile=expdp_trans_df.par
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:
/home/oracle/ops/exp/tts.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS1:
/u01/oradata/DG/tts1_01.dbf
/u01/oradata/DG/tts1_02.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:35:40
--now switch to target database which using ASM
5. copy the data file to ASM disk group
SYS@ORCL1> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
USERS
+DGDATA/orcl/users01.dbf
UNDOTBS1
+DGDATA/orcl/undotbs01.dbf
SYSAUX
+DGDATA/orcl/sysaux01.dbf
SYSTEM
+DGDATA/orcl/system01.dbf
EXAMPLE
+DGDATA/orcl/example01.dbf
UNDOTBS2
+DGDATA/orcl/undotbs02.dbf
6 rows selected.
--copy to ASM
orarac1poc:ORCL1:/home/oracle/ops/exp> su - grid
Password:
[grid@orarac1poc ~]$ ls -l /u01/oradata/DG/tts*
-rw-r----- 1 oracle asmadmin 20979712 Mar 20 10:27 /u01/oradata/DG/tts1_01.dbf
-rw-r----- 1 oracle asmadmin 20979712 Mar 20 10:27 /u01/oradata/DG/tts1_02.dbf
[grid@orarac1poc ~]$ asmcmd
ASMCMD> cp /u01/oradata/DG/tts1_01.dbf +DGDATA/orcl/
copying /u01/oradata/DG/tts1_01.dbf -> +DGDATA/orcl/tts1_01.dbf
ASMCMD> cp /u01/oradata/DG/tts1_02.dbf +DGDATA/orcl/
copying /u01/oradata/DG/tts1_02.dbf -> +DGDATA/orcl/tts1_02.dbf
ASMCMD> ls -l +DGDATA/orcl/
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N control01.ctl => +DGDATA/ORCL/CONTROLFILE/Current.260.803128289
N control02.ctl => +DGDATA/ORCL/CONTROLFILE/Current.261.803128289
N example01.dbf => +DGDATA/ORCL/DATAFILE/UNKNOWN.265.803128321
N redo01.log => +DGDATA/ORCL/ONLINELOG/group_1.262.803128313
N redo02.log => +DGDATA/ORCL/ONLINELOG/group_2.263.803128313
N redo03.log => +DGDATA/ORCL/ONLINELOG/group_3.267.803128495
N redo04.log => +DGDATA/ORCL/ONLINELOG/group_4.268.803128495
N spfileORCL.ora => +DGDATA/ORCL/PARAMETERFILE/spfile.269.803128497
N sysaux01.dbf => +DGDATA/ORCL/DATAFILE/SYSAUX.257.803128215
N system01.dbf => +DGDATA/ORCL/DATAFILE/SYSTEM.256.803128215
N temp01.dbf => +DGDATA/ORCL/TEMPFILE/TEMP.264.803128317
N tts1_01.dbf => +DGDATA/ASM/DATAFILE/tts1_01.dbf.270.810557179
N tts1_02.dbf => +DGDATA/ASM/DATAFILE/tts1_02.dbf.271.810557191
N undotbs01.dbf => +DGDATA/ORCL/DATAFILE/UNDOTBS1.258.803128215
N undotbs02.dbf => +DGDATA/ORCL/DATAFILE/UNDOTBS2.266.803128451
N users01.dbf => +DGDATA/ORCL/DATAFILE/USERS.259.803128215
ASMCMD> exit
[grid@orarac1poc ~]$ exit
logout
6. create id, data pump working directory, and import the metadata.
orarac1poc:ORCL1:/home/oracle/ops/exp> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 20 10:52:14 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@ORCL1> create directory tranp_dir as '/home/oracle/ops/exp';
Directory created.
SYS@ORCL1> grant read, write on directory tranp_dir to system;
Grant succeeded.
SYS@ORCL1> create user tranp identified by oracle123 default tablespace tts1;
create user tranp identified by oracle123 default tablespace tts1
*
ERROR at line 1:
ORA-00959: tablespace 'TTS1' does not exist
SYS@ORCL1> create user tranp identified by oracle123;
User created.
SYS@ORCL1> grant create session, resource to tranp;
Grant succeeded.
orarac1poc:ORCL1:/home/oracle/ops/exp> cat impdp_trans_df.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
#TRANSPORT_TABLESPACES=tts1
TRANSPORT_DATAFILES='+DGDATA/orcl/tts1_01.dbf' ,'+DGDATA/orcl/tts1_02.dbf'
orarac1poc:ORCL1:/home/oracle/ops/exp> impdp system parfile=impdp_trans_df.par
Import: Release 11.2.0.2.0 - Production on Wed Mar 20 10:58:29 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, 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/******** parfile=impdp_trans_df.par
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 10:59:00
8. verification
orarac1poc:ORCL1:/home/oracle/ops/exp> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 20 10:59:28 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@ORCL1> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
USERS
+DGDATA/orcl/users01.dbf
UNDOTBS1
+DGDATA/orcl/undotbs01.dbf
SYSAUX
+DGDATA/orcl/sysaux01.dbf
SYSTEM
+DGDATA/orcl/system01.dbf
EXAMPLE
+DGDATA/orcl/example01.dbf
UNDOTBS2
+DGDATA/orcl/undotbs02.dbf
TTS1
+DGDATA/orcl/tts1_02.dbf
TTS1
+DGDATA/orcl/tts1_01.dbf
8 rows selected.
SYS@ORCL1> select count(*) from tranp.t01;
COUNT(*)
----------
60708
SYS@ORCL1> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
TTS1 READ ONLY
8 rows selected.
SYS@ORCL1> alter tablespace tts1 read write;
Tablespace altered.
SYS@ORCL1> alter user tranp default tablespace tts1;
User altered.
SYS@ORCL1> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
TTS1 ONLINE
8 rows selected.
@> conn tranp/oracle123
Connected.
TRANP@ORCL1> select count(*) from t01;
COUNT(*)
----------
60708
References:
Oracle传输表空间总结
1. check endian format and character set.
SYS@DG> desc V$TRANSPORTABLE_PLATFORM
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
PLATFORM_ID NUMBER
PLATFORM_NAME VARCHAR2(101)
ENDIAN_FORMAT VARCHAR2(14)
SYS@DG> column PLATFORM_NAME format a30
SYS@DG> select * from V$TRANSPORTABLE_PLATFORM ;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------ --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
13 Linux x86 64-bit Little
16 Apple Mac OS Big
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
19 HP IA Open VMS Little
20 Solaris Operating System (x86- Little
64)
21 Apple Mac OS (x86-64) Little
20 rows selected.
SYS@DG> select PLATFORM_NAME from v$database;
PLATFORM_NAME
------------------------------
Linux x86 64-bit
select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from
(select VALUE$ aa from sys.props$ where name='NLS_LANGUAGE')tab1,
(select VALUE$ bb from sys.props$ where name='NLS_ISO_CURRENCY')tab2,
4 (select VALUE$ cc from sys.props$ where name='NLS_CHARACTERSET')tab3;
TAB1.AA||'_'||TAB2.BB||'.'||TAB3.CC
-------------------------------------------------------------------------------------------------------------------
AMERICAN_AMERICA.WE8MSWIN1252
2. Create test tablespace TTS1 having two data files, id, for transport.
SYS@DG> select file_name from dba_data_files;
FILE_NAME
-------------------------------------------------------------------------------------------------------------------
/u01/oradata/DG/system01.dbf
/u01/oradata/DG/sysaux01.dbf
/u01/oradata/DG/undotbs01.dbf
/u01/oradata/DG/users01.dbf
SYS@DG> create tablespace tts1 datafile '/u01/oradata/DG/tts1_01.dbf' size 20m;
Tablespace created.
SYS@DG> alter tablespace tts1 add datafile '/u01/oradata/DG/tts1_02.dbf' size 20m;
Tablespace altered.
SYS@DG> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
SYSTEM
/u01/oradata/DG/system01.dbf
SYSAUX
/u01/oradata/DG/sysaux01.dbf
UNDOTBS1
/u01/oradata/DG/undotbs01.dbf
USERS
/u01/oradata/DG/users01.dbf
TTS1
/u01/oradata/DG/tts1_01.dbf
TTS1
/u01/oradata/DG/tts1_02.dbf
6 rows selected.
SYS@DG> create user tranp identified by oracle123 default tablespace tts1;
User created.
SYS@DG> grant create session, resource to tranp;
Grant succeeded.
SYS@DG> create table tranp.t01 as select * from sys.dba_objects;
Table created.
3.TRANSPORT_SET_CHECK Procedure
This procedure checks if a set of tablespaces (to be transported) is self-contained. After calling this procedure, the user may select from a view to see a list of violations, if there are any.
If the view does not return any rows, then the set of tablespaces is self-contained. For example,
SQLPLUS> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('foo,bar', TRUE);
SQLPLUS> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
in my case:
SYS@DG> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TTS1',TRUE);
PL/SQL procedure successfully completed.
SYS@DG> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
@> conn system
Connected.
4. export the tablespace metadata.
SYS@DG> create directory tranp_dir as '/home/oracle/ops/exp';
Directory created.
SYS@DG> grant read, write on directory tranp_dir to system;
Grant succeeded.
SYS@DG> alter tablespace tts1 read only;
Tablespace altered.
orarac1poc:DG:/home/oracle/ops/exp> cat expdp_trans_df.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
TRANSPORT_TABLESPACES=tts1
orarac1poc:DG:/home/oracle/ops/exp> expdp system parfile=expdp_trans_df.par
Export: Release 11.2.0.2.0 - Production on Wed Mar 20 10:34:14 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** parfile=expdp_trans_df.par
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:
/home/oracle/ops/exp/tts.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS1:
/u01/oradata/DG/tts1_01.dbf
/u01/oradata/DG/tts1_02.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:35:40
--now switch to target database which using ASM
5. copy the data file to ASM disk group
SYS@ORCL1> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
USERS
+DGDATA/orcl/users01.dbf
UNDOTBS1
+DGDATA/orcl/undotbs01.dbf
SYSAUX
+DGDATA/orcl/sysaux01.dbf
SYSTEM
+DGDATA/orcl/system01.dbf
EXAMPLE
+DGDATA/orcl/example01.dbf
UNDOTBS2
+DGDATA/orcl/undotbs02.dbf
6 rows selected.
--copy to ASM
orarac1poc:ORCL1:/home/oracle/ops/exp> su - grid
Password:
[grid@orarac1poc ~]$ ls -l /u01/oradata/DG/tts*
-rw-r----- 1 oracle asmadmin 20979712 Mar 20 10:27 /u01/oradata/DG/tts1_01.dbf
-rw-r----- 1 oracle asmadmin 20979712 Mar 20 10:27 /u01/oradata/DG/tts1_02.dbf
[grid@orarac1poc ~]$ asmcmd
ASMCMD> cp /u01/oradata/DG/tts1_01.dbf +DGDATA/orcl/
copying /u01/oradata/DG/tts1_01.dbf -> +DGDATA/orcl/tts1_01.dbf
ASMCMD> cp /u01/oradata/DG/tts1_02.dbf +DGDATA/orcl/
copying /u01/oradata/DG/tts1_02.dbf -> +DGDATA/orcl/tts1_02.dbf
ASMCMD> ls -l +DGDATA/orcl/
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N control01.ctl => +DGDATA/ORCL/CONTROLFILE/Current.260.803128289
N control02.ctl => +DGDATA/ORCL/CONTROLFILE/Current.261.803128289
N example01.dbf => +DGDATA/ORCL/DATAFILE/UNKNOWN.265.803128321
N redo01.log => +DGDATA/ORCL/ONLINELOG/group_1.262.803128313
N redo02.log => +DGDATA/ORCL/ONLINELOG/group_2.263.803128313
N redo03.log => +DGDATA/ORCL/ONLINELOG/group_3.267.803128495
N redo04.log => +DGDATA/ORCL/ONLINELOG/group_4.268.803128495
N spfileORCL.ora => +DGDATA/ORCL/PARAMETERFILE/spfile.269.803128497
N sysaux01.dbf => +DGDATA/ORCL/DATAFILE/SYSAUX.257.803128215
N system01.dbf => +DGDATA/ORCL/DATAFILE/SYSTEM.256.803128215
N temp01.dbf => +DGDATA/ORCL/TEMPFILE/TEMP.264.803128317
N tts1_01.dbf => +DGDATA/ASM/DATAFILE/tts1_01.dbf.270.810557179
N tts1_02.dbf => +DGDATA/ASM/DATAFILE/tts1_02.dbf.271.810557191
N undotbs01.dbf => +DGDATA/ORCL/DATAFILE/UNDOTBS1.258.803128215
N undotbs02.dbf => +DGDATA/ORCL/DATAFILE/UNDOTBS2.266.803128451
N users01.dbf => +DGDATA/ORCL/DATAFILE/USERS.259.803128215
ASMCMD> exit
[grid@orarac1poc ~]$ exit
logout
6. create id, data pump working directory, and import the metadata.
orarac1poc:ORCL1:/home/oracle/ops/exp> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 20 10:52:14 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@ORCL1> create directory tranp_dir as '/home/oracle/ops/exp';
Directory created.
SYS@ORCL1> grant read, write on directory tranp_dir to system;
Grant succeeded.
SYS@ORCL1> create user tranp identified by oracle123 default tablespace tts1;
create user tranp identified by oracle123 default tablespace tts1
*
ERROR at line 1:
ORA-00959: tablespace 'TTS1' does not exist
SYS@ORCL1> create user tranp identified by oracle123;
User created.
SYS@ORCL1> grant create session, resource to tranp;
Grant succeeded.
orarac1poc:ORCL1:/home/oracle/ops/exp> cat impdp_trans_df.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
#TRANSPORT_TABLESPACES=tts1
TRANSPORT_DATAFILES='+DGDATA/orcl/tts1_01.dbf' ,'+DGDATA/orcl/tts1_02.dbf'
orarac1poc:ORCL1:/home/oracle/ops/exp> impdp system parfile=impdp_trans_df.par
Import: Release 11.2.0.2.0 - Production on Wed Mar 20 10:58:29 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, 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/******** parfile=impdp_trans_df.par
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 10:59:00
8. verification
orarac1poc:ORCL1:/home/oracle/ops/exp> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 20 10:59:28 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@ORCL1> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
USERS
+DGDATA/orcl/users01.dbf
UNDOTBS1
+DGDATA/orcl/undotbs01.dbf
SYSAUX
+DGDATA/orcl/sysaux01.dbf
SYSTEM
+DGDATA/orcl/system01.dbf
EXAMPLE
+DGDATA/orcl/example01.dbf
UNDOTBS2
+DGDATA/orcl/undotbs02.dbf
TTS1
+DGDATA/orcl/tts1_02.dbf
TTS1
+DGDATA/orcl/tts1_01.dbf
8 rows selected.
SYS@ORCL1> select count(*) from tranp.t01;
COUNT(*)
----------
60708
SYS@ORCL1> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
TTS1 READ ONLY
8 rows selected.
SYS@ORCL1> alter tablespace tts1 read write;
Tablespace altered.
SYS@ORCL1> alter user tranp default tablespace tts1;
User altered.
SYS@ORCL1> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
TTS1 ONLINE
8 rows selected.
@> conn tranp/oracle123
Connected.
TRANP@ORCL1> select count(*) from t01;
COUNT(*)
----------
60708
References:
Oracle传输表空间总结
Oracle® Database PL/SQL Packages and Types Reference11g Release 2 (11.2)
Subscribe to:
Posts (Atom)
