数据库迁移至ASM
本实验主要介绍将数据库迁移到ASM的过程，并不介绍ASM实例的搭建。实验环境：solaris10+oracle 10R
1.迁移前期准备
1.1 ASM实例搭建(略)
1.2 磁盘分区使用说明
| RDBMS 实例 | ASM实例 | |
| /dev/dsk/c1t0d0s0 | /u01/oracle | |
| /dev/rdsk/c2t0d0s1 | data01：数据文件,控制文件,联机日志 | |
| /dev/rdsk/c2t1d0s1 | ||
| /dev/rdsk/c2t2d0s1 | data02：控制文件，recovery area | |
| /dev/rdsk/c2t3d0s1 |
2. RDBMS迁移到ASM
2.1备份RDBMS至data01
bash-3.00$ rman target / Recovery Manager: Release 10.2.0.2.0 - Production on Mon Apr 15 12:22:31 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: SUN (DBID=1913551800) --控制文件自动备份关闭 RMAN> show all; RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backup/ctl_%d_%F'; CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/oracle/dbs/snapcf_sun.f'; # default RMAN> --备份全库 RMAN> backup as copy database format '+DATA01'; Starting backup at 15-APR-13 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 ........ channel ORA_DISK_1: starting datafile copy --在全备情况下，即使自动备份关闭，控制文件，SPFILE也会被备份 copying current control file output filename=+DATA01/sun/controlfile/backup.266.812809561 tag=TAG20130415T122257 recid=83 stamp=812809563 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current SPFILE in backupset channel ORA_DISK_1: starting piece 1 at 15-APR-13 channel ORA_DISK_1: finished piece 1 at 15-APR-13 piece handle=+DATA01/sun/backupset/2013_04_15/nnsnf0_tag20130415t122257_0.267.812809565 tag=TAG20130415T122257 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 Finished backup at 15-APR-13
2.2修改闪回目录及大小
1 SQL> show parameter db_recovery_ 2 3 NAME TYPE VALUE 4 ------------------------------------ ----------- ------------------------------ 5 db_recovery_file_dest string /u01/flash_recovery_area 6 db_recovery_file_dest_size big integer 2G 7 SQL> alter system set db_recovery_file_dest='+DATA02' scope=spfile; 8 9 System altered. 10 11 SQL> alter system set db_recovery_file_dest_size=500M scope=spfile; 12 13 System altered. 14 15 SQL>
2.3修改联机日志，自动创建数据文件目录
1 SQL> show parameter db_create 2 3 NAME TYPE VALUE 4 ------------------------------------ ----------- ------------------------------ 5 db_create_file_dest string 6 db_create_online_log_dest_1 string 7 db_create_online_log_dest_2 string 8 db_create_online_log_dest_3 string 9 db_create_online_log_dest_4 string 10 db_create_online_log_dest_5 string 11 SQL> alter system set db_create_file_dest='+DATA01' scope=spfile; 12 13 System altered. 14 15 SQL> alter system set db_create_online_log_dest_1='+DATA01' scope=spfile; 16 17 System altered. 18 19 SQL>
2.4在每个联机日志组中添加一个日志文件
1 SQL> alter database add logfile member '+DATA01' to group 1; 2 3 Database altered. 4 5 SQL> alter database add logfile member '+DATA01' to group 2; 6 7 Database altered. 8 9 SQL> alter database add logfile member '+DATA01' to group 3; 10 11 Database altered. 12 13 --查看结果 14 SQL> select group#,member from v$logfile order by 1; 15 16 GROUP# MEMBER 17 ---------- --------------------------------------------- 18 1 /u01/oradata/sunbak/redo01.log 19 1 +DATA01/sun/onlinelog/group_1.268.812811035 20 2 +DATA01/sun/onlinelog/group_2.269.812811077 21 2 /u01/oradata/sunbak/redo02.log 22 3 +DATA01/sun/onlinelog/group_3.270.812811139 23 3 /u01/oradata/sunbak/redo03.log 24 25 6 rows selected. 26 27 SQL> 28 29 4.5删除原来的联机日志 30 --联机日志的状态必须是INACTIVE时，删除才能成功 31 --利用以下sql，将联机日志状态变为INACTIVE(v$log.status) 32 SQL> alter system switch logfile; 33 SQL> alter system checkpoint; 34 SQL> select GROUP#,STATUS from v$log; 35 36 GROUP# STATUS 37 ---------- ---------------- 38 1 INACTIVE 39 2 INACTIVE 40 3 CURRENT 41 42 SQL> alter database drop logfile member '/u01/oradata/sunbak/redo01.log'; 43 SQL> alter database drop logfile member '/u01/oradata/sunbak/redo02.log'; 44 SQL> alter database drop logfile member '/u01/oradata/sunbak/redo03.log';
2.5 迁移临时表空间至ASM
1 查看当前临时表空间配置 2 SQL> select ts#,bytes/1024/1024,name from v$tempfile; 3 4 TS# BYTES/1024/1024 NAME 5 ---------- --------------- ---------------------------------------- 6 3 20 /u01/oradata/sunbak/temp01.dbf 7 8 SQL> select ts#,name from v$tablespace where ts#=3; 9 10 TS# NAME 11 ---------- ---------------------------------------- 12 3 TEMP 13 SQL>
2.5.1 给临时表空间添加文件
1 SQL> alter tablespace temp add tempfile '+DATA01' size 50M; 2 3 Tablespace altered. 4 5 SQL> select name from v$tempfile; 6 7 NAME 8 ---------------------------------------- 9 +DATA01/sun/tempfile/temp.271.812812791 10 /u01/oradata/sunbak/temp01.dbf 11 12 SQL>
3.把控制文件，数据文件迁移至ASM
3.1 记录当前控制文件配置
1 SQL> show parameter control 2 3 NAME TYPE VALUE 4 ------------------------------------ ----------- ------------------------------ 5 control_file_record_keep_time integer 7 6 control_files string /u01/oradata/sunbak/control01. 7 ctl, /u01/oradata/sunbak/contr 8 ol02.ctl, /u01/oradata/sunbak/ 9 control03.ctl 10 SQL>
3.2 修改控制文件的目录为'+DATA01','+DATA02'
1 SQL> alter system set control_files='+DATA01/sun/CONTROLFILE/control01','+DATA02/sun/CONTROLFILE/control02' scope=spfile; 2 3 System altered. 4 5 SQL>
3.3 关闭数据库
1 SQL> shutdown immediate 2 Database closed. 3 Database dismounted. 4 ORACLE instance shut down. 5 SQL>
3.4 将数据库起到nomount状态
1 SQL> startup nomount 2 ORACLE instance started. 3 4 Total System Global Area 289406976 bytes 5 Fixed Size 1279820 bytes 6 Variable Size 109054132 bytes 7 Database Buffers 176160768 bytes 8 Redo Buffers 2912256 bytes 9 SQL>
3.5 恢复新的控制文件
1 bash-3.00$ rman target / 2 3 Recovery Manager: Release 10.2.0.2.0 - Production on Mon Apr 15 13:32:13 2013 4 5 Copyright (c) 1982, 2005, Oracle. All rights reserved. 6 7 connected to target database: sun (not mounted) 8 9 RMAN> restore controlfile from '/u01/oradata/sunbak/control01.ctl'; 10 11 Starting restore at 15-APR-13 12 using target database control file instead of recovery catalog 13 allocated channel: ORA_DISK_1 14 channel ORA_DISK_1: sid=156 devtype=DISK 15 16 channel ORA_DISK_1: copied control file copy 17 output filename=+DATA01/sun/controlfile/control01 18 output filename=+DATA02/sun/controlfile/control02 19 Finished restore at 15-APR-13 20 21 RMAN>
3.6 将数据库启到mount
1 RMAN> alter database mount; 2 3 database mounted 4 released channel: ORA_DISK_1 5 6 RMAN>
3.7 切换数据文件到拷贝数据文件
switch database to copy 文档中给的解释：Renames the datafiles and control files to use the filenames of image copies of these files. RMAN switches to the latest image copy of each file.After a database switch, RMAN considers the previous database files as datafile copies.
1 RMAN> switch database to copy; 2 3 datafile 1 switched to datafile copy "+DATA01/sun/datafile/system.265.812809377" 4 datafile 2 switched to datafile copy "+DATA01/sun/datafile/undotbs1.261.812809543" 5 datafile 3 switched to datafile copy "+DATA01/sun/datafile/sysaux.264.812809463" 6 datafile 4 switched to datafile copy "+DATA01/sun/datafile/users.262.812809557" 7 datafile 5 switched to datafile copy "+DATA01/sun/datafile/example.263.812809507" 8 datafile 6 switched to datafile copy "+DATA01/sun/datafile/sun01.260.812809551" 9 datafile 7 switched to datafile copy "+DATA01/sun/datafile/sun02.258.812809553" 10 datafile 8 switched to datafile copy "+DATA01/sun/datafile/sun03.257.812809557" 11 datafile 9 switched to datafile copy "+DATA01/sun/datafile/users.259.812809559" 12 datafile 10 switched to datafile copy "+DATA01/sun/datafile/users.256.812809561" 13 14 RMAN>
3.8 数据库恢复：我是用备份做的迁移，恢复是必须的！
1 RMAN> recover database; 2 3 Starting recover at 15-APR-13 4 allocated channel: ORA_DISK_1 5 channel ORA_DISK_1: sid=156 devtype=DISK 6 7 starting media recovery 8 9 archive log thread 1 sequence 13 is already on disk as file /u01/admin/sun/arch/1_13_808092233.dbf 10 archive log thread 1 sequence 14 is already on disk as file /u01/admin/sun/arch/1_14_808092233.dbf 11 archive log thread 1 sequence 15 is already on disk as file /u01/admin/sun/arch/1_15_808092233.dbf 12 archive log thread 1 sequence 16 is already on disk as file /u01/admin/sun/arch/1_16_808092233.dbf 13 archive log thread 1 sequence 17 is already on disk as file /u01/admin/sun/arch/1_17_808092233.dbf 14 archive log thread 1 sequence 18 is already on disk as file /u01/admin/sun/arch/1_18_808092233.dbf 15 archive log thread 1 sequence 19 is already on disk as file /u01/admin/sun/arch/1_19_808092233.dbf 16 archive log thread 1 sequence 20 is already on disk as file /u01/admin/sun/arch/1_20_808092233.dbf 17 archive log thread 1 sequence 21 is already on disk as file /u01/admin/sun/arch/1_21_808092233.dbf 18 archive log thread 1 sequence 22 is already on disk as file /u01/admin/sun/arch/1_22_808092233.dbf 19 archive log filename=/u01/admin/sun/arch/1_13_808092233.dbf thread=1 sequence=13 20 archive log filename=/u01/admin/sun/arch/1_14_808092233.dbf thread=1 sequence=14 21 archive log filename=/u01/admin/sun/arch/1_15_808092233.dbf thread=1 sequence=15 22 archive log filename=/u01/admin/sun/arch/1_16_808092233.dbf thread=1 sequence=16 23 archive log filename=/u01/admin/sun/arch/1_17_808092233.dbf thread=1 sequence=17 24 archive log filename=/u01/admin/sun/arch/1_18_808092233.dbf thread=1 sequence=18 25 archive log filename=/u01/admin/sun/arch/1_19_808092233.dbf thread=1 sequence=19 26 archive log filename=/u01/admin/sun/arch/1_20_808092233.dbf thread=1 sequence=20 27 media recovery complete, elapsed time: 00:00:20 28 Finished recover at 15-APR-13 29 30 RMAN>
4.打开数据库
1 RMAN> alter database open; 2 3 database opened 4 5 RMAN>
5.验证
1 --控制文件 2 SQL> select name from v$controlfile; 3 4 NAME 5 -------------------------------------------------------------------------------- 6 +DATA01/sun/controlfile/control01 7 +DATA02/sun/controlfile/control02 8 9 SQL> 10 --数据文件 11 SQL> select name,status from v$datafile; 12 13 NAME STATUS 14 --------------------------------------------- ------- 15 +DATA01/sun/datafile/system.265.812809377 SYSTEM 16 +DATA01/sun/datafile/undotbs1.261.812809543 ONLINE 17 +DATA01/sun/datafile/sysaux.264.812809463 ONLINE 18 +DATA01/sun/datafile/users.262.812809557 ONLINE 19 +DATA01/sun/datafile/example.263.812809507 ONLINE 20 +DATA01/sun/datafile/sun01.260.812809551 ONLINE 21 +DATA01/sun/datafile/sun02.258.812809553 ONLINE 22 +DATA01/sun/datafile/sun03.257.812809557 ONLINE 23 +DATA01/sun/datafile/users.259.812809559 ONLINE 24 +DATA01/sun/datafile/users.256.812809561 ONLINE 25 26 10 rows selected. 27 28 SQL> 29 --临时表空间 30 SQL> select name from v$tempfile; 31 32 NAME 33 --------------------------------------------- 34 +DATA01/sun/tempfile/temp.271.812812791 35 /u01/oradata/sunbak/temp01.dbf 36 37 SQL>
6.删除多余文件
1 --删除原来的联机日志 2 --联机日志的状态必须是INACTIVE时，删除才能成功 3 --利用以下sql，将联机日志状态变为INACTIVE(v$log.status) 4 SQL> alter system switch logfile; 5 SQL> alter system checkpoint; 6 SQL> select GROUP#,STATUS from v$log; 7 8 GROUP# STATUS 9 ---------- ---------------- 10 1 INACTIVE 11 2 INACTIVE 12 3 CURRENT 13 14 SQL> alter database drop logfile member '/u01/oradata/sunbak/redo01.log'; 15 SQL> alter database drop logfile member '/u01/oradata/sunbak/redo02.log'; 16 SQL> alter database drop logfile member '/u01/oradata/sunbak/redo03.log'; 17 18 --删除临时文件 19 20 SQL> alter database tempfile '/u01/oradata/sunbak/temp01.dbf' drop; 21 22 Database altered. 23 24 SQL>
至此，迁移完成。本实验主要参考：《大话 oracle rac》
