Oracle 12c R2 新特性: PDB refresh

首页 » Cloud, ORACLE 9i-23ai » Oracle 12c R2 新特性: PDB refresh

Oracle 12c R2 新特性: PDB refresh

2017/03/10
Cloud, ORACLE 9i-23ai
2,013 views
Oracle 12c R2 新特性: PDB refresh已关闭评论

PDB refresh功能有些像PDB HOT Clone , 同时又像PDB级的Data Guard(实际不是),PDB refresh可以手动同步刷新或自定义同步间隔自动同步. 应用场景是开发和测试环境的搭建, 因为是增量的同步也减少了对源数据库的影响, 或作为一种online的备份库. PDB refresh是数据库自带的功能, 不用借助其它第三方工具, 像我们环境中存储级的BCV产品功能. PDB refresh支持两种refresh模式:自动和手动, 如果存在以上的需求建议尝试12.2的PDB refresh功能..

PDB refresh限制条件和HOT clone类似, 这个功能种种失败让我鼓捣了2天, 当然对我而言过程比结果更重要. PDB refresh有以下要求

1, 12.2 EE 以上版本, 我测试了12.2 bate版本不支持
2, 源库需要开启在归档模式, 因为刷新同步时是利用dblink传输对方的redo, 有时，当需要更新刷新副本时，源PDB或源PDB所属的CDB不可访问。 在这些情况下可以设置REMOTE_RECOVERY_FILE_DEST参数，将尝试从此参数指定的目录中读取归档日志文件。
3, 源库LOCAL UNDO 模式
4, 刷新模式可以方便的在手动和自动之间来回切换, 也可以切换刷新模式为NONE(创建PDB的默认值), 但是切换到了NONE就不再支持切回可刷新
5, pdb refresh理论是应该在不同的CDB通过DBLINK的Remote PDB Clone, 如有生产库同步到其它test/dev环境,但是如果像我一样只是测试也可以dblink指向自己, 如果不带dblink语法都无法通过.
6, 和DG一样如果源库创建了新的表空间,需要配置PDB_FILE_NAME_CONVERT,刷新后目标端是自动创建.
7, 和DG一样(不同ADG),目标端的pdb可以打开在read-only状态, 但是打开不刷新,刷新要在非open状态,OPEN状态手动尝试刷新会报错,mount状态可以手动或自动刷新.
8, PDB refresh刷新操作必须在目标pdb container中执行.
9, 创建PDB refresh时源pdb和listener是在打开状态.
10, 目标库上创建到源库的dblink (但是我开始测试发现没有dblink使用了@instance_name, LOCAL clone也成功了还不确认原因)
11, 原理迭代REDO Copy和 ROLLBACK

— demo —
环境 Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 ON OL6 X64 , 因为只有一个CDB环境, PDB测试PDBANBOB 同步DEVANBOB。只是为了测试所以DBLINK指向的还是自身，alert日志显示了同步和应用两种操作。实际环境中建议是不同CDB之间Remote PDB Refresh.

# 源库启动用归档模式和LOCAL UNDO
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1560281088 bytes
Fixed Size                  8793160 bytes
Variable Size            1006633912 bytes
Database Buffers          536870912 bytes
Redo Buffers                7983104 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.
# 目标库创建DBLINK到源库
SQL> create database link link_prod connect to system identified by oracle using '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = anbob.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdbanbob.com)
    )
  )';

SQL> @dblinks

OWNER                DB_LINK              USERNAME             HOST                                     CREATED
-------------------- -------------------- -------------------- ---------------------------------------- -------------------
SYS                  SYS_HUB                                   SEEDDATA                                 2016-12-09 21:02:24
SYS                  LINK_PROD.COM        SYSTEM               (DESCRIPTION =                           2017-03-09 20:44:45
                                                                   (ADDRESS = (PROTOCOL = TCP)(HOST = a
                                                               nbob.com)(PORT = 1521))
                                                                   (CONNECT_DATA =
                                                                     (SERVER = DEDICATED)
                                                                     (SERVICE_NAME = pdbanbob.com)
                                                                   )
                                                                 )


