Oracle Cross-platform Transport Tablespace
Cross-platform Transport Tablespace
標籤： Oracle
Cross-platform Transport Tablespace的注意事項：
Oracle8/8i=>兩個database必須有相同oracle版本,OS平台,block size,characterset
Oracle9i=>兩個database必須有相同oracle版本,OS平台,characterset.block size可以不相同(可以有1個standard block,4個non-standard block同時存在)
Oracle10g/11g=>兩個database必須有相同oracle版本,characterset.block size與os平台可以不相同
做法：
Source:Export Metadata,offline db datafile
target:Import Metadata, cp source db datafile
SQL> col value format a40
SQL> select parameter,value from nls_database_parameters where parameter like '%CHARACTERSET';
PARAMETER VALUE
—————————— —————————————-
NLS_CHARACTERSET WE8MSWIN1252 –source與target資料庫.可以不同platform,不同block size,但是database characterset必須相同
NLS_NCHAR_CHARACTERSET AL16UTF16 –national characterset可以不同
SQL> col platform_name format a50
SQL> select * from v$transportable_platform order by platform_id;
–只要相同endian_format的os,就可以直接複製檔案,不需任何轉換.
–如果不同endian則須先使用rman轉換即可
–通常CISC(little),RISC(big)
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
———– ———————————————————— —————————-
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
5 HP Tru64 UNIX Little
6 AIX-Based Systems (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little
12 Microsoft Windows x86 64-bit Little
13 Linux x86 64-bit Little
15 HP Open VMS Little
16 Apple Mac OS Big
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
19 HP IA Open VMS Little
20 Solaris Operating System (x86-64) Little
21 Apple Mac OS (x86-64) Little
20 rows selected.
確認版本是否都一樣：
SQL> select * from v$version;
SQL> select platform_name from v$database;
建立Directory Home
SQL> create directory home_dir as '/home/oracle';
SQL> create tablespace fromlinux datafile '/u02/oradata/orcl/fromlinux01.dbf' size 10M;
SQL> create table hr.fromlinux tablespace fromlinux as select name,dbid,platform_name from v$database;
**transport tablespace之前,tablespace必須是read only狀態
SQL> alter tablespace fromlinux read only;
**檢驗fromlinux tablespace是否為self-contained constraints,full check
SQL> execute dbms_tts.transport_set_check('FROMLINUX',true,true);
PL/SQL procedure successfully completed.
**確認tablespace為self-contained
SQL> select * from transport_set_violations;
no rows selected –遵守self-contain規則(沒有任何的ORA代表是正常的)
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
**違反self-contained的範例
SQL> execute dbms_tts.transport_set_check('USERS',TRUE,TRUE);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
VIOLATIONS
————————————————————————————————————–
ORA-39917: SYS owned object T2 in tablespace USERS not allowed in pluggable set
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
–注意此處為transport_tablespaces,而不是tablespaces，只export metadata
[oracle@oracleDB ~]$ expdp system/oracle directory=home_dir dumpfile=fromlinux_ts_meta.dmp transport_tablespaces=fromlinux
**tablespace data藉由os複製指令完成搬移
[oracle@oracleDB ~]$ cp /u02/oradata/orcl/fromlinux01.dbf /home/oracle/fromlinux01.dbf –data複製
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
PS. **以下方式稱為tablespace mode(包含metadata,data都會被export到dumpfile)
expdp system/oracle directory=home_dir dumpfile=fromlinux_ts.dmp tablespaces=fromlinux
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
如果需要到不同endian的platform,需要使用rman轉換byte order
[oracle@oracleDB ~]$ export ORACLE_SID=orcl
[oracle@oracleDB ~]$ rman target /
connected to target database: ORCL (DBID=1240924906)
**請確定此時tablespace狀態為read only
RMAN> convert tablespace fromlinux to platform='Solaris[tm] OE (64-bit)' format '/home/oracle/fromlinux_to_solaris64.dbf';
–/home/oracle/fromlinux_to_solaris64.dbf為轉換為Solaris 64格式的檔案
RMAN> exit
SQL> alter tablespace fromlinux online;
Tablespace altered.
**然後將dumpfile/datafile一同傳遞到target database進行import即可
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
在Windows機器上
1.取得dumpfile與datafile
winscp方式取得dumpfile/datafile
**將datafile搬移到正確的目錄下
C:\>copy c:\fromlinux01.dbf C:\ORADATA\ORCL\fromlinux01.dbf
C:\>mkdir c:\wutemp
C:\>copy c:\fromlinux_ts_meta.dmp c:\wutemp\fromlinux_ts_meta.dmp
複製了 1 個檔案。
2.載入dumpfile/datafile
C:\>sqlplus / as sysdba
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
————————————————————
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
已選取 6 個資料列. –確定目前此database沒有叫做fromlinux的tablespace
SQL> create directory wutempdir as 'c:\wutemp';
已建立目錄.
–使用transport_datafiles指定datafile位置
C:\>impdp system/oracle directory=wutempdir dumpfile=fromlinux_ts_meta.dmp transport_datafiles=C:\ORADATA\ORCL\fromlinux01.dbf
C:\>sqlplus / as sysdba
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
—————— ———
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
FROMLINUX READ ONLY–tablespace已經成功載入
已選取 7 個資料列.
SQL> alter tablespace fromlinux online; –載入後tablespace還是維持read only
已更改表格空間.
SQL> select * from hr.fromlinux;
NAME DBID PLATFORM_NAME
—————— ———- ——————————
ORCL 1257464825 Linux IA (32-bit) –table也成功載入
EM->Data Movement->Move Database Files->Transport Tablespaces