| 查看源库的表空间信息 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 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. 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 R 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 R 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 R2 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 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. 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 Copyright (c) 1982,2017, Oracle and/or its affiliates. Allrights reserved. 连接到: 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; 表空间已更改。 SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------------------------------------ ------------------ SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE HYCS ONLINE | 
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2020-4-5 16:29:11
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对