SQL> select sysdate from dual@LINK_PROD.COM;
SYSDATE
-------------------
2017-03-09 21:08:36

# 目标库初始化创建PDB  devanbob，手动刷新
SQL> CREATE PLUGGABLE DATABASE  devanbob from pdbanbob@link_prod REFRESH MODE manual;
CREATE PLUGGABLE DATABASE  devanbob from pdbanbob@link_prod REFRESH MODE manual
                                                                              *
ERROR at line 1:
ORA-65016: FILE_NAME_CONVERT must be specified
SQL> alter session set pdb_file_name_convert='pdbanbob','devanbob';
Session altered.

SQL> CREATE PLUGGABLE DATABASE  devanbob from pdbanbob@link_prod REFRESH MODE manual;
Pluggable database created.

SQL> select name from v$dbfile;
NAME
------------------------------------------------------------
/u02/app/oracle/oradata/anbob/devanbob/system01.dbf
/u02/app/oracle/oradata/anbob/devanbob/sysaux01.dbf
/u02/app/oracle/oradata/anbob/devanbob/undotbs01.dbf
/u02/app/oracle/oradata/anbob/devanbob/users01.dbf
/u02/app/oracle/oradata/anbob/devanbob/lower01.dbf

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBANBOB                       READ WRITE NO
         5 DEVANBOB                       MOUNTED
         6 PDBWEEJAR                      MOUNTED

# 源库做DML 测试同步

[oracle@anbob ~]$ ora
SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 9 21:10:44 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBANBOB                       READ WRITE NO
         5 DEVANBOB                       MOUNTED
         6 PDBWEEJAR                      MOUNTED

SQL> alter session set container=pdbanbob;
Session altered.

SQL> col name for a60
SQL> select name from v$dbfile;

NAME
------------------------------------------------------------
/u02/app/oracle/oradata/anbob/pdbanbob/system01.dbf
/u02/app/oracle/oradata/anbob/pdbanbob/sysaux01.dbf
/u02/app/oracle/oradata/anbob/pdbanbob/undotbs01.dbf
/u02/app/oracle/oradata/anbob/pdbanbob/users01.dbf
/u02/app/oracle/oradata/anbob/pdbanbob/lower01.dbf


SQL> create table u1.t200 (v date);
Table created.

SQL> insert into u1.t200 values(sysdate);
1 row created.

SQL> commit;
Commit complete.

# 目标库,检查同步

SQL> ALTER PLUGGABLE DATABASE devanbob REFRESH;
ALTER PLUGGABLE DATABASE devanbob REFRESH
*
ERROR at line 1:
ORA-65118: operation affecting a pluggable database cannot be performed from another pluggable database

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=devanbob;
Session altered.

SQL> ALTER PLUGGABLE DATABASE devanbob REFRESH;
Pluggable database altered.

SQL> @o u1.t200
    dba_objects
    *
ERROR at line 11:
ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only

SQL> alter database open read only;
Database altered.

SQL> @o u1.t200
owner                     object_name                    object_type          status           OID      D_OID CREATED             LAST_DDL_TIME
------------------------- ------------------------------ -------------------- --------- ---------- ---------- ------------------- -------------------
U1                        T200                           TABLE                VALID          73237      73237 2017-03-09 21:10:57 2017-03-09 21:10:57

SQL> select * from u1.t200;
V
-------------------
2017-03-09 21:11:05

Note:

表和记录已同步到目标PDB

# DB alert log

2017-03-09 21:09:19.461000 +08:00
CREATE PLUGGABLE DATABASE  devanbob from pdbanbob@link_prod REFRESH MODE manual
Opatch XML is skipped for PDB PDBANBOB (conid=3)
 AUDSYS.AUD$UNIFIED (SQL_TEXT) - CLOB populated
