- Written by: ilmarkerm
- Category: Blog entry
- Published: October 2, 2024
In the previous post I looked how to create Oracle database home in OCI, using custom patches. Here I create a Oracle Base database on top of that custom home.
I was struggling for days with this, mostly because I first wanted to create database on LVM storage, but after running for many many hours, the provisioning got stuck always at 42%, without any error messages. Finally, with tons of experimentation, I found out that if I replace LVM with ASM storage, it all just works.
# List all availability domainsdata "oci_identity_availability_domains" "ads" {
compartment_id = oci_identity_compartment.compartment.id
}
# Create the Oracle Base database
resource "oci_database_db_system" "test23ai" {
# Waits until DB system is provisioned
# It can take a few hours
availability_domain = data.oci_identity_availability_domains.ads.availability_domains[0].name
compartment_id = oci_identity_compartment.compartment.id
db_home {
database {
admin_password = "correct#HorseBatt5eryS1-_ple"
character_set = "AL32UTF8"
#database_software_image_id = oci_database_database_software_image.db_23051.id
db_name = "test23ai"
pdb_name = "PDB1"
db_backup_config {
auto_backup_enabled = false
}
}
db_version = oci_database_database_software_image.db_23051.patch_set
database_software_image_id = oci_database_database_software_image.db_23051.id
}
hostname = "test23ai1"
shape = "VM.Standard.E5.Flex"
ssh_public_keys = ["ssh-rsa AAAAB3NzaC1yc2EAAA paste your own ssh public key here"]
subnet_id = oci_core_subnet.subnet.id
cpu_core_count = 2
data_storage_size_in_gb = 256
database_edition = "ENTERPRISE_EDITION"
db_system_options {
storage_management = "ASM" # LVM did not work for me, provisioning stuck at 42% for many many hours until it times out
}
display_name = "Test 23ai"
domain = "dev.se1"
#fault_domains = var.db_system_fault_domains
license_model = "LICENSE_INCLUDED"
node_count = 1
# Network Security Groups
#nsg_ids = var.db_system_nsg_ids
source = "NONE"
storage_volume_performance_mode = "BALANCED"
time_zone = "UTC"
}
And to see the connection options, they are visible from terraform state. Here we see database connection information, donnection strings in different formats are visible – so your terraform code could now take these values and store it in some service discovery/parameter store.
ilmar_kerm@codeeditor:oci-terraform-example (eu-stockholm-1)$ terraform state show oci_database_db_system.test23ai
# oci_database_db_system.test23ai:
resource "oci_database_db_system" "test23ai" {
availability_domain = "AfjF:EU-STOCKHOLM-1-AD-1"
compartment_id = "ocid1.compartment.oc1..aaaa"
cpu_core_count = 2
data_storage_percentage = 80
data_storage_size_in_gb = 256
database_edition = "ENTERPRISE_EDITION"
defined_tags = {
"Oracle-Tags.CreatedBy" = "default/ilmar.kerm@gmail.com"
"Oracle-Tags.CreatedOn" = "2024-10-02T14:57:03.606Z"
}
disk_redundancy = "HIGH"
display_name = "Test 23ai"
domain = "dev.se1"
fault_domains = [
"FAULT-DOMAIN-2",
]
freeform_tags = {}
hostname = "test23ai1"
id = "ocid1.dbsystem.oc1.eu-stockholm-1.anqxeljr4ebxpbqanr42p2zebku5hdk5nci2"
iorm_config_cache = []
license_model = "LICENSE_INCLUDED"
listener_port = 1521
maintenance_window = []
memory_size_in_gbs = 32
node_count = 1
reco_storage_size_in_gb = 256
scan_ip_ids = []
shape = "VM.Standard.E5.Flex"
source = "NONE"
ssh_public_keys = [
"ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQCvccb2GOc+VU6V0lw367a5sgKqn0epAok9vCVboK6WvQid6byo7hkWUSixIuB6ZPGG89n3ig4r my ssh public key",
]
state = "AVAILABLE"
storage_volume_performance_mode = "BALANCED"
subnet_id = "ocid1.subnet.oc1.eu-stockholm-1.aaaaaaaal6ru"
time_created = "2024-10-02 14:57:04.037 +0000 UTC"
time_zone = "UTC"
version = "23.5.0.24.07"
vip_ids = []
data_collection_options {
is_diagnostics_events_enabled = false
is_health_monitoring_enabled = false
is_incident_logs_enabled = false
}
db_home {
create_async = false
database_software_image_id = "ocid1.databasesoftwareimage.oc1.eu-stockholm-1.anqxeljr4ebxpbqa3h6dawz"
db_home_location = "/u01/app/oracle/product/23.0.0.0/dbhome_1"
db_version = "23.5.0.24.07"
defined_tags = {}
display_name = "dbhome20241002145704"
freeform_tags = {}
id = "ocid1.dbhome.oc1.eu-stockholm-1.anqxeljrb3hetziaby6hwudav"
state = "AVAILABLE"
time_created = "2024-10-02 14:57:04.042 +0000 UTC"
database {
admin_password = (sensitive value)
character_set = "AL32UTF8"
connection_strings = [
{
all_connection_strings = {
"cdbDefault" = "test23ai1.dev.se1:1521/test23ai_rf6_arn.dev.se1"
"cdbIpDefault" = "(DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.2.80)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=test23ai_rf6_arn.dev.se1)))"
}
cdb_default = "test23ai1.dev.se1:1521/test23ai_rf6_arn.dev.se1"
cdb_ip_default = "(DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.2.80)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=test23ai_rf6_arn.dev.se1)))"
},
]
database_software_image_id = "ocid1.databasesoftwareimage.oc1.eu-stockholm-1.anqxeljr4ebxpbqa3h6"
db_name = "test23ai"
db_unique_name = "test23ai_rf6_arn"
db_workload = "OLTP"
defined_tags = {
"Oracle-Tags.CreatedBy" = "default/ilmar.kerm@gmail.com"
"Oracle-Tags.CreatedOn" = "2024-10-02T14:57:03.740Z"
}
freeform_tags = {}
id = "ocid1.database.oc1.eu-stockholm-1.anqxeljr4ebxpbqac3v4lrr"
ncharacter_set = "AL16UTF16"
pdb_name = "PDB1"
pluggable_databases = []
state = "AVAILABLE"
time_created = "2024-10-02 14:57:04.043 +0000 UTC"
db_backup_config {
auto_backup_enabled = false
auto_full_backup_day = "SUNDAY"
recovery_window_in_days = 0
run_immediate_full_backup = false
}
}
}
db_system_options {
storage_management = "ASM"
}
}- Written by: ilmarkerm
- Category: Blog entry
- Published: September 29, 2024
I’ll continue exploring using OCI services with Terraform. Now it is time to start looking into databases. High Oracle PM-s have been lobbying for a database image creation service, where you just supply patch numbers and Oracle will return you the fully built database home. I see that this service is now available in the cloud (for cloud databases only).
I’ll try it out, using terraform.
resource "oci_database_database_software_image" "db_23051" {
# NB! Waits until image is provisioned
# This took 10m47s to provision
compartment_id = oci_identity_compartment.compartment.id
display_name = "23-db-23051"
image_shape_family = "VM_BM_SHAPE" # For use in Database Base service
# oci db version list
# NB! 23.0.0.0 seems to be behind on patches, 23.0.0.0.0 seems to be current
database_version = "23.0.0.0.0"
image_type = "DATABASE_IMAGE"
# Can't find how to query that list - but the format seems quite self-explanatory
# Exadata Cloud Service Software Versions (Doc ID 2333222.1)
patch_set = "23.5.0.24.07"
}I had hard time finding out the allowed values for parameter patch_set, but they seem to be described in Doc ID 2333222.1 (and what the contents are).
Examining the state of the created resource
ilmar_kerm@codeeditor:oci-terraform-example (eu-stockholm-1)$ terraform state show oci_database_database_software_image.db_23051
# oci_database_database_software_image.db_23051:
resource "oci_database_database_software_image" "db_23051" {
compartment_id = "ocid1.compartment.oc1..aaaaaaaasbzzr7i54kpv6oc5s7i23isiij6n2tyentd5udc34ptzagovrgqa"
database_software_image_included_patches = [
"35221462",
"36741532",
"36744688",
]
database_software_image_one_off_patches = [
"35221462",
"36741532",
"36744688",
]
database_version = "23.0.0.0.0"
defined_tags = {
"Oracle-Tags.CreatedBy" = "default/ilmar.kerm@gmail.com"
"Oracle-Tags.CreatedOn" = "2024-09-29T12:36:02.119Z"
}
display_name = "23-db-23051"
freeform_tags = {}
id = "ocid1.databasesoftwareimage.oc1.eu-stockholm-1.anqxeljr4ebxpbqadhgioquzxv6qtrui72e3sn3c7iwxcljncmdq7fx5jdbq"
image_shape_family = "VM_BM_SHAPE"
image_type = "DATABASE_IMAGE"
is_upgrade_supported = false
patch_set = "23.5.0.24.07"
state = "AVAILABLE"
time_created = "2024-09-29 12:36:02.123 +0000 UTC"
}One thing I notice here (verified with testing), that the parameter database_software_image_one_off_patches gets pre-populated with included patches after the image is created – so you have to include the included patches also the the parameter value.
With 19c version process is similar
resource "oci_database_database_software_image" "db_19241" {
# NB! Waits until image is provisioned
# This took 16m4s to provision
compartment_id = oci_identity_compartment.compartment.id
display_name = "19-db-19241"
image_shape_family = "VM_BM_SHAPE" # For use in Database Base service
# oci db version list
database_version = "19.0.0.0"
image_type = "DATABASE_IMAGE"
patch_set = "19.24.0.0"
}I did try to apply MRP on top of it, but maybe the cloud patch numbers are different, since the usual MRP patch number did not apply on top of it.
In the next post I’ll try to spin up an actual database using the image.
- Written by: ilmarkerm
- Category: Blog entry
- Published: August 21, 2024
I previously wrote about how after successfully downgrading APEX users still get the error “Application Express is currently unavailable”. I now ran to the same issue again, with newer versions and the procedure how ORDS detects is APEX is currently being patched has changed.
This post is about downgrading to APEX 23.1 and ORDS 24.1.
After completing APEX downgrade to 23.1, ORDS 24.1 still reported that “Application Express is currently unavailable”. I followed my own previous blog and flipped the APEX internal patching status to APPLIED, but no luck this time. PANIC! I turned on tracking for ORDS sessions and this PL/SQL block seems to be the culprit.
DECLARE
NM OWA.VC_ARR := :1 ;
VL OWA.VC_ARR := :2 ;
L_PATCH VARCHAR2(10) := null;
L_VERSION VARCHAR2(100) := null;
SCHEMA VARCHAR2(30);
PART1 VARCHAR2(30);
PART2 VARCHAR2(30);
DBLINK VARCHAR2(30);
PART1_TYPE NUMBER(10);
OBJECT_NUM NUMBER(10);
BEGIN
OWA.INIT_CGI_ENV(:3 , NM, VL);
HTP.INIT;
HTP.HTBUF_LEN := 63;
--CHECK THE SCHEMA OVERRIDE FOR PL/SQL GATEWAY APPLICATION DETAILS
DECLARE
GATEWAY_DETAILS VARCHAR2(2000) := q'[ SELECT
(SELECT VALUE FROM ORDS_METADATA.USER_ORDS_PROPERTIES WHERE KEY = 'plsql.gateway.patching') as patchingValue,
(SELECT VALUE FROM ORDS_METADATA.USER_ORDS_PROPERTIES WHERE KEY = 'plsql.gateway.version') as versionValue
FROM dual]';
BEGIN
BEGIN
EXECUTE IMMEDIATE GATEWAY_DETAILS INTO L_PATCH, L_VERSION;
EXCEPTION
WHEN OTHERS THEN
-- ignore exception and leave l_patch as null so fallback is executed
NULL;
END;
IF L_PATCH IS NULL THEN
-- L_PATCH IS NULL, EITHER AN OLDER VERSION OF APEX IS PRESENT OR APEX IS NOT INSTALLED
-- CHECK IF APEX PRESENT
DECLARE
L_SQL VARCHAR2(200) := 'select patch_applied from apex_release';
BEGIN
DBMS_UTILITY.NAME_RESOLVE('APEX_RELEASE', 0, SCHEMA, PART1, PART2, DBLINK, PART1_TYPE, OBJECT_NUM);
EXECUTE IMMEDIATE L_SQL INTO L_PATCH;
EXCEPTION
WHEN OTHERS THEN
-- Could not access apex_release. Default l_patch to 'N' so it's still served
L_PATCH :='N';
END;
END IF;
END;
:4 := L_PATCH;
:5 :=L_VERSION;
END;The flag that tells ORDS that APEX is currently being patched has been moved to ORDS_METADATA schema. And in my case the query indeed returned TRUE.
SELECT VALUE FROM ORDS_METADATA.USER_ORDS_PROPERTIES WHERE KEY = 'plsql.gateway.patching'After flipping it to FALSE, downgraded APEX started working again.
UPDATE ORDS_METADATA.USER_ORDS_PROPERTIES set value='FALSE' WHERE KEY = 'plsql.gateway.patching';- Written by: ilmarkerm
- Category: Blog entry
- Published: July 29, 2024
We are refreshing development databases from production using storage thin cloning. Everything works automatically and part of refresh procedure is also running some SQLPlus scripts, using OS authentication, as SYS.
One database also has APEX, that uses APEX Social Login feature to give users single sign-on for both APEX Builder and the end user application. You can read here how to set it up using Azure SSO. But since APEX is fully inside the database, this means that the production SSO credentials get copied over during database refresh.
I would like to have a SQL script that would run as part of the database refresh procedure, that would replace the APEX SSO credentials. And I’d like that script to run as SYS.
- Written by: ilmarkerm
- Category: Blog entry
- Published: July 18, 2024
We have been using Single-Sign On for APEX applications for a long time, way before there was social login feature available in APEX itself. We implemented it on web server level in front of APEX. But in order to simplify the webserver setup (mainly to disable sticky load balancer setup) and since APEX for quite some time can do some SSO protocols now internally – we finally moved to APEX social sign-in – using OpenID connector. I blogged about it here earlier.
Enabled it last evening, everything worked fine over night (low use), and suddenly, this morning, when the real use begins, application users started getting the following login failures:
The HTTP request to “https://login.microsoftonline.com/<tenancy_id>/v2.0/.well-known/openid-configuration” failed.
Quite un-informative… why did it fail? This URL is the OpenID discovery URL and the message comes from APEX, not Azure SSO. A few quick checks:
- The URL works, and it returns proper 200 OK status code, even from the database server
- If there were any TLS errors, then the error message would be different
- No new messages in APEX_DEBUG_MESSAGES since the issue started
- Nothing in APEX_WEBSERVICE_LOG
- Nothing in alert.log 🤔
- Changing APEX instance proxy did not help
- TLS wallet was accessible and good
- The error is returned ONLY to application users, even though builder uses the same OpenID Discovery URL – and APEX builder login works without any issues
- And every developer swore, that noone has changed anything today
As a side-note – the end-user applications are quite heavily used and there are hundreds of internal users.
Finally I started creating new Authentications scheme using Generic OAuth2 instead and they seemed to connect to Azure services without issues. But then messages started appearing in APEX_DEBUG_MESSAGES (what triggered that DEBUG logging started writing? accessing applications via Builder?) and hidden deep in messages I found the gem:
Exception in "GET https://login.microsoftonline.com/azure_tenancy_id/v2.0/.well-known/openid-configuration":
Error Stack: ORA-20001: You have exceeded the maximum number of web service requests per workspace. Please contact your administrator.Looks like, at least in APEX 23.1, calls to OpenID discovery URL count towards the APEX workspace web service request limiter, after increasing the limit the authentication started working again.
exec APEX_INSTANCE_ADMIN.SET_PARAMETER('MAX_WEBSERVICE_REQUESTS', 100000);Problem solved, but it was quite confusing to troubleshoot due to lack of more detailed error messages. I really do not expect authentication hitting a rate limiter and then being completely silent about the underlying cause.