一、生产环境数据泵导出
1. 进入生产容器数据库
[oracle@ ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 17 14:48:08 2024
Version 19.13.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 EKPDB READ WRITE NO
4 APEXTESTPDB MOUNTED
5 WEYNBYDB READ WRITE NO
6 CYYYPDB READ WRITE NO
7 APEXTESTPDB3 MOUNTED
8 APPEXPDB19C READ WRITE NO
9 NCCDB READ WRITE NO
10 SALESPORTAL READ WRITE NO
SQL> alter session set container=APPEXPDB19C;
Session altered.
2. 查询生产环境数据泵导出目录
SQL> set lines 180 pages 333
col DIRECTORY_PATH for a60
col DIRECTORY_NAME for a30
select directory_name,directory_path from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';
DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------------------------------------
DATA_PUMP_DIR /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/log/ED9618910F
8CA999E0533E040B0AB2D5
3. 查询生产环境数据数据量大小
SQL> set lines 180 pages 333
col username for a38
select username,CREATED, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where account_status = 'OPEN' order by 2;
USERNAME CREATED DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
-------------------------------------- ------------------- ------------------------------ ------------------------------
SYS 2019-04-17 00:56:32 SYSTEM TEMP
SYSTEM 2019-04-17 00:56:33 SYSTEM TEMP
DBSNMP 2019-04-17 01:32:53 SYSAUX TEMP
APEXTEST 2022-11-16 19:53:25 APEX TEMP
APEX_200200 2022-11-16 20:05:07 SYSAUX TEMP
APEX_PUBLIC_USER 2022-11-16 20:05:08 SYSAUX TEMP
APEX_LISTENER 2022-11-16 20:18:01 SYSAUX TEMP
APEX_REST_PUBLIC_USER 2022-11-16 20:18:01 SYSAUX TEMP
DEV_BIPLATFORM 2022-11-16 20:31:51 DEV_BIPLATFORM DEV_IAS_TEMP
DEV_UMS 2022-11-16 20:31:51 DEV_IAS_UMS DEV_IAS_TEMP
YNBY_APEX 2022-11-16 20:31:51 APEX_3701179619811561 TEMP
JKTEST 2022-11-16 20:31:51 USERS TEMP
DEV_WLS_RUNTIME 2022-11-16 20:31:52 DEV_WLS DEV_IAS_TEMP
DEV_STB 2022-11-16 20:31:52 DEV_STB DEV_IAS_TEMP
DEV_WLS 2022-11-16 20:31:52 DEV_WLS DEV_IAS_TEMP
DEV_IAU_APPEND 2022-11-16 20:31:53 DEV_IAU DEV_IAS_TEMP
DEV_OPSS 2022-11-16 20:31:53 DEV_IAS_OPSS DEV_IAS_TEMP
DEV_IAU_VIEWER 2022-11-16 20:31:53 DEV_IAU DEV_IAS_TEMP
DEV_MDS 2022-11-16 20:31:53 DEV_MDS DEV_IAS_TEMP
DEV_IAU 2022-11-16 20:31:54 DEV_IAU DEV_IAS_TEMP
GD 2022-11-16 20:44:24 APEX TEMP
ORDS_METADATA 2022-11-16 20:46:14 SYSAUX TEMP
ZABBIX 2022-11-18 17:29:41 SYSTEM TEMP
C##BAKUSR 2023-09-26 11:44:50 APEX TEMP
24 rows selected.
SQL> select sum(bytes)/1024/1024/1024 ,owner from dba_segments where owner in('APEXTEST','APEX_200200','APEX_PUBLIC_USER','APEX_LISTENER','APEX_REST_PUBLIC_USER','DEV_BIPLATFORM','DEV_UMS','YNBY_APEX','JKTEST','DEV_WLS_RUNTIME','DEV_STB','DEV_WLS','DEV_IAU_APPEND','DEV_OPSS','DEV_IAU_VIEWER','DEV_MDS','DEV_IAU','GD','ORDS_METADATA','ZABBIX','C##BAKUSR') group by owner;
SUM(BYTES)/1024/1024/1024 OWNER
------------------------- --------------------------------------------------------------------------------------------------------------------------------
.00012207 DEV_WLS_RUNTIME
.000976563 ORDS_METADATA
.00012207 DEV_WLS
.000854492 DEV_STB
.031555176 YNBY_APEX
.007385254 DEV_MDS
.050048828 DEV_OPSS
.00994873 DEV_BIPLATFORM
.00012207 DEV_IAU
1.72277832 APEX_200200
.006958008 DEV_UMS
11 rows selected.
根据创建时间，这里迁移导出SCHEMA：APEXTEST,APEX_200200,APEX_PUBLIC_USER,APEX_LISTENER,APEX_REST_PUBLIC_USER,DEV_BIPLATFORM,DEV_UMS,YNBY_APEX,JKTEST,DEV_WLS_RUNTIME,DEV_STB,DEV_WLS,DEV_IAU_APPEND,DEV_OPSS,DEV_IAU_VIEWER,DEV_MDS,DEV_IAU,GD,ORDS_METADATA,数据泵导出数据至/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/log/ED9618910F8CA999E0533E040B0AB2D5
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
4. 查询配置具有导出权限账号和密码
配置具有DBA权限访问PDB的账号
[oracle@ypzh19db1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 17 17:27:03 2024
Version 19.13.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 EKPDB READ WRITE NO
4 APEXTESTPDB MOUNTED
5 WEYNBYDB READ WRITE NO
6 CYYYPDB READ WRITE NO
7 APEXTESTPDB3 MOUNTED
8 APPEXPDB19C READ WRITE NO
9 NCCDB READ WRITE NO
10 SALESPORTAL READ WRITE NO
SQL> alter session set container=APPEXPDB19C;
Session altered.
SQL> alter user gd identified by "temp_2024";
User altered.
SQL> grant dba to gd;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
[oracle@ypzh19db1 ~]$ sqlplus gd/temp_2024@10.11.*.*:1527/APPEXPDB19C
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 17 16:51:25 2024
Version 19.13.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Mon Jan 22 2024 14:46:29 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
5. 查询生产环境数据泵导出目录大小
[oracle@ypzh19db1 ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 504G 0 504G 0% /dev
tmpfs 504G 8.2G 496G 2% /dev/shm
tmpfs 504G 4.1G 500G 1% /run
tmpfs 504G 0 504G 0% /sys/fs/cgroup
/dev/mapper/ol-root 68G 34G 35G 50% /
/dev/mapper/VgOracle-lv_u01 400G 214G 186G 54% /u01
/dev/loop0 4.4G 4.4G 0 100% /mnt
/dev/sdy1 1014M 172M 843M 17% /boot
tmpfs 101G 0 101G 0% /run/user/1100
tmpfs 101G 0 101G 0% /run/user/0
6. 从生产环境数据泵导出数据
[root@ypzh19db1 ~]# su - oracle
Last login: Thu Jul 18 08:46:18 CST 2024
[oracle@ypzh19db1 ~]$ nohup expdp gd/temp_2024@10.11.4.62:1521/APPEXPDB19C SCHEMAS=APEXTEST,APEX_200200,APEX_PUBLIC_USER,APEX_LISTENER,APEX_REST_PUBLIC_USER,DEV_BIPLATFORM,DEV_UMS,YNBY_APEX,JKTEST,DEV_WLS_RUNTIME,DEV_STB,DEV_WLS,DEV_IAU_APPEND,DEV_OPSS,DEV_IAU_VIEWER,DEV_MDS,DEV_IAU,GD,ORDS_METADATA,ZABBIX,C##BAKUSR directory=DATA_PUMP_DIR dumpfile=apex_20240718.dmp job_name=apex_20240718_expdp METRICS=Y logfile=apex_20240718_expdp.log COMPRESSION=ALL &
[2] 5532
[oracle@ypzh19db1 ~]$ nohup: ignoring input and appending output to 'nohup.out'
[oracle@ypzh19db1 ~]$ ll
total 15568
-rw-r--r--. 1 oracle oinstall 0 May 9 14:22 1.sql
-rw-r--r--. 1 oracle oinstall 781652 Apr 6 2023 67.sql
-rw-r--r--. 1 oracle asmadmin 2901 May 23 19:55 6.ora
-rw-r--r--. 1 oracle oinstall 12692441 May 9 14:24 99.sql
-rw-r--r--. 1 oracle oinstall 162 Jan 17 2024 afiedt.buf
-rw-r--r--. 1 oracle oinstall 240279 Sep 8 2023 awrrpt_1_1726_1756.html
-rw-r--r--. 1 oracle oinstall 1467941 Apr 17 11:25 awrrpt_1_36883_36889.html
-rw-r--r--. 1 oracle oinstall 636759 Aug 9 2023 g_3.sql
drwxr-xr-x. 2 oracle oinstall 4096 Apr 9 14:02 gd
-rw-r--r--. 1 oracle oinstall 93675 Jan 17 2024 grant_s.sql
-rw-------. 1 oracle oinstall 2006 Jul 18 08:59 nohup.out
-rw-r--r--. 1 oracle asmadmin 1930 May 24 10:12 pfile.bak
[oracle@ypzh19db1 ~]$ tail -f nohup.out
Export: Release 19.0.0.0.0 - Production on Thu Jul 18 16:34:57 2024
Version 19.13.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
Starting "GD"."APEX_20240718_EXPDP": gd/********@10.11.4.62:1521/APPEXPDB19C SCHEMAS=APEXTEST,APEX_200200,APEX_PUBLIC_USER,APEX_LISTENER,APEX_REST_PUBLIC_USER,DEV_BIPLATFORM,DEV_UMS,YNBY_APEX,JKTEST,DEV_WLS_RUNTIME,DEV_STB,DEV_WLS,DEV_IAU_APPEND,DEV_OPSS,DEV_IAU_VIEWER,DEV_MDS,DEV_IAU,GD,ORDS_METADATA,ZABBIX,C##BAKUSR directory=DATA_PUMP_DIR dumpfile=apex_20240718.dmp job_name=apex_20240718_expdp METRICS=Y logfile=apex_20240718_expdp.log COMPRESSION=ALL
W-1 Startup took 1 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
W-1 Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
W-1 Completed 30 PACKAGE_BODY objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
W-1 Completed 1525 INDEX_STATISTICS objects in 1 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
W-1 Completed 5 INDEX_STATISTICS objects in 1 seconds
......
W-1 . . exported "YNBY_APEX"."BHSC_INNER_RETURN_LINES" 0 KB 0 rows in 0 seconds using direct_path
W-1 . . exported "YNBY_APEX"."CUX_QMS_NOTIFY_USER" 0 KB 0 rows in 0 seconds using direct_path
W-1 . . exported "YNBY_APEX"."EBA_ARCHIVE_ERRORS" 0 KB 0 rows in 0 seconds using direct_path
W-1 . . exported "YNBY_APEX"."EBA_ARCHIVE_NOTIFICATIONS" 0 KB 0 rows in 0 seconds using direct_path
ORA-39165: Schema APEX_PUBLIC_USER was not found.
ORA-39165: Schema APEX_LISTENER was not found.
ORA-39165: Schema APEX_REST_PUBLIC_USER was not found.
W-1 Completed 499 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 15 seconds
W-1 Master table "GD"."APEX_20240718_EXPDP" successfully loaded/unloaded
******************************************************************************
Dump file set for GD.APEX_20240718_EXPDP is:
/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/log/ED9618910F8CA999E0533E040B0AB2D5/apex_20240718.dmp
Job "GD"."APEX_20240718_EXPDP" completed with 3 error(s) at Thu Jul 18 16:43:07 2024 elapsed 0 00:06:17
二、数据泵导入测试环境
1. 查询测试环境数据库永久表空间
[root@ ~]# su - oracle
Last login: Thu Jul 18 09:23:55 CST 2024 on pts/0
[oracle@~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 18 13:41:20 2024
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> set lines 180 pages 333
col NAME for a35
col VALUE$ for a30
select NAME, VALUE$ FROM PROPS$ where NAME='DEFAULT_PERMANENT_TABLESPACE';
NAME VALUE$
----------------------------------- ------------------------------------------------------------
DEFAULT_PERMANENT_TABLESPACE USERS
SQL> set lines 180 pages 333
col NAME for a35
col VALUE$ for a30
SELECT NAME, VALUE$ FROM PROPS$ WHERE NAME='DEFAULT_TEMP_TABLESPACE';
NAME VALUE$
----------------------------------- ------------------------------
DEFAULT_TEMP_TABLESPACE TEMP
SQL> set lines 180 pages 333
col FILE_NAME for a50
col TABLESPACE_NAME for a30
SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_TEMP_FILES;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/APEXTESTDB/temp01.dbf TEMP
SQL> set lines 180 pages 333
col FILE_NAME for a50
col TABLESPACE_NAME for a30
SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/APEXTESTDB/system01.dbf SYSTEM
/u01/app/oracle/oradata/APEXTESTDB/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/APEXTESTDB/users01.dbf USERS
/u01/app/oracle/oradata/APEXTESTDB/undotbs01.dbf UNDOTBS1
2. 查询数据泵目录
SQL> select * from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';
OWNER
--------------------------------------------------------------------------------
DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
ORIGIN_CON_ID
-------------
SYS
DATA_PUMP_DIR
/u01/app/oracle/admin/Apextestdb/dpdump/
0
3. 创建与生产环境一致的表空间
查询测试环环境用户和表空间
SQL> set lines 180 pages 333
col username for a38
select username,CREATED, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where account_status = 'OPEN' order by 2;
SQL> set lines 180 pages 333
col file_name for a60
col tablespace_name for a30
select file_name , tablespace_name from dba_data_files;
4. 创建用户及设置默认表空间
SQL> create tablespace APEX datafile '/u01/app/oracle/oradata/APEXTESTDB/apex01.dbf' size 2G autoextend on;
create tablespace DEV_BIPLATFORM datafile '/u01/app/oracle/oradata/APEXTESTDB/dev_biplatform01.dbf' size 200M autoextend on;
create tablespace DEV_IAS_UMS datafile '/u01/app/oracle/oradata/APEXTESTDB/dev_ias_ums01.dbf' size 200M autoextend on;
create tablespace APEX_3701179619811561 datafile '/u01/app/oracle/oradata/APEXTESTDB/apex_370117961981156101.dbf' size 100M autoextend on;
create tablespace DEV_WLS datafile '/u01/app/oracle/oradata/APEXTESTDB/dev_wls01.dbf' size 100M autoextend on;
create tablespace DEV_STB datafile '/u01/app/oracle/oradata/APEXTESTDB/dev_stb01.dbf' size 100M autoextend on;
create tablespace DEV_IAU datafile '/u01/app/oracle/oradata/APEXTESTDB/dev_iau01.dbf' size 100M autoextend on;
create tablespace DEV_IAS_OPSS datafile '/u01/app/oracle/oradata/APEXTESTDB/dev_ias_opss01.dbf' size 100M autoextend on;
create tablespace DEV_MDS datafile '/u01/app/oracle/oradata/APEXTESTDB/dev_mds01.dbf' size 100M autoextend on;
create temporary tablespace DEV_IAS_TEMP tempfile '/u01/app/oracle/oradata/APEXTESTDB/dev_ias_temp01.dbf' size 1G autoextend off;
1
SQL> create user APEXTEST identified by "password";
SQL> alter user APEXTEST default tablespace APEX temporary tablespace TEMP;
2
SQL> create user APEX_200200 identified by "password";
SQL> alter user APEX_200200 default tablespace SYSAUX temporary tablespace TEMP;
3
SQL> create user APEX_PUBLIC_USER identified by "password";
SQL> alter user APEX_PUBLIC_USER default tablespace SYSAUX temporary tablespace TEMP;
4
SQL> create user APEX_LISTENER identified by "password";
SQL> alter user APEX_LISTENER default tablespace SYSAUX temporary tablespace TEMP;
5
SQL> create user APEX_REST_PUBLIC_USER identified by "password";
SQL> alter user APEX_REST_PUBLIC_USER default tablespace SYSAUX temporary tablespace TEMP;
6
SQL> create user DEV_BIPLATFORM identified by "password";
SQL> alter user DEV_BIPLATFORM default tablespace DEV_BIPLATFORM temporary tablespace DEV_IAS_TEMP;
7
SQL> create user DEV_UMS identified by "password";
SQL> alter user DEV_UMS default tablespace DEV_IAS_UMS temporary tablespace DEV_IAS_TEMP;
8
SQL> create user YNBY_APEX identified by "password";
SQL> alter user YNBY_APEX default tablespace APEX_3701179619811561 temporary tablespace TEMP;
9
SQL> create user JKTEST identified by "password";
SQL> alter user JKTEST default tablespace USERS temporary tablespace TEMP;
10
SQL> create user DEV_WLS_RUNTIME identified by "password";
SQL> alter user DEV_WLS_RUNTIME default tablespace DEV_WLS temporary tablespace DEV_IAS_TEMP;
11
SQL> create user DEV_STB identified by "password";
SQL> alter user DEV_STB default tablespace DEV_STB temporary tablespace DEV_IAS_TEMP;
12
SQL> create user DEV_WLS identified by "password";
SQL> alter user DEV_WLS default tablespace DEV_WLS temporary tablespace DEV_IAS_TEMP;
13
SQL> create user DEV_IAU_APPEND identified by "password";
SQL> alter user DEV_IAU_APPEND default tablespace DEV_IAU temporary tablespace DEV_IAS_TEMP;
14
SQL> create user DEV_OPSS identified by "password";
SQL> alter user DEV_OPSS default tablespace DEV_IAS_OPSS temporary tablespace DEV_IAS_TEMP;
15
SQL> create user DEV_IAU_VIEWER identified by "password";
SQL> alter user DEV_IAU_VIEWER default tablespace DEV_IAU temporary tablespace DEV_IAS_TEMP;
16
SQL> create user DEV_MDS identified by "password";
SQL> alter user DEV_MDS default tablespace DEV_MDS temporary tablespace DEV_IAS_TEMP;
17
SQL> create user DEV_IAU identified by "password";
SQL> alter user DEV_IAU default tablespace DEV_IAU temporary tablespace DEV_IAS_TEMP;
18
SQL> create user GD identified by "password";
SQL> alter user GD default tablespace APEX temporary tablespace TEMP;
19
SQL> create user ORDS_METADATA identified by "password";
SQL> alter user ORDS_METADATA default tablespace SYSAUX temporary tablespace TEMP;
20
SQL> create user ZABBIX identified by "password";
SQL> alter user ZABBIX default tablespace SYSTEM temporary tablespace TEMP;
21
SQL> create user CBAKUSR identified by "password";
SQL> alter user CBAKUSR default tablespace APEX temporary tablespace TEMP;
5. 查询用户及设置默认表空间
SQL> set lines 180 pages 333
col username for a38
select username,CREATED, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where account_status = 'OPEN' order by 2;
6. 将生产服务器上文件传输至测试服务器并修改权限
[root@ypzh19db1 ~]# scp /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/log/ED9618910F8CA999E0533E040B0AB2D5/apex_20240718.dmp 10.11.203.167:/u01/app/oracle/admin/Apextestdb/dpdump
The authenticity of host '10.11.203.167 (10.11.203.167)' can't be established.
ECDSA key fingerprint is bd:b7:1c:9c:c9:d1:8c:e1:41:02:e6:69:dc:e5:a5:2f.
Are you sure you want to continue connecting (yes/no)?
Warning: Permanently added '10.11.203.167' (ECDSA) to the list of known hosts.
root@10.11.203.167's password:
apex_20240718.dmp 100% 12MB 11.6MB/s 00:01
[root@wxc-ecs-devtest-ebsapex-dzsw-db-002 ~]# chown -R oracle:oinstall /u01/app/oracle/admin/Apextestdb/dpdump/apex_20240718.dmp
[root@wxc-ecs-devtest-ebsapex-dzsw-db-002 ~]# chmod -R 755 /u01/app/oracle/admin/Apextestdb/dpdump/apex_20240718.dmp
7. 数据泵导入数据
[root@wxc-ecs-devtest-ebsapex-dzsw-db-002 ~]# su - oracle
Last login: Sat Jul 20 13:43:55 CST 2024 on pts/0
[oracle@wxc-ecs-devtest-ebsapex-dzsw-db-002 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 21 22:48:03 2024
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
SQL> select * from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';
OWNER
--------------------------------------------------------------------------------
DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
ORIGIN_CON_ID
-------------
SYS
DATA_PUMP_DIR
/u01/app/oracle/admin/Apextestdb/dpdump/
0
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@wxc-ecs-devtest-ebsapex-dzsw-db-002 ~]$ cd /u01/app/oracle/admin/Apextestdb/dpdump/
[oracle@wxc-ecs-devtest-ebsapex-dzsw-db-002 dpdump]$ ll
total 11896
-rwxr-xr-x 1 oracle oinstall 12177408 Jul 20 13:30 apex_20240718.dmp
-rw-r----- 1 oracle oinstall 163 Jul 17 12:01 dp.log
[oracle@wxc-ecs-devtest-ebsapex-dzsw-db-002 dpdump]$ du -sh *
12M apex_20240718.dmp
4.0K dp.log
[oracle@wxc-ecs-devtest-ebsapex-dzsw-db-002 dpdump]$ nohup impdp "'/ as sysdba'" directory=DATA_PUMP_DIR dumpfile=apex_20240718.dmp job_name=apex_20240720_impdp METRICS=Y logfile=apex_20240720_impdp.log &
[1] 32872
[oracle@wxc-ecs-devtest-ebsapex-dzsw-db-002 dpdump]$ nohup: ignoring input and appending output to ‘nohup.out’
[oracle@wxc-ecs-devtest-ebsapex-dzsw-db-002 dpdump]$ ls -lrt
total 11900
-rw-r----- 1 oracle oinstall 163 Jul 17 12:01 dp.log
-rwxr-xr-x 1 oracle oinstall 12177408 Jul 20 13:30 apex_20240718.dmp
-rw-r--r-- 1 oracle oinstall 0 Jul 21 22:51 apex_20240720_impdp.log
-rw------- 1 oracle oinstall 1910 Jul 21 22:52 nohup.out
[oracle@wxc-ecs-devtest-ebsapex-dzsw-db-002 dpdump]$
[oracle@wxc-ecs-devtest-ebsapex-dzsw-db-002 dpdump]$
[oracle@wxc-ecs-devtest-ebsapex-dzsw-db-002 dpdump]$ tail -30f apex_20240720_impdp.log
^Z
[2]+ Stopped tail -30f apex_20240720_impdp.log
[oracle@wxc-ecs-devtest-ebsapex-dzsw-db-002 dpdump]$ tail -20f nohup.out
ORA-01435: user does not exist
Failing sql is:
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'APPEXPDB19C', inst_scn=>'6335193129155');COMMIT; END;
W-1 Completed 18 PROCACT_SCHEMA objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/DB_LINK
W-1 Completed 1 DB_LINK objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
W-1 Completed 284 SYNONYM objects in 1 seconds
W-1 Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
W-1 Completed 25 TYPE objects in 0 seconds
……
W-1 . . imported "DEV_OPSS"."CT_0" 5.070 KB 15 rows in 0 seconds using direct_path
W-1 . . imported "DEV_BIPLATFORM"."CDS_OBJECT" 0 KB 0 rows in 0 seconds using direct_path
W-1 . . imported "DEV_BIPLATFORM"."SERVICELCM_ATTRIBUTE_COMBO" 0 KB 0 rows in 0 seconds using direct_path
W-1 . . imported "DEV_OPSS"."CT_61" 0 KB 0 rows in 0 seconds using direct_path
W-1 . . imported "DEV_BIPLATFORM"."S_NQ_SEARCH_WEBCAT_EVENTS" 0 KB 0 rows in 0 seconds using direct_path
W-1 Processing object type SCHEMA_EXPORT/TABLE/COMMENT
W-1 Completed 158 COMMENT objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
W-1 Completed 30 PACKAGE objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
W-1 Completed 4 OBJECT_GRANT objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
W-1 Completed 1 PROCEDURE objects in 0 seconds
[1] Exit 5 nohup impdp "'/ as sysdba'" directory=DATA_PUMP_DIR dumpfile=apex_20240718.dmp job_name=apex_20240720_impdp METRICS=Y logfile=apex_20240720_impdp.log
