同平台传输表空间实验

最新推荐文章于 2021-09-25 20:34:03 发布

blockheading

最新推荐文章于 2021-09-25 20:34:03 发布

阅读量272

点赞数

分类专栏： Oracle import export 文章标签： oracle 11g

本文链接：https://blog.csdn.net/u013169075/article/details/54318025

版权

Oracle import export 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

--同平台传输表空间实验

前提：
1.数据库字符集一致
2.版本由低到高
3.平台要一致

步骤：
1.查看本机字符集
2.查看操作系统与数据库版本
3.查看本机可直接传输的平台（此实验不需要进行平台处理）
4.查看要传输表空间下的用户
5.在目标机器查找用户不存在则创建
6.查看目标机器字符集、操作系统和数据库版本
7.导出TT表空间的数据字典
8.导出并将数据文件拷贝到目标路径下
9.导入的目标库

实验：
要求：将PROD1中的TT表空间及数据传输到PROD2中。
过程：

--查看当前库字符集
SYS@PROD1> col value for a30
SYS@PROD1> select * from nls_database_parameters where parameter like '%CHAR%';

PARAMETER		       VALUE
------------------------------ ------------------------------
NLS_NUMERIC_CHARACTERS	       .,
NLS_CHARACTERSET	       AL32UTF8
NLS_NCHAR_CONV_EXCP	       FALSE
NLS_NCHAR_CHARACTERSET	       AL16UTF16

--操作系统和数据库版本
SYS@PROD1> ho uname -a
Linux ocm1 2.6.18-164.el5PAE #1 SMP Thu Sep 3 02:28:20 EDT 2009 i686 i686 i386 GNU/Linux

SYS@PROD1> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE	11.2.0.3.0	Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production	

--可直接转换的平台
SYS@PROD1> col platform_name for a40
SYS@PROD1> select * from v$db_transportable_platform;

PLATFORM_ID PLATFORM_NAME			     ENDIAN_FORMAT
----------- ---------------------------------------- --------------
	  7 Microsoft Windows IA (32-bit)	     Little
	 10 Linux IA (32-bit)			     Little
	  5 HP Tru64 UNIX			     Little
	 11 Linux IA (64-bit)			     Little
	 15 HP Open VMS 			     Little
	  8 Microsoft Windows IA (64-bit)	     Little
	 13 Linux x86 64-bit			     Little
	 12 Microsoft Windows x86 64-bit	     Little
	 17 Solaris Operating System (x86)	     Little
	 19 HP IA Open VMS			     Little
	 20 Solaris Operating System (x86-64)	     Little
	 21 Apple Mac OS (x86-64)		     Little

12 rows selected.

--创建实验环境TT用户及表空间
SYS@PROD1> create tablespace tt datafile '/u01/app/oracle/oradata/PROD1/tt.dbf' size 20m;

Tablespace created.

SYS@PROD1> create user tt identified by oracle default tablespace tt;

User created.

SYS@PROD1> grant connect,resource to tt;

Grant succeeded.

SYS@PROD1> ho ls $ORACLE_HOME/rdbms/admin/utlsampl.sql
/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/utlsampl.sql

SYS@PROD1> ho cp $ORACLE_HOME/rdbms/admin/utlsampl.sql /tmp

SYS@PROD1> ho vi /tmp/utlsampl.sql

SYS@PROD1> conn tt/oracle
Connected.

TT@PROD1> start /tmp/utlsampl.sql
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@PROD1> conn tt/oracle
Connected.
TT@PROD1> select * from tab;

TNAME			       TABTYPE	CLUSTERID
------------------------------ ------- ----------
BONUS			       TABLE
DEPT			       TABLE
EMP			       TABLE
SALGRADE		       TABLE

TT@PROD1> select tablespace_name,table_name from user_tables;

TABLESPACE_NAME 	       TABLE_NAME
------------------------------ ------------------------------
TT			       DEPT
TT			       EMP
TT			       BONUS
TT			       SALGRADE

--查找要传输的表空间下有哪些用户
SYS@PROD1> select distinct owner from dba_tables where tablespace_name='TT';

OWNER
------------------------------
TT

SYS@PROD1> select distinct owner from dba_indexes where tablespace_name='TT';

OWNER
------------------------------
TT

--目标数据库

--检查目标数据库可传输类型，字符集
SYS@PROD2> col platform_name for a40
SYS@PROD2> select * from v$db_transportable_platform;

PLATFORM_ID PLATFORM_NAME			     ENDIAN_FORMAT
----------- ---------------------------------------- --------------
	  7 Microsoft Windows IA (32-bit)	     Little
	 10 Linux IA (32-bit)			     Little
	  5 HP Tru64 UNIX			     Little
	 11 Linux IA (64-bit)			     Little
	 15 HP Open VMS 			     Little
	  8 Microsoft Windows IA (64-bit)	     Little
	 13 Linux x86 64-bit			     Little
	 12 Microsoft Windows x86 64-bit	     Little
	 17 Solaris Operating System (x86)	     Little
	 19 HP IA Open VMS			     Little
	 20 Solaris Operating System (x86-64)	     Little
	 21 Apple Mac OS (x86-64)		     Little

12 rows selected.

SYS@PROD2> col value for a30
SYS@PROD2> select * from nls_database_parameters where parameter like '%CHAR%';

