磁盘组中的数据文件批量复制到本地磁盘

denglj

实验目标：实现磁盘组中的数据文件批量复制到本地磁盘.

实际操作如下：

SQL>select name from v$datafile;

NAME

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

+DATA/ORCLCDB/DATAFILE/system.257.1082993319

+DATA/ORCLCDB/DATAFILE/sysaux.258.1082993363

+DATA/ORCLCDB/DATAFILE/undotbs1.259.1082993389

+DATA/ORCLCDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.270.1082993933

+DATA/ORCLCDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.271.1082993933

+DATA/ORCLCDB/DATAFILE/users.260.1082993391

+DATA/ORCLCDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.272.1082993933

+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/DATAFILE/system.276.1082994661

+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/DATAFILE/sysaux.277.1082994661

+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/DATAFILE/undotbs1.275.1082994661

+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/DATAFILE/users.279.1082994667

NAME

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

/home/oracle/tsp_idx01.dbf

/home/oracle/tsp_item01.dbf

+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/DATAFILE/tsp_test01.dbf

+DATA/ORCLCDB/DATAFILE/tsp_treate.dbf

+DATA/ORCLCDB/DATAFILE/apnurrec.dbf

+DATA/ORCLCDB/DATAFILE/apnurrec01.dbf

+DATA/ORCLCDB/DATAFILE/apnurrec02.dbf

+DATA/ORCLCDB/DATAFILE/apnurrec03.dbf

+DATA/ORCLCDB/DATAFILE/apnurrec04.dbf

20 rowsselected.

如下单个文件复制.

SQL>cp +DATA/ORCLCDB/DATAFILE/system.257.1082993319/home/oracle/system.257.1082993319

如果数据文件很多，就需要批量生成复制语句，既提高效率同时也减少误操作.

使用如下语句生成：

SQL>  select 'cp ' ||name || ' /home/oracle/' ||substr(name,instr(name,'/',-1)+1)

       from v$datafile

'CP'||NAME||'/HOME/ORACLE/'||SUBSTR(NAME,INSTR(NAME,'/',-1)+1)

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

cp+DATA/ORCLCDB/DATAFILE/system.257.1082993319 /home/oracle/system.257.1082993319

cp+DATA/ORCLCDB/DATAFILE/sysaux.258.1082993363 /home/oracle/sysaux.258.1082993363

cp+DATA/ORCLCDB/DATAFILE/undotbs1.259.1082993389/home/oracle/undotbs1.259.1082993389

cp+DATA/ORCLCDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.270.1082993933/home/oracle/system.270.1082993933

cp+DATA/ORCLCDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.271.1082993933/home/oracle/sysaux.271.1082993933

cp+DATA/ORCLCDB/DATAFILE/users.260.1082993391 /home/oracle/users.260.1082993391

cp+DATA/ORCLCDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.272.1082993933/home/oracle/undotbs1.272.1082993933

cp+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/DATAFILE/system.276.1082994661/home/oracle/system.276.1082994661

cp+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/DATAFILE/sysaux.277.1082994661/home/oracle/sysaux.277.1082994661

cp+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/DATAFILE/undotbs1.275.1082994661/home/oracle/undotbs1.275.1082994661

cp+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/DATAFILE/users.279.1082994667/home/oracle/users.279.1082994667

'CP'||NAME||'/HOME/ORACLE/'||SUBSTR(NAME,INSTR(NAME,'/',-1)+1)

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

cp/home/oracle/tsp_idx01.dbf /home/oracle/tsp_idx01.dbf

cp/home/oracle/tsp_item01.dbf /home/oracle/tsp_item01.dbf

cp+DATA/ORCLCDB/CBB47E352F7AAEA7E0537885A8C03007/DATAFILE/tsp_test01.dbf/home/oracle/tsp_test01.dbf

cp+DATA/ORCLCDB/DATAFILE/tsp_treate.dbf /home/oracle/tsp_treate.dbf

