Creating DB for Continuous Integration

Submitted by code_admin on Wed, 07/25/2018 - 15:48

Oracle 12C Multitenant - setup CI Database and CI Database User

Create directory for tablespace on DB server:
(Delete if already there)
mkdir C:\oracle\products\oracledb_12_102\oradata\orcl\PORTABLE\CITESTDB_001\

Run as SYS user with SYSDBA role.

  1. create pluggable database "CITESTDB_001"
  2. admin user "CIDBADMIN" identified by "CIDBADMIN"
  3. file_name_convert = (
  4. 'C:\ORACLE\PRODUCTS\ORACLEDB_12_102\ORADATA\ORCL\PDBSEED\', 'C:\oracle\products\oracledb_12_102\oradata\orcl\PORTABLE\CITESTDB_001\'
  5. )
  6. ;
  7.  
  8. alter pluggable database "CITESTDB_001" open read write;
  9.  
  10. alter session set container="CITESTDB_001";
  11. grant sysdba to CIDBADMIN;
  12.  
  13. GRANT create user to CIDBADMIN WITH ADMIN OPTION;
  14. GRANT alter user to CIDBADMIN WITH ADMIN OPTION;
  15. GRANT drop user to CIDBADMIN WITH ADMIN OPTION;
  16. GRANT create session TO CIDBADMIN WITH ADMIN OPTION;
  17. GRANT alter session TO CIDBADMIN WITH ADMIN OPTION;
  18. GRANT alter system TO CIDBADMIN WITH ADMIN OPTION;
  19.  
  20. GRANT create any table TO CIDBADMIN WITH ADMIN OPTION;
  21. GRANT alter any table TO CIDBADMIN WITH ADMIN OPTION;
  22. GRANT drop any table TO CIDBADMIN WITH ADMIN OPTION;
  23. GRANT select any table TO CIDBADMIN WITH ADMIN OPTION;
  24. GRANT insert any table TO CIDBADMIN WITH ADMIN OPTION;
  25. GRANT delete any table TO CIDBADMIN WITH ADMIN OPTION;
  26. GRANT create any index TO CIDBADMIN WITH ADMIN OPTION;
  27. GRANT alter any index TO CIDBADMIN WITH ADMIN OPTION;
  28. GRANT drop any index TO CIDBADMIN WITH ADMIN OPTION;
  29.  
  30. GRANT create any view TO CIDBADMIN WITH ADMIN OPTION;
  31. GRANT drop any view TO CIDBADMIN WITH ADMIN OPTION;
  32. GRANT create any materialized view TO CIDBADMIN WITH ADMIN OPTION;
  33. GRANT drop any materialized view TO CIDBADMIN WITH ADMIN OPTION;
  34. GRANT create any trigger TO CIDBADMIN WITH ADMIN OPTION;
  35. GRANT alter any trigger TO CIDBADMIN WITH ADMIN OPTION;
  36. GRANT drop any trigger TO CIDBADMIN WITH ADMIN OPTION;
  37. GRANT create any procedure TO CIDBADMIN WITH ADMIN OPTION;
  38. GRANT alter any procedure TO CIDBADMIN WITH ADMIN OPTION;
  39. GRANT drop any procedure TO CIDBADMIN WITH ADMIN OPTION;
  40. GRANT create any sequence TO CIDBADMIN WITH ADMIN OPTION;
  41. GRANT alter any sequence TO CIDBADMIN WITH ADMIN OPTION;
  42. GRANT drop any sequence TO CIDBADMIN WITH ADMIN OPTION;
  43. GRANT create any synonym TO CIDBADMIN WITH ADMIN OPTION;
  44. GRANT drop any synonym TO CIDBADMIN WITH ADMIN OPTION;
  45.  
  46. GRANT lock any table TO CIDBADMIN WITH ADMIN OPTION;
  47. GRANT create tablespace TO CIDBADMIN WITH ADMIN OPTION;
  48. GRANT UNLIMITED TABLESPACE TO CIDBADMIN;
  49. grant select on DBA_DATA_FILES to CIDBADMIN;
  50. GRANT EXECUTE ANY PROCEDURE TO CIDBADMIN;

When using the OTN Virtualbox Appliance

(http://www.oracle.com/technetwork/database/enterprise-edition/databasea…)
the plugabble databases are
/u01/app/oracle/oradata/orcl12c

You must connect to the database with
user: sys
password: oracle
role: sysdba

  1. mkdir -p /u01/app/oracle/oradata/orcl12c/PORTABLE/CITESTDB_001

Then create the DB

  1. create pluggable database "CITESTDB_001"
  2. admin user "CIDBADMIN" identified by "CIDBADMIN"
  3. file_name_convert = (
  4. '/u01/app/oracle/oradata/orcl12c/pdbseed/', '/u01/app/oracle/oradata/orcl12c/PORTABLE/CITESTDB_001/PORTABLE/CITESTDB_001/'
  5. )
  6. ;

Tags

RJM Article Type
Work Notes