PARAMETER		       VALUE
------------------------------ ------------------------------
NLS_NUMERIC_CHARACTERS	       .,
NLS_CHARACTERSET	       AL32UTF8
NLS_NCHAR_CONV_EXCP	       FALSE
NLS_NCHAR_CHARACTERSET	       AL16UTF16

--查看目标数据库有无同名的表空间，如果有需要改名。
SYS@PROD2> select tablespace_name from dba_tablespaces where tablespace_name='TT';

no rows selected

--查看有无存在用户，不存在需要创建
SYS@PROD2> select username from all_users where username like 'TT';

no rows selected

SYS@PROD2> grant dba to tt identified by oracle;

Grant succeeded.

--导出TT表空间的数据字典
SYS@PROD1> alter tablespace tt read only;

Tablespace altered.

[oracle@ocm1 ~]$ exp \'/ as sysdba\' tablespaces=tt file=tt.dmp transport_tablespace=y

Export: Release 11.2.0.3.0 - Production on Tue Jan 10 16:03:34 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TT ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                           DEPT
. . exporting table                            EMP
. . exporting table                          BONUS
. . exporting table                       SALGRADE
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

--导出并将数据文件拷贝到目标路径下
RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name PROD1

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    780      SYSTEM               ***     /u01/app/oracle/oradata/PROD1/system01.dbf
2    720      SYSAUX               ***     /u01/app/oracle/oradata/PROD1/sysaux01.dbf
3    20       LOB_DATA             ***     /u01/app/oracle/oradata/PROD1/lob_data.dbf
4    2891     USERS                ***     /u01/app/oracle/oradata/PROD1/users01.dbf
5    345      EXAMPLE              ***     /u01/app/oracle/oradata/PROD1/example01.dbf
6    50       TEST                 ***     /u01/app/oracle/oradata/PROD1/text01.dbf
7    90       REGISTRATION         ***     /u01/app/oracle/oradata/PROD1/registration.dbf
8    180      UNDOTBS2             ***     /u01/app/oracle/oradata/PROD1/undotbs02.dbf
9    20       SF_DATA              ***     /u01/app/oracle/oradata/PROD1/sf_data.dbf
10   35       MSSM                 ***     /u01/app/oracle/oradata/PROD1/mssm.dbf
11   76       ASSM                 ***     /u01/app/oracle/oradata/PROD1/assm.dbf
12   19       P1                   ***     /u01/app/oracle/oradata/PROD1/PROD1/datafile/o1_mf_p1_d24hxlxk_.dbf
13   27       P2                   ***     /u01/app/oracle/oradata/PROD1/PROD1/datafile/o1_mf_p2_d24hxpq0_.dbf
14   1200     BIG1                 ***     /u01/app/oracle/oradata/PROD1/PROD1/datafile/o1_mf_big1_d24kc13r_.dbf
15   1200     BIG2                 ***     /u01/app/oracle/oradata/PROD1/PROD1/datafile/o1_mf_big2_d24kczft_.dbf
16   50       TBS1                 ***     /u01/app/oracle/oradata/PROD1/tbs1.dbf
17   10       CATADB               ***     /u01/app/oracle/oradata/PROD1/catadb.dbf
18   10       FDA                  ***     /u01/app/oracle/oradata/PROD1/fda01.dbf
19   20       TT                   ***     /u01/app/oracle/oradata/PROD1/tt.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    611      TEMP                 32767       /u01/app/oracle/oradata/PROD1/temp01.dbf
2    608      TEMP                 32767       /u01/app/oracle/oradata/PROD1/temp02.dbf

找到TT的file号码和路径。

RMAN> backup as copy datafile 19 format '/u01/app/oracle/oradata/PROD2/tt.dbf';

Starting backup at 10-JAN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00019 name=/u01/app/oracle/oradata/PROD1/tt.dbf
output file name=/u01/app/oracle/oradata/PROD2/tt.dbf tag=TAG20170110T160603 RECID=1 STAMP=932918765
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 10-JAN-17

Starting Control File and SPFILE Autobackup at 10-JAN-17
piece handle=/u01/app/oracle/fast_recovery_area/PROD1/autobackup/2017_01_10/o1_mf_s_932918766_d795hh7g_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 10-JAN-17

SYS@PROD1> alter tablespace tt read write;

Tablespace altered.

--导入到目标库
[oracle@ocm1 ~]$ export ORACLE_SID=PROD2
[oracle@ocm1 ~]$ imp \'/ as sysdba\' datafiles=/u01/app/oracle/oradata/PROD2/tt.dbf file=tt.dmp transport_tablespace=y

Import: Release 11.2.0.3.0 - Production on Tue Jan 10 16:09:46 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing TT's objects into TT
. . importing table                         "DEPT"
. . importing table                          "EMP"
. . importing table                        "BONUS"
. . importing table                     "SALGRADE"
. importing SYS's objects into SYS
Import terminated successfully without warnings.

SYS@PROD2> conn tt/oracle  --迁移成功
Connected.
TT@PROD2> select * from tab;

TNAME			       TABTYPE	CLUSTERID
------------------------------ ------- ----------
BONUS			       TABLE
DEPT			       TABLE
EMP			       TABLE
SALGRADE		       TABLE