福和思

foxhello

0%

oracle导入dmp文件

发表于 更新于 分类于 ， ， 阅读次数：

oracle 导入 dmp 文件

文件上传服务器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
文件上传
sftp root@10.1.20.252

复制文件
docker cp LMIS20210118.7z oracle12c:/home/oracle/LMIS20210118.7z

7z文件解压
ubuntu：
apt-get install p7zip-full
7z x filename.7z

centos:
yum install p7zip -y
7za x  file.7z

imp 导入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
文件头几行内容
head -n 10 filename
tail -n 10 filename

docker exec -it oracle12c /bin/bash
sqlplus system/oracle@ORCL

create user rwms identified by rwms;
grant connect,resource,dba to rwms;

imp rwms/rwms@EE file=/home/oracle/xjrwms20210119.dmp ignore=y full=y

export TNS_ADMIN=/u01/app/oracle/product/12.2.0/EE/network/admin
EE =
  (DESCRIPTION =
    (ADDRESS_LIST =
	   (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1 )(PORT = 1521))
	)
	(CONNECT_DATA =
	   (SID = EE)
//     (SERVICE_NAME = EE) 修改为上面的内容
	)
)

lsnrctl status
重启监听服务
lsnrctl stop
lsnrctl start

impdp 导入

1
2
3
4
5
create user ntixjyc identified by ntixjyc;
grant connect,resource,dba to ntixjyc;

mv /home/oracle/filename.dmp /u01/app/oracle/admin/orcl/dpdump/
impdp ntixjyc/ntixjyc@ORCL dumpfile=NTIXJYC20210118.DMP

问题

  • IMP-00038: Could not convert to environment character set’s handle
    使用 impdp 进行导入

  • ORA-12547: TNS:lost contact+oracle 开启监听失败

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    sqlplus / as sysdba
    show parameter service_names
    show parameter local_l

    alter system set local_listener=local_l;

    alter system register;

    ```[相关参考资料1](https://blog.csdn.net/gua___gua/article/details/50983145)[相关参考资料2](https://logic.edchen.org/how-to-resolve-ora-12514-tns-listener-does-not-currently-know-of-service-requested-in-connect-descriptor/)_

  • ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
    _在$ORACLE_HOME/network/admin/添加 tnsnames.ora 文件_

  • ORA-12505: TNS:listener does not currently know of SID given in connect
    sqlplus rwms/rwms@EE