当前位置:Gxlcms > mysql > 把AIX中的Oracle10gR2数据迁移到HP集群Oracle11gR2中

把AIX中的Oracle10gR2数据迁移到HP集群Oracle11gR2中

时间:2021-07-01 10:21:17 帮助过:15人阅读

描述: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台服务器中找不到文件夹,所以只能配置一个节点。

linux

人气教程排行