cp+DATA/ORCLCDB/DATAFILE/apnurrec.dbf /home/oracle/apnurrec.dbf

cp+DATA/ORCLCDB/DATAFILE/apnurrec01.dbf /home/oracle/apnurrec01.dbf

cp+DATA/ORCLCDB/DATAFILE/apnurrec02.dbf /home/oracle/apnurrec02.dbf

cp+DATA/ORCLCDB/DATAFILE/apnurrec03.dbf /home/oracle/apnurrec03.dbf

cp+DATA/ORCLCDB/DATAFILE/apnurrec04.dbf /home/oracle/apnurrec04.dbf

郑全

这个脚本是哪个用户去执行，如果是ORACLE用户去执行？能成功执行吗？能否把执行脚本的过程发出来看看

denglj

郑全 发表于 2022-7-6 11:54
这个脚本是哪个用户去执行，如果是ORACLE用户去执行？能成功执行吗？能否把执行脚本的过程发出来看看

上述复制脚本需由grid用户进入asmcmd命令窗口执行，同时需要在系统层面将oracle用户的home目录的组权限添加上“wx”权限.

当组权限只有“x”时，复制报错

ASMCMD>cp +DATA/ORCLCDB/DATAFILE/tsp_treate.dbf /home/oracle/tsp_treate.dbf

ASMCMD-9463:operation failed due to lack of write permissions

权限查看：

[root@dbserverhome]# pwd

/home

[root@dbserverhome]# chmod g+x oracle

[root@dbserverhome]# ll

total 8

drwx------.  7 grid  oinstall  221 Jul  4 10:15 grid

drwx--x---.22 oracle oinstall 4096 Jul  4 10:16oracle

当组权限只有“w”时，复制报错

ASMCMD>cp +DATA/ORCLCDB/DATAFILE/apnurrec.dbf /home/oracle/apnurrec.dbf

copying+DATA/ORCLCDB/DATAFILE/apnurrec.dbf -> /home/oracle/apnurrec.dbf

ASMCMD-8016:copy source '+DATA/ORCLCDB/DATAFILE/apnurrec.dbf' and target'/home/oracle/apnurrec.dbf' failed

ORA-19505:failed to identify file "/home/oracle/apnurrec.dbf"

ORA-27037:unable to obtain file status

Linux-x86_64Error: 13: Permission denied

Additionalinformation: 9

ORA-15120:ASM file name '/home/oracle/apnurrec.dbf' does not begin with the ASM prefixcharacter

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

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

权限查看：

[root@dbserverhome]# pwd

/home

[root@dbserverhome]# ll

total 8

drwx------.  7 grid  oinstall  221 Jul  4 10:15 grid

drwx-w----.22 oracle oinstall 4096 Jul  6 13:03oracle

当组权限同时拥有“wx”时，复制无异常

ASMCMD>cp +DATA/ORCLCDB/DATAFILE/apnurrec.dbf /home/oracle/apnurrec.dbf

copying+DATA/ORCLCDB/DATAFILE/apnurrec.dbf -> /home/oracle/apnurrec.dbf

权限查看：

[root@dbserverhome]# pwd

/home

[root@dbserverhome]# ll

total 8

drwx------.  7 grid  oinstall  221 Jul  4 10:15 grid

drwx-wx---. 22 oracleoinstall 4096 Jul  6 13:03 oracle

以下oracle、grid用户的信息，可以看到oracle、grid同属ointall组.
[root@dbserver ~]# id oracle
uid=1001(oracle) gid=1006(oinstall) groups=1006(oinstall),1003(asmdba),1004(dba),1005(oper),1007(backupdba),1008(dgdba),1009(kmdba),1010(racdba)
[root@dbserver ~]# id grid
uid=1002(grid) gid=1006(oinstall) groups=1006(oinstall),1001(asmadmin),1002(asmoper),1003(asmdba),1004(dba),1010(racdba)
说明：通常情况因为权限问题，我们采用的是复制到/home/grid目录.