[2022-04-27] refresh PDB

qxy0503

已于 2022-04-27 13:46:51 修改

阅读量1.1k

点赞数

分类专栏： oracle 文章标签： PDB REFRESH SWITCHOVER

于 2022-04-27 13:44:28 首次发布

本文链接：https://blog.csdn.net/m15217321304/article/details/124445680

版权

oracle 专栏收录该内容

60 篇文章 2 订阅

订阅专栏

参考资料：

Switching Over a Refreshable Clone PDB

About Refreshable Clone PDBs

refresh pdb使用情况如：原PDB所在CDB的主机资源紧张，可以迁走部分PDB减轻主机压力。或者对PDB做容灾使用等。PDB的刷新可以分两种，手动刷新和自动刷新。

自动和手动刷新模式
您可以将克隆 PDB 配置为按设定的时间间隔自动刷新，也可以使用ALTER PLUGGABLE DATABASE REFRESH语句手动刷新它。

该REFRESH MODE子句仅在CREATE PLUGGABLE DATABASE ... FROM语句中受支持。您可以使用此子句指定以下选项之一：

指定REFRESH MODE NONE默认值以创建不可刷新的 PDB。

您可以将可刷新的克隆 PDB 更改为普通 PDB，方法是REFRESH MODE NONE在语句中包含该子句，ALTER PLUGGABLE DATABASE然后以读/写模式打开 PDB。您不能将普通 PDB 更改为可刷新的克隆 PDB。可刷新克隆 PDB 转换为普通 PDB 后，您无法将其改回可刷新克隆 PDB。

指定REFRESH MODE MANUAL创建必须手动刷新的可刷新 PDB。

指定创建一个可刷新的 PDB，该 PDB 在指定的分钟数过后自动刷新。使用自动刷新的可刷新 PDB 也可以手动刷新。 REFRESH MODE EVERY number_of_minutes MINUTES

支持三种类型的源端数据库，分别是Local PDB/PDB in a remote CDB/Non-CDB。不管是本地还是远程，都需要创建dblink。

本次测试案例使用同一个CDB，需要使用的PDB为PDB01

SQL> show  pdbs

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

1、确保PDB01是read write状态

In SQL*Plus, connect to cdb1 as a user with administrator privileges, and then ensure sure that cdb1_pdb1 is open in read/write mode (sample output included):

SQL> show  pdbs

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

2、创建一个common用户

Create a common user named c##u1 (replace pwd with a user-specified password):

DROP USER c##u1 CASCADE;
CREATE USER c##u1 IDENTIFIED BY pwd;
GRANT CREATE SESSION, RESOURCE, CREATE ANY TABLE, UNLIMITED TABLESPACE TO c##u1 CONTAINER=ALL;
GRANT CREATE PLUGGABLE DATABASE TO c##u1 CONTAINER=ALL;
GRANT SYSOPER TO c##u1 CONTAINER=ALL;

SQL> CREATE USER c##u1 IDENTIFIED BY tiger;

User created.

SQL> GRANT CREATE SESSION, RESOURCE, CREATE ANY TABLE, UNLIMITED TABLESPACE TO c##u1 CONTAINER=ALL;

Grant succeeded.

SQL> GRANT CREATE PLUGGABLE DATABASE TO c##u1 CONTAINER=ALL;
GRANT SYSOPER TO c##u1 CONTAINER=ALL;
Grant succeeded.

SQL> 

Grant succeeded.

SQL>

3、创建PDB01容器的测试数据

Set the container to cdb1_pdb1, and then create a table t1 to use for testing (sample output included):

ALTER SESSION SET CONTAINER = cdb1_pdb1;
CREATE TABLE t1(n1 NUMBER);
INSERT INTO t1 VALUES(1);
COMMIT;
SELECT * FROM t1;

        N1
----------
         1


SQL> ALTER SESSION SET CONTAINER =PDB01;

Session altered.

SQL> create table t1(id int);

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1 
  2  ;

        ID
----------
         1

SQL>

4、连接目标端，创建common用户，由于的源端和目标端是同一个CDB，并且在第二步已经创建common用户，这步就可以忽略

Connect to cdb2 as a user with administrator privileges, and then create the common user named c##u1 (replace pwd with a user-specified password):

CONNECT SYS@cdb2 AS SYSDBA
Enter password: *******

DROP USER c##u1 CASCADE;
CREATE USER c##u1 IDENTIFIED BY pwd;
GRANT CREATE SESSION, RESOURCE, CREATE ANY TABLE, UNLIMITED TABLESPACE TO c##u1 CONTAINER=ALL;
GRANT CREATE PLUGGABLE DATABASE TO c##u1 CONTAINER=ALL;
GRANT SYSOPER TO c##u1 CONTAINER=ALL;

5、创建到源库的DB LINK

Create a database link to cdb1.

The following command specifies user c##u1, password pwd, and service name cdb1:

CREATE DATABASE LINK cdb1_datalink CONNECT TO c##u1 IDENTIFIED BY pwd USING 'cdb1';

配置到PDB01的tns

PDB01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19cnoadg)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb01)
    )
  )

检查监听

