Solaris oracle数据库离线迁移

一、测试环境描述
1.操作系统版本:solaris 10;
2.原测试数据库oracle 10g安装在系统分区/u01目录下，并且数据库也创建在子目录下，大概2.8G；
3.磁盘分区/dev/dsk/c1t4d0s0有60多G的空间。

root@sun10 # du -sh /u01
2.8G /u01
root@sun10 # ls /u01/oracle
arclog oraInventory product
root@sun10 # ls /u01/oracle/product/db_1/
OPatch flash_recovery_area lib32 ord sqlplus
admin has log oui srvm
assistants hs md owm sun10_ora10g
bin install mesg perl sysman
cdata install.platform mgw plsql tg4ifmx
cfgtoollogs instantclient32 network precomp tg4ingr
clone inventory nls racg tg4sybs
config javavm oc4j rdbms tg4tera
crs jdbc odbc relnotes uix
css jdk olap root.sh wwg
ctx jlib opmn root.sh.old xdk
dbs jre oraInst.loc slax
demo ldap oracore sqlj
diagnostics lib oradata sqlnet.log
二、测试目标：
把系统盘/u01目录下oracle数据拷贝到另一块磁盘的分区/dev/dsk/c1t4d0s0下，确保oracle数据及属性等完整拷贝到新的磁盘空间，并且数据库能正常运行。

三、实施过程：
1.检测环境：
数据库运行正常：
SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
ora10g OPEN

空间使用情况：
SQL> select df.tablespace_name "表空间名",totalspace "总空间M",freespace "剩余空间M",round((1-freespace/totalspace)*100,2) "使用率%"
2 from
3 (select tablespace_name,round(sum(bytes)/1024/1024) totalspace
4 from dba_data_files
5 group by tablespace_name) df,
6 (select tablespace_name,round(sum(bytes)/1024/1024) freespace
7 from dba_free_space
8 group by tablespace_name) fs
9 where df.tablespace_name=fs.tablespace_name;

表空间情况：

表空间名 总空间M 剩余空间M 使用率%
------------------------------ ---------- ---------- ----------
SYSTEM 480 9 98.13
USERS 5 5 0
ZMQ_DATA 50 50 0
SYSAUX 240 2 99.17
UNDOTBS1 25 1 96

zmq用户表：
SQL> select * from zmq.test_emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

2.关闭oracle监听和实例
-bash-3.00$ lsnrctl stop

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 14-JUN-2012 14:50:24

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sun10)(PORT=1521)))
The command completed successfully

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

3.检查原目录大小和文件数量
-bash-3.00$ pwd
/u01
-bash-3.00$ du -sk oracle/
2948626 oracle
-bash-3.00$ ls -alR |wc -l
28016
4.挂载新目录挂载磁盘c1t4d0s0
root@sun10 # mkdir u01new
root@sun10 # mount /dev/dsk/c1t4d0s0 /u01new
root@sun10 # df -h
Filesystem size used avail capacity Mounted on
/dev/dsk/c1t0d0s0 34G 9.7G 24G 30% /
/devices 0K 0K 0K 0% /devices
ctfs 0K 0K 0K 0% /system/contract
proc 0K 0K 0K 0% /proc
mnttab 0K 0K 0K 0% /etc/mnttab
swap 6.3G 1.1M 6.3G 1% /etc/svc/volatile
objfs 0K 0K 0K 0% /system/object
/platform/sun4u-us3/lib/libc_psr/libc_psr_hwcap1.so.1
34G 9.7G 24G 30% /platform/sun4u-us3/lib/libc_psr.so.1
/platform/sun4u-us3/lib/sparcv9/libc_psr/libc_psr_hwcap1.so.1
34G 9.7G 24G 30% /platform/sun4u-us3/lib/sparcv9/libc_psr.so.1
fd 0K 0K 0K 0% /dev/fd
/dev/dsk/c1t0d0s3 1.9G 551M 1.3G 30% /var
swap 6.3G 32K 6.3G 1% /tmp
swap 6.3G 32K 6.3G 1% /var/run
/dev/dsk/c1t0d0s5 19G 5.9G 13G 31% /export
/dev/dsk/c1t0d0s6 1.9G 2.0M 1.8G 1% /zones
/dev/dsk/c1t0d0s4 1.9G 2.0M 1.8G 1% /var/audit
/dev/dsk/c1t4d0s0 67G 64M 66G 1% /u01new #确认有足够的空间

5.利用cpio命令拷贝
root@sun10 # cd /u01
root@sun10 # pwd
/u01
root@sun10 # ls
oracle
root@sun10 # time find . |cpio -pdmu /u01new/
6156432 blocks

real 2m35.127s #花了2分多钟
user 0m2.100s
sys 0m45.207s

root@sun10 # du -sk oracle/
3020200 oracle #目录大小跟源拷贝目录有差异，大一点
root@sun10 # ls -alR |wc -l
28016 #说明拷贝的文件数量跟源目录一致

6.
重新挂载

