同平台传输表空间实验

来源：互联网发布：剑三藏剑正太脸数据编辑：程序博客网时间：2024/04/30 14:02
--同平台传输表空间实验

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

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

实验：
要求：将PROD1中的TT表空间及数据传输到PROD2中。
过程：
--查看当前库字符集SYS@PROD1> col value for a30SYS@PROD1> select * from nls_database_parameters where parameter like '%CHAR%';PARAMETER       VALUE------------------------------ ------------------------------NLS_NUMERIC_CHARACTERS       .,NLS_CHARACTERSET       AL32UTF8NLS_NCHAR_CONV_EXCP       FALSENLS_NCHAR_CHARACTERSET       AL16UTF16--操作系统和数据库版本SYS@PROD1> ho uname -aLinux ocm1 2.6.18-164.el5PAE #1 SMP Thu Sep 3 02:28:20 EDT 2009 i686 i686 i386 GNU/LinuxSYS@PROD1> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE11.2.0.3.0ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - Production--可直接转换的平台SYS@PROD1> col platform_name for a40SYS@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)     Little12 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.sqlSYS@PROD1> ho cp $ORACLE_HOME/rdbms/admin/utlsampl.sql /tmpSYS@PROD1> ho vi /tmp/utlsampl.sqlSYS@PROD1> conn tt/oracleConnected.TT@PROD1> start /tmp/utlsampl.sqlDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSYS@PROD1> conn tt/oracleConnected.TT@PROD1> select * from tab;TNAME       TABTYPECLUSTERID------------------------------ ------- ----------BONUS       TABLEDEPT       TABLEEMP       TABLESALGRADE       TABLETT@PROD1> select tablespace_name,table_name from user_tables;TABLESPACE_NAME        TABLE_NAME------------------------------ ------------------------------TT       DEPTTT       EMPTT       BONUSTT       SALGRADE--查找要传输的表空间下有哪些用户SYS@PROD1> select distinct owner from dba_tables where tablespace_name='TT';OWNER------------------------------TTSYS@PROD1> select distinct owner from dba_indexes where tablespace_name='TT';OWNER------------------------------TT--目标数据库--检查目标数据库可传输类型，字符集SYS@PROD2> col platform_name for a40SYS@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)     Little12 rows selected.SYS@PROD2> col value for a30SYS@PROD2> select * from nls_database_parameters where parameter like '%CHAR%';PARAMETER       VALUE------------------------------ ------------------------------NLS_NUMERIC_CHARACTERS       .,NLS_CHARACTERSET       AL32UTF8NLS_NCHAR_CONV_EXCP       FALSENLS_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 selectedSYS@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=yExport: Release 11.2.0.3.0 - Production on Tue Jan 10 16:03:34 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in US7ASCII character set and AL16UTF16 NCHAR character setserver uses AL32UTF8 character set (possible charset conversion)Note: table data (rows) will not be exportedAbout 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 exportExport terminated successfully without warnings.--导出并将数据文件拷贝到目标路径下RMAN> report schema;using target database control file instead of recovery catalogReport of database schema for database with db_unique_name PROD1List of Permanent Datafiles===========================File Size(MB) Tablespace           RB segs Datafile Name---- -------- -------------------- ------- ------------------------1    780      SYSTEM               ***     /u01/app/oracle/oradata/PROD1/system01.dbf2    720      SYSAUX               ***     /u01/app/oracle/oradata/PROD1/sysaux01.dbf3    20       LOB_DATA             ***     /u01/app/oracle/oradata/PROD1/lob_data.dbf4    2891     USERS                ***     /u01/app/oracle/oradata/PROD1/users01.dbf5    345      EXAMPLE              ***     /u01/app/oracle/oradata/PROD1/example01.dbf6    50       TEST                 ***     /u01/app/oracle/oradata/PROD1/text01.dbf7    90       REGISTRATION         ***     /u01/app/oracle/oradata/PROD1/registration.dbf8    180      UNDOTBS2             ***     /u01/app/oracle/oradata/PROD1/undotbs02.dbf9    20       SF_DATA              ***     /u01/app/oracle/oradata/PROD1/sf_data.dbf10   35       MSSM                 ***     /u01/app/oracle/oradata/PROD1/mssm.dbf11   76       ASSM                 ***     /u01/app/oracle/oradata/PROD1/assm.dbf12   19       P1                   ***     /u01/app/oracle/oradata/PROD1/PROD1/datafile/o1_mf_p1_d24hxlxk_.dbf13   27       P2                   ***     /u01/app/oracle/oradata/PROD1/PROD1/datafile/o1_mf_p2_d24hxpq0_.dbf14   1200     BIG1                 ***     /u01/app/oracle/oradata/PROD1/PROD1/datafile/o1_mf_big1_d24kc13r_.dbf15   1200     BIG2                 ***     /u01/app/oracle/oradata/PROD1/PROD1/datafile/o1_mf_big2_d24kczft_.dbf16   50       TBS1                 ***     /u01/app/oracle/oradata/PROD1/tbs1.dbf17   10       CATADB               ***     /u01/app/oracle/oradata/PROD1/catadb.dbf18   10       FDA                  ***     /u01/app/oracle/oradata/PROD1/fda01.dbf19   20       TT                   ***     /u01/app/oracle/oradata/PROD1/tt.dbfList of Temporary Files=======================File Size(MB) Tablespace           Maxsize(MB) Tempfile Name---- -------- -------------------- ----------- --------------------1    611      TEMP                 32767       /u01/app/oracle/oradata/PROD1/temp01.dbf2    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-17allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=45 device type=DISKchannel ORA_DISK_1: starting datafile copyinput datafile file number=00019 name=/u01/app/oracle/oradata/PROD1/tt.dbfoutput file name=/u01/app/oracle/oradata/PROD2/tt.dbf tag=TAG20170110T160603 RECID=1 STAMP=932918765channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03Finished backup at 10-JAN-17Starting Control File and SPFILE Autobackup at 10-JAN-17piece handle=/u01/app/oracle/fast_recovery_area/PROD1/autobackup/2017_01_10/o1_mf_s_932918766_d795hh7g_.bkp comment=NONEFinished Control File and SPFILE Autobackup at 10-JAN-17SYS@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=yImport: Release 11.2.0.3.0 - Production on Tue Jan 10 16:09:46 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport file created by EXPORT:V11.02.00 via conventional pathAbout to import transportable tablespace(s) metadata...import done in US7ASCII character set and AL16UTF16 NCHAR character setimport 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 SYSImport terminated successfully without warnings.SYS@PROD2> conn tt/oracle  --迁移成功Connected.TT@PROD2> select * from tab;TNAME       TABTYPECLUSTERID------------------------------ ------- ----------BONUS       TABLEDEPT       TABLEEMP       TABLESALGRADE       TABLE
0 0