[oracle@oracle19cnoadg ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 27-APR-2022 11:27:36

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19cnoadg)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                27-APR-2022 11:27:11
Uptime                    0 days 0 hr. 0 min. 25 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/database/network/admin/listener.ora
Listener Log File         /oracle/app/diag/tnslsnr/oracle19cnoadg/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19cnoadg)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
  Instance "prodpdb", status READY, has 1 handler(s) for this service...
Service "dd5eb78e33395714e0531f28a8c07bd2" has 1 instance(s).
  Instance "prodpdb", status READY, has 1 handler(s) for this service...
Service "dd6379106a279583e0531f28a8c06b78" has 1 instance(s).
  Instance "prodpdb", status READY, has 1 handler(s) for this service...
Service "pdb01" has 1 instance(s).
  Instance "prodpdb", status READY, has 1 handler(s) for this service...
Service "pdb02" has 1 instance(s).
  Instance "prodpdb", status READY, has 1 handler(s) for this service...
Service "prodpdb" has 1 instance(s).
  Instance "prodpdb", status READY, has 1 handler(s) for this service...
Service "prodpdbXDB" has 1 instance(s).
  Instance "prodpdb", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oracle19cnoadg ~]$

创建DB LINK，并测试连通性

SQL> CREATE DATABASE LINK PDB01 CONNECT TO c##u1 IDENTIFIED BY tiger using 'PDB01';

Database link created.

SQL> 
SQL> select * from dual@PDB01;

D
-
X

SQL>

6、创建手动模式刷新PDB

Create the manually refreshable PDB named cdb1_pdb1_ref

The following statement specifies the database link cdb1_datalink and the file destination /dsk1/df:

CREATE PLUGGABLE DATABASE cdb1_pdb1_ref FROM cdb1_pdb1@cdb1_datalink 
  CREATE_FILE_DEST='/dsk1/df' 
  REFRESH MODE MANUAL;

SQL> CREATE PLUGGABLE DATABASE REFRESH_PDB FROM pdb01@PDB01 CREATE_FILE_DEST='/oracle/app/oradata/PRODPDB' REFRESH MODE MANUAL;

Pluggable database created.

SQL>

日志：

CREATE PLUGGABLE DATABASE REFRESH_PDB FROM pdb01@PDB01 CREATE_FILE_DEST='/oracle/app/oradata/PRODPDB' REFRESH MODE MANUAL
2022-04-27T11:33:28.147208+08:00
PDB01(3): AUDSYS.AUD$UNIFIED (SQL_TEXT) - CLOB populated
2022-04-27T11:33:32.713910+08:00
REFRESH_PDB(5):Endian type of dictionary set to little
****************************************************************
Pluggable Database REFRESH_PDB 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-0x0000000000000118
****************************************************************
2022-04-27T11:33:34.805604+08:00
Applying media recovery for pdb-3 from SCN 3451756 to SCN 3451805
Remote log information: count-1
thr-1, seq-33, logfile-/oracle/app/fast_recovery_area/PRODPDB/foreign_archivelog/PDB01/2022_04_27/o1_mf_1_33_2753998305_.arc, los-3413276, nxs-18446744073709551615
REFRESH_PDB(5):Media Recovery Start
2022-04-27T11:33:34.832692+08:00
REFRESH_PDB(5):Serial Media Recovery started
REFRESH_PDB(5):max_pdb is 8
2022-04-27T11:33:35.062703+08:00
REFRESH_PDB(5):Media Recovery Log /oracle/app/fast_recovery_area/PRODPDB/foreign_archivelog/PDB01/2022_04_27/o1_mf_1_33_2753998305_.arc
2022-04-27T11:33:36.287440+08:00
REFRESH_PDB(5):Incomplete Recovery applied until change 3451805 time 04/27/2022 11:33:34
2022-04-27T11:33:36.304641+08:00
REFRESH_PDB(5):Media Recovery Complete (prodpdb)
2022-04-27T11:33:36.448659+08:00
Deleted Oracle managed file /oracle/app/fast_recovery_area/PRODPDB/foreign_archivelog/PDB01/2022_04_27/o1_mf_1_33_2753998305_.arc
Completed: CREATE PLUGGABLE DATABASE REFRESH_PDB FROM pdb01@PDB01 CREATE_FILE_DEST='/oracle/app/oradata/PRODPDB' REFRESH MODE MANUAL

7、刷新PDB

Refresh cdb1_pdb1_ref:

ALTER SESSION SET CONTAINER = cdb1_pdb1_ref;
ALTER PLUGGABLE DATABASE REFRESH;

SQL> ALTER SESSION SET CONTAINER = REFRESH_PDB;

Session altered.

SQL> ALTER PLUGGABLE DATABASE REFRESH;

Pluggable database altered.

SQL>

8、检查测试表T1的值

Query t1 to check that the refreshable clone PDB contains the correct contents (sample output included):

ALTER PLUGGABLE DATABASE OPEN READ ONLY;
SELECT * FROM t1;

        N1
----------
         1

SQL> ALTER PLUGGABLE DATABASE REFRESH_PDB open read only ;

Pluggable database altered.

