80-Oracle 19c PDB dmp数据泵导入-实操

原创已于 2025-06-25 14:12:41 修改 · 1.1k 阅读

13 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库 #database #sql #大数据

于 2025-06-25 14:05:19 首次发布

上一期79-Oracle 19c PDB下-建表空间-用户-赋权-验证-基本操作-优快云博客

在19C pdb上新建了用户和赋权，现在需要将dmp文件导入，进行生产环境部署。

1.1 导入数据

数据库导出的DMP文件如下:

1.1.1 导入newods.sql文件(登录newods用户)--按照生产要求提前导入sql，

如果仅导入dmp，无需此步骤。

1. 使用PL/SQL软件，文件->新建->命令窗口，打开命令窗口。

2. 将提供的《newods.sql》文件的内容，全部复制到命令窗口的编辑器重，然后点击执行按钮。

1.1.2导入etl_mgr.dmp文件

现场为19c数据库,如果是11g数据库导入后缀是11g的dmp文件即可;

采用数据泵的形式导入dmp文件,首先在数据库服务其上创建directory目录,以数据库服务器为windows服务器为例,如果是linux数据库服务器, 注意directory的物理路径写法.

使用SQL: select * from all_directories; 查看现有directory的实际物理路径作为参考;

select * from all_directories;

   OWNER              DIRECTORY_NAME                                                          DIRECTORY_PATH    ORIGIN_CON_ID
________ ___________________________ _______________________________________________________________________ ________________
SYS      DBMS_OPTIM_LOGDIR           /u01/app/oracle/product/19.3.0/db/cfgtoollogs                                          1
SYS      DBMS_OPTIM_ADMINDIR         /u01/app/oracle/product/19.3.0/db/rdbms/admin                                          1
SYS      SDO_DIR_WORK                                                                                                       1
SYS      SDO_DIR_ADMIN               /u01/app/oracle/product/19.3.0/db/md/admin                                             1
SYS      XMLDIR                      /u01/app/oracle/product/19.3.0/db/rdbms/xml                                            1
SYS      XSDDIR                      /u01/app/oracle/product/19.3.0/db/rdbms/xml/schema                                     1
SYS      OPATCH_INST_DIR             /u01/app/oracle/product/19.3.0/db/OPatch                                               1
SYS      ORACLE_OCM_CONFIG_DIR2      /u01/app/oracle/product/19.3.0/db/ccr/state                                            1
SYS      ORACLE_BASE                 /u01/app/oracle                                                                        1
SYS      ORACLE_HOME                 /u01/app/oracle/product/19.3.0/db                                                      1
SYS      ORACLE_OCM_CONFIG_DIR       /u01/app/oracle/product/19.3.0/db/ccr/state                                            1
SYS      DATA_PUMP_DIR               /u01/app/oracle/admin/HBICDB/dpdump/383C85C956F239E2E063BD00060A05F5                   1
SYS      OPATCH_SCRIPT_DIR           /u01/app/oracle/product/19.3.0/db/QOpatch                                              1
SYS      OPATCH_LOG_DIR              /u01/app/oracle/product/19.3.0/db/rdbms/log                                            1
SYS      JAVA$JOX$CUJS$DIRECTORY$    /u01/app/oracle/product/19.3.0/db/javavm/admin/                                        1

15 rows selected.

1. 在数据库服务器创建数据库需要的PUMP_DIR;

linux系统注意创建好文件或目录之后,使用chmod 把文件夹和文件的权限允许写入,否则导入dmp时会报与权限有关的错误;

chmod +x 383C85C956F239E2E063BD00060A05F5
[oracle@HBIDB-189:/u01/app/oracle/admin/HBICDB/dpdump]$ ls -lrht
total 4.0K
-rw-r-----. 1 oracle oinstall 159 Jun 23 19:18 dp.log
drwxr-x---. 2 oracle oinstall   6 Jun 23 19:18 383C528C153827DFE063BD00060AE0B5
drwxr-x---. 2 oracle oinstall   6 Jun 23 19:29 383C85C956F239E2E063BD00060A05F5
[oracle@HBIDB-189:/u01/app/oracle/admin/HBICDB/dpdump]$

2. 将dmp文件传入该文件夹中;

[root@HBIDB-189:/u01/app/oracle/admin/HBICDB/dpdump]# ll
total 173264
drwxr-x---. 2 oracle oinstall         6 Jun 23 19:18 383C528C153827DFE063BD00060AE0B5
drwxr-x---. 2 oracle oinstall         6 Jun 25 13:18 383C85C956F239E2E063BD00060A05F5
-rw-r-----. 1 oracle oinstall       159 Jun 23 19:18 dp.log
-rwxr-xr-x  1 root   root     149671936 Jun 25 13:15 etl_mgr_19c.DMP
-rwxr-xr-x  1 root   root      27746304 Jun 25 13:15 newbi_19c.DMP
[root@HBIDB-189:/u01/app/oracle/admin/HBICDB/dpdump]#

3. sqlplus登录system用户,执行创建directory语句, 指向物理路径/u01/app/oracle/admin/HBICDB/dpdump,并授权给etl_mgr用户, newbi用户读写权限;

