--==================================
-- 使用RMAN迁移文件系统数据库到ASM
--==================================
在实际的工作过程中，由于ASM磁盘管理的便利性，因此很多时候需要将文件系统的数据库迁移到ASM，本文演示了如何将文件系统数据库迁移到ASM实例。
有关如何创建ASM实例及ASM磁盘管理请参考
创建ASM实例及数据库
ASM 磁盘组及磁盘的管理
一、主要步骤(假定ASM实例已创建)
1.计算目标数据库(文件系统)的大小
2.根据目标数据库的大小，为新ASM数据库(辅助数据库)准备可用磁盘空间
3.为辅助数据库配置初始化参数文件，密码文件，创建目录
4.备份目标数据库
5.迁移目标数据库到辅助数据库
二、实施迁移
本次迁移在同一台主机实现，因此采用不同的ORACLE_SID
环境：Oracle Linux 5.4 + Oracle 10g R2
目标数据库：orcl
辅助数据库：orclasm
1.计算目标数据库(文件系统)的大小
SQL> show parameter db_name
NAMETYPEVALUE
------------------------------------ ----------- ------------------------------
db_name stringorcl
SQL> select * from v$version whererownum<2;
BANNER
----------------------------------------------------------------
Oracle Database10g Enterprise Edition Release 10.2.0.4.0 - Prod
SQL> selectsum(bytes)/1024/1024 ||'MB'from dba_segments;
SUM(BYTES)/1024/1024||'MB'
------------------------------------------
1195.5MB
2.为辅助数据库准备可用空间，下面显示DG1中有3016MB可用空间，可以满足迁移的需要
ASMCMD> ls -s
Sector BlockAU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
512409610485766134603203016 0DG1/
5124096104857620471997019970REV/
3.配置辅助数据库
a.启动目标数据库，并为目标数据库创建pfile
[oracle@oradb ~]$ echo $ORACLE_SID
orcl
[oracle@oradb ~]$ sqlplus / assysdba
idle> startup
sys@ORCL> createpfilefromspfile;
b.复制目标数据库的pfile来生成辅助数据库的pfile并对其进行修改
[oracle@oradb dbs]$ cd $ORACLE_HOME/dbs
[oracle@oradb dbs]$ cp initorcl.ora initorclasm.ora
修改initorclasm.ora
对文件中所有的orcl使用替换命令替换为orclasm(使用vi工具 :%s/orcl/orclasm/g来替换)
修改控制文件为1个(磁盘DG1使用了normal redundancy)，路径为'+DG1/orclasm/controlfile/'--使用ASM应注意目录结构的变化
修改db_recovery_file_dest路径为'+REV'
修改log_archive_dest_1路径为'LOCATION=+REV/orclasm'
修改db_create_file_dest路径为'+DG1'
增加下列参数(恢复完毕后清除)
*.db_file_name_convert=("orcl","orclasm")
*.log_file_name_convert=("orcl","orclasm")
下面列出发生变化的几个重要参数
*.audit_file_dest='/u01/app/oracle/admin/orclasm/adump'
*.background_dump_dest='/u01/app/oracle/admin/orclasm/bdump'
*.control_files='+DG1/orclasm/controlfile/control01.ctl'
*.core_dump_dest='/u01/app/oracle/admin/orclasm/cdump'
*.db_name='orclasm'
*.db_recovery_file_dest='+REV/orclasm'
*.log_archive_dest_1='LOCATION=+REV/orclasm'
*.user_dump_dest='/u01/app/oracle/admin/orclasm/udump'
*.db_create_file_dest='+DG1'
*.db_file_name_convert=("orcl","orclasm")
*.log_file_name_convert=("orcl","orclasm")
c.根据刚刚修改过的参数创建目录
[oracle@oradb ~]$ mkdir -p $ORACLE_BASE/admin/orclasm/{a,b,c,u}dump
[oracle@oradb ~]$ ls $ORACLE_BASE/admin/orclasm
adump bdump cdump udump
d.创建密码文件
[oracle@oradb dbs]$ orapwd file=$ORACLE_HOME/dbs/orapworclasm password=oracle entries=8
4.备份目标数据库
a.目标数据库应处于归档模式下
SQL> selectname,log_mode from v$database;
NAMELOG_MODE
--------- ------------
ORCL ARCHIVELOG
b.连接到RMAN并进行备份，此处使用了非catalog方式
[oracle@oradb ~]$ uniread rman target sys/oracle@orcl nocatalog
RMAN> crosscheck archivelogall; --校验归档日志
RMAN> delete expired archivelogall; --删除无效的归档日志
RMAN> report obsolete;
RMAN> delete noprompt obsolete; --删除废弃的备份
RMAN> show channel; --查看缺省的备份路径
RMAN configuration parameters are:
CONFIGURE CHANNEL DEVICE TYPEDISK FORMAT '/u01/bk/orcl/data_%d_%U';
RMAN> showcontrolfile autobackup; --查看控制文件的自动备份启用情况
using target database control fileinsteadofrecovery catalog
RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> showcontrolfile autobackup format; --查看控制文件的备份路径、格式
RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPEDISKTO'/u01/bk/orcl/auto_ctl_%d_%F';
RMAN> run{ --对目标数据库进行备份，此备份为0级增量包含了归档日志，控制文件spfile将自动备份
2> allocate channel ch1 device typedisk;
3> backupas compressed backupset
4> incremental level0database format '/u01/bk/orcl/data_%d_%U'
5> plus archivelog format '/u01/bk/orcl/bk_lg_%U'
6> tag='Inc0_log';
7> release channel ch1;}
5.迁移目标数据到ASM
a.查看目标数据库的SEQUENCE，当前为10，便于恢复时指定SEQUENCE
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
119524288001YES ACTIVE 52044530-OCT-10
2110524288001NOCURRENT52058530-OCT-10
b.查看目标数据库的数据文件的相关信息，后续需要为datafile指定文件名
RMAN> reportschema;
Reportofdatabaseschema
ListofPermanentDatafiles
===========================
FileSize(MB) TablespaceRB segs DatafileName
---- -------- -------------------- ------- ------------------------
1480SYSTEM*** /u01/app/oracle/oradata/orcl/system01.dbf
225UNDOTBS1 *** /u01/app/oracle/oradata/orcl/undotbs01.dbf
3240SYSAUX*** /u01/app/oracle/oradata/orcl/sysaux01.dbf
45USERS*** /u01/app/oracle/oradata/orcl/users01.dbf
5100EXAMPLE *** /u01/app/oracle/oradata/orcl/example01.dbf
ListofTemporary Files
=======================
FileSize(MB) TablespaceMaxsize(MB) TempfileName
---- -------- -------------------- ----------- --------------------
120TEMP 32767/u01/app/oracle/oradata/orcl/temp01.dbf
c.查看asm实例是否已正常提供服务，以及磁盘的状态并启动新的orclasm实例
SQL> show parameter instance_name
NAMETYPEVALUE
------------------------------------ ----------- ------------------------------
instance_name string+ASM
SQL> select group_number,name,state,type,total_mb,free_mb,usable_file_mb from v$asm_diskgroup;
GROUP_NUMBER NAMESTATE TYPETOTAL_MB FREE_MB USABLE_FILE_MB
------------ ------------------------------ ----------- ------ ---------- ---------- --------------
1 DG1 MOUNTED NORMAL6134 60323016
2 REV MOUNTED EXTERN 204719971997
d.启动辅助数据库到nomount状态
[oracle@oradb ~]$ export ORACLE_SID=orclasm
[oracle@oradb ~]$ sqlplus / assysdba
idle> startup nomount
ORACLE instance started.
e.使用RMAN连接到目标数据库和辅助数据库来完成迁移
[oracle@oradb dbs]$ rman auxiliary / target sys/redhat@orcl
connected to target database: ORCL (DBID=1263182651)
connected to auxiliary database: ORCLASM (not mounted)
RMAN> run {
2> allocate auxiliary channel ach1 device typedisk;
3> setuntilsequence10thread1;
4> set newname fordatafile1to'+DG1';
5> set newname fordatafile2to'+DG1';
6> set newname fordatafile3to'+DG1';
7> set newname fordatafile4to'+DG1';
8> set newname fordatafile5to'+DG1';
9> set newname fortempfile1to'+DG1';
10> duplicate target databaseto orclasm logfile
11> group1('+DG1') size5m reuse,
12> group2('+DG1') size5m reuse;
13> }
contentsofMemory Script:
{
Alterclonedatabaseopenresetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at30-OCT-10
6.后续处理
a.查看数据库状态并关闭数据库
[oracle@oradb ~]$ export ORACLE_SID=orclasm
[oracle@oradb ~]$ sqlplus / assysdba
SQL> selectname,open_mode from v$database;
NAMEOPEN_MODE
--------- ----------
ORCLASM READWRITE
SQL> shutdownimmediate;
b.使用vi工具编辑initorclasm.ora
清除下列参数
*.db_file_name_convert=("orcl","orclasm")
*.log_file_name_convert=("orcl","orclasm")
c.启动数据库并创建spfile
SQL> startup
SQL> createspfilefrompfile;
SQL> startup force; --如果是生产库建议先shutdown immediate,然后startup
7.验证迁移结果
SQL> show parameter db_name
NAMETYPEVALUE
------------------------------------ ----------- ------------------------------
db_name stringorclasm
SQL> col name format a60
SQL> selectname,status from v$datafile;
NAMESTATUS
------------------------------------------------------------ -------
+DG1/orclasm/datafile/system.256.752170937 SYSTEM
+DG1/orclasm/datafile/undotbs1.259.752170937 ONLINE
+DG1/orclasm/datafile/sysaux.257.752170937 ONLINE
+DG1/orclasm/datafile/users.260.752170937 ONLINE
+DG1/orclasm/datafile/example.258.752170937 ONLINE
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
11552428802YES INACTIVE 56371627-MAY-11
21652428802NOCURRENT58398527-MAY-11
SQL> select * from v$logfile;
GROUP# STATUS TYPEMEMBERIS_
---------- ------- ------- -------------------------------------------------- ---
2ONLINE+DG1/orclasm/onlinelog/group_2.263.752171103 NO
1 STALE ONLINE+DG1/orclasm/onlinelog/group_1.262.752171103 NO
1 STALE ONLINE+REV/orclasm/onlinelog/group_1.256.752171103 YES
2ONLINE+REV/orclasm/onlinelog/group_2.257.752171105 YES
SQL> select file#,creation_change#, status,enabled,bytes,namefrom v$tempfile;
FILE# CREATION_CHANGE# STATUS ENABLEDBYTES NAME
---------- ---------------- ------- ---------- ---------- --------------------------------------------------
1464714ONLINEREADWRITE20971520 +DG1/orclasm/tempfile/temp.264.752171113
--如果在使用duplicate时没有生成tempfile文件，可以使用下面的方式来手动添加tempfile文件。
altertablespace temp addtempfile'+DG1'size100m autoextendoff;
--可以添加归档目录，并修改一下归档路径
--连接到ASM实例创建归档目录
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
------------------------------ ------------
+ASMSTARTED
SQL> alterdiskgroup REV adddirectory'+REV/orclasm/arch';
Diskgroup altered.
--在orclasm实例中修改归档路径
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
orclasm OPEN
SQL> altersystemset log_archive_dest_1='LOCATION=+REV/orclasm/arch';
System altered.
SQL> selectnamefrom v$archived_log whererownum<2orderby stamp;
NAME
--------------------------------------------------
+REV/orclasm/1_1_752171102.arc
1084
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
