今日はクリスマス、世界に愛と平和を贈ります。
Oracle Cloud Infrastructure (OCI) は 現在 40以上のリージョンがあります。
世界中に配置した Oracle Database を Data Guard (DG)で直列に繋ぎ世界一周して届けます。
■ 構成イメージ
東京リージョンは TOKYO と SHIBUYA を Loacal Data Guard として作成し、TOKYOを始点、SHIBUYAを終点となるように構成して、TOKYO → PHOENIX → ASHBURN → FRANKFURT → MUMBAI → SEOUL → OSAKA → SHIBUYA(Tokyo Region) へ世界一周するよう直列に REDO転送するように Data Guardを構成します。
Oracle Cloud Infrastructure(OCI)の Dynamic Routing Gateway（DRG）は Oracleバックボーンを使用して Global Network を構成することができます。
これにより、オンプレミス環境から1つの OCIリージョンに接続して、世界中のOCIリージョンに接続できます。
・リージョン間を Remote VCN Peering してみてみた
■ Data Guard 設定手順
● Data Guard設定
Data Guardの作成は次の手順で作成します。2台、4台、8台の構成も手順は変わりません。
1つ1つ心を込めて Standby Databaseを追加してData Guard Brokerで登録して制御します。
・Oracle Exadata 手順: 手動コマンドで Active Data Guard を構成してみてみた
・Oracle Database 手順: Data Guard 複数スタンバイ構成してみてみた
・Data Guard REDO転送制御手順: Data Guard Broker で複数スタンバイ・データベース構成の REDO転送を制御してみてみた
■ 直列カスケード設定
Data Guard Broker の RedoRoutes プロパティを使用して、リアルタイム・カスケードを構成することができます。
TOKYOデータベースでは、TOKYOがプライマリ・ロールである場合、同期転送モードを使用してPHOENIXデータベースにREDOを送信するように、RedoRoutesプロパティを指定する必要があります。
このルールにより、プライマリのTOKYOデータベース が PHOENIX以外のデータベースに直接REDOデータを送信することがなくなります。
PHOENIXデータベースでは、TOKYOがプライマリ・ロールの場合、PHOENIXが、TOKYOから受け取ったREDOをAHBURNに転送するように、RedoRoutesプロパティを指定し、他データベースも同様に直列転送するように設定します。
そのためには、RedoRoutesプロパティを次のように設定します。
● 直列カスケード設定
1) dgmgrl接続
[oracle@db-tokyo1 ~]$ dgmgrl sys/<Password>@$ORACLE_UNQNAME
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Dec 24 10:22:42 2022
Version 19.17.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "CDB_TOKYO"
Connected as SYSDBA.
DGMGRL>
2) RedoRoutes確認
デフォルトは、プライマリ・データベースから全部のスタンバイ・データベースへRedo同期がされます。
現在、Tokyoから世界各地へ Redo同期がされています。
Tokyoから地球の裏まで地球を覆うように伝播されています。
花火のように
なんてロマンチック
DGMGRL> show configuration
Configuration - dg_config
Protection Mode: MaxPerformance
Members:
CDB_TOKYO - Primary database
CDB_OSAKA - Physical standby database
CDB_SHIBUYA - Physical standby database
CDB_MUMBAI - Physical standby database
CDB_PHOENIX - Physical standby database
CDB_ASHBURN - Physical standby database
CDB_FRANKFURT - Physical standby database
CDB_SEOUL - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 57 seconds ago)
3) 直列カスケード設定
DGMGRL> EDIT DATABASE 'CDB_TOKYO' SET PROPERTY 'RedoRoutes' = '(LOCAL : CDB_PHOENIX ASYNC)';
Property "RedoRoutes" updated
DGMGRL> EDIT DATABASE 'CDB_PHOENIX' SET PROPERTY 'RedoRoutes' = '(CDB_TOKYO : CDB_ASHBURN ASYNC)';
Property "RedoRoutes" updated
DGMGRL> EDIT DATABASE 'CDB_ASHBURN' SET PROPERTY 'RedoRoutes' = '(CDB_TOKYO : CDB_FRANKFURT ASYNC)';
Property "RedoRoutes" updated
DGMGRL> EDIT DATABASE 'CDB_FRANKFURT' SET PROPERTY 'RedoRoutes' = '(CDB_TOKYO : CDB_MUMBAI ASYNC)';
Property "RedoRoutes" updated
DGMGRL> EDIT DATABASE 'CDB_MUMBAI' SET PROPERTY 'RedoRoutes' = '(CDB_TOKYO : CDB_SEOUL ASYNC)';
Property "RedoRoutes" updated
DGMGRL> EDIT DATABASE 'CDB_SEOUL' SET PROPERTY 'RedoRoutes' = '(CDB_TOKYO : CDB_OSAKA ASYNC)';
Property "RedoRoutes" updated
DGMGRL> EDIT DATABASE 'CDB_OSAKA' SET PROPERTY 'RedoRoutes' = '(CDB_TOKYO : CDB_SHIBUYA ASYNC)';
Property "RedoRoutes" updated
DGMGRL> EDIT DATABASE 'CDB_SHIBUYA' SET PROPERTY 'RedoRoutes' = '';
Property "RedoRoutes" updated
● 直列カスケード設定DGMGRL確認
1) 直列カスケード構成確認
SHOW CONFIGURATIONコマンドを使用して、CDB_TOKYO から CDB_SHIBUYAまでネストして直列に REDOを伝搬することを確認
DGMGRL> show configuration
Configuration - dg_config
Protection Mode: MaxPerformance
Members:
CDB_TOKYO - Primary database
CDB_PHOENIX - Physical standby database (receiving current redo)
CDB_ASHBURN - Physical standby database (receiving current redo)
CDB_FRANKFURT - Physical standby database (receiving current redo)
CDB_MUMBAI - Physical standby database (receiving current redo)
CDB_SEOUL - Physical standby database (receiving current redo)
CDB_OSAKA - Physical standby database (receiving current redo)
CDB_SHIBUYA - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 58 seconds ago)
2) Database RedoRoutes設定確認
各Databaseの RedoRoutes パラメータが設定されていることを確認
・ TOKYO 確認
DGMGRL> show database verbose CDB_TOKYO
Database - CDB_TOKYO
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
CDB1
CDB2
Properties:
DGConnectIdentifier = 'cdb_tokyo'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = '(LOCAL : CDB_PHOENIX ASYNC)'
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName(*)
StaticConnectIdentifier(*)
TopWaitEvents(*)
SidName(*)
(*) - Please check specific instance for the property value
Log file locations(*):
(*) - Check specific instance for log file locations.
Database Status:
SUCCESS
・ PHOENIX 確認
DGMGRL> show database verbose CDB_PHOENIX
Database - CDB_PHOENIX
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 7.00 KByte/s
Active Apply Rate: 1.07 MByte/s
Maximum Apply Rate: 3.74 MByte/s
Real Time Query: ON
Instance(s):
CDB1
CDB2 (apply instance)
Properties:
DGConnectIdentifier = 'cdb_phoenix'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = '(CDB_TOKYO : CDB_ASHBURN ASYNC)'
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName(*)
StaticConnectIdentifier(*)
TopWaitEvents(*)
SidName(*)
(*) - Please check specific instance for the property value
Log file locations(*):
(*) - Check specific instance for log file locations.
Database Status:
SUCCESS
・ ASHBURN 確認
DGMGRL> show database verbose CDB_ASHBURN
Database - CDB_ASHBURN
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 7.00 KByte/s
Active Apply Rate: 1.08 MByte/s
Maximum Apply Rate: 3.86 MByte/s
Real Time Query: ON
Instance(s):
CDB1
CDB2 (apply instance)
Properties:
DGConnectIdentifier = 'cdb_ashburn'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = '(CDB_TOKYO : CDB_FRANKFURT ASYNC)'
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName(*)
StaticConnectIdentifier(*)
TopWaitEvents(*)
SidName(*)
(*) - Please check specific instance for the property value
Log file locations(*):
(*) - Check specific instance for log file locations.
Database Status:
SUCCESS
・ FRANKFURT 確認
DGMGRL> show database verbose CDB_FRANKFURT
Database - CDB_FRANKFURT
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 7.00 KByte/s
Active Apply Rate: 1.06 MByte/s
Maximum Apply Rate: 3.92 MByte/s
Real Time Query: ON
Instance(s):
CDB1 (apply instance)
CDB2
Properties:
DGConnectIdentifier = 'cdb_frankfurt'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = '(CDB_TOKYO : CDB_MUMBAI ASYNC)'
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName(*)
StaticConnectIdentifier(*)
TopWaitEvents(*)
SidName(*)
(*) - Please check specific instance for the property value
Log file locations(*):
(*) - Check specific instance for log file locations.
Database Status:
SUCCESS
・ MUMBAI 確認
DGMGRL> show database verbose CDB_MUMBAI
Database - CDB_MUMBAI
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 7.00 KByte/s
Active Apply Rate: 1.05 MByte/s
Maximum Apply Rate: 3.59 MByte/s
Real Time Query: ON
Instance(s):
CDB1 (apply instance)
CDB2
Properties:
DGConnectIdentifier = 'cdb_mumbai'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = '(CDB_TOKYO : CDB_SEOUL ASYNC)'
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName(*)
StaticConnectIdentifier(*)
TopWaitEvents(*)
SidName(*)
(*) - Please check specific instance for the property value
Log file locations(*):
(*) - Check specific instance for log file locations.
Database Status:
SUCCESS
・ SEOUL 確認
DGMGRL> show database verbose CDB_SEOUL
Database - CDB_SEOUL
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 7.00 KByte/s
Active Apply Rate: 1.07 MByte/s
Maximum Apply Rate: 4.00 MByte/s
Real Time Query: ON
Instance(s):
CDB1 (apply instance)
CDB2
Properties:
DGConnectIdentifier = 'cdb_seoul'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = '(CDB_TOKYO : CDB_OSAKA ASYNC)'
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName(*)
StaticConnectIdentifier(*)
TopWaitEvents(*)
SidName(*)
(*) - Please check specific instance for the property value
Log file locations(*):
(*) - Check specific instance for log file locations.
Database Status:
SUCCESS
・ OSAKA 確認
DGMGRL> show database verbose CDB_OSAKA
Database - CDB_OSAKA
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 7.00 KByte/s
Active Apply Rate: 1.04 MByte/s
Maximum Apply Rate: 16.91 MByte/s
Real Time Query: ON
Instance(s):
CDB1 (apply instance)
CDB2
Properties:
DGConnectIdentifier = 'cdb_osaka'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = '(CDB_TOKYO : CDB_SHIBUYA ASYNC)'
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName(*)
StaticConnectIdentifier(*)
TopWaitEvents(*)
SidName(*)
(*) - Please check specific instance for the property value
Log file locations(*):
(*) - Check specific instance for log file locations.
Database Status:
SUCCESS
・ SHIBUYA 確認
DGMGRL> show database verbose CDB_SHIBUYA
Database - CDB_SHIBUYA
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 5 seconds (computed 0 seconds ago)
Apply Lag: 6 seconds (computed 0 seconds ago)
Average Apply Rate: 7.00 KByte/s
Active Apply Rate: 1.07 MByte/s
Maximum Apply Rate: 3.26 MByte/s
Real Time Query: ON
Instance(s):
CDB1
CDB2 (apply instance)
Properties:
DGConnectIdentifier = 'cdb_shibuya'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = '(LOCAL : CDB_OSAKA ASYNC)'
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName(*)
StaticConnectIdentifier(*)
TopWaitEvents(*)
SidName(*)
(*) - Please check specific instance for the property value
Log file locations(*):
(*) - Check specific instance for log file locations.
Database Status:
SUCCESS
● LOG_ARCHIVE パラメーター確認
Data Brokerによって、各データベースのLOG ARCHIVEに関するパラメータが設定されます。
1) LOG_ARCHIVE確認スクリプト作成
log_archive.txtスクリプト
>
[oracle@db-tokyo1 ~]$ cat log_archive.txt
#!/bin/bash
echo '### TOKYO ###'
sqlplus -s sys/<Password>@CDB_TOKYO as sysdba <<EOF
set lin 250
col NAME format a25
col VALUE format a200
select INST_ID, NAME, VALUE from gv\$parameter where NAME='log_archive_config' or NAME='log_archive_dest_2' order by NAME,INST_ID;
exit
EOF
echo '### PHOENIX ###'
sqlplus -s sys/<Password>@CDB_PHOENIX as sysdba <<EOF
set lin 250
col NAME format a25
col VALUE format a200
select INST_ID, NAME, VALUE from gv\$parameter where NAME='log_archive_config' or NAME='log_archive_dest_2' order by NAME,INST_ID;
exit
EOF
echo '### ASHBURN ###'
sqlplus -s sys/<Password>@CDB_ASHBURN as sysdba <<EOF
set lin 250
col NAME format a25
col VALUE format a200
select INST_ID, NAME, VALUE from gv\$parameter where NAME='log_archive_config' or NAME='log_archive_dest_2' order by NAME,INST_ID;
exit
EOF
echo '### FRANKFURT ###'
sqlplus -s sys/<Password>@CDB_FRANKFURT as sysdba <<EOF
set lin 250
col NAME format a25
col VALUE format a200
select INST_ID, NAME, VALUE from gv\$parameter where NAME='log_archive_config' or NAME='log_archive_dest_2' order by NAME,INST_ID;
exit
EOF
echo '### MUMBAI ###'
sqlplus -s sys/<Password>@CDB_MUMBAI as sysdba <<EOF
set lin 250
col NAME format a25
col VALUE format a200
select INST_ID, NAME, VALUE from gv\$parameter where NAME='log_archive_config' or NAME='log_archive_dest_2' order by NAME,INST_ID;
exit
EOF
echo '### SEOUL ###'
sqlplus -s sys/<Password>@CDB_SEOUL as sysdba <<EOF
set lin 250
col NAME format a25
col VALUE format a200
select INST_ID, NAME, VALUE from gv\$parameter where NAME='log_archive_config' or NAME='log_archive_dest_2' order by NAME,INST_ID;
exit
EOF
echo '### OSAKA ###'
sqlplus -s sys/<Password>@CDB_OSAKA as sysdba <<EOF
set lin 250
col NAME format a25
col VALUE format a200
select INST_ID, NAME, VALUE from gv\$parameter where NAME='log_archive_config' or NAME='log_archive_dest_2' order by NAME,INST_ID;
exit
EOF
echo '### SHIBUYA ###'
sqlplus -s sys/<Password>@CDB_SHIBUYA as sysdba <<EOF
set lin 250
col NAME format a25
col VALUE format a200
select INST_ID, NAME, VALUE from gv\$parameter where NAME='log_archive_config' or NAME='log_archive_dest_2' order by NAME,INST_ID;
exit
EOF
exit
2) LOG_ARCHIVE確認スクリプト実行と確認
[oracle@db-tokyo1 ~]$ bash log_archive.txt
### TOKYO ###
INST_ID NAME VALUE
---------- ------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 log_archive_config dg_config=(CDB_TOKYO,CDB_OSAKA,CDB_SHIBUYA,CDB_MUMBAI,CDB_PHOENIX,CDB_ASHBURN,CDB_FRANKFURT,CDB_SEOUL)
2 log_archive_config dg_config=(CDB_TOKYO,CDB_OSAKA,CDB_SHIBUYA,CDB_MUMBAI,CDB_PHOENIX,CDB_ASHBURN,CDB_FRANKFURT,CDB_SEOUL)
1 log_archive_dest_2 service="cdb_phoenix", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="CDB_PHOENIX" net_timeout=30, valid_for=(online_logfile,all_roles)
2 log_archive_dest_2 service="cdb_phoenix", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="CDB_PHOENIX" net_timeout=30, valid_for=(online_logfile,all_roles)
### PHOENIX ###
INST_ID NAME VALUE
---------- ------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 log_archive_config dg_config=(CDB_PHOENIX,CDB_TOKYO,CDB_SHIBUYA,CDB_OSAKA,CDB_MUMBAI,CDB_ASHBURN,CDB_FRANKFURT,CDB_SEOUL)
2 log_archive_config dg_config=(CDB_PHOENIX,CDB_TOKYO,CDB_SHIBUYA,CDB_OSAKA,CDB_MUMBAI,CDB_ASHBURN,CDB_FRANKFURT,CDB_SEOUL)
1 log_archive_dest_2 service="cdb_ashburn", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="CDB_ASHBURN" net_timeout=30, valid_for=(standby_logfile,all_roles)
2 log_archive_dest_2 service="cdb_ashburn", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="CDB_ASHBURN" net_timeout=30, valid_for=(standby_logfile,all_roles)
### ASHBURN ###
INST_ID NAME VALUE
---------- ------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 log_archive_config dg_config=(CDB_ASHBURN,CDB_TOKYO,CDB_SHIBUYA,CDB_OSAKA,CDB_MUMBAI,CDB_PHOENIX,CDB_FRANKFURT,CDB_SEOUL)
2 log_archive_config dg_config=(CDB_ASHBURN,CDB_TOKYO,CDB_SHIBUYA,CDB_OSAKA,CDB_MUMBAI,CDB_PHOENIX,CDB_FRANKFURT,CDB_SEOUL)
1 log_archive_dest_2 service="cdb_frankfurt", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="CDB_FRANKFURT" net_timeout=30, valid_for=(standby_logfile,all_roles)
2 log_archive_dest_2 service="cdb_frankfurt", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="CDB_FRANKFURT" net_timeout=30, valid_for=(standby_logfile,all_roles)
### FRANKFURT ###
INST_ID NAME VALUE
---------- ------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 log_archive_config dg_config=(CDB_FRANKFURT,CDB_TOKYO,CDB_SHIBUYA,CDB_OSAKA,CDB_MUMBAI,CDB_PHOENIX,CDB_ASHBURN,CDB_SEOUL)
2 log_archive_config dg_config=(CDB_FRANKFURT,CDB_TOKYO,CDB_SHIBUYA,CDB_OSAKA,CDB_MUMBAI,CDB_PHOENIX,CDB_ASHBURN,CDB_SEOUL)
1 log_archive_dest_2 service="cdb_mumbai", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="CDB_MUMBAI" net_timeout=30, valid_for=(standby_logfile,all_roles)
2 log_archive_dest_2 service="cdb_mumbai", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="CDB_MUMBAI" net_timeout=30, valid_for=(standby_logfile,all_roles)
### MUMBAI ###
INST_ID NAME VALUE
---------- ------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 log_archive_config dg_config=(CDB_MUMBAI,CDB_OSAKA,CDB_SHIBUYA,CDB_TOKYO,CDB_PHOENIX,CDB_ASHBURN,CDB_FRANKFURT,CDB_SEOUL)
2 log_archive_config dg_config=(CDB_MUMBAI,CDB_OSAKA,CDB_SHIBUYA,CDB_TOKYO,CDB_PHOENIX,CDB_ASHBURN,CDB_FRANKFURT,CDB_SEOUL)
1 log_archive_dest_2 service="cdb_seoul", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="CDB_SEOUL" net_timeout=30, valid_for=(standby_logfile,all_roles)
2 log_archive_dest_2 service="cdb_seoul", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="CDB_SEOUL" net_timeout=30, valid_for=(standby_logfile,all_roles)
### SEOUL ###
INST_ID NAME VALUE
---------- ------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 log_archive_config dg_config=(CDB_SEOUL,CDB_TOKYO,CDB_SHIBUYA,CDB_OSAKA,CDB_MUMBAI,CDB_PHOENIX,CDB_ASHBURN,CDB_FRANKFURT)
2 log_archive_config dg_config=(CDB_SEOUL,CDB_TOKYO,CDB_SHIBUYA,CDB_OSAKA,CDB_MUMBAI,CDB_PHOENIX,CDB_ASHBURN,CDB_FRANKFURT)
1 log_archive_dest_2 service="cdb_osaka", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="CDB_OSAKA" net_timeout=30, valid_for=(standby_logfile,all_roles)
2 log_archive_dest_2 service="cdb_osaka", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="CDB_OSAKA" net_timeout=30, valid_for=(standby_logfile,all_roles)
### OSAKA ###
INST_ID NAME VALUE
---------- ------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 log_archive_config dg_config=(CDB_OSAKA,CDB_TOKYO,CDB_SHIBUYA,CDB_MUMBAI,CDB_PHOENIX,CDB_ASHBURN,CDB_FRANKFURT,CDB_SEOUL)
2 log_archive_config dg_config=(CDB_OSAKA,CDB_TOKYO,CDB_SHIBUYA,CDB_MUMBAI,CDB_PHOENIX,CDB_ASHBURN,CDB_FRANKFURT,CDB_SEOUL)
1 log_archive_dest_2 service="cdb_shibuya", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="CDB_SHIBUYA" net_timeout=30, valid_for=(standby_logfile,all_roles)
2 log_archive_dest_2 service="cdb_shibuya", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="CDB_SHIBUYA" net_timeout=30, valid_for=(standby_logfile,all_roles)
### SHIBUYA ###
INST_ID NAME VALUE
---------- ------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 log_archive_config dg_config=(CDB_SHIBUYA,CDB_OSAKA,CDB_TOKYO,CDB_MUMBAI,CDB_PHOENIX,CDB_ASHBURN,CDB_FRANKFURT,CDB_SEOUL)
2 log_archive_config dg_config=(CDB_SHIBUYA,CDB_OSAKA,CDB_TOKYO,CDB_MUMBAI,CDB_PHOENIX,CDB_ASHBURN,CDB_FRANKFURT,CDB_SEOUL)
1 log_archive_dest_2
2 log_archive_dest_2
■ DB起動確認
次のようなスクリプトを作成して全Database起動と同期状態を確認します。
● Data Guard状態確認スクリプト作成
dg_statusスクリプト
[oracle@db-tokyo1 ~]$ cat dg_status.txt
#!/bin/bash
echo '### CDB_TOKYO ###'
sqlplus -s sys/<Password>@CDB_TOKYO as sysdba <<EOF
set lin 200
select INST_ID, DB_UNIQUE_NAME, database_role, open_mode, CURRENT_SCN from gv\$database;
col NAME format a30
select INST_ID, NAME, OPEN_MODE, CREATE_SCN from gv\$pdbs order by NAME,INST_ID;
exit
EOF
echo '### CDB_PHOENIX ###'
sqlplus -s sys/<Password>@CDB_PHOENIX as sysdba <<EOF
set lin 200
select INST_ID, DB_UNIQUE_NAME, database_role, open_mode, CURRENT_SCN from gv\$database;
col NAME format a30
select INST_ID, NAME, OPEN_MODE, CREATE_SCN from gv\$pdbs order by NAME,INST_ID;
exit
EOF
echo '### CDB_ASHBURN ###'
sqlplus -s sys/<Password>@CDB_ASHBURN as sysdba <<EOF
set lin 200
select INST_ID, DB_UNIQUE_NAME, database_role, open_mode, CURRENT_SCN from gv\$database;
col NAME format a30
select INST_ID, NAME, OPEN_MODE, CREATE_SCN from gv\$pdbs order by NAME,INST_ID;
exit
EOF
echo '### CDB_FRANKFURT ###'
sqlplus -s sys/<Password>@CDB_FRANKFURT as sysdba <<EOF
set lin 200
select INST_ID, DB_UNIQUE_NAME, database_role, open_mode, CURRENT_SCN from gv\$database;
col NAME format a30
select INST_ID, NAME, OPEN_MODE, CREATE_SCN from gv\$pdbs order by NAME,INST_ID;
exit
EOF
echo '### CDB_MUMBAI ###'
sqlplus -s sys/<Password>@CDB_MUMBAI as sysdba <<EOF
set lin 200
select INST_ID, DB_UNIQUE_NAME, database_role, open_mode, CURRENT_SCN from gv\$database;
col NAME format a30
select INST_ID, NAME, OPEN_MODE, CREATE_SCN from gv\$pdbs order by NAME,INST_ID;
exit
EOF
echo '### CDB_SEOUL ###'
sqlplus -s sys/<Password>@CDB_SEOUL as sysdba <<EOF
set lin 200
select INST_ID, DB_UNIQUE_NAME, database_role, open_mode, CURRENT_SCN from gv\$database;
col NAME format a30
select INST_ID, NAME, OPEN_MODE, CREATE_SCN from gv\$pdbs order by NAME,INST_ID;
exit
EOF
echo '### CDB_OSAKA ###'
sqlplus -s sys/<Password>@CDB_OSAKA as sysdba <<EOF
set lin 200
select INST_ID, DB_UNIQUE_NAME, database_role, open_mode, CURRENT_SCN from gv\$database;
col NAME format a30
select INST_ID, NAME, OPEN_MODE, CREATE_SCN from gv\$pdbs order by NAME,INST_ID;
exit
EOF
echo '### CDB_SHIBUYA ###'
sqlplus -s sys/<Password>@CDB_SHIBUYA as sysdba <<EOF
set lin 200
select INST_ID, DB_UNIQUE_NAME, database_role, open_mode, CURRENT_SCN from gv\$database;
col NAME format a30
select INST_ID, NAME, OPEN_MODE, CREATE_SCN from gv\$pdbs order by NAME,INST_ID;
exit
EOF
exit
● Data Guard状態確認スクリプト実行と確認
[oracle@db-tokyo1 ~]$ bash dg_status.txt
### CDB_TOKYO ###
INST_ID DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CURRENT_SCN
---------- ------------------------------ ---------------- -------------------- -----------
2 CDB_TOKYO PRIMARY READ WRITE 44586345
1 CDB_TOKYO PRIMARY READ WRITE 44586345
INST_ID NAME OPEN_MODE CREATE_SCN
---------- ------------------------------ ---------- ----------
1 PDB READ WRITE 18888851
2 PDB READ WRITE 18888851
1 PDB$SEED READ ONLY 2479478
2 PDB$SEED READ ONLY 2479478
### CDB_PHOENIX ###
INST_ID DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CURRENT_SCN
---------- ------------------------------ ---------------- -------------------- -----------
1 CDB_PHOENIX PHYSICAL STANDBY READ ONLY WITH APPLY 44586346
2 CDB_PHOENIX PHYSICAL STANDBY READ ONLY WITH APPLY 44586346
INST_ID NAME OPEN_MODE CREATE_SCN
---------- ------------------------------ ---------- ----------
1 PDB READ ONLY 18888851
2 PDB READ ONLY 18888851
1 PDB$SEED READ ONLY 2479478
2 PDB$SEED READ ONLY 2479478
### CDB_ASHBURN ###
INST_ID DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CURRENT_SCN
---------- ------------------------------ ---------------- -------------------- -----------
2 CDB_ASHBURN PHYSICAL STANDBY READ ONLY WITH APPLY 44586348
1 CDB_ASHBURN PHYSICAL STANDBY READ ONLY WITH APPLY 44586348
INST_ID NAME OPEN_MODE CREATE_SCN
---------- ------------------------------ ---------- ----------
1 PDB READ ONLY 18888851
2 PDB READ ONLY 18888851
1 PDB$SEED READ ONLY 2479478
2 PDB$SEED READ ONLY 2479478
### CDB_FRANKFURT ###
INST_ID DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CURRENT_SCN
---------- ------------------------------ ---------------- -------------------- -----------
1 CDB_FRANKFURT PHYSICAL STANDBY READ ONLY WITH APPLY 44586356
2 CDB_FRANKFURT PHYSICAL STANDBY READ ONLY WITH APPLY 44586356
INST_ID NAME OPEN_MODE CREATE_SCN
---------- ------------------------------ ---------- ----------
1 PDB READ ONLY 18888851
2 PDB READ ONLY 18888851
1 PDB$SEED READ ONLY 2479478
2 PDB$SEED READ ONLY 2479478
### CDB_MUMBAI ###
INST_ID DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CURRENT_SCN
---------- ------------------------------ ---------------- -------------------- -----------
2 CDB_MUMBAI PHYSICAL STANDBY READ ONLY WITH APPLY 44586356
1 CDB_MUMBAI PHYSICAL STANDBY READ ONLY WITH APPLY 44586356
INST_ID NAME OPEN_MODE CREATE_SCN
---------- ------------------------------ ---------- ----------
1 PDB READ ONLY 18888851
2 PDB READ ONLY 18888851
1 PDB$SEED READ ONLY 2479478
2 PDB$SEED READ ONLY 2479478
### CDB_SEOUL ###
INST_ID DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CURRENT_SCN
---------- ------------------------------ ---------------- -------------------- -----------
2 CDB_SEOUL PHYSICAL STANDBY READ ONLY WITH APPLY 44586356
1 CDB_SEOUL PHYSICAL STANDBY READ ONLY WITH APPLY 44586356
INST_ID NAME OPEN_MODE CREATE_SCN
---------- ------------------------------ ---------- ----------
1 PDB READ ONLY 18888851
2 PDB READ ONLY 18888851
1 PDB$SEED READ ONLY 2479478
2 PDB$SEED READ ONLY 2479478
### CDB_OSAKA ###
INST_ID DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CURRENT_SCN
---------- ------------------------------ ---------------- -------------------- -----------
2 CDB_OSAKA PHYSICAL STANDBY READ ONLY WITH APPLY 44586356
1 CDB_OSAKA PHYSICAL STANDBY READ ONLY WITH APPLY 44586356
INST_ID NAME OPEN_MODE CREATE_SCN
---------- ------------------------------ ---------- ----------
1 PDB READ ONLY 18888851
2 PDB READ ONLY 18888851
1 PDB$SEED READ ONLY 2479478
2 PDB$SEED READ ONLY 2479478
### CDB_SHIBUYA ###
INST_ID DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CURRENT_SCN
---------- ------------------------------ ---------------- -------------------- -----------
2 CDB_SHIBUYA PHYSICAL STANDBY READ ONLY WITH APPLY 44586358
1 CDB_SHIBUYA PHYSICAL STANDBY READ ONLY WITH APPLY 44586358
INST_ID NAME OPEN_MODE CREATE_SCN
---------- ------------------------------ ---------- ----------
1 PDB READ ONLY 18888851
2 PDB READ ONLY 18888851
1 PDB$SEED READ ONLY 2479478
2 PDB$SEED READ ONLY 2479478
■ 世界へ愛と平和を贈る
1) Tokyo Database接続
[oracle@db-tokyo1 ~]$ export NLS_LANG=Japanese_Japan.AL32UTF8
[oracle@db-tokyo1 ~]$ sqlplus SantaClaus/<Password>@TOKYO
SQL*Plus: Release 19.0.0.0.0 - Production on 日 12月 25 00:000:00 2022
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
に接続されました。
SANTACLAUS@TOKYO SQL>
2) 世界TABLE作成
SANTACLAUS@TOKYO SQL>
CREATE TABLE "世界"
(
val VARCHAR2(30),
time TIMESTAMP WITH LOCAL TIME ZONE
)
;
表が作成されました。
3) 愛と平和をINSERT
SANTACLAUS@TOKYO SQL> insert into "世界" (val, time) values ('平和', sysdate);
1行が作成されました。
SANTACLAUS@TOKYO SQL> insert into "世界" (val, time) values ('愛', sysdate);
1行が作成されました。
SANTACLAUS@TOKYO SQL> commit;
コミットが完了しました。
4) 確認
SANTACLAUS@TOKYO SQL> SELECT * FROM "世界";
VAL TIME
-------------------- ------------------------------
平和 22-12-25 00:00:01.000000
愛 22-12-25 00:00:02.000000
● データ伝搬確認スクリプト作成
全リージョンの Databaseへ伝搬されていることを確認
world.txt スクリプト
[oracle@db-tokyo1 ~]$ cat world.txt
#!/bin/bash
echo '### TOKYO ###'
sqlplus -s SantaClaus/<Password>@TOKYO <<EOF
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS TZH:TZM';
set lin 200
col "Tokyo Time" format a30
col val format a10
SELECT val ,CAST(time AT TIME ZONE 'Asia/Tokyo' AS TIMESTAMP WITH TIME ZONE) AS "Tokyo Time" FROM "世界";
exit
EOF
echo '### PHOENIX ###'
sqlplus -s SantaClaus/<Password>@PHOENIX <<EOF
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS TZH:TZM';
set lin 200
col "Phoenix Time" format a30
col val format a10
SELECT val ,CAST(time AT TIME ZONE 'America/Phoenix' AS TIMESTAMP WITH TIME ZONE) AS "Phoenix Time" FROM "世界";
exit
EOF
echo '### ASHBURN ###'
sqlplus -s SantaClaus/<Password>@ASHBURN <<EOF
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS TZH:TZM';
set lin 200
col "US Central Time" format a30
col val format a10
SELECT val ,CAST(time AT TIME ZONE 'America/Chicago' AS TIMESTAMP WITH TIME ZONE) AS "US Central Time" FROM "世界";
exit
EOF
echo '### FRANKFURT ###'
sqlplus -s SantaClaus/<Password>@FRANKFURT <<EOF
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS TZH:TZM';
set lin 200
col "Germany Time" format a30
col val format a10
SELECT val ,CAST(time AT TIME ZONE 'Europe/Berlin' AS TIMESTAMP WITH TIME ZONE) AS "Germany Time" FROM "世界";
exit
EOF
echo '### MUMBAI ###'
sqlplus -s SantaClaus/<Password>@MUMBAI <<EOF
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS TZH:TZM';
set lin 200
col "India Time" format a30
col val format a10
SELECT val ,CAST(time AT TIME ZONE 'Asia/Calcutta' AS TIMESTAMP WITH TIME ZONE) AS "India Time" FROM "世界";
exit
EOF
echo '### SEOUL ###'
sqlplus -s SantaClaus/<Password>@SEOUL <<EOF
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS TZH:TZM';
set lin 200
col "Seoul Time" format a30
col val format a10
SELECT val ,CAST(time AT TIME ZONE 'Asia/Seoul' AS TIMESTAMP WITH TIME ZONE) AS "Seoul Time" FROM "世界";
exit
EOF
echo '### OSAKA ###'
sqlplus -s SantaClaus/<Password>@OSAKA <<EOF
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS TZH:TZM';
set lin 200
col "Osaka Time" format a30
col val format a10
SELECT val ,CAST(time AT TIME ZONE 'Asia/Tokyo' AS TIMESTAMP WITH TIME ZONE) AS "Osaka Time" FROM "世界";
exit
EOF
echo '### SHIBUYA ###'
sqlplus -s SantaClaus/<Password>@SHIBUYA <<EOF
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS TZH:TZM';
set lin 200
col "Tokyo Time" format a30
col val format a10
SELECT val ,CAST(time AT TIME ZONE 'Asia/Tokyo' AS TIMESTAMP WITH TIME ZONE) AS "Tokyo Time" FROM "世界";
exit
EOF
exit
● データ伝搬確認スクリプト実行と結果
・ TOKYO
[oracle@db-tokyo1 ~]$ bash world.txt
### TOKYO ###
VAL Tokyo
---------- ------------------------------
平和 2022-12-25 09:00:01 +09:00
愛 2022-12-25 09:00:02 +09:00
・PHOENIX
### PHOENIX ###
VAL Phoenix Time
---------- ------------------------------
平和 2022-12-24 05:00:01 -07:00
愛 2022-12-24 05:00:02 -07:00
・ASHBURN
### ASHBURN ###
VAL US Central Time
---------- ------------------------------
平和 2022-12-24 06:00:01 -06:00
愛 2022-12-24 06:00:02 -06:00
・FRANKFURT
### FRANKFURT ###
VAL Germany Time
---------- ------------------------------
平和 2022-12-25 01:00:01 +01:00
愛 2022-12-25 01:00:02 +01:00
・MUMBAI
### MUMBAI ###
VAL India Time
---------- ------------------------------
平和 2022-12-25 05:00:01 +05:30
愛 2022-12-25 05:00:02 +05:30
・SEOUL
### SEOUL ###
VAL Seoul Time
---------- ------------------------------
平和 2022-12-25 09:00:01 +09:00
愛 2022-12-25 09:00:02 +09:00
・OSAKA
### OSAKA ###
VAL Osaka Time
---------- ------------------------------
平和 2022-12-25 09:00:01 +09:00
愛 2022-12-25 09:00:02 +09:00
・SHIBUYA
### SHIBUYA ###
VAL Tokyo Time
---------- ------------------------------
平和 2022-12-25 09:00:01 +09:00
愛 2022-12-25 09:00:02 +09:00
トランザクションが発行されるたびに世界を周って伝搬される
なんてロマンチック
素敵⭐️
Xmas
世界が平和でありますように
世界が愛であふれますように
Happy Xmas!