Oracle RAMN迁移ASM详细过程(Migrating to ASM Using RMAN)

一、Migrating to ASM Using RMAN (理论)The following method shows how a primary database can be migrated to ASM from a disk based backup:

[list]
[*]Disable change tracking (only available in Enterprise Edition) if it is currently being used.
[indent]SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;[/indent]
[*]Shutdown the database.
[indent]SQL> SHUTDOWN IMMEDIATE[/indent]
Modify the parameter file of the target database as follows:
[list]
[*]Set the [font=新宋体]DB_CREATE_FILE_DEST and [font=新宋体]DB_CREATE_ONLINE_LOG_DEST_n parameters to the relevant ASM disk groups.
[*]Remove the [font=新宋体]CONTROL_FILES parameter from the spfile so the control files will be moved to the [font=新宋体]DB_CREATE_* destination and the spfile gets updated automatically. If you are using a pfile the [font=新宋体]CONTROL_FILES parameter must be set to the appropriate ASM files or aliases.

[*]Start the database in nomount mode.
[indent]RMAN> STARTUP NOMOUNT[/indent]
[*]Restore the controlfile into the new location from the old location.
[indent]RMAN> RESTORE CONTROLFILE FROM 'old_control_file_name';[/indent]
[*]Mount the database.
[indent]RMAN> ALTER DATABASE MOUNT;[/indent]
[*]Copy the database into the ASM disk group.
[indent]RMAN> BACKUP AS COPY DATABASE FORMAT '+disk_group';[/indent]
[*]Switch all datafile to the new ASM location.
[indent]RMAN> SWITCH DATABASE TO COPY;[/indent]
[*]Open the database.
[indent]RMAN> ALTER DATABASE OPEN;[/indent]
[*]Create new redo logs in ASM and delete the old ones.
[*]Enable change tracking if it was being used.
[indent]SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;[/indent]

[indent]
二、Migrating to ASM Using RMAN (实践具体操作过程)
文件系统迁移至ASM
[oracle@localhost orcl]$ export ORACLE_SID=orcl
[oracle@localhost orcl]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 7 11:55:41 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> show parameter db_create;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> alter system set db_create_file_dest='+diskgroup' scope=spfile;
System altered.
SQL> alter system set db_create_online_log_dest_1='+diskgroup' scope=spfile;
System altered.
SQL> alter system set db_create_online_log_dest_2='+diskgroup' scope=spfile;
System altered.
SQL> alter system set control_files='+DISKGROUP/orcl/control01.ctl' scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost orcl]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jul 7 11:59:06 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
RMAN> restore controlfile from '/oracle/oradata/orcl/control01.ctl';
Starting restore at 07-JUL-08
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=+DISKGROUP/orcl/control01.ctl
Finished restore at 07-JUL-08
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> backup as copy database format '+diskgroup';
Starting backup at 07-JUL-08
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=/oracle/oradata/orcl/system01.dbf
output filename=+DISKGROUP/orcl/datafile/system.256.659448231 tag=TAG20080707T120349 recid=1 stamp=659448263
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/oracle/oradata/orcl/undotbs01.dbf
output filename=+DISKGROUP/orcl/datafile/undotbs1.262.659448267 tag=TAG20080707T120349 recid=2 stamp=659448286
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/oracle/oradata/orcl/sysaux01.dbf
output filename=+DISKGROUP/orcl/datafile/sysaux.269.659448291 tag=TAG20080707T120349 recid=3 stamp=659448301
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/oracle/oradata/orcl/users01.dbf
output filename=+DISKGROUP/orcl/datafile/users.268.659448307 tag=TAG20080707T120349 recid=4 stamp=659448307
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=+DISKGROUP/orcl/controlfile/backup.267.659448307 tag=TAG20080707T120349 recid=5 stamp=659448309
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 07-JUL-08
channel ORA_DISK_1: finished piece 1 at 07-JUL-08
piece handle=+DISKGROUP/orcl/backupset/2008_07_07/nnsnf0_tag20080707t120349_0.266.659448311 tag=TAG20080707T120349 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 07-JUL-08
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DISKGROUP/orcl/datafile/system.256.659448231"
datafile 2 switched to datafile copy "+DISKGROUP/orcl/datafile/undotbs1.262.659448267"
datafile 3 switched to datafile copy "+DISKGROUP/orcl/datafile/sysaux.269.659448291"
datafile 4 switched to datafile copy "+DISKGROUP/orcl/datafile/users.268.659448307"
RMAN> alter database open;
database opened
RMAN> report schema;
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 300 SYSTEM *** +DISKGROUP/orcl/datafile/system.256.659448231
2 200 UNDOTBS1 *** +DISKGROUP/orcl/datafile/undotbs1.262.659448267
3 120 SYSAUX *** +DISKGROUP/orcl/datafile/sysaux.269.659448291
4 5 USERS *** +DISKGROUP/orcl/datafile/users.268.659448307
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /oracle/oradata/orcl/temp01.dbf
迁移临时数据文件
[oracle@localhost orcl]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 7 12:07:48 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter tablespace temp add tempfile '+diskgroup' size 100M;
Tablespace altered.
SQL> alter database tempfile '/oracle/oradata/orcl/temp01.dbf' drop;
Database altered.
下面迁移在线重做日志文件
SQL> select member from v$Logfile;
MEMBER
--------------------------------------------------------------------------------
/oracle/oradata/orcl/redo03.log
/oracle/oradata/orcl/redo02.log
/oracle/oradata/orcl/redo01.log
SQL> alter database add logfile group 4 size 50m;
Database altered.
SQL> alter database add logfile group 5 size 50m;
Database altered.
SQL> alter database add logfile group 6 size 50m;
Database altered.
SQL> select group#,status from v$Log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 UNUSED
3 INACTIVE
4 UNUSED
5 UNUSED
6 UNUSED
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
镜象控制文件

