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?
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 clauseThat 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 userSharing=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>