Oracle 11g R2 RAC with ASM存储迁移【手记】【摘要】Oracle数据库文件部署在ASM上，需要尽量短的停机时间完成此次存储更换。由于不涉及异构的迁移转换，迁移起来也不难，无需借助三方的工具来完成这次高可用切换。因此使用迁移ASM DISKGROUP 的方式完成存储迁移， 该方法实现迁移过程中尽量缩短系统的停机时间。【正文】首先，介绍迁移的简单过程。步骤如下：1) 划分asm disk，并检查或更改UDEV配置文件，使得新存储的asm disk对ASM实例可识别。2) 备份OCR、Voting Disk、ASM disk header和数据库。3) 创建新的DISKGROUP4) 迁移OCR和Vote Disks到新磁盘组（ASM diskgroup）5) 迁移ASM Spfile到新磁盘组（ASM diskgroup）6) 迁移数据库相关文件至新磁盘组（ASM diskgroup）7) 在线修改数据库参数文件（归档路径、闪回等）8) 删除旧磁盘组9) 观察期。10) 执行数据库备份本文重点介绍ASM的热添加和删除磁盘技术，所以第1、2步在此不列出，由第3步开始。[color=rgb(0, 176, 80)]一、[color=rgb(0, 176, 80)]添加ASM磁盘[color=rgb(0, 176, 80)]组[color=rgb(0, 176, 80)]下面开始添加asm磁盘，这里使用ASM的REBALANCE技术来解决在线迁移数据。[size=13pt]查看当前[size=13pt]ASM磁盘信息登录到ASM实例，查看当前磁盘组的信息：[grid@rac1 ~]$ 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 Management optionsSQL> select instance_name from v$instance;INSTANCE_NAME----------------+ASM1 SQL> 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 1048576 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_diskgroup where name = 'DATA');FAILGROUP NAME------------------------------ ----------DATA_0001 DATA_0001DATA_0000 DATA_0000 利用asmca命令创建DISKGROUP[img=555,331]file:///C:\Users\ThinkPad\AppData\Local\Temp\ksohtml\wps2C65.tmp.jpg[/img] [img=555,381]file:///C:\Users\ThinkPad\AppData\Local\Temp\ksohtml\wps2C66.tmp.jpg[/img] [img=555,381]file:///C:\Users\ThinkPad\AppData\Local\Temp\ksohtml\wps2C77.tmp.jpg[/img] [img=555,330]file:///C:\Users\ThinkPad\AppData\Local\Temp\ksohtml\wps2C78.tmp.jpg[/img]
相关的SQL语句SQL> CREATE DISKGROUP DATA2 EXTERNAL REDUNDANCY DISK '/dev/asm-diskk' SIZE 20480M ATTRIBUTE 'compatible.asm'='11.2.0.0.0','au_size'='1M' /* 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 ONLINE 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 [grid@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 22 10:02:37 2016 Copyright (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 options SQL> select name,state from v$asm_diskgroup; NAME STATE------------------------------ -----------DATA MOUNTEDFRA MOUNTEDSYSTEMDG MOUNTEDDATA2 MOUNTEDFRA2 MOUNTED [color=rgb(0, 176, 80)]二、 迁移OCR和Vote Disks到新磁盘组（ASM diskgroup）[color=rgb(0, 176, 80)]查看ocr信息：[grid@rac1 ~]$ 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 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 bypassed due to non-privileged user 添加ocr磁盘组[root@rac1 bin]# ./ocrconfig -add +DATA2[root@rac1 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 Device/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旧磁盘组[root@rac1 bin]# ./ocrconfig -delete +SYSTEMDG[root@rac1 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/File not configured Device/File not configured Device/File not configured Cluster registry integrity check succeeded查看Votedisk信息，迁移至新磁盘组[grid@rac1 ~]$ crsctl query css votedisk## STATE File Universal Id File Name Disk group-- ----- ----------------- --------- --------- 1. ONLINE b0746a43f93c4ff2bf067cb97ffedf4e (/dev/asm-diskb) [SYSTEMDG] 2. ONLINE 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) [SYSTEMDG]Located 5 voting disk(s). [grid@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 3b9ff9c1f7884f02bfedb22d5cdfc463.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 replaced[grid@rac1 ~]$ crsctl query css votedisk## STATE File Universal Id File Name Disk group-- ----- ----------------- --------- --------- 1. ONLINE 06d88831dc8b4fa0bf0213c802aeb8d8 (/dev/asm-diskk) [DATA2]Located 1 voting disk(s).[size=7.5pt] 三、Moving server side ASM SPfile to new ASM diskgroup[color=rgb(0, 176, 80)]查看ASM SPFILE 信息，迁移到新磁盘组 [grid@rac1 ~]$ sqlplus / as sysasmSQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 22 10:16:53 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> show parameter spfileNAME TYPE VALUE------ ------ ------------------------------spfile string +SYSTEMDG/vmac-cluster/asmparameterfile/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.865429227 SQL> create pfile='/tmp/asm_pfile.ora' from spfile
SQL> create spfile='+DATA2' from pfile='/tmp/asm_pfile.ora';File created.New SPfile location will be logged on the ASM alert log
NOTE: updated gpnp profile ASM SPFILE to +DATA2/dbatst-scan/asmparameterfile/registry.253.828801675[grid@rac1 trace]$ tail alert_+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.915187315 [grid@rac1 ~]$ asmcmdASMCMD> spget+DATA2/vmac-cluster/asmparameterfile/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[color=rgb(0, 176, 80)]控制文件迁移SQL> show parameter controlNAME TYPE VALUE------------------------------------ ----------- ------------------------------control_file_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=spfile sid='*';System altered. [oracle@rac1 ~]$ srvctl stop database -d dcdb启动实例1到nomount状态[oracle@rac1 ~]$ srvctl start instance -d dcdb -i dcdb1 -o nomount[oracle@rac1 ~]$ srvctl status instance -d dcdb -i dcdb1Instance dcdb1 is running on node rac1 [oracle@rac1 ~]$ rman target /Recovery Manager: Release 11.2.0.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 target 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.915188241Finished restore at 22-JUN-16 [oracle@rac1 ~]$ 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, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing options SQL> alter database mount 2 ;Database altered. SQL> alter database open ;Database altered. SQL> show parameter controlNAME TYPE VALUE------------------------------------ ----------- ------------------------------control_file_record_keep_time integer 7control_files string +DATA2/dcdb/controlfile/curren t.256.915188239, +FRA2/dcdb/co ntrolfile/current.256.91518824 1五. Moving SPfile to new ASM diskgroup[color=rgb(0, 176, 80)]查看参数文件SQL> show parameter spfileNAME TYPE VALUE------------------------------------ ----------- ------------------------------spfile string +DATA/dcdb/spfiledcdb.ora SQL> create pfile='/tmp/pfile_db.ora' from spfile;SQL> create spfile='+DATA2' from pfile='/tmp/pfile_db.ora'; [grid@rac1 trace]$ asmcmdASMCMD> lsDATA/DATA2/FRA/FRA2/SYSTEMDG/ASMCMD> cd data2ASMCMD> lsDCDB/vmac-cluster/ASMCMD> cd dcdbASMCMD> lsCONTROLFILE/PARAMETERFILE/ASMCMD> cd parameerfileASMCMD-8002: entry 'parameerfile' does not exist in directory '+data2/dcdb/'lASMCMD> sCONTROLFILE/PARAMETERFILE/ASMCMD> cd parameterfileASMCMD> lsspfile.257.915188699 ASMCMD> mkalias +DATA2/dcdb/parameterfile/spfile.257.915188699 spfiledcdb.oraASMCMD> ls -lType Redund Striped Time Sys Name Y CONTROLFILE/ Y PARAMETERFILE/ N spfiledcdb.ora => +DATA2/DCDB/PARAMETERFILE/spfile.257.915188699[grid@rac1 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.oraDomain: 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 managed[oracle@rac1 ~]srvctl modify database -d dcdb -p +DATA2/dcdb/spfiledcdb.ora
[oracle@rac1 ~]$ srvctl config database -d dcdbDatabase 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,dcdb2Disk Groups: DATA,FRA,DATA2,FRA2Mount point paths: Services: Type: RACDatabase is administrator managed Modify the pfiles that give reference to the SPFile in $ORACLE_HOME/dbs[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs[oracle@rac1 dbs]$ lshc_dcdb1.dat initdcdb1.ora init.ora orapwdcdb1 snapcf_dcdb1.f[oracle@rac1 dbs]$ cat initdcdb1.ora SPFILE='+DATA/dcdb/spfiledcdb.ora'编辑后浏览[oracle@rac1 dbs]$ cat initdcdb1.ora SPFILE='+DATA2/dcdb/spfiledcdb.ora' [oracle@rac2 dbs]$ cat initdcdb2.ora SPFILE='+DATA/dcdb/spfiledcdb.ora'修改成如下：[oracle@rac2 dbs]$ cat initdcdb2.ora SPFILE='+DATA2/dcdb/spfiledcdb.ora' [oracle@rac2 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: 0 SQL> startupORACLE 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 '|| 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';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 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';}
[td=568]RMAN> run {backup as copy datafile 1 format '+DATA2';backup as copy datafile 2 format '+DATA2';backup as 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';[size=10.5000pt]}2> 3> 4> 5> 6> 7> 8> Starting backup at 22-JUN-16using target database control file instead 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=TAG20160622T112825 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 setchannel ORA_DISK_1: starting piece 1 at 22-JUN-16channel ORA_DISK_1: finished piece 1 at 22-JUN-16piece handle=+DATA2/dcdb/backupset/2016_06_22/nnsnf0_tag20160622t112825_0.260.915190125 tag=TAG20160622T112825 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 22-JUN-16[size=10.5000pt] Starting backup at 22-JUN-16using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile file number=00002 name=+DATA/dcdb/datafile/sysaux.260.865439507output file name=+DATA2/dcdb/datafile/sysaux.261.915190133 tag=TAG20160622T112852 RECID=3 STAMP=915190150channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25Finished backup at 22-JUN-16[size=10.5000pt] Starting backup at 22-JUN-16using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile file number=00003 name=+DATA/dcdb/datafile/undotbs1.261.865439519output file name=+DATA2/dcdb/datafile/undotbs1.262.915190159 tag=TAG20160622T112918 RECID=4 STAMP=915190167channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15Finished backup at 22-JUN-16[size=10.5000pt] Starting backup at 22-JUN-16using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile file number=00004 name=+DATA/dcdb/datafile/undotbs2.263.865439543output file name=+DATA2/dcdb/datafile/undotbs2.263.915190177 tag=TAG20160622T112937 RECID=5 STAMP=915190184channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15Finished backup at 22-JUN-16[size=10.5000pt] Starting backup at 22-JUN-16using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile file number=00005 name=+DATA/dcdb/datafile/users.264.865439549output file name=+DATA2/dcdb/datafile/users.264.915190195 tag=TAG20160622T112954 RECID=6 STAMP=915190195channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finished backup at 22-JUN-16[size=10.5000pt] Starting backup at 22-JUN-16using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile file number=00006 name=+DATA/dcdb/datafile/imaa.268.914573605output file name=+DATA2/dcdb/datafile/imaa.265.915190201 tag=TAG20160622T113001 RECID=7 STAMP=915190203channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03Finished backup at 22-JUN-16[size=10.5000pt]
[oracle@rac2 dbs]$ srvctl stop database -d dcdb[oracle@rac2 dbs]$ srvctl start instance -d dcdb -i dcdb2 -o mountSQL> select 'switch datafile '|| file#||' to copy;' from v$datafile;'SWITCHDATAFILE'||FILE#||'TOCOPY;'-----------------------------------------------------------------switch datafile 1 to copy;switch datafile 2 to copy;switch datafile 3 to copy;switch datafile 4 to copy;switch datafile 5 to copy;switch datafile 6 to copy;6 rows selected. SQL> select 'recover datafile '|| file# ||';' from v$datafile;'RECOVERDATAFILE'||FILE#||';'----------------------------------------------------------recover datafile 1;recover datafile 2;recover datafile 3;recover datafile 4;recover datafile 5;recover datafile 6;6 rows selected.
[td=568][oracle@rac2 dbs]$ rman target /[size=10.5000pt] Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jun 22 11:41:49 2016[size=10.5000pt] Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.[size=10.5000pt] connected to target database: DCDB (DBID=3006999415, not open)[size=10.5000pt] RMAN> switch datafile 1 to copy;switch datafile 2 to copy;switch datafile 3 to copy;switch datafile 4 to copy;switch datafile 5 to copy;switch datafile 6 to copy;[size=10.5000pt] using target database control file instead of recovery catalogdatafile 1 switched to datafile copy "+DATA2/dcdb/datafile/system.258.915190107"[size=10.5000pt] RMAN> datafile 2 switched to datafile copy "+DATA2/dcdb/datafile/sysaux.261.915190133"[size=10.5000pt] RMAN> datafile 3 switched to datafile copy "+DATA2/dcdb/datafile/undotbs1.262.915190159"[size=10.5000pt] RMAN> datafile 4 switched to datafile copy "+DATA2/dcdb/datafile/undotbs2.263.915190177"[size=10.5000pt] RMAN> datafile 5 switched to datafile copy "+DATA2/dcdb/datafile/users.264.915190195"[size=10.5000pt] RMAN> datafile 6 switched to datafile copy "+DATA2/dcdb/datafile/imaa.265.915190201"

[td=568]RMAN> report schema;[size=10.5000pt] Report of database schema for database with db_unique_name DCDB[size=10.5000pt] List of Permanent Datafiles===========================File Size(MB) Tablespace RB segs Datafile Name---- -------- -------------------- ------- ------------------------1 710 SYSTEM *** +DATA2/dcdb/datafile/system.258.9151901072 1220 SYSAUX *** +DATA2/dcdb/datafile/sysaux.261.9151901333 590 UNDOTBS1 *** +DATA2/dcdb/datafile/undotbs1.262.9151901594 465 UNDOTBS2 *** +DATA2/dcdb/datafile/undotbs2.263.9151901775 5 USERS *** +DATA2/dcdb/datafile/users.264.9151901956 100 IMAA *** +DATA2/dcdb/datafile/imaa.265.915190201[size=10.5000pt] List of Temporary Files=======================File Size(MB) Tablespace Maxsize(MB) Tempfile Name---- -------- -------------------- ----------- --------------------1 83 TEMP 32767 +DATA/dcdb/tempfile/temp.262.865439525

[td=568]RMAN> recover datafile 1;recover datafile 2;recover datafile 3;recover datafile 4;recover datafile 5;recover datafile 6;[size=10.5000pt] Starting recover at 22-JUN-16allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=38 instance=dcdb2 device type=DISK[size=10.5000pt] starting media recoverymedia recovery complete, elapsed time: 00:00:01[size=10.5000pt] Finished recover at 22-JUN-16[size=10.5000pt] RMAN> Starting recover at 22-JUN-16using channel ORA_DISK_1[size=10.5000pt] starting media recoverymedia recovery complete, elapsed time: 00:00:02[size=10.5000pt] Finished recover at 22-JUN-16[size=10.5000pt] RMAN> Starting recover at 22-JUN-16using channel ORA_DISK_1[size=10.5000pt] starting media recoverymedia recovery complete, elapsed time: 00:00:02[size=10.5000pt] Finished recover at 22-JUN-16[size=10.5000pt] RMAN> Starting recover at 22-JUN-16using channel ORA_DISK_1[size=10.5000pt] starting media recoverymedia recovery complete, elapsed time: 00:00:01[size=10.5000pt] Finished recover at 22-JUN-16[size=10.5000pt] RMAN> Starting recover at 22-JUN-16using channel ORA_DISK_1[size=10.5000pt] starting media recoverymedia recovery complete, elapsed time: 00:00:01[size=10.5000pt] Finished recover at 22-JUN-16[size=10.5000pt] RMAN> Starting recover at 22-JUN-16using channel ORA_DISK_1[size=10.5000pt] starting media recoverymedia recovery complete, elapsed time: 00:00:02[size=10.5000pt] Finished recover at 22-JUN-16
[oracle@rac2 dbs]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 22 11:44:49 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, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsSQL> alter database open;Database altered. [size=7.5pt]TAKE A FULL BACKUP
[td=568][size=10.5000pt] RMAN> delete datafilecopy all;[size=10.5000pt] allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=58 instance=dcdb2 device type=DISKList of Datafile Copies=======================[size=10.5000pt] Key File S Completion Time Ckp SCN Ckp Time ------- ---- - --------------- ---------- ---------------8 1 A 22-JUN-16 31929208 22-JUN-16 Name: +DATA/dcdb/datafile/system.259.865439491[size=10.5000pt] 9 2 A 22-JUN-16 31929208 22-JUN-16 Name: +DATA/dcdb/datafile/sysaux.260.865439507[size=10.5000pt] 10 3 A 22-JUN-16 31929208 22-JUN-16 Name: +DATA/dcdb/datafile/undotbs1.261.865439519[size=10.5000pt] 11 4 A 22-JUN-16 31929208 22-JUN-16 Name: +DATA/dcdb/datafile/undotbs2.263.865439543[size=10.5000pt] 12 5 A 22-JUN-16 31929208 22-JUN-16 Name: +DATA/dcdb/datafile/users.264.865439549[size=10.5000pt] 13 6 A 22-JUN-16 31929208 22-JUN-16 Name: +DATA/dcdb/datafile/imaa.268.914573605[size=10.5000pt] [size=10.5000pt] Do you really want to delete the above objects (enter YES or NO)? yesdeleted datafile copydatafile copy file name=+DATA/dcdb/datafile/system.259.865439491 RECID=8 STAMP=915190925deleted datafile copydatafile copy file name=+DATA/dcdb/datafile/sysaux.260.865439507 RECID=9 STAMP=915190926deleted datafile copydatafile copy file name=+DATA/dcdb/datafile/undotbs1.261.865439519 RECID=10 STAMP=915190928deleted datafile copydatafile copy file name=+DATA/dcdb/datafile/undotbs2.263.865439543 RECID=11 STAMP=915190929deleted datafile copydatafile copy file name=+DATA/dcdb/datafile/users.264.865439549 RECID=12 STAMP=915190930deleted datafile copydatafile copy file name=+DATA/dcdb/datafile/imaa.268.914573605 RECID=13 STAMP=915190932Deleted 6 objects[size=10.5000pt] [size=10.5000pt]
七. Moving temp files to new ASM diskgroup[color=rgb(0, 176, 80)]
[td=568][oracle@rac2 dbs]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 22 11:50:46 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, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsSQL> select file_name from dba_temp_files;FILE_NAME-------------------------------------------------------------------------------+DATA/dcdb/tempfile/temp.262.865439525[size=10.5000pt] SQL> alter tablespace temp add tempfile '+data2(tempfile)';Tablespace altered.[size=10.5000pt] SQL> select file_name from dba_temp_files;FILE_NAME-------------------------------------------------------------------------------+DATA/dcdb/tempfile/temp.262.865439525+DATA2/dcdb/tempfile/temp.266.915191501[size=10.5000pt] SQL> alter tablespace temp drop tempfile '+DATA/dcdb/tempfile/temp.262.865439525';Tablespace altered.[size=10.5000pt] SQL> select file_name from dba_temp_files;FILE_NAME-------------------------------------------------------------------------------+DATA2/dcdb/tempfile/temp.266.915191501[size=10.5000pt] SQL>
八. Moving online redo log files to new ASM diskgroup
[td=568]SQL> select group#,member from v$logfile; GROUP# MEMBER---------- ------------------------------------------------------------ 1 +DATA/dcdb/onlinelog/group_1.257.865439485 1 +FRA/dcdb/onlinelog/group_1.257.865439487 2 +DATA/dcdb/onlinelog/group_2.258.865439487 2 +FRA/dcdb/onlinelog/group_2.258.865439489 3 +DATA/dcdb/onlinelog/group_3.265.865447401 3 +FRA/dcdb/onlinelog/group_3.259.865447401 4 +DATA/dcdb/onlinelog/group_4.266.865447403 4 +FRA/dcdb/onlinelog/group_4.260.8654474038 rows selected.[size=10.5000pt] SQL> alter database add logfile member '+DATA2','+FRA2' to group 1;Database altered.[size=10.5000pt] SQL> alter database add logfile member '+DATA2','+FRA2' to group 2;Database altered.[size=10.5000pt] SQL> alter database add logfile member '+DATA2','+FRA2' to group 3;Database altered.[size=10.5000pt] SQL> alter database add logfile member '+DATA2','+FRA2' to group 4;Database altered.[size=10.5000pt] 删除原日志成员（注：切换为非当前日志进行删除）SQL> alter database drop logfile member '+DATA/dcdb/onlinelog/group_4.266.865447403';Database altered.[size=10.5000pt] SQL> alter database drop logfile member '+FRA/dcdb/onlinelog/group_2.258.865439489';Database altered.[size=10.5000pt] SQL> alter database drop logfile member '+DATA/dcdb/onlinelog/group_1.257.865439485';Database altered.[size=10.5000pt] SQL> alter database drop logfile member '+DATA/dcdb/onlinelog/group_3.265.865447401';Database altered.[size=10.5000pt] SQL> alter system switch logfile;[size=10.5000pt] SQL> alter system checkpoint[size=10.5000pt];System altered.SQL> col member for a45SQL> select group#,member from v$logfile order by 1; GROUP# MEMBER---------- --------------------------------------------- 1 +DATA2/dcdb/onlinelog/group_1.267.915191927 1 +FRA2/dcdb/onlinelog/group_1.257.915191927 2 +DATA2/dcdb/onlinelog/group_2.268.915191939 2 +FRA2/dcdb/onlinelog/group_2.258.915191939 3 +DATA2/dcdb/onlinelog/group_3.269.915191943 3 +FRA2/dcdb/onlinelog/group_3.259.915191945 4 +DATA2/dcdb/onlinelog/group_4.270.915191951 4 +FRA2/dcdb/onlinelog/group_4.260.915191951[size=10.5000pt] 8 rows selected.
修改闪回区SQL> show parameter recoverNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_recovery_file_dest string +FRAdb_recovery_file_dest_size big integer 5727Mdb_unrecoverable_scn_tracking boolean TRUErecovery_parallelism integer 0SQL> alter system set db_recovery_file_dest='+FRA2';System altered.SQL> show parameter recoverNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_recovery_file_dest string +FRA2db_recovery_file_dest_size big integer 5727Mdb_unrecoverable_scn_tracking boolean TRUESQL> show parameter db_create_file_destNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_create_file_dest string +DATASQL> alter system set db_create_file_dest='+DATA2';System altered.SQL> show parameter db_create_file_destNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_create_file_dest string +DATA2 修改归档路径：SQL> select max(sequence#),thread# from v$archived_log group by thread#;MAX(SEQUENCE#) THREAD#-------------- ---------- 506 1 434 2SQL> select name from v$archived_log where (sequence#=506 and thread#=1) or (sequence#=434 and thread#=2);NAME-------------------------------------------------------------------------------+FRA/dcdb/archivelog/2016_06_23/thread_1_seq_506.312.915269175+FRA/dcdb/archivelog/2016_06_23/thread_2_seq_434.318.915269177 SQL> show parameter log_archive_dest_1NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_1 string LOCATION=+FRASQL> alter system set log_archive_dest_1='LOCATION=+FRA2';System altered. SQL> show parameter log_archive_dest_1NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_1 string LOCATION=+FRA2 执行数据库物理全备九 . Dropping old disk groups[root@rac2 bin]# ./crsctl stop cluster –all[root@rac2 bin]# ./crsctl start cluster –all[grid@rac2 ~]$ sqlplus / as sysasmSQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 23 10:07:55 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> alter diskgroup data dismount ;SQL> alter diskgroup fra dismount;Diskgroup altered.Drop the disks from the other nodeSQL> drop diskgroup fra including contents;Diskgroup dropped.SQL> drop diskgroup data including contents;Diskgroup dropped.SQL>[size=7.5pt] Remove the disks from OCR[oracle@rac01 ~]$ srvctl remove diskgroup -g DATA
PRCA-1002 : Failed to remove CRS resource ora.DATA.dg for ASM Disk Group DATA
PRCR-1028 : Failed to remove resource ora.DATA.dg
PRCR-1072 : Failed to unregister resource ora.DATA.dg
CRS-0222: Resource ‘ora.DATA.dg’ has dependency error.[oracle@dba01tst ~]$ srvctl modify database -d dcdb -a “DATA2,FRA2”
[oracle@dba01tst ~]$ srvctl remove diskgroup -g DATA
[oracle@dba01tst ~]$ srvctl remove diskgroup -g FRA
[oracle@dba01tst ~]$ srvctl start database -d dcdb至此，完成存储的迁移，新的磁盘组已替换旧的磁盘组。