ASM文件系统迁移

此文是在oracle10g的基础上，将 数据库从 文件系统 迁移到 ASM管理的文件系统上。

 

查看源文件系统

SQL> select * from v$version where rownum<5;

BANNER ITPUB个人空间V I `Y5u3?f/WE
----------------------------------------------------------------
k7I h$E9C/rCG0 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
"`i[4r-En"qz0PL/SQL Release 10.2.0.1.0 - Production ITPUB个人空间r&x1q1@UUo1r6z
CORE    10.2.0.1.0      Production
K iE&}2N}0TNS for Linux: Version 10.2.0.1.0 - Production  

SQL> conn test/test 
ZB"e%Sad(@8z0Connected.

测试数据 ITPUB个人空间j ?{Zl:Hk
SQL> create table test (id number,name varchar2(10));

Table created.

SQL> insert into test values (1,'yallonking');

1 row created.

SQL> commit;

SQL> select file_name from dba_data_files;

FILE_NAME ITPUB个人空间Os#Q Hw,{Q+h
--------------------------------------------------------------------------------
2y1T2pF[ h |0/u01/app/oracle/oradata/ora10gr2/users01.dbf
?'eKy[:h:o4X0/u01/app/oracle/oradata/ora10gr2/sysaux01.dbf
kT)aY*DH(w0/u01/app/oracle/oradata/ora10gr2/undotbs01.dbf
\4r1A"q j0/u01/app/oracle/oradata/ora10gr2/system01.dbf
0c9JL L(Q0/u01/app/oracle/oradata/ora10gr2/test01.dbf

 

查看ASM磁盘组 ITPUB个人空间 X$H a9U;v){
SQL> select * from v$version where rownum<5;

BANNER ITPUB个人空间*i;ZQ^+Y
----------------------------------------------------------------
N9u#o%}+U2[?K0Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
lU)Ojk1~"fVw0PL/SQL Release 10.2.0.1.0 - Production
{3rXpJ|~0CORE    10.2.0.1.0      Production
L LlpIM3A t0TNS for Linux: Version 10.2.0.1.0 - Production

SQL> !
ZKo5s-ZA \1w K0[oracle@rhl5 ~]$ asmcmd ITPUB个人空间.cE}F0A%kpw
ASMCMD> lsdg ITPUB个人空间@V"u4K9{L
State    Type    Rebal  Unbal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Name ITPUB个人空间f:l:ks"N niY
MOUNTED  EXTERN  N      N         512   4096  1048576     20480    20430                0           20430              0  DATA/

ITPUB个人空间o,~6vw g8V9l~#~
------------------------

查看当前文件系统的控制文件路径
L[&j+I:H2{Nu-Z a0SQL> select name from v$controlfile;

NAME
4M u rwK|0-------------------------------------------------------------------------------- ITPUB个人空间?)trC1B2C
/u01/app/oracle/oradata/ora10gr2/control01.ctl
o"cF/K TK1a2p0/u01/app/oracle/oradata/ora10gr2/control02.ctl ITPUB个人空间)?z%I6LZdu8\T0e
/u01/app/oracle/oradata/ora10gr2/control03.ctl

SQL> shutdown immediate ITPUB个人空间Tg F4C!k5_
Database closed.
YN"G3r;U `L-^!vQ0Database dismounted. ITPUB个人空间E^]P hU.y'n\#L
ORACLE instance shut down.
3SZ#O|g WzW0SQL> startup nomount ITPUB个人空间 KT TS"K.^2k8lh0T H
ORACLE instance started.

Total System Global Area  608174080 bytes
,n+pY,w4mG0p#{"q0Fixed Size                  1220844 bytes ITPUB个人空间 Q)lN(Al0h!{
Variable Size             155193108 bytes
(@(u"b7~ o H&Q e)\j8Z)?/p_0Database Buffers          448790528 bytes ITPUB个人空间 s!WjXq4d
Redo Buffers                2969600 bytes ITPUB个人空间Qg Z*z4Bw'e"\ st
SQL> !

转移控制文件
x bg DHlk k_0[oracle@rhl5 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Aug 21 00:56:47 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: ora10gr2 (not mounted)

RMAN> restore controlfile to '+DATA' from '/u01/app/oracle/oradata/ora10gr2/control01.ctl';

Starting restore at 21-AUG-12
jFR DZ^0using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
Z;W1k#M1Il{0Finished restore at 21-AUG-12

ITPUB个人空间9s"qcs_ Y0y!q6[O
查看新的控制文件
D8j4DfFr/R0ASMCMD> pwd ITPUB个人空间f/j'{"g'_@EV,I
+data ITPUB个人空间*Q/C@+S*P
ASMCMD> find -t controlfile . *
5eD2{,n#i$J)~uzdp a0+data/ORA10GR2/CONTROLFILE/backup.256.791859655

修改当前系统的控制文件参数相关 ITPUB个人空间 Z5IMoHb
SQL> alter system set control_files='+data/ORA10GR2/CONTROLFILE/backup.256.791859655' scope=spfile;

System altered.

SQL> shutdown immediate ITPUB个人空间"r` ]bw#u9d Q0J
ORA-01507: database not mounted

ITPUB个人空间 [3r&F*V:t7F3ca
ORACLE instance shut down.

SQL> startup mount; ITPUB个人空间3e(TW/FoD @E6Q2Ft
ORACLE instance started.

Total System Global Area  608174080 bytes ITPUB个人空间y$_I`Dv{TA
Fixed Size                  1220844 bytes ITPUB个人空间s]+i&HE3d v
Variable Size             155193108 bytes ITPUB个人空间u$JZ*EGn}
Database Buffers          448790528 bytes
(mSU/o[_uI0Redo Buffers                2969600 bytes
p(?M%WB8n0Database mounted.
Q-p:O-tp%j0SQL> !

将数据库文件系统 备份到ASM磁盘组 ITPUB个人空间r bb x0_)H|%ATL
[oracle@rhl5 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Aug 21 01:19:21 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: ORA10GR2 (DBID=4093928674, not open)

RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';

Starting backup at 21-AUG-12 ITPUB个人空间.jZ3]cz3P1B6c
using target database control file instead of recovery catalog
w+M*O7_ E{(M2H7c0allocated channel: ORA_DISK_1
vR{[X0{.P0channel ORA_DISK_1: sid=151 devtype=DISK
!eu2vpY1d)i^0channel ORA_DISK_1: starting datafile copy
sFc%xMz:U?b0input datafile fno=00001 name=/u01/app/oracle/oradata/ora10gr2/system01.dbf
6{.~uVY!|.?0output filename=+DATA/ora10gr2/datafile/system.257.791860783 tag=TAG20120821T011941 recid=1 stamp=791860831
|t'o2P:Ak[ Y0channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56
Jn _y^4oX0channel ORA_DISK_1: starting datafile copy
ro$k!c3uP?'a+W0input datafile fno=00003 name=/u01/app/oracle/oradata/ora10gr2/sysaux01.dbf
znn,{f\7@9NP0output filename=+DATA/ora10gr2/datafile/sysaux.258.791860837 tag=TAG20120821T011941 recid=2 stamp=791860867 ITPUB个人空间 a#fU,g \8`{;Z
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:36
q zF/D6Z6ewz&g"eR%R0channel ORA_DISK_1: starting datafile copy ITPUB个人空间'QXV5v`?EX-D
input datafile fno=00002 name=/u01/app/oracle/oradata/ora10gr2/undotbs01.dbf
f+Pb,c5V!Vg D0output filename=+DATA/ora10gr2/datafile/undotbs1.259.791860873 tag=TAG20120821T011941 recid=3 stamp=791860876
&p9xc r G^w;s0channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 ITPUB个人空间f-vQae'n I,c5s4|Mm
channel ORA_DISK_1: starting datafile copy
!AJ)i5z[2Kp!`!E0input datafile fno=00005 name=/u01/app/oracle/oradata/ora10gr2/test01.dbf
(z-ura9zzg0output filename=+DATA/ora10gr2/datafile/test.260.791860881 tag=TAG20120821T011941 recid=4 stamp=791860882 ITPUB个人空间 \v5L*u5v;L@r&i;f
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 ITPUB个人空间m}H)zFI.x
channel ORA_DISK_1: starting datafile copy ITPUB个人空间yn$SG6T
input datafile fno=00004 name=/u01/app/oracle/oradata/ora10gr2/users01.dbf ITPUB个人空间"mA/A}/U&VT/^
output filename=+DATA/ora10gr2/datafile/users.261.791860885 tag=TAG20120821T011941 recid=5 stamp=791860885
wsy4s(?0channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
$sxo.bk q)]t;{0channel ORA_DISK_1: starting datafile copy
|e#h4p7ba'j)E0copying current control file
gx3E3y!IfMr#q0output filename=+DATA/ora10gr2/controlfile/backup.262.791860885 tag=TAG20120821T011941 recid=6 stamp=791860886 ITPUB个人空间\{,i{[
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
h!a)A"E)C R0channel ORA_DISK_1: starting full datafile backupset ITPUB个人空间iP `:oS(\z]G
channel ORA_DISK_1: specifying datafile(s) in backupset
5m!s.T6Z\}c2zV0including current SPFILE in backupset
%SV!vU"{-u0channel ORA_DISK_1: starting piece 1 at 21-AUG-12 ITPUB个人空间7E^uj3M2{
channel ORA_DISK_1: finished piece 1 at 21-AUG-12 ITPUB个人空间8G)X {:li2r
piece handle=+DATA/ora10gr2/backupset/2012_08_21/nnsnf0_tag20120821t011941_0.263.791860889 tag=TAG20120821T011941 comment=NONE ITPUB个人空间j#@h*zms)W0y
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04 ITPUB个人空间 nLvX tP^
Finished backup at 21-AUG-12

将数据库文件系统切换到ASM磁盘组 ITPUB个人空间pt.h[:PF&I3`/D
RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DATA/ora10gr2/datafile/system.257.791860783" ITPUB个人空间c5K x,ui.B \\0F
datafile 2 switched to datafile copy "+DATA/ora10gr2/datafile/undotbs1.259.791860873"
k#|5Q)H+eI0datafile 3 switched to datafile copy "+DATA/ora10gr2/datafile/sysaux.258.791860837"
"y^A6U ~0datafile 4 switched to datafile copy "+DATA/ora10gr2/datafile/users.261.791860885"
%WA} },X"P!B-W0datafile 5 switched to datafile copy "+DATA/ora10gr2/datafile/test.260.791860881"

f l9zz_%Du&[ g0修改临时文件 ITPUB个人空间m'dwXQ
/*
Ea'^`/I {?0run { ITPUB个人空间o9Gs?wD
set newname for tempfile 1 to '+DATA';
\ I jj w;g2O \ b!MJ0set newname for tempfile 2 to '+DATA'; ITPUB个人空间`!U0}Tp:n5H5g:W
... ITPUB个人空间8YI!Bc&{LP'C+[
switch tempfile all;
,`:x0e"o C&cJ0} ITPUB个人空间P7g6R'xB
*/

RMAN> ALTER DATABASE OPEN;

database opened

逐步替换日志文件
B0gd#\z J$O#xh0[oracle@rhl5 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 21 01:27:35 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn /as sysdba
!O_P+\#Bu0Connected.
'?,k5CFPe5s4d0SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

    GROUP# ITPUB个人空间 JB#Wh+B4^HI
---------- ITPUB个人空间 nI;LA@(V.YV:_@ @
MEMBER
2N5S4D5LKju0--------------------------------------------------------------------------------
E%c0H Vc0STATUS
mh6XPdZ7h0----------------
I:Xo ~];g+V0         3
eo5L m(Q k.K0/u01/app/oracle/oradata/ora10gr2/redo03.log ITPUB个人空间4J!D |2Z*~1k[
INACTIVE

         2
q!}w9NGS*^0/u01/app/oracle/oradata/ora10gr2/redo02.log ITPUB个人空间$I?z)jsP;a
UNUSED

    GROUP#
]-Ce)i-n.]l0---------- ITPUB个人空间ma"C"PHB
MEMBER
}T3Rj"m)?0c[3q:T7~0--------------------------------------------------------------------------------
wV R _'R0STATUS ITPUB个人空间 a;~Ke,tc-Sx
----------------

         1 ITPUB个人空间$s-gPH$o O/i
/u01/app/oracle/oradata/ora10gr2/redo01.log
{#ZM$YYO'vG0CURRENT

 

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 1;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 2;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 3;

Database altered.

WpJ*inP0SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

ITPUB个人空间WQ3Z'^"b(e
SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

    GROUP# MEMBER                                        STATUS ITPUB个人空间:l!n@3App@+j*U n
---------- --------------------------------------------- ----------------
:B0x3RG,bx_v)k0         3 /u01/app/oracle/oradata/ora10gr2/redo03.log   INACTIVE ITPUB个人空间's|-adUJ's+W)T lK
         2 /u01/app/oracle/oradata/ora10gr2/redo02.log   CURRENT ITPUB个人空间6q,Y#Y o)r;}!Y2Cj
         1 /u01/app/oracle/oradata/ora10gr2/redo01.log   INACTIVE
{6UOf1~*g&[n3wg0         1 +DATA/ora10gr2/onlinelog/group_1.264.79186145 INACTIVE
0al"f W%XSs"]7N0           9

         2 +DATA/ora10gr2/onlinelog/group_2.265.79186146 CURRENT ITPUB个人空间*MN.O3Y9\Y
           7

         3 +DATA/ora10gr2/onlinelog/group_3.266.79186148 INACTIVE
eT%n y%__0           3

    GROUP# MEMBER                                        STATUS
V7[5x#{)YOsVme+d q,Z0---------- --------------------------------------------- ----------------

7[3G)b g1Ie!E06 rows selected.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/ora10gr2/redo03.log';

Database altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/ora10gr2/redo01.log';

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

    GROUP# MEMBER                                        STATUS ITPUB个人空间|\9x8|@vh&o
---------- --------------------------------------------- ----------------
9Q+wO8qvq/A8J?S+`0         2 /u01/app/oracle/oradata/ora10gr2/redo02.log   INACTIVE
,v^6V B9z.Dnf"q7Q0         1 +DATA/ora10gr2/onlinelog/group_1.264.79186145 INACTIVE
d.DGQ hh@7jy0           9

         2 +DATA/ora10gr2/onlinelog/group_2.265.79186146 INACTIVE
8u-_EqR1r7EF!u0           7

         3 +DATA/ora10gr2/onlinelog/group_3.266.79186148 CURRENT
0o|-_v1P5b;]U;M&_0           3

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/ora10gr2/redo02.log';

Database altered.

ITPUB个人空间i,L"}B[2F
验证结果
x)f;hM2m;N%G0SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

    GROUP# MEMBER                                        STATUS ITPUB个人空间"m LRxZ*@7sn6Uv
---------- --------------------------------------------- ---------------- ITPUB个人空间yx&E iuy%AB
         1 +DATA/ora10gr2/onlinelog/group_1.264.79186145 INACTIVE ITPUB个人空间H JUTNw2v8o
           9

         2 +DATA/ora10gr2/onlinelog/group_2.265.79186146 INACTIVE
W^YX'w"]K0           7

         3 +DATA/ora10gr2/onlinelog/group_3.266.79186148 CURRENT ITPUB个人空间\[+u7r`B&{
           3
"@/VG#{+_ ||N0SQL> select file_name from dba_data_files;

FILE_NAME ITPUB个人空间D^Q9G?+@$W d
--------------------------------------------------------------------------------
a @j~O0+DATA/ora10gr2/datafile/users.261.791860885 ITPUB个人空间.k q5]|T8n!E2zS
+DATA/ora10gr2/datafile/sysaux.258.791860837 ITPUB个人空间ruiY3B5i(n@
+DATA/ora10gr2/datafile/undotbs1.259.791860873
vs5S#x4~ F0+DATA/ora10gr2/datafile/system.257.791860783
(}1Q5EL9s ZUy0+DATA/ora10gr2/datafile/test.260.791860881

SQL> conn test/test ITPUB个人空间([$~u^ S-N
Connected. ITPUB个人空间 [ x$`!wVd4yK
SQL> select * from test;

        ID NAME ITPUB个人空间$t,}&k:Ktd R
---------- ----------
xu^ \t W*`0         1 yallonking
*V:E.]i4A8r2p5{_0

至此，全部OK！