Oracle-DataGuard参数enabled_PDBs_on_standby禁用PDB同步_oralce adg 先择性同步pdb-优快云博客

本文链接：https://blog.youkuaiyun.com/sinat_36757755/article/details/126442769

本文解析了enabled_PDBs_on_standby参数在DG主备同步中的作用，重点说明了如何配置以控制新pdb的同步，并揭示其对现有pdb同步的影响。通过实例展示了参数对新建pdb的限制和对已存在pdb的透明性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前言：

enabled_PDBs_on_standby参数用于在DG主备同步中指定允许同步到备库的pdb，那么如何使用该参数指定同步的pdb以及参数的作用范围是什么？

本文接下来的内容将对enabled_PDBs_on_standby参数的使用以及作用范围进行解析。

数据库环境:

db_role

uniq_n

root_db

pdb

primary

orcl

CDB$ROOT

PDB$SEED

pdb1

pdb2

standby

orcldg

CDB$ROOT

PDB$SEED

pdb1

pdb2

参数信息:

参数只在备库设置生效，默认值为*，即不限制pdb的同步，参数主要控制新建的pdb是否同步到备库，不作用已有pdb的同步

测试参数对已有pdb的同步是否存在作用:

1 在备库orcldg配置只允许pdb1进行同步

SQL> alter system set enabled_PDBs_on_standby=pdb1;

System altered.
SQL>
 show parameter enable

NAME             TYPE   VALUE
------------------------------------ ----------- ------------------------------
enabled_PDBs_on_standby        string   PDB1
SQL> 
---备库重启mrp进程
alter database recover managed standby database cancel;
alter database recover managed standby database using current logfile disconnect from session;

2 在主库orcl pdb2创建测试表数据

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY
SQL>
 alter session set container=pdb2;

Session altered.
SQL>
 
SQL> create table test20220820 as select * from dba_objects;

Table created.

3 在备库orcldg检查pdb2依然在进行数据同步

---可以查到同步过来的test20220820数据
SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> alter session set container=pdb2;

Session altered.

SQL> select count(*) from test20220820;

  COUNT(*)
----------
     72356

测试结果:enabled_PDBs_on_standby对已有备库的PDB同步不起限制作用

测试对新建pdb的限制作用:

1 在备库设置只允许pdb1的同步

SQL> alter system set enabled_PDBs_on_standby=pdb1;

System altered.

SQL> show parameter pdbs;

NAME             TYPE   VALUE
------------------------------------ ----------- ------------------------------
autotask_max_active_pdbs       integer   2
enabled_PDBs_on_standby        string   PDB1
max_pdbs           integer   254
target_pdbs           integer   2

注:create pluggable database standbys参与enabled_pdbs_on_standby的关系参考以下关系图

Case	Parameter : Value	Datafile in Standby (*1)	Recovery Status in Standby (*2)
1	STANDBYS : ALL enabled_pdbs_on_standby : Included in PDB-list	Created	Enable
2	STANDBYS : ALL enabled_pdbs_on_standby : Not included in PDB-list	Not Created	Disable
3	STANDBYS : NONE enabled_pdbs_on_standby : Included in PDB-list	Created	Enable
4	STANDBYS : NONE enabled_pdbs_on_standby : Not included in PDB-list	Not Created	Disable
5	STANDBYS : ALL enabled_pdbs_on_standby : *	Created	Enable
6	STANDBYS : NONE enabled_pdbs_on_standby : *	Not Created	Disable

2 在主库orcl创建pdb3数据库

---创建成功并open
SQL> create pluggable database PDB3 admin user pdbadmin identified by "pdbadmin" STANDBYS=ALL;

Pluggable database created.

SQL> alter pluggable database pdb3 open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME        OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
   2 PDB$SEED        READ ONLY  NO
   3 PDB3         READ WRITE NO
   4 PDB1         READ WRITE NO
   5 PDB2         READ WRITE NO
SQL>

3 查看备库orcldg的同步情况

---pdb3虽然可以在备库看到，但数据文件并没有进行恢复同步，是个空库
  1  select name,status
  2  from v$datafile
  3* where con_id=3

NAME                             STATUS
---------------------------------------------------------------------------------------------------- -------
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00049               SYSOFF
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00050               RECOVER
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00051               RECOVER

SQL> select name,status
  2  from v$datafile_header
  3  where con_id=3;

