如何将Oracle的数据文件由文件系统转换为ASM格式

发布时间:  2016-03-09 浏览次数:  426 下载次数:  0
问题描述
在Oracle数据库采用XTTS方法从小机迁移到X86后,在目标库上如何将数据文件由文件系统转换成ASM格式?
解决方案

更改数据文件由文件系统转换为ASM格式。有以下几种方式:
1. 采用PL/SQL下面的DBMS_FILE_TRANSFER命令转换格式;
2. 采用RMAN来转换数据文件格式。

先介绍第一种方式:通过PL/SQL下面的DBMS_FILE_TRANSFER命令,将datafile转换为ASM磁盘。以下为转储步骤参考。
步骤 1 查看并创建directory
SQL>select directory_name, directory_path from dba_directories;
SQL> create directory D_OUTPUT as '/home/oracle/tts_transfer';
SQL> create directory D_ASM_SPFILE as '+DATA/ORA11G/DATAFILE';
oracle@ogg01:~> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Feb 27 02:36:36 2016

Copyright (c) 1982, 2009, Oracle.All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> 
SQL> 
SQL> col directory_path format a50 
SQL>select directory_name, directory_path from dba_directories;

DIRECTORY_NAME
------------------------------
DIRECTORY_PATH
--------------------------------------------------
XMLDIR
/ade/b/2125410156/oracle/rdbms/xml

DATA_PUMP_DIR
/u01/app/oracle/admin/ora11g/dpdump/

ORACLE_OCM_CONFIG_DIR
/u01/app/oracle/product/11.2.0/db_1/ccr/state


SQL> create directory D_OUTPUT as '/home/oracle/tts_transfer';

Directory created.

SQL> create directory D_ASM_SPFILE as '+DATA/ORA11G/DATAFILE';

Directory created.

SQL> select directory_name, directory_path from dba_directories;

DIRECTORY_NAME
------------------------------
DIRECTORY_PATH
--------------------------------------------------
D_ASM_SPFILE
+DATA/ORA11G/DATAFILE

XMLDIR
/ade/b/2125410156/oracle/rdbms/xml

D_OUTPUT
/home/oracle/tts_transfer


DIRECTORY_NAME
------------------------------
DIRECTORY_PATH
--------------------------------------------------
DATA_PUMP_DIR
/u01/app/oracle/admin/ora11g/dpdump/

ORACLE_OCM_CONFIG_DIR
/u01/app/oracle/product/11.2.0/db_1/ccr/state
步骤 2 采用dbms_file_transfer执行文件格式转换。
SQL> exec dbms_file_transfer.copy_file('D_OUTPUT','data_D-ORA11G_I-404909_TS-TEST_OGG_FNO-7_0rquqb6g','D_ASM_SPFILE', 'TEST_OGG');

SQL> exec dbms_file_transfer.copy_file('D_OUTPUT','data_D-ORA11G_I-404909_TS-TEST_OGG_FNO-7_0rquqb6g','D_ASM_SPFILE', 'TEST_OGG');

PL/SQL procedure successfully completed.

步骤 3 将目标库的数据文件重命名
SQL> startup mount;
SQL> alter database rename file '+DATA/ORA11G/DATAFILE/TEST_OGG.304.904876309' to '+DATA/ORA11G/DATAFILE/TEST_OGG';

SQL> startup mount;
ORACLE instance started.

Total System Global Area 2.0310E+10 bytes
Fixed Size2212976 bytes
Variable Size1.3153E+10 bytes
Database Buffers6979321856 bytes
Redo Buffers175132672 bytes
Database mounted.

SQL> alter database rename file '+DATA/ORA11G/DATAFILE/TEST_OGG.304.904876309' to '+DATA/ORA11G/DATAFILE/TEST_OGG';

Database altered.
步骤 4 启动数据库,重新查看数据文件路径是否更新。本例中,数据库启动时需要执行recover操作。
SQL> alter database open;
SQL> recover
SQL> select tablespace_name, file_name from dba_data_files;
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '+DATA/ora11g/datafile/tbs_ogg'



SQL> recover
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
USERS
+DATA/ora11g/datafile/users.308.904876313

UNDOTBS1
+DATA/ora11g/datafile/undotbs1.305.904876309

SYSAUX
+DATA/ora11g/datafile/sysaux.293.904876293


TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
SYSTEM
+DATA/ora11g/datafile/system.292.904876293

TBS_OGG
+DATA/ora11g/datafile/tbs_ogg

TEST_OGG
+DATA/ora11g/datafile/test_ogg


6 rows selected.

由上看出数据文件路径已经更新到ASM磁盘上。
----结束


再介绍第二种方式,通过RMAN转换将datafile转换由文件系统转换为ASM磁盘。以下为转储步骤参考。
步骤 1 进入MOUNT状态
oracle@ogg01:~> rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Feb 27 02:25:40 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area20310007808 bytes

