Cloud Native OCI

Connecting Oracle ATP


Created with ❤ by Oracle A-Team

Lab Overview

  • Introducing microservices using ATP
  • Oracle Autonomous Transaction Processing (ATP) setup
  • Connecting to ATP
  • Build & validation

MuShop Services with ATP

The MuShop runtime includes the services below, which connect to Oracle Autonomous Transaction Processing (ATP) databases. Each uses its own schema and connection, while providing REST services over HTTP.


Catalogue: src/catalogue
Services for MuShop products, categories, and search
Users: src/user
eCommerce customer data with profile and authentication services
Carts: src/carts
Customer shopping cart with SODA (Simple Oracle Document Access) table schemas
Orders: src/orders
Java, Springboot
Customer order processing services

Connecting to ATP

  • Provision ATP
  • Download Wallet
  • Create Secrets
  • Configure Chart
  • Deploy
  • Under the Hood
  • Create your Oracle ATP Database instance ℹ️ Documentation
    Using the Cloud Shell:
    
    export compartment_id="<COMPARTMENT_ID>"
                                
    
    oci db autonomous-database create \
    --cpu-core-count=1 \
    --data-storage-size-in-tbs=1 \
    --db-name=OCIWorkshop \
    --display-name=OCIWorkshop \
    --compartment-id=${compartment_id} \
    --admin-password=<PASSWORD>
                                
    📝 Be sure to make note of the ADMIN Password used when provisioning the ATP Instance
  • Download the database connection (Wallet): ℹ️ Documentation
    DB Connection (Wallet) folder contents:
    
    oci db autonomous-database list \
    --query "data[*].{name:\"db-name\", OCID:id, State:\"lifecycle-state\"}" \
    --output table \
    --compartment-id=${compartment_id}
    
                
    
    oci db autonomous-database generate-wallet --file=wallet.zip --autonomous-database-id=<ATP_OCID> --password=<PASSWORD>
                
    
    unzip wallet.zip -d wallet
    ls -ltr wallet
    cat wallet/tnsnames.ora
                
    📝 Be sure to make note of the Wallet Password used and connection TNS Name when downloading the Wallet
  • Configure ATP connection details in the form of Kubernetes secrets
    1. Create oadb-admin secret containing the database administrator password:
      kubectl create secret generic oadb-admin \
        --from-literal=oadb_admin_pw='<DB_ADMIN_PASSWORD>'
      ℹ️ The admin credentials are used once to initialize schemas in the application
    2. Create oadb-wallet secret with the Wallet contents using the downloaded Wallet_*.zip The extracted directory is specified as the secret file path:
      kubectl create secret generic oadb-wallet \
        --from-file='<PATH_TO_EXTRACTED_WALLET_FOLDER>'
      ℹ️ Each file will become a key name in the secret data.
    3. Create oadb-connection secret with the Wallet password and the service TNS name to use for connections:
      kubectl create secret generic oadb-connection \
        --from-literal=oadb_wallet_pw='<DB_WALLET_PASSWORD>' \
        --from-literal=oadb_service='<DB_TNS_NAME>'
      ℹ️ NOTE: Here each service will connect using the same conneciton for simplicity. It is expected for each service to support an independent instance and schema.
  • Next create (or edit) a myvalues.yaml file to specifiy connection information for the ATP-backed microservices:
    1. Traverse into the helm chart deployment directory:
      cd deploy/complete/helm-chart
    2. If not done already, download or copy an example values file into your workspace:
      (from download)
      mv ~/Downloads/myvalues.yaml ./myvalues.yaml
      - OR -
      (copy from sample)
      cp mushop/values-dev.yaml ./myvalues.yaml
    3. Enter helm chart configuation for each service:
      # Global service configurations
      global:
        #...
        # ATP Secrets
        oadbAdminSecret: oadb-admin           # Name of DB Admin secret created separately
        oadbWalletSecret: oadb-wallet         # Name of wallet secret created separately
        oadbConnectionSecret: oadb-connection # Name of connection secret created separately
        
      #...
      ℹ️ NOTE: Secrets for the remaining services may be omitted
  • With the values configured, the application is ready to deploy
    Remove a previous deployment (if applicable):
    helm del mushop
    Install from the deploy/complete/helm-cart directory:
    helm install mushop mushop \
      --set tags.streaming=false \
      -f myvalues.yaml
    ℹ️ If streaming has also been configured, omit the --set tags.streaming=false flag to deploy with all services:
    helm install mushop mushop \
      -f myvalues.yaml

    kubectl get pod --watch

    👀 You will notice several pods with an -init- component in their name. These are pods corresponding to Kubernetes Job definitions, where each is performing DB schema initializations.

    View the logs of an init pod like so (name will vary):
    kubectl logs mushop-carts-init-1-d475m
    ⏲️ It may take a few moments for all services to become ready
  • In this exercise, the use of helm presents a degree of opaque automation that we can de-mystify by reviewing the mechanics under the hood. Using the src/user service as an example:
    For each service connecting to ATP, a ConfigMap with SQL schema instructions along with an init Job are created:
    kubectl describe configmap mushop-user-init
    kubectl get job mushop-user-init-1 -o yaml
    apiVersion: batch/v1
    kind: Job
    metadata:
      name: mushop-user-init-1
    spec:
      # ...
      template:
        metadata:
          creationTimestamp: null
          labels:
            job-name: mushop-user-init-1
        spec:
          containers:
          - image: iad.ocir.io/oracle/ateam/mushop-user:1.1.0
            imagePullPolicy: IfNotPresent
            name: user
            # TypeORM table schema initializations
            command: ["npm", "run", "schema:sync"]
            env:
            # Schema user credentials
            - name: OADB_USER
              valueFrom:
                secretKeyRef:
                  key: oadb_user
                  name: user-oadb-credentials
            - name: OADB_PW
              valueFrom:
                secretKeyRef:
                  key: oadb_pw
                  name: user-oadb-credentials
            - name: OADB_SERVICE
              valueFrom:
                secretKeyRef:
                  key: oadb_service
                  name: oadb-connection
            volumeMounts:
            - mountPath: /usr/lib/oracle/19.3/client64/lib/network/admin/
              name: wallet
              readOnly: true
          # ...
          # Schema initialization with ADMIN user
          initContainers:
          - image: iad.ocir.io/oracle/ateam/mushop-dbtools:1.0.1
            imagePullPolicy: IfNotPresent
            name: init
            args:
            - sqlplus ADMIN/\"${OADB_ADMIN_PW}\"@${OADB_SERVICE} @service.sql ${OADB_USER^^}
              ${OADB_PW}
            command: ["/bin/sh", "-c"]
            env:
            # ... 
            # ADMIN user connection
            - name: OADB_ADMIN_PW
              valueFrom:
                secretKeyRef:
                  key: oadb_admin_pw
                  name: oadb-admin
            volumeMounts:
            # mounted 'wallet' volume to contain the wallet secret contents
            - mountPath: /usr/lib/oracle/19.3/client64/lib/network/admin/
              name: wallet
              readOnly: true
          # ...
          volumes:
          # define the 'wallet' volume from the wallet secret files
          - name: wallet
            secret:
              defaultMode: 256
              secretName: oadb-wallet
          # mounted configmap as SQL file
          - configMap:
              defaultMode: 420
              items:
              - key: atp.init.sql
                path: service.sql
              name: mushop-user-init
            name: initdb
    Example Deployment of user service:
    kubectl get deployment mushop-user
    apiVersion: apps/v1
    kind: Deployment
    metadata:
      name: mushop-user
      labels:
        name: mushop-user
    spec:
      template:
        metadata:
          labels:
            name: mushop-user
        spec:
          containers:
          - name: mushop-user
            image: iad.ocir.io/oracle/ateam/mushop-user:latest
            ports:
            - name: http
              containerPort: 3000
            # ...
            env:
            # Unique schema credentials
            - name: OADB_USER
              valueFrom:
                secretKeyRef:
                  name: user-oadb-credentials
                  key: oadb_user
            - name: OADB_PW
              valueFrom:
                secretKeyRef:
                  name: user-oadb-credentials
                  key: oadb_pw
            # use specified connection secret
            - name: OADB_SERVICE
              valueFrom:
                secretKeyRef:
                  name: oadb-connection
                  key: oadb_service
            # ...
            volumeMounts:
            # mount information for the the 'wallet' volume definition
            - name: wallet
              mountPath: /usr/lib/oracle/19.3/client64/lib/network/admin
              readOnly: true
          volumes:
          # define the 'wallet' volume from the wallet secret files
          - name: wallet
            secret:
              secretName: oadb-wallet
              defaultMode: 256
    Note that the service runtime is connecting with the oadb-wallet and oadb-connection secrets configured separatley.

