环境:
sys@ORCL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
sys@ORCL> !uname -a
Linux localhost.localdomain 2.6.18-308.el5xen #1 SMP Fri Jan 27 17:59:00 EST 2012 i686 i686 i386 GNU/Linux
① 规划
+DG1:用于存放数据文件、控制文件、联机日志
+DG2:用于存放联机日志
+RECOVERY:用于recovery area
idle> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TOTAL_MB FREE_MB
------------ ---------- --------------------------------- ---------- ----------
1 DG1 MOUNTED 3072 3016
2 DG2 MOUNTED 768 718
3 RECOVERY MOUNTED 2304 2250
② 修改RDBMS参数
idle> alter system set db_recovery_file_dest='+RECOVERY' scope=both;
System altered.
idle> alter system set db_create_file_dest='+DG1' scope=both;
System altered.
idle> alter system set db_create_online_log_dest_1='+DG1' scope=both;
System altered.
idle> alter system set db_create_online_log_dest_2='+DG2' scope=both;
System altered.
③ 日志文件迁移
idle> alter database add logfile group 4 ('+DG1','+DG2') size 80m;
Database altered.
idle> alter database add logfile group 5 ('+DG1','+DG2') size 80m;
Database altered.
idle> alter database add logfile group 6 ('+DG1','+DG2') size 80m;
Database altered.
日志组的状态为inactive时,删除才能成功
idle> alter system checkpoint;
idle> alter database drop logfile group 1;
Database altered.
idle> alter database drop logfile group 2;
Database altered.
idle> alter database drop logfile group 3;
Database altered.
idle> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
4 INACTIVE
5 CURRENT
6 UNUSED
idle> select member from v$logfile;
MEMBER
-----------------------------------------------------------------
+DG1/orcl/onlinelog/group_4.256.798634749
+DG2/orcl/onlinelog/group_4.256.798634753
+DG1/orcl/onlinelog/group_5.259.798634795
+DG2/orcl/onlinelog/group_5.259.798634801
+DG1/orcl/onlinelog/group_6.260.798634825
+DG2/orcl/onlinelog/group_6.260.798634829
④ 临时文件迁移
idle> select file_name,tablespace_name from dba_temp_files;
FILE_NAME TABLESPACE_NAME
------------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_8050j3 TEMP
4j_.tmp
idle> alter tablespace temp add tempfile '+DG1';
Tablespace altered.
idle> select file_name,tablespace_name from dba_temp_files;
FILE_NAME TABLESPACE_NAME
------------------------------------------------------- ------------------------------
+DG1/orcl/tempfile/temp.258.798635383 TEMP
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_8050j3 TEMP
4j_.tmp
idle> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_8050j34j_.tmp';
Tablespace altered.
idle> select file_name,tablespace_name from dba_temp_files;
FILE_NAME TABLESPACE_NAME
------------------------------------------------------- ------------------------------
+DG1/orcl/tempfile/temp.258.798635383 TEMP
⑤ 迁移控制文件、数据文件
在整个迁移过程,只有这一步需要对数据库实例进行关闭和启动
idle> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/ORCL/c
ontrolfile/o1_mf_8050hgfp_.ctl
, /u01/app/oracle/flash_recove
ry_area/ORCL/controlfile/o1_mf
_8050hgqh_.ctl
idle> alter database backup controlfile to '/home/oracle/asmctl02.ctl';
Database altered.
idle> alter system set control_files='+DG1/ORCL/CONTROLFILE/control01','+DG2/ORCL/CONTROLFILE/control02' scope=spfile;
System altered.
idle> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
RMAN> startup nomount;
Oracle instance started
Total System Global Area 419430400 bytes
Fixed Size 1219760 bytes
Variable Size 146801488 bytes
Database Buffers 268435456 bytes
Redo Buffers 2973696 bytes
RMAN> restore controlfile from '/home/oracle/asmctl02.ctl';
Starting restore at 06-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+DG1/orcl/controlfile/control01
output filename=+DG2/orcl/controlfile/control02
Finished restore at 06-NOV-12
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> backup as copy database format '+DG1';
Starting backup at 06-NOV-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_8050fk2z_.dbf
output filename=+DG1/orcl/datafile/system.261.798636159 tag=TAG20121106T112238 recid=2 stamp=798636197
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_8050fk3w_.dbf
output filename=+DG1/orcl/datafile/sysaux.262.798636203 tag=TAG20121106T112238 recid=3 stamp=798636232
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_8050jhm7_.dbf
output filename=+DG1/orcl/datafile/example.263.798636239 tag=TAG20121106T112238 recid=4 stamp=798636252
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_8050fkc6_.dbf
output filename=+DG1/orcl/datafile/undotbs1.264.798636255 tag=TAG20121106T112238 recid=5 stamp=798636257
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_8050fkdh_.dbf
output filename=+DG1/orcl/datafile/users.265.798636261 tag=TAG20121106T112238 recid=6 stamp=798636261
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 06-NOV-12
RMAN-06497: WARNING: control file is not current, control file autobackup skipped
RMAN> recover database;
Starting recover at 06-NOV-12
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 16 is already on disk as file +DG1/orcl/onlinelog/group_5.259.798634795
archive log filename=+DG1/orcl/onlinelog/group_5.259.798634795 thread=1 sequence=16
media recovery complete, elapsed time: 00:00:02
Finished recover at 06-NOV-12
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DG1/orcl/datafile/system.261.798636159"
datafile 2 switched to datafile copy "+DG1/orcl/datafile/undotbs1.264.798636255"
datafile 3 switched to datafile copy "+DG1/orcl/datafile/sysaux.262.798636203"
datafile 4 switched to datafile copy "+DG1/orcl/datafile/users.265.798636261"
datafile 5 switched to datafile copy "+DG1/orcl/datafile/example.263.798636239"
RMAN> alter database open resetlogs;
database opened
⑥ 确认所有文件已经迁移到ASM中
sys@ORCL> select name,status from v$datafile;
NAME STATUS
-------------------------------------------------- -------
+DG1/orcl/datafile/system.261.798636159 SYSTEM
+DG1/orcl/datafile/undotbs1.264.798636255 ONLINE
+DG1/orcl/datafile/sysaux.262.798636203 ONLINE
+DG1/orcl/datafile/users.265.798636261 ONLINE
+DG1/orcl/datafile/example.263.798636239 ONLINE
sys@ORCL> select name from v$controlfile;
NAME
--------------------------------------------------
+DG1/orcl/controlfile/control01
+DG2/orcl/controlfile/control02
sys@ORCL> select member from v$logfile;
MEMBER
----------------------------------------------------------------------------------------------------
+DG1/orcl/onlinelog/group_4.256.798634749
+DG2/orcl/onlinelog/group_4.256.798634753
+DG1/orcl/onlinelog/group_5.259.798634795
+DG2/orcl/onlinelog/group_5.259.798634801
+DG1/orcl/onlinelog/group_6.260.798634825
+DG2/orcl/onlinelog/group_6.260.798634829
6 rows selected.
sys@ORCL> select name,status from v$tempfile;
NAME STATUS
-------------------------------------------------- -------
+DG1/orcl/tempfile/temp.258.798635383 ONLINE
⑦ 最后验证
idle> startup
ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1217836 bytes
Variable Size 57502420 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
sys@ORCL> startup
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219760 bytes
Variable Size 150995792 bytes
Database Buffers 264241152 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
ASMCMD> lsct
DB_Name Status Software_Version Compatible_version Instance_Name
orcl CONNECTED 10.2.0.1.0 10.2.0.1.0 orcl
orcl CONNECTED 10.2.0.1.0 10.2.0.1.0 orcl
小结:
这一次迁移,比起上一篇,改进:
⑴ 多增加了2个磁盘组,多了份故障保证,但由于空间方面,还是选择了external ASM
⑵ 对spfile没有进行迁移
⑶ 对控制文件也作了多路镜像:+DG1和+DG2
Good luck
by Think
2012/11/6