重庆思庄Oracle、Redhat认证学习论坛
标题: 磁盘组中的数据文件批量复制到本地磁盘 [打印本页]
作者: denglj 时间: 2022-7-6 09:58
标题: 磁盘组中的数据文件批量复制到本地磁盘
实验目标：实现磁盘组中的数据文件批量复制到本地磁盘.
实际操作如下：
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
作者: 郑全 时间: 2022-7-6 11:54
本帖最后由 郑全 于 2022-7-6 11:56 编辑
这个脚本是哪个用户去执行，如果是ORACLE用户去执行？能成功执行吗？能否把执行脚本的过程发出来看看
作者: denglj 时间: 2022-7-6 13:21
本帖最后由 denglj 于 2022-7-6 14:03 编辑
上述复制脚本需由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目录.
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) |
Powered by Discuz! X3.2 |