重庆思庄Oracle、Redhat认证学习论坛

标题: oracle从linux往windows传输表空间 [打印本页]

作者: 王亮 时间: 2020-4-5 16:29
标题: oracle从linux往windows传输表空间

1、查看数据库数据传输支持的操作系统

Select * fromv$transportable_platfrom order3;

SQL> select * from v$transportable_platform order by 3;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT

----------- ---------------------------------------- --------------

3 HP-UX (64-bit) Big

6 AIX-Based Systems (64-bit) Big

18 IBM Power Based Linux Big

2 Solaris[tm] OE (64-bit) Big

4 HP-UX IA (64-bit) Big

16 Apple Mac OS Big

1 Solaris[tm] OE (32-bit) Big

9 IBM zSeries Based Linux Big

17 Solaris Operating System (x86) Little

19 HP IA Open VMS Little

20 Solaris Operating System (x86-64) Little

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT

----------- ---------------------------------------- --------------

12 Microsoft Windows x86 64-bit Little

13 Linux x86 64-bit Little

8 Microsoft Windows IA (64-bit) Little

21 Apple Mac OS (x86-64) Little

11 Linux IA (64-bit) Little

5 HP Tru64 UNIX Little

10 Linux IA (32-bit) Little

7 Microsoft Windows IA (32-bit) Little

15 HP Open VMS Little

20 rows selected.

2、查看数据库字符集

源数据库

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')

----------------------------------------------------

AMERICAN_AMERICA.AL32UTF8

目标数据库

file:///C:/Users/LENOVO/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png

file:///C:/Users/LENOVO/AppData/Local/Temp/msohtmlclip1/01/clip_image002.png

3、进行表空间传输

查看源库的表空间信息

SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC CON_ID

---------- ------------------------------ --- --- --- --- ----------

0 SYSTEM YES NO YES 0

1 SYSAUX YES NO YES 0

2 UNDOTBS1 YES NO YES 0

4 USERS YES NO YES 0

6 HYCS YES NO YES 0

7 TEMP1 NO NO YES 0

6 rows selected.

为HYCS表空间创建用户

SQL> create user HYCS identified by HYCS default tablespace HYCS temporary tablespace temp1;

User created.

SQL> grant connect to HYCS;

grant resource to HYCS;

Grant succeeded.

SQL>

Grant succeeded.

使用新创建的用户创建一张测试表

SQL> create table hycs_test as

2 select rownum as id,

3 to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime,

4 trunc(dbms_random.value(0, 100)) as random_id,

5 dbms_random.string('x', 20) random_string

6 from dual

7 connect by level <= 1000;

Table created.

SQL>

将HYCS表空间设置为只读模式

SQL> alter tablespace HYCS read only;

Tablespace altered.

将HYCS表空间文件进行转换

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Fri Apr 3 05:04:01 2020

connected to target database: ORCL (DBID=1562527214)

RMAN> convert tablespace HYCS

2> to platform 'Microsoft Windows x86 64-bit'

3> format '/home/oracle/HYCS1.dbf';

Starting conversion at source at 03-APR-20

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: starting datafile conversion

input datafile file number=00005 name=/u01/app/oradata/orcl/HYCS.DBF

converted datafile=/home/oracle/HYCS1.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25

Finished conversion at source at 03-APR-20

RMAN>

查看源数据库的数据泵配置，准备导出HYCS表空间

SQL> select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from dba_directories

OWNER DIRECTORY_NAME DIRECTORY_PATH

---------- -------------------- ------------------------------------------------------------

SYS XSDDIR /u01/app/oracle/product/12.2.1/dbhome_1/rdbms/xml/schema

SYS ORA_DBMS_FCP_LOGDIR /u01/app/oracle/product/12.2.1/dbhome_1/cfgtoollogs

SYS ORA_DBMS_FCP_ADMINDI /u01/app/oracle/product/12.2.1/dbhome_1/rdbms/admin

SYS OPATCH_INST_DIR /u01/app/oracle/product/12.2.1/dbhome_1/OPatch

