【ASM】ASM資料檔案和OS檔案（FILESYSTEM）轉移方法總結
【ASM】ASM資料檔案和OS檔案（FILESYSTEM）轉移方法總結
blog文件結構圖：
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
SQL>
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 ~]$
[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> **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{
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> **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命令
11g的asm例項可以直接使用cp命令來實現，從asm例項的cp幫助資訊上看，已經可以直接從本地資料庫直接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 ;
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>
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.
SQL>
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{
2> shutdown immediate;
3> startup mount;
4> backup as copy datafile 14 format '/home/oracle/testdg.dbf';
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=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
RMAN>
3.3 rman convert +open狀態
使用rman的convert命令來實現，同樣適用於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 ~]$ vi 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.
SQL>
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{
2> shutdown immediate;
3> startup mount;
4> set newname for datafile 14 to '/home/oracle/testdg.dbf';
5> restore datafile 14;
6> switch datafile 14;
7> recover datafile 14;
8> alter database open;
9> }
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命令
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
[root@rhel6_lhr ~]#
alter database rename file'+DATA/orclasm/datafile/testdg.dbf' to '/home/oracle/testdg.dbf';
alter tablespace testdg online ;
4 總結
以上提供的各種辦法各有優缺點，有的需要重啟資料庫，有的不需要，有的全在rman中執行，有的需要在sql下執行，大家需仔細領悟，如有牛人可以列出表格總結一下各種方法的優缺點我將不勝感激。
About Me
...............................................................................................................................
● 本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用
● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文部落格園地址：http://www.cnblogs.com/lhrbest
● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群：230161599 微信群：私聊
● 聯絡我請加QQ好友(646634621)，註明新增緣由
● 於 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成
● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
...............................................................................................................................
拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-1400516/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle使用RMAN將普通資料檔案轉成ASMOracleASM
- ORACLE RAC ASM資料檔案遷移OMF檔案報錯ORA-01276解決OracleASM
- Oracle：ASM & 密碼檔案OracleASM密碼
- ASM叢集檔案系統ACFS（ASM Cluster File System）ASM
- IDA批量處理VirusShare樣本獲得asm檔案與bytes檔案ASM
- 將spfile從ASM裡遷移到檔案系統ASM
- ASM單例項安裝後，需要手動設定ASM的引數檔案ASM單例
- 12c複製 RAC ASM中的密碼檔案到檔案系統ASM密碼
- [20191128]11GR2 asm例項audit檔案.txtASM
- xbbed一鍵讀取ASM block到檔案系統ASMBloC
- 無法寫入檔案 Filesystem
- ASM有自己的引數、密碼、alert、監聽檔案ASM密碼
- SQLServer移動資料檔案SQLServer
- Oracle 12c的DG自動同步密碼檔案--ASM 新特性：共享密碼檔案Oracle密碼ASM
- go-filesystem 檔案管理器Go
- Oracle Linux 7使用syslog來管理Oracle ASM的審計檔案OracleLinuxASM
- 關於os認證和口令檔案認證(轉)
- Python檔案操作方法大總結Python
- linux下如何使用檔案來模擬硬碟進行ASM測試Linux硬碟ASM
- (12)caffe總結之影像資料轉換成db（leveldb/lmdb)檔案
- [20201218]資料檔案OS頭的修復.txt
- Oracle資料檔案和臨時檔案的管理Oracle
- git的gitignore檔案排除資料夾和檔案Git
- 遷移資料庫的檔案到不同路徑(轉)資料庫
- Hive表小檔案合併方法總結Hive
- FastDFS檔案系統遷移和資料恢復AST資料恢復
- PDF檔案轉HTML方法HTML
- 從定位資料塊所在ASM磁碟到ASM strippingASM
- MyBatis配置檔案總結MyBatis
- 重建共享(db或asm)密碼檔案 in Oracle 19c RAC-20220209ASM密碼Oracle
- GBFF檔案轉GFF檔案
- Xamarin Essentials應用教程檔案系統FileSystem
- ASM下遷移spfileASM
- 遷移ASM磁碟組ASM
- 【/proc/檔案淺析】另類辦法恢復資料檔案和控制檔案
- 12c pdb線上移動資料檔案或者重新命名資料檔案
- java 位元組流檔案複製方法總結Java
- 資料表結構更新後，遷移檔案怎麼使用？