Oracle 11g R2 RAC with ASM存储迁移-手记

1、Oracle 11g R2 RAC with ASM存储迁移【手记】2016.06【摘要】Oracle数据库文件部署在ASM上，需要尽量短的停机时间完成此次存储更换。由于不涉及异构的迁移转换，迁移起来也不难，无需借助三方的工具来完成这次高可用切换。因此使用迁移ASM DISKGROUP 的方式完成存储迁移， 该方法实现迁移过程中尽量缩短系统的停机时间。【正文】首先，介绍迁移的简单过程。步骤如下：1) 划分asm disk，并检查或更改UDEV配置文件，使得新存储的asm disk对ASM实例可识别。2) 备份OCR、Voting Disk、ASM disk header和数据库。3)

2、 创建新的DISKGROUP4) 迁移OCR和Vote Disks到新磁盘组（ASM diskgroup）5) 迁移ASM Spfile到新磁盘组（ASM diskgroup）6) 迁移数据库相关文件至新磁盘组（ASM diskgroup）7) 在线修改数据库参数文件（归档路径、闪回等）8) 删除旧磁盘组9) 观察期。10) 执行数据库备份本文重点介绍ASM的热添加和删除磁盘技术，所以第1、2步在此不列出，由第3步开始。一、添加ASM磁盘组下面开始添加asm磁盘，这里使用ASM的REBALANCE技术来解决在线迁移数据。查看当前ASM磁盘信息登录到ASM实例，查看当前磁盘组的信息：gridra

3、c1 $ sqlplus / as sysasmSQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 22 09:08:26 2016Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Real Application Clusters and Automatic Storage Managemen

4、t optionsSQL> select instance_name from v$instance;INSTANCE_NAME-+ASM1SQL> col name for a10SQL> set linesize 150SQL> select NAME, ALLOCATION_UNIT_SIZE, STATE, TOTAL_MB, FREE_MB USABLE_FILE_MB from gv$asm_diskgroup;NAME ALLOCATION_UNIT_SIZE STATE TOTAL_MB USABLE_FILE_MB- - - - -DATA 10485

5、76 MOUNTED 10240 6721FRA 1048576 MOUNTED 13312 11081SYSTEMDG 1048576 MOUNTED 25600 24344DATA 1048576 MOUNTED 10240 6721FRA 1048576 MOUNTED 13312 11081SYSTEMDG 1048576 MOUNTED 25600 243446 rows selected.SQL> select failgroup, name from v$asm_disk where group_number=(select group_number from v$asm_

6、diskgroup where name = 'DATA');FAILGROUP NAME- -DATA_0001 DATA_0001DATA_0000 DATA_0000利用asmca命令创建DISKGROUP相关的sql语句SQL> CREATE DISKGROUP DATA2 EXTERNAL REDUNDANCY DISK '/dev/asm-diskk' SIZE 20480M ATTRIBUTE 'compatible.asm'='11.2.0.0.0','au_size'='1M'

7、; /* ASMCA */SQL> CREATE DISKGROUP FRA2 EXTERNAL REDUNDANCY DISK '/dev/asm-diskl' SIZE 5120M ATTRIBUTE 'compatible.asm'='11.2.0.0.0','au_size'='1M' /* ASMCA */磁盘组状态ora.DATA.dg ONLINE ONLINE rac1 ONLINE ONLINE rac2 ora.DATA2.dg ONLINE ONLINE rac1 ONLINE ONLI

8、NE rac2 ora.FRA.dg ONLINE ONLINE rac1 ONLINE ONLINE rac2 ora.FRA2.dg ONLINE ONLINE rac1 ONLINE ONLINE rac2 ora.LISTENER.lsnr ONLINE ONLINE rac1 ONLINE OFFLINE rac2 ora.SYSTEMDG.dg ONLINE ONLINE rac1 ONLINE ONLINE rac2 ora.asm ONLINE ONLINE rac1 Started ONLINE ONLINE rac2 Started gridrac1 $ sqlplus /

9、 as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 22 10:02:37 2016Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Real Application Clusters and Automatic Storage Management optionsSQL&g