SYS OPATCH_SCRIPT_DIR /u01/app/oracle/product/12.2.1/dbhome_1/QOpatch

SYS ORACLE_BASE /

SYS OPATCH_LOG_DIR /u01/app/oracle/product/12.2.1/dbhome_1/QOpatch

SYS ORACLE_HOME /

SYS XMLDIR /u01/app/oracle/product/12.2.1/dbhome_1/rdbms/xml

OWNER DIRECTORY_NAME DIRECTORY_PATH

---------- -------------------- ------------------------------------------------------------

SYS ORACLE_OCM_CONFIG_DI /u01/app/oracle/product/12.2.1/dbhome_1/ccr/state

SYS DATA_PUMP_DIR /u01/app/oracle/product/12.2.1/dbhome_1/rdbms/log/

SYS ORACLE_OCM_CONFIG_DI /u01/app/oracle/product/12.2.1/dbhome_1/ccr/state

12 rows selected.

导出HYCS表空间：

[oracle@localhost ~]$ expdp \' / as sysdba\' directory=DATA_PUMP_DIR dumpfile=HYCS.dmp transport_tablespaces=HYCS

Export: Release 12.2.0.1.0 - Production on Fri Apr 3 07:01:47 2020

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=HYCS.dmp transport_tablespaces=HYCS

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

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:

/u01/app/oracle/product/12.2.1/dbhome_1/rdbms/log/HYCS.dmp

******************************************************************************

Datafiles required for transportable tablespace HYCS:

/u01/app/oradata/orcl/HYCS.DBF

Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Fri Apr 3 07:02:21 2020 elapsed 0 00:00:29

目标库

在目标库中，创建还原表空间的用户（目标库使用的12C的多租户，需要将PDB转换到要还原的PDB下）

先查看数据库有哪些PDB

file:///C:/Users/LENOVO/AppData/Local/Temp/msohtmlclip1/01/clip_image003.png

切换到ORCLPDB下

file:///C:/Users/LENOVO/AppData/Local/Temp/msohtmlclip1/01/clip_image004.png

file:///C:/Users/LENOVO/AppData/Local/Temp/msohtmlclip1/01/clip_image005.png

已切换到ORCLPDB中，然后创建HYCS表空的用户

SQL> create user HYCS identified by HYCS default tablespace HYCS temporary tablespace temp;

用户已创建。

将dmp文件与数据文件，拷贝到windows系统的pdbs对应的目录和数据库泵中

file:///C:/Users/LENOVO/AppData/Local/Temp/msohtmlclip1/01/clip_image007.jpg

file:///C:/Users/LENOVO/AppData/Local/Temp/msohtmlclip1/01/clip_image009.jpg

导入表空间

C:\Users\Administrator>impdp '/@orclpdb as sysdba' directory=DATA_PUMP_DIR dumpfile=HYCS.dmp transport_datafiles='C:\app\Administrator\virtual\oradata\orcl\orclpdb\HYCS1.dbf'

Import: Release12.2.0.1.0 - Production on 星期五 4月 3 18:15:07 2020

连接到: OracleDatabase 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Produc

tion

警告: 连接到容器数据库的根或种子时通常不需要 Oracle Data Pump 操作。

已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TRANSPORTABLE_01"

启动"SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/********@orclpdb AS SYSDBA"directo

ry=DATA_PUMP_DIRdumpfile=HYCS.dmp transport_datafiles='C:\app\Administrator\vir

tual\oradata\orcl\orclpdb\HYCS1.dbf'

处理对象类型TRANSPORTABLE_EXPORT/PLUGTS_BLK

处理对象类型TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

作业"SYS"."SYS_IMPORT_TRANSPORTABLE_01" 已于星期五 4月 3 18:15:14 2020 elapsed

0 00:00:05 成功完成

查看导入的表空间的状态

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

------------------------------------------------------------ ------------------

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

HYCS READ ONLY

将表空间的只读状态修改为读写状态

SQL> alter tablespace HYCS read write;

表空间已更改。