利用RMAN将数据库从文件系统迁移到ASM

一、打开ASM实例

[oracle@ASM ~]$ echo $ORACLE_SID

+ASM

[oracle@ASM ~]$ sqlplus /nolog

 

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 6 01:01:28 2009

 

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

 

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup

ASM instance started

 

Total System Global Area   79691776 bytes

Fixed Size                  1217812 bytes

Variable Size              53308140 bytes

ASM Cache                  25165824 bytes

ASM diskgroups mounted

SQL> select name,state from v$asm_diskgroup;

 

NAME                           STATE

------------------------------ -----------

DATA                           MOUNTED

DGROUP1                       MOUNTED          

二、修改目标数据库(TOASM)的SPFILE

SQL> ALTER SYSTEM SET CONTROL_FILES='+DGROUP1'  SCOPE=SPFILE;

 

System altered.

 

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST='+DGROUP1' SCOPE=SPFILE;

 

System altered.

三、关闭目标数据库

SQL> SHUTDOWN IMMEDIATE

Database closed.

Database dismounted.

ORACLE instance shut down.

四、通过RMAN连接到目标数据库，并启动到NOMOUNT状态

   

[oracle@ASM admin]$ rman target /

 

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Apr 6 01:06:39 2009

 

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

 

connected to target database (not started)

 

RMAN> startup nomount

 

Oracle instance started

 

Total System Global Area     230686720 bytes

 

Fixed Size                     1218676 bytes

Variable Size                 75499404 bytes

Database Buffers             150994944 bytes

Redo Buffers                   2973696 bytes

 

RMAN>

五、还原控制文件到ASM磁盘组,并将数据库启动到MOUNT状态

   

RMAN> RESTORE CONTROLFILE FROM '/u01/oradata/TOASM/control01.ctl';

 

Starting restore at 06-APR-09

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

 

channel ORA_DISK_1: copied control file copy

output filename=+DGROUP1/toasm/controlfile/backup.269.683428107

Finished restore at 06-APR-09

RMAN> ALTER DATABASE MOUNT;

 

database mounted

released channel: ORA_DISK_1

 

RMAN>

六、利用RMAN复制数据文件到ASM磁盘组

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

Starting backup at 06-APR-09

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile copy

input datafile fno=00001 name=/u01/oradata/TOASM/system01.dbf

output filename=+DGROUP1/toasm/datafile/system.258.683428337 tag=TAG20090406T011215 recid=2 stamp=683428412

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25

channel ORA_DISK_1: starting datafile copy

input datafile fno=00003 name=/u01/oradata/TOASM/sysaux01.dbf

output filename=+DGROUP1/toasm/datafile/sysaux.259.683428421 tag=TAG20090406T011215 recid=3 stamp=683428457

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting datafile copy

input datafile fno=00005 name=/u01/oradata/TOASM/example01.dbf

output filename=+DGROUP1/toasm/datafile/example.261.683428467 tag=TAG20090406T011215 recid=4 stamp=683428481

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/oradata/TOASM/undotbs01.dbf

output filename=+DGROUP1/toasm/datafile/undotbs1.260.683428483 tag=TAG20090406T011215 recid=5 stamp=683428486

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile fno=00004 name=/u01/oradata/TOASM/users01.dbf

output filename=+DGROUP1/toasm/datafile/users.262.683428489 tag=TAG20090406T011215 recid=6 stamp=683428491

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile copy

copying current control file

output filename=+DGROUP1/toasm/controlfile/backup.278.683428493 tag=TAG20090406T011215 recid=7 stamp=683428495

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 06-APR-09

channel ORA_DISK_1: finished piece 1 at 06-APR-09

piece handle=+DGROUP1/toasm/backupset/2009_04_06/nnsnf0_tag20090406t011215_0.277.683428497 tag=TAG20090406T011215 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05

Finished backup at 06-APR-09

 

RMAN>

七、利用RMAN的SWITCH 命令修改控制文件内数据文件的指针，使其指向新位置。

   

RMAN> SWITCH DATABASE TO COPY;

 

datafile 1 switched to datafile copy "+DGROUP1/toasm/datafile/system.258.683428337"

datafile 2 switched to datafile copy "+DGROUP1/toasm/datafile/undotbs1.260.683428483"

datafile 3 switched to datafile copy "+DGROUP1/toasm/datafile/sysaux.259.683428421"

