Oracle-DataGuard参数enabled_PDBs_on_standby禁用PDB同步

前言：

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进行同步

相关阅读:
产品外观设计公司怎么选？这篇文章告诉你
 2022-08-27 第五组张明敏学习笔记
 写好 Spring Starter : 控制好Bean的加载顺序与原理
 计算机的源头：二进制
 【混沌工程】混沌工程原理
 【小程序】IDEA实现qq邮件的发送
 面试--spring基础
 Java-文件操作
 SourceTree 使用
 hdlbits系列verilog解答（always块casez语句）-35
原文地址：https://blog.csdn.net/sinat_36757755/article/details/126442769