데이터 펌프로 자율운영 데이터베이스에 데이터 임포트

사전 준비

데이터 펌프(expdp)로 내려받은 덤프 파일 (*.dmp)

데이터 펌프(impdp)를 실행할 인스턴스 (자율운영 데이터베이스는 OS 레벨 접근 불가능하므로)

https://docs.oracle.com/en-us/iaas/autonomous-database-serverless/doc/connect-prepare-oci-wallets.html

https://docs.oracle.com/en/database/oracle/oracle-database/21/lacli/install-instant-client-using-rpm.html#GUID-2E81E2AE-E94C-413F-99B2-AE9A3949F05D

 

인스턴트 클라이언트 설치 (Oracle Linux 8)

sudo dnf list installed | grep instantclient
(sudo dnf remove oracle-instantclient19.5-basic.x86_64)
(sudo dnf remove oracle-release-el8)

sudo dnf install -y oracle-instantclient-release-el8
sudo dnf install -y oracle-instantclient-basic
sudo dnf install -y oracle-instantclient-sqlplus
sudo dnf install -y oracle-instantclient-tools

(sudo yum install -y yum-plugin-versionlock)
(sudo dnf versionlock -y oracle-instantclient-release-el8)

 

자율운영 데이터베이스 Wallet 파일 다운로드

 

접속 : Connect SQL*Plus with a Wallet (mTLS)

https://docs.oracle.com/en-us/iaas/autonomous-database-serverless/doc/connect-tools.html

mkdir adb_credentials
cd adb_credentials/
ls -ltr
unzip Wallet_ODIREPO.zip
pwd
vi sqlnet.ora
export TNS_ADMIN=/home/opc/adb_credentials
sqlplus admin@odirepo_low

 

sudo find / -name impdp
export PATH=/usr/lib/oracle/21/client64/bin:$PATH
impdp

 

Credential 생성 - username, password는 치환 필요

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME_DATAPUMP',
    username => 'adb_user@example.com',
    password => 'password'
  );
END;

SELECT credential_name, username FROM DBA_CREDENTIALS;

 

임포트 할 사용자 생성 (스키마)

 

 

 

덤프파일 오브젝트 스토리지에 업로드

 

데이터펌프 임포트

impdp admin@odirepo_low \
      directory=data_pump_dir \
      credential=DEF_CRED_NAME_DATAPUMP \
      dumpfile= https://objectstorage.ap-singapore-1.oraclecloud.com/p/....../b/bucket-datapump/o/DATAPUMP.dmp \
      encryption_pwd_prompt=yes \
      remap_schema=IMPDPTEST_SOURCE:IMPDPTEST \
      exclude=statistics \

BEGIN
   DBMS_STATS.GATHER_SCHEMA_STATS(
      ownname  => 'YOUR_SCHEMA_NAME',
      estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
      method_opt => 'FOR ALL COLUMNS SIZE AUTO',
      cascade => TRUE
   );
END;

또는

EXEC DBMS_STATS.GATHER_TABLE_STATS('YOUR_SCHEMA_NAME', 'YOUR_TABLE_NAME');

 

>> 2 ECPU
at Thu Oct 12 15:26:19 2023 elapsed 0 00:23:09

>> 16 ECPU + parallel=16
at Thu Oct 12 15:38:38 2023 elapsed 0 00:04:14

 

 

 

 

참고

https://docs.oracle.com/en-us/iaas/autonomous-database-serverless/doc/connect-prepare-oci-wallets.html

https://docs.oracle.com/en/database/oracle/oracle-database/21/lacli/install-instant-client-using-rpm.html#GUID-2E81E2AE-E94C-413F-99B2-AE9A3949F05D

https://www.oracle.com/technetwork/database/features/instant-client/index-097480.html