天达云 科技型企业 | 亚太互联网络信息中心(APNIC)成员 | 注册免费体验
天达云首页 > 互联网学习教程 > 关系型数据库 > oracle 12c中怎么搭建PDB refresh

oracle 12c中怎么搭建PDB refresh
更新：HHH   时间：2023-1-7

这篇文章主要讲解了“oracle 12c中怎么搭建PDB refresh”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“oracle 12c中怎么搭建PDB refresh”吧！

PDB Refresh是12C推出的新特性，可以对源端PDB进行增量同步，可以快速实现拷贝一份数据库镜像副本，有点类似运营商喜欢使用的EMC的BCV技术，存储层的复制功能。但是副本只能以read only打开，拷贝速度较慢，刷新走网络流量，实际应用场景较少。

PDB refresh源端与目标端可以在同一个cdb，也可以在不同的cdb中，刷新方式必须通过dblink。

PDB refresh搭建

数据库版本

SYS@cdbtest1(CDB$ROOT)> select banner   from v$version where rownum=1;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 12c Enterprise Edition   Release 12.2.0.1.0 - 64bit Production

 

pdb refresh有两个基本要求：归档和local undo

检查归档是否开启：

SYS@cdbtest1(CDB$ROOT)> archive log   list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /arch

Oldest online log sequence     42

Next log sequence to archive   45

Current log sequence           45

 

检查undo是否为local模式

SYS@cdbtest1(CDB$ROOT)> col   PROPERTY_NAME for a25

SYS@cdbtest1(CDB$ROOT)> col   PROPERTY_VALUE for a30

SYS@cdbtest1(CDB$ROOT)>  select PROPERTY_NAME,PROPERTY_VALUE from   database_properties where property_name='LOCAL_UNDO_ENABLED';

 

PROPERTY_NAME             PROPERTY_VALUE

-------------------------   ------------------------------

LOCAL_UNDO_ENABLED        TRUE

 

当前pdb信息

SYS@cdbtest1(CDB$ROOT)> show  pdbs

 

      CON_ID CON_NAME                         OPEN MODE  RESTRICTED

---------- ------------------------------   ---------- ----------

           2 PDB$SEED                         READ ONLY  NO

           3 CLONEMING                        READ WRITE NO

           4 MING                           READ WRITE NO

               

快速刷新pdb的数据文件存放位置跟普通pdb有点区别，dba_data_files是查不到的，需要用v$dbfile视图。

select name from v$dbfile;

 

NAME

----------------------------------------------------------------------------------------------------

/oradata/cdb/CDBTEST1/79C426B26B8C175BE053023DA8C0C3CA/datafile/o1_mf_system_fxvbwmck_.dbf

/oradata/cdb/CDBTEST1/79C426B26B8C175BE053023DA8C0C3CA/datafile/o1_mf_sysaux_fxvbwmcp_.dbf

/oradata/cdb/CDBTEST1/79C426B26B8C175BE053023DA8C0C3CA/datafile/o1_mf_test_fxvbwmcq_.dbf

/oradata/cdb/CDBTEST1/79C426B26B8C175BE053023DA8C0C3CA/datafile/o1_mf_test_fxvbwmcr_.dbf

/oradata/cdb/CDBTEST1/79C426B26B8C175BE053023DA8C0C3CA/datafile/o1_mf_undo_1_fxvbwmcr_.dbf

它的数据文件是放在cdb目录下的

               

以MING为源端pdb，需要创建到该cdb的dblink

SYS@cdbtest1(CDB$ROOT)> create   database link dblk_pdbming connect to system identified by "oracle"   using 'pdbming';

 

Database link created.

 

create database link dblk_pdbming connect   to system identified by "oracle" using 'pdbming';

 

SYS@cdbtest1(CDB$ROOT)>  select sysdate from dual@dblk_pdbming;

 

SYSDATE

------------

03-NOV-18

 

创建手动刷新refresh pdb

create pluggable   database mingdev1 from ming@dblk_pdbming refresh mode manual;

 

alert日志

create pluggable database mingdev1 from   ming@dblk_pdbming refresh mode manual

2018-11-03T22:07:14.174103+08:00

MING(4):Opatch XML is skipped for PDB   MING (conid=4)

