Focus On Oracle,MySQL,SQL Server and PostgreSQL..

Oracle refresh pdb功能的测试和应用场景

—

由

dbsupport
于 , ,

Oracle 12.2引入的refresh pdb(可刷新 PDB)特性一直以来没有进行过测试，刚好今天有个项目可能涉及到，因此做一下相关测试。

我这里使用了Oracle 19.14版本进行了测试。

首先在源端cdb创建用于克隆刷新的账户：

+++源端
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TDEPDB                         READ WRITE NO
         4 PDB01                          READ WRITE NO
         5 JYZ                            READ WRITE NO
SQL>
SQL>
SQL> create user c##sync identified by enmotech123 container=all;

User created.

SQL> grant create session,sysoper to c##sync container=all;

Grant succeeded.

SQL>

接下来在目标端创建DBLINK，然后创建refresh pdb：

+++目标端
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ROGER                          READ WRITE NO
SQL> create public database link to_db19rac connect to c##sync identified by enmotech123 using '//192.168.11.5/ORA19C';

Database link created.

SQL> select sysdate from dual@to_db19rac;

SYSDATE
---------
24-MAR-22

SQL>
SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';

Session altered.

SQL> create pluggable database r_pdb from pdb01@to_db19rac refresh mode every 1 minutes create_file_dest='+data';

Pluggable database created.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ROGER                          READ WRITE NO
         4 R_PDB                          MOUNTED
SQL>

备注：我这里2个CDB都在同一个RAC环境中.

这里我们可以来观察一下创建可刷新pdb的alert log日志过程：

