【DB笔试面试248】在Oracle中，ASM文件和OS文件互相转换有哪些方法？

2022-03-27 04:17:46

Q 题目如下所示：

在Oracle中，ASM文件和OS文件互相转换有哪些方法？

A 答案如下所示：

答案：主要采用set newname、convert datafile、dbms_file_transfer.copy_file、backup as copy datafile、alter database rename file这几种办法。

首先介绍OS文件到ASM文件转换的方法。

①　set newname方式

run{

crosscheck backup;

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';

}

②　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;

③　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 ;

④　backup as copy

run{

shutdown immediate;

startup mount;

backup as copy datafile 14 format '+DATA';

}

SWITCH TABLESPACE testdg TO COPY;

alter database open;

⑤　在ASMCMD中cp拷贝

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 ;

而ASM文件到OS文件转换的方法有如下几种：

①　recover datafile

在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/testdg.dbf';

recover datafile 14;

alter tablespace testdg online;

②　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('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 ;

③　set newname

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;

}

④　switch tablespace

run{

shutdown immediate;

startup mount;

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

}

switch tablespace testdg to copy;

alter database open;

⑤　ASMCMD中的cp拷贝

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

ASMCMD>

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

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

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

alter tablespace testdg online ;

& 说明：

有关ASM和OS文件互相转换的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-1400516/。