Synchronizing Multiple Applications In Application PDBs
Overview
This practice shows how to reduce the number of synchronization statements when you have to synchronize multiple applications in application PDBs. In previous Oracle Database versions, you had to execute as many synchronization statements as applications.
Before starting any new practice, refer to the Practices Environment recommendations.
Step 1 : Set up the environment
Install the
TOYS_APPand theSALES_TOYS_APPapplications in theTOYS_ROOTapplication container for bothROBOTSandDOLLSapplication PDBs. The script defines the application container, installs the two applications in the application container, and creates the two application PDBs in the application container.To be able to connect during the shell script execution to
TOYS_ROOT,ROBOTSandDOLLS, create entries in thetnsnames.orafile as explained in the Practices Environment recommendations.Execute the shell script.
$ 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 $
Step 2 : Display the installed applications
Display the applications that have been installed.
$ 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 SQL>Observe that the
toys_appandsales_toys_appapplications are installed in the application container at version 1.0.
Step 3 : Synchronize the application PDBs
Synchronize the application PDBs with the new
toys_appandsales_toys_appapplications.SQL> CONNECT sys@robots AS SYSDBA Enter password: SQL> ALTER PLUGGABLE DATABASE APPLICATION toys_app, sales_toys_app SYNC; Pluggable database altered. SQL>Display the applications installed in the application container.
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. SQL> EXIT $
No comments:
Post a Comment