{OCI データベース} DB起動停止自動化

 

-- 1. VCN、コンピュートインスタンス作成

cat <<-'EOF' > variables.tf

locals {
  tenancy_ocid = "ocid1.tenancy.oc1..111111111111111111111111111111111111111111111111111111111111"

}

variable "compartment_name" {
  description = "compartment_name"
  type = string
  default = "cmp01"
}

EOF

 

cat <<-'EOF' > main.tf

terraform {
  required_version = ">= 1.0.0, < 2.0.0"
  required_providers {
    oci = {
       source  = "hashicorp/oci"
       version = "= 6.21.0"
    }
  }
}

provider "oci" {
  tenancy_ocid = local.tenancy_ocid
  user_ocid = "ocid1.user.oc1..111111111111111111111111111111111111111111111111111111111111" 
  private_key_path = "~/.oci/oci_api_key.pem"
  fingerprint = "45:ed:22:e6:cc:fd:63:97:12:9d:62:7a:90:12:65:7a"
  region = "us-ashburn-1"
}

resource "oci_identity_compartment" "cmp01" {
    # Required
    compartment_id = local.tenancy_ocid
    description = var.compartment_name
    name = var.compartment_name
    
    enable_delete = true
}

resource "oci_core_vcn" "vcn01" {
    #Required
    compartment_id = oci_identity_compartment.cmp01.id

    #Optional
    cidr_block = "10.0.0.0/16"
    display_name = "vcn01"
    dns_label = "vcn01"

}

resource "oci_core_internet_gateway" "igw01" {
    #Required
    compartment_id = oci_identity_compartment.cmp01.id
    vcn_id = oci_core_vcn.vcn01.id

    #Optional
    enabled = true
    display_name = "igw01"
}

resource "oci_core_route_table" "rt01" {
    #Required
    compartment_id = oci_identity_compartment.cmp01.id
    vcn_id = oci_core_vcn.vcn01.id

    #Optional
    display_name = "rt01"
    route_rules {
        #Required
        network_entity_id = oci_core_internet_gateway.igw01.id
        #Optional
        destination = "0.0.0.0/0"
    }
    
}

resource "oci_core_security_list" "sl01" {
    #Required
    compartment_id = oci_identity_compartment.cmp01.id
    vcn_id = oci_core_vcn.vcn01.id

    #Optional
    display_name = "sl01"
    
    egress_security_rules {
        destination = "0.0.0.0/0"
        protocol = "all"
        stateless = false
    }
    
    ingress_security_rules {
        protocol = "6"
        source = "0.0.0.0/0"
        stateless = false
        tcp_options {
            max = 22
            min = 22
        }
    }
    ingress_security_rules {
        protocol = "6"
        source = "0.0.0.0/0"
        stateless = false
        tcp_options {
            max = 1522
            min = 1522
        }
    }
    ingress_security_rules {
        protocol = "6"
        source = "0.0.0.0/0"
        stateless = false
        tcp_options {
            max = 80
            min = 80
        }
    }
}

 

resource "oci_core_subnet" "subnet01" {
    #Required
    cidr_block = "10.0.1.0/24"
    compartment_id = oci_identity_compartment.cmp01.id
    vcn_id = oci_core_vcn.vcn01.id

    #Optional

    display_name = "subnet01"
    dns_label = "subnet01"
    route_table_id = oci_core_route_table.rt01.id
    security_list_ids = [oci_core_security_list.sl01.id]
}

resource "oci_core_subnet" "subnet02" {
    #Required
    cidr_block = "10.0.2.0/24"
    compartment_id = oci_identity_compartment.cmp01.id
    vcn_id = oci_core_vcn.vcn01.id

    #Optional

    display_name = "subnet02"
    dns_label = "subnet02"
    route_table_id = oci_core_route_table.rt01.id
    security_list_ids = [oci_core_security_list.sl01.id]
}

resource "oci_core_nat_gateway" "ngw01" {
    #Required
    compartment_id = oci_identity_compartment.cmp01.id
    vcn_id = oci_core_vcn.vcn01.id

    #Optional
    block_traffic = false
    display_name = "ngw01"
}

data "oci_core_services" "svc01" {
  filter {
    name   = "name"
    values = ["All .* Services In Oracle Services Network"]
    regex  = true
  }
}

resource "oci_core_service_gateway" "sgw01" {
    #Required
    compartment_id = oci_identity_compartment.cmp01.id
    services {
        #Required
        service_id = data.oci_core_services.svc01.services.0.id
    }
    vcn_id = oci_core_vcn.vcn01.id

    #Optional
    display_name = "sgw01"
}

EOF

 

cat <<-'EOF' > outputs.tf

output "cmp01_id" {
  value = oci_identity_compartment.cmp01.id
  description = "cmp01.id"
}

output "vcn01_id" {
  value = oci_core_vcn.vcn01.id
  description = "vcn01.id"
}

output "igw01_id" {
  value = oci_core_internet_gateway.igw01.id
  description = "igw01.id"
}
output "rt01_id" {
  value = oci_core_route_table.rt01.id
  description = "rt01.id"
}

output "sl01_id" {
  value = oci_core_security_list.sl01.id
  description = "sl01.id"
}

output "subnet01_id" {
  value = oci_core_subnet.subnet01.id
  description = "subnet01.id"
}
output "subnet02_id" {
  value = oci_core_subnet.subnet02.id
  description = "subnet02.id"
}

output "ngw01_id" {
  value = oci_core_nat_gateway.ngw01.id
  description = "ngw01.id"
}

output "svc01_id" {
  value = data.oci_core_services.svc01.services.0.id
  description = "svc01.id"
}

