Skip to Main Content
  • Questions
  • Multi Tenancy - Application SYNC to PDB

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ranjith.

Asked: April 23, 2020 - 12:30 pm UTC

Answered by: Connor McDonald - Last updated: May 14, 2020 - 4:08 am UTC

Category: Database Development - Version: 19c

Viewed 100+ times

You Asked

Hi Friends, I am trying to get the idea clear regarding Application Sync in Multi-Tenancy architecture.



I have an Application Container, and I started installing application

- created a user (application schema)

- created objects in it using sql*pus connected to sys of approot



Then when I sync the Application to a PDB, The user and objects get synced perfectly.



But If I directly connecting to the schema (during the install/upgrade mode) and create the objects, those objects are not getting synced. Am I doing something wrong here?. How will I create objects directly in the schema?



Here is my case:




[oracle@f816ab794845 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 21 03:41:06 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

--- Create Application container

SQL> CREATE PLUGGABLE DATABASE APPROOT AS APPLICATION CONTAINER ADMIN USER approot_admin IDENTIFIED BY Password1

DEFAULT TABLESPACE APPUSER_TS
DATAFILE '/u01/app/oracle/oradata/CDB1/APPROOT/APPUSER_TS.DBF' SIZE 250M AUTOEXTEND ON
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/CDB1/pdbseed/','/u01/app/oracle/oradata/CDB1/APPROOT/');  2    3    4

Pluggable database created.

SQL> ALTER PLUGGABLE DATABASE APPROOT OPEN;

Pluggable database altered.

SQL> ALTER SESSION SET container = APPROOT;

Session altered.

--- Create PDB where I want the application to be synced

SQL> CREATE PLUGGABLE DATABASE PDB1 ADMIN USER pdb_admin IDENTIFIED BY Password1
DEFAULT TABLESPACE APPUSER_TS
DATAFILE '/u01/app/oracle/oradata/CDB1/PDB1/APPUSER_TS.DBF' SIZE 250M AUTOEXTEND ON
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/CDB1/pdbseed/','/u01/app/oracle/oradata/CDB1/PDB1/');
  2    3    4

Pluggable database created.

SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN;

Pluggable database altered.

SQL> ALTER SESSION SET container = PDB1;

Session altered.

SQL> ALTER PLUGGABLE DATABASE APPLICATION ALL SYNC;

Pluggable database altered.

--- Install Application

SQL> ALTER SESSION SET container = APPROOT;

Session altered.

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

Pluggable database altered.

SQL> CREATE USER APP_USER IDENTIFIED BY Password1
  DEFAULT TABLESPACE APPUSER_TS
  QUOTA UNLIMITED ON APPUSER_TS
  CONTAINER=ALL;
  2    3    4

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO APP_USER;

Grant succeeded.

-- Here I created an object in APP_USER schema while I am connected to SYS user of Approot:

SQL> CREATE TABLE APP_USER.TEST_TB SHARING=DATA(n number);

Table created.

SQL> insert into  APP_USER.test_tb values (1);

1 row created.

SQL> commit;

Commit complete.

--- Now I connected directly to the user, and created another table TEST_TB_2.
SQL> conn APP_USER@"(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =xxxx )(PORT = nnnn  )) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = APPROOT)))"
Enter password:
Connected.

-- firstly I am not able to specify SHARING clause:

SQL> CREATE TABLE APP_USER.TEST_TB_2 SHARING=DATA(n number);

CREATE TABLE APP_USER.TEST_TB_2 SHARING=DATA(n number)

ERROR at line 1:
ORA-65021: illegal use of SHARING clause

-- So I created without the sharing clause. The default setting is metadata sharing.

SQL> CREATE TABLE APP_USER.TEST_TB_2 (n number);

Table created.

SQL> insert into  APP_USER.test_tb_2 values (2);

1 row created.

-- Here I insert a record to the table that was created using sys account:

SQL> insert into  APP_USER.test_tb  values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@f816ab794845 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 21 03:41:06 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0


-- End install

SQL> ALTER SESSION SET container = APPROOT;

Session altered.

SQL>  ALTER PLUGGABLE DATABASE APPLICATION APP END INSTALL;

Pluggable database altered.

-- Sync Application to the PDB

SQL> ALTER SESSION SET container = PDB1;

Session altered.

SQL> ALTER PLUGGABLE DATABASE APPLICATION APP SYNC;

Pluggable database altered.

-- The only object I see now is the first table TEST_TB that was created using sys account.

SQL> select object_name from all_objects where owner = 'APP_USER';

OBJECT_NAME
--------------------------------------------------------------------------------
TEST_TB

-- However, I can see the data that was inserted by the user.

SQL> select * from APP_USER.TEST_TB;

        N
----------
        1
        2
SQL>

 



Can you help to understand how can I create objects as an application user and not necessarily as SYS, and then sync it to the destination PDB?

and we said...

I'll comment on what I've done first, and my output is at the end of the question

1) firstly I am not able to specify SHARING clause

