Thursday 27 August 2020

Multitenant - Install an application in the application root database


  • Multitenant - Install an application in the application root database

oracle@srv4:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 25 17:37:16 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
         7 HR_AC                           READ WRITE NO

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


  • Install a new application in an application root 


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

Pluggable database altered.

SQL> CREATE TABLESPACE hr_tbs;

Tablespace created.

SQL> CREATE USER HR IDENTIFIED BY oracle DEFAULT TABLESPACE HR_TBS QUOTA UNLIMITED ON
HR_TBS CONTAINER = ALL;  2

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE
PROCEDURE, CREATE TRIGGER TO HR;  2

Grant succeeded.

SQL> ALTER SESSION SET CURRENT_SCHEMA=hr;

Session altered.

SQL> host mkdir /home/oracle/scripts

hr_app_v1.0.sql


  • Upload tables and data on hr_app_v1.0.sql file as per data accordingly : 

SQL> host vi /home/oracle/scripts/hr_app_v1.0.sql

SQL> host vi /home/oracle/scripts/hr_app_v1.0.sql


SQL> @/home/oracle/scripts/hr_app_v1.0.sql

Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


  • After executing query end application installation

SQL> ALTER PLUGGABLE DATABASE APPLICATION hr_app END INSTALL '1.0';

Pluggable database altered.

SQL> column app_name format a15
SQL> column app_version format a10
SQL> column app_status format a15
SQL>SELECT APP_NAME, APP_VERSION, APP_STATUS FROM DBA_APPLICATIONS WHERE APP_IMPLICIT='N';

APP_NAME        APP_VERSIO APP_STATUS
--------------- ---------- ---------------
HR_APP          1.0        NORMAL

Posted by at No comments:
Labels:

Multitenant- Create Application pluggable database on Application root Container database

Multitenant- Create Application pluggable database on Application root Container database


  • In this Sceanario we will create 2 application pluggable database HR_PDB1 and HR_PDB2


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


SQL> CREATE PLUGGABLE DATABASE hr_pdb1 admin user hr_pdb1adm identified by oracle;


Pluggable database created.

SQL> SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         7 HR_AC                          READ WRITE NO
         8 HR_PDB1                        MOUNTED

SQL> ALTER PLUGGABLE DATABASE hr_pdb1 OPEN;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE hr_pdb1 SAVE STATE;

Pluggable database altered.

SQL> SELECT STATUS FROM DBA_PDBS WHERE PDB_NAME='HR_PDB1';

STATUS
----------
NORMAL

SQL> ALTER SESSION SET CONTAINER=hr_pdb1;

Session altered.

SQL> ALTER PLUGGABLE DATABASE APPLICATION hr_app SYNC;

Pluggable database altered.


  • We connect to application pdb database and check status of tables that were created at root application container database.  We will share link:::::

SQL> conn hr/oracle@//srv4:1523/hr_pdb1
Connected.

SQL> SELECT TNAME FROM TAB ;

TNAME
--------------------------------------------------------------------------------
REGIONS
COUNTRIES
LOCATIONS
WAREHOUSES
EMPLOYEES
PRODUCT_CATEGORIES
PRODUCTS
CUSTOMERS
CONTACTS
ORDERS
ORDER_ITEMS

TNAME
--------------------------------------------------------------------------------
INVENTORIES

12 rows selected.

SQL> SELECT * FROM REGIONS ;

no rows selected

SQL> SELECT SEQUENCE_NAME FROM USER_SEQUENCES ;

SEQUENCE_NAME
--------------------------------------------------------------------------------
ISEQ$$_73166
ISEQ$$_73171
ISEQ$$_73174
ISEQ$$_73177
ISEQ$$_73180
ISEQ$$_73183
ISEQ$$_73186
ISEQ$$_73189
ISEQ$$_73192

9 rows selected.


  • Create another application PDB database hr_pdb2.

SQL> conn sys/system123@hr_ac as sysdba
Connected.
SQL> CREATE PLUGGABLE DATABASE hr_pdb2 admin user hr_pdb2adm identified by oracle;

Pluggable database created.

SQL> ALTER PLUGGABLE DATABASE hr_pdb2 OPEN;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE hr_pdb2 SAVE STATE;

Pluggable database altered.

SQL> ALTER SESSION SET CONTAINER=hr_pdb2;

Session altered.

SQL> ALTER PLUGGABLE DATABASE APPLICATION hr_app SYNC;

Pluggable database altered.

SQL> conn hr/oracle@//srv4:1523/hr_pdb2
Connected.
SQL> SELECT TNAME FROM TAB;

TNAME
--------------------------------------------------------------------------------
REGIONS
COUNTRIES
LOCATIONS
WAREHOUSES
EMPLOYEES
PRODUCT_CATEGORIES
PRODUCTS
CUSTOMERS
CONTACTS
ORDERS
ORDER_ITEMS

TNAME
--------------------------------------------------------------------------------
INVENTORIES

12 rows selected.


  • Check Application container root database 

oracle@srv4:~> sqlplus sys/system123@hr_ac as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 26 11:46:36 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
---------- ------------------------------ ---------- ----------
         7 HR_AC                          READ WRITE NO
         8 HR_PDB1                        READ WRITE NO
         9 HR_PDB2                        READ WRITE NO



  • Check overall status of container database including application root container database with there pdb.

oracle@srv4:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 26 11:46:56 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
         7 HR_AC                          READ WRITE NO
         8 HR_PDB1                        READ WRITE NO
         9 HR_PDB2                        READ WRITE NO
SQL>

Posted by at No comments:
Labels:

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



Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)