合 ASM数据文件和OS文件系统互相转移方法总结（移动或重命名数据文件）
Tags： OracleASM重命名移动转换文件系统ASM文件
👉 本文共约1104个字，系统预计阅读时间或需5分钟。
目录
说明
本blog介绍了各种asm数据文件和filesystem文件之间的转换方法，有的记录了过程，有的没有记录过程只记录了相关代码，大家若有兴趣可以自行测试。
主要采用set newname、convert datafile、dbms_file_transfer.copy_file、backup as copy datafile、alter database rename file这几种办法。
本次测试的表空间、磁盘组和os文件关系如下，文档中不再说明：
| 表空间名 | 磁盘组 | filesystem文件 |
|---|---|---|
| testdg | +DATA/orclasm/datafile/ | /home/oracle/ |
用的数据库环境：
1 2 3 4 5 6 7 8 9 10 11 | 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> |
os–>asm
rman + set newname+ open状态
1 2 3 4 5 6 7 8 9 | 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'; } |
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 | [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 ~]$ [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{ 2> sql 'alter tablespace testdg offline immediate'; 3> set newname for datafile 14 to'+DATA'; 4> restore tablespace testdg; 5> switch datafile 14; 6> recover tablespace testdg; 7> sql 'alter tablespace testdg online'; 8> } 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> 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> |
rman+backup as copy+mount状态
1 2 3 4 5 6 7 | run{ shutdown immediate; startup mount; backup as copy datafile 14 format '+DATA'; } SWITCH TABLESPACE testdg TO COPY; alter database open; |
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 | 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{ 2> shutdown immediate; 3> startup mount; 4> backup as copy datafile 14 format '+DATA'; 5> } 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> 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> |
dbms_file_transfer（推荐）
1 2 3 4 5 6 | 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 ; |
RMAN convert（推荐）
rman下：
1 | convert datafile '/home/oracle/testdg.dbf' format '+DATA'; |
必须为归档模式，否则会报错：ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
sql 下：
1 2 3 4 | 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; |
cp命令
11g的asm实例可以直接使用cp命令来实现，从asm实例的cp帮助信息上看，已经可以直接从本地数据库直接cp到远端数据库了！
1 2 3 4 5 6 7 8 9 10 | 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 ; |
copy + switch
1 2 3 4 5 6 7 | sql "alter database datafile 8 offline"; recover datafile 8; run { copy datafile '/home/oracle/a.dbf' to '+data'; } run { switch datafile '/home/oracle/a.dbf' to datafilecopy '+DATA/gbk/datafile/test.302.1120761841'; } sql "alter database datafile 8 online"; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
