上一期79-Oracle 19c PDB下-建表空间-用户-赋权-验证-基本操作-CSDN博客
在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:041.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 NO2. 使用命令，修改密码永不过期
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
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