root@sun10 # mv u01 u01bak
root@sun10 # mkdir u01
root@sun10 # umount /u01new
root@sun10 # mount /dev/dsk/c1t4d0s0 /u01
root@sun10 # ls /u01
oracle
root@sun10 # df -h
Filesystem size used avail capacity Mounted on
/dev/dsk/c1t0d0s0 34G 9.7G 24G 30% /
/devices 0K 0K 0K 0% /devices
ctfs 0K 0K 0K 0% /system/contract
proc 0K 0K 0K 0% /proc
mnttab 0K 0K 0K 0% /etc/mnttab
swap 6.3G 1.1M 6.3G 1% /etc/svc/volatile
objfs 0K 0K 0K 0% /system/object
/platform/sun4u-us3/lib/libc_psr/libc_psr_hwcap1.so.1
34G 9.7G 24G 30% /platform/sun4u-us3/lib/libc_psr.so.1
/platform/sun4u-us3/lib/sparcv9/libc_psr/libc_psr_hwcap1.so.1
34G 9.7G 24G 30% /platform/sun4u-us3/lib/sparcv9/libc_psr.so.1
fd 0K 0K 0K 0% /dev/fd
/dev/dsk/c1t0d0s3 1.9G 551M 1.3G 30% /var
swap 6.3G 32K 6.3G 1% /tmp
swap 6.3G 32K 6.3G 1% /var/run
/dev/dsk/c1t0d0s5 19G 5.9G 13G 31% /export
/dev/dsk/c1t0d0s6 1.9G 2.0M 1.8G 1% /zones
/dev/dsk/c1t0d0s4 1.9G 2.0M 1.8G 1% /var/audit
/dev/dsk/c1t4d0s0 67G 2.9G 63G 5% /u01
root@sun10 #
root@sun10 # du -sk /u01
3020201 /u01

注：
如果原/u01是mount了磁盘，这时只需要
umount /u01 #卸载原来磁盘分区
umount /u01new #卸载/dev/dsk/c1t4d0s0
mount /dev/dsk/c1t4d0s0 /u01 #将/dev/dsk/c1t4d0s0挂载到/u01

6.启动数据库，验证数据
root@sun10 # su - oracle
启用监听
-bash-3.00$ lsnrctl start

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 14-JUN-2012 17:08:47

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

Starting /u01/oracle/product/db_1/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
System parameter file is /u01/oracle/product/db_1/network/admin/listener.ora
Log messages written to /u01/oracle/product/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sun10)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sun10)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
Start Date 14-JUN-2012 17:08:47
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/product/db_1/network/admin/listener.ora
Listener Log File /u01/oracle/product/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sun10)(PORT=1521)))
Services Summary...
Service "ora10g" has 1 instance(s).
Instance "ora10g", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

启动数据库

-bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 14 15:42:21 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2550136832 bytes
Fixed Size 2079112 bytes
Variable Size 547243640 bytes
Database Buffers 1996488704 bytes
Redo Buffers 4325376 bytes
Database mounted.
Database opened.
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
ZMQ_TEMP
ZMQ_DATA

7 rows selected.

SQL> select * from zmq.test_emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.
数据库启动正常，数据能正常访问。

7.将在/etc/vfstab中设置/dev/dsk/c1t4d0s0自动挂载/u01
cp /etc/vfstab /etc/vfstab.bak20120614 #先备份文件
vi /etc/vfstab
#device device mount FS fsck mount mount
#to mount to fsck point type pass at boot options
#
fd - /dev/fd fd - no -
/proc - /proc proc - no -
/dev/dsk/c1t0d0s0 /dev/rdsk/c1t0d0s0 / ufs 1 no -
/dev/dsk/c1t0d0s3 /dev/rdsk/c1t0d0s3 /var ufs 1 no -
/dev/dsk/c1t0d0s5 /dev/rdsk/c1t0d0s5 /export ufs 2 yes -
/dev/dsk/c1t0d0s4 /dev/rdsk/c1t0d0s4 /var/audit ufs 2 yes -
/dev/dsk/c1t0d0s6 /dev/rdsk/c1t0d0s6 /zones ufs 2 yes -
/devices - /devices devfs - no -
ctfs - /system/contract ctfs - no -
objfs - /system/object objfs - no -
swap - /tmp tmpfs - yes -
/dev/dsk/c1t4d0s0 /dev/rdsk/c1t4d0s0 /u01 ufs 2 yes -

关闭数据库重启系统再次查看
root@sun10 # df -h
Filesystem size used avail capacity Mounted on
/dev/dsk/c1t0d0s0 34G 9.7G 24G 30% /
/devices 0K 0K 0K 0% /devices
ctfs 0K 0K 0K 0% /system/contract
proc 0K 0K 0K 0% /proc
mnttab 0K 0K 0K 0% /etc/mnttab
swap 6.6G 1.1M 6.6G 1% /etc/svc/volatile
objfs 0K 0K 0K 0% /system/object
/platform/sun4u-us3/lib/libc_psr/libc_psr_hwcap1.so.1
34G 9.7G 24G 30% /platform/sun4u-us3/lib/libc_psr.so.1
/platform/sun4u-us3/lib/sparcv9/libc_psr/libc_psr_hwcap1.so.1
34G 9.7G 24G 30% /platform/sun4u-us3/lib/sparcv9/libc_psr.so.1
fd 0K 0K 0K 0% /dev/fd
/dev/dsk/c1t0d0s3 1.9G 551M 1.3G 30% /var
swap 6.6G 0K 6.6G 0% /tmp
swap 6.6G 32K 6.6G 1% /var/run
/dev/dsk/c1t4d0s0 67G 2.9G 63G 5% /u01
/dev/dsk/c1t0d0s5 19G 5.9G 13G 31% /export
/dev/dsk/c1t0d0s6 1.9G 2.0M 1.8G 1% /zones
/dev/dsk/c1t0d0s4 1.9G 2.0M 1.8G 1% /var/audit

root@sun10 # su - oracle
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
-bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 14 17:00:38 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2550136832 bytes
Fixed Size 2079112 bytes
Variable Size 547243640 bytes
Database Buffers 1996488704 bytes
Redo Buffers 4325376 bytes
Database mounted.
Database opened.
SQL>

四、测试结果。
迁移测试成功。