ITPub博客

首页 > 数据库 > Oracle > oracle 传输表空间一例

oracle 传输表空间一例

原创 Oracle 作者:lhrbest 时间:2014-12-21 21:00:29 0 删除 编辑

文档结构图:

image

1  场景描述

源平台为:rhel6.5 + oracle 11.2.0.3.0 + asm 64

目标平台:windows xp 32系统 + oracle 11.2.0.1.0

要实现将自定义的应用程序表空间app1tbs,app2tbs,idxtbs从源平台传递到目标平台

 

2  环境准备

2.1  在源库上创建3个用户应用的表空间

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 21 17:00:38 2014

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

 

SQL> select * from v$version;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit 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

 

SQL> create tablespace app1tbs datafile '+DATA' size 50m;

 

Tablespace created.

 

SQL> create tablespace app2tbs datafile '+DATA' size 50m;

 

Tablespace created.

 

SQL> create tablespace idxtbs datafile '+DATA' size 50m;

 

Tablespace created.

 

SQL> set line 9999 pagesize 9999

SQL> SELECT   a.NAME,  b.NAME FROM v$tablespace a , v$datafile b WHERE a.TS#=b.TS#  ;

 

NAME        NAME

------------------------------ --------------------------------------------------------------------------------------------------------

SYSTEM        +DATA/orclasm/datafile/system.256.850260145

SYSAUX        +DATA/orclasm/datafile/sysaux.257.850260145

UNDOTBS1        +DATA/orclasm/datafile/undotbs1.258.851526539

USERS        +DATA/orclasm/datafile/users.259.850260147

EXAMPLE        +DATA/orclasm/datafile/example.265.850260295

UNDOTBS2        +DATA/orclasm/datafile/undotbs2.267.851204361

TBS_RC        +DATA/orclasm/datafile/tbs_rc.268.852116523

TS_LHR        +DATA/orclasm/datafile/ts_lhr.269.852632495

ENCRYPTED_TS    +DATA/orclasm/datafile/encrypted_ts.272.854650889

GOLDENGATE        +DATA/orclasm/datafile/goldengate.273.862829891

APP1TBS        +DATA/orclasm/datafile/app1tbs.274.866911939

APP2TBS        +DATA/orclasm/datafile/app2tbs.275.866912075

IDXTBS        +DATA/orclasm/datafile/idxtbs.276.866912133

 

13 rows selected.

 

SQL>

 

2.2  在相应的表空间创建表和索引

SQL> create table scott.app1_tab tablespace app1tbs as select * from scott.emp;

 

Table created.

 

SQL> create table scott.app2_tab tablespace app2tbs as select * from scott.dept;

 

Table created.

 

SQL> create index scott.idx_emp_ename on scott.app1_tab(ename) tablespace idxtbs;

 

Index created.

 

SQL> create index scott.idx_dept_dname on scott.app2_tab(dname) tablespace idxtbs;

 

Index created.

 

SQL>

 

 

3  判断平台支持并确定字节序

如果传输表空间集到不同的平台,则要确定对于源和目标平台这种跨平台表空间被支持,也要确定每个平台的字节序,如果平台具有相同的字节序,则不需要进行转化,否则必须做一个表空间集转化,在源端或目标端。

3.1  在源平台查询

SQL> col platform_name for a40

SQL> select d.platform_name,tp.endian_format from v$transportable_platform tp,v$database d

  2  where tp.platform_name=d.platform_name;

 

PLATFORM_NAME ENDIAN_FORMAT

---------------------------------------- --------------

Linux x86 64-bit Little

 

SQL>

 

 

结论:当前的系统平台支持跨平台表空间传输(因为上面的查询有记录返回)

 

3.2  在目标平台查询

 

SQL> col platform_name for a40

SQL> select d.platform_name,tp.endian_format from v$transportable_platform tp,v$database d

  2  where tp.platform_name=d.platform_name;

 

PLATFORM_NAME                            ENDIAN_FORMAT

---------------------------------------- --------------

Microsoft Windows IA (32-bit)            Little

 

 

结论: 当前的windows 平台支持跨平台的表空间传输

源平台和目标平台的Endian_format 相同(均为Little),不需要进行表空间集转换

 

4  选择自包含的表空间集(目前要传输app1tbsidxtbs2个表空间)

4.1  进行检查

Indicates whether a full or partial dependency check is required. If TRUE, treats all IN and OUT pointers(dependencies) and captures them as violations if they are not self-contained in the transportable set.