10、t; select name,state from v$asm_diskgroup;NAME STATE- -DATA MOUNTEDFRA MOUNTEDSYSTEMDG MOUNTEDDATA2 MOUNTEDFRA2 MOUNTED二、 迁移OCR和Vote Disks到新磁盘组（ASM diskgroup）查看ocr信息：gridrac1 $ ocrcheckStatus of Oracle Cluster Registry is as follows : Version : 3 Total space (kbytes) : 262120 Used space (kbytes) : 2

11、916 Available space (kbytes) : 259204 ID : 997200134 Device/File Name : +SYSTEMDG Device/File integrity check succeeded Device/File not configured Device/File not configured Device/File not configured Device/File not configured Cluster registry integrity check succeeded Logical corruption check bypa

12、ssed due to non-privileged user添加ocr磁盘组rootrac1 bin# ./ocrconfig -add +DATA2rootrac1 bin# ./ocrcheckStatus of Oracle Cluster Registry is as follows : Version : 3 Total space (kbytes) : 262120 Used space (kbytes) : 2916 Available space (kbytes) : 259204 ID : 997200134 Device/File Name : +SYSTEMDG Dev

13、ice/File integrity check succeeded Device/File Name : +DATA2 Device/File integrity check succeeded Device/File not configured Device/File not configured Device/File not configured Cluster registry integrity check succeeded Logical corruption check succeeded删除OCR旧磁盘组rootrac1 bin# ./ocrconfig -delete

14、+SYSTEMDGrootrac1 bin# ./ocrcheckStatus of Oracle Cluster Registry is as follows : Version : 3 Total space (kbytes) : 262120 Used space (kbytes) : 2916 Available space (kbytes) : 259204 ID : 997200134 Device/File Name : +DATA2 Device/File integrity check succeeded Device/File not configured Device/F

15、ile not configured Device/File not configured Device/File not configured Cluster registry integrity check succeeded查看Votedisk信息，迁移至新磁盘组gridrac1 $ crsctl query css votedisk# STATE File Universal Id File Name Disk group- - - - - 1. ONLINE b0746a43f93c4ff2bf067cb97ffedf4e (/dev/asm-diskb) SYSTEMDG 2. O

16、NLINE a54ec305b2c94febbf10c7426bad5ab8 (/dev/asm-diskc) SYSTEMDG 3. ONLINE 3b9ff9c1f7884f02bfedb22d5cdfc463 (/dev/asm-diskd) SYSTEMDG 4. ONLINE a4c764cb429e4fcdbf4ac458b9f51803 (/dev/asm-diske) SYSTEMDG 5. ONLINE 23f5f064b0734f9bbf486a9d3a6df62f (/dev/asm-diskf) SYSTEMDGLocated 5 voting disk(s).grid

17、rac1 $ crsctl replace votedisk +DATA2Successful addition of voting disk 06d88831dc8b4fa0bf0213c802aeb8d8.Successful deletion of voting disk b0746a43f93c4ff2bf067cb97ffedf4e.Successful deletion of voting disk a54ec305b2c94febbf10c7426bad5ab8.Successful deletion of voting disk 3b9ff9c1f7884f02bfedb22d

18、5cdfc463.Successful deletion of voting disk a4c764cb429e4fcdbf4ac458b9f51803.Successful deletion of voting disk 23f5f064b0734f9bbf486a9d3a6df62f.Successfully replaced voting disk group with +DATA2.CRS-4266: Voting file(s) successfully replacedgridrac1 $ crsctl query css votedisk# STATE File Universa

19、l Id File Name Disk group- - - - - 1. ONLINE 06d88831dc8b4fa0bf0213c802aeb8d8 (/dev/asm-diskk) DATA2Located 1 voting disk(s).三、Moving server side ASM SPfile to new ASM diskgroup查看ASM SPFILE 信息，迁移到新磁盘组 gridrac1 $ sqlplus / as sysasmSQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 22 10:16:53 2016Co

20、pyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Real Application Clusters and Automatic Storage Management optionsSQL> show parameter spfileNAME TYPE VALUE- - -spfile string +SYSTEMDG/vmac-cluste

21、r/asmpara meterfile/registry.253.8654292 27验证spfile路径ASMCMD> spget +SYSTEMDG/vmac-cluster/asmparameterfile/registry.253.865429227+SYSTEMDG/vmac-cluster/asmparameterfile/registry.253.865429227ASMCMD> spget+SYSTEMDG/vmac-cluster/asmparameterfile/registry.253.865429227SQL> create pfile='/t

