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
$ /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
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.
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