sidb with loadBalancer=true Unhealth and unavalable

[rbaumgar]

When I create a sidb (enterprise, free, xe, ..) it works fine until I set loadBalancer = true.
db orperator is namespace based.

No error in operator log!

siddb.yaml

apiVersion: database.oracle.com/v1alpha1
kind: SingleInstanceDatabase
metadata:
  name: siddb
spec:
  sid: ORCL1
  edition: enterprise
  createAs: primary
  primaryDatabaseRef: ""
  adminPassword:
    secretName: siddb-admin-secret
  charset: AL32UTF8
  pdbName: orclpdb1
  flashBack: false
  archiveLog: false
  forceLog: false
  tcpsCertRenewInterval: 8760h
  initParams:
    cpuCount: 0
    processes: 0
    sgaTarget: 0
    pgaAggregateTarget: 0
  image:
    pullFrom: container-registry.oracle.com/database/enterprise:21.3.0.0
    pullSecrets: ocirsecret
    prebuiltDB: true
  persistence:
    size: 10Gi
    storageClass: nfs-csi-storage
    datafilesVolumeName: ""
    scriptsVolumeName: ""
  resources:
    requests:
      cpu: 200m
      memory: 2Gi
    limits: 
      cpu: 1
      memory: 8Gi
  loadBalancer: false
  serviceAccountName: 'sidb-sa'
  replicas: 1

status:

  archiveLog: "false"
  charset: AL32UTF8
  clusterConnectString: siddb-ext.oracle:1521/ORCL1
  conditions:
  - lastTransitionTime: "2024-09-28T17:41:14Z"
    message: processing datapatch execution
    observedGeneration: 1
    reason: LastReconcileCycleBlocked
    status: "True"
    type: ReconcileBlocked
  - lastTransitionTime: "2024-09-28T17:59:53Z"
    message: no reconcile errors
    observedGeneration: 3
    reason: LastReconcileCycleQueued
    status: "True"
    type: ReconcileQueued
  - lastTransitionTime: "2024-09-28T17:59:57Z"
    message: no reconcile errors
    observedGeneration: 3
    reason: LastReconcileCycleCompleted
    status: "True"
    type: ReconcileComplete
  connectString: 192.168.50.15:32489/ORCL1
  createdAs: primary
  datafilesCreated: "true"
  datafilesPatched: "true"
  edition: Enterprise
  flashBack: "false"
  forceLog: "false"
  initParams:
    cpuCount: 4
    pgaAggregateTarget: 512
    processes: 300
    sgaTarget: 1536
  isTcpsEnabled: false
  oemExpressUrl: https://192.168.50.15:30162/em
  pdbConnectString: 192.168.50.15:32489/ORCLPDB1
  pdbName: orclpdb1
  persistence:
    accessMode: ReadWriteOnce
    setWritePermissions: true
    size: 10Gi
    storageClass: nfs-csi-storage
  prebuiltDB: true
  releaseUpdate: 21.3.0.0.0
  replicas: 1
  role: PRIMARY
  sid: ORCL1
  status: Healthy
  tcpsConnectString: Unavailable
  tcpsPdbConnectString: Unavailable
  tcpsTlsSecret: ""

status after loadBalancer=true

  archiveLog: "false"
  charset: AL32UTF8
  clusterConnectString: siddb-ext.oracle:1521/ORCL1
  conditions:
  - lastTransitionTime: "2024-09-28T17:41:14Z"
    message: processing datapatch execution
    observedGeneration: 1
    reason: LastReconcileCycleBlocked
    status: "True"
    type: ReconcileBlocked
  - lastTransitionTime: "2024-09-28T17:59:57Z"
    message: no reconcile errors
    observedGeneration: 3
    reason: LastReconcileCycleCompleted
    status: "True"
    type: ReconcileComplete
  - lastTransitionTime: "2024-09-28T18:10:07Z"
    message: no reconcile errors
    observedGeneration: 4
    reason: LastReconcileCycleQueued
    status: "True"
    type: ReconcileQueued
  connectString: Unavailable
  createdAs: primary
  datafilesCreated: "true"
  datafilesPatched: "true"
  edition: Enterprise
  flashBack: "false"
  forceLog: "false"
  initParams:
    cpuCount: 4
    pgaAggregateTarget: 512
    processes: 300
    sgaTarget: 1536
  isTcpsEnabled: false
  oemExpressUrl: Unavailable
  pdbConnectString: Unavailable
  pdbName: orclpdb1
  persistence:
    accessMode: ReadWriteOnce
    setWritePermissions: true
    size: 10Gi
    storageClass: nfs-csi-storage
  prebuiltDB: true
  releaseUpdate: 21.3.0.0.0
  replicas: 1
  role: PRIMARY
  sid: ORCL1
  status: Updating
  tcpsConnectString: Unavailable
  tcpsPdbConnectString: Unavailable
  tcpsTlsSecret: ""

