ITPub博客

首页 > 数据库 > Oracle > ASM数据和File System文件转移方法集锦

ASM数据和File System文件转移方法集锦

Oracle 作者:luashin 时间:2016-01-18 22:10:55 0 删除 编辑

BLOG文档结构图:

ASM数据文件和File System系统文件转移方法集锦

1、说明

2、OS -> ASM

    2.1 rman + set newname + open状态

    2.2 rman + backup as copy + mount状态

    2.3 dbms_file_transfer实现

    2.4 RMAN Convert

    2.5 cp命令

3、ASM -> OS

    3.1 dbms_file_transfer实现

    3.2 rman + backup as copy状态

    3.3 rman convert + open状态

    3.4 rman + set newname + mount状态

    3.5 cp命令

4、总结

1、说明

    本blog介绍了各种asm数据文件和filesystem文件之间的转换方法,有的记录了过程,有的没有记录过程只记录了相关代码,大家若有兴趣可以自行测试。

本次测试的表空间、磁盘组和OS文件关系如下,文档中不再说明:

表空间名

磁盘组

filesystem文件

testdg

+DATA/orclasm/datafile/

/home/oracle/

用的数据库环境:

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


2、OS -> ASM

2.1 rman + set newname + open状态

[oracle@rhel6_lhr ~]$ more b.sql

run{

sql 'alter tablespace testdg offline immediate';

set newname for datafile 14 to '+DATA';

restore tablespace testdg;

switch datafile 14;

recover tablespace testdg;

sql 'alter tablespace testdg online';

}

