Oracle 21c 新特性 PDB 应用程序容器同步的扩展语法




  • ALTER PLUGGABLE DATABASE APPLICATION ... SYNC 语句现在接受多个应用程序名称和要排除的名称。 例如,在应用程序 PDB 中发出的单个语句可以同步 app1 和 app2,或者同步除 app3 之外的所有应用程序。

    扩展语法使您能够减少同步语句的数量。 此外,数据库以正确的顺序重播语句。 假设您将 ussales 从 v1 升级到 v2,然后将 eusales 从 v1 升级到 v2,然后将 ussales 从 v2 升级到 v3。 语句 ALTER PLUGGABLE DATABASE APPLICATION ussales, eusales SYNC 依次重放语句,将 ussales 升级到 v2,然后将 eusales 升级到 v2,然后将 ussales 升级到 v3。

    介绍PDB 应用程序容器同步的扩展语法

    此页面提供有关增强应用程序 PDB 中的应用程序同步的更多详细信息。

    在 Oracle Database 19c 中,ALTER PLUGGABLE DATABASE APPLICATION ... SYNC 语句只接受一个应用程序名称来与应用程序根目录同步。您必须执行与应用程序数量一样多的语句才能与应用程序根同步。

    在 Oracle Database 21c 中,ALTER PLUGGABLE DATABASE APPLICATION ... SYNC 语句允许您对多个应用程序名称只执行一次该语句。例如,在应用程序 PDB 中发出的单个语句可以同步 apexapp 和 ordsapp,或者同步除 ordsapp 之外的所有应用程序。

    当应用程序相互依赖时,为了功能的正确性,需要在单个语句中同步它们。假设您将 apexapp 从 1.0 升级到 2.0,将 ordsapp 从 1.0 升级到 2.0,然后将 apexapp 升级到 3.0。 ALTER PLUGGABLE DATABASE APPLICATION apexapp, ordsapp SYNC 语句按顺序重放升级,将 apexapp 升级到 2.0,ordsapp 到 2.0,然后将 apexapp 升级到 3.0。在单独的语句中同步 apexapp 和 ordsapp 不会保留升级顺序。

    实践:在应用PDB中同步多个应用

    概述 此实践展示了当您必须在应用程序 PDB 中同步多个应用程序时如何减少同步语句的数量。 在以前的 Oracle 数据库版本中,您必须执行与应用程序一样多的同步语句。

    在开始任何新的实践之前,请参阅实践环境建议。

    第 1 步:设置环境 在 TOYS_ROOT 应用程序容器中为 ROBOTS 和 DOLLS 应用程序 PDB 安装 TOYS_APP 和 SALES_TOYS_APP 应用程序。 该脚本定义应用程序容器,在应用程序容器中安装两个应用程序,并在应用程序容器中创建两个应用程序 PDB。

    为了能够在 shell 脚本执行期间连接到 TOYS_ROOT、ROBOTS 和 DOLLS,请按照实践环境建议中的说明在 tnsnames.ora 文件中创建条目。

    执行shell脚本。

    $ cd /home/oracle/labs/M104780GC10
    $ /home/oracle/labs/M104780GC10/setup_apps.sh

    Copyright (c) 1982, 2020, Oracle. All rights reserved.

    Connected to:

    SQL> ALTER PLUGGABLE DATABASE toys_root CLOSE IMMEDIATE;

    Pluggable database altered.

    SQL> DROP PLUGGABLE DATABASE robots INCLUDING DATAFILES;

    Pluggable database dropped.

    SQL> DROP PLUGGABLE DATABASE dolls INCLUDING DATAFILES;

    Pluggable database dropped.

    SQL> DROP PLUGGABLE DATABASE toys_root INCLUDING DATAFILES;

    Pluggable database dropped.

    SQL> CREATE PLUGGABLE DATABASE toys_root AS APPLICATION CONTAINER
    2         FROM pdb21 KEYSTORE IDENTIFIED BY password;

    Pluggable database created.

    SQL> alter PLUGGABLE DATABASE toys_root open;

    Pluggable database altered.

    SQL> exit

    Copyright (c) 1982, 2020, Oracle. All rights reserved.

    Connected to:

    SQL> ALTER PLUGGABLE DATABASE APPLICATION toys_app begin install '1.0';

    Pluggable database altered.

    SQL> DROP TABLESPACE toys_tbs INCLUDING CONTENTS AND DATAFILES;
    DROP TABLESPACE toys_tbs INCLUDING CONTENTS AND DATAFILES
    *
    ERROR at line 1:
    ORA-00959: tablespace 'TOYS_TBS' does not exist


    SQL> CREATE TABLESPACE toys_tbs DATAFILE SIZE 100M autoextend on next 10M maxsize 200M ;

    Tablespace created.

    SQL> create user toys_owner identified by password container=all;

    User created.

    SQL> grant create session, dba to toys_owner;

    Grant succeeded.

    SQL>
    SQL> CREATE TABLE toys_owner.categories SHARING=DATA (c1 number, category varchar2(20));

    Table created.

    SQL> INSERT INTO toys_owner.categories VALUES (1,'GAMES');

    1 row created.

    SQL> INSERT INTO toys_owner.categories VALUES (2,'PUPPETS');

    1 row created.

    SQL> INSERT INTO toys_owner.categories VALUES (3,'VEHICLES');

    1 row created.

    SQL> COMMIT;

    Commit complete.

    SQL>
    SQL> ALTER PLUGGABLE DATABASE APPLICATION toys_app end install '1.0';

    Pluggable database altered.

    SQL>
    SQL> ALTER PLUGGABLE DATABASE APPLICATION sales_toys_app BEGIN INSTALL '1.0';

    Pluggable database altered.

    SQL>
    SQL> CREATE USER sales_toys IDENTIFIED BY password CONTAINER=ALL;

    User created.

    SQL> GRANT create session, dba TO sales_toys;

    Grant succeeded.

    SQL> ALTER USER sales_toys DEFAULT TABLESPACE toys_tbs;

    User altered.

    SQL> CREATE TABLE sales_toys.sales_data sharing=extended data
    2 (year       number(4),
    3   region     varchar2(10),
    4   quarter   varchar2(4),
    5   revenue   number);

    Table created.

    SQL> INSERT INTO sales_toys.sales_data VALUES (2019,'US','Q1',100000);

    1 row created.

    SQL> INSERT INTO sales_toys.sales_data VALUES (2019,'US','Q2',400000);

    1 row created.

    SQL> INSERT INTO sales_toys.sales_data VALUES (2019,'EU','Q2',50000);

    1 row created.

    SQL> INSERT INTO sales_toys.sales_data VALUES (2019,'ASIA','Q3',300000);

    1 row created.

    SQL> INSERT INTO sales_toys.sales_data VALUES (2019,'EU','Q3',20000);

    1 row created.

    SQL> COMMIT;

    Commit complete.

    SQL>
    SQL> ALTER PLUGGABLE DATABASE APPLICATION sales_toys_app END INSTALL '1.0';

    Pluggable database altered.

    SQL>
    SQL> exit

    Copyright (c) 1982, 2020, Oracle. All rights reserved.

    Connected to:

    SQL> create pluggable database robots ADMIN USER admin identified by password ROLES=(CONNECT) KEYSTORE IDENTIFIED BY password;

    Pluggable database created.

    SQL> create pluggable database dolls ADMIN USER admin identified by password ROLES=(CONNECT) KEYSTORE IDENTIFIED BY password;

    Pluggable database created.

    SQL>
    SQL> alter pluggable database robots open;

    Pluggable database altered.

    SQL> alter pluggable database dolls     open;

    Pluggable database altered.

    SQL>
    SQL> host rm -r /opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNAME/bak_cwallet

    SQL> host mkdir /opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNAME/bak_cwallet

    SQL> host mv /opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNAME/cwallet.sso /opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNAME/bak_cwallet/

    SQL> conn / as sysdba
    Connected.
    SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE close;

    keystore altered.

    SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE open IDENTIFIED BY password;

    keystore altered.

    SQL>
    SQL> ALTER SESSION SET CONTAINER=TOYS_ROOT;

    Session altered.

    SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE open IDENTIFIED BY password;

    keystore altered.

    SQL> ALTER SESSION SET CONTAINER=ROBOTS;

    Session altered.

    SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE open IDENTIFIED BY password;

    keystore altered.

    SQL> administer key management set key identified by password with backup;

    keystore altered.

    SQL> ALTER SESSION SET CONTAINER=DOLLS;

    Session altered.

    SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE open IDENTIFIED BY password;

    keystore altered.

    SQL> administer key management set key identified by password with backup;

    keystore altered.

    SQL> alter session set container=CDB$ROOT;

    Session altered.

    SQL> administer key management create AUTO_LOGIN keystore from keystore '/opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNAME' identified by password;

    keystore altered.

    SQL> exit

    Copyright (c) 1982, 2020, Oracle. All rights reserved.

    Connected to:

    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> exit

    Copyright (c) 1982, 2020, Oracle. All rights reserved.

    Connected to an idle instance.

    SQL> STARTUP
    ORACLE instance started.

    Total System Global Area 851440288 bytes
    Fixed Size                 9691808 bytes
    Variable Size             599785472 bytes
    Database Buffers         104857600 bytes
    Redo Buffers               19664896 bytes
    In-Memory Area           117440512 bytes
    Database mounted.
    Database opened.
    SQL> ALTER PLUGGABLE DATABASE all OPEN;

    Pluggable database altered.

    SQL> exit

     

    第 2 步:显示已安装的应用程序 显示已安装的应用程序。

    $ sqlplus / AS SYSDBA

    Connected to:

    SQL> COL app_name FORMAT A16
    SQL> COL app_version FORMAT A12
    SQL> COL pdb_name FORMAT A10
    SQL> SELECT app_name, app_version, app_status, p.pdb_name
        FROM   cdb_applications a, cdb_pdbs p
        WHERE a.con_id = p.pdb_id
        AND   app_name NOT LIKE '%APP$%'
        ORDER BY 1;

    APP_NAME         APP_VERSION APP_STATUS   PDB_NAME
    ---------------- ------------ ------------ ----------
    SALES_TOYS_APP   1.0         NORMAL       TOYS_ROOT
    TOYS_APP         1.0         NORMAL       TOYS_ROOT

    观察到 Toys_app 和 sales_toys_app 应用程序安装在版本 1.0 的应用程序容器中。

     

    第 3 步:同步应用程序 PDB 将应用程序 PDB 与新的 Toys_app 和 sales_toys_app 应用程序同步。

    SQL> CONNECT sys@robots AS SYSDBA
    Enter password:
    SQL> ALTER PLUGGABLE DATABASE APPLICATION toys_app, sales_toys_app SYNC;

    Pluggable database altered.
    显示应用程序容器中安装的应用程序。

    SQL> SELECT app_name, app_version, app_status, p.pdb_name
    FROM   cdb_applications a, cdb_pdbs p
    WHERE a.con_id = p.pdb_id
    AND   app_name NOT LIKE '%APP$%'
    ORDER BY 1;

    APP_NAME         APP_VERSION APP_STATUS   PDB_NAME
    ---------------- ------------ ------------ ----------
    SALES_TOYS_APP   1.0         NORMAL       ROBOTS
    TOYS_APP         1.0         NORMAL       ROBOTS

    SQL> CONNECT sys@dolls AS SYSDBA
    Enter password:
    SQL> ALTER PLUGGABLE DATABASE APPLICATION toys_app, sales_toys_app SYNC;

    Pluggable database altered.

    SQL> SELECT app_name, app_version, app_status, p.pdb_name
    FROM   cdb_applications a, cdb_pdbs p
    WHERE a.con_id = p.pdb_id
    AND   app_name NOT LIKE '%APP$%'
    ORDER BY 1;

    APP_NAME         APP_VERSION APP_STATUS   PDB_NAME
    ---------------- ------------ ------------ ----------
    SALES_TOYS_APP   1.0         NORMAL       DOLLS
    TOYS_APP         1.0         NORMAL       DOLLS

    SQL> CONNECT / AS SYSDBA
    Connected.
    SQL> SELECT app_name, app_version, app_status, p.pdb_name
    FROM   cdb_applications a, cdb_pdbs p
    WHERE a.con_id = p.pdb_id
    AND   app_name NOT LIKE '%APP$%'
    ORDER BY 1;

    APP_NAME         APP_VERSION APP_STATUS   PDB_NAME
    ---------------- ------------ ------------ ----------
    SALES_TOYS_APP   1.0         NORMAL       DOLLS
    SALES_TOYS_APP   1.0         NORMAL       ROBOTS
    SALES_TOYS_APP   1.0         NORMAL       TOYS_ROOT
    TOYS_APP         1.0         NORMAL       DOLLS
    TOYS_APP         1.0         NORMAL       TOYS_ROOT
    TOYS_APP         1.0         NORMAL       ROBOTS

    6 rows selected.

    Speak Your Mind

    *

    京ICP备14059771号-2