把AIX中的Oracle 10g R2数据迁移到HP 集群Oracle 11g R2中
描述：AIX为64位的ORACLE10g R2数据库，HP UNIX为64位的ORACLE11g R2数据库，现在把AIX数据库某个用户的数据迁移到HP数据库中
描述：AIX为64位的Oracle10g R2数据库，HP UNIX为64位的ORACLE11g R2数据库，现在把AIX数据库某个用户的数据迁移到HP数据库中
1、在AIX系统操作
在数据库查看有没有expdp_dir目录
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------
SYS EXPDP_DIR
/oracle/oraarch/expdp_dir
如果没有，则可以重新创建：
SQL> create directory expdp_dir as '/oracle/oraarch/expdp_dir';
授权：
SQL> grant read,write on directory expdp_dir to bhomswas;
Grant succeeded.
在操作系统查看是否有/oracle/oraarch/expdp_dir，，没有则需要建立目录
$ mkdir expdp_dir
导出：
expdp bhomswas/password DIRECTORY=expdp_dir DUMPFILE=bhomswas.dmp logfile=bhomswas.log
2、在HP操作
在/home/oraoms目录下建立impdp_dir目录
CBDBS01-> mkdir impdp_dir
把在aix导出来的数据文件bhomswas.dmp用ftp上传到hp系统/home/oraoms/impdp_dir目录中
ftp HPIP
ftp>bin
ftp>put bhomswas.dmp
然后系统授权：
CBDBS01-> chmod 777 bhomswas.dmp
CBDBS01-> ls -l
total 1679072
-rwxrwxrwx 1 oraoms oinstall 859684864 Nov 23 11:17 bhomswas.dmp
4、在HP数据库中创建表空间及其用户
确认表空间的数据文件路径
select * from dba_data_files
创建1个表空间
--1
create tablespace MLOG_NORM_SPACE
datafile '+DATADG/cboms/datafile/MLOG_NORM_SPACE.dbf'
size 5M autoextend on
创建用户：
create user bhomswas
identified by password
default tablespace PUB_NORM_SPACE
grant dba to bhomswas;
在数据库创建目录并授权
create directory impdp_dir as '/home/oraoms/impdp_dir';
grant read,write on directory impdp_dir to bhomswas;
3、在AIX操作
修改tnsnames.ora，增加
cboms1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HPIP)(PORT = 1568))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cboms)
(INSTANCE_NAME = cboms1)
)
)
测试了解hp服务器
sqlplus user/password@cboms1
impdpbhomswas/password@cboms1DIRECTORY=impdp_dir dumpfile=bhomswas.dmp logfile=Imp.log
完毕！
附：
如果HP数据库是两台的RAC数据库，AIX的数据库不能下面这样配置。
配置/oracle/oms/102_64/network/admin/tnsnames.ora,添加hp数据库的信息
CBOMS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HPIP)(PORT = 1568))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.46.28)(PORT = 1568))
(LOAD_BALANCE = yes)
(FAILOVER = ON)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cboms)
(FAILOVER_MODE =
(TYPE = Select)
(METHOD = BASIC)
)
)
)
导入数据到hp数据库
impdpbhomswas/password@CBOMSDIRECTORY=impdp_dir dumpfile=bhomswas.dmp logfile=Imp.log
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
这样报错，是因为在HP的2台服务器中找不到文件夹，所以只能配置一个节点。