SQL> ALTER SESSION SET CONTAINER = REFRESH_PDB;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
REFRESH_PDB
SQL> select * from t1;

        ID
----------
         1

SQL>

可以通过下面的方式更新刷新方式

alter pluggable database refresh_pdb refresh mode manual;
alter pluggable database refresh_pdb refresh mode every 1 hours;
alter pluggable database refresh_pdb refresh mode every 1 minutes;
alter pluggable database refresh_pdb refresh mode none;

查看PDB的刷新方式

SQL> col pdb_name for a30
SQL> select pdb_name, REFRESH_MODE,REFRESH_INTERVAL from dba_pdbs;

PDB_NAME                       REFRES REFRESH_INTERVAL
------------------------------ ------ ----------------
PDB01                          NONE
PDB$SEED                       NONE
REFRESH_PDB                    MANUAL
PDB02                          NONE

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

Pluggable database altered.

SQL> select pdb_name, REFRESH_MODE,REFRESH_INTERVAL from dba_pdbs;

PDB_NAME                       REFRES REFRESH_INTERVAL
------------------------------ ------ ----------------
PDB01                          NONE
PDB$SEED                       NONE
REFRESH_PDB                    AUTO                  1
PDB02                          NONE

SQL>

自动刷新的时候提示如下：

2022-04-27T11:43:49.574834+08:00
REFRESH_PDB(5):alter pluggable database refresh
REFRESH_PDB(5):REFRESH_PDB(5):ERROR:PDB needs to be closed for auto refresh
REFRESH_PDB(5):Completed: alter pluggable database refresh

把PDB启动关闭，再次查看日志

REFRESH_PDB(5):alter pluggable database refresh
2022-04-27T11:46:52.850937+08:00
Applying media recovery for pdb-3 from SCN 3452320 to SCN 3452701
Remote log information: count-1
thr-1, seq-33, logfile-/oracle/app/fast_recovery_area/PRODPDB/foreign_archivelog/PDB01/2022_04_27/o1_mf_1_33_2753998305_.arc, los-3413276, nxs-18446744073709551615
REFRESH_PDB(5):Media Recovery Start
2022-04-27T11:46:52.860697+08:00
REFRESH_PDB(5):Serial Media Recovery started
REFRESH_PDB(5):max_pdb is 8
2022-04-27T11:46:52.917859+08:00
REFRESH_PDB(5):Media Recovery Log /oracle/app/fast_recovery_area/PRODPDB/foreign_archivelog/PDB01/2022_04_27/o1_mf_1_33_2753998305_.arc
2022-04-27T11:46:53.254925+08:00
REFRESH_PDB(5):Incomplete Recovery applied until change 3452701 time 04/27/2022 11:46:50
2022-04-27T11:46:53.259891+08:00
REFRESH_PDB(5):Media Recovery Complete (prodpdb)
2022-04-27T11:46:53.453766+08:00
Deleted Oracle managed file /oracle/app/fast_recovery_area/PRODPDB/foreign_archivelog/PDB01/2022_04_27/o1_mf_1_33_2753998305_.arc
REFRESH_PDB(5):Completed: alter pluggable database refresh

验证数据

SQL> alter session set container=REFRESH_PDB;

Session altered.

SQL> alter database open read only;

Database altered.

SQL> 
SQL> select * from t1;

        ID
----------
         1
         2

SQL>

9、激活PDB为read write模式. （这步也可以和源库PDB做switchover)

SQL> alter pluggable database REFRESH_PDB close immediate instances=all;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB01                          READ WRITE NO
         4 PDB02                          MOUNTED
         5 REFRESH_PDB                    MOUNTED
SQL>  alter pluggable database REFRESH_PDB refresh mode none;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB01                          READ WRITE NO
         4 PDB02                          MOUNTED
         5 REFRESH_PDB                    MOUNTED
SQL> col pdb_name for a30
SQL> select pdb_name, REFRESH_MODE,REFRESH_INTERVAL from dba_pdbs;

PDB_NAME                       REFRES REFRESH_INTERVAL
------------------------------ ------ ----------------
PDB01                          NONE
PDB$SEED                       NONE
REFRESH_PDB                    NONE
PDB02                          NONE

SQL> 
SQL> alter pluggable database REFRESH_PDB open read write;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB01                          READ WRITE NO
         4 PDB02                          MOUNTED
         5 REFRESH_PDB                    READ WRITE NO
SQL> alter session set container=REFRESH_PDB;

Session altered.

SQL> select * from t1;

        ID
----------
         1
         2

SQL>

qxy0503

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
[2022-04-27] refresh PDB

参考资料：Switching Over a Refreshable Clone PDBAbout Refreshable Clone PDBsrefresh pdb使用情况如：原PDB所在CDB的主机资源紧张，可以迁走部分PDB减轻主机压力。或者对PDB做容灾使用等。PDB的刷新可以分两种，手动刷新和自动刷新。自动和手动刷新模式您可以将克隆 PDB 配置为按设定的时间间隔自动刷新，也可以使用ALTER PLUGGABLE DATABASE REFRESH语句手动刷新它。该REFR.
复制链接

扫一扫