Multitenant- Create Local Users on current PDB

Multitenant- Create  Local Users on current PDB

• Current status of users with PDB name

SQL> set lines 200
SQL> col USERNAME for a40
SQL> SELECT U.USERNAME, P.PDB_NAME
FROM CDB_USERS U, CDB_PDBS P
WHERE U.CON_ID = P.CON_ID AND COMMON='NO'
ORDER BY 2,1;

USERNAME                                 PDB_NAME
---------------------------------------- ----------
INFODBA                                  PLM2
PDB1ADMIN                                PLM2
INFODBA                                  PLM4
TESTUSER                                 PLM4
INFODBA                                  PLM5

• Try to create common user on PLM2 PDB database using CONTAINER=ALL;  it will through error.

SQL> CREATE USER C##USER2 IDENTIFIED BY x CONTAINER=ALL;
CREATE USER C##USER2 IDENTIFIED BY x CONTAINER=ALL
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in root.

• Try to create local user with mentioning CONTAINER=CURRENT on PLM2 pdb database

SQL> CREATE USER  LUSER2 IDENTIFIED BY  LUSER2 CONTAINER=CURRENT;

User created.

• Test Connecting  luser2 onPLM2 pluggable database. 

SQL>
oracle@srv3:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin> sqlplus sys/system123@SMRCDB as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 13 18:57:35 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> connect LUSER2/ LUSER2@PLM2
Connected.
SQL> sho user
USER is "LUSER2"

• It will not connect as luser2 was created on plm2 db using container=current

SQL> connect LUSER2/ LUSER2@PLM3
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Warning: You are no longer connected to ORACLE.
SQL> connect LUSER2/ LUSER2@SMRCDB
ERROR:
ORA-01017: invalid username/password; logon denied

Multitenant - Create Common Users in CDB and PDB

Multitenant - Create  Common Users in CDB and PDB

• Create common user in CDB and connect it from PDB database

sqlplus / as sysdba


SQL> CREATE USER C##USER1 IDENTIFIED BY oracle CONTAINER=ALL;

User created.

SQL> GRANT CREATE SESSION TO C##USER1 CONTAINER=ALL;

Grant succeeded.


oracle@srv3:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin> sqlplus sys/system123@SMRCDB as sysdba


SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 13 18:38:46 2020

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production



SQL> select name,open_mode,database_Role from v$database;



NAME      OPEN_MODE            DATABASE_ROLE

--------- -------------------- ----------------

SMRCDB    READ WRITE           PRIMARY



SQL> sho pdbs



    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PLM1                           MOUNTED

         5 PLM2                           READ WRITE NO

         6 PLM4                           READ WRITE NO

         8 PLM5                           READ WRITE NO


SQL> connect C##USER1/oracle@SMRCDB
Connected.
SQL> connect C##USER1/oracle@PLM2
Connected.

Multitenant - Refreshable PDB Creation from local to remote CDB

CREATE PLUGGABLE DATABASE pdbref FROM PLM4@proxy_lnk  REFRESH MODE MANUAL;

Scenario Preview:

Create Refreshable PDB from one pdb to another , and it is always available in read only mode mainly used fro reporting purpose.

Refresh Mode Type  - Manual | Automatic

We have done practice manual in our case.


Hostname : SRV3 | Local CDB Name : SMRCDB | Local PDB : PLM4

Hostname : SRV4 | Remote CDB Name : STDRAC | Refresh PDB : PDBREF

• Connect remote database STDRAC  and create refreshable database PDBREF from PLM4 PDB database.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
oracle@srv4:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin>

 sqlplus sys/system123@STDRAC as sysdba



SQL> CREATE PLUGGABLE DATABASE pdbref FROM PLM4@proxy_lnk  REFRESH MODE MANUAL;

Pluggable database created.

• By default it is in mounted state  make it into read only mode. We cannot open refreshable pdb in read write mode.

SQL> ALTER PLUGGABLE DATABASE pdbref open read only;

Pluggable database altered.

• Check status of refreshable pdb.

SQL>col PDB_NAME format a10

SQL> SELECT CON_ID, PDB_NAME, STATUS, REFRESH_MODE FROM CDB_PDBS WHERE PDB_NAME='PDBREF';

    CON_ID PDB_NAME   STATUS     REFRES
---------- ---------- ---------- ------
         5 PDBREF     REFRESHING MANUAL

• Connect local pdb PLM4 and create user and table from it. once down shutdown refreshable pdb which is PDBREF and open it in read only mode we will see same  data should be reflected there.

oracle@srv3:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin> sqlplus sys/system123@SMRCDB as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 11 21:01:43 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> ALTER SESSION SET CONTAINER=PLM4;

Session altered.

SQL> CREATE TABLESPACE test_tbs;

Tablespace created.

SQL> CREATE USER testuser IDENTIFIED BY oracle DEFAULT TABLESPACE test_tbs QUOTA UNLIMITED ON test_tbs;

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE TO testuser;

Grant succeeded.

SQL> CREATE TABLE testuser.t1 ( RID NUMBER );

Table created.

SQL> INSERT INTO testuser.t1 VALUES (1);

1 row created.

SQL> COMMIT;

Commit complete.

• Now connect to refreshable PDB 

oracle@srv4:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin> sqlplus sys/system123@STDRAC as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 11 22:43:01 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> conn system/system123@//srv4:1523/PDBREF
Connected.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
oracle@srv4:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin> sqlplus sys/system123@STDRAC as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 11 22:45:22 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TEST1                          READ WRITE NO
         4 PLM1                           READ WRITE NO
         5 PDBREF                         READ ONLY  NO
         6 PROXYPDB                       READ WRITE NO

• Shutdown pdbref and refresh the database using command and open it into read only mode.

SQL> ALTER PLUGGABLE DATABASE PDBREF close immediate;

Pluggable database altered.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TEST1                          READ WRITE NO
         4 PLM1                           READ WRITE NO
         5 PDBREF                         MOUNTED
         6 PROXYPDB                       READ WRITE NO


SQL> alter session set container=PDBREF;

Session altered.

SQL> ALTER PLUGGABLE DATABASE REFRESH;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE OPEN READ ONLY;

Pluggable database altered.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 PDBREF                         READ ONLY  NO

SQL> SELECT NAME FROM V$TABLESPACE;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TEST_TBS

6 rows selected.

• Check the content of table.

SQL> SELECT * FROM TESTUSER.T1;

       RID
----------
         1


SQL> ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;

Pluggable database altered.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 PDBREF                         MOUNTED

SQL> ALTER PLUGGABLE DATABASE OPEN read only;

Pluggable database altered.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         5 PDBREF                         READ ONLY  NO