oracle 11g r2 单实例 asm 数据库数据磁盘组迁移 在使用asm作为数据库存储的时候，可能往往我们会因为某些原因，会考虑将数据的存储数据迁移到一个新的磁盘组。 例如：创建数据库的时候，数据存储在一个external redundancy 的磁盘组，没有冗余镜像。后期我
oracle 11g r2 单实例 asm 数据库数据磁盘组迁移
在使用asm作为数据库存储的时候，可能往往我们会因为某些原因，会考虑将数据的存储数据迁移到一个新的磁盘组。
例如：创建数据库的时候，数据存储在一个external redundancy 的磁盘组，没有冗余镜像。后期我们会考虑将数据库数据迁移到一个normal redundancy 或者 high redundancy的磁盘组。
测试环境：
主机环境
sql> host lsb_release -a
lsb version: :core-4.0-amd64:core-4.0-ia32:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-ia32:printing-4.0-noarch
distributor id: enterpriseenterpriseserver
description: enterprise linux enterprise linux server release 5.7 (carthage)
release: 5.7
codename: carthage
数据库版本：
sql> select instance_name,version from v$instance;
instance_name version
---------------- -----------------
orcl 11.2.0.3.0
##单实例
asm版本
sql> select instance_name,version from v$instance;
instance_name version
---------------- -----------------
+asm 11.2.0.3.0
asm磁盘组共三个
sql> select name from v$asm_diskgroup;
name
------------------------------
backup
systemdg
data
目前数据数据存储于 systemdg ，包括（数据文件，临时文件，undo文件，redo文件、控制文件、参数文件） 本测试将数据迁移至data磁盘组,backup磁盘组为备份dest。环境交代完毕！
需要迁移的文件：数据文件，临时文件，undo文件，redo文件、控制文件、参数文件
迁移前，先记下数据库的dbid
开始迁移
#首先，consistent 关闭数据库sql> shutdown immediate;database closed.database dismounted.oracle instance shut down.#将数据库启动至mount状态sql> startup mount;oracle instance started.total system global area 542814208 bytesfixed size 2230152 bytesvariable size 180357240 bytesdatabase buffers 352321536 bytesredo buffers 7905280 bytesdatabase mounted.#启动rman工具，将数据库镜像备份至data磁盘组rman> backup as copy database format '+data';starting backup at 28-oct-13using target database control file instead of recovery catalogallocated channel: ora_disk_1channel ora_disk_1: sid=137 device type=diskallocated channel: ora_disk_2channel ora_disk_2: sid=16 device type=diskchannel ora_disk_1: starting datafile copyinput datafile file number=00001 name=+systemdg/orcl/datafile/system.261.829928875channel ora_disk_2: starting datafile copyinput datafile file number=00002 name=+systemdg/orcl/datafile/sysaux.262.829928875output file name=+data/orcl/datafile/system.257.830041439 tag=tag20131028t230354 recid=23 stamp=830041580channel ora_disk_1: datafile copy complete, elapsed time: 00:02:25channel ora_disk_1: starting datafile copyinput datafile file number=00003 name=+systemdg/orcl/datafile/undotbs1.263.829929061output file name=+data/orcl/datafile/sysaux.256.830041437 tag=tag20131028t230354 recid=22 stamp=830041580channel ora_disk_2: datafile copy complete, elapsed time: 00:02:26channel ora_disk_2: starting datafile copyinput datafile file number=00005 name=+systemdg/orcl/datafile/users01.264.829929061output file name=+data/orcl/datafile/undotbs1.258.830041581 tag=tag20131028t230354 recid=25 stamp=830041611channel ora_disk_1: datafile copy complete, elapsed time: 00:00:35channel ora_disk_1: starting datafile copyinput datafile file number=00004 name=+systemdg/orcl/datafile/users.265.829929107output file name=+data/orcl/datafile/users01.259.830041581 tag=tag20131028t230354 recid=24 stamp=830041611channel ora_disk_2: datafile copy complete, elapsed time: 00:00:35output file name=+data/orcl/datafile/users.260.830041617 tag=tag20131028t230354 recid=26 stamp=830041617channel ora_disk_1: datafile copy complete, elapsed time: 00:00:01finished backup at 28-oct-13starting control file and spfile autobackup at 28-oct-13piece handle=+backup/orcl/autobackup/2013_10_28/s_830041354.262.830041621 comment=nonefinished control file and spfile autobackup at 28-oct-13#完成copy#查看当前控制文件value，并修改为希望的值sql> show parameter control_filesname type value------------------------------------ ----------- ------------------------------control_files string +systemdg/orcl/control01.ctl, +backup/orcl/controlfile/curre nt.256.829519259 sql> alter system set control_files='+data','+backup/orcl/controlfile/current.256.829519259' scope=spfile;system altered.#关闭数据库，让修改的control_files参数生效sql> shutdown immediate;ora-01109: database not opendatabase dismounted.oracle instance shut down. #将数据库启动至nomount阶段sql> startup nomount;oracle instance started.total system global area 542814208 bytesfixed size 2230152 bytesvariable size 180357240 bytesdatabase buffers 352321536 bytesredo buffers 7905280 bytes#打开rman工具，通过设置dbid的方式，恢复控制文件，此时，数据库会根据control_files设置的值，将控制文件恢复到指定的位置。[oracle@linusfay-up ~]$ rman target /recovery manager: release 11.2.0.3.0 - production on mon oct 28 23:56:54 2013copyright (c) 1982, 2011, oracle and/or its affiliates. all rights reserved.connected to target database: orcl (not mounted)rman> set dbid=1357299984executing command: set dbid#从自动备份中恢复控制文件rman> restore controlfile from autobackup;starting restore at 28-oct-13using target database control file instead of recovery catalogallocated channel: ora_disk_1channel ora_disk_1: sid=136 device type=diskrecovery area destination: +backupdatabase name (or database unique name) used for search: orclchannel ora_disk_1: autobackup +backup/orcl/autobackup/2013_10_28/s_830041354.262.830041621 found in the recovery areachannel ora_disk_1: looking for autobackup on day: 20131028channel ora_disk_1: restoring control file from autobackup +backup/orcl/autobackup/2013_10_28/s_830041354.262.830041621channel ora_disk_1: control file restore from autobackup completeoutput file name=+data/orcl/controlfile/current.261.830042181output file name=+backup/orcl/controlfile/current.256.829519259finished restore at 28-oct-13#控制文件恢复成功，将数据库启动至mount阶段rman> alter database mount;#查看之前备份的数据库镜像rman> list copy of database;list of datafile copies=======================key file s completion time ckp scn ckp time ------- ---- - --------------- ---------- ---------------23 1 a 28-oct-13 1087234 28-oct-13 name: +data/orcl/datafile/system.257.830041439 tag: tag20131028t23035422 2 a 28-oct-13 1087234 28-oct-13 name: +data/orcl/datafile/sysaux.256.830041437 tag: tag20131028t23035425 3 a 28-oct-13 1087234 28-oct-13 name: +data/orcl/datafile/undotbs1.258.830041581 tag: tag20131028t23035426 4 a 28-oct-13 1087234 28-oct-13 name: +data/orcl/datafile/users.260.830041617 tag: tag20131028t23035424 5 a 28-oct-13 1087234 28-oct-13 name: +data/orcl/datafile/users01.259.830041581 tag: tag20131028t230354database mountedreleased channel: ora_disk_1 #将数据库switch至database copyrman> switch database to copy;starting implicit crosscheck backup at 28-oct-13allocated channel: ora_disk_1channel ora_disk_1: sid=136 device type=diskallocated channel: ora_disk_2channel ora_disk_2: sid=16 device type=diskcrosschecked 5 objectsfinished implicit crosscheck backup at 28-oct-13starting implicit crosscheck copy at 28-oct-13using channel ora_disk_1using channel ora_disk_2crosschecked 5 objectsfinished implicit crosscheck copy at 28-oct-13searching for all files in the recovery areacataloging files...cataloging donelist of cataloged files=======================file name: +backup/orcl/autobackup/2013_10_28/s_830041354.262.830041621file name: +backup/orcl/autobackup/2013_10_27/s_829931410.271.829931413file name: +backup/orcl/autobackup/2013_10_27/s_829920770.278.829920963file name: +backup/orcl/autobackup/2013_10_27/s_829920770.276.829921283datafile 1 switched to datafile copy +data/orcl/datafile/system.257.830041439datafile 2 switched to datafile copy +data/orcl/datafile/sysaux.256.830041437datafile 3 switched to datafile copy +data/orcl/datafile/undotbs1.258.830041581datafile 4 switched to datafile copy +data/orcl/datafile/users.260.830041617datafile 5 switched to datafile copy +data/orcl/datafile/users01.259.830041581 #ok，完成数据文件的switch#恢复数据库rman> recover database;#完成恢复，以resetlogs方式打开数据库。rman> alter database open resetlogs;database openedsql> select name from v$datafile;name--------------------------------------------------------------------------------+data/orcl/datafile/system.257.830041439+data/orcl/datafile/sysaux.256.830041437+data/orcl/datafile/undotbs1.258.830041581+data/orcl/datafile/users.260.830041617+data/orcl/datafile/users01.259.830041581#数据库打开成功，接下里就是迁移 tempfile，redo lofile，修改 数据库db_create_file_dest=+datasql> create temporary tablespace temp tempfile '+data';tablespace created.sql> select file_name from dba_temp_files;file_name--------------------------------------------------------------------------------+systemdg/orcl/tempfile/temp1.267.830034603+data/orcl/tempfile/temp.262.830042531#修改默认临时表空间，并删除废弃的临时表空间以及数据文件sql> alter database default temporary tablespace temp;database altered.sql> drop tablespace temp1 including contents and datafiles;tablespace dropped.sql> show parameter control_filesname type value------------------------------------ ----------- ------------------------------control_files string +data/orcl/controlfile/current .261.830042181, +backup/orcl/c ontrolfile/current.256.8295192 59#修改db_create_file_destsql> show parameter db_create_file_destname type value------------------------------------ ----------- ------------------------------db_create_file_dest string +systemdgsql> alter system set db_create_file_dest='+data' scope=spfile;system altered. #重启数据库，让参数生效sql> shutdown immediatedatabase closed.database dismounted.oracle instance shut down.sql> startupora-32004: obsolete or deprecated parameter(s) specified for rdbms instanceoracle instance started.total system global area 542814208 bytesfixed size 2230152 bytesvariable size 180357240 bytesdatabase buffers 352321536 bytesredo buffers 7905280 bytesdatabase mounted.database opened. #最后 迁移redo logfile 采用 新加成员 删除废弃成员的方式来迁移sql> select member from v$logfile;member--------------------------------------------------------------------------------+backup/orcl/onlinelog/group_1.257.829519279+systemdg/orcl/onlinelog/group_1.268.830035813+backup/orcl/onlinelog/group_2.258.829519305+systemdg/orcl/onlinelog/group_2.269.830035825+backup/orcl/onlinelog/group_3.259.829519331+systemdg/orcl/onlinelog/group_3.270.8300358356 rows selected. sql> alter database add logfile member '+data' to group 1;database altered.sql> alter database add logfile member '+data' to group 2;database altered.sql> alter database add logfile member '+data' to group 3;database altered.sql> alter system switch logfile;system altered.sql> alter system checkpoint;system altered.sql> alter database drop logfile member '+systemdg/orcl/onlinelog/group_1.268.830035813';database altered.#迁移参数文件sql> create pfile='$oracle_home/dbs/init_orcl.ora' from spfile;file created.sql> shutdown immediate;asm diskgroups dismountedasm instance shutdownsql> startup pfile='$oracle_home/dbs/init_orcl.ora';asm instance startedtotal system global area 283930624 bytesfixed size 2227664 bytesvariable size 256537136 bytesasm cache 25165824 bytesasm diskgroups mountedsql> create spfile ='+data' from pfile='$oracle_home/dbs/init_orcl.ora';file created.sql> show parameter pfilename type value------------------------------------ ----------- ------------------------------spfile string +data/asm/asmparameterfile/reg istry.253.830084811
#迁移redo logfile的时候，删除的时候，可能会因为是current 或者active状态无法删除，此时可根据实际情况
#使用 alter system switch logfile 或者 alter system checkpoint 来改变日志组的状态来达到删除的目的。#至此,数据库存储从systemdg diskgroup 迁移到 data磁盘组完成