MING(4): AUDSYS.AUD$UNIFIED (SQL_TEXT) -   CLOB populated

2018-11-03T22:07:15.368785+08:00

Opatch validation is skipped for PDB   MINGDEV1 (con_id=6)

2018-11-03T22:08:37.761704+08:00

MINGDEV1(6):Endian type of dictionary set   to little

****************************************************************

Pluggable Database MINGDEV1 with pdb id -   6 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-0x0000000000a123e2

****************************************************************

2018-11-03T22:08:44.807546+08:00

Applying media recovery for pdb-4 from   SCN 10563994 to SCN 10564176

Remote log information: count-1

thr-1, seq-45,   logfile-/arch/parlog_1_45_839db4a4_967464795.arc, los-10555323,   nxs-18446744073709551615

MINGDEV1(6):Media Recovery Start

2018-11-03T22:08:44.933181+08:00

MINGDEV1(6):Serial Media Recovery started

2018-11-03T22:08:45.086257+08:00

MINGDEV1(6):Media Recovery Log   /arch/parlog_1_45_839db4a4_967464795.arc

2018-11-03T22:08:45.637158+08:00

MINGDEV1(6):Incomplete Recovery applied   until change 10564176 time 11/03/2018 22:08:39

2018-11-03T22:08:45.639481+08:00

MINGDEV1(6):Media Recovery Complete   (cdbtest1)

2018-11-03T22:08:45.958911+08:00

Completed: create pluggable database   mingdev1 from ming@dblk_pdbming refresh mode manual

 

SYS@cdbtest1(CDB$ROOT)> sho pdbs

 

      CON_ID CON_NAME                         OPEN MODE  RESTRICTED

---------- ------------------------------   ---------- ----------

           2 PDB$SEED                         READ ONLY  NO

         3 CLONEMING                      READ WRITE NO

           4 MING                             READ WRITE NO

           6 MINGDEV1                         MOUNTED

 

SYS@cdbtest1(CDB$ROOT)> alter   pluggable database mingdev1 open;

alter pluggable database mingdev1 open

*

ERROR at line 1:

ORA-65341: cannot open pluggable database   in read/write mode

 

后来发现报错

ORA-12012: ִҵ "SYS"."ORA$AT_OS_OPT_SY_101"

ORA-20001: Statistics Advisor: Invalid   task name for the current user

ORA-06512: SYS.DBMS_STATS", line   47207

ORA-06512: SYS.DBMS_STATS_ADVISOR",   line 882

ORA-06512: SYS.DBMS_STATS_INTERNAL",   line 20059

ORA-06512: SYS.DBMS_STATS_INTERNAL",   line 22201

ORA-06512: SYS.DBMS_STATS", line   47197

 

虽然不能open read write，但是可以以read only模式打开

SYS@cdbtest1(CDB$ROOT)> alter pluggable   database mingdev1 open read only;

 

Pluggable database altered.

 

SYS@cdbtest1(CDB$ROOT)> sho pdbs

 

      CON_ID CON_NAME                         OPEN MODE  RESTRICTED

---------- ------------------------------   ---------- ----------

           2 PDB$SEED                       READ ONLY  NO

           3 CLONEMING                        READ WRITE NO

           4 MING                             READ WRITE NO

           6 MINGDEV1                         READ ONLY  NO

 

sys用户登录目标pdb

SYS@mingdev1(MINGDEV1)> alter pluggable database mingdev1 refresh;

alter pluggable database mingdev1 refresh

*

ERROR at line 1:

ORA-65025: Pluggable database MINGDEV1 is   not closed on all instances.

刷新pdb必须将目标pdb先关闭或者mount

 

SYS@mingdev1(MINGDEV1)> alter   pluggable database mingdev1 close immediate;

 

Pluggable database altered.

 

SYS@mingdev1(MINGDEV1)> sho pdbs

 

      CON_ID CON_NAME                         OPEN MODE  RESTRICTED

---------- ------------------------------   ---------- ----------

           6 MINGDEV1                         MOUNTED

SYS@mingdev1(MINGDEV1)> alter   pluggable database mingdev1 refresh;

 

Pluggable database altered.

 

SYS@mingdev1(MINGDEV1)> sho pdbs

 

      CON_ID CON_NAME                         OPEN MODE  RESTRICTED

