• Oracle-Dataguard-CDB指定PDB同步

    前言:

    CDB容器数据库在部署Dataguard备库时，有时我们只想同步指定的PDB排除哪些不重要的或者数据量大的PDB，这种方式Dataguard是可以支持实现的，但需要通过参数设置以及专门的部署步骤。

    本文接下来将主要介绍如何通过参数设置以及专门的部署步骤实现CDB容器数据库在配置DG备库时只同步指定的PDB。

    数据库环境：

    ​db_roleipuniq_nameroot_dbpdb
    primary192.168.146.2orclCDB$ROOT

    PDB$SEED

    pdb

    pdb1

    pdb3

    standby192.168.146.3orcldgCDB$ROOTpdb1

    部署步骤:

    1    检查数据库开启归档以及force_logging

    1. ---数据库开启归档模式
    2. archive log list 
    3. ---对于cdb容器数据库，该参数在cdb容器设置即可
    4. select force_logging from v$database;
    5. alter database force logging;

    2    检查并开启SYS远程登录

    1. ---确认参数remote_login_passwordfile为EXCLUSIVE,对于cdb容器数据库，该参数在cdb容器设置即可
    2. show parameter remote_login_passwordfile

    3    配置主备TNS

    1. ---主库tns（每个节点都设置）
    2. orcl =
    3.  (DESCRIPTION =
    4.  (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.146.2)(PORT = 1521))
    5.   (CONNECT_DATA =
    6.       (SERVER = DEDICATED)
    7.       (SERVICE_NAME = orcl)
    8.     )
    9.    ) 
    10. ---备库tns（每个节点都设置）
    11. orcldg=
    12.   (DESCRIPTION =
    13.   (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.146.3)(PORT = 1521))
    14.     (CONNECT_DATA =
    15.       (SERVER = DEDICATED)
    16.       (SERVICE_NAME = orcldg)
    17.      )
    18.   )

    4    主库参数配置

    1. ---备份spfile
    2. create pfile='/tmp/initorcl.bak' from spfile;  
    3. ---设置当前DG主备库对应唯一名
    4. alter system set log_archive_config='dg_config=(orcl,orcldg)' scope=both  sid='*';
    5. ---设置主库到备库的投递链路
    6. alter system set log_archive_dest_2=
    7. 'service=orcldg LGWR ASYNC NOAFFIRM delay=0 optional 
    8.  compression=disable max_failure=0  reopen=30 
    9.  net_timeout=300 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) 
    10.  db_unique_name=orcldg' scope=both  sid='*';
    11. ---设置捕获归档日志的对应的主备客户端TNS
    12. alter system set fal_client=orcl scope=both sid='*'; 
    13. alter system set fal_server=orcldg scope=both sid='*'; 
    14. ---设置备库文件自动管理，以确保新增数据文件能同步到备库
    15. alter system set standby_file_management=AUTO scope=both sid='*';
    16. ---设置omf文件路径，可以确保主备数据数据文件存放路径不一致时，备库可通过omf自动在新路径生成数据文件
    17. alter system set db_create_file_dest='/u01/app/oracle/oradata' scope=both sid='*';

    5    文件配置

    1. ---把主库的密码文件远程拷贝到备库的$ORACLE_HOME/dbs下面，再修改成orapw$SID的方式
    2. scp -rp $ORACLE_HOME/dbs/orapworcl oracle@192.168.146.3:$ORACLE_HOME/dbs/orapworcldg
    3. ---把主库新转出的pfile文件远程拷贝到备库的$ORACLE_HOME/dbs下面
    4. create pfile='/tmp/initorcldg.ora' from spfile;
    5. scp -rp /tmp/initorcldg.ora oracle@192.168.146.3:$ORACLE_HOME/dbs/initorcldg.ora

    6    备库参数配置

    1. ---备库修改从主库拷贝过来的参数文件
    2. ---参数文件修改
    3. *.audit_file_dest=/u01/app/oracle/admin/orcldg/adump
    4. *.control_files='/u01/app/oracle/oradata/ORCLDG/controlfile/control01.ctl'
    5. *.dispatchers='(PROTOCOL=TCP) (SERVICE=orcldgXDB)'
    6. *.fal_client='ORCLDG'
    7. *.fal_server='ORCL'
    8. *.log_archive_dest_2='service=orcl LGWR ASYNC NOAFFIRM delay=0 optional
    9.  compression=disable max_failure=0  reopen=30
    10.  net_timeout=300 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)
    11.  db_unique_name=orcl'
    12. *.standby_file_management='AUTO'
    13. *.log_archive_dest_1='/u01/app/oracle/archive'
    14. ---内存参数，进程数，根据实际需求按比例减
    15. *.sga_max_size
    16. *.shared_pool_size
    17. *.db_cache_size
    18. *.large_pool_size
    19. *.pga_aggregate_target
    20. *.processes
    21. *.sessions
    22. ---添加参数
    23. *.db_unique_name=orcldg
    24. #添加要DG同步的pdb名称
    25. *.ENABLED_PDBS_ON_STANDBY=pdb1

    7    备库目录创建

    1. su - oracle 
    2. mkdir -p /u01/app/oracle/admin/orcldg/adump
    3. mkdir -p /u01/app/oracle/oradata/ORCLDG/controlfile
    4. mkdir -p /u01/app/oracle/oradata/ORCLDG/datafile
    5. mkdir -p /u01/app/oracle/oradata/ORCLDG/onlinelog
    6. mkdir -p /u01/app/oracle/oradata/ORCLDG/tempfile

    8    启动备库实例

    1. ---nomount启动备库实例
    2. export ORACLE_SID=orcldg 
    3. sqlplus / as sysdba
    4. startup nomount 
    5. ---生成spfile文件
    6. create spfile from pfile;
    7. shutdown immediate;
    8. startup nomount;

    9    备份根容器root以及要同步的pdb

    1. backup format '/home/oracle/backup/root_%U' database  root ;
    2. backup format '/home/oracle/backup/pdb_%U' pluggable database pdb1 plus archivelog;
    3. ---将备份拷贝到备库相同目录下
    4. scp -rp /home/oracle/backup oracle@192.168.146.3:/home/oracle/

    10    备库控制文件standby controlfile

    1. alter database create standby controlfile as '/tmp/controlstd.ctl';
    2. ---把主库的控制文件远程拷贝到备库控制文件路径的下面
    3. scp -rp /tmp/controlstd.ctl oracle@192.168.146.3:/u01/app/oracle/oradata/ORCLDG/controlfile/control01.ctl

    11    将备库启动到mount

    alter database mount;

    12    进行全备恢复

    1. select con_id,name  from v$pdbs
    2. ​
    3.     CON_ID NAME
    4. ---------- --------------------------------------------------------------------------------------------------------------------------------
    5.    2 PDB$SEED
    6.    3 PDB
    7.    4 PDB1
    8.    6 PDB3
    9. ---批量生成根容器root(con_id=1)以及pdb容器pdb1(con_id=4)的路径修改命令
    10. set pagesize 0
    11. set linesize 200
    12. select 'set newname for datafile '||file#||' to '''||replace(name,'ORCL','ORCLDG')||''';' from v$datafile where con_id in (1,4);
    13. ​
    14. set pagesize 0
    15. set linesize 200
    16. select 'set newname for tempfile '||file#||' to '''||replace(name,'ORCL','ORCLDG')||''';' from v$tempfile where con_id in (1,4);
    17. ​
    18. set newname for datafile 1 to '/u01/app/oracle/oradata/ORCLDG/datafile/o1_mf_system_khpomwxx_.dbf';
    19. set newname for datafile 3 to '/u01/app/oracle/oradata/ORCLDG/datafile/o1_mf_sysaux_khponp2n_.dbf';
    20. set newname for datafile 4 to '/u01/app/oracle/oradata/ORCLDG/datafile/o1_mf_undotbs1_khpoo54z_.dbf';
    21. set newname for datafile 7 to '/u01/app/oracle/oradata/ORCLDG/datafile/o1_mf_users_khpoo66v_.dbf';
    22. set newname for datafile 13 to '/u01/app/oracle/oradata/ORCLDG/E658EB1E89977B15E0536401A8C0D7D3/datafile/o1_mf_system_khppx4x7_.dbf';
    23. set newname for datafile 14 to '/u01/app/oracle/oradata/ORCLDG/E658EB1E89977B15E0536401A8C0D7D3/datafile/o1_mf_sysaux_khppx4xg_.dbf';
    24. set newname for datafile 15 to '/u01/app/oracle/oradata/ORCLDG/E658EB1E89977B15E0536401A8C0D7D3/datafile/o1_mf_undotbs1_khppx4xh_.dbf';
    25. set newname for datafile 16 to '/u01/app/oracle/oradata/ORCLDG/E658EB1E89977B15E0536401A8C0D7D3/datafile/o1_mf_users_khppxbgj_.dbf';
    26. set newname for tempfile 1 to '/u01/app/oracle/oradata/ORCLDG/datafile/o1_mf_temp_khpoppd2_.tmp';
    27. set newname for tempfile 4 to '/u01/app/oracle/oradata/ORCLDG/E658EB1E89977B15E0536401A8C0D7D3/datafile/o1_mf_temp_khppx4xh_.dbf';
    28. ​
    29. ---语句批量生成跳过表空间
    30. set pagesize 400
    31. set linesize 400
    32. select listagg('"'||b.name||'"'||':'||a.name||',')
    33. from v$tablespace a,v$containers b
    34. where a.con_id=b.con_id and b.name not in ('CDB$ROOT','PDB1');
    35. ​
    36. "PDB$SEED":SYSTEM,"PDB$SEED":SYSAUX,"PDB$SEED":UNDOTBS1,"PDB$SEED":TEMP,"PDB":SYSAUX,"PDB":SYSTEM,"PDB":UNDOTBS1,"PDB":USERS,"PDB":TEMP,"PDB3":SYSTEM,"PDB3":UNDOTBS1,"PDB3":SYSAUX,"PDB3":TEMP,
    37. ---创建备份目录
    38. mkdir -p /u01/app/oracle/oradata/ORCLDG/datafile
    39. mkdir -p /u01/app/oracle/oradata/ORCLDG/E658EB1E89977B15E0536401A8C0D7D3/datafile
    40. ---进行备份恢复
    41. rman target / 
    42. run
    43. {
    44. set newname for datafile 1 to '/u01/app/oracle/oradata/ORCLDG/datafile/o1_mf_system_khpomwxx_.dbf';
    45. set newname for datafile 3 to '/u01/app/oracle/oradata/ORCLDG/datafile/o1_mf_sysaux_khponp2n_.dbf';
    46. set newname for datafile 4 to '/u01/app/oracle/oradata/ORCLDG/datafile/o1_mf_undotbs1_khpoo54z_.dbf';
    47. set newname for datafile 7 to '/u01/app/oracle/oradata/ORCLDG/datafile/o1_mf_users_khpoo66v_.dbf';
    48. set newname for datafile 13 to '/u01/app/oracle/oradata/ORCLDG/E658EB1E89977B15E0536401A8C0D7D3/datafile/o1_mf_system_khppx4x7_.dbf';
    49. set newname for datafile 14 to '/u01/app/oracle/oradata/ORCLDG/E658EB1E89977B15E0536401A8C0D7D3/datafile/o1_mf_sysaux_khppx4xg_.dbf';
    50. set newname for datafile 15 to '/u01/app/oracle/oradata/ORCLDG/E658EB1E89977B15E0536401A8C0D7D3/datafile/o1_mf_undotbs1_khppx4xh_.dbf';
    51. set newname for datafile 16 to '/u01/app/oracle/oradata/ORCLDG/E658EB1E89977B15E0536401A8C0D7D3/datafile/o1_mf_users_khppxbgj_.dbf';
    52. set newname for tempfile 1 to '/u01/app/oracle/oradata/ORCLDG/datafile/o1_mf_temp_khpoppd2_.tmp';
    53. set newname for tempfile 4 to '/u01/app/oracle/oradata/ORCLDG/E658EB1E89977B15E0536401A8C0D7D3/datafile/o1_mf_temp_khppx4xh_.dbf';
    54. restore database root;
    55. restore pluggable database PDB1;
    56. switch datafile all;
    57. switch tempfile all;
    58. recover database skip forever tablespace "PDB$SEED":SYSTEM,"PDB$SEED":SYSAUX,"PDB$SEED":UNDOTBS1,"PDB$SEED":TEMP,"PDB":SYSAUX,"PDB":SYSTEM,"PDB":UNDOTBS1,"PDB":USERS,"PDB":TEMP,"PDB3":SYSTEM,"PDB3":UNDOTBS1,"PDB3":SYSAUX,"PDB3":TEMP;
    59. }

    13    添加standby log

    1. alter database ADD standby logfile thread 1 group 21 size 200M ;
    2. alter database ADD standby logfile thread 1 group 22 size 200M ; 
    3. alter database ADD standby logfile thread 1 group 23 size 200M ;

    14    打开根容器

    alter database open;

    15    禁用其他容器(除了root,pdb1)的恢复

    1. alter session set container=PDB$SEED;
    2. alter pluggable database PDB$SEED disable recovery;
    3. alter session set container=PDB;
    4. alter pluggable database  PDB disable recovery;
    5. alter session set container=PDB3;
    6. alter pluggable database PDB3 disable recovery;

    16    打开容器pdb1

    alter pluggable database pdb1 open;

    17    启动mrp进程

    alter database recover managed standby database using current logfile disconnect from session parallel 8;

    18    检查备库同步

    1. ---实时同步中
    2.  select * from v$dataguard_stats
    3. ​
    4. SOURCE_DBID SOURCE_DB_UNIQUE_NAME       NAME            VALUE                     UNIT        TIME_COMPUTED         DATUM_TIME       CON_ID
    5. ----------- -------------------------------- -------------------------------- ---------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ----------
    6.  1640672463 orcl           transport lag          +00 00:00:00                   day(2) to second(0) interval   08/19/2022 22:51:10       08/19/2022 22:51:09         0
    7.  1640672463 orcl           apply lag            +00 00:00:00                   day(2) to second(0) interval   08/19/2022 22:51:10       08/19/2022 22:51:09         0
    8.  1640672463 orcl           apply finish time                           day(2) to second(3) interval   08/19/2022 22:51:10                 0
    9.     0                     estimated startup time        13                     second      08/19/2022 22:51:10                 0

    19    测试数据同步

    1. ---在主库pdb1创建表
    2. SQL> select database_role from v$database;
    3. ​
    4. DATABASE_ROLE
    5. ----------------
    6. PRIMARY
    7. SQL> alter session set container=pdb1;
    8. Session altered.
    9. ​
    10. ​
    11. SQL> create table test as select * from dba_objects;
    12. ​
    13. Table created.
    14. ​
    15. ---在备库可以查到
    16. SQL> select database_role from v$database;
    17. ​
    18. DATABASE_ROLE
    19. ----------------
    20. PHYSICAL STANDBY
    21. ​
    22. SQL> alter session set container=pdb1;
    23. ​
    24. Session altered.
    25. ​
    26. SQL> select count(*) from test;
    27. ​
    28.   COUNT(*)
    29. ----------
    30.      72356
    31. ​
    32. ---在主库pdb3创建表，备库没有同步，并且同步状态正常

  • 相关阅读:
    strcspn、strchr特殊字符校验
    接口自动化测试框架【reudom】
    DMA方式
    浅谈Spring Cloud Gateway源码
    Java进阶——IO 流
    Leetcode-206 反转链表
    苹果“欠”用户的承诺，何时会兑现？
    【Spring IOC/DI】bean 的 5 种注册 与 5 种注入
    《昇思25天学习打卡营第17天|K近邻算法实现红酒聚类》
    RabbitMQ学习总结（11）—— RabbitMQ 核心概念与架构
  • 原文地址：https://blog.csdn.net/sinat_36757755/article/details/126415701