Practice: 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_APP and the SALES_TOYS_APP applications in the TOYS_ROOT application container for both ROBOTS and DOLLS application 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, ROBOTS and DOLLS, create entries in the tnsnames.ora file 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_app and sales_toys_app applications are installed in the application container at version 1.0.

Step 3 : Synchronize the application PDBs

  • Synchronize the application PDBs with the new toys_app and sales_toys_app applications.

    
    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
    $