数据库管理-第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建库过程中已按照默认方式完成以下配置：

• 创建空CDB（无PDB）
• 开启OMF
• 开启指定FRA
• 开启归档日志

后续在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展示。
老规矩，知道写了些啥