SQL> execute dbms_tts.transport_set_check('app1tbs,idxtbs',true);

 

PL/SQL procedure successfully completed.

 

 

4.2  查看检查结果

SQL> col violations for a70

SQL> select * from transport_set_violations;

 

VIOLATIONS

------------------------------------------------------------------------------------------------------------

ORA-39907: Index scott.IDX_DEPT_DNAME in tablespace IDXTBS points to table scott.APP2_TAB in tablespace APP2TBS.

 

 

SQL>

结论: 在idxtbs表空间中IDX_DEPT_DNAME索引指向了表空间集外的SYS.APP2_TAB表,所以这里选择app1tabs,app2tabs,idxtbs作为新的表空间集再次进行检查

 

SQL> execute dbms_tts.transport_set_check('app1tbs,app2tbs,idxtbs',true);

 

PL/SQL procedure successfully completed.

 

SQL> select * from transport_set_violations;

 

no rows selected

 

SQL>

 

结论: 此时这个表空间集已经不在违背自包含的条件,可以确定为一个可传输表空间集

 

5  产生可传输表空间集

5.1  使自包含的表空间集中的所有表空间变为只读状态

SQL> alter tablespace app1tbs read only;

 

Tablespace altered.

 

SQL> alter tablespace app2tbs read only;

 

Tablespace altered.

 

SQL> alter tablespace idxtbs read only;

 

Tablespace altered.

 

 

5.2  使用数据泵导出工具,导出要传输的各个表空间的元数据

5.2.1  确定导出目录

SQL> set line 9999

SQL> col directory_name for a25

SQL> col directory_path for a100

SQL> select directory_name,directory_path from dba_directories;

 

DIRECTORY_NAME   DIRECTORY_PATH

------------------------- ----------------------------------------------------------------------------------------------------

REPDIR   /oradata06/repdir

DIR_ALERT_LHR   /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace

SUBDIR   /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep

SS_OE_XMLDIR   /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/

DIR_ALERT   /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace

DIR_ALERT_XML_LHR   /u01/app/oracle/diag/rdbms/orclasm/orclasm/alert

LOG_FILE_DIR   /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/

MEDIA_DIR   /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/

XMLDIR   /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml

DATA_FILE_DIR   /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/

DATA_PUMP_DIR   /u01/app/oracle/admin/orclasm/dpdump/

 

DIRECTORY_NAME   DIRECTORY_PATH

------------------------- ----------------------------------------------------------------------------------------------------

ORACLE_OCM_CONFIG_DIR   /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state

 

12 rows selected.

 

 

5.2.2  开始导出

[oracle@rhel6_lhr ~]$ env | grep ORACLE

ORACLE_UNQNAME=orclasm

ORACLE_SID=orclasm

ORACLE_HOSTNAME=192.168.59.130

ORACLE_BASE=/u01/app/oracle

ORACLE_ALERT=/u01/app/oracle/diag/rdbms/orclasm/orclasm/trace

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

[oracle@rhel6_lhr ~]$ expdp system dumpfile=expdat.dmp directory=DATA_PUMP_DIR transport_tablespaces=app1tbs,app2tbs,idxtbs logfile=tts_export.log

 

Export: Release 11.2.0.3.0 - Production on Sun Dec 21 17:48:20 2014

 

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

Password:

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=expdat.dmp directory=DATA_PUMP_DIR transport_tablespaces=app1tbs,app2tbs,idxtbs logfile=tts_export.log

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:

  /u01/app/oracle/admin/orclasm/dpdump/expdat.dmp

******************************************************************************

Datafiles required for transportable tablespace APP1TBS:

  +DATA/orclasm/datafile/app1tbs.274.866911939

Datafiles required for transportable tablespace APP2TBS:

  +DATA/orclasm/datafile/app2tbs.275.866912075

Datafiles required for transportable tablespace IDXTBS:

  +DATA/orclasm/datafile/idxtbs.276.866912133

Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 17:49:47

 

[oracle@rhel6_lhr ~]$

 

查看文件:

[root@rhel6_lhr ~]# cd /u01/app/oracle/admin/orclasm/dpdump/

[root@rhel6_lhr dpdump]# ll

total 108

-rw-r----- 1 oracle asmadmin 106496 Dec 21 17:49 expdat.dmp

-rw-r--r-- 1 oracle asmadmin   1499 Dec 21 17:49 tts_export.log

[root@rhel6_lhr dpdump]#

 

告警日志可以看到:

Sun Dec 21 17:48:50 2014

