记一次数据库迁移测试
记一次数据库迁移测试最近有个项目涉及到数据库迁移，从旧阵列把数据库迁移到新阵列，于是作了以下测试；供坛友讨论环境1、P170 AIX53TL092、VMWARE SERVER3、OPENFILER4、ORACLE 10GR2测试目标使用MIRRORVG方式，将数据在线迁移到其他PV1、PV、VG、LV等资源的创建# lspvhdisk0&...显示全部
记一次数据库迁移测试
最近有个项目涉及到数据库迁移，从旧阵列把数据库迁移到新阵列，于是作了以下测试；
供坛友讨论
环境
1、P170 AIX53TL09
2、VMWARE SERVER
3、OPENFILER
4、ORACLE 10GR2
测试目标
使用MIRRORVG方式，将数据在线迁移到其他PV
1、PV、VG、LV等资源的创建
# lspv
hdisk0 0003744f5808d0c8 rootvg active
hdisk1 0003744f6c0b4091 None
hdisk2 0003744f6c0b61b1 None
hdisk3 0003744f80dcb788 None
hdisk4 0003744f80e36ace None
# chdev -l hdisk1 -a pv=yes
hdisk1 changed
# chdev -l hdisk2 -a pv=yes
hdisk2 changed
# mkvg -S -C -s 16 -y vg1 hdisk1
vg1
mkvg: This concurrent capable volume group must be varied on manually.
# varyonvg vg1
# lsvg -o
vg1
rootvg
# lsvg vg1
VOLUME GROUP: vg1 VG IDENTIFIER: 0003744f00004c0000000128c73228dd
VG STATE: active PP SIZE: 16 megabyte(s)
VG PERMISSION: read/write TOTAL PPs: 59 (944 megabytes)
MAX LVs: 256 FREE PPs: 59 (944 megabytes)
LVs: 0 USED PPs: 0 (0 megabytes)
OPEN LVs: 0 QUORUM: 2 (Enabled)
TOTAL PVs: 1 VG DESCRIPTORS: 2
STALE PVs: 0 STALE PPs: 0
ACTIVE PVs: 1 AUTO ON: no
Concurrent: Enhanced-Capable Auto-Concurrent: Disabled
VG Mode: Non-Concurrent
MAX PPs per VG: 32768 MAX PVs: 1024
LTG size (Dynamic): 256 kilobyte(s) AUTO SYNC: no
HOT SPARE: no BB POLICY: relocatable
# mklv -t raw -y tbs1 vg1 2 hdisk1
tbs1
2、创建表空间及数据文件
# chown oracle:oinstall /dev/rtbs1
SQL> create tablespace mytbs1 datafile '/dev/rtbs1' size 20M;
Tablespace created.
SQL> create table mytable1(id number(4),name varchar2(14)) tablespace mytbs1;
Table created.
SQL> insert into mytable1 values(001,'aixchina.net');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from mytable1;
ID NAME
---------- --------------
1 aixchina.net
SQL> host lslv tbs1
LOGICAL VOLUME: tbs1 VOLUME GROUP: vg1
LV IDENTIFIER: 0003744f00004c0000000128c73228dd.1 PERMISSION: read/write
VG STATE: active/complete LV STATE: opened/syncd
TYPE: raw WRITE VERIFY: off
MAX LPs: 512 PP SIZE: 16 megabyte(s)
COPIES: 1 SCHED POLICY: parallel
LPs: 2 PPs: 2
STALE PPs: 0 BB POLICY: relocatable
INTER-POLICY: minimum RELOCATABLE: yes
INTRA-POLICY: middle UPPER BOUND: 1024
MOUNT POINT: N/A LABEL: None
MIRROR WRITE CONSISTENCY: on/ACTIVE
EACH LP COPY ON A SEPARATE PV ?: yes
Serialize IO ?: NO
DEVICESUBTYPE : DS_LVZ
3、检查数据文件所在PV
SQL> host lslv -l tbs1
tbs1:N/A
PV COPIES IN BAND DISTRIBUTION
hdisk1 002:000:000 100% 000:002:000:000:000
4、MIRROR把卷组的源LV镜像到新PV
# chdev -l hdisk2 -a pv=yes
hdisk2 changed
# extendvg vg1 hdisk2
# mirrorvg vg1 hdisk2
0516-1804 chvg: The quorum change takes effect immediately.
5、确认MIRROR是否完成
# lsvg -p vg1
vg1:
PV_NAME PV STATE TOTAL PPs FREE PPs FREE DISTRIBUTION
hdisk1 active 59 57 12..10..11..12..12
hdisk2 active 59 59 12..12..11..12..12
# lspv hdisk2
PHYSICAL VOLUME: hdisk2 VOLUME GROUP: vg1
PV IDENTIFIER: 0003744f6c0b61b1 VG IDENTIFIER 0003744f00004c0000000128c73228dd
PV STATE: active
STALE PARTITIONS: 0 ALLOCATABLE: yes
PP SIZE: 16 megabyte(s) LOGICAL VOLUMES: 0
TOTAL PPs: 59 (944 megabytes) VG DESCRIPTORS: 1
FREE PPs: 59 (944 megabytes) HOT SPARE: no
USED PPs: 0 (0 megabytes) MAX REQUEST: 256 kilobytes
FREE DISTRIBUTION: 12..12..11..12..12
USED DISTRIBUTION: 00..00..00..00..00
# lspv hdisk1
PHYSICAL VOLUME: hdisk1 VOLUME GROUP: vg1
PV IDENTIFIER: 0003744f6c0b4091 VG IDENTIFIER 0003744f00004c0000000128c73228dd
PV STATE: active
STALE PARTITIONS: 0 ALLOCATABLE: yes
PP SIZE: 16 megabyte(s) LOGICAL VOLUMES: 1
TOTAL PPs: 59 (944 megabytes) VG DESCRIPTORS: 2
FREE PPs: 57 (912 megabytes) HOT SPARE: no
USED PPs: 2 (32 megabytes) MAX REQUEST: 256 kilobytes
FREE DISTRIBUTION: 12..10..11..12..12
USED DISTRIBUTION: 00..02..00..00..00
注意到STALE PV为0，QUORUM已经DISABLED
# lsvg vg1
VOLUME GROUP: vg1 VG IDENTIFIER: 0003744f00004c0000000128c73228dd
VG STATE: active PP SIZE: 16 megabyte(s)
VG PERMISSION: read/write TOTAL PPs: 118 (1888 megabytes)
MAX LVs: 256 FREE PPs: 114 (1824 megabytes)
LVs: 1 USED PPs: 4 (64 megabytes)
OPEN LVs: 1 QUORUM: 1 (Disabled)
TOTAL PVs: 2 VG DESCRIPTORS: 3
STALE PVs: 0 STALE PPs: 0
ACTIVE PVs: 2 AUTO ON: no
Concurrent: Enhanced-Capable Auto-Concurrent: Disabled
VG Mode: Non-Concurrent
MAX PPs per VG: 32768 MAX PVs: 1024
LTG size (Dynamic): 256 kilobyte(s) AUTO SYNC: no
HOT SPARE: no BB POLICY: relocatable
LV的COPY为2份
# lslv tbs1
LOGICAL VOLUME: tbs1 VOLUME GROUP: vg1
LV IDENTIFIER: 0003744f00004c0000000128c73228dd.1 PERMISSION: read/write
VG STATE: active/complete LV STATE: opened/syncd
TYPE: raw WRITE VERIFY: off
MAX LPs: 512 PP SIZE: 16 megabyte(s)
COPIES: 2 SCHED POLICY: parallel
LPs: 2 PPs: 4
STALE PPs: 0 BB POLICY: relocatable
INTER-POLICY: minimum RELOCATABLE: yes
INTRA-POLICY: middle UPPER BOUND: 1024
MOUNT POINT: N/A LABEL: None
MIRROR WRITE CONSISTENCY: on/ACTIVE
EACH LP COPY ON A SEPARATE PV ?: yes
Serialize IO ?: NO
DEVICESUBTYPE : DS_LVZ
LV已经同时存在于2个PV
# lslv -l tbs1
tbs1:N/A
PV COPIES IN BAND DISTRIBUTION
hdisk1 002:000:000 100% 000:002:000:000:000
hdisk2 002:000:000 100% 000:002:000:000:000
6、UNMIRRO打破镜像
# unmirrorvg vg1 hdisk1
0516-1804 chvg: The quorum change takes effect immediately.
注意，UNMIRROR之后，QUORUM已经是ENABLED状态
# lsvg vg1
VOLUME GROUP: vg1 VG IDENTIFIER: 0003744f00004c0000000128c73228dd
VG STATE: active PP SIZE: 16 megabyte(s)
VG PERMISSION: read/write TOTAL PPs: 118 (1888 megabytes)
MAX LVs: 256 FREE PPs: 116 (1856 megabytes)
LVs: 1 USED PPs: 2 (32 megabytes)
OPEN LVs: 1 QUORUM: 2 (Enabled)
TOTAL PVs: 2 VG DESCRIPTORS: 3
STALE PVs: 0 STALE PPs: 0
ACTIVE PVs: 2 AUTO ON: no
Concurrent: Enhanced-Capable Auto-Concurrent: Disabled
VG Mode: Non-Concurrent
MAX PPs per VG: 32768 MAX PVs: 1024
LTG size (Dynamic): 256 kilobyte(s) AUTO SYNC: no
HOT SPARE: no BB POLICY: relocatable
LV已经迁移到HDISK2上，并且COPY为1份
# lslv -l tbs1
tbs1:N/A
PV COPIES IN BAND DISTRIBUTION
hdisk2 002:000:000 100% 000:002:000:000:000
# lslv tbs1
LOGICAL VOLUME: tbs1 VOLUME GROUP: vg1
LV IDENTIFIER: 0003744f00004c0000000128c73228dd.1 PERMISSION: read/write
VG STATE: active/complete LV STATE: opened/syncd
TYPE: raw WRITE VERIFY: off
MAX LPs: 512 PP SIZE: 16 megabyte(s)
COPIES: 1 SCHED POLICY: parallel
LPs: 2 PPs: 2
STALE PPs: 0 BB POLICY: relocatable
INTER-POLICY: minimum RELOCATABLE: yes
INTRA-POLICY: middle UPPER BOUND: 1024
MOUNT POINT: N/A LABEL: None
MIRROR WRITE CONSISTENCY: on/ACTIVE
EACH LP COPY ON A SEPARATE PV ?: yes
Serialize IO ?: NO
DEVICESUBTYPE : DS_LVZ
# reducevg vg1 hdisk2
# lspv
hdisk0 0003744f5808d0c8 rootvg active
hdisk1 0003744f6c0b4091 vg1 active
hdisk2 0003744f6c0b61b1 None
hdisk3 0003744f80dcb788 None
hdisk4 0003744f80e36ace None
检查迁移前插入的数据
SQL> select * from mytable1;
ID NAME
---------- --------------
1 aixchina.net
SQL> insert into mytable1 values(2,'myguangzhou');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from mytable1;
ID NAME
---------- --------------
1 aixchina.net
2 myguangzhou
7、使用migratepv来迁移更简单
闲的无聊，想把数据迁移回原PV
这次不使用MIRROR了，直接使用MIGRATEPV更直接更简单
# migratepv -l tbs1 hdisk2 hdisk1
# lslv -l tbs1
tbs1:N/A
PV COPIES IN BAND DISTRIBUTION
hdisk1 002:000:000 100% 000:002:000:000:000
#
迁移过程中Alert.log没有任何警告信息
$ tail -20 $ORACLE_BASE/admin/myoracle/bdump/alert*
ORA-959 signalled during: drop tablespace tbs3 including contents and datafiles...
Sun May 23 16:49:20 2010
drop tablespace FS_TBS3 including contents and datafiles
Sun May 23 16:49:21 2010
Deleted file /dev/rscal_lv_tbs3
Completed: drop tablespace FS_TBS3 including contents and datafiles
Sun May 23 16:49:48 2010
drop tablespace tbs1 including contents and datafiles
Sun May 23 16:49:54 2010
Deleted file /dev/roriginal_tbs1
Completed: drop tablespace tbs1 including contents and datafiles
Sun May 23 16:50:28 2010
drop tablespace tbs2 including contents and datafiles
Sun May 23 16:50:29 2010
Deleted file /dev/rscalable_tbs2
Completed: drop tablespace tbs2 including contents and datafiles
Sun May 23 17:10:57 2010
create tablespace mytbs1 datafile '/dev/rtbs1' size 20M
Sun May 23 17:11:01 2010
Completed: create tablespace mytbs1 datafile '/dev/rtbs1' size 20M收起
最近有个项目涉及到数据库迁移，从旧阵列把数据库迁移到新阵列，于是作了以下测试；
供坛友讨论
环境
1、P170 AIX53TL09
2、VMWARE SERVER
3、OPENFILER
4、ORACLE 10GR2
测试目标
使用MIRRORVG方式，将数据在线迁移到其他PV
1、PV、VG、LV等资源的创建
# lspv
hdisk0 0003744f5808d0c8 rootvg active
hdisk1 0003744f6c0b4091 None
hdisk2 0003744f6c0b61b1 None
hdisk3 0003744f80dcb788 None
hdisk4 0003744f80e36ace None
# chdev -l hdisk1 -a pv=yes
hdisk1 changed
# chdev -l hdisk2 -a pv=yes
hdisk2 changed
# mkvg -S -C -s 16 -y vg1 hdisk1
vg1
mkvg: This concurrent capable volume group must be varied on manually.
# varyonvg vg1
# lsvg -o
vg1
rootvg
# lsvg vg1
VOLUME GROUP: vg1 VG IDENTIFIER: 0003744f00004c0000000128c73228dd
VG STATE: active PP SIZE: 16 megabyte(s)
VG PERMISSION: read/write TOTAL PPs: 59 (944 megabytes)
MAX LVs: 256 FREE PPs: 59 (944 megabytes)
LVs: 0 USED PPs: 0 (0 megabytes)
OPEN LVs: 0 QUORUM: 2 (Enabled)
TOTAL PVs: 1 VG DESCRIPTORS: 2
STALE PVs: 0 STALE PPs: 0
ACTIVE PVs: 1 AUTO ON: no
Concurrent: Enhanced-Capable Auto-Concurrent: Disabled
VG Mode: Non-Concurrent
MAX PPs per VG: 32768 MAX PVs: 1024
LTG size (Dynamic): 256 kilobyte(s) AUTO SYNC: no
HOT SPARE: no BB POLICY: relocatable
# mklv -t raw -y tbs1 vg1 2 hdisk1
tbs1
2、创建表空间及数据文件
# chown oracle:oinstall /dev/rtbs1
SQL> create tablespace mytbs1 datafile '/dev/rtbs1' size 20M;
Tablespace created.
SQL> create table mytable1(id number(4),name varchar2(14)) tablespace mytbs1;
Table created.
SQL> insert into mytable1 values(001,'aixchina.net');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from mytable1;
ID NAME
---------- --------------
1 aixchina.net
SQL> host lslv tbs1
LOGICAL VOLUME: tbs1 VOLUME GROUP: vg1
LV IDENTIFIER: 0003744f00004c0000000128c73228dd.1 PERMISSION: read/write
VG STATE: active/complete LV STATE: opened/syncd
TYPE: raw WRITE VERIFY: off
MAX LPs: 512 PP SIZE: 16 megabyte(s)
COPIES: 1 SCHED POLICY: parallel
LPs: 2 PPs: 2
STALE PPs: 0 BB POLICY: relocatable
INTER-POLICY: minimum RELOCATABLE: yes
INTRA-POLICY: middle UPPER BOUND: 1024
MOUNT POINT: N/A LABEL: None
MIRROR WRITE CONSISTENCY: on/ACTIVE
EACH LP COPY ON A SEPARATE PV ?: yes
Serialize IO ?: NO
DEVICESUBTYPE : DS_LVZ
3、检查数据文件所在PV
SQL> host lslv -l tbs1
tbs1:N/A
PV COPIES IN BAND DISTRIBUTION
hdisk1 002:000:000 100% 000:002:000:000:000
4、MIRROR把卷组的源LV镜像到新PV
# chdev -l hdisk2 -a pv=yes
hdisk2 changed
# extendvg vg1 hdisk2
# mirrorvg vg1 hdisk2
0516-1804 chvg: The quorum change takes effect immediately.
5、确认MIRROR是否完成
# lsvg -p vg1
vg1:
PV_NAME PV STATE TOTAL PPs FREE PPs FREE DISTRIBUTION
hdisk1 active 59 57 12..10..11..12..12
hdisk2 active 59 59 12..12..11..12..12
# lspv hdisk2
PHYSICAL VOLUME: hdisk2 VOLUME GROUP: vg1
PV IDENTIFIER: 0003744f6c0b61b1 VG IDENTIFIER 0003744f00004c0000000128c73228dd
PV STATE: active
STALE PARTITIONS: 0 ALLOCATABLE: yes
PP SIZE: 16 megabyte(s) LOGICAL VOLUMES: 0
TOTAL PPs: 59 (944 megabytes) VG DESCRIPTORS: 1
FREE PPs: 59 (944 megabytes) HOT SPARE: no
USED PPs: 0 (0 megabytes) MAX REQUEST: 256 kilobytes
FREE DISTRIBUTION: 12..12..11..12..12
USED DISTRIBUTION: 00..00..00..00..00
# lspv hdisk1
PHYSICAL VOLUME: hdisk1 VOLUME GROUP: vg1
PV IDENTIFIER: 0003744f6c0b4091 VG IDENTIFIER 0003744f00004c0000000128c73228dd
PV STATE: active
STALE PARTITIONS: 0 ALLOCATABLE: yes
PP SIZE: 16 megabyte(s) LOGICAL VOLUMES: 1
TOTAL PPs: 59 (944 megabytes) VG DESCRIPTORS: 2
FREE PPs: 57 (912 megabytes) HOT SPARE: no
USED PPs: 2 (32 megabytes) MAX REQUEST: 256 kilobytes
FREE DISTRIBUTION: 12..10..11..12..12
USED DISTRIBUTION: 00..02..00..00..00
注意到STALE PV为0，QUORUM已经DISABLED
# lsvg vg1
VOLUME GROUP: vg1 VG IDENTIFIER: 0003744f00004c0000000128c73228dd
VG STATE: active PP SIZE: 16 megabyte(s)
VG PERMISSION: read/write TOTAL PPs: 118 (1888 megabytes)
MAX LVs: 256 FREE PPs: 114 (1824 megabytes)
LVs: 1 USED PPs: 4 (64 megabytes)
OPEN LVs: 1 QUORUM: 1 (Disabled)
TOTAL PVs: 2 VG DESCRIPTORS: 3
STALE PVs: 0 STALE PPs: 0
ACTIVE PVs: 2 AUTO ON: no
Concurrent: Enhanced-Capable Auto-Concurrent: Disabled
VG Mode: Non-Concurrent
MAX PPs per VG: 32768 MAX PVs: 1024
LTG size (Dynamic): 256 kilobyte(s) AUTO SYNC: no
HOT SPARE: no BB POLICY: relocatable
LV的COPY为2份
# lslv tbs1
LOGICAL VOLUME: tbs1 VOLUME GROUP: vg1
LV IDENTIFIER: 0003744f00004c0000000128c73228dd.1 PERMISSION: read/write
VG STATE: active/complete LV STATE: opened/syncd
TYPE: raw WRITE VERIFY: off
MAX LPs: 512 PP SIZE: 16 megabyte(s)
COPIES: 2 SCHED POLICY: parallel
LPs: 2 PPs: 4
STALE PPs: 0 BB POLICY: relocatable
INTER-POLICY: minimum RELOCATABLE: yes
INTRA-POLICY: middle UPPER BOUND: 1024
MOUNT POINT: N/A LABEL: None
MIRROR WRITE CONSISTENCY: on/ACTIVE
EACH LP COPY ON A SEPARATE PV ?: yes
Serialize IO ?: NO
DEVICESUBTYPE : DS_LVZ
LV已经同时存在于2个PV
# lslv -l tbs1
tbs1:N/A
PV COPIES IN BAND DISTRIBUTION
hdisk1 002:000:000 100% 000:002:000:000:000
hdisk2 002:000:000 100% 000:002:000:000:000
6、UNMIRRO打破镜像
# unmirrorvg vg1 hdisk1
0516-1804 chvg: The quorum change takes effect immediately.
注意，UNMIRROR之后，QUORUM已经是ENABLED状态
# lsvg vg1
VOLUME GROUP: vg1 VG IDENTIFIER: 0003744f00004c0000000128c73228dd
VG STATE: active PP SIZE: 16 megabyte(s)
VG PERMISSION: read/write TOTAL PPs: 118 (1888 megabytes)
MAX LVs: 256 FREE PPs: 116 (1856 megabytes)
LVs: 1 USED PPs: 2 (32 megabytes)
OPEN LVs: 1 QUORUM: 2 (Enabled)
TOTAL PVs: 2 VG DESCRIPTORS: 3
STALE PVs: 0 STALE PPs: 0
ACTIVE PVs: 2 AUTO ON: no
Concurrent: Enhanced-Capable Auto-Concurrent: Disabled
VG Mode: Non-Concurrent
MAX PPs per VG: 32768 MAX PVs: 1024
LTG size (Dynamic): 256 kilobyte(s) AUTO SYNC: no
HOT SPARE: no BB POLICY: relocatable
LV已经迁移到HDISK2上，并且COPY为1份
# lslv -l tbs1
tbs1:N/A
PV COPIES IN BAND DISTRIBUTION
hdisk2 002:000:000 100% 000:002:000:000:000
# lslv tbs1
LOGICAL VOLUME: tbs1 VOLUME GROUP: vg1
LV IDENTIFIER: 0003744f00004c0000000128c73228dd.1 PERMISSION: read/write
VG STATE: active/complete LV STATE: opened/syncd
TYPE: raw WRITE VERIFY: off
MAX LPs: 512 PP SIZE: 16 megabyte(s)
COPIES: 1 SCHED POLICY: parallel
LPs: 2 PPs: 2
STALE PPs: 0 BB POLICY: relocatable
INTER-POLICY: minimum RELOCATABLE: yes
INTRA-POLICY: middle UPPER BOUND: 1024
MOUNT POINT: N/A LABEL: None
MIRROR WRITE CONSISTENCY: on/ACTIVE
EACH LP COPY ON A SEPARATE PV ?: yes
Serialize IO ?: NO
DEVICESUBTYPE : DS_LVZ
# reducevg vg1 hdisk2
# lspv
hdisk0 0003744f5808d0c8 rootvg active
hdisk1 0003744f6c0b4091 vg1 active
hdisk2 0003744f6c0b61b1 None
hdisk3 0003744f80dcb788 None
hdisk4 0003744f80e36ace None
检查迁移前插入的数据
SQL> select * from mytable1;
ID NAME
---------- --------------
1 aixchina.net
SQL> insert into mytable1 values(2,'myguangzhou');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from mytable1;
ID NAME
---------- --------------
1 aixchina.net
2 myguangzhou
7、使用migratepv来迁移更简单
闲的无聊，想把数据迁移回原PV
这次不使用MIRROR了，直接使用MIGRATEPV更直接更简单
# migratepv -l tbs1 hdisk2 hdisk1
# lslv -l tbs1
tbs1:N/A
PV COPIES IN BAND DISTRIBUTION
hdisk1 002:000:000 100% 000:002:000:000:000
#
迁移过程中Alert.log没有任何警告信息
$ tail -20 $ORACLE_BASE/admin/myoracle/bdump/alert*
ORA-959 signalled during: drop tablespace tbs3 including contents and datafiles...
Sun May 23 16:49:20 2010
drop tablespace FS_TBS3 including contents and datafiles
Sun May 23 16:49:21 2010
Deleted file /dev/rscal_lv_tbs3
Completed: drop tablespace FS_TBS3 including contents and datafiles
Sun May 23 16:49:48 2010
drop tablespace tbs1 including contents and datafiles
Sun May 23 16:49:54 2010
Deleted file /dev/roriginal_tbs1
Completed: drop tablespace tbs1 including contents and datafiles
Sun May 23 16:50:28 2010
drop tablespace tbs2 including contents and datafiles
Sun May 23 16:50:29 2010
Deleted file /dev/rscalable_tbs2
Completed: drop tablespace tbs2 including contents and datafiles
Sun May 23 17:10:57 2010
create tablespace mytbs1 datafile '/dev/rtbs1' size 20M
Sun May 23 17:11:01 2010
Completed: create tablespace mytbs1 datafile '/dev/rtbs1' size 20M收起