Opatch validation is skipped for PDB DEVANBOB (con_id=5)
2017-03-09 21:09:55.080000 +08:00
Endian type of dictionary set to little
****************************************************************
Pluggable Database DEVANBOB with pdb id - 5 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-0x00000000000000d5
****************************************************************
2017-03-09 21:09:58.018000 +08:00
Applying media recovery for pdb-3 from SCN 3154765 to SCN 3154845
Remote log information: count-1
thr-1, seq-14, logfile-/u02/app/oracle/product/12.2.0/db_1/dbs/archparlog_1_14_d859ac79_933629279.arc, los-2859559, nxs-18446744073709551615
Media Recovery Start
Serial Media Recovery started
Media Recovery Log /u02/app/oracle/product/12.2.0/db_1/dbs/archparlog_1_14_d859ac79_933629279.arc
2017-03-09 21:09:59.348000 +08:00
Incomplete Recovery applied until change 3154845 time 03/09/2017 21:09:57
Media Recovery Complete (anbob)
Completed: CREATE PLUGGABLE DATABASE  devanbob from pdbanbob@link_prod REFRESH MODE manual


2017-03-09 21:12:05.104000 +08:00
ALTER PLUGGABLE DATABASE devanbob REFRESH
ORA-65118 signalled during: ALTER PLUGGABLE DATABASE devanbob REFRESH...
2017-03-09 21:13:01.330000 +08:00
ALTER PLUGGABLE DATABASE devanbob REFRESH
2017-03-09 21:13:04.042000 +08:00
Applying media recovery for pdb-3 from SCN 3154845 to SCN 3155052
Remote log information: count-1
thr-1, seq-14, logfile-/u02/app/oracle/product/12.2.0/db_1/dbs/archparlog_1_14_d859ac79_933629279.arc, los-2859559, nxs-18446744073709551615
Media Recovery Start
Serial Media Recovery started
Media Recovery Log /u02/app/oracle/product/12.2.0/db_1/dbs/archparlog_1_14_d859ac79_933629279.arc
Incomplete Recovery applied until change 3155052 time 03/09/2017 21:13:01
Media Recovery Complete (anbob)
Completed: ALTER PLUGGABLE DATABASE devanbob REFRESH
2017-03-09 21:14:06.883000 +08:00
alter database open read only

# 目标库修改为自动刷新模式

SQL> ALTER PLUGGABLE DATABASE devanbob REFRESH MODE EVERY 1 MINUTES;
Pluggable database altered.

SQL> SELECT pdb_id, pdb_name, refresh_mode, refresh_interval
  2  FROM   dba_pdbs
  3  ORDER BY 1;

    PDB_ID PDB_NAME                       REFRES REFRESH_INTERVAL
---------- ------------------------------ ------ ----------------
         2 PDB$SEED                       NONE
         3 PDBANBOB                       NONE
         4 DEVANBOB                       AUTO                  1
         6 PDBWEEJAR                      NONE

# 源库第二次DML

SQL> insert into u1.t200 values(sysdate);
1 row created.

SQL> commit;
Commit complete.

# 目标库在OPEN状态下并不会刷新，close后会自动刷新

SQL> select * from u1.t200;
V
-------------------
2017-03-09 21:11:05

SQL> select * from u1.t200;
V
-------------------
2017-03-09 21:11:05

SQL> alter pluggable database devanbob close;
Pluggable database altered.

# 源库做第3次DML

SQL> insert into u1.t200 values(sysdate);
1 row created.
SQL> commit;
Commit complete.

# 目标库在CLOSE状态上会自动刷新

SQL> alter pluggable database devanbob open;
alter pluggable database devanbob open
*
ERROR at line 1:
ORA-65341: cannot open pluggable database in read/write mode


SQL> alter pluggable database devanbob open read only;
Pluggable database altered.

SQL> select * from u1.t200;
V
-------------------
2017-03-09 21:11:05   -- first  
2017-03-09 21:26:49   -- second
2017-03-09 21:31:53   -- third

# alert log