DM00 started with pid=45, OS id=13188, job SYSTEM.SYS_EXPORT_TRANSPORTABLE_01

Sun Dec 21 17:48:56 2014

DW00 started with pid=46, OS id=13190, wid=1, job SYSTEM.SYS_EXPORT_TRANSPORTABLE_01

Sun Dec 21 17:49:15 2014

XDB installed.

XDB initialized.

 

5.3  生成数据文件

[root@rhel6_lhr ~]# su - grid

[grid@rhel6_lhr ~]$ asmcmd

ASMCMD> cd  +DATA/orclasm/datafile/

ASMCMD> ls

APP1TBS.274.866911939

APP2TBS.275.866912075

ENCRYPTED_TS.272.854650889

EXAMPLE.265.850260295

GOLDENGATE.273.862829891

IDXTBS.276.866912133

SYSAUX.257.850260145

SYSTEM.256.850260145

TBS_RC.268.852116523

TS_LHR.269.852632495

UNDOTBS1.258.851526539

UNDOTBS2.267.851204361

USERS.259.850260147

example.265.850260295_bk

ASMCMD> cp APP1TBS.274.866911939 /u01/app/oracle/admin/orclasm/dpdump

copying +DATA/orclasm/datafile/APP1TBS.274.866911939 -> /u01/app/oracle/admin/orclasm/dpdump/APP1TBS.274.866911939

ASMCMD-8016: copy source->'+DATA/orclasm/datafile/APP1TBS.274.866911939' and target->'/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.274.866911939' failed

ORA-19505: failed to identify file "/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.274.866911939"

ORA-27040: file create error, unable to create file

Linux-x86_64 Error: 13: Permission denied

Additional information: 1

ORA-15120: ASM file name '/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.274.866911939' does not begin with the ASM prefix character

ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 413

ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)

无权限,暂时拷贝到grid目录下:

ASMCMD> cp APP1TBS.274.866911939 /home/grid

copying +DATA/orclasm/datafile/APP1TBS.274.866911939 -> /home/grid/APP1TBS.274.866911939

ASMCMD> cp APP2TBS.275.866912075 /home/grid

copying +DATA/orclasm/datafile/APP2TBS.275.866912075 -> /home/grid/APP2TBS.275.866912075

ASMCMD> cp IDXTBS.276.866912133 /home/grid

copying +DATA/orclasm/datafile/IDXTBS.276.866912133 -> /home/grid/IDXTBS.276.866912133

ASMCMD>

 

 

然后再拷贝到一个目录下:

[root@rhel6_lhr dpdump]# ll

total 108

-rw-r----- 1 oracle asmadmin 106496 Dec 21 17:49 expdat.dmp

-rw-r--r-- 1 oracle asmadmin   1499 Dec 21 17:49 tts_export.log

[root@rhel6_lhr dpdump]# cp /home/grid/APP1TBS.274.866911939 /u01/app/oracle/admin/orclasm/dpdump/

[root@rhel6_lhr dpdump]# cp /home/grid/APP2TBS.275.866912075 /u01/app/oracle/admin/orclasm/dpdump/

[root@rhel6_lhr dpdump]# cp /home/grid/IDXTBS.276.866912133 /u01/app/oracle/admin/orclasm/dpdump/

[root@rhel6_lhr dpdump]# ll

total 153732

-rw-r----- 1 root   root     52436992 Dec 21 18:05 APP1TBS.274.866911939

-rw-r----- 1 root   root     52436992 Dec 21 18:06 APP2TBS.275.866912075

-rw-r----- 1 oracle asmadmin   106496 Dec 21 17:49 expdat.dmp

-rw-r----- 1 root   root     52436992 Dec 21 18:06 IDXTBS.276.866912133

-rw-r--r-- 1 oracle asmadmin     1499 Dec 21 17:49 tts_export.log

[root@rhel6_lhr dpdump]#

 

 

 

6  还原源库中的表空间为读/写模式

SQL> alter tablespace app1tbs read write;

 

Tablespace altered.

 

SQL> alter tablespace app2tbs read write;

 

Tablespace altered.

 

SQL> alter tablespace idxtbs read write;

 

Tablespace altered.

 

SQL>

 

7  传输文件

这里需要传输转储元文件和数据文件到目标库

 

7.1  传输转储元文件到目标库

 

wpsFEA0.tmp 

 

7.2  查看目标库数据文件位置和目录

SQL> select name from v$datafile;

 

NAME

--------------------------------------------------------------------------------

F:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF

F:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF

F:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF

