Migrating to ASM Using RMAN (实践具体操作过程)
[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