2022-03-24T19:08:45.061887+08:00
create pluggable database r_pdb from pdb01@to_db19rac refresh mode every 1 minutes create_file_dest='+data'
2022-03-24T19:08:50.128305+08:00
R_PDB(4):Endian type of dictionary set to little
****************************************************************
Pluggable Database R_PDB with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x0000000000000118
****************************************************************
2022-03-24T19:08:53.189298+08:00
Applying media recovery for pdb-4099 from SCN 59694602 to SCN 59694634
Remote log information: count-2
thr-2,seq-1670,logfile-+ARCH/ORA19C/partial_archivelog/2022_03_24/thread_2_seq_1670.1844.1100200133,los-59687554,nxs-18446744073709551615,maxblks-561
thr-1,seq-1678,logfile-+ARCH/ORA19C/partial_archivelog/2022_03_24/thread_1_seq_1678.1842.1100200133,los-59687463,nxs-18446744073709551615,maxblks-572
R_PDB(4):Media Recovery Start
2022-03-24T19:08:53.195220+08:00
R_PDB(4):Serial Media Recovery started
R_PDB(4):max_pdb is 4
2022-03-24T19:08:53.283193+08:00
R_PDB(4):Media Recovery Log +ARCH/ORA19C/partial_archivelog/2022_03_24/thread_1_seq_1678.1842.1100200133
2022-03-24T19:08:53.339386+08:00
R_PDB(4):Media Recovery Log +ARCH/ORA19C/partial_archivelog/2022_03_24/thread_2_seq_1670.1844.1100200133
2022-03-24T19:08:53.366976+08:00
ALTER SYSTEM SET remote_listener=' ora19c-cluster:1521' SCOPE=MEMORY SID='enmotech';
2022-03-24T19:08:53.367982+08:00
ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='enmotech';
2022-03-24T19:08:53.399832+08:00
R_PDB(4):Incomplete Recovery applied until change 59694634 time 03/24/2022 19:08:50
2022-03-24T19:08:53.400469+08:00
R_PDB(4):Media Recovery Complete (enmotech)
Completed: create pluggable database r_pdb from pdb01@to_db19rac refresh mode every 1 minutes create_file_dest='+data'
2022-03-24T19:08:53.573507+08:00
R_PDB(4):alter pluggable database refresh
2022-03-24T19:08:56.178497+08:00
Applying media recovery for pdb-4099 from SCN 59694634 to SCN 59694667
Remote log information: count-2
thr-2,seq-1670,logfile-+ARCH/ORA19C/partial_archivelog/2022_03_24/thread_2_seq_1670.1842.1100200137,los-59687554,nxs-18446744073709551615,maxblks-567
thr-1,seq-1678,logfile-+ARCH/ORA19C/partial_archivelog/2022_03_24/thread_1_seq_1678.1844.1100200137,los-59687463,nxs-18446744073709551615,maxblks-582
R_PDB(4):Media Recovery Start
2022-03-24T19:08:56.181352+08:00
R_PDB(4):Serial Media Recovery started
R_PDB(4):max_pdb is 4
2022-03-24T19:08:56.265942+08:00
R_PDB(4):Media Recovery Log +ARCH/ORA19C/partial_archivelog/2022_03_24/thread_2_seq_1670.1842.1100200137
2022-03-24T19:08:56.336048+08:00
R_PDB(4):Media Recovery Log +ARCH/ORA19C/partial_archivelog/2022_03_24/thread_1_seq_1678.1844.1100200137
2022-03-24T19:08:56.346523+08:00
ALTER SYSTEM SET remote_listener=' ora19c-cluster:1521' SCOPE=MEMORY SID='enmotech';
2022-03-24T19:08:56.347355+08:00
ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='enmotech';
2022-03-24T19:08:56.396730+08:00
R_PDB(4):Incomplete Recovery applied until change 59694667 time 03/24/2022 19:08:54
2022-03-24T19:08:56.397389+08:00
R_PDB(4):Media Recovery Complete (enmotech)
R_PDB(4):Completed: alter pluggable database refresh
2022-03-24T19:09:53.648347+08:00
R_PDB(4):alter pluggable database refresh
2022-03-24T19:09:56.235790+08:00
Applying media recovery for pdb-4099 from SCN 59694667 to SCN 59697140
Remote log information: count-2
thr-2,seq-1670,logfile-+ARCH/ORA19C/partial_archivelog/2022_03_24/thread_2_seq_1670.1842.1100200197,los-59687554,nxs-18446744073709551615,maxblks-803
thr-1,seq-1678,logfile-+ARCH/ORA19C/partial_archivelog/2022_03_24/thread_1_seq_1678.1844.1100200197,los-59687463,nxs-18446744073709551615,maxblks-987
R_PDB(4):Media Recovery Start
2022-03-24T19:09:56.238828+08:00
R_PDB(4):Serial Media Recovery started
R_PDB(4):max_pdb is 4
2022-03-24T19:09:56.335501+08:00
R_PDB(4):Media Recovery Log +ARCH/ORA19C/partial_archivelog/2022_03_24/thread_2_seq_1670.1842.1100200197
2022-03-24T19:09:56.385955+08:00
R_PDB(4):Media Recovery Log +ARCH/ORA19C/partial_archivelog/2022_03_24/thread_1_seq_1678.1844.1100200197
2022-03-24T19:09:56.404380+08:00
ALTER SYSTEM SET remote_listener=' ora19c-cluster:1521' SCOPE=MEMORY SID='enmotech';
2022-03-24T19:09:56.405068+08:00
ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='enmotech';
2022-03-24T19:09:56.440219+08:00
R_PDB(4):Incomplete Recovery applied until change 59697140 time 03/24/2022 19:09:53
2022-03-24T19:09:56.440854+08:00
R_PDB(4):Media Recovery Complete (enmotech)
R_PDB(4):Completed: alter pluggable database refresh

从上述日志来看,Oracle这里是将源库CDB中的pdb数据文件全部拉到了本地，然后开始做archivelog的不完全恢复。

接下来我们来在主库插入测试验证一下可刷新pdb的可靠性：

+++主库创建测试表并插入数据
SQL>   alter session set container=PDB01;

Session altered.

SQL> create table test0324 as select * from dba_objects;

Table created.

SQL> insert into test0324 select * from test0324;

72968 rows created.

SQL> /

145936 rows created.

SQL> commit;

Commit complete.

SQL> select count(1) from test0324;

  COUNT(1)
----------
    291872

SQL>insert into test0324 select * from test0324;

291872 rows created.

SQL> commit

Commit complete.

SQL> select count(1) from test0324;

  COUNT(1)
----------
    583744

SQL>

 

由于我前面创建的时候指定了自动刷新，并且刷新同步频率为1分钟，那我们来观察一下alert log日志，看下同步情况：

