RMAN跨小版本跨平台與位元組序傳輸資料表空間
將Linux平台上的來源資料庫中的tspitr與test資料表空間傳輸到AIX平台上。並在源主機上使用目錄/u02/transport來儲存被轉換的資料檔案。操作步驟如下:
1.將要被傳輸的資料表空間tspitr與test設定為唯讀
SQL> alter tablespace tspitr read only;
Tablespace altered.
SQL> alter tablespace test read only;
Tablespace altered.
2.檢查源平台與目標平台資訊支不支援傳輸操作
資料庫所支援的平台資訊:
SQL> select platform_name,endian_format from v$transportable_platform;
PLATFORM_NAME ENDIAN_FORMAT
-------------------------------------------------------------------------------- --------------
Solaris[tm] OE (32-bit) Big
Solaris[tm] OE (64-bit) Big
Microsoft Windows IA (32-bit) Little
Linux IA (32-bit) Little
AIX-Based Systems (64-bit) Big
HP-UX (64-bit) Big
HP Tru64 UNIX Little
HP-UX IA (64-bit) Big
Linux IA (64-bit) Little
HP Open VMS Little
Microsoft Windows IA (64-bit) Little
IBM zSeries Based Linux Big
Linux x86 64-bit Little
Apple Mac OS Big
Microsoft Windows x86 64-bit Little
Solaris Operating System (x86) Little
IBM Power Based Linux Big
Solaris Operating System (x86-64) Little
HP IA Open VMS Little
源平台:
SQL> select d.platform_name,endian_format from v$transportable_platform tp,v$database d where tp.platform_name=d.platform_name ;
PLATFORM_NAME ENDIAN_FORMAT
----------------------------------------------------------------------------------------------------- --------------
Linux 64-bit for AMD Little
目標平台
SQL> select d.platform_name,endian_format from v$transportable_platform tp,v$database d where tp.platform_name=d.platform_name ;
PLATFORM_NAME ENDIAN_FORMAT
-------------------------------------------------------------------------------- --------------
AIX-Based Systems (64-bit) Big
3.確認要被傳輸的資料表空間是否是自包含資料表空間(TSPITR,TEST):
SQL> exec sys.dbms_tts.transport_set_check('TSPITR',true);
PL/SQL procedure successfully completed.
SQL> exec sys.dbms_tts.transport_set_check('TEST',true);
PL/SQL procedure successfully completed.
SQL> select * from sys.transport_set_violations;
no rows selected
如果沒有行選擇，表示該資料表空間只包含表資料，可以傳輸。
4.記錄資料表空間傳輸前表tspitr與test中的記錄：
SQL> select count(*) from tspitr.tspitr;
COUNT(*)
----------
50315
SQL> select count(*) from test.test;
COUNT(*)
----------
50316
5.使用RMAN將來源資料庫中的資料表空間tspitr,test轉換為目標平台位元組序格式，使用format參數來控制被轉換後資料檔案的檔案名稱和儲存目錄.
[Oracle@oracle11g ~]export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
[oracle@oracle11g ~]rman target/
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Mar 30 10:35:41 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TEST (DBID=2168949517)
RMAN> convert tablespace "TSPITR","TEST"
2> to platform 'AIX-Based Systems (64-bit)'
3> format ='/u02/transport/%U';
Starting backup at 2015-03-30 10:37:27
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=/u01/app/oracle/oradata/test/tspitr01.dbf
converted datafile=/u02/transport/data_D-TEST_I-2168949517_TS-TSPITR_FNO-6_3aq349v8
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00007 name=/u01/app/oracle/oradata/test/test01.dbf
converted datafile=/u02/transport/data_D-TEST_I-2168949517_TS-TEST_FNO-7_3bq349vo
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 2015-03-30 10:37:45
[oracle@oracle11g transport]$ ls -lrt
total 112776
-rw-r----- 1 oracle oinstall 104865792 Mar 30 10:37 data_D-TEST_I-2168949517_TS-TSPITR_FNO-6_3aq349v8
-rw-r----- 1 oracle oinstall 10493952 Mar 30 10:37 data_D-TEST_I-2168949517_TS-TEST_FNO-7_3bq349vo
6.使用匯出工具建立傳輸資料表空間中繼資料dump檔案
SQL> create or replace directory test_dump as '/u02/transport';
Directory created.
SQL> grant read,write on directory test_dump to public;
Grant succeeded.
[oracle@oracle11g dump_test]$ expdp \'sys/zzh_2046@test as sysdba\' directory=test_dump dumpfile=tspitr_test.dmp logfile=tspitr_test.log transport_tablespaces=TSPITR,TEST
Export: Release 10.2.0.5.0 - Production on Monday, 30 March, 2015 10:57:37
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "sys/********@test AS SYSDBA" directory=test_dump dumpfile=tspitr_test.dmp logfile=tspitr_test.log transport_tablespaces=TSPITR,TEST
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/u02/dump_test/tspitr_test.dmp
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:57:55
[oracle@oracle11g dump_test]ls -lrt
-rw-r----- 1 oracle oinstall 960 Mar 30 15:23 tspitr_test.log
-rw-r----- 1 oracle oinstall 90112 Mar 30 15:23 tspitr_test.dmp
7.將轉換後儲存在/u02/transport目錄中的資料檔案與匯出的中繼資料檔案tspitr_test.dmp傳輸到目標主機的目錄/yb_oradata/transport中
ftp> put data_D-TEST_I-2168949517_TS-TSPITR_FNO-6_3aq349v8
200 PORT command successful.
150 Opening data connection for data_D-TEST_I-2168949517_TS-TSPITR_FNO-6_3aq349v
8.
226 Transfer complete.
ftp: 發送 104865792 位元組，用時 8.86秒 11839.88KB/秒。
ftp> put data_D-TEST_I-2168949517_TS-TEST_FNO-7_3bq349vo
200 PORT command successful.
150 Opening data connection for data_D-TEST_I-2168949517_TS-TEST_FNO-7_3bq349vo.
226 Transfer complete.
ftp: 發送 10493952 位元組，用時 0.90秒 11659.95KB/秒。
ftp> put tspitr_test.dmp
200 PORT command successful.
150 Opening data connection for tspitr_test.dmp.
226 Transfer complete.
[IBMP740-1:oracle:/yb_oradata]$ls -lrt
-rwxrwxrwx 1 oracle dba 104865792 Mar 30 12:42 data_D-TEST_I-2168949517_TS-TSPITR_FNO-6_3aq349v8
-rwxrwxrwx 1 oracle dba 10493952 Mar 30 12:42 data_D-TEST_I-2168949517_TS-TEST_FNO-7_3bq349vo
-rwxrwxrwx 1 oracle dba 98304 Mar 30 12:42 tspitr_test.dmp
8.將要被傳輸的資料表空間附加到目標資料庫中
[IBMP740-1:oracle:/yb_oradata]$sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Mar 30 11:31:47 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user tspitr identified by "tspitr";
User created.
SQL> grant dba,connect,resource to tspitr;
Grant succeeded.
SQL> create user test identified by "test";
User created.
SQL> grant dba,connect,resource to test;
Grant succeeded.
SQL> create or replace directory test_dump as '/yb_oradata/transport';
Directory created.
SQL> grant read,write on directory test_dump to public;
Grant succeeded.
[IBMP740-1:oracle]$impdp system/system directory=test_dump dumpfile=tspitr_test.dmp transport_datafiles=/yb_oradata/data_D-TEST_I-2168949517_TS-TEST_FNO-7_3bq349vo,/yb_oradata/data_D-TEST_I-2168949517_TS-TSPITR_FNO-6_3aq349v8
Import: Release 10.2.0.4.0 - 64bit Production on Monday, 30 March, 2015 11:45:48
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=test_dump dumpfile=tspitr_test.dmp transport_datafiles=/yb_oradata/transport/data_D-TEST_I-2168949517_TS-TEST_FNO-7_3bq349vo,/yb_oradata/transport/data_D-TEST_I-2168949517_TS-TSPITR_FNO-6_3aq349v8
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-00721: changes by release 10.2.0.5.0 cannot be used by release 10.2.0.3.0
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 11:45:53
出錯原因是因為來源資料庫的compatible=10.2.0.5.0,目標資料庫的compatible=10.2.0.3.0這裡在匯出使用version=10.2.0.3.0匯出中繼資料後再執行匯出也是同樣會報這個錯誤，因為這裡不是邏輯匯出，而是傳輸資料表空間，所以version參數不起作用。所以想修改來源資料庫的compatible參數為10.2.0.3.0,但在10g以後,compatible參數只能增大不能減少。
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.5.0
SQL> alter system set compatible='10.2.0.3.0' scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1272600 bytes
Variable Size 109053160 bytes
Database Buffers 54525952 bytes
Redo Buffers 2920448 bytes
ORA-00201: control file version 10.2.0.5.0 incompatible with ORACLE version
10.2.0.3.0
ORA-00202: control file: '/u01/app/oracle/oradata/test/control01.ctl'
在將來源資料庫的compatible參數修改為10.2.0.3.0後無法啟動資料庫。
更多詳情見請繼續閱讀下一頁的精彩內容：
--------------------------------------推薦閱讀 --------------------------------------
RMAN 配置歸檔日誌刪除策略
Oracle基礎教程之通過RMAN複製資料庫
RMAN備份策略制定參考內容
RMAN備份學習筆記
OracleDatabase Backup加密 RMAN加密
--------------------------------------分割線 --------------------------------------
|
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