datafile 4 switched to datafile copy "+DGROUP1/toasm/datafile/users.262.683428489"

datafile 5 switched to datafile copy "+DGROUP1/toasm/datafile/example.261.683428467"

 

RMAN> RECOVER DATABASE;

 

Starting recover at 06-APR-09

using channel ORA_DISK_1

 

starting media recovery

media recovery complete, elapsed time: 00:00:00

 

Finished recover at 06-APR-09

 

RMAN>

八、打开数据库

   RMAN> ALTER DATABASE OPEN;

 

database opened

   RMAN>

九、迁移临时文件

由于临时文件不会被迁移，所以我们只需要删除原来的增加新的就可以。

 

SQL> SELECT NAME FROM V$TEMPFILE;

 

NAME

--------------------------------------------------

/u01/oradata/TOASM/temp01.dbf

 

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DGROUP1';

 

Tablespace altered.

 

SQL> ALTER TABLESPACE TEMP DROP TEMPFILE '/u01/oradata/TOASM/temp01.dbf';

 

Tablespace altered.

 

SQL> SELECT NAME FROM V$TEMPFILE;

 

NAME

--------------------------------------------------

+DGROUP1/toasm/tempfile/temp.263.683428909

 

SQL>

十、增加新的ONLINE REDOLOGS 到ASM

  SQL> SELECT * FROM V$LOG;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

         1          1          5   10485760          1 YES INACTIVE                473286 06-APR-09

         2          1          6   10485760          1 NO  CURRENT                 474627 06-APR-09

         3          1          4   10485760          1 YES INACTIVE                472097 06-APR-09

 

SQL> ALTER DATABASE ADD LOGFILE '+DGROUP1' SIZE 10M;

 

Database altered.

 

SQL> ALTER DATABASE ADD LOGFILE '+DGROUP1' SIZE 10M;

 

Database altered.

 

SQL> ALTER DATABASE ADD LOGFILE '+DGROUP1' SIZE 10M;

 

Database altered.

 

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

 

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

 

System altered.

 

SQL> ALTER SYSTEM SWITCH LOGFILE;

 

System altered.

 

SQL> SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

ALTER DATABASE DROP LOGFILE GROUP 2

*

ERROR at line 1:

ORA-01624: log 2 needed for crash recovery of instance TOASM (thread 1)

ORA-00312: online log 2 thread 1: '/u01/oradata/TOASM/redo02.log'

 

 

SQL> ALTER SYSTEM CHECKPOINT;

 

System altered.

 

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

 

Database altered.

 

SQL>ALTER DATABASE DROP LOGFILE GROUP 3;

 

Database altered.

SQL> SELECT MEMBER FROM V$LOGFILE;

 

MEMBER

--------------------------------------------------

+DGROUP1/toasm/onlinelog/group_4.275.683429043

+DGROUP1/toasm/onlinelog/group_5.276.683429047

+DGROUP1/toasm/onlinelog/group_6.264.683429049

 

SQL>

十一、最后删除原来的数据库文件

  

[oracle@ASM ~]$ ls -lh /u01/oradata/TOASM/

total 892M

-rw-r-----  1 oracle oinstall 6.8M Apr  6 01:05 control01.ctl

-rw-r-----  1 oracle oinstall 6.8M Apr  6 01:05 control02.ctl

-rw-r-----  1 oracle oinstall 6.8M Apr  6 01:05 control03.ctl

-rw-r-----  1 oracle oinstall 101M Apr  6 01:05 example01.dbf

-rw-r-----  1 oracle oinstall  11M Apr  6 01:17 redo01.log

-rw-r-----  1 oracle oinstall  11M Apr  6 01:24 redo02.log

-rw-r-----  1 oracle oinstall  11M Apr  6 01:17 redo03.log

-rw-r-----  1 oracle oinstall 231M Apr  6 01:05 sysaux01.dbf

-rw-r-----  1 oracle oinstall 481M Apr  6 01:05 system01.dbf

-rw-r-----  1 oracle oinstall  26M Apr  6 01:05 undotbs01.dbf

-rw-r-----  1 oracle oinstall 5.1M Apr  6 01:05 users01.dbf

[oracle@ASM ~]$ rm -rf /u01/oradata/TOASM/*

[oracle@ASM ~]$ ls -lh /u01/oradata/TOASM/

total 0

[oracle@ASM ~]$

迁移完毕！