以下の内容はhttps://htn20190109.hatenablog.com/entry/2024/08/29/161828より取得しました。

{OCI データベース} Base Database Service(バックアップ/リストア)

OCI

https://oracle-japan.github.io/ocitutorials/basedb/dbcs105-restore/
https://www.oracle.com/jp/database/base-database-service/pricing/
https://docs.oracle.com/ja-jp/iaas/dbcs/doc/backup-and-recovery-overview.html
https://docs.oracle.com/en-us/iaas/tools/oci-cli/3.46.0/oci_cli_docs/cmdref/db/backup.html

 

STANDARD_EDITION
19.21.0.0
VM.Standard2.1
LVM

 

-- 1. terraformによるVPC構築


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 = "= 5.23.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 = 1521
            min = 1521
        }
    }
}

 

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

 


terraform init
terraform fmt
terraform -version

# export TF_VAR_compartment_name=cmp01


terraform plan

 

terraform apply -auto-approve


# terraform destroy -auto-approve

 

 

 

-- 2. Oracleベース・データベース作成

oci db version list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 

oci db system-shape list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111  \
--query 'sort_by(data, &"name").{"name":"name","shape":"shape","available-core-count":"available-core-count"}' \
--output table

 

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


oci db system launch \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111  \
--admin-password 'passwordpassword' \
--availability-domain OEIw:US-ASHBURN-AD-3 \
--cpu-core-count 1 \
--database-edition STANDARD_EDITION \
--db-name db02 \
--db-version 19.21.0.0 \
--hostname orcl02 \
--shape VM.Standard2.1 \
--ssh-authorized-keys-file "$HOME/.ssh/id_rsa.pub" \
--subnet-id ocid1.subnet.oc1.iad.111111111111111111111111111111111111111111111111111111111111 \
--auto-backup-enabled false \
--character-set AL32UTF8 \
--cluster-name cluster02 \
--db-unique-name dbu02 \
--db-workload OLTP \
--disk-redundancy NORMAL \
--display-name dbs02 \
--domain subnet01.vcn01.oraclevcn.com \
--initial-data-storage-size-in-gb 256 \
--is-diagnostics-events-enabled false \
--is-health-monitoring-enabled false \
--is-incident-logs-enabled false \
--license-model LICENSE_INCLUDED \
--ncharacter-set AL16UTF16 \
--node-count 1 \
--pdb-name dbu02pdb01 \
--private-ip 10.0.1.102 \
--storage-management LVM \
--storage-performance BALANCED \
--time-zone "Asia/Tokyo" 

 


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

 

oci db node list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 \
--db-system-id ocid1.dbsystem.oc1.iad.111111111111111111111111111111111111111111111111111111111111 


oci db db-home list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 \
--db-system-id ocid1.dbsystem.oc1.iad.111111111111111111111111111111111111111111111111111111111111 

oci db database list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 \
--db-system-id ocid1.dbsystem.oc1.iad.111111111111111111111111111111111111111111111111111111111111 


while true ; do
oci db system list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 \
--query 'data.{"display-name":"display-name","id":"id","lifecycle-state":"lifecycle-state"}' \
--output table ; date; sleep 60
done

 

oci db system terminate \
--db-system-id ocid1.dbsystem.oc1.iad.111111111111111111111111111111111111111111111111111111111111 \
--force 

 

 

 

-- 3. DB接続

ノードのパブリックIPを確認

 


ssh -i $HOME/.ssh/id_rsa opc@192.0.2.1

 

sudo su -

dbcli describe-system
dbcli list-databases

su - oracle
sqlplus / as sysdba

show pdbs
alter session set container = DBU02PDB01 ;

create user TESTUSER identified by passwordpassword ;
grant CREATE SESSION,CONNECT,RESOURCE,UNLIMITED TABLESPACE to TESTUSER ;
exit


lsnrctl status

sqlplus testuser/passwordpassword@10.0.1.102:1521/DBU02PDB01.subnet01.vcn01.oraclevcn.com

sho user
create table tab1(col1 int);
insert into tab1 values(1);
commit;
select * from tab1;

exit


-- 4. バックアップ

 

oci db backup list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 


oci db backup create \
--database-id ocid1.database.oc1.iad.111111111111111111111111111111111111111111111111111111111111 \
--display-name dbu02backup01 


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