[ilfur]

Hello Robert, please do send some oraoperator pod logs along anyway, it seems as if it's waiting for something (an IP address from the loadbalancer?) and will not update the database status but also wont error out then. Can You also see a service of type LoadBalancer which maybe is PENDING , waiting for an IP address ?
But perhaps its an OpenShift permission problem again, so some logs would be great...

[rbaumgar]

you are right, svc extrnal-ip is pending. But I don't want external IP...

$ oc get svc -n oracle
NAME              TYPE           CLUSTER-IP      EXTERNAL-IP   PORT(S)                         AGE
siddb             ClusterIP      172.30.30.189   <none>        1521/TCP                        40h
siddb-ext         LoadBalancer   172.30.206.29   <pending>     5500:30162/TCP,1521:32489/TCP   40h
$ oc get svc -n oracle siddb-ext -o yaml
apiVersion: v1
kind: Service
metadata:
  creationTimestamp: "2024-09-28T17:40:18Z"
  labels:
    app: siddb
  name: siddb-ext
  namespace: oracle
  ownerReferences:
  - apiVersion: database.oracle.com/v1alpha1
    blockOwnerDeletion: true
    controller: true
    kind: SingleInstanceDatabase
    name: siddb
    uid: 4dbf1a88-cd09-41b7-83e6-df2559aea9e4
  resourceVersion: "2614998387"
  uid: 20187167-43a2-4969-baf7-27d02bd0a3b8
spec:
  allocateLoadBalancerNodePorts: true
  clusterIP: 172.30.206.29
  clusterIPs:
  - 172.30.206.29
  externalTrafficPolicy: Cluster
  internalTrafficPolicy: Cluster
  ipFamilies:
  - IPv4
  ipFamilyPolicy: SingleStack
  ports:
  - name: xmldb
    nodePort: 30162
    port: 5500
    protocol: TCP
    targetPort: 5500
  - name: listener
    nodePort: 32489
    port: 1521
    protocol: TCP
    targetPort: 1521
  selector:
    app: siddb
  sessionAffinity: None
  type: LoadBalancer
status:
  loadBalancer: {}

Operator log when "loadBalancer=true"

