{OCI データベース} Base Database Service(オンプレミスPDBの移行)

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 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. 移行元のデータベース(19c)からPDBをアンプラグする

alter session set container=pdb1;
select tablespace_name, file_name from dba_data_files;

alter pluggable database pdb1 close;

conn / as sysdba

alter pluggable database pdb1 unplug into '/tmp/pdb1.xml';

-- 4. 対象PDBで使用しているデータファイル、及びアンプラグ時に生成されたxmlファイルをBaseDB上のディレクトリにコピー

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

ssh -i $HOME/.ssh/id_rsa opc@192.0.2.1
TMOUT=0

sudo su -
TMOUT=0

mkdir -p -m 777 /u01/test
ls -ld /u01/test

-- データファイルをBaseDBサーバへ転送
scp -i $HOME/.ssh/id_rsa /tmp/pdb1.xml opc@192.0.2.1:/u01/test
scp -i $HOME/.ssh/id_rsa /tmp/*.dbf opc@192.0.2.1:/u01/test

ls -l /u01/test
chown oracle: /u01/test/*
chmod 640 /u01/test/*

-- 5. BaseDBにPDBをプラグする

アンプラグ時に生成されたxmlファイルを開き、データファイルのパスをBaseDB上のパスに書き換えます

su - oracle
TMOUT=0

vi /u01/test/pdb1.xml

/u01/app/oracle/oradata/orcl/pdb1/
↓
/u01/test/

※ xmlファイルには一時ファイルも含まれているが、一時ファイルは転送していないが、同様に書き換え実施

sqlplus / as sysdba

create pluggable database pdb1 using '/u01/test/pdb1.xml'
file_name_convert = ('/u01/test/','/u02/app/oracle/oradata/dbu02/DBU02/pdb1/');

alter pluggable database pdb1 open;

set line 1000
set pages 5000
select * from pdb_plug_in_violations ;

select * from pdb_plug_in_violations where type = 'ERROR';

alter session set container=pdb1;

select count(*) from test.tab1;
select * from test.tab1;

エラーがある場合、「RESTRICTED」モードでの起動となる

PDB1について、下記エラーが2件ある
Call datapatch to install in the PDB or the CDB

参考サイトに従い、下記コマンドを実行

/u01/app/oracle/product/19.0.0/dbhome_1/OPatch/datapatch

コマンド実行後、再確認

sqlplus / as sysdba

set line 1000
set pages 5000
select * from pdb_plug_in_violations ;

select * from pdb_plug_in_violations where type = 'ERROR';

show pdbs;

alter pluggable database pdb1 close;
alter pluggable database pdb1 open;

show pdbs

select * from pdb_plug_in_violations where type = 'ERROR';

※PDB再起動により STATUSが「PENDING」→「RESOLVED」に変化。「RESTRICTED」はNOになった。

-- 6. 表領域の暗号化を行う

alter session set container=pdb1;
administer key management set key force keystore identified by "passwordpassword" with backup;

alter session set container=pdb1;

select * from dba_tablespaces;

alter tablespace SYSTEM encryption online encrypt;
alter tablespace SYSAUX encryption online encrypt;
alter tablespace USERS encryption online encrypt;
alter tablespace USERTBS encryption online encrypt;
alter tablespace TBS1 encryption online encrypt;

-- alter tablespace TEMP encryption online encrypt;
alter tablespace UNDO_1 encryption online encrypt;

select * from dba_tablespaces;

select * from pdb_plug_in_violations ;
select * from pdb_plug_in_violations where type = 'ERROR';

-- 7. アンプラグした移行元PDBの復旧

show pdbs

create pluggable database pdb11 as clone using '/tmp/pdb1.xml'
file_name_convert = ('/u01/app/oracle/oradata/orcl/pdb1/','/u01/app/oracle/oradata/orcl/pdb11/');

show pdbs
drop pluggable database pdb1 INCLUDING DATAFILES;
show pdbs

alter pluggable database pdb11 open restricted;
alter session set container=pdb11;
alter pluggable database rename global_name to pdb1;

alter session set container=cdb$root;

alter pluggable database pdb1 close;
alter pluggable database pdb1 open;

show pdbs