[oracle@rhel6_lhr ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 13 12:14:43 2015

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

connected to target database: ORCLASM (DBID=3424884828)

RMAN> report schema;

Report of database schema for database with db_unique_name ORCLASM

List of Permanent Datafiles

===========================

File Size(MB) Tablespace  RB segs Datafile Name

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

1  890    SYSTEM     ***   +DATA/orclasm/datafile/system.256.850260145

2  940    SYSAUX     ***   +DATA/orclasm/datafile/sysaux.257.850260145

3  190    UNDOTBS1    *** +DATA/orclasm/datafile/undotbs1.258.851526539

4  2676    USERS      ***   +DATA/orclasm/datafile/users.259.850260147

5  345    EXAMPLE     ***  +DATA/orclasm/datafile/example.265.850260295

6  5      UNDOTBS2    ***  +DATA/orclasm/datafile/undotbs2.267.851204361

7  50     TBS_RC    ***   +DATA/orclasm/datafile/tbs_rc.268.852116523

8  100     TS_LHR    ***   +DATA/orclasm/datafile/ts_lhr.269.852632495

9  1      ENCRYPTED_TS *** +DATA/orclasm/datafile/encrypted_ts.272.854650889

10  100     GOLDENGATE  *** +DATA/orclasm/datafile/goldengate.273.862829891

11  50      APP1TBS    ***   +DATA/orclasm/datafile/app1tbs.274.866911939

12  50      APP2TBS    ***  +DATA/orclasm/datafile/app2tbs.275.866912075

13  50      IDXTBS    ***   +DATA/orclasm/datafile/idxtbs.276.866912133

14  2      TESTDG    ***     /home/oracle/test1.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

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

1  237   TEMP    32767       +DATA/orclasm/tempfile/temp.264.850260283

2  10    TEMP1   10     +DATA/orclasm/tempfile/temp1.270.853779297


RMAN> @/home/oracle/b.sql

RMAN> run{

sql 'alter tablespace testdg offline immediate';

set newname for datafile 14 to'+DATA';

restore tablespace testdg;

switch datafile 14;

recover tablespace testdg;

sql 'alter tablespace testdg online';

}

using target database control file instead of recovery catalog

sql statement: alter tablespace testdg offline immediate

executing command: SET NEWNAME

Starting restore at 13-JAN-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=14 device type=DISK

creating datafile file number=14 name=+DATA

restore not done; all files read only, offline, or already restored

Finished restore at 13-JAN-15

datafile 14 switched to datafile copy

input datafile copy RECID=3 STAMP=868882494 file name=+DATA/orclasm/datafile/testdg.277.868882493

Starting recover at 13-JAN-15

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 13-JAN-15

sql statement: alter tablespace testdg online

RMAN> **end-of-file**


RMAN> report schema;

Report of database schema for database with db_unique_name ORCLASM

List of Permanent Datafiles

===========================

File Size(MB) Tablespace  RB segs Datafile Name

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

1  890   SYSTEM    ***   +DATA/orclasm/datafile/system.256.850260145

2  940   SYSAUX    ***   +DATA/orclasm/datafile/sysaux.257.850260145

3  190   UNDOTBS1   ***  +DATA/orclasm/datafile/undotbs1.258.851526539

4  2676   USERS     ***   +DATA/orclasm/datafile/users.259.850260147

5  345   EXAMPLE    ***  +DATA/orclasm/datafile/example.265.850260295

6  5    UNDOTBS2   ***  +DATA/orclasm/datafile/undotbs2.267.851204361

7  50    TBS_RC    ***   +DATA/orclasm/datafile/tbs_rc.268.852116523

8  100   TS_LHR    ***   +DATA/orclasm/datafile/ts_lhr.269.852632495

9  1    ENCRYPTED_TS *** +DATA/orclasm/datafile/encrypted_ts.272.854650889

10 100    GOLDENGATE  *** +DATA/orclasm/datafile/goldengate.273.862829891

11 50    APP1TBS    ***  +DATA/orclasm/datafile/app1tbs.274.866911939

12 50    APP2TBS    ***  +DATA/orclasm/datafile/app2tbs.275.866912075

13 50    IDXTBS    ***   +DATA/orclasm/datafile/idxtbs.276.866912133

14 2     TESTDG    ***   +DATA/orclasm/datafile/testdg.277.868882493

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

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

1    237  TEMP   32767       +DATA/orclasm/tempfile/temp.264.850260283

2    10   TEMP1   10     +DATA/orclasm/tempfile/temp1.270.853779297

RMAN>


2.2 rman + backup as copy+mount状态

run{

shutdown immediate;

startup mount;

backup as copy datafile 14 format '+DATA';

}

SWITCH TABLESPACE testdg TO COPY;

alter database open;

 

[oracle@rhel6_lhr ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 13 15:00:19 2015

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

connected to target database: ORCLASM (DBID=3424884828)

RMAN> report schema;

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name ORCLASM

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1  890   SYSTEM    ***   +DATA/orclasm/datafile/system.256.850260145

2  940   SYSAUX    ***   +DATA/orclasm/datafile/sysaux.257.850260145

3  190   UNDOTBS1   ***  +DATA/orclasm/datafile/undotbs1.258.851526539

4  2676   USERS     ***   +DATA/orclasm/datafile/users.259.850260147

5  345   EXAMPLE    ***  +DATA/orclasm/datafile/example.265.850260295

6  5    UNDOTBS2   ***  +DATA/orclasm/datafile/undotbs2.267.851204361

7  50    TBS_RC    ***   +DATA/orclasm/datafile/tbs_rc.268.852116523

8  100   TS_LHR    ***   +DATA/orclasm/datafile/ts_lhr.269.852632495

9  1    ENCRYPTED_TS *** +DATA/orclasm/datafile/encrypted_ts.272.854650889

10 100    GOLDENGATE  *** +DATA/orclasm/datafile/goldengate.273.862829891

11 50    APP1TBS    ***  +DATA/orclasm/datafile/app1tbs.274.866911939

12 50    APP2TBS    ***  +DATA/orclasm/datafile/app2tbs.275.866912075

13 50    IDXTBS    ***   +DATA/orclasm/datafile/idxtbs.276.866912133

14 2     TESTDG    ***   /home/oracle/testdg.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

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

1  237   TEMP    32767    +DATA/orclasm/tempfile/temp.264.850260283

2  10    TEMP1   10     +DATA/orclasm/tempfile/temp1.270.853779297


RMAN> @/home/oracle/h.sql

RMAN> run{

shutdown immediate;

startup mount;

backup as copy datafile 14 format '+DATA';

}

database closed

database dismounted

Oracle instance shut down

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area     375828480 bytes

Fixed Size                     2228464 bytes

Variable Size                268439312 bytes

Database Buffers             100663296 bytes

Redo Buffers                   4497408 bytes

Starting backup at 13-JAN-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=399 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00014 name=/home/oracle/testdg.dbf

output file name=+DATA/orclasm/datafile/testdg.282.868892465 tag=TAG20150113T150104 RECID=35 STAMP=868892465

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02

Finished backup at 13-JAN-15

Starting Control File and SPFILE Autobackup at 13-JAN-15

piece handle=/home/oracle/oracle_bk/orclasm/control_c-3424884828-20150113-09.bak comment=NONE

Finished Control File and SPFILE Autobackup at 13-JAN-15


RMAN> SWITCH TABLESPACE testdg TO COPY;

datafile 14 switched to datafile copy "+DATA/orclasm/datafile/testdg.282.868892465"


RMAN> alter database open;

database opened

RMAN> **end-of-file**

 

RMAN> report schema;

Report of database schema for database with db_unique_name ORCLASM

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1  890   SYSTEM    ***   +DATA/orclasm/datafile/system.256.850260145

2  940   SYSAUX    ***   +DATA/orclasm/datafile/sysaux.257.850260145

3  190   UNDOTBS1   ***  +DATA/orclasm/datafile/undotbs1.258.851526539

4  2676   USERS     ***   +DATA/orclasm/datafile/users.259.850260147

5  345   EXAMPLE    ***  +DATA/orclasm/datafile/example.265.850260295

6  5    UNDOTBS2   ***  +DATA/orclasm/datafile/undotbs2.267.851204361

7  50    TBS_RC    ***   +DATA/orclasm/datafile/tbs_rc.268.852116523

8  100   TS_LHR    ***   +DATA/orclasm/datafile/ts_lhr.269.852632495

9  1    ENCRYPTED_TS *** +DATA/orclasm/datafile/encrypted_ts.272.854650889

10 100    GOLDENGATE  *** +DATA/orclasm/datafile/goldengate.273.862829891

11 50    APP1TBS    ***  +DATA/orclasm/datafile/app1tbs.274.866911939

12 50    APP2TBS    ***  +DATA/orclasm/datafile/app2tbs.275.866912075

13 50    IDXTBS    ***   +DATA/orclasm/datafile/idxtbs.276.866912133

14 2     TESTDG    ***   +DATA/orclasm/datafile/testdg.282.868892465

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

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

1  237   TEMP    32767    +DATA/orclasm/tempfile/temp.264.850260283

2  10    TEMP1   10     +DATA/orclasm/tempfile/temp1.270.853779297

RMAN>


2.3  dbms_file_transfer

create directory asmsrc as '+DATA/orclasm/datafile/';

create directory osdesc as '/home/oracle/';

alter tablespace testdg offline;

exec dbms_file_transfer.copy_file('osdesc','testdg.dbf','ASMSRC','testdg.dbf);

alter database rename file'/home/oracle/testdg.dbf'  to '+DATA/orclasm/datafile/testdg.dbf';

alter tablespace testdg online;


2.4  RMAN convert实现

RMAN下:

convert datafile '/home/oracle/testdg.dbf' format '+DATA';

SQL下:

alter tablespace testdg offline ;

alter tablespace testdg rename datafile '/home/oracle/testdg.dbf' to '+DATA/orclasm/datafile/testdg.284.868895277';

recover datafile 14;

alter tablespace testdg online;


2.5 cp命令

Oracle Database 11g的ASM实例可以直接使用cp命令来实现,从ASM实例的cp帮助信息上看,已经可以直接从本地数据库直接cp到远端数据库了!

SQL> alter tablespace testdg offline;

[root@rhel6_lhr ~]# cp /home/oracle/testdg.dbf /home/grid/testdg.dbf

[root@rhel6_lhr ~]# chown grid:oinstall /home/grid/testdg.dbf

[root@rhel6_lhr ~]# su - grid

ASMCMD> cp /home/grid/testdg.dbf +DATA/orclasm/datafile/testdg.dbf

copying /home/grid/testdg.dbf -> +DATA/orclasm/datafile/testdg.dbf

ASMCMD> 

alter database rename file '/home/oracle/testdg.dbf'  to '+DATA/orclasm/datafile/testdg.dbf';

alter tablespace testdg online;


3、ASM -> OS

3.1 dbms_file_transfer实现

SQL下执行:

create directory asmsrc as'+DATA/orclasm/datafile/';

create directory osdesc as '/home/oracle/';

alter tablespace testdg offline;

exec dbms_file_transfer.copy_file('ASMSRC','testdg.282.868891371','osdesc','testdg.dbf'); 

alter database rename file '+DATA/orclasm/datafile/testdg.282.868891371' to '/home/oracle/testdg.dbf';

alter tablespace testdg online ;


SQL> select name,status from v$datafile;

NAME                                         STATUS

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

+DATA/orclasm/datafile/system.256.850260145    SYSTEM

+DATA/orclasm/datafile/sysaux.257.850260145    ONLINE

+DATA/orclasm/datafile/undotbs1.258.851526539   ONLINE

+DATA/orclasm/datafile/users.259.850260147    ONLINE

+DATA/orclasm/datafile/example.265.850260295   ONLINE

+DATA/orclasm/datafile/undotbs2.267.851204361   ONLINE

+DATA/orclasm/datafile/tbs_rc.268.852116523    ONLINE

+DATA/orclasm/datafile/ts_lhr.269.852632495    ONLINE

+DATA/orclasm/datafile/encrypted_ts.272.854650889 ONLINE

+DATA/orclasm/datafile/goldengate.273.862829891  ONLINE

+DATA/orclasm/datafile/app1tbs.274.866911939   ONLINE

+DATA/orclasm/datafile/app2tbs.275.866912075   ONLINE

+DATA/orclasm/datafile/idxtbs.276.866912133    ONLINE

+DATA/orclasm/datafile/testdg.282.868891371    ONLINE

SQL> edit f.sql

SQL> host more f.sql

create directory asmsrc as'+DATA/orclasm/datafile/';

create directory osdesc as '/home/oracle/';

alter tablespace testdg offline;

exec dbms_file_transfer.copy_file('ASMSRC','testdg.282.868891371','osdesc','testdg.dbf');

alter database rename file '+DATA/orclasm/datafile/testdg.282.868891371' to '/home/oracle/testdg.dbf';

alter tablespace testdg online;

SQL> @f.sql

Directory created.

Directory created.

Tablespace altered.

PL/SQL procedure successfully completed.

Database altered.

Tablespace altered.


SQL> set pagesize 9999 line 9999

SQL> col name format a100

SQL> select name ,status from v$datafile;

NAME      STATUS

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

+DATA/orclasm/datafile/system.256.850260145      SYSTEM

+DATA/orclasm/datafile/sysaux.257.850260145      ONLINE

+DATA/orclasm/datafile/undotbs1.258.851526539     ONLINE

+DATA/orclasm/datafile/users.259.850260147       ONLINE

+DATA/orclasm/datafile/example.265.850260295     ONLINE

+DATA/orclasm/datafile/undotbs2.267.851204361     ONLINE

+DATA/orclasm/datafile/tbs_rc.268.852116523      ONLINE

+DATA/orclasm/datafile/ts_lhr.269.852632495      ONLINE

+DATA/orclasm/datafile/encrypted_ts.272.854650889   ONLINE

+DATA/orclasm/datafile/goldengate.273.862829891    ONLINE

+DATA/orclasm/datafile/app1tbs.274.866911939     ONLINE

+DATA/orclasm/datafile/app2tbs.275.866912075     ONLINE

+DATA/orclasm/datafile/idxtbs.276.866912133      ONLINE

/home/oracle/testdg.dbf                          ONLINE

14 rows selected.


3.2 rman + backup as copy

run{

shutdown immediate;

startup mount;

backup as copy datafile 14 format '/home/oracle/testdg.dbf';

}

switch tablespace testdg to copy;

alter database open;


[oracle@rhel6_lhr ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 13 14:20:32 2015

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

connected to target database: ORCLASM (DBID=3424884828)

RMAN> report schema;

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name ORCLASM

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1  890   SYSTEM    ***   +DATA/orclasm/datafile/system.256.850260145

2  940   SYSAUX    ***   +DATA/orclasm/datafile/sysaux.257.850260145

3  190   UNDOTBS1   ***  +DATA/orclasm/datafile/undotbs1.258.851526539

4  2676   USERS     ***   +DATA/orclasm/datafile/users.259.850260147

5  345   EXAMPLE    ***  +DATA/orclasm/datafile/example.265.850260295

6  5    UNDOTBS2   ***  +DATA/orclasm/datafile/undotbs2.267.851204361

7  50    TBS_RC    ***   +DATA/orclasm/datafile/tbs_rc.268.852116523

8  100   TS_LHR    ***   +DATA/orclasm/datafile/ts_lhr.269.852632495

9  1    ENCRYPTED_TS *** +DATA/orclasm/datafile/encrypted_ts.272.854650889

10 100    GOLDENGATE  *** +DATA/orclasm/datafile/goldengate.273.862829891

11 50    APP1TBS    ***  +DATA/orclasm/datafile/app1tbs.274.866911939

12 50    APP2TBS    ***  +DATA/orclasm/datafile/app2tbs.275.866912075

13 50    IDXTBS    ***   +DATA/orclasm/datafile/idxtbs.276.866912133

14 2     TESTDG     ***  +DATA/orclasm/datafile/testdg.281.868889825

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

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

1  237   TEMP    32767   +DATA/orclasm/tempfile/temp.264.850260283

2  10    TEMP1    10     +DATA/orclasm/tempfile/temp1.270.853779297

 

RMAN> @/home/oracle/e.sql

RMAN> run{

shutdown immediate;

startup mount;

backup as copy datafile 14 format '/home/oracle/testdg.dbf';

}

database closed

database dismounted

Oracle instance shut down

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area     375828480 bytes

Fixed Size                     2228464 bytes

Variable Size                268439312 bytes

Database Buffers             100663296 bytes

Redo Buffers                   4497408 bytes

Starting backup at 13-JAN-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=13 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00014 name=+DATA/orclasm/datafile/testdg.281.868889825

output file name=/home/oracle/testdg.dbf tag=TAG20150113T142110 RECID=30 STAMP=868890071

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 13-JAN-15

Starting Control File and SPFILE Autobackup at 13-JAN-15

piece handle=/home/oracle/oracle_bk/orclasm/control_c-3424884828-20150113-06.bak comment=NONE

Finished Control File and SPFILE Autobackup at 13-JAN-15

RMAN> **end-of-file**


RMAN> switch tablespace testdg to copy;

datafile 14 switched to datafile copy "/home/oracle/testdg.dbf"


RMAN> alter database open;

database opened

 

RMAN> report schema;

Report of database schema for database with db_unique_name ORCLASM

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1  890   SYSTEM    ***   +DATA/orclasm/datafile/system.256.850260145

2  940   SYSAUX    ***   +DATA/orclasm/datafile/sysaux.257.850260145

3  190   UNDOTBS1   ***  +DATA/orclasm/datafile/undotbs1.258.851526539

4  2676   USERS     ***   +DATA/orclasm/datafile/users.259.850260147

5  345   EXAMPLE    ***  +DATA/orclasm/datafile/example.265.850260295

6  5    UNDOTBS2   ***  +DATA/orclasm/datafile/undotbs2.267.851204361

7  50    TBS_RC    ***   +DATA/orclasm/datafile/tbs_rc.268.852116523

8  100   TS_LHR    ***   +DATA/orclasm/datafile/ts_lhr.269.852632495

9  1    ENCRYPTED_TS *** +DATA/orclasm/datafile/encrypted_ts.272.854650889

10 100    GOLDENGATE  *** +DATA/orclasm/datafile/goldengate.273.862829891

11 50    APP1TBS    ***  +DATA/orclasm/datafile/app1tbs.274.866911939

12 50    APP2TBS    ***  +DATA/orclasm/datafile/app2tbs.275.866912075

13 50    IDXTBS    ***   +DATA/orclasm/datafile/idxtbs.276.866912133

14 2     TESTDG    ***   /home/oracle/testdg.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace  Maxsize(MB) Tempfile Name

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

1  237   TEMP    32767    +DATA/orclasm/tempfile/temp.264.850260283

2  10    TEMP1    10     +DATA/orclasm/tempfile/temp1.270.853779297


3.3 rman convert + open状态

使用rmanconvert命令来实现,同样适用于10g

rman下:

convert datafile '+DATA/orclasm/datafile/testdg.277.868887219' format '/home/oracle/testdg.dbf';
 

sql下:

alter tablespace testdg offline ;

alter tablespace testdg rename datafile '+DATA/orclasm/datafile/testdg.277.868887219' to '/home/oracle/testdg1.dbf';

recover datafile 14;

alter tablespace testdg online;


[oracle@rhel6_lhr ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 13 13:35:46 2015

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

connected to target database: ORCLASM (DBID=3424884828)

RMAN> report schema;

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name ORCLASM

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1  890   SYSTEM    ***   +DATA/orclasm/datafile/system.256.850260145

2  940   SYSAUX    ***   +DATA/orclasm/datafile/sysaux.257.850260145

3  190   UNDOTBS1   ***  +DATA/orclasm/datafile/undotbs1.258.851526539

4  2676   USERS     ***   +DATA/orclasm/datafile/users.259.850260147

5  345   EXAMPLE    ***  +DATA/orclasm/datafile/example.265.850260295

6  5    UNDOTBS2   ***  +DATA/orclasm/datafile/undotbs2.267.851204361

7  50    TBS_RC    ***   +DATA/orclasm/datafile/tbs_rc.268.852116523

8  100   TS_LHR    ***   +DATA/orclasm/datafile/ts_lhr.269.852632495

9  1    ENCRYPTED_TS *** +DATA/orclasm/datafile/encrypted_ts.272.854650889

10 100    GOLDENGATE  *** +DATA/orclasm/datafile/goldengate.273.862829891

11 50    APP1TBS    ***  +DATA/orclasm/datafile/app1tbs.274.866911939

12 50    APP2TBS    ***  +DATA/orclasm/datafile/app2tbs.275.866912075

13 50    IDXTBS    ***   +DATA/orclasm/datafile/idxtbs.276.866912133

14 2     TESTDG    ***   +DATA/orclasm/datafile/testdg.277.868887219

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

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

1  237   TEMP    32767    +DATA/orclasm/tempfile/temp.264.850260283

2  10    TEMP1   10     +DATA/orclasm/tempfile/temp1.270.853779297


RMAN> convert datafile '+DATA/orclasm/datafile/testdg.277.868887219' format '/home/oracle/testdg.dbf';

Starting conversion at target at 13-JAN-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile conversion

input file name=+DATA/orclasm/datafile/testdg.277.868887219

converted datafile=/home/oracle/testdg.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01

Finished conversion at target at 13-JAN-15

Starting Control File and SPFILE Autobackup at 13-JAN-15

piece handle=/home/oracle/oracle_bk/orclasm/control_c-3424884828-20150113-04.bak comment=NONE

Finished Control File and SPFILE Autobackup at 13-JAN-15

RMAN>


[oracle@rhel6_lhr ~]$ vim d.sql

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 13 13:38:36 2015

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> host more /home/oracle/d.sql

alter tablespace testdg offline ;

alter tablespace testdg rename datafile '+DATA/orclasm/datafile/testdg.277.868887219' to '/home/oracle/testdg1.dbf';

recover datafile 14;

alter tablespace testdg online;


SQL> @/home/oracle/d.sql

Tablespace altered.

Tablespace altered.

Media recovery complete.

Tablespace altered.


SQL> set pagesize 9999

SQL> select name from v$datafile;

NAME

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

+DATA/orclasm/datafile/system.256.850260145

+DATA/orclasm/datafile/sysaux.257.850260145

+DATA/orclasm/datafile/undotbs1.258.851526539

+DATA/orclasm/datafile/users.259.850260147

+DATA/orclasm/datafile/example.265.850260295

+DATA/orclasm/datafile/undotbs2.267.851204361

+DATA/orclasm/datafile/tbs_rc.268.852116523

+DATA/orclasm/datafile/ts_lhr.269.852632495

+DATA/orclasm/datafile/encrypted_ts.272.854650889

+DATA/orclasm/datafile/goldengate.273.862829891

+DATA/orclasm/datafile/app1tbs.274.866911939

+DATA/orclasm/datafile/app2tbs.275.866912075

+DATA/orclasm/datafile/idxtbs.276.866912133

/home/oracle/testdg1.dbf

14 rows selected.

3.4 rman + set newname + mount状态

run{

shutdown immediate;

startup mount;

set newname for datafile 14 to '/home/oracle/testdg.dbf';

restore datafile 14;

switch datafile 14;

recover datafile 14;

alter database open;

}


[oracle@rhel6_lhr ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 13 13:58:39 2015

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

connected to target database: ORCLASM (DBID=3424884828)

RMAN> report schema;

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name ORCLASM

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1  890   SYSTEM    ***   +DATA/orclasm/datafile/system.256.850260145

2  940   SYSAUX    ***   +DATA/orclasm/datafile/sysaux.257.850260145

3  190   UNDOTBS1   ***  +DATA/orclasm/datafile/undotbs1.258.851526539

4  2676   USERS     ***   +DATA/orclasm/datafile/users.259.850260147

5  345   EXAMPLE    ***  +DATA/orclasm/datafile/example.265.850260295

6  5    UNDOTBS2   ***  +DATA/orclasm/datafile/undotbs2.267.851204361

7  50    TBS_RC    ***   +DATA/orclasm/datafile/tbs_rc.268.852116523

8  100   TS_LHR    ***   +DATA/orclasm/datafile/ts_lhr.269.852632495

9  1    ENCRYPTED_TS *** +DATA/orclasm/datafile/encrypted_ts.272.854650889

10 100    GOLDENGATE  *** +DATA/orclasm/datafile/goldengate.273.862829891

11 50    APP1TBS    ***  +DATA/orclasm/datafile/app1tbs.274.866911939

12 50    APP2TBS    ***  +DATA/orclasm/datafile/app2tbs.275.866912075

13 50    IDXTBS    ***   +DATA/orclasm/datafile/idxtbs.276.866912133

14 2     TESTDG    ***   +DATA/orclasm/datafile/testdg.279.868888623

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

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

1  237   TEMP    32767   +DATA/orclasm/tempfile/temp.264.850260283

2  10   TEMP1    10     +DATA/orclasm/tempfile/temp1.270.853779297


RMAN> @/home/oracle/c.sql

RMAN> run{

shutdown immediate;

startup mount;

set newname for datafile 14 to '/home/oracle/testdg.dbf';

restore datafile 14;

switch datafile 14;

recover datafile 14;

alter database open;

}

database closed

database dismounted

Oracle instance shut down

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area     375828480 bytes

Fixed Size                     2228464 bytes

Variable Size                268439312 bytes

Database Buffers             100663296 bytes

Redo Buffers                   4497408 bytes

executing command: SET NEWNAME

Starting restore at 13-JAN-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=13 device type=DISK

datafile 14 is already restored to file /home/oracle/testdg.dbf

restore not done; all files read only, offline, or already restored

Finished restore at 13-JAN-15

datafile 14 switched to datafile copy

input datafile copy RECID=20 STAMP=868888765 file name=/home/oracle/testdg.dbf

Starting recover at 13-JAN-15

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 13-JAN-15

database opened

RMAN> **end-of-file**


RMAN> report schema;

Report of database schema for database with db_unique_name ORCLASM

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1  890   SYSTEM    ***   +DATA/orclasm/datafile/system.256.850260145

2  940   SYSAUX    ***   +DATA/orclasm/datafile/sysaux.257.850260145

3  190   UNDOTBS1   ***  +DATA/orclasm/datafile/undotbs1.258.851526539

4  2676   USERS     ***   +DATA/orclasm/datafile/users.259.850260147

5  345   EXAMPLE    ***  +DATA/orclasm/datafile/example.265.850260295

6  5    UNDOTBS2   ***  +DATA/orclasm/datafile/undotbs2.267.851204361

7  50    TBS_RC    ***   +DATA/orclasm/datafile/tbs_rc.268.852116523

8  100   TS_LHR    ***   +DATA/orclasm/datafile/ts_lhr.269.852632495

9  1    ENCRYPTED_TS *** +DATA/orclasm/datafile/encrypted_ts.272.854650889

10 100    GOLDENGATE  *** +DATA/orclasm/datafile/goldengate.273.862829891

11 50    APP1TBS    ***  +DATA/orclasm/datafile/app1tbs.274.866911939

12 50    APP2TBS    ***  +DATA/orclasm/datafile/app2tbs.275.866912075

13 50    IDXTBS    ***   +DATA/orclasm/datafile/idxtbs.276.866912133

14 2     TESTDG    ***   /home/oracle/testdg.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

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

1  237   TEMP    32767    +DATA/orclasm/tempfile/temp.264.850260283

2   10    TEMP1    10     +DATA/orclasm/tempfile/temp1.270.853779297

RMAN>


3.5 cp命令

SQL> alter tablespace testdg offline;

[root@rhel6_lhr ~]# su - grid

ASMCMD> cp +DATA/orclasm/datafile/testdg.dbf /home/grid/testdg.dbf

copying +DATA/orclasm/datafile/testdg.dbf -> /home/grid/testdg.dbf

[root@rhel6_lhr ~]# cp /home/grid/testdg.dbf /home/oracle/testdg.dbf

[root@rhel6_lhr ~]# chown oracle:oinstall /home/oracle/testdg.dbf

[root@rhel6_lhr ~]$ sqlplus / as sysdba;

SQL> alter database rename file'+DATA/orclasm/datafile/testdg.dbf' to '/home/oracle/testdg.dbf';

alter tablespace testdg online;


4 总结

    以上提供的各种办法各有优缺点,有的需要重启数据库,有的不需要,有的全在rman中执行,有的需要在sql下执行,大家需仔细领悟,如有牛人可以列出表格总结一下各种方法的优缺点我将不胜感激。

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

请登录后发表评论 登录
全部评论

注册时间:2015-02-18

  • 博文量
    290
  • 访问量
    1048514