ALTER PLUGGABLE DATABASE devanbob REFRESH MODE EVERY 1 MINUTES
Completed: ALTER PLUGGABLE DATABASE devanbob REFRESH MODE EVERY 1 MINUTES
alter pluggable database refresh
Completed: alter pluggable database refresh
2017-03-09 21:27:37.460000 +08:00
alter pluggable database refresh
Completed: alter pluggable database refresh
2017-03-09 21:28:37.669000 +08:00
alter pluggable database refresh
Completed: alter pluggable database refresh
2017-03-09 21:29:37.866000 +08:00
alter pluggable database refresh
Completed: alter pluggable database refresh
2017-03-09 21:30:28.708000 +08:00
alter pluggable database devanbob close
JIT: pid 6802 requesting stop
Pluggable database DEVANBOB closed
Completed: alter pluggable database devanbob close
2017-03-09 21:30:37.997000 +08:00
alter pluggable database refresh
2017-03-09 21:30:41.097000 +08:00
Applying media recovery for pdb-3 from SCN 3155052 to SCN 3156374
Remote log information: count-1
thr-1, seq-14, logfile-/u02/app/oracle/product/12.2.0/db_1/dbs/archparlog_1_14_d859ac79_933629279.arc, los-2859559, nxs-18446744073709551615
Media Recovery Start
Serial Media Recovery started
Media Recovery Log /u02/app/oracle/product/12.2.0/db_1/dbs/archparlog_1_14_d859ac79_933629279.arc
Incomplete Recovery applied until change 3156374 time 03/09/2017 21:30:38
Media Recovery Complete (anbob)
Completed: alter pluggable database refresh
2017-03-09 21:31:38.201000 +08:00
alter pluggable database refresh
2017-03-09 21:31:41.369000 +08:00
Applying media recovery for pdb-3 from SCN 3156374 to SCN 3156415
Remote log information: count-1
thr-1, seq-14, logfile-/u02/app/oracle/product/12.2.0/db_1/dbs/archparlog_1_14_d859ac79_933629279.arc, los-2859559, nxs-18446744073709551615
Media Recovery Start
Serial Media Recovery started
Media Recovery Log /u02/app/oracle/product/12.2.0/db_1/dbs/archparlog_1_14_d859ac79_933629279.arc
Incomplete Recovery applied until change 3156415 time 03/09/2017 21:31:38
Media Recovery Complete (anbob)
Completed: alter pluggable database refresh
2017-03-09 21:32:37.387000 +08:00
alter pluggable database refresh
2017-03-09 21:32:38.551000 +08:00
Applying media recovery for pdb-3 from SCN 3156415 to SCN 3156460
Remote log information: count-1
thr-1, seq-14, logfile-/u02/app/oracle/product/12.2.0/db_1/dbs/archparlog_1_14_d859ac79_933629279.arc, los-2859559, nxs-18446744073709551615
Media Recovery Start
Serial Media Recovery started
Media Recovery Log /u02/app/oracle/product/12.2.0/db_1/dbs/archparlog_1_14_d859ac79_933629279.arc
Incomplete Recovery applied until change 3156460 time 03/09/2017 21:32:37
Media Recovery Complete (anbob)
Completed: alter pluggable database refresh
2017-03-09 21:32:55.322000 +08:00
alter pluggable database devanbob open
ORA-65341 signalled during: alter pluggable database devanbob open...
2017-03-09 21:33:04.060000 +08:00
alter pluggable database devanbob open read only
Autotune of undo retention is turned on. 
Endian type of dictionary set to little
Undo initialization finished serial:0 start:6186572 end:6186572 diff:0 ms (0.0 seconds)
Database Characterset for DEVANBOB is AL32UTF8
Opatch validation is skipped for PDB DEVANBOB (con_id=0)
Opening pdb with no Resource Manager plan active
Pluggable database DEVANBOB opened read only
Completed: alter pluggable database devanbob open read only
2017-03-09 21:33:37.703000 +08:00
alter pluggable database refresh
Completed: alter pluggable database refresh

# 源库归档文件，因为没有配置归档路径，默认在$OH/dbs下，可以看到多次更新读取的都是同一个归档文件