F:\APP\ORACLE\ORADATA\ORCL\USERS01.DBF

F:\APP\ORACLE\ORADATA\ORCL\RMAN.DBF

 

SQL> set line 9999

SQL> col directory_name for a25

SQL> col directory_path for a100

SQL> select directory_name,directory_path from dba_directories;

 

DIRECTORY_NAME            DIRECTORY_PATH

------------------------- -----------------------------------------------------

XMLDIR                    c:\ade\aime_dadvfm0254\oracle\rdbms\xml

DATA_PUMP_DIR             F:\app\oracle\admin\orcl\dpdump\

ORACLE_OCM_CONFIG_DIR     F:\app\oracle\product\ccr\state

 

SQL>

 

7.3  拷贝文件到目标库相应位置

 

将表空间文件拷贝到F:\APP\ORACLE\ORADATA\ORCL\下,将expdat.dmp 文件拷贝到F:\app\oracle\admin\orcl\dpdump\ 下,如下:

wpsFEA1.tmp 

wpsFEB2.tmp 

 

8  开始导入

8.1  生成parfile文件

文件内容如下:

 

DUMPFILE=expdat.dmp

DIRECTORY=DATA_PUMP_DIR

TRANSPORT_DATAFILES=

F:\app\oracle\oradata\orcl\APP1TBS.274.866911939,

F:\app\oracle\oradata\orcl\APP2TBS.275.866912075,

F:\app\oracle\oradata\orcl\IDXTBS.276.866912133

LOGFILE=tts_import.log

 

 

 

8.2  开始导入

C:\Documents and Settings\Administrator>set oracle_sid=orcl

 

C:\Documents and Settings\Administrator>impdp sys parfile='F:\app\oracle\admin\orcl\dpdump\par.f'

 

Import: Release 11.2.0.1.0 - Production on 星期日 1221 18:39:02 2014

 

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

口令:

 

UDI-28009: 操作产生了 ORACLE 错误 28009

ORA-28009: 应当以 SYSDBA 身份或 SYSOPER 身份建立 SYS 连接

 

用户名: sys as sysdba

口令:

 

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TRANSPORTABLE_01"

启动 "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  sys/******** AS SYSDBA parfile='F:\app\oracle\admin\orcl\dpdump\par.f'

处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK

处理对象类型 TRANSPORTABLE_EXPORT/TABLE

处理对象类型 TRANSPORTABLE_EXPORT/INDEX/INDEX

处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

作业 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" 已于 18:39:15 成功完成

 

 

8.3  查看目标平台信息

 

C:\Documents and Settings\Administrator>

 

 

查看目标库表空间状态

SQL> select tablespace_name,status from dba_tablespaces;

 

TABLESPACE_NAME                STATUS

------------------------------ ---------

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP                           ONLINE

USERS                          ONLINE

RMAN_TS                        ONLINE

APP1TBS                        READ ONLY

APP2TBS                        READ ONLY

IDXTBS                         READ ONLY

 

已选择9行。

 

SQL> alter tablespace app1tbs read write;

 

表空间已更改。

 

SQL> alter tablespace app2tbs read write;

 

表空间已更改。

 

SQL> alter tablespace idxtbs read write;

 

表空间已更改。

 

SQL>

 

SQL>

 

 

SQL> set line 9999 pagesize 9999

SQL> select * from scott.app1_tab;

 

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

      9000 lastwiner

      9001 lastwiner

      7369 SMITH      CLERK           7902 17-12月-80            800                    20

      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30

      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30

      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20

      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30

      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30

      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10

      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20

      7839 KING       PRESIDENT            17-11月-81           5000                    10

      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30

      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20

      7900 JAMES      CLERK           7698 03-12月-81            950                    30

      7902 FORD       ANALYST         7566 03-12月-81           3000                    20

      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

 

已选择16行。

 

SQL> select * from scott.app2_tab;

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

 

SQL> select D.owner,D.index_name,D.table_name,D.tablespace_name from dba_indexes d WHERE d.table_name in ('APP1_TAB','APP2_TAB');

 

OWNER                          INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------ ------------------------------ ------------------------------------------------------------

SCOTT                            IDX_EMP_ENAME                  APP1_TAB                       IDXTBS

SCOTT                           IDX_DEPT_DNAME                 APP2_TAB                       IDXTBS

 

SQL> SELECT   a.NAME,  b.NAME FROM v$tablespace a , v$datafile b WHERE a.TS#=b.TS#  ;

 

NAME                           NAME

------------------------------ -----------------------------------------------------------------------------------------------------------------------

