oracle 数据在users表空间 用tts怎么传

发布网友 发布时间：2022-05-02 23:42

我来回答

共2个回答

懂视网 时间：2022-05-03 04:03

6.2 64位 endianness格式: little 数据库版本：11.2.0.3   Target 端： 操作系统：OracleLinux 6.2 64位 endianness 格式: little 数据库版本：11.2.0.3 1、查看操作系统endianness格式 col platform_name for a40 SELECT * FROM V$TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_ID; 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. --分别查看 source 端 和target端操作系统endianness格式 --source 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 x86 64-bit Little --target 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 x86 64-bit Little 2、在source端创建测试表空间 select tablespace_name, status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE UNDOTBS1 ONLINE SYSAUX ONLINE TEMPTS1 ONLINE USERS ONLINE OUTLN ONLINE 6 rows selected. select file_name from dba_data_files; FILE_NAME ------------------------------------------------ /u01/app/oracle/oradata/normal/system01.dbf /u01/app/oracle/oradata/normal/undotbs01.dbf /u01/app/oracle/oradata/normal/sysaux01.dbf /u01/app/oracle/oradata/normal/users01.dbf /u01/app/oracle/oradata/normal/undotbs02.dbf /u01/app/oracle/oradata/normal/system02.dbf /u01/app/oracle/oradata/normal/outln01.dbf 7 rows selected. --创建表空间创建表空间 tset create tablespace tset datafile ‘/u01/app/oracle/oradata/normal/test01.dbf‘ size 50M; Tablespace created. --创建用户source_test，并指定表空间 --在source端 create user source_test identified by oracle default tablespace tset temporary tablespace TEMPTS1; User created. grant connect,resource to source_test; Grant succeeded. --在target端（暂时只先创建用户） create user target_test identified by oracle temporary tablespace TEMPTS1; User created. grant connect,resource to target_test; Grant succeeded. --创建测试表 SQL> conn source_test/oracle Connected. SQL> create table t1(id number, name varchar2(30)); Table created. SQL> insert into t1 values(1, ‘AAAAA‘); 1 row created. SQL> insert into t1 values(2, ‘BBBBB‘); 1 row created. SQL> commit; Commit complete. select * from t1; ID NAME ---------- ------------------------------ 1 AAAAA 2 BBBBB 3、在source端和target端创建 backup 的目录 [oracle@normal ~]$ mkdir -p /u01/backup [oracle@normal ~]$ ls -l /u01 total 24 drwxr-xr-x 3 oracle oinstall 4096 Jul 28 12:31 app drwxr-xr-x 2 oracle oinstall 4096 Sep 14 16:21 backup SQL> show user USER is "SYS" SQL> create directory backup as ‘/u01/backup‘; Directory created. SQL> col owner format a5 SQL> col directory_name format a25 SQL> col DIRECTORY_PATH format a50 SQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH ----- ------------------------- -------------------------------------------------- SYS BACKUP /u01/backup SYS OUTLN_DIR /home/oracle SYS DATA_PUMP_DIR /u01/app/oracle/product/11.2.0/db_1/rdbms/log/ SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/db_1/ccr/state SQL> GRANT read, write ON DIRECTORY backup TO source_test; Grant succeeded. --在target端 [oracle@test ~]$ mkdir -p /u01/backup [oracle@test ~]$ ls -l /u01 total 24 drwxr-xr-x 3 oracle oinstall 4096 Aug 28 09:09 app drwxr-xr-x 2 oracle oinstall 4096 Sep 14 16:40 backup SQL> show user USER is "SYS" SQL> create directory backup as ‘/u01/backup‘; Directory created. SQL> col owner format a5 SQL> col directory_name format a25 SQL> col DIRECTORY_PATH format a50 SQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH ----- ------------------------- -------------------------------------------------- SYS BACKUP /u01/backup SYS OUTLN_DIR /home/oracle SYS DATA_PUMP_DIR /u01/app/oracle/product/11.2.0/db_1/rdbms/log/ SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/db_1/ccr/state SQL> GRANT read, write ON DIRECTORY backup TO target_test; Grant succeeded. 4、检查表空间自包含（就是改表空间里的数据没有和其他表空间数据有关联，如果有关联会报错） SQL> execute dbms_tts.transport_set_check(‘TEST‘, TRUE); PL/SQL procedure successfully completed. --查看自包含验证结果： SQL> select * from transport_set_violations; no rows selected --没有记录说明没有错 5、将表空间TEST设置成read­-only SQL> alter tablespace TEST read only; Tablespace altered. select tablespace_name, status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE UNDOTBS1 ONLINE SYSAUX ONLINE TEMPTS1 ONLINE USERS ONLINE OUTLN ONLINE TEST READ ONLY 7 rows selected. 6、生成：Transportable Tablespace Set Transportable Tablespace Set有两部分： 1.expdp 导出的表空间的metadata 2.还有就是表空间对应的数据文件 --expdp 导出的表空间的metadata [oracle@normal normal]$ pwd /u01/app/oracle/oradata/normal [oracle@normal normal]$ ll total 2294664 -rw-r----- 1 oracle oinstall 9781248 Sep 14 16:46 control01.ctl drwx------ 2 oracle oinstall 16384 Aug 22 12:44 lost+found -rw-r----- 1 oracle oinstall 20979712 Sep 14 15:52 outln01.dbf -rw-r----- 1 oracle oinstall 52429312 Sep 14 16:45 redo01a.log -rw-r----- 1 oracle oinstall 52429312 Sep 14 16:45 redo01b.log -rw-r----- 1 oracle oinstall 52429312 Sep 14 15:52 redo02a.log -rw-r----- 1 oracle oinstall 52429312 Sep 14 15:52 redo02b.log -rw-r----- 1 oracle oinstall 52429312 Sep 14 15:52 redo03a.log -rw-r----- 1 oracle oinstall 52429312 Sep 14 15:52 redo03b.log -rw-r--r-- 1 oracle oinstall 22633 Aug 22 17:00 su.lst -rw-r----- 1 oracle oinstall 340795392 Sep 14 16:40 sysaux01.dbf -rw-r----- 1 oracle oinstall 340795392 Sep 14 16:43 system01.dbf -rw-r----- 1 oracle oinstall 314580992 Sep 14 16:43 system02.dbf -rw-r----- 1 oracle oinstall 20979712 Sep 14 15:53 temp01.dbf -rw-r----- 1 oracle oinstall 52436992 Sep 14 15:53 temp02.dbf -rw-r----- 1 oracle oinstall 52436992 Sep 14 16:31 test01.dbf -rw-r----- 1 oracle oinstall 209723392 Sep 14 16:43 undotbs01.dbf -rw-r----- 1 oracle oinstall 209723392 Sep 14 16:40 undotbs02.dbf -rw-r----- 1 oracle oinstall 524296192 Sep 14 15:52 users01.dbf [oracle@normal normal]$ expdp dumpfile=test01.dmp directory=backup transport_tablespaces=TEST transport_full_check=y logfile=TEST.log Export: Release 11.2.0.3.0 - Production on Sun Sep 14 16:54:30 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": /********/ AS SYSDBA dumpfile=test01.dmp directory=backup transport_tablespaces=TEST transport_full_check=y logfile=TEST.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE 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/backup/test01.dmp ****************************************************************************** Datafiles required for transportable tablespace TEST: /u01/app/oracle/oradata/normal/test01.dbf Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 16:55:13 [oracle@normal normal]$ ls -l /u01/backup/ total 80 -rw-r----- 1 oracle oinstall 77824 Sep 14 16:55 test01.dmp -rw-r--r-- 1 oracle oinstall 1160 Sep 14 16:55 TEST.log 7、将Transportable Tablespace set 传送到Target端 1）将表空间test 对应的数据文件copy到Target 对应的ORADATA目录下。 2）将expdp 导出的表空间metadta 数据copy 到Target 端的backup 目录下 --将表空间test 对应的数据文件copy到Target 对应的ORADATA目录下。 [oracle@normal normal]$ scp /u01/backup/test01.dmp 192.168.137.12:/u01/backup oracle@192.168.137.12 s password: test01.dmp 100% 76KB 76.0KB/s 00:00 --将expdp 导出的表空间metadta 数据copy 到Target 端的backup 目录下 [oracle@normal normal]$ scp test01.dbf 192.168.137.12:/u01/app/oracle/oradata/normal/test01.dbf --要提前将数据文件拷贝到目标端的某个位置下，这里很关键 oracle@192.168.137.12 s password: test01.dbf 100% 50MB 16.7MB/s 00:03 --在target端查看文件是否已经传输 [oracle@test ~]$ ll /u01/backup/ total 76 -rw-r----- 1 oracle oinstall 77824 Sep 14 17:03 test01.dmp [oracle@test ~]$ ll $ORACLE_BASE/oradata/normal/test01.dbf -rw-r----- 1 oracle oinstall 52436992 Sep 14 17:04 /u01/app/oracle/oradata/normal/test01.dbf 8、在Target 系统上Import 表空间的metadata(使用target_test用户，需要用到remap_schema) [oracle@test ~]$ impdp directory=backup dumpfile=test01.dmp transport_datafiles=/u01/app/oracle/oradata/normal/test01.dbf remap_schema=source_test:target_test logfile=test.log Import: Release 11.2.0.3.0 - Production on Sun Sep 14 17:09:25 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": /********/ AS SYSDBA directory=backup dumpfile=test01.dmp transport_datafiles=/u01/app/oracle/oradata/normal/test01.dbf remap_schema=source_test:target_test logfile=test.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 17:09:55 9、查看并修改表空间状态 select tablespace_name, status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE UNDOTBS1 ONLINE SYSAUX ONLINE TEMPTS1 ONLINE USERS ONLINE OUTLN ONLINE TEST READ ONLY 7 rows selected. SQL> alter tablespace TEST read write; Tablespace altered. 10、验证 SQL> conn target_test/oracle Connected. SQL> select * from t1; ID NAME ---------- ------------------------------ 1 AAAAA

 

参考自文章：https://blog.csdn.net/ch7543658/article/details/39271135

oracle的tts迁移

标签：grant   版本   bit   64bit   release   cte   ibm   sdn   检查   

热心网友 时间：2022-05-03 01:11

oracle 数据在users表空间 用tts怎么传
oracle10g默认用户的default tablespace是users，所以不能删除，先将database级别的default tablespace改成其他的tablespace就可以删除了： [php]SYS@ning>alter database default tablespace test;Database altered.--[/php]