项目背景：
某客户计划整合升级迁移多个11g的库到oracle19c多租户一体机环境。
各应用厂商独立操作，确保系统和数据安全并且要求互不干扰。
每个应用系统为11g的数据库对应到一个19c的PDB库。
每个应用系统对应一个应用厂商，自行负责迁移11g数据到19c对应的pdb中(expdp方式，非exp)。
一体机数据库维护厂商负责建sftp用户、规划安装pdb库以及相应的dba权限用户。
可以理解为如下格式：
实现目标：
为了安全考虑，避免用户权限乱用。
各厂商只能sftp上传自己的dmp文件到指定目录，不允许ssh登录数据库服务器，只允许使用sqldeveloper等客户端工具远程连接到pdb导入自己的dmp数据。
处理步骤：
1.数据库维护厂商规划创建各应用厂商pdb库对应的dba用户，存放dmp的目录，以及创建相应的sftp用户和目录权限。培训指导应用厂商使用工具导入操作。
2.应用厂商通过sftp上传自己的dmp到对应data目录
3.应用厂商通过sqldeveloper工具的dba导入数据功能，导入dmp数据
详细的操作记录参考：
数据库环境准备：例如pdb为jyc库，用户密码为jyc/jyc
[oracle@oem ~]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 20-APR-2023 14:21:21
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19.3.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.3.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oem/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oem)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oem.local)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 20-APR-2023 14:21:23
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.3.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oem/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oem)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@oem ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 20 14:21:28 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
sConnected to an idle instance.
SQL> tartup
ORACLE instance started.
Total System Global Area 5368705768 bytes
Fixed Size 9147112 bytes
Variable Size 1627389952 bytes
Database Buffers 3707764736 bytes
Redo Buffers 24403968 bytes
Database mounted.
Database opened.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 OEM READ WRITE NO
4 JYC READ WRITE NO
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
ip[oracle@oem ~]$ ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:7d:01:0a brd ff:ff:ff:ff:ff:ff
inet 192.168.52.129/24 brd 192.168.52.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet6 fe80::5fef:1aa6:fe6f:cc53/64 scope link noprefixroute
valid_lft forever preferred_lft forever
3: ens35: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast master bond0 state UP group default qlen 1000
link/ether 00:0c:29:7d:01:14 brd ff:ff:ff:ff:ff:ff
4: ens36: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast master bond0 state UP group default qlen 1000
link/ether 00:0c:29:7d:01:14 brd ff:ff:ff:ff:ff:ff
5: bond0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP group default qlen 1000
link/ether 00:0c:29:7d:01:14 brd ff:ff:ff:ff:ff:ff
inet 192.168.52.139/24 brd 192.168.52.255 scope global noprefixroute bond0
valid_lft forever preferred_lft forever
inet6 fe80::fe7a:160f:c3fb:5920/64 scope link noprefixroute
valid_lft forever preferred_lft forever
[oracle@oem ~]$ sqlplus jyc/jyc@192.168.52.129:1521/jyc
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 20 14:22:13 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Tue Apr 04 2023 10:21:38 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@oem ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
ORACLE_SID=orcl;export ORACLE_SID
ORACLE_UNQNAME=orcl;export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/19.3.0/db_1; export ORACLE_HOME
NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"; export NLS_DATE_FORMAT
NLS_LANG=american_america.ZHS16GBK; export NLS_LANG
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
ORA_NLS11=$ORACLE_HOME/nls/data; export ORA_NLS11
PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin:$ORA_CRS_HOME/bin
PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export CLASSPATH
THREADS_FLAG=native; export THREADS_FLAG
export TEMP=/tmp
export TMPDIR=/tmp
umask 022
[oracle@oem ~]$ exit
logout
[root@oem ~]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 6.6G 0 6.6G 0% /dev
tmpfs 6.7G 0 6.7G 0% /dev/shm
tmpfs 6.7G 12M 6.6G 1% /run
tmpfs 6.7G 0 6.7G 0% /sys/fs/cgroup
/dev/mapper/centos-root 132G 84G 49G 64% /
/dev/mapper/datavg-datalv 988M 2.6M 919M 1% /app
/dev/sda1 1014M 198M 817M 20% /boot
tmpfs 1.4G 0 1.4G 0% /run/user/0
tmpfs 1.4G 0 1.4G 0% /run/user/1001
创建sftp用户：
[root@oem ~]# groupadd sftponly
[root@oem ~]# useradd -d /sftp/user1 -G sftponly -s /sbin/nologin user1
useradd: cannot create directory /sftp/user1
[root@oem ~]# passwd user1
Changing password for user user1.
New password:
BAD PASSWORD: The password is shorter than 7 characters
Retype new password:
passwd: all authentication tokens updated successfully.
[root@oem ~]# id user1
uid=1002(user1) gid=1002(user1) groups=1002(user1),54332(sftponly)
[root@oem ~]# mkdir -p /sftp/user1/data
[root@oem ~]# chown root /sftp/user1
[root@oem ~]# chmod g+rx /sftp/user1
[root@oem ~]# chown user1:user1 /sftp/user1/data
[root@oem ~]# ll /sftp
total 0
drwxr-xr-x 3 root root 18 Apr 20 14:38 user1
[root@oem ~]# ll /sftp/user1/
total 0
drwxr-xr-x 2 user1 user1 6 Apr 20 14:38 data
[root@oem ~]# ls -ld /sftp
drwxr-xr-x 3 root root 19 Apr 20 14:38 /sftp
[root@oem ~]# vi /etc/ssh/sshd_config
# $OpenBSD: sshd_config,v 1.100 2016/08/15 12:32:04 naddy Exp $
# This is the sshd server system-wide configuration file. See
# sshd_config(5) for more information.
# This sshd was compiled with PATH=/usr/local/bin:/usr/bin
# The strategy used for options in the default sshd_config shipped with
# OpenSSH is to specify options with their default value where
# possible, but leave them commented. Uncommented options override the
# default value.
# If you want to change the port on a SELinux system, you have to tell
# SELinux about this change.
# semanage port -a -t ssh_port_t -p tcp #PORTNUMBER
#
#Port 22
#AddressFamily any
#ListenAddress 0.0.0.0
#ListenAddress ::
HostKey /etc/ssh/ssh_host_rsa_key
#HostKey /etc/ssh/ssh_host_dsa_key
HostKey /etc/ssh/ssh_host_ecdsa_key
HostKey /etc/ssh/ssh_host_ed25519_key
# Ciphers and keying
#RekeyLimit default none
# Logging
#SyslogFacility AUTH
SyslogFacility AUTHPRIV
#LogLevel INFO
# Authentication:
#LoginGraceTime 2m
#PermitRootLogin yes
#StrictModes yes
#MaxAuthTries 6
#MaxSessions 10
#PubkeyAuthentication yes
# The default is to check both .ssh/authorized_keys and .ssh/authorized_keys2
# but this is overridden so installations will only check .ssh/authorized_keys
AuthorizedKeysFile .ssh/authorized_keys
#AuthorizedPrincipalsFile none
#AuthorizedKeysCommand none
#AuthorizedKeysCommandUser nobody
# For this to work you will also need host keys in /etc/ssh/ssh_known_hosts
#HostbasedAuthentication no
/sftp
# Set this to 'yes' to enable PAM authentication, account processing,
# and session processing. If this is enabled, PAM authentication will
# be allowed through the ChallengeResponseAuthentication and
# PasswordAuthentication. Depending on your PAM configuration,
# PAM authentication via ChallengeResponseAuthentication may bypass
# the setting of "PermitRootLogin without-password".
# If you just want the PAM account and session checks to run without
# PAM authentication, then enable this but set PasswordAuthentication
# and ChallengeResponseAuthentication to 'no'.
# WARNING: 'UsePAM no' is not supported in Red Hat Enterprise Linux and may cause several
# problems.
UsePAM yes
#AllowAgentForwarding yes
#AllowTcpForwarding yes
#GatewayPorts no
X11Forwarding yes
#X11DisplayOffset 10
#X11UseLocalhost yes
#PermitTTY yes
#PrintMotd yes
#PrintLastLog yes
#TCPKeepAlive yes
#UseLogin no
#UsePrivilegeSeparation sandbox
#PermitUserEnvironment no
#Compression delayed
#ClientAliveInterval 0
#ClientAliveCountMax 3
#ShowPatchLevel no
#UseDNS yes
#PidFile /var/run/sshd.pid
#MaxStartups 10:30:100
#PermitTunnel no
#ChrootDirectory none
#VersionAddendum none
# no default banner path
#Banner none
# Accept locale-related environment variables
AcceptEnv LANG LC_CTYPE LC_NUMERIC LC_TIME LC_COLLATE LC_MONETARY LC_MESSAGES
AcceptEnv LC_PAPER LC_NAME LC_ADDRESS LC_TELEPHONE LC_MEASUREMENT
AcceptEnv LC_IDENTIFICATION LC_ALL LANGUAGE
AcceptEnv XMODIFIERS
# override default of no subsystems
####Subsystem sftp /usr/libexec/openssh/sftp-server注释此行，增加如下内容：
###-----start-------####
Subsystem sftp internal-sftp
Match Group sftponly
ChrootDirectory /sftp/%u
X11Forwarding no
AllowTcpForwarding no
ForceCommand internal-sftp
###------end----------####
# Example of overriding settings on a per-user basis
#Match User anoncvs
# X11Forwarding no
# AllowTcpForwarding no
# PermitTTY no
# ForceCommand cvs server
"/etc/ssh/sshd_config" 146L, 4053C written
[root@oem ~]# systemctl restart sshd
[root@oem ~]#
测试ssh和sftp是否满足要求：不能ssh登录，但可以sftp上传下载，并且其它目录无权限。
[root@oem ~]# ssh user1@192.168.52.129
The authenticity of host '192.168.52.129 (192.168.52.129)' can't be established.
ECDSA key fingerprint is SHA256:+q409P6FaVrYlRDxCBlf4ePv0sczqogNnZ5V86boC3o.
ECDSA key fingerprint is MD5:c3:89:7e:6b:a7:f3:24:05:c8:27:0c:09:19:83:1c:40.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.52.129' (ECDSA) to the list of known hosts.
user1@192.168.52.129's password:
This service allows sftp connections only.
Connection to 192.168.52.129 closed.
[root@oem ~]# sftp user1@192.168.52.129
user1@192.168.52.129's password:
Connected to 192.168.52.129.
sftp> ls
data
sftp> mkdir test
Couldn't create directory: Permission denied
sftp> cd data
sftp> ls
sftp> mkdir test
sftp> ls
test
sftp> pwd
Remote working directory: /data
sftp> cd test
sftp> pwd
Remote working directory: /data/test
sftp> cd ..
sftp> rm test
Removing /data/test
Couldn't delete file: Failure
sftp> ls
test
sftp> rmdir test
sftp> ls
You-Are-the-One-You've-Been-Waiting-For-full-galley-NOT-FINAL.pdf
sftp> pwd
Remote working directory: /data
sftp> get You-Are-the-One-You\'ve-Been-Waiting-For-full-galley-NOT-FINAL.pdf
Fetching /data/You-Are-the-One-You've-Been-Waiting-For-full-galley-NOT-FINAL.pdf to You-Are-the-One-You've-Been-Waiting-For-full-galley-NOT-FINAL.pdf
/data/You-Are-the-One-You've-Been-Waiting-For-full-galley-NOT-FINAL.pdf 100% 1126KB 121.5MB/s 00:00
sftp> ls
You-Are-the-One-You've-Been-Waiting-For-full-galley-NOT-FINAL.pdf
sftp> del You-Are-the-One-You\'ve-Been-Waiting-For-full-galley-NOT-FINAL.pdf
Invalid command.
sftp> rm You-Are-the-One-You\'ve-Been-Waiting-For-full-galley-NOT-FINAL.pdf
Removing /data/You-Are-the-One-You've-Been-Waiting-For-full-galley-NOT-FINAL.pdf
sftp> ls
sftp> put You-Are-the-One-You\'ve-Been-Waiting-For-full-galley-NOT-FINAL.pdf
Uploading You-Are-the-One-You've-Been-Waiting-For-full-galley-NOT-FINAL.pdf to /data/You-Are-the-One-You've-Been-Waiting-For-full-galley-NOT-FINAL.pdf
You-Are-the-One-You've-Been-Waiting-For-full-galley-NOT-FINAL.pdf 100% 1126KB 108.4MB/s 00:00
sftp> ls
You-Are-the-One-You've-Been-Waiting-For-full-galley-NOT-FINAL.pdf
sftp> exit
通过winscp测试传输文件，在data目录下才可传输，其它目录无权限
[root@oem test]# ll /sftp/user1/data/
total 11928
-rw-r--r-- 1 user1 user1 11059200 Jun 9 2021 jyc.dmp
-rw-r--r-- 1 user1 user1 1152573 Apr 20 14:50 You-Are-the-One-You've-Been-Waiting-For-full-galley-NOT-FINAL.pdf
创建用来impdp导入的dmp目录：
[root@oem test]# su - oracle
Last login: Thu Apr 20 14:21:16 CST 2023 on pts/0
[oracle@oem ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 20 14:56:19 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 OEM READ WRITE NO
4 JYC READ WRITE NO
SQL> alter session set container=jyc;
Session altered.
SQL> create or replace directory dmp as '/sftp/user1/data/';
Directory created.
SQL> grant read,write on directory dmp to jyc;
Grant succeeded.
SQL>
sqldeveloper工具导入：
工具下载地址：https://www.oracle.com/database/sqldeveloper/technologies/download/
点击查看DBA
此处注意：如果选择DMP目录，因为该目录是用户上传文件的目录，对于oracle用户无权限写入日志，最后导入会报错。
查看PL/SQL代码，如果不用图形工具，则可以直接使用代码导入。
代码参考：(工具其实是调用的代码做导入而已)
set scan off
set serveroutput on
set escape off
whenever sqlerror exit
DECLARE
s varchar2(1000);
h1 number;
errorvarchar varchar2(100):= 'ERROR';
tryGetStatus number := 0;
begin
h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'SCHEMA', job_name => 'JYC02-15_46_09', version => 'COMPATIBLE');
tryGetStatus := 1;
dbms_datapump.set_parallel(handle => h1, degree => 1);
dbms_datapump.add_file(handle => h1, filename => 'IMPORT-jyc-'||to_char(sysdate,'hh24_mi_ss')||'.LOG', directory => 'DATA_PUMP_DIR', filetype=>DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 1);
dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''JYC'')');
dbms_datapump.add_file(handle => h1, filename => 'jyc.dmp', directory => 'DMP', filetype => 1);
dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_SCHEMA', old_value => 'JYC', value => UPPER('JYC') );
dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
dbms_datapump.set_parameter(handle => h1, name => 'TABLE_EXISTS_ACTION', value => 'REPLACE');
dbms_datapump.set_parameter(handle => h1, name => 'SKIP_UNUSABLE_INDEXES', value => 0);
dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
dbms_datapump.detach(handle => h1);
errorvarchar := 'NO_ERROR';
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF ((errorvarchar = 'ERROR')AND(tryGetStatus=1)) THEN
DBMS_DATAPUMP.DETACH(h1);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
RAISE;
END;
/
导入完成后，检查记录验证：
简单总结：
1.明确客户安全需求，确保用户只能sftp，而不可ssh，各厂商数据隔离，需要严格测试验证。
2.通过客户端工具连接数据库导入，推荐使用oracle自己免费的sqldevelper工具操作，也可以通过上述PL/SQL脚本的方式直接操作。导入可以根据自身实际情况选择全库，方案，表等方式导入，灵活应对。
3.如果有采用exp导出的dmp(非expdp)则可以直接在客户端做导入，无需sftp传输到数据库服务器上，注意验证最终导入数据和对象的准确性。
最后修改时间：2023-04-21 10:29:18
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