Fixed Size2212976 bytes
Variable Size13153340304 bytes
Database Buffers6979321856 bytes
Redo Buffers175132672 bytes
步骤 2 执行以下命令
RMAN> 
run{

allocate channel c1 device type disk;

allocate channel c2 device type disk;

backup as copy database format '+DATA';

}
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 

allocated channel: c1
channel c1: SID=162 device type=DISK

allocated channel: c2
channel c2: SID=178 device type=DISK

Starting backup at 2016-02-27 02:31:33
channel c1: starting datafile copy
input datafile file number=00001 name=+DATA/ora11g/datafile/system.296.904790983
channel c2: starting datafile copy
input datafile file number=00002 name=+DATA/ora11g/datafile/sysaux.301.904790983
output file name=+DATA/ora11g/datafile/system.292.904876293 tag=TAG20160227T023133 RECID=2 STAMP=904876305
channel c1: datafile copy complete, elapsed time: 00:00:15
channel c1: starting datafile copy
input datafile file number=00006 name=/home/oracle/tts_transfer/data_D-ORA11G_I-404909_TS-TEST_OGG_FNO-7_0rquqb6g
output file name=+DATA/ora11g/datafile/sysaux.293.904876293 tag=TAG20160227T023133 RECID=1 STAMP=904876304
channel c2: datafile copy complete, elapsed time: 00:00:15
channel c2: starting datafile copy
input datafile file number=00003 name=+DATA/ora11g/datafile/undotbs1.300.904790983
output file name=+DATA/ora11g/datafile/test_ogg.304.904876309 tag=TAG20160227T023133 RECID=4 STAMP=904876310
channel c1: datafile copy complete, elapsed time: 00:00:03
channel c1: starting datafile copy
input datafile file number=00005 name=/home/oracle/tts_transfer/data_D-ORA11G_I-404909_TS-TBS_OGG_FNO-6_0pquqb5r
output file name=+DATA/ora11g/datafile/undotbs1.305.904876309 tag=TAG20160227T023133 RECID=3 STAMP=904876310
channel c2: datafile copy complete, elapsed time: 00:00:03
channel c2: starting datafile copy
copying current control file
output file name=+DATA/ora11g/datafile/tbs_ogg.306.904876311 tag=TAG20160227T023133 RECID=5 STAMP=904876312
channel c1: datafile copy complete, elapsed time: 00:00:01
channel c1: starting datafile copy
input datafile file number=00004 name=+DATA/ora11g/datafile/users.299.904790983
output file name=+DATA/ora11g/controlfile/backup.307.904876311 tag=TAG20160227T023133 RECID=6 STAMP=904876312
channel c2: datafile copy complete, elapsed time: 00:00:02
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c2: starting piece 1 at 2016-02-27 02:31:53
output file name=+DATA/ora11g/datafile/users.308.904876313 tag=TAG20160227T023133 RECID=7 STAMP=904876313
channel c1: datafile copy complete, elapsed time: 00:00:01
channel c2: finished piece 1 at 2016-02-27 02:31:54
piece handle=+DATA/ora11g/backupset/2016_02_27/nnsnf0_tag20160227t023133_0.309.904876313 tag=TAG20160227T023133 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-02-27 02:31:54
released channel: c1
released channel: c2

步骤 3 切换数据文件
RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/ora11g/datafile/system.292.904876293"
datafile 2 switched to datafile copy "+DATA/ora11g/datafile/sysaux.293.904876293"
datafile 3 switched to datafile copy "+DATA/ora11g/datafile/undotbs1.305.904876309"
datafile 4 switched to datafile copy "+DATA/ora11g/datafile/users.308.904876313"
datafile 5 switched to datafile copy "+DATA/ora11g/datafile/tbs_ogg.306.904876311"
datafile 6 switched to datafile copy "+DATA/ora11g/datafile/test_ogg.304.904876309"
步骤 4 恢复和打开数据库,查看数据文件是否转换成功
RMAN> recover database;

Starting recover at 2016-02-27 02:35:35
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=162 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 2016-02-27 02:35:36

RMAN> alter database open;

database opened

oracle@ogg01:~> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Feb 27 02:36:36 2016

Copyright (c) 1982, 2009, Oracle.All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
USERS
+DATA/ora11g/datafile/users.308.904876313

UNDOTBS1
+DATA/ora11g/datafile/undotbs1.305.904876309

SYSAUX
+DATA/ora11g/datafile/sysaux.293.904876293


TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
SYSTEM
+DATA/ora11g/datafile/system.292.904876293

TBS_OGG
+DATA/ora11g/datafile/tbs_ogg.306.904876311

TEST_OGG
+DATA/ora11g/datafile/test_ogg.304.904876309


6 rows selected.
----结束

END