数据库管理-第358期 23ai：DG PDB（20250818）
作者：胖头鱼的鱼缸（尹海文） Oracle ACE Pro: Database PostgreSQL ACE Partner 10年数据库行业经验 拥有OCM 11g/12c/19c、MySQL 8.0 OCP、Exadata、CDP等认证 墨天轮MVP，ITPUB认证专家 圈内拥有“总监”称号，非著 名社恐（社交恐怖分子） 公众号：胖头鱼的鱼缸 CSDN：胖头鱼的鱼缸（尹海文） 墨天轮：胖头鱼的鱼缸 ITPUB：yhw1809 IFClub：胖头鱼的鱼缸 除授权转载并标明出处外，均为“非法”抄袭
在
《数据库管理-第141期 DG PDB - Oracle DB 23c（20240129）》中，在Oracle 23c（内测版23.4）上部署过一次DG PDB，趁着内测版本更新到了23.9，且相较于之前官方文档已经完善，因此也在23ai上也再次部署一下DG PDB。
1 环境
DBCA建库过程中已按照默认方式完成以下配置：
后续在dbaas1中创建对应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节点执行：
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 数据库配置
两个CDB均需要执行：
alter system set dg_broker_start=true; alter system set standby_file_management=auto; alter database flashback on; alter database force logging;
不同CDB执行：
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 创建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 添加（关联）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 创建源PDB的DG配置
DGMGRL> ADD PLUGGABLE DATABASE 'proddg' AT dbaas2 SOURCE is 'proddb' AT dbaas1;
2.8 复制数据文件
检查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数据文件路径与文件名
检查数据文件：
alter session set container=proddg; select name from v$datafile;
修改数据文件至正确路径与文件名：
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中执行，可追平日志传输：
ALTER SYSTEM ARCHIVE LOG CURRENT; ALTER SYSTEM ARCHIVE LOG CURRENT;
2.13 开启proddg
alter pluggable database proddg open;
2.14 同步测试
proddb执行：
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执行：
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 监控DG PDB
5.1 监控configuration
DGMGRL> show configuration DGMGRL> show configuration verbose dgconf_1 DGMGRL> show configuration verbose dgconf_2
5.2 监控CDB
DGMGRL> show database dbaas1 DGMGRL> show database verbose dbaas2
5.3 监控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展示。 老规矩，知道写了些啥