2024-09-30T09:59:18Z INFO bash -c echo -e "SELECT 'log_mode:' || log_mode AS log_mode ,'flashback_on:' || flashback_on AS flashback_on ,'force_logging:' || force_logging AS force_logging FROM v\$database;" | sqlplus -s / as sysdba {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "ExecCommand": {"name":"siddb","namespace":"oracle"}}
2024-09-30T09:59:18Z INFO CheckModes Output {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "CheckDBParams": {"name":"siddb","namespace":"oracle"}}
2024-09-30T09:59:18Z INFO
LOG_MODE FLASHBACK_ON
--------------------- -------------------------------
FORCE_LOGGING
-----------------------------------------------------
log_mode:NOARCHIVELOG flashback_on:NO
force_logging:NO
{"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "CheckDBParams": {"name":"siddb","namespace":"oracle"}}
2024-09-30T09:59:18Z INFO FlashBackStatus {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "CheckDBParams": {"name":"siddb","namespace":"oracle"}, "Status :": false}
2024-09-30T09:59:18Z INFO ArchiveLogStatus {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "CheckDBParams": {"name":"siddb","namespace":"oracle"}, "Status :": false}
2024-09-30T09:59:18Z INFO ForceLoggingStatus {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "CheckDBParams": {"name":"siddb","namespace":"oracle"}, "Status :": false}
2024-09-30T09:59:18Z INFO Executing Command : {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "ExecCommand": {"name":"siddb","namespace":"oracle"}}
2024-09-30T09:59:18Z INFO bash -c echo -e "SELECT 'log_mode:' || log_mode AS log_mode ,'flashback_on:' || flashback_on AS flashback_on ,'force_logging:' || force_logging AS force_logging FROM v\$database;" | sqlplus -s / as sysdba {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "ExecCommand": {"name":"siddb","namespace":"oracle"}}
2024-09-30T09:59:19Z INFO CheckModes Output {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "CheckDBParams": {"name":"siddb","namespace":"oracle"}}
2024-09-30T09:59:19Z INFO
LOG_MODE FLASHBACK_ON
--------------------- -------------------------------
FORCE_LOGGING
-----------------------------------------------------
log_mode:NOARCHIVELOG flashback_on:NO
force_logging:NO
{"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "CheckDBParams": {"name":"siddb","namespace":"oracle"}}
2024-09-30T09:59:19Z INFO FlashBackStatus {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "CheckDBParams": {"name":"siddb","namespace":"oracle"}, "Status :": false}
2024-09-30T09:59:19Z INFO ArchiveLogStatus {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "CheckDBParams": {"name":"siddb","namespace":"oracle"}, "Status :": false}
2024-09-30T09:59:19Z INFO ForceLoggingStatus {"controller": "singleinstancedatabase", "controllerGroup": "database.oracle.com", "controllerKind": "SingleInstanceDatabase", "SingleInstanceDatabase": {"name":"siddb","namespace":"oracle"}, "namespace": "oracle", "name": "siddb", "reconcileID": "c9ad6767-35c9-42c4-9ffc-eec1d022469f", "CheckDBParams": {"name":"siddb","namespace":"oracle"}, "Status :": false}
2024-09-30T09:59:19Z INFO controllers.database.SingleInstanceDatabase Flashback {"updateDBConfig": {"name":"siddb","namespace":"oracle"}, "Status :": false}
2024-09-30T09:59:19Z INFO controllers.database.SingleInstanceDatabase ArchiveLog {"updateDBConfig": {"name":"siddb","namespace":"oracle"}, "Status :": false}
2024-09-30T09:59:19Z INFO controllers.database.SingleInstanceDatabase ForceLog {"updateDBConfig": {"name":"siddb","namespace":"oracle"}, "Status :": false}

[IshaanDesai45]

@rbaumgar I don' think the problem is from the operator code. So if the cloud provider requires some service annotation for the load balancer then you have to give that in the singleinstancedatabase config yaml file.

Can I know which provider are you using ? And please try just to deploy a load balancer on the provider to check what annotation are required

[rbaumgar]

@IshaanDesai45 it's on OpenShift and I don't want an external IP. I only want an IP from the pod network.

[IshaanDesai45]

So you would need to create a private/internal load balancer if you don't want an external IP. OpenShift might have some service annotations for creating the same like OKE have it here

[rbaumgar]

What do you think is needed to create a service in OpenShift? No annotation is required.
Here is an example

kind: Service
apiVersion: v1
metadata:
  labels:
    app: oracle-crud-jvm
  name: oracle-crud-jvm
spec:
  ports:
    - name: 8080-tcp
      protocol: TCP
      port: 8080
      targetPort: 8080
  selector:
    app: oracle-crud-jvm
    deployment: oracle-crud-jvm

$ oc get svc oracle-crud-jvm 
NAME              TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)    AGE
oracle-crud-jvm   ClusterIP   172.30.52.248   <none>        8080/TCP   10s

[psaini79]

@rbaumgar please try to create the service and let me know if you still see the issue.

[rbaumgar]

@psaini79 see entry from Oct 25.

[psaini79]

@rbaumgar thanks for the reply and sorry I didn't realize you already tried. Since you need only internal service to connect to the DB pod and you also used the selectors, did the service you create helped? Also, you want to to connect to DB on port 8080? If you need this connection for DB connection then DB port is 1521.

[rbaumgar]

@psaini79 Yes, creating a service helped. Yes, you are right. Port 1521 has to be used. Not a perfect example.

[psaini79]

Thanks @rbaumgar . Closing this issue, please open new one if you have any question.

[rbaumgar]

@psaini79, I don't know why you closed this issue. It is not solved; I only presented a workaround.

[psaini79]

I closed as it seems the problem from openshift side. I think your main concern that it should happen automatically from Oracle DB operator and in this case you did it manually. Let me test this scenario on openshift. I have following questions before I replicate the problem:

• Environment details of your openshift i.e. Oracle Cloud or any other
• openshift version