output "sgw01_id" {
  value = oci_core_service_gateway.sgw01.id
  description = "sgw01.id"
}

EOF

 

# インスタンス(always free)
# Canonical-Ubuntu-22.04-aarch64-2023.10.13-0
# VM.Standard.A1.Flex

cat <<-'EOF' > instance.tf

resource "oci_core_instance" "vm11" {
    #Required
    availability_domain = "OEIw:US-ASHBURN-AD-1"
    compartment_id = oci_identity_compartment.cmp01.id
    shape = "VM.Standard.A1.Flex"

    shape_config {

        memory_in_gbs = 6
        ocpus = 1
    }
    
    #Optional

    create_vnic_details {
        #Optional
        assign_public_ip = true
        subnet_id = oci_core_subnet.subnet01.id
    }

    display_name = "vm11"

    metadata = {
        ssh_authorized_keys = file("~/.ssh/id_rsa.pub")
    } 

    source_details {
        #Required
        source_id = "ocid1.image.oc1.iad.aaaaaaaamphrdqdgcjfdmo5fzql4m6ewcuxkbepjbobgky254svsk3ueppfa"
        source_type = "image"

        #Optional
        boot_volume_size_in_gbs = 50
    }
    preserve_boot_volume = false
}

EOF

 

 

terraform init
terraform fmt
terraform -version

export TF_VAR_compartment_name=cmp01

terraform plan

 

terraform apply -auto-approve

 

# terraform destroy -auto-approve

-- 2. ubuntu22にociコマンドインストール
-- コンピュートインスタンスでの作業

ssh -i $HOME/.ssh/id_rsa ubuntu@192.0.2.2

 

bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)"
exec -l $SHELL
oci -v

 

 

-- 3. 動的グループ作成

 

oci iam dynamic-group list 

oci iam dynamic-group create \
--description dg01 \
--matching-rule "Any {instance.compartment.id = 'ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111'}" \
--name dg01 

 

oci iam dynamic-group delete \
--dynamic-group-id ocid1.dynamicgroup.oc1..111111111111111111111111111111111111111111111111111111111111 \
--force 

 

-- 4. 動的グループポリシー作成

oci iam policy list \
--compartment-id ocid1.tenancy.oc1..111111111111111111111111111111111111111111111111111111111111 

oci iam policy create \
--compartment-id ocid1.tenancy.oc1..111111111111111111111111111111111111111111111111111111111111 \
--description policy11 \
--name policy11 \
--statements '[
"Allow dynamic-group dg01 to manage all-resources in tenancy"
]' 

 

oci iam policy delete \
--policy-id ocid1.policy.oc1..111111111111111111111111111111111111111111111111111111111111 \
--force 

-- 5. 動作確認(CLIに対するインスタンス・プリンシパル認可の有効化後)
-- コンピュートインスタンスでの作業

 

oci compute instance list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 

oci compute instance list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 \
--auth instance_principal 

 

export OCI_CLI_AUTH=instance_principal

 

oci compute instance list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 

 

-- 6. データベースの作成

oci db autonomous-database list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 

oci db autonomous-database list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 \
--display-name adb11 

 

oci db autonomous-database create \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 \
--admin-password 'passwordpassword' \
--db-name adb11 \
--db-version 19c \
--db-workload OLTP \
--display-name adb11 \
--is-auto-scaling-enabled FALSE \
--is-auto-scaling-for-storage-enabled FALSE \
--is-local-data-guard-enabled FALSE \
--is-mtls-connection-required FALSE \
--whitelisted-ips '[
"192.0.2.1"
]' \
--is-free-tier TRUE

 

 

oci db autonomous-database list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 \
--query 'data[?"db-name"==`'adb11'`]."id" | [0]' \
--raw-output

 

oci db autonomous-database delete \
--autonomous-database-id ocid1.autonomousdatabase.oc1.iad.111111111111111111111111111111111111111111111111111111111111 \
--force

 

-- 7. 動作確認(CLIに対するインスタンス・プリンシパル認可の有効化後)
-- コンピュートインスタンスでの作業

export OCI_CLI_AUTH=instance_principal

oci db autonomous-database stop \
--autonomous-database-id ocid1.autonomousdatabase.oc1.iad.111111111111111111111111111111111111111111111111111111111111 

oci db autonomous-database start \
--autonomous-database-id ocid1.autonomousdatabase.oc1.iad.111111111111111111111111111111111111111111111111111111111111 

oci db autonomous-database list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 \
--query 'data[].{"db-name":"db-name","id":"id","lifecycle-state":"lifecycle-state"}' \
--output table

 

-- 8. shell作成
-- コンピュートインスタンスでの作業

cat <<-'EOF' > /home/ubuntu/stopDB.sh
#!/bin/bash
export OCI_CLI_AUTH=instance_principal

/home/ubuntu/bin/oci db autonomous-database stop \
--autonomous-database-id ocid1.autonomousdatabase.oc1.iad.111111111111111111111111111111111111111111111111111111111111 

exit 0

EOF

cat <<-'EOF' > /home/ubuntu/startDB.sh
#!/bin/bash
export OCI_CLI_AUTH=instance_principal

/home/ubuntu/bin/oci db autonomous-database start \
--autonomous-database-id ocid1.autonomousdatabase.oc1.iad.111111111111111111111111111111111111111111111111111111111111 

exit 0

EOF

ls -l
chmod +x /home/ubuntu/stopDB.sh
chmod +x /home/ubuntu/startDB.sh

cat /home/ubuntu/stopDB.sh
cat /home/ubuntu/startDB.sh

 

crontab -l

crontab -e

52 10 * * * /home/ubuntu/stopDB.sh
54 10 * * * /home/ubuntu/startDB.sh