ATP Schemas

Each service manages its own schema in the database, with specific grants by the ADMIN

SELECT
  table_name, owner
FROM
  all_tables
WHERE
  owner IN (
    'CARTS_USER',
    'CATALOGUE_USER',
    'ORDERS_USER',
    'USER_USER')
ORDER BY
  owner, table_name
+---------------------+----------------+
| TABLE_NAME          | OWNER          |
+---------------------+----------------+
  CART                  CARTS_USER
  CATEGORIES            CATALOGUE_USER
  PRODUCTS              CATALOGUE_USER
  PRODUCT_CATEGORY      CATALOGUE_USER
  ADDRESS               ORDERS_USER
  CARD                  ORDERS_USER
  CART                  ORDERS_USER
  CART_ITEMS            ORDERS_USER
  CUSTOMER              ORDERS_USER
  CUSTOMER_ADDRESSES    ORDERS_USER
  CUSTOMER_CARDS        ORDERS_USER
  CUSTOMER_ORDER        ORDERS_USER
  CUSTOMER_ORDER_ITEMS  ORDERS_USER
  ITEM                  ORDERS_USER
  SHIPMENT              ORDERS_USER
  user                  USER_USER
  user_address          USER_USER
  user_card             USER_USER
+---------------------+----------------+
Version: 1.8.0
Build: 2022-02-17T05:02:17Z
© 2022, Oracle and/or its affiliates. All rights reserved.