https://oracle-japan.github.io/ocitutorials/basedb/dbcs202-dbms-cloud/
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 'password' \
--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. バケット作成
oci os bucket list \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111
oci os bucket create \
--compartment-id ocid1.compartment.oc1..111111111111111111111111111111111111111111111111111111111111 \
--name bucket01 \
--auto-tiering Disabled \
--object-events-enabled false \
--public-access-type NoPublicAccess \
--storage-tier Standard \
--versioning Disabled
oci os object list \
--bucket-name bucket01
wget https://oracle-japan.github.io/ocitutorials/basedb/dbcs202-dbms-cloud/ocitutorials_sales.csv
oci os object put \
--bucket-name bucket01 \
--file ocitutorials_sales.csv
oci os object list \
--bucket-name bucket01
oci os bucket delete \
--name bucket01 \
--empty \
--force
-- 4. 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 USER01 identified by password ;
grant CREATE SESSION,CONNECT,RESOURCE,UNLIMITED TABLESPACE to USER01 ;
exit
lsnrctl status
sqlplus USER01/password@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
-- 5. 保存先の作成と関連ファイルのダウンロード
su - oracle
mkdir -p /home/oracle/dbc
mkdir -p /home/oracle/cert
mkdir -p /opt/oracle/dcs/commonstore/wallets/ssl
cd /home/oracle/dbc
wget https://oracle-japan.github.io/ocitutorials/basedb/dbcs202-dbms-cloud/dbms_cloud_install.sql
wget https://oracle-japan.github.io/ocitutorials/basedb/dbcs202-dbms-cloud/dbc_aces.sql
wget https://oracle-japan.github.io/ocitutorials/basedb/dbcs202-dbms-cloud/verify_aces.sql
wget https://oracle-japan.github.io/ocitutorials/basedb/dbcs202-dbms-cloud/grant_user.sql
wget https://oracle-japan.github.io/ocitutorials/basedb/dbcs202-dbms-cloud/config_aces_for_user.sql
wget https://oracle-japan.github.io/ocitutorials/basedb/dbcs202-dbms-cloud/validate_user_config.sql
cd /home/oracle/cert
wget https://objectstorage.us-phoenix-1.oraclecloud.com/p/QsLX1mx9A-vnjjohcC7TIK6aTDFXVKr0Uogc2DAN-Rd7j6AagsmMaQ3D3Ti4a9yU/n/adwcdemo/b/CERTS/o/dbc_certs.tar
OCIユーザ確認と認証トークンの作成
-- 6. DBMS_CLOUD PL/SQLパッケージのインストール
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys/password --force_pdb_mode 'READ WRITE' -b dbms_cloud_install -d /home/oracle/dbc -l /home/oracle/dbc dbms_cloud_install.sql
cd /home/oracle/dbc
ls -l
grep -i error dbms*.log dbms*.lst
sqlplus / as sysdba
show con_name;
set lines 1000
set pages 5000
select con_id, owner, object_name, status, sharing, oracle_maintained
from cdb_objects
where object_name = 'DBMS_CLOUD'
order by con_id;
alter session set container=dbu02pdb01;
show con_name;
select con_id, owner, object_name, status, sharing, oracle_maintained
from cdb_objects
where object_name = 'DBMS_CLOUD'
order by con_id;
exit
-- 7. Walletの作成
cd /home/oracle/cert
ll
tar xvf dbc_certs.tar
ll
cd /opt/oracle/dcs/commonstore/wallets/ssl
orapki wallet create -wallet . -pwd password -auto_login
for i in /home/oracle/cert/*.cer
do
orapki wallet add -wallet . -trusted_cert -cert "$i" -pwd password
done
orapki wallet display -wallet .
-- 8. Walletの場所の設定
cd /u01/app/oracle/product/19.0.0/dbhome_1/network/admin
vi sqlnet.ora
下記を追記
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/dcs/commonstore/wallets/ssl)))
-- 9. ACEs(Access Control Entries)の作成
cd /home/oracle/dbc
sed -i 's#<Set SSL Wallet Directory>#/opt/oracle/dcs/commonstore/wallets/ssl#' dbc_aces.sql
cat dbc_aces.sql
sqlplus / as sysdba
show con_name
@dbc_aces.sql
Proxyを使わないは空欄のままEnter
select * from database_properties where property_name in ('SSL_WALLET','HTTP_PROXY');
exit
-- 10. DBMS_CLOUDの設定を検証
cd /home/oracle/dbc
sed -i 's#<Set SSL Wallet Directory>#/opt/oracle/dcs/commonstore/wallets/ssl#; s/<Set SSL Wallet password>/password/; s#eu-frankfurt-1#us-ashburn-1#' verify_aces.sql
cat verify_aces.sql
sqlplus / as sysdba
show con_name
@verify_aces.sql
exit
-- 11. ユーザへの権限付与
cd /home/oracle/dbc
sed -i 's/SCOTT/USER01/' grant_user.sql
cat grant_user.sql
sqlplus / as sysdba
alter session set container=dbu02pdb01;
show con_name
@grant_user.sql
exit
-- 12. ユーザのためのACEsを設定
cd /home/oracle/dbc
sed -i 's/SCOTT/USER01/; s#<Set SSL Wallet Directory>#/opt/oracle/dcs/commonstore/wallets/ssl#' config_aces_for_user.sql
cat config_aces_for_user.sql
sqlplus / as sysdba
alter session set container = dbu02pdb01;
show con_name
@config_aces_for_user.sql
exit
-- 13. クレデンシャルの作成と検証
sqlplus USER01/password@10.0.1.102:1521/DBU02PDB01.subnet01.vcn01.oraclevcn.com
sho user
show con_name
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'CRED01',
username => 'oracleidentitycloudservice/hoge@example.com',
password => 'xxxxxxxxxxxxxxxxxxxx'
);
END;
/
URLは「オブジェクト・ストレージ」→ 「バケットの詳細」→ 「オブジェクトの詳細」から確認できます。
set lines 1000
set pages 5000
select * from dbms_cloud.list_objects('CRED01','https://objectstorage.us-ashburn-1.oraclecloud.com/n/111111111111/b/bucket01/o/');
exit
cd /home/oracle/dbc
sed -i 's/SCOTT/USER01/; s#<Set SSL Wallet Directory>#/opt/oracle/dcs/commonstore/wallets/ssl#; s/<Set SSL Wallet password>/password/; s#eu-frankfurt-1#us-ashburn-1#' validate_user_config.sql
cat validate_user_config.sql
sqlplus USER01/password@10.0.1.102:1521/DBU02PDB01.subnet01.vcn01.oraclevcn.com
sho user
show con_name
@validate_user_config.sql
-- 14. 外部表を作成し、オブジェクト・ストレージのファイルを参照する
begin DBMS_CLOUD.CREATE_EXTERNAL_TABLE(table_name=>'TUTORIAL_SALES',
credential_name => 'CRED01',
file_uri_list => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/111111111111/b/bucket01/o/ocitutorials_sales.csv',
format => json_object('delimiter' value ',' ),
column_list =>'channel_short varchar2(1),
channel_long varchar2(20),
channel_class varchar2(20)');
end;
/
ALTER TABLE TUTORIAL_SALES REJECT LIMIT UNLIMITED;
SELECT * FROM TUTORIAL_SALES;
