oracle数据库转移（备份）的一次实践

 原数据库服务器：192.168.2.156，redhat 6.2, oracle 11G，转移到目标数据库192.168.2.155,也是redhat 6.2 ，oracle 11G

1.先停了两服务器上的数据库，在sqlplus 中执行shutdown immediate,再备份155原数据库：mv  /u01/oradata/zydb   /home/oracle

2.把156数据库的数据文件复制到155:

scp oracle@192.168.2.156:/home/oracle/zydb/*  /u01/oradata/zydb

3备份然后复制

在sqlplus中执行SQL> show parameter spfile

得到spfile文件路径：/u01/11.2.0/db_1/dbs/spfilezydb.ora

[oracle@rac1 dbs]$ cd /u01/11.2.0/db_1/dbs

[oracle@rac1 dbs]$ mv spfilezydb.ora spfilezydb_bak.ora

[oracle@rac1 dbs]scp oracle@192.168.2.156:/u01/11.2.0/db_1/dbs/spfilezydb.ora ./

4.启动oracle 

[oracle@rac1 zydb]$ sqlplus / as sysdba

SQL> startup

报下面的错：

ORA-00211: control file does not match previous control files
ORA-00202: control file: '/opt/oracle/flash_recovery_area/orcl/control02.ctl'

5.解决办法

[oracle@rac1 zydb]$ cd  /u01/flash_recovery_area/zydb
[oracle@rac1 zydb]$ mv control02.ctl control02_bak.ctl

[oracle@rac1 zydb]$ scp oracle@192.168.2.156:/u01/flash_recovery_area/zydb/control02.ctl ./

再启动：

[oracle@rac1 zydb]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 24 00:29:48 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1.3429E+10 bytes
Fixed Size		    2217992 bytes
Variable Size		 7449085944 bytes
Database Buffers	 5905580032 bytes
Redo Buffers		   71770112 bytes
Database mounted.
Database opened.

启动成功，检查转移来的数据库是否和原来的数据库一致：

SQL> select table_name from user_tables;


TABLE_NAME
------------------------------
VIDEO_TRAFFICFLOW
EQUIPMENT
LANE_WAY
DIRECTION
TRAFFICAVGSPEED
TRAFFICFLOW
TRAFFICSHARE
LGT_DEVICE
RIM_TRAFFIC_FLOW


9 rows selected.

SQL> select count(*) from lane_way;


  COUNT(*)
----------
10

经检查，两数据库内容一致，至此数据库转移成功，也可用此方法备份数据库