SYSTEM                         F:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF

SYSAUX                         F:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF

UNDOTBS1                       F:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF

USERS                          F:\APP\ORACLE\ORADATA\ORCL\USERS01.DBF

RMAN_TS                        F:\APP\ORACLE\ORADATA\ORCL\RMAN.DBF

APP1TBS                        F:\APP\ORACLE\ORADATA\ORCL\APP1TBS.274.866911939

APP2TBS                        F:\APP\ORACLE\ORADATA\ORCL\APP2TBS.275.866912075

IDXTBS                         F:\APP\ORACLE\ORADATA\ORCL\IDXTBS.276.866912133

 

已选择8行。

 

SQL>

 

 

9  修改表空间对应的文件名

 

执行如下脚本:

create directory asmsrc as 'F:\app\oracle\oradata\orcl';

 

alter tablespace app1tbs offline ;

alter tablespace app2tbs offline ;

alter tablespace idxtbs offline ;

 

begin

   dbms_file_transfer.copy_file('ASMSRC','APP1TBS.274.866911939','ASMSRC','APP1TBS.DBF');

   dbms_file_transfer.copy_file('ASMSRC','APP2TBS.275.866912075','ASMSRC','APP2TBS.DBF');

   dbms_file_transfer.copy_file('ASMSRC','IDXTBS.276.866912133','ASMSRC','IDXTBS.DBF');

END;

/

 

alter database rename file'F:\APP\ORACLE\ORADATA\ORCL\APP1TBS.274.866911939'  to'F:\APP\ORACLE\ORADATA\ORCL\APP1TBS.DBF';

alter database rename file'F:\APP\ORACLE\ORADATA\ORCL\APP2TBS.275.866912075'  to'F:\APP\ORACLE\ORADATA\ORCL\APP2TBS.DBF';

alter database rename file'F:\APP\ORACLE\ORADATA\ORCL\IDXTBS.276.866912133'  to'F:\APP\ORACLE\ORADATA\ORCL\IDXTBS.DBF';

 

alter tablespace app1tbs online ;

alter tablespace app2tbs online ;

alter tablespace idxtbs online ;

 

 

查看结果:

SQL> SELECT  a.NAME,  b.NAME FROM v$tablespace a , v$datafile b WHERE a.TS#=b.TS#;

 

NAME                           NAME

------------------------------ ---------------------------------------------------------

SYSTEM                         F:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF

SYSAUX                         F:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF

UNDOTBS1                       F:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF

USERS                          F:\APP\ORACLE\ORADATA\ORCL\USERS01.DBF

RMAN_TS                        F:\APP\ORACLE\ORADATA\ORCL\RMAN.DBF

APP1TBS                        F:\APP\ORACLE\ORADATA\ORCL\APP1TBS.DBF

APP2TBS                        F:\APP\ORACLE\ORADATA\ORCL\APP2TBS.DBF

IDXTBS                         F:\APP\ORACLE\ORADATA\ORCL\IDXTBS.DBF

 

已选择8行。

 

 

查询数据:

SQL> select tablespace_name,status from dba_tablespaces;

 

TABLESPACE_NAME                STATUS

------------------------------ ---------

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP                           ONLINE

USERS                          ONLINE

RMAN_TS                        ONLINE

APP1TBS                        ONLINE

APP2TBS                        ONLINE

IDXTBS                         ONLINE

 

已选择9行。

 

SQL>  set line 9999 pagesize 9999

SQL> select * from scott.app1_tab;

 

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

      9000 lastwiner

      9001 lastwiner

      7369 SMITH      CLERK           7902 17-12月-80            800                    20

      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30

      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30

      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20

      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30

      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30

      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10

      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20

      7839 KING       PRESIDENT            17-11月-81           5000                    10

      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30

      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20

      7900 JAMES      CLERK           7698 03-12月-81            950                    30

      7902 FORD       ANALYST         7566 03-12月-81           3000                    20

      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

 

已选择16行。

 

SQL>

 

10  结束语

本实例是源库为asm,目标库为os文件,另外,如果源库为os文件,而目标库为asm文件的话,以上步骤不变,在最后修改表空间对应的数据文件名称的时候使用rman镜像拷贝来重命名文件即可。

  ①  启动目标库到mount状态

  ② rman 执行:

backup as copy datafile 6 format +DATA/ORCLASM/XXX.DBF;

SWITCH TABLESPACE APP1TBS TO COPY;

ALTER DATABSE OPEN;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-1375260/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1355
  • 访问量
    8252599