课程第34次
select* from v$transportable_platformorder by 2;
PLATFORM_IDPLATFORM_NAMEENDIAN_FORMAT
16AIX-Based Systems (64-bit)Big
216Apple Mac OSBig
321Apple Mac OS (x86-64)Little
419HP IA Open VMSLittle
515HP Open VMSLittle
65HP Tru64 UNIXLittle
73HP-UX (64-bit)Big
84HP-UX IA (64-bit)Big
918IBM Power Based LinuxBig
109IBM zSeries Based LinuxBig
1110Linux IA (32-bit)Little
1211Linux IA (64-bit)Little
1313Linux x86 64-bitLittle
147Microsoft Windows IA (32-bit)Little
158Microsoft Windows IA (64-bit)Little
1612Microsoft Windows x86 64-bitLittle
1717Solaris Operating System (x86)Little
1820Solaris Operating System (x86-64)Little
191Solaris OE (32-bit)Big
202Solaris OE (64-bit)Big
传送表空间要大的跨平台：转换数据文件头+数据文件体。
如果要convert（不同的endian序列才要covert）,源头convert语法是：convert tablespace to platform
目的地convert语法是：convert datafile from platform
----------------------------------------------------WINORCL 原来是4K ，重新穿件控制文件和日志文件-----------------------------
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area617975808 bytes
Fixed Size 2283744 bytes
Variable Size 234882848 bytes
Database Buffers 373293056 bytes
Redo Buffers 7516160 bytes
数据库装载完毕。
SQL> alter database open ;
alter database open
*
第 1 行出现错误:
ORA-03113: 通信通道的文件结尾
进程 ID: 254808
会话 ID: 191 序列号: 3 ------------------------------------------------
drop 或者clear unarchived 都过不去：
SQL> conn / as sysdba
已连接。
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
第 1 行出现错误:
ORA-01623: 日志 2 是实例 winorcl (线程 1) 的当前日志 - 无法删除
ORA-00312: 联机日志 2 线程 1: 'C:\APP\APPLE\ORADATA\WINORCL\REDO02.LOG'
SQL> alter database clear unarchived logfile group 2blocksize 4096;
alter database clear unarchived logfile group 2blocksize 4096
*
第 1 行出现错误:
ORA-00933: SQL 命令未正确结束
脚本重建控制文件，编辑去掉"4096"，要选择set 2：
CREATE CONTROLFILE REUSE DATABASE "WINORCL" RESETLOGSARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 2 'C:\APP\APPLE\ORADATA\WINORCL\REDO02.LOG'SIZE 50M BLOCKSIZE 512,
GROUP 4 'C:\APP\APPLE\ORADATA\WINORCL\REDO04.LOG'SIZE 100M BLOCKSIZE 512,
GROUP 5 'C:\APP\APPLE\ORADATA\WINORCL\REDO05.LOG'SIZE 100M BLOCKSIZE 512,
GROUP 6 'C:\APP\APPLE\ORADATA\WINORCL\REDO06.LOG'SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'C:\APP\APPLE\ORADATA\WINORCL\SYSTEM01.DBF',
'C:\APP\APPLE\ORADATA\WINORCL\SYSAUX01.DBF',
'C:\APP\APPLE\ORADATA\WINORCL\UNDOTBS01.DBF',
'C:\APP\APPLE\ORADATA\WINORCL\USERS01.DBF',
'C:\APP\APPLE\ORADATA\WINORCL\RCAT01.DBF',
'C:\APP\APPLE\ORADATA\WINORCL\TBS05319.DBF',
'C:\APP\APPLE\ORADATA\WINORCL\TBS05319X.DBF',
'C:\APP\APPLE\ORADATA\WINORCL\TBSSOLARIS.DBF'
CHARACTER SET AL32UTF8
;
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\APP\APPLE\ORADAT\WINORCL\TEMP01.DBF' REUSE;
---------------------------------------------------------------------------------------------------------------------
源头转换：
create tablespace tbstrans1 datafile size 10M;
create tablespace tbstrans2 datafile size 10M;
alter tablespace tbstrans1 read only;
alter tablespace tbstrans2 read only ;
begin
dbms_tts.transport_set_check('TBSTRANS1,TBSTRANS2');
end;
select* from transport_set_violations;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TOBACKUPSET;
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
released channel: ORA_DISK_5
released channel: ORA_DISK_6
released channel: ORA_DISK_7
released channel: ORA_DISK_8
RMAN>convert tablespace tbstrans1to platform 'Microsoft Windows IA (64-bit)' format '/home/oracle/tbstrans1.dbf';
Starting conversion at source at 2019-12-22:21:29:11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=79 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=77 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=130 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=20 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=69 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=145 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=143 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=203 device type=DISK
ignoring encryption for proxy or image copies
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00005 name=+DATA/orcl/datafile/tbstrans1.261.1027718433
converted datafile=/home/oracle/tbstrans1.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at source at 2019-12-22:21:29:13
RMAN>
C:\Users\Apple>impdp system/oracle_4Udirectory=dirwin dumpfile=tbstrans1.dmpTRANSPORT_DATAFILES='C:\app\Apple\oradata\winorcl\tbstrans1.dbf'
Import: Release 11.2.0.4.0 - Production on 星期日 12月 22 22:00:47 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"
源时区为 +00:00, 目标时区为 +08:00。
启动 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":system/******** directory=dirwin dumpfile=tbstrans1.dmp TRANSPORT_DATAFILES='C:\app\Apple\oradata\winorcl\tbstrans1.dbf'
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
作业 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 已于 星期日 12月 22 22:00:59 2019 elapsed 0 00:00:06 成功完成
C:\Users\Apple>
-------------
目的地转换：
在源头上故意转不对的平台：
RMAN>convert tablespace tbstrans2 to platform 'IBM zSeries Based Linux' format '/home/oracle/tbstrans2.dbf' ;
Starting conversion at source at 2019-12-22:22:07:10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=82 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=143 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=130 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=204 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=138 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=197 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=21 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=83 device type=DISK
ignoring encryption for proxy or image copies
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00006 name=+DATA/orcl/datafile/tbstrans2.262.1027718497
converted datafile=/home/oracle/tbstrans2.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at source at 2019-12-22:22:07:28
RMAN>
expdp部分倒是一样的：
$ expdp system/oracle_4U directory=dir1 dumpfile=tbstrans2.dmpTRANSPORT_TABLESPACES=tbstrans2
Export: Release 11.2.0.4.0 - Production on Sun Dec 22 22:05:38 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":system/******** directory=dir1 dumpfile=tbstrans2.dmp TRANSPORT_TABLESPACES=tbstrans2
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/dir1/tbstrans2.dmp
******************************************************************************
Datafiles required for transportable tablespace TBSTRANS2:
+DATA/orcl/datafile/tbstrans2.262.1027718497
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Sun Dec 22 22:07:40 2019 elapsed 0 00:02:00
如果传送的数据文件endian序列不是目的地的，就会报“文件头”出错：
C:\Users\Apple>impdp system/oracle_4Udirectory=dirwin dumpfile=tbstrans2.dmpTRANSPORT_DATAFILES='C:\app\Apple\oradata\winorcl\tbstrans2.dbf'
Import: Release 11.2.0.4.0 - Production on 星期日 12月 22 22:10:51 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"
源时区为 +00:00, 目标时区为 +08:00。
启动 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":system/******** directory=dirwin dumpfile=tbstrans2.dmp TRANSPORT_DATAFILES='C:\app\Apple\oradata\winorcl\tbstrans2.dbf'
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: 数据泵可传输的表空间作业中止
ORA-01565: 标识文件 'C:\app\Apple\oradata\winorcl\tbstrans2.dbf' 时出错
ORA-27048: skgfifi: 文件标头信息无效
OSD-04001: ？???？С？Ч (OS 2097152)
作业 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 因致命错误于 星期日 12月 22 22:10:57 2019 elapsed 0 00:00:04 停止
解决办法就是在目的地上再转换：
C:\Users\Apple>rman target/
恢复管理器: Release 11.2.0.4.0 - Production on 星期日 12月 22 22:14:05 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.All rights reserved.
已连接到目标数据库: WINORCL (DBID=2162311502)
RMAN> convert datafile 'C:\app\Apple\oradata\winorcl\tbstrans2.dbf' from platform 'IBM zSeries Based Linux'format 'C:\app\Apple\oradata\winorcl\tbstrans2b.dbf' ;
启动 conversion at target 于 22-12月-19
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=10 设备类型=DISK
通道 ORA_DISK_1: 启动数据文件转换
输入文件名=C:\APP\APPLE\ORADATA\WINORCL\TBSTRANS2.DBF
已转换的数据文件 = C:\APP\APPLE\ORADATA\WINORCL\TBSTRANS2B.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
完成 conversion at target 于 22-12月-19
再次导入（与tbstrans1一样）：
C:\Users\Apple>impdp system/oracle_4Udirectory=dirwin dumpfile=tbstrans2.dmpTRANSPORT_DATAFILES='C:\app\Apple\oradata\winorcl\tbstrans2b.dbf'
Import: Release 11.2.0.4.0 - Production on 星期日 12月 22 22:19:52 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"
源时区为 +00:00, 目标时区为 +08:00。
启动 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":system/******** directory=dirwin dumpfile=tbstrans2.dmp TRANSPORT_DATAFILES='C:\app\Apple\oradata\winorcl\tbstrans2b.dbf'
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
作业 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 已于 星期日 12月 22 22:20:00 2019 elapsed 0 00:00:05 成功完成
-----------传送数据库：
先把源头库只读，然后：
$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Dec 22 22:31:33 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.All rights reserved.
connected to target database: ORCL (DBID=1554722616)
RMAN> convert database to platform'Microsoft Windows IA (64-bit)' format '/home/oracle/dbtrans/%U';
Starting conversion at source at 2019-12-22:22:32:25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=194 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=8 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=72 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=134 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=197 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=7 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=71 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=135 device type=DISK
Directory SYS.DIR1 found in the database
Directory SYS.XMLDIR found in the database
Directory SYS.ORACLE_OCM_CONFIG_DIR2 found in the database
Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
User SYS with SYSDBA and SYSOPER privilege found in password file
ignoring encryption for proxy or image copies
channel ORA_DISK_2: starting datafile conversion
input datafile file number=00001 name=+DATA/orcl/datafile/system.283.1026941797
channel ORA_DISK_3: starting datafile conversion
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.282.1026941797
channel ORA_DISK_4: starting datafile conversion
input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.281.1026941797
channel ORA_DISK_5: starting datafile conversion
input datafile file number=00005 name=+DATA/orcl/datafile/tbstrans1.261.1027718433
channel ORA_DISK_6: starting datafile conversion
input datafile file number=00006 name=+DATA/orcl/datafile/tbstrans2.262.1027718497
channel ORA_DISK_7: starting datafile conversion
input datafile file number=00004 name=+DATA/orcl/datafile/users.265.1027544577
converted datafile=/home/oracle/dbtrans/data_D-ORCL_I-1554722616_TS-TBSTRANS1_FNO-5_34uk3jgh
channel ORA_DISK_5: datafile conversion complete, elapsed time: 00:00:00
converted datafile=/home/oracle/dbtrans/data_D-ORCL_I-1554722616_TS-TBSTRANS2_FNO-6_35uk3jgh
channel ORA_DISK_6: datafile conversion complete, elapsed time: 00:00:01
converted datafile=/home/oracle/dbtrans/data_D-ORCL_I-1554722616_TS-USERS_FNO-4_36uk3jgh
channel ORA_DISK_7: datafile conversion complete, elapsed time: 00:00:02
converted datafile=/home/oracle/dbtrans/data_D-ORCL_I-1554722616_TS-UNDOTBS1_FNO-3_33uk3jgh
channel ORA_DISK_4: datafile conversion complete, elapsed time: 00:00:04
converted datafile=/home/oracle/dbtrans/data_D-ORCL_I-1554722616_TS-SYSTEM_FNO-1_31uk3jgf
channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:28
converted datafile=/home/oracle/dbtrans/data_D-ORCL_I-1554722616_TS-SYSAUX_FNO-2_32uk3jgg
channel ORA_DISK_3: datafile conversion complete, elapsed time: 00:00:27
Edit init.ora file /home/oracle/dbtrans/init_00uk3jgf_1_0.ora. This PFILE will be used to create the database on the target platform
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished conversion at source at 2019-12-22:22:33:15
RMAN>
注意观察ADR中的日志，在以上rman命令结束的最后一个日志：
-rw-r-----. 1 oracle asmadmin 81 Dec 22 22:28 orcl_ora_19483.trm
-rw-r-----. 1 oracle asmadmin 978 Dec 22 22:28 orcl_ora_19483.trc
-rw-r-----. 1 oracle asmadmin 76 Dec 22 22:28 orcl_vktm_19500.trm
-rw-r-----. 1 oracle asmadmin 1092 Dec 22 22:28 orcl_vktm_19500.trc
-rw-r-----. 1 oracle asmadmin 60 Dec 22 22:28 orcl_asmb_19527.trm
-rw-r-----. 1 oracle asmadmin 873 Dec 22 22:28 orcl_asmb_19527.trc
-rw-r-----. 1 oracle asmadmin 69 Dec 22 22:28 orcl_mark_19535.trm
-rw-r-----. 1 oracle asmadmin 871 Dec 22 22:28 orcl_mark_19535.trc
-rw-r-----. 1 oracle asmadmin 60 Dec 22 22:28 orcl_mman_19512.trm
-rw-r-----. 1 oracle asmadmin 873 Dec 22 22:28 orcl_mman_19512.trc
-rw-r-----. 1 oracle asmadmin 110 Dec 22 22:29 orcl_dbw0_19514.trm
-rw-r-----. 1 oracle asmadmin 1259 Dec 22 22:29 orcl_dbw0_19514.trc
-rw-r-----. 1 oracle asmadmin 104 Dec 22 22:29 orcl_dbrm_19508.trm
-rw-r-----. 1 oracle asmadmin 1321 Dec 22 22:29 orcl_dbrm_19508.trc
-rw-r-----. 1 oracle asmadmin 173246 Dec 22 22:33 alert_orcl.log
-rw-r-----. 1 oracle asmadmin 156 Dec 22 22:33 orcl_ora_20401.trm
-rw-r-----. 1 oracle asmadmin 4700 Dec 22 22:33 orcl_ora_20401.trc
-rw-r-----. 1 oracle asmadmin 82 Dec 22 22:34 orcl_mmon_19529.trm
-rw-r-----. 1 oracle asmadmin 1099 Dec 22 22:34 orcl_mmon_19529.trc
$ cp orcl_ora_20401.trc/home/oracle/dbtrans/
$
所以/home/oracle/dbtrans目录下有所有的数据文件+已经在里面的参数文件和刚刚拷贝进去的trace（控制文件set2脚本）
页:
[1]