---------- ------------------------------   ---------- ----------

           6 MINGDEV1                         MOUNTED

SYS@mingdev1(MINGDEV1)> alter   pluggable database mingdev1 open;

alter pluggable database mingdev1 open

*

ERROR at line 1:

ORA-65341: cannot open pluggable database   in read/write mode

 

 

SYS@mingdev1(MINGDEV1)> alter pluggable   database mingdev1 open read only;

 

Pluggable database altered.

 

实际上刷新的过程就是一个介质恢复的过程

MINGDEV1(6):alter pluggable database   mingdev1 refresh

2018-11-04T09:05:44.122795+08:00

Applying media recovery for pdb-4 from   SCN 10564176 to SCN 10654056

Remote log information: count-4

thr-1, seq-47,   logfile-/arch/1_47_967464795.dbf, los-10621887, nxs-10641265

thr-1, seq-46,   logfile-/arch/1_46_967464795.dbf, los-10598476, nxs-10621887

thr-1, seq-45,   logfile-/arch/1_45_967464795.dbf, los-10555323, nxs-10598476

thr-1, seq-48,   logfile-/arch/parlog_1_48_839db4a4_967464795.arc, los-10641265,   nxs-18446744073709551615

MINGDEV1(6):Media Recovery Start

2018-11-04T09:05:44.172534+08:00

MINGDEV1(6):Serial Media Recovery started

2018-11-04T09:05:44.440066+08:00

MINGDEV1(6):Media Recovery Log   /arch/1_45_967464795.dbf

2018-11-04T09:05:49.927526+08:00

MINGDEV1(6):Media Recovery Log   /arch/1_46_967464795.dbf

2018-11-04T09:05:54.341779+08:00

MINGDEV1(6):Media Recovery Log   /arch/1_47_967464795.dbf

2018-11-04T09:05:58.832855+08:00

MINGDEV1(6):Media Recovery Log   /arch/parlog_1_48_839db4a4_967464795.arc

2018-11-04T09:06:02.365844+08:00

MINGDEV1(6):Incomplete Recovery applied   until change 10654056 time 11/04/2018 09:05:38

2018-11-04T09:06:02.367512+08:00

MINGDEV1(6):Media Recovery Complete   (cdbtest1)

MINGDEV1(6):Completed: alter pluggable   database mingdev1 refresh

 

增量同步

desc ming.tx

 Name                                        Null?    Type

 -----------------------------------------   -------- ----------------------------

 A                                                    NUMBER(38)

 

在源pdb内做一些DDL和DML操作：

alter table ming.tx add b int;

insert into ming.tx values(1,1);

commit;

select * from ming.tx;

           A          B

---------- ----------

           1          1

 

此时refresh pdb内还看不到

sho con_name

CON_NAME

------------------------------

MINGDEV1

PDB refresh必须在目标pdb中执行

alter pluggable database mingdev1   refresh;

SYS@cdbtest1(CDB$ROOT)> alter   pluggable database MINGDEV1 open read only;

 

Pluggable database altered.

 

 

desc ming.tx

 Name                                        Null?    Type

 -----------------------------------------   -------- ----------------------------

 A                                                  NUMBER(38)

 B                                                    NUMBER(38)

 

select * from ming.tx;

 

           A          B

---------- ----------

           1          1

 

SYS@cdbtest1(CDB$ROOT)> select   index_name from dba_indexes where table_name='TX';

 

INDEX_NAME

--------------------------------------------------------------------------------

TX_IDX_01

 

 

这种方式的实现方式是通过定时job的方式实现的，如下job详情：

begin dbms_scheduler.create_job(

job_name     =>'MINGDEV1_4131117117_refresh',                                       

job_type     => 'PLSQL_BLOCK',                                                   

job_action => 'begin                  

               execute immediate ''alter   session set container = MINGDEV1'';                    

               execute immediate ''alter   pluggable database refresh'';                                            end;',            

start_date => systimestamp,                                           

repeat_interval => 'FREQ = MINUTELY;   INTERVAL = 1',                           

enabled => TRUE,               

comments => 'MINGDEV1 refresh');                        

end;

 

手动refresh变为自动refresh

下面再看一下自动刷新，该动作需要在快速刷新pdb中执行

