はじめに
本記事では、Oracle Database 23aiの新機能であるData Guard per Pluggable Database (DGPDB)の構築方法をご紹介します。
検証概要
以下、検証環境の情報です。Oracle Cloud Infrastructure (OCI)上のDBPaaSであるBase Database Service (BaseDB)で実施しています。本検証では、NRTリージョンのTOKYO_SALES (ソースPDB)からOSAKAリージョンにOSAKA_SALES (ターゲットPDB)を構築し、DGPDB構成を組みます。
| site1 | site2 | |
|---|---|---|
| リージョン | NRT | KIX |
| ホスト名 | dbtokyo | dbosaka |
| DB version | 23.4 | 23.4 |
| CDB | TOKYO_1 | OSAKA_1 |
| PDB | TOKYO_SALES | OSAKA_SALES (DGPDB) |
構成イメージ図:
DGPDBの構築手順
CDBを準備する
- NRT環境にTOKYO_1 (CDB)、KIX環境にOSAKA_1 (CDB)を作成します。
また、TOKYO_1にはソースPDBとなるTOKYO_SALES (PDB)を作成します。本検証環境では、DGPDBを組まない他のPDBを作成していますが、こちらは任意です。
NRT環境の確認:
[oracle@dbtokyo ~]$ sqlplus / as sysdba
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud on Wed Aug 14 13:56:14 2024
Version 23.4.1.24.06
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai EE Extreme Perf Release 23.0.0.0.0 - for Oracle Cloud
Version 23.4.1.24.06
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string tokyo_1
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TOKYO_SALES READ WRITE NO
4 TOKYO_FIN READ WRITE NO
KIX環境の確認:
[oracle@dbosaka ~]$ sqlplus / as sysdba
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud on Wed Aug 14 14:02:04 2024
Version 23.4.1.24.06
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai EE Extreme Perf Release 23.0.0.0.0 - for Oracle Cloud
Version 23.4.1.24.06
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string osaka_1
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 OSAKA_PUB READ WRITE NO
- それぞれのCDBのtnsadmin.oraファイルにお互いの接続情報を入力します (両環境で実施します)
記入例 (NRT):
[oracle@dbtokyo admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/23.0.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_TOKYO =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbtokyo)(PORT = 1521))
TOKYO_1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbtokyo)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tokyo_1.sub08020728580.tokyovcn.oraclevcn.com)
)
)
OSAKA_1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 217.142.232.223)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = osaka_1.sub08020729380.osakavcn.oraclevcn.com)
)
)
KIX環境も同様に記入します。
- 最終確認のためのテストデータをTOKYO_SALESに追加します
TOKYO_PDBで実行:
SQL> alter session set container = tokyo_sales;
Session altered.
SQL> create user testuser identified by WelCome123#123#;
User created.
SQL> grant CREATE SESSION,CONNECT,RESOURCE,UNLIMITED TABLESPACE to TESTUSER;
Grant succeeded.
SQL> create table testuser.employee (ID varchar(3), Name varchar(10));
Table created.
SQL> insert into testuser.employee (ID, Name) Values ('1', 'James');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from testuser.employee;
ID NAME
--- ----------
1 James
- 各初期化パラメータの設定
- Data Guard Brokerの自動起動を有効化
- スタンバイ・データベース上にファイルが自動的に作成され、場合によっては既存のファイルが上書きされるようにするための設定
- アーカイブ先の指定
TOKYO_1, OSAKA_1で実行:
SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE scope = both;
System altered.
SQL> alter system set standby_file_management = AUTO scope = both;
System altered.
SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TOKYO_1' scope=both;
System altered.
実行後は再起動を実施します。
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 3.2694E+10 bytes
Fixed Size 7697192 bytes
Variable Size 3959422976 bytes
Database Buffers 2.8588E+10 bytes
Redo Buffers 138514432 bytes
Database mounted.
Database opened.
- ログ関連の設定
- Force Logging, Flashback Database, Archivelovモードが有効になっているか確認します
- 無効化の場合、それぞれ有効化します
TOKYO_1, OSAKA_1で確認する:
SQL> select name,FORCE_LOGGING,FLASHBACK_ON from v$database;
NAME FORCE_LOGGING FLASHBACK_ON
--------- ----------------------- ------------------
TOKYO YES YES
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
Oracle Walletを設定する
DGPDBの構成を作成、管理していく上で必要なOracle Walletを作成します。
- Walletの作成とsysユーザの認証資格の格納
TOKYO_1で実行:
[oracle@dbtokyo ~]$ mkdir -p $ORACLE_HOME/dbs/wallets
[oracle@dbtokyo ~]$ chmod -R 700 $ORACLE_HOME/dbs/wallets
[oracle@dbtokyo ~]$ mkstore -wrl $ORACLE_HOME/dbs/wallets/dgpdb -create
Oracle Secret Store Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2024, Oracle and/or its affiliates. All rights reserved.
Enter password:
Enter password again:
[oracle@dbtokyo ~]$ mkstore -wrl $ORACLE_HOME/dbs/wallets/dgpdb -createCredential TOKYO_1 'sys'
Oracle Secret Store Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2024, Oracle and/or its affiliates. All rights reserved.
Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:
[oracle@dbtokyo ~]$ mkstore -wrl $ORACLE_HOME/dbs/wallets/dgpdb -createCredential OSAKA_1 'sys'
Oracle Secret Store Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2024, Oracle and/or its affiliates. All rights reserved.
Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:
OSAKA_1で実行:
[oracle@dbosaka ~]$ mkdir -p $ORACLE_HOME/dbs/wallets
[oracle@dbosaka ~]$ chmod -R 700 $ORACLE_HOME/dbs/wallets
[oracle@dbosaka ~]$ mkstore -wrl $ORACLE_HOME/dbs/wallets/dgpdb -create
Oracle Secret Store Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2024, Oracle and/or its affiliates. All rights reserved.
Enter password:
Enter password again:
[oracle@dbosaka ~]$ mkstore -wrl $ORACLE_HOME/dbs/wallets/dgpdb -createCredential OSAKA_1 'sys'
Oracle Secret Store Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2024, Oracle and/or its affiliates. All rights reserved.
Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:
[oracle@dbosaka ~]$ mkstore -wrl $ORACLE_HOME/dbs/wallets/dgpdb -createCredential TOKYO_1 'sys'
Oracle Secret Store Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2024, Oracle and/or its affiliates. All rights reserved.
Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:
- sqlnet.oraファイルの内容の編集 (両環境で実施します)
記入例 (NRT, KIX):
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/product/23.0.0.0/dbhome_1/dbs/wallets/dgpdb)
)
)
SQLNET.WALLET_OVERRIDE=TRUE
- 設定完了後はデータベースを再起動します
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 3.2694E+10 bytes
Fixed Size 7697192 bytes
Variable Size 3959422976 bytes
Database Buffers 2.8588E+10 bytes
Redo Buffers 138514432 bytes
Database mounted.
Database opened.
- Walletを使用した接続確認をします
NRTで実行:
[oracle@dbtokyo ~]$ sqlplus /@osaka_1 as sysdba
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud on Tue Jul 23 18:17:52 2024
Version 23.4.1.24.06
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai EE Extreme Perf Release 23.0.0.0.0 - for Oracle Cloud
Version 23.4.1.24.06
SQL>
KIXで実行:
[oracle@dbosaka ~]$ sqlplus /@tokyo_1 as sysdba
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud on Tue Jul 23 18:18:05 2024
Version 23.4.1.24.06
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai EE Extreme Perf Release 23.0.0.0.0 - for Oracle Cloud
Version 23.4.1.24.06
SQL>
Data Guard Brokerを構成する
- Data Guard BrokerでBroker Configurationを作成します
NRTで実行:
[oracle@dbtokyo ~]$ dgmgrl /@tokyo_1
DGMGRL for Linux: Release 23.0.0.0.0 - for Oracle Cloud on Tue Jul 23 18:18:58 2024
Version 23.4.1.24.06
Copyright (c) 1982, 2024, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "tokyo_1"
Connected as SYSDBA.
DGMGRL> create configuration site1 as primary database is TOKYO_1 connect identifier is TOKYO_1;
Connected to "tokyo_1"
Configuration "site1" created with primary database "tokyo_1"
- 確認します
DGMGRL> show configuration
Configuration - site1
Protection Mode: MaxPerformance
Members:
tokyo_1 - Primary database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration
Configuration - site1
Protection Mode: MaxPerformance
Members:
tokyo_1 - Primary database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 3 seconds ago)
- OSAKA_1のConfigurationも同様に作成します
DGMGRL> create configuration site2 as primary database is OSAKA_1 connect identifier is OSAKA_1;
Connected to "osaka_1"
Configuration "site2" created with primary database "osaka_1"
確認します
DGMGRL> show configuration
Configuration - site2
Protection Mode: MaxPerformance
Members:
osaka_1 - Primary database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration
Configuration - site2
Protection Mode: MaxPerformance
Members:
osaka_1 - Primary database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 5 seconds ago)
- 作成したBroker Configuration同士の接続を確立します
[oracle@dbtokyo ~]$ dgmgrl /
DGMGRL for Linux: Release 23.0.0.0.0 - for Oracle Cloud on Tue Jul 23 18:23:02 2024
Version 23.4.1.24.06
Copyright (c) 1982, 2024, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "tokyo_1"
Connected as SYSDG.
DGMGRL> add configuration site2 connect identifier is OSAKA_1;
Configuration site2 added.
TOKYO_1とOSAKA_1がPrimary databaseとして構成されていることを確認します
DGMGRL> show configuration
Configuration - site1
Protection Mode: MaxPerformance
Members:
tokyo_1 - Primary database
osaka_1 - Primary database in site2 configuration
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 2 seconds ago)
DGPDB_INTユーザ・アカウントをアンロックする
- 以下のように、DGPDB_INTユーザ・アカウントをアンロックします
[oracle@dbtokyo ~]$ dgmgrl /
DGMGRL for Linux: Release 23.0.0.0.0 - for Oracle Cloud on Tue Jul 23 18:23:02 2024
Version 23.4.1.24.06
Copyright (c) 1982, 2024, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "tokyo_1"
Connected as SYSDG.
DGMGRL> edit configuration prepare dgpdb;
Enter password for DGPDB_INT account at tokyo_1:
Enter password for DGPDB_INT account at osaka_1:
Prepared Data Guard for Pluggable Database at osaka_1.
Prepared Data Guard for Pluggable Database at tokyo_1.
ターゲットPDBを追加する
- OSAKA_1にOSAKA_SALES (ターゲットPDB)を追加します
[oracle@dbtokyo ~]$ dgmgrl /@tokyo_1
DGMGRL for Linux: Release 23.0.0.0.0 - for Oracle Cloud on Tue Jul 23 23:19:08 2024
Version 23.4.1.24.06
Copyright (c) 1982, 2024, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "tokyo_1"
Connected as SYSDBA.
DGMGRL> add pluggable database 'osaka_sales' at 'osaka_1' source is 'tokyo_sales' at 'tokyo_1' PDBFileNameConvert IS "'+DATA/TOKYO_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/','+DATA/OSAKA_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/'" 'keystore identified by "Welcome#1Welcome#1"';
Pluggable Database "OSAKA_SALES" added
PDBFileNameは、以下のようにTOKYO_SALES (ソースPDB)で確認できます
SQL> alter session set container = tokyo_sales;
Session altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/TOKYO_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/system.276.1173811313
+DATA/TOKYO_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/sysaux.273.1173811319
+DATA/TOKYO_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/undotbs1.274.1173811325
+DATA/TOKYO_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/users.277.1173811391
- OSAKA_1でOSAKA_SALESが追加されていることを確認します
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 OSAKA_PUB READ WRITE NO
4 OSAKA_SALES MOUNTED
- ターゲットPDBが追加されると、REDOログの転送が開始するが、データファイルの転送とスタンバイREDOログが作成が行われていないため、REDO適用は開始されません
- また、TDEウォレットを使用した環境(BaseDBではデフォルト)の場合、REDO適用を開始する前に、データファイルとREDOログの暗号化鍵の転送が必要です
以下のように、BrokerでOSAKA_SALESのステータスを確認するとエラーが出力されます。
DGMGRL> show pluggable database osaka_sales at osaka_1
Pluggable database - OSAKA_SALES at osaka_1
Data Guard Role: Physical Standby
Con_ID: 4
Source: con_id 3 at tokyo_1
Transport Lag: 32 seconds (computed 43 seconds ago)
Apply Lag: (unknown)
Intended State: APPLY-ON
Apply State: Not Running
Pluggable Database Status:
ORA-16914: Redo Apply Services were not started at the standby pluggable database.
TDE暗号化鍵を転送する
- TDEウォレットを使用した環境(BaseDBではデフォルト)の場合、データファイルとREDOログの暗号化鍵の転送する必要があります
- ソースPDB用の暗号化鍵(データファイル用)とCDB$ROOT用の暗号化鍵(REDOログ用) は、転送されたデータファイルとREDOログをそれぞれ複合化するために、ソースPDB側の環境のキーストアにインポートする必要があります
TOKYO_1とTOKYO_SALESの暗号化鍵をそれぞれexportする:
[oracle@dbtokyo ~]$ sqlplus /as sysdba
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud on Tue Jul 23 23:24:00 2024
Version 23.4.1.24.06
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai EE Extreme Perf Release 23.0.0.0.0 - for Oracle Cloud
Version 23.4.1.24.06
SQL> administer key management export encryption keys with secret secret to '/home/oracle/tde/export.exp' force keystore identified by Welcome#1Welcome#1;
keystore altered.
SQL> alter session set container = tokyo_sales;
Session altered.
SQL> administer key management export encryption keys with secret secret to '/home/oracle/tde/exportp.exp' force keystore identified by Welcome#1Welcome#1;
keystore altered.
- scpコマンドなどを使用して、KIX環境にexportファイルを転送します
[oracle@dbtokyo tde]$ scp export* oracle@xxx.xxx.xxx.xxx:/home/oracle/tde/
- KIX環境で転送してきたexportファイルをOSAKA_1とOSAKA_SALESにそれぞれimportします
[oracle@dbosaka tde]$ sqlplus /as sysdba
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud on Tue Jul 23 23:28:42 2024
Version 23.4.1.24.06
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai EE Extreme Perf Release 23.0.0.0.0 - for Oracle Cloud
Version 23.4.1.24.06
SQL> administer key management import encryption keys with secret secret from '/home/oracle/tde/export.exp' force keystore identified by "Welcome#1Welcome#1" with backup;
keystore altered.
SQL> alter session set container = osaka_sales;
Session altered.
SQL> administer key management import encryption keys with secret secret from '/home/oracle/tde/exportp.exp' force keystore identified by "Welcome#1Welcome#1" with backup;
keystore altered.
ソースPDBのデータファイルを転送する
本記事では、RMANを使用してデータファイルを転送します。
- TOKYO_SALES (ソースPDB)のデータファイルの情報を確認します
SQL> alter session set container = tokyo_sales;
Session altered.
SQL> select file_id, tablespace_name,file_name from dba_data_files;
FILE_ID TABLESPACE_NAME
---------- ------------------------------
FILE_NAME
--------------------------------------------------------------------------------
9 SYSAUX
+DATA/TOKYO_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/sysaux.273.1173811319
10 UNDOTBS1
+DATA/TOKYO_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/undotbs1.274.1173811325
8 SYSTEM
+DATA/TOKYO_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/system.276.1173811313
FILE_ID TABLESPACE_NAME
---------- ------------------------------
FILE_NAME
--------------------------------------------------------------------------------
12 USERS
+DATA/TOKYO_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/users.277.1173811391
- RMANコマンドを使用してTOKYO_SALESからOSAKA_SALESへデータを転送します
[oracle@dbtokyo ~]$ rman target sys/Welcome#1Welcome#1@tokyo_1 auxiliary sys/Welcome#1Welcome#1@osaka_1
Recovery Manager: Release 23.0.0.0.0 - for Oracle Cloud on Tue Jul 23 23:38:40 2024
Version 23.4.1.24.06
Copyright (c) 1982, 2024, Oracle and/or its affiliates. All rights reserved.
connected to target database: TOKYO (DBID=2411374040)
connected to auxiliary database: OSAKA (DBID=3253884017)
RMAN> run{
allocate channel ch1 type disk;
backup as copy reuse datafile 8,9,10,12 auxiliary format new;
}
run{
2> allocate channel ch1 type disk;
3> backup as copy reuse datafile 8,9,10,12 auxiliary format new;
4>
}
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=22 device type=DISK
Starting backup at 23-JUL-24
channel ch1: starting datafile copy
input datafile file number=00009 name=+DATA/TOKYO_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/sysaux.273.1173811319
output file name=+DATA/OSAKA_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/sysaux.258.1175125221 tag=TAG20240723T234017
channel ch1: datafile copy complete, elapsed time: 00:00:07
channel ch1: starting datafile copy
input datafile file number=00008 name=+DATA/TOKYO_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/system.276.1173811313
output file name=+DATA/OSAKA_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/system.283.1175125225 tag=TAG20240723T234017
channel ch1: datafile copy complete, elapsed time: 00:00:03
channel ch1: starting datafile copy
input datafile file number=00012 name=+DATA/TOKYO_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/users.277.1173811391
output file name=+DATA/OSAKA_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/users.284.1175125229 tag=TAG20240723T234017
channel ch1: datafile copy complete, elapsed time: 00:00:01
channel ch1: starting datafile copy
input datafile file number=00010 name=+DATA/TOKYO_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/undotbs1.274.1173811325
output file name=+DATA/OSAKA_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/undotbs1.285.1175125231 tag=TAG20240723T234017
channel ch1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-JUL-24
released channel: ch1
- OSAKA_SALESでデータファイルが転送されているか確認します
SQL> alter session set container = osaka_sales;
Session altered.
SQL> select file#,name from v$datafile;
FILE#
----------
NAME
--------------------------------------------------------------------------------
13
+DATA/MUST_RENAME_THIS_DATAFILE_8.4294967295.4294967295
14
+DATA/MUST_RENAME_THIS_DATAFILE_9.4294967295.4294967295
15
+DATA/MUST_RENAME_THIS_DATAFILE_10.4294967295.4294967295
FILE#
----------
NAME
--------------------------------------------------------------------------------
16
+DATA/MUST_RENAME_THIS_DATAFILE_12.4294967295.4294967295
- ファイル名がMUST_RENAME_THIS_DATAFILEと表示されているため、名前をそれぞれ変更します
SQL> alter session set container = osaka_sales;
Session altered.
SQL> alter database rename file '+DATA/MUST_RENAME_THIS_DATAFILE_8.4294967295.4294967295' to '+DATA/OSAKA_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/system.283.1175125225';
alter database rename file '+DATA/MUST_RENAME_THIS_DATAFILE_9.4294967295.4294967295' to '+DATA/OSAKA_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/sysaux.258.1175125221';
alter database rename file '+DATA/MUST_RENAME_THIS_DATAFILE_10.4294967295.4294967295' to '+DATA/OSAKA_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/undotbs1.285.1175125231';
alter database rename file '+DATA/MUST_RENAME_THIS_DATAFILE_12.4294967295.4294967295' to '+DATA/OSAKA_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/users.284.1175125229';
Database altered.
* ファイル名が変更されているか確認します
SQL> select file#,name from v$datafile;
FILE#
----------
NAME
--------------------------------------------------------------------------------
13
+DATA/OSAKA_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/system.283.1175125225
14
+DATA/OSAKA_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/sysaux.258.1175125221
15
+DATA/OSAKA_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/undotbs1.285.1175125231
FILE#
----------
NAME
--------------------------------------------------------------------------------
16
+DATA/OSAKA_1/1CBA380DAC856B86E0637500020AF57E/DATAFILE/users.284.1175125229
スタンバイREDOログをターゲットPDBに追加する
DGPDB構成ではスタンバイREDOログをPDBレベルで作成します。
スタンバイREDOログはターゲット・ロールのPDBでのみ作成可能です。
- オンラインREDOログを確認します
- 追加するスタンバイREDOログはソースCDBのオンラインREDOログと同じサイズである必要があります
- スタンバイREDOログの数はソースCDBのオンラインREDOログよりも1以上多い必要があります
SQL> select group#,thread#,bytes from v$log;
GROUP# THREAD# BYTES
---------- ---------- ----------
1 1 1073741824
2 1 1073741824
3 1 1073741824
- スタンバイログをOSAKA_SALES (ターゲットPDB)に追加します
SQL> alter session set container = osaka_sales;
Session altered.
SQL> select group#,thread#,bytes from v$standby_log;
no rows selected
SQL> alter database add standby logfile size 1073741824;
Database altered.
SQL> alter database add standby logfile size 1073741824;
Database altered.
SQL> alter database add standby logfile size 1073741824;
Database altered.
SQL> alter database add standby logfile size 1073741824;
Database altered.
- 確認します
SQL> select group#,thread#,bytes from v$standby_log;
GROUP# THREAD# BYTES
---------- ---------- ----------
1 0 1073741824
2 0 1073741824
3 0 1073741824
4 0 1073741824
- Data Guard Brokerで確認することもできます
[oracle@dbtokyo ~]$ dgmgrl /@tokyo_1
DGMGRL for Linux: Release 23.0.0.0.0 - for Oracle Cloud on Tue Jul 23 23:50:56 2024
Version 23.4.1.24.06
Copyright (c) 1982, 2024, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "tokyo_1"
Connected as SYSDBA.
DGMGRL> validate pluggable database osaka_sales at osaka_1
Ready for Switchover: NO
Data Guard Role: Physical Standby
Apply State: Not Running
Standby Redo Log Files: 4
Source: TOKYO_SALES (con_id 3) at tokyo_1
REDO適用を開始する
- OSAKA_SALES (ターゲットPDB)へのREDO適用を開始します
DGMGRL> edit pluggable database osaka_sales at osaka_1 set state = 'apply-on';
Succeeded.
- ターゲットPDBへのREDO適用のステータスをData Guard Brokerで確認します
- 転送ラグ、適用ラグが表示され、Apply Stateが"Running"になっていることを確認します
DGMGRL> show pluggable database osaka_sales at osaka_1
Pluggable database - OSAKA_SALES at osaka_1
Data Guard Role: Physical Standby
Con_ID: 4
Source: con_id 3 at tokyo_1
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Intended State: APPLY-ON
Apply State: Running
Apply Instance: osaka
Average Apply Rate: 954 KByte/s
Real Time Query: OFF
Pluggable Database Status:
SUCCESS
- 転送ラグと適用ラグが大きい場合は、ソースPDBのCDBでログスイッチを実行します
SQL> alter system archive log current;
System altered.
SQL> alter system archive log current;
System altered.
スイッチオーバーを実行する
- スイッチオーバーを実行します
DGMGRL> switchover to pluggable database osaka_sales at osaka_1;
Performing switchover NOW, please wait...
Switchover succeeded, new primary is "osaka_sales"
- ステータスを確認します
DGMGRL> show configuration
Configuration - site1
Protection Mode: MaxPerformance
Members:
TOKYO_1 - Primary database
Error: ORA-16778: Redo transport error occurred for one or more members.
OSAKA_1 - Primary database in site2 configuration
Data Guard for PDB: Enabled in TARGET role
Configuration Status:
SUCCESS (status updated 50 seconds ago)
上記のように、初回のスイッチオーバーの実行は失敗します。
理由は以下です:
- 新ターゲットPDBでスタンバイREDOログが構成されていないため
- 新ターゲットPDB側のCDBに新ソースPDB側のCDBの暗号化鍵が転送されていないため (TDEウォレットを使用した環境のみ)
暗号化鍵の転送とスタンバイREDOログの構成を行います。
- 新ソースPDBを起動します (スイッチオーバー後、mount状態になっているため)
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 OSAKA_PUB READ WRITE NO
4 OSAKA_SALES MOUNTED
SQL> alter pluggable database osaka_sales open;
Pluggable database altered.
- 新ターゲットPDBへのREDO適用を停止します
DGMGRL> edit pluggable database tokyo_sales at tokyo_1 set state='apply-off';
Succeeded.
- 暗号化鍵をエクスポートします
OSAKA_1で実行:
SQL> administer key management export encryption keys with secret secret to '/home/oracle/tde/exportk.exp' force keystore identified by Welcome#1Welcome#1;
keystore altered.
-
scpコマンドなどでexportファイルをNRT環境に転送します
-
暗号化鍵をインポートします
TOKYO_1で実行:
SQL> administer key management import encryption keys with secret secret from '/home/oracle/tde/exportk.exp' force keystore identified by "Welcome#1Welcome#1" with backup;
keystore altered.
- スタンバイREDOログをTOKYO_SALES (新ターゲットPDB)で作成します
TOKYO_SALESで実行：
SQL> select group#,thread#,bytes from v$standby_log;
no rows selected
SQL> alter database add standby logfile size 1073741824;
Database altered.
SQL> alter database add standby logfile size 1073741824;
Database altered.
SQL> alter database add standby logfile size 1073741824;
Database altered.
SQL> alter database add standby logfile size 1073741824;
Database altered.
SQL> select group#,thread#,bytes from v$standby_log;
GROUP# THREAD# BYTES
---------- ---------- ----------
1 0 1073741824
2 0 1073741824
3 0 1073741824
4 0 1073741824
- TOKYO_SALES (新ターゲットPDB)へのREDO適用を開始します
DGMGRL> edit pluggable database tokyo_sales at tokyo_1 set state = 'apply-on';
Succeeded.
- 新ターゲットPDBへのREDO適用ステータスを確認します
DGMGRL> show pluggable database tokyo_sales at tokyo_1
Pluggable database - TOKYO_SALES at tokyo_1
Data Guard Role: Physical Standby
Con_ID: 3
Source: con_id 4 at osaka_1
Transport Lag: 0 seconds (computed 2 seconds ago)
Apply Lag: 0 seconds (computed 2 seconds ago)
Intended State: APPLY-ON
Apply State: Running
Apply Instance: tokyo
Average Apply Rate: 123 KByte/s
Real Time Query: OFF
Pluggable Database Status:
SUCCESS
- テストデータを確認します
SQL> alter session set container = osaka_sales;
Session altered.
SQL> select * from testuser.employee;
ID NAME
--- ----------
1 James
スイッチバック
一度スタンバイREDOログを作成すると、その後は作成を必要としないため、スイッチオーバー実行後の追加手順なく成功します。
- スイッチオーバーを実行します
DGMGRL> switchover to pluggable database tokyo_sales at tokyo_1;
Performing switchover NOW, please wait...
Switchover succeeded, new primary is "tokyo_sales"
今回はエラーがありませんでした。
- ステータスを確認します
DGMGRL> show configuration
Configuration - site1
Protection Mode: MaxPerformance
Members:
tokyo_1 - Primary database
osaka_1 - Primary database in site2 configuration
Data Guard for PDB: Enabled in SOURCE role
Configuration Status:
SUCCESS (status updated 48 seconds ago)
DGMGRL> show pluggable database osaka_sales at osaka_1
Pluggable database - OSAKA_SALES at osaka_1
Data Guard Role: Physical Standby
Con_ID: 4
Source: con_id 3 at tokyo_1
Transport Lag: 0 seconds (computed 2112 seconds ago)
Apply Lag: 0 seconds (computed 2112 seconds ago)
Intended State: APPLY-ON
Apply State: Running
Apply Instance: osaka
Average Apply Rate: 96 KByte/s
Real Time Query: OFF
Pluggable Database Status:
SUCCESS
本記事の内容は以上です。