oracle 传输表空间一例

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/lihuarongaini/article/details/99173115

 

文档结构图:

image

 

 场景描述

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

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

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

 

 环境准备

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.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),不需要进行表空间集转换

 

 选择自包含的表空间集(目前要传输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.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 星期日 12月 21 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;

展开阅读全文

没有更多推荐了,返回首页