SYS@cdbtest1(CDB$ROOT)> @entdb   mingdev1

SYS@cdbtest1(MINGDEV1)> ALTER   PLUGGABLE DATABASE mingdev1 REFRESH MODE EVERY 1 MINUTES;

 

Pluggable database altered.

 

SELECT pdb_id, pdb_name, refresh_mode,   refresh_interval FROM  dba_pdbs;

 

查看：

SYS@cdbtest1(CDB$ROOT)> col pdb_name   for a30

SYS@cdbtest1(CDB$ROOT)> SELECT pdb_id,   pdb_name, refresh_mode, refresh_interval FROM    dba_pdbs;

 

      PDB_ID PDB_NAME                         REFRES REFRESH_INTERVAL

---------- ------------------------------   ------ ----------------

           2 PDB$SEED                         NONE

           3 CLONEMING                        NONE

           4 MING                             NONE

           6 MINGDEV1                         AUTO                  1

源pdb中插入一条数据

insert into ming.tx values(2,2);

commit;

select * from ming.tx;

 

           A          B

---------- ----------

           1          1

           2          2

alert日志中可以发现：     

MINGDEV1(6):ALTER PLUGGABLE DATABASE   mingdev1 REFRESH MODE EVERY 1 MINUTES

MINGDEV1(6):Completed: ALTER PLUGGABLE   DATABASE mingdev1 REFRESH MODE EVERY 1 MINUTES

2018-12-09T12:03:55.230825+08:00

MINGDEV1(6):alter pluggable database   refresh

MINGDEV1(6):Completed: alter pluggable   database refresh

2018-12-09T12:04:54.144791+08:00

MINGDEV1(6):alter pluggable database   refresh

MINGDEV1(6):Completed: alter pluggable   database refresh

2018-12-09T12:05:54.347415+08:00

MINGDEV1(6):alter pluggable database   refresh

MINGDEV1(6):Completed: alter pluggable   database refresh

 

但是refresh pdb中的数据并没有变化

SYS@cdbtest1(MINGDEV1)> select * from   ming.tx;

 

           A          B

---------- ----------

           1          1

 

那是因为refresh pdb是read only状态，read only模式下不会自动刷新，需要将pdb关闭或者mount

SYS@cdbtest1(MINGDEV1)> shutdown   immediate

Pluggable Database closed.

SYS@cdbtest1(CDB$ROOT)>  alter pluggable database MINGDEV1 open read   only;

 

Pluggable database altered.

 

再次查看

SYS@cdbtest1(MINGDEV1)> select * from   ming.tx;

 

           A          B

---------- ----------

           1          1

           2          2

 

自动刷新转变为手动刷新

SYS@cdbtest1(MINGDEV1)> alter pluggable database MINGDEV1 refresh mode manual;

 

Pluggable database altered.

refresh PDB转变为普通PDB

SYS@cdbtest1(CDB$ROOT)> sho pdbs

 

      CON_ID CON_NAME                         OPEN MODE  RESTRICTED

---------- ------------------------------   ---------- ----------

           2 PDB$SEED                         READ ONLY  NO

           3 CLONEMING                        READ WRITE NO

           4 MING                           READ WRITE NO

           6 MINGDEV1                         READ ONLY  NO

SYS@cdbtest1(CDB$ROOT)> alter   pluggable database MINGDEV1 close immediate;

 

Pluggable database altered.

SYS@cdbtest1(MINGDEV1)> alter pluggable database MINGDEV1 refresh mode none;

 

Pluggable database altered.

SYS@cdbtest1(CDB$ROOT)> alter   pluggable database MINGDEV1 open;

 

Pluggable database altered.

 

注意：此过程不可逆，一旦完成转换，就不能再实现refresh了

感谢各位的阅读，以上就是“oracle 12c中怎么搭建PDB refresh”的内容了，经过本文的学习后，相信大家对oracle 12c中怎么搭建PDB refresh这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是天达云，小编将为大家推送更多相关知识点的文章，欢迎关注！
返回关系型数据库教程...
400-837-6568
7 * 24小时全天全国服务热线400电话
天达云·专业的云计算服务平台 全国400服务电话：400-837-6568
Copyright © 2011-2025 Tdyun.com. All Rights Reserved. 天达云 版权所有 粤ICP备18083267号-7