2022-03-24T19:13:56.594231+08:00
R_PDB(4):Serial Media Recovery started
R_PDB(4):max_pdb is 4
2022-03-24T19:13:56.676074+08:00
R_PDB(4):Media Recovery Log +ARCH/ORA19C/partial_archivelog/2022_03_24/thread_2_seq_1670.1842.1100200437
2022-03-24T19:13:56.738322+08:00
R_PDB(4):Media Recovery Log +ARCH/ORA19C/partial_archivelog/2022_03_24/thread_1_seq_1678.1844.1100200437
2022-03-24T19:13:56.792853+08:00
ALTER SYSTEM SET remote_listener=' ora19c-cluster:1521' SCOPE=MEMORY SID='enmotech';
2022-03-24T19:13:56.794760+08:00
ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='enmotech';
R_PDB(4):Resize operation completed for file# 13, fname +DATA/ENMOTECH/DAF5D2B2EC6F5D33E053050BA8C05FC4/DATAFILE/system.320.1100200127, old size 378880K, new size 389120K
R_PDB(4):Resize operation completed for file# 13, fname +DATA/ENMOTECH/DAF5D2B2EC6F5D33E053050BA8C05FC4/DATAFILE/system.320.1100200127, old size 389120K, new size 399360K
R_PDB(4):Resize operation completed for file# 13, fname +DATA/ENMOTECH/DAF5D2B2EC6F5D33E053050BA8C05FC4/DATAFILE/system.320.1100200127, old size 399360K, new size 409600K
R_PDB(4):Resize operation completed for file# 13, fname +DATA/ENMOTECH/DAF5D2B2EC6F5D33E053050BA8C05FC4/DATAFILE/system.320.1100200127, old size 409600K, new size 419840K
2022-03-24T19:13:58.206230+08:00
R_PDB(4):Resize operation completed for file# 13, fname +DATA/ENMOTECH/DAF5D2B2EC6F5D33E053050BA8C05FC4/DATAFILE/system.320.1100200127, old size 419840K, new size 430080K
2022-03-24T19:13:58.310663+08:00
R_PDB(4):Incomplete Recovery applied until change 59709802 time 03/24/2022 19:13:53
2022-03-24T19:13:58.311707+08:00
R_PDB(4):Media Recovery Complete (enmotech)
R_PDB(4):Completed: alter pluggable database refresh
 2022-03-24T19:14:53.453586+08:00
R_PDB(4):alter pluggable database refresh
2022-03-24T19:14:56.628745+08:00
Applying media recovery for pdb-4099 from SCN 59709802 to SCN 59716401
Remote log information: count-2
thr-2,seq-1670,logfile-+ARCH/ORA19C/partial_archivelog/2022_03_24/thread_2_seq_1670.1842.1100200497,los-59687554,nxs-18446744073709551615,maxblks-2455
thr-1,seq-1678,logfile-+ARCH/ORA19C/partial_archivelog/2022_03_24/thread_1_seq_1678.1844.1100200497,los-59687463,nxs-18446744073709551615,maxblks-95744
R_PDB(4):Media Recovery Start
2022-03-24T19:14:56.631334+08:00
R_PDB(4):Serial Media Recovery started
R_PDB(4):max_pdb is 4
2022-03-24T19:14:56.682717+08:00
R_PDB(4):Media Recovery Log +ARCH/ORA19C/partial_archivelog/2022_03_24/thread_2_seq_1670.1842.1100200497
2022-03-24T19:14:56.729346+08:00
R_PDB(4):Media Recovery Log +ARCH/ORA19C/partial_archivelog/2022_03_24/thread_1_seq_1678.1844.1100200497
2022-03-24T19:14:56.804749+08:00
ALTER SYSTEM SET remote_listener=' ora19c-cluster:1521' SCOPE=MEMORY SID='enmotech';
2022-03-24T19:14:56.807259+08:00
ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='enmotech';
2022-03-24T19:14:56.885570+08:00
R_PDB(4):Incomplete Recovery applied until change 59716401 time 03/24/2022 19:14:53
2022-03-24T19:14:56.886643+08:00
R_PDB(4):Media Recovery Complete (enmotech)
R_PDB(4):Completed: alter pluggable database refresh
 2022-03-24T19:15:53.496636+08:00