22、mp/asm_pfile.ora' from spfileSQL> create spfile='+DATA2' from pfile='/tmp/asm_pfile.ora'File created.New SPfile location will be logged on the ASM alert logNOTE: updated gpnp profile ASM SPFILE to +DATA2/dbatst-scan/asmparameterfile/registry.253.828801675gridrac1 trace$ tail a

23、lert_+ASM1.logWed Jun 22 10:41:55 2016NOTE: updated gpnp profile ASM diskstring: /dev/asm*NOTE: updated gpnp profile ASM diskstring: /dev/asm*NOTE: updated gpnp profile ASM SPFILE to +DATA2/vmac-cluster/asmparameterfile/registry.253.915187315gridrac1 $ asmcmdASMCMD> spget+DATA2/vmac-cluster/asmpa

24、rameterfile/registry.253.915187315ASMCMD> spget +DATA2/vmac-cluster/asmparameterfile/registry.253.915187315+DATA2/vmac-cluster/asmparameterfile/registry.253.915187315ASMCMD>四、 Moving database related files to new ASM diskgroup控制文件迁移SQL> show parameter controlNAME TYPE VALUE- - -control_file

25、_record_keep_time integer 7control_files string +DATA/dcdb/controlfile/current .256.865439483, +FRA/dcdb/cont rolfile/current.256.865439483SQL> create pfile='/home/oracle/pfile.ora' from spfile;File created.SQL> alter system set control_files='+DATA2','+FRA2' scope=spfi

26、le sid='*'System altered.oraclerac1 $ srvctl stop database -d dcdb启动实例1到nomount状态oraclerac1 $ srvctl start instance -d dcdb -i dcdb1 -o nomountoraclerac1 $ srvctl status instance -d dcdb -i dcdb1Instance dcdb1 is running on node rac1oraclerac1 $ rman target /Recovery Manager: Release 11.2.0.

27、3.0 - Production on Wed Jun 22 10:56:08 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: DCDB (not mounted)RMAN> restore controlfile from '+DATA/dcdb/controlfile/current.256.865439483'2> ;Starting restore at 22-JUN-16using ta

28、rget database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=37 instance=dcdb1 device type=DISKchannel ORA_DISK_1: copied control file copyoutput file name=+DATA2/dcdb/controlfile/current.256.915188239output file name=+FRA2/dcdb/controlfile/current.256.9

29、15188241Finished restore at 22-JUN-16oraclerac1 $ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 22 10:58:24 2016Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning

30、, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsSQL> alter database mount 2 ;Database altered.SQL> alter database open ;Database altered.SQL> show parameter controlNAME TYPE VALUE- - -control_file_record_keep_time integer 7cont

31、rol_files string +DATA2/dcdb/controlfile/curren t.256.915188239, +FRA2/dcdb/co ntrolfile/current.256.91518824 1五. Moving SPfile to new ASM diskgroup查看参数文件SQL> show parameter spfileNAME TYPE VALUE- - -spfile string +DATA/dcdb/spfiledcdb.oraSQL> create pfile='/tmp/pfile_db.ora' from spfi

32、le;SQL> create spfile='+DATA2' from pfile='/tmp/pfile_db.ora'gridrac1 trace$ asmcmdASMCMD> lsDATA/DATA2/FRA/FRA2/SYSTEMDG/ASMCMD> cd data2ASMCMD> lsDCDB/vmac-cluster/ASMCMD> cd dcdbASMCMD> lsCONTROLFILE/PARAMETERFILE/ASMCMD> cd parameerfileASMCMD-8002: entry '

33、;parameerfile' does not exist in directory '+data2/dcdb/'lASMCMD> sCONTROLFILE/PARAMETERFILE/ASMCMD> cd parameterfileASMCMD> lsspfile.257.915188699ASMCMD> mkalias +DATA2/dcdb/parameterfile/spfile.257.915188699 spfiledcdb.oraASMCMD> ls -lType Redund Striped Time Sys Name Y

34、CONTROLFILE/ Y PARAMETERFILE/ N spfiledcdb.ora => +DATA2/DCDB/PARAMETERFILE/spfile.257.915188699gridrac1 trace$ srvctl config database -d dcdbDatabase unique name: dcdbDatabase name: dcdbOracle home: /s01/oracle/app/oracle/product/11.2.0/dbhome_1Oracle user: oracleSpfile: +DATA/dcdb/spfiledcdb.or