[oracle@anbob dbs]$ ls -lrt
total 68220
-rw-r--r-- 1 oracle oinstall      3079 May 15  2015 init.ora
-rw-r----- 1 oracle oinstall        24 Jan 18 21:24 lkANBOB
-rw-r----- 1 oracle oinstall      3584 Jan 18 21:38 orapwanbob
-rw-r----- 1 oracle oinstall      3584 Mar  9 21:11 spfileanbob.ora
-rw-r----- 1 oracle oinstall  18726912 Mar  9 21:15 snapcf_anbob.f
-rw-r----- 1 oracle oinstall  18825216 Mar  9 21:15 c-1310880857-20170309-00
-rw-r----- 1 oracle oinstall 209715712 Mar  9 21:32 archparlog_1_14_d859ac79_933629279.arc
-rw-rw---- 1 oracle oinstall      1544 Mar  9 21:32 hc_anbob.dat

# 转换为目标库PDB 为非refresh PDB

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> alter pluggable database devanbob close;
Pluggable database altered.

SQL> alter session set container=devanbob;
Session altered.

SQL> ALTER PLUGGABLE DATABASE devanbob REFRESH MODE NONE;
Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE devanbob REFRESH MODE manual;
ALTER PLUGGABLE DATABASE devanbob REFRESH MODE manual
*
ERROR at line 1:
ORA-65261: pluggable database DEVANBOB not enabled for refresh

Note:
禁用PDB refresh功能后，就不能再转换为refresh pdb

ALERT LOG

Pluggable database DEVANBOB closed
Completed: alter pluggable database devanbob close
2017-03-09 21:37:54.731000 +08:00
ALTER PLUGGABLE DATABASE devanbob REFRESH MODE NONE
ORA-65118 signalled during: ALTER PLUGGABLE DATABASE devanbob REFRESH MODE NONE...
2017-03-09 21:38:17.011000 +08:00
ALTER PLUGGABLE DATABASE devanbob REFRESH MODE NONE
2017-03-09 21:38:19.360000 +08:00
Applying media recovery for pdb-3 from SCN 3156460 to SCN 3156929
Remote log information: count-1
thr-1, seq-14, logfile-/u02/app/oracle/product/12.2.0/db_1/dbs/archparlog_1_14_d859ac79_933629279.arc, los-2859559, nxs-18446744073709551615
Media Recovery Start
Serial Media Recovery started
Media Recovery Log /u02/app/oracle/product/12.2.0/db_1/dbs/archparlog_1_14_d859ac79_933629279.arc
Incomplete Recovery applied until change 3156929 time 03/09/2017 21:38:17
Media Recovery Complete (anbob)
Autotune of undo retention is turned on.
2017-03-09 21:38:20.616000 +08:00
Endian type of dictionary set to little
[7703] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:6503169 end:6503297 diff:128 ms (0.1 seconds)
Database Characterset for DEVANBOB is AL32UTF8
JIT: pid 7703 requesting stop
2017-03-09 21:38:21.724000 +08:00
Completed: ALTER PLUGGABLE DATABASE devanbob REFRESH MODE NONE
2017-03-09 21:38:40.812000 +08:00
ALTER PLUGGABLE DATABASE devanbob REFRESH MODE manual
ORA-65261 signalled during: ALTER PLUGGABLE DATABASE devanbob REFRESH MODE manual...

Summary:
演示了PDB refresh的能过DBLINK的初始化，刷新模式之间的转换：手动刷新和自动刷新及NONE 刷新，close状态的自动刷新，及从ALERT LOG可以明确记录整个过程。

打赏

hot clone, local undo, pdb dg, pdb refresh

对不起，这篇文章暂时关闭评论。

上一篇： Oracle 12c R2 新特性: Online PDB relocate (PDB hot move)

下一篇： Materialized View Fast Refresh Is Slow, 10046 trace show HASH_SJ hint (物化视图因为hash_sj hint刷新性能差)

ANBOB™

Oracle 12c R2 新特性: PDB refresh

对不起，这篇文章暂时关闭评论。

最新文章

标签云集

文章索引

MySql Link

ORACLE Link

Others Link

国内好友

管理功能

微信公众号/Wechat