Native PL/SQL Application to Capture Source Code and Configuration Data
There are many software tools available to “dump” PL/SQL, SQL, and Data from a database into text files. ODBCapture does the same, but with a unique capability to create layered, or additive, installation scripts.
ODBCapture includes the unique capability to create complete database build scripts with all the required database objects and data. The build scripts captured by ODBCapture can be used to create a working, functioning database. Build layering of these different build scripts allow different databases to be built from the same database source code and data for different purposes like development, testing, and quality assurance. ODBCapture can also create build scripts that support different environments.
Examples of these build layers include:
ODBCapture includes the ability to define non-default Build Layers for Database Objects in order to place them in their proper installation scripts.
The ability to properly capture database objects in each Build layer requires specific information:
There are implications for this Build Layer information:
The ODBCapture installation scripts use Build Layering to accomodate different Database versions, editions, and options. Different implementations of the Oracle database can be missing dependent database objects for certain ODBCapture functions. Build Layers are configured to offer different installation options for various resources and services available in these different versions, editions, and options of Oracle database software.
ODBCapture Configuration Data (OCD) defines what database objects and data to capture. Each application will have its own OCD. Some OCD is included when ODBCapture is installed in a database.
It is important to note that OCD must be created to save the OCD for each application. OCD for the ODBCapture application is located in the “grbdat” Build Layer.
Best Practices:
Parallel Build Layer Paths are useful for things like environment settings where multiple databases need different parameters based on the usage. An example would be URLs to web services that differ between development, test, and UAT. Parallel Build Layer Paths can be created for each different URL setting that would allow some Build Layers to be installed without the others.
One caveat here is the need to connect Build Layer Paths throughout the installation sequence. To correctly filter database objects from the default Build Layer, all Build Layers must be connected to the default Build Layer thorugh Build Layer Paths. It can be useful to create an artificial Build Layer as an end point collector for parallel Build Layer Paths. This artificial Build Layer has no contents, would never be installed, but allows the necessary paths to the default Build Layer.
The Build Layer Sequencing determines precedence for which database objects get installed in a Build Layer. A unique sequence number is assigned to each Build Layer. The sequence number dictates the installation order for each Build Layer.
2 tables are used to define Build Layer Sequencing:
Aspects of Implementation:
This appendix is provided to give some indication of the complexity of various configurations of empty Oracle database based on:
Mike Dietrich (Oracle Vice President of Product Management and Development for Database Upgrade, Cloud Migrations and Patching) has a blog entry “Remove and Clean Up Components from Oracle Database 11.2 – 19c” that goes into these database options. At the bottom of the blog, Mike includes graphic that shows the order of removal of these components from a database.
Since this blog was written, Oracle has deprecated the XDB Repository. This is especially complicated because 12c required the XDB Repository. “XDB became a mandatory component since Oracle Database 12.1.0.1. You can’t have an Oracle 12c database without XDB”
In the blog referenced above, Mike includes a link to a query that shows the components installed in an Oracle database.
select CON_ID, COMP_ID, comp_name, schema, status, version from CDB_REGISTRY order by 1,2;
The following are component lists from different database installations.
Oracle Enterprise Edition 21.3:
| COMP_ID | COMP_NAME |
|---|---|
| APS | OLAP Analytic Workspace |
| CATALOG | Oracle Database Catalog Views |
| CATJAVA | Oracle Database Java Packages |
| CATPROC | Oracle Database Packages and Types |
| CONTEXT | Oracle Text |
| DV | Oracle Database Vault |
| JAVAVM | JServer JAVA Virtual Machine |
| LCTR | Oracle Locator |
| OLS | Oracle Label Security |
| ORDIM | Oracle Multimedia |
| OWM | Oracle Workspace Manager |
| RAC | Oracle Real Application Clusters |
| SDO | Spatial |
| XDB | Oracle XML Database |
| XML | Oracle XDK |
| XOQ | Oracle OLAP API |
Oracle Autonomous Database 21.3:
| COMP_ID | COMP_NAME |
|---|---|
| APEX | Oracle APEX |
| CATALOG | Oracle Database Catalog Views |
| CATPROC | Oracle Database Packages and Types |
| CONTEXT | Oracle Text |
| LCTR | Oracle Locator |
| DV | Oracle Database Vault |
| OLS | Oracle Label Security |
| RAC | Oracle Real Application Clusters |
| SDO | Spatial |
A review of the Oracle Database Technologies website reveals several different groupings for these components. NOTE: This part of Oracle’s website appears to be marketing driven and can be quite dynamic regarding content and details.