R_PDB(4):alter pluggable database refresh
2022-03-24T19:15:56.682139+08:00
Applying media recovery for pdb-4099 from SCN 59716401 to SCN 59723066
Remote log information: count-2
thr-2,seq-1670,logfile-+ARCH/ORA19C/partial_archivelog/2022_03_24/thread_2_seq_1670.1842.1100200557,los-59687554,nxs-18446744073709551615,maxblks-2835
thr-1,seq-1678,logfile-+ARCH/ORA19C/partial_archivelog/2022_03_24/thread_1_seq_1678.1844.1100200557,los-59687463,nxs-18446744073709551615,maxblks-96267
R_PDB(4):Media Recovery Start
2022-03-24T19:15:56.684987+08:00
R_PDB(4):Serial Media Recovery started
R_PDB(4):max_pdb is 4
2022-03-24T19:15:56.774190+08:00
R_PDB(4):Media Recovery Log +ARCH/ORA19C/partial_archivelog/2022_03_24/thread_2_seq_1670.1842.1100200557
2022-03-24T19:15:56.835653+08:00
R_PDB(4):Media Recovery Log +ARCH/ORA19C/partial_archivelog/2022_03_24/thread_1_seq_1678.1844.1100200557
2022-03-24T19:15:56.852785+08:00
ALTER SYSTEM SET remote_listener=' ora19c-cluster:1521' SCOPE=MEMORY SID='enmotech';
2022-03-24T19:15:56.853956+08:00
ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='enmotech';
2022-03-24T19:15:56.987793+08:00
R_PDB(4):Incomplete Recovery applied until change 59723066 time 03/24/2022 19:15:53
2022-03-24T19:15:56.988486+08:00
R_PDB(4):Media Recovery Complete (enmotech)
R_PDB(4):Completed: alter pluggable database refresh

我们可以看到r_pdb确实完成了相关的不完全日志恢复。

接下来我们打开这个pdb看下数据是否同步完成：

SQL>  alter pluggable database r_pdb open read only;

Pluggable database altered.

SQL> alter session set container=r_pdb;

Session altered.

SQL> select count(1) from test0324;

  COUNT(1)
----------
    583744

SQL> alter pluggable database r_pdb refresh mode every 1 minutes;

Pluggable database altered.

SQL> alter pluggable database r_pdb refresh;
alter pluggable database r_pdb refresh
*
ERROR at line 1:
ORA-65025: Pluggable database R_PDB is not closed on all instances.


SQL> alter pluggable database r_pdb close immediate;

Pluggable database altered.

SQL> alter pluggable database r_pdb refresh;

Pluggable database altered.

从上述测试来看我们可以看出，当进行refresh时，被刷新同步的pdb必须处于mount状态；当read only打开之后手工进行刷新会报错；同时观察alert log也会看到相关的信息：

2022-03-24T19:18:57.008156+08:00
R_PDB(4):Pluggable database R_PDB opening in read only
R_PDB(4):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
R_PDB(4):Autotune of undo retention is turned on.
R_PDB(4):Undo initialization finished serial:0 start:377283560 end:377283560 diff:0 ms (0.0 seconds)
R_PDB(4):Database Characterset for R_PDB is AL32UTF8
R_PDB(4):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog)
R_PDB(4):Opening pdb with no Resource Manager plan active
Pluggable database R_PDB opened read only
Completed: alter pluggable database r_pdb open read only
 2022-03-24T19:19:53.682945+08:00
R_PDB(4):alter pluggable database refresh
2022-03-24T19:19:53.683051+08:00
R_PDB(4):R_PDB(4):ERROR:PDB needs to be closed for auto refresh
R_PDB(4):Completed: alter pluggable database refresh

从整个测试来看，refresh pdb特性还是不错；可用于一些异地容灾（基于部分业务pdb等）；或者查询业务。

不过由于read only情况下无法进行刷新，因此可以结合定时任务等来实现定时刷新即可(如果对数据实时性要求不高的话)。

总的来说Oracle refresh pdb特性有一定的应用场景！

评论

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注