35、aDomain: Start options: openStop options: immediateDatabase role: PRIMARYManagement policy: AUTOMATICServer pools: dcdbDatabase instances: dcdb1,dcdb2Disk Groups: DATA,FRA,DATA2,FRA2Mount point paths: Services: Type: RACDatabase is administrator managedoraclerac1 $ srvctl config database -d dcdbData

36、base unique name: dcdbDatabase name: dcdbOracle home: /s01/oracle/app/oracle/product/11.2.0/dbhome_1Oracle user: oracleSpfile: +DATA2/dcdb/spfiledcdb.oraDomain: Start options: openStop options: immediateDatabase role: PRIMARYManagement policy: AUTOMATICServer pools: dcdbDatabase instances: dcdb1,dcd

37、b2Disk Groups: DATA,FRA,DATA2,FRA2Mount point paths: Services: Type: RACDatabase is administrator managedModify the pfiles that give reference to the SPFile in $ORACLE_HOME/dbsoraclerac1 $ cd $ORACLE_HOME/dbsoraclerac1 dbs$ lshc_dcdb1.dat initdcdb1.ora init.ora orapwdcdb1 snapcf_dcdb1.foraclerac1 db

38、s$ cat initdcdb1.ora SPFILE='+DATA/dcdb/spfiledcdb.ora'编辑后浏览oraclerac1 dbs$ cat initdcdb1.ora SPFILE='+DATA2/dcdb/spfiledcdb.ora'oraclerac2 dbs$ cat initdcdb2.ora SPFILE='+DATA/dcdb/spfiledcdb.ora'修改成如下：oraclerac2 dbs$ cat initdcdb2.ora SPFILE='+DATA2/dcdb/spfiledcdb.ora&

39、#39;oraclerac2 dbs$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 22 11:17:17 2016Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to an idle instance.SQL> show sgaORA-01034: ORACLE not availableProcess ID: 0Session ID: 0 Serial number: 0SQL> startupORACL

40、E instance started.Total System Global Area 1653518336 bytesFixed Size 2228904 bytesVariable Size 1258294616 bytesDatabase Buffers 385875968 bytesRedo Buffers 7118848 bytesDatabase mounted.Database opened.六. Moving data files to new ASM diskgroup迁移数据文件至新磁盘组SQL> select 'backup as copy datafile

41、 '| file#|' format ''+DATA2''' from v$datafile;'BACKUPASCOPYDATAFILE'|FILE#|'FORMAT''+DATA2'''-backup as copy datafile 1 format '+DATA2'backup as copy datafile 2 format '+DATA2'backup as copy datafile 3 format '+DATA2

42、9;backup as copy datafile 4 format '+DATA2'backup as copy datafile 5 format '+DATA2'backup as copy datafile 6 format '+DATA2'6 rows selected.rman rman target /run backup as copy datafile 1 format '+DATA2'backup as copy datafile 2 format '+DATA2'backup as copy

43、datafile 3 format '+DATA2'backup as copy datafile 4 format '+DATA2'backup as copy datafile 5 format '+DATA2'backup as copy datafile 6 format '+DATA2'RMAN> run backup as copy datafile 1 format '+DATA2'backup as copy datafile 2 format '+DATA2'backup a

44、s copy datafile 3 format '+DATA2'backup as copy datafile 4 format '+DATA2'backup as copy datafile 5 format '+DATA2'backup as copy datafile 6 format '+DATA2'2> 3> 4> 5> 6> 7> 8> Starting backup at 22-JUN-16using target database control file instead

45、 of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=58 instance=dcdb2 device type=DISKchannel ORA_DISK_1: starting datafile copyinput datafile file number=00001 name=+DATA/dcdb/datafile/system.259.865439491output file name=+DATA2/dcdb/datafile/system.258.915190107 tag=TAG2016062

46、2T112825 RECID=1 STAMP=915190117channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15channel ORA_DISK_1: starting datafile copycopying current control fileoutput file name=+DATA2/dcdb/controlfile/backup.259.915190121 tag=TAG20160622T112825 RECID=2 STAMP=915190123channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current SPFILE in backup