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. 创建新的DISKGROUP
4. 迁移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实例，查看当前磁盘组的信息：

[[grid@rac1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 22 09:08:26 2016

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

+ASM1

SQL> col name for a10

SQL> set linesize 150

SQL> 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 6721

FRA 1048576 MOUNTED 13312 11081

SYSTEMDG 1048576 MOUNTED 25600 24344

DATA 1048576 MOUNTED 10240 6721

FRA 1048576 MOUNTED 13312 11081

SYSTEMDG 1048576 MOUNTED 25600 24344

6 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_0001

DATA_0000 DATA_0000

利用asmca命令创建DISKGROUP

相关的sql语句

二、 迁移OCR和Vote Disks到新磁盘组（ASM diskgroup）

查看ocr信息：

[grid@rac1 ~]$ ocrcheck

Status 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]# ./ocrcheck

Status 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]# ./ocrcheck

Status 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 +DATA2

Successful 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).

三、Moving server side ASM SPfile to new ASM diskgroup

查看ASM SPFILE 信息，迁移到新磁盘组

[grid@rac1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 22 10:16:53 2016

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

SQL> show parameter spfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string +SYSTEMDG/vmac-cluster/asmpara

meterfile/registry.253.8654292

27

验证spfile路径

ASMCMD> spget +SYSTEMDG/vmac-cluster/asmparameterfile/registry.253.865429227

+SYSTEMDG/vmac-cluster/asmparameterfile/registry.253.865429227

ASMCMD> 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.log

Wed Jun 22 10:41:55 2016

NOTE: 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 ~]$ asmcmd

ASMCMD> spget

+DATA2/vmac-cluster/asmparameterfile/registry.253.915187315

ASMCMD> spget +DATA2/vmac-cluster/asmparameterfile/registry.253.915187315

+DATA2/vmac-cluster/asmparameterfile/registry.253.915187315

ASMCMD>

四、 Moving database related files to new ASM diskgroup

控制文件迁移

SQL> show parameter control

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 7

control_files string +DATA/dcdb/controlfile/current

.256.865439483, +FRA/dcdb/cont

rolfile/current.256.865439483

SQL> 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 dcdb1

Instance 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 2016

Copyright (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-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=37 instance=dcdb1 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=+DATA2/dcdb/controlfile/current.256.915188239

output file name=+FRA2/dcdb/controlfile/current.256.915188241

Finished restore at 22-JUN-16

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 22 10:58:24 2016

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With 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 control

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 7

control_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 spfile

NAME 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]$ asmcmd

ASMCMD> ls

DATA/

DATA2/

FRA/

FRA2/

SYSTEMDG/

ASMCMD> cd data2

ASMCMD> ls

DCDB/

vmac-cluster/

ASMCMD> cd dcdb

ASMCMD> ls

CONTROLFILE/

PARAMETERFILE/

ASMCMD> cd parameerfile

ASMCMD-8002: entry 'parameerfile' does not exist in directory '+data2/dcdb/'

lASMCMD> s

CONTROLFILE/

PARAMETERFILE/

ASMCMD> cd parameterfile

ASMCMD> ls

spfile.257.915188699

ASMCMD> mkalias +DATA2/dcdb/parameterfile/spfile.257.915188699 spfiledcdb.ora

ASMCMD> ls -l

Type 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 dcdb

Database unique name: dcdb

Database name: dcdb

Oracle home: /s01/oracle/app/oracle/product/11.2.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/dcdb/spfiledcdb.ora

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: dcdb

Database instances: dcdb1,dcdb2

Disk Groups: DATA,FRA,DATA2,FRA2

Mount point paths:

Services:

Type: RAC

Database is administrator managed

[oracle@rac1 ~]srvctl modify database -d dcdb -p +DATA2/dcdb/spfiledcdb.ora

[oracle@rac1 ~]$ srvctl config database -d dcdb

Database unique name: dcdb

Database name: dcdb

Oracle home: /s01/oracle/app/oracle/product/11.2.0/dbhome_1

Oracle user: oracle

Spfile: +DATA2/dcdb/spfiledcdb.ora

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: dcdb

Database instances: dcdb1,dcdb2

Disk Groups: DATA,FRA,DATA2,FRA2

Mount point paths:

Services:

Type: RAC

Database 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]$ ls

hc_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 sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 22 11:17:17 2016

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> show sga

ORA-01034: ORACLE not available

Process ID: 0

Session ID: 0 Serial number: 0

SQL> startup

ORACLE instance started.

Total System Global Area 1653518336 bytes

Fixed Size 2228904 bytes

Variable Size 1258294616 bytes

Database Buffers 385875968 bytes

Redo Buffers 7118848 bytes

Database 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';

}

[oracle@rac2 dbs]$ srvctl stop database -d dcdb

[oracle@rac2 dbs]$ srvctl start instance -d dcdb -i dcdb2 -o mount

SQL> 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.

[oracle@rac2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 22 11:44:49 2016

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> alter database open;

Database altered.

TAKE A FULL BACKUP

七. Moving temp files to new ASM diskgroup

八. Moving online redo log files to new ASM diskgroup

修改闪回区

SQL> show parameter recover

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string +FRA

db_recovery_file_dest_size big integer 5727M

db_unrecoverable_scn_tracking boolean TRUE

recovery_parallelism integer 0

SQL> alter system set db_recovery_file_dest='+FRA2';

System altered.

SQL> show parameter recover

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string +FRA2

db_recovery_file_dest_size big integer 5727M

db_unrecoverable_scn_tracking boolean TRUE

SQL> show parameter db_create_file_dest

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_create_file_dest string +DATA

SQL> alter system set db_create_file_dest='+DATA2';

System altered.

SQL> show parameter db_create_file_dest

NAME 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 2

SQL> 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_1

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_1 string LOCATION=+FRA

SQL> alter system set log_archive_dest_1='LOCATION=+FRA2';

System altered.

SQL> show parameter log_archive_dest_1

NAME 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 sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 23 10:07:55 2016

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

SQL> alter diskgroup data dismount ;

SQL> alter diskgroup fra dismount;

Diskgroup altered.

Drop the disks from the other node

SQL> drop diskgroup fra including contents;

Diskgroup dropped.

SQL> drop diskgroup data including contents;

Diskgroup dropped.

SQL>

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

至此，完成存储的迁移，新的磁盘组已替换旧的磁盘组。