while true ; do
oci db backup list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 \
--query 'data.{"display-name":"display-name","id":"id","lifecycle-state":"lifecycle-state"}' \
--output table; date; sleep 60
done


30分程度でバックアップ取得完了

oci db backup delete \
--backup-id ocid1.dbbackup.oc1.iad.111111111111111111111111111111111111111111111111111111111111 \
--force 


-- 5. リストア(同一DB)

sqlplus testuser/passwordpassword@10.0.1.102:1521/DBU02PDB01.subnet01.vcn01.oraclevcn.com

sho user
insert into tab1 values(2);
commit;
select * from tab1;

exit

 


oci db database list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 \
--db-system-id ocid1.dbsystem.oc1.iad.111111111111111111111111111111111111111111111111111111111111 


oci db database restore \
--database-id ocid1.database.oc1.iad.111111111111111111111111111111111111111111111111111111111111 \
--timestamp 2024-08-29T05:41:16Z


oci db database list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 \
--db-system-id ocid1.dbsystem.oc1.iad.111111111111111111111111111111111111111111111111111111111111 


while true ; do
oci db database list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 \
--db-system-id ocid1.dbsystem.oc1.iad.111111111111111111111111111111111111111111111111111111111111 \
--query 'data.{"db-name":"db-name","id":"id","lifecycle-state":"lifecycle-state"}' \
--output table; date; sleep 60
done


10分程度でリカバリ終了

 

 

sqlplus testuser/passwordpassword@10.0.1.102:1521/DBU02PDB01.subnet01.vcn01.oraclevcn.com

sho user
select * from tab1;

exit

 

 

 

-- 6. リストア(別DBシステム)

※同一DBシステムの別DBへのリストアは下記エラーとなる
CreateDbHome is not allowed for dbSystems with VMDB shapes

 

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


oci db system launch-from-backup \
--admin-password 'passwordpassword' \
--backup-tde-password 'passwordpassword' \
--availability-domain OEIw:US-ASHBURN-AD-3 \
--backup-id ocid1.dbbackup.oc1.iad.111111111111111111111111111111111111111111111111111111111111 \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111  \
--cpu-core-count 1 \
--database-edition STANDARD_EDITION \
--hostname orcl03 \
--shape VM.Standard2.1 \
--ssh-authorized-keys-file "$HOME/.ssh/id_rsa.pub" \
--subnet-id ocid1.subnet.oc1.iad.111111111111111111111111111111111111111111111111111111111111 \
--db-name db03 \
--db-unique-name dbu03 \
--disk-redundancy NORMAL \
--display-name dbs03 \
--domain subnet01.vcn01.oraclevcn.com \
--initial-data-storage-size-in-gb 256 \
--is-diagnostics-events-enabled false \
--is-health-monitoring-enabled false \
--is-incident-logs-enabled false \
--license-model LICENSE_INCLUDED \
--node-count 1 \
--private-ip 10.0.1.103 \
--storage-management LVM \
--storage-performance BALANCED \
--time-zone "Asia/Tokyo" 

 

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

 

oci db node list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 \
--db-system-id ocid1.dbsystem.oc1.iad.111111111111111111111111111111111111111111111111111111111111 


oci db db-home list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 \
--db-system-id ocid1.dbsystem.oc1.iad.111111111111111111111111111111111111111111111111111111111111 

oci db database list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 \
--db-system-id ocid1.dbsystem.oc1.iad.111111111111111111111111111111111111111111111111111111111111 


while true ; do
oci db system list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 \
--query 'data.{"display-name":"display-name","id":"id","lifecycle-state":"lifecycle-state"}' \
--output table ; date; sleep 60
done

 

oci db system terminate \
--db-system-id ocid1.dbsystem.oc1.iad.111111111111111111111111111111111111111111111111111111111111 \
--force 

 


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


sudo su -

dbcli describe-system
dbcli list-databases

su - oracle
sqlplus / as sysdba

show pdbs
alter session set container = DBU02PDB01 ;
select * from TESTUSER.tab1;

exit

 

 

 

 

 



以上の内容はhttps://htn20190109.hatenablog.com/entry/2024/08/29/161828より取得しました。
このページはhttp://font.textar.tv/のウェブフォントを使用してます
不具合報告/要望等はこちらへお願いします。
モバイルやる夫Viewer Ver0.14