That is my understanding of how it it *meant* to be. The installation is performed by a privileged user, not by "normal" users, hence they do not get access to the sharing clause. See in my demo below - if I want to run DDL scripts as if they were the normal user you created, you can run the "set current_schema" in your session

2) I can see the data that was inserted by the user

Sharing=data means that all pdbs see a common view of the data, so the two rows should be expected.



SQL> conn / as sysdba
Connected.

SQL> CREATE PLUGGABLE DATABASE APPROOT AS APPLICATION CONTAINER
  2  ADMIN USER approot_admin IDENTIFIED BY Password1
  3  DEFAULT TABLESPACE APPUSER_TS
  4  DATAFILE 'X:\ORACLE\ORADATA\DB19\APPROOT\APPUSER_TS.DBF' SIZE 50M AUTOEXTEND ON
  5  FILE_NAME_CONVERT=('X:\ORACLE\ORADATA\DB19\PDBSEED\','X:\ORACLE\ORADATA\DB19\APPROOT\');

Pluggable database created.

SQL>
SQL> ALTER PLUGGABLE DATABASE APPROOT OPEN;

Pluggable database altered.

SQL> ALTER SESSION SET container = APPROOT;

Session altered.

SQL>
SQL> CREATE PLUGGABLE DATABASE APPPDB1 ADMIN USER pdb_admin IDENTIFIED BY Password1
  2  DEFAULT TABLESPACE APPUSER_TS
  3  DATAFILE 'X:\ORACLE\ORADATA\DB19\APPPDB1\APPUSER_TS.DBF' SIZE 50M AUTOEXTEND ON
  4  FILE_NAME_CONVERT=('X:\ORACLE\ORADATA\DB19\PDBSEED\','X:\ORACLE\ORADATA\DB19\APPPDB1\');

Pluggable database created.

SQL>
SQL> ALTER PLUGGABLE DATABASE appPDB1 OPEN;

Pluggable database altered.

SQL>
SQL> ALTER SESSION SET container = appPDB1;

Session altered.

SQL>
SQL> ALTER PLUGGABLE DATABASE APPLICATION ALL SYNC;

Pluggable database altered.

SQL>
SQL> ALTER SESSION SET container = APPROOT;

Session altered.

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

Pluggable database altered.

SQL> CREATE USER APP_USER IDENTIFIED BY Password1
  2    DEFAULT TABLESPACE APPUSER_TS
  3    QUOTA UNLIMITED ON APPUSER_TS
  4    CONTAINER=ALL;

User created.

SQL>
SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO APP_USER;

Grant succeeded.

SQL>
SQL> CREATE TABLE APP_USER.TEST_TB SHARING=DATA (n number);

Table created.

SQL>
SQL> insert into  APP_USER.test_tb values (1);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> alter session set current_schema = app_user;

Session altered.

SQL>
SQL> CREATE TABLE TEST_TB_2 (n number);

Table created.

SQL> insert into  test_tb_2 values (2);

1 row created.

SQL>
SQL> insert into  APP_USER.test_tb  values (2);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> conn / as sysdba
Connected.

SQL>
SQL> ALTER SESSION SET container = APPROOT;

Session altered.

SQL> ALTER PLUGGABLE DATABASE APPLICATION APP END INSTALL;

Pluggable database altered.

SQL>
SQL> ALTER SESSION SET container = appPDB1;

Session altered.

SQL>
SQL> ALTER PLUGGABLE DATABASE APPLICATION APP SYNC;

Pluggable database altered.

SQL>
SQL> select object_name from all_objects where owner = 'APP_USER';

OBJECT_NAME
----------------------------------------
TEST_TB
TEST_TB_2

2 rows selected.

SQL> select * from APP_USER.TEST_TB;

         N
----------
         1
         2

2 rows selected.

SQL>


and you rated our response

  (4 ratings)

Reviews

What privilege is required, other than having to use "SYS"

April 28, 2020 - 7:58 am UTC

Reviewer: A reader

Thank you Connor

Yup I had figured out we could use "alter session set current_schema". However we are connecting to SYS, that's what I was trying to avoid.

1) Is there any privilege that we can grant to a user so that they are a little more than a "normal" user but less than the Holy SYS.
2) If I want to impdp for importing objects in the application schema, instead of running DDLs using sql*plus how would I do it? Would I use impdp "sys / as sysdba" schemas=appuser ... ?


