将表空间、数据文件从文件系统迁移到ASM
1. 迁移
数据库的迁移，不管是出于什么原因和需要的进行，唯一需要考虑的两个因素就是：停机时间和存储容量。因为这两个因素往往决定了你可以采用什么样的方式进行数据库迁移：如果停机时间不充裕，可以选择热迁移，但是要求足够的存储空间；如果存储容量不足，可以选择冷迁移，但是要求足够充裕的停机时间。上述两个因素是相互制约的。
下面的例子将多种方式进行迁移试验，从OS File System到ASM，或者反之；从表空间、数据文件和数据库层面。
1.0. 数据库环境环境为Linux 5.7 x64+Oracle 10.2.0.5 x64+4块1Gb磁盘
[oracle@gtser1 gt10g]$ uname -a
Linux gtser1 2.6.32-200.13.1.el5uek #1 SMP WedJul 27 21:02:33 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux
[oracle@gtser1 gt10g]$ cat /etc/issue
Oracle Linux Server release 5.7
Kernel \r on an \m
SQL> select * from v$version;
BANNER
----------------------------
Oracle Database 10g Enterprise Edition Release10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
[oracle@gtser1 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.5.0 - Production on MonFeb 25 15:09:59 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and RealApplication Testing options
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/10g/arch
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9
SQL> set linesize 150
SQL> column ts# format 9
SQL> column name format a50
--使用表空间GTLIONS作为迁移对象，一共包含两个数据文件
SQL> Select ts#,Name From v$tablespace WhereName='GTLIONS'
2 Union All
3 Select file#,Name From v$datafile Wherets#=7;
TS# NAME
-----------------------------------------------------
7 GTLIONS
5/u01/oracle/10g/oradata/gt10g/gtlions01.dbf
6/u01/oracle/10g/oradata/gt10g/gtlions02.dbf
--使用RMAN进行迁移
[oracle@gtser1 ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Productionon Mon Feb 25 15:11:03 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: GT10G(DBID=2268277830)
--列出当前用户信息
RMAN> report schema;
using target database control file instead ofrecovery catalog
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- -------------------------------
1 440 SYSTEM *** /u01/oracle/10g/oradata/gt10g/system01.dbf
2 25 UNDOTBS1 *** /u01/oracle/10g/oradata/gt10g/undotbs1.dbf
3 250 SYSAUX *** /u01/oracle/10g/oradata/gt10g/sysaux01.dbf
4 5 USERS *** /u01/oracle/10g/oradata/gt10g/users01.dbf
5 0 GTLIONS *** /u01/oracle/10g/oradata/gt10g/gtlions01.dbf
6 0 GTLIONS *** /u01/oracle/10g/oradata/gt10g/gtlions02.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 100 TEMP 100 /u01/oracle/10g/oradata/gt10g/temp01.dbf
4 100 GTLIONSTEMP 100 /u01/oracle/10g/oradata/gt10g/gtlionstemp01.dbf
--使表空间脱机
RMAN> sql 'alter tablespace gtlions offline';
sql statement: alter tablespace gtlions offline
--开始备份两个数据文件
RMAN> backup as copy datafile 5 format'+data01';
Starting backup at 25-FEB-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005name=/u01/oracle/10g/oradata/gt10g/gtlions01.dbf
outputfilename=+DATA01/gt10g/datafile/gtlions.258.808326729 tag=TAG20130225T151205recid=117 stamp=808326729
channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:07
Finished backup at 25-FEB-13
RMAN> backup as copy datafile 6 format'+data01';
Starting backup at 25-FEB-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006name=/u01/oracle/10g/oradata/gt10g/gtlions02.dbf
outputfilename=+DATA01/gt10g/datafile/gtlions.265.808326737 tag=TAG20130225T151217recid=118 stamp=808326738
channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:03
Finished backup at 25-FEB-13
--切换到cope副本
RMAN> switch datafile 5 to copy;
datafile 5 switched to datafile copy"+DATA01/gt10g/datafile/gtlions.258.808326729"
RMAN> switch datafile 6 to copy;
datafile 6 switched to datafile copy"+DATA01/gt10g/datafile/gtlions.265.808326737"
--重新联机GTLIONS表空间
RMAN> sql 'alter tablespace gtlions online';
sql statement: alter tablespace gtlions online
--在RMAN检查迁移结果
RMAN> report schema;
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- -------------------------------
1 440 SYSTEM *** /u01/oracle/10g/oradata/gt10g/system01.dbf
2 25 UNDOTBS1 *** /u01/oracle/10g/oradata/gt10g/undotbs1.dbf
3 250 SYSAUX *** /u01/oracle/10g/oradata/gt10g/sysaux01.dbf
4 5 USERS *** /u01/oracle/10g/oradata/gt10g/users01.dbf
5 10 GTLIONS *** +DATA01/gt10g/datafile/gtlions.258.808326729
6 10 GTLIONS *** +DATA01/gt10g/datafile/gtlions.265.808326737
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- -------------------------------
1 100 TEMP 100 /u01/oracle/10g/oradata/gt10g/temp01.dbf
4 100 GTLIONSTEMP 100 /u01/oracle/10g/oradata/gt10g/gtlionstemp01.dbf
--在SQL*PLUS检查迁移结果
SQL> Select ts#,Name From v$tablespace WhereName='GTLIONS'
2 Union All
3 Select file#,Name From v$datafile Wherets#=7;
TS# NAME
-----------------------------------------------------
7 GTLIONS
5+DATA01/gt10g/datafile/gtlions.258.808326729
6+DATA01/gt10g/datafile/gtlions.265.808326737
-The End-