在本文中，我们将了解如何在涉及容器数据库时使用导出-导入实用程序。
在我们的测试用例中，我们有 PROD 和 TEST 服务器。两个数据库版本都是19c，操作系统是OEL 7.9
A. 第一步是准备 Prod 和 Test 数据库的导出过程。
- 检查将从主系统导出的模式大小
SELECT (Sum(bytes)/1024/1024) AS total_size_MB FROM dba_segments WHERE owner ='X';
–500 GB
2. 对于这两个系统检查目录路径 ve 磁盘大小。
— on PROD database
SQL> alter session set container=PROD;
SQL> SELECT * FROM dba_directories;
SQL> create directory EXP_DIR as ‘/orabck/export/’;
SQL> grant exp_full_database to helios;
SQL> grant read , write on directory EXP_DIR to helios;
–on TEST database
SQL> alter session set container=TEST;
SQL> SELECT * FROM dba_directories;
SQL> create directory EXP_DIR as ‘/orabck/export/’;
SQL> grant exp_full_database to helios;
SQL> grant read , write on directory EXP_DIR to helios;
3. 您可能需要在删除之前更改 TEST 用户的密码。
select dbms_metadata.get_ddl(‘USER’,’PPC_WEB’) from dual;
–User X on Test database
ALTER USER “X” IDENTIFIED BY VALUES ‘S:F1B891902ADD0F179EB37EBBBD75B548E81064D4691120D214A9A810B810BC87
4. 两个数据库上的表空间检查。
select distinct(TABLESPACE_NAME) from dba_extents where owner='X';
B. EXPORT 流程步骤
- 如果您将使用 PARALLEL 选项，请检查 CPU 计数。
–CPU：12
2. 在 Prod 数据库上启动导出过程。
–on prod
nohup expdp helios/xxx@PROD SCHEMAS=X DIRECTORY=EXP_DIR DUMPFILE=X_30032022_%U.dmp PARALLEL=6 LOGFILE=X_30032022.log compression=all EXCLUDE=STATISTICS &
— Control expdp session
SELECT B.USERNAME, A.SID, B.OPNAME, B.TARGET,
ROUND(B.SOFAR*100/B.TOTALWORK,0) || ‘%’ AS “%DONE”, B.TIME_REMAINING,
TO_CHAR(B.START_TIME,’YYYY/MM/DD HH24:MI:SS’) START_TIME
FROM V$SESSION_LONGOPS B, V$SESSION A
WHERE A.SID = B.SID
AND B.OPNAME LIKE ‘%EXPORT%’
ORDER BY 6;
3. 开始复制过程到测试服务器
nohup scp /orabck/export/*.dmp oracle@TEST_SERVER:/yedek/export &
C. 在测试服务器上让我们启动 imp 进程。
- 在删除用户之前（如果需要）采用模式脚本
- 杀死用户会话
select ‘ALTER SYSTEM KILL SESSION ”’ || SID || ‘,’ || SERIAL# ||’,’|| ‘@’ ||inst_id|| ”’ immediate;’ from gv$session where schemaname=’X’;
3. 锁定用户
ALTER USER X ACCOUNT LOCK;
4. Drop user ==> on test server!!!
DROP USER X CASCADE;
D.开始导入
- 检查测试服务器 CPU 计数
–CPU：8
2. 开始导入
–X
export ORACLE_PDB_SID=TEST <<< YOU NEED TO SET IT!
impdp “‘/ as sysdba'” directory=EXP_DIR dumpfile=X_30032022_%U.dmp parallel=4 logfile=imp_X_30032022.log
- Check import process SELECT B.USERNAME, A.SID, B.OPNAME, B.TARGET,
ROUND(B.SOFAR*100/B.TOTALWORK,0) || ‘%’ AS “%DONE”, B.TIME_REMAINING,
TO_CHAR(B.START_TIME,’YYYY/MM/DD HH24:MI:SS’) START_TIME
FROM V$SESSION_LONGOPS B, V$SESSION A
WHERE A.SID = B.SID
AND B.OPNAME LIKE ‘%IMPORT%’
ORDER BY 6;
E. Start start for schema after import –> Its recommended
exec dbms_stats.gather_schema_stats('X', cascade=>TRUE,degree=>4);
原文标题：How to export&import process on container database
原文作者：Helios
原文链接：https://heliosguneserol.com/2022/04/07/how-to-exportimport-process-on-container-database/
