Usage2ADW 무료 툴 - OCI 사용량 DBCS로 저장

사전준비

2023.06.21 - [4. 개발 플랫폼 Low Code] - APEX 수동설치 on DBCS

2023.06.21 - [4. 개발 플랫폼 Low Code] - Usage2ADW 무료 툴 - OCI 사용량 확인 with APEX

 

http://<Public IP>:8080/

 

APEX 연결 확인 및 기존 Workspace 생성여부 확인 (APEX만 설치된 상태로 Workspace 생성되어 있지 않음)

 

앞서 생성한 Usage2ADW의 VM 실행 스택에서 Terraform 파일 다운받아 bootstrap.tpl 내용 확인

#cloud-config
  
runcmd:
  - su opc
  - cd /home/opc/
  - export LOG=/home/opc/boot.log
  - export APPDIR=/home/opc/usage_reports_to_adw
  - export CRED=$APPDIR/config.user
  - export WALLET=/home/opc/wallet.zip
  - echo "Start process at `date`" > $LOG
  - chown opc:opc $LOG
  
  # Create the adw properties file
  - echo "Post variables into config.user file." >> $LOG
  - echo "DATABASE_USER=USAGE" > $CRED   
  - echo "DATABASE_ID=${db_db_id}" >> $CRED
  - echo "DATABASE_NAME=${db_db_name}_low" >> $CRED
  - echo "DATABASE_PASS=${db_app_password}" >> $CRED 
  - echo "DATABASE_ADMIN=${db_admin_password}" >> $CRED
  - echo "EXTRACT_DATE=${extract_from_date}" >> $CRED
  - echo "TAG_SPECIAL=${extract_tag1_special_key}" >> $CRED
  - echo "TAG2_SPECIAL=${extract_tag2_special_key}" >> $CRED
  - chown opc:opc $CRED

  # get wallet from oci
  - echo "Download OCI wallet" >> $LOG
  - echo "oci db autonomous-database generate-wallet --autonomous-database-id ${db_db_id} --password ${db_app_password} --file $WALLET --auth instance_principal" >> $LOG
  - su - opc -c "oci db autonomous-database generate-wallet --autonomous-database-id ${db_db_id} --password ${db_app_password} --file $WALLET --auth instance_principal" >> $LOG
  - chown opc:opc $WALLET >> $LOG

  # Sleep 60 seconds to wait for the policy to be created 
  - echo "Waiting 60 seconds..." >> $LOG
  - sleep 20
  - echo "Waiting 40 seconds..." >> $LOG
  - sleep 20
  - echo "Waiting 20 seconds..." >> $LOG
  - sleep 20

  # Continue Setup using setup_usage2adw.sh
  - echo "Executing setup_usage2adw.sh" >> $LOG
  - su - opc -c '/home/opc/usage_reports_to_adw/setup/setup_usage2adw.sh' >>$LOG

final_message: "The system is finally up, after $UPTIME seconds"

 

Shell Script 확인 및 새로 생성

cp /home/opc/usage_reports_to_adw/setup/setup_usage2adw.sh /home/opc/usage_reports_to_adw/setup/setup_usage2dbcs.sh
cd /home/opc/usage_reports_to_adw/setup/

vi setup_usage2dbcs.sh

 

아래와 같이 수정 필요

1) export SCRIPT=$APPDIR/setup/setup_usage2adw.sh TO export SCRIPT=$APPDIR/setup/setup_usage2dbcs.sh

2) Comment Extract Wallet from wallet.zip part
###########################################
# Extract Wallet from wallet.zip
###########################################
#mkdir -p /home/opc/ADWCUSG
#echo "" | tee -a $LOG
#echo "2. Extract database wallet." | tee -a $LOG
#unzip -o $HOME/wallet.zip -d /home/opc/ADWCUSG
#if [ $? -eq 0 ]; then
#   echo "   Database Wallet extracted successfully" | tee -a $LOG
#else
#   echo "   Error Extracting, Abort" | tee -a $LOG
#   exit 1
#fi
#rm -f wallet.zip wallet_file

3) create application schema and enable APEX part user change
3-1) ADMIN 사용자명을 SYSTEM 사용자명으로
3-2) dwrole 삭제

4) add tnsnames
vi /home/opc/ADWCUSG/tnsnames.ora
usage2dbcs = (DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.1.137)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=DB06202_pdb1.usagejkm.usagejkm.oraclevcn.com)))

4-1) optional for telnet install
cd /etc/yum.repos.d/
sudo vi oracle-instantclient-ol8.repo

FROM>>
[ol8_oracle_instantclient21]
name=Oracle Instant Client 21 for Oracle Linux $releasever ($basearch)
baseurl=https://yum$ociregion.oracle.com/repo/OracleLinux/OL8/oracle/instantclient21/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1

TO>>
[ol8_oracle_instantclient21]
name=Oracle Instant Client 21 for Oracle Linux $releasever ($basearch)
baseurl=http://yum.oracle.com/repo/OracleLinux/OL7/oracle/instantclient21/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1

sudo yum -y install telnet
telnet 10.0.1.137 1521
sqlplus system@usage2dbcs

5) vi /home/opc/usage_reports_to_adw/config.user
DATABASE_USER=USAGE
DATABASE_ID=<Find from OCI Console DB SYSTEM>
DATABASE_NAME=usage2dbcs
DATABASE_PASS=<DB Password>
DATABASE_ADMIN=<DB Admin Password>
EXTRACT_DATE=2023-05
TAG_SPECIAL=Oracle-Tags.CreatedBy
TAG2_SPECIAL=Oracle-Tags.Project

 

 

 

 

 

참고