更改数据文件由文件系统转换为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.
----结束