Connor McDonald

Followup  

May 01, 2020 - 7:41 am UTC

You should be able to use the admin account for the PDB, ditto for import


Set_module

May 13, 2020 - 2:55 pm UTC

Reviewer: A reader

Something I figured out today, and later read in documentation :)

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/administering-application-containers-with-sql-plus.html#GUID-2CE112B0-0F67-41DE-9072-AA78D043EC94

Apparently all it requires is to set DBMS_APPLICATION_INFO.SET_MODULE, before begin install/upgrade, and set the same module name in the "Normal user's" session, for it to register the statements for sync.


SQL> ALTER PLUGGABLE DATABASE APPLICATION APP SYNC;

Pluggable database altered.

SQL> select object_name from all_objects where owner = 'APP_USER';

OBJECT_NAME
--------------------------------------------------------------------------------
TEST_TB
TEST_TB_2


Connor McDonald

Followup  

May 14, 2020 - 4:08 am UTC

Well there you go!

Thanks very much for adding this review. That's why I love doing AskTOM - I learn lots of new stuff as well.

Set_module

May 13, 2020 - 2:56 pm UTC

Reviewer: A reader

Something I figured out today, and later read in documentation :)

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/administering-application-containers-with-sql-plus.html#GUID-2CE112B0-0F67-41DE-9072-AA78D043EC94

Apparently all it requires is to set DBMS_APPLICATION_INFO.SET_MODULE, before begin install/upgrade, and set the same module name in the "Normal user's" session, for it to register the statements for sync.


SQL> ALTER PLUGGABLE DATABASE APPLICATION APP SYNC;

Pluggable database altered.

SQL> select object_name from all_objects where owner = 'APP_USER';

OBJECT_NAME
--------------------------------------------------------------------------------
TEST_TB
TEST_TB_2


May 13, 2020 - 2:58 pm UTC

Reviewer: A reader

