Multitenant - APPLICATION CONTAINER Database 12.2 version

Multitenant - APPLICATION CONTAINER Database 12.2 version

• Open container database STDRAC

oracle@srv4:~> . oraenv
ORACLE_SID = [SMRNONCDB] ? STDRAC
The Oracle base remains unchanged with value /u01/app/oracle
oracle@srv4:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 25 13:48:19 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

• Create APPLICATION root CONTAINER Database 12.2 version 

SQL> CREATE PLUGGABLE DATABASE hr_ac AS APPLICATION CONTAINER ADMIN USER hr_acadm IDENTIFIED BY oracle;

Pluggable database created.

SQL> ALTER PLUGGABLE DATABASE hr_ac OPEN;

Pluggable database altered.

• Check Application root  container database status

SQL> col name format a10
SQL>SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS WHERE APPLICATION_ROOT='YES';

    CON_ID NAME       OPEN_MODE
---------- ---------- ----------
         7 HR_AC      READ WRITE

SQL> ALTER PLUGGABLE DATABASE hr_ac SAVE STATE;

Pluggable database altered.

• Check status of root application container database

SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM CDB_DATA_FILES WHERE CON_ID=7;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/SMRCDB/STDRAC/ADB299969CDB0AB5E0534A38A8C0071F/datafile/
o1_mf_system_hn9vs1qd_.dbf
SYSTEM

/u01/app/oracle/oradata/SMRCDB/STDRAC/ADB299969CDB0AB5E0534A38A8C0071F/datafile/
o1_mf_sysaux_hn9vs1tf_.dbf
SYSAUX

/u01/app/oracle/oradata/SMRCDB/STDRAC/ADB299969CDB0AB5E0534A38A8C0071F/datafile/

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
o1_mf_undotbs1_hn9vs1tf_.dbf
UNDOTBS1


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
         7 HR_AC                          READ WRITE NO


SQL> ALTER SESSION SET CONTAINER=HR_AC;

Session altered.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         7 HR_AC                          READ WRITE NO

• Check granted roles and privileges

SQL> col grantee format a10
SQL>col granted_role format a15
SQL> SELECT GRANTEE, GRANTED_ROLE, COMMON FROM DBA_ROLE_PRIVS where GRANTEE ='HR_ACADM';

GRANTEE    GRANTED_ROLE    COM
---------- --------------- ---
HR_ACADM   PDB_DBA         NO

SQL> col role format a10
SQL>col privilege format a30
SQL> SELECT ROLE, PRIVILEGE, ADMIN_OPTION, COMMON, INHERITED FROM ROLE_SYS_PRIVS WHERE ROLE='PDB_DBA';

ROLE       PRIVILEGE                      ADM COM INH
---------- ------------------------------ --- --- ---
PDB_DBA    CREATE SESSION                 NO  NO  NO
PDB_DBA    SET CONTAINER                  NO  NO  NO
PDB_DBA    CREATE PLUGGABLE DATABASE      NO  NO  NO

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         7 HR_AC                          READ WRITE NO
SQL> sho parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      STDRAC
SQL> select service_id,name,pdb from v$services;

SERVICE_ID NAME
---------- ----------
PDB
--------------------------------------------------------------------------------
        19 hr_ac
HR_AC

• Connect root container database as an sysdba

SQL> conn sys/system123@hr_ac as sysdba
Connected.
SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         7 HR_AC                          READ WRITE NO