数据库|mysql教程
oracle,11g,单实例,实例,ASM,数据库,数据,磁
数据库-mysql教程
商家联盟源码,安装ubuntu建立引导,tomcat相关服务器,hbase爬虫数据,什么值得买 php,张家界360seo优化成交价lzw
oracle 11g R2 单实例 ASM 数据库数据磁盘组迁移 在使用ASM作为数据库存储的时候，可能往往我们会因为某些原因，会考虑将数据的存储数据迁移到一个新的磁盘组。 例如：创建数据库的时候，数据存储在一个External redundancy 的磁盘组，没有冗余镜像。后期我
企业产品官网源码,最新的ubuntu版本,爬虫中extract作用,php 5.4.28,大理seo排名lzw
弹性云 销售 源码,Vscode语音包插件,Ubuntu防macos,tomcat文件过大,sqlite3异步写入问题,出租屋地上有很多小爬虫,php循环字符串,珠海品牌seo推广公司,免费网站采集软件,主题页模板免费下载lzw
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 Buffers352321536 bytesRedo Buffers7905280 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=TAG1028T230354 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=TAG1028T230354 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=TAG1028T230354 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=TAG1028T230354 RECID=24 STAMP=830041611channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:35output file name=+DATA/orcl/datafile/users.260.830041617 tag=TAG1028T230354 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/_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 Buffers352321536 bytesRedo Buffers7905280 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 Copyright (c) 1982, , 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/_10_28/s_830041354.262.830041621 found in the recovery areachannel ORA_DISK_1: looking for AUTOBACKUP on day: 1028channel ORA_DISK_1: restoring control file from AUTOBACKUP +backup/ORCL/AUTOBACKUP/_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=======================KeyFile S Completion Time Ckp SCN Ckp Time ------- ---- - --------------- ---------- ---------------231 A 28-OCT-13 1087234 28-OCT-13 Name: +DATA/orcl/datafile/system.257.830041439 Tag: TAG1028T230354222 A 28-OCT-13 1087234 28-OCT-13 Name: +DATA/orcl/datafile/sysaux.256.830041437 Tag: TAG1028T230354253 A 28-OCT-13 1087234 28-OCT-13 Name: +DATA/orcl/datafile/undotbs1.258.830041581 Tag: TAG1028T230354264 A 28-OCT-13 1087234 28-OCT-13 Name: +DATA/orcl/datafile/users.260.830041617 Tag: TAG1028T230354245 A 28-OCT-13 1087234 28-OCT-13 Name: +DATA/orcl/datafile/users01.259.830041581 Tag: TAG1028T230354database 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/_10_28/s_830041354.262.830041621File Name: +backup/ORCL/AUTOBACKUP/_10_27/s_829931410.271.829931413File Name: +backup/ORCL/AUTOBACKUP/_10_27/s_829920770.278.829920963File Name: +backup/ORCL/AUTOBACKUP/_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-3: obsolete or deprecated parameter(s) specified for RDBMS instanceORACLE instance started.Total System Global Area 542814208 bytesFixed Size 2230152 bytesVariable Size 180357240 bytesDatabase Buffers352321536 bytesRedo Buffers7905280 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------------------------------------ ----------- ------------------------------spfilestring+DATA/asm/asmparameterfile/reg istry.253.830084811
#迁移redo logfile的时候，删除的时候，可能会因为是current 或者active状态无法删除，此时可根据实际情况
#使用 alter system switch logfile 或者 alter system checkpoint 来改变日志组的状态来达到删除的目的。
#至此,数据库存储从SYSTEMDG DISKGROUP 迁移到 DATA磁盘组完成