[oracle@d617b0f9a31e ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 13 14:32:39 2020
Version 19.7.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

SQL> CREATE PLUGGABLE DATABASE APPROOT AS APPLICATION CONTAINER ADMIN USER approot_admin IDENTIFIED BY Password1
DEFAULT TABLESPACE APPUSER_TS
DATAFILE '/u01/app/oracle/oradata/FCCMNMGR/APPROOT/APPUSER_TS.DBF' SIZE 250M AUTOEXTEND ON
FILE_NAME_CONVERT=('/opt/oracle/oradata/FCCMNMGR/','/opt/oracle/oradata/APPROOT/');  2    3    4

Pluggable database created.

SQL>
SQL> ALTER PLUGGABLE DATABASE APPROOT OPEN;

Pluggable database altered.

SQL> ALTER SESSION SET container = APPROOT;

Session altered.

SQL>
SQL> CREATE PLUGGABLE DATABASE PDB1 ADMIN USER pdb_admin IDENTIFIED BY Password1
DEFAULT TABLESPACE APPUSER_TS
DATAFILE '/u01/app/oracle/oradata/FCCMNMGR/PDB1/APPUSER_TS.DBF' SIZE 250M AUTOEXTEND ON
  2    3    4  FILE_NAME_CONVERT=('/opt/oracle/oradata/FCCMNMGR/','/opt/oracle/oradata/PDB1/');


Pluggable database created.

SQL>
SQL>
SQL>
SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN;

Pluggable database altered.

SQL> ALTER SESSION SET container = PDB1;

Session altered.

SQL>  ALTER PLUGGABLE DATABASE APPLICATION ALL SYNC;

Pluggable database altered.

SQL> ALTER SESSION SET container = APPROOT;

Session altered.

-- ######################################################################
-- SET MODULE before app begin install, 
-- and use the same module name in the other sessions !!
-- ######################################################################
SQL> exec DBMS_APPLICATION_INFO.SET_MODULE('MODU','ACTI');

PL/SQL procedure successfully completed.

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

Pluggable database altered.

SQL> CREATE USER APP_USER IDENTIFIED BY Password1  DEFAULT TABLESPACE  SYSTEM QUOTA UNLIMITED ON SYSTEM CONTAINER=ALL;

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO APP_USER;

Grant succeeded.

SQL>  CREATE TABLE APP_USER.TEST_TB SHARING=DATA(n number);

Table created.

SQL> insert into  APP_USER.test_tb values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
[oracle@d617b0f9a31e ~]$ sqlplus APP_USER/Password1@localhost:1521/APPROOT

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 13 14:43:40 2020
Version 19.7.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

-- ######################################################################
-- SET MODULE before app begin install, 
-- and use the same module name in the other sessions !!
-- ######################################################################

SQL>  exec DBMS_APPLICATION_INFO.SET_MODULE('MODU','ACTI');

PL/SQL procedure successfully completed.


SQL>  CREATE TABLE APP_USER.TEST_TB_2 SHARING=DATA(n number);

Table created.

-- ######################################################################
-- SHARING CLAUSE Worked because as the "normal user" because it identifies
-- the "Application Action" based on Module Name.
-- ######################################################################


SQL>  insert into  APP_USER.test_tb_2 values (2);

1 row created.

SQL>  insert into  APP_USER.test_tb  values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> exit;
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
[oracle@d617b0f9a31e ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 13 14:44:45 2020
Version 19.7.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

SQL>  exec DBMS_APPLICATION_INFO.SET_MODULE('MODU','ACTI');

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET container = APPROOT;

Session altered.

SQL>  exec DBMS_APPLICATION_INFO.SET_MODULE('MODU','ACTI');

PL/SQL procedure successfully completed.

SQL> ALTER PLUGGABLE DATABASE APPLICATION APP END INSTALL;

Pluggable database altered.

SQL> ALTER SESSION SET container = PDB1;

Session altered.

SQL> ALTER PLUGGABLE DATABASE APPLICATION APP SYNC;

Pluggable database altered.

SQL> select object_name from all_objects where owner = 'APP_USER';

OBJECT_NAME
--------------------------------------------------------------------------------
TEST_TB
TEST_TB_2

SQL>

-- ######################################################################
-- This is what i wanted :)
-- ######################################################################