oracle 11g R2 单实例 ASM 数据库数据磁盘组迁移
来源：互联网 发布：multimap json 编辑：程序博客网 时间：2024/04/27 17:46
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.830041439"datafile 2 switched to datafile copy "+DATA/orcl/datafile/sysaux.256.830041437"datafile 3 switched to datafile copy "+DATA/orcl/datafile/undotbs1.258.830041581"datafile 4 switched to datafile copy "+DATA/orcl/datafile/users.260.830041617"datafile 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磁盘组完成
- oracle 11g R2 单实例 ASM 数据库数据磁盘组迁移
- oracle 11g asm单实例迁移文件系统
- 11g R2 +ASM 单实例安装
- 连接 Oracle 11G R2 ASM实例
- Linux 创建 Oracle 11g r2 asm磁盘出错
- Oracle 11G R2 RAC 添加节点,添加asm磁盘
- oracle 11g 从+DATA迁移到+newdata ASM 磁盘组迁移
- oracle 11g 单节点安装 ASM，找不到ASM磁盘组
- Oracle 10g R2 删除ASM实例
- oracle 11g R2 单实例 ASM 启动：ORA-29701:+unable+to+connect+to+Cluster+Synchronization+Service
- oracle 11g R2 单实例 ASM 启动：ORA-29701:+unable+to+connect+to+Cluster+Synchronization+Service
- ORACLE 11g将数据库移动到不同的ASM磁盘组/修改ASM磁盘组的冗余属性
- ASM磁盘数据迁移
- CentOS 5.6 上安装 Oracle 11g R2 单实例数据库详解
- Oracle 11g R2 rac通过rman 恢复到单实例数据库
- Oracle 11g单实例使用ASM注意事项
- Oracle 11g R2 ASM:了解 Oracle ASM 基本概念
- oracle RAC 更换存储迁移数据(在线迁移ASM磁盘组）测试
- Action
- poj 3498 March of the Penguins（满足点容量限制的网络流）
- tcpclient tcpserver
- 前端与后台快速联调
- Leetocde: Palindrome Partitioning II
- oracle 11g R2 单实例 ASM 数据库数据磁盘组迁移
- JAVG的学习方法！
- 英文聽力練習網站(有字幕)
- Java 集合学习<7>Collections
- [android篇]IntentFilter简介
- struts的<s:select>标签
- 泛型
- java的学习路线！
- 算法工程师的爱与哀愁
