Oracle从文件系统迁移到ASM存储

2015-12-04 14:07:53来源:作者:博客园精华区人点击

环境:RHEL 6.4 + Oracle 11.2.0.4需求:数据库存储由文件系统迁移到ASM

数据库存储迁移到ASM磁盘组

1.1 编辑参数文件指定新的控制文件路径 1.2 启动数据库到nomount模式 1.3 RMAN恢复控制文件 1.4 启动数据库到mount模式 1.5 RMAN Copy 数据文件 1.6 RMAN Switch 数据文件 1.7 RMAN Switch 临时文件,并打开数据库 1.8 迁移重做日志文件 1.9 服务器参数文件,并重启数据库 1.10 验证各文件存储位置

Reference

数据库存储迁移到ASM磁盘组

实验环境前期准备:

文件系统数据库模拟环境《 Oracle 11g静默安装简明版》 ASM实例和磁盘组模拟环境《 单机静默安装GI软件并创建ASM实例和ASM磁盘组》

迁移前原数据库各文件存储信息查询如下:

--控制文件select name from v$controlfile;--数据文件select name from v$datafile;--临时文件select name from v$tempfile;--日志文件select member from v$logfile;--参数文件show parameter pfile--查询结果如下SQL> select name from v$controlfile;NAME--------------------------------------------------------------------------------/u02/oradata/jingyu/control01.ctl/u02/app/oracle/fast_recovery_area/jingyu/control02.ctlSQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/u02/oradata/jingyu/system01.dbf/u02/oradata/jingyu/sysaux01.dbf/u02/oradata/jingyu/undotbs01.dbf/u02/oradata/jingyu/users01.dbfSQL> select name from v$tempfile;NAME--------------------------------------------------------------------------------/u02/oradata/jingyu/temp01.dbfSQL> select member from v$logfile;MEMBER--------------------------------------------------------------------------------/u02/oradata/jingyu/redo03.log/u02/oradata/jingyu/redo02.log/u02/oradata/jingyu/redo01.logSQL> show parameter pfileNAME TYPE VALUE------------------------------------ ----------- ------------------------------spfile string /u02/app/oracle/product/11.2.0 /dbhome_1/dbs/spfilejingyu.ora 1.1 编辑参数文件指定新的控制文件路径 SQL> create pfile='/tmp/pfile.ora' from spfile;SQL> shutdown immediate-- 修改controlfile参数值$ vi /tmp/pfile.oracontrolfile='+DATA1/control01.ctl' 1.2 启动数据库到nomount模式 SQL> startup nomount pfile='/tmp/pfile.ora' 1.3 RMAN恢复控制文件

注意Oracle用户需要有读写ASM磁盘的权限

RMAN> restore controlfile from '/u02/oradata/jingyu/control01.ctl'; 1.4 启动数据库到mount模式 RMAN> alter database mount; 1.5 RMAN Copy 数据文件 RMAN> backup as copy database format '+DATA1'; 1.6 RMAN Switch 数据文件 RMAN> switch database to copy; 1.7 RMAN Switch 临时文件,并打开数据库 RMAN> run {set newname for tempfile 1 to '+DATA1';switch tempfile all;}RMAN> ALTER DATABASE OPEN; 1.8 迁移重做日志文件 set linesize 200col member for a60SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA1' TO GROUP 1;SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA1' TO GROUP 2;SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA1' TO GROUP 3;SQL> ALTER SYSTEM SWITCH LOGFILE;SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u02/oradata/jingyu/redo01.log';SQL> ALTER SYSTEM SWITCH LOGFILE;SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u02/oradata/jingyu/redo02.log';SQL> ALTER SYSTEM SWITCH LOGFILE;SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u02/oradata/jingyu/redo03.log';SQL> ALTER SYSTEM CHECKPOINT;SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#; 1.9 服务器参数文件,并重启数据库 -- 在ASM磁盘组中创建服务器参数文件SQL> create spfile='+DATA1' from pfile='/tmp/pfile.ora';-- 正常关闭数据库SQL> shutdown immediate -- 删除$ORACLE_HOME/dbs/spfilejingyu.ora$ rm $ORACLE_HOME/dbs/spfilejingyu.ora-- 建立$ORACLE_HOME/dbs/initjingyu.ora,编辑内容指定ASM磁盘组中的服务器参数文件。$ vi initjingyu.oraspfile='+DATA1/JINGYU/PARAMETERFILE/spfile.266.897474951'--启动数据库(会自动找到参数文件进而转到ASM磁盘组中的服务器参数文件)SQL> startup 1.10 验证各文件存储位置 select name from v$datafile union allselect name from v$tempfile union allselect member from v$logfile union allselect name from v$controlfile;show parameter pfile

结果如下:

SQL> select name from v$datafile union all 2 select name from v$tempfile union all 3 select member from v$logfile union all 4 select name from v$controlfile;NAME--------------------------------------------------------------------------------+DATA1/jingyu/datafile/system.257.897474123+DATA1/jingyu/datafile/sysaux.258.897474149+DATA1/jingyu/datafile/undotbs1.259.897474165+DATA1/jingyu/datafile/users.261.897474169+DATA1/jingyu/tempfile/temp.262.897474267+DATA1/jingyu/onlinelog/group_1.263.897474349+DATA1/jingyu/onlinelog/group_2.264.897474359+DATA1/jingyu/onlinelog/group_3.265.897474363+DATA1/control01.ctl9 rows selected.Elapsed: 00:00:00.02SQL> show parameter pfileNAME TYPE VALUE------------------------------------ ----------- ------------------------------spfile string +DATA1/jingyu/parameterfile/sp file.266.897474951

至此,数据库存储已经成功由文件系统全部迁移到ASM。

Reference Steps To Migrate/Move a Database From Non-ASM to ASM And Vice-Versa (文档 ID 252219.1)

微信扫一扫

第七城市微信公众平台