oracle 11g 传输表空间（数据迁移）-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

oracle 11g 传输表空间（数据迁移）

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:37 PM

oraclestransmissionConditiondataenvironmentspacemigrate

环境情况 Source 端：操作系统： OracleLinux 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 a40SELECT

环境情况

Source 端：

操作系统： OracleLinux 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('TSET', TRUE);

PL/SQL procedure successfully completed.

--查看自包含验证结果：
SQL> select * from transport_set_violations;

no rows selected
--没有记录说明没有错

5、将表空间TSET设置成read--only

SQL> alter tablespace TSET 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
TSET                           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=TSET transport_full_check=y logfile=TSET.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=TSET transport_full_check=y logfile=TSET.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 TSET:
  /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 TSET.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
TSET                           READ ONLY

7 rows selected.

SQL> alter tablespace TSET read write;

Tablespace altered.

10、验证

SQL> conn target_test/oracle
Connected.

SQL> select * from t1;

        ID NAME
---------- ------------------------------
         1 AAAAA
         2 BBBBB

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

oracle怎么查询所有索引May 13, 2022 pm 05:23 PM

方法：1、利用“select*from user_indexes where table_name=表名”语句查询表中索引；2、利用“select*from all_indexes where table_name=表名”语句查询所有索引。

什么是oracle asmApr 18, 2022 pm 04:16 PM

oracle asm指的是“自动存储管理”，是一种卷管理器，可自动管理磁盘组并提供有效的数据冗余功能；它是做为单独的Oracle实例实施和部署。asm的优势：1、配置简单、可最大化推动数据库合并的存储资源利用；2、支持BIGFILE文件等。

oracle全角怎么转半角May 13, 2022 pm 03:21 PM

在oracle中，可以利用“TO_SINGLE_BYTE(String)”将全角转换为半角；“TO_SINGLE_BYTE”函数可以将参数中所有多字节字符都替换为等价的单字节字符，只有当数据库字符集同时包含多字节和单字节字符的时候有效。

Oracle怎么查询端口号May 13, 2022 am 10:10 AM

在Oracle中，可利用lsnrctl命令查询端口号，该命令是Oracle的监听命令；在启动、关闭或重启oracle监听器之前可使用该命令检查oracle监听器的状态，语法为“lsnrctl status”，结果PORT后的内容就是端口号。

oracle怎么查询数据类型May 13, 2022 pm 04:19 PM

在oracle中，可以利用“select ... From all_tab_columns where table_name=upper('表名') AND owner=upper('数据库登录用户名');”语句查询数据库表的数据类型。

oracle怎么删除sequenceMay 13, 2022 pm 03:35 PM

在oracle中，可以利用“drop sequence sequence名”来删除sequence；sequence是自动增加数字序列的意思，也就是序列号，序列号自动增加不能重置，因此需要利用drop sequence语句来删除序列。

oracle查询怎么不区分大小写May 10, 2022 pm 05:45 PM

方法：1、利用“LOWER(字段值)”将字段转为小写，或者利用“UPPER(字段值)”将字段转为大写；2、利用“REGEXP_LIKE(字符串,正则表达式,'i')”，当参数设置为“i”时，说明进行匹配不区分大小写。

Oracle怎么修改sessionMay 13, 2022 pm 05:06 PM

方法：1、利用“alter system set sessions=修改后的数值 scope=spfile”语句修改session参数；2、修改参数之后利用“shutdown immediate – startup”语句重启服务器即可生效。

See all articles

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)

2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

How Long Does It Take To Beat Split Fiction?

1 months agoByDDD

R.E.P.O. Best Graphic Settings

2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Assassin's Creed Shadows: Seashell Riddle Solution

1 weeks agoByDDD

R.E.P.O. How to Fix Audio if You Can't Hear Anyone

2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Dreamweaver Mac version

Visual web development tools

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

SublimeText3 Chinese version

Chinese version, very easy to use

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

Hot Topics

Where is the login entrance for gmail email?

7404

1630

1358

1268

1218