--创建名为bi_dump的directory,并指向物理路径/u01/app/oracle/admin/HBICDB/dpdump
create directory bi_dump as '/u01/app/oracle/admin/HBICDB/dpdump';
--授权读写权限给etl_mgr, newbi用户
grant read,write on directory bi_dump to etl_mgr;
grant read,write on directory bi_dump to newbi;
--
[root@HBIDB-189:/u01/app/oracle/admin/HBICDB/dpdump]# su - oracle
[oracle@HBIDB-189:/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 25 13:21:37 2025
Version 19.24.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0

SYS@HBICDB SQL> alter session set container=PDBHBI;

Session altered.

SYS@HBICDB SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDBHBI                         READ WRITE NO
SYS@HBICDB SQL> create directory bi_dump as '/u01/app/oracle/admin/HBICDB/dpdump';

Directory created.

SYS@HBICDB SQL> grant read,write on directory bi_dump to etl_mgr;
grant read,write on directory bi_dump to newbi;
Grant succeeded.

SYS@HBICDB SQL>

Grant succeeded.

4. 在系统的命令行操作窗口,执行一下命令:

impdp BI数据库用户名/密码@数据库服务器IP:端口号/数据库实例名 directory=bi_dump dumpfile=ETL_MGR_19C.DMP schemas=ETL_MGR

例如:bi数据库的用户名为system,密码为root,ip为10.10.10.10,端口为1521, 数据库实例为orcl,则导入语句为:

impdp system/oracle@10.6.0.189:1521/PDBHBI directory=bi_dump dumpfile=etl_mgr_19c.DMP schemas=ETL_MGR

[oracle@HBIDB-189:/home/oracle]$ impdp system/oracle@10.6.0.189:1521/PDBHBI directory=bi_dump dumpfile=etl_mgr_19c.DMP schemas=ETL_MGR

Import: Release 19.0.0.0.0 - Production on Wed Jun 25 13:24:55 2025
Version 19.24.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/********@10.6.0.189:1521/PDBHBI directory=bi_dump dumpfile=etl_mgr_19c.DMP schemas=ETL_MGR
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ETL_MGR" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "ETL_MGR"."UDE_TASK_COMPLEMENT_NOTES"       3.996 MB   31535 rows
. . imported "ETL_MGR"."USER_TB_PURVIEW"                 5.539 KB       3 rows
……………………
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
ORA-39082: Object type PACKAGE BODY:"ETL_MGR"."KETTLE_MGR" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"ETL_MGR"."TASK_MGR" created with compilation warnings

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 3 error(s) at Wed Jun 25 13:26:16 2025 elapsed 0 00:01:21
[oracle@HBIDB-189:/home/oracle]$

注意:数据库版本要对应,否则导入不成功;以下是现场生产需要，如果仅为导入DMP不需此步骤。

导入成功后，在etl_mgr用户下执行赋权限语句，使newbi可以访问etl_mgr下的函数：

grant execute on record_task_state to newbi;

grant execute on record_error_log to newbi;

1.1.3 导入newbi.dmp文件

上面的的步骤,导入newbi对应的dmp文件

impdp BI数据库用户名/密码@数据库服务器IP:端口号/数据库实例名 directory=bi_dump dumpfile=NEWBI_19C.DMP schemas=NEWBI

impdp system/oracle@10.6.0.189:1521/PDBHBI directory=bi_dump dumpfile=newbi_19c.DMP schemas=NEWBI

如果出现需要第二次导入的情况需要覆盖

impdp system/oracle@10.6.0.189:1521/PDBHBI \
  directory=bi_dump \
  dumpfile=newbi_19c.DMP \
  schemas=NEWBI \
  table_exists_action=replace \
  include=grant
  --
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-31655: no data or metadata objects selected for job
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/********@10.6.0.189:1521/PDBHBI directory=bi_dump dumpfile=newbi_19c.DMP schemas=NEWBI table_exists_action=replace include=grant
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Wed Jun 25 13:42:21 2025 elapsed 0 00:00:04

1.2 设置密码永不过期(按照实际业务需求)

重要说明：对于现场的数据库使用情况，既要符合安全检测又要适合业务将数据库的密码设定为永不过期

1. Oracle数据库安装完成后，默认密码的有限期为180天，可使用如下命令查看。

select t.* from dba_profiles t where t.profile = 'DEFAULT' and t.resource_name = 'PASSWORD_LIFE_TIME';

   PROFILE         RESOURCE_NAME    RESOURCE_TYPE        LIMIT    COMMON    INHERITED    IMPLICIT
__________ _____________________ ________________ ____________ _________ ____________ ___________
DEFAULT    PASSWORD_LIFE_TIME    PASSWORD         UNLIMITED    NO        NO           NO

2. 使用命令，修改密码永不过期

  alter profile default limit password_life_time unlimited;

3. 查看用户、状态、密码过期时间等

select t.username, t.account_status, t.expiry_date, t.profile from dba_users t;

select t.username, t.account_status, t.expiry_date, t.profile from dba_users t;

                 USERNAME      ACCOUNT_STATUS    EXPIRY_DATE    PROFILE
_________________________ ___________________ ______________ __________
SYS                       OPEN                               DEFAULT
SYSTEM                    OPEN                               DEFAULT
XS$NULL                   EXPIRED & LOCKED                   DEFAULT
………………
SYSDG                     LOCKED                             DEFAULT
ORDSYS                    LOCKED                             DEFAULT
NEWBI                     OPEN                               DEFAULT
ETL_MGR                   OPEN                               DEFAULT
NEWODS                    OPEN                               DEFAULT

39 rows selected.

4. 如果现场不允许进行密码永不过期操作，经过180天后遇到密码过期的情况，可使用命令将HBI用到的用户密码恢复。

alter user etl_mgr identified by etl_mgr;
alter user newbi identified by softnewbi;
alter user newods identified by softnewods;
alter user dxp identified by dxp;
......

1.3 常见问题举例解决办法

常见问题1：对表空间无权限

IMP-00058: 遇到 ORACLE 错误 1950
ORA-01950: 对表空间 'BIDATA' 无权限
  解决办法：
  执行下GRANT unlimited tablespace TO etl_mgr