Linux平台Oracle数据库向ASM的迁移
迁移实验的准备:
1、安装完成后Linux下的Oracle数据库
2、一块剩余空间充足的硬盘,(在本实验中,新添加了一个容量为20GB的SCSI硬盘)
说明:
Oracle数据库数据文件位置:/oradata/test
实例名:test
磁盘组名:MAI
一、ASM磁盘组的构建
-- 分区:
将新加的磁盘进行分区(在此本人分为了4个分区,每个分区容量5GB)
fdisk /dev/sdb
/dev/sdb /dev/sdb1
/dev/sdb2 /dev/sdb3
/dev/sdb4
-- 配置裸设备:
编辑/etc/sysconfig/rawdevices
/dev/raw/raw1 /dev/sdb1
/dev/raw/raw2 /dev/sdb2
/dev/raw/raw3 /dev/sdb3
/dev/raw/raw4 /dev/sdb4
-- 重启rawdevices服务:
service rawdevices restart
/etc/init.d/rawdevices restart
-- 查看并修改设备属性:
chown -R oracle:oinstall /dev/raw
ll /dev/raw
crwxr-xr-x 1 oracle oinstall 162, 1 May 14 21:35 raw1
crwxr-xr-x 1 oracle oinstall 162, 2 May 14 21:35 raw2
crwxr-xr-x 1 oracle oinstall 162, 3 May 14 21:35 raw3
crwxr-xr-x 1 oracle oinstall 162, 4 May 14 21:35 raw4
-- 创建ASM磁盘组
dbca &
根据提示创建Automatic Storage Management(ASM)
在此选择低冗余(external)模式
注:在此创建的名称为MAI,Oracle数据库实例名为TEST
二、配置磁盘组
shell> ORACLE_SID=+ASM
shell> asmcmd
ASMCMD> cd MAI
ASMCMD> mkdir controlfile
ASMCMD> mkdir datafile
ASMCMD> mkdir redolog
三、开始迁移
-- 迁移数据文件和控制文件
SQL> startup
设置控制文件的新路径,并保存在spfile中:
SQL> alter system set
control_files='+MAI/controlfile/control01.ctl' scope=spfile;
SQL> shutdown immediate;
RMAN> startup mount;
RMAN> alter database mount;
将当前的控制文件作为源用resotre将控制文件复制到ASM磁盘组中:
RMAN> restore controlfile from
'/oradata/test/control01.ctl';
对当前数据库做镜像拷贝,目标ASM磁盘组:
RMAN> backup as copy database format
'+MAI';
将数据文件切换到ASM的磁盘组中的数据文件:
RMAN> switch database to copy;
-- 迁移日志文件
SQL> alter database open;
添加新的日志文件组:
SQL> alter database add logfile group 4
'+MAI/redolog/redo04.log' size 50m;
SQL> alter database add logfile group 5
'+MAI/redolog/redo05.log' size 50m;
SQL> alter database add logfile group 6
'+MAI/redolog/redo06.log' size 50m;
删除旧的日志文件组:
将非1、2、3号日志文件组作为current日志文件组:
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 INACTIVE
3 INACTIVE
4 ACTIVE
5 CURRENT
6 UNUSED
由于组1当前处于active状态,在此做一个checkpoing来改变其状态:
SQL> alter database checkpoint;
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 ACTIVE
5 CURRENT
6 UNUSED
删除1、2、3日志文件组:
SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;
查看当前的日志文件组状态:
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
4 ACTIVE
5 CURRENT
6 ACTIVE
-- 重建temp表空间:
SQL> alter tablespace temp add tempfile
'+MAI/datafile/TEMP01.DBF' size 100m;
SQL> alter tablespace temp drop tempfile
'/oradata/test/TEMP01.DBF';
SQL> select file_name,tablespace_name from
dba_temp_files;
FILE_NAME TABLESPACE_NAME
--------------------------- -------------------
+MAI/datafile/temp01.dbf TEMP
-- 多路复用控制文件:
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database backup controlfile to
'+MAI/controlfile/control02.ctl';
SQL> alter database backup controlfile to
'+MAI/controlfile/control03.ctl';
SQL> shutdown immediate;
SQL> startup;
至此数据库迁移完成!
四、检查迁移状态:
数据文件:
SQL> select name from v$datafile;
NAME
----------------------------------------
+MAI/test/datafile/system.257.783285585
+MAI/test/datafile/undotbs1.260.783285733
+MAI/test/datafile/sysaux.258.783285671
+MAI/test/datafile/users.263.783285749
+MAI/test/datafile/example.259.783285709
+MAI/test/datafile/for_mai.261.783285741
+MAI/test/datafile/for_mai.262.783285745
控制文件:
SQL> select name from v$controlfile;
NAME
----------------------------------------
+MAI/controlfile/control01.ctl
+MAI/controlfile/control02.ctl
+MAI/controlfile/control03.ctl
重做日志文件:
SQL> select member from v$logfile;
MEMBER
-----------------------------
+MAI/redolog/redo05.log
+MAI/redolog/redo04.log
+MAI/redolog/redo06.log
临时表空间:
SQL> select file_name,tablespace_name from
dba_temp_files;
FILE_NAME TABLESPACE_NAME
--------------------------- -------------------
+MAI/datafile/temp01.dbf TEMP
检验完成!至此就彻底的完成了Linux平台下的Oracle数据库迁移(到ASM磁盘组)