从文件系统移植数据库到ASM

一、实验版本：

SYS@ning>select * from v$version;

BANNER
---------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

关于ASM实例和磁盘组的设置，请参考官方文档，本实验中，创建了一个名为DG1的ASM磁盘组。如果没有多个硬盘的环境，ASM也可以采用文件

来模拟，如何利用文件来模拟ASM，请参考http://ningoo.itpub.net/post/2149/233340

二、修改控制文件路径到ASM磁盘组

SYS@ning>alter system set control_files='+DG1/ning/control01.ctl' scope=spfile;

System altered.

三、关闭数据库

SYS@ning>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

四、利用rman移植控制文件到ASM

E:/>rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Fri Mar 23 10:48:45 2007

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

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 142606336 bytes

Fixed Size 1289340 bytes
Variable Size 83886980 bytes
Database Buffers 50331648 bytes
Redo Buffers 7098368 bytes

RMAN> restore controlfile from 'd:/oradata/ning/control01.ctl';

Starting restore at 23-MAR-07
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/ning/control01.ctl
Finished restore at 23-MAR-07

五、利用rman copy命令移植数据文件到ASM

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> backup as copy database format '+DG1';

Starting backup at 23-MAR-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=D:/ORADATA/NING/SYSTEM01.DBF
output filename=+DG1/ning/datafile/system.257.617972079 tag=TAG20070323T105438 recid=1 stamp=617972104
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=D:/ORADATA/NING/UNDOTBS01.DBF
output filename=+DG1/ning/datafile/undotbs1.258.617972115 tag=TAG20070323T105438 recid=2 stamp=617972126
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=D:/ORADATA/NING/SYSAUX01.DBF
output filename=+DG1/ning/datafile/sysaux.259.617972131 tag=TAG20070323T105438 recid=3 stamp=617972137
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=D:/ORADATA/NING/USERS01.DBF
output filename=+DG1/ning/datafile/users.260.617972145 tag=TAG20070323T105438 recid=4 stamp=617972146
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DG1/ning/controlfile/backup.261.617972147 tag=TAG20070323T105438 recid=5 stamp=617972149
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 23-MAR-07
channel ORA_DISK_1: finished piece 1 at 23-MAR-07
piece handle=+DG1/ning/backupset/2007_03_23/nnsnf0_tag20070323t105438_0.262.617972151 tag=TAG20070323T105438 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 23-MAR-07

六、利用rman的switch database命令切换数据文件的位置到ASM

这个命令其实相当于一系列的alter database rename file

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DG1/ning/datafile/system.257.617972079"
datafile 2 switched to datafile copy "+DG1/ning/datafile/undotbs1.258.617972115"
datafile 3 switched to datafile copy "+DG1/ning/datafile/sysaux.259.617972131"
datafile 4 switched to datafile copy "+DG1/ning/datafile/users.260.617972145"

七、在ASM上创建新的online redo logfile，然后删除现有文件系统上的online redo logfile

RMAN> exit


Recovery Manager complete.

E:/>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Mar 23 11:01:07 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

@>conn / as sysdba
Connected.
SYS@ning>alter database open;

Database altered.

SYS@ning>alter database add logfile group 4 '+dg1/ning/redo04.log' size 5m;

Database altered.

SYS@ning>alter database add logfile group 5 '+dg1/ning/redo05.log' size 5m;

Database altered.

SYS@ning>alter database add logfile group 6 '+dg1/ning/redo06.log' size 5m;

Database altered.

SYS@ning>select group#,status from v$log;

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
4 UNUSED
5 UNUSED
6 UNUSED

6 rows selected.

SYS@ning>alter system switch logfile;

System altered.

SYS@ning>select group#,status from v$log;

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 CURRENT
5 UNUSED
6 UNUSED

6 rows selected.

SYS@ning>alter database drop logfile group 1;

Database altered.

SYS@ning>alter database drop logfile group 2;

Database altered.

SYS@ning>alter database drop logfile group 3;

Database altered.

八、移植临时表空间到ASM

SYS@ning>select file_name,tablespace_name from dba_temp_files;

FILE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
D:/ORADATA/NING/TEMP01.DBF TEMP

SYS@ning>alter tablespace temp add tempfile '+DG1/ning/TEMP01.DBF' size 100m;

Tablespace altered.

SYS@ning>select file_name,tablespace_name from dba_temp_files;

FILE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
+DG1/ning/temp01.dbf TEMP
D:/ORADATA/NING/TEMP01.DBF TEMP

SYS@ning>alter tablespace temp drop tempfile 'D:/ORADATA/NING/TEMP01.DBF';

Tablespace altered.

SYS@ning>select file_name,tablespace_name from dba_temp_files;

FILE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
+DG1/ning/temp01.dbf TEMP

九、创建更多的控制文件

SYS@ning>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ning>startup mount;
ORACLE instance started.

Total System Global Area 142606336 bytes
Fixed Size 1289340 bytes
Variable Size 83886980 bytes
Database Buffers 50331648 bytes
Redo Buffers 7098368 bytes
Database mounted.
SYS@ning>alter database backup controlfile to '+DG1/ning/control02.ctl';

Database altered.

SYS@ning>alter system set control_files='+DG1/ning/control01.ctl','+DG1/ning/control02.ctl'
2 scope=spfile;

System altered.

SYS@ning>shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SYS@ning>startup
ORACLE instance started.

Total System Global Area 142606336 bytes
Fixed Size 1289340 bytes
Variable Size 83886980 bytes
Database Buffers 50331648 bytes
Redo Buffers 7098368 bytes
Database mounted.

十、移植完成，检查一下

SYS@ning>select name from v$controlfile;

NAME
----------------------------------------
+DG1/ning/control01.ctl
+DG1/ning/control02.ctl

SYS@ning>select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------------
+DG1/ning/datafile/system.257.617972079
+DG1/ning/datafile/undotbs1.258.617972115
+DG1/ning/datafile/sysaux.259.617972131
+DG1/ning/datafile/users.260.617972145

SYS@ning>select group#,member from v$logfile;

GROUP# MEMBER
---------- ----------------------------------------
4 +DG1/ning/redo04.log
5 +DG1/ning/redo05.log
6 +DG1/ning/redo06.log

(