[오라클] DATA PUMP 스크립트

-- 1번 데이터베이스(orcl hr) --> 2번 데이터베이스(orcl02 scott)으로 expdp

-- 현재 인스턴스 확인 및 export할 table, index의 tablespace 확인
SQL> select instance_name, status from v$instance;
SQL> select table_name, tablespace_name from all_tables where owner='HR';
SQL> select index_name, tablespace_name from all_indexes where owner='HR';

SQL> CONN / AS SYSDBA
SQL> CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
SQL> GRANT READ, WRITE ON DIRECTORY test_dir TO hr;
SQL> !
$ expdp hr/hr tables=employees,departments directory=test_dir dumpfile=emp_dept.dmp logfile=expdpemp_dept.log

...사전 작업...
...동일 호스트 USER(유저명 oracle)로 DBCA 사용 두번째 데이터베이스 ORCL02 생성...

$ . oraenv
-- oraenv 에러날 경우 오타 및 /etc/oratab 확인. database을 orcl02로 변경 후 계속 진행.

$ sqlplus / as sysdba

-- 인스턴스명 및 상태 확인 (nomount / mount / open)
SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
orcl02           OPEN

-- smon 프로세스 여부 확인
$ ps -ef | grep smon

-- startup 기동 안될 경우 파라미터 파일 확인 및 생성 
SQL> create pfile from spfile;

-- 파라미터 파일 생성 시 ORA-01565, ORA-27037에러 날 경우 spfile 위치를 수동으로 잡아준다.
   (ERROR at line 1: ORA-01565: error in identifying file '?=/dbs/spfile@.ora' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7)
SQL> create pfile from spfile='/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileorcl02.ora';

-- 파라미터 생성 확인 
SQL> !
[oracle@enp0s3 dbs]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1/dbs
[oracle@enp0s3 dbs]$ ls -la
합계 48
drwxr-xr-x.  2 oracle oinstall 4096  2월  8 10:31 .
drwxr-xr-x. 70 oracle oinstall 4096  1월 27 15:04 ..
-rw-r--r--.  1 oracle oinstall 1263  2월  8 10:31 initorcl2.ora
-rw-r-----.  1 oracle oinstall 3584  2월  8 09:54 spfileorcl02.ora
[oracle@enp0s3 dbs]$ exit
SQL> startup
ORACLE instance started.

Total System Global Area  893384752 bytes
Fixed Size                  8902704 bytes
Variable Size             239075328 bytes
Database Buffers          641728512 bytes
Redo Buffers                3678208 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode

-- database 두 개 이상 mount 안될 경우, 1번 데이터베이스 shutdown 후 2번 startup, 그 후 다시 1번 startup
-- 기존 사용중인 orcl database shutdown 후 orcl02 startup mount 성공 
SQL> shutdown immediate
[oracle@enp0s3 dbs]$ . oraenv
ORACLE_SID = [orcl] ? orcl02
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@enp0s3 dbs]$ sqlplus / as sysdba

-- import받을 유저 생성
SQL> create user scott identified by tiger;
SQL> grant create session to scott;
SQL> conn scott/tiger

-- create와 grant 한번에 할 수 있는 shortcut도 있다.
-- 아래와 같이 grant 뒤에 identified by를 추가
SQL> grant create session to scott identified by scott;
-- 단, 해당 유저가 이미 존재할 경우 권한을 부여하고 비밀번호도 변경되므로 주의 

-- 테이블 생성 권한 부여 (DML,drop은 본인 소유 오브젝트에서 기본이라 create만 필요) 및 imp_full_database 권한 부여 
SQL> conn / as sysdba
SQL> grant create table to scott;
SQL> grant IMP_FULL_DATABASE to USER_B;

-- optional. export한 테이블이 sysaux등 tablespace사용하여 export한 경우, 
-- tablespace에 대한 권한 부여
SQL> grant unlimited tablespace to scott;

-- insert 시 tablespace에 대한 권한이 없을 경우 ORA-01950 에러 발생.. alter .. quota .. 명령으로 사용 용량(권한)할당 
   ORA-01950: no privileges on tablespace 'USERS'
SQL> alter user scott quota 50M on users;

-- scott 계정으로 다시 접속하여 사용 권한 및 용량 확인 
SQL> conn scott/scott
SQL> select tablespace_name, bytes, max_bytes
from user_ts_quotas;

-- orcl, orcl02 양쪽에서 아래 쿼리로 tablespace 명칭 일치 여부 확인 
SQL> select tablespace_name, contents from dba_tablespaces;

-- import 전 scott 소유 테이블 확인 
SQL> select table_name from user_tables;
-- import 전 디렉토리 여부 확인. 없을 경우 생성
SQL> select owner, directory_name from all_directories
SQL> create or replace directory test_dir as '/u01/app/oracle/oradata';
SQL> grant read, write on directory test_dir to scott;


