Oracle refresh pdb功能的测试和应用场景
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: Oracle refresh pdb功能的测试和应用场景
Oracle 12.2引入的refresh pdb(可刷新 PDB)特性一直以来没有进行过测试，刚好今天有个项目可能涉及到，因此做一下相关测试。
我这里使用了Oracle 19.14版本进行了测试。
首先在源端cdb创建用于克隆刷新的账户：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
+++源端 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：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
+++目标端 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日志过程：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
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的可靠性：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
+++主库创建测试表并插入数据 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日志，看下同步情况：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
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看下数据是否同步完成：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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也会看到相关的信息：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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特性有一定的应用场景！
Leave a Reply
You must be logged in to post a comment.