SQL> alter system set control_files='+diskgroup/orcl/control01.ctl','+diskgroup/orcl/control02.ctl' scope=spfile;
System altered.
SQL> alter database backup controlfile to '+diskgroup/orcl/control02.ctl';
Database altered.
最后数据文件验证是否都迁移到ASM上
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DISKGROUP/orcl/onlinelog/group_4.261.659448607
+DISKGROUP/orcl/onlinelog/group_4.271.659448613
+DISKGROUP/orcl/onlinelog/group_5.258.659448631
+DISKGROUP/orcl/onlinelog/group_5.259.659448637
+DISKGROUP/orcl/onlinelog/group_6.263.659448669
+DISKGROUP/orcl/onlinelog/group_6.265.659448673
6 rows selected.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+DISKGROUP/orcl/control01.ctl
+DISKGROUP/orcl/control02.ctl

SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DISKGROUP/orcl/tempfile/temp.260.659448493
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DISKGROUP/orcl/datafile/system.256.659448231
+DISKGROUP/orcl/datafile/undotbs1.262.659448267
+DISKGROUP/orcl/datafile/sysaux.269.659448291
+DISKGROUP/orcl/datafile/users.268.659448307

3、从ASM迁移至文件系统

有时候我们从文件系统迁移到了ASM存储后 因为某些原因,需要重新用回到文件系统
以下步骤就是操作方法:
[oracle@localhost orcl]$sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 7 12:45:36 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter system set db_create_file_dest='' scope=spfile;
System altered.

SQL> alter system set db_create_online_log_dest_1='' scope=spfile;
System altered.
SQL> alter system set db_create_online_log_dest_2='' scope=spfile;
System altered.

SQL> alter system set control_files='/oracle/oradata/orcl/control01.ctl' scope=spfile;
System altered.
SQL> shutdown
Database closed.
Database dismounted.
[oracle@localhost orcl]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jul 7 12:56:17 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
RMAN> restore controlfile from '+DISKGROUP/orcl/control01.ctl' ;
Starting restore at 07-JUL-08
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=/oracle/oradata/orcl/control01.ctl
Finished restore at 07-JUL-08
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> copy datafile '+DISKGROUP/orcl/datafile/system.256.659448231' to '/oracle/oradata/orcl/system01.dbf';
Starting backup at 07-JUL-08
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=+DISKGROUP/orcl/datafile/system.256.659448231
output filename=/oracle/oradata/orcl/system01.dbf tag=TAG20080707T125746 recid=15 stamp=659451492
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
Finished backup at 07-JUL-08
RMAN> copy datafile '+DISKGROUP/orcl/datafile/undotbs1.262.659448267' to '/oracle/oradata/orcl/undotbs1.dbf';
Starting backup at 07-JUL-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=+DISKGROUP/orcl/datafile/undotbs1.262.659448267
output filename=/oracle/oradata/orcl/undotbs1.dbf tag=TAG20080707T125852 recid=16 stamp=659451551
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 07-JUL-08
RMAN> copy datafile '+DISKGROUP/orcl/datafile/sysaux.269.659448291' to '/oracle/oradata/orcl/sysaux01.dbf';
Starting backup at 07-JUL-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=+DISKGROUP/orcl/datafile/sysaux.269.659448291
output filename=/oracle/oradata/orcl/sysaux01.dbf tag=TAG20080707T125929 recid=17 stamp=659451577
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 07-JUL-08
RMAN> copy datafile '+DISKGROUP/orcl/datafile/users.268.659448307' to '/oracle/oradata/orcl/users01.dbf';
Starting backup at 07-JUL-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+DISKGROUP/orcl/datafile/users.268.659448307
output filename=/oracle/oradata/orcl/users01.dbf tag=TAG20080707T130002 recid=18 stamp=659451606
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:05
Finished backup at 07-JUL-08

RMAN> switch datafile 1 to copy;
datafile 1 switched to datafile copy "/oracle/oradata/orcl/system01.dbf"
RMAN> switch datafile 2 to copy;
datafile 2 switched to datafile copy "/oracle/oradata/orcl/undotbs1.dbf"
RMAN> switch datafile 3 to copy;
datafile 3 switched to datafile copy "/oracle/oradata/orcl/sysaux01.dbf"
RMAN> switch datafile 4 to copy;
datafile 4 switched to datafile copy "/oracle/oradata/orcl/users01.dbf"
RMAN> alter database open;
database opened
SQL> alter tablespace temp add tempfile '/oracle/oradata/orcl/temp01.dbf' size 100M;
Tablespace altered.
SQL> alter tablespace temp drop tempfile '+DISKGROUP/orcl/tempfile/temp.260.659448493';
Tablespace altered.
SQL> alter database add logfile group 1 '/oracle/oradata/orcl/redo01.log' size 20M;
Database altered.
SQL> alter database add logfile group 2 '/oracle/oradata/orcl/redo02.log' size 20M;
Database altered.
SQL> alter database add logfile group 3 '/oracle/oradata/orcl/redo03.log' size 20M;
Database altered.
SQL> alter system switch logfile;
System altered.
sql>/
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oracle/oradata/orcl/redo01.log
/oracle/oradata/orcl/redo02.log
/oracle/oradata/orcl/redo03.log
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/orcl/system01.dbf
/oracle/oradata/orcl/undotbs1.dbf
/oracle/oradata/orcl/sysaux01.dbf
/oracle/oradata/orcl/users01.dbf
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/orcl/temp01.dbf
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/orcl/control01.ctl
[/indent]