-- impdp 실행
-- 대상 데이터베이스인 orcl02에서 directory 생성 안되서 오류 발생, import 실패 
$ impdp scott/tiger tables=employees,departments directory=test_dir dumpfile=emp_dept.dmp logfile=impdpemp_dept.log

Import: Release 19.0.0.0.0 - Production on Tue Feb 8 16:00:21 2022
Version 19.3.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
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name TEST_DIR is invalid

-- directory 생성, 권한 부여

-- 다시 시도 -> 유저명 달라서 remap 필요.. 
-- 참고: https://support.oracle.com/epmos/faces/DocumentDisplay?parent=EXTERNAL_SEARCH&sourceId=PROBLEM&id=1341446.1&_adf.ctrl-state=15rcp07cp3_4&_afrLoop=241305629763973 
$ impdp scott/tiger tables=hr.employees,hr.departments directory=test_dir dumpfile=emp_dept.dmp logfile=impdpemp_dept.log

Import: Release 19.0.0.0.0 - Production on Tue Feb 8 16:25:46 2022
Version 19.3.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
ORA-31655: no data or metadata objects selected for job
ORA-39154: Objects from foreign schemas have been removed from import
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/pw tables=hr.employees,hr.departments directory=test_dir dumpfile=emp_dept.dmp logfile=impdpemp_dept.log
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Tue Feb 8 16:25:55 2022 elapsed 0 00:00:05

-- 시도 3. source db의 hr과 target db의 scott의 tablespace 각각 달라(sysaux, users) import 실패 
-- remap_tablespace, remap_schema, remap_table 고려. 
[oracle@enp0s3 ~]$ impdp scott/tiger tables=hr.employees,hr.departments directory=test_dir dumpfile=emp_dept.dmp logfile=impdpemp_dept.log remap_schema=hr:scott

Import: Release 19.0.0.0.0 - Production on Wed Feb 9 09:31:15 2022
Version 19.3.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 "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/pw tables=hr.employees,hr.departments directory=test_dir dumpfile=emp_dept.dmp logfile=impdpemp_dept.log remap_schema=hr:scott
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"SCOTT"."DEPARTMENTS" failed to create with error:
ORA-01950: no privileges on tablespace 'SYSAUX'

-- 시도 4. grant unlimited tablespace 부여, ORA-39083 에러 발생 <-- 부모 테이블인 LOCATIONS 생성되지 않아 constraint, trigger object 오류발생
;;;
Import: Release 19.0.0.0.0 - Production on Wed Feb 9 09:52:42 2022
Version 19.3.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 "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/pw tables=hr.employees,hr.departments directory=test_dir dumpfile=emp_dept.dmp logfile=impdpemp_dept.log remap_schema=hr:scott
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMPLOYEES"                         17.08 KB     107 rows
. . imported "SCOTT"."DEPARTMENTS"                       7.125 KB      27 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT:"SCOTT"."DEPT_LOC_FK" failed to create with error:
ORA-00942: table or view does not exist

Failing sql is:
ALTER TABLE "SCOTT"."DEPARTMENTS" ADD CONSTRAINT "DEPT_LOC_FK" FOREIGN KEY ("LOCATION_ID") REFERENCES "SCOTT"."LOCATIONS" ("LOCATION_ID") ENABLE
ORA-39083: Object type REF_CONSTRAINT:"SCOTT"."EMP_JOB_FK" failed to create with error:
ORA-00942: table or view does not exist

Failing sql is:
ALTER TABLE "SCOTT"."EMPLOYEES" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "SCOTT"."JOBS" ("JOB_ID") ENABLE
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
ORA-39082: Object type TRIGGER:"SCOTT"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"SCOTT"."UPDATE_JOB_HISTORY" created with compilation warnings
Job "SCOTT"."SYS_IMPORT_TABLE_01" completed with 4 error(s) at Wed Feb 9 09:53:05 2022 elapsed 0 00:00:22

-- 시도 5. 4번째에서 expdp할때 FK CONSTRAINT 걸려있는 테이블을 포함하지 않아 IMPORT할때 가져오지 않은 테이블을 참조하는 CONSTRAINT, TRIGGER같은 OBJECT 처리에서 실패가 발생.. 4번과 똑같은 커맨드로 다시 실행 

;;;
Import: Release 19.0.0.0.0 - Production on Wed Feb 9 11:06:10 2022
Version 19.3.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 "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/pw tables=hr.employees,hr.departments directory=test_dir dumpfile=emp_dept.dmp logfile=impdpemp_dept.log remap_schema=hr:scott
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "SCOTT"."DEPARTMENTS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."EMPLOYEES" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SCOTT"."SYS_IMPORT_TABLE_01" completed with 2 error(s) at Wed Feb 9 11:06:16 2022 elapsed 0 00:00:04

-- 제약 조건 확인 시 4번에서 문제가 발생한 "DEPT_LOC_FK", EMP_JOB_FK는 import안된 것을 확인할 수 있다. 
SELECT * FROM ALL_CONSTRAINTS ac WHERE table_name='DEPARTMENTS' AND owner = 'SCOTT';