原库为10.2.0.4单机，文件系统方式，非归档模式

第一步：数据迁移之前，先进行原环境检查

原环境创建了一个用户tt，并创建测试表tt

创建了一个用户tt作为测试用

SQL> create tablespace tt datafile '/home/oracle/oradata/fsdb/tt.dbf' size 30m;

Tablespace created.

SQL> create user  tt  identified by tt default tablespace tt;

User created.

SQL> grant dba to tt;

Grant succeeded.

SQL> connect tt/tt;
Connected.
SQL> create table tt as select * from dba_objects;

Table created.

SQL> insert into tt select * from tt;

50616 rows created.

SQL> /

101232 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from tt;

  COUNT(*)
----------
    202464

原数据库及库文件的存储位置如下

[oracle@fshost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 5 16:16:58 2015

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /home/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence     30
Current log sequence           32
SQL>

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/oradata/fsdb/users01.dbf
/home/oracle/oradata/fsdb/sysaux01.dbf
/home/oracle/oradata/fsdb/undotbs01.dbf
/home/oracle/oradata/fsdb/system01.dbf
/home/oracle/oradata/fsdb/example01.dbf

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@fshost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 5 16:16:58 2015

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /home/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence     30
Current log sequence           32
SQL>
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/home/oracle/oradata/fsdb/control01.ctl
/home/oracle/oradata/fsdb/control02.ctl
/home/oracle/oradata/fsdb/control03.ctl

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/home/oracle/oradata/fsdb/system01.dbf
/home/oracle/oradata/fsdb/undotbs01.dbf
/home/oracle/oradata/fsdb/sysaux01.dbf
/home/oracle/oradata/fsdb/users01.dbf
/home/oracle/oradata/fsdb/example01.dbf
/home/oracle/oradata/fsdb/tt.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/home/oracle/oradata/fsdb/redo03.log
/home/oracle/oradata/fsdb/redo02.log
/home/oracle/oradata/fsdb/redo01.log

SQL>
SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/home/oracle/oradata/fsdb/temp01.dbf

SQL>

原ORACLE_HOME信息如下

[oracle@fshost ~]$ more .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

export ORACLE_SID=fsdb;
export ORACLE_BASE=/home/oracle;
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1;
export PATH=$ORACLE_HOME/bin:${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin:$
{JAVA_HOME}/bin;

PATH=$PATH:$HOME/bin

export PATH

从以上信息可以看到，oracle数据库除了库文件在存储上以外，其他的ORACLE软件目录都存在主机本地磁盘上

第二步：将新存储接入主机，并划分分区，创建挂载点目录，并设置挂载点随主机重启而自动挂载

[root@fshost ~]# fdisk -l

Disk /dev/sda: 26.8 GB, 26843545600 bytes
255 heads, 63 sectors/track, 3263 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          13      104391   83  Linux
/dev/sda2              14        3263    26105625   8e  Linux LVM

Disk /dev/sdb: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System

[root@fshost ~]# fdisk /dev/sdb

The number of cylinders for this disk is set to 1305.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
   (e.g., DOS FDISK, OS/2 FDISK)

Command (m for help): m
Command action
   a   toggle a bootable flag
   b   edit bsd disklabel
   c   toggle the dos compatibility flag
   d   delete a partition
   l   list known partition types
   m   print this menu
   n   add a new partition
   o   create a new empty DOS partition table
   p   print the partition table
   q   quit without saving changes
   s   create a new empty Sun disklabel
   t   change a partition's system id
   u   change display/entry units
   v   verify the partition table
   w   write table to disk and exit
   x   extra functionality (experts only)

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1305, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-1305, default 1305):
Using default value 1305

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
You have new mail in /var/spool/mail/root

[root@fshost ~]# fdisk -l

Disk /dev/sda: 26.8 GB, 26843545600 bytes
255 heads, 63 sectors/track, 3263 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          13      104391   83  Linux
/dev/sda2              14        3263    26105625   8e  Linux LVM

Disk /dev/sdb: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1        1305    10482381   83  Linux

然后在对应分区上创建文件系统

[root@fshost ~]# mkfs -t ext3 -c /dev/sdb1
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
1310720 inodes, 2620595 blocks
131029 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=2684354560
80 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632

Checking for bad blocks (read-only test): done                               
Writing inode tables: done                           
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 29 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.

修改/etc/fstab使分区随主机启动而启动

[root@fshost ~]# more /etc/fstab
/dev/VolGroup00/LogVol00 /                       ext3    defaults        1 1
LABEL=/boot             /boot                   ext3    defaults        1 2
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
/dev/VolGroup00/LogVol01 swap                    swap    defaults        0 0
/dev/sdb1             /newdata                   ext3    defaults        1 2

最终查看文件系统如下

[root@fshost ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                       22G   11G   11G  50% /
/dev/sda1              99M   13M   82M  13% /boot
tmpfs                 768M     0  768M   0% /dev/shm
/dev/scd0             2.9G  2.9G     0 100% /media/RHEL_5.5 i386 DVD
/dev/sdb1             9.9G  151M  9.2G   2% /newdata

第三步：现在将 ORACLE软件目录保持不变，然后将所有的库文件都移到新存储挂点 /newdat上

[root@fshost fsdb]# su - oracle

[oracle@fshost ~]$ cd $ORACLE_HOME/dbs

[oracle@fshost dbs]$ pwd
/home/oracle/product/10.2.0/db_1/dbs

[oracle@fshost dbs]$ ls
hc_fsdb.dat  initfsdb.ora  lkFSDB     spfilefsdb.ora
initdw.ora   init.ora      orapwfsdb  spfilefsdb.ora.bak
[oracle@fshost dbs]$ ls -l
total 52
-rw-rw---- 1 oracle dba  1544 Mar  5 15:07 hc_fsdb.dat
-rw-r--r-- 1 oracle dba 12920 May  3  2001 initdw.ora
-rw-r----- 1 oracle dba   746 Feb  9 14:38 initfsdb.ora
-rw-r----- 1 oracle dba  8385 Sep 11  1998 init.ora
-rw-r----- 1 oracle dba    24 Feb  9 12:34 lkFSDB
-rw-r----- 1 oracle dba  1536 Feb  9 12:36 orapwfsdb
-rw-r----- 1 oracle dba  2560 Mar  5 15:07 spfilefsdb.ora
-rw-r----- 1 oracle dba  2560 Feb  9 15:02 spfilefsdb.ora.bak

查看参数文件内容

[oracle@fshost dbs]$ more initfsdb.ora

aq_tm_processes=0
audit_file_dest=/home/oracle/admin/fsdb/adump
background_dump_dest=/home/oracle/admin/fsdb/bdump
compatible=10.2.0.1.0
control_files=/home/oracle/oradata/fsdb/control01.ctl, /home/oracle/oradata/fsdb
/control02.ctl, /home/oracle/oradata/fsdb/control03.ctl
core_dump_dest=/home/oracle/admin/fsdb/cdump
db_block_size=8192
db_domain=""
db_file_multiblock_read_count=16
db_name=fsdb
dispatchers="(PROTOCOL=TCP) (SERVICE=fsdbXDB)"
job_queue_processes=10
nls_length_semantics=BYTE
open_cursors=300
pga_aggregate_target=149946368
processes=150
remote_login_passwordfile=EXCLUSIVE
resource_manager_plan=""
sga_target=452984832
undo_management=AUTO
undo_retention=900
undo_tablespace=UNDOTBS1
user_dump_dest=/home/oracle/admin/fsdb/udump
 

第四步：正式迁移

1：首先执行 lsnrctl stop 关闭监听

2：以shutdown immediate方式关闭数据库

[oracle@fshost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 5 17:05:32 2015

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

3：新建文件夹，并修改相关权限

[root@fshost ~]# cd /newdata/

[root@fshost newdata]# mkdir fsdb
[root@fshost newdata]# pwd
/newdata
[root@fshost newdata]# ls
fsdb  lost+found
[root@fshost newdata]# chown -R oracle:dba /newdata/fsdb/
[root@fshost newdata]#
[root@fshost newdata]# chmod -R 777 /newdata/fsdb/
[root@fshost newdata]#

再次检查确认相关目录的属主是否为oracle用户，dba组

4：然后将原库文件拷贝到新存储挂载点 /newdata/fsdb目录下

 
[oracle@fshost fsdb]$ nohup cp -R /home/oracle/oradata/fsdb/* /newdata/fsdb  &
[1] 5794
[oracle@fshost fsdb]$ nohup: appending output to `nohup.out'

拷贝完成之后，检查文件的个数是否一致，文件的大小是否一致

[oracle@fshost fsdb]$ ls -l |wc -l
15

注意：undo表空间对应的数据文件大小可能不一致，不影响

5：备份原pfile和spfile文件

[oracle@fshost fsdb]$ cd $ORACLE_HOME/dbs
 
[oracle@fshost dbs]$ ls -l
total 48
-rw-rw---- 1 oracle dba  1544 Mar  5 17:05 hc_fsdb.dat
-rw-r--r-- 1 oracle dba 12920 May  3  2001 initdw.ora
-rw-r----- 1 oracle dba   746 Feb  9 14:38 initfsdb.ora
-rw-r----- 1 oracle dba  8385 Sep 11  1998 init.ora
-rw-r----- 1 oracle dba    24 Feb  9 12:34 lkFSDB
-rw-r----- 1 oracle dba  1536 Feb  9 12:36 orapwfsdb
-rw-r----- 1 oracle dba  2560 Mar  5 15:07 spfilefsdb.ora

[oracle@fshost dbs]$ cp spfilefsdb.ora spfilefsdb.ora.bak
[oracle@fshost dbs]$
[oracle@fshost dbs]$ cp initfsdb.ora initfsdb.ora.bak
[oracle@fshost dbs]$

然后删除spfile文件 spfilefsdb.ora

6：修改pfile文件initfsdb.ora，将控制文件的路径改为最新的路径 ，其他的路径不变

修改后的  initfsdb.ora  文件内容如下

[oracle@fshost dbs]$ cat initfsdb.ora
aq_tm_processes=0
audit_file_dest=/home/oracle/admin/fsdb/adump
background_dump_dest=/home/oracle/admin/fsdb/bdump
compatible=10.2.0.1.0
control_files=/newdata/fsdb/control01.ctl, /newdata/fsdb/control02.ctl, /newdata/fsdb/control03.ctl
core_dump_dest=/home/oracle/admin/fsdb/cdump
db_block_size=8192
db_domain=""
db_file_multiblock_read_count=16
db_name=fsdb
dispatchers="(PROTOCOL=TCP) (SERVICE=fsdbXDB)"
job_queue_processes=10
nls_length_semantics=BYTE
open_cursors=300
pga_aggregate_target=149946368
processes=150
remote_login_passwordfile=EXCLUSIVE
resource_manager_plan=""
sga_target=452984832
undo_management=AUTO
undo_retention=900
undo_tablespace=UNDOTBS1
user_dump_dest=/home/oracle/admin/fsdb/udump

7：将数据库启动到mount状态

[oracle@fshost ~]$ cd $ORACLE_HOME/dbs
[oracle@fshost dbs]$ ls -l
total 52
-rw-rw---- 1 oracle dba  1544 Mar  5 17:05 hc_fsdb.dat
-rw-r--r-- 1 oracle dba 12920 May  3  2001 initdw.ora
-rw-r----- 1 oracle dba   711 Mar  5 22:37 initfsdb.ora
-rw-r----- 1 oracle dba   746 Mar  5 22:33 initfsdb.ora.bak
-rw-r----- 1 oracle dba  8385 Sep 11  1998 init.ora
-rw-r----- 1 oracle dba    24 Feb  9 12:34 lkFSDB
-rw-r----- 1 oracle dba  1536 Feb  9 12:36 orapwfsdb
-rw-r----- 1 oracle dba  2560 Mar  5 22:33 spfilefsdb.ora.bak

[oracle@fshost dbs]$ sqlplus  / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 5 22:40:29 2015

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

Connected to an idle instance.

SQL> startup mount pfile='/home/oracle/product/10.2.0/db_1/dbs/initfsdb.ora';
ORACLE instance started.

Total System Global Area  452984832 bytes
Fixed Size                  1268028 bytes
Variable Size             130025156 bytes
Database Buffers          318767104 bytes
Redo Buffers                2924544 bytes
Database mounted.
SQL>

查看到控制文件记录的数据文件、日志文件、临时文件还是原来的位置

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/home/oracle/oradata/fsdb/system01.dbf
/home/oracle/oradata/fsdb/undotbs01.dbf
/home/oracle/oradata/fsdb/sysaux01.dbf
/home/oracle/oradata/fsdb/users01.dbf
/home/oracle/oradata/fsdb/example01.dbf
/home/oracle/oradata/fsdb/tt.dbf

6 rows selected.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/home/oracle/oradata/fsdb/temp01.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/home/oracle/oradata/fsdb/redo03.log
/home/oracle/oradata/fsdb/redo02.log
/home/oracle/oradata/fsdb/redo01.log

8：然后将数据文件、日志文件、临时文件的位置改到最新位置

修改数据文件位置

SQL> alter database rename file '/home/oracle/oradata/fsdb/system01.dbf' to '/newdata/fsdb/system01.dbf';

Database altered.

SQL> alter database rename file  '/home/oracle/oradata/fsdb/undotbs01.dbf' to '/newdata/fsdb/undotbs01.dbf';

Database altered.

SQL> alter database rename file  '/home/oracle/oradata/fsdb/sysaux01.dbf' to '/newdata/fsdb/sysaux01.dbf';

Database altered.

SQL> alter database rename file  '/home/oracle/oradata/fsdb/users01.dbf' to '/newdata/fsdb/users01.dbf';

Database altered.

SQL> alter database rename file  '/home/oracle/oradata/fsdb/example01.dbf' to '/newdata/fsdb/example01.dbf';

Database altered.

SQL> alter database rename file '/home/oracle/oradata/fsdb/tt.dbf' to '/newdata/fsdb/tt.dbf';

Database altered.

修改日志文件位置

SQL> alter database rename file '/home/oracle/oradata/fsdb/redo03.log' to '/newdata/fsdb/redo03.log';

Database altered.

SQL> alter database rename file '/home/oracle/oradata/fsdb/redo02.log' to '/newdata/fsdb/redo02.log';

Database altered.

SQL> alter database rename file '/home/oracle/oradata/fsdb/redo01.log' to '/newdata/fsdb/redo01.log';

Database altered.

SQL>

修改临时文件位置

SQL> alter database rename file  '/home/oracle/oradata/fsdb/temp01.dbf' to '/newdata/fsdb/temp01.dbf';

Database altered.

9：将数据库启动到open状态

SQL> alter database open;

Database altered.

10：检查库文件是否都已经在新的存储上

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/newdata/fsdb/users01.dbf
/newdata/fsdb/sysaux01.dbf
/newdata/fsdb/undotbs01.dbf
/newdata/fsdb/system01.dbf
/newdata/fsdb/example01.dbf
/newdata/fsdb/tt.dbf

6 rows selected.

、
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/newdata/fsdb/control01.ctl
/newdata/fsdb/control02.ctl
/newdata/fsdb/control03.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/newdata/fsdb/redo03.log
/newdata/fsdb/redo02.log
/newdata/fsdb/redo01.log

SQL>
SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/newdata/fsdb/temp01.dbf

SQL>

11：验证tt表里的业务数据

SQL> connect tt/tt;
Connected.
SQL> select count(*) from tt;

  COUNT(*)
----------
    202464

SQL>

业务数据结果一致，数据迁移成功

12：老存储上解除LUN对主机的映射