NAME                             STATUS
---------------------------------------------------------------------------------------------------- -------
                             OFFLINE
                             OFFLINE
                             OFFLINE
SQL> show pdbs;

    CON_ID CON_NAME        OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
   2 PDB$SEED        READ ONLY  NO
   3 PDB3         MOUNTED
   4 PDB1         READ ONLY  NO
   5 PDB2         READ ONLY  NO
SQL>

4 查看备库orcldg的后台日志

---可以看到对于pdb3的数据文件skipped for excluded/offline tablespace的提示
Recovery created pluggable database PDB3
PDB3(3):File copy for ts-SYSTEM skipped for excluded/offline tablespace
PDB3(3):File #49 added to control file as 'UNNAMED00049'. Originally created as:
PDB3(3):'/u01/app/oracle/oradata/ORCL/E6BDA43BAF9E31C0E0536402A8C0F9C8/datafile/o1_mf_system_kj3xczfk_.dbf'
PDB3(3):because the pluggable database was created with nostandby
PDB3(3):or the tablespace belonging to the pluggable database is
PDB3(3):offline.
2022-08-21T17:06:10.657633+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcldg/orcldg/trace/orcldg_dbw0_5805.trc:
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/u01/app/oracle/oradata/ORCL/datafile/temp012022-08-16_16-38-20-408-PM.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2022-08-21T17:06:10.661330+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcldg/orcldg/trace/orcldg_dbw0_5805.trc:
ORA-01186: file 202 failed verification tests
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/u01/app/oracle/oradata/ORCL/datafile/temp012022-08-16_16-38-20-408-PM.dbf'
2022-08-21T17:06:10.661425+08:00
File 202 not verified due to error ORA-01157
2022-08-21T17:06:10.707125+08:00
PDB3(3):File copy for ts-SYSAUX skipped for excluded/offline tablespace
PDB3(3):File #50 added to control file as 'UNNAMED00050'. Originally created as:
PDB3(3):'/u01/app/oracle/oradata/ORCL/E6BDA43BAF9E31C0E0536402A8C0F9C8/datafile/o1_mf_sysaux_kj3xczft_.dbf'
PDB3(3):because the pluggable database was created with nostandby
PDB3(3):or the tablespace belonging to the pluggable database is
PDB3(3):offline.

5 将备库orcldg设置为没有pdb同步限制*

SQL> alter system set enabled_PDBs_on_standby=*;

---主库再重新创建pdb3
SQL> create pluggable database PDB3 admin user pdbadmin identified by "pdbadmin" STANDBYS=ALL;
Pluggable database created.

SQL> SQL> alter pluggable database pdb3 open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME        OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
   2 PDB$SEED        READ ONLY  NO
   4 PDB1         READ WRITE NO
   5 PDB2         READ WRITE NO
   6 PDB3         READ WRITE NO

6 查看备库orcldg的pdb3同步情况

---pdb3可以在备库看到，数据文件也进行恢复同步
SQL> select name,status
  2  from v$datafile_header
  3  where con_id=6;

NAME                             STATUS
---------------------------------------------------------------------------------------------------- -------
/u01/app/oracle/oradata/ORCLDG/E6BDA43BAFA331C0E0536402A8C0F9C8/datafile/o1_mf_system_kj3y7944_.dbf  ONLINE
/u01/app/oracle/oradata/ORCLDG/E6BDA43BAFA331C0E0536402A8C0F9C8/datafile/o1_mf_sysaux_kj3y7945_.dbf  ONLINE
/u01/app/oracle/oradata/ORCLDG/E6BDA43BAFA331C0E0536402A8C0F9C8/datafile/o1_mf_undotbs1_kj3y7945_.db ONLINE

---pdb3可以正常打开
SQL> alter pluggable database pdb3 open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME        OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
   2 PDB$SEED        READ ONLY  NO
   4 PDB1         READ ONLY  NO
   5 PDB2         READ ONLY  NO
   6 PDB3         READ ONLY  NO

测试结果:enabled_PDBs_on_standby用于控制新建的pdb是否同步到备库，对于参数所配置允许的pdb，备库会自动对pdb进行同步

总结:

1 enabled_PDBs_on_standby对已有备库的PDB同步不起限制作用

2 enabled_PDBs_on_standby用于控制新建的pdb是否同步到备库，对于参数所配置允许的pdb，备库会自动对pdb进行同步