數(shù)據(jù)庫管理358期 2025-08-18
- 數(shù)據(jù)庫管理-第358期 23ai:DG PDB(20250818)
- 1 環(huán)境
- 2 配置DG PDB
- 2.1 配置tnsnames
- 2.2 復制密碼文件
- 2.3 數(shù)據(jù)庫配置
- 2.4 創(chuàng)建DG配置
- 2.5 添加(關聯(lián))DG配置
- 2.6 啟用DG配置并檢查
- 2.7 啟用DG PDB
- 2.8 創(chuàng)建源PDB的DG配置
- 2.8 復制數(shù)據(jù)文件
- 2.9 修改proddg數(shù)據(jù)文件路徑與文件名
- 2.10 proddg添加standby logfile
- 2.11 驗證proddg
- 2.12 啟動日志同步
- 2.13 開啟proddg
- 2.14 同步測試
- 3 DG PDB切換
- 4 Failover
- 5 監(jiān)控DG PDB
- 6 刪除DG PDB
- 總結
數(shù)據(jù)庫管理-第358期 23ai:DG PDB(20250818)
作者:胖頭魚的魚缸(尹海文) Oracle ACE Pro: Database PostgreSQL ACE Partner 10年數(shù)據(jù)庫行業(yè)經(jīng)驗 擁有OCM 11g/12c/19c、MySQL 8.0 OCP、Exadata、CDP等認證 墨天輪MVP,ITPUB認證專家 圈內擁有“總監(jiān)”稱號,非著名社恐(社交恐怖分子) 公眾號:胖頭魚的魚缸 CSDN:胖頭魚的魚缸(尹海文) 墨天輪:胖頭魚的魚缸 ITPUB:yhw1809 IFClub:胖頭魚的魚缸 除授權轉載并標明出處外,均為“非法”抄襲

在《數(shù)據(jù)庫管理-第141期 DG PDB - Oracle DB 23c(20240129)》中,在Oracle 23c(內測版23.4)上部署過一次DG PDB,趁著內測版本更新到了23.9,且相較于之前官方文檔已經(jīng)完善,因此也在23ai上也再次部署一下DG PDB。
1 環(huán)境

DBCA建庫過程中已按照默認方式完成以下配置:
- 創(chuàng)建空CDB(無PDB)
- 開啟OMF
- 開啟指定FRA
- 開啟歸檔日志
后續(xù)在dbaas1中創(chuàng)建對應PDB。
2 配置DG PDB
2.1 配置tnsnames
vim /u01/app/oracle/product/23.0.0/dbhome_1/network/admin/tnsnames.ora DBAAS1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.151)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbaas1) ) ) DBAAS2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.152)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbaas2) ) )
2.2 復制密碼文件
兩個CDB需要使用相同的密碼文件,在oracle239-01節(jié)點執(zhí)行:
scp /u01/app/oracle/product/23.0.0/dbhome_1/dbs/orapwdbaas1 oracle239-02:/u01/app/oracle/product/23.0.0/dbhome_1/dbs/orapwdbaas2
2.3 數(shù)據(jù)庫配置
兩個CDB均需要執(zhí)行:
alter system set dg_broker_start=true;
alter system set standby_file_management=auto;
alter database flashback on;
alter database force logging;
不同CDB執(zhí)行:
dbaas1:
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dbaas1' scope=both;
dbaas2:
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dbaas2' scope=both;


2.4 創(chuàng)建DG配置
dgmgrl sys@dbaas1
DGMGRL> CREATE CONFIGURATION 'dgconf_1' AS PRIMARY DATABASE IS 'dbaas1' CONNECT IDENTIFIER IS dbaas1;

dgmgrl sys@dbaas2
DGMGRL> CREATE CONFIGURATION 'dgconf_2' AS PRIMARY DATABASE IS 'dbaas2' CONNECT IDENTIFIER IS dbaas2;

2.5 添加(關聯(lián))DG配置
dgmgrl sys@dbaas1
DGMGRL> ADD CONFIGURATION 'dgconf_2' CONNECT IDENTIFIER IS dbaas2;

2.6 啟用DG配置并檢查
DGMGRL> ENABLE CONFIGURATION ALL;


2.7 啟用DG PDB
DGMGRL> EDIT CONFIGURATION PREPARE DGPDB;

2.8 創(chuàng)建源PDB的DG配置
DGMGRL> ADD PLUGGABLE DATABASE 'proddg' AT dbaas2 SOURCE is 'proddb' AT dbaas1;

2.8 復制數(shù)據(jù)文件
檢查dbaas1中proddg的文件編號:
select file# from v$datafile;

rman target sys@dbaas1 auxiliary sys@dbaas2
run {
allocate channel ch1 type disk;
backup as copy reuse datafile 12,13,14 auxiliary format NEW;
}

這里需要明確恢復文件的路徑與文件名。
2.9 修改proddg數(shù)據(jù)文件路徑與文件名
檢查數(shù)據(jù)文件:
alter session set container=proddg;
select name from v$datafile;

修改數(shù)據(jù)文件至正確路徑與文件名:
alter database rename file '/u01/app/oracle/oradata/DBAAS1/3C8E6114F10BDF09E063970A0A0AA6F6/datafile/o1_mf_system_nb3dy189_.dbf' to '/u01/app/oracle/oradata/DBAAS2/3C8E6114F10BDF09E063970A0A0AA6F6/datafile/o1_mf_system_0a0te910_.dbf';
alter database rename file '/u01/app/oracle/oradata/DBAAS1/3C8E6114F10BDF09E063970A0A0AA6F6/datafile/o1_mf_sysaux_nb3dy18j_.dbf' to '/u01/app/oracle/oradata/DBAAS2/3C8E6114F10BDF09E063970A0A0AA6F6/datafile/o1_mf_sysaux_09pse90p_.dbf';
alter database rename file '/u01/app/oracle/oradata/DBAAS1/3C8E6114F10BDF09E063970A0A0AA6F6/datafile/o1_mf_undotbs1_nb3dy18j_.dbf' to '/u01/app/oracle/oradata/DBAAS2/3C8E6114F10BDF09E063970A0A0AA6F6/datafile/o1_mf_undotbs1_0b1te911_.dbf';

2.10 proddg添加standby logfile
alter session set container=proddg;
ALTER DATABASE ADD STANDBY LOGFILE thread 1
group 4 size 200M,
group 5 size 200M,
group 6 size 200M,
group 7 size 200M;

2.11 驗證proddg
DGMGRL> VALIDATE PLUGGABLE DATABASE proddg AT dbaas2;

2.12 啟動日志同步
DGMGRL> EDIT PLUGGABLE DATABASE proddg AT dbaas2 SET STATE='APPLY-ON';

在dbaas1中執(zhí)行,可追平日志傳輸:
ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM ARCHIVE LOG CURRENT;

2.13 開啟proddg
alter pluggable database proddg open;


2.14 同步測試
proddb執(zhí)行:
create tablespace users datafile size 50m;
create table test (id number,name varchar2(20)) tablespace users;
insert into test values(1,'sky');
commit;

proddg檢查:

3 DG PDB切換
DGMGRL> SWITCHOVER TO PLUGGABLE DATABASE proddg AT dbaas2;

因為PRODDB中未添加standby logfile,無法正常同步,所以需要添加standby logfile:
DGMGRL> EDIT PLUGGABLE DATABASE proddb AT dbaas1 SET STATE='APPLY-OFF';
alter session set container=proddb;
ALTER DATABASE ADD STANDBY LOGFILE thread 1
group 4 size 200M,
group 5 size 200M,
group 6 size 200M,
group 7 size 200M;

DGMGRL> EDIT PLUGGABLE DATABASE proddb AT dbaas1 SET STATE='APPLY-ON';
dbaas2執(zhí)行:
ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM ARCHIVE LOG CURRENT;
同步恢復正常:

4 Failover
DGMGRL> FAILOVER TO PLUGGABLE DATABASE proddb AT dbaas1;

重新開啟同步即可恢復proddg:
DGMGRL> reinstate pluggable database proddg at dbaas2;
DGMGRL> EDIT PLUGGABLE DATABASE proddg AT dbaas2 SET STATE='APPLY-ON';

5 監(jiān)控DG PDB
5.1 監(jiān)控configuration
DGMGRL> show configuration
DGMGRL> show configuration verbose dgconf_1
DGMGRL> show configuration verbose dgconf_2



5.2 監(jiān)控CDB
DGMGRL> show database dbaas1
DGMGRL> show database verbose dbaas2


5.3 監(jiān)控PDB
DGMGRL> show pluggable database proddb at dbaas1;
DGMGRL> show pluggable database proddg at dbaas2;
DGMGRL> show all pluggable database at dbaas1;

5.4 驗證DG PDB
DGMGRL> validate pluggable database proddb at dbaas1; DGMGRL> validate pluggable database proddg at dbaas2; DGMGRL> validate pluggable database pdb1 at dbaas1;

6 刪除DG PDB
這里僅展示命令,不做演示。
dbaas1:
dgmgrl sys@dbaas1
DGMGRL> REMOVE PLUGGABLE DATABASE proddg AT dbaas2 REMOVE DATAFILES; DGMGRL> REMOVE CONFIGURATION dgconf_2; DGMGRL> REMOVE CONFIGURATION;
dbaas2:
dgmgrl sys@dbaas1
DGMGRL> REMOVE CONFIGURATION;
總結
本期在Oracle 23ai最新內測版23.9上進行了完整的DG PDB展示。
老規(guī)矩,知道寫了些啥
最后修改時間:2025-08-18 10:14:03
「喜歡這篇文章,您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創(chuàng)內容,轉載時必須標注文章的來源(墨天輪),文章鏈接,文章作者等基本信息,否則作者和墨天輪有權追究責任。如果您發(fā)現(xiàn)墨天輪中有涉嫌抄襲或者侵權的內容,歡迎發(fā)送郵件至:contact@modb.pro進行舉報,并提供相關證據(jù),一經